Design inventory management across warehouses

A single 'quantity' number is not enough. Track buckets per (SKU, warehouse): on_hand (physically there), reserved (held for unpaid checkouts), allocated (paid orders waiting to be picked), and a safety_buffer. Sellable available = on_hand - reserved - allocated - safety_buffer. Each business event moves quantity between buckets: reserve (available -> reserved), commit (reserved -> allocated), ship (allocated and on_hand both down), return-to-stock (on_hand up after QC), damage (on_hand down). Store every change as an append-only movement row with a unique movement_id, and update the balance row in the same database transaction. This is a ledger pattern like double-entry accounting: the balance is a fast cached sum, the movements are the truth, and you can rebuild or audit any balance at any point in time. The unique movement_id makes every warehouse scan and every API retry idempotent: a duplicate insert fails, and the balance is not changed twice. Interviewers like this because it answers 'how do you debug why stock is wrong?'

Two checkouts for the last unit arrive at the same time. A read-then-write ('read available = 1, then write 0') races and both succeed. Fixes in order of preference: a single conditional UPDATE that checks and decrements atomically (the database row lock is held only for microseconds); optimistic concurrency with a version column and retry; or SELECT ... FOR UPDATE inside a short transaction. The conditional update is simplest and scales to a few thousand updates/s per row. For multi-line orders, lock rows in a consistent order (sorted by sku_id, warehouse_id) to avoid deadlocks, or reserve lines independently and release the successful ones if any line fails, which is a mini-saga. Sharding by sku_id keeps a SKU's warehouses together. For extremely hot SKUs during sales (a single row at 50k attempts/s), move the SKU's sellable count to Redis with a Lua check-and-decrement fronting the DB, or split the stock into sub-rows (stripes) that are tried in turn, as in the flash-sale design.

Browse traffic (~100k reads/s) is 10-100x checkout traffic and does not need perfect accuracy; checkout does. So run two paths. The availability view in Redis is a projection updated from StockChanged events, usually within a second or two. The availability service combines it with serviceability (which warehouses deliver to this pincode and in how many days, considering today's cut-off time) to answer 'in stock, delivery by Thursday'. Store coarse buckets rather than exact counts where possible; 'only 3 left' needs a number, but most cards only need yes/no, and buckets mean fewer cache writes. Checkout never trusts the view; it reserves against the database. The small window where the view says yes but the reservation fails is handled with a clear 'just sold out' message and alternative sellers. The safety buffer per SKU-warehouse covers the opposite error, when physical stock is lower than the system thinks because of damage or mis-scans.

Software counts drift from reality: items are damaged, misplaced, stolen, or scanned twice. Warehouses run cycle counts (counting a subset of bins every day) and full counts periodically. A count produces an ADJUST movement with a reason, never a direct overwrite of the balance, so the history explains the change. Large or frequent adjustments for a SKU are flagged for investigation. Separately, a reconciliation job checks internal consistency: every balance must equal the sum of its movements; every HELD reservation past its expiry must be released; every allocated quantity must map to a live order. Expired reservations are released by a sweeper using the (status, expires_at) index, in small batches with a conditional status update so a commit racing with expiry has exactly one winner. Mention 'phantom inventory' (system says 1, shelf is empty) as the main cause of cancellations after order, and safety buffers per SKU velocity as the fix.

Balance-only is simpler and faster to write but cannot explain discrepancies or support idempotent replays. The ledger costs extra writes and storage (~10 GB/day) but gives audit, debugging and rebuilds. For inventory, use both.

Per-warehouse databases keep warehouses working during network issues but make cross-warehouse allocation a distributed problem. A central service sharded by sku_id, with the WMS buffering scans locally during outages, is simpler for allocation.

Short TTLs (5 min) free stock quickly but may expire during slow UPI payments; long TTLs (30 min) lock stock that never sells. ~15 minutes with an extension when payment is in progress is a common middle.

Exact counts cause a cache write for every unit sold; buckets (0, 1-5, 5+) cut writes drastically. Use exact only where the UI shows 'only N left'.

Row lock contention at thousands of updates/s on one (SKU, warehouse). Use Redis-fronted counters or stock striping, and pre-assign sale stock to a separate pool.

~30M rows/day. Partition by month, keep 90 days hot, archive older partitions to the data lake, and periodically snapshot balances so rebuilds do not replay years of history.

A page with 40 products needs 40 lookups. Batch with MGET and keep in-stock flags in the search index so listings rarely call availability at all.

Reservations for SKUs on that shard fail fast; the order service shows 'try again shortly' instead of overselling. Browse still reads the Redis view. Failover to the synchronous replica takes ~30 s.

The Redis view becomes stale, so the site may show stock that is gone. Checkout still checks the DB, so no oversell; alert on projector lag and widen safety buffers if lag persists.

The WMS keeps scanning offline and queues movements with their movement_ids; on reconnect it replays them and idempotency prevents double counting. Meanwhile, allocation stops routing new orders to that warehouse.