Handling Stale Inventory Counts From a Lagging Projection in Availability Lookups

A parts availability check can return an internally consistent, well-formed answer and still be wrong, even when no HTTP cache or proxy is anywhere in the request path — the failure documented in real-time parts availability checks via REST APIs never applies. This page covers a different, purely internal fault: the availability query reads a materialized on_hand projection that trails the authoritative movement ledger because the events that would update it are queued but not yet applied. The response is not cached anywhere; it is simply out of date at the source, inside the inventory service itself.

Incident Profile

An event-sourced inventory service records every issue and receipt as an immutable event on an append-only ledger, keyed by an ever-increasing offset. A separate projector consumes that ledger and maintains a materialized on_hand table — the fast, queryable read model that availability lookups hit. Under normal load the projector keeps pace within milliseconds. During a burst of overnight parts issues against a single SKU, the projector’s consumer group fell behind, and a routing job queried availability squarely inside that backlog.

2026-07-14 09:41:12,003 INFO  [inventory_projector] applied event offset=118 type=RECEIPT sku=SEAL-2210 loc=WH-04 delta=+10 -> on_hand=8
2026-07-14 09:41:12,004 INFO  [inventory_projector] watermark advanced to offset=118
2026-07-14 09:41:31,206 INFO  [ledger_writer] appended offset=119 type=ISSUE sku=SEAL-2210 loc=WH-04 delta=-3 wo=WO-90991
2026-07-14 09:41:33,884 INFO  [ledger_writer] appended offset=120 type=ISSUE sku=SEAL-2210 loc=WH-04 delta=-3 wo=WO-90994
2026-07-14 09:41:36,102 INFO  [ledger_writer] appended offset=121 type=ISSUE sku=SEAL-2210 loc=WH-04 delta=-2 wo=WO-90996
2026-07-14 09:41:42,517 DEBUG [availability_service] GET /inventory/projection/SEAL-2210?location=WH-04 -> on_hand=8 (watermark=118)
2026-07-14 09:41:42,518 INFO  [routing_engine] WO-91007 dispatched against SEAL-2210 qty=8 at WH-04
2026-07-14 09:41:43,880 ERROR [ledger_audit] ledger head=121 for SEAL-2210@WH-04; three ISSUE events (119-121, total -8) already recorded; true on_hand=0

The technician arrives at WH-04 to find the bin empty. Nothing in this trace is a transport error — the HTTP call succeeded, no gateway sits between the caller and the service, and the projection table itself is not corrupted. It is simply three events behind the ledger’s head at the exact moment the availability query ran, and nothing in the query path noticed.

Root Cause Analysis

The projector applies events in order and eventually converges on the truth, which is exactly what “eventually consistent” means — and exactly why it is unsafe to treat a materialized view as ground truth without qualification.

  1. The read model is a derived, lagging copy by design. The on_hand table the availability service queries is not the ledger; it is the output of a consumer that processes the ledger asynchronously. Any gap between “event appended” and “event applied” is a window where the projection under-reports consumption.
  2. The availability query never asks how far behind it is. The projector already knows its own progress — it advances a watermark (the offset or timestamp of the last event it applied) every time it commits a batch. The query in the incident reads on_hand straight out of the table without ever comparing that watermark to the ledger’s current head.
  3. No freshness budget is enforced anywhere in the decision. Even a projector that is usually fast can fall behind during a burst, a redeploy, or a slow downstream write. Without a defined tolerance — how many unapplied events, or how much wall-clock lag, is acceptable before the projection is untrustworthy — every read is implicitly trusted regardless of how stale it actually is.

This is a different failure surface than the one covered for asset lookup and inventory synchronization in the HTTP-caching page: there, an intermediary served a stale response to a fresh request and the fix lived entirely in the client’s cache directives. Here, the request is always fresh and always reaches the service directly — the staleness is baked into the read model the service consults, and the fix has to live inside the service’s own consistency contract, by exposing and gating on projector lag rather than by touching HTTP headers at all.

Lagging inventory projection versus a watermark-gated recompute A sequence diagram with three lifelines: the availability service, the materialized inventory projection, and the write-side event ledger. In the stale path, the availability service queries only the projection, which returns on_hand 8 from watermark offset 118 without ever comparing itself to the ledger; the ledger has already recorded three ISSUE events at offsets 119, 120 and 121 bringing true on_hand to 0, but the service dispatches a work order against the phantom 8 units. In the fixed path, the availability service asks the projection for its on_hand value and watermark, then asks the ledger for its head offset; it computes lag as head minus watermark, finds a lag of 3 events exceeds the freshness budget of 1, and instead of trusting the projection it recomputes on_hand by replaying the ledger tail from offset 119 to 121, arriving at the true value of 0 and holding the work order closed. Why on_hand=8 lies: ungated projection versus watermark/lag gate Availability service dispatch decision Inventory projection materialized read model Event ledger write-side, authoritative STALE PATH — the bug GET projection.on_hand no watermark check ledger head never consulted on_hand=8 · watermark=118 dispatch against qty=8 ledger already at 0, WO blocked FIXED PATH — watermark/lag gate GET on_hand + watermark GET ledger head head=121 lag=3 > budget=1 → recompute tail recomputed on_hand=0 fail closed → hold WO

