USDA drawdown commit · wiki guide

What this is

USDA lot drawdown commit

USDA commodity drawdown is the workflow that keeps weighted-average costing honest when a lot exhausts. The pricing of every assembly that uses the commodity (barrel cheddar, etc) depends on which lot we're drawing from — if a lot ends and we forget to recompute, every quote and pricing_master row sourced from that commodity is wrong.

The cascade re-bases the vendor_costs entry, recomputes the assembly_cost_rollup for every affected assembly, flags open quotes for re-quote, and updates the USDA program reconciliation status.

Risk level 4 (high) — because the cost basis touches active customer pricing.

When to use it

Trigger conditions

Inventory cron detects a lot remaining_qty crosses zero.
Manual lot close from /admin-dashboard.html when reconciling with USDA reports.
Triggered from assembly_build_review when a BOM change references a different commodity lot.
USDA entitlement period rollover — old lots retire en masse.

Heuristic

The cost_diff_reasonable precondition warns if the new lot's cost differs by >30% from the exhausted lot. This catches the most common data error: wrong lot mapped to wrong commodity.

Step-by-step what happens

The 4 beats

01

INSERT new vendor_costs row

INSERT into vendor_costs with the new weighted-average unit_cost and effective_from. The prior row remains (active=false implicit via effective_from ordering).

Writes vendor_costs

Time ~80ms

Kind d1_write

Status stub
02

Recompute affected assemblies

Call recompute_assembly_cost_rollup for each assembly whose BOM references the commodity sku. Rollups persist with fresh last_computed.

Writes assembly_cost_rollup

Time ~200ms per assembly

Kind chat_tool

Status stub
03

Flag affected open quotes

For each quote in (draft, proposed, sent) with a line on the commodity sku, stage flag_stale_quote in proposed_actions.

Writes proposed_actions

Time ~50ms per quote

Kind stage_proposed_action

Status real

Note one_per affected quote
04

USDA program reconciliation

UPDATE usda_commodity_programs SET reconciliation_status='pending' for affected programs. Surfaces in the USDA reconciliation queue.

Writes usda_commodity_programs

Time ~80ms

Kind d1_write

Status stub

Outcomes

What's different after the workflow runs

New cost basis

Applied

≤ 10s

Assemblies

Recomputed

per BOM hit

Quotes

Flagged

in proposed_actions

USDA reconcile

Pending

queued

A new vendor_costs row carries the weighted-average from the next lot.
Every affected assembly has a fresh cost rollup.
Each open quote touching the commodity sku is in Mike's queue for re-quote.
usda_commodity_programs reconciliation is pending.
reflexion_log carries a usda_drawdown,sku:? tagged row.

Failure modes

What can go wrong and how to recover

No next lot available

Precondition blocks: current_lots.length > 0. This means we have no commodity supply for the SKU. Manual escalation: USDA program manager to provision a new lot.

Cost differential >30%

Precondition warns. Likely a data error (wrong lot mapped). Mike verifies the lot assignment before approving.

Assembly recompute partial

Retry policy is 2 linear-backoff attempts. Persistent failures alert; manual POST /admin/assembly/recompute recovers.

No quotes affected

Sometimes a commodity drawdown happens with no open quotes. Cascade still completes; no flag rows stage.

Adjacent workflows + diagrams

For developers

Code paths + invariants

Concern	Where
Workflow contract	workflow_definitions WHERE workflow_type='usda_drawdown_commit'
Lot table	usda_commodity_lots
Program table	usda_commodity_programs
Cost-basis pattern	weighted-average across active lots
Reflexion tag	usda_drawdown,sku:<sku>
Risk level	4
Expected duration	~30 min
Trigger	event · sources=inventory_threshold_cron, manual_lot_close

// Weighted-average across remaining active lots const avg = currentLots.reduce( (sum, l) => sum + (l.remaining_qty * l.unit_cost), 0 ) / currentLots.reduce((sum, l) => sum + l.remaining_qty, 0); await db.run( "INSERT INTO vendor_costs (vendor_item_id, unit_cost, effective_from, source, status) " + "VALUES (?, ?, ?, 'usda_drawdown', 'active')", [vendor_item_id, avg, effective_from] );

Changelog

Dated trail · spot stale claims

Dated trail of when this doc was last touched, what changed, and what to look at if it feels stale.

