ID-47.2 PLAN — DB-layer type source-of-truth (warp model): implementation decomposition

ID-47.2 PLAN — DB-layer type source-of-truth (warp model): implementation decomposition

Status: ratified-ready · awaiting Liam ratification before implementation Subtask dispatch Audience: Knowledge Hub engineering (future task-executor agents for 47.5..47.9) Author: task-planner agent, S265 (fresh Planner instance per Q-PLANNER-2; PRODUCT/TECH intentionally skipped for ID-47 — developer infrastructure, no user surface, per the ID-47 task record + WP-D type-safety-pipeline precedent) Inputs: ratified docs/specs/id-47-db-layer-type-source-of-truth/RESEARCH.md (412 lines, S265 commit a296409a, Checker-verified PASS_WITH_NOTES)

§1 Context

This PLAN decomposes the DB-layer warp-model work into five sibling-only implementation Subtasks (47.5..47.9) appended to Task ID-47 in docs/reference/task-list.json. The Task makes supabase/types/database.types.ts (+ a MergeDeep JSONB override at supabase/types/database-overrides.ts) the canonical, CI-guarded source of truth for DB/row shapes — the warp-model analog for KH’s DB layer — and retires the manually-maintained docs/reference/SCHEMA-QUICK-REFERENCE.md.

Scope boundary (per RESEARCH §5 + OQ-4, confirmed): ID-47 is the TS-side MergeDeep override + parity-CI + doc-retirement slice only. The 14 opaque-Json RPC RETURNS TABLE migrations (RESEARCH §3.2) are a separate, later sprint (WP-C’s S10 candidate, gated on Liam). If any Subtask tries to fold a DB migration into ID-47, that is a Task-boundary signal → escalate to split, do not expand ID-47.

Three workstreams (RESEARCH §5), dependency-ordered into a chain:

RESEARCH workstream	Surface	Lands in Subtask
(i) Overrides to author	supabase/types/database-overrides.ts (greenfield) — wave-1 HIGH shapes via MergeDeep	47.5
(iii) CI job to add	supabase-types-parity job in ci.yml + ci-summary wiring	47.6
(canonical wiring)	CLAUDE.md “TypeScript conventions” note (DB shapes from Tables<>/override)	47.7
(ii) Consumers to migrate	10 Tier-1 consumers + product-doc template off SCHEMA-QUICK-REFERENCE.md	47.8
(ii) Doc retirement	SCHEMA-QUICK-REFERENCE.md → redirect stub + doc-inventory + guard coordination	47.9

§2 Pre-ratification empirical verification (OQ-3 — Q-EX2 forcing function)

The only external symbol the implementation relies on is type-fest’s MergeDeep. Verified against the installed tree (25/05/2026):

Symbol	Pinned/installed	Result	Consequence
type-fest (direct dep)	ABSENT — not in package.json dependencies nor devDependencies	ABSENT	47.5 must ADD it as a direct dependency.
type-fest (transitive)	type-fest@0.7.1 in bun.lock (pulled by stacktrace-parser)	present-but-stale	MergeDeep does NOT exist in 0.7.1 — it was added in type-fest v2.x. The transitive pin is unusable; a modern direct dep (current major v4.x) is required.
type-fest.MergeDeep (post-install)	n/a until installed	MUST RE-VERIFY in 47.5	47.5’s first step installs a modern type-fest and runs bun --print "typeof (await import('type-fest'))" / a throwaway .ts import type { MergeDeep } from 'type-fest' compiled under tsc --noEmit to confirm MergeDeep resolves. Recorded as the OQ-3 gate in 47.5’s testStrategy.
strictNullChecks (TECH :810 caveat)	tsconfig.json compilerOptions.strict: true (no extends)	SATISFIED — strict enables strictNullChecks	No action; MergeDeep semantics are sound under strict-null.

This is not a blocker — it resolves with the install step in 47.5 — but it upgrades the RESEARCH §5(i) “confirm (or add)” gate from a version-check to a fresh-install + post-install import check. The PLAN bakes the re-verify into 47.5 so the Executor cannot skip it.

Other RESEARCH claims re-checked PASS (cheap re-greps, 25/05/2026):

