Form-Extraction Subsystem — TECH

Form-Extraction Subsystem — TECH

Type: TECH (implementation plan). Companion to PRODUCT.md (26 numbered behaviour invariants, ratified S273 by Liam) and RESEARCH.md (empirical grounding) in this same directory. This document translates PRODUCT intent into a concrete implementation plan against the live KH codebase + Supabase schema.

Task: ID-52 (form-extraction). Subtask: {52.3}. Authored S274 by a fresh Planner context (distinct from {52.1} RESEARCH and {52.2} PRODUCT authors). UK English throughout.

Inherited hard gates (from docs/research/s273-canonical-pipeline-finals/id52-final.yaml tech_phase_flags):

1. cocoindex.ExtractByLlm + cocoindex.LlmSpec ABSENT in cocoindex==1.0.3 (RESEARCH §8). All form-classification work uses custom @coco.fn decorators; the S234 feedback-findings direction is SUPERSEDED.
2. Per-format reader pins: pdfplumber==0.11.9 (Python), exceljs@4.4.0 (TS), mammoth@^1.12.0 + turndown@^7.2.4 + docx@^9.6.1 (TS); pdf-parse + xlsx ABSENT from package.json; legacy .xls has NO installed reader (out of automated scope per Inv-3).
3. TS-vs-Python placement, the folder→workspace convention mechanism, the CV-loader fix direction, the Inv-16 idempotency mechanism, and the Inv-21 Path-C surface are TECH decisions — committed below, not left open.

Schema canonical source: supabase/types/database.types.ts (auto-generated, never hand-edited). Row shapes via Tables<'x'> / QueryData<>; JSONB column domain types via supabase/types/database-overrides.ts.

---

1. Context

1.1 What’s being built

Path B (deterministic per-format readers, Python-side, fully pipeline-owned) + Path C (human-confirmed cataloguing skill, TS-side Claude Plugin Skill) for the form-extraction subsystem (Mode-3). The pipeline ingests blank {PDF, XLSX, DOCX} forms from a folder→workspace-mapped localfs source, extracts every question as a form_template_fields row (with row_index / col_index / table_index / word_limit / section_name / field_type / fill_status / placeholder marking preserved), and writes the workspace-scoped form_templates instance row. Path C promotes confirmed instance fields into the global form_template_requirements catalogue, generating requirement_embedding (vector(1024)) for T10 matching to read.

Behaviour is owned by PRODUCT.md Inv-1 through Inv-26 — referenced by number, not restated.

1.2 Current-state file inventory (line anchors)

Live, retained:

• scripts/cocoindex_pipeline/flow.py:791-934 — ingest_file @coco.fn(memo=True) component (Stages 2→6 per source item). Today mounts three targets only: content_items, q_a_extractions, source_documents (lines 1012-1029). NO form_templates / form_template_fields mount → Mode-3 orphan.
• scripts/cocoindex_pipeline/flow.py:919-934 — current lossy q_a_extractions write (drops expected_response_kind / evaluation_criteria / evidence_requirements / scope_tags from each QAPair). Out of scope for ID-52 per OQ-52-LOSSY ratification → tracked as separate Task ID-54.
• scripts/cocoindex_pipeline/extraction.py:121-177 — FormMetadata + QAPair + QAFormExtraction Pydantic models. form_metadata is built but unwired in flow.py (Mode-3 orphan, half of which this TECH fixes).
• scripts/cocoindex_pipeline/extraction.py:121-142 — hard-coded 11-value form_type: Literal[...] in FormMetadata (one of the three drift-prone sources; Inv-1 lockstep).
• scripts/cocoindex_pipeline/adapters.py:39-79 — convert_binary_to_markdown outer fn + Docling/pullmd/passthrough inner tier. Path A markdown-conversion surface — retained; Path B reads the raw bytes alongside, NOT the markdown (PRODUCT R2 ratification).
• scripts/extract_tender_questions.py (507 lines) — DOCX table extractor; canonical _QUESTION_HEADERS set; _classify_header heuristic. Reused under a new wrapper module (§2.4 Path B DOCX).
• scripts/analyse_template.py (363 lines) — DOCX merged-cell + placeholder + word-limit
  • section-heading + tracked-changes detector. Reused under the new wrapper.
• scripts/extract_pdf_text.py (67 lines) — minimal helper; superseded by the new pdfplumber-based reader (§2.4 Path B PDF).
• supabase/types/database.types.ts:1519 form_template_fields, 1597 form_template_requirements, 1686 form_templates, 1755 form_types — all live; see RESEARCH §3.
• supabase/migrations/20260416102457_pre_squash_reconciliation.sql:4133-4135 — current CHECK constraints: field_type ∈ {empty_cell, placeholder, highlighted}, fill_status ∈ {pending, filled, skipped, failed}, mapping_status ∈ {unreviewed, confirmed, rejected, manual, unmapped}.
• supabase/migrations/20260416102457_pre_squash_reconciliation.sql:4192 — form_templates.mime_type CHECK is currently DOCX-only (application/vnd.openxmlformats-officedocument.wordprocessingml.document). Must be widened to accept PDF + XLSX MIME types (§2.5 Migration M1).
• lib/ontology/schemas.ts:23 — STATUS_VALUES = ['active','planned','needed'] — blocks 4 APPLIED-S{NNN} files. Fix direction in §2.6.
• lib/ontology/loader.ts:43-76 — loadOntologyCVs() throws fatally on first invalid file. Inv-1 gate hinges on this loading successfully.
• scripts/tests/fixtures/taxonomy_snapshot.json — dual-source canonical for Python-side CV reads (per CLAUDE.md “Taxonomy dual-source”). Has content_types; does NOT yet have form_types — added by §2.6 Migration M2.

Live, retired by this build:

• app/api/procurement/[id]/templates/[templateId]/analyse/route.ts (143 lines) — the app-side enqueue route. Retired per PRODUCT R3 / Inv-6 (Option A: fully pipeline-owned write). The processing-queue job_type: 'template_analyse' consumer (if any built one) is also retired. Retirement Subtask scoped separately (the sub-orchestrator opens it under ID-52 after PLAN authoring; this TECH names the surface
  • the test deletions so the retirement is well-bounded).
• __tests__/api/procurement/[id]/templates/[templateId]/analyse/route.test.ts (if present) — deleted with the route.
• Inv-24 still gates app-side mutating actions on getAuthorisedClient(['admin','editor'])
  • authFailureResponse(auth). The Path-C confirmation step retains that gate (§2.7); the ingest write runs under the pipeline service identity (no getAuthorisedClient — pipeline binds via service-account UUID a0000000-0000-4000-8000-000000000001, the CLAUDE.md-mandated value).

1.3 Ownership boundaries

Concern	Side	Module
Folder→workspace resolution	Python pipeline	scripts/cocoindex_pipeline/workspace_resolver.py (new)
Path B raw extractors (PDF, XLSX, DOCX)	Python pipeline	scripts/cocoindex_pipeline/form_extractors/ (new package)
form_templates + form_template_fields write	Python pipeline	scripts/cocoindex_pipeline/flow.py (extended)
Path C cataloguing skill (human-confirmed)	TS / Claude Plugin Skill	.claude/skills/catalogue-form-requirements/ (new)
form_template_requirements write + embedding	TS (Path-C-driven script)	scripts/catalogue-from-instance.ts (new)
CV-loader gate fix	TS	lib/ontology/schemas.ts + 4 .md edits + Python snapshot
App-side analyse route	RETIRED	n/a

---

2. Proposed changes

2.1 Folder→workspace resolution mechanism (PRODUCT Inv-4 / Inv-5)

Mechanism: a single JSON manifest at the root of the ingest source folder.

The pipeline reads <COCOINDEX_SOURCE_PATH>/.kh-workspace-map.json once at flow start (extends app_main in scripts/cocoindex_pipeline/flow.py):

