Available-to-Promise: Why On-Hand Is the Wrong Number to Show a Customer

Read this framing first. What follows is a design exercise. KMJ Tire does not run the system described here, has not built it, has not deployed it, and has no incident history behind it. There is no rollout, no migration, no adoption curve, and no measured throughput to report. Every quantity below — casings on a rack, a TTL in minutes, a drift threshold, a row of sample output — is illustrative and was chosen to make an argument legible, not lifted from a running service. What is real is the operating environment, and that part earns its keep: a small Calgary tire and oil-change business that sells physical goods it also consumes during scheduled work, keeps a rolling inventory on a mobile van, and absorbs a demand spike every October that would embarrass plenty of production systems. The code here is meant to be argued with rather than pasted.

The Number on the Screen Was Never a Fact

Somebody at the counter types a size into a search box and the screen answers 6.

That six is doing an enormous amount of unearned work. It is being read as a promise — we can sell you four of these right now — when the underlying row was never designed to make promises. Four of those six are spoken for by a Thursday changeover that a customer confirmed on Tuesday. One is physically on the rack but has a sidewall gouge that a technician noticed and mentioned to nobody. One is genuinely, unambiguously sellable. The honest answer to how many can I promise you is one, and the system said six with total confidence.

This is not an exotic failure. It is the default behaviour of the most common inventory schema in existence:

create table product (
  sku      text primary key,
  name     text not null,
  quantity integer not null default 0
);

That column is a lie by construction, and not because anyone was careless. It is a lie because a single integer cannot distinguish between stock that exists, stock that exists but belongs to someone else already, and stock that will exist on Thursday morning when the distributor's truck arrives. Those are three different facts with three different failure modes, and a business that consumes its own merchandise during service work touches all three every single day.

The specific pain of a tire and oil-change operation is that merchandise and labour are entangled. A retail customer buying a set of four takes stock off the rack. A seasonal changeover consumes valve stems and wheel weights that were also sitting in inventory. An oil change consumes a filter and several litres from a drum. None of those consumption events look like a checkout. All of them decrement something real.

Four Quantities That One Integer Flattens

Before touching any schema, it is worth writing down what you actually need to know. There are four distinct numbers, and most inventory bugs are two of them being confused for each other.

On-hand is physical reality: units under this roof right now, regardless of who they are spoken for. It changes when something is unloaded off a truck, handed to a customer, dropped and ruined, or discovered missing during a count. It never changes because a customer expressed interest.

Committed — allocated, reserved, spoken-for, whatever your team calls it — is the portion of on-hand that already has someone's name attached. It is still physically present. Pointing at it and saying "we have six" is technically true and commercially useless.

Inbound is stock that a supplier says will arrive. It is not yours, it is not here, and it is the least trustworthy of the four because it is somebody else's assertion about the future.

Available-to-promise is the only number a customer should ever be shown, and it is the only one of the four that should never be stored.

Quantity	Answers	Moves when	What breaks if you conflate it
On-hand	What is physically here?	Receipt, hand-off, damage, count correction	You subtract holds from physical stock and the shelf count stops matching the database forever
Committed	How much is already spoken for?	Hold placed, hold released, hold expired, sale finalized	Two customers get promised the same set of four
Inbound	What is coming, and when?	Purchase order issued, shipment confirmed, receipt posted	You promise Thursday delivery against a truck that has not left
Available-to-promise	What can I safely offer?	Derived — it never moves on its own	You store it, it drifts, and nobody can explain why

The relationship is not complicated:

atp(sku, location, as_of) =
      on_hand(sku, location)
    - committed(sku, location, as_of)
    + inbound(sku, location, arriving_before => as_of)

Everything difficult about inventory is downstream of taking that formula seriously — specifically, of refusing to cache its result in a column that anything is allowed to write.

Derived Means Derived, Even When It Hurts

The temptation to store available-to-promise is enormous, and it comes from a good place: reads are frequent, the fold is expensive, and a number in a column is fast. So somebody adds available_qty and writes to it from the reservation path. Then from the receiving path. Then from the point-of-sale path. Then from the nightly correction job somebody wrote in a hurry during October.

Now you have five writers and no owner. When available_qty reads -3, there is no procedure for determining which writer was wrong, because the column holds a result with no derivation attached. You cannot replay it. You cannot diff it against anything. The only remedy anybody ever finds is a repair script that overwrites the number with a fresh guess, which works until the next time.

There is a cached form of this number later in the design, and I will defend it. The distinction that makes it safe is that a cache is reconstructible and checkable: there is an authoritative computation, the cache is a memo of it, and a background comparison can prove the two agree. A stored figure with no derivation behind it is not a cache. It is a rumour with a primary key.

Movements, Not Mutations

The alternative to mutating a quantity is recording why it changed and letting the quantity fall out.

create type stock_stream as enum ('physical', 'claim');

create type stock_reason as enum (
  'receipt',
  'sale',
  'hold_place',
  'hold_expire',
  'hold_release',
  'shrink',
  'damage_out',
  'count_adjust',
  'transfer_out',
  'transfer_in'
);

