Design an inventory management system

Stock counts across warehouses that stay correct under concurrent orders and returns.

Problem 045 of 100Commerce, booking and paymentsL51 Nov 2026

Concepts this design leans on

Distributed transactions (2PC and sagas) — All-or-nothing across services, or a sequence of steps with compensations.
Idempotency and exactly-once — Making a repeated request harmless, which is how 'exactly once' is really built.
Sharding and partitioning — Splitting data by key so each machine holds a slice it can handle.
Message queues and pub/sub — Decoupling producers from consumers with a durable log in between.
Replication — Keeping copies so reads scale and a dead machine loses nothing.

01The design, in the six-step framework

1. Requirements

Functional

Track stock per SKU per warehouse: on hand, reserved for orders, available to promise.
Reserve stock when an order is placed, commit on shipment, release on cancellation or return.
Receive inbound shipments and record cycle-count corrections.
Answer availability queries for product pages and for allocation (which warehouse ships).
Out of scope: demand forecasting, purchase ordering, warehouse robotics.

Non-functional

10 million SKUs across 200 warehouses; 2 billion SKU-warehouse rows.
20,000 reservation operations a second at peak; 500,000 availability reads a second.
Never oversell: reserved plus available never exceed on hand; every change is auditable.
Availability reads may be a few seconds stale; reservations are strongly consistent.
99.99 percent availability for reservations; a warehouse system outage must not block others.

Back of the envelope

2 × 10⁹ rows × 100 B = 200 GB of inventory state; with indexes and history pointers about 500 GB, sharded by SKU.

20,000 reservations a second, each a short transaction on one or a few rows: a sharded transactional database with 20 shards does this with room to spare.

Ledger: every change is a row; 20,000 × 86,400 ≈ 1.7 billion ledger rows a day, about 200 GB a day, kept hot for 90 days and archived after.

Availability reads at 500,000 a second come from a cache of available-to-promise per SKU (sum over warehouses), refreshed by change events.

2. Core entities

Stock position: sku_id, warehouse_id, on_hand, reserved, damaged, version.
Reservation: reservation_id, order_id, sku_id, warehouse_id, quantity, state (held, committed, released), expires_at.
Ledger entry: entry_id, sku_id, warehouse_id, delta_on_hand, delta_reserved, reason (receipt, reserve, ship, cancel, count), reference, actor, timestamp.
Availability snapshot: sku_id, available_total, per-warehouse available, updated_at.

3. API

GET /availability/{sku}: available to promise, total and by warehouse, from the cache. Called by product pages.
POST /reservations with order_id, lines (sku, qty), preferred warehouse, idempotency key; returns per-line results. Called by the order service.
POST /reservations/{id}/commit on shipment; POST /reservations/{id}/release on cancellation.
POST /receipts: inbound stock by warehouse; POST /adjustments: counts and damage. Called by warehouse systems.
GET /ledger?sku=…&warehouse=…&from=…: the audit trail.

4. High-level design

Reserving two lines for an order:

The order service calls reserve with an idempotency key equal to the order id.
The inventory service chooses a warehouse per line (nearest with stock, from the availability cache) and runs, per line, a conditional update on the stock row: reserved = reserved + q where on_hand − reserved ≥ q and version = v; a ledger entry is written in the same transaction.
If any line fails, the service releases the lines already reserved (compensation) and returns the failing lines; the order service can retry with another warehouse or tell the customer.
The transaction's ledger rows are read by an outbox publisher and emitted as change events; the projector updates the availability cache within a second.
On shipment, commit moves quantity from reserved to shipped (on_hand − q, reserved − q); on cancellation, release reverses the reservation; both are idempotent on reservation id.

5. Deep dives

How does a reservation across two warehouses stay consistent without a distributed transaction?

Option	Behaviour	Trade-off
Two-phase commit across shards	Atomic across warehouses	Locks held across shards; a coordinator failure blocks rows; slow at 20,000 a second
Saga: reserve each line, compensate on failure	Each line is one local transaction; a failed line triggers releases of the others	A brief window where one line is held and another not; acceptable because holds expire and nothing is shipped yet
Single-warehouse orders only	Reserve all lines on one shard	Simple, but fails orders that could be split

The saga is the choice; it is the standard form of cross-shard consistency here because reservations are temporary and compensations are natural. Shards are by SKU so that one SKU's rows across all warehouses are on one shard, which makes the common case, one line, a single-shard transaction.

What stops a double reservation when the caller retries or two threads race?

The conditional update is the race guard: two concurrent reservations for the last unit both read available = 1, but the update where on_hand − reserved ≥ q succeeds for only one; the other gets zero rows updated and reports out of stock. Retries are handled by the idempotency key: the reservation row is keyed by order id and line, so a repeated call finds the existing reservation and returns it. The interviewer's multithreading follow-up is about exactly this: the database row version does the serialisation, not application locks, so the service can run as many threads and instances as needed.

Why a ledger, not just a counter?

A counter tells you what the stock is; the ledger tells you why. Every change writes a ledger row in the same transaction as the counter change, with a reason and a reference, so the stock position at any moment is the sum of the ledger, and a cycle count that disagrees is reconciled as an adjustment entry rather than an overwrite. This is the inventory form of a double-entry ledger: nothing is ever edited, only appended. It is also what makes the outbox possible: the ledger rows are the events.

How are availability reads fast and roughly right?

Summing available across 200 warehouses on every product page is 500,000 multi-row reads a second on the transactional store. The projector consumes change events and maintains a per-SKU availability record in a cache; reads cost one lookup, lag by about a second, and the reservation transaction remains the truth, so a stale yes becomes a clean out-of-stock at checkout rather than an oversell. For flash-sale SKUs the projector publishes more often and the product page shows a conservative number.

What happens when a warehouse's own system is down or sends receipts late?

Receipts and adjustments are accepted asynchronously with the warehouse's own event ids as idempotency keys, so a late or repeated feed is harmless. While a warehouse feed is down, its on_hand is frozen but reservations against it still work from the last known state; if the gap grows beyond a threshold, the allocator stops preferring that warehouse so new orders go elsewhere, which isolates the outage without blocking sales.

6. What a strong answer sounds like

State the invariant first, on_hand ≥ reserved always, and show the one conditional update that enforces it.
Explain the saga for multi-warehouse orders and why holds that expire make compensation safe.
Quote 20,000 reservations a second against 500,000 reads a second and route each to a different store.
Expect follow-ups on multithreading (the row version serialises), on reconciliation with physical counts, and on what to show on the page when the cache is behind.
A common mistake is to decrement a counter in a cache during checkout and reconcile later; interviewers will ask where the truth is and expect the ledger.

02Where this question was reported

LeetCode Discuss · Follow-up round focused on scalability and multithreading (search snippet)