ID-130 {130.3} TECH — Procurement / Form Domain Model + IA

⚠️ PARTIALLY SUPERSEDED (S462, 2026-07-11) — DR-038 + ID-145. The C/Hybrid workspace-umbrella half of this ratified model (PRODUCT B-1, B-3, B-7, B-19, B-22, B-23; TECH AD-2, T-B23 — procurement = a workspaces umbrella holding many forms, procurement_workspaces roll-up) is superseded by DR-038 (S452) + the owner ruling of 2026-07-11: a procurement item IS one form instance. The per-form half (PRODUCT B-2, B-4..B-6, B-8..B-16, B-20, B-21; TECH AD-1, AD-3, AD-4) remains the canonical keeper machinery. Rework owner: ID-145 — specs/id-145-procurement-form-first/RESEARCH.md. Do not cite the umbrella invariants in new specs.

TECH — Procurement / Form Domain Model + Information Architecture

Status: RATIFIED — 25/06/2026 (S411). Authored S412 by a FRESH Planner (Q-PLANNER-2 / B4 — not the {130.2} PRODUCT author) against the RATIFIED PRODUCT.md (B-1..B-25, status banner P1–P4) and RESEARCH.md (§6.0 D1–D8, §2.2 DB-verified schema), plus a fresh code-intelligence + live-DB orientation pass (zjqbrdctesqvouboziae, 25/06/2026). Revised S411 per the consolidated fidelity + staff-engineer review and Liam’s two ratified decisions. UK English throughout.

Liam’s ratified decisions (S411):

1. Win-rate / final-award form-type set = {itt, tender, bid, rfp} (P2 re-ratified to the wider set — bid/rfp are win/lose final-award forms too); gateway/shortlist set = {psq, questionnaire, checklist}. This set is now data-driven via the new form_outcome_types CV (AD-4), not hardcoded — AD-2, AD-4, and T-B7 all read the CV’s counts_toward_win_rate flag, with no remaining internal contradiction.
2. form_type key = psq, label "Selection Questionnaire (SQ/PSQ)".

What this doc owns (the questions PRODUCT deferred): the form-engagement storage home (D6, B-2/B-23), the roll-up materialisation call (B-7), the workflow config-vs-code single-source-of-truth call (B-11), the pqq→psq CV migration (B-13/P4), the per-stage outcome mechanism (the form_outcome_types CV, AD-4), the ordered migration plan (staging-first, Unit-E learnings), and a one-to-one Proposed change per invariant with file:line landings + blast radius.

Companion artefacts: PRODUCT.md (behaviour invariants — referenced by ID, never restated here), RESEARCH.md (decision register + evidence base). {130.4} PLAN decomposes the Proposed changes below into implementation Subtasks.

How to use this doc

§Context grounds the plan in the live code/DB (cited verbatim from the orientation pass so the implementer never re-runs it). §Architecture decisions resolves the four deferred questions with justification. §Proposed changes maps one entry per PRODUCT invariant (T-B1..T-B25) onto file:line landings + blast radius. §Migration plan gives the ordered, staging-first DDL. §Testing maps each invariant to a verification step. Read PRODUCT.md for what the behaviour is; this doc is how and where.

---

Context

What is being built

The platform conflates a procurement (umbrella engagement) with a form (the individual PSQ/ITT/tender artefact). Today a procurement IS a workspaces row and one workspace can physically hold only one tender — buyer/deadline/outcome/workflow-state all live in workspaces.domain_metadata JSONB. PRODUCT ratified D1 (Candidate C / Hybrid): the workspace becomes an umbrella holding many forms; per-stage facts move to the form; the workspace exposes a derived roll-up. This TECH spec encodes that altitude shift in schema + code + IA, landing before ID-45 GO (RESEARCH §8) so the first-client re-ingest populates the correct altitude.

Code-intelligence orientation (gitnexus /Users/liamj/Documents/development/canonical + ast-dataflow + live DB, 25/06/2026 — cited verbatim)

Note on repo handle: the gitnexus index registers this codebase under the absolute path /Users/liamj/Documents/development/canonical (three checkouts share the canonical name; the dev working tree is …/procurement). The implementer passes the absolute path to disambiguate; the index was 4 commits behind HEAD at orientation time (607c8c1a) — re-run bun run gitnexus:analyze if symbol lines drift.

Live-DB facts (Platform zjqbrdctesqvouboziae, 25/06/2026 — empirically verified, supersede RESEARCH §2.2 counts where they differ):

Fact	Verified value
Live procurement workspaces	12 (was 11 at RESEARCH time — model is count-independent)
form_templates rows	0 (schema-present, data-empty)
procurement_workspaces rows	0 (empty satellite; columns id, workspace_id, created_at, updated_at only)
form_questions rows	30, keyed to workspace_id; NO form_template_id column (verified information_schema.columns = 0)
form_responses rows	6 (form-altitude; the win-rate join threads through these)
form_templates outcome/submission_date columns	0 (neither column exists — verified)
form_types CV	8 rows: bid, checklist, itt, pqq, questionnaire, rfp, sales_proposal_template, tender; 7 procurement-applicable (all except sales_proposal_template); pqq label = "PQQ (Pre-Qualification Questionnaire)"

Symbol orientation (gitnexus query/context/impact + grep/ast):