create table stock_movement (
  movement_id  bigserial     primary key,
  sku          text          not null,
  location_id  text          not null,
  stream       stock_stream  not null,
  qty_delta    integer       not null,
  reason       stock_reason  not null,
  ref_type     text,
  ref_id       text,
  actor        text          not null,
  occurred_at  timestamptz   not null default now(),
  memo         text,
  constraint nonzero_delta check (qty_delta <> 0)
);

create index movement_by_slot
    on stock_movement (sku, location_id, movement_id);

Two design choices in there deserve defending.

The first is stream. Physical movements and claims are different kinds of fact, and mixing them into one signed column is how you end up subtracting a customer's intention from a shelf count. A hold_place row does not move rubber. It stakes a claim against rubber that is already sitting there. Keeping them in one table gives you a single ordered narrative per slot; keeping them in separate streams means the physical fold and the claim fold never contaminate each other. You get one story and two arithmetics.

The second is that there is no update path and no delete path. Rows are inserted and never touched again. If you take one idea from this piece, take that one — the audit value of an append-only log collapses the instant somebody is allowed to edit history, because from then on every reader has to wonder.

Enforce it at the database rather than in a code review convention:

revoke update, delete on stock_movement from application_role;
grant  insert, select on stock_movement to application_role;

A convention is not a control. A revoked privilege is.

Reason Codes Are a Vocabulary, Not an Enum

The reason column looks like bookkeeping trivia. It is actually the most valuable field in the table, because it is the only place where the why survives.

Consider four movements that all read -1 on the physical stream. A sale means money arrived. A damage_out means a technician found a bead tear during mounting and pulled the unit before it went on a vehicle. A shrink means the count came up short and nobody knows where it went. A transfer_out means it left for the van and is still yours. Collapse those four into a bare decrement and you have thrown away the ability to answer questions that matter more than the running total: what is our damage rate on a particular line, is a specific location losing stock, are we writing off inventory that is actually sitting in the van.

A few conventions I would insist on:

• Reason codes are closed. New ones require a migration, which forces a conversation. An open text field becomes "adjustment", "adj", "ADJUSTMENT", and "fix" within a year.
• ref_type plus ref_id are the tether back to the originating document — an invoice number, a reservation identifier, a count sheet, a purchase order line. A movement with no tether is a movement nobody can defend later.
• actor is a human or a named service, never system. When an automated sweeper releases forty holds during the October rush, you want the sweeper's name on all forty rows so you can find them in one query.
• occurred_at is when the physical event happened; movement_id is the order in which the database learned about it. Late-arriving paperwork from the van makes those two disagree, and the disagreement is information rather than an error.

Folding a Ledger Into a Number

With movements in place, every quantity in the design is an aggregate.

-- Current position for one slot, split by stream.
select
    coalesce(sum(qty_delta) filter (where stream = 'physical'), 0)  as on_hand,
    coalesce(-sum(qty_delta) filter (where stream = 'claim'), 0)    as committed
from stock_movement
where sku = $1
  and location_id = $2;

The sign convention is worth stating once and then never varying: on the claim stream, staking a claim is negative and giving it back is positive, so committed is the negation of that stream's sum. A hold for four writes -4. Its expiry writes +4. The stream sums to zero when nothing is outstanding, which is a property you can assert in a test.

Available-to-promise then takes a horizon, because the question is always available for when:

select
    p.on_hand,
    p.committed,
    coalesce(i.arriving, 0)                              as arriving,
    p.on_hand - p.committed + coalesce(i.arriving, 0)    as atp
from (
    select
        coalesce(sum(qty_delta) filter (where stream = 'physical'), 0) as on_hand,
        coalesce(-sum(qty_delta) filter (where stream = 'claim'), 0)   as committed
    from stock_movement
    where sku = $1 and location_id = $2
) p
left join lateral (
    select sum(qty_outstanding) as arriving
    from inbound_line
    where sku = $1
      and destination_id = $2
      and status = 'confirmed'
      and expected_at <= $3
) i on true;

Note what inbound is not: it is not a row in the movement ledger. A purchase order is a promise from a third party, not something that moved. It gets its own table, its own confidence semantics, and — as discussed further down — a healthy amount of scepticism. It joins the ledger only at the moment a receipt is posted, which is a real movement with a real actor.

Holds That Expire on Their Own

A quote or a pending service visit needs to soft-lock stock. Somebody rings about a set of four for Thursday, the counter confirms availability, and thirty seconds later a second request arrives for the same size. Without a hold, both get told yes.

With a hold that never expires, you get the opposite disease: quotes that nobody accepts silently sterilize inventory, and in the middle of the October crunch you have forty sets of winter tires that the system will not sell because of conversations that ended weeks ago.

So holds carry a TTL and a lifecycle.

create type reservation_state as enum ('held', 'consumed', 'released', 'expired');

