# Bid Center Architecture

Engineering reference for the 4th lifecycle pillar. Sister docs:
SO_LIFECYCLE.md, PO_LIFECYCLE.md, WO_LIFECYCLE.md.

ADR: ADR-037 (Accepted 2026-05-26).
Migration: `migrations/schema/136_bid_center_pillar.sql`.
Sync: `scripts/sync-bid-center-to-d1.js`.
Source of truth: `~/Desktop/GFS-NetSuite-Cloudflare/source-documents/Customer-Pricing/`.

---

## 1. The 4 Pillars

| Pillar | Lifecycle | Owner table(s) | When it fires |
|---|---|---|---|
| Sales Order (SO) | transactional | `transactions`, `so_lines` | Per-order, real-time |
| Purchase Order (PO) | transactional | `transactions` (type=PurchOrd) | Per-order, real-time |
| Work Order / Assembly (WO) | transactional | `work_orders`, `assembly_builds` | Per-build, on demand |
| **Bid Center** | **contractual** | **`bid_customers`, `bid_external_pipeline`** | **Continuous quoting; annual July 1 anchor** |

Why Bid Center is not folded into SO: a bid is a *commitment to a price at a future date*. It has no inventory effect, no GL impact, no fulfillment leg. It binds future SOs to a price. The lifecycle is months-long (logged → submitted → awarded → bound to future SOs through SY end) where SO/PO/WO are days-to-weeks.

---

## 2. The 8 D1 Tables

All declared in migration 136. FKs are documented in comments — D1 does not enforce them; the sync script and HITL gates do.

### `bid_customers` (target: ~69 active rows)
Mirrors `Customer-Pricing/Commercial_Price_Quote/Customers/<Folder>/customer.json`. Unique key: `customer_name`. Carries `version` (e.g. `SY2627-v5.40`), `archived` flag, `ns_customer_id` (FK to `customers.id`, nullable until match), and `source_json_blob` for round-trip safety. Invariants: customers are NEVER deleted, only archived (`archived=1`).

### `bid_items` (target: ~141 rows — 127 commercial + 14 commodity)
One row per `item_code` (globally unique). Mirrors `commercial_items.json` (8 brands) + `commodity_items.json` (3 Melt Mates sections). Commodity-specific columns (`section`, `usda_value_per_lb`, `commodity_code`, `units_per_case`, `lbs_per_case`) are nullable for commercial rows. `ns_item_id` links to NS items mirror, nullable until match.

### `bid_regional_prices` (target: ~556 rows = 139 items × 4 regions)
Composite PK `(item_code, region, school_year)`. `case_price REAL`. `source` distinguishes `manual` / `synced` / `imported`. `0.0` = N/A in that region (preserved per JSON convention).

### `bid_customer_prices` (target: ~2,240 rows)
The workshop layer: per-customer-per-item overrides. Sourced from each `customer.json`'s `prices` block. `price_source` IN ('regional','bid_override','negotiated','program_allowance'). `bid_id` links back to a specific `bid_external_pipeline` row when the price came from a bid. `version_locked` ties the override to a customer version (`SY2627-v5.40`).

### `bid_external_pipeline` (target: ~44 rows)
Mirrors `External_Bids/bids_log.json`. `bid_id` unique (pattern `EXT-YYYY-NNN`). `status` IN ('logged','submitted','pending_review','awarded','lost','declined','overdue','withdrawn'). `attachments_json` lists files stored in R2 (`gfs-bid-attachments/<bid_id>/...`). `source_email_id` ties back to `email_log` when the bid arrived at `bids@ai-globalfoodsolutions.co`.

### `bid_reviews` (target: ~23 rows)
Mirrors `Bid_Reviewer/*.md`. `body_md` stored verbatim. `content_hash` (sha-256) for change detection. `vectorize_doc_id` pre-populated as `bid_review:<review_id>` — Agent O (knowledge corpus build per ADR-035) will embed these on next rebuild.

### `bid_programs` (target: ~2 rows, growing)
Mirrors `Programs/HPS_MAP_SY*.md`. `allowance_per_case_json` is a parsed map like `{"RS8627": 2.5}`. HPS MAP SY2627 (renewal pending Mike approval per ADR memo) is the canonical seed.

### `bid_price_snapshots` (target: ~32 rows, append-only)
Mirrors `Price_History/*.json`. Snapshot every customer pricing event. `trigger_event` IN ('pre_bid_update','july_1_rollover','manual_snapshot','customer_archive','pre_program_renewal','imported'). `prior_snapshot_id` self-refs to chain a customer's history.

