Inventory Management System Low-Level Design

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

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.