• database-overrides.ts ABSENT (greenfield) — confirmed.
• supabase-types-parity ABSENT from .github/workflows/ — confirmed (SPECCED-not-shipped; full YAML at type-safety-pipeline/TECH.md:640-679).
• grep -c 'Returns: Json' → 14 — confirmed.
• ProcurementMetadata at types/procurement.ts:27 with documented shape; types/bid-metadata.ts ABSENT — DRIFT correction #1 confirmed.
• SummaryData at types/content.ts:78 = {executive, detailed, takeaways[], generated_at, model, tokens_used?} — DRIFT correction #2 confirmed; the spec’s {key_points, quotes} is stale. This is the installed shape the 47.5 override must use.
• QueueJobPayload<TBody> at lib/queue/envelope.ts:117 — confirmed (envelope only; body stays generic).
• Committed database.types.ts already reflects the two NEW columns (application_types.state_machine_config @ L28, q_a_extractions.extraction_metadata @ L2399) and the S251 digests→change_reports rename (digests table ABSENT, change_reports present @ L285) — so the parity job (47.6) should pass on first run against the env whose schema the committed file was generated from (see §5 OQ-2 first-run caveat).
• Base-table vs RPC-return disambiguation: domain_metadata appears twice in database.types.ts — L3385 (workspaces.Row, the base-table override target) and L4031 (get_item_workspaces RPC Returns block, the Gap-3 structured-RPC axis, NOT an override target). 47.5 targets L3385 only. This confirms RESEARCH §3.2’s DRIFT correction.

§3 Decomposition

Subtask	Title	Sibling deps	Effort	Files-touched estimate
47.5	Add type-fest dep + author database-overrides.ts (wave-1 HIGH shapes) + selective-adoption re-export	none	~1.5h	4 files (package.json, bun.lock, supabase/types/database-overrides.ts (new), __tests__/supabase/database-overrides.test-d.ts (new) )
47.6	Add supabase-types-parity CI job (lift specced YAML) + wire into ci-summary needs/env/loop	none	~1h	1 file (.github/workflows/ci.yml)
47.7	Wire database.types.ts (+ override) as canonical type source — CLAUDE.md “TypeScript conventions” note	[5]	~0.5h	1 file (CLAUDE.md)
47.8	Migrate 10 Tier-1 consumers + product-doc template off SCHEMA-QUICK-REFERENCE.md	[7]	~1h	11 files (3 code/test + CLAUDE.md* + AGENTS.md + 4 SKILL.md + doc-inventory + template)
47.9	Retire SCHEMA-QUICK-REFERENCE.md via redirect stub + coordinate doc-freshness.test.ts guard	[8]	~0.75h	1–2 files (docs/reference/SCHEMA-QUICK-REFERENCE.md body→stub; guard/inventory touch if needed)

* CLAUDE.md is touched by both 47.7 (add conventions note) and 47.8 (redirect the :74 schema line). Sequenced 47.7→47.8 so the conventions note exists before the :74 redirect points at it. Different lines; no conflict within a serial chain.

Total effort estimate: ~4.75h (matches the ID-47 backlog estimate “~1–2 days”).

Subtask sizing (planning-and-task-breakdown rubric): all five are S–M (1–11 files, but each is mechanically narrow — 47.8’s 11 files are single-line prose redirects). Well within the agent-friendly band.

25-Subtask soft-ceiling status (§3.4 / A7): 5 implementation Subtasks within Task ID-47 (47.5..47.9), plus 47.1 (RESEARCH) + 47.2 (PLAN) = 7 total. Well within the soft ceiling. No escalation.

§4 Dependency graph

Two independent heads (47.5 override, 47.6 CI) that can run in parallel, converging into a serial doc-retirement chain rooted on the override:

47.5 (type-fest + override file + type-tests)          47.6 (supabase-types-parity CI + ci-summary wiring)
   │                                                       │  [independent — guards the GENERATED file,
   └─▶ 47.7 (CLAUDE.md TS-conventions note)                │   not the override; can land any time]
          │
          └─▶ 47.8 (migrate 10 Tier-1 consumers + template)
                 │
                 └─▶ 47.9 (retire SCHEMA-QUICK-REFERENCE.md → redirect stub)

Why 47.6 is dependency-free (and override-aware-by-construction): the parity job diffs the generated database.types.ts against a fresh gen types. The MergeDeep override lives in a separate file (database-overrides.ts) and is never regenerated, so the diff is automatically override-blind — exactly the RESEARCH §5(iii) requirement. The job therefore needs neither the override file nor a special “compare-generated-not-merged” code path; it satisfies that caveat by construction because KH keeps the override out of the generated file. 47.6 may land before, after, or alongside 47.5.

Why the doc chain is strictly serial (47.5→47.7→47.8→47.9):

