S507 ast-dataflow schema-wiring audit (id-128)

Schema-wiring audit — built but never wired

Task: the schema-wiring audit owed from S506, run with the ast-dataflow skill family. Date: 2026-07-28 · Repo: /Users/liamj/Documents/development/canonical @ main (8ef32193) Scope: all 807 columns of the public schema; cocoindex pipeline tables prioritised. Mode: read-only. No code edited, no commits, no DDL. The only writes were to this scratchpad.

---

1. Headline

The originating finding — “~80+ columns built but never wired” — reproduces but overstates the problem. ast-dataflow schema-coverage alone reports 88 unwired columns, but that query sees only the TypeScript corpus. Adding the two writer surfaces it is blind to (the Python pipeline, and cocoindex’s declarative TableSchema exports) rescues part of that set, and adding live-database defaults separates genuine gaps from bookkeeping columns that Postgres fills by design.

The single most consequential finding is not a created_at-class column at all:

source_documents.extracted_text is read by 12 production TypeScript sites (the MCP content field, the review queue, the diff adapters) and is never written by the ingestion pipeline. Confirmed empirically on staging: of the 7 pipeline-produced source_documents rows (op_id IS NOT NULL), 0 have extracted_text populated.

This directly explains an integration-test failure of the exact shape the brief asked about — see §4.

---

2. Counts by wiring state

Merged across all three writer surfaces (TypeScript, Python, cocoindex declarative):

Wiring state	Columns	Share
wired (written and read)	372	46%
read-never-written	179	22%
never-written-never-read	185	23%
written-never-read	71	9%
Total	807

Severity ranks how much each gap actually matters, using live-DB nullability and defaults:

Severity	Columns	Meaning
none	372	wired
high	98	a reader consumes a column nothing ever writes, and there is no DB default — the read always sees NULL
medium	105	semantic column permanently NULL, or read-never-written but defaulted
low	97	written but never read (dead payload), or defaulted-and-unused
info	135	PG-defaulted bookkeeping (created_at/updated_at/id) — deliberately omitted by convention

The created_at/created_by class that prompted this audit is the info bucket: 135 columns, and it is working as designed. flow.py states the convention explicitly at scripts/cocoindex_pipeline/flow.py:1318: “PG-defaulted columns (created_at, entity_type_override, normalisation_version) are OMITTED per the existing content_text_hash GENERATED ALWAYS convention — explicit INSERTs of those columns would either duplicate the PG default behaviour or (for the GENERATED ALWAYS family) raise SQLSTATE 428C9.” These are not defects and should not be “fixed”.

TS-only verdict vs. merged verdict

Of the 88 columns schema-coverage calls unwired, 7 are wired on another surface:

Column	Rescued by
entity_pair_resolutions.decision / .entity_type / .name_a / .name_b	Python raw SQL (stage_5.py, pair_resolver.py)
q_a_extractions.evaluation_criteria / .extraction_metadata	cocoindex declarative TableSchema
record_embeddings.updated_at	Python read

The remaining 81 are genuinely unwritten — but most are the PG-defaulted info class.

---

3. Method, and why the single-tool answer is wrong

Three writer surfaces reach these tables. Any audit using only one will over-report.

