Stock is a ledger, not a number

Every inventory system starts with products.quantity. Sell one, decrement. Receive ten, increment. It is the obvious model and it is the same mistake as a mutable account balance.

The question you cannot answer

The count says 47. The shelf says 44. Where did three go?

With a quantity column there is no answer. You cannot tell whether it was a miscount at receiving, a sale that decremented twice, a return that never incremented, or theft. You can only correct the number to 44 and lose the evidence that anything happened.

Do that a few times and nobody trusts the system, so people keep their own spreadsheets, and now you have two wrong numbers.

Movements, append-only

stock_movements
  sku
  location_id
  delta            -- signed: +10 receipt, -1 sale
  reason           -- receipt | sale | return | adjustment
                   -- | transfer_in | transfer_out | damage
  reference        -- order id, transfer id, count sheet id
  batch_id         -- null unless tracked
  occurred_at
  actor

Quantity on hand is SUM(delta) filtered by sku and location. Never stored, never updated, only derived.

Every row is immutable. A mistake is corrected by writing the opposite movement with reason = 'adjustment' and a reference to the count that found it — so the correction is as visible as the error.

The performance objection, answered

Summing millions of rows per lookup is genuinely slow, and this is where teams retreat to a quantity column. The right answer is a periodic snapshot:

stock_snapshots
  sku, location_id, as_of, quantity

Current stock is the latest snapshot plus movements since. Snapshot nightly and the sum covers a day, not a decade. Crucially the snapshot is a cache: delete every row and it rebuilds from movements. A quantity column cannot be rebuilt from anything, which is the whole difference.

What it costs and what it buys

One extra table and a nightly job. In return: “why is this wrong” becomes a query, cycle counts write adjustments instead of overwriting truth, and stock at any past date is answerable — which matters the first time a supplier disputes a delivery from six weeks ago.