Designing an inventory ledger that can't oversell: append-only movements and FIFO cost layers in MongoDB

The worst inventory bugs are rarely in the UI: two cashiers sell the last unit at once, a dispatch goes out at the wrong cost, or stock quietly drifts away from the general ledger. When we built Inventra, our multi-tenant inventory and ERP platform on Next.js 15 and MongoDB, we set one rule early: stock is a ledger, not a number. This article explains how that rule is enforced: conditional updates that stop overselling, cost layers that implement FIFO and FEFO, and the test that checks both under concurrency.

Three collections, one transaction

Every stock change goes through one engine, lib/services/inventory.ts. Its header comment states that it is "the only code allowed to change StockLevel, CostLayer and SerialUnit." That engine writes three collections inside a single MongoDB transaction:

• StockMovement is the append-only history. Each record holds the change in on-hand quantity, the change in allocated quantity, the value moved (in integer minor units), the reference document, and the bin, batch and serials. There are 11 movement types: RECEIPT, RESERVE, RELEASE, DISPATCH, TRANSFER_OUT, TRANSFER_IN, ADJUSTMENT, COUNT, RETURN, POS_SALE and PUTAWAY. The engine only ever inserts movements. A correction is a new movement, never an edit.
• StockLevel is the cache. It has one row per product, warehouse and bin, holding on-hand, allocated and available quantities.
• CostLayer is both the lot record and the FIFO cost layer. It stores what was received, when, at what unit cost, with which expiry date, and how much remains.

We store quantityAvailable instead of computing it. The schema comment explains why:

/**
 * Invariant (maintained only by lib/services/inventory.ts): quantityAvailable = quantityOnHand − quantityAllocated.
 * quantityAvailable is stored so the atomic reserve filter `{ quantityAvailable: { $gte: q } }` can use an index.
 */

Every operation runs inside runStockTx. It wraps the driver's session.withTransaction and passes the session to every write, so the movement, the level, the layer and any journal entry commit or roll back together. That is why Inventra requires a MongoDB replica set: a standalone mongod has no multi-document transactions.

The guard lives in the filter

The guarantee against overselling does not come from a lock or from reading a value and then writing it. It comes from putting the precondition inside the update filter (condensed from takeFromRows):

const rows = await StockLevel.find(filter, null, { session: tx.session })
  .sort({ quantityAvailable: -1 }).select('binId quantityAvailable').lean();
const total = rows.reduce((s, r) => s + r.quantityAvailable, 0);
if (total < qty) throw new InsufficientStockError(p.sku, qty, total);
let remaining = qty;
for (const row of rows) {
  if (remaining <= 0) break;
  const take = Math.min(row.quantityAvailable, remaining);
  const inc = mode === 'reserve'
    ? { quantityAvailable: -take, quantityAllocated: take }
    : { quantityAvailable: -take, quantityOnHand: -take };
  const res = await StockLevel.updateOne(
    { tenantId: tx.tenantId, _id: row._id, quantityAvailable: { $gte: take } },
    { $inc: inc }, { session: tx.session });
  if (res.modifiedCount !== 1) throw new InsufficientStockError(p.sku, qty, qty - remaining);
  // ...record the allocation, decrement remaining
}

The find only plans. The real check is quantityAvailable: { $gte: take } inside updateOne. If another transaction took the stock first, the filter matches nothing and the transaction aborts with a typed InsufficientStockError. When two transactions write the same document at once, MongoDB reports a write conflict and the driver retries the transaction against the committed value. The same pattern protects shipping (quantityAllocated: { $gte }), releases, and cost layers (qtyRemaining: { $gte }).

The per-facility safety buffer depends on a sum across bins, not one row. For it, assertBuffer increments a lockVersion on the FacilityProduct document inside the transaction, so two concurrent buffered reservations conflict and one retries instead of both slipping under the buffer.

Consuming cost layers: bin, then FEFO, then FIFO

Every receipt creates a CostLayer. A dispatch consumes layers and returns the cost of goods sold (COGS) along with the lots it used. The ordering is a single sort (condensed from consumeLayers):

