Full Stack • Java • System Design • Cloud • AI Engineering

Inventory Management System Design

Design a scalable enterprise Inventory Management System — covering stock tracking, multi-warehouse management, reservation and commitment lifecycle, reorder automation, stock movement audit, supplier purchase orders, cycle counting, and real-time stock visibility across channels.

1-Hour Interview Roadmap

Time Topic
0 – 5 min Requirements clarification
5 – 10 min Capacity estimation
10 – 18 min High-level architecture + core services
18 – 28 min Stock tracking model — on-hand, reserved, committed, available
28 – 36 min Reservation lifecycle + atomic operations
36 – 44 min Multi-warehouse management + stock movement audit
44 – 50 min Reorder automation + supplier purchase orders
50 – 56 min Database design + API design
56 – 60 min Trade-offs + common interview mistakes

What Are We Building?

An enterprise Inventory Management System (IMS) that provides real-time, accurate visibility of stock levels across all warehouses and sales channels, and coordinates every movement of inventory from supplier receipt through customer delivery.

The IMS is the source of truth for all stock data. Every system that touches physical goods — the Order Management System, the storefront, the purchasing department, the warehouse floor — reads from and writes to the IMS. If the IMS is wrong, the business ships orders it cannot fulfill, or blocks sales it could complete.

Scale reference: Amazon manages 350+ million SKUs across 175+ fulfillment centers. A mid-tier retailer manages 50,000–500,000 SKUs across 5–50 warehouses. Design for 200,000 SKUs across 10 warehouses, handling 500,000 stock movements per day.

Key unique challenges:

  • Concurrent reservation races — thousands of customers may attempt to reserve the same last unit simultaneously; exactly one must succeed
  • Stock accuracy — physical counts rarely match system counts exactly; cycle counting, shrinkage, and adjustments must be tracked
  • Multi-channel visibility — the same SKU is sold through online, retail stores, and B2B channels; available stock must be accurate across all channels simultaneously
  • Reorder timing — ordering too late causes stockouts; ordering too early ties up capital in slow-moving inventory; reorder points must be data-driven
  • Audit completeness — every unit's location must be traceable from supplier receipt to customer shipment, including losses and adjustments

Step 1 — Requirements

Functional Requirements

# Requirement
1 Track real-time stock levels per SKU per warehouse: on-hand, reserved, committed, and available
2 Reserve inventory atomically when a customer places an order (no overselling)
3 Commit reserved inventory when an order is confirmed and payment is captured
4 Release reservations when an order is cancelled or reservation expires
5 Record every stock movement with actor, reason, quantity, and timestamp
6 Receive inventory from supplier purchase orders and update on-hand quantities
7 Transfer stock between warehouses with full traceability
8 Automatically trigger reorder when available stock falls below the reorder point
9 Create and manage supplier purchase orders; receive and reconcile against PO
10 Support cycle counting — warehouse staff count physical stock and reconcile with system
11 Provide real-time stock visibility API for storefronts, OMS, and B2B partners
12 Alert operations team when stock falls to critical levels or expected stockouts

Non-Functional Requirements

# Requirement
1 Stock reservation must be strongly consistent — zero overselling under any concurrency
2 Stock level reads for the storefront must respond within 50ms (p99)
3 All stock mutations must be idempotent — retries must not double-count movements
4 Full audit trail — every unit movement permanently logged; no deletion, no overwrite
5 High availability — 99.9% uptime for the stock reservation API
6 Horizontally scalable reads — stock queries scale by adding read replicas
7 Support 50,000+ concurrent reservation checks during flash sales

Out of Scope

  • Warehouse physical layout and bin optimization (handled by WMS)
  • Demand forecasting and ML-based reorder quantity optimization
  • Freight and shipping carrier management
  • Point-of-sale terminal integration
  • Returns quality inspection workflow (handled by Returns Service)

Step 2 — Capacity Estimation

Traffic Estimates

SKUs tracked:                200,000
Warehouses:                  10
Total SKU-location records:  200,000 × 10 = 2,000,000

Daily stock movements:       500,000
  Order reservations:        ~200,000/day   (from OMS)
  Order commits:             ~190,000/day   (successful orders)
  Reservation releases:      ~10,000/day    (cancellations + expirations)
  Supplier receipts:         ~50,000/day    (inbound PO lines)
  Adjustments/transfers:     ~50,000/day    (cycle counts, warehouse moves)

Average movement rate:       500K / 86,400 = ~6 movements/sec
Peak rate (flash sale):      ~500 reservations/sec (focused on hot SKUs)

Stock level reads (storefronts + OMS):
  Each product page reads stock ~3 times (initial load, add to cart, checkout)
  2M page views/day × 3 reads = 6M reads/day = ~70 QPS average
  Peak (flash sale):         ~7,000 QPS

Storage Estimates

Per SKU-location record:       ~500 bytes
Total stock ledger:            2M records × 500 bytes = ~1 GB

Stock movement log:
  Per movement:                ~400 bytes
  Daily:                       500K × 400 bytes = ~200 MB/day
  Annual:                      200 MB × 365 = ~73 GB/year

Purchase orders:
  10,000 POs/day × 2 KB avg = ~20 MB/day

Reservation store (Redis + DB):
  200K active reservations × 300 bytes = ~60 MB active

Key Insights

  • Stock table is small — 2M records at ~1 GB fits entirely in memory; reads can be served from a Redis cache
  • Movement log is the large table — 73 GB/year; needs time-based partitioning and archival policy
  • Peak reservation load is concentrated — flash sales create 500 res/sec on ~5 hot SKUs; most SKUs see <1 reservation/min
  • Reads massively dominate writes — storefront queries at 7,000 QPS peak vs 500 reservations/sec; separate read path is essential

Step 3 — High-Level Architecture

