Core Requirements
An inventory management system tracks the quantity of products across one or more warehouses, coordinates stock reservations during checkout, triggers reorders when stock runs low, and reconciles physical counts against system records. At scale: Amazon manages hundreds of millions of SKUs across thousands of fulfillment centers. The key challenges: concurrent reservations (race conditions on the last unit), accurate real-time availability across locations, and automated replenishment to prevent stockouts.
Data Model
Product: product_id, sku, name, category, unit_of_measure, reorder_point, reorder_quantity, lead_time_days. Warehouse: warehouse_id, name, address, type (FULFILLMENT, DISTRIBUTION, RETAIL). Inventory: inventory_id, product_id, warehouse_id, quantity_on_hand, quantity_reserved, quantity_available (computed: on_hand – reserved), last_updated. InventoryTransaction: txn_id, product_id, warehouse_id, txn_type (RECEIVE, RESERVE, RELEASE, SHIP, ADJUST, TRANSFER), quantity, reference_id (order_id or PO_id), created_at. PurchaseOrder: po_id, supplier_id, product_id, warehouse_id, ordered_qty, received_qty, status (DRAFT, SENT, PARTIAL, COMPLETE), expected_delivery.
Concurrent Reservation
When a customer adds an item to cart and proceeds to checkout, the inventory must be reserved to prevent overselling. Race condition: two customers both see quantity_available=1 and both try to reserve. Both reservations succeed — one will fail at fulfillment. Solution using optimistic locking:
def reserve(product_id, warehouse_id, quantity, order_id):
with db.transaction():
inv = db.query(
"SELECT * FROM inventory WHERE product_id=%s AND warehouse_id=%s FOR UPDATE",
product_id, warehouse_id
)
if inv.quantity_available < quantity:
raise InsufficientStockError()
db.execute(
"UPDATE inventory SET quantity_reserved = quantity_reserved + %s WHERE inventory_id = %s",
quantity, inv.inventory_id
)
db.execute(
"INSERT INTO inventory_transactions (product_id, warehouse_id, txn_type, quantity, reference_id) VALUES (%s, %s, %s, %s, %s)",
product_id, warehouse_id, "RESERVE", quantity, order_id
)quantity_available is a computed column: quantity_on_hand – quantity_reserved. The SELECT FOR UPDATE locks the inventory row, serializing concurrent reservations. When the order ships: decrement quantity_on_hand by the shipped quantity and decrement quantity_reserved (the reservation is fulfilled). On order cancellation: decrement quantity_reserved only (releasing the hold back to available).
Multi-Warehouse Fulfillment
An order may be fulfilled from multiple warehouses. Fulfillment allocation algorithm: (1) Find all warehouses with sufficient stock for each order line. (2) Prefer the warehouse closest to the delivery address (minimize shipping cost and time). (3) Minimize the number of shipments (consolidate to one warehouse if possible). (4) Consider warehouse capacity and current workload (avoid overloading one warehouse). This is an optimization problem: use a greedy assignment for most cases (nearest warehouse with full quantity), falling back to split shipments. Store the fulfillment allocation per order line: order_line_id → {warehouse_id, quantity}.
Reorder Automation
Reorder point (ROP) = average daily demand * lead time in days + safety stock. Safety stock absorbs demand variability and supplier delays. When quantity_available drops below the reorder_point: trigger a purchase order to the supplier. Reorder quantity = economic order quantity (EOQ) or a fixed quantity set by the purchasing team. Implementation: a background job runs every hour, scanning for products where quantity_available < reorder_point AND no open PO exists for this product. Creates a draft PO, routes it for approval (or auto-approves if below a cost threshold). Sends the approved PO to the supplier via EDI (Electronic Data Interchange) or supplier portal API. Track PO status; receive goods against the PO (update quantity_on_hand, record receive transaction).
Inventory Reconciliation
Physical counts (cycle counting) periodically verify the system’s quantity matches the warehouse floor. Discrepancies occur due to theft, damage, receiving errors, or system bugs. Reconciliation process: warehouse staff scans actual quantities. The system computes the variance (physical count – system count). Adjustments above a threshold require manager approval. Apply approved adjustments: INSERT a ADJUST transaction, update quantity_on_hand. Track adjustment frequency per warehouse location (high-variance locations get more frequent counts). Shrinkage rate (% of inventory lost to theft/damage) is a key retail KPI, typically 1-2% of sales.
Interview Tips
- Eventual vs strong consistency: for inventory reservations, strong consistency is required (cannot oversell). For analytics (total warehouse value), eventual consistency is acceptable.
- Negative inventory: never allow quantity_on_hand or quantity_available to go below zero at the database constraint level. Add a CHECK constraint.
- Event sourcing: inventory is a natural fit for event sourcing — the inventory_transactions table IS the event log; the current quantity is derived by summing transactions.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you prevent overselling when multiple customers buy the last unit simultaneously?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Use database row-level locking with SELECT FOR UPDATE. When a reservation request arrives: begin a transaction, SELECT the inventory row FOR UPDATE (this acquires a lock and blocks other transactions from modifying the same row), check quantity_available, decrement quantity_reserved if sufficient, commit. Only one transaction holds the lock at a time; concurrent requests queue behind it. If inventory is insufficient for any waiting transaction, it returns InsufficientStockError without modifying the row. For very high-traffic items (flash sales, hot products): pre-load available quantity into Redis as a counter. Use DECRBY in Redis as a first gate (fast, ~0.1ms). Only the transactions that pass the Redis gate proceed to the database. This sheds load from the DB while maintaining database as the source of truth.”
}
},
{
“@type”: “Question”,
“name”: “What is a reorder point and how do you calculate safety stock?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The reorder point (ROP) is the inventory level that triggers a new purchase order: ROP = average_daily_demand * lead_time_days + safety_stock. Safety stock absorbs variability in demand and supplier lead time: safety_stock = Z * sqrt(lead_time * demand_variance + avg_demand^2 * lead_time_variance). Z is the service level factor (Z=1.65 for 95% service level, Z=2.33 for 99%). In practice, companies simplify: safety_stock = max_daily_demand * max_lead_time – avg_daily_demand * avg_lead_time. Reorder quantity (how much to order): Economic Order Quantity (EOQ) = sqrt(2 * annual_demand * order_cost / holding_cost_per_unit). EOQ minimizes total cost (ordering costs decrease with larger orders; holding costs increase). For highly seasonal products, dynamic safety stock is recalculated monthly using recent demand data.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle inventory across multiple warehouses for order fulfillment?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “When an order contains multiple items, the fulfillment engine determines which warehouse(s) to fulfill from. Greedy single-warehouse preference: first, check if one warehouse can fulfill the entire order (minimizes shipments, simplifies logistics). If yes, assign to the warehouse with the lowest shipping cost to the delivery address. If no single warehouse can fulfill: split — assign each order line to the nearest warehouse with sufficient stock. For split shipments: some platforms promise one shipment but split internally; others inform the customer of multiple shipments with different tracking. Inventory reservation: after warehouse assignment, reserve stock at the assigned warehouse. Track at the order-line level: line.warehouse_id, line.reserved_qty. On shipment confirmation: decrement quantity_on_hand and quantity_reserved at that warehouse.”
}
},
{
“@type”: “Question”,
“name”: “How do you reconcile inventory discrepancies from physical counts?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Cycle counting is a continuous reconciliation practice: instead of counting all inventory once per year (a full physical inventory count), count a subset of SKUs each day so that every SKU is counted several times per year. High-value or high-velocity items are counted more frequently. Process: warehouse staff scans or counts physical quantity for assigned SKUs. The system displays the expected quantity (quantity_on_hand). Staff records actual count. Variance = actual – expected. Variances above a threshold (e.g., +/- 5 units or +/- $100) require manager review and explanation. Approved adjustments: INSERT an ADJUST transaction into inventory_transactions, update quantity_on_hand. Root cause tracking: categorize adjustments by cause (theft, damage, receiving error, system error). High shrinkage locations get more frequent counts and security review.”
}
},
{
“@type”: “Question”,
“name”: “How do you design the inventory system to support both online and in-store channels?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Omnichannel inventory: the same physical product exists in a pool shared by online orders, in-store sales, and ship-from-store orders. Each store is modeled as a warehouse with its own inventory record. Online orders check availability across all relevant warehouses (online fulfillment centers + stores that allow ship-from-store). In-store POS sales decrement store inventory in real-time via the same inventory service API. Reserve online orders against specific warehouses. Challenges: in-store transactions happen at the POS and may not sync instantly (offline POS mode). Buffer stock: reserve a portion of store inventory for walk-in customers (e.g., never allow online orders to reserve the last 2 units at a store). Expose a single available-to-promise quantity to product pages, aggregated across fulfillable warehouse locations.”
}
}
]
}
Asked at: Shopify Interview Guide
Asked at: DoorDash Interview Guide
Asked at: Uber Interview Guide
Asked at: Databricks Interview Guide