Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 40 ยท Inventory

Inventory Tracking

Model e-commerce inventory the right way: on hand, committed, available and incoming stock, locations, a movements ledger, backorders and preorders.

Every product page makes a promise: "you can buy this". Inventory is the system that decides whether the promise is true. Get it wrong in one direction and you sell things you do not have, then apologize by email. Get it wrong in the other and you hide products that are sitting on a shelf.

"How many do we have?" turns out to be several different questions. In this lesson you will separate on hand, committed, available and incoming stock, track stock per location, and store every change as a row in a movements ledger instead of overwriting a single number. You will also see how backorders and preorders bend the rules on purpose. The next lesson, Reservations and Overselling, deals with what happens when many shoppers compete for the same units.

The four numbers

Think of one SKU (a single sellable variant, see Variants and Options) in one warehouse.

Number Meaning
On hand Units physically in the building, including ones already promised to someone
Committed Units promised to open orders but not yet shipped
Available What the storefront may still sell: on hand - committed
Incoming Units on purchase orders or transfers that have not arrived yet

The storefront should read available, never on hand. Imagine 5 tents on the shelf and 4 of them already tied to unshipped orders. On hand is 5, but only 1 is available to sell.

Incoming stock is usually not sellable. It becomes useful for delivery estimates and for backorders, which you will meet below. Some shops also track damaged, quarantined or reserved for wholesale quantities. These are just more buckets that are subtracted from available.

Different platforms use different words (Shopify says "unavailable", ERPs say "allocated"), but the idea is always the same: one number says what exists, one says what is promised, and the difference is what you may sell.

Locations and warehouses

A shop with one warehouse has one stock record per SKU. Once you add a second warehouse, a retail store or a drop-shipping supplier, the unit of tracking becomes the pair (SKU, location).

That pair is the row you update. The number a shopper sees is usually the sum across the locations that can ship to them. Choosing which location fulfills an order (nearest, cheapest, or the only one with enough stock) is an allocation decision made at order time. It is separate from tracking.

flowchart LR
    S["Storefront<br/>shows available = sum"] --> A["Berlin<br/>on hand 34, committed 2"]
    S --> B["Paris<br/>on hand 15, committed 0"]
    PO["Purchase order<br/>incoming 50"] -.-> A
    O["Order"] -->|"allocate"| A

The storefront reads a sum; orders are allocated to one specific location.

Keep one more rule in mind: an order line normally ships from one location. If the total available is 10 but 6 are in Berlin and 4 in Paris, a customer who wants 8 either gets a split shipment or a "not available" message, depending on your policy.

The movements ledger

The naive design is a single column, stock = 17, which the code overwrites. It works until the first question you cannot answer: "Why is it 17? It was 20 yesterday."

A better design treats stock like a bank account. You never edit the balance. You append movements, and the balance is computed from them (often cached in a summary table for speed). Each movement records what changed, where, why, and who or what caused it.

Typical movement kinds:

  • received: a purchase order or transfer arrived (adds to on hand)
  • committed / released: an order promises units, or the promise is cancelled
  • shipped: units leave the building (reduces on hand and committed)
  • adjusted: a correction after a cycle count, damage or theft (signed, with a reason)
  • returned: a customer return was inspected and restocked

A ledger gives you an audit trail, makes bugs debuggable (replay the rows and see where it went wrong) and lets you rebuild the summary at any time. The cost is a little more storage, which is almost always worth it.

Python A stock ledger with computed levels

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Run it and check the Berlin numbers by hand: 40 received, 4 shipped and 2 written off leave 34 on hand. Six were committed and four of those shipped, so 2 are still committed and 32 are available. Notice that "shipped" touches both counters. Forgetting that is a classic bug: the units leave the building but still look promised, so available drifts downward forever.

Adjustments need reasons

Real counts never match the system perfectly. Shrinkage, mislabeled bins and miscounts all happen. Always record an adjustment as a movement with a reason code (damaged, lost, recount, found), never as a silent overwrite. Finance will want those reasons, and so will you when a pattern shows up.

