Constrained Forecast-to-Order and the Recall Graph: A Foodservice Build

It is 5 a.m. at the DC, and tomorrow’s order has to leave in two hours. The roast chicken for the hospital route has three days of usable life left on the lots you have on hand. There is a storm forecast that will move soup and stall salad. And a supplier just emailed that one of last week’s leafy-greens lots is “under review.” The spreadsheet your buyer is keying right now knows none of that — it averages four weeks of history, multiplies by a fudge factor, and prints a par. So you over-order the perishable that won’t sell through, short the item the weather will spike, and have no fast way to say whether the flagged lot ever reached that hospital. Three different failures, one root cause: the order plan and the traceability record are computed by tools that can’t see remaining shelf life, lead time, or genealogy.

This is a build document for the people who have to fix that — a foodservice supply-chain analytics lead, or the food-safety systems owner who gets paged when a recall lands. The companion executive paper argues where to spend against shrink and recall liability. This one specifies the two systems that actually move those numbers: a constrained forecast-to-order pipeline that emits item-location-day order, rotation, and markdown actions respecting the life left in the box, and a lot-genealogy graph that turns a contamination trace from a multi-day reconciliation into a single traversal. We walk each from interface to tolerance to the runbook you execute when the recall is real.

Keep the ERP, the WMS, and supplier EDI exactly where they are. They hold the authoritative lots, purchase orders, receipts, and master data, and they were never designed to solve a constrained optimization at 5 a.m. or walk a genealogy graph during a recall. What you build is a governed layer beside them: it reads their truth, computes the order plan and the trace, proposes actions, and writes only the decisions back — never the lot record itself.

Authoritative recordERP · WMS · supplier EDI · lot masterlots, POs, receipts, on-hand by location — slow, must stay intact

Constrained forecast-to-actiondemand model · shelf-life + lead-time constraints · solveremits order, rotation, markdown per item-location-day

Lot-genealogy graphsupplier lot → DC → outlet → menu itemforward/backward recall trace in one traversal

Where people actorder proposals · markdown labels · pull listsbuyers, outlet managers, QA, dispatch

Two derived systems share one boundary with the record: the pipeline produces the network's order plan, the graph answers the recall question. Both read receipts and write decisions — neither owns the lot.

The two are deliberately separate. The forecaster’s job is to be right about quantity under constraints; the graph’s job is to be complete about lineage. Fusing them would make a slow optimizer block a recall query that must return in minutes. Keeping them apart means you can re-train the demand model on Tuesday without touching the traversal that legally proves where every case went.

The constrained forecast: a naive number wastes perishables

A standard demand forecaster predicts how much you’ll sell. The number it returns is an unconstrained quantity — it assumes you can hold inventory indefinitely, that the truck arrives whenever you want, and that any lot is as good as any other. For perishables, each assumption is a specific, recurring loss. The fix is not a better point forecast; it is wrapping the forecast in constraints that turn a predicted quantity into an orderable one.

Three constraints do most of the work. Remaining shelf life caps how far ahead you can pre-position: ten days of a three-day item guarantees spoilage no matter how good the signal is. Lead time sets the horizon you must commit over — you order for the gap to the next delivery you can place, not for tomorrow alone. And on-hand life-by-lot changes the answer before you order a case: if the DC already holds a fast mover with one day of life, the order shrinks and the rotation gets urgent.

So the pipeline runs in two stages. The demand model emits a per-item-location distribution over the lead-time horizon — point forecast plus an uncertainty band, because the band sizes the safety stock. A constrained solver turns that distribution into an action minimizing expected stockout cost plus expected spoilage cost, subject to the life and lead-time limits and the on-hand lots. Order short on slow-moving high-perishables where spoilage cost dominates; let robust fast movers carry depth where stockout cost dominates.

