Inventory: Stock, Reservations and Overselling Prevention

In one line

Inventory tracks stock per product per warehouse, reservations held during checkout, and an append-only stock movement ledger. Overselling is prevented with conditional atomic decrements or row locks, and reservations expire if payment doesn't complete. Distributed inventory adds per-warehouse allocation and eventual consistency for availability display.

0/8 · 0%

Think of it like this

A concert ticket counter that puts tickets "on hold" for 10 minutes while you pay. Held tickets can't be sold to anyone else; if you walk away, they go back on sale.

Key ideas

01
Tables: product, warehouse, stock(product_id, warehouse_id, on_hand, reserved) with CHECK (reserved <= on_hand AND reserved >= 0), reservation(id, order_id, product_id, warehouse_id, qty, expires_at, status), stock_movement(id, product_id, warehouse_id, delta, reason, ref_id, at).
02
Reserve: UPDATE stock SET reserved = reserved + :q WHERE product_id = :p AND warehouse_id = :w AND on_hand - reserved >= :q (0 rows = not enough), plus insert the reservation, in one transaction.
03
Confirm (payment succeeded): decrement on_hand and reserved, mark the reservation confirmed, insert a stock_movement. Expire: a job releases expired reservations with FOR UPDATE SKIP LOCKED.
04
The movement ledger is the audit and reconciliation source: on_hand should equal the sum of movements; a nightly job checks it.
05
Distributed inventory: availability shown on product pages is a cached aggregate (eventually consistent); the authoritative check is the reservation transaction in the warehouse's shard. Hot flash-sale products use the patterns from Topic 5.3 (bucketed stock or Redis tokens).

Code & diagrams

inventory.sqlsql

CREATE TABLE stock (
  product_id   bigint NOT NULL REFERENCES product(id),
  warehouse_id bigint NOT NULL REFERENCES warehouse(id),
  on_hand      int NOT NULL CHECK (on_hand >= 0),
  reserved     int NOT NULL DEFAULT 0 CHECK (reserved >= 0 AND reserved <= on_hand),
  PRIMARY KEY (product_id, warehouse_id)
);
CREATE TABLE reservation (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL, product_id bigint NOT NULL, warehouse_id bigint NOT NULL,
  qty int NOT NULL CHECK (qty > 0),
  status text NOT NULL CHECK (status IN ('held','confirmed','released')),
  expires_at timestamptz NOT NULL
);
CREATE INDEX reservation_expiry ON reservation (expires_at) WHERE status = 'held';

-- reserve (one transaction)
BEGIN;
UPDATE stock SET reserved = reserved + 2
WHERE product_id = 42 AND warehouse_id = 3 AND on_hand - reserved >= 2;   -- 0 rows -> out of stock
INSERT INTO reservation (order_id, product_id, warehouse_id, qty, status, expires_at)
VALUES (9001, 42, 3, 2, 'held', now() + interval '10 minutes');
COMMIT;

Interview problem

The problem

Prevent overselling across web and app channels

Stock is sold via website, mobile app and marketplace partners; overselling happens during sales because each channel checks stock and then decrements. Design the inventory database and flows.

When it breaks

Reservation expiry job stops

What you see

Abandoned carts keep stock reserved; products show out of stock while warehouses are full, and sales drop.

Fix & prevent

Monitor the count and age of expired-but-held reservations; make the job idempotent and redundant; also release lazily when checking availability.

Explain it without notes

Why keep a stock movement ledger if you already have on_hand?

Practice

Write the release query for expired reservations that is safe with multiple job instances.

Trade-offs

↔
Reservations prevent overselling at the cost of temporarily unavailable stock and an expiry process.

Done when you can

I can design inventory with atomic reservations, expiry, a movement ledger and reconciliation.

Command Palette