flowchart TD
    OMS["Order Management System"]
    Storefront["Storefront / Mobile App"]
    WMS["Warehouse Mgmt System"]
    Purchasing["Purchasing System"]
    B2B["B2B Partners"]

    AG["API Gateway\nAuth + Rate Limiting"]
    IS["Inventory Service\n(Core — writes + orchestration)"]
    RS["Reservation Service\n(Atomic reservations — Redis + DB)"]
    MS["Movement Service\n(Audit log writer)"]
    ROS["Reorder Service\n(Threshold monitor + PO creation)"]
    POS["Purchase Order Service\n(PO lifecycle management)"]

    IDB[("PostgreSQL\nInventory DB\n(source of truth)")]
    REDIS[("Redis\nStock Cache + Reservation Lock")]
    KF["Kafka\nInventory Events"]
    NS["Notification Service"]

    OMS --> AG
    Storefront --> AG
    WMS --> AG
    Purchasing --> AG
    B2B --> AG

    AG --> IS
    AG --> RS
    AG --> POS

    IS --> IDB
    IS --> REDIS
    IS --> KF
    RS --> REDIS
    RS --> IDB
    MS --> IDB
    KF --> ROS
    KF --> MS
    KF --> NS
    ROS --> POS
    POS --> IDB

Component Responsibilities

Component Responsibility
Inventory Service Core orchestrator — manages stock ledger, coordinates all stock mutations, publishes events
Reservation Service Atomic reservation operations — Redis-backed locking + PostgreSQL persistence
Movement Service Append-only writer for the stock movement audit log; never updates, only inserts
Reorder Service Monitors available stock against reorder points; creates POs when threshold is crossed
Purchase Order Service PO lifecycle — create, send to supplier, receive, reconcile
Inventory DB PostgreSQL — stock_levels, movements, reservations, purchase_orders (source of truth)
Redis Stock level cache (hot reads); reservation locks (prevent concurrent overselling)
Kafka Decouples inventory events from downstream consumers (reorder, notifications, analytics)

Step 4 — The Stock Quantity Model

The most important design decision in any IMS is how stock quantities are modeled. A naive model stores only a single quantity field. This is insufficient for concurrent operations — it cannot represent stock that is "spoken for" by an in-progress order but not yet physically removed from the shelf.

Four-State Stock Model

Every unit of inventory exists in exactly one state at any point in time:

flowchart LR
    OnHand["On-Hand\nPhysically in warehouse\nNot yet assigned"]
    Reserved["Reserved\nHeld for a pending order\n(checkout in progress)"]
    Committed["Committed\nOrder confirmed + paid\nPending physical pick"]
    Sold["Sold / Dispatched\nPhysically removed\nfrom warehouse"]

    OnHand -->|"Customer reserves"| Reserved
    Reserved -->|"Payment captured"| Committed
    Committed -->|"Picker removes from shelf"| Sold
    Reserved -->|"Reservation expires\nor order cancelled"| OnHand
    Committed -->|"Order cancelled\n(before picking)"| OnHand

Quantity Fields

on_hand_qty     = Total physical units in the warehouse
reserved_qty    = Units held by pending reservations (checkout in progress)
committed_qty   = Units assigned to confirmed, paid orders (awaiting pick)
available_qty   = on_hand_qty - reserved_qty - committed_qty

available_qty is what the storefront shows as "In Stock"

Why All Four States Matter

Without Reserved State Without Committed State
Two customers check out simultaneously — both see "1 in stock" Order confirmed but warehouse hasn't picked yet — if cancel happens, available_qty would be wrong
Both proceed to payment — one gets charged for a unit that doesn't exist Can't distinguish "reserved by checkout" from "assigned to confirmed order"
Result: overselling Result: can't correctly account for in-progress fulfillment 
-- Stock level record — one row per SKU per warehouse
CREATE TABLE stock_levels (
    stock_id          UUID         NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    sku               VARCHAR(50)  NOT NULL,
    warehouse_id      VARCHAR(50)  NOT NULL,
    on_hand_qty       INT          NOT NULL DEFAULT 0 CHECK (on_hand_qty >= 0),
    reserved_qty      INT          NOT NULL DEFAULT 0 CHECK (reserved_qty >= 0),
    committed_qty     INT          NOT NULL DEFAULT 0 CHECK (committed_qty >= 0),
    reorder_point     INT          NOT NULL DEFAULT 0,
    reorder_qty       INT          NOT NULL DEFAULT 0,
    last_counted_at   TIMESTAMPTZ,
    updated_at        TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (sku, warehouse_id),
    CHECK (reserved_qty + committed_qty <= on_hand_qty)
);

-- Computed available quantity view
CREATE VIEW stock_available AS
SELECT
    sku,
    warehouse_id,
    on_hand_qty,
    reserved_qty,
    committed_qty,
    on_hand_qty - reserved_qty - committed_qty AS available_qty,
    updated_at
FROM stock_levels;

The CHECK (reserved_qty + committed_qty <= on_hand_qty) constraint is the database-level guard against overselling. No application bug can bypass it.

Step 5 — Reservation Lifecycle

Reservations are the most concurrency-sensitive operations in the IMS. The reservation lifecycle covers four operations, each requiring atomic execution.

Reservation States

stateDiagram-v2
    [*] --> ACTIVE : Customer checkout begins\nstock atomically decremented from available

    ACTIVE --> COMMITTED : Payment captured\nOrder confirmed by OMS
    ACTIVE --> RELEASED : Reservation expires (30-min TTL)\nor customer abandons checkout
    ACTIVE --> RELEASED : Order cancelled before payment

    COMMITTED --> FULFILLED : Warehouse picker removes item from shelf
    COMMITTED --> RELEASED : Order cancelled after payment\n(refund initiated by OMS)

    RELEASED --> [*] : available_qty restored
    FULFILLED --> [*] : on_hand_qty decremented