Surface	Tool	What it sees
TypeScript	bun run ast-dataflow schema-coverage	.from() query chains in the tsconfig corpus. 807 columns in 3.9 s.
Python	bun run ast-dataflow-py (tools/ast_dataflow_py/)	raw asyncpg SQL + supabase-py .from_() in scripts/**
Declarative	(no tool — read manually)	cocoindex TableSchema + ColumnDef exports in flow.py

The third surface is the important one and no tool covers it. The pipeline’s real writes are declared as data, not code:

scripts/cocoindex_pipeline/flow.py
  1186  Q_A_EXTRACTIONS_SCHEMA      (12 cols)
  1216  SOURCE_DOCUMENTS_SCHEMA     (22 cols)
  1291  REFERENCE_ITEMS_SCHEMA      (12 cols)
  1325  ENTITY_MENTIONS_SCHEMA       (9 cols)
  1354  ENTITY_RELATIONSHIPS_SCHEMA  (6 cols)
  1420  CONTENT_CHUNKS_SCHEMA        (7 cols)
  1464  RECORD_EMBEDDINGS_SCHEMA     (4 cols)

There is no literal SQL and no .from_() chain, so both ast-dataflow tools report zero writes for every column the pipeline actually populates. reference_items is the clearest case: all 12 of its columns show tsWrites=0, pyWrites=0, which reads as “nothing writes this table” — yet it is the pipeline’s Layer-5 output target, upserted at flow.py:3716 via REFERENCE_ITEMS_SCHEMA.

Two fidelity notes on the run itself:

• ast-dataflow-py ships with sqlglot optional and it was not installed, silently degrading every SQL verdict to regex-matched indirect confidence. I installed it into a throwaway venv in the scratchpad (sqlglotenv/, sqlglot 30.14.0) and re-ran with HAVE_SQLGLOT: True, so the Python results here are exact. Nothing in the repo or the user’s Python environment was modified. Anyone re-running this audit should check the sqlglot boolean in the response envelope.
• Verdicts were checked against the live Platform staging DB (rbwqewalexrzgxtvcqrh) for nullability, defaults, and actual population rates. Static “never written” plus a live “0 of N rows populated” is a much stronger claim than either alone, and in two places (below) the live check overturned the static inference.

Reproduction artefacts, all in this scratchpad: schema-coverage-raw.json (TS), py_all.json (Python), tableschemas.json (declarative), dbcols.json (live DB), merged.json (merged verdicts), py_sweep.py + merge.py (drivers).

---

4. Cocoindex integration-test cross-reference

This is the section the brief asked for: which unwired columns can produce expected null not to be null or expected 0 > 0 in __tests__/integration/cocoindex/.

4.1 CONFIRMED CAUSE — source_documents.extracted_text

Severity: high. This test cannot pass in its current form.

__tests__/integration/cocoindex/sidecar-mime-coverage.integration.test.ts polls source_documents and breaks out of the loop only when extracted_text is truthy:

// sidecar-mime-coverage.integration.test.ts:139-152
const { data } = await client
  .from('source_documents')
  .select('id, extracted_text')
  .ilike('filename', `${TEST_PREFIX}-${mime.kind}%`)
  .limit(1);

if (data && data.length > 0 && data[0]!.extracted_text) {
  landedRow = { ... };
  break;
}

then asserts, at lines 159-160:

expect(landedRow).not.toBeNull();                          // → "expected null not to be null"
expect(landedRow!.extracted_text.length).toBeGreaterThan(0); // → "expected 0 > 0"

Both reported failure shapes come from this one test, and one runs per MIME fixture in MIME_SET.

Evidence that nothing populates the column on the pipeline path:

• extracted_text is absent from SOURCE_DOCUMENTS_SCHEMA (flow.py:1216), which lists 22 columns and not this one.

• Python corpus: pyWrites=0. The only Python mention is a SELECT list at scripts/cocoindex_pipeline/sources/l_records.py:424.

• TypeScript writers exist but are all off the ingest path: lib/edit-intent/sweep.ts:153, plus scripts/seed-e2e-users.ts:387 and scripts/mcp-eval/fixtures.ts:451.

• Live staging, decisive:

  op_id IS NOT NULL (pipeline-produced)	rows	extracted_text set
  true	7	0
  false	64	5

  Every populated value is on a non-pipeline row.

Root cause is a half-finished retarget, not a missing column. The test’s own comment (sidecar-mime-coverage.integration.test.ts:136) records the intent — “ID-131.19 M6 retirement: content_items DROPPED at M6; source_documents.filename replaces title, extracted_text replaces content_text” — but when content_items.content_text was retired, the extracted body was rehomed to content_chunks.content and reference_items.body (both declaratively written and both populated), not to source_documents.extracted_text. The test was retargeted to a column the pipeline was never taught to write.

Two coherent fixes, for the owner to choose:

1. Add extracted_text to SOURCE_DOCUMENTS_SCHEMA and populate it from the extractor output — this also fixes the 12 production readers (lib/mcp/resources.ts:143, lib/mcp/tools/content.ts:119, app/api/review/queue/route.ts:797, lib/diff/adapters/source-document-revision.ts:65), which today all read a permanently-NULL column on pipeline-ingested documents.
2. Retarget the test to content_chunks.content / reference_items.body, where the extracted body actually lands — but this leaves the 12 production readers broken.

Given the production readers, option 1 is the substantive fix; option 2 only makes the test agree with a product-level gap. Note lib/diff/adapters/source-document-revision.ts:11 already calls extracted_text “legacy, used only as the binary-leg fallback”, so the owner may consider the readers deprecated — that judgment is theirs, and it is the decision this finding needs from them.

4.2 NOT a cause — cases I checked and cleared

I traced every other non-null / greater-than-zero assertion in the cocoindex suite. These look like candidates but are sound; recording them so the next session does not re-investigate:

Assertion site	Column(s)	Why it is fine
test-helpers.ts:283 expect(run.started_at).not.toBeNull()	pipeline_runs.started_at	Overturned my own static finding. recordPipelineRun (lib/pipeline/record-run.ts:221-235) omits started_at from its INSERT payload, which looks fatal — but the column is DEFAULT now() NOT NULL, so Postgres fills it. 902/902 rows populated.
chunking.integration.test.ts:150-153	content_chunks.heading_text / heading_level / heading_path	The test asserts these are NULL, under a documented [GAP-CMI-004] marker. Live DB agrees: 0/204 rows populated. Test and reality are aligned.
audit-log-shipping.integration.test.ts:201, non-pipeline-write.integration.test.ts:158	audit_log.*	audit_log does not exist in any schema (checked pg_class across all namespaces). But the tests probe first and skip cleanly — an intentional RLS-PATTERN P-5 [DEFERRED-v1.1] gate. Handled.
sidecar-cold-start.integration.test.ts:169-170	record_embeddings.embedding	Correctly retargeted post-M6 to record_embeddings keyed (owner_kind, owner_id), which is declaratively written. Contrast with §4.1 — same M6 retarget, done right.
extract-contract-honour.integration.test.ts:282	source_documents.classification_confidence	Declaratively written. Wired.
url-landing-set.integration.test.ts:199-216	reference_items.body / .ingestion_source / .published_at	All in REFERENCE_ITEMS_SCHEMA. Wired — and a good example of the §3 blind spot, since both tools report zero writes.
all .from('content_items') sites	—	The ID-131.19 M6 zero-content_items gate holds: no live query chain remains anywhere in app/, lib/, scripts/, or __tests__/. Every hit is a retirement comment.

4.3 Secondary risk — pipeline_runs counters are structurally always zero

items_updated and items_skipped are never explicitly written by any surface. They appear only inside the ...extraFields spread at lib/pipeline/update-progress.ts:85, which is why schema-coverage rates them undecidable rather than unwired. Both default to 0.

Live staging, across 902 pipeline_runs rows: items_updated <> 0 in 0 rows; items_skipped <> 0 in 0 rows. They are decorative.

No current cocoindex test asserts on them, so this is not a live failure — but any future assertion of the form expect(run.items_skipped).toBeGreaterThan(0) would fail permanently, and any dashboard reading them is showing a hardcoded zero. pipeline_runs.cost is the same story: 9 write sites, all in tests, and 0 of 902 production rows populated.

---

5. Priority pipeline tables — full column detail

58 non-wired columns across the ten cocoindex-adjacent tables. TS/Python columns are read/write counts from each corpus; declarative marks membership of a flow.py TableSchema.

table.column	wiring state	severity	DB shape	TS	Python	declarative	nearest-miss evidence
source_documents.original_filename	read-never-written	high	nullable, no default	R7/W0	R0/W0	no	app/reference/[id]/page.tsx:110
source_documents.auth	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.cadence	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.locator	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.parent_id	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.pipeline_run_id	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.uploaded_by	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.version	read-never-written	medium	NOT NULL, 1	R1/W0	R0/W0	no	app/documents/[id]/diff/page.tsx:62
source_documents.workspace_id	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	app/api/health/route.ts:41
source_documents.origin_type	written-never-read	low	nullable, no default	R0/W0	R0/W1	no	app/api/health/route.ts:41
source_documents.status	written-never-read	low	NOT NULL, ‘uploaded’::characte	R0/W2	R0/W0	no	app/api/health/route.ts:41
source_documents.created_at	read-never-written	info	NOT NULL, now()	R8/W0	R0/W0	no	app/reference/[id]/page.tsx:110
content_chunks.heading_level	read-never-written	high	nullable, no default	R1/W0	R0/W0	no	lib/mcp/tools/content.ts:317
content_chunks.heading_text	read-never-written	high	nullable, no default	R1/W0	R0/W0	no	lib/mcp/tools/content.ts:317
content_chunks.heading_path	read-never-written	medium	NOT NULL, ’{}‘::text[]	R1/W0	R0/W0	no	lib/mcp/tools/content.ts:317
content_chunks.parent_chunk_id	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	—
content_chunks.content	written-never-read	low	NOT NULL, no default	R0/W0	R0/W1	yes	tests/integration/id138-erasure-cascade.integration.test.ts:197
content_chunks.created_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	—
content_chunks.updated_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	—
entity_mentions.normalisation_version	never-written-never-read	low	nullable, 1	R0/W0	R0/W0	no	scripts/verify-platform-promotion-gate.ts:475
entity_mentions.created_at	never-written-never-read	info	nullable, now()	R0/W0	R0/W0	no	scripts/verify-platform-promotion-gate.ts:475
entity_mentions.updated_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	scripts/verify-platform-promotion-gate.ts:475
entity_relationships.created_at	never-written-never-read	info	nullable, now()	R0/W0	R0/W0	no	scripts/verify-platform-promotion-gate.ts:495
entity_relationships.updated_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	scripts/verify-platform-promotion-gate.ts:495
pipeline_runs.cost	written-never-read	low	nullable, no default	R0/W9	R0/W0	no	tests/integration/queue/batch-reclassify.integration.test.ts:539
pipeline_runs.items_skipped	never-written-never-read	low	nullable, 0	R0/W0	R0/W0	no	lib/pipeline/start-run.ts:138
pipeline_runs.items_updated	never-written-never-read	low	nullable, 0	R0/W0	R0/W0	no	lib/pipeline/start-run.ts:138
pipeline_runs.created_at	read-never-written	info	NOT NULL, now()	R4/W0	R0/W0	no	tests/integration/queue/procurement-draft-all.integration.test.ts:1233
record_embeddings.created_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	—
record_embeddings.id	read-never-written	info	NOT NULL, gen_random_uuid()	R2/W0	R1/W0	no	tests/integration/q-a-pairs/promote-corpus.integration.test.ts:136
record_embeddings.updated_at	read-never-written	info	NOT NULL, now()	R0/W0	R1/W0	no	scripts/cocoindex_pipeline/qa_dedup_proposer.py:200
q_a_extractions.alternate_question_phrasings	written-never-read	low	NOT NULL, ’{}‘::text[]	R0/W0	R0/W1	yes	lib/q-a-pairs/promotion-candidate-review.ts:375
q_a_extractions.evaluation_criteria	written-never-read	low	nullable, no default	R0/W0	R0/W1	yes	scripts/cocoindex_pipeline/flow.py:3127
q_a_extractions.evidence_requirements	written-never-read	low	NOT NULL, ’{}‘::text[]	R0/W0	R0/W1	yes	tests/integration/cocoindex/extract-contract-honour.integration.test.ts:309
q_a_extractions.expected_response_kind	written-never-read	low	nullable, no default	R0/W0	R0/W1	yes	tests/integration/cocoindex/extract-contract-honour.integration.test.ts:309
q_a_extractions.extracted_answer_text	written-never-read	low	nullable, no default	R0/W4	R0/W1	yes	tests/integration/cocoindex/extract-contract-honour.integration.test.ts:309
q_a_extractions.extracted_question_text	written-never-read	low	NOT NULL, no default	R0/W4	R0/W1	yes	tests/integration/cocoindex/extract-contract-honour.integration.test.ts:309
q_a_extractions.extraction_metadata	written-never-read	low	NOT NULL, ’{}‘::jsonb	R0/W0	R0/W1	yes	scripts/cocoindex_pipeline/flow.py:3127
q_a_extractions.extractor_kind	written-never-read	low	NOT NULL, no default	R0/W4	R0/W1	yes	tests/integration/q-a-pairs/promote-corpus.integration.test.ts:197
q_a_extractions.scope_tags	written-never-read	low	NOT NULL, ’{}‘::text[]	R0/W0	R0/W1	yes	tests/integration/cocoindex/extract-contract-honour.integration.test.ts:309
q_a_extractions.created_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	—
q_a_extractions.updated_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	lib/q-a-pairs/promote-corpus.ts:530
reference_items.superseded_by	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	—
reference_items.thumbnail_url	never-written-never-read	medium	nullable, no default	R0/W0	R0/W0	no	—
reference_items.ingestion_source	written-never-read	low	NOT NULL, no default	R0/W0	R0/W0	yes	tests/integration/cocoindex/url-landing-set.integration.test.ts:188
reference_items.layer	written-never-read	low	nullable, no default	R0/W0	R0/W0	yes	—
reference_items.published_at	written-never-read	low	nullable, no default	R0/W0	R0/W0	yes	tests/integration/cocoindex/url-landing-set.integration.test.ts:188
reference_items.created_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	—
reference_items.updated_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	—
entity_aliases.alias	read-never-written	high	NOT NULL, no default	R0/W0	R2/W0	no	tests/integration/cocoindex/test-helpers.ts:502
entity_aliases.canonical	read-never-written	high	NOT NULL, no default	R0/W0	R2/W0	no	tests/integration/cocoindex/test-helpers.ts:502
entity_aliases.is_active	read-never-written	medium	NOT NULL, true	R0/W0	R2/W0	no	lib/entities/entity-aliases.ts:58
entity_aliases.provenance	read-never-written	medium	NOT NULL, ‘core’::text	R0/W0	R1/W0	no	tests/integration/cocoindex/test-helpers.ts:502
entity_aliases.created_at	never-written-never-read	info	NOT NULL, now()	R0/W0	R0/W0	no	tests/integration/cocoindex/test-helpers.ts:502
entity_aliases.id	never-written-never-read	info	NOT NULL, gen_random_uuid()	R0/W0	R0/W0	no	tests/integration/cocoindex/test-helpers.ts:502
entity_pair_resolutions.op_id	written-never-read	low	nullable, no default	R0/W0	R0/W1	no	tests/integration/cocoindex/pair-resolver-determinism.integration.test.ts:83
entity_pair_resolutions.resolved_at	never-written-never-read	low	NOT NULL, now()	R0/W0	R0/W0	no	—
entity_pair_resolutions.id	never-written-never-read	info	NOT NULL, gen_random_uuid()	R0/W0	R0/W0	no	tests/integration/cocoindex/pair-resolver-determinism.integration.test.ts:83

---

6. Broader schema

6.1 High severity — read by code, written by nothing (93 outside the priority tables)

Every row here is a production read of a column with no writer and no DB default. Read this list with §6.2’s caveat in hand — a subset is populated by SQL functions or migration seed data that neither tool can see, and I verified which below rather than reporting all 93 as defects.

table.column	DB shape	TS reads	nearest-miss read site
application_types.default_colour	nullable	1	app/api/application-types/route.ts:36
application_types.default_icon	nullable	1	app/api/application-types/route.ts:36
application_types.description	nullable	1	app/api/application-types/route.ts:36
application_types.key	NOT NULL	5	tests/integration/intelligence-golden-path.integration.test.ts:64
application_types.label	NOT NULL	2	app/api/application-types/route.ts:36
application_types.label_plural	nullable	1	app/api/application-types/route.ts:36
citations.cited_concept_path	nullable	3	app/api/procurement/[id]/citations/route.ts:189
citations.cited_end	nullable	1	app/api/procurement/[id]/citations/route.ts:189
citations.cited_location_kind	nullable	1	app/api/procurement/[id]/citations/route.ts:189
citations.cited_reference_item_id	nullable	3	app/api/procurement/[id]/citations/route.ts:189
citations.cited_source_document_id	nullable	4	app/api/procurement/[id]/citations/route.ts:189
citations.cited_start	nullable	1	app/api/procurement/[id]/citations/route.ts:189
citations.cited_text	nullable	2	app/api/procurement/[id]/citations/route.ts:189
citations.cited_version	nullable	1	lib/mcp/tools/procurement.ts:530
classification_disputes.disputed_field	NOT NULL	1	components/provenance/disputes-tab-stub.tsx:55
classification_disputes.source_document_id	NOT NULL	1	components/provenance/disputes-tab-stub.tsx:55
coverage_targets.domain_id	NOT NULL	2	lib/mcp/tools/apps.ts:301
coverage_targets.metric_name	NOT NULL	1	lib/mcp/tools/apps.ts:301
coverage_targets.target_value	NOT NULL	1	lib/mcp/tools/apps.ts:301
engagement_groups.name	NOT NULL	2	app/api/engagement-groups/route.ts:53
eval_runs.passed	NOT NULL	1	lib/eval/graduation.ts:146
eval_runs.touchpoint_id	NOT NULL	1	lib/eval/graduation.ts:146
eval_touchpoints.graduation_metric	nullable	1	lib/eval/graduation.ts:164
eval_touchpoints.kind	NOT NULL	1	app/api/refinement/touchpoints/[id]/version-history/route.ts:25
eval_touchpoints.owner	NOT NULL	1	app/api/refinement/touchpoints/[id]/version-history/route.ts:25
eval_touchpoints.suite_name	NOT NULL	1	app/api/refinement/touchpoints/[id]/version-history/route.ts:25
feed_sources.etag	nullable	1	app/api/intelligence/workspaces/[id]/sources/[sourceId]/test/route.ts:21
feed_sources.last_modified	nullable	1	app/api/intelligence/workspaces/[id]/sources/[sourceId]/test/route.ts:21
form_instances.engagement_group_id	nullable	3	app/api/procurement/[id]/attachments/route.ts:443
form_instances.field_count	nullable	1	app/api/procurement/[id]/fields/route.ts:122
form_instances.outcome	nullable	3	app/api/procurement/[id]/outcome/integrate/route.ts:58
form_instances.outcome_notes	nullable	2	app/api/procurement/route.ts:84
form_instances.outcome_recorded_at	nullable	1	app/api/procurement/route.ts:84
form_instances.outcome_recorded_by	nullable	1	app/api/procurement/route.ts:84
form_instances.structure_path	nullable	2	app/api/procurement/[id]/fields/route.ts:122
form_instances.submission_date	nullable	1	app/api/procurement/route.ts:84
form_questions.assigned_to	nullable	5	app/api/procurement/[id]/questions/[qId]/route.ts:61
form_requirement_templates.description	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.matching_guidance	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.matching_keywords	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.primary_domain	nullable	2	lib/content/content-suggestions.ts:360
form_requirement_templates.primary_subtopic	nullable	2	lib/content/content-suggestions.ts:360
form_requirement_templates.question_number	nullable	2	lib/domains/procurement/form-templating/catalogue/from-instance.ts:437
form_requirement_templates.requirement_text	NOT NULL	3	lib/content/content-suggestions.ts:360
form_requirement_templates.requirement_type	NOT NULL	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.secondary_domain	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.secondary_subtopic	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.section_name	NOT NULL	2	lib/content/content-suggestions.ts:360
form_requirement_templates.section_ref	NOT NULL	2	lib/domains/procurement/form-templating/catalogue/from-instance.ts:437
form_requirement_templates.sector_applicability	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.template_name	NOT NULL	7	app/api/cron/content-gaps/route.ts:62
form_requirement_templates.template_type	NOT NULL	2	lib/domains/procurement/form-templating/template-coverage.ts:501
form_requirement_templates.template_version	nullable	4	app/api/cron/content-gaps/route.ts:62
form_requirement_templates.word_limit_guidance	nullable	1	lib/domains/procurement/form-templating/template-coverage.ts:501
form_response_history.change_reason	nullable	1	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_response_history.edited_by	nullable	9	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_response_history.response_id	NOT NULL	6	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_response_history.response_text	nullable	1	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_response_history.response_text_advanced	nullable	1	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_response_history.review_status	NOT NULL	1	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_response_history.version	NOT NULL	3	app/api/procurement/[id]/responses/[rId]/history/route.ts:66
form_responses.approved_by	nullable	2	app/api/procurement/[id]/responses/[rId]/route.ts:55
form_types.key	NOT NULL	2	tests/integration/q-a-pairs/question-match-search.integration.test.ts:712
form_types.label	NOT NULL	2	components/procurement/form-type-picker.tsx:52
guide_sections.content_type_filter	nullable	2	lib/guide-section-mapping.ts:120
guide_sections.description	nullable	1	lib/mcp/tools/guides.ts:268
guide_sections.expected_layer	nullable	2	lib/guide-section-mapping.ts:120
guide_sections.section_name	NOT NULL	2	lib/guide-section-mapping.ts:120
guide_sections.subtopic_filter	nullable	2	lib/guide-section-mapping.ts:120
ingestion_quality_log.ingestion_batch	nullable	1	tests/integration/bl46-quality-scan-flag-type.integration.test.ts:47
ingestion_quality_log.resolution_notes	nullable	1	app/api/review/history/route.ts:96
notifications.dismissed_at	nullable	6	app/api/notifications/route.ts:21
q_a_pair_history.answer_advanced	nullable	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.answer_standard	NOT NULL	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.changed_by	nullable	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.edit_intent	nullable	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.origin_kind	NOT NULL	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.publication_status	NOT NULL	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.q_a_pair_id	NOT NULL	8	tests/integration/q-a-pairs/promote-corpus.integration.test.ts:442
q_a_pair_history.question_text	NOT NULL	1	app/api/q-a-pairs/[id]/history/route.ts:60
q_a_pair_history.version	NOT NULL	3	app/api/procurement/[id]/responses/draft-stream/route.ts:196
record_lifecycle.previous_freshness	nullable	2	app/api/cron/freshness-transitions/route.ts:160
record_lifecycle.q_a_pair_id	nullable	1	lib/mcp/tools/governance.ts:1116
taxonomy_domains.description	nullable	3	app/api/admin/taxonomy-sync/route.ts:49
taxonomy_domains.display_name	nullable	1	contexts/taxonomy-context.tsx:48
taxonomy_subtopics.display_name	nullable	1	contexts/taxonomy-context.tsx:64
template_completions.job_id	nullable	1	app/api/procurement/[id]/fields/route.ts:289
user_profiles.email	nullable	1	app/api/admin/users/route.ts:88
user_profiles.full_name	nullable	1	app/api/admin/users/route.ts:88
user_profiles.id	NOT NULL	2	app/api/admin/users/route.ts:88
user_roles.display_name	nullable	1	app/api/review/history/route.ts:122
verification_history.source_document_id	nullable	1	app/api/admin/provenance/export/verification-history/route.ts:84
workspaces.status	nullable	1	app/api/workspaces/route.ts:60

6.2 Which of those are real — verified against the live DB

I checked SQL-function inserters, triggers, and live row counts for the largest clusters:

Table	fn inserters	triggers	live rows	Verdict
q_a_pair_history	1	0	1,449	Not a gap — RPC-written, heavily populated
form_response_history	1	0	0	Not a gap — RPC-written, feature simply unused so far
user_profiles	1	0	7	Not a gap — RPC/trigger-written
application_types	0	0	6	Not a gap — migration seed data
taxonomy_domains	0	0	7	Not a gap — migration seed data
entity_aliases	0	0	14	Not a gap — migration-seeded, read by the Python legacy-alias preload. read-never-written is the intended design
citations	0	0	1	Worth a look — 8 read-never-written columns, one row
form_requirement_templates	0	0	0	Real gap — see below
content_chunks	0	0	204	Declaratively written; heading_* confirmed permanently NULL
source_documents	2	2	71	Mixed; see §4.1 and §6.3

form_requirement_templates is the one genuine cluster: 17 columns read by production code, zero writers on any surface, and zero rows in staging. Its intended writer is the human-gated catalogue-form-requirements skill (Path C promotion), which appears never to have been run. Every read of this table currently returns nothing. This is a product gap, not a test bug, and it is worth raising with the owner separately from the S506 thread.

6.3 source_documents columns that are permanently NULL

Confirmed on staging across 71 rows — no writer on any surface, and empirically zero populated:

Column	rows populated
pipeline_run_id	0 / 71
workspace_id	0 / 71
uploaded_by	0 / 71

pipeline_run_id is the notable one: it is the obvious provenance join from a document back to the run that produced it, the FK exists, and nothing ever sets it. Tests currently reconstruct that link by matching op_id instead. workspace_id being unset means source_documents rows are not workspace-scoped in practice, which is worth confirming against the multi-tenant RLS intent.

Also never written and never read: auth, cadence, locator, parent_id — four columns that look like a planned ingestion-source feature that was never built.

6.4 Dead columns — never written and never read anywhere (157 across 45 non-priority tables)

Retirement candidates. Anything here that is also absent from a migration seed is safe to consider dropping, though the §6.2 caveat about invisible SQL writers applies to each.

table	never-written & never-read columns	count
tag_morphology_drift_flags	affected_content_ids, decided_at, decided_by, decision, decision_rationale, detected_at, id, proposed_canonical, stored_tag, usage_count	10
question_matches	created_at, embedding_score, form_question_id, fulltext_score, id, matched_at, q_a_pair_id, question_kind, updated_at	9
classification_disputes	current_value, disputed_by, proposed_value, rationale, resolution_notes, resolved_at, resolved_by, updated_at	8
q_a_pair_history	alternate_question_phrasings, anti_scope_tag, scope_tag, source_workspace_id, superseded_by, valid_from, valid_to	7
form_outcome_types	applicable_form_types, counts_toward_win_rate, key, label, provenance, stage	6
eval_touchpoints	created_at, file_sha256, grounding_shape, severity_on_fail, updated_at, variance_band	6
promotion_dispositions	action, actor, created_at, extraction_id, id, proposed_snapshot	6
user_notification_prefs	auto_generate_change_reports, created_at, email_owned_content_flagged, email_review_assigned, email_weekly_change_report, updated_at	6
corpus_writer_fence_lease	acquired_at, expires_at, fence_name, holder_label, holder_token	5
coverage_targets	created_at, created_by, id, updated_at, updated_by	5
eval_runs	exit_class, metrics, run_at, severity_disposition, source	5
application_types	created_at, provenance, state_machine_config, updated_at	4
competitor_research_workspaces	created_at, id, updated_at, workspace_id	4
product_guide_workspaces	created_at, id, updated_at, workspace_id	4
sales_proposal_workspaces	created_at, id, updated_at, workspace_id	4
tenant_config	config, created_at, id, updated_at	4
training_onboarding_workspaces	created_at, id, updated_at, workspace_id	4
user_roles	created_at, granted_by, id, updated_at	4
content_propagation_version	applied_at, payload_checksum, payload_key, version	4
engagement_group_content	created_at, engagement_group_id, id, q_a_pair_id	4
taxonomy_domains	accepted_at, created_at, recommended_at, recommended_by	4
engagement_groups	created_at, created_by, updated_at	3
intelligence_workspaces	created_at, id, updated_at	3
taxonomy_subtopics	accepted_at, recommended_at, recommended_by	3
eval_baseline_audit	at, id	2
form_types	created_at, provenance	2
taxonomy_sync_state	created_at, id	2
company_profiles	created_at, updated_at	2
feed_articles	created_at, updated_at	2
feed_sources	created_at, updated_at	2
form_instances	evaluation_methodology, status_reason	2
form_requirement_templates	created_at, updated_at	2
form_response_history	metadata, source_record_ids	2
guide_sections	created_at, parent_section_id	2
record_lifecycle	created_at, updated_at	2
review_assignments	completed_at, updated_at	2
signup_policy	allowed_domain, id	2
verification_history	owner_kind, q_a_pair_id	2
si_processing_queue	created_at	1
user_profiles	updated_at	1
citations	cited_q_a_pair_version	1
eval_baselines	id	1
form_questions	template_requirement_id	1
ingestion_quality_log	source_url	1
q_a_pairs	valid_to	1

---

7. Recommendations

1. Fix source_documents.extracted_text (§4.1). It is the only finding here that is actively breaking a test, and it also leaves 12 production read sites consuming a permanently-NULL column. Prefer adding it to SOURCE_DOCUMENTS_SCHEMA over retargeting the test.
2. Decide on pipeline_runs.items_updated / items_skipped / cost (§4.3) — populate them or drop them. Today they are guaranteed-zero columns that look like real telemetry.
3. Raise form_requirement_templates separately (§6.2) — 17 columns, 0 rows, a whole feature surface with no data behind it. Not an S506 concern, but it should not stay invisible.
4. Wire source_documents.pipeline_run_id (§6.3), or drop the FK. Provenance is currently reconstructed by op_id matching because the dedicated column is empty.
5. Leave the 135 info-class columns alone. created_at/updated_at/id omission is a deliberate, documented convention.
6. Tooling: two gaps surfaced that will distort any repeat of this audit — sqlglot is not installed (silently degrading Python SQL verdicts to regex), and neither tool can see cocoindex TableSchema declarative writes. The latter is worth a small addition to ast-dataflow-py: parse TableSchema(columns={...}) literals in flow.py and emit them as writes. Without it, schema-coverage will keep reporting the pipeline’s genuine output columns as unwired.

8. Known limits of this audit

• Invisible writers. RPC/SQL-function bodies, triggers, migration seed data, api.* views, and external PostgREST consumers are outside both tools. §6.2 spot-checks the largest clusters; it is not exhaustive across all 45 tables in §6.4.
• select('*') reads are counted as soft/wildcard evidence and deliberately never promote a column to wired — conservative by construction, so a few read-never-written entries may have a wildcard reader.
• Staging row counts are small (7 pipeline-produced source_documents rows). The extracted_text conclusion is robust because it is 0-of-7 and backed by the static absence from SOURCE_DOCUMENTS_SCHEMA; conclusions resting on row counts alone would not be.
• Verdicts reflect main @ 8ef32193 and the staging DB as of 2026-07-28.