Four warehouses, 18,400 SKUs, and a salesperson who needs to answer "do we have it in stock?" right now. This is the most personal performance test in Netorigo Logistics, and we learned a lot in the last six months about why a multi-warehouse stock view is non-trivial.
Why the naive solution does not work
Our first version queried the stock_movements table on every lookup, summed the running balance per SKU per warehouse, added pending reservations from reservations, subtracted in-transit lines, and returned the result in 4 seconds. Four seconds during a live sales call is forever. The fix: a materialized view holding aggregated rows per SKU+warehouse, refreshed via an async worker triggered by stock_movements inserts (PostgreSQL pg_notify + worker). The stock view loads in 280ms today, regardless of warehouse count.
The conceptual model that matters
Every SKU has stock across four dimensions:
- at_warehouse: physically on the shelf, unreserved.
- in_transit: travelling between two warehouses (cross-stock or replenishment).
- reserved: held by an active cart, quote, or order.
- available: at_warehouse minus reserved.
These four columns appear in every drill-down. The warehouse manager looks at at_warehouse + in_transit ("how much do we have across the network"), the salesperson at available ("how much can I promise today"), procurement at SKUs below the available - safety_stock threshold ("what do we need to order").
Why we kept available deliberately optimistic
Our most controversial decision: the available field is NOT decremented on cart-add. It only drops when the pick-list is generated (after the warehouse allocator has run on a confirmed order). So two carts can hold the last unit in parallel, and only the one whose pick-list generates first wins. The reasoning: B2B cart abandonment is 67%. If every cart-add reserves immediately, stock artificially "sells out," and the next visitor sees a false out-of-stock. We would then need an idle-cart cleanup job running every 30 minutes, which is its own race-condition source.
The reservation lifecycle
States in the reservations table:
cart- in a visitor's cart, soft hold, 24h TTL.quote- on a quote document, 30-day TTL, harder hold.order- ordered, waiting for pick-list.picking- on an active pick-list, cannot be moved out of the picking warehouse.shipped- dispatched, no longer Logistics' problem.
Every state transition writes an audit row to reservation_events. There was a bug in March 2026 where the quote -> order transition did not re-index the reservation against the warehouse allocator, so the pick-list generated against the wrong warehouse (delivered via cross-stock with +18h SLA slip). The audit trail surfaced the issue the same evening.
Drill-down
The default stock view is aggregated (4 warehouses, one number per SKU). One click on a SKU opens the per-warehouse breakdown: how much is at each warehouse, how much is in transit, which warehouses hold the reservations. One click on the reservation count opens the reservation list: which cart/quote/order ID, and when it expires.
80% of managers stay on the aggregated view. The 20% specialised warehouse managers use drill-down, mostly when making cross-stock decisions: "warehouse 18 has 4 units, warehouse 33 has 12, ship 6 to warehouse 18 for tomorrow's pick wave." The cross-stock action can be triggered directly from drill-down, and the stock view reflects it within 280ms.
What we would do differently next time
The materialized-view approach works for 18,000 SKUs across 4 warehouses. Above 100,000 SKUs and 12 warehouses we would probably switch to event-sourced (Kafka + KSQL), with the stock view built from a streaming projection. We do not have a partner that large yet, so materialized view is good for the next 12 months.