Resolution

The fix is to stop treating the projection as an oracle and start treating it as what it is: a cache of the ledger with a measurable, checkable lag. The projector already knows its own watermark; the service simply has to ask for it, compare it to the ledger head, and decide what to do when the gap is too wide.

Before — read the projection, trust it unconditionally:

def get_availability(projection_store, sku: str, location_id: str) -> int:
    # Reads the materialized view only. No idea how far behind the ledger it is.
    row = projection_store.fetch_on_hand(sku, location_id)
    return row["on_hand"]

After — expose the watermark, compute lag against the ledger head, gate the decision:

from dataclasses import dataclass


@dataclass
class ProjectionReadingError(RuntimeError):
    sku: str
    location_id: str
    lag: int


def get_availability(ledger, projection_store, sku: str, location_id: str,
                      max_lag_events: int = 1) -> int:
    row = projection_store.fetch_on_hand(sku, location_id)
    watermark = row["watermark"]          # last event offset this projection applied
    ledger_head = ledger.head_offset(sku, location_id)  # authoritative current offset
    lag = ledger_head - watermark

    if lag <= max_lag_events:
        # Projection is fresh enough to trust directly.
        return row["on_hand"]

    # Projection is behind the ledger by more than the freshness budget allows.
    # Do NOT trust it — recompute on_hand from the last known-good watermark
    # forward, replaying only the unapplied tail rather than the full history.
    tail_events = ledger.events_after(sku, location_id, since_offset=watermark)
    recomputed = row["on_hand"]
    for event in tail_events:
        recomputed += event.delta
    return recomputed

Two changes carry the fix. First, the projection now surfaces its watermark alongside on_hand, so the caller can measure how stale the view is instead of assuming freshness. Second, when lag exceeds max_lag_events, the service performs a bounded recompute — replaying only the ledger events appended since the watermark, not the entire history — which is cheap because the tail is small even when the projector has fallen meaningfully behind. The service never blocks on the projector catching up; it does the small amount of extra work itself and returns a number it can trust.

Minimal Reproducible Pipeline

This script runs standalone. It models a toy event ledger, a projector that can be made to lag deliberately, a watermark/lag gate, and an availability decision that recomputes from the ledger tail rather than trusting a stale projection — feeding the canonical WorkOrderPayload used across the site.

import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
from enum import Enum
from typing import Dict, List, Optional

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s [%(name)s] %(message)s")
logger = logging.getLogger("cmms_projection_gate")


class Priority(str, Enum):
    CRITICAL = "critical"
    HIGH = "high"
    STANDARD = "standard"


@dataclass
class WorkOrderPayload:
    """Canonical CMMS work order — SLA fields are mandatory site-wide."""
    work_order_id: str
    asset_id: str
    part_skus: List[str]
    required_quantities: Dict[str, int]
    location_id: str
    priority: Priority = Priority.STANDARD
    requested_completion: Optional[datetime] = None
    escalation_tier: int = 0
    status: str = "open"
    created_at: datetime = field(default_factory=lambda: datetime.now(timezone.utc))


@dataclass
class LedgerEvent:
    offset: int
    sku: str
    location_id: str
    delta: int


class EventLedger:
    """Append-only, authoritative movement log keyed by monotonically increasing offset."""

    def __init__(self):
        self._events: List[LedgerEvent] = []
        self._next_offset = 1

    def append(self, sku: str, location_id: str, delta: int) -> int:
        event = LedgerEvent(self._next_offset, sku, location_id, delta)
        self._events.append(event)
        self._next_offset += 1
        return event.offset

    def head_offset(self, sku: str, location_id: str) -> int:
        matching = [e.offset for e in self._events if e.sku == sku and e.location_id == location_id]
        return max(matching, default=0)

    def events_after(self, sku: str, location_id: str, since_offset: int) -> List[LedgerEvent]:
        return [
            e for e in self._events
            if e.sku == sku and e.location_id == location_id and e.offset > since_offset
        ]


class InventoryProjection:
    """Materialized read model. Applies events one at a time to simulate consumer lag."""

    def __init__(self):
        self._on_hand: Dict[tuple, int] = {}
        self._watermark: Dict[tuple, int] = {}

    def apply_next(self, ledger: EventLedger, sku: str, location_id: str) -> bool:
        key = (sku, location_id)
        watermark = self._watermark.get(key, 0)
        pending = ledger.events_after(sku, location_id, since_offset=watermark)
        if not pending:
            return False
        event = pending[0]  # apply strictly one event per call — simulates a slow consumer
        self._on_hand[key] = self._on_hand.get(key, 0) + event.delta
        self._watermark[key] = event.offset
        return True

    def fetch_on_hand(self, sku: str, location_id: str) -> dict:
        key = (sku, location_id)
        return {"on_hand": self._on_hand.get(key, 0), "watermark": self._watermark.get(key, 0)}


