Serialised inventory and stock model

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; any transition not listed is rejected. `on_hand` is the count of units available for sale — those in `in_stock`. Reserved, in-flight (sold / shipped / delivered), returned, refunded, damaged, and expired units are still accounted for but are excluded from `on_hand`, so every transition that enters or leaves `in_stock` moves `on_hand` by ±1, and transitions between other states leave it unchanged.

A unit cannot skip states — it reaches `delivered` only through `sold → shipped → delivered`, and it returns to sellable stock only through the explicit `restock` transition, the one path back into `in_stock` (and therefore the one that re-increments `on_hand`). `damaged`, `expired`, and `refunded` are terminal for the sellable lifecycle unless a `restock` returns a repaired, resellable unit.

Any transition not in this table MUST be rejected; on_hand changes only when a unit enters or leaves in_stock, so the reconciliation query (which counts in_stock) stays consistent with the aggregate.

Contract

Transition audit trail

Every state transition MUST be logged — including rejected ones. Each record captures the unit, the from- and to-state (the target is the attempted state for a rejection), the outcome, the actor, a server timestamp, and a reason, so a unit's full chain of custody is reconstructable and an illegal or suspicious attempt is visible rather than silently dropped.

The log is append-only and follows the audit-log spec (revoke UPDATE/DELETE, hash-chained); a serialised unit is exactly the kind of regulated or high-value item whose history an auditor will ask to see. A rejected transition matters as much as an accepted one — a run of rejected `sell` attempts against a `damaged` unit is a signal, not noise.

Every transition — accepted or rejected — MUST be recorded with unit, from/to, actor, timestamp, and reason, in the append-only audit log.

Contract

Partial fulfilment

An order can mix fungible line items (a quantity of a product) and serialised line items (specific tracked units). The order's fulfilment policy MUST be explicit — all-or-nothing or partial-with-backorder — and is a property of the order, not an accident of which items happened to be in stock at allocation time.

Under all-or-nothing, the order commits only if every line — the fungible quantities and each serialised unit — can be allocated in one transaction; if any line cannot, nothing is reserved and the order is rejected or held. Under partial allocation, the lines that can be allocated are reserved and fulfilled and the remainder becomes a backorder, allocated when stock arrives — each partial allocation and each backordered line is its own recorded event.

Whichever policy applies, allocation is atomic per fulfilment: the fungible decrements and the serialised reservations in a single fulfilment commit together or not at all. A partial *fulfilment* never leaves a partial *write* — the order may be split across time, but no individual allocation is left half-applied.

The fulfilment policy (all-or-nothing vs partial-with-backorder) MUST be an explicit property of the order; every allocation is atomic, so a split order never produces a half-applied write.

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);

Contract

Concurrency control

Transactions that mutate stock run at READ COMMITTED with explicit `SELECT … FOR UPDATE` row locks (or SERIALIZABLE where the workload prefers it), never on unlocked reads — the read-modify-write of a count or a status is only safe under a lock. The lock order is fixed (stock level before serialised unit, per above), so consistently-ordered writers form no deadlock cycle.

Locks are acquired with a bounded `lock_timeout`; if a lock cannot be taken within it, the transaction aborts and fails closed — a contended sale is refused and retried, never allowed to proceed on a stale read. Deadlocks that still occur (an out-of-order path, a cross-table cycle) are detected by the database, which aborts one transaction; the application retries the aborted transaction with bounded attempts and exponential backoff, because a deadlock is a transient, expected condition, not an error to surface.

Retries are safe only because each stock transaction is idempotent on its business key (a sale line, a return RMA): re-running a retried transaction reaches the same end state rather than double-decrementing. A transaction that exhausts its retry budget is surfaced, never silently dropped.

SET LOCAL lock_timeout = '2s';                    -- fail closed if contended
BEGIN;
  SELECT on_hand FROM stock_levels
   WHERE product_id = :p AND branch_id = :b FOR UPDATE;    -- lock A (first)
  SELECT status FROM serial_units WHERE id = :u FOR UPDATE; -- lock B (second)
  -- decide, update both, write the transition record
COMMIT;
-- On deadlock / lock_timeout: abort, then retry (bounded, backoff) — idempotent.

Stock mutations MUST take explicit row locks in the fixed order under a bounded lock_timeout (fail closed); deadlocks are retried with backoff, safe because each transaction is idempotent on its business key.

Contract

Reconciliation job

The consistency invariant is checked by a scheduled reconciliation job — daily by default (more often for high-velocity or regulated stock) — running the drift query across every product and branch. Its purpose is detection, not correction: on finding a drift it MUST log the discrepancy and alert, and MUST NOT silently auto-correct the counts. An automatic fix would paper over the cause — a missed decrement, a bug, or theft — and destroy the evidence of what happened.

A drift is escalated, not merely logged: the alert pages the on-call owner, the affected product/branch and the magnitude are captured, and a discrepancy above a threshold — or any drift on a regulated category — is raised as an incident with the transition audit trail attached, so the investigation starts from the unit history, not a bare count mismatch.

Reconciliation reads consistently (a snapshot or per-row locks) so it does not itself race live sales; it is read-only and never writes to the counts it audits.

Reconciliation runs at least daily; on drift it logs and alerts but MUST NOT auto-correct — a human investigates with the transition audit trail, because auto-correction hides the cause (bug, miss, or theft).