Retail systems track stock two ways at once: an aggregate on-hand count for fungible goods, and individually-serialised units for regulated or high-value items. This spec defines both, the state machine a serialised unit moves through, and the invariant that ties the two representations together so a deadlock rollback or a missed decrement cannot leave them disagreeing unnoticed.
Entity model
Entity-relationship model
A product has one stock level per branch (the aggregate) and zero-or-more serialised units (the individually-tracked instances). A sale is composed of line items, each referencing either a product-and-quantity (fungible) or a specific serialised unit (tracked).
Definition
Serialised-unit state machine
A serialised unit moves through a fixed set of states via named transitions. Illegal transitions are rejected; a unit cannot go from `sold` back to `in_stock` except through the explicit `return`/`void` transition, which is the only path that also re-increments the aggregate.
| From | Event | To | Aggregate effect |
|---|---|---|---|
| in_stock | reserve | reserved | none |
| reserved | sell | sold | on_hand − 1 |
| in_stock | sell | sold | on_hand − 1 |
| sold | void / return | in_stock | on_hand + 1 |
| reserved | release | in_stock | none |
Definition
Consistency invariant and lock order
The two representations are tied by one invariant: for any product and branch, the count of serialised units in stock equals the aggregate on-hand. Any transaction that mutates both must acquire their row locks in a single fixed order (stock level before serialised unit) so concurrent sales and voids cannot deadlock into a partial write.
The invariant is asserted continuously by a reconciliation job so a drift is detected the same day, not at stocktake.
-- Must return zero rows. Any row is a ledger-vs-shelf drift.
SELECT s.product_id, s.branch_id, s.on_hand, COUNT(u.id) AS serialised_in_stock
FROM stock_levels s
LEFT JOIN serial_units u
ON u.product_id = s.product_id
AND u.branch_id = s.branch_id
AND u.status = 'in_stock'
AND u.tenant_id = s.tenant_id
GROUP BY s.product_id, s.branch_id, s.on_hand
HAVING s.on_hand <> COUNT(u.id);Invariants this spec guarantees
- For every product and branch, serialised-units-in-stock equals aggregate on-hand.
- A serialised unit only changes state via a permitted transition; illegal transitions are rejected.
- Transactions touching both representations lock them in one fixed order, so they cannot deadlock into a partial write.
- Drift between the two representations is surfaced by a same-day reconciliation check, not discovered at stocktake.