• 47.7 documents the override file as part of the canonical source, so it depends on 47.5 (the file must exist to reference database-overrides.ts).
• 47.8 re-points the 10 consumers at the canonical source; the CLAUDE.md “TypeScript conventions” note (47.7) is the redirect target, so 47.8 depends on 47.7.
• 47.9 retires the doc; per the ID-47 task record the retirement Subtask depends on the consumer-migration Subtask (47.8) so no consumer is left pointing at a stub. Hard dependency.

Sibling-only constraint (§3.3 / A6, enforced by lib/validation/task-list-schema.ts TaskSchema.superRefine()): verified — every Subtask dependency references only sibling Subtasks within Task ID-47 (47.5..47.9). No cross-Task Subtask dependency surfaced. The opaque-Json RETURNS TABLE migrations are out-of-scope (separate sprint), so the cross-Task pull that would otherwise force a boundary split does not arise.

Cross-Task interactions surfaced but NOT expressed as Subtask deps:

• The route/fetcher type-drift-parity gate (ci.yml:870, R-WP17) is a different axis and is untouched by ID-47. No coordination.
• schema-parity.yml (prod↔staging SQL diff, workflow_dispatch) is the natural companion to 47.6’s project-ref decision (OQ-2) but is not modified here.
• The 14 opaque-Json RPCs (WP-C triage R-WP12-opaque-json-rpcs.md) and the 2 delete-candidate RPCs are deferred (OQ-4, OQ-5).

§5 Open questions for Liam (flag, do not resolve)

Carried from RESEARCH §6, sharpened by this PLAN’s re-checks. None blocks ratification of the decomposition shape; each is a per-Subtask decision the Executor needs answered before that Subtask lands.

1. OQ-1 — Override first-wave scope (47.5). RESEARCH §5(i)/S262 Q4 propose workspaces.domain_metadata→ProcurementMetadata, content_items.summary_data→SummaryData, feed_prompts.performance_snapshot, processing_queue.payload→QueueJobPayload as wave 1. Confirm the wave-1 set. Note summary_data’s installed shape is {executive, detailed, takeaways, generated_at, model, tokens_used?} (verified) — this PLAN uses the installed shape, not the spec’s stale {key_points, quotes}. Confirm that is canonical.
2. OQ-2 — Parity-CI project ref (47.6) [first-run risk]. The specced YAML generates from staging (turayklvaunphgbgscat); the CLAUDE.md regen command + the committed database.types.ts use prod (rovrymhhffssilaftdwd). If prod↔staging schemas diverge, the staging-generated parity job fails on first run even with no app change. Pick one ref and make it a workflow-level constant. Recommendation: match the spec (staging) for CI, but gate the first green run on schema-parity.yml confirming prod↔staging parity, OR generate from prod to match the committed file. Flag for Liam — this is the one OQ with a concrete first-run failure mode.
3. OQ-3 — Doc retirement depth (47.9). Redirect-stub (preserves ~30 Tier-2 prose links + keeps doc-freshness.test.ts green, since that guard asserts backtick-quoted doc paths still resolve) vs full git rm + sweep-all-links. This PLAN assumes redirect-stub (the provisional default; lower-risk and guard-compatible). Confirm.
4. OQ-4 — Opaque-Json RETURNS TABLE migrations: in or out of ID-47? This PLAN keeps them OUT (separate sprint). Confirm the ID-47 boundary is TS-side MergeDeep + parity-CI + doc-retirement only.
5. OQ-5 — Delete the caller-less RPCs? get_bid_summary, get_verification_stats, get_workspace_counts (now caller-less). Out of ID-47 scope; surfaced for the later WP-C sprint.

§6 Implementation Subtask records (TM-shape — for the Orchestrator to append to task-list.json under Task ID-47)

The orchestrator writes these into docs/reference/task-list.json. status is pending (initial). Dependencies are sibling-only integer IDs local to Task ID-47. Each details field is load-bearing — an Executor never needs to read the full RESEARCH/this PLAN.

See §7 below for the verbatim JSON block.

§7 Per-Subtask acceptance detail

47.5 — type-fest dep + database-overrides.ts (wave-1 HIGH shapes) + type-tests

