AtomicFIFOInventoryDeductioninMongoDB

In multi-tenant ERP software such as Next Web Business, inventory balance integrity during concurrent invoicing is a critical reliability requirement. When multiple billing operators simultaneously invoice the same SKU, conventional read-then-write updates inevitably encounter race conditions, leading to overselling and negative stock quantities.

Why Optimistic Checking Fails Under Concurrency

A naive implementation fetches the product stock from the database, checks in application code whether product.availableQty >= requestedQty, and then saves the decremented quantity. If two concurrent HTTP requests arrive within 10 milliseconds of each other, both requests read the same balance, both pass the conditional check, and both decrement the balance—driving inventory into a negative state.

Conditional Atomic Balance Deduction with $expr Guards

To eliminate race conditions without locking entire tables, the decrement condition must be pushed directly into MongoDB's atomic write filter using $expr. If available quantity has changed in the intervening microseconds, the write returns modifiedCount: 0, allowing the application to reject the transaction cleanly.

async function deductStockWithFifo(session, tenantId, productId, requestedQty) {
  // Lock-free atomic balance verification and ledger deduction
  const updateResult = await ProductStock.updateOne(
    {
      tenantId,
      productId,
      $expr: { $gte: ["$availableQty", requestedQty] } // Race-condition guard
    },
    {
      $inc: { availableQty: -requestedQty, reservedQty: requestedQty },
      $set: { updatedAt: new Date() }
    },
    { session }
  );

  if (updateResult.modifiedCount === 0) {
    throw new Error("Insufficient available stock for concurrent transaction");
  }

  // Record audit trail ledger entry
  await StockLedger.create(
    [{ tenantId, productId, deltaQty: -requestedQty, type: "OUT_INVOICE" }],
    { session }
  );
}

Double-Entry Immutable Stock Ledgers

Directly mutating product balance numbers without a journal creates untraceable discrepancies over time. In addition to the atomic balance update, every deduction writes an immutable journal record to StockLedger within the same ACID transaction session. Even in the event of an unexpected application crash, MongoDB guarantees that balance modifications and audit entries commit atomically or roll back completely.