Inventory stock model (ADR)

Executive summary

On-hand quantity for a store is authoritative in stocks (warehouse_id × item_id × optional item_variation_id, column available_qty). The column items.stock is not a second source of truth — it is a materialized aggregate (read cache) updated only by backend sync after warehouse stock moves.

Phase 1 (this document): lock the model and naming so product, inventory, and POS teams do not treat items.stock as editable truth.

Do not drop items.stock in Phase 1. Hundreds of list filters, POS guards, finance KPIs, and legacy paths read it. Removal is a later migration once aggregation is conversion-aware and all writers go through one path.

---

Goals and non-goals

Goals	Non-goals
One mental model: warehouse rows = truth, item row = cache	Remove items.stock column immediately
Clarify which warehouses feed items.stock today	Full UOM conversion in aggregate (Phase 2)
Plan API “virtual” display without breaking SQL sorts	Change POS deduction in Phase 1

---

Source of truth

stocks (per warehouse, per item, optional item_variation_id)
  available_qty  ← receipts, adjustments, transfers, POS decrement, returns restock

item_variations
  SKU metadata (unit, pack, base_variation_id) — no stock column

items.variations (JSON)
  Legacy per-variant `stock` in JSON — still updated on some POS paths; target: display-only cache (Phase 4)

items.stock (DECIMAL)
  Materialized sum for catalog list / low-stock / POS aggregate guard — NOT manually authoritative

Rule: Any inventory mutation must update stocks first (or in the same transaction). items.stock is refreshed by VendorProductsService.syncAggregatedStockForStoreScope (and related callers), not by arbitrary product PATCH bodies except legacy admin flows that explicitly sync warehouses.

---

What items.stock means today (code-backed)

Property	Current behavior
Warehouses included	Store default warehouse(s) only (resolveDefaultWarehouseIdsForStores), not every warehouse in the store
Grouping	SUM(available_qty) grouped by item_id only (item_variation_id rows with a variation id are folded into the parent item row)
UOM / conversion	Raw sum — does not normalize case vs each via units_per_pack / base_variant
Writers	applyAggregatedStockUpdatesToItems after sync; some legacy paths still touch JSON variations[].stock or items.stock on POS without item_variation_id
Readers	Product list, low-stock (listLowStock), auto-purchase cron, POS validateAggregatedCartStock, finance low-stock when store filter is “all”, order return restock message

Constants and comments in backend: item-stock-semantics.constants.ts, catalog-item-stock-string.util.ts, ItemEntity.stock JSDoc.

---

Virtual field vs updating items.stock

Option A — Virtual stock on API only (computed at read time)

• Pros: No drift; impossible to “fix stock” on the product form and forget warehouses.
• Cons: Every product list/search/low-stock query needs a subquery or join; harder indexes; large refactor across Nest, vendor-web, admin, mobile/Laravel if still coupled.

Option B — Keep items.stock as materialized cache (recommended for this codebase)

• Pros: Fast filters (WHERE stock <= threshold), minimal change to existing screens; single writer (syncAggregatedStock*) can be fixed in Phase 2.
• Cons: Must discipline writers; label clearly in UI/docs so staff do not edit “stock” on catalog expecting warehouse truth.

Option C — items.stock = sum across all warehouses

• Different product meaning than today (default warehouse only). Use only if UX explicitly needs “total company on-hand” on the catalog row; otherwise prefer:
  • items.stock (or rename later to default_warehouse_stock) — default WH, base UOM (Phase 2)
  • total_stock_all_warehouses — optional API field computed on product detail / report, not the low-stock cron unless policy changes

Decision (Phase 1): Option B — retain the column as derived cache with documented scope; treat API stock as read-only derived in new features (do not accept arbitrary stock on vendor product PATCH without warehouse lines). Long-term, expose the same value as a “virtual” field in OpenAPI descriptions while the DB column remains for performance until Phase 4.

Not recommended: Let vendors edit items.stock directly while stocks rows disagree — that recreates two truths.

---

Two-field display model: stock + total_stock (recommended naming)

Yes — keeping stock on items and adding total_stock is clearer for vendors if each field has a fixed meaning and labels in the UI match.