• Acceptance: type-fest (current major, v4.x) added to package.json dependencies; bun install updates bun.lock; import type { MergeDeep } from 'type-fest' resolves under tsc --noEmit (OQ-3 gate, recorded). supabase/types/database-overrides.ts exports an overridden Database type = MergeDeep<GeneratedDatabase, Overrides> where Overrides types the wave-1 base-table JSONB columns: workspaces.Row.domain_metadata → ProcurementMetadata (types/procurement.ts:27), content_items.Row.summary_data → SummaryData (types/content.ts:78, installed shape {executive, detailed, takeaways, generated_at, model, tokens_used?}), feed_prompts.Row.performance_snapshot → {pass_rate, flag_rate, articles_scored} (authored from usage), processing_queue.Row.payload → QueueJobPayload<Record<string, unknown>> (lib/queue/envelope.ts:117, envelope only — body stays generic). The file re-exports Tables/TablesInsert/TablesUpdate-equivalent helpers bound to the overridden Database so consumers opt in by importing from database-overrides.ts — no global swap of existing @/supabase/types/database.types imports (selective adoption, TECH :940). A .test-d.ts type-test asserts Tables<'workspaces'>['domain_metadata'] is ProcurementMetadata (not Json) and the other three wave-1 columns resolve to their domain types.
• Files: package.json (+type-fest dep), bun.lock (regen), supabase/types/database-overrides.ts (new), __tests__/supabase/database-overrides.test-d.ts (new type-test).
• Effort: ~1.5h. Sibling deps: none.
• CI-verifiable outcome: bun run test passes the new .test-d.ts (or tsc --noEmit passes if type-tests run via the typecheck job); bun lint + bun run build clean. The base-table-only target (L3385, not the L4031 RPC return) is verified by the type-test asserting the override applies to Tables<'workspaces'>['Row'], not to any RPC return.

47.6 — supabase-types-parity CI job + ci-summary wiring

• Acceptance: New job supabase-types-parity added to .github/workflows/ci.yml, lifted from the specced YAML (docs/specs/id-16-ast-dataflow-tool/type-safety-pipeline/TECH.md:640-679): checkout → install Supabase CLI → supabase gen types typescript --project-id "$PROJECT_REF" --schema public > /tmp/database.types.generated.ts → diff -q against supabase/types/database.types.ts, fail with ::error:: + a diff -u … | head -200 and the regen remediation command on drift. PROJECT_REF is a workflow-level env constant (resolves OQ-2 — value per Liam: staging turayklvaunphgbgscat per spec, or prod rovrymhhffssilaftdwd per the committed file). environment: Staging and SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }} per the spec. Wired into the ci-summary aggregator at three points (ci.yml ~:1019-1085): (a) append - supabase-types-parity to needs:; (b) add SUPABASE_TYPES_PARITY: ${{ needs.supabase-types-parity.result }} to the aggregator step env:; (c) add "supabase-types-parity=$SUPABASE_TYPES_PARITY" to the for entry in loop. No MergeDeep-override-aware code path needed — the override lives in a separate file (database-overrides.ts) the generated file never contains, so the diff is override-blind by construction (RESEARCH §5(iii)).
• Files: .github/workflows/ci.yml (1 file, ~3 edit regions: new job + 3 aggregator wiring points).
• Effort: ~1h. Sibling deps: none.
• CI-verifiable outcome: on a known-good schema state the job passes (committed database.types.ts already reflects the live schema — NEW columns + change_reports rename present, verified §2); on a deliberately-stale committed types file the job fails (TECH :889 acceptance criterion). ci-summary shows supabase-types-parity -> success. First-run caveat (OQ-2): if generated from staging while the committed file is prod-generated and the two schemas diverge, the job fails spuriously — gate the first green run on the project-ref decision.

47.7 — CLAUDE.md “TypeScript conventions” note (canonical type source)

• Acceptance: A new “TypeScript conventions” note added to CLAUDE.md (Gotchas → Data & Architecture, alongside the TanStack/auth bullets) stating: DB/row shapes come from Tables<'x'> / QueryData<> off @/supabase/types/database.types (or database-overrides.ts for JSONB-typed columns); composed/API responses come from z.infer<typeof schema>; database.types.ts is generated (never hand-edited) and CI-guarded by supabase-types-parity; the MergeDeep override at supabase/types/database-overrides.ts is where JSONB column domain types live. UK English. This note is the redirect target for 47.8.
• Files: CLAUDE.md (1 file, new note; does NOT touch the :74 schema line — that is 47.8’s edit).
• Effort: ~0.5h. Sibling deps: [5] (the override file must exist to be referenced as canonical).
• CI-verifiable outcome: bun run test passes (doc-freshness.test.ts resolves the backtick-quoted supabase/types/database-overrides.ts path — which exists after 47.5; and supabase/types/database.types.ts). No broken-path guard failure.