Atomic Reserve Operation

The reservation operation must be atomic — it must both check availability and decrement reserved_qty in a single database operation with no gap between check and update.

-- Atomic reservation — reserve N units of SKU at warehouse
-- Returns reservation_id on success, empty on failure
WITH lock_stock AS (
    UPDATE stock_levels
    SET
        reserved_qty = reserved_qty + :quantity,
        updated_at   = NOW()
    WHERE sku          = :sku
      AND warehouse_id = :warehouse_id
      AND (on_hand_qty - reserved_qty - committed_qty) >= :quantity
    RETURNING stock_id, sku, warehouse_id
)
INSERT INTO reservations (sku, warehouse_id, order_id, quantity, expires_at)
SELECT :sku, :warehouse_id, :order_id, :quantity, NOW() + INTERVAL '30 minutes'
FROM lock_stock
RETURNING reservation_id;

-- If lock_stock returns 0 rows → insufficient stock → INSERT skipped → returns empty

Why not a SELECT then UPDATE? A read-then-write pattern creates a TOCTOU (time-of-check / time-of-use) race. Two concurrent requests both read available_qty = 1, both decide to reserve, and both update — resulting in reserved_qty = 2 against on_hand_qty = 1. The single atomic UPDATE … WHERE available >= qty eliminates this race entirely.

Redis Reservation Lock for Ultra-High Concurrency

For hot SKUs during flash sales (hundreds of reservations/second on the same item), even PostgreSQL row-level locking can become a bottleneck. A Redis layer provides a first-pass atomic check before touching the database:

Redis key:    stock:available:{sku}:{warehouse_id}
Redis value:  Integer — current available quantity
Operation:    DECRBY atomically decrements; returns new value
              If new value < 0 → INCRBY to restore + return "out of stock"
              If new value >= 0 → proceed to DB reservation

Flow:
  1. Redis DECRBY {sku}:{warehouse_id} by :quantity
     → If result < 0: INCRBY to restore, return InsufficientStock
     → If result >= 0: proceed
  2. Write reservation to PostgreSQL (guaranteed to succeed if Redis passed)
  3. On any DB failure: INCRBY Redis to restore

Redis TTL:  None (persistent; invalidated on stock change)
Sync:       Redis refreshed from DB on startup and after every stock mutation

This two-tier approach means most "out of stock" responses are served entirely from Redis in sub-millisecond time, and PostgreSQL only receives requests that are highly likely to succeed.

Reservation Commit and Release

-- Commit a reservation (order confirmed + payment captured)
UPDATE stock_levels
SET
    reserved_qty  = reserved_qty - :quantity,
    committed_qty = committed_qty + :quantity,
    updated_at    = NOW()
WHERE sku          = :sku
  AND warehouse_id = :warehouse_id
  AND reserved_qty >= :quantity;

UPDATE reservations
SET status = 'COMMITTED', updated_at = NOW()
WHERE reservation_id = :reservation_id;

-- Release a reservation (cancellation or expiry)
UPDATE stock_levels
SET
    reserved_qty = reserved_qty - :quantity,
    updated_at   = NOW()
WHERE sku          = :sku
  AND warehouse_id = :warehouse_id;

UPDATE reservations
SET status = 'RELEASED', released_at = NOW()
WHERE reservation_id = :reservation_id;

-- Fulfill a committed reservation (item physically removed from shelf)
UPDATE stock_levels
SET
    on_hand_qty   = on_hand_qty - :quantity,
    committed_qty = committed_qty - :quantity,
    updated_at    = NOW()
WHERE sku          = :sku
  AND warehouse_id = :warehouse_id;

UPDATE reservations
SET status = 'FULFILLED', fulfilled_at = NOW()
WHERE reservation_id = :reservation_id;

Step 6 — Stock Movements and Audit Log

Every change to stock quantities — regardless of cause — is recorded as an immutable stock movement. The movement log is the complete history of every unit through the warehouse.

Movement Types

Movement Type Trigger Effect on stock_levels
RECEIVE Supplier delivers PO shipment on_hand_qty += quantity
RESERVE Customer checkout begins reserved_qty += quantity
COMMIT Payment captured reserved_qty -= qty; committed_qty += qty
FULFILL Item picked from shelf on_hand_qty -= qty; committed_qty -= qty
RELEASE Order cancelled / reservation expired reserved_qty -= quantity
ADJUST_UP Cycle count finds surplus on_hand_qty += quantity
ADJUST_DOWN Cycle count finds shortage / shrinkage on_hand_qty -= quantity
TRANSFER_OUT Stock moved to another warehouse on_hand_qty -= quantity at source warehouse
TRANSFER_IN Stock received from another warehouse on_hand_qty += quantity at destination
RETURN_RECEIVE Customer return received + inspected on_hand_qty += quantity (if resalable)
DAMAGE_WRITE_OFF Item damaged; permanently removed on_hand_qty -= quantity

Movement Log Schema

