SYSTEM DESIGN · DESIGN PROBLEMS · PLATFORM SERVICES · HARD · 46 OF 51

Design an Inventory and Order Management System: Reservations, No Overselling, Order States

Design the stock and order core of an online retailer with many warehouses: on-hand versus reserved stock, reserving at checkout with a timeout, a conditional update that makes overselling impossible, hot items, choosing a warehouse, the order state machine from placed to delivered, a saga that keeps orders and stock consistent, a stock movement ledger and daily reconciliation.

Try it on the practice board Drag the blocks, wire them up, and check your design against the reference.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
  35. 35
  36. 36
  37. 37
  38. 38
  39. 39
  40. 40
  41. 41
  42. 42
  43. 43
  44. 44
  45. 45
  46. 46
  47. 47
  48. 48
  49. 49
  50. 50
  51. 51
1 / 19

Press play to hear the narration, or step through at your own pace.

Notes

Listen

Listen. Product pages show whether an item is in stock. At checkout we hold the stock, and release it if the shopper does not pay. Orders can be placed, cancelled and returned. Stock sits in many warehouses, so each order is assigned to one, or split. Shoppers track their order's status. And the physical world changes stock too: deliveries from suppliers, damaged goods and manual counts. Pricing, search and the payment system itself are out of scope.

The qualities split in two. The number on the product page is only a hint, so it can be a few seconds stale and served from cache. The decision at checkout must be exact: reserved stock can never exceed what is physically on hand. Orders and stock live in different services but must agree within minutes, even after crashes. Checkout stays under three hundred milliseconds during a sale at twenty times normal load, and every stock change is auditable.

Functional
  • show in stock / low / sold out
  • reserve stock at checkout,
  • release if unpaid
  • place, cancel and return
  • orders
  • many warehouses; pick one
  • per order or split it
  • track order status for users
  • restocks, damage and counts
  • update stock
Non-functional
  • no oversell: reserved can never
  • exceed on hand, per warehouse
  • product page stock: may lag a
  • few seconds (it is a hint)
  • checkout stock: exact, strong
  • order and stock agree within
  • minutes, even after crashes
  • checkout p99 < 300 ms
  • survive a sale: 20× normal
  • full audit of every change

Ask scale

Scale. Two million orders on a normal day, ten million on a sale day: about a hundred fifteen a second on average, and around two thousand three hundred a second at the peak minute. With two and a half lines per order, that is under six thousand stock reservations a second, which sharded SQL handles. Stock rows total about forty gigabytes. Reads are much bigger: a hundred thousand availability checks a second from product pages. The real risk is one hot item.

Here is the number that drives a deep dive. A conditional update holds a row lock for about two milliseconds, including the commit, so a single row tops out near five hundred updates a second. A hot deal on one product at two thousand reservations a second is four times over that limit, and checkouts would queue and time out. So we will need to split hot stock across several rows, or absorb the burst in memory first.

Back of the envelope
orders: 2M/day normal
  sale day 10M/day ≈ 115/s avg
  peak ×20 ≈ 2,300 orders/s
  × 2.5 lines ≈ 5,800 holds/s
stock rows: 50M SKUs, sparse
  ≈ 200M (sku, warehouse) rows
  × 200 B = 40 GB
page views: 1B/day ≈ 12k/s
  peak ≈ 100k/s availability reads
hot SKU: 2,000 holds/s on 1 row
The hot row
a locked update holds the row
  ≈ 2 ms (update + commit)
  → max ≈ 500 updates/s per row
deal on one SKU: 2,000 holds/s
  → 4× over, queues and timeouts

options:
  split stock into N sub-rows
  pre-deduct in Redis, then SQL
  (see the flash sale problem)

Define API

The API. Availability takes product ids and a delivery area and returns stock status with a delivery estimate. Reservations are created for an order's lines, return the chosen warehouse and an expiry, and are later committed or released. All of these are idempotent, keyed by the order id. The order endpoints take an idempotency key. Warehouses report stock movements, such as received or damaged, through their own endpoint.

API
GET  /v1/availability?skus=a,b
  &zip=560001 → in stock, ETA
POST /v1/reservations
  { order_id, lines, zip }
  → { id, warehouse, expires }
POST /v1/reservations/{id}/commit
POST /v1/reservations/{id}/release
POST /v1/orders  Idempotency-Key
POST /v1/orders/{id}/cancel
POST /v1/stock/movements  (WMS)

Data model

Data model, all relational. The stock table has one row per product per warehouse with on hand and reserved counts; available is the difference. Each hold is a reservation row, unique per order line, with a state and an expiry. Every physical change is an append-only movement with a reason and a reference, which is our audit trail. Orders carry a state and a version, plus an outbox table for events. Shard stock by product id.