// Lots in the bins we shipped from go first (lot traceability per bin), then FEFO/FIFO:
// expiry date ascending with undated lots last, then oldest receipt first.
const pref = new Set(preferBins.map(String));
const exp = (d: Date | null) => (d ? d.getTime() : Number.MAX_SAFE_INTEGER);
layers.sort((a, b) =>
  Number(!pref.has(String(a.binId))) - Number(!pref.has(String(b.binId))) ||
  exp(a.expiryDate) - exp(b.expiryDate) ||
  a.receivedAt.getTime() - b.receivedAt.getTime() ||
  String(a._id).localeCompare(String(b._id)));

The sort applies four keys in order:

1. Lots in the bin the goods physically left from come first, so lot traceability matches the shelf.
2. Earliest expiry comes next (FEFO).
3. Then the oldest receipt (FIFO).
4. The document id breaks any remaining ties, so the order is deterministic.

Each layer is then decremented with its own $gte guard. Serial-tracked products skip this path and use specific identification: each unit leaves at the cost of its own receipt layer. The resulting valueChange is written on the movement, so inventory value and COGS trace back to individual documents.

Proving it: 50 parallel reservations

A claim like "can't oversell" needs a test that tries to break it. Inventra's integration suite runs against a real replica-set database, and the first inventory test is this one:

it('never oversells under concurrency: 50 parallel reserves against 10 units', async () => {
  const t = await makeTenant();
  const p = await makeProduct(t.tenantId);
  await runStockTx(t.ctx(), (tx) => receive(tx, { productId: p._id, warehouseId: t.warehouseId, qty: 10, unitCost: 500, ref: R() }));
  const results = await Promise.allSettled(Array.from({ length: 50 }, () =>
    runStockTx(t.ctx(), (tx) => reserve(tx, { productId: p._id, warehouseId: t.warehouseId, qty: 1, ref: R() }))));
  expect(results.filter((r) => r.status === 'fulfilled').length).toBe(10);
  for (const r of results) if (r.status === 'rejected') expect(r.reason).toBeInstanceOf(InsufficientStockError);
  const s = (await stockByProduct(t.tenantId, [p._id])).get(String(p._id))!;
  expect(s).toEqual({ onHand: 10, allocated: 10, available: 0 });
  await invariant(t.tenantId);
});

Exactly 10 reservations succeed, the other 40 fail with the typed error, and an invariant helper checks available === onHand − allocated on every row. Sibling tests check that dispatching 7 units from layers of 5 at 100 and 5 at 200 costs 5 × 100 + 2 × 200, and that the batch expiring sooner is consumed first even if it arrived later. The full suite has 112 test cases across 19 files.

Money follows the goods: balanced journals and the period lock

Movements that change value also post a double-entry journal through postJournal, inside the same transaction. The function rejects two kinds of entry (condensed):

const debit = lines.reduce((s, l) => s + l.debit, 0);
const credit = lines.reduce((s, l) => s + l.credit, 0);
if (debit !== credit) throw new UnbalancedEntryError(debit, credit);
const tenant = await Tenant.findById(tenantId, null, { session })
  .select('baseCurrency financeLockedThrough').lean();
if (tenant.financeLockedThrough && date <= tenant.financeLockedThrough) {
  throw new UserError(`The books are closed through ${tenant.financeLockedThrough.toISOString().slice(0, 10)}. ...`);
}

Amounts are integer minor units, so the balance check is an exact comparison. Because the journal shares the stock transaction, a rejected journal also rolls back the stock change that caused it. Journals are corrected the same way as movements: reverseJournal posts a mirror-image entry and links it to the original.

Trade-offs

• Replica set required. Local setup needs mongod --replSet rs0 and rs.initiate(). The transaction helper falls back to running without a session on a standalone server, but that gives up the guarantees above. We treat the fallback as a development convenience, not a supported deployment.
• Hot SKUs serialise. Heavy contention on one product row means retries, not parallelism. Fine for trading businesses; a flash-sale workload would need another design.
• The stored derived field needs discipline. quantityAvailable stays correct only because one module writes it. The tests assert the invariant.
• One deliberate exception. A sale made on an offline POS till and synced later may take stock negative rather than lose a completed sale. It is costed at the product's standard cost and flagged. We cover it in a separate article.

Conclusion

None of this is exotic: an append-only history, a cache guarded by conditional $inc filters, lots that double as cost layers, and one transaction around everything. It works because every write path follows these rules and the tests exercise them under concurrency. The Inventra case study covers the wider architecture. The live demo is read-only and offers one-click demo logins. The source is on our marketplace. If you need this kind of consistency in your own product, our SaaS engineering team can help.