Inventory Management System Low-Level Design

By /

Requirements

  • Track stock levels across multiple warehouses for millions of SKUs
  • Reserve inventory when an order is placed; deduct when fulfilled
  • Prevent overselling: never allow stock to go below zero
  • Support backorders and pre-orders (negative stock allowed for specific SKUs)
  • Real-time inventory queries for product availability display (<50ms)
  • 10K orders/second during peak sales events

Data Model

Product(product_id UUID, name, sku VARCHAR UNIQUE, category, weight_g, dimensions)

Inventory(inventory_id UUID, product_id UUID, warehouse_id UUID,
          quantity_on_hand INT,    -- physically in warehouse
          quantity_reserved INT,  -- reserved by pending orders
          quantity_available INT  -- on_hand - reserved (computed or stored)
          reorder_threshold INT, reorder_quantity INT)

InventoryTransaction(txn_id UUID, product_id UUID, warehouse_id UUID,
                     type ENUM(RECEIVE,RESERVE,RELEASE,FULFILL,ADJUST,TRANSFER),
                     quantity INT,  -- negative for deductions
                     reference_id UUID, reference_type VARCHAR,
                     created_at, created_by)

Warehouse(warehouse_id UUID, name, location, capacity)

Preventing Overselling (Atomic Reservation)

Two concurrent orders for the last unit of a SKU. Without locking, both see quantity_available=1, both reserve — oversell. Solution:

def reserve_inventory(product_id, warehouse_id, quantity):
    BEGIN TRANSACTION
    SELECT quantity_available FROM Inventory
    WHERE product_id = :pid AND warehouse_id = :wid
    FOR UPDATE  -- row-level lock

    if quantity_available < quantity:
        ROLLBACK
        raise InsufficientStock

    UPDATE Inventory
    SET quantity_reserved = quantity_reserved + :qty,
        quantity_available = quantity_available - :qty
    WHERE product_id = :pid AND warehouse_id = :wid

    INSERT INTO InventoryTransaction (type='RESERVE', quantity=:qty, ...)
    COMMIT
    return True

The SELECT FOR UPDATE serializes concurrent reservations for the same product+warehouse. Only one transaction holds the lock; the second waits, sees the reduced quantity, and fails if insufficient.

Inventory Operations

  • RECEIVE: new stock arrives at warehouse. quantity_on_hand += N.
  • RESERVE: order placed. quantity_reserved += N, quantity_available -= N.
  • RELEASE: order cancelled. quantity_reserved -= N, quantity_available += N.
  • FULFILL: order shipped. quantity_on_hand -= N, quantity_reserved -= N.
  • ADJUST: physical count correction (cycle count). Direct update with reason.
  • TRANSFER: move stock between warehouses. Deduct from source, add to destination.

Multi-Warehouse Availability

For a product available across 10 warehouses: total available = SUM(quantity_available) across warehouses. For order routing: select the warehouse closest to the shipping address with sufficient stock. Cache per-warehouse availability in Redis for fast product page rendering:

# Cache total available across all warehouses
redis.setex(f'stock:{product_id}', 60, total_available)

# On reservation: DECRBY (atomic)
remaining = redis.decrby(f'stock:{product_id}', quantity)
if remaining < 0:
    redis.incrby(f'stock:{product_id}', quantity)  # undo
    # Fall back to DB check

Reorder Management

Trigger replenishment when stock falls below reorder_threshold. After each FULFILL or ADJUST transaction: check if quantity_on_hand < reorder_threshold. If yes: create a PurchaseOrder to the supplier for reorder_quantity units. Automated via a daily batch job that queries all Inventory records below threshold. Supplier lead time tracking: expected_arrival_date on PurchaseOrder enables “Ships in 3-5 days” messaging even when current stock is depleted.

Key Design Decisions

  • SELECT FOR UPDATE for atomic reservation — prevents overselling without application-level locking
  • InventoryTransaction ledger — full audit trail, enables reconciliation between expected and physical stock
  • Redis cache for availability display — fast product page rendering, DB only for actual reservation
  • Separate quantity_on_hand and quantity_reserved — accurate visibility into committed vs available stock
  • Warehouse routing — pick closest warehouse with stock to minimize fulfillment time and cost