Field	Meaning	Warehouses	Variants	Used for
stock (existing column)	Sellable quantity for operations tied to default warehouse (POS aggregate guard, low-stock cron, catalog list badge)	Default warehouse only (current code)	Folded into one item-level number in base retail units (Phase 2; raw sum today)	Low stock, auto-purchase draft, “how much can we sell from main WH”
total_stock (new)	Company-wide on-hand for the product in the store	All active warehouses	All stocks rows for this item_id (every item_variation_id), converted to one base unit before sum (Phase 2)	Product detail, reports, “total in all branches” — not the low-stock cron unless product changes policy

Implementation preference

1. Phase 2a: expose total_stock on API as a computed field on product list/detail (SUM over stocks with conversion), no new DB column yet — avoids two caches drifting.
2. Phase 2b (optional): add items.total_stock materialized column only if list screens need ORDER BY total_stock at scale; same sync job as stock, second pass with warehouse_scope = all.

Do not define total_stock as a naive SUM(available_qty) across case + each rows without UOM normalization — that repeats today’s over-count risk.

UI copy (example)

• stock → “Default warehouse” / “Kho chính”
• total_stock → “All warehouses” / “Tất cả kho”

Per-variant breakdown stays on warehouse stock screens and optional variations[].stock display cache — not a third duplicate total on items.

stock_scope query param

The product list endpoint accepts an optional stock_scope (default default):

Value	Filter / sort driver	When to use
default	items.stock (the default-warehouse cache shown in the table)	Day-to-day operations tied to the default warehouse — POS guard, low-stock cron, “what can I sell from main WH”
all	Per-item SUM(stocks.available_qty) across every warehouse owned by the vendor / accessible store list (matches total_stock)	Company-wide visibility on the catalog grid: e.g. out of stock at HQ but full at branch should not appear in a vendor’s “out of stock” band

Behavior

• stock_scope only affects the stock state band filter (stock_state ∈ {in_stock, low_stock, out_of_stock}) and the stock_asc / stock_desc sort. Other filters (category, status, supplier, etc.) are scope-independent.
• When a warehouse_id filter is set, the warehouse-specific path takes priority and stock_scope is ignored.
• The frontend only sends stock_scope when it actually matters (a stock-state filter or stock sort is active) and only when it differs from default. This keeps default URLs clean and the cached path fast.
• total_stock on the list payload always reflects all warehouses regardless of stock_scope — only the filter / sort input changes.

---

Conversion units (case / each / pack)

Conversion metadata lives on the same items.variations[] row as the sellable catalog SKU — e.g. one Case row with is_conversion_unit: true, units_per_pack: 24, and optional unpack/receiving flags. The product form must not create a second ghost row (legacy Conv + separate Case).

Pattern	Meaning
Unified row	variant = unit label (e.g. Case); is_conversion_unit: true; conversion flags on that row
Orphan (legacy)	Separate row with variant: Conv and base_variant pointing at another catalog row — merged away on load/save via mergeOrphanConversionRowsIntoCatalog

The Variants table lists unified conversion catalog rows once. Delete is disabled for rows with conversion metadata (is_conversion_unit / pack flags); edit pack settings in Conversion units.

Conversion rows expose three exclusive flags per row (stored in JSON, not separate DB columns):

JSON flag	UI label	Meaning
is_default_sell_unit	Selling unit	Default sell UOM for POS on this conversion row
is_default_import_unit	Receiving unit	Default purchase / receiving UOM
is_unpack_unit	Unpack	Pack/case row used for automatic break-case when retail sellable stock in base units is insufficient

Shared toggle helpers: packages/shared/src/product-form/conversion-unit-flags.util.ts (applyDefaultSellUnitSelection, applyDefaultImportUnitSelection, applyUnpackUnitSelection, findUnpackConversionRow).

Today POS can compute virtual sellable quantity in base retail units (pos-sellable-stock.util.ts, conversion catalog link) — e.g. 0 gói + 5 thùng×24 → 120 sellable gói on the sale screen — while syncAggregatedStockForStoreIds still raw-sums stocks.available_qty. That can over-count when both a case row and an each row hold quantity for the same product.

Implemented (post-sale): After checkout decrement, when the sold variant’s warehouse row hits the low-stock band, the backend auto-unpacks from the is_unpack_unit row when case stock exists; otherwise eligible items (auto_add_purchase_on_low_stock) are appended to the store’s draft purchase order after commit. Pre-checkout validation still uses the virtual sellable pool; unpack is a write path triggered by low stock, not mid-cart.

Phase 2: Aggregation and low-stock should use the same base-unit pool logic as POS (or stock only on the catalog / default variation row in stocks).

