Inventory Stock

1. Overview

Property	Value
ID	FEAT-INV-STOCK
Status	Stable
Owner	inventory-team
Depends on	Material / ProductVariant (polymorphic principal), InventoryLocation

The stock model is three layers — InventoryItem (polymorphic principal × merchant), InventoryStock (per-location bucket), InventoryIdentifier (SKU / barcode / serial / etc.). The atomic mutator InventoryStockRepository.adjustStock performs read+write in a single SQL UPDATE with optional forceNonNegative guard.

2. Entity Model

InventoryItem

Field	Type	Required	Description
merchantId	text	✓	Owner
itemType	text	✓	MATERIAL / PRODUCT_VARIANT
itemId	text	✓	FK target id (polymorphic, no DB FK)
identifier	text	✓	Auto, INI prefix
status	text	✓	InventoryItemStatuses (default ACTIVATED)

Idempotent upsert: InventoryItemRepository.ensureInventoryItem({ merchantId, itemType, itemId }) — keyed by partial unique index (merchantId, itemType, itemId) WHERE deletedAt IS NULL.

InventoryStock — bucket per (item × location × lot × serial)

Field	Type	Required	Description
inventoryItemId	text	✓	FK
inventoryLocationId	text	✓	FK
merchantId	text	✓	Denormalized
quantityOnHand	decimal(15,4)	✓	Total physical stock
quantityReserved	decimal(15,4)	✓	Reserved for orders
quantityAvailable	decimal(15,4)	✓	Stored field; service maintains as onHand − reserved
lotNumber	text		Bucket-key extension
serialNumber	text		Bucket-key extension
expiryDate	timestamptz		FEFO support (data only)
manufactureDate	timestamptz		—
lastCountedAt	timestamptz		Last cycle count
lastStockedAt	timestamptz		Last receipt
averageCost	decimal(15,4)		AVCO snapshot
costingMethod	text	✓	Default AVERAGE
metadata	jsonb	✓	Per-bucket config — IInventoryStockMetadata: { allowOversell?: boolean; lowStockThreshold?: number }. lowStockThreshold is a per-location override of the InventoryItem.metadata default.

Bucket key: unique (inventoryItemId, inventoryLocationId, lotNumber, serialNumber) with NULLS NOT DISTINCT — rows with NULL lot/serial still count as duplicates.

InventoryIdentifier — polymorphic tag

Field	Type	Required	Description
principalType	text	✓	INVENTORY_ITEM / INVENTORY_STOCK
principalId	text	✓	FK target id
scheme	text	✓	SKU / BARCODE / QRCODE / IMEI / SERIAL
value	text	✓	Identifier string

Constraint: unique (scheme, value) per principal.

3. Lifecycle

InventoryStock rows have no status — existence implies an active bucket. Rows are soft-deletable but typically only deleted as part of merchant offboarding.

InventoryItem is also stateless — its lifecycle mirrors the principal (Material / ProductVariant).

4. Operations

Read

Method	Source	Purpose
find / findById	InventoryStockService (CRUD)	Merchant-scoped read
Bulk-load by item + location	Service-internal	Used by workers; avoids N+1

Write — atomic

Method	Signature	Purpose
InventoryStockRepository.adjustStock	{ stockId, adjustOnHand?, adjustAvailable?, adjustReserved?, forceNonNegative? }	Single SQL UPDATE; returns post-state or null if guard fails
InventoryService.seedInventoryAcrossLocations	{ merchantId, itemType, itemId, baseUomId, locations?, ... }	Idempotent multi-location seed (used at merchant onboard, ProductVariant create)
InventoryService.receiveInventoryFromPurchaseOrderItem	{ purchaseOrderId, merchantId, inventoryLocationId, item, transaction }	Increment from PO receive; writes tracking with effectivePrice

adjustStock SQL semantics

