DEV Community

Cover image for How I Made Stock Reservation Concurrency-Safe in a Multi-Tenant ERP
Bambang Saputra Jaya , S.I.Kom
Bambang Saputra Jaya , S.I.Kom

Posted on

How I Made Stock Reservation Concurrency-Safe in a Multi-Tenant ERP

#ai #postgres #nestjs #automation

How I Made Stock Reservation Concurrency-Safe in a Multi-Tenant ERP

When you build an ERP from scratch, the one thing that will keep you up at night is concurrency. Two sales agents hit "confirm" at the same time on the same SKU. Will your database oversell?

I just shipped a complete ERP platform as a portfolio piece — github.com/ibam28/erp-automation-platform. Here's how I made the inventory race-safe, and why the same pattern scales to multi-tenant AI integration.

The Naive Version (and Why It Fails) 

// ❌ Race condition — oversells under concurrent load
const stock = await prisma.stock.findUnique({ where: { id } });
if (stock.onHand - stock.reserved < qty) {
  throw new InsufficientStockError();
}
await prisma.stock.update({
  where: { id },
  data: { reserved: { increment: qty } },
});
Enter fullscreen mode Exit fullscreen mode

Two concurrent requests both read stock.onHand=10, reserved=0. Both check 10 ≥ 8. Both update to reserved=16. We've oversold 6 units.

The fix is well-known: pessimistic locking with in-transaction re-check.

The Correct Version 

await prisma.$transaction(async (tx) => {
  // Acquire row-level exclusive lock — concurrent tx blocks here
  const stocks = await tx.$queryRaw<{ id: string; on_hand: Decimal; reserved: Decimal }[]>`
    SELECT id, on_hand, reserved FROM stocks
    WHERE organization_id = ${orgId}::uuid
      AND warehouse_id    = ${whId}::uuid
      AND product_id      = ${prodId}::uuid
    FOR UPDATE
  `;

  if (Number(stocks[0].on_hand) - Number(stocks[0].reserved) < qty) {
    throw new InsufficientStockError();
  }

  await tx.stock.update({
    where: { id: stocks[0].id },
    data: { reserved: { increment: qty } },
  });

  await tx.stockReservation.create({
    data: {
      organizationId, salesOrderId, productId, warehouseId,
      quantity: qty, status: 'ACTIVE',
      expiresAt: new Date(Date.now() + 72 * 3600 * 1000),
    },
  });

  await tx.salesOrder.update({
    where: { id: salesOrderId },
    data: { status: 'RESERVED', reservationExpiresAt },
  });
});
Enter fullscreen mode Exit fullscreen mode

Why this works: PostgreSQL's FOR UPDATE acquires a row-level exclusive lock. The second transaction blocks until the first commits, then re-reads the new reserved value (now 8), finds 10 - 8 = 2 < 8, and fails with InsufficientStockError.

The test (apps/api/src/modules/__tests__/hero-workflow.spec.ts):

  • Creates 2 sales orders in DRAFT
  • Reserves each for qty=8 against stock.onHand=10, reserved=0
  • Expects: ONE success, ONE InsufficientStockError
  • Verifies: stock.reserved=8 (not 16)

Run jest and it passes deterministically. Every. Single. Time.

The Cost: Lock Contention

Pessimistic locking serializes all reservations on the same product/warehouse. For a hot SKU during a flash sale, this becomes a bottleneck.

