id-375 research — baseline

baseline

Summary

ast-dataflow’s column-reads/column-writes work well for hot, literally-addressed tables (e.g. 22 exact reads on form_questions.question_text) but the tool cannot support a trustworthy schema-coverage (“built-not-wired”) report today. Measured across 40 CLI invocations over 18 columns: every invocation is cold (mean 9.3s wall, 5.5s of it repeated project-load overhead; a 70-table/807-column full sweep would take ~4.2h serially), there is no schema enumeration, no table/column existence validation (the dropped table bid_questions — still the tool’s own catalogue example — returns silent 0/0), and three of four “unwired” (0-reads+0-writes) verdicts in my sample were false: real access hides behind variable .from(TABLE_CONST) sites (~20+ in-repo, acknowledged as “the 22 dynamic .from(variable) sites” in lib/supabase/schema.ts), SQL function bodies (62 public RPCs, invisible to both tools), and the Python pipeline (separate ast-dataflow-py CLI with no result union). Meanwhile indirect/wildcard rows are unfalsifiable noise: a nonexistent column got 5 indirect “writes” and 4 wildcard “reads”. RPC column detection effectively never fires because the codebase’s p_-prefixed RPC params never equal column names, and api-schema views (63 of them, the app’s actual runtime surface) plus Tables<‘x’> type-only usage are outside the model entirely.

Findings

1. Schema inventory (from supabase/types/database.types.ts)

• 70 public-schema tables, 807 Row columns (parsed via scratch script; e.g. source_documents line 7377 with 44 cols, form_instances line 5594 with 29 cols).
• 0 Views in public — but the generated types contain an api schema (line 15) and the latest api-views migration (supabase/migrations/20260717112347_id145_api_views_regen3.sql) contains 63 CREATE VIEW api.* WITH (security_invoker = true) statements.
• 62 public RPC functions (claim_next_job, hybrid_search, merge_entities, corpus_writer_fence_*, hook_restrict_signup_to_allowed_domain, …).
• The prompt’s “hot tables” content_items and bid_questions no longer exist; yet bid_questions is still the example in the CLI catalogue (tools/ast-dataflow/cli.ts:146,158), in ROADMAP.md:60’s smoke-of-record (“48 rows on bid_questions.project_id”), and content_items in .claude/skills/ast-dataflow/SKILL.md:120,136.

2. Measured runs (commands + timings)

Single manual run: time bun run ast-dataflow column-reads --table form_questions --column question_text → 13.4s wall, 22 rows all exact. Then 36 invocations via a batch driver spawning one CLI process each (scratchpad/audit-matrix.ts; full log tasks/bcn2do29p.output, per-row JSON scratchpad/audit-matrix-results.json): 336s total, mean 9.3s, range 5.5–19.8s. Envelope per run: {query, args, results[], truncated, durationMs}; rows: {file, line, column, confidence, method, columnPath, table, isTyped}. Mean in-process query time 3.9s vs 9.3s wall → 5.45s (58%) is per-invocation process+ts-morph-project-load overhead (index.ts:46–53 builds a fresh Project from tsconfig every call; no cache exists — PRODUCT.md invariants 19–21 “warm-cache” are unimplemented, ROADMAP R-WP9 explicitly deferred). No errors, exit 0 on everything, including nonexistent tables/columns.

table.column	reads (confidence)	writes (confidence)	verdict
form_questions.question_text	22 (22 exact)	11 (6e/5i)	wired
form_instances.workflow_state	18 (18e)	10 (6e/4i)	wired
source_documents.publication_status	20 (14e/4w/2i)	12 (4e/8i)	wired
q_a_pairs.answer_standard	14 (10e/4w)	26 (19e/7i)	wired
feed_articles.extraction_method	0	6 (2e/4i)	write-only (grep-confirmed true)
record_lifecycle.previous_freshness	5 (2e/3w)	4 (4i)	wired
citations.cited_version	1 (1e)	1 (1i)	thinly wired
form_responses.overall_score	3 (3e)	6 (3e/3i)	wired
signup_policy.allowed_domain	0	0	”unwired” — FALSE NEGATIVE
content_propagation_version.payload_checksum	0	0	”unwired” — FALSE NEGATIVE
corpus_writer_fence_lease.holder_token	0	0	SQL/Python-only — invisible to both tools
source_documents.retention_class	4 (4 wildcard)	10 (1e/9i)	undecidable from tool output
q_a_pairs.anti_scope_tag	4 (4w)	9 (2e/7i)	undecidable reads
tag_morphology_drift_flags.decision_rationale	2 (2i)	2 (2i)	low-confidence wired
eval_baseline_audit.registry_version	0	1 (1e)	write-only candidate
form_instances.evaluation_methodology	0	4 (4i)	built-not-wired candidate (see §4)