UPDATE "InventoryStock"
SET quantity_on_hand = quantity_on_hand + :adjustOnHand,
    quantity_available = quantity_available + :adjustAvailable,
    quantity_reserved = quantity_reserved + :adjustReserved
WHERE id = :stockId
  AND ( :forceNonNegative = false
        OR (
            quantity_on_hand + :adjustOnHand >= 0
            AND quantity_available + :adjustAvailable >= 0
            AND quantity_reserved + :adjustReserved >= 0
        ) )
RETURNING quantity_on_hand, quantity_available, quantity_reserved;

Outcome	Behavior
Guard satisfied → row updated	Returns { quantityOnHand, quantityAvailable, quantityReserved }
Guard fails → 0 rows updated	Returns null — caller writes OVERSELL_BLOCKED tracking note and skips
forceNonNegative=false	Always succeeds; allows negative quantities

See ADR-0003 for rationale.

5. REST Endpoints

Verb	Path	Auth	Permission	Handler
6× CRUD	/inventory-items	JWT/BASIC	InventoryItem.<crud>	merchant-scoped
GET	/inventory-items/list	JWT/BASIC	InventoryItem.find	InventoryItemService.getList — slim list + summary (left pane of Stock Edit)
GET	/inventory-items/list/count	JWT/BASIC	InventoryItem.count	InventoryItemService.getCount — total only, mirrors /products/list/count
GET	/inventory-items/{id}/stocks	JWT/BASIC	InventoryItem.find	InventoryItemService.getStocks — per-bucket detail for the right pane
PATCH	/inventory-items/{id}/stocks/{inventoryStockId}	JWT/BASIC	InventoryStock.updateById	InventoryItemService.updateStock — atomic single-bucket patch
6× CRUD	/inventory-stocks	JWT/BASIC	InventoryStock.<crud>	merchant-scoped
GET	/inventory-stocks/overview	JWT/BASIC	InventoryStock.find	InventoryStockService.getOverview
6× CRUD	/inventory-identifiers	JWT/BASIC	InventoryIdentifier.<crud>	merchant-scoped

No mutation endpoints for adjustStock are exposed — stock changes happen only via PurchaseOrder.receive, sale-payment Kafka, kitchen-ticket Kafka, or InventoryTicket.complete.

GET /inventory-stocks/overview — Stock List dashboard

Powers the four KPI cards on the Inventory > Stock List screen. The four metrics are fanned out from the repository in parallel.

Query

Name	Required	Default	Description
merchantId	✓	—	Must fall inside the caller's Casbin merchant scope (admin / always-allowed bypass).
inventoryLocationId		—	Narrow stock and needAttention to a single location. The variant + location counts ignore it.

Low-stock threshold is not a query input. It's a two-tier inventory-domain config: per-location override on InventoryStock.metadata.lowStockThreshold, item-level default on InventoryItem.metadata.lowStockThreshold, system fallback 5. Per-bucket readers resolve COALESCE(stock override, item default, 5). Seeded at item creation from the aggregate's inventory.lowStockThreshold; the default is edited live via PATCH /inventory-items/{id} ({ metadata: { lowStockThreshold } }) and the override via the stock PATCH below.

Response — InventoryStockOverviewResponse

Card	Field	Source
Storable Variant	storableVariant.total	COUNT(*) ProductVariant WHERE type ∈ {STORABLE, MANUFACTURED} (merchant-scoped).
Storable Variant ("tracked")	storableVariant.tracked	Same filter + metadata.inventory.isInventoryTracked = true.
Location	location.total / physical / simulation	COUNT(*) InventoryLocation with FILTERs by type. total = physical + simulation.
Stock	stock.totalOnHand	SUM(quantity_on_hand) across every live InventoryStock bucket in scope. Returned as a numeric-precision string.
Stock	stock.totalValue	SUM(quantity_on_hand × COALESCE(average_cost, 0)) — monetary inventory value across every live bucket in scope. NULL average_cost is treated as 0 (untracked cost). Returned as a numeric-precision string.
Need Attention	needAttention.out	quantity_available ≤ 0 — empty or negative.
Need Attention	needAttention.oversell	quantity_available < 0 — negative; the more specific subset of out. Happens when the variant allows oversell and a sale / reservation pushed stock below zero.
Need Attention	needAttention.low	0 < quantity_available <= threshold, where threshold is COALESCE((stock.metadata->>lowStockThreshold)::numeric, (inv_item.metadata->>lowStockThreshold)::numeric, 5). Per-bucket — the stock row's override wins, else the InventoryItem default, else 5.
Need Attention	needAttention.total	out + low — distinct buckets needing attention. oversell is a subset of out, so it is not added again.