def get_availability(ledger: EventLedger, projection: InventoryProjection, sku: str,
                      location_id: str, max_lag_events: int = 1) -> int:
    row = projection.fetch_on_hand(sku, location_id)
    watermark = row["watermark"]
    ledger_head = ledger.head_offset(sku, location_id)
    lag = ledger_head - watermark

    if lag <= max_lag_events:
        logger.info("Projection fresh (lag=%d): trusting on_hand=%d", lag, row["on_hand"])
        return row["on_hand"]

    tail = ledger.events_after(sku, location_id, since_offset=watermark)
    recomputed = row["on_hand"] + sum(e.delta for e in tail)
    logger.warning(
        "Projection stale (lag=%d > budget=%d): recomputed on_hand=%d from ledger tail",
        lag, max_lag_events, recomputed,
    )
    return recomputed


def can_dispatch(ledger: EventLedger, projection: InventoryProjection, wo: WorkOrderPayload) -> bool:
    for sku in wo.part_skus:
        required = wo.required_quantities.get(sku, 1)
        available = get_availability(ledger, projection, sku, wo.location_id)
        if available < required:
            logger.warning("Short on %s: need %d, have %d", sku, required, available)
            return False
    return True


if __name__ == "__main__":
    ledger = EventLedger()
    projection = InventoryProjection()

    # Receipt lands and the projector keeps up.
    ledger.append("SEAL-2210", "WH-04", +10)
    projection.apply_next(ledger, "SEAL-2210", "WH-04")

    # Three issues arrive back-to-back; the projector falls behind (simulated by
    # NOT calling apply_next for each one before the availability check runs).
    ledger.append("SEAL-2210", "WH-04", -3)
    ledger.append("SEAL-2210", "WH-04", -3)
    ledger.append("SEAL-2210", "WH-04", -2)

    work_order = WorkOrderPayload(
        work_order_id="WO-91007",
        asset_id="PUMP-19",
        part_skus=["SEAL-2210"],
        required_quantities={"SEAL-2210": 1},
        location_id="WH-04",
        priority=Priority.CRITICAL,
        escalation_tier=1,
    )

    if can_dispatch(ledger, projection, work_order):
        logger.info("Routing %s to a technician.", work_order.work_order_id)
    else:
        logger.info("Holding %s — projection was stale, recompute shows shortage.", work_order.work_order_id)

Running this prints a WARNING line showing the gate catching a lag of 3 against a budget of 1, recomputing on_hand=0 from the ledger tail, and can_dispatch correctly returning False. Comment out the three ledger.append(..., -3/-3/-2) calls and rerun to see the fresh, lag-free path return True instead. The same watermark discipline generalizes past routing: a deferred dispatch here is exactly the signal that should flow into automated reorder triggers once a genuine shortage — as opposed to a stale read — is confirmed.

Prevention Checklist

FAQ

Is this the same problem as the HTTP cache page, just described differently?

No. Real-time parts availability checks via REST APIs covers a request that reaches an intermediary cache before it reaches the origin — the fix is entirely about cache-control headers and bypass parameters on an otherwise-correct backend. This page covers a request that always reaches the correct, uncached service directly; the staleness lives inside that service, between its write-side ledger and its own materialized read model. Fixing one does nothing for the other.

What freshness budget should I set for max_lag_events?

Start from the acceptable staleness window for your busiest SKU-location pairs, not a global constant. A budget of zero forces a recompute on every read once any event is pending, which is safest but costs the most CPU; a budget in the low single digits tolerates normal projector jitter while still catching a real backlog. Tune it against your projector’s typical apply latency under peak load, then alert well below the point where recomputes become the common case.

Is replaying the ledger tail expensive at scale?

Only if lag is allowed to grow unbounded. Because the recompute starts from the projection’s last-known value and applies only events after the watermark, the cost scales with lag, not with total ledger history. That is precisely why the freshness budget matters — it keeps the tail small enough that a fallback recompute is always cheaper than blocking the dispatch decision.

Does this apply if my projection is a database trigger instead of an event-sourced consumer?

Yes, with a small mapping: whatever mechanism updates your on_hand table asynchronously has some notion of “last change it has processed,” even if it isn’t called a watermark. Expose that marker, compare it to the true source of truth’s latest change, and apply the same lag gate — the pattern doesn’t require a formal event-sourcing architecture, only an honest measurement of how far the read model trails the write side.

This page addresses an internal read-model fault inside parts availability checks; contrast it with the transport-layer fault in real-time parts availability checks via REST APIs, see the broader read-and-reconcile flow in asset lookup and inventory synchronization, forward confirmed shortages to automated reorder triggers, and size ledger-write batching with async batch processing.

Part of: Parts Availability Checks.