id-399 — Wiring-census protocol (four-surface verdict discipline)

Wiring-census protocol — four surfaces or no verdict

Task: id-399 (initiative 12 phase-1 gate). Landed: 2026-07-29. Rule: no sweep verdict is believed until it covers three code surfaces (TS query chains + Python + flow.py TableSchema declaratives) plus a pg_proc/migrations scan. This note is the runbook the S6 wire-or-retire sweep runs from, and the record of the first full census run.

The S507 audit (specs/id-128-test-ci-integration/S507-ast-dataflow-schema-wiring-audit.md) proved two blind spots that motivated this protocol: cocoindex TableSchema declarative writes were invisible to every tool, and DB-side readers/writers (RPC bodies, views, triggers, seed data) were invisible to every tool. Both are now closed at the tool layer and locked by regression fixtures (§5).

1. The four surfaces and who covers them

Surface	Producer	Output
TS query chains (.from() in the tsconfig corpus)	bun run ast-dataflow schema-coverage	verdicts (consumer of the sidecars below)
Python (raw asyncpg SQL, supabase-py, _SQL_* consts)	bun run ast-dataflow-py schema-uses --exclude-tests	v1 evidence sidecar (source: ast-dataflow-py)
flow.py declaratives (TableSchema / mount_table_target / declare_row)	same schema-uses run (source: declarative rows)	merged into the same sidecar
pg_proc/migrations (function bodies, views, triggers, DO blocks, seed/backfill DML)	python3 scripts/census/pg_evidence.py	v1 evidence sidecar (source: pg-census)

Everything joins through the v1 evidence-sidecar contract ({schemaVersion: 1, source, rows: [{table, column|"*", direction, confidence, method, file, line, source}]}) consumed by schema-coverage --evidence — verdict logic never learns where a row came from; evidence changes, not rules.

2. How to run the census

One command (from the canonical repo root):

python3 scripts/census/run_census.py --verify
# artefacts land in $TMPDIR/canonical-census/ (override with --out-dir):
#   py-evidence.json, pg-evidence.json, census-verdicts.json, census-report.md

The runner is the protocol: it hard-fails if the Python sweep’s envelope does not carry sqlglot: true ({399.2} — a sqlglot-less run contributes no SQL rows and must never pass as a clean sweep), echoes every producer caveat, and --verify re-runs the S507 regression fixtures (§5). Equivalent manual steps:

bun run ast-dataflow-py schema-uses --exclude-tests > /tmp/py.json   # check "sqlglot": true
python3 scripts/census/pg_evidence.py --out /tmp/pg.json
bun run ast-dataflow schema-coverage --evidence /tmp/py.json --evidence /tmp/pg.json \
  --report /tmp/census-report.md

2.1 Live pg_proc parity (the staging cross-check)

The static scan replays migration history; the live check proves the history matches reality. Read-only, against Platform staging only:

python3 scripts/census/pg_evidence.py --emit-live-sql
# run that query against staging (MCP supabase execute_sql, or psql),
# save the JSON array, then:
python3 scripts/census/run_census.py --verify --live-json /path/to/live-pg-proc.json

The parity result lands in the pg sidecar’s livePgProcParity caveat: liveOnlyFunctions (out-of-band DDL — scanned for evidence when the dump includes prosrc), migrationOnlyFunctions (stale static evidence), and bodyDriftFunctions (md5 mismatch between the last-writer migration body and live prosrc).

3. Evidence semantics (what each method value means)

