Inventory Management: Knowing What You Can Still Promise

"We have ten notebooks. How many can we sell?"

Sana runs KaagazKart, an online stationery shop. In June, every school in the city sends its book list at once. Her warehouse manager shouts across the floor: "We have ten blue A4 notebooks." Sana asks the question that matters: "How many can we still promise to a customer?" (Sana, KaagazKart and every number here are made up for teaching.)

The answer is five:

• 10 notebooks are on the shelf,
• 3 of them are already packed for an order that hasn't left yet,
• and 2 are water-damaged.

Counting objects on a shelf is different from counting objects you can promise. Good inventory software records why quantities change, and it stops two orders claiming the same stock.

Back-to-school season tests every part of her system:

This example uses whole units and immediate fulfilment from one warehouse at first. Backorders, fractional quantities, manufacturing and multi-warehouse optimisation are later extensions.

In 30 seconds, as an architect would frame it: "I track stock per SKU per warehouse as on-hand, reserved and unavailable, so available is the difference. A reservation is one conditional update that only succeeds if enough is available, plus a reservation row and an outbox event in the same transaction. Shipment consumes the reservation and on-hand together. Every change also writes a movement record, so the balance can be explained and reconciled. Caches and read views help browsing, but only the atomic update decides."

1. "On hand says 10, but we can only sell 5": the quantity model

A stock keeping unit (SKU) identifies a sellable variant, such as a blue A4 notebook. Inventory belongs to a SKU at a location. A product description alone is not a stock balance.

available = on_hand - reserved - unavailable

Here, unavailable means units physically present but excluded, like the damaged ones. Incoming goods and items in transit are tracked separately, and they aren't available until the receiving policy allows it.

flowchart TD
    subgraph OH["On hand: 10 notebooks physically in the building"]
        R@{ icon: "algomi:svc-cube", label: "Reserved: 3 packed for an order", pos: "b", h: 52 }
        U@{ icon: "algomi:svc-cube", label: "Unavailable: 2 damaged", pos: "b", h: 52 }
        A@{ icon: "algomi:svc-cube", label: "Available to promise: 10 - 3 - 2 = 5", pos: "b", h: 52 }
    end

Reading the diagram. The outer box is everything physically in the building, which is what the warehouse manager counted. Inside it are three buckets that don't overlap. Only the Available bucket can be sold. A customer's order never touches "on hand" directly. It moves units from available into reserved.

Reading the table. Each operation moves numbers between buckets, and shipping is the only operation that reduces on-hand for a sale. It reduces reserved by the same amount. That's why reserving and shipping must be separate steps. Subtracting on-hand at order time and at shipping time would count the same notebook twice.

The invariant: on_hand >= reserved + unavailable >= 0, with every component non-negative. Damaging a notebook that is already reserved means reallocating or resolving that reservation, not breaking the invariant.

2. "What happened to SKU 4471 last Tuesday?": balances and history

An auditor asks why the calculator count dropped by 12 one evening. A single number can't answer that.

• Make (warehouse_id, sku_id) unique.
• Deduplicate with unique external references. A delivery note number on a receipt, or a courier's shipment ID, stops a repeated message from being applied twice.
• Add batch or lot IDs and expiry dates for stock that must be tracked by lot, such as glue sticks and ink.

erDiagram
    SKU ||--o{ STOCK_BALANCE : held_at
    WAREHOUSE ||--o{ STOCK_BALANCE : contains
    ORDER_LINE ||--o{ STOCK_RESERVATION : allocated_by
    STOCK_BALANCE ||--o{ STOCK_RESERVATION : supports
    STOCK_BALANCE ||--o{ STOCK_MOVEMENT : records