Quarterly bid eligibility refresh

What this is

Quarterly bid eligibility refresh

Bid eligibility lives in the spec data. If a spec's kosher certificate expired, or its USDA K-12 attestation is missing, or its last_updated is older than 180 days, a bid response that includes it can be disqualified on review. This workflow catches those before they cost us.

It runs quarterly because spec currency typically rolls over on a quarterly+ cadence. Tighter would be noisy; looser would miss windows.

Risk level 2 (low). Output is staged proposals — no writes to NS, no customer-facing changes.

When to use it

Trigger conditions

Quarterly cron on the first of Jan / Apr / Jul / Oct at 09:00 ET.
Manual re-run from /admin-dashboard.html when a bid is imminent and we want fresh eligibility.
After a USDA program change that retired or added cert requirements.
Pre-bid prep for a major bid (B5875 cycle, etc.).

Heuristic

Stale criterion: updated_at < 180 days OR (kosher = Y AND kosher_certificate IS NULL). This catches both passive staleness and active cert misses.

Step-by-step what happens

The 2 beats

01

Stage propose_spec_update per stale spec

For each row in stale_specs, a proposed_actions row stages with action_type=propose_spec_update. The proposal includes which fields need refresh and which cert is missing.

Writes proposed_actions

Time ~80ms per stale

Kind stage_proposed_action

Status real
02

Notify Nutrition Admin

A draft email to the nutrition admin lists the stale specs and which need vendor follow-up vs internal action.

Writes outbound_email_log

Time ~80ms

Kind hitl_email_draft

Status stub

Note target · nutrition_admin

Outcomes

What's different after the workflow runs

Stale specs

Surfaced

one_per stale

Cert checks

Validated

USDA, K-12, kosher

Quarterly cadence

Jan/Apr/Jul/Oct

09:00 ET

Owner

Nutrition Admin

email queued

Each stale spec has a propose_spec_update row in Mike's queue.
Nutrition admin has a roll-up email.
workflow_runs records the quarter's pass.
reflexion_log carries a bid_eligibility_refresh tagged row.

Failure modes

What can go wrong and how to recover

No stale specs found

A clean quarter. The summary email still sends so the nutrition admin sees the all-clear.

Cert validation API down

External cert validation (kosher cert lookup) occasionally fails. The workflow stages those specs with severity=warn instead of confirming pass/fail.

Nutrition admin out

Email still drafts; Mike can re-route or hold. No SLA on individual stale specs unless a bid is pending.

Adjacent workflows + diagrams

For developers

Code paths + invariants

Concern	Where
Workflow contract	workflow_definitions WHERE workflow_type='quarterly_bid_eligibility_refresh'
Cron	0 9 1 /3 America/New_York
Stale criterion	updated_at < 180d OR (kosher='Y' AND kosher_certificate IS NULL)
Owner	Nutrition Admin
Reflexion tag	bid_eligibility_refresh
Risk level	2
Expected duration	~90 min
Trigger	cron · 0 9 1 /3 (America/New_York)

// Stale spec detection const stale = await db.prepare( "SELECT id, item_code, brand FROM spec_items " + "WHERE deleted_at IS NULL AND (" + " updated_at < datetime('now','-180 days') " + " OR (kosher='Y' AND kosher_certificate IS NULL))" ).all();