PIPELINE ConstrainedOrder(item, location, asof)
  demand   = forecast_dist(item, location, horizon = lead_time(item, location))
  on_hand  = lots_with_remaining_life(item, location)          # FEFO-sorted
  usable   = sum(life_remaining_days(lot) >= sell_through_days for lot in on_hand)

  order_qty = argmin_q  E[stockout_cost(demand, on_hand + q)]
                      + E[spoilage_cost(on_hand + q, demand, shelf_life(item)))]
              subject to:
                q + usable  <=  demand.p90 * coverage_cap(item)    # don't pre-position past life
                q           >=  reorder_floor(item, location)
                lead_time(item, location) <= shelf_life(item)      # else flag, never auto-order

  emit Action.Order(item, location, qty = order_qty, basis = demand, lots = on_hand)

The coverage_cap keyed to remaining life is the line that separates this from every par-sheet tool: it refuses to pre-position past what the product can survive, so the forecast can be aggressive without the order being wasteful.

Markdown and rotation: FEFO logic at the item-location level

The same pipeline that decides what to order decides what to discount and what to pick first, because the inputs are identical — remaining life by lot and forecasted sell-through. When the usable life of an on-hand lot drops below the demand it can realistically clear before expiry, the engine proposes a markdown deep enough to move it and a rotation order that fronts it. The discount is not a flat “30% off three days out”; it is a function of how much life is left and how fast this item-location actually moves, so the slow mover at a quiet outlet gets cut harder and earlier than the same item at a busy one.

RULE MarkdownAndRotate(item, location)
  FOR lot IN on_hand(item, location) ORDER BY life_remaining ASC:        # FEFO
    clearable = forecast_units(item, location, within = life_remaining(lot))
    IF lot.qty > clearable:
      excess        = lot.qty - clearable
      markdown_pct  = price_curve(life_remaining(lot), sell_through_rate(item, location))
      emit Action.Markdown(lot, markdown_pct, reason = "projected_spoil", excess = excess)
    emit Action.Rotate(lot, pick_priority = rank_by(life_remaining))      # front the oldest life

Two cases received the same day can carry different markdowns because one outlet’s forecast clears it and the other’s doesn’t. That is the whole point of doing this at the item-location grain: the network does not have a markdown policy, each shelf does.

The lot-genealogy graph: forward and backward in one traversal

The order plan keeps margin. The genealogy graph is what you reach for when the supplier email says “under review.” Most operators store lots as flat batch rows, so answering “where did SUP-LOT-99177A go” means joining receipts to transfers to production to sales, table by table, across systems that don’t share keys — which is why recalls still take days even though the data technically exists. By the time the answer assembles, the product is served.

Model it as a graph instead. Nodes are entities — supplier lot, DC receipt, outlet transfer, prepped batch, menu item, fulfilled order. Edges are the Critical Tracking Events FSMA 204 already requires you to log: receive, transform, ship. A backward trace walks from a sick-customer order to the supplier lot behind it; a forward trace walks from a flagged lot to every outlet and menu item it reached. Both are the same recursive traversal over one append-only edge table.

-- forward + backward recall trace from a flagged supplier lot, one traversal
WITH RECURSIVE genealogy AS (
  SELECT lot_id, parent_lot_id, node_type, location_id, 0 AS hop
    FROM lot_edge WHERE lot_id = :flagged_lot
  UNION ALL
  SELECT e.lot_id, e.parent_lot_id, e.node_type, e.location_id, g.hop + 1
    FROM lot_edge e JOIN genealogy g
      ON e.parent_lot_id = g.lot_id      -- walk forward to descendants
      OR e.lot_id = g.parent_lot_id      -- walk backward to ancestors
)
SELECT location_id, node_type, min(hop) AS distance
  FROM genealogy
 WHERE node_type IN ('outlet','menu_item','order')
 GROUP BY location_id, node_type;        -- the affected-outlet pull list

The output is not a report a person reads and then acts on — it is an executable pull list keyed to specific outlets, lots, and orders, ready to hand to dispatch. Keep the lot master in the ERP; the graph is a derived, append-only projection of the CTEs, rebuildable from the event log if it ever drifts. That property — rebuildable from events — is what lets a QA director swear to its completeness in an inspection.