Probes: bid_questions.project_id (dropped table) → 0/0, exit 0, no error. form_questions.no_such_column_xyz → 0 reads but 5 indirect writes. source_documents.definitely_not_a_column → 4 wildcard reads. workspaces.workspace_id (not a column of workspaces) → 0 incl. rpc-payload.

3. Verified false negatives (grep sanity checks)

1. signup_policy.allowed_domain — tool 0/0, but scripts/seed-tenant-from-bundle.ts:161–162 does .from(SIGNUP_POLICY_TABLE).select('allowed_domain') and scripts/reseed-tenant-instance.ts:96–97 .from(SIGNUP_POLICY_TABLE).upsert(...). findFromCalls (queries/supabase-shared.ts:140–172) only matches string-literal/no-substitution-template table args, so const-identifier table names are invisible. The column is additionally load-bearing in the SQL auth hook hook_restrict_signup_to_allowed_domain — a fail-closed signup gate a naive report would flag as dead.
2. content_propagation_version.payload_checksum — tool 0/0, but scripts/propagate-canonical-content.ts:60 (const VERSION_TABLE = 'content_propagation_version') and :483–487 upsert payload_checksum: checksum via .from(VERSION_TABLE).
3. corpus_writer_fence_lease.holder_token — all access is inside SQL function bodies (corpus_writer_fence_lease_acquire/_release) invoked from Python raw SQL (SELECT public.corpus_writer_fence_lease_acquire($1,$2,$3), per scripts/tests/test_cocoindex_writer_fence.py:108,145). Invisible to both TS and PY tools — neither parses migration SQL.
4. Blind-spot scale: lib/supabase/schema.ts:8–9 itself documents “the 22 dynamic .from(variable) sites”; grep found 20+ non-storage ones (lib/mcp/tools/governance.ts:264, lib/mcp/tools/content.ts:1132,1192, lib/governance/review-action-owner.ts:72, app/api/taxonomy/reorder/route.ts:66, scripts/quality-gate.ts:450,482, scripts/export-user-data.ts:431, scripts/propagate-canonical-content.ts:207 — which does .from(table).select('*') over whole tables).

True negative confirmed: feed_articles.extraction_method reads=0 is genuine — every extraction_method read (app/reference/[id]/page.tsx:113, components/source-document-detail/source-document-provenance.tsx:52) targets source_documents; writers are lib/intelligence/pipeline.ts:368,453 + scripts/seed-platform-feed.ts:241. The tool’s per-table attribution beat naive grep here.

4. Cross-language gap (ast-dataflow-py)

bun run ast-dataflow-py column-writes --table source_documents --column extraction_method → 1.2s, finds scripts/cocoindex_pipeline/flow.py:2809 (_upsert_source_document, source:"sql"), but "sqlglot": false — sqlglot not installed, so SQL parsing runs on regex fallback. Same envelope, separate CLI, no unioned result. For form_instances.evaluation_methodology, the Python pipeline extracts the value (scripts/cocoindex_pipeline/prompts.py:109, extraction.py:350) yet both tools report 0 confirmed writes and 0 reads anywhere — a textbook built-not-wired candidate that required manual grep across three corpora to even suspect. Py tool scans scripts/ only; migrations SQL is explicitly “a future extension” (tools/ast_dataflow_py/cli.py:36–37).

5. Views / RPC / typed-only access

• Views: the tool has no schema concept. At runtime every app .from('x') resolves to the api.x view (lib/supabase/schema.ts DB_OPTION seam); attribution to public tables works only by the 1:1 same-name coincidence. Column-level view coverage drift is checked by a separate script (scripts/check-api-view-coverage.ts), not by ast-dataflow. External PostgREST consumers of api views are invisible by nature.
• RPC: reads-side detects only .rpc('fn', { <column-name>: v }) payload keys — table-blind (queries/column-reads.ts:94–120 never links fn→table) and keyed on parameter names; since this codebase prefixes RPC params (p_workspace_id, app/api/intelligence/workspaces/[id]/metrics/trend/route.ts:35–36), the detector produced 0 rpc-payload rows in all 40 runs. Writes-side has no RPC detection at all (“deferred to S5+”, queries/column-writes.ts:24–28). The 62 RPC function bodies are invisible to everything. PRODUCT.md inv. 5’s promised “return-type fields when the type checker resolves them” and “destructuring on rows” are unimplemented.
• Typed-but-never-queried: column queries only walk .from()/.rpc() call chains — Tables<'x'> type usage (20 sites in app code) is never consulted, so “typed but never queried” is not a detectable category. type-evolution exists but requires a named interface + property, not a table.

