Stackbook Logo
← Back to System Architectures

Designing an Inventory Management System

Design an inventory management system that tracks stock across warehouses, prevents overselling and reconciles physical counts with the ledger.

5 min read

Inventory answers one question for many consumers: how many of this item can we sell right now, and where are they? Sales channels, warehouses and suppliers all change the answer constantly, so the design centers on a trustworthy movement ledger and fast, safe reservations.

Requirements

Functional

  • Track on-hand, reserved and available quantity per SKU per location.
  • Receive stock, transfer between warehouses, pick, pack, ship, return, adjust.
  • Reserve stock for orders and release on cancellation.
  • Reorder alerts and cycle count reconciliation.

Non-functional

  • No overselling for tracked items.
  • Availability reads p99 under 50 ms; 10,000 reservations per second at peak.
  • 99.95% availability; every change auditable.
  • Eventual consistency of downstream views within 5 seconds.

Capacity Estimation

Assume 10M SKUs across 200 locations, but only 2% have stock at a given location: about 40M stock rows at 100 bytes is 4 GB. Order lines at 5M per day average about 60 per second; sales peaks multiply by 100 giving 6,000 reservations per second plus movements from warehouse scanners. Movement records of 150 bytes at 30M per day add 4.5 GB per day, kept in partitioned tables with older partitions archived.

High-Level Design

Storefront/OMS --> Reservation API --> Stock DB (partitioned by SKU)
WMS scanners   --> Movement API    --> Movement Ledger (append-only)
Supplier ASN   --> Receiving       -->      |
                                     Outbox -> Kafka -> Availability Cache (Redis)
                                                     -> Search/Catalog (in stock flag)
                                                     -> Replenishment, Analytics
Cycle Count Service -> Adjustments -> Movement Ledger

Data Model

  • stock(sku, location_id, on_hand, reserved, version) with available defined as on_hand minus reserved
  • movements(movement_id, sku, location_id, type, qty_delta, ref_type, ref_id, idempotency_key UNIQUE, created_at)
  • reservations(reservation_id, order_id, sku, location_id, qty, state, expires_at)
  • transfers(transfer_id, from_loc, to_loc, sku, qty, state)
  • lots(lot_id, sku, location_id, qty, expiry_date) for perishable or serialized goods

The movement ledger is the truth. The stock table is a materialized running total updated in the same transaction as each movement insert.

Deep Dives

Preventing oversell

Reserve with an atomic conditional update: increase reserved by n where on_hand minus reserved is at least n. The statement either affects one row or zero; no separate read is needed. For orders with many lines, run all line updates in a single transaction ordered by SKU to avoid deadlocks, rolling back if any line fails.

Hot SKUs

A launch item can receive thousands of reservations per second against one row, which serializes on its lock. Split the quantity into buckets: a SKU with 10,000 units is stored as 20 rows of 500 units. A reservation picks a random bucket and, if empty, tries the others. Reads sum the buckets, usually from cache. This trades a slightly more complex read and some stranded quantity for roughly linear write scalability.

Idempotency

Every movement and reservation carries an idempotency key, often the order line id or scanner event id, with a unique index. Scanner devices on flaky Wi-Fi resend, and message consumers redeliver; both are safe. Returns include the original shipment reference so the same return cannot be received twice.

Multi-location allocation

Choosing which warehouse fulfills an order is an optimization: minimize shipping cost and splits while respecting service-level promises. Allocation reads cached availability to propose, then reserves in the authoritative database. If reservation fails, choose the next best location. Cache staleness is tolerated because the reservation is the check.

Reconciliation

Physical counts differ from system counts through damage, theft and mis-picks. Cycle counts post adjustment movements with a reason code rather than editing quantities. Large variances trigger review. A nightly job verifies that each stock row equals the sum of its movements, and that reserved equals the sum of active reservations.

Reservation lifecycle

Reservations from carts expire after 15 minutes, converting back to available. Order reservations persist until shipment, at which point both on_hand and reserved decrease in one transaction through a ship movement.

Failure Modes and Trade-offs

  • Cache shows stock that is gone: reservation rejects; the UI offers alternatives. Prefer the cache erring toward under-reporting by subtracting a safety buffer.
  • Duplicate event delivery: idempotency keys on movements prevent double counting.
  • Warehouse offline: the scanner queues movements locally and syncs later, so the central system may temporarily overstate availability; protect with safety stock for that location.
  • Negative stock: allow only through explicit adjustments, flag for investigation; never silently clamp at zero.
  • Strong versus eventual consistency: reservations are strong; everything downstream is eventual.
  • Bucket splitting: improves throughput but complicates reads and rebalancing.
  • Reserve at cart versus at checkout: cart reservation protects customers but wastes inventory through abandonment.

Key takeaways

  • An append-only movement ledger is the source of truth; stock counts are derived and verifiable.
  • Reserve with a single atomic conditional update, never read then write.
  • Shard hot SKUs into quantity buckets to scale writes.
  • Idempotency keys make scanners, retries and redelivery safe.
  • Caches propose, the database decides.
  • Reconcile counts continuously and make corrections as new adjustment records.