Mitigations I considered:

  1. Short transactions: Keep the locked region under a few ms. We avoid business logic inside the lock.
  2. Skip-locked alternative: FOR UPDATE SKIP LOCKED lets one writer skip rows another is touching — good for the worker, bad for reservations (we'd rather fail than skip).
  3. Pre-check: Query available stock outside the lock; if obviously insufficient, fail fast. Doesn't change the race, just reduces wasted lock time.

I documented all this in ADR-011. The ADR pattern is one of 21 in the project.

The Outbox Pattern: Reliable Events

Stock reservation is one concern. Telling other systems (n8n for notifications, AI for reminders) is another. If you write to the database, then POST to n8n, and the POST fails — you've got an inconsistency.

The fix: transactional outbox.

await prisma.$transaction(async (tx) => {
  // Business mutation
  await tx.invoice.update({ ... });

  // Outbox event in the SAME transaction
  await tx.outboxEvent.create({
    data: {
      eventId: uuidv4(),
      eventType: 'invoice.paid',
      aggregateType: 'invoice',
      aggregateId: invoiceId,
      // ... full envelope ...
      payload: { amount: 1000 },
      status: 'PENDING',
    },
  });
});
Enter fullscreen mode Exit fullscreen mode

If the business write fails → event not written. If event write fails → business rolls back. Atomic.

A separate worker process claims pending events with:

SELECT id, event_id FROM outbox_events
WHERE status = 'PENDING'
  AND (next_attempt_at IS NULL OR next_attempt_at <= now())
ORDER BY created_at ASC
LIMIT 50
FOR UPDATE SKIP LOCKED
Enter fullscreen mode Exit fullscreen mode

SKIP LOCKED makes this safe to run from multiple worker replicas. Each event is processed at-least-once. The worker's event_id is the idempotency key for downstream consumers.

Retry with Backoff + Jitter

Pure exponential backoff has a problem: all workers retry at the same time after an outage, causing a thundering herd. Adding up to 25% positive jitter spreads the load:

function computeBackoffMs(attempt: number): number {
  const base = 1000 * Math.pow(2, attempt - 1);
  const jitter = Math.random() * 0.25 * base;
  return Math.floor(base + jitter);
}
Enter fullscreen mode Exit fullscreen mode

The dev server's /health/ready endpoint reflects current state, so orchestrators can decide to restart the worker.

Replay

When an event goes DEAD (retries exhausted), an admin can manually replay it. The event_id lineage is preserved — no duplicate events, no corrupted state. This is the admin replay endpoint:

POST /api/admin/outbox-events/:event_id/replay
Enter fullscreen mode Exit fullscreen mode

Authorized + tenant-scoped + audit-logged. No direct DB editing in operations.

Multi-Tenant: Tenant Scope is Sacred

Every business table has organization_id. Every query filters by it. The JWT carries the org claim, and TenantGuard enforces it at the API layer. URL queries like ?org=other-tenant-id are ignored — the JWT wins.

// apps/api/src/common/errors.ts
export class TenantIsolationError extends DomainError {
  constructor(message = 'Cross-tenant access denied') {
    super('TENANT_ISOLATION', message, HttpStatus.FORBIDDEN);
  }
}
Enter fullscreen mode Exit fullscreen mode

The test in tenant-isolation.e2e-spec.ts proves: even with valid credentials from Tenant A, login to Tenant B returns 401. Cross-tenant logins are rejected.

AI as a First-Class Pillar

I treated AI as an engineering problem, not a "call OpenAI and pray" problem. The AI module has:

  • Provider abstraction — MockAiProvider for local dev, OpenAI/Anthropic in prod
  • Tool registry — every tool declares required permission, critical-mutation flag, zod input schema
  • Authorization context — tenant + user + permissions + correlation ID flows into every tool call
  • Critical-mutation guardrail — every critical tool requires explicit human approval (no MVP tool uses it, but the gate is in place)
  • Deterministic fallback — when the provider fails/times out, a hardcoded template generates the reminder

This means: the AI can't accidentally oversell, can't bypass tenant isolation, can't destroy data autonomously.

// Critical mutation guard (ADR-018)
if (tool.criticalMutation && !opts.approvedBy) {
  return { 
    ok: false, 
    code: 'MUTATION_REQUIRES_APPROVAL', 
    approvalId: `pending-${tool.name}-${Date.now()}`
  };
}
Enter fullscreen mode Exit fullscreen mode

The Numbers

What Result
Phases shipped 6 of 6
ADRs documented 21
Test suites passing 13 of 13
Tests passing 113 of 113
Concurrency hero (race test) ✅ passes deterministically
Hero workflow (Customer → Invoice → Pay) ✅ passes end-to-end

What's Next (post-MVP)

The architecture is ready for:

  • OpenAI / Anthropic providers behind MockAiProvider
  • Multi-instance worker (already safe at the DB layer)
  • Per-request AI audit dashboard
  • Customer/supplier portals

All explicitly out of MVP scope per V5 contract. See docs/phase0-checklist.md for what's left.

Try It 

git clone https://github.com/ibam28/erp-automation-platform
cd erp-automation-platform

cp .env.example .env
# Edit JWT_SECRET (>=32 chars) and N8N_WEBHOOK_SECRET (>=16 chars)

docker compose up -d postgres redis
npm install
npx prisma generate --schema=packages/database/prisma/schema.prisma
npx prisma migrate deploy --schema=packages/database/prisma/schema.prisma
npx tsx packages/database/prisma/seed.ts

docker compose up -d api worker n8n
Enter fullscreen mode Exit fullscreen mode

Then http://localhost:3000/api/docs for the Swagger UI.

Deployment

The repo ships with two deployment paths:

  1. Docker Compose (local / on-prem): docker compose up -d — Postgres, Redis, API, worker, n8n. Five minutes from zero to running locally.
  2. Fly.io / Railway (cloud): infra/fly/ holds production-grade fly.toml per app. Railway is supported via Dockerfile + nixpacks.toml. Both platforms auto-detect the Dockerfile.

The deployment story is intentionally minimal — the focus is the application architecture, not cloud-specific glue. A 12-factor config (DATABASE_URL, JWT_SECRET, etc.) is enough.

Closing Thought

Concurrency is solvable — with the right primitives. Outbox pattern is well-known — once you see the failure mode. Multi-tenancy is non-negotiable — build it in from day one, not later.

What patterns have you used? I'd love to hear from folks running real production outboxes — the failure modes and recovery strategies that aren't in the textbooks.

🔗 https://github.com/ibam28/erp-automation-platform

ERP #PostgreSQL #Concurrency #OutboxPattern #NestJS #BackendEngineering #MultiTenant #AI #SoftwareArchitecture

Top comments (0)