Date	Round	Change	Touched by
`2026-05-26`	`R586`	Added CHANGELOG · SCHEMA · RUNBOOK · BACKLOG sections — wiki became best-in-class operating documentation.	Mike + Claude
`2026-05-25`	`R584/R585`	Wiki originally shipped — 8-section structure (hero / what / when / steps / outcomes / failure-modes / related / for-developers).	Mike + Claude

If today is more than 60 days past the latest changelog row, treat live system behavior as the source of truth. The doc may have drifted — verify against the workflow contract in workflow_definitions WHERE workflow_type='usda_drawdown_commit' before acting on these claims.

Schema · data contract

The machine-readable spec

Canonical fields, table names, endpoint signatures. What code should match, what tests should assert. workflow_type · usda_drawdown_commit · risk_level · 3.

Inputs (required + optional)

Field	Type	Description
`entitlement_id`	`string`	USDA entitlement reference. Required.
`item_id`	`integer`	Required — the USDA-eligible item.
`draw_qty`	`decimal`	Quantity to draw. Required.
`draw_date`	`date`	Effective date.

D1 tables written

Table	Operation	Trigger
`usda_drawdowns`	INSERT	FIFO drawdown record
`proposed_actions`	INSERT	Drawdown commit proposal
`usda_entitlement_balance`	UPDATE (post-HITL)	Decrement balance
`events`	INSERT (usda.drawn)	downstream bid_price_update (net cost shift)

Endpoints called

Method	Path	Purpose
`POST`	`/api/workflow/execute`	Runner entrypoint
`GET`	`/api/usda/entitlements/:id/balance`	Live balance

Events fired

event_type	When	Subscribers
`usda.drawn`	Post-HITL commit	bid_price_update on barrel cheddar lines

Runbook · when it breaks

It broke at 2am — what now

Different from "how do I use this." This is the page Mike pulls up when something is wrong: logs to check, recovery steps, who to escalate to.

Scenario · Drawdown committed but balance didn't decrement

Post-HITL UPDATE didn't fire.

Verify: SELECT balance FROM usda_entitlement_balance WHERE entitlement_id=?
Manual decrement: UPDATE ... SET balance = balance - ?
Log: Add a usda_drawdowns row if missing.

Scenario · Balance went negative

Drawdown exceeded available — FIFO check failed.

Check: SELECT balance FROM usda_entitlement_balance WHERE entitlement_id=?
Roll back: UPDATE usda_drawdowns SET status='rolled_back' WHERE id=<id>; restore balance.
Fix the FIFO: Drawdown workflow must verify balance >= draw_qty before staging.

Scenario · Drawdown affected barrel cheddar pricing but bid wasn't updated

usda.drawn event subscribed but bid_price_update didn't trigger.

Check subscriptions: SELECT * FROM event_subscriptions WHERE event_type_pattern IN ('usda.drawn','usda.*')
Manual trigger: Use propose_price_change with new net cost.

Logs to check

workflow_run_log · top-level run audit
workflow_step_log · per-step trace
workflow_verify_results · post-window verify outcomes
cron_locks · stuck cron lock detection
events · workflow.completed / workflow.failed event trail
reflexion_log · per-run narrative (if reflexion_enabled)
npx wrangler tail · live Worker logs

Kill switch · emergency stop

If this workflow is misbehaving in a high-impact way (creating bad proposed_actions in volume, pushing wrong things to NS), flip a kill switch:

kill:ns_writes · stops every NS push platform-wide
kill:proposed_apply · stops HITL approvals from executing fan-out
kill:high_risk_ops · stops risk_level >= 4 fan-out

See kill-switches-state-machine.html for the full state machine + recovery procedure.

Escalation

Primary: Mike Levine (single-admin) · mikelevine@globalfoodsolutions.co. For prolonged outage during business hours, notify warehouse lead + accounting lead so they can defer dependent work.

Backlog · open questions

What's not done · what's uncertain

What's not done, what's uncertain, what we punted. Captured so it survives context switches and doesn't die in someone's head.

STUB
Auto-drawdown on bid award
Today manual. Should auto-stage when a USDA-eligible bid is awarded.
OPEN
Multi-entitlement allocation
Today single entitlement per draw. Cross-entitlement allocation isn't supported.
DEFER
USDA portal sync
Today balance is manually tracked. Direct sync from USDA portal would help.