Stockroom

Stockroom is a multi-location inventory and reservation engine. It tracks stock across warehouses and storefronts and lets a checkout hold units without letting two checkouts hold the same unit.

The problem

10 units left, 50 people click buy in the same second. The obvious way to write a reservation is read-then-write: read what's available, check it against the requested quantity, then write the new reserved total. That's correct for one request and wrong under concurrent ones — every one of the 50 reads can see "10 available" before any of them has written anything back, so all 50 decide they're allowed to proceed. The gap between read and write is normally microseconds, which is why the bug survives a single test or a one-person demo and only shows up once real concurrency closes that gap — at a launch or a sale, not in CI.

The fix

reserveStock (src/lib/stock/reserve.ts) puts the availability check inside the UPDATE's WHERE clause instead of in code that runs before the write:

UPDATE "StockLevel" SET "reserved" = "reserved" + $qty
 WHERE "workspaceId" = $workspaceId AND "locationId" = $locationId
   AND "productId" = $productId AND "onHand" - "reserved" >= $qty

There is no window, because there is no separate decision — Postgres evaluates the condition and performs the write as one atomic step and serialises writers to the same row. Zero rows updated means nothing was available; the caller gets a typed error.

A CHECK constraint on StockLevel (reserved <= onHand AND reserved >= 0 AND onHand >= 0) is the second layer: the database refuses an oversell even if the code above regresses, so the invariant doesn't depend on every future caller getting the application logic right.

The proof

src/lib/stock/reserve-naive.ts is a deliberately incorrect read-then-write twin, kept in the codebase (never imported by application code) so its bug can be measured, not just asserted. tests/stock/concurrency.test.ts runs both, 50 concurrent attempts against 10 units.

A measured run — from a machine in India against a Neon branch in US-East, ~306ms per round trip warm:

	atomic	naive
confirmed	10	10
insufficient	40	0
CHECK violations	0	40
oversold	0	0

The atomic column is what the suite asserts, exactly: confirmed === 10, insufficient === 40, checkViolations === 0. The naive column is not pinned to these numbers — its read-versus-write timing depends on round-trip latency against the fixed 20ms delay and on pool queueing under 50-way contention, so the split between confirmed, insufficient and CHECK violations will differ by environment. The suite only asserts that the naive path produces at least one CHECK violation and that oversold stays 0 in both columns, which the database enforces regardless of how the application logic behaves. That asymmetry — atomic pinned exactly, naive guaranteed only to fail loudly rather than silently oversell — is the actual claim, and it holds in every environment, unlike "40".

In this run, the naive version's application logic approved all 50 attempts — every attempt read "10 available" before any of them wrote back — and the database refused the 40 writes that would have oversold.

Running the tests

npm test

Needs DATABASE_URL and STOCKROOM_TEST_DB="1" in .env.test. The suite truncates every table before each test, so point it at a disposable database.

Scope

Phase 1 is the reservation engine only — no UI, no API, no auth yet.

Notes

npm audit reports advisories transitive to the pinned Prisma CLI's dependency tree (devDependency only, no runtime exposure — the app depends on @prisma/client and @prisma/adapter-pg, not prisma).

Dependency pinning follows a rule, not a list: anything that ships to production, or that must stay version-locked to something that does (Prisma's client and CLI together, Next, React, eslint-config-next pinned to the next version, @neon/config), is pinned exact; everything else — tooling that only runs at dev/build/test time (vitest, dotenv, eslint, typescript, tailwind, tsx, the @types/* packages) — floats on a caret range.