Warehouse inventory

Why this design

Every inventory system starts with a quantity column and every one that survives contact with a real warehouse ends up with a ledger. The reason is not elegance, it is auditing: the number on the screen disagrees with the shelf at least once a day, and the only question that matters then is "which event made it wrong". A field that is overwritten cannot answer that. A table of movements can, for every unit, forever. Interviewers use this question to see whether you separate facts (something moved) from derived state (how many are here now), and whether you know where the derived state is allowed to be wrong.

What you will learn

• Why stock_movements is append-only and on-hand is the sum of its rows, and why a correction is a new movement the other way rather than an UPDATE.
• Why every movement has a from AND a to location, with virtual supplier, customer and loss locations, so that every item nets to zero across the whole system.
• How stock_balances keeps reads cheap as a projection written in the same transaction, and the one query that proves it has not drifted.
• Why a reservation is its own row with an expiry, and why available-to-promise is on_hand minus reserved, never a third stored number.
• Why lots and expiry dates turn "pick any unit" into "pick the one expiring first" (FEFO), and how a cycle count becomes an adjustment movement.
• How a hot SKU serialises on one balance row, and the bucketed balance that relieves it.

How to read this board

1. Top row: WHERE (warehouses, locations - including the virtual ones) and WHAT (items, lots).
2. Bottom left: THE LEDGER - stock_movements, the only table that records a fact, and cycle_counts, which feeds it.
3. Bottom right: DERIVED - stock_balances and reservations, both of which can be rebuilt or expired.
4. Press play for the walkthrough; it lights each table and the foreign key that joins it.
5. 1 · Requirements, 2 · Capacity, 3 · Storage, 4 · Availability and integrity, 5 · How it works, 6 · Deployment and migrations. Then Generate SQL and read the DDL against page 5.

Ask these before drawing anything

• One warehouse or many? Many here, each with its own locations. Transfers between warehouses are two movements through an "in transit" virtual location, because the truck is a place where stock is for two days.
• Are items lot- or serial-tracked? Lot-tracked where the item says so (food, pharma, anything with a recall). Serial tracking, one row per physical unit, is out of scope and would replace quantity with identity.
• What is the unit of measure, and can it be fractional? Integers of the base unit (each, gram, millimetre). A case of 12 is a packaging fact, converted at receipt, never stored as 0.0833 of something.
• Who reserves stock, and for how long? Sales orders, for 30 minutes at checkout and until pick once paid. A reservation that nobody releases must expire on its own.
• How often are counts done? Cycle counting, a slice of locations every night, rather than an annual wall-to-wall stocktake that closes the warehouse.
• Is negative stock allowed? Never on a real location. If the system says pick 3 and the shelf has 2, the pick shorts and a count is triggered - the database refuses to pretend.

What the schema must guarantee

• Every change in quantity is a stock_movements row: who, when, why, from where, to where, how many. Nothing else changes a quantity.
• Movements are never updated or deleted. A wrong movement is reversed by an equal and opposite one that references it.
• For every item, the sum of balances over all locations, virtual ones included, is zero.
• A real location never has a negative balance; the CHECK is on stock_balances and the movement insert fails if it would be violated.
• Available to promise is computed, never stored: on_hand minus active reservations.
• A replayed scanner event cannot double-move stock: idempotency_key is UNIQUE.

Non-functional

• Pick confirmation (one movement + one or two balance updates): p99 under 30 ms in the database.
• Available-to-promise for one SKU across a warehouse: p95 under 10 ms, because checkout calls it on every cart view.
• Zero lost movements on failover - a lost movement is stock that exists on the shelf and nowhere else.

Out of scope

• Purchase orders and supplier management (a receipt references one by id).
• Slotting and pick-path optimisation - which location an item SHOULD live in is a different problem from where it is.
• Costing (FIFO / weighted average valuation). It reads this ledger; it is a finance board.