Sellable, backorder and preorder

Zero available does not have to mean "sold out". Three policies exist, and each one is a per-product (or per-variant) setting:

  • Deny: when available is zero, the product cannot be bought. This is the safe default.
  • Backorder: the shop keeps selling past zero, because stock is expected. You promise a later ship date, ideally based on incoming purchase orders.
  • Preorder: the product has not been released yet. Customers pay (or authorize) now and the item ships at launch.

The difference matters for messaging and for payment timing. Backorders are usually for things you normally stock; preorders are for launches. Both should show an honest date on the product page and in the confirmation email, and both let the customer cancel if the date slips. See Authorization, Capture, Refunds and Chargebacks for why charging at order time versus ship time is a real decision.

Python Deny, backorder or preorder

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The lamp has 3 on hand and 1 committed, so 2 are available: asking for 2 works and asking for 3 does not. The stove has nothing on the shelf but 50 arriving on 20 March, so it can be backordered with a real date. The jacket is a preorder only until its window closes. After that, with nothing in stock, it falls back to "sold out".

Where the numbers live in the database

A common, robust layout has two tables:

SQL
-- append-only history
CREATE TABLE stock_movements (
  id           BIGINT PRIMARY KEY,
  sku          VARCHAR(64) NOT NULL,
  location_id  INT NOT NULL,
  kind         VARCHAR(20) NOT NULL,
  qty          INT NOT NULL,
  reason       VARCHAR(100),
  reference    VARCHAR(100),   -- order id, PO number
  created_at   TIMESTAMP NOT NULL
);

-- fast, derived read model
CREATE TABLE stock_levels (
  sku          VARCHAR(64) NOT NULL,
  location_id  INT NOT NULL,
  on_hand      INT NOT NULL,
  committed    INT NOT NULL,
  PRIMARY KEY (sku, location_id)
);

Writers insert a movement and update the level row in the same transaction, so the two never disagree. Readers (the storefront) only touch stock_levels. A nightly job can recompute levels from the movements and alert you if they differ. That check catches bugs long before a customer does.

Do not trust a stock number you cannot explain. If your system cannot answer "which movements produced this?", the first stock bug will take days to find.

Common mistakes

  • Selling on hand instead of available. Shows items that are already promised.
  • One global number for multiple warehouses. You cannot tell which building to ship from.
  • Overwriting instead of appending. No history, no audit, no replay.
  • Letting stock go negative silently. Negative stock is sometimes allowed on purpose (backorders), but it should be a decision, not an accident.
  • Forgetting returns. A returned item is not sellable until it has been inspected; restock it with a movement when it passes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A SKU has 12 units on hand and 5 committed to unshipped orders. How many can the storefront still sell?

    Available is on hand minus committed, so 12 - 5 = 7. Selling on hand would promise units that are already spoken for.
  2. 2. What should happen to the counters when a committed unit is shipped?

    The unit leaves the building (on hand drops) and the promise is fulfilled (committed drops). The difference, available, stays the same.
  3. 3. Why record stock changes as ledger rows instead of overwriting one number?

    Appending movements records who changed what and why, so you can replay history and explain any number. Reads are usually served from a derived summary table.
  4. 4. Which policy fits a new product that has not been released yet and will ship at launch?

    A preorder sells an unreleased item against a launch date. A backorder is for items you normally stock that are temporarily out.

Summary

  • Track on hand, committed, available (on hand - committed) and incoming separately; sell against available.
  • The unit of tracking is the pair (SKU, location); the storefront shows a sum over locations that can ship.
  • Store stock as an append-only movements ledger with reasons, and keep a derived levels table updated in the same transaction.
  • Shipping reduces both on hand and committed; adjustments always carry a reason.
  • Backorders and preorders are explicit policies with honest dates, not accidents of negative stock.

Next: Reservations and Overselling.