{
  "schema_version": 1,
  "mappings": [
    { "path_prefix": "phew-procurement/", "workspace_id": "<uuid>" },
    { "path_prefix": "acme-bids/2026/",   "workspace_id": "<uuid>" }
  ]
}

Resolution rule (deterministic per Inv-4):

• For each ingested file’s rel_path (the localfs path relative to COCOINDEX_SOURCE_PATH, already computed via file.file_path.path.as_posix() — flow.py:853), find the longest matching path_prefix. The associated workspace_id is the resolved workspace.
• Ambiguous prefixes (two mappings of equal length) → resolution failure (Inv-5: never silent default).
• No matching prefix → resolution failure.
• Missing or unparseable manifest at flow start → flow aborts with a structured manifest_missing / manifest_invalid error (surfaced through the existing flow-end webhook + _emit_stage_error_log); the form path does not run.

Why a manifest, not a path-pattern convention: the alternative (mapping each top-level folder name 1-to-1 to a workspace) couples the ingest filesystem to workspace UUIDs and would silently degrade if a folder were renamed. The manifest is explicit, version- controlled inside the ingest source, and supports nested-prefix mappings (one client may maintain multiple workspaces under one folder hierarchy).

Why JSON, not a per-folder workspace.json sidecar: one read at flow start vs N reads per ingest is materially simpler and lets the resolver behave deterministically without filesystem-walk-order dependencies.

Validation: the manifest is parsed against a Zod-equivalent Pydantic model (WorkspaceManifest) on load. The workspace UUIDs are NOT verified against the live workspaces table at manifest-load time (FK enforcement at INSERT time gives a canonical error if a UUID is stale; verifying on load adds DB round-trips for no additional safety).

File added: scripts/cocoindex_pipeline/workspace_resolver.py (~80 LOC). Exports load_workspace_manifest(path) -> WorkspaceManifest and resolve_workspace(manifest, rel_path) -> WorkspaceId | ResolutionFailure.

Maps to PRODUCT: Inv-4, Inv-5, Inv-6 (resolution is the precondition for the pipeline-owned write).

2.2 Per-format reader assignment (PRODUCT Inv-2, Inv-3)

Path B lives entirely in Python, alongside the existing cocoindex pipeline. Rationale:

• All three readers (pdfplumber, openpyxl for XLSX, python-docx for DOCX) are Python; the prior art (extract_tender_questions.py, analyse_template.py) is Python; cocoindex Stage-6 row declarations are Python.
• Crossing the TS/Python boundary mid-flow (e.g. spawning Node from Python, or moving the write to TS while keeping extraction Python) introduces orchestration overhead and a second auth surface for no benefit — the form_templates write target is a plain Supabase table any side can write to, but the pipeline already holds an asyncpg pool (DB_CTX, flow.py:1012-1029) under managed_by=ManagedBy.USER.
• The TS-side libraries (exceljs, mammoth, turndown) remain available for Path C (TS-side, app-context) and any UI-side preview — they are not used in Path B itself.

Reader matrix:

Format	Reader	Pin	Module
PDF	pdfplumber	==0.11.9 (already in requirements.txt)	scripts/cocoindex_pipeline/form_extractors/pdf.py (new)
XLSX	openpyxl	(available in the Python environment; pin explicitly as a direct dependency in requirements.txt for Path B)	scripts/cocoindex_pipeline/form_extractors/xlsx.py (new)
DOCX	python-docx	(already in requirements.txt)	scripts/cocoindex_pipeline/form_extractors/docx.py (new)
.xls (legacy)	—	—	NOT IMPLEMENTED (Inv-3, manual pre-convert)

Why openpyxl, not exceljs: the corpus inspection (RESEARCH §2.2, §2.3) was done with openpyxl and reads the EFA merged cells (42 ranges) + CSP merged cells (23 ranges) cleanly. Adding exceljs would require either a Python→Node bridge (rejected for complexity) or moving the entire XLSX extractor TS-side (rejected for split ownership). The exceljs@4.4.0 pin in package.json remains available for any TS-side need (Path C preview UI; not used by Path B).

Why pdfplumber, not pdf-parse: pdf-parse is ABSENT from package.json (RESEARCH §8) and would require adding a JS dep for a Python-side pipeline. pdfplumber==0.11.9 is already pinned, and it is what read the SQ PDF as 57 pages (the 8-page container artefact) cleanly in RESEARCH §2.1.

Why python-docx, not mammoth: the prior-art extract_tender_questions.py already uses python-docx; reusing avoids re-deriving the _QUESTION_HEADERS heuristic and the merged-cell / placeholder detection in TS.

Per-reader shape (each form_extractors/<format>.py module exports):

class ExtractedField(BaseModel):
    """One field row destined for form_template_fields."""
    model_config = ConfigDict(strict=True, extra="forbid")

    question_text: str | None      # placeholder → None; authored → text
    placeholder_text: str | None   # placeholder → text; authored → None
    field_type: Literal["empty_cell", "placeholder", "highlighted"]
    fill_status: Literal["pending", "filled", "skipped", "failed"]
    row_index: int | None          # None for non-tabular regions
    col_index: int | None
    table_index: int | None
    section_name: str | None
    sequence: int                  # reading-order position within the form
    word_limit: int | None
    reference_urls: list[str]      # preserved per Inv-14

class ExtractedForm(BaseModel):
    form_metadata: FormMetadata    # reuses extraction.py:121 — extended fields tolerated
    fields: list[ExtractedField]

async def extract(raw_bytes: bytes, filename: str) -> ExtractedForm: ...

Reuse of existing DOCX prior art: form_extractors/docx.py imports _classify_header, _QUESTION_HEADERS, _detect_merged_cells, _is_empty_or_placeholder, _extract_word_limit, _extract_section_headings, _has_tracked_changes from the existing scripts under a thin compatibility wrapper. The existing scripts/analyse_template.py:1-50 placeholder regex set is folded into the new module as-is (it already covers [Insert…], {{…}}, <<…>>, n/a, dash/ellipsis sentinels — verified RESEARCH §2.4).

Maps to PRODUCT: Inv-2 (formats), Inv-3 (.xls/HTML out of scope), Inv-7 (form-level metadata via reused FormMetadata), Inv-8 (coordinates), Inv-9 (placeholder vs authored), Inv-10 (mandatory flag), Inv-11 (word limit), Inv-12 (section + sequence), Inv-14 (reference URLs), Inv-15 (full content extent — pdfplumber.open(...).pages honours the true 57-page count).

2.3 Repeated-block dedup (PRODUCT Inv-13)

Mechanism: stable content-hash dedup keyed on (section_name, question_text_normalised) within a single extracted form.

Implementation in form_extractors/xlsx.py:

• After table-walking all sheets, group extracted candidate fields by (section_name, normalise(question_text)) where normalise lowercases, strips whitespace runs, and removes terminal punctuation.
• For each group, keep the first occurrence in reading order; discard subsequent copies.
• Preserve the original coordinates (row_index / col_index / table_index) from the first occurrence; dropped duplicates surface as a count in the extractor’s structured log (not as a field row).

Why per-form dedup, not pipeline-wide dedup: instance fields are workspace-scoped to one form (Inv-25). Two different forms in the same workspace may legitimately share question text (e.g. boilerplate SQ questions across multiple ITTs); dedup must not collapse those into one. Catalogue-level reuse is Path-C’s concern (§2.7).

Verifiable via: the EFA fixture (Bidder 1 ≡ Bidder 2 sheets) produces N fields, not 2N (Inv-13 acceptance fixture). The Charnwood .xls is out of scope, but the companion ITT Services.docx has no such duplication so the heuristic does not over- dedup.

Maps to PRODUCT: Inv-13.

2.4 Path B custom @coco.fn shape (replacing the absent ExtractByLlm)

