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. Scanning at the moment stock physically moves is what makes those four cases distinguishable at all; a ledger fed from memory at the end of a shift is a quantity column with extra steps.

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. It is also the number every sales channel subscribes to, and overselling across four marketplaces is a great deal easier to diagnose when you can replay exactly how the figure they were given came to exist.

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. It is the decision that separates an inventory system people rely on from one they keep a spreadsheet next to.