Core Entities
Product: product_id, sku, name, category_id, unit_cost, unit_weight_grams, dimensions (JSONB: length, width, height in mm), reorder_point, reorder_quantity, lead_time_days, is_active. Warehouse: warehouse_id, name, address, timezone, capacity_cubic_meters. Location: location_id, warehouse_id, aisle, bay, level, bin, type (BULK, PICK_FACE, OVERFLOW), max_weight_kg, max_volume_cubic_cm. Inventory: inventory_id, product_id, location_id, quantity_on_hand, quantity_reserved, quantity_available (computed: on_hand – reserved), last_counted_at, last_moved_at. InventoryTransaction: txn_id, product_id, location_id, txn_type (RECEIVE, SHIP, ADJUST, TRANSFER, RESERVE, RELEASE), quantity (positive=in, negative=out), reference_id (order_id or PO_id), reference_type, performed_by, created_at. PurchaseOrder: po_id, supplier_id, status (DRAFT, SENT, CONFIRMED, PARTIAL, RECEIVED, CANCELLED), line_items (JSONB), expected_date, created_at.
Stock Reservation and Atomic Allocation
class InventoryService:
def reserve_stock(self, product_id: int, quantity: int,
order_id: int) -> bool:
with db.transaction():
# Atomic read-modify-write with row-level lock
inv = db.query_one(
"SELECT inventory_id, quantity_on_hand, quantity_reserved "
"FROM inventory WHERE product_id = %s "
"FOR UPDATE", # row lock prevents concurrent oversell
product_id
)
if not inv:
raise ProductNotFound(product_id)
available = inv.quantity_on_hand - inv.quantity_reserved
if available < quantity:
raise InsufficientStock(
product_id, available, quantity
)
# Reserve stock
db.execute(
"UPDATE inventory SET quantity_reserved = quantity_reserved + %s "
"WHERE inventory_id = %s",
quantity, inv.inventory_id
)
# Audit trail
db.insert("inventory_transactions", {
"product_id": product_id,
"location_id": inv.location_id,
"txn_type": "RESERVE",
"quantity": -quantity, # negative = outbound reservation
"reference_id": order_id,
"reference_type": "ORDER"
})
return True
def fulfill_reservation(self, product_id: int, quantity: int,
order_id: int):
with db.transaction():
inv = db.query_one(
"SELECT * FROM inventory WHERE product_id = %s FOR UPDATE",
product_id
)
db.execute(
"UPDATE inventory "
"SET quantity_on_hand = quantity_on_hand - %s, "
" quantity_reserved = quantity_reserved - %s "
"WHERE inventory_id = %s",
quantity, quantity, inv.inventory_id
)
db.insert("inventory_transactions", {
"product_id": product_id,
"txn_type": "SHIP",
"quantity": -quantity,
"reference_id": order_id,
"reference_type": "ORDER"
})Receiving and Put-Away
def receive_purchase_order(self, po_id: int,
received_lines: list[dict]) -> Receipt:
po = db.get_po(po_id)
if po.status not in ("CONFIRMED", "PARTIAL"):
raise InvalidPOStatus()
for line in received_lines:
product_id = line["product_id"]
qty_received = line["quantity"]
location_id = line.get("location_id") or
self._suggest_putaway_location(product_id)
with db.transaction():
# Upsert inventory record for this product+location
existing = db.query_one(
"SELECT inventory_id, quantity_on_hand FROM inventory "
"WHERE product_id = %s AND location_id = %s FOR UPDATE",
product_id, location_id
)
if existing:
db.execute(
"UPDATE inventory SET quantity_on_hand = quantity_on_hand + %s "
"WHERE inventory_id = %s",
qty_received, existing.inventory_id
)
else:
db.insert("inventory", {
"product_id": product_id,
"location_id": location_id,
"quantity_on_hand": qty_received,
"quantity_reserved": 0
})
db.insert("inventory_transactions", {
"product_id": product_id,
"location_id": location_id,
"txn_type": "RECEIVE",
"quantity": qty_received,
"reference_id": po_id,
"reference_type": "PO"
})
self._update_po_status(po_id, received_lines)
self._check_replenishment_triggers(received_lines)Replenishment and Reorder Triggers
Trigger: after any stock-reducing transaction (SHIP, ADJUST), check if quantity_available falls below product.reorder_point. If yes: check if an open PO already exists for this product (avoid duplicate orders), then create a PurchaseOrder with quantity = reorder_quantity, expected_date = TODAY + lead_time_days. Auto-replenishment can be enabled per product. Replenishment background job (daily): scans all products WHERE quantity_available – reorder_point = 2 * average_daily_demand * lead_time_days (safety stock formula).
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you prevent overselling in a warehouse inventory system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use database row-level locking (SELECT FOR UPDATE) on the inventory record within a transaction. The lock prevents concurrent reservations from reading the same quantity_available simultaneously. Flow: (1) BEGIN TRANSACTION, (2) SELECT … FOR UPDATE (blocks other transactions), (3) check quantity_available >= requested, (4) UPDATE quantity_reserved += requested, (5) INSERT audit transaction record, (6) COMMIT. This serializes concurrent reservations for the same product, preventing oversell. Alternative: optimistic locking with a version column, retrying on conflict, which is more scalable but complex.”}},{“@type”:”Question”,”name”:”What is the difference between quantity_on_hand and quantity_available?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”quantity_on_hand is the physical count of units in the warehouse. quantity_reserved is the count allocated to pending orders not yet shipped. quantity_available = quantity_on_hand – quantity_reserved – this is what can actually be sold. When a customer places an order, quantity_reserved increases (quantity_available decreases) but quantity_on_hand stays the same. When the order ships, both quantity_on_hand and quantity_reserved decrease by the same amount. quantity_available stays constant through the ship operation. This ensures orders cannot be oversold even if physically held stock is allocated to another order.”}},{“@type”:”Question”,”name”:”How do you maintain an audit trail for inventory changes?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use an append-only InventoryTransaction table: product_id, location_id, txn_type (RECEIVE/SHIP/ADJUST/RESERVE/RELEASE/TRANSFER), quantity (positive=in, negative=out), reference_id (order or PO), reference_type, performed_by, created_at. Every inventory mutation inserts a record – never deletes or updates transaction records. The current inventory state can be reconstructed by summing all transactions for a product+location. This provides a complete audit trail for shrinkage investigation, discrepancy resolution, and regulatory compliance. Current state is stored in the Inventory table for fast reads; transactions are the source of truth.”}},{“@type”:”Question”,”name”:”How does automatic replenishment work in a warehouse inventory system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each product has reorder_point (trigger threshold) and reorder_quantity. After any stock-reducing transaction, check if quantity_available falls below reorder_point. If yes: verify no open PO already exists (avoid duplicate orders), then create a PurchaseOrder for reorder_quantity units with expected_date = today + lead_time_days. For batch replenishment: a nightly job scans all products below reorder point and creates POs grouped by supplier (one PO per supplier). Safety stock formula: reorder_quantity >= 2 * average_daily_demand * lead_time_days ensures coverage during the lead time even with demand variability.”}},{“@type”:”Question”,”name”:”How do you handle multi-location inventory in a warehouse?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Model inventory at the location level: Inventory table has (product_id, location_id) as a unique key. A product can have stock in multiple locations (bulk storage, pick face, overflow). Total available stock = SUM(quantity_on_hand – quantity_reserved) across all locations for the product. For order fulfillment, pick from the location with the highest quantity to minimize split picks. For receiving (put-away), suggest the optimal location based on product weight/dimensions, zone affinity (pick face for fast movers, bulk for slow movers), and available space. Location capacity constraints: enforce weight and volume limits on the Location record.”}}]}
Shopify system design rounds cover inventory management. Review warehouse inventory LLD patterns for Shopify interview: inventory and warehouse management design.
DoorDash system design interviews cover inventory and supply chain systems. See design patterns for DoorDash interview: inventory and supply chain system design.
Stripe system design interviews cover transactional reservation systems. Review atomic inventory patterns for Stripe interview: transactional inventory and reservation systems.