Design an order and inventory management system: LLD

The key facts

• The entity set: Product (with variants), Location, StockMovement (append-only, signed), Order and OrderLine (stateful), PurchaseOrder, Supplier, Customer.
• The invariant that shapes everything: stock on hand is derived from movements — never a stored, editable balance.
• The state machine: Order → received, validated, allocated, picked, packed, invoiced, paid/cancelled, with legal transitions enforced in code.
• The two edge cases every review asks: two orders racing for the last unit, and the cancel-after-reservation path. Design both or fail both.
• The full picture: the full order flow is the hub page for the order side.

Entities and tables

products — id, sku, name, unit, cost price; product_variants — one row per sellable variation (size, pack), each with its own sku. locations — wherever stock physically sits. stock_movements — the heart of the design: id, product_variant_id, location_id, signed quantity, reference type and id (order, purchase, adjustment), timestamp, actor, reason. Append-only: no UPDATE, no DELETE; corrections are new rows referencing the error.

customers and customer_prices — the per-customer price list that wholesale reality demands. orders — id, customer_id, state, timestamps; order_lines — order_id, variant_id, quantity, unit price (copied at order time, never looked up later). purchase_orders and purchase_order_lines — supplier, expected date, state; goods receipt writes movements and closes the loop.

The design decision reviewers should probe: quantities exist nowhere as a writable column. On-hand is SELECT SUM(signed_quantity) ... GROUP BY variant, location or a maintained projection rebuilt from movements — the full schema reasoning walks this in depth.

The order state machine

States: received → validated → allocated → picking → picked → packed → invoiced → paid, with cancelled reachable from every pre-invoice state and backordered as a sub-state of allocated. Enforce transitions in one service layer — an order cannot reach invoiced without passing allocated and picked, and cancelled after invoiced is a credit note, not a state change.

The allocation transition is where order and inventory systems meet: it writes a reservation (a movement type or a reservation row) without consuming stock, so the shelf count stays honest while the promise is held. Stock is consumed at invoiced — a deliberate approval step, which is the operational pattern real wholesale systems use and the answer to "where does stock deduct?" that interviewers want.

API surface, minimal and sufficient

Read: GET /products, GET /stock/{variant}, GET /orders/{id} (state + history), GET /reorder-report. Write: POST /orders (validates against customer prices), POST /orders/{id}/allocate, /pick, /pack, /invoice — each transition its own endpoint, each idempotent (a retry must not double-allocate). POST /stock-adjustments for count corrections with a mandatory reason. Purchasing mirrors the shape: POST /purchase-orders, POST /purchase-orders/{id}/receive.

Idempotency keys on every write, actor and timestamp on every transition, pagination on the movement list — the audit trail will be the largest table in the database, by design. The worked example traces one order through every endpoint.

The two edge cases that decide the review

The race for the last unit. Two allocation requests hit one remaining unit. The answer is database-level serialisation — SELECT ... FOR UPDATE on the movement aggregation or an atomic reservation insert with a unique constraint — not application-level "check then write", which fails under concurrent load. A system that has not chosen its answer here has chosen overselling.

The cancel-after-reservation. A reserved order cancels: the reservation must release (not delete stock — release a reservation) and the freed quantity must become visible to allocation in the same transaction. The failure mode this prevents — phantom allocations accumulating until the counts go mad — is the classic bug of homemade systems. The LLD companion piece covers the higher-level decomposition, and the trial-first exit matters because these edge cases are exactly what a maintained product has already survived: BSimple keeps the same movement-based model — reservations, deliberate invoice approval, mirrored payment status — as live cloud software with a public REST API, from $180/month AUD.