Phase 3: POS always decrements via item_variation_id → one stocks row per line.

Phase 4: Stop treating JSON variations[].stock as inventory truth.

---

Low stock and auto-purchase cron

• Band: 0 < items.stock <= effective_threshold (per-item low_stock_threshold or store dashboard_prefs.pos.low_stock_threshold, default 10).
• Cron: VendorLowStockAutoPurchaseDraftCron every 30 minutes — sync aggregate → listLowStock with auto_add_purchase_only=1 → merge into draft lot note = auto_low_stock_purchase.
• POS post-sale: same draft lot when a sold variant is still low after checkout (and optional auto-unpack) — tryAppendItemsToAutoPurchaseDraft runs after order commit for items with auto_add_purchase_on_low_stock = 1.
• Phase 2 consideration: Evaluate low stock per stocks row (SKU + warehouse) instead of flattened items.stock when conversion is fixed.

See Inventory (Vendor) for routes and env CRON_LOW_STOCK_AUTO_PURCHASE_ENABLED.

---

Catalog form vs adjustments

On-hand quantity in stocks.available_qty is the SSOT. The vendor product form splits quantity from warehouse settings:

Field	Create product	Edit product
Stock quantity	Opening stock on the Warehouse tab → persisted as an adjustment with reason: opening_balance	Editable on the Warehouse tab; PATCH sends product_warehouse_stocks[].qty / variation_warehouse_stocks[].qty and the backend applies a manual_correction adjustment when the target differs from stocks.available_qty. Optional Adjust still opens POST /vendor/adjustments.
Stock alert (low_stock_threshold)	Saved with the product	Saved with the product (PATCH /vendor/products/:id)
Location	Saved with the product	Saved with the product

PATCH /vendor/products/:id rejects legacy top-level stock with stock_qty_not_editable_via_catalog. Per-warehouse quantity must use product_warehouse_stocks / variation_warehouse_stocks (with qty). Admin master-catalog PATCH /admin/products/:id upserts qty on warehouse lines directly when admin_only warehouse rows are sent.

Adjustment reason values

opening_balance, manual_correction, recount, damage, theft, transfer_correction, other (optional on POST /vendor/adjustments; defaults to manual_correction).

---

Phased roadmap

Phase	Deliverable
1 (done)	This ADR; entity/util comments; no schema drop
1b (done)	Catalog form: editable qty on edit (vendor via adjustments on save); opening balance on create; PATCH rejects top-level stock only
2 (done)	syncAggregatedStock* uses conversion-aware base units; API total_stock (all warehouses); stock = default-warehouse cache
2b (optional)	Materialized items.total_stock column if list sort needs it
2c (done)	API stock_scope query param on product list (default | all) so vendors can filter / sort by company-wide stock without changing the cache semantics; UI exposed as Stock scope picker on admin and vendor product list
3a (done)	PATCH warehouse lines include qty on edit (vendor applies manual_correction adjustments); create opening balance via adjustments (reason: opening_balance, createdType: admin on admin catalog create); top-level stock on PATCH rejected
3 (in progress)	POS single deduction path via item_variation_id. resolvePosCartLineItemVariationIds upgrades JSON-only variant lines to the SSOT path before placeOrderWithStock runs the decrement. Post-sale replenishment: warehouse low-stock auto-unpack (is_unpack_unit) + post-commit auto purchase draft for flagged items still low.
4	Deprecate JSON variant stock; remove legacy product_warehouse_stocks qty alias on PATCH after observation window; optional DB migration to drop items.stock after all readers use computed stock

---

Test / audit checklist (Phase 2 prep)

Check	Action
Same item_id with item_variation_id IS NULL and non-null variation rows	SQL audit; consolidate or exclude from raw sum
Product form saves stock without warehouse lines	Confirm PATCH does not bypass stocks
POS line without item_variation_id	Phase 3 resolver (resolvePosCartLineItemVariationIds) promotes the line to the SSOT path when its variation.variant matches a registered item_variations row; remaining unresolved lines (true JSON-only variants) still hit the items.stock cache path and are tracked for Phase 4
Multi-warehouse store	Confirm low-stock uses default WH only (documented above); stock_scope=all is the documented escape hatch when product / vendor needs catalog filters/sort across warehouses

---

Related

• Inventory (Vendor) — warehouses, lots, adjustments, cron
• Catalog and products — variations, units/pack, auto low-stock flag
• Catalog — variant sell unit and pack