method	Meaning	Weight in wire-or-retire
pg-function-body	statement in a live SQL/plpgsql function (last-writer CREATE [OR REPLACE], DROP respected)	runtime evidence
pg-view	a live public-schema view’s defining SELECT	runtime evidence
pg-view-api	api.* mirror-view projection — reads demoted to indirect	exposure, NOT consumption — never wires a column
pg-trigger-body	NEW./OLD. refs in a live trigger’s function, attributed to the attached table (NEW.col := at a statement boundary = write; other refs = read)	runtime evidence
migration-dml	top-level INSERT/UPDATE/DELETE in a migration (seed/backfill)	HISTORICAL write — proves the column was populated once, not that anything populates it today
migration-do-block	statement inside a DO $$…$$ block	historical, as above
seed-sql-dml / seed-sql-do-block	supabase/seed.sql statements. The S507 §6.2 “migration seed data” clusters (entity_aliases, application_types, taxonomy_domains, form_types, guide_sections…) actually live HERE, not in migrations	seed surface — applied on db reset, not on deploy; weigh accordingly
pg-function-body-live	statement in a live-only function from --live-json (out-of-band DDL)	runtime evidence, flagged provenance

Attribution is scope-aware (sqlglot traverse_scope): qualified columns resolve through the per-scope alias map at exact; unqualified columns resolve against the current generated schema — exactly one in-scope candidate table is exact (Postgres’s own resolution rule), several candidates are indirect on each. Rows naming tables/columns outside the CURRENT schema are dropped and counted (rowsFilteredUnknownTableOrColumn) — the pg sidecar is guaranteed to join cleanly (zero evidenceUnknownTables entries from pg-census on the first run; the 60 entries present all come from ast-dataflow-py’s known multi-table over-attribution).

One deliberate divergence from ast-dataflow-py: write direction is confined to columns a statement actually mutates (INSERT column lists / ON CONFLICT SET / UPDATE SET targets). Columns in WHERE / USING / RETURNING / DELETE predicates are READS — a column consumed only as a filter is read, not written.

4. Standing verdict rules (bake these into every sweep verdict)

1. Declaration-only columns are undecidable, never wired. TableSchema membership emits indirect writes only (declared ≠ written — source_documents declares 22 columns, its raw-SQL upsert writes 26). A resolved declare_row payload is a real (exact) write.
2. select('*') / SELECT * reads are wildcard evidence — soft, never promote a column to wired.
3. api.* mirror-view reads never wire a column (demoted to indirect by the producer). PostgREST exposure is not consumption. External PostgREST consumers remain invisible to every static surface — that residual caveat stands.
4. migration-dml / seed-sql-* writes are historical/seed writers. They legitimately clear “read-never-written” false alarms (S507 §6.2), but a retire decision needs a runtime writer or an explicit seed-data rationale.
5. Heed the caveat channels — each names evidence the sweep did NOT see:
   • py sidecar: sqlFunctionSourceSites (stored-proc calls, zero column tokens), sqlSitesUnresolvedDynamic (3 l_records conditional sites), rpcPayloadSitesSkipped;
   • pg sidecar: sqlUnparsedStatements (1 on the first run — a LIKE…ESCAPE shape in a squash-baseline pg_tables helper), dynamicSqlExecuteSites (6 plpgsql EXECUTE sites), triggersOnUnknownTables (bindings to renamed/dropped tables), livePgProcParity;
   • consumer: evidenceUnknownTables, unattributableSites.
6. A wired verdict is necessary, not sufficient, to keep a column — and an unwired verdict is necessary, not sufficient, to drop one: static absence plus a live population check (S507 method) is the drop standard.

5. R8-protected columns — NEVER blind-drop

Whatever verdict the census returns, these five are protected: they are the future source-binding register shape (initiative 12 R8). The runner prints their verdicts on every --verify run and fails if any leaves the schema.

Column	First-run verdict (2026-07-29)
source_documents.auth	undecidable
source_documents.cadence	undecidable
source_documents.locator	undecidable
source_documents.origin_type	write-only
source_documents.parent_id	read-only

(The api-mirror demotion is what keeps auth/cadence/locator honest: before it, the generated api.source_documents projection flipped them to read-only on mechanical evidence.)

6. Regression fixtures ({399.3}) — the census’s own acceptance test

