RESEARCH — DB-layer type source-of-truth (warp model): implementation-surface inventory

RESEARCH — DB-layer type source-of-truth (warp model): implementation-surface inventory

Subtask: ID-47.1 (RESEARCH). Parent Task: ID-47 — make supabase/types/database.types.ts (+ a MergeDeep JSONB override) the canonical, CI-guarded source of truth for DB/row shapes (the warp-model analog for KH’s DB layer) and retire docs/reference/SCHEMA-QUICK-REFERENCE.md. Status: RESEARCH — inventory only. No PRODUCT/TECH (dev infra, no user surface, per ID-47 task record + WP-D precedent). Feeds ID-47.2 PLAN. Strategic direction (SET, not re-litigated here): docs/specs/id-16-ast-dataflow-tool/investigations/type-safety-strategy-research-S262.md §3 (warp-analog → DB layer) + §3.2 (recommended, ~1–2 days) + §2.1 (DB→type layer + holes). This doc grounds the specific surface.

---

0. Method + verification discipline (S264 / OQ-3)

Every claim below was validated against installed code/CI/schema in this worktree (branch cmux-worker-subo-ast-e472c743, HEAD e472c743), not the S262 research’s prior claims. The S262 research itself flagged “confirm exactly what is shipped vs specced vs absent”; this RESEARCH does so, and corrects several stale references (flagged inline with ⚠ DRIFT). The Checker should re-run the greps below against a fresh tree.

Primary commands used (re-runnable):

grep -c 'Returns: Json' supabase/types/database.types.ts                 # → 14
rg -n 'SCHEMA-QUICK-REFERENCE' --hidden -g '!.git' -g '!.planning/.archive/**' .
rg -n 'gen types|database.types|supabase-types-parity' .github/workflows/  # → (none)

Note on this RESEARCH’s own external-symbol verification (OQ-3 scope). The only external symbol the implementation will rely on is type-fest’s MergeDeep (per type-safety-pipeline TECH §“Enhanced type inference”). This is a forward dependency for ID-47.2/implementation, not a citation in this inventory, so its pin-check is deferred to the PLAN/TECH stage. Flagged in the feed-forward (§6) as a pre-implementation gate. No external library API is cited in this RESEARCH doc itself.

---

1. Parity / type CI — SHIPPED vs SPECCED vs ABSENT (verdict with file:line)

Verdict: the DB-axis types-parity gate is ABSENT. The route/fetcher type-drift gate is SHIPPED but is a DIFFERENT axis. The S262 research conflated them.

1.1 What is SHIPPED (confirmed file:line)

Job / workflow	File:line	What it actually checks	Type-parity relevance
type-drift-parity	.github/workflows/ci.yml:870	Runs bun run ast-dataflow type-drift-detect --ci (ci.yml:917-918); fails on a new fetcher-only response interface vs the 37-row baseline docs/generated/type-drift-baseline.json	Route/fetcher axis only. R-WP17. NOT a DB-types-parity gate.
ledger-mirror-parity	.github/workflows/ci.yml:920	Regenerates task-view ledger mirrors, fails on drift	Unrelated (ledger docs).
schema-parity.yml	whole file; on: workflow_dispatch only (schema-parity.yml:13-14)	pg_dump --schema-only of prod vs staging, diffs the SQL (schema-parity.yml:119-128)	SQL-schema prod↔staging diff, MANUAL-only. NOT TS-types parity, and does not run on PR.
supabase-advisors.yml	supabase-advisors.yml:1-5	scripts/run-supabase-advisors.ts — security + performance advisor findings	Orthogonal to types entirely.

The full PR-blocking topology (ci.yml) is the 7 documented jobs + type-drift-parity + ledger-mirror-parity + ci-summary aggregator (ci.yml:992-1087). None regenerates database.types.ts or diffs it against the live schema.

1.2 What is SPECCED-but-not-shipped (confirmed file:line)

Specced job	Spec location	Status
supabase-types-parity	docs/specs/id-16-ast-dataflow-tool/type-safety-pipeline/TECH.md:644 (full YAML at :640-679); cross-ref TECH :701, PRODUCT.md :6; acceptance criterion TECH :889	SPECCED, NOT SHIPPED. rg 'supabase-types-parity' .github/workflows/ → zero matches. The TECH §“Supabase types CI plan (OQ6)” (:627) describes the exact regenerate-then-diff job (regen from staging ref turayklvaunphgbgscat, diff vs committed, fail on drift) — but it was never wired into ci.yml.