The out / oversell / low conditions here are the same ones used by the /inventory-items/list per-item flags — both come from the shared stock-posture.sql fragments. The only difference is the rollup: the overview counts buckets, the item list does BOOL_OR per item.

Authorization: reuses InventoryStock.find since this is a read-only aggregate over the same rows. The service rejects mis-scoped merchants with HTTP 403.

Caching: response is cached in cache-redis for 60 seconds keyed per (merchantId, inventoryLocationId). The merchant-scope check runs before the cache lookup, so a cached entry can never be returned to an unauthorised caller. Cache failures fall through to the live query — Redis is best-effort, never a hard dependency.

GET /inventory-items/list — Stock Edit left pane

Paginated list. Slim row + aggregated summary; per-bucket detail is fetched lazily via the /stocks endpoint below.

Query (mirrors CRUD find)

Name	Description
filter[where][merchantId]	Required — the screen's merchant picker. The query is scoped to this single merchant.
filter[where][itemType]	ProductVariant / Material — omit for both.
filter[order]	Whitelist: name · id · identifier · status · itemType · createdAt · modifiedAt. Default: name ASC NULLS LAST, id ASC (stable tiebreaker).
filter[limit] / filter[offset]	Page size (max 250) / offset.

where[merchantId] is required (400 if missing) and validated against the caller's scope (403 if outside it; admin / always-allowed callers may read any merchant). /inventory-items/list/count takes the same where[merchantId] requirement.

Response — InventoryItemListResponse (array of InventoryItemListRowResponse)

Field	Source
id / identifier / status / merchantId	InventoryItem columns
itemId / itemType	The polymorphic principal — ProductVariant.id or Material.id
itemName	COALESCE(ProductInfo.name, Material.name) — i18n jsonb. PV name comes from ProductInfo(principalType=ProductVariant), Material name from Material.name directly.
identifiers[]	{ scheme, identifier } chips — from ProductIdentifier (PV) or MaterialIdentifier (Material). Polymorphic source flattened by the SQL projection; FE doesn't branch.
summary.total	{ location: COUNT(DISTINCT inventory_location_id), quantity: SUM(onHand), value: SUM(onHand × averageCost) }
summary.onHand / summary.reserved	{ quantity, value } slices
needAttention	{ out, low, oversell } booleans — per-location (BOOL_OR across the item's buckets): true if ANY bucket hits the condition. out = ∃ bucket available ≤ 0 (empty or negative); oversell = ∃ bucket available < 0 (the more specific subset of out); low = ∃ bucket 0 < available ≤ COALESCE(stock override, item default, 5) (same per-bucket threshold cascade as the overview's low). Flags are independent / can overlap (a negative bucket sets both out and oversell; an item can be low at one location and oversell at another); all false = healthy. Items with no stock rows → all false. Note: these conditions are identical to the /inventory-stocks/overview cards (both derive from the shared stock-posture.sql fragments — out = available ≤ 0, oversell = available < 0 ⊂ out, low on the same threshold cascade). The only difference is the rollup: the overview counts buckets, this list does BOOL_OR per item.

Pagination total comes back in the Content-Range HTTP response header (records 0-49/123). Body shape obeys the x-request-count-data toggle (same convention as CRUD find):