No LLM call in Path B. Path B is deterministic — the format-specific readers are the extractors. The custom @coco.fn wrapping them is purely orchestration:

@coco.fn(memo=True)
async def extract_form_structure(
    file: "coco.resources.file.FileLike",  # type: ignore[name-defined]
) -> ExtractedForm | None:
    """Path B Stage-3a — raw-format extraction of blank-form structure.

    Returns ExtractedForm when the file is a recognised form-bearing format
    AND the workspace resolves AND the reader succeeds; returns None when
    the file is not form-relevant (e.g. .md content). Resolution and
    extraction failures raise — Inv-17 (failure isolation) catches them at
    the ingest_file boundary so one form's failure does not block the batch.

    @coco.fn(memo=True): unchanged file bytes (cocoindex content_fingerprint)
    skip extraction on the next run — Inv-16 idempotency substrate.
    """
    suffix = file.file_path.path.suffix.lower()
    if suffix == ".pdf":
        return await extract_pdf_form(await file.read(), file.file_path.path.name)
    if suffix == ".xlsx":
        return await extract_xlsx_form(await file.read(), file.file_path.path.name)
    if suffix == ".docx":
        return await extract_docx_form(await file.read(), file.file_path.path.name)
    if suffix == ".xls":
        # Inv-3: legacy .xls out of scope. Log + return None (NOT an error).
        _logger.info(
            json.dumps({"event": "form_extractor.skip", "reason": "xls_out_of_scope",
                        "rel_path": file.file_path.path.as_posix()}))
        return None
    return None

Why no LLM: Path B’s contract is deterministic structural extraction — every cell, row, and merged-block decision is reproducible from the raw bytes. Adding an LLM would introduce nondeterminism for no signal a deterministic reader cannot recover. Form-type classification (FormMetadata.form_type) is filled by the existing Path A extract_qa_form LLM call against markdown, which already infers form type — Path B adopts that value through extraction.py’s FormMetadata (it does not re-classify).

Why @coco.fn(memo=True) instead of a plain async def: memoisation lets cocoindex’s content-fingerprint cache skip re-extraction when a file is unchanged across runs (the substrate for Inv-16 idempotency below).

Error surfacing per Inv-17: the extractor functions raise typed exceptions (FormExtractionError(reason, rel_path, …)) that the caller in ingest_file catches in a try/except block scoped per-file (not flow-wide). On catch: emit _emit_stage_error_log(stage="form_extraction", …) (the existing helper), record a form_template_fields-less form_templates row with status='analysis_failed' IF workspace resolution succeeded (so the failure is visible) — or skip the write entirely if workspace resolution itself failed (Inv-5). Other forms in the same flow continue.

Maps to PRODUCT: Inv-2, Inv-6, Inv-17.

2.5 Pipeline write into form_templates + form_template_fields (PRODUCT Inv-6, Inv-7, Inv-8 et al.)

Two new mount_table_target calls in app_main (scripts/cocoindex_pipeline/flow.py after line 1029, alongside ci_target / qa_target / sd_target):

ft_target = await mount_table_target(
    DB_CTX,
    "form_templates",
    FORM_TEMPLATES_SCHEMA,
    managed_by=ManagedBy.USER,
)
ftf_target = await mount_table_target(
    DB_CTX,
    "form_template_fields",
    FORM_TEMPLATE_FIELDS_SCHEMA,
    managed_by=ManagedBy.USER,
)

Targets are passed positionally to coco.mount_each(ingest_file, source.items(), ci_target, qa_target, sd_target, ft_target, ftf_target) and ingest_file’s signature extended to accept the two new targets. managed_by=ManagedBy.USER preserves the “DDL via Supabase CLI only” rule (the migration in §2.6 owns all DDL; cocoindex writes rows only).

ingest_file extension (one new block after the existing Stage-6 declares):

1. Compute workspace_id = resolve_workspace(MANIFEST, rel_path). On ResolutionFailure, emit _emit_stage_error_log(stage='workspace_resolution', …) and return (NO form_templates write, NO form_template_fields write — Inv-5).
2. Call await extract_form_structure(file). On return None, the file is not a form; exit the form-write block (other Stages-2/3 already ran). On FormExtractionError, declare a form_templates row with status='analysis_failed' and exit (no fields — Inv-17).
3. On success, declare the form_templates row:

form_template_id = uuid.uuid5(_KH_PIPELINE_DOC_NS, f"ft:{rel_path}")
ft_target.declare_row(row={
    "id": form_template_id,
    "workspace_id": workspace_id,
    "created_by": SERVICE_ACCOUNT_UUID,           # a0000000-0000-4000-8000-000000000001
    "name": extracted.form_metadata.form_title or file.file_path.path.stem,
    "filename": file.file_path.path.name,
    "file_size": file.size,                       # cocoindex File.size
    "mime_type": MIME_BY_SUFFIX[file.file_path.path.suffix.lower()],
    "storage_path": rel_path,                     # SAME stable string used elsewhere
    "structure_path": None,                       # populated by Path C later
    "description": extracted.form_metadata.evaluation_methodology,
    "field_count": len(extracted.fields),
    "mapped_count": 0,
    "status": "analysed",
    # M1b dedicated columns (per §2.6d) — written from day one; no packing.
    "form_type": extracted.form_metadata.form_type,
    "deadline": extracted.form_metadata.deadline,
    "issuing_organisation": extracted.form_metadata.issuing_organisation,
    "evaluation_methodology": extracted.form_metadata.evaluation_methodology,
})

4. Iterate the deduped fields (§2.3) and declare each as form_template_fields:

for field in extracted.fields:
    ftf_target.declare_row(row={
        "id": uuid.uuid5(_KH_PIPELINE_DOC_NS,
                         f"ftf:{rel_path}:{field.sequence}"),  # see §2.6 idempotency
        "template_id": form_template_id,
        "question_text": field.question_text,
        "placeholder_text": field.placeholder_text,
        "field_type": field.field_type,
        "fill_status": field.fill_status,
        "row_index": field.row_index,
        "col_index": field.col_index,
        "table_index": field.table_index,
        "section_name": field.section_name,
        "sequence": field.sequence,
        "word_limit": field.word_limit,
        "reference_urls": field.reference_urls,
        # mapping_status defaults 'unreviewed' per CHECK default
        # question_id is filled later by Path C link-back, NOT here
    })

op_id / extractor-version stamping: form_templates and form_template_fields do NOT currently have an op_id column (verified §3.2 / §3.3 in RESEARCH). The pipeline’s per-flow op_id is recorded against pipeline_runs.op_id and the items_created array (existing webhook plumbing in flow.py:1132+). The form-write slice does not introduce a new op_id column in v1 — see Follow-ups.

Maps to PRODUCT: Inv-6 (entire write), Inv-7 (form-level metadata), Inv-8 (coordinates), Inv-9 (placeholder vs authored, via field_type + placeholder_text), Inv-10 (mandatory flag — field_type='highlighted' for the SQ M/O case is captured as the EFA-style flag column; ALSO see §2.5a below for the schema gap), Inv-11 (word limit), Inv-12 (section + sequence), Inv-15 (true content extent).

2.5a Schema gap: mandatory flag column on form_template_fields

The live form_template_fields schema has NO column for the mandatory/optional flag. RESEARCH §3.3 enumerated the columns: field_type ∈ {empty_cell, placeholder, highlighted}, fill_status ∈ {pending, filled, skipped, failed} — neither is a mandatory flag.

Fix (Migration M1, §2.6): add is_mandatory boolean | null to form_template_fields. Null = the form expressed no such status (Inv-10’s “where the form expresses no such status, the field records no mandatory flag rather than defaulting to one”). True/false = explicit value extracted from the form (SQ Annex B M/O).

2.6 Migrations (TWO migrations, one each for schema + ontology snapshot)

Migration M1 — <timestamp>_id52_form_extraction_schema.sql (created via supabase migration new id52_form_extraction_schema):