{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you prevent overselling in an inventory system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Overselling happens when two concurrent orders both read available_quantity=1 and both succeed. Prevention via SELECT FOR UPDATE: when reserving stock, lock the inventory row with SELECT … FOR UPDATE. The first transaction acquires the lock, checks and reserves. The second waits, re-reads the (now reduced) quantity, and fails if insufficient. This serializes concurrent reservations for the same SKU at the same warehouse. Alternative: optimistic locking with a version column. UPDATE Inventory SET reserved += qty WHERE product_id = :p AND available >= qty. If the update affects 0 rows (another reservation already reduced available), retry. For high-concurrency flash sales (10K concurrent requests for the same SKU): use a Redis atomic counter for the initial check — DECRBY stock:{product_id} {qty}; if result < 0, INCRBY to undo and reject. Fall through to DB reservation only for confirmed reservations.”}},{“@type”:”Question”,”name”:”What is the difference between quantity_on_hand, quantity_reserved, and quantity_available?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”These three quantities track the full inventory lifecycle: quantity_on_hand: the total physical count of units present in the warehouse right now. This is what a physical inventory count would find. It decreases when items are shipped (FULFILL) and increases when received from suppliers (RECEIVE). quantity_reserved: units that are committed to pending orders but not yet shipped. Increased on order placement (RESERVE), decreased on order cancellation (RELEASE) or fulfillment (FULFILL). quantity_available: quantity_on_hand – quantity_reserved. This is what can be sold to new customers. The number displayed on the product page as "X in stock." Example: 10 on_hand, 3 reserved (pending orders) = 7 available. When order 4 is placed: reserved becomes 4, available becomes 6. When order 1 ships: on_hand becomes 9, reserved becomes 3, available remains 6. This three-field model prevents displaying items as available that are already committed to other orders.”}},{“@type”:”Question”,”name”:”How do you handle inventory across multiple warehouses?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Multi-warehouse inventory: each (product_id, warehouse_id) pair has its own Inventory record. Total available = SUM(quantity_available) across all warehouses for a product. For product display: query the sum and cache in Redis (key=stock:{product_id}, TTL=60s). For order routing: when an order is placed, select the optimal warehouse — closest to the shipping address that has sufficient stock of all ordered items. Use a shipping zone table or carrier rate API to estimate delivery time from each warehouse. Prefer warehouses with shorter delivery time (fewest shipping zones away). Single-warehouse stock check: if one warehouse has all items, prefer it over splitting. Split fulfillment: if no single warehouse has all items, split the order across two warehouses that together cover all items. Minimize splits — customers prefer to receive all items together. Track which warehouse is assigned to each OrderItem in the FulfillmentAssignment table.”}},{“@type”:”Question”,”name”:”How do you design the inventory transaction ledger for auditing?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Never directly update quantity_on_hand or quantity_reserved without a corresponding transaction record. Every inventory change should be recorded as an InventoryTransaction row: (txn_id, product_id, warehouse_id, type, quantity, reference_id, reference_type, created_at, created_by). For a RESERVE: quantity = +qty to reserved (or -qty to available). For FULFILL: quantity = -qty from on_hand and -qty from reserved. This is double-entry bookkeeping for inventory. Benefits: (1) Reconciliation: sum all transactions for a product to get the expected on_hand. Compare to actual count — discrepancies indicate theft, damage, or system bugs. (2) Audit trail: every stock movement is traceable to an order, adjustment, or transfer. (3) Rollback: if an order is cancelled, insert a RELEASE transaction — no need to modify previous transactions. (4) Analytics: identify which products are most frequently adjusted (shrinkage indicators).”}},{“@type”:”Question”,”name”:”How do you handle flash sale inventory management when demand spikes 100x?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Flash sales (Black Friday, product launches) create sudden extreme demand. With normal DB reservation, 10K concurrent requests all hit SELECT FOR UPDATE on the same rows — massive lock contention, high latency, potential deadlocks. Redis-based reservation: pre-load the available quantity into Redis before the sale: SET flash_stock:{product_id} {quantity}. On each reservation attempt: remaining = DECRBY flash_stock:{product_id} {qty}; if remaining < 0: INCRBY flash_stock:{product_id} {qty} (undo); return "sold out". Redis DECRBY is atomic — no lock contention, ~0.5ms per operation. Redis handles 1M+ ops/second. After the Redis check: persist the reservation to the DB asynchronously (enqueue a reservation job). The Redis counter is the authoritative lock for the flash sale window. After the sale: reconcile Redis counter with DB reservations. Handle Redis failure: fall back to DB with timeouts and circuit breakers.”}}]}

Shopify system design is the canonical inventory management interview topic. See common questions for Shopify interview: inventory management system design.

Amazon system design covers inventory management at scale. Review patterns for Amazon interview: inventory and warehouse management design.

Stripe system design covers payment and inventory reservation. See design patterns for Stripe interview: inventory reservation and payment design.

Scroll to Top