Inventory Truth: A Stock Movement Ledger, Not a Column

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.

// prisma/schema.prisma
model StockMovement {
  id          String   @id @default(cuid())
  branchId    String            // every query scopes by this
  productId   String
  qtyDelta    Int               // signed: -2 sale, +24 receipt
  type        MovementType      // SALE | PURCHASE | ADJUSTMENT | TRANSFER
  refType     String            // 'Order' | 'Purchase' | 'StockOpname'
  refId       String
  createdAt   DateTime @default(now())

  product     Product  @relation(fields: [productId], references: [id])
  branch      Branch   @relation(fields: [branchId], references: [id])

  @@index([branchId, productId])   // fast per-product SUM
  @@index([branchId, createdAt])
}

model Product {
  id        String @id @default(cuid())
  branchId  String
  name      String
  stockQty  Int    @default(0)   // CACHE ONLY - derived, never trusted
}

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.

// The source of truth: SUM(qtyDelta), never Product.stockQty
async function trueStock(branchId: string, productId: string) {
  const agg = await prisma.stockMovement.aggregate({
    where: { branchId, productId },
    _sum: { qtyDelta: true },
  });
  return agg._sum.qtyDelta ?? 0;
}

// Recording a sale is an INSERT, never an UPDATE of a quantity
await prisma.stockMovement.create({
  data: { branchId, productId, qtyDelta: -qtySold,
          type: 'SALE', refType: 'Order', refId: order.id },
});

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.

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.

// scripts/reconcile-stock.ts - safe to run any time, idempotent
const truth = await prisma.stockMovement.groupBy({
  by: ['productId'],
  where: { branchId },
  _sum: { qtyDelta: true },
});

for (const row of truth) {
  const computed = row._sum.qtyDelta ?? 0;
  await prisma.product.update({
    where: { id: row.productId },
    data: { stockQty: computed },      // cache := source of truth
  });
}
// Because it only ever overwrites the cache FROM the ledger,
// re-running it changes nothing once caches already match.

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.