Header x-request-count-data	Response body
omitted / true	{ data: [...], count: <page-row-count> }
false	[...] (bare array)

The list + count queries run inside one transaction so they see the same snapshot — range.total always agrees with the rows on the page.

GET /inventory-items/list/count — total companion

Same where filter shape as CRUD count. Returns { count: number }. FE pagination footer reads from here when it just needs a fresh total without re-fetching rows.

GET /inventory-items/{id}/stocks — Stock Edit right pane

One row per InventoryStock × InventoryLocation bucket for a single InventoryItem. Each row is self-contained (location nested + principal's uom + per-bucket allowOversell + the resolved lowStockThreshold tiers) so the FE renders the edit pane without extra lookups.

Response — InventoryStocksResponse (array of InventoryStockLocationRowResponse)

Field	Source
stock.id	InventoryStock.id — needed by FE to build the PATCH URL /inventory-items/{id}/stocks/{stockId}
location.id / identifier / type / name / isDefault	InventoryLocation joined on stock.inventory_location_id
uom	COALESCE(ProductVariant.uom, Material.uom) — { base, purchase, sale }
allowOversell	COALESCE((stock.metadata→>allowOversell)::boolean, false) — per-bucket
lowStockThreshold	{ default, byItem, byStock } numeric strings: default = system 5; byItem = COALESCE(item default, 5) (effective item-level); byStock = COALESCE(stock override, item default, 5) (the threshold actually in effect for this bucket). FE shows byStock as the active value and compares it to byItem to tell whether the bucket overrides the item default. All tiers jsonb_typeof = 'number' guarded (non-number → degrade to next tier).
averageCost	InventoryStock.average_cost (string)
onHand / reserved / available	{ quantity, value } where value = quantity × averageCost

Rows are ordered default-location-first then by location id. Merchant scope is enforced via the parent InventoryItem.merchant_id so an out-of-scope caller cannot read another merchant's stock by guessing ids.

PATCH /inventory-items/{id}/stocks/{inventoryStockId} — atomic single-bucket patch

Body — all fields optional, only provided fields change:

{
  "onHand": "12.5",
  "reserved": "3",
  "averageCost": "40000",
  "allowOversell": true,
  "lowStockThreshold": 4.5
}

Single SQL statement — no read-then-write race. quantity_available is re-derived inside the same UPDATE as quantity_on_hand - quantity_reserved so the three counters stay coherent regardless of which fields the caller touched. allowOversell and lowStockThreshold are shallow-merged into the row's metadata jsonb via ||, so unrelated metadata keys are preserved. lowStockThreshold is the per-location override (decimal allowed); it wins over the InventoryItem default in per-bucket low-stock readers:

UPDATE inventory."InventoryStock"
SET quantity_on_hand   = COALESCE($onHand,         quantity_on_hand),
    quantity_reserved  = COALESCE($reserved,       quantity_reserved),
    quantity_available = COALESCE($onHand,         quantity_on_hand)
                       - COALESCE($reserved,       quantity_reserved),
    average_cost       = COALESCE($averageCost,    average_cost),
    metadata           = COALESCE(metadata, '{}'::jsonb)
                       || COALESCE($metadataPatch::jsonb, '{}'::jsonb)
WHERE id = $stockId
  AND inventory_item_id = $itemId   -- enforces stock-belongs-to-item
  AND merchant_id IN ($scope)       -- server-side scope guard
  AND deleted_at IS NULL
RETURNING *;

The service translates top-level allowOversell / lowStockThreshold into the metadata-patch object (only present keys are included), e.g. { allowOversell: true } → metadata = metadata || '{"allowOversell": true}'. Omitting a field from the body leaves its existing value untouched.

Strict-mode pre-check. Before the UPDATE fires, the service runs a findOne to load the row and predict the post-patch state. If allowOversell would be false after the patch (either set by the body or inherited from the row's current metadata) AND any of the predicted onHand, reserved, or available = onHand − reserved is < 0, the request is rejected with HTTP 409 and messageCode: server.inventory.inventory_stock.update.oversell_disable_requires_non_negative. The caller must either fix the quantities in the same PATCH (e.g. send a non-negative onHand alongside allowOversell: false) or keep allowOversell: true. This prevents leaving a bucket in a state the sale-deduct guard can never recover from.

If RETURNING yields 0 rows → HTTP 404 with messageCode: server.inventory.inventory_stock.find.not_found (bad stockId, mismatched itemId, or out-of-scope merchant — all collapse into the same not-found response).

Still on the principal, not the stock row:

Field	Update via
Base UoM	PATCH /product-variants/{id}/aggregate (or material aggregate) — uom.base is shared across all the principal's stock buckets.

Source of truth for allowOversell

The flag previously lived on ProductVariant.metadata.inventory.allowOversell / Material.metadata.inventory.allowOversell. As of migration 0009_charming_armor it lives on InventoryStock.metadata.allowOversell (per location) — runtime reads at the deduct/reserve hot path source from the stock row.

The principal's metadata.inventory.allowOversell is now seed-only: read once at first-time stock creation (CDC handler / Material aggregate create) and never again. Editing it after stocks have been seeded has no runtime effect — use this PATCH endpoint per location instead.

Editing lowStockThreshold (two-tier)

• Per-location override → this stock PATCH (lowStockThreshold in the body). Wins for the bucket it targets.
• Item-level default → PATCH /inventory-items/{id} with { "metadata": { "lowStockThreshold": 30 } } (the standard InventoryItem CRUD update, merchant-scoped). Applies to every bucket that has no override.
• Seed at create → the aggregate's inventory.lowStockThreshold (PV via CDC, Material in-process) writes the InventoryItem default once at creation; later catalog edits don't propagate (use the item update above).
• System fallback 5 when neither tier is set.

(As of migration 0010_mellow_threshold the threshold no longer lives on ProductVariant/Material metadata at runtime.)

6. Events

Inbound stock-mutating Kafka topics (handled by workers, not via HTTP):

Topic	Worker	Stock effect
payment.success	InventoryWorkerService.handlePaymentSuccess	−quantity for product items; reserve materials per recipe
kitchen-ticket-item.status-changed	MaterialWorkerService.handleKitchenTicketItemStatusChanged	Material consume on READY; restore on VOIDED
material.transferred	InventoryWorkerService.handleMaterialTransferred	− from source, + at destination
CDC nx.seller.public.product_variant	InventoryWorkerService.handleProductVariantCDC	ensureInventoryItem upsert

Outbound:

Topic	When
material.stock-changed	After material consume/transfer
WebSocket observation/inventory/inventory-stock	After any adjustStock write

7. Sale Deduction Flow

8. Side effects per stock change

Side effect	When
InventoryTracking row insert (referenceType + referenceId)	Always — every stock change writes audit
WebSocket broadcast on 3 rooms	After adjustStock returns non-null
Possible Kafka emit (MATERIAL_STOCK_CHANGED)	If material
InventoryStock.lastStockedAt / lastCountedAt update	Per operation type

9. Idempotency

Caller	Key
Sale payment	(SALE_ORDER, saleOrderId, stockId)
Kitchen READY	(KITCHEN_TICKET_ITEM, kitchenTicketItemId, materialStockId)
PO receive	(PURCHASE_ORDER, purchaseOrderId, stockId)
Material transfer	(transferId, fromStockId) + (transferId, toStockId)

All keys queried in InventoryTracking before write — see ADR-0004.

10. Related Pages

• Inventory Tracking — audit trail format + query patterns
• Inventory Location — location lifecycle
• Purchase Order — receive flow caller
• Material & BOM — material reservation + consumption
• ADR-0001 Polymorphic InventoryItem
• ADR-0003 Atomic adjustStock