-- 1. Widen form_templates.mime_type CHECK to include PDF + XLSX (was DOCX-only).
ALTER TABLE public.form_templates
  DROP CONSTRAINT form_templates_mime_type_check;
ALTER TABLE public.form_templates
  ADD CONSTRAINT form_templates_mime_type_check CHECK (
    mime_type IN (
      'application/vnd.openxmlformats-officedocument.wordprocessingml.document', -- DOCX
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',       -- XLSX
      'application/pdf'                                                          -- PDF
    )
  );

-- 2. Add is_mandatory to form_template_fields (Inv-10 substrate).
ALTER TABLE public.form_template_fields
  ADD COLUMN is_mandatory boolean NULL;
COMMENT ON COLUMN public.form_template_fields.is_mandatory IS
  'Explicit mandatory/optional flag from the source form (Inv-10). '
  'NULL = form expressed no such status (NOT defaulted to optional).';

-- 3. Add reference_urls to form_template_fields (Inv-14 substrate).
ALTER TABLE public.form_template_fields
  ADD COLUMN reference_urls text[] NULL;
COMMENT ON COLUMN public.form_template_fields.reference_urls IS
  'External URLs preserved from the source form question / section (Inv-14). '
  'NULL or [] = no reference URLs on this field.';

-- 4. Add ingest_source to form_templates (provenance: pipeline vs app).
ALTER TABLE public.form_templates
  ADD COLUMN ingest_source text NOT NULL DEFAULT 'pipeline' CHECK (
    ingest_source IN ('pipeline', 'app_upload')
  );
COMMENT ON COLUMN public.form_templates.ingest_source IS
  'Provenance of this template row. v1 = pipeline (folder→workspace). '
  'app_upload reserved for the thin UI front-end per OQ-52-UI-UPLOAD-TENSION.';

-- Inv-5 (workspace-resolution failure) surfacing is handled in code via
-- _emit_stage_error_log + zero form_template_fields + zero form_templates rows
-- (see §2.5 step 1). No new form_templates.status CHECK value is added — a
-- 'failed_workspace_resolution' row is schema-impossible because form_templates.workspace_id
-- is NOT NULL and no workspace_id is resolved on this failure path.

No new PL/pgSQL functions, no anon EXECUTE grants needed (this migration is purely ALTER TABLE + CHECK adjustments; no new public.*() functions). RLS predicates are unchanged — the role-based gate on form_templates / form_template_fields remains as set in the pre-squash migration. The pipeline writes via the asyncpg pool under the service-account role (RLS-bypassing where the role grants), per existing precedent (flow.py:1012-1029).

Migration M2 — taxonomy snapshot regen. Not strictly a DDL migration but a fixture update:

• Run bun run sync:taxonomy (per CLAUDE.md taxonomy dual-source) after extending the snapshot to include the form_types table contents (currently missing from scripts/tests/fixtures/taxonomy_snapshot.json per §1.2 audit).
• Manually patch scripts/tests/fixtures/taxonomy_snapshot.json to add a form_types array of {key, label} objects matching the live form_types table rows.
• scripts/cocoindex_pipeline/extraction.py:130-142 FormMetadata.form_type: Literal[...] is replaced by FormMetadata.form_type: str with a field_validator that asserts the value is in the snapshot’s form_types array (matches the existing _load_canonical_content_types pattern at extraction.py:76-86). The Python Literal is no longer the source of truth.

Maps to PRODUCT: Inv-1 (CV lockstep), Inv-7 (form-level metadata persistence), Inv-10 (mandatory column), Inv-14 (reference URLs column), Inv-5 (resolution-failure status).

2.6d Migration M1b — Dedicated form_templates metadata columns

S275 amendment (Liam ratification): v1 lands the schema-clean breakout in ID-52, NOT as a follow-up. Rationale: v1.1 needs the columns regardless; deferring forces a later migration + backfill + consumer rewrite, all avoidable if the columns land before the pipeline writer ever ships. M1 is already committed in {52.7}; M1b is sequenced as a sibling migration in a NEW Subtask {52.M1b} (deps [7]) BEFORE Phase-5 writer impl ({52.12+}) so the writer code targets the proper columns from day one.

Migration M1b — <timestamp>_id52_form_templates_dedicated_metadata_columns.sql (created via supabase migration new id52_form_templates_dedicated_metadata_columns):

-- Add dedicated form-level metadata columns to form_templates.
-- v1.1 promotes facets currently packed into description / structure_path into
-- first-class columns. v1 writes to these from day one (no packing prose).

ALTER TABLE public.form_templates
  ADD COLUMN form_type text NULL REFERENCES public.form_types(key);
COMMENT ON COLUMN public.form_templates.form_type IS
  'FK to form_types.key — the form-type CV value '
  '(matches FormMetadata.form_type per CV-lockstep, TECH §2.6b). '
  'NULL permitted for app_upload rows pre-classification.';

ALTER TABLE public.form_templates
  ADD COLUMN deadline timestamptz NULL;
COMMENT ON COLUMN public.form_templates.deadline IS
  'Form submission deadline parsed from the source form (Inv-7 substrate). '
  'NULL = no deadline expressed.';

ALTER TABLE public.form_templates
  ADD COLUMN issuing_organisation text NULL;
COMMENT ON COLUMN public.form_templates.issuing_organisation IS
  'Issuing-organisation string parsed from the source form (Inv-7 substrate). '
  'NULL = no issuer expressed.';

ALTER TABLE public.form_templates
  ADD COLUMN evaluation_methodology text NULL;
COMMENT ON COLUMN public.form_templates.evaluation_methodology IS
  'Evaluation-methodology string parsed from the source form (Inv-7 substrate). '
  'Replaces the v1-deferred description-packing scheme. '
  'NULL = no methodology expressed.';

-- Partial index on form_type for downstream filters (T10 / observability).
CREATE INDEX IF NOT EXISTS idx_form_templates_form_type
  ON public.form_templates (form_type) WHERE form_type IS NOT NULL;

No backfill needed — v1 writer ({52.12}) ships these columns populated from day one; pre-v1 rows (none in staging beyond fixture data) carry NULL safely.

Writer adaptation: §2.5 step 3 already writes to all four columns directly (no packing prose). The pre-amendment “form_type / deadline / issuing_organisation go into description+structure_path” code comment is REMOVED by this amendment.

Maps to PRODUCT: Inv-7 (form-level metadata persistence — first-class columns instead of packed description). Inv-1 (CV lockstep — form_type FK gives DB-level referential integrity to form_types.key).

Sequencing within ID-52: {52.M1b} = id: 18, deps [7]. MUST land before Phase-5 ({52.12+}) writer dispatch so writer code targets the dedicated columns from day one.

2.6a CV-loader fix direction (Option (b) — re-baseline)

Decision: re-baseline the 4 APPLIED-S{NNN} files to status: active, NOT extend STATUS_VALUES.

Why re-baseline (Option b) over extending the enum (Option a):

• STATUS_VALUES is the drafting/editing lifecycle for the ontology (RESEARCH §4.2, per wp6-ontology-harness/TECH.md): planned → needed → active. APPLIED-S{NNN} was a Drafter-wave provenance marker that conflates “this CV has shipped a migration” (a historical fact) with “what stage is the CV in” (the enum’s purpose). Once shipped, the CV’s status IS active — the migration reference belongs in core_seed_path (which it already does in all four files, e.g. 26-form-type.md:8).
• Extending the enum to applied would propagate a marker that has no remaining lifecycle meaning (the CV is shipped — what does applied differentiate from active?). It would also propagate a session-ID-suffixed convention (APPLIED-S246, APPLIED-S249) that the enum cannot capture without further fragmentation.
• The two stale claims inside 26-form-type.md (RESEARCH §4.2 bullets) are corrected in the same edit pass (deleting the “table not yet in production schema” + the 10-value template_requirements CHECK references).