6. Workflow friction catalogued (what a schema-coverage report must automate)

1. Schema enumeration — no query lists tables/columns; I wrote a parser for database.types.ts by hand (the data sits unused in the tool’s own corpus).
2. Per-column CLI loops — 2 invocations/column; 70×807-scale sweep = 1,614 invocations ≈ 4.2h serial wall, ~58% of it redundant project reloads. A single corpus walk could collect all .from() chains for every table/column at once (~one query’s cost).
3. No existence validation — dropped tables and typo’d columns return clean 0/0 (exit 0), indistinguishable from “built-not-wired”. The strongest signal the mission needs is the one the tool cannot give.
4. Unfalsifiable indirect/wildcard tiers — nonexistent column: 5 indirect writes, 4 wildcard reads. 44 select('*') sites repo-wide poison the read side of every column on those tables. Consumers must invent their own “exact-only vs cannot-rule-out” classification.
5. No cross-language union — TS and PY results must be merged by hand; SQL (migrations, function bodies, views) has no scanner at all.
6. No report/rollup output — everything is per-column JSON; type-drift-detect proves the tool can render classified Markdown reports (cli.ts:259–362) but no schema-shaped equivalent exists (nothing in ROADMAP.md either — the only report machinery is type-drift).
7. Dynamic .from(variable) sites dropped silently, despite one-hop identifier resolution already existing for write payloads (queries/column-writes.ts:49–81) — the same technique is simply not applied to the table argument.

Recommendations

A schema-coverage report query needs these concrete capabilities, roughly in dependency order:

1. Schema enumeration from the generated types — parse Database['public']['Tables'] (and ['api'], ['Functions']) out of supabase/types/database.types.ts to drive the sweep and to validate table/column existence, erroring loudly (unknown_table / unknown_column structured error) instead of silent 0/0. This single change removes the dead-table-looks-unwired failure and fixes the stale bid_questions/content_items examples problem at the root.
2. One-pass, all-columns scan — a single corpus walk that indexes every .from() chain and emits per-(table, column, direction, confidence-tier) counts for the whole schema in one invocation (~10–20s), instead of 1,614 CLI calls (~4.2h). The per-file .from()-chain walk already exists in supabase-shared.ts; only the fan-out is per-column today.
3. Resolve one-hop const table names — apply the existing resolveOneHopObjectLiteral-style identifier resolution (column-writes.ts:49) to the .from(X) argument. This alone converts 3 observed false “unwired” verdicts (signup_policy, content_propagation_version, and ~20+ acknowledged dynamic sites) into correct attributions; sites that still can’t resolve should be reported as a per-table “unattributable access” flag, not dropped.
4. Confidence-tiered classification semantics — report each column as one of: wired-exact (≥1 exact read AND write), write-only, read-only, undecidable (only wildcard/indirect evidence — e.g. source_documents.retention_class), unwired (0 rows AND no wildcard/indirect/unattributable smoke on the table). Never let indirect/wildcard rows count as wiring evidence — a nonexistent column collects both.
5. Cross-language union built in — invoke/import ast-dataflow-py (1.2s/query; near-free in one pass) and merge per column with a source: ts|py field; install sqlglot so the PY side isn’t regex-only. Add a third scanner (or at minimum a declared “SQL-opaque” flag) for tables touched by the 62 RPC function bodies and api views, sourced from migrations — otherwise columns like corpus_writer_fence_lease.* will be misreported as dead.
6. RPC→table mapping — the current param-name heuristic never fires (p_ prefix convention). Either parse RPC function bodies from migrations SQL to map fn→columns, or maintain a small fn→table manifest; also add rpc detection to the writes side (currently absent).
7. Owner-facing report output — reuse the type-drift-detect pattern (Markdown summary table + classified sections + JSONL backing + --ci baseline diff) for a docs/generated/schema-coverage-report.md: per-table rollups, “unwired columns” first, each with its evidence tier and the caveats (wildcard sites on the table, unattributable dynamic access, SQL-opaque RPCs). A non-technical owner needs the caveats attached to each verdict, not raw row counts.
8. Amortise the cold start — for the report this is solved by one-pass design (item 2); for interactive per-column use, either the deferred LMDB cache (ROADMAP R-WP9) or the deferred MCP server (OQ-R4, keeps the ts-morph project resident) — measured saving is 5.45s of 9.3s per invocation.
9. Optional: Tables<'x'> type-usage cross-check — flag columns that appear in type annotations but have zero query-chain evidence (“typed but never queried”), a distinct built-not-wired flavour the current queries cannot see.