create table reservation (
  reservation_id   uuid              primary key default gen_random_uuid(),
  idempotency_key  text              not null unique,
  location_id      text              not null,
  sku              text              not null,
  quantity         integer           not null check (quantity > 0),
  state            reservation_state not null default 'held',
  held_for         text              not null,
  work_order_ref   text,
  needed_at        timestamptz,
  created_at       timestamptz       not null default now(),
  expires_at       timestamptz       not null,
  settled_at       timestamptz,
  constraint expiry_after_creation check (expires_at > created_at),
  constraint settled_iff_terminal check (
    (state = 'held' and settled_at is null)
    or (state <> 'held' and settled_at is not null)
  )
);

create index reservation_sweep
    on reservation (expires_at)
    where state = 'held';

The partial index is deliberate. Sweeping only ever asks about outstanding holds, and in a mature dataset those are a vanishing fraction of the rows.

TTL length is a product decision that engineers should refuse to make alone. A verbal quote over the counter might deserve fifteen minutes. A confirmed changeover slot three days out deserves a hold that lives until the work is done. A speculative online enquiry deserves less than either. Encoding one global TTL constant is the same mistake as one global cache expiry: it is not a policy, it is an absence of one.

Sweeper Versus Lazy Expiry, and Why I Run Both

There are two ways to make an expired hold stop counting, and the internet will tell you to pick one. I would run both, for reasons that are about failure modes rather than elegance.

Lazy expiry at read treats expires_at as part of the query predicate. Nothing needs to run on a schedule; an expired hold simply stops being counted the next time anybody looks.

select coalesce(sum(quantity), 0) as committed
from reservation
where sku = $1
  and location_id = $2
  and state = 'held'
  and expires_at > now();

This is correct and it never falls behind. Its costs are real, though. Every read path must remember the predicate, and the one that forgets is a bug you find in October. The ledger and the reservation table now disagree — the ledger still shows a -4 claim with no compensating row — so your audit trail no longer explains the current position. And nothing observable happens at expiry, so you cannot alert on a backlog you are not producing.

A sweeper does the opposite. It runs on a schedule, writes real hold_expire movements, and transitions the reservation row. The trail stays complete and the position is explained entirely by movements. The failure mode is that the sweeper stops, and stock silently stays locked while the counter wonders why the system will not sell anything.

with due as (
    select reservation_id, sku, location_id, quantity
    from reservation
    where state = 'held'
      and expires_at <= now()
    order by expires_at
    limit 200
    for update skip locked
),
expired as (
    update reservation r
       set state = 'expired',
           settled_at = now()
      from due d
     where r.reservation_id = d.reservation_id
    returning r.reservation_id, r.sku, r.location_id, r.quantity
)
insert into stock_movement
    (sku, location_id, stream, qty_delta, reason, ref_type, ref_id, actor)
select e.sku, e.location_id, 'claim', e.quantity, 'hold_expire',
       'reservation', e.reservation_id::text, 'sweeper/expiry'
from expired e;

for update skip locked with a bounded limit is what makes this safe to run in several copies at once, and what keeps a stuck batch from blocking every other batch. Each pass is small, idempotent in effect, and interruptible.

Running both means the sweeper produces the clean narrative and the read predicate is a correctness backstop for the window when the sweeper is behind. Belt and braces, and the braces cost one and in a query.

The Race That Sells the Same Set Twice

Here is the code that every inventory system contains at least once, usually written on a quiet afternoon by someone reasonable.

def reserve_naive(conn, sku, location_id, qty):
    available = conn.scalar(
        "select available from stock_slot where sku=%s and location_id=%s",
        (sku, location_id),
    )
    if available < qty:
        raise OutOfStock(sku)
    conn.execute(
        "update stock_slot set available = available - %s "
        "where sku=%s and location_id=%s",
        (qty, sku, location_id),
    )

It is correct in every test you will write for it, because your tests are sequential. It is wrong the moment two requests overlap, and the window is not narrow — it spans a network round trip, application logic, possibly a template render, and the second query.

Two requests, four units on the rack, four units wanted each. Both read 4. Both pass the check. Both subtract. The column now says -4 and two customers have been promised the same rubber. The one who arrives second gets a phone conversation nobody enjoys.

The seasonal peak is exactly when this surfaces. Through July the arrival rate is low enough that overlapping requests for the same size are rare, and the bug hides for years. Come October, everyone in the city wants the same handful of popular sizes in the same two weeks, and a defect with a probability proportional to concurrency squared stops being theoretical. Load is not the thing that breaks it. Correlated load is — everybody reaching for the same slot at once.

Worth naming precisely: the fault is a read-modify-write cycle whose read is not protected against a concurrent write between the two steps. Every real fix closes that gap somewhere.

Four Fixes, and What Each One Costs

Take a row lock and hold it. select ... for update on the slot row, then decide, then write, then commit. Straightforward, easy to reason about, and it serializes all traffic for one SKU at one location. Under correlated October load on a popular size that is a queue, and if your transaction does anything slow while holding the lock — an external stock enquiry, say — you have built a bottleneck with a network dependency inside it.

