# Inventory Truth: A Stock Movement Ledger, Not a Column

> Why I made SUM of an append-only StockMovement ledger the source of truth in my carwash ERP, and treated Product.stockQty as a cache I rebuild.

URL: https://www.matthewswong.com/en/blog/stock-movement-source-of-truth-vs-cached-qty/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2026-07-14
- Updated: 2026-07-14
- Category: Backend

> **Key takeaway:** In my carwash ERP, the true stock of any product is the SUM of every StockMovement.qtyDelta row, never the number in Product.stockQty. The ledger is append-only and authoritative; the column is a denormalized cache for fast reads. When they disagree, the ledger wins and the cache is rebuilt, so no update can silently corrupt inventory.

One morning a branch manager called me because the app said we had negative three bottles of tire shine. Not zero, negative three. That is the kind of number that only appears when several people update the same quantity column at nearly the same moment and one write clobbers another. I had built the inventory module the obvious way first: Product.stockQty was a plain integer, and every sale or restock did an UPDATE that added or subtracted from it. It worked in the demo and rotted in production.

The fix was to stop treating the quantity as a value I own and start treating it as a value I compute. Instead of one mutable number, I recorded an immutable row for every event that changes stock: a sale of two units is qtyDelta of minus two, a purchase receipt of a carton is qtyDelta of plus twenty-four. The current quantity is then just an aggregate. This is the same append-only, event-sourced idea that large inventory systems reach for, and it is worth understanding why before you copy it.

## Why a mutable quantity column rots

A single mutable integer looks efficient, but it destroys information on every write. An UPDATE that sets stockQty from ten to eight tells you the new value and erases the reason, the actor, and the previous state. In a multi-branch, multi-terminal setup that is a losing trade. Here is what a mutating column cost me in practice.

- Lost audit trail: once the number changes, there is no built-in way to ask how it got there. Debugging a wrong count meant guessing, because the evidence was already overwritten.
- Lost-update race conditions: two concurrent read-modify-write cycles on the same row produce the negative-three phantom, because the second write is based on a stale read.
- Unfixable corruption: if a bug ever writes a wrong value, the wrong value is now the truth. There is nothing to replay and nothing to reconcile against.
- No natural place for context: batch numbers, source documents, and the branch that moved the stock have nowhere to live on a scalar column.

## The append-only StockMovement ledger

Every physical thing that moves stock writes exactly one immutable row. There are no UPDATE and no DELETE statements against this table in application code. Each row carries a signed qtyDelta, the branch that owns it, a movement type, and a reference back to the document that caused it. Note that qtyDelta is an integer of whole units, the same integer discipline I use for money, where all IDR amounts are integers and never floats.

The authoritative quantity for a product is a single aggregate over that table, scoped by branchId exactly like every other list and detail query in the API. Scoping by branchId is also the IDOR fix I apply everywhere, so a terminal in one branch can never read or move another branch's stock. In Prisma the read is a grouped sum.

> An append-only ledger composes cleanly with the rest of the ERP. My AuditLog is append-only for the same reason, payment settlement is protected by idempotency keys so a retried request never double-writes, and the cash book is a derived union view rather than a stored table. Stock is just one more thing you compute instead of store.

## Ledger versus mutable column, side by side

The trade-off is not free. The ledger costs storage and needs a cache to stay fast. Here is the honest comparison I used to justify the rewrite to myself.

| Concern | Append-only ledger | Mutable quantity column |
| --- | --- | --- |
| Source of truth | SUM of all qtyDelta rows | The current column value |
| Audit trail | Complete and built in, every change is a row | None, each write erases history |
| Concurrency | Inserts never conflict, no lost updates | Read-modify-write races corrupt the count |
| Recover from a bug | Post a correcting row, replay to rebuild | Manual guesswork, truth already gone |
| Read latency | Slower unless cached, needs an index | Instant single-row read |
| Storage cost | Grows with every movement | Constant, one row per product |

## Rebuilding and reconciling the cache

Reading a grouped SUM on every product listing would hammer the database, so Product.stockQty stays, but only as a cache. It is written in the same transaction as the movement insert, and it is disposable. If it is ever wrong, the ledger rebuilds it. The rebuild is a pure projection: read the ledger, write the caches, done.

1. Aggregate the ledger per product for the branch, grouped by productId with a sum of qtyDelta.
2. Compare each computed total against the cached Product.stockQty and collect the differences.
3. In one transaction, overwrite every stale cache value with the computed truth from the ledger.
4. Write one AuditLog row per corrected product so the reconciliation itself is traceable.

> A physical stock count that disagrees with the ledger is never fixed by editing stockQty. That would lie to the source of truth. Instead I post a movement of type ADJUSTMENT whose qtyDelta is the counted difference, referencing the stock-opname document. The count becomes another event in the history, not a silent overwrite of it.

## Drift detection in production

Because the cache can theoretically drift from the ledger, I do not wait for a phone call to find out. A scheduled job runs the same aggregate as the reconciler, but in read-only mode: it compares computed truth against cached stockQty and emits a metric for any product where they differ. In the six months since the rewrite, real drift has been effectively zero, which is the point. The cache is now provably a projection of the ledger, and the negative-three phantom cannot come back, because there is no shared quantity for two writers to clobber. When drift is non-zero, the alert tells me exactly which product and branch to inspect, and the reconciler makes the fix a one-command operation rather than a late-night guessing game.

## Sources

- Event Sourcing pattern - Microsoft Azure Architecture Center
- Design Inventory Availability System using Event Sourcing - Walmart Global Tech
- Aggregation, grouping, and summarizing - Prisma Documentation

## Source links

- https://learn.microsoft.com/en-us/azure/architecture/patterns/event-sourcing
- https://medium.com/walmartglobaltech/design-inventory-availability-system-using-event-sourcing-1d0f022e399f
- https://www.prisma.io/docs/orm/prisma-client/queries/aggregation-grouping-summarizing

## FAQ

### Why is SUM of StockMovement the source of truth instead of Product.stockQty?

Because the ledger records every change as an immutable row, the sum of all qtyDelta values is a complete, reconstructable history rather than a single overwritable number. Product.stockQty is only a cache written for fast reads. If the two ever disagree, the ledger is authoritative and the cache is rebuilt from it.

### Does aggregating a movement ledger make inventory reads too slow?

Not in practice. A composite index on branchId and productId keeps the grouped SUM fast, and the denormalized Product.stockQty cache serves the common product-listing reads instantly. You only aggregate the ledger directly when you need guaranteed truth, such as during reconciliation or drift checks.

### How do you fix a stock count that is wrong after a physical count?

You never edit Product.stockQty or a movement row directly. You post a new StockMovement of type ADJUSTMENT whose qtyDelta equals the counted difference, referencing the stock-opname document. This keeps the ledger append-only and makes the correction a traceable event in the history.

### How is drift between the cache and the ledger detected?

A scheduled read-only job runs the same grouped SUM the reconciler uses and compares each computed total against the cached stockQty. Any product where they differ emits a metric and an alert naming the product and branch. The reconciler can then overwrite the cache from the ledger in a single command.

### Isn't an append-only ledger just wasted storage compared to one column?

It grows with every movement, which is a real cost, but it buys a complete audit trail, immunity to lost-update races, and the ability to rebuild state after any bug. For a multi-branch ERP where a wrong count means real money, that trade is easily worth the extra rows.

---

- HTML version: https://www.matthewswong.com/en/blog/stock-movement-source-of-truth-vs-cached-qty/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/stock-movement-source-of-truth-vs-cached-qty/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json