-- Immutable movement log — append-only, never updated or deleted
CREATE TABLE stock_movements (
    movement_id      UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    sku              VARCHAR(50)   NOT NULL,
    warehouse_id     VARCHAR(50)   NOT NULL,
    movement_type    VARCHAR(30)   NOT NULL,   -- see Movement Types table above
    quantity         INT           NOT NULL,   -- always positive; direction implied by type
    reference_type   VARCHAR(30),              -- 'ORDER' | 'PO' | 'TRANSFER' | 'CYCLE_COUNT'
    reference_id     UUID,                     -- order_id / po_id / transfer_id
    actor_type       VARCHAR(20)   NOT NULL,   -- 'SYSTEM' | 'WAREHOUSE_OP' | 'SUPERVISOR'
    actor_id         VARCHAR(100)  NOT NULL,
    notes            TEXT,
    snapshot_on_hand INT           NOT NULL,   -- on_hand_qty AFTER this movement
    snapshot_avail   INT           NOT NULL,   -- available_qty AFTER this movement
    occurred_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- Partitioned by month for query performance and archival
CREATE TABLE stock_movements_2025_03
    PARTITION OF stock_movements
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Why the Movement Log is Append-Only

The stock movement log must never be updated or deleted. This is a fundamental design principle:

  1. Auditability — every discrepancy in physical stock can be traced to the exact movement that caused it
  2. Reconciliation — on-hand quantity can be recomputed at any point in time by replaying the movement log
  3. Compliance — financial auditors require complete, unmodified records of all inventory movements
  4. Debugging — if a stock level is wrong, the movement log shows exactly what happened and when

If a movement was entered in error, the correct action is a compensating movement — a new ADJUST_UP or ADJUST_DOWN that corrects the count, with a note referencing the original movement ID.

Step 7 — Multi-Warehouse Management

Warehouse Topology

flowchart TB
    Central["Central IMS\n(Global stock visibility)"]

    subgraph East["East Region"]
        WH_NY["Warehouse NY\n(Primary — high volume)"]
        WH_BOS["Warehouse Boston\n(Secondary)"]
    end

    subgraph West["West Region"]
        WH_LA["Warehouse LA\n(Primary — high volume)"]
        WH_SEA["Warehouse Seattle\n(Secondary)"]
    end

    subgraph Intl["International"]
        WH_UK["Warehouse UK"]
        WH_DE["Warehouse Germany"]
    end

    Central --> WH_NY
    Central --> WH_BOS
    Central --> WH_LA
    Central --> WH_SEA
    Central --> WH_UK
    Central --> WH_DE

The IMS maintains stock_levels records for every SKU-warehouse combination. A product query for a SKU returns stock at all warehouses, plus a global aggregate:

-- Global stock view across all warehouses
SELECT
    sku,
    SUM(on_hand_qty)   AS total_on_hand,
    SUM(reserved_qty)  AS total_reserved,
    SUM(committed_qty) AS total_committed,
    SUM(on_hand_qty - reserved_qty - committed_qty) AS total_available
FROM stock_levels
WHERE sku = :sku
GROUP BY sku;

Inter-Warehouse Stock Transfer

When one warehouse runs low and another has surplus, a stock transfer moves inventory between locations with full audit trail.

sequenceDiagram
    participant Ops as Operations Team
    participant IS as Inventory Service
    participant DB as Inventory DB
    participant KF as Kafka

    Ops->>IS: POST /v1/transfers\n{from_warehouse, to_warehouse, sku, qty, reason}

    IS->>DB: BEGIN TRANSACTION
    IS->>DB: INSERT transfer {status: PENDING}
    IS->>DB: UPDATE stock_levels SET on_hand_qty -= qty\nWHERE warehouse_id = from_warehouse AND sku = :sku
    IS->>DB: INSERT stock_movement {TRANSFER_OUT, from_warehouse, qty}
    IS->>DB: COMMIT

    IS-->>Ops: 201 {transfer_id, status: IN_TRANSIT}

    Note over Ops: Physical stock shipped from WH-A to WH-B

    Ops->>IS: POST /v1/transfers/{transfer_id}/receive

    IS->>DB: BEGIN TRANSACTION
    IS->>DB: UPDATE stock_levels SET on_hand_qty += qty\nWHERE warehouse_id = to_warehouse AND sku = :sku
    IS->>DB: INSERT stock_movement {TRANSFER_IN, to_warehouse, qty}
    IS->>DB: UPDATE transfer SET status = COMPLETED
    IS->>DB: COMMIT

    IS->>KF: Publish inventory.stock_transferred {sku, from_wh, to_wh, qty}

Why split into two operations? Stock is decremented at the source when it physically leaves the building. It is incremented at the destination only when physically received and counted. The IN_TRANSIT state represents units that are physically between locations — not counted at either warehouse until they arrive.

Step 8 — Supplier Purchase Orders

When available stock falls below the reorder point, the system creates a purchase order to replenish from the supplier.

Reorder Trigger Flow

flowchart TB
    Movement["Stock Movement Written\n(FULFILL or ADJUST_DOWN)"]
    Movement --> Check["Reorder Service\nChecks available_qty vs reorder_point"]

    Check -->|"available_qty > reorder_point"| NoAction["No action needed"]
    Check -->|"available_qty <= reorder_point"| AlreadyPending{"Active PO\nalready exists\nfor this SKU?"}

    AlreadyPending -->|"Yes"| Skip["Skip — PO already in progress"]
    AlreadyPending -->|"No"| CreatePO["Create Purchase Order\n{supplier_id, sku, qty: reorder_qty,\nrequested_delivery: NOW() + lead_time}"]

    CreatePO --> Notify["Notify Purchasing Team\n+ Send PO to Supplier via EDI / email"]

Purchase Order Lifecycle

stateDiagram-v2
    [*] --> DRAFT : Auto-created by Reorder Service\nor manually by Purchasing

    DRAFT --> SUBMITTED : Sent to supplier (EDI / email / API)
    SUBMITTED --> ACKNOWLEDGED : Supplier confirms receipt of PO
    ACKNOWLEDGED --> PARTIALLY_RECEIVED : Some line items received
    PARTIALLY_RECEIVED --> FULLY_RECEIVED : All line items received
    ACKNOWLEDGED --> FULLY_RECEIVED : All items received in single shipment
    SUBMITTED --> CANCELLED : Supplier cannot fulfill / PO withdrawn
    ACKNOWLEDGED --> CANCELLED : PO cancelled before shipment

    FULLY_RECEIVED --> CLOSED : Reconciliation completed
    CANCELLED --> [*]
    CLOSED --> [*]

PO Receipt and Stock Update

When a supplier shipment arrives at the warehouse, the operator records the receipt against the PO:

sequenceDiagram
    participant WH as Warehouse Operator
    participant POS as PO Service
    participant IS as Inventory Service
    participant DB as Inventory DB
    participant KF as Kafka

    WH->>POS: POST /v1/purchase-orders/{po_id}/receive\n{line_items: [{sku, received_qty, condition}]}

    POS->>POS: Compare received_qty vs ordered_qty
    Note over POS: Short receive, over receive, or exact match

    POS->>IS: ReceiveStock(sku, warehouse_id, qty, po_id)

    IS->>DB: BEGIN TRANSACTION
    IS->>DB: UPDATE stock_levels SET on_hand_qty += received_qty
    IS->>DB: INSERT stock_movement {RECEIVE, warehouse_id, qty, reference: po_id}
    IS->>DB: UPDATE po_line_items SET received_qty = received_qty + :qty
    IS->>DB: COMMIT

    IS->>KF: Publish inventory.stock_received {sku, warehouse_id, qty, po_id}
    KF->>NS: Send "PO partially/fully received" notification to Purchasing team

PO Schema

CREATE TABLE purchase_orders (
    po_id            UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    supplier_id      VARCHAR(50)   NOT NULL,
    warehouse_id     VARCHAR(50)   NOT NULL,
    status           VARCHAR(30)   NOT NULL DEFAULT 'DRAFT',
    total_cost       BIGINT,                  -- in cents
    currency         CHAR(3)       NOT NULL DEFAULT 'USD',
    requested_by     VARCHAR(100),            -- user_id or 'SYSTEM' (auto-reorder)
    submitted_at     TIMESTAMPTZ,
    expected_delivery DATE,
    notes            TEXT,
    created_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE TABLE po_line_items (
    po_line_id       UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    po_id            UUID          NOT NULL REFERENCES purchase_orders(po_id),
    sku              VARCHAR(50)   NOT NULL,
    ordered_qty      INT           NOT NULL CHECK (ordered_qty > 0),
    received_qty     INT           NOT NULL DEFAULT 0,
    unit_cost        BIGINT        NOT NULL,  -- in cents
    status           VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
    created_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

Step 9 — Cycle Counting

Physical inventory counts never exactly match system counts. Shrinkage (theft, damage, evaporation), receiving errors, and picking mistakes all create discrepancies. Cycle counting is the process of periodically counting sections of the warehouse and reconciling the physical count against the system count.

Cycle Count Workflow

sequenceDiagram
    participant SM as Supervisor
    participant IS as Inventory Service
    participant OP as Warehouse Operator
    participant DB as Inventory DB
    participant KF as Kafka

    SM->>IS: POST /v1/cycle-counts\n{warehouse_id, skus: [SKU-001, SKU-002, ...]}
    IS->>DB: INSERT cycle_count {status: OPEN, assigned_to: operator_id}
    IS-->>SM: 201 {cycle_count_id}

    OP->>IS: GET /v1/cycle-counts/{id}/items  (operator's count sheet)
    IS-->>OP: [{sku, bin_location, system_qty}] — system qty shown AFTER count submission, not before

    Note over OP: Operator physically counts items in bins
    OP->>IS: POST /v1/cycle-counts/{id}/submit\n{items: [{sku, physical_count}]}

    IS->>IS: Compare physical_count vs on_hand_qty for each SKU
    IS->>DB: INSERT cycle_count_results {sku, system_qty, counted_qty, variance}

    loop For each SKU with variance
        IS->>IS: Compute adjustment = counted_qty - system_qty
        IS->>DB: UPDATE stock_levels SET on_hand_qty = counted_qty
        IS->>DB: INSERT stock_movement {ADJUST_UP or ADJUST_DOWN, qty: abs(variance), reference: cycle_count_id}
    end

    IS->>KF: Publish inventory.cycle_count_completed {warehouse_id, total_variance, sku_adjustments}
    KF->>NS: Alert ops team if total shrinkage exceeds threshold

Why System Quantity is Hidden During Counting

The operator receives the count sheet without the system quantity intentionally. If the operator can see what the system expects, they are biased toward that number — they may round their count to match or skip a thorough count. Showing the system quantity only after the physical count has been submitted eliminates this anchoring bias and produces accurate counts.

Variance Thresholds

Variance Level Action
< 0.5% of on-hand value Auto-approve and apply adjustment
0.5% – 2% of on-hand value Flag for supervisor review before applying
> 2% of on-hand value Require recount + investigation before applying
Negative variance (shortage) Always investigate for theft or damage before applying

Step 10 — Real-Time Stock Visibility API

The storefront and OMS query stock availability millions of times per day. This is the highest-volume read path in the IMS and must be optimized separately from the write path.

Read Path Architecture

flowchart LR
    Storefront["Storefront\n(product page stock badge)"]
    OMS["OMS\n(checkout availability check)"]
    B2B["B2B Partner\n(bulk availability query)"]

    CDN["CDN Cache\n(public stock status)\nTTL: 60 seconds"]
    REDIS["Redis Cache\n(available_qty per SKU)\nTTL: 30 seconds"]
    Replica["PostgreSQL Read Replica\n(source on cache miss)"]
    Primary["PostgreSQL Primary\n(writes only)"]

    Storefront --> CDN
    CDN -->|"Cache miss"| REDIS
    OMS --> REDIS
    B2B --> REDIS
    REDIS -->|"Cache miss"| Replica
    Primary -->|"Streaming replication"| Replica

    Note["On every stock_levels UPDATE:\n→ Invalidate Redis key\n→ Redis refreshed from replica\nCDN TTL expiry handles eventual refresh"]

Stock Visibility Response Tiers

Caller Data Needed Latency Target Served From
Storefront (product page) in_stock: true/false + approximate count < 50ms CDN → Redis
Checkout (cart validation) Exact available_qty < 100ms Redis
OMS (reservation pre-check) Exact available_qty per warehouse < 100ms Redis
B2B partner (bulk query) Available qty across all warehouses < 500ms Redis cluster
Ops dashboard Full stock breakdown (on-hand, reserved, committed) < 2s PostgreSQL Read Replica
Analytics / reporting Historical movements, trends No SLA Data warehouse (Redshift / BigQuery)

Stock API Responses

GET /v1/stock/{sku} — single SKU, global view:

{
  "sku": "SKU-001",
  "global": {
    "on_hand_qty":   450,
    "reserved_qty":  25,
    "committed_qty": 80,
    "available_qty": 345
  },
  "warehouses": [
    { "warehouse_id": "WH-NY",  "available_qty": 200 },
    { "warehouse_id": "WH-LA",  "available_qty": 145 }
  ],
  "in_stock": true,
  "last_updated": "2025-03-01T10:00:01Z"
}

POST /v1/stock/bulk — batch availability check:

{
  "skus": ["SKU-001", "SKU-044", "SKU-099"]
}

Response:

{
  "results": [
    { "sku": "SKU-001", "available_qty": 345, "in_stock": true  },
    { "sku": "SKU-044", "available_qty": 0,   "in_stock": false },
    { "sku": "SKU-099", "available_qty": 12,  "in_stock": true  }
  ]
}

Step 11 — Database Design

Core Tables

-- Reservation records
CREATE TABLE reservations (
    reservation_id   UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    sku              VARCHAR(50)   NOT NULL,
    warehouse_id     VARCHAR(50)   NOT NULL,
    order_id         UUID,
    quantity         INT           NOT NULL CHECK (quantity > 0),
    status           VARCHAR(20)   NOT NULL DEFAULT 'ACTIVE',
    expires_at       TIMESTAMPTZ   NOT NULL,
    committed_at     TIMESTAMPTZ,
    released_at      TIMESTAMPTZ,
    fulfilled_at     TIMESTAMPTZ,
    created_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    idempotency_key  VARCHAR(255)  UNIQUE    -- prevents duplicate reservation on retry
);

-- Inter-warehouse stock transfers
CREATE TABLE stock_transfers (
    transfer_id      UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    from_warehouse   VARCHAR(50)   NOT NULL,
    to_warehouse     VARCHAR(50)   NOT NULL,
    sku              VARCHAR(50)   NOT NULL,
    quantity         INT           NOT NULL CHECK (quantity > 0),
    status           VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
    initiated_by     VARCHAR(100)  NOT NULL,
    notes            TEXT,
    dispatched_at    TIMESTAMPTZ,
    received_at      TIMESTAMPTZ,
    created_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- Cycle count sessions
CREATE TABLE cycle_counts (
    cycle_count_id   UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    warehouse_id     VARCHAR(50)   NOT NULL,
    status           VARCHAR(20)   NOT NULL DEFAULT 'OPEN',
    assigned_to      VARCHAR(100)  NOT NULL,
    approved_by      VARCHAR(100),
    total_skus       INT           NOT NULL DEFAULT 0,
    adjusted_skus    INT           NOT NULL DEFAULT 0,
    total_variance   BIGINT        NOT NULL DEFAULT 0,  -- in units
    created_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    completed_at     TIMESTAMPTZ
);

CREATE TABLE cycle_count_items (
    item_id          UUID          NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    cycle_count_id   UUID          NOT NULL REFERENCES cycle_counts(cycle_count_id),
    sku              VARCHAR(50)   NOT NULL,
    bin_location     VARCHAR(50),
    system_qty       INT           NOT NULL,             -- qty at time of count creation
    counted_qty      INT,                                -- NULL until operator submits
    variance         INT GENERATED ALWAYS AS (counted_qty - system_qty) STORED,
    status           VARCHAR(20)   NOT NULL DEFAULT 'PENDING'
);

Key Indexes

-- Primary read path — stock by SKU across warehouses
CREATE INDEX idx_stock_levels_sku    ON stock_levels(sku);

-- Primary write path — reservation atomic update
CREATE INDEX idx_stock_levels_wh_sku ON stock_levels(warehouse_id, sku);

-- Active reservations scan (expiry job)
CREATE INDEX idx_reservations_expiry ON reservations(expires_at)
    WHERE status = 'ACTIVE';

-- Reservations by order (OMS join)
CREATE INDEX idx_reservations_order  ON reservations(order_id)
    WHERE order_id IS NOT NULL;

-- Movement log queries (per SKU history, per warehouse)
CREATE INDEX idx_movements_sku       ON stock_movements(sku, occurred_at DESC);
CREATE INDEX idx_movements_warehouse ON stock_movements(warehouse_id, occurred_at DESC);
CREATE INDEX idx_movements_reference ON stock_movements(reference_id)
    WHERE reference_id IS NOT NULL;

-- Open PO lookup (reorder dedup check)
CREATE INDEX idx_po_lines_open       ON po_line_items(sku, status)
    WHERE status IN ('PENDING', 'PARTIALLY_RECEIVED');

Step 12 — Kafka Event Architecture

The IMS publishes events for every material stock change. Downstream systems subscribe to stay synchronized without polling.

Event Topics

Topic Published By Subscribers When
inventory.stock_reserved Inventory Service Analytics Customer reserves inventory
inventory.stock_committed Inventory Service Analytics Payment captured — reservation committed
inventory.stock_fulfilled Inventory Service Analytics, Finance Item picked from shelf
inventory.stock_released Inventory Service Analytics Reservation released (cancel / expiry)
inventory.stock_received PO Service Purchasing, Analytics Supplier delivery received
inventory.stock_adjusted Inventory Service Finance, Analytics Cycle count adjustment applied
inventory.stock_transferred Inventory Service Analytics Inter-warehouse transfer completed
inventory.low_stock_alert Reorder Service Notification, Purchasing Available qty hit reorder point
inventory.out_of_stock Inventory Service Storefront, OMS, Notification Available qty reached zero
inventory.back_in_stock Inventory Service Notification, Storefront Out-of-stock SKU restocked

Back-in-Stock Notification Flow

sequenceDiagram
    participant IS as Inventory Service
    participant KF as Kafka
    participant NS as Notification Service
    participant WL as Waitlist Service

    IS->>KF: Publish inventory.back_in_stock\n{sku, warehouse_id, available_qty: 50}

    KF->>WL: Consume event
    WL->>WL: Load waitlist for SKU-001\n(customers who pressed "Notify Me")
    WL->>NS: Send back-in-stock notification\nto top 50 customers on waitlist

    Note over WL: Only notify as many customers\nas there are available units\n(don't notify 1,000 customers for 50 units)

Step 13 — Reorder Service

The Reorder Service listens to stock fulfillment events and evaluates whether the available quantity has dropped below the configured reorder point for any SKU.

Reorder Point Calculation

A static reorder point is a fixed threshold. A dynamic reorder point adapts to demand velocity and supplier lead time:

Static Reorder Point:
  reorder_point = safety_stock + (avg_daily_demand × lead_time_days)

Example:
  avg_daily_demand = 100 units/day
  lead_time_days   = 7 days (supplier delivery time)
  safety_stock     = 200 units (2 days of buffer)
  reorder_point    = 200 + (100 × 7) = 900 units

Reorder Quantity:
  reorder_qty = EOQ (Economic Order Quantity) or fixed business rule
  EOQ         = sqrt((2 × annual_demand × order_cost) / holding_cost_per_unit)

Simplified rule: reorder_qty = 30-day supply at current demand rate

Duplicate PO Prevention

The Reorder Service must not create multiple POs for the same SKU when stock drops in rapid succession (e.g., 10 orders processed in a burst all trigger the reorder check):

Before creating a PO for SKU X:
  SELECT COUNT(*) FROM purchase_orders po
  JOIN po_line_items pli ON po.po_id = pli.po_id
  WHERE pli.sku = :sku
    AND po.status NOT IN ('FULLY_RECEIVED', 'CLOSED', 'CANCELLED')
    AND po.warehouse_id = :warehouse_id

  IF count > 0 → Active PO already exists → SKIP, do not create duplicate
  IF count = 0 → Create new PO

Step 14 — Failure Handling

Reservation Expiry Job

Schedule:    Every 5 minutes (cron)
Query:       SELECT * FROM reservations
             WHERE status = 'ACTIVE' AND expires_at < NOW()
             LIMIT 1,000
             FOR UPDATE SKIP LOCKED

For each expired reservation:
  1. UPDATE stock_levels: reserved_qty -= quantity
  2. UPDATE reservation: status = 'RELEASED', released_at = NOW()
  3. INSERT stock_movement: {RELEASE, reason: 'EXPIRY'}
  4. REDIS: INCRBY stock:available:{sku}:{warehouse_id} by quantity
  5. Publish inventory.stock_released event to Kafka

The FOR UPDATE SKIP LOCKED pattern ensures that if multiple expiry job instances run (during a rolling deployment or auto-scaling), they process different rows without conflicts.

Stock Level Consistency Check

A daily reconciliation job verifies that the stock_levels table is internally consistent:

-- Find rows where the check constraint would be violated
-- (should never exist; if found, indicates a bug)
SELECT sku, warehouse_id, on_hand_qty, reserved_qty, committed_qty,
       on_hand_qty - reserved_qty - committed_qty AS available_qty
FROM stock_levels
WHERE (reserved_qty + committed_qty) > on_hand_qty
   OR on_hand_qty < 0
   OR reserved_qty < 0
   OR committed_qty < 0;

-- Find stock_levels that cannot be reconciled from movement log
SELECT sl.sku, sl.warehouse_id, sl.on_hand_qty,
       SUM(CASE WHEN sm.movement_type IN ('RECEIVE','ADJUST_UP','TRANSFER_IN','RETURN_RECEIVE')
                THEN sm.quantity ELSE -sm.quantity END) AS computed_on_hand
FROM stock_levels sl
JOIN stock_movements sm USING (sku, warehouse_id)
GROUP BY sl.sku, sl.warehouse_id, sl.on_hand_qty
HAVING sl.on_hand_qty != SUM(...);

Any discrepancy found by the reconciliation job is immediately escalated to the engineering and operations teams. In a correctly implemented IMS, this query returns zero rows.

Idempotency for Stock Mutations

Every stock mutation from the OMS carries an idempotency key. The IMS uses this to prevent double-counting if a network failure causes the OMS to retry:

Idempotency key:  movement:{reference_type}:{reference_id}:{sku}:{movement_type}
Store:            PostgreSQL UNIQUE constraint on reservations.idempotency_key
Check:            On duplicate key violation → return existing reservation_id
Effect:           Retry of a reserve/commit/release is always safe

Step 15 — Observability

Key Metrics

Metric Description Alert Threshold
inventory.reservation_success_rate % reservations that succeed < 95% (stockout signal)
inventory.reservation_latency_p99 End-to-end reservation latency > 200ms
inventory.stock_level_accuracy % SKUs matching cycle count < 98%
inventory.expiry_job_lag Age of oldest un-processed expired reservation > 10 minutes
inventory.low_stock_sku_count Number of SKUs below reorder point Spike alert
inventory.out_of_stock_sku_count Number of SKUs at zero available Any increase
inventory.po_overdue_count POs past expected delivery date > 0
inventory.redis_cache_hit_rate Cache hit rate for stock reads < 95%
kafka.consumer_lag Consumer lag on inventory topics > 5,000 messages

Stock Health Dashboard

The operations team sees a live dashboard of inventory health:

Warehouse Total SKUs In-Stock Low Stock Out of Stock Active Reservations Open POs
WH-NY 200,000 194,200 (97.1%) 4,500 (2.3%) 1,300 (0.6%) 12,400 87
WH-LA 200,000 196,800 (98.4%) 2,800 (1.4%) 400 (0.2%) 9,200 54
WH-UK 150,000 145,500 (97.0%) 3,900 (2.6%) 600 (0.4%) 6,100 31

SKUs in the "Out of Stock" column are surfaced with their daily demand rate, days-out-of-stock, and whether a PO is in progress — giving purchasing teams a prioritized restock queue.

Design Trade-offs

Trade-off 1: Single Inventory DB vs Per-Warehouse DB

Approach Pros Cons
Single DB (chosen) Global stock view in a single query; atomic cross-warehouse operations; simpler schema All warehouses share DB load; single point of failure for all warehouse operations
Per-warehouse DB Independent failure domains; lower cross-warehouse query rate No atomic cross-warehouse transactions; global stock aggregation requires distributed query

Decision: Single DB with per-warehouse rows. At 500,000 movements/day, the write load (~6/sec) is well within PostgreSQL capacity. The operational simplicity of a single schema for global stock visibility outweighs the theoretical scaling benefit of database sharding. Sharding is a future option if load grows.

Trade-off 2: Redis as Primary vs Cache for Reservations

Approach Pros Cons
Redis as cache only (chosen) PostgreSQL is authoritative; no Redis failure causes data loss; simple consistency model Flash sale hot-key pressure still hits PostgreSQL for writes
Redis as primary for reservations Sub-millisecond reservation latency; handles extreme flash sale concurrency Redis data loss on failure; complex Redis → DB sync; difficult consistency guarantees

Decision: Redis as a cache and first-pass gate, PostgreSQL as the write authority. The atomic UPDATE … WHERE available >= qty pattern in PostgreSQL handles hundreds of concurrent reservations correctly. Redis reduces DB read load by 95%+ for stock availability checks.

Trade-off 3: Synchronous vs Asynchronous Stock Mutation

Approach Pros Cons
Synchronous writes (chosen) — direct DB write + Redis update in request path Immediate consistency; caller gets confirmation of success Higher latency per mutation; DB under direct write load
Asynchronous via Kafka — publish event, worker updates DB Decoupled; high write throughput Stock levels are eventually consistent; checkout could succeed on stale data

Decision: Synchronous writes for all reservation and commitment operations. These are financial operations — eventual consistency is not acceptable. The movement log and analytics events are published to Kafka asynchronously after the synchronous DB write succeeds.

Trade-off 4: Static vs Dynamic Reorder Points

Approach Pros Cons
Static reorder point (chosen for v1) Simple; predictable; easy to configure Does not adapt to seasonal demand changes; may over- or under-order
Dynamic (ML-based) reorder point Adapts to demand velocity, seasonality, trends Requires demand forecasting model; complex; can be wrong in novel situations

Decision: Static reorder points configurable per SKU per warehouse in v1. Dynamic reorder point calculation is a future enhancement that plugs into the same Reorder Service without changing the downstream PO creation flow.

Common Interview Mistakes

Mistake Why It Matters
Using a single quantity field without reserved/committed breakdown Cannot represent in-progress orders; checkout races cause overselling
SELECT then UPDATE for reservation TOCTOU race — two concurrent checkouts both see stock and both reserve, causing overselling
Mutable movement log Without an append-only audit trail, discrepancies cannot be traced and compliance fails
Ignoring reservation expiry Abandoned checkout reservations permanently lock inventory, producing false out-of-stock
No idempotency on stock mutations OMS retries cause double-deduction; one sale removes two units from inventory
Missing the committed state Cannot distinguish "held by checkout" from "assigned to confirmed order"; cancellations are mishandled
Global stock check without warehouse context Showing "in stock" because one warehouse has units, while the order ships from an empty warehouse
Not handling split PO receipts Supplier delivers partial shipment; system must handle partial receipt without closing the PO prematurely

Summary

The Inventory Management System is the source of truth for every physical unit in the business. Its correctness directly determines whether customers receive orders they paid for and whether the business maintains accurate financial records.

flowchart TB
    Core["Inventory Management System"]

    Core --> StockModel["Four-State Stock Model\non_hand / reserved\ncommitted / available"]
    Core --> Atomic["Atomic Reservations\nSingle UPDATE with\ncheck constraint"]
    Core --> Audit["Append-Only Audit Log\nEvery movement\npermanently recorded"]
    Core --> MultiWH["Multi-Warehouse\nGlobal visibility\nInter-warehouse transfers"]
    Core --> Reorder["Reorder Automation\nThreshold monitoring\nPO creation"]
    Core --> ReadScale["Read Scalability\nRedis → Read Replica\nCDN for public data"]

The three non-negotiable design principles:

  1. The four-state model is the foundation — available_qty = on_hand - reserved - committed is the one number the business trusts. Every other system consumes it. It must be correct under any concurrency.

  2. Every stock mutation is a movement record — stock levels are not the truth; the movement log is. If the stock level is ever wrong, the movement log is the only way to find out what happened and correct it.

  3. The reservation is a contract — once a reservation is created, the customer has been shown a commitment. Allowing that reservation to be silently bypassed by another operation is a broken promise with a financial consequence.