Concurrency & Transaction Model

Point of Sale environments present intense concurrency challenges:

• Multiple cashiers billing identical high-demand products at peak hours.
• Concurrent cash drop and handover actions on register drawers.
• Sequence number generation for tax invoices under burst load.

MEGSTAT POS implements a strict concurrency architecture designed for PostgreSQL's READ COMMITTED isolation level.

The Lost-Update Problem

Under naive read-modify-write patterns:

1. Transaction A reads product stock: stock = 10.
2. Transaction B reads product stock: stock = 10.
3. Transaction A decrements 2 and writes: stock = 8.
4. Transaction B decrements 5 and writes: stock = 5.
5. Result: 7 items were sold, but stock shows 5 instead of 3. Two units are lost from inventory.

Guarded Conditional Updates

MEGSTAT POS bans read-then-write logic for all mutable numerical counters (stock levels, gift card balances, customer credit, cash drawer totals).

Instead, all mutations follow the Guarded Update Pattern:

// backend/src/services/inventory.ts
const result = await prisma.product.updateMany({
  where: {
    id: productId,
    tenantId,
    stock: { gte: -qty } // Guard: Ensure stock won't drop below threshold
  },
  data: {
    stock: {
      increment: qty // Atomic database-level increment / decrement
    }
  }
});

if (result.count === 0) {
  throw new InsufficientStockError(`Product ${productId} has insufficient stock`);
}

Why This Is Concurrency-Safe in Postgres

In PostgreSQL, when an UPDATE touches a row that another concurrent transaction has modified and committed:

• PostgreSQL locks the target row.
• It re-evaluates the WHERE condition (stock >= -qty) against the latest committed row version.
• If the stock is no longer sufficient, result.count evaluates to 0.
• The service catches count === 0 and rejects the sale immediately.

Inventory Single Choke Point: applyMovement

Stock mutations are strictly prohibited from occurring directly inside route handlers.

Every inventory change in the system—sales deductions, returns, purchase receipts, stock audit adjustments, branch transfers—must funnel through applyMovement in backend/src/services/inventory.ts.

applyMovement executes atomically:

1. Applies the guarded updateMany with increment to Product.stock.
2. Appends an immutable StockMovement row recording:
   • productId, tenantId, branchId
   • quantity (positive for inward, negative for outward)
   • movementType (SALE, PURCHASE, ADJUSTMENT, TRANSFER, RETURN)
   • referenceId (links to Sale ID, Purchase ID, etc.)
   • costPrice and sellingPrice at movement timestamp

Atomic Sequence Numbering: numbering.next()

Indian GST requires tax invoices to maintain a strictly continuous, gap-free alphanumeric sequence within a financial year.

In SQLite, serialised transactions allowed catch-and-retry patterns. In PostgreSQL, a unique constraint collision immediately aborts the active transaction block.

MEGSTAT POS implements sequence numbering as an atomic upsert:

// backend/src/lib/numbering.ts
const sequence = await prisma.documentSequence.upsert({
  where: {
    tenantId_type_financialYear: {
      tenantId,
      type: documentType,
      financialYear
    }
  },
  update: {
    lastNumber: { increment: 1 }
  },
  create: {
    tenantId,
    type: documentType,
    financialYear,
    lastNumber: 1
  }
});

const invoiceNumber = `${prefix}/${financialYear}/${String(sequence.lastNumber).padStart(6, '0')}`;

The database row-lock on documentSequence serializes sequence generation across concurrent cashiers without aborting parent transactions.

Atomic Transaction Boundaries

Complex multi-entity workflows (such as finalizing a sale) run within an isolated prisma.$transaction:

createSale Transaction Boundary:
 ┌────────────────────────────────────────────────────────┐
 │ 1. Compute invoice sequence via atomic upsert          │
 │ 2. Create Sale header and SaleItem records             │
 │ 3. Decrement Product stock via applyMovement           │
 │ 4. Record Payment entries (Cash / Card / UPI / Credit) │
 │ 5. Append LedgerEntry (debit customer/cash, credit rev)│
 │ 6. Update Shift / Cash Drawer totals                   │
 └────────────────────────────────────────────────────────┘

If any step fails (e.g. insufficient stock or card verification error), the entire transaction rolls back cleanly, leaving database state intact.