Push the predicate into the write. One statement that only applies when the condition still holds, plus an affected-row check. My default, and the subject of the next section.

Serializable isolation plus retry. Let the database detect the conflict and abort one transaction, then retry. Clean, and it composes across multi-row invariants that a single conditional update cannot express. The costs: every transaction site needs a retry wrapper, retries must be genuinely idempotent, and under heavy contention on one slot the abort rate can exceed the throughput you were trying to protect. Serializable is a correctness tool, not a contention tool.

Single writer per SKU. Route all mutations for a slot through one consumer — a partitioned queue, an actor, a lock service. Contention disappears by construction and ordering is free. You have also introduced a queue with its own liveness, backlog, and rebalancing concerns, and you have made every write asynchronous, which changes what the reservation endpoint can honestly return. For a business with a few thousand slots this is a large amount of machinery to avoid a where clause.

There is no universally right answer, but there is a right first answer, and for an operation of this size it is the conditional update.

Conditional Decrement, and Checking What Came Back

The cached position lives in a slot row, which doubles as the lock anchor:

create table stock_slot (
  sku              text        not null references product (sku),
  location_id      text        not null,
  on_hand          integer     not null default 0,
  committed        integer     not null default 0,
  last_movement_id bigint      not null default 0,
  refreshed_at     timestamptz not null default now(),
  primary key (sku, location_id),
  constraint committed_within_hand check (committed between 0 and on_hand)
);

That check constraint is not decoration. It converts a whole class of logic error into a failed transaction rather than a wrong answer, and a constraint violation in the logs is infinitely easier to diagnose than a negative number discovered three weeks later.

The reservation write is then a single statement:

update stock_slot
   set committed   = committed + $3,
       refreshed_at = now()
 where sku         = $1
   and location_id = $2
   and on_hand - committed >= $3;

The reason this is safe under plain read-committed isolation is worth understanding rather than memorizing. When two transactions target the same row, the second one blocks on the first's row lock. On release, PostgreSQL does not simply proceed — it re-evaluates the where clause against the updated version of the row. If the first transaction consumed the last available unit, the predicate now fails and the second statement affects zero rows. The comparison and the write are one atomic step, because the engine is doing the comparison after acquiring the lock rather than before.

Which means everything depends on inspecting the affected-row count:

result = conn.execute(RESERVE_SQL, (sku, location_id, qty))
if result.rowcount == 0:
    raise InsufficientAvailability(sku, location_id, qty)

Zero rows here is ambiguous — it means either "not enough available" or "no such slot" — and collapsing those two into one error message is a debugging tax you will pay repeatedly. returning on_hand - committed as remaining distinguishes them: a returned row proves the slot exists.

The ledger insert and the slot update go in the same transaction. The ledger is the record; the slot is the memo. They must not be able to disagree by more than a crash.

Retrying Without Reserving Twice

Networks time out. Mobile data in a parkade drops halfway through a request. A user with a spinning cursor presses the button again. Any of these produces a second request that is semantically the same intent as the first, and a system that treats it as new intent will hold eight units for a customer who wanted four.

The fix is a client-generated key that identifies the intent rather than the transmission.

import uuid
from dataclasses import dataclass
from datetime import timedelta

import psycopg
from psycopg import errors


@dataclass(frozen=True)
class HoldRequest:
    idempotency_key: str
    sku: str
    location_id: str
    quantity: int
    ttl: timedelta
    held_for: str
    work_order_ref: str | None = None


class InsufficientAvailability(Exception):
    pass


class ConflictingReuse(Exception):
    pass


class DuplicateIntent(Exception):
    """Raised when an idempotency key has already been recorded."""


def place_hold(conn: psycopg.Connection, req: HoldRequest) -> uuid.UUID:
    """Reserve stock exactly once per idempotency key.

    Safe to retry with the same key. A repeat returns the original
    reservation without moving any quantity a second time.
    """
    with conn.transaction():
        moved = conn.execute(
            """
            update stock_slot
               set committed = committed + %(qty)s,
                   refreshed_at = now()
             where sku = %(sku)s
               and location_id = %(loc)s
               and on_hand - committed >= %(qty)s
            returning on_hand - committed as remaining
            """,
            {"qty": req.quantity, "sku": req.sku, "loc": req.location_id},
        ).fetchone()

        if moved is None:
            raise InsufficientAvailability(req.sku)

        try:
            row = conn.execute(
                """
                insert into reservation
                    (idempotency_key, sku, location_id, quantity,
                     held_for, work_order_ref, expires_at)
                values
                    (%(key)s, %(sku)s, %(loc)s, %(qty)s,
                     %(who)s, %(ref)s, now() + %(ttl)s)
                returning reservation_id
                """,
                {
                    "key": req.idempotency_key,
                    "sku": req.sku,
                    "loc": req.location_id,
                    "qty": req.quantity,
                    "who": req.held_for,
                    "ref": req.work_order_ref,
                    "ttl": req.ttl,
                },
            ).fetchone()
        except errors.UniqueViolation:
            raise DuplicateIntent(req.idempotency_key) from None

        conn.execute(
            """
            insert into stock_movement
                (actor, sku, location_id, stream, reason,
                 ref_type, ref_id, qty_delta)
            values
                (%(who)s, %(sku)s, %(loc)s, 'claim', 'hold_place',
                 'reservation', %(rid)s, %(neg)s)
            """,
            {
                "sku": req.sku,
                "loc": req.location_id,
                "neg": -req.quantity,
                "rid": str(row[0]),
                "who": req.held_for,
            },
        )
        return row[0]