### Indexes
Every lookup column (customer_name, item_code, brand_prefix, region, school_year, status, due_date, bid_id, captured_at) is indexed. Composite indexes on `(customer_id, item_code)` and `(item_code, region, school_year)` carry the hot paths.

---

## 3. The 11 Workflow Contracts

All registered in `workflow_definitions` via `INSERT OR REPLACE`. Each follows the v2 contract shape (trigger → load context → preconditions → fan-out → post-actions → verify → retry). Risk scale: 1=read-only, 2=customer-scoped write, 3=touches NS items/customers, 4=batch with broad blast radius.

| # | workflow_type | Risk | Source SKILL.md |
|---|---|---|---|
| 1 | `bid_center_new_customer_onboarding` | 2 | CREATE_NEW_CUSTOMER.md |
| 2 | `bid_center_update_customer_pricing` | 2 | ADD_UPDATE_CUSTOMER_PRICING.md |
| 3 | `bid_center_add_or_update_item` | 3 | ADD_UPDATE_ITEM.md |
| 4 | `bid_center_fill_regional_prices` | 2 | FILL_REGIONAL_PRICES.md |
| 5 | `bid_center_rebuild_regional_sheets` | 1 | REBUILD_REGIONAL_SHEETS.md |
| 6 | `bid_center_compare_customer_quotes` | 1 | COMPARE_CUSTOMER_QUOTES.md |
| 7 | `bid_center_log_external_bid` | 2 | LOG_EXTERNAL_BID.md |
| 8 | `bid_center_bid_pipeline_review` | 1 | BID_PIPELINE.md |
| 9 | `bid_center_archive_customer` | 3 | ARCHIVE_CUSTOMER.md |
| 10 | `bid_center_system_health_check` | 1 | SYSTEM_HEALTH_CHECK.md |
| 11 | `bid_center_prepare_next_school_year` | 4 | PREPARE_NEXT_SCHOOL_YEAR.md |

**#11 is the big one.** It batches 69 `proposed_actions` under one `batch_id`, one per active customer, each carrying the proposed NS named-price-list write. Mike approves the batch (or individual rows) in `/admin-dashboard.html`; only approved rows fire `ns_push`. Per-customer isolation: one failure doesn't block the rest. Verify checks confirm at +24h that every approved row pushed, all customer versions bumped, and regional sheets rebuilt.

---

## 4. Bid-Season vs. July-1-Rollover Modes

**Bid-season mode (Feb–Jun, also Sep–Dec for spring solicitations):**
Continuous low-volume writes. New bids arrive at `bids@ai-globalfoodsolutions.co` → workflow #7 fires → bid logged. Reviewers run #8 weekly. Price changes per customer flow through #2 with mandatory snapshot. Items added via #3 trigger #4 (regional fill) and #5 (sheet rebuild). Risk-2 writes throughout.

**July-1-rollover mode (annual, June 1–July 15):**
The system pivots. June 1 cron-reminder fires → Mike invokes #11 → batch stages 69 proposed_actions → HITL approval window opens → on approval, NS writes fire in parallel (per-customer isolation, max 10 concurrent). Mike's authoring surface (Customer-Pricing/ JSON) is rewritten on approval. New SY's regional sheets rebuilt. Prior SY moves to archive folders.

---

## 5. Customer + SY-Version Threading

The combination `(customer_name, version)` — e.g. `('Driscoll Foods', 'SY2627-v5.40')` — is the equivalent of a customer PO# for bid work. Every `bid_customer_prices` row has `version_locked`. Every `bid_price_snapshots` row has `version_label`. The `version_log` in the source JSON is preserved in `source_json_blob` for round-trip; the topmost (newest) `note` is mirrored to `bid_customers.last_note` for at-a-glance scanning.

When workflow #2 fires, it always:
1. Snapshots prior state (trigger_event=pre_bid_update).
2. Applies the price delta.
3. Bumps the version (minor or major per inputs).
4. Prepends a `version_log` entry to the JSON blob.

This makes "what was Driscoll paying for RS8627 on 04/30/2026?" a single SQL query: `SELECT case_price FROM bid_customer_prices WHERE customer_id=X AND item_code='RS8627' AND version_locked='SY2627-v5.27'` (or use snapshots for richer point-in-time).

---

## 6. NetSuite Integration Boundaries

**Read from NS (already mirrored):**
- `customers` (linked via `bid_customers.ns_customer_id`)
- `items` (linked via `bid_items.ns_item_id`)
- `transactions` (for revenue-by-customer joins in bid_pipeline_review)