• Workflow module lib/domains/procurement/procurement-workflow.ts — exports canTransition (73), getAvailableTransitions (80), isTerminal (86), isActive (90), PROCUREMENT_WORKFLOW_PROGRESSION (95), the label/colour records (7–55); transition table is module-private VALID_TRANSITIONS (57). Re-exports PROCUREMENT_WORKFLOW_STATES + ProcurementWorkflowState from types/procurement.ts. isTerminal is hardcoded ['won','lost','withdrawn'] (87) — the workflow terminal set, NOT the per-form outcome set.
• canTransition blast radius — impact upstream = CRITICAL (6 direct callers, 5 processes): app/api/procurement/[id]/outcome/route.ts:POST (67), …/questions/extract, …/questions/match, app/api/procurement/[id]/route.ts:PATCH, and the cron queue app/api/cron/process-queue/route.ts:GET (8 affected sub-processes). Any change to the workflow’s home/signature ripples through the whole procurement API + the queue handler.
• getAvailableTransitions blast radius = LOW (1 direct caller: app/procurement/[id]/page.tsx).
• Outcome write surface app/api/procurement/[id]/outcome/route.ts:POST (20–200): reads the workspace via application_types!inner(key='procurement') (46–51), parses ProcurementMetadataSchema (60), validates canTransition(currentStatus, outcome) (67), writes {outcome, outcome_notes, outcome_recorded_at, outcome_recorded_by} back into workspaces.domain_metadata + workspaces.status (79–90). This is the per-stage outcome write that B-9 re-anchors to the form.
• Win-rate engine is entirely SQL-side. app/api/analytics/win-rate/route.ts:GET (54) is a thin pass-through that .rpc('get_aggregate_win_rate_stats') (62). The denominator logic lives in public.get_aggregate_win_rate_stats() (squash-baseline 2020–2089; Unit-E migration 20260624130000 re-aliased unique_bids→unique_procurements, bid_outcome→outcome). Its CTE joins citations cc → form_responses br → form_questions bq → workspaces w and reads w.domain_metadata->>'outcome' (baseline line 2033). public.get_content_win_rate(p_content_item_id) (per-item; pre-M6 squash-baseline signature — the live param is p_q_a_pair_id post id-131 regen 20260706150000, and the per-item body was rewritten by {130.7}) follows the same pattern — its body is at squash-baseline line 2248 (line 513 is the api INVOKER wrapper, not the body); both public.* bodies are wrapped by api.* INVOKER entrypoints (20260623140000). The roll-up (B-7) must re-thread this join through the form-engagement outcome; the win-rate denominator is the set of forms whose form_outcome_types.counts_toward_win_rate=true (the CV encodes the ratified {itt,tender,bid,rfp} final-award set — AD-4), NOT an inlined form_type IN (...) list.
• form_questions re-key blast radius (.from('form_questions') consumers, ast/grep): ~24 TS sites — lib/domains/procurement/{procurement-export-data,draft-response}.ts, lib/mcp/{resources,tools/procurement,tools/shared}.ts, lib/queue/handlers/procurement-draft-all.ts, ~16 app/api/procurement/[id]/** routes, plus scripts/{catalogue-from-instance,export-user-data,seed-procurement-test-data}.ts. SQL consumers (verified squash-baseline grep): 7 functions — get_aggregate_win_rate_stats, get_content_win_rate, get_form_question_stats, get_form_question_stats_batch, get_form_summary, hybrid_search, search_for_form_response (the procurement-detail aggregates at baseline 2915–2977 read form_questions WHERE workspace_id = …).
• form_templates consumers (~10 TS sites): app/api/procurement/[id]/route.ts (GET umbrella surface, 26–132), the …/templates/** route group, scripts/catalogue-from-instance.ts, scripts/generate-api-views.ts (introspects the api.form_templates view).
• form_type UI surface = zero hits (gitnexus + grep across components/procurement/ + app/procurement/) — confirms B-16’s infer+confirm picker is net-new IA, not a relocation.
• pqq code sites = exactly 2 (verified): lib/coverage/gap-scoring.ts:26-31 (TEMPLATE_TYPE_WEIGHTS — already keys both sq:10 and pqq:10) and components/coverage/template-coverage-content.tsx:112-123 (TEMPLATE_TYPE_LABELS — pqq: 'Pre-Qualification Questionnaire', alongside sq, eqq, gcloud, dps, framework etc.). form_type is NOT an MCP tool arg (no form_type in lib/mcp/tools). DB CV = the one form_types row + 0 form_templates.form_type rows referencing it.
• The 10-state enum is double-written: ProcurementMetadataSchema.status inline (lib/validation/schemas.ts:1022-1033) AND VALID_BID_STATUSES (lib/validation/schemas.ts:2007-2018, feeding ProcurementListParamsSchema.status at 2021). Both are verbatim copies of the workflow module’s 10 states.
• application_types.state_machine_config jsonb exists (squash-baseline 5377) but is unused — no code reads it (grep: only DDL + the api.application_types view select it).

Schema source-of-truth: supabase/types/database.types.ts (auto-generated, never hand-edit; --schema public,api) + supabase/types/database-overrides.ts (JSONB domain overrides — ProcurementMetadataSchema shape lives here as the domain_metadata override). Tool catalogue: .gitnexus/CLAUDE.md, .ast-dataflow/CLAUDE.md. Migration discipline: supabase/CLAUDE.md.

External-library API verification (OQ-3 / Q-EX2): this spec introduces no net-new external-library symbols. All proposed code uses internal Canonical symbols (the workflow module, the auth helper, sb()/tryQuery()), Postgres/Supabase built-ins, and Zod (already pinned + in use at schemas.ts). No import-and-call check has an in-scope target — recorded explicitly so the Checker can verify the step was considered, not skipped.

---

Architecture decisions (resolving PRODUCT’s deferred questions)

Four decisions PRODUCT deferred to TECH, each resolved with justification. These are PROPOSED — Liam ratifies alongside this spec.

AD-1 — Form-engagement storage home: extend form_templates with the per-stage engagement columns (resolves D6, B-2, B-23)

Decision: Add the per-stage engagement facts as first-class columns on form_templates (the form IS the engagement; no separate per-form table, no procurement_workspaces per-form repurpose). New columns: outcome text (FK to the new form_outcome_types.key CV — AD-4), outcome_recorded_at timestamptz, outcome_recorded_by uuid — bare uuid, NO REFERENCES auth.users(id) FK (matches the table-family house style: form_responses.drafted_by is a bare uuid — a hard FK to auth.users would break user-deletion / GDPR flows), outcome_notes text, submission_date timestamptz, workflow_state text (the 10-state per-form workflow, B-8), and a buyer resolution that reuses the existing issuing_organisation column (B-2’s “buyer” = issuing organisation — no new column). deadline already exists on form_templates (becomes canonical per D5).

form_templates is already an INSTANCE table. Despite the name, form_templates holds per-engagement form instances (the reusable requirement catalogue is the separate form_template_requirements table). Adding the engagement columns here is therefore semantically correct, not a layering violation. The misleading name is tracked as deferred debt (Follow-ups: a gitnexus_rename-driven form_templates → form_instances rename, NOT in this Task).

Migration step 1 documents the 3 orthogonal axes via COMMENT ON TABLE form_templates: status = ingest/analysis-pipeline lifecycle (existing); workflow_state = the procurement 10-state workflow (B-8); outcome = per-stage resolution (B-5). These never collapse into one column.

Why form_templates, not a new table or the satellite:

• Lowest-friction landing (RESEARCH §9 guardrail). form_templates already owns form_type, deadline, issuing_organisation, evaluation_methodology, status, status_reason as first-class columns and the AI/matching stack + pipeline already write it. Adding 5–6 columns to a 0-row table is a pure ALTER TABLE ADD COLUMN — no data backfill, no FK re-point on the form side.
• A separate form_engagements table would be premature normalisation. One form = one engagement (1:1, never 1:many) — there is no cardinality that a satellite buys us. A separate table adds a JOIN to every read path (the ~24 form_questions consumers + the win-rate engine) for zero modelling gain.
• form_templates.status collision is real but avoidable. form_templates.status is the analysis-pipeline lifecycle (uploaded/analysing/analysed/… — CHECK at baseline 6619), NOT the 10-state procurement workflow. We therefore name the new workflow column workflow_state (not status) to avoid overloading the existing column. This keeps the pipeline-analysis status and the procurement-workflow state as distinct axes (mirrors the outcome-is-a-separate-axis-from-workflow principle, B-5/B-8).

Satellite fate (B-23): procurement_workspaces becomes the workspace roll-up store (see AD-2) — activated, not dropped. It gains roll-up columns and one row per procurement workspace; the per-form facts do NOT live here.

Column-naming note: outcome on form_templates is the per-stage outcome (B-5), whose permissible value set is stage-appropriate (AD-4 below) — it is NOT constrained to the workflow terminal set. workflow_state is the 10-state machine value.

AD-2 — Roll-up materialisation: materialised on procurement_workspaces, pipeline/trigger-maintained (resolves B-7)

Decision: Activate procurement_workspaces as the materialised roll-up store. Add roll-up columns: nearest_deadline timestamptz, overall_outcome text, counts_toward_win_rate boolean, rollup_updated_at timestamptz. Maintain them via a Postgres trigger on form_templates (AFTER INSERT/UPDATE/DELETE of the engagement columns) that recomputes the parent workspace’s roll-up row, plus a one-shot recompute function callable from the pipeline.

Why materialised, not computed-on-read:

• Preserves the existing read surface cheaply. The buyer-facing list (app/procurement/page.tsx) and the win-rate engine read per-workspace aggregates today. A materialised column is a single indexed read; computed-on-read forces a correlated subquery over form_templates on every list render.
• The win-rate engine is SQL-side and already aggregate-shaped. get_aggregate_win_rate_stats already GROUPs; pointing its outcome source at a materialised overall_outcome + counts_toward_win_rate flag is a clean rewrite (T-B7 below) vs. inlining the final-award-stage derivation into every win-rate query.
• Trigger maintenance is bounded. Per-workspace recompute touches only that workspace’s forms (≤ a handful per engagement). The trigger fires on the engagement-column writes, which are low-frequency (state transitions + outcome recording), not on every question/response edit.

Derivation (verbatim from PRODUCT B-7, encoded in the recompute fn — reads the form_outcome_types CV (AD-4) for the stage/denominator classification, never an inlined form_type list):

• nearest_deadline = MIN(deadline) across non-terminal forms (workflow_state NOT IN ('won','lost','withdrawn')); NULL when all terminal or no forms.
• overall_outcome: the final-award-stage form = latest form joined to a form_outcome_types row with stage='final_award' (the CV’s {itt,tender,bid,rfp} set), ordered by deadline (tie-break created_at). won ⇒ that form won; lost ⇒ that form lost OR the engagement withdrawn OR any shortlist-stage form (CV stage='shortlist' → {psq, questionnaire, checklist}) resolved not_shortlisted (P3 shortlist-failure ⇒ lost); in_progress while no terminal final-stage outcome.
• counts_toward_win_rate (P2 denominator) = the engagement reached a form whose form_outcome_types.counts_toward_win_rate=true (i.e. a stage='final_award' form) with a terminal won/lost outcome. Shortlist-stage losses (counts_toward_win_rate=false in the CV) set overall_outcome='lost' but do NOT enter the win-rate denominator — tracked as the separate shortlist pass-rate (see T-B7).

Trade-off acknowledged: materialisation risks staleness if a write path bypasses the trigger (e.g. a raw SQL backfill). Mitigation: the recompute function is idempotent and re-runnable; a CI/guard integration test asserts roll-up consistency after a transition (T-B7), and the migration runs a full recompute after the initial mint.

AD-3 — Workflow single source of truth: code-resident in procurement-workflow.ts; de-dup the two schemas.ts copies to import from it (resolves B-11)

Decision: Keep the workflow code-resident. The canonical state set is PROCUREMENT_WORKFLOW_STATES (exported from @/types/procurement, re-exported by the workflow module) + the exported predicates (canTransition/getAvailableTransitions/isTerminal/ isActive); the transition table VALID_TRANSITIONS is module-private (procurement-workflow.ts:57) and stays so — it is not part of the public surface. De-dup by deleting the two inline copies in schemas.ts and importing the canonical const directly from @/types/procurement (no barrel):

• ProcurementMetadataSchema.status (schemas.ts:1022-1033) → z.enum(PROCUREMENT_WORKFLOW_STATES).
• VALID_BID_STATUSES (schemas.ts:2007-2018) → deleted; ProcurementListParamsSchema.status (2021) consumes z.enum(PROCUREMENT_WORKFLOW_STATES) directly.

Why code-resident, NOT config-driven (application_types.state_machine_config):

• The config column is unused dead infrastructure (orientation: zero readers). Wiring the workflow to read+parse+validate a JSONB config at runtime is net-new complexity for a state machine that is stable, small (10 states), and changes only via a deliberate ratification cycle (B-8 forbids state changes in this Task).
• z.enum needs a compile-time tuple. Driving Zod validation from a runtime DB column would force a runtime-built schema or a parallel hardcoded fallback — defeating the single-source goal. A const tuple the schemas import is the genuine single source.
• Blast-radius safety. canTransition is CRITICAL-risk (6 callers + the queue). Keeping the machine where its callers already import it (no signature/home change) is the lowest-risk de-dup — we only delete duplicates and re-point two imports, we do NOT move the machine. state_machine_config stays unused (a no-op cleanup candidate noted in Follow-ups, not removed here — out of scope per B-25).

AD-4 — pqq → psq rename + the per-stage outcome value sets (resolves B-13/P4, B-5)

pqq→psq (RATIFIED P4): a controlled-vocabulary rename, not a data migration (0 form_templates.form_type='pqq' rows). Scope (exactly, from orientation):

1. DB CV: UPDATE public.form_types SET key='psq', label='Selection Questionnaire (SQ/PSQ)' WHERE key='pqq'; Because form_templates.form_type has 0 rows there is no FK fan-out to migrate. (If a FK constraint form_templates.form_type → form_types.key exists it is ON UPDATE-safe with 0 child rows; the migration verifies and, if absent, this is moot.) Also rewrite any applicable_application_types references (none — it is a scalar text[] of application keys, not form-type keys; verified).
2. Code site 1 lib/coverage/gap-scoring.ts:26-31 — TEMPLATE_TYPE_WEIGHTS already has sq:10; replace the pqq:10 entry with psq:10 (keep sq:10 for back-compat with any legacy sq string). Net: { sq:10, psq:10, itt:7, rfp:7 }.
3. Code site 2 components/coverage/template-coverage-content.tsx:112-123 — TEMPLATE_TYPE_LABELS: replace pqq:'Pre-Qualification Questionnaire' with psq:'Selection Questionnaire (SQ/PSQ)'; the existing sq:'Selection Questionnaire' entry stays. (The stale gcloud/dps/framework/eqq label keys are NOT in the live CV — out of scope; left untouched, they are harmless label fallbacks.)

psq vs sq (RATIFIED P4): stored key = psq (the Procurement Act 2023 supplier-selection stage is the PSQ in restricted/CFP procedures; “SQ” is the umbrella term). User-facing label carries both — "Selection Questionnaire (SQ/PSQ)" — so the IA reads correctly regardless of a buyer’s local terminology. One CV key, satisfying B-13’s “align label to current UK terminology”.

Per-stage outcome mechanism (B-5) — a new form_outcome_types CV table, NOT a hardcoded CHECK. Rather than freeze a form_type-aware CHECK (which would re-bake the stage→outcome mapping into DDL and re-hardcode the form-type sets in three SQL functions), the per-stage outcome value set is data-driven by a new controlled-vocabulary table mirroring the form_types pattern:

CREATE TABLE public.form_outcome_types (
  key text PRIMARY KEY,
  label text NOT NULL,
  stage text NOT NULL CHECK (stage IN ('shortlist','final_award')),
  applicable_form_types text[] NOT NULL,
  counts_toward_win_rate boolean NOT NULL,
  provenance text NOT NULL DEFAULT 'core'
);
-- Seed (the ratified sets):
-- ('shortlisted','Shortlisted','shortlist','{psq,questionnaire,checklist}',false)
-- ('not_shortlisted','Not shortlisted','shortlist','{psq,questionnaire,checklist}',false)
-- ('won','Won','final_award','{itt,tender,bid,rfp}',true)
-- ('lost','Lost','final_award','{itt,tender,bid,rfp}',true)

• Validation of form_templates.outcome: an FK form_templates.outcome → form_outcome_types.key (any seeded value is structurally valid) plus an app-layer FormOutcomeSchema (Zod, discriminated on form_type, new in schemas.ts, importing PROCUREMENT_WORKFLOW_STATES for the workflow_state field) that enforces the stage-appropriate subset (a psq form may only carry a stage='shortlist' outcome), plus an optional lightweight trigger that cross-checks form_type ∈ applicable_form_types on write. No frozen CHECK — adding a new outcome value or shifting a form-type’s stage is a CV row edit, not a migration.
• Value names kept: shortlisted / not_shortlisted (shortlist stage) and won / lost (final-award stage). not_shortlisted is the P3 shortlist-failure that resolves the engagement to lost (AD-2) without entering the win-rate denominator (counts_toward_win_rate=false).
• withdrawn is a workflow_state terminal (B-8), not an outcome value — a withdrawn form has workflow_state='withdrawn' and outcome=NULL. This makes the outcome-is-a-separate-axis-from-workflow-state principle (B-5/B-9) concrete.

Why a CV, not inlined lists (the ripple that kills the prior contradiction): the win-rate denominator and the roll-up now read form_outcome_types.counts_toward_win_rate / stage — so get_aggregate_win_rate_stats, get_content_win_rate, AND the recompute_procurement_rollup fn share one source for the {itt,tender,bid,rfp} final-award set, instead of three hardcoded copies. The P2 set is therefore data-driven and consistent by construction across AD-2 / AD-4 / T-B7.

The audit fields (outcome_recorded_at/by) tighten from .optional() to required-on-terminal (B-9) — enforced in the write path (T-B9), with the columns themselves NULLable at the DB level (NULL until a terminal outcome is recorded).

---

Proposed changes (one entry per PRODUCT invariant)

Each T-Bn maps onto PRODUCT invariant B-n. File:line landings are orientation-sourced; blast radius noted where non-LOW.

Section A — Entity model

T-B1 — Procurement = workspace umbrella holding many forms. No schema change to workspaces (it already carries identity-only + domain_metadata; the per-stage fields move OUT). The umbrella read surface app/api/procurement/[id]/route.ts:GET (26–132) changes from “read the single tender’s facts off domain_metadata” to “read the roll-up off procurement_workspaces + list child forms off form_templates”. Guardrail: no tenant_id column introduced. Blast: GET route + list page; MEDIUM (read-shape change).

T-B2 — Form owns per-stage buyer/deadline/outcome/workflow. ALTER TABLE form_templates ADD COLUMN per AD-1: outcome text REFERENCES form_outcome_types(key) (the CV — AD-4), outcome_recorded_at timestamptz, outcome_recorded_by uuid (bare uuid, no auth.users FK — AD-1 house-style rationale), outcome_notes text, submission_date timestamptz, workflow_state text NOT NULL DEFAULT 'draft'. buyer reuses issuing_organisation; deadline already present (canonical per D5). Add the COMMENT ON TABLE documenting the 3 axes (AD-1). Regenerate api.form_templates view (it must SELECT the new columns — see Migration plan step 10) + types. 0-row table → pure ADD COLUMN, no backfill (the 12 minted forms in T-B22 get workflow_state via COALESCE(domain_metadata->>'status','draft')).

T-B3 — 1:many cardinality, cascade delete. Add form_templates.workspace_id is already NOT NULL FK to workspaces; ensure ON DELETE CASCADE (verify current FK action; the squash baseline FK — confirm + alter to CASCADE if not). Deleting a workspace cascades to its form_templates → form_questions (after T-B4 re-key) → form_responses. Verify existing FK action before altering.

T-B4 — form_questions re-keyed to a form id. ALTER TABLE form_questions ADD COLUMN form_template_id uuid REFERENCES form_templates(id) ON DELETE CASCADE; Backfill: with one minted form per workspace (B-22), UPDATE form_questions fq SET form_template_id = ft.id FROM form_templates ft WHERE ft.workspace_id = fq.workspace_id; (30 rows, deterministic 1:1). Keep workspace_id for now (denormalised convenience read; the win-rate join can use either) — do NOT drop it this Task (the ~24 TS consumers + 7 SQL functions read it; dropping is a separate de-risk). Regenerate api.form_questions view + types. Blast: ~24 TS + 7 SQL consumers (get_aggregate_win_rate_stats, get_content_win_rate, get_form_question_stats, get_form_question_stats_batch, get_form_summary, hybrid_search, search_for_form_response) read form_questions; HIGH — additive column keeps them green, but new per-form reads must filter on form_template_id. UNIQUE scope: the existing UNIQUE(workspace_id, question_text) is retained this Task (with one form per workspace it is equivalent to per-form uniqueness); a follow-up re-scopes it to UNIQUE(form_template_id, question_text) when workspace_id is dropped. Guardrail: q_a_pairs stay corpus-level — this re-key is form_questions-only.

T-B5 — Per-form outcome, multi-valued. Implemented by AD-4’s form_outcome_types CV table (new) + the form_templates.outcome FK + FormOutcomeSchema (Zod, discriminated on form_type)

• an optional cross-check trigger. No single frozen enum across stages — the value set is a CV row edit. New CV table + seed + new Zod schema in schemas.ts.

T-B6 — Per-form outcome audit provenance. outcome_recorded_at/outcome_recorded_by columns (T-B2), written as a side-effect of the recording action in the write path (T-B9) — not user-typed. Re-anchors the schemas.ts:1043-1044 audit semantics to the form. Carried verbatim from the workspace surface.

T-B7 — Workspace roll-up + the win-rate denominator rewrite (the riskiest SQL change). Per AD-2: activate procurement_workspaces with roll-up columns + a recompute fn + trigger. The win-rate engine rewrite is the load-bearing change:

• get_aggregate_win_rate_stats() (and get_content_win_rate(), body at squash-baseline 2248) currently read w.domain_metadata->>'outcome' via citations → form_responses → form_questions → workspaces. Rewrite the CTE to resolve outcome through the form: join form_responses br → form_questions fq → form_templates ft, read ft.outcome, and join form_outcome_types fot ON fot.key = ft.outcome — the win-rate denominator is fot.counts_toward_win_rate = true with a terminal won/lost outcome (P2 — the CV encodes the ratified {itt,tender,bid,rfp} final-award set; no inlined form_type IN (...)). Add a separate shortlist pass-rate aggregate (forms joined to fot.stage='shortlist', numerator ft.outcome='shortlisted').
• Postgres 42P13 hazard (Unit-E learning): get_aggregate_win_rate_stats is a RETURNS TABLE function and the rewrite adds a column (the shortlist pass-rate) → a return-shape change. CREATE OR REPLACE CANNOT change a RETURNS TABLE output signature → must drop the dependent api.get_aggregate_win_rate_stats wrapper first, then DROP FUNCTION public.get_aggregate_win_rate_stats(), then CREATE, then recreate the api.* wrapper. get_content_win_rate: its BODY must change (it reads the decommissioned domain_metadata outcome) but its return shape is unchanged ⇒ a plain CREATE OR REPLACE is sufficient — no DROP-wrapper dance needed for this one. The route app/api/analytics/win-rate/route.ts types (DomainStats/OverallStats 11–30, WinRateStatsSchema 41–49) extend with the shortlist fields. *Blast: CRITICAL — touches the citation win-rate aggregator (dashboard + MCP formatContentEffectiveness

• app/api/items/[id]/effectiveness/route.ts). Staging-first mandatory; generate-api-views --check must pass.*

Section B — Workflow

T-B8 — 10-state workflow on the form, per stage. The workflow_state column (T-B2) holds the per-form state. The workflow MODULE is unchanged (states/transitions verbatim per B-8); only its subject moves from workspaces.status to form_templates.workflow_state. No change to procurement-workflow.ts itself.

T-B9 — Submission-date + terminal-outcome side-effects, re-anchored to the form. Rework app/api/procurement/[id]/outcome/route.ts:POST (and the transition write in …/route.ts:PATCH): the transition now targets a form (the route gains a form id, or operates on the workspace’s single v1 form), writes form_templates.workflow_state, and on submitted sets form_templates.submission_date = now(), on terminal sets {outcome, outcome_recorded_at, outcome_recorded_by} atomically. Audit fields become required-on-terminal (B-9) — enforced in the write before the state commit. Use sb() / tryQuery() (never raw client); verify row-count post-UPDATE (REST PATCH silent-no-op gotcha). After the write, fire the roll-up recompute (T-B7 trigger handles it, or call explicitly). Blast: outcome route is a canTransition caller (CRITICAL set); change the write target, NOT canTransition’s signature.

T-B10 — Transitions validated against the live table. canTransition / getAvailableTransitions unchanged; now invoked per form (the currentStatus source becomes form_templates.workflow_state instead of workspaces.status). Verbatim behaviour preserved.

T-B11 — Single source of truth for the workflow enum. Per AD-3: delete VALID_BID_STATUSES (schemas.ts:2007-2018); rewrite ProcurementMetadataSchema.status (1022-1033) and ProcurementListParamsSchema.status (2021) to z.enum(PROCUREMENT_WORKFLOW_STATES) imported from @/types/procurement (direct file import — no barrel). Blast: ProcurementMetadataSchema impact = LOW (consumed by parseProcurementMetadata 1049 only). state_machine_config stays unused (Follow-ups).

Section C — form_type closed list + surfacing

T-B12 — Canonical 7-key procurement closed list. No DDL beyond the AD-4 key rename. The closed list (post-rename) = bid, checklist, itt, psq, questionnaire, rfp, tender. The picker’s option list is fetched at runtime from api.form_types (filtered to 'procurement' = ANY(applicable_application_types)) via TanStack Query — so the CV stays the single source of truth and a future CV add/remove needs no code change. The TS side keeps only a minimal Zod enum (for request-body validation where a compile-time tuple is required) — NOT a second hand-maintained option list. No framework/dps/gcloud re-added; no duplicated CV source.

T-B13 — pqq→psq CV migration. Per AD-4 step 1–3. Blast: 2 code sites + 1 CV row + 0 form rows; LOW, fully enumerated.

T-B14 — Each form carries exactly one form_type. form_templates.form_type already exists (NULLable for pre-classification per the column comment). After B-16 confirmation it is required at the application layer (the create/confirm path rejects a form without a confirmed type); DB stays NULLable to allow the infer-pending state. Application-layer required, DB NULLable by design.

T-B15 — Mixed form types per procurement over its lifetime. No constraint preventing sibling forms of different types (the 1:many FK + per-form form_type already allows it). The detail surface (T-B19) lists each form’s type. No schema constraint needed — emergent from the model.

T-B16 — Infer-then-confirm form_type picker (net-new IA). Classification source: the upload/create path classifies the document (reuse the existing form-classification the pipeline already runs to populate form_templates.form_type — orientation confirms the pipeline writes form_type; the UI path calls the same classifier). New picker component in components/procurement/ (semantic tokens only, WCAG label+selected-state, UK labels from the B-13 review). Pre-selects the inferred type; records the confirmed choice as form_templates.form_type. Confirm-first: no downstream behaviour keys on an unconfirmed inference (any speculative pre-fetch is a latency optimisation, not depended-on). TanStack Query for the form_types option fetch + the confirm mutation; keys in lib/query/query-keys.ts. Net-new surface; no relocation.

Section D — Information architecture + copy

T-B17 — Singular nav noun “Procurement”. IA copy change at app/procurement/page.tsx (ProcurementListRow 337–382) + nav surfaces + back-nav “Back to Procurement”. Resolves ID-61 PJ-1. Copy/IA, not mechanical rename.

T-B18 — Persona/helper copy rephrase (PJ-2..PJ-5). page.tsx:155 rephrase (PJ-2); keep “bid-writing” craft term (PJ-3); “Past responses” card (PJ-4); the mcp-apps-local Bid* type names (PJ-5) — TECH naming call: retain Bid* as form_type-scoped names where they describe the bid form_type legitimately (they are not the umbrella), rename only where they denote the umbrella. UK English throughout. Folds ID-61 Unit F copy per UNIT-F-SCOPE-MAP; NOT a standalone sweep.

T-B19 — Procurement detail surface lists its forms. Net-new form-list level in the detail surface (app/procurement/[id]/): list each form with form_type, workflow_state, deadline, outcome. Selecting a form opens its composer (T-B20). Umbrella shows the roll-up (T-B7); form shows per-stage facts. Single-form v1 case still renders one-item list. Net-new multi-form navigation; the live UI has no form-list level today.

T-B20 — Composer re-anchors to the form. The composer renders inside a form’s question-review surface, scoped to that form’s questions (via form_template_id, T-B4). Match candidates still come from corpus-level q_a_pairs (guardrail — no corpus partition). TanStack Query keys re-scope from workspace to form. Re-anchor of the existing composer; data layer keys change.

T-B21 — Q&A import + UC5 promotion re-anchor to the form, corpus unchanged. Imports still land corpus-level (guardrail). The NET-NEW addition: associate promotion lineage with a form_template_id — i.e. the corpus q_a_pair provenance records both source_workspace_id (exists, nullable) AND the originating form_template_id. Add a nullable q_a_pairs.source_form_template_id uuid provenance column (corpus stays corpus-level — this is lineage, not partition). Lost/withdrawn responses default skip-on-promotion (preserved). Additive nullable provenance column; promotion UI shape stays deferred (B-25).

Section E — Migration shape

T-B22 — Near-trivial data migration; mint one form per workspace. The data migration (in the same combined migration): for each of the 12 live procurement workspaces, INSERT INTO form_templates one row lifting workspaces.domain_metadata onto the minted form, with these explicit transforms:

• issuing_organisation ← domain_metadata->>'buyer'; deadline ← domain_metadata->>'deadline'; submission_date ← domain_metadata->>'submission_date'.
• workflow_state ← COALESCE(domain_metadata->>'status','draft') (live status is NULL on the current workspaces — the COALESCE guarantees the NOT NULL column).
• outcome / withdrawn transform: when domain_metadata->>'outcome' = 'withdrawn' set workflow_state='withdrawn' and outcome=NULL (withdrawn is a workflow terminal, not an outcome — AD-4); only {won,lost} values lift into form_templates.outcome; audit fields (outcome_recorded_at/by) lift alongside a lifted {won,lost} outcome.
• form_type of the minted form: the live workspaces carry no form_type signal (domain_metadata->>'form_type' is absent) — set it from any available classification signal, else the documented default bid (the generic procurement final-award form_type; it is in the {itt,tender,bid,rfp} final-award set, so a minted form with a won/lost outcome correctly enters the win-rate denominator — reconciled with the T-B7 parity assertion).

Then T-B4 backfills form_questions.form_template_id to that form. deadline canonicalises to the form (D5); workspaces.domain_metadata per-engagement fields are deprecated (left in place this Task to avoid breaking any unmigrated reader — NOT dropped here, to keep the migration reversible; concrete drop trigger in Follow-ups). 12-row INSERT + 30-row UPDATE; deterministic.

T-B23 — Satellite fate resolved. Per AD-2: procurement_workspaces activated as the roll-up store (one row per procurement workspace, minted in the migration), NOT dropped, NOT per-form. Resolves D6.

Section F — Scope

T-B24 — Subsumes ID-61 Unit F + deferred wire-field cleanup. Unit F copy folds into T-B17/T-B18 (per UNIT-F-SCOPE-MAP, not a mechanical rename). The deferred ID-61 wire-field cleanup (active_bids/bid_summary wire fields, the now-renamed bid_outcome→outcome alias already done in Unit-E 20260624130000, stale /api/bids JSDoc, --bid-* tokens bl-349, persisted-literal classes bl-370) is cleaned where the surfaces above touch them — not as a separate program. Opportunistic cleanup within the touched surfaces.

T-B25 — Out of scope (no change). Per-method scoring, promotion-UI detail, q_a_pair corpus model, cocoindex standup (ID-127), question_matches internals (ID-57), tenant_id. The state_machine_config column removal is also out of scope (left unused — Follow-ups). Scope boundary held.

---

Migration plan (ordered, staging-first)

One combined migration (or a tight sequence) per supabase/CLAUDE.md discipline. DDL via CLI only (supabase migration new + db push foreground) — never MCP execute_sql/apply_migration for DDL. Staging-first on branch rbwqewalexrzgxtvcqrh (its own cached DB password — run db push WITHOUT -p; generate-api-views.ts needs a local/connected DB). Verify cat supabase/.temp/project-ref before any push (drift → relink to the platform ref).

Ordered steps:

1. form_outcome_types CV table + seed (T-B5/AD-4). CREATE TABLE public.form_outcome_types (per AD-4) + the 4 seed rows + api.form_outcome_types view + grants + REVOKE … FROM anon where applicable. This lands FIRST so step 2’s form_templates.outcome FK has a target.
2. form_templates engagement columns + 3-axis COMMENT (T-B2/AD-1). ALTER TABLE form_templates ADD COLUMN the engagement columns (outcome FK → form_outcome_types(key); outcome_recorded_by bare uuid; workflow_state text NOT NULL DEFAULT 'draft'; etc.); COMMENT ON TABLE form_templates documenting status/workflow_state/outcome axes. Optional cross-check trigger for form_type ∈ applicable_form_types. All new PL/pgSQL gets SET search_path = public, extensions.
3. form_questions.form_template_id FK (T-B4). ADD COLUMN … REFERENCES form_templates(id) ON DELETE CASCADE. Verify/alter the form_templates.workspace_id + form_questions cascade actions (T-B3). Retain UNIQUE(workspace_id, question_text) (T-B4 note).
4. procurement_workspaces roll-up columns (T-B7/T-B23). ADD COLUMN nearest_deadline, overall_outcome, counts_toward_win_rate, rollup_updated_at.
5. q_a_pairs.source_form_template_id provenance (T-B21). ADD COLUMN … uuid NULL REFERENCES form_templates(id).
6. pqq→psq CV rename (T-B13). UPDATE form_types …. (Pre-check for an FK from form_templates.form_type; with 0 rows it is safe regardless.)
7. Roll-up recompute fn + trigger (T-B7). CREATE FUNCTION recompute_procurement_rollup(p_workspace_id uuid) (SET search_path, REVOKE EXECUTE … FROM anon) — reads form_outcome_types for the stage/denominator classification; idempotent INSERT … ON CONFLICT (workspace_id) DO UPDATE so it can run before OR after the rows exist. AFTER trigger on form_templates engagement-column writes.
8. Win-rate engine rewrite (T-B7) — the 42P13-sensitive step. get_aggregate_win_rate_stats (return-shape change: + shortlist pass-rate) → drop api.* wrapper first, then DROP FUNCTION public.get_aggregate_win_rate_stats(), then CREATE, then recreate the wrapper. get_content_win_rate (body-only change, return shape unchanged) → plain CREATE OR REPLACE (no DROP dance). Both rewritten bodies join form_outcome_types (no inlined form_type list). Qualify all column refs (Unit-E param-collision learning).
9. Data migration (T-B22). For each of the 12 procurement workspaces: INSERT the procurement_workspaces roll-up row FIRST (or rely on the step-7 UPSERT), mint one form_templates row (with the T-B22 transforms incl. the withdrawn→workflow_state and the bid default form_type), UPDATE form_questions.form_template_id (30 rows), then run recompute_procurement_rollup for all 12 (the UPSERT makes ordering safe either way). Transient note: there is NO valid win-rate output between steps 8 and 9 — the rewritten functions read form_templates.outcome, which is empty until this backfill completes; defer any win-rate validation until after step 9.
10. Regenerate api.* views for form_templates, form_questions, procurement_workspaces, form_outcome_types (new columns/table must appear) via bun scripts/generate-api-views.ts (needs connected DB); run generate-api-views --check — must pass (no drift).
11. Regenerate types: /opt/homebrew/bin/supabase gen types typescript --project-id <platform-ref> --schema public,api > supabase/types/database.types.ts; update database-overrides.ts for the new form_templates engagement shape + the FormOutcomeSchema types. Never hand-edit database.types.ts.
12. RLS: the new columns inherit form_templates / form_questions / procurement_workspaces table RLS (delegate-to-workspaces EXISTS pattern, role-based via get_user_role()); form_outcome_types is a public read-only CV (same RLS posture as form_types). New functions get explicit REVOKE EXECUTE … FROM anon + grants to authenticated/service_role. Verify procurement_workspaces RLS exists (confirm via pg_policy).
13. Code changes (T-B9/B11/B16/B19/B20 etc.) land after the schema + types are green, so TS binds against the regenerated types.

Sequencing constraint: the whole migration lands before ID-45 GO (RESEARCH §8) so the re-ingest populates the form altitude. generate-api-views --check + bun run test are the PR-blocking gates.

---

Risks and mitigations

• Win-rate SQL rewrite (T-B7) is the highest-risk change. It feeds the dashboard, MCP formatContentEffectiveness, and app/api/items/[id]/effectiveness/route.ts. Mitigation: staging-first + a synthetic-fixture parity integration test. A pre/post snapshot against live data is vacuous — all 12 live workspaces carry NULL outcome/status, so the citation→outcome join is a 0-row degenerate (both old and new return zeros; the live snapshot proves nothing). Instead the test seeds a fixture: one won itt/tender form + one not_shortlisted psq form (each with a citing form_response + citation), then asserts (a) the won final-award form counts in BOTH numerator and denominator; (b) the not_shortlisted shortlist form sets overall_outcome='lost' with counts_toward_win_rate=false and is ABSENT from the win-rate denominator; (c) it appears in the separate shortlist pass-rate aggregate. The test states explicitly that the live snapshot is degenerate and the fixture is the real coverage.
• Postgres 42P13 on RETURNS TABLE change (Unit-E precedent). Mitigation: explicit DROP-dependent-wrapper-then-DROP-then-CREATE ordering (step 7), not CREATE OR REPLACE.
• form_templates.status vs workflow_state overload. Mitigation: AD-1 names the new column workflow_state (never reuses status); a guard test asserts the two are distinct.
• form_questions.workspace_id retained alongside form_template_id risks readers using the wrong key. Mitigation: additive-only this Task (no drop); per-form reads MUST filter on form_template_id; a follow-up de-risks the drop once all ~24 consumers migrate.
• Roll-up staleness if a write bypasses the trigger. Mitigation: idempotent recompute fn
  • consistency integration test + full recompute in the migration.
• Proxy publicRoutes allowlist: if the picker (T-B16) or form-list (T-B19) adds a new API route, confirm it is auth-gated (it is — getAuthorisedClient); no new public route is introduced, so no proxy.ts publicRoutes change.
• Schema parity prod ↔ staging: the schema-parity side workflow + task-view-vendor-drift reminder apply since ledger-adjacent schemas change; run staging-first and verify parity.

---

Testing and validation

Each PRODUCT invariant maps to a verification step (bun run test, never bun test; behaviour-first per test-philosophy.md; shared Supabase mock):

• B-1/B-3 — integration: create a procurement workspace, add 2 forms, delete the workspace → both forms + their questions/responses gone; delete one form → sibling + workspace intact.
• B-2/B-5/B-6/B-9 — integration: record a terminal outcome on a form → form_templates outcome/outcome_recorded_at/outcome_recorded_by/submission_date populated atomically; audit fields rejected as missing on terminal (required-on-terminal); withdrawn sets workflow_state not outcome; dual-writer guard: after T-B9 re-anchors the write, assert NO writer remains on the deprecated workspaces.domain_metadata->>{outcome,deadline, submission_date,status,outcome_recorded_*} keys (grep-guard test — prevents a split-brain dual-writer hazard).
• B-4 — integration: re-key 30 questions; “questions for form X” returns only X’s questions, never a sibling form’s; q_a_pairs unaffected (corpus guardrail).
• B-7 — synthetic-fixture integration (the load-bearing test — NOT a live-snapshot parity, which is vacuous against the all-NULL live data; see Risks): seed a won itt/tender form + a not_shortlisted psq form; assert (a) the won form counts in numerator+denominator, (b) the shortlist loss sets overall_outcome='lost' + counts_toward_win_rate=false and is ABSENT from the denominator, (c) it appears in the separate shortlist pass-rate; roll-up nearest_deadline/overall_outcome/counts_toward_win_rate derived correctly per the AD-2 rule (reading form_outcome_types); roll-up consistency after a transition (no staleness).
• B-8/B-10 — unit: procurement-workflow.ts 10 states + transition table + isTerminal verbatim (guard test against a committed snapshot); illegal transition rejected, form stays put.
• B-11 — unit: PROCUREMENT_WORKFLOW_STATES is the single source; schemas.ts imports it; grep for a second inline copy / VALID_BID_STATUSES returns zero.
• B-12/B-13/B-14 — unit + DB: closed list = the 7 keys post-rename; form_types has psq not pqq; gap-scoring.ts + template-coverage-content.tsx carry psq; a form requires a confirmed form_type at the application layer.
• B-16 — component/integration: picker pre-selects the inferred type, single-click confirm, override from the 7-key list, no-document → no pre-selection (never silent-assign), nothing downstream keys on an unconfirmed inference; WCAG label+selected-state; UK labels.
• B-15/B-19/B-20 — integration/component: detail surface lists mixed-type forms with per-stage facts; selecting a form opens its composer scoped to its questions; match candidates from corpus-level q_a_pairs.
• B-17/B-18 — copy/snapshot: nav noun singular “Procurement”; “Back to Procurement”; PJ-2..PJ-5 rephrases; UK English.
• B-21 — integration: promotion records q_a_pairs.source_form_template_id lineage; corpus stays corpus-level; lost/withdrawn default skip.
• B-22/B-23 — migration test (staging): 12 forms minted, 12 roll-up rows, 30 questions re-keyed, domain_metadata deadline canonicalised to the form; satellite activated not dropped.

CI gates: generate-api-views --check (no drift), bun run test (full regression), schema-parity side workflow, MCP eval if tool surfaces change (they do not rename here).

---

Follow-ups

• Drop the deprecated workspaces.domain_metadata per-engagement keys (outcome, deadline, submission_date, status, outcome_recorded_*). Concrete trigger: drop them when a grep over app/ + lib/ + supabase/migrations/ (excluding the deprecation migration itself) returns zero readers of those keys AND the win-rate parity test has been green for one release. Target: PRE-GO (before ID-45) — the deprecated keys must not survive into the first-client re-ingest. (Kept in-place this Task only for migration reversibility — T-B22.)
• Drop form_questions.workspace_id once all ~24 TS consumers + the 7 SQL functions (T-B4) filter on form_template_id; re-scope UNIQUE(workspace_id, question_text) → UNIQUE(form_template_id, question_text) at the same time (additive-only this Task — T-B4).
• Rename form_templates → form_instances (gitnexus_rename-driven, NOT find-and-replace) — the table holds per-engagement instances, not templates (the catalogue is form_template_requirements). Deferred debt, NOT in-Task (AD-1).
• Remove the unused application_types.state_machine_config column (dead infrastructure — AD-3; out of scope per B-25).

---

End of TECH — RATIFIED 25/06/2026 (S411). {130.4} PLAN decomposes the T-Bn Proposed changes + the ordered 13-step Migration plan into implementation Subtasks (the form_outcome_types CV + form_templates engagement columns, the win-rate SQL rewrite, the data migration, the workflow de-dup, the picker, the form-list IA, and the copy fold as chain-dependent slices).