Data model (SQL)
stock(sku, wh, on_hand, reserved,
  version)  PK (sku, wh)
  available = on_hand - reserved
reservations(id, order_id, line,
  sku, wh, qty, state, expires_at)
  UNIQUE (order_id, line)
movements(id, sku, wh, delta,
  reason, ref, at)  append only
orders(id, user, state, version)
order_lines, outbox(event, sent)

High-level design

Trace a checkout. The order service creates the order in a pending state, then asks the inventory service to reserve each line. Inventory picks a warehouse, holds the stock with a fifteen-minute expiry, and returns. The order service then asks payments to authorize the amount. On success it commits the reservation and marks the order confirmed. If any step fails, it releases what was held.

After confirmation, the rest is driven by events. The order service publishes order confirmed through its outbox. The warehouse system for the chosen site picks and packs the items and books a carrier. When the parcel ships, a shipped event comes back. Inventory then turns the reservation into a real deduction: on hand goes down and reserved goes down by the same amount. The order service moves the order to shipped.

Deep dive: no oversell

Deep dive one: never oversell. Do not read the available count and then write, because two buyers can both read one and both succeed. Instead, do it in one conditional update: add to reserved only where on hand minus reserved is still at least the quantity. The database row lock serialises the two buyers; one updates a row, the other updates zero and is told it is sold out. Insert the reservation in the same transaction, unique per order line, so retries are harmless.

Atomic reservation
BEGIN
UPDATE stock
   SET reserved = reserved + :q
 WHERE sku = :s AND wh = :w
   AND on_hand - reserved >= :q
-- 0 rows → not enough stock
INSERT reservations (...)
  ON CONFLICT (order_id, line)
  DO NOTHING   -- idempotent
COMMIT

Deep dive: when to hold

Deep dive two: when to hold stock. At add to cart is too early: most carts are abandoned, and stock would sit locked. After payment is too late: you might take money for something already gone. The usual answer is at checkout, with a short expiry, about fifteen minutes. An expiry sweeper runs every thirty seconds and, in one transaction, releases expired holds and gives the units back. If a payment confirms after its hold expired, try to reserve again, or void the payment.

When to reserve
  • at add-to-cart: most carts are
  • abandoned → stock locked up
  • after payment: paid, then
  • sold out → refunds, angry
  • at checkout + 15 min expiry:
  • short, honest hold (chosen)
  • sweeper every 30 s:
  • expired HELD → RELEASED,
  • reserved -= qty, in one txn
  • payment late? re-reserve or void

Deep dive: hot items

Deep dive three: the read flood and hot items. Product pages read availability from a cache that is updated from stock change events, so a hundred thousand reads a second never touch the database. For a hot product, split its stock into, say, ten rows of a hundred units each. A reservation picks a random bucket and tries the next if it is empty. Ten rows give ten times the write capacity, around five thousand holds a second. For extreme drops, add an in-memory gate in front, as in the flash sale problem.

Reads and hot rows
  • pages read the cache: 100k/s,
  • updated from stock changes
  • (show "only 3 left" loosely)
  • hot SKU: split 1,000 units into
  • 10 rows of 100 (sub-buckets)
  • reserve from a random bucket,
  • try the next one if empty
  • → ~5,000 holds/s capacity
  • extreme drops: Redis pre-check
  • then the same SQL update

Deep dive: warehouses

Deep dive four: warehouses. At reservation time, list the warehouses that can deliver to the shopper's area and score them by delivery time, shipping cost, stock depth and how busy each one is. Try the best one; if the conditional update returns zero rows, try the next. If no single site has every line, decide by policy whether to split the shipment or wait. Keep a small safety stock for channels where stock syncs slowly, such as external marketplaces.

Choosing a warehouse
  • candidates: warehouses that
  • serve the delivery zip
  • score: distance (ETA), cost,
  • stock depth, current backlog
  • try best first; if 0 rows,
  • try the next one
  • no single site has all lines:
  • split shipment vs wait
  • (rule per category)
  • safety stock for slow syncs

Deep dive: order states

Deep dive five: the order state machine. Pending, reserved, paid, confirmed, picking, packed, shipped, delivered. Cancellation is allowed only before shipping, and it releases stock and voids the payment. Returns go from delivered to return requested to returned, and stock comes back only after inspection. Every transition is a conditional update on the current state and version, so a duplicate or late event cannot move an order backwards.

Order state machine
PENDING → RESERVED → PAID
  → CONFIRMED → PICKING → PACKED
  → SHIPPED → DELIVERED
cancel: any state before SHIPPED
  → CANCELLED (release, void)
DELIVERED → RETURN_REQUESTED
  → RETURNED (restock if OK)