**Write to NS (only via proposed_actions, HITL-gated):**
- `ns_item_upsert` — workflow #3 only.
- `ns_customer_inactivate` — workflow #9 only (when Mike opts in).
- `ns_named_price_list_write` — workflow #11 only (the batch case).

There is no direct NS write from any chat tool or workflow runner in this pillar. ADR-031's HITL invariant is enforced as a precondition check (`feature_flag.ns_named_price_list_writes='on'`) in workflow #11.

---

## 7. Email Intake — bids@ai-globalfoodsolutions.co

Workflow #7 (`bid_center_log_external_bid`) trigger_type is `email_intake`. The CF Email Service handler routes mail addressed to `bids@` into the pillar's intake function (owned by another agent in `src/email.ts`). Parsed fields populate a draft `bid_external_pipeline` row; attachments R2-upload to `gfs-bid-attachments/<bid_id>/`. Mike approves the staged proposed_action (`create_bid_review`) to trigger the markdown analysis pass (workflow #8 follow-up).

Mailbox config is owned by the email agent. This pillar consumes only the inbound rows.

---

## 8. HITL Invariants (ADR-031 extension)

Every write-back to NS or business state passes through `proposed_actions`. Mike is always the loop step. In Bid Center specifically:

- Workflow #3 stages `ns_item_upsert` — Mike approves before NS items write.
- Workflow #9 stages `ns_customer_inactivate` when opted in.
- Workflow #11 stages 69 `ns_named_price_list_write` rows under one `batch_id`.
- Workflows #2 / #4 / #7 are D1-only writes; HITL is the chat conversation itself (Mike confirms the inputs).

The runner does not bypass this even for low-risk operations. The proposed_action row is the audit trail.

---

## 9. July 1 Rollover Runbook

**T-30 (June 1):** Cron reminder fires.
**T-7:** Mike invokes `bid_center_prepare_next_school_year` via chat.
**T-7+5min:** Workflow runs preconditions (sync_fresh, all_69_have_version, all_69_have_prices). Blocks if any fails.
**T-7+10min:** Snapshots 69 customers (trigger_event=july_1_rollover).
**T-7+15min:** Stages 69 proposed_actions under one batch_id. Email Mike: "batch ready in /admin-dashboard.html".
**T-7 to T-0 (Mike approval window):** Mike reviews. Three modes:
  - Batch-approve all → 69 NS writes queue.
  - Approve subset → only those queue.
  - Reject row → that customer holds; Mike can re-trigger later.
**T-0 (July 1):** Approved rows fire to NS via `NS_PUSH_QUEUE` (concurrency 10). Per-customer try/catch isolates failures. Bumps `bid_customers.version` on success.
**T-0+1h:** Workflow #5 fires (rebuild regional sheets for new SY).
**T-0+24h:** Verify checks run. Alerts on any unpushed approved row.

**Retry:** `max_attempts=3, backoff=exponential`. After final failure, per-customer alert to Mike (not blocking the rest).

**Rollback:** Snapshots from `trigger_event=july_1_rollover` are the rollback source. A `bid_center_revert_sy_rollover` workflow (TBD) would re-INSERT the snapshot's `item_prices_json` back into `bid_customer_prices`.

---

## 10. Open TBDs

- **ns_customer_id matching pass.** 70 bid_customers rows need to be matched against ~2,300 NS customers. Match by entityid + companyname fuzzy. Unmatched rows flagged for Mike.
- **ns_item_id matching pass.** 141 bid_items rows against NS items mirror.
- **Phase 2 — Worker-side sync.** ADR-002 sync.sh dependency applies. Promote `sync-bid-center-to-d1.js` to a Worker `scheduled()` handler reading Dropbox API.
- **Email intake parser.** `src/email.ts` agent owns the bids@ mailbox parser; this pillar consumes parsed rows but the parser itself is upstream.
- **Vectorize ingestion of bid_reviews.** Per ADR-035, the next `build-knowledge-corpus.mjs` run should pick up the 23 review rows. Confirm `source_type='bid_review'` is added.
- **Customer-health signal addition.** Once bid_external_pipeline is mirrored, `bid_decline_signal` (declining bid volume in the last 90d) can join the customer-health weights per ADR-036.
- **`bid_center_revert_sy_rollover` workflow.** Rollback procedure for #11 — currently manual.
- **Programs allowance math.** `bid_programs.allowance_per_case_json` is captured but not yet wired to price calculation. Workflow #2 should auto-compute net price when a program is active.
- **Reverse sync.** D1 is read-mirror today. Phase 2 should write back to Customer-Pricing/ JSON on Mike approval so the Dropbox folder stays current as the human authoring surface.