Auto-act tolerances and the anomaly gate

The pipeline proposes orders and markdowns continuously, across thousands of item-location pairs, every night. Routing all of that through a human kills the whole point; auto-applying all of it without a brake is how one bad forecast empties a budget. The resolution is a tolerance band: a proposed order or markdown that sits close to the current plan and inside policy limits applies itself; anything that deviates beyond the band, or trips a hard rule, is held and escalated as an anomaly with the reason attached.

GATE AutoActTolerance(action)
  delta = abs(action.qty - prior_committed(action)) / max(prior_committed(action), 1)

  WHEN action.type == "Order"
   AND delta <= 0.20                       # within 20% of the standing plan
   AND action.qty <= demand.p90 * coverage_cap(item)
   AND lead_time(item, location) <= shelf_life(item)
  THEN auto_apply = true

  WHEN action.type == "Markdown" AND action.markdown_pct <= max_auto_markdown[category]
  THEN auto_apply = true

  WHEN delta > 0.20 OR forecast.uncertainty > drift_threshold OR new_supplier_lot
  THEN auto_apply = false AND escalate("Buyer", reason = anomaly_reason(action))

  WHEN recall_match(action.lots) == true
  THEN auto_apply = false AND block = true AND escalate("QA")     # never auto-order a flagged lot

Every evaluation — applied or escalated — emits one replay-safe event. It is the unit of audit, and the thing your buyer or QA director defends later:

{
  "eventType": "ConstrainedOrderEvaluated",
  "item": "CHICKEN-BREAST-10LB",
  "location": "DC-07 → OUTLET-HOSP-12",
  "orderQty": 18,
  "priorCommitted": 16,
  "deltaPct": 0.125,
  "forecast": { "p50": 15, "p90": 21, "horizonDays": 3 },
  "onHandUsableDays": 1.5,
  "coverageCapDays": 3,
  "decision": "AutoApply",
  "markdownProposed": null,
  "policyVersion": "food.order.v12",
  "modelVersion": "demand-forecaster-4.2.0",
  "correlationId": "fdsvc-ord-7741c",
  "evaluatedAt": "2026-02-24T05:12:00Z"
}

The two fields a par-sheet can never produce are onHandUsableDays and coverageCapDays: together they prove the order was sized to the life actually left in the box, not to a calendar average. Events carry lot, location, and order references — not customer PII — and retention and rights rules apply to the evidence store, not only the order record.

Closing the loop: spoilage and stockouts re-train the forecaster

A forecaster that never learns from what it cost decays into the same fudge factor it replaced. Feed outcomes back as labeled signal: every order is a hypothesis, and realized sell-through, spoiled units, and stockout-substitutions are the answer. Spoilage tells the model it over-ordered into short life; a stockout that forced a substitution tells it the safety band was too thin for that item-location. Both flow back keyed to the same correlationId, so the next training run weights the cost it actually incurred, not abstract forecast error.

Spoilage / waste % of COGS1.5–5.5%0.5–2.5%Constrained order + FEFO markdown
Stockout / substitution rate2–8%0.5–3%Uncertainty-band safety stock
Recall trace-and-pull latency6–48 hrs15–90 minGenealogy graph traversal
Forecast actions auto-applied0%70–85%Tolerance band vs anomaly gate
OTIF84–95%94–98.5%Lead-time-aware ordering

Planning ranges to set against your own baseline, category mix, and outlet types. Track one cost, one service, and one risk number every cycle — spoilage, OTIF, and trace latency are a serviceable default triad.

The auto-apply rate is the number that proves the loop is working: it should climb as outcomes accumulate and the model’s calibrated uncertainty tightens, because well-calibrated forecasts fall inside the tolerance band more often. If it stalls, the model is not learning from its own spoilage and stockouts — that is the symptom to chase, not the overall error metric.