47.8 — Migrate 10 Tier-1 consumers + product-doc template

• Acceptance: All 10 Tier-1 SCHEMA-QUICK-REFERENCE.md references re-pointed at the canonical source (the 47.7 CLAUDE.md note / Enums<'user_role'> / Tables<> as appropriate). Code/test (3): lib/governance/publication-transitions.ts:42 (doc-comment → database.types.ts Enums<'user_role'>), scripts/generate-taxonomy-snapshot.ts:84 (comment → note that the real fallback reads information_schema, not the doc; verify no literal doc-path read), __tests__/migrations/no-app-guc-rls-policy.test.ts:158 (failure-message string → point at the canonical source). Agent/skill/root (7): CLAUDE.md:74, AGENTS.md:121 + :253, and the 4 SKILL.md files (implement-subtask:76, spec-driven-implementation:201, implement-specs:55, write-tech-spec:60). Template (1): docs/product-functionality/_templates/technical.template.md:7 (stops propagating the reference into every new product-functionality doc). All redirects point at the canonical source; none introduces a broken path.
• Files: 11 files — the 3 code/test + CLAUDE.md (the :74 line) + AGENTS.md + the 4 SKILL.md + docs/reference/documentation-inventory.md (if the catalogue prose must change — coordinate with 47.9) + docs/product-functionality/_templates/technical.template.md.
• Effort: ~1h. Sibling deps: [7] (the CLAUDE.md canonical note must exist as the redirect target).
• CI-verifiable outcome: bun run test passes (no-app-guc-rls-policy.test.ts failure-message change does not break its assertions; doc-freshness.test.ts resolves all referenced paths). bun lint clean. A grep -rn 'SCHEMA-QUICK-REFERENCE' across lib/, scripts/, __tests__/, CLAUDE.md, AGENTS.md, .claude/skills/, and the template returns only the doc’s own file + the doc-inventory catalogue row (handled by 47.9) — zero live consumer routes left.

47.9 — Retire SCHEMA-QUICK-REFERENCE.md via redirect stub

• Acceptance: docs/reference/SCHEMA-QUICK-REFERENCE.md body replaced with a redirect stub (NOT git rm): a short doc stating “Schema truth now lives in supabase/types/database.types.ts + supabase/types/database-overrides.ts; see CLAUDE.md → TypeScript conventions. For live introspection use the Supabase MCP / information_schema.” The Last verified header is updated. The doc-freshness.test.ts guard stays green (the file still exists, so backtick-quoted references to docs/reference/SCHEMA-QUICK-REFERENCE.md still resolve; the stub references valid paths). docs/reference/documentation-inventory.md:193 catalogue row updated from authoritative to a redirect/deprecated status (coordinate so the inventory guard — if any freshness assertion keys off this doc — does not fail). The ~30 Tier-2 prose links survive (they 200 against the stub). Full git rm + link-sweep is deferred to a later cleanup (OQ-3 default = stub).
• Files: docs/reference/SCHEMA-QUICK-REFERENCE.md (body → stub), docs/reference/documentation-inventory.md (catalogue status, if not already done in 47.8).
• Effort: ~0.75h. Sibling deps: [8] (per ID-47 task record — retirement depends on consumer-migration so no consumer is left pointing at a stub).
• CI-verifiable outcome: bun run test passes (doc-freshness.test.ts + any doc-inventory guard green — the file still exists and the inventory row is consistent). bun lint clean. The doc renders as a stub; no Tier-2 link 404s.

§8 Notes for the Orchestrator

• Two parallel heads: 47.5 and 47.6 are independent and can be dispatched concurrently (different files, no shared state). The doc chain 47.7→47.8→47.9 is strictly serial.
• OQ-2 is the one OQ with a concrete CI failure mode (project-ref / first-run drift) — resolve before 47.6’s first green run is expected.
• CLAUDE.md is touched by 47.7 (new note) and 47.8 (the :74 redirect) on different lines; the serial 47.7→47.8 order avoids a within-Task edit collision. If the Orchestrator parallelises these (it should not, per the dep graph), a conflict would arise.
• No escalation triggered: sibling-only honoured (the opaque-Json DB migrations that would have forced a Task-boundary split are out-of-scope), 25-Subtask ceiling not approached (5 impl Subtasks), no cross-Task Subtask dep surfaced.