Executable in two places: python3 scripts/census/run_census.py --verify (fixtures F1–F5 against a fresh sweep) and python3 -m pytest scripts/tests/test_census_pg_evidence.py (17 tests: synthetic-corpus unit coverage + the real-corpus S507 fixtures; static-only, runs in the default pytest lane).

Fixture	Assertion	First-run result
F1 — S507 blind spot (i)	pg sidecar surfaces exact migration-sourced reads of content_chunks.content, including supabase/migrations/20260716120000_id145_37_repoint_search_rpcs_to_form_instances.sql (hybrid_search’s snippet/rank/summary reads, the body containing lines 446–466)	PASS — 3 exact DB-side reads (id131 search RPCs :385, id145_37 :338, api regen view)
F2	merged verdict content_chunks.content = wired	PASS
F3 — S507 blind spot (ii)	py sidecar carries a declarative write for reference_items.body; merged verdicts for reference_items.body/ingestion_source/published_at = wired	PASS (PR #150 tool half confirmed)
F4 — negative control	form_requirement_templates.requirement_text stays read-only — the S507-confirmed REAL gap (17 read columns, zero writers, zero rows) must not be rescued by any new surface	PASS
F5	all five R8 columns present in the verdict set, verdicts printed	PASS

Note on F1/F2 time-drift: since S507, id-392 also landed a TS composed-body read of content_chunks.content, so the merged verdict would be read-wired even without pg evidence. F1 therefore asserts on the pg producer’s own rows (the blind-spot closure), not just the merged verdict — the fixture cannot be silently satisfied by app-code drift.

7. First full census run — 2026-07-29 (repo @ 8b397be1, staging rbwq…)

Merged verdict summary (807 columns): wired 435, read-only 154, write-only 86, undecidable 115, unwired 17 — against the S507-era TS+py baseline of wired 364 / unwired 78. 256 verdicts changed once DB-side evidence joined. Notable reclassifications:

Column	S507-era	Census	Why
content_chunks.content	written-never-read	wired	search-RPC readers visible (F1)
entity_aliases.alias/canonical/is_active/provenance	read-never-written	wired	seed.sql writer visible
application_types.*, taxonomy_domains.* seed clusters	read-never-written	wired	seed.sql writer visible
q_a_pair_history.q_a_pair_id/version	read-never-written	wired	RPC/trigger writers visible (snapshot…/q_a_pairs_history_trigger)
pipeline_runs.items_updated/items_skipped	never-written	undecidable (soft only)	correctly NOT rescued — still decorative
form_requirement_templates.*	read-never-written (real gap)	read-only	correctly NOT rescued (F4)

Live pg_proc parity against staging: 127 live = 127 static; zero live-only, zero migration-only. bodyDriftFunctions: public.search_content (two live overloads collapse under the scanner’s name-keying — known limitation, see §8) and public.record_lifecycle_domain_sync (comment-only drift, dispositioned BENIGN and CLOSED at the S515 id-402 sweep — zero executable difference, zero evidence effect; see tasks/id-402.md. Do not re-investigate).

8. Known limits

• Functions are keyed by schema.name — overloads collapse to the last definition (matches scripts/check-migration-fn-table-refs.ts). Affects public.search_content (2 overloads).
• RLS policies, CHECK constraints, and index predicates are out of scope — column references there are not census evidence.
• Dynamic SQL (EXECUTE format(...)) is counted, never parsed (dynamicSqlExecuteSites).
• Triggers on renamed tables: bindings recorded under the old name are skipped and listed (triggersOnUnknownTables); Postgres carries the trigger to the new name, so trigger evidence there is under-reported. The live parity check is the corrective.
• seed.sql applies on db reset, not on deploy — seed writes are a distinct method so the sweep can weigh them.
• External PostgREST consumers remain invisible everywhere; api mirror views prove exposure only (§4, rule 3).