Build it in the order the loop demands, not all at once. The accountability is fixed even as autonomy rises: the pipeline proposes, named humans own the irreversible calls.

01Forecastitem-location-day demand band
02Constrainapply shelf-life + lead-time caps
03Proposeorder, markdown, rotation
04Gateauto-apply in band · escalate anomalies
05Learnspoilage + stockout re-train

The dashed gate is the buyer/QA escalation. It widens — more actions auto-apply — only as outcome feedback proves the forecast calibrated. Recall-matched and high-acuity-outlet orders never auto-apply.

Role	RACI	Owns
Supply-Chain Analytics Lead	A	Forecaster, constraints, and tolerance bands
Food-Safety Systems Owner	A	Lot-genealogy completeness and recall trace
Buyer / Replenishment	R	Reviews escalated orders; commits POs
Outlet / DC Ops	R	Executes markdowns, rotations, pull lists
Compliance / Legal	C	FSMA 204 CTE/KDE recordkeeping
COO	I	Spoilage, OTIF, auto-apply, and trace-latency KPIs

Runbook: executing a recall trace against the genealogy graph

When a supplier flags a lot, the clock that matters is the one between the notice and a pulled, accounted-for case at every outlet. The graph makes that a procedure, not a fire drill.

T+0 to 15 min — traceSeed the recursive traversal with the flagged supplier lot. Forward-walk to every DC receipt, outlet transfer, prepped batch, and fulfilled order it reached. The output is the affected-outlet pull list, keyed to lots and orders — not a report.
T+15 to 45 min — block and pullThe tolerance gate auto-blocks any open order touching the flagged lots. Dispatch executes the pull list; outlets confirm physical removal against the keyed units. High-acuity outlets (hospital, school) are worked first by policy.
T+45 to 90 min — prove and notifyEach pull confirmation appends an event; the genealogy projection now shows the lot fully contained. Compliance exports the CTE/KDE chain for FSMA 204; the same trace, run backward, supports any source-attribution claim.
After — feed the loopThe recall outcome and any forced markdowns flow back as labeled signal. Supplier lots with repeated flags raise their inbound sampling weight in the next training run.

Two failure modes quietly defeat teams who skip the fundamentals. First, an unconstrained forecast bolted to a par sheet — accurate demand, ruinous orders, because nothing stops it pre-positioning past shelf life; the fix is the coverage cap keyed to remaining life. Second, flat batch rows instead of a genealogy graph — the data exists but the trace is a cross-system reconciliation that finishes after the food is served; the fix is the append-only CTE edge table where the trace is one traversal. Get those two right and the tolerance gate and the feedback loop have something true to stand on.

What we covered

The takeaways

Constrain the forecast before you order. Wrap the demand band in shelf-life and lead-time limits and a coverage cap so the order is sized to the life in the box, not a calendar average.
Markdown and rotation are the same pipeline. Discount depth and pick order fall out of remaining life and item-location sell-through — a policy per shelf, not per network.
Traceability is a graph. Model supplier lot → DC → outlet → menu item as CTE edges and forward/backward recall is one traversal that emits a pull list.
Auto-act in the band, escalate the anomalies. Orders and markdowns near the standing plan apply themselves; deviations, drift, and recall matches route to a named human.
Let outcomes teach the forecaster. Spoilage and stockouts re-train the model keyed to the same correlation ID, and the auto-apply rate is the proof the loop is closing.

One pipeline turns a demand forecast into a network-wide order plan the product can actually survive; one graph turns a contamination notice into a pull list before the case is served. Build those two beside the systems you already trust, gate them to your own tolerance, and the 5 a.m. order stops being a buyer’s guess and the recall stops being a search party.

References: FSMA 204 CTE/KDE traceability rule · shelf-life and FEFO rotation standards · perishable demand forecasting under uncertainty · recall execution playbooks · NIST AI RMF · NIST Privacy Framework · NIST CSF 2.0 · GDPR · CISA Secure by Design.