I have spent more late nights than I would like reconciling a stock table that swore it was right.
The pattern is always the same: a quantity column gets updated in place, two operations race, and
the number that survives is the one that committed last, not the one that was correct. CRUD ledgers
do not just lose history. Under concurrency they lose accuracy.
The failure mode
A CRUD ledger stores the current quantity and overwrites it:
UPDATE stock SET quantity = quantity - 1 WHERE sku = 'ABC-123';Read-modify-write without the right isolation, and two concurrent picks both read 10, both write 9, and you have shipped two units while the book dropped by one. You cannot reconstruct what happened, because the intermediate states were overwritten.
The append-only model
Store events, not state. Every movement is an immutable fact:
(sku, delta, reason, source_txn, occurred_at)
ABC-123, +100, receipt, PO-5512, 2026-05-01T09:00Z
ABC-123, -1, pick, SO-8841, 2026-05-01T11:14Z
ABC-123, -1, pick, SO-8842, 2026-05-01T11:14ZOn-hand is a fold over the log: SUM(delta). Two concurrent picks are two appended rows, not a
contested update, so neither is lost. History is the table, so “what did we believe on May 1?” is a
query, not an archaeology project.
Does it scale? The numbers
The obvious worry is that summing an ever-growing log gets slow. It does, so you snapshot. On a test ledger of 5 million events across 40,000 SKUs (Postgres 16, modest 4-vCPU box), I measured:
- Naive
SUM(delta)per SKU on the full log: ~310 ms p95. - With a daily snapshot + fold of same-day events only: ~4 ms p95.
The snapshot is itself derived from the log, so it is a cache, not a second source of truth. The append-only table stays authoritative.
What you give up
More storage, and a mindset shift: you never edit a fact, you append a correcting one. A wrong receipt is not patched, it is reversed with a compensating event that itself carries a reason. For inventory, where “how did we get here” matters as much as “where are we,” that trade has paid off every time I have made it.
Implementing this at your scale?
The walkthrough above comes from production work. AvanSaber’s inventory practice has implemented variations of this pattern across multiple customer engagements.
If you are building this and want expert review of your design, or would rather have the team that built this build yours, book a discovery conversation or describe your situation at [email protected].