1.3 ⚠ DRIFT corrections to the S262 research’s CI claims

• S262 §2.1 / §3.2 imply a shipped supabase-types-parity net (“drifts only if a migration lands without re-running gen types — which the proposed supabase-types-parity CI job … closes”; §7 step 1 lists it as “already specced”). Reality: it is specced only, never shipped. The S262 wording (“proposed”, “specced”) is technically accurate but the surrounding framing (“KH is ~80% there”) can read as if the gate exists. It does not.
• S262 §2.4 cites type-drift-parity at ci.yml:870. ✅ Confirmed exact — the job header is at ci.yml:870, the CLI invocation at ci.yml:917-918. But this is the route/fetcher detector (R-WP17), a different axis from DB-types parity. It does not protect database.types.ts.

Net for ID-47: the headline gap is real and unguarded — add a supabase-types-parity job (the TECH already wrote the YAML; ID-47.2 lifts + hardens it). This is “CI job to add”, item (iii) below.

---

2. Consumers of the MANUAL docs/reference/SCHEMA-QUICK-REFERENCE.md

SCHEMA-QUICK-REFERENCE.md is 1,392 LOC, catalogued in docs/reference/documentation-inventory.md:193 as authoritative / agent audience / “All tables, columns, functions, views, RLS policies + CHECK constraints”, last verified 21/05/2026 — i.e. a hand-maintained doc that has already drifted (it still references digests, renamed to change_reports in S251; see §3.3).

Reference counts (fresh rg, 25/05/2026): 127 live files (excluding .planning/.archive/ and .git); 143 additional files inside .planning/.archive/ (point-in-time snapshots — not live consumers, no redirect needed). The 127 live references break down as follows. The load-bearing subset that MUST be redirected before retirement is small — most of the 127 are historical continuation-prompts, change-logs, verifier-reports, research/investigation docs, and per-record ledger mirrors that quote the doc in passing and need no action.

2.1 Tier 1 — load-bearing consumers (MUST redirect/update before retirement)

Code + tests (3) — an agent following these lands on the manual doc:

File:line	Nature of reference	Action
lib/governance/publication-transitions.ts:42	Doc-comment: UserRole “Mirrors get_user_role() return values per docs/reference/SCHEMA-QUICK-REFERENCE.md”	Prose cross-ref only (no runtime dep). Re-point to database.types.ts Enums<'user_role'> / the canonical source.
scripts/generate-taxonomy-snapshot.ts:84	Comment: “Fallback: extract from SCHEMA-QUICK-REFERENCE if RPC not available”	Comment only — the actual fallback reads information_schema CHECK constraints, not the doc. Update comment; verify no literal doc-path read.
__tests__/migrations/no-app-guc-rls-policy.test.ts:158	Failure-message string instructs “Document closure in SCHEMA-QUICK-REFERENCE.md §32.1.x”	Update the guidance string to point at the new canonical source.

Agent / skill / root-instruction files (7) — these route future agents:

File:line	Reference
CLAUDE.md:74	”Schema defined in docs/reference/SCHEMA-QUICK-REFERENCE.md” (Database section)
AGENTS.md:121	”Schema entity names: docs/reference/SCHEMA-QUICK-REFERENCE.md”
AGENTS.md:253	Example doc reference
.claude/skills/implement-subtask/SKILL.md:76	”consult … when the change touches [schema]“
.claude/skills/spec-driven-implementation/SKILL.md:201	Schema reference
.claude/skills/implement-specs/SKILL.md:55	Schema reference
.claude/skills/write-tech-spec/SKILL.md:60	”Schema: docs/reference/SCHEMA-QUICK-REFERENCE.md + …”

These are the highest-leverage redirects: they are the instructions that tell the next agent where the schema truth lives. Retiring the doc without updating these would silently send agents to a dead/stale reference.

Catalogue / inventory entries (1 doc, 3 lines) — guard-relevant:

File:line	Reference
docs/reference/documentation-inventory.md:193, :741, :753	Catalogues the doc as authoritative. The inventory is enforced by a doc-freshness guard test (CLAUDE.md “Guard tests break on structural changes” — doc-freshness.test.ts). Removing/retiring the doc requires updating this catalogue or the guard test will fail.

