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.
Asked at: Shopify Interview Guide
Asked at: DoorDash Interview Guide
Asked at: Uber Interview Guide
Asked at: Databricks Interview Guide