The unique violation is caught, but notice that the transaction is going to roll back — which is exactly right, because the slot increment that already happened in this transaction must not survive. The retry path then looks up the prior reservation outside the transaction:

def place_hold_idempotent(conn, req: HoldRequest) -> uuid.UUID:
    try:
        return place_hold(conn, req)
    except DuplicateIntent:
        prior = conn.execute(
            """
            select reservation_id, sku, location_id, quantity, state
            from reservation
            where idempotency_key = %s
            """,
            (req.idempotency_key,),
        ).fetchone()
        rid, sku, loc, qty, state = prior
        if (sku, loc, qty) != (req.sku, req.location_id, req.quantity):
            raise ConflictingReuse(req.idempotency_key)
        return rid

Three details that separate working idempotency from the appearance of it. The key is generated by the client, before the first attempt, and reused on every retry — a server-generated key defeats the purpose entirely. The stored request parameters are compared against the incoming ones, so a key reused for a genuinely different intent is rejected loudly instead of silently returning the wrong reservation. And uniqueness is a database constraint, not an application check, because a select followed by an insert is the same read-modify-write race in a different costume.

Keys should also expire. A unique index over an unbounded key history grows without limit; a retention window of some days covers every legitimate retry and nothing else.

When the Fold Gets Too Long to Fold

Summing every movement for a busy SKU is fine at ten thousand rows and unpleasant at ten million. The fix is a checkpoint, and the way to do it wrong is to overwrite the ledger with a summary.

create table stock_checkpoint (
  sku              text        not null,
  through_movement bigint      not null,
  location_id      text        not null,
  on_hand          integer     not null,
  committed        integer     not null,
  computed_at      timestamptz not null default now(),
  primary key (sku, location_id, through_movement)
);

The current position is then the checkpoint plus the tail:

select
    c.on_hand + coalesce(sum(m.qty_delta)
        filter (where m.stream = 'physical'), 0)  as on_hand,
    c.committed - coalesce(sum(m.qty_delta)
        filter (where m.stream = 'claim'), 0)     as committed
from stock_checkpoint c
left join stock_movement m
       on m.sku = c.sku
      and m.location_id = c.location_id
      and m.movement_id > c.through_movement
where c.sku = $1
  and c.location_id = $2
  and c.through_movement = (
      select max(through_movement)
      from stock_checkpoint
      where sku = $1 and location_id = $2
  )
group by c.on_hand, c.committed;

Rules I would treat as non-negotiable. Checkpoints are additive rows keyed by the movement they cover through, so an older one always survives and any two can be compared. Movements are never deleted just because a checkpoint has passed them — archive them to cold storage if volume demands, but a checkpoint is not a licence to destroy the thing it summarizes. And the boundary is a movement identifier, not a timestamp, because timestamps have ties and clocks have opinions.

The stock_slot row from earlier is the degenerate case of this: a checkpoint of exactly one row per slot, updated in place. That is a legitimate optimization precisely because last_movement_id makes it verifiable. A background job can recompute from the ledger, compare, and report drift. That comparison is the whole reason the memo is trustworthy, and a cache that nobody audits is a cache that is quietly wrong.

The Shelf Is the Truth, the Ledger Is the Intent

Every stock system built on events eventually meets a person holding a clipboard, and the person wins.

The ledger records what the organization believes happened. Reality includes events nobody recorded: a casing that rolled behind a rack, a filter used from the wrong box, a set delivered to a customer whose paperwork got lost in the October volume, an item counted twice by two people on the same afternoon. The ledger is an excellent record of intent. It is not a measurement of the physical world, and treating it as one is a category error that gets more expensive the longer it goes unexamined.

Cycle counting is the reconciliation ritual: count a small subset frequently rather than everything annually. Fast movers weekly, the long tail quarterly, anything that just produced a surprise immediately. The point is not the count itself — it is the adjustment, which is where belief and reality are forced to meet in a row that somebody signed.

create table cycle_count (
  count_id     uuid        primary key default gen_random_uuid(),
  sku          text        not null,
  location_id  text        not null,
  counted_qty  integer     not null check (counted_qty >= 0),
  expected_qty integer     not null,
  counted_by   text        not null,
  counted_at   timestamptz not null default now(),
  variance     integer generated always as (counted_qty - expected_qty) stored,
  explanation  text
);

expected_qty is captured at count time and stored, not recomputed later. Recomputation gives you today's expectation compared against a count taken last Tuesday, which is a comparison of two different moments dressed up as a variance.