The four files re-baselined to status: active:

1. docs/ontology/26-form-type.md (was APPLIED-S246)
2. docs/ontology/30-procurement-vehicle.md (was APPLIED-S246)
3. docs/ontology/31-procurement-vehicle-instance.md (was APPLIED-S246)
4. docs/ontology/32-q-a-pair.md (was APPLIED-S249)

The session-ID provenance is preserved in core_seed_path (already pointing at the migration filename in each file). No data is lost.

Maps to PRODUCT: Inv-1 (CV gate — the loader loads successfully after this edit, so the form path can run).

2.6b form_type triple-source lockstep

Decision: drive the Python side from the taxonomy snapshot fixture (the mechanism recommended in RESEARCH §5 item #6 / §4.2).

• Source of truth: the live form_types Postgres table.
• TS consumer: loadOntologyCVs() reads 26-form-type.md (the CV markdown). A new test (__tests__/lib/ontology/form-type-parity.test.ts) asserts the 11 baseline values in 26-form-type.md exactly match the live form_types.key rows (mirrors the existing markdown-parity.test.ts pattern; uses the snapshot fixture).
• Python consumer: FormMetadata.form_type (and any future form_template_fields consumer Python-side) reads from scripts/tests/fixtures/taxonomy_snapshot.json:form_types, matching the existing _load_canonical_content_types pattern.
• Snapshot fixture refresh: bun run sync:taxonomy (per CLAUDE.md) extended to include form_types (a one-line additional SELECT key, label FROM form_types fold-in). Tracked as a separate Subtask in PLAN.

Maps to PRODUCT: Inv-1.

2.6c Layer-5 KG-entity CV schema shape (PRODUCT Inv-1a)

Cross-Task touch ratified S275 (OQ-52-WAVE-1-A, Option 3). lib/ontology/schemas.ts is shared infrastructure owned by wp6-ontology-harness (lib/ontology/schemas.ts:11 ownership comment; wp6 TECH.md §6 + E1 frontmatter ratifier). This TECH amendment authorises a forward-compatible schema relaxation explicitly scoped to the Layer-5 KG-entity case; the wp6-ontology-harness B2 + E1 verifier reports surveyed the 29 Layer-1 corpus pre-32-q-a-pair.md and recorded no Layer-5 KG-entity treatment because no Layer-5 file existed at C2 time (32-q-a-pair.md added S249 in fa0f4710 AFTER the wp6 D1 schema .strict() + baseline_values.min(1) landed in a258d144). The amendment below honours the wp6 D1 invariants for Layer-1 CVs unchanged while admitting Layer-5 KG-entity files.

Decision: relax OntologyCVSchema per-layer (NOT blanket).

Two changes to lib/ontology/schemas.ts:

1. baseline_values becomes conditionally required. For layer ∈ {1, 2, 3, 4, 6}, baseline_values remains required + .min(1) (Layer-1 CV invariant unchanged). For layer === 5 (KG-entity), baseline_values is OPTIONAL — its absence is well-formed, its presence (if a future Layer-5 file enumerated rows) is still validated by BaselineValueSchema.

2. .strict() is replaced by a per-layer extra-key whitelist. Layer-1 files remain .strict() (zero tolerance for unknown keys — Drafter-wave additions still fail loudly, preserving wp6 D1 R-A invariant). Layer-5 files admit three additional OPTIONAL keys: related_ontology: z.array(z.string()).optional(), source_of_truth: z.array(z.string()).optional(), last_updated: z.string().optional(). Any OTHER unknown key on a Layer-5 file still fails (the relaxation is enumerated, not blanket).

Implementation pattern (Zod base + .superRefine per-layer refinement):

const OntologyCVBaseSchema = z.object({
  cv_name: z.string().min(1).regex(/^[A-Za-z][A-Za-z0-9_]*$/),
  layer: z.union([
    z.literal(1), z.literal(2), z.literal(3),
    z.literal(4), z.literal(5), z.literal(6),
  ]),
  provenance_model: z.enum(PROVENANCE_MODEL_VALUES),
  client_extensible: z.boolean(),
  editable_via: z.enum(EDITABLE_VIA_VALUES),
  core_seed_path: z.string().min(1).nullable(),
  status: z.enum(STATUS_VALUES),
  baseline_values: z.array(BaselineValueSchema).optional(), // was .min(1)
  related_layers: z.array(/* … */).default([]),
  related_ontology: z.array(z.string()).optional(),
  source_of_truth: z.array(z.string()).optional(),
  last_updated: z.string().optional(),
}).strict();

export const OntologyCVSchema = OntologyCVBaseSchema.superRefine((data, ctx) => {
  if (data.layer === 5) return;
  if (!data.baseline_values || data.baseline_values.length < 1) {
    ctx.addIssue({
      code: z.ZodIssueCode.custom,
      path: ['baseline_values'],
      message: 'baseline_values required for non-Layer-5 CVs (must have ≥1 entry)',
    });
  }
  for (const key of ['related_ontology', 'source_of_truth', 'last_updated'] as const) {
    if (data[key] !== undefined) {
      ctx.addIssue({
        code: z.ZodIssueCode.custom,
        path: [key],
        message: `${key} is Layer-5-only; not permitted on layer ${data.layer}`,
      });
    }
  }
});

Why per-layer refinement, NOT a Zod discriminated union on layer: the discriminated union would require declaring two parallel object shapes diverging on one column (baseline_values required vs optional) and re-validating all shared columns twice. The single base schema + superRefine is leaner, keeps the consumer type (type OntologyCV = z.infer<typeof OntologyCVSchema>) cleanly inferable, and surfaces per-layer violations with the exact layer value in the error message.

Verification: post-amendment loadOntologyCVs() behaviour. 32-q-a-pair.md loads successfully (layer:5, no baseline_values, three Layer-5 keys present). All 28 pre-existing Layer-1 files load unchanged. A hypothetical malformed Layer-1 file omitting baseline_values still fails with a clear “baseline_values required for non-Layer-5 CVs” error. A hypothetical Layer-1 file adding source_of_truth still fails with “source_of_truth is Layer-5-only; not permitted on layer 1”.

Test additions: extend __tests__/lib/ontology/markdown-parity.test.ts with three cases: (a) 32-q-a-pair.md (Layer-5, no baseline_values) parses successfully; (b) constructed Layer-1 fixture missing baseline_values fails with expected message; (c) constructed Layer-1 fixture with stray source_of_truth key fails “Layer-5-only”.

Maps to PRODUCT: Inv-1a (Layer-5 KG-entity CVs load alongside Layer-1); Inv-1 (register loads heterogeneously — both kinds together).

Coordination with §2.6a re-baseline (NO conflict): §2.6a re-baselines 26-form-type.md, 30-procurement-vehicle.md, 31-procurement-vehicle-instance.md, AND 32-q-a-pair.md from APPLIED-S{NNN} to status: active. That edit clears the status-enum throw. §2.6c (this) clears the four structural-shape violations beneath the status throw on 32-q-a-pair.md. Both are needed; both run under separate Subtasks ({52.5} = §2.6a status flip; {52.5a} = §2.6c schema relaxation). Post-both, loadOntologyCVs() returns 29 records (28 Layer-1 + 1 Layer-5).

2.7 Path C cataloguing skill (PRODUCT Inv-20, Inv-21, Inv-22)

Decision: Path C is a Claude Plugin Skill (TS-side), invoked from the procurement UI or the task-view editor, that drives a TS catalogue-from-instance script under human-confirmation.

Why a Claude Plugin Skill, not a CLI / UI dialog:

• Sequencing §2.3 names Path C explicitly: “a Claude Plugin Skill authoring requirement_type + taxonomy + matching keywords with human confirmation.”
• The existing hand-written catalogue scripts (scripts/catalogue-standard-sq.ts, scripts/catalogue-charnwood-itt.ts) prove the catalogue WRITE shape. Path C generalises them under skill output.
• A pure CLI would lack the human-confirmation surface; a pure UI dialog would couple the catalogue write to one app page. A skill produces an artefact (the generated catalogue script) that is reviewed in PR / task-view, runs deterministically once confirmed, and is auditable in commit history.
• A Plugin Skill can call the Anthropic API for requirement_type classification + matching_keywords generation (same claude-opus-4-6 model the pipeline uses) and pass the result through a structured confirmation step.

Skill location: .claude/skills/catalogue-form-requirements/SKILL.md (new). Skill inputs:

• A form_templates.id UUID (the instance to catalogue from).
• An optional template_type override (the form-type CV key).
• Human-confirmation flag (no default).

Skill output: an executable TS file at scripts/catalogue-from-instance-<form-template-id>.ts that, when run:

1. Reads the form_template_fields rows for the instance via Supabase RPC (read-only).
2. For each field, calls Anthropic with the Q_A_FORM_PROMPT pattern (already in scripts/cocoindex_pipeline/prompts.py) extended with a requirement_type + matching_keywords + matching_guidance classification, returning a form_template_requirements-shaped row.
3. Generates the requirement_embedding via the same LiteLLMEmbedder / text-embedding-3-large config the pipeline Stage-4 uses (consistency with T10 matching’s read shape). Embedding serialisation via JSON.stringify(embedding) per CLAUDE.md.
4. Presents the candidate catalogue rows for human confirmation in stdout (or via task-view if invoked from there). The script halts pending explicit y/n per row.
5. On confirmation, writes each row to form_template_requirements via the existing safe Supabase pattern (tryQuery() from @/lib/supabase/safe; per-row insert in a single transaction wrapper).

Auth gate (Inv-24): the skill runs locally / in-PR review, but the human-confirmation write step uses getAuthorisedClient(['admin','editor']) against the actual database session — unauthorised callers receive authFailureResponse(auth). The skill SHALL refuse to call the WRITE step if the auth check fails; the script logs and exits.

File added:

• .claude/skills/catalogue-form-requirements/SKILL.md (the skill definition).
• scripts/catalogue-from-instance.ts (the generic template; the skill emits per-form copies).
• lib/catalogue/from-instance.ts (the read + LLM + embed + write helpers, imported by the generated scripts; uses direct file imports, not barrels — per CLAUDE.md).

No mempalace_* / gitnexus_* MCP calls in Path C — the skill is a pure read→classify→embed→confirm→write flow; impact analysis on the catalogue (Inv-22’s T10 read boundary) is a separate matching spec.

Maps to PRODUCT: Inv-20 (instance ≠ catalogue), Inv-21 (human-confirmed), Inv-22 (T10 read boundary — Path C writes the catalogue rows T10 reads), Inv-23 (catalogue rows have no workspace FK), Inv-24 (auth gate retained).

2.8 Re-ingest idempotency mechanism (PRODUCT Inv-16)

Mechanism: deterministic per-document UUIDs + cocoindex @coco.fn(memo=True) + declare_row UPSERT.

This is the SAME pattern proven by the existing Stage-6 declares (RESEARCH §4 + flow.py:778-788 _KH_PIPELINE_DOC_NS rationale comment). Specifically:

• form_templates.id = uuid5(_KH_PIPELINE_DOC_NS, f"ft:{rel_path}") — stable across runs, so the second ingest mints the SAME UUID and declare_row UPSERTs (UPDATEs) the existing row.
• form_template_fields.id = uuid5(_KH_PIPELINE_DOC_NS, f"ftf:{rel_path}:{sequence}") — stable per (form, reading-order position) pair. The second ingest UPSERTs each field row.

Why (rel_path, sequence) and not (rel_path, question_text_hash): the question text MAY legitimately edit between ingests (typo fix, clarification revision). Using sequence as the secondary key means an edit to question 7’s text updates question 7’s row in-place; using a hash would orphan the old row and insert a new one.

Edge case: field-count shrink (form revised; question 8 removed). A second ingest that produces only 7 fields would leave the original row 8 stranded with stale data. Fix: before declaring the new field rows, delete form_template_fields rows WHERE template_id = <id> AND sequence > <new_max_sequence>. Implemented as a one-line asyncpg call inside ingest_file’s form-write block, gated on the per-flow op_id (so a mid-flow failure doesn’t truncate state):

# Inv-16: trim stale field rows from a previous larger ingest.
async with DB_CTX.get().acquire() as conn:
    await conn.execute(
        "DELETE FROM public.form_template_fields "
        "WHERE template_id = $1 AND sequence > $2",
        form_template_id, max(f.sequence for f in extracted.fields) if extracted.fields else -1,
    )

@coco.fn(memo=True) cooperation: extract_form_structure is memoised on the cocoindex content-fingerprint. An unchanged file skips re-extraction; declare_row is not re-invoked; the existing rows retain their state. A changed file (different fingerprint) re-extracts, re-declares (UPSERT), and the trim above removes stragglers.

Why NOT a content-hash-based UPSERT key: content-hashing the question text would fight the legitimate-edit case above. Cocoindex’s content-fingerprint already covers the “unchanged file” path; the deterministic-UUID-on-rel_path covers the “changed file” path without requiring per-field hashing.

Why NOT clear-and-rewrite (the app-side route’s approach in analyse/route.ts:81-89): clear-and-rewrite invalidates any downstream FKs (e.g. q_a_pairs.question_match_id references that may form later via T10). The UPSERT pattern preserves row identity across ingests, which T10’s question_matches will depend on (sequencing §5a).

Maps to PRODUCT: Inv-16.

2.9 OQ-52-UI-UPLOAD-TENSION resolution (PRODUCT recorded ratification)

PRODUCT ratified: “Thin front-end drops file into resolved workspace folder.” Implementation: the existing app-side upload UI is retained in v1 BUT its server-side handler stops writing form_templates directly. Instead:

• The UI upload writes the file bytes to <COCOINDEX_SOURCE_PATH>/<workspace-folder>/ (using the same folder→workspace mapping the pipeline reads).
• The cocoindex live-fs-watch (localfs.walk_dir(..., live=True, recursive=True) — flow.py:1034-1037) picks up the new file on the next walk tick.
• The pipeline runs the full Path B write under its service identity.
• Inv-6 holds (no app-side instance-row write); Inv-16 holds (the same UPSERT pattern applies whether the file arrived by manual drop or UI upload); Inv-24 holds (the UI upload still requires getAuthorisedClient(['admin','editor']) to write into the folder; the file-system write needs no form_templates row of its own).

Scope note: the UI-handler rewire is out of scope for ID-52 — it touches the app/api/procurement/... upload routes that ID-50 (OPS-T1 route rollout) is migrating to defineRoute. Cross-link to ID-50’s per-route-group wave; flag as a follow-up Subtask in PLAN.md ({52.4}).

Maps to PRODUCT: OQ-52-UI-UPLOAD-TENSION (ratified record).

2.10 OQ-52-LOSSY cross-link (NOT in this Task)

PRODUCT ratified: “Fold to a Path-A follow-up, as a sub-task / new task (NOT a backlog item).” The existing flow.py:919-934 lossy q_a_extractions write IS in the same file this TECH extends. To avoid scope creep, this TECH does NOT modify that block.

ID-54 (already opened S273 per id52-final.yaml) carries the fix: expected_response_kind / evaluation_criteria / evidence_requirements / scope_tags need either (a) dedicated columns on q_a_extractions or (b) a structured extraction_metadata JSONB shape. That’s ID-54’s call.

Maps to PRODUCT: Inv-18 (no silent loss — the form-side fields are handled by §2.5), Inv-19 (Mode-1 unchanged in scope).

2.11 Pre-ratification empirical verification (Q-EX2 forcing function)

Per the Planner brief and the OQ-3 / Q-EX2 forcing function, the external-library symbols cited above were verified against the installed pins.

Date	Pin	Symbol	Result	Note
28/05/2026	cocoindex==1.0.3	coco.fn (decorator)	PRESENT	Used at flow.py:791, extraction.py (multiple); proven shape.
28/05/2026	cocoindex==1.0.3	coco.mount_each	PRESENT	Used at flow.py:1060; reactive per-item shape.
28/05/2026	cocoindex==1.0.3	mount_table_target	PRESENT	Used at flow.py:1012-1029; managed_by=ManagedBy.USER.
28/05/2026	cocoindex==1.0.3	coco.resources.file.FileLike	PRESENT	Type-hint target at flow.py:793.
28/05/2026	cocoindex==1.0.3	cocoindex.ExtractByLlm	ABSENT (RESEARCH §8 re-verified)	NOT cited in this TECH; Path B is deterministic.
28/05/2026	pdfplumber==0.11.9	pdfplumber.open	PRESENT	Read 57-page SQ PDF cleanly (RESEARCH §2.1).
28/05/2026	python-docx	docx.Document	PRESENT	Used at scripts/extract_tender_questions.py:27.
28/05/2026	(Python env; pin direct in requirements.txt for Path B)	openpyxl	PRESENT	Read EFA / CSP merged cells (RESEARCH §2.2-2.3).
28/05/2026	package.json	exceljs@4.4.0	PRESENT but NOT USED by Path B (Python-side decision §2.2).
28/05/2026	database.types.ts	form_templates, form_template_fields, form_template_requirements, form_types	PRESENT (lines 1686 / 1519 / 1597 / 1755)	Schema canonical.

No ABSENT / SIGNATURE_DRIFT blocks this TECH from ratification.

---

3. Testing and validation

Validation owns proving the build meets every PRODUCT invariant. Each row below names a concrete test or verification step.

3.1 Per-invariant validation matrix

PRODUCT Inv	Test / verification	File
Inv-1 (CV gate)	loadOntologyCVs() returns 29 records; bun run test __tests__/lib/ontology/markdown-parity.test.ts PASSES post §2.6a edit.	__tests__/lib/ontology/markdown-parity.test.ts
Inv-1 (triple-source lockstep)	New __tests__/lib/ontology/form-type-parity.test.ts asserts 26-form-type.md baseline_values keys == form_types table keys via snapshot fixture.	new
Inv-2 (formats)	extract_form_structure(.pdf/.xlsx/.docx) returns ExtractedForm; .txt/.md returns None.	scripts/tests/test_form_extractors.py (new)
Inv-3 (.xls)	extract_form_structure(.xls) returns None and logs form_extractor.skip.	same
Inv-4 (workspace resolution)	resolve_workspace(manifest, "phew-procurement/SQ.pdf") returns the mapped workspace UUID; same call returns the SAME UUID on repeated invocation.	scripts/tests/test_workspace_resolver.py (new)
Inv-5 (loud failure)	resolve_workspace(manifest, "unmapped/X.pdf") returns ResolutionFailure (not a default); ingest_file records zero form_template_fields rows.	same + integration test
Inv-6 (pipeline-owned write)	Integration test: place a .pdf in a workspace-mapped folder; run app_main once; assert one form_templates row + N form_template_fields rows exist. NO app interaction.	__tests__/integration/form-extraction.integration.test.ts (new)
Inv-7 (form-level metadata)	The SQ PDF ingest produces a form_templates row with name, filename, mime_type='application/pdf', file_size>0, description populated from FormMetadata.evaluation_methodology.	same integration test
Inv-8 (coordinates)	EFA XLSX scoring-matrix question row has row_index, col_index, table_index populated; SQ PDF prose row has col_index=NULL.	per-format reader unit tests + corpus fixture assertions
Inv-9 (placeholder vs authored)	Charnwood DOCX Insert question title grid rows have field_type='placeholder', placeholder_text populated, question_text=NULL. CSP TYPE RESPONSE HERE>>>> cells → field_type='placeholder'.	DOCX + XLSX reader unit tests
Inv-10 (mandatory flag)	SQ Annex B question 6.2 row has is_mandatory=true (M flag); CSP free-text preamble item has is_mandatory=NULL.	PDF reader unit test
Inv-11 (word limit)	SQ Annex B 6.2 row has word_limit=500; EFA “Page Limit” row has the integer limit; CSP rows with no stated limit have word_limit=NULL.	PDF + XLSX reader unit tests
Inv-12 (section + sequence)	EFA fields ordered by sequence reproduce the form’s reading order; section_name carries “Part 2 — OVERALL APPROACH” etc.	XLSX reader unit test
Inv-13 (dedup)	EFA fixture produces N fields, NOT 2N (Bidder 1 + Bidder 2 deduped).	XLSX reader unit test + corpus fixture assertion
Inv-14 (reference URLs)	CSP fixture row carrying NCSC URL has reference_urls=['https://www.ncsc.gov.uk/...'].	XLSX reader unit test
Inv-15 (full content extent)	SQ PDF reader sees 57 pages, NOT 8; Annex B/C questions extracted from pp.17-46.	PDF reader unit test
Inv-16 (idempotency)	Re-ingest same file: integration test ingests once (N fields), then again (still N fields, same UUIDs); assert no duplicate rows; assert row IDs stable. Then mutate the source (field 7 removed): re-ingest yields N-1 fields, the stranded row 8 trimmed.	__tests__/integration/form-extraction.integration.test.ts (idempotency case)
Inv-17 (failure isolation)	Integration test ingests a batch of [corrupt_file.pdf, sq.pdf, efa.xlsx, charnwood.docx]; assert 3 successful instances + 1 surfaced failure (status='analysis_failed'), batch not halted.	same
Inv-18 (no silent loss)	For each corpus fixture, assert every metadata facet the form expresses on a sampled question appears on that question’s form_template_fields row (M/O, limit, URL, section, coordinate, placeholder).	corpus fixture assertions
Inv-19 (Mode-1 unchanged)	Existing __tests__/scripts/cocoindex_pipeline/* tests asserting Path A’s q_a_extractions behaviour continue to PASS.	existing tests unchanged
Inv-20 (instance ≠ catalogue)	Pipeline ingest writes only form_templates + form_template_fields; assert form_template_requirements row count is unchanged.	integration test assertion
Inv-21 (human-confirmed)	Path C skill output halts pending confirmation; the generated script SHALL NOT write form_template_requirements without an explicit y per row.	__tests__/lib/catalogue/from-instance.test.ts (new)
Inv-22 (T10 boundary)	Manual: Path C output includes requirement_embedding (vector(1024)), matching_keywords, requirement_type — the read shape T10 will consume. (T10’s match-scoring tests live in procurement-question-matching.)	manual + schema assertion
Inv-23 (no workspace FK)	Schema assertion: form_template_requirements Relationships array contains only template_type → form_types.key — verified via supabase/types/database.types.ts.	structural test
Inv-24 (auth gate)	Path C confirmation step gates on getAuthorisedClient(['admin','editor']) + authFailureResponse. Test: a viewer-role caller is refused.	__tests__/lib/catalogue/from-instance.test.ts
Inv-25 (workspace scoping)	RLS-level test: a viewer of workspace A cannot SELECT form_templates rows from workspace B; catalogue rows are visible regardless of workspace.	__tests__/integration/form-extraction-rls.integration.test.ts (new)
Inv-26 (AI-invisible)	Manual: UI surfacing of form_template_fields reads as structured records, not “AI-extracted”. (Lib-level test: no copy strings in lib/catalogue/from-instance.ts reference “AI”/“extraction” to end users.)	manual review

3.2 Acceptance fixtures (PRODUCT § “Acceptance fixtures”)

The four real-corpus fixtures in docs/testing/test-data/templates/ drive the integration tests in §3.1. Each is added as a fixture under __tests__/fixtures/form-extraction/ (or referenced by path; the integration test copies them into a temporary COCOINDEX_SOURCE_PATH for the test run).

Real-behaviour discipline (per docs/reference/test-philosophy.md): the per-format reader unit tests use the actual corpus files and the actual pdfplumber / openpyxl / python-docx libraries — NO mocks of the reader internals. The integration test uses a real Supabase staging branch (matching __tests__/integration/**.integration.test.ts convention per CLAUDE.md bun run test:integration). Only the Anthropic client (for the Path-C requirement_type classification under Inv-21 / Inv-22) is mocked at the SDK boundary in the Path-C unit test — the integration test for Path C is gated on a real Anthropic key in CI.

3.3 Migration validation

• Run supabase migration new id52_form_extraction_schema, paste §2.6 M1, run supabase db push --env=staging against the staging branch (turayklvaunphgbgscat).
• Verify via supabase gen types typescript --project-id turayklvaunphgbgscat --schema public > supabase/types/database.types.ts that the diff matches the expected shape (new is_mandatory, reference_urls, ingest_source columns; widened mime_type CHECK).
• Confirm no anon EXECUTE grant warnings via migration-revoke-guard.yml CI workflow.
• Confirm the schema-parity.yml workflow against prod has the new shape staged for prod cutover.

3.4 Verification commands

bun run test __tests__/lib/ontology/markdown-parity.test.ts
bun run test __tests__/lib/ontology/form-type-parity.test.ts
bun run test __tests__/lib/catalogue/from-instance.test.ts
python3 -m pytest scripts/tests/test_form_extractors.py scripts/tests/test_workspace_resolver.py -v
bun run test:integration __tests__/integration/form-extraction.integration.test.ts
bun run test:integration __tests__/integration/form-extraction-rls.integration.test.ts

CI: the existing ci.yml quality-test shards pick up the new Vitest tests; the integration job picks up the new .integration.test.ts files; the cloud-run-deploy workflow picks up the new Python modules under scripts/cocoindex_pipeline/. No new CI job needed.

---

4. Follow-ups

These are deferred cleanups / future work named so the build does not silently accrete them.

• op_id column on form_templates + form_template_fields — v1 records op_id on pipeline_runs.items_created[] only. A dedicated column would let T10 / observability filter by ingest run. Tracked separately.
• UI upload re-wire (OQ-52-UI-UPLOAD-TENSION rewire) — the app-side upload UI’s server-side handler change (writes file into folder, not form_templates) is out of scope for ID-52 and cross-links to ID-50 (OPS-T1 route rollout). Add a Subtask under the appropriate ID-50 route-group wave.
• App-side analyse/route.ts retirement — separate Subtask scoped by the sub-orchestrator after PLAN authoring; the retirement deletes the route file + its unit tests + the processing_queue consumer for job_type: 'template_analyse' (if present).
• ID-54 Path-A lossy q_a_extractions fix — already opened; see §2.10.
• Mandatory-flag inference for non-flag forms — Inv-10 currently records NULL when the form expresses no flag. Future heuristic could infer from section context (e.g. EFA weighting > 0 ⇒ mandatory); deferred until corpus evidence shows the heuristic is worth the false-positive cost.
• Path C re-run idempotency — re-running Path C against an already-catalogued instance currently appends new form_template_requirements rows. A confirmation-step “skip already catalogued” flag is a follow-up. T10’s read should already be tolerant via is_current.

---

5. Risks and mitigations

• Risk: form_template_fields.is_mandatory column addition is non-destructive but a CHECK widening on mime_type and status is. Supabase migration order matters — apply DROP CONSTRAINT before ADD CONSTRAINT to avoid name collision. Migration validated against staging first via --env=staging (per CLAUDE.md staging-first discipline). Mitigation: the migration script in §2.6 uses explicit DROP CONSTRAINT … ADD CONSTRAINT … pairs (not ALTER … RENAME TO …).
• Risk: silent Supabase failures. The pipeline-side declares use mount_table_target which has its own error surface; the Path-C TS-side writes go through tryQuery() from @/lib/supabase/safe (per CLAUDE.md). Mitigation: code review gate (code-review-and-quality skill) before merge.
• Risk: schema parity prod ↔ staging. The schema-parity.yml workflow guards this; the M1 migration must land on staging first, then prod, with the workflow PASS in between. Mitigation: existing CI gate.
• Risk: CV-loader re-baseline (§2.6a) is a breaking change to any consumer that reads status: APPLIED-S{NNN} literally. Grep verifies no such consumer exists (the loader is the only reader, and it rejects the marker). Mitigation: a pre-commit grep over lib/** + scripts/** confirms zero literal APPLIED-S matches.
• Risk: workspace manifest drift (manifest UUID stale, workspace deleted). v1 relies on the asyncpg INSERT-time FK to surface a canonical error. A future workspace-existence pre-check at manifest-load time is a follow-up if the FK error proves too noisy.
• Risk: Anthropic 503 / rate limit in Path C. The skill reuses the existing _anthropic_retry tenacity wrapper pattern from extraction.py:9-11. Mitigation: bounded retries + structured error surfacing.

---

6. End-to-end flow

(One Mermaid sequence diagram, because Path B + Path C cross three layers and the data ownership boundary matters.)

sequenceDiagram
    participant User as Authorised user (admin/editor)
    participant UI as UI upload (optional)
    participant FS as <COCOINDEX_SOURCE_PATH>/<workspace>/
    participant Flow as cocoindex flow.py:app_main
    participant Resolver as workspace_resolver.py
    participant Extractor as form_extractors/{pdf,xlsx,docx}.py
    participant DB as Supabase (form_templates / form_template_fields)
    participant Skill as .claude/skills/catalogue-form-requirements
    participant Catalogue as form_template_requirements (global)

    User->>UI: Upload blank-form file (optional path)
    UI->>FS: Drop file in workspace-mapped folder
    Note over FS: OR: file added directly to folder (manual)

    Flow->>Resolver: load_workspace_manifest()
    Resolver-->>Flow: WorkspaceManifest
    Flow->>FS: localfs.walk_dir(live=True, recursive=True)
    FS-->>Flow: (rel_path, FileLike)*
    loop per file (mount_each)
        Flow->>Resolver: resolve_workspace(manifest, rel_path)
        alt resolution failure
            Resolver-->>Flow: ResolutionFailure
            Flow->>Flow: _emit_stage_error_log(workspace_resolution); skip
        else success
            Resolver-->>Flow: workspace_id
            Flow->>Extractor: extract_form_structure(file)
            alt extraction failure
                Extractor-->>Flow: FormExtractionError
                Flow->>DB: declare_row form_templates(status='analysis_failed')
            else success
                Extractor-->>Flow: ExtractedForm
                Flow->>DB: declare_row form_templates(status='analysed')
                Flow->>DB: DELETE stale field rows (sequence > new_max)
                loop per field
                    Flow->>DB: declare_row form_template_fields
                end
            end
        end
    end

    Note over User,Catalogue: Path C — human-confirmed cataloguing (separate run)
    User->>Skill: invoke catalogue-form-requirements <form_template_id>
    Skill->>DB: SELECT form_template_fields WHERE template_id = ?
    DB-->>Skill: fields
    Skill->>Skill: Anthropic classify (requirement_type, matching_keywords)
    Skill->>Skill: embed (text-embedding-3-large → vector(1024))
    Skill->>User: present candidates for confirmation
    User-->>Skill: y/n per row
    Skill->>Catalogue: INSERT confirmed rows (via getAuthorisedClient gate)
    Note over Catalogue: T10 (procurement-question-matching) reads from here

---

End of TECH — ID-52.3. Output: docs/specs/id-52-form-extraction/TECH.md. Authored by a fresh Planner context; not committed (the sub-orchestrator commits after the Checker gate).