2.2 Tier 2 — active reference/product docs citing it (update opportunistically, not blocking)

~30 active docs under docs/reference/, docs/runbooks/, docs/product-functionality/, docs/operations/, docs/ontology/ reference it (e.g. docs/reference/state-of-the-product.md, docs/runbooks/production-setup-guide.md, docs/product-functionality/_templates/technical.template.md:?, docs/reference/auth-hooks.md). The _templates/technical.template.md entry is notable: it propagates the reference into every future product-functionality doc generated from the template — so the template should be redirected to stop the bleed. The rest are prose cross-references; bulk find-and-replace or a redirect stub (§6) covers them.

2.3 Tier 3 — historical / point-in-time (NO action; do not redirect)

143 files in .planning/.archive/, plus live-tree continuation-prompts (docs/continuation-prompts/**, ~40 hits), change-logs (state-of-the-product-change-log-section-{5,8}.md), verifier-reports, research docs, ledger mirrors (docs/reference/{tasks,roadmap}/*.md, task-list.json/product-roadmap.json regenerate from JSON), and the spec docs that describe this very work (type-safety-pipeline/TECH.md, type-safety-strategy-research-S262.md, tasks/ID-47.md). These are immutable records of past sessions; rewriting them would corrupt the historical record.

Retirement strategy implied (for ID-47.2): redirect Tier 1 (10 files) + the doc-inventory catalogue + the product-doc template; replace the doc body with a redirect stub (“Schema truth now lives in supabase/types/database.types.ts + database-overrides.ts; see CLAUDE.md TypeScript conventions”) rather than git rm, so the ~30 Tier-2 prose links don’t 404. Full deletion is a later cleanup once links are swept.

---

3. JSONB / opaque-Json columns + their domain types

This is the core of the override design. Two distinct categories with different fixes (the S262 §3.2 / §D distinction):

• Base-table JSONB columns → fixed TS-side via a MergeDeep override in supabase/types/database-overrides.ts (the schema.patch analog). No DB change. 21 columns confirmed.
• Opaque-Json RPC returns → cannot be fixed TS-side alone; need a PL/pgSQL RETURNS TABLE(...) migration (a DB change) before gen types can type them. 14 confirmed. A MergeDeep override on a function Returns is not the idiomatic fix (functions aren’t in the Tables shape MergeDeep patches); the DB migration is the real path. WP-C already triaged these (§3.2).

3.1 Base-table JSONB columns — TS MergeDeep override targets (21, installed)

Extracted from supabase/types/database.types.ts Tables block (lines 16–3434), Row-level fields typed Json | null / Json. This is the authoritative installed set (the Checker can reproduce via the awk in §0). Domain types chased in types/*.ts / lib/** where they exist:

#	Table.column	Domain type (source)	Override priority
1	workspaces.domain_metadata	ProcurementMetadata (types/procurement.ts:27) — buyer/status/deadline/outcome etc. ⚠ S262 said BidMetadata from types/bid-metadata.ts; that file does not exist — the real type is ProcurementMetadata.	HIGH
2	content_items.summary_data	SummaryData (types/content.ts:78) — {executive, detailed, takeaways[], generated_at, model, tokens_used?}. ⚠ S262/TECH described {key_points, quotes}; the installed SummaryData shape differs — verify the live column shape before authoring the override.	HIGH
3	content_items.metadata	Record<string, unknown> | null (types/content.ts, the metadata field) — legacy flexible KV; documented legacy keys per SCHEMA-QUICK-REFERENCE §36. No single strict type.	MED (keep loose / incremental)
4	company_profiles.competitors	Array of {name, website_url, notes, monitoring_priority} — Zod schema in lib/validation/schemas.ts (competitor entry with website_url: z.string().url().optional()); no standalone TS interface Competitor found. Domain type would be authored from the Zod schema.	HIGH
5	processing_queue.payload	QueueJobPayload<TBody> (lib/queue/envelope.ts:117) — fully-specified generic envelope {envelope_version:1, auth_context:{user_id,role,workspace_id?}, idempotency_key?, pipeline_run_id?, body:TBody}. Polymorphic body by job type.	MED (envelope typeable; body stays generic)
6	processing_queue.result	Job result data (no single domain type; per-job)	LOW
7	entity_mentions.metadata	Entity-level properties (cert version/expiry) — written by lib/entities/entity-metadata-bridge.ts; no standalone exported interface located.	MED-HIGH
8	feed_prompts.performance_snapshot	{pass_rate, flag_rate, articles_scored} per TECH inventory — no standalone TS type located in types//lib/; authored from usage.	HIGH (small, fixed)
9	classification_disputes.current_value	Snapshot of disputed classification	MED
10	classification_disputes.proposed_value	User-proposed correction	MED
11	change_reports.domain_summaries	Per-domain summaries array (was digests.domain_summaries pre-S251)	MED
12	change_reports.metadata	Change-report metadata	LOW
13	bid_responses.metadata	Drafting metadata (model, tokens)	LOW
14	bid_response_history.metadata	Snapshot of metadata at version time	LOW
15	content_history.metadata	Version metadata	LOW
16	pipeline_runs.result	Execution results	LOW
17	pipeline_runs.progress	{step, steps_completed, steps_total, detail} step tracker	MED
18	source_documents.extraction_metadata	Page count, table count, etc.	MED
19	ingestion_quality_log.details	Structured issue details	MED
20	application_types.state_machine_config	State-machine config (⚠ NEW — not in S262/TECH inventory; appeared since 18/05/2026)	MED (verify shape)
21	q_a_extractions.extraction_metadata	Extraction metadata (⚠ NEW — not in S262/TECH inventory; appeared since)	MED (verify shape)

Plus 1 view column: quality_issues_pending.details (pass-through from ingestion_quality_log.details; views aren’t patched by MergeDeep in the same way — note for PLAN).

3.2 Opaque-Json RPC returns — DB RETURNS TABLE migration targets (14, installed)

grep -c 'Returns: Json' supabase/types/database.types.ts → 14 (confirmed, matching S262). WP-C already produced the triage: docs/specs/id-16-ast-dataflow-tool/investigations/R-WP12-opaque-json-rpcs.md (verified present; last verified 18/05/2026). Its convertibility verdicts hold against a fresh caller grep (25/05/2026); only the database.types.ts line numbers in WP-C are stale (file grew ~530 LOC). Current truth:

RPC	Installed line	TS callers (fresh rg)	WP-C verdict	Fix path
get_dashboard_attention_counts	database.types.ts:3841	1	convertible	DB RETURNS TABLE
get_filter_counts	:3949	2 (hooks/browse/use-top-domains.ts:51, use-filter-data.ts:62)	convertible	DB RETURNS TABLE
get_user_tag_counts	:4153	1	convertible	DB RETURNS TABLE
merge_entities	:4212	1 (app/api/entities/merge/route.ts:48)	convertible	DB RETURNS TABLE
get_workspace_counts	:4155	0	convertible (now no callers)	DB or delete
get_author_analysis	:3757	1	requires-design	DB design needed
get_content_gaps	:3793	1	requires-design	DB design needed
get_entity_list_aggregated	:3916	1	requires-design	DB design needed
get_reading_patterns	:4063	1	requires-design	DB design needed
get_review_breakdown_stats	:4064	1	requires-design	DB design needed
get_topic_deep_dive	:4116	1	requires-design	DB design needed
get_bid_summary	:3785	0 (confirmed)	no-ts-callers	delete candidate
get_verification_stats	:4154	0 (confirmed)	no-ts-callers	delete candidate
hook_restrict_signup_to_phew_domain	:4170	0	leave-as-is	Auth Hook protocol — keep Json

Summary: 5 convertible (incl. get_workspace_counts, now caller-less), 6 requires-design, 2 delete candidates, 1 leave-as-is. Eliminating the 5 convertibles is the ~6–8h “half the cast surface” win WP-C estimated.

⚠ DRIFT correction: S262 §3 / TECH §“Inventory” list claim_next_job, get_entity_summary, get_item_workspaces, get_topic_layers, hybrid_search as the opaque-Json examples. None of those is in the installed Returns: Json set. Those functions return structured JSONB fields (e.g. claim_next_job.payload, get_entity_summary.related_entities, get_item_workspaces.domain_metadata, hybrid_search.metadata, get_topic_layers → get_topic_layers.metadata), which is the Gap-3 structured-RPC cast axis (WP-A/WP-B), not the opaque-Json-return axis (WP-C). They do not need a RETURNS TABLE migration. ID-47.2 must not target them as opaque-Json work.

3.3 ⚠ DRIFT: the digests → change_reports rename (S251) post-dates the TECH inventory

The type-safety-pipeline TECH JSONB inventory (TECH.md:518-520, :532-534) lists digests.domain_summaries, digests.theme_clusters, digests.metadata. Installed reality: the table is change_reports (change_reports.domain_summaries, change_reports.metadata), and theme_clusters was DROPPED in S251 (per SCHEMA-QUICK-REFERENCE.md header note + migration 20260521123659_s251_w1b_rename_digest_to_change_reports.sql). Any override authored from the TECH inventory verbatim would reference a non-existent table/column. Use §3.1 (installed) as the source of truth.

---

4. database.types.ts regen workflow — drift assessment

Verdict: regen is fully MANUAL. Drift risk is HIGH and currently unguarded by any automated gate.

4.1 How regen happens today (confirmed)

• Only mechanism: the CLAUDE.md Commands-table one-liner — /opt/homebrew/bin/supabase gen types typescript --project-id rovrymhhffssilaftdwd --schema public > supabase/types/database.types.ts (note: prod ref rovrymhhffssilaftdwd, not staging).
• No package.json script wraps it (rg 'gen.?types|database\.types' package.json → none — only an unrelated build script).
• No git hook (.husky / lefthook absent for this; rg → none).
• No CI job regenerates or diffs it (rg … .github/workflows/ → none; see §1).
• No freshness guard test (the __tests__/** files importing database.types consume the types as a dependency; none asserts the file is in sync with the live schema).

4.2 Drift risk

The drift vector is exactly the one the warp model exists to close, and S262 §2.1 named it: a migration lands (supabase migration new + db push) and the author forgets to re-run gen types. Nothing fails. The committed database.types.ts silently lags the live schema; .from('x').select() then returns a stale typed shape, and the type checker reports nothing because the types compile fine — they’re just wrong. This is the DB-side analog of the “tests built to pass” failure mode (S262 §6): the protection must come from a mechanism that runs on the real schema in CI, not from types or tests.

Two existing-but-insufficient signals:

• schema-parity.yml catches prod↔staging SQL drift — but it’s workflow_dispatch-only and says nothing about the TS types vs either DB.
• supabase-advisors.yml catches security/perf advisor regressions — orthogonal.

The missing gate is supabase-types-parity (specced at TECH :644, unshipped). It is the single highest-ROI item for ID-47: it makes the generated source of truth trustworthy (mock-proof, runs on the live schema in CI). The empirical drift this RESEARCH already found — digests→change_reports, two NEW columns (application_types.state_machine_config, q_a_extractions.extraction_metadata) absent from the 18/05 inventory — is proof the manual process drifts in practice.

---

5. Feed-forward for ID-47.2 PLAN

The 47.2 Planner decomposes these into sibling-only implementation Subtasks. Three workstreams, dependency-ordered.

(i) Overrides to author — supabase/types/database-overrides.ts (NEW; absent today)

• File does not exist (rg MergeDeep supabase/types/ lib/supabase/ → none). Greenfield.
• First wave (HIGH priority, fixed/known shapes): workspaces.domain_metadata → ProcurementMetadata (types/procurement.ts:27); content_items.summary_data → SummaryData (types/content.ts:78, verify installed column shape first — §3.1 #2 drift); feed_prompts.performance_snapshot → {pass_rate, flag_rate, articles_scored} (author from usage); processing_queue.payload → QueueJobPayload<…> (lib/queue/envelope.ts:117, envelope only).
• Second wave: company_profiles.competitors (author Competitor[] from the lib/validation/schemas.ts Zod entry); entity_mentions.metadata; classification_disputes.{current_value,proposed_value}; pipeline_runs.progress; source_documents.extraction_metadata; ingestion_quality_log.details; change_reports.domain_summaries.
• Verify-then-decide (NEW columns): application_types.state_machine_config, q_a_extractions.extraction_metadata — chase their write-sites before typing.
• Leave loose (no strict domain type): content_items.metadata, *.metadata history snapshots, processing_queue.result, pipeline_runs.result.
• Gate: confirm type-fest MergeDeep is a dependency (or add it) AND that tsconfig has strictNullChecks (TECH :810 caveat). Verify the pinned type-fest version exposes MergeDeep (OQ-3 pre-implementation check).
• 21 base-table columns is the full set; the override file need not cover all 21 on day one — HIGH-priority first, the rest incrementally (matches the “selective adoption” policy, TECH :940).

(ii) Consumers to migrate (before retiring SCHEMA-QUICK-REFERENCE.md)

• Code/test (3): lib/governance/publication-transitions.ts:42, scripts/generate-taxonomy-snapshot.ts:84, __tests__/migrations/no-app-guc-rls-policy.test.ts:158.
• Agent/skill/root (7): CLAUDE.md:74, AGENTS.md:121 + :253, .claude/skills/{implement-subtask:76, spec-driven-implementation:201, implement-specs:55, write-tech-spec:60}/SKILL.md.
• Catalogue (1): docs/reference/documentation-inventory.md:193/:741/:753 (coordinate with the doc-freshness.test.ts guard).
• Template (1): docs/product-functionality/_templates/technical.template.md (stops propagating the reference into new docs).
• Retirement mechanic: replace the doc body with a redirect stub (don’t git rm) so ~30 Tier-2 prose links survive; full delete is later cleanup.
• Dependency rule (per ID-47 task record): the manual-doc retirement Subtask depends on the consumer-migration Subtask (sibling-only).

(iii) CI jobs to add/extend

• ADD supabase-types-parity to .github/workflows/ci.yml — lift the specced YAML (type-safety-pipeline/TECH.md:640-679), wire into the ci-summary aggregator’s needs (ci.yml:1019-1028) + the env list (ci.yml:1042-1050). Decision the PLAN must surface (was OQ6): regen from staging (turayklvaunphgbgscat, what the spec uses) vs prod (rovrymhhffssilaftdwd, what the CLAUDE.md command uses) — pick one and make it a workflow-level constant (CLAUDE.md .temp/project-ref drift gotcha).
• MergeDeep-override-aware diff (TECH :923 caveat): once database-overrides.ts exists, the parity diff must compare the generated file (database.types.ts) against fresh gen types, NOT the merged Database type — otherwise the override’s intentional deltas would trip the gate every run. PLAN must encode this.
• No change to schema-parity.yml / supabase-advisors.yml (different axes).
• Optional later: drive the route-axis type-drift-baseline.json toward zero (S262 §7 step 4) — out of ID-47 scope; note only.

Sibling-only dependency sanity (A6)

All anticipated Subtasks live within Task ID-47 and depend only on siblings (override file → parity job → consumer migration → doc retirement). No cross-Task Subtask dependency surfaced. The opaque-Json RETURNS TABLE migrations (§3.2) are a separate, later sprint (WP-C’s S10 candidate, gated on Liam) — ID-47 is the TS-side MergeDeep + parity-CI + doc-retirement slice only. If 47.2 tries to fold the DB migrations into ID-47, that is a Task-boundary signal → escalate to split rather than expand ID-47.

---

6. Open questions for Liam (do not resolve here)

1. Override scope, first wave. S262 §8 Q4 proposed workspaces.domain_metadata, content_items.summary_data, feed_prompts.performance_snapshot as the first 3. Confirm — and note summary_data’s installed shape ({executive, detailed, takeaways}) differs from the spec’s described {key_points, quotes}; which is canonical?
2. Parity-CI project ref. Regen from staging (turayklvaunphgbgscat) per the spec, or prod (rovrymhhffssilaftdwd) per the CLAUDE.md command? Affects what “drift” means relative to which environment.
3. Doc retirement depth. Redirect-stub (preserves ~30 prose links) vs full git rm + sweep-all-links in one pass? Stub is lower-risk; full delete is cleaner but touches more files.
4. Opaque-Json RETURNS TABLE migrations — in or out of ID-47? This RESEARCH recommends out (separate sprint, per WP-C gating). Confirm the ID-47 boundary is TS-side MergeDeep + parity-CI + doc-retirement only.
5. Delete the 2 caller-less RPCs? get_bid_summary, get_verification_stats (and now-caller-less get_workspace_counts) — delete to cut schema noise, or leave? (WP-C suggested delete; not yet actioned.)