Never Rewrite the Past to Match the Present

The wrong reconciliation is one line long and extremely tempting:

-- Do not do this.
update stock_slot set on_hand = 5 where sku = 'EXAMPLE-SKU' and location_id = 'main';

It makes the number right and destroys everything else. The variance is gone, so nobody can total shrink for the quarter. The event has no actor, so nobody knows who decided. There is no explanation, so a pattern of one unit disappearing every week from the same line looks like nothing at all. And the ledger no longer explains the position, which means the fold and the memo now disagree permanently — you have broken the invariant that made the whole design auditable, in order to fix a display.

The right reconciliation writes a new fact:

-- Reconciliation posted as a new fact, signed and explained.
insert into stock_movement
    (sku, location_id, reason, stream, qty_delta, ref_type, ref_id, actor, memo)
values
    ('EXAMPLE-SKU', 'main', 'count_adjust', 'physical', -1,
     'cycle_count', 'CC-EXAMPLE-0042', 'j.rivera',
     'Illustrative: counted 5, ledger position 6, no explanation found');

Same resulting number. Completely different system. One of these can answer "when did this start", and the other cannot answer anything.

A discipline worth adopting: an adjustment above some threshold does not post automatically. It queues for a recount, because the most common cause of a large variance is a miscount, and posting a bad correction pollutes the record you would otherwise use to detect real loss. Below the threshold, post it and move on — the arithmetic of chasing a single valve stem is not favourable.

Stock Has a Date, Not Just a Quantity

Available is meaningless without when, and this is where a lot of otherwise sound designs quietly fail.

A customer wants a set of four mounted on Thursday. Four are on the rack today. Between now and Thursday, three other confirmed jobs will consume seven units of that same size, and a delivery of eight is expected Wednesday afternoon. Today's number is 4. Thursday's projected position is 5. Neither one is wrong; they answer different questions, and showing the wrong one produces either a broken promise or a lost sale.

from datetime import datetime


def projected_atp(conn, sku: str, location_id: str, at: datetime) -> int:
    """Position at a future instant: today's free stock, minus claims
    that land before `at`, plus inbound confirmed to arrive before it."""
    row = conn.execute(
        """
        with position as (
            select on_hand, committed
            from stock_slot
            where sku = %(sku)s and location_id = %(loc)s
        ),
        future_claims as (
            select coalesce(sum(quantity), 0) as qty
            from reservation
            where location_id = %(loc)s
              and sku = %(sku)s
              and state = 'held'
              and expires_at > now()
              and coalesce(needed_at, now()) <= %(at)s
        ),
        inbound as (
            select coalesce(sum(qty_outstanding), 0) as qty
            from inbound_line
            where sku = %(sku)s
              and destination_id = %(loc)s
              and status = 'confirmed'
              and expected_at <= %(at)s
        )
        select p.on_hand - f.qty + i.qty
        from position p, future_claims f, inbound i
        """,
        {"sku": sku, "loc": location_id, "at": at},
    ).fetchone()
    return row[0]

Two honest caveats about that function. It assumes claims consume stock at needed_at, which is a simplification — a unit is really unavailable from the moment it is spoken for, and the projection is about ordering the sequence of events between now and the horizon. And it trusts expected_at, which is the least reliable field in the entire design. The projection is a forecast, and forecasts should be labelled as such wherever a human reads them.

Anything committed against a firm calendar slot — the self-serve reservation page feeding a confirmed changeover, for instance — deserves a hold whose expiry is tied to the job rather than to a clock ticking in minutes.

The Van Is a Location, and So Is Everything Else

The moment stock exists in two places, available stops being a property of a SKU and becomes a property of a pair.

A mobile service unit carries a small rolling inventory: a handful of popular sizes, valve stems, weights, plugs and patches, oil filters, a drum. It is the same business and the same catalogue, but it is emphatically not the same shelf. Four units on the van are unavailable for work at the main location, and four units at the main location cannot be mounted at a customer's driveway across the city.

Which means every quantity in this design is keyed by (sku, location_id), without exception, and any query that omits the location is a bug even when it happens to return a number somebody likes.

Transfers are two movements, not one:

-- One physical transfer, two ledger rows, a single shared reference,
-- committed together or not at all.
insert into stock_movement
    (ref_type, ref_id, actor, location_id, sku, stream, reason, qty_delta)
values
    ('transfer', 'TR-EXAMPLE-118', 'a.okafor', 'main',
     'EXAMPLE-SKU', 'physical', 'transfer_out', -2),
    ('transfer', 'TR-EXAMPLE-118', 'a.okafor', 'van-01',
     'EXAMPLE-SKU', 'physical', 'transfer_in', 2);

Sharing a ref_id across the pair is what lets you find half-completed transfers, and half-completed transfers are the single most common source of phantom inventory in any multi-location operation. Stock that left one place and never arrived at the other is invisible in both totals until somebody counts.