UPDATE orders SET state = :new,
  version = version + 1
 WHERE id = :id AND state = :old

Deep dive: consistency

Deep dive six: keeping order and stock consistent without a distributed transaction. Checkout is a saga: a series of local transactions, each with an undo. Reserve pairs with release, authorize with void. If payment is declined, the order service releases the hold. Each service writes its state change and an outbox event in one transaction, and consumers deduplicate by event id. Two safety nets: an expired hold frees stock even if a release was lost, and a nightly job checks reserved counts against open holds.

Keeping order and stock in step
  • no distributed transaction:
  • a saga of local transactions
  • each step has an undo:
  • reserve ↔ release
  • authorize ↔ void
  • state + outbox in one txn,
  • consumers dedupe by event id
  • nets: hold expiry frees stock
  • even if release is lost
  • nightly: reserved = open holds

Deep dive: real stock

Deep dive seven: the physical world. The database is only a model of the shelves, and the two drift: items get damaged, lost or miscounted. Every change is recorded as a movement: received against a purchase order, shipped against an order, damaged, returned. On hand is updated in the same transaction. When a cycle count finds four hundred ninety-seven instead of five hundred, we add an adjustment of minus three, never silently overwrite. On hand can always be rebuilt from the movements.

Stock movements
  • every change is a movement:
  • +500 received (PO 77)
  • -2 shipped (order 881)
  • -1 damaged, +1 returned
  • on_hand updated in the same
  • txn as the movement
  • cycle count finds 497, not 500
  • → adjustment movement -3
  • rebuild on_hand from movements
  • any time; they must agree

Trade-offs

Review the trade-offs. We are strongly consistent exactly where money and stock meet, the reservation, and eventually consistent everywhere else, like product pages. Holding stock per warehouse is precise but costs more retries than one pooled national number. A saga keeps services independent at the price of brief mismatches, covered by expiries and reconciliation. Monitor oversells, which must be zero, expired holds, orders stuck in one state, and reconciliation differences.

Trade-offs and failures
  • exact SQL for holds; cached,
  • stale counts for pages
  • per-warehouse holds: exact,
  • but more retries than one
  • pooled national number
  • saga: services stay separate,
  • brief windows of mismatch
  • stock DB shard down → those
  • SKUs show unavailable
  • monitor: oversells (must be 0),
  • expired holds, stuck orders

Interview summary

In the interview: stock is on hand minus reserved, per product and warehouse. One conditional update reserves stock, so overselling is impossible. Hold at checkout with a fifteen-minute expiry. Serve pages from a cache and split hot items across rows. Choose the warehouse when reserving. Move orders through a versioned state machine, keep order and stock in step with a saga and outbox events, record every movement, and reconcile nightly.

Say it like this
  • available = on_hand - reserved
  • conditional UPDATE → no oversell
  • hold at checkout, 15 min expiry
  • cache for pages, split hot SKUs
  • warehouse picked at reserve time
  • order state machine, versioned
  • saga: reserve/release,
  • authorize/void, outbox events
  • movements ledger + cycle counts
  • nightly reconciliation

Interview questions

How do you prevent overselling?

Reserve with a single conditional update that adds to the reserved count only where on hand minus reserved is still at least the requested quantity. The row lock serialises concurrent buyers and zero affected rows means not enough stock.

When should stock be reserved: add to cart, checkout or payment?

Usually at checkout with a short expiry such as 15 minutes. Reserving at add to cart locks stock for abandoned carts, while reserving after payment risks charging for items that are already gone.

How do reservations get released if the user never pays?

Each hold has an expiry time and a sweeper periodically releases expired holds, returning the units in the same transaction; the order service also releases explicitly when payment fails or the order is cancelled.

How do you handle one very popular item?

A single stock row supports only a few hundred locked updates per second, so split the item stock into several sub-rows and reserve from a random one, or put an in-memory gate such as Redis in front for extreme drops, while still writing the final hold in the database.

How do you keep orders and inventory consistent across services?

Use a saga of local transactions with compensating actions such as release for reserve and void for authorize, publish state changes through a transactional outbox, make consumers idempotent and rely on hold expiries and nightly reconciliation as safety nets.

What does the order state machine look like?

Pending, reserved, paid, confirmed, picking, packed, shipped and delivered, with cancellation allowed before shipping and a return path afterwards. Each transition is a conditional update on the current state and version so duplicates cannot move an order backwards.

Should the product page show exact stock?

No, it reads a cached, slightly stale count updated from stock change events, which handles very high read volume. Only the reservation at checkout must be exact, and it is checked against the database.

How do you keep database stock in line with the physical warehouse?

Record every change as an append-only movement with a reason and reference, run cycle counts and post adjustment movements for differences, and keep a safety stock buffer for channels whose stock syncs slowly.