StockPilot: Stock, Orders and Shipping for Many Companies, Without Ever Selling What You Don't Have

The problem

Two customers order the last five headphones at the same moment. If the application reads "5 available" for each of them before writing, it sells ten headphones it does not have. Add several companies on the same database, payments that arrive late or twice, a customer who double-clicks: StockPilot is a stock, order and shipping platform built so that none of these situations ever produces overselling, a duplicate order or data leaking between companies.

The guarantee comes from the database, not the code

Checking stock and then reserving leaves a window between the read and the write. StockPilot does not read: it writes under a condition, and PostgreSQL serialises concurrent writes on the row. A constraint rejects a reserved quantity above the physical one anyway.

UPDATE stock_items
SET quantity_reserved = quantity_reserved + $quantity, version = version + 1
WHERE id = $line AND quantity_on_hand - quantity_reserved >= $quantity
RETURNING id;   -- no row: insufficient stock, nothing was reserved

The lines of a multi-item order are locked in a fixed order, so two orders A+B and B+A never wait on each other. Confirming, releasing or expiring a reservation all go through a conditional update of its status: only one wins, even when the payment and the expiry arrive in the same millisecond.

Tenant isolation follows the same idea: the application connects with a PostgreSQL role that cannot bypass Row-Level Security, and the current company is set for the transaction only. A connection returned to the pool keeps nothing from the previous request.

Order, payment, compensation

An order is created with an Idempotency-Key: the same request sent again (dropped connection, double click) returns the same order; the same key with a different body is rejected. The order, the reservation, the expected payment, the audit entry, the event and the key are written in a single transaction. Prices come from the catalogue, never from the client.

The payment provider reports the result through a signed call (HMAC of the timestamp and the body). Everything that can go wrong has a planned answer:

• payment refused: the reservation is released and the order cancelled;
• no payment in time: the worker cancels the order when the reservation expires;
• payment arriving after the expiry: nothing leaves the stock, the order is cancelled and a refund is required;
• notification sent twice, unexpected amount, forged or stale signature: no effect, rejected.

What follows (shipping, customer emails) goes through an outbox: the event is written with the change it announces, published to BullMQ afterwards, and each consumer handles it once.

Measured under load

200 buyers for the last 50 units
✓ reservations_acceptees  count==50       ✓ reservations_refusees  count==150
✓ reserved = on hand, never above         ✓ http_req_failed  rate==0

On the full journey (stock read, order, payment or cancellation), 20 concurrent sellers pass comfortably: 67 ms at p95 to create an order. At 100 sellers, throughput levels off around 125 requests per second. I checked the hypotheses one by one: a bigger connection pool changes nothing, PostgreSQL uses 1.2 cores out of 16, but the API process uses a full one. The limit is one API instance, not the database: the answer is to run several.

Operations

What is verified

167 tests against real PostgreSQL, Redis and SMTP servers, 12 of them in a real browser (Cypress), and an 8-job CI: tests, browser journeys, demos against the built applications, observability, load, security, infrastructure. I also deliberately broke 46 safeguards, one at a time: 44 make a test fail, the other two are redundant with another protection, and that is documented.

What I take away

A guarantee that depends on code discipline eventually breaks the day someone forgets a filter. So I handed the most important ones to the database: conditional writes, constraints, Row-Level Security, triggers that refuse to modify a log. And a load-test number is worth nothing without the verified hypothesis that explains it.