Where the van genuinely differs is connectivity. A driveway in an outer community may have no usable signal, so the van's writes queue locally and land later. That does not change the model — it changes occurred_at versus movement_id, which the ledger already distinguishes, and it means the van's slot memo is allowed to be stale in a bounded way while the main location's is not. The set of areas the van covers is, in a very direct sense, part of the system's consistency model.

For fleet accounts there is a third flavour: stock physically here but designated for a specific customer's units. That is not a separate location; it is a long-lived claim with an owner attached, which the reservation table already expresses.

Somebody Else's Warehouse Is a Claim

Distributor feeds tempt you into treating an external number as inventory. It is not inventory. It is an assertion, made at a moment that has already passed, about a warehouse you cannot see, by a party with no obligation to you.

Keep the boundary explicit in the schema:

create table supplier_availability (
  supplier_id   text        not null,
  supplier_sku  text        not null,
  our_sku       text,
  claimed_qty   integer     not null,
  observed_at   timestamptz not null,
  feed_run_id   uuid        not null,
  primary key (supplier_id, supplier_sku, feed_run_id)
);

Note what is missing: any column called available. What the feed gives you is claimed_qty at observed_at, and the honest presentation to a human is a range plus an age rather than a hard figure — likely obtainable for Thursday, last confirmed a few hours ago. A number rendered with the same visual weight as your own count will be read as equally true, and it is not.

There is also a whole failure category around identity. Their SKU is not your SKU, and the mapping is where errors hide: a load rating that differs by one step, a slightly different construction, a run-flat variant that carries the same nominal size. Anyone who has read a sidewall carefully knows how much meaning is packed into a short string, and a mapping table that treats two near-identical strings as equivalent will eventually put the wrong thing on a vehicle. The load index in particular is not a detail you round.

My rule: external stock may raise a delivery estimate, never an available-now count. Where the public catalogue shows something the business does not physically hold, the correct presentation is an obtainable-by date, not a quantity.

A Worked Trace Through One SKU

All numbers here are invented for illustration. The SKU is fictional and the sequence is compressed.

Monday, receipt. Six units arrive at the main location.

-- Monday: six units unloaded off the distributor truck.
insert into stock_movement (sku, location_id, stream, qty_delta, reason,
                            ref_type, ref_id, actor)
values ('EXAMPLE-SKU', 'main', 'physical', 6, 'receipt',
        'purchase_order', 'PO-EXAMPLE-9001', 'receiving');

Position: on-hand 6, committed 0, ATP 6.

Tuesday morning, a quote goes out. A customer wants a set of four for a Thursday changeover. A hold is placed with a TTL of two days, tied to the quote.

hold_place  claim  -4  ref=reservation/6f1c…  actor=counter/t.mendez

Position: on-hand 6, committed 4, ATP 2. The rack still holds six units. The number a second customer should be shown is two.

Tuesday afternoon, a second enquiry. Someone else wants four of the same size. The conditional update evaluates 6 - 4 >= 4, which is false, and affects zero rows. The request is refused, correctly, while six units sit visibly on the rack. This is the moment the design either earns its keep or gets overridden by somebody who trusts their eyes.

Thursday morning, the quote lapses. The customer never confirmed. The sweeper finds the hold past expires_at.

hold_expire  claim  +4  ref=reservation/6f1c…  actor=sweeper/expiry

Position: on-hand 6, committed 0, ATP 6. The claim stream now sums to zero for that reservation, which is the invariant mentioned earlier, and it is directly assertable in a test.

Thursday midday, a real sale. A walk-in buys two and has them mounted and balanced.

sale  physical  -2  ref=invoice/INV-EXAMPLE-4417  actor=counter/t.mendez

Position: on-hand 4, committed 0, ATP 4. Had this sale been preceded by a hold, it would be two rows in one transaction: a sale on the physical stream and a hold_release on the claim stream, so that the claim does not double-count against a unit that has already physically left.

Friday, cycle count. The counter sheet says three. The ledger says four.

count_adjust  physical  -1  ref=cycle_count/CC-EXAMPLE-0042  actor=j.rivera

Position: on-hand 3, committed 0, ATP 3. Nothing was overwritten. The variance is a queryable row with a name on it, and if that line produces a -1 every week, the pattern is sitting in the data waiting for somebody to run a group by.

The full narrative for the week reads back as six inserts. There is no state to reverse-engineer, no repair script in anyone's home directory, and no argument about what the number used to be.

Invariants Worth Waking Someone For

Metrics on an inventory system should be about disagreement, because a total on its own is never suspicious.

Fold-versus-memo drift. Recompute the position from the ledger on a rolling schedule and diff it against stock_slot. Any non-zero difference means a write path is bypassing the ledger, and that is a page rather than a ticket. This one check subsumes an enormous number of specific bugs.

Negative available events. With the check constraint in place these become constraint violations rather than bad data, which is the point. Count them anyway — a rising rate means some path is attempting an unsafe decrement and getting caught, and you would like to know before the constraint is dropped by someone in a hurry.

Oversell counter. Occasions where a promise was made that physical stock could not honour. Ideally always zero. It is the only metric on this list that a non-engineer will care about immediately, because it maps to a conversation somebody had to have with a customer.

Hold expiry backlog age. The oldest expires_at still in state held. If that exceeds the sweeper interval by a healthy multiple, the sweeper is stuck and stock is being sterilized silently. During the changeover peak this is the metric I would put on a wall.

Reservation rejection rate by SKU. A spike means either genuine scarcity or a bug in the availability calculation, and distinguishing those is a five-minute investigation you can only do if you kept the rejections.

Adjustment magnitude distribution. Not the mean — the tail. A slow rise in large corrections on one line is what theft, systematic miscounting, or a broken receiving process looks like from the data side, months before it looks like anything on the floor.

Ledger append latency from the van. How long between occurred_at and the row landing. Rising latency means field writes are queuing, and every downstream number is stale by that amount.

Questions That Come Up Every Time

Does a hold need to be a ledger row at all, given the reservation table has state?
Strictly, no. You could derive committed quantity entirely from reservation rows and skip the claim stream. What you lose is a single ordered narrative — with two sources you must join and interleave to answer "what happened to this SKU on Tuesday", and the interleaving is exactly the code that gets subtly wrong. The claim stream costs one insert per lifecycle transition and buys you a readable history.

Why not just use serializable isolation everywhere and stop thinking about this?
Because the retry logic is the thinking, relocated. Serializable turns a correctness problem into a throughput problem, and under the correlated load that makes this hard in the first place, the abort rate on a hot slot can be brutal. It is a good tool for invariants spanning several rows. It is a poor substitute for a where clause.

How do I handle a partial fulfilment — three of four available?
Make it an explicit product decision rather than an emergent one. Either the reservation is all-or-nothing, or it supports partial with a documented policy, and both are defensible. What is not defensible is a service that silently reserves three when four were requested and returns success. If you support partial holds, the response must state the quantity actually secured, and the caller must be built to notice.

What about serialized items — a specific casing with a DOT code, not a fungible unit?
Then quantity is the wrong abstraction entirely and you want an item table with per-unit state. Most of a tire inventory is genuinely fungible within a SKU, so this design applies; anything you track individually should be modelled individually. Mixing the two in one table produces the worst of both.

Do consumables really belong in this model?
Wheel weights, valve stems, patches, oil filters — yes, but with a much coarser grain. Nobody is placing a hold on a valve stem. Track them at the slot level with periodic counts and a low reorder trigger, skip the reservation lifecycle, and accept a variance tolerance that would be unacceptable on a set of four. The mistake is applying identical rigour to a two-dollar consumable and a four-figure set.

Where This Is More Machinery Than You Need

I have argued for an append-only ledger for six thousand words. Here is when I would not build one.

A single location with a single person doing the counting, low volume, and no reservations at all — a quantity column with an audit trigger is genuinely fine. The ledger's value is proportional to how many independent actors can change stock and how often two of them collide. One actor cannot collide with themselves.

If the goods are not worth reconciling, skip it. Nobody should build event sourcing for a bin of rags.

If reads are overwhelmingly point lookups and the write rate is trivial, the fold buys you nothing. The design pays off when history is queried and when concurrency is real.

And if the team is one person who will be maintaining this in three years, weigh that seriously. An append-only log with checkpoints, a sweeper, and a drift monitor is four moving parts. Four moving parts maintained by nobody is worse than one column maintained attentively.

The trigger I would use, stated as a rule: build the ledger the first time two independent processes can decrement the same slot without knowing about each other, or the first time somebody asks a question about last month that the current schema cannot answer. Before either of those, you are building for a problem you do not have. After either of them, you are already late.

What I Would Build in an Afternoon

Stripped to the parts that carry the weight, and in this order:

1. stock_slot with (sku, location_id) as the key, an on_hand/committed split, and the check constraint that forbids committing more than exists. This alone kills the read-then-write race.
2. Conditional update ... where on_hand - committed >= n, with the affected-row count treated as a real branch rather than an afterthought. One statement, one row lock, no lost updates.
3. stock_movement, append-only, with a closed set of reason codes and update/delete revoked at the role level. Write to it in the same transaction as the slot change.
4. Idempotency keys on the reservation path, unique-indexed, generated by the caller.
5. A drift check comparing the fold to the memo, on a schedule, that pages.

Not in the afternoon build: checkpoints, projections into the future, multi-location transfers, supplier reconciliation. Those are real, and they are what you add when the volume or the second location arrives.

None of this is specific to rubber. The domain just happens to make the problem unusually legible: the merchandise is bulky, the peak is savage and predictable, and the same physical object can be sold, consumed during a balancing job, used in a puncture repair, transferred to a van, damaged, or miscounted — often within the same week. Any system that models that honestly will handle a warehouse without breaking a sweat.

Understanding what a purchase actually involves at a single-location independent is not tangential to the engineering. It is the requirements document, and it is available to anyone willing to stand at the counter for an hour in October.