← back to Homesonspec
docs(homesonspec): TK-10878 Phase 2 drafts — pg_partman SourceEvidence conversion + provenance-safe retention (not applied)
21b9aad61e78c984b963818315202e34996b3587 · 2026-08-31 12:05:05 -0700 · Steve
Draft-only: monthly RANGE partitions on createdAt with batched backfill and
full rollback path; retention DISABLED for SourceEvidence (provenance not
prunable) with a manual detach-to-archive review procedure. Plan file updated
with a Phase-2 preflight checklist. No prod touch, no migration run.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M ops/o3-partition-and-disk-plan.mdA ops/phase2-drafts/20260901000000_partition_sourceevidence_by_month/migration.sqlA ops/phase2-drafts/20260901000100_sourceevidence_retention_policy/migration.sqlA ops/phase2-drafts/README.md
Diff
commit 21b9aad61e78c984b963818315202e34996b3587
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Aug 31 12:05:05 2026 -0700
docs(homesonspec): TK-10878 Phase 2 drafts — pg_partman SourceEvidence conversion + provenance-safe retention (not applied)
Draft-only: monthly RANGE partitions on createdAt with batched backfill and
full rollback path; retention DISABLED for SourceEvidence (provenance not
prunable) with a manual detach-to-archive review procedure. Plan file updated
with a Phase-2 preflight checklist. No prod touch, no migration run.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
ops/o3-partition-and-disk-plan.md | 29 +++-
.../migration.sql | 191 +++++++++++++++++++++
.../migration.sql | 98 +++++++++++
ops/phase2-drafts/README.md | 36 ++++
4 files changed, 348 insertions(+), 6 deletions(-)
diff --git a/ops/o3-partition-and-disk-plan.md b/ops/o3-partition-and-disk-plan.md
index e77e0d1f..ee406603 100644
--- a/ops/o3-partition-and-disk-plan.md
+++ b/ops/o3-partition-and-disk-plan.md
@@ -12,13 +12,30 @@ pg_partman's conversion of an **existing** large table (`partition_data_proc`) r
- **Recommended target: +100 GB minimum** → clears the 90 % pressure, gives headroom for the 55 GB `SourceEvidence` conversion rewrite, and buffers ~months of the 16 GB/wk growth. Exact size = Steve's Kamatera-plan call.
- Verify after grow: `df -h /` shows ≥75 GB free before any conversion (also unblocks the pg_repack gate if ever needed).
-### PHASE 2 — pg_partman monthly range partitions (gated: prod DDL, maintenance window)
+### PHASE 2 — pg_partman monthly range partitions + retention (gated: prod DDL, maintenance window)
Targets: `SourceEvidence` on `createdAt`, `ValidationEvent` on `runAt` (monthly RANGE).
-- Install/confirm `pg_partman` extension on prod.
-- These are the two firehose tables; `StagedRecord`/`InventoryHome` stay unpartitioned.
-- **Conversion approach (online, existing data):** create the partitioned parent + `create_parent(...)`, then background `partition_data_proc` in batches (throttled) to move existing rows — NOT a single blocking rewrite. Verify row counts parent-vs-source before cutover.
-- **Prisma caveat:** Prisma has no declarative partitioning — this is a **raw-SQL migration** (a `migration.sql` with the partman calls), and `schema.prisma` keeps the models as-is (Prisma treats the partitioned parent as a normal table for reads/writes). Do NOT let a later `prisma migrate dev` try to "fix" drift — mark it a manual/raw migration.
-- **Payoff:** future reclaim = `DROP PARTITION` (instant, zero WAL); growth becomes bounded + operationally trivial.
+**DRAFTS READY (not applied):** `ops/phase2-drafts/` —
+- `20260901000000_partition_sourceevidence_by_month/migration.sql` — the pg_partman conversion.
+- `20260901000100_sourceevidence_retention_policy/migration.sql` — the retention policy.
+- `ops/phase2-drafts/README.md` — Prisma manual-migration handling + the one data-model change to confirm.
+
+Notes:
+- Install/confirm `pg_partman` extension on prod. These are the two firehose tables; `StagedRecord`/`InventoryHome` stay unpartitioned.
+- **Partition key = `createdAt`** (insert-time, monotonic, governs the ~16 GB/wk growth), **monthly** interval (~64-70 GB/month keeps partitions DETACH-friendly without thousands of daily children). `retrievedAt` is scrape-time, NOT the growth axis — not used.
+- **PK change (confirm):** PG requires the partition column in every unique/PK constraint, so the PK becomes `("id","createdAt")` not `("id")`. Nothing FK-references `SourceEvidence`, so no referential integrity breaks — but it is the one real data-model change; Steve signs off.
+- **Conversion approach (online, existing data):** rename live table → new partitioned parent + `create_parent(...)`, then a **babysat, throttled `partition_data_proc` loop** drains existing rows in batches — NOT a single blocking rewrite. Verify parent-vs-source counts before the gated final `DROP TABLE "SourceEvidence_old"`.
+- **Retention = provenance-safe.** SourceEvidence is real/unique/live-read → auto-retention is **DISABLED**; the ONLY reclaim path is the manual, Steve-gated detach-to-archive review procedure in the retention draft. **Never auto-DROP a SourceEvidence partition.** (Optional ValidationEvent retention — rebuildable data — is left commented + off pending Steve's call.)
+- **Prisma caveat:** raw-SQL migration; `schema.prisma` stays as-is; after applying, `prisma migrate resolve --applied` so a later `migrate dev` never un-partitions it.
+- **Payoff:** operability (per-month indexes/vacuum, DETACH-to-archive if ever needed); NOT space-reclaim-by-deletion — provenance stays whole.
+
+#### PHASE 2 PREFLIGHT CHECKLIST (all must pass before running, in the window)
+1. [ ] **Disk:** `df -h /` shows **≥75 GB free** on the Postgres volume (Phase 1 done; backfill needs ~table-sized scratch).
+2. [ ] **Backup fresh + verified:** a `pg_dump`/base-backup completed since the last import, and `pg_restore --list` (or a scratch restore) confirms it is valid — this IS the post-drop rollback.
+3. [ ] **Maintenance window** scheduled (low import traffic; the import launchd sweep paused).
+4. [ ] **pg_partman** installed on prod (`CREATE EXTENSION IF NOT EXISTS pg_partman;` succeeds).
+5. [ ] `p_start_partition` set to the real `date_trunc('month', min("createdAt"))` from the source table (not the placeholder literal).
+6. [ ] **Post-cutover verify:** `count(*)` on the new partitioned parent **==** the pre-conversion source count, `SourceEvidence_old` count **== 0**, and `partman.check_default(...)` shows a contiguous partition set with no gap at `now()` — BEFORE the gated `DROP TABLE "SourceEvidence_old"`.
+7. [ ] **Prisma:** `prisma migrate resolve --applied` recorded so drift-detection won't revert it.
### PHASE 3 — STEP 5 size canary (gated on Steve provisioning prod-read connectivity)
homesonspec DB-size canary → fleet-health-rollup (PASS/WARN/FAIL, `data/latest.json`), WARN on regrowth past a threshold. Mac2 is classifier-blocked from prod, so it must either run on Kamatera or use a Steve-provisioned read-only conn. Author when connectivity exists.
diff --git a/ops/phase2-drafts/20260901000000_partition_sourceevidence_by_month/migration.sql b/ops/phase2-drafts/20260901000000_partition_sourceevidence_by_month/migration.sql
new file mode 100644
index 00000000..e5ea741b
--- /dev/null
+++ b/ops/phase2-drafts/20260901000000_partition_sourceevidence_by_month/migration.sql
@@ -0,0 +1,191 @@
+-- ============================================================================
+-- TK-10878 PHASE 2 (a) — Convert "SourceEvidence" to monthly RANGE partitions
+-- via pg_partman. DRAFT — NOT YET APPLIED. DO NOT RUN outside a scheduled
+-- maintenance window on the GROWN disk (Phase 1 must be done first).
+-- ============================================================================
+--
+-- SCOPE / ASSUMPTION (Steve must confirm):
+-- * Partition key = "createdAt" (TIMESTAMP(3), DEFAULT now(), monotonic
+-- insert-time column). This is what the plan named and what governs the
+-- ~16 GB/wk growth. "retrievedAt" is also a timestamp but is scrape-time,
+-- not insert-time, so it is NOT the growth axis and is NOT used here.
+-- * Interval = monthly. ~16 GB/wk ⇒ ~64-70 GB/month of new rows; monthly
+-- keeps each partition in the tens-of-GB range (DROP/DETACH-friendly) while
+-- avoiding thousands of tiny daily partitions. Confirm before running.
+-- * SourceEvidence is ~55 GB / ~122M rows. The batched backfill below moves
+-- existing rows into partitions in throttled chunks — NOT a single blocking
+-- rewrite — but it still needs ~table-sized scratch during the copy, which
+-- is exactly why Phase 1 (disk ≥75 GB free) is a hard prerequisite.
+--
+-- PRISMA NOTE: Prisma has no declarative partitioning. This is a RAW-SQL
+-- migration. schema.prisma keeps `model SourceEvidence` unchanged (Prisma
+-- treats the partitioned parent as an ordinary table for read/write). Mark
+-- this migration `applied`/manual so a later `prisma migrate dev` does NOT try
+-- to "fix drift" and un-partition the table. See phase2-drafts/README below.
+--
+-- ============================================================================
+
+-- ---------------------------------------------------------------------------
+-- 0. Guards
+-- ---------------------------------------------------------------------------
+CREATE EXTENSION IF NOT EXISTS pg_partman; -- installs into schema "partman" by default; adjust if your prod uses a different target schema.
+
+-- Fail fast if the disk precheck was skipped: partman + backfill need headroom.
+-- (Advisory only — real gate is the Phase-1 df check in the preflight runbook.)
+DO $$
+BEGIN
+ IF current_setting('server_version_num')::int < 130000 THEN
+ RAISE EXCEPTION 'pg_partman range-detach/DROP tooling assumes PG13+; verify prod version';
+ END IF;
+END $$;
+
+-- ---------------------------------------------------------------------------
+-- 1. Rename the live table out of the way; it becomes the source we drain from.
+-- A brief ACCESS EXCLUSIVE on the rename only — bounded by lock_timeout.
+-- ---------------------------------------------------------------------------
+SET lock_timeout = '5s';
+ALTER TABLE "SourceEvidence" RENAME TO "SourceEvidence_old";
+-- Rename its indexes/constraints too so the new parent can reuse the names.
+ALTER INDEX "SourceEvidence_pkey" RENAME TO "SourceEvidence_old_pkey";
+ALTER INDEX "SourceEvidence_entityType_entityId_idx" RENAME TO "SourceEvidence_old_entityType_entityId_idx";
+ALTER INDEX "SourceEvidence_stagedRecordId_idx" RENAME TO "SourceEvidence_old_stagedRecordId_idx";
+ALTER TABLE "SourceEvidence_old" RENAME CONSTRAINT "SourceEvidence_stagedRecordId_fkey" TO "SourceEvidence_old_stagedRecordId_fkey";
+RESET lock_timeout;
+
+-- ---------------------------------------------------------------------------
+-- 2. Create the partitioned PARENT with the identical column set.
+--
+-- CRITICAL: PostgreSQL requires the partition-key column to be part of every
+-- UNIQUE / PRIMARY KEY constraint on a partitioned table. The old PK was
+-- ("id") alone — that is ILLEGAL on a table partitioned by "createdAt".
+-- The new PK MUST become ("id","createdAt"). "id" stays globally unique in
+-- practice (cuid), and nothing FK-references SourceEvidence, so widening the
+-- PK breaks no referential integrity. << CONFIRM this is acceptable — it is
+-- the one real data-model change in this migration. >>
+-- ---------------------------------------------------------------------------
+CREATE TABLE "SourceEvidence" (
+ "id" TEXT NOT NULL,
+ "stagedRecordId" TEXT,
+ "entityType" "EntityType",
+ "entityId" TEXT,
+ "field" TEXT NOT NULL,
+ "sourceUrl" TEXT NOT NULL,
+ "retrievedAt" TIMESTAMP(3) NOT NULL,
+ "contentHash" TEXT,
+ "extractorVersion" TEXT NOT NULL,
+ "rawValue" TEXT,
+ "normalizedValue" TEXT,
+ "evidenceText" TEXT,
+ "confidence" DOUBLE PRECISION NOT NULL,
+ "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ CONSTRAINT "SourceEvidence_pkey" PRIMARY KEY ("id", "createdAt")
+) PARTITION BY RANGE ("createdAt");
+
+-- Re-create the two secondary indexes on the parent (propagate to all children).
+CREATE INDEX "SourceEvidence_entityType_entityId_idx" ON "SourceEvidence" ("entityType", "entityId");
+CREATE INDEX "SourceEvidence_stagedRecordId_idx" ON "SourceEvidence" ("stagedRecordId");
+
+-- Re-create the outbound FK (SourceEvidence is the *referencing* side — allowed
+-- on a partitioned table). ON DELETE SET NULL / ON UPDATE CASCADE as before.
+ALTER TABLE "SourceEvidence"
+ ADD CONSTRAINT "SourceEvidence_stagedRecordId_fkey"
+ FOREIGN KEY ("stagedRecordId") REFERENCES "StagedRecord"("id")
+ ON DELETE SET NULL ON UPDATE CASCADE;
+
+-- ---------------------------------------------------------------------------
+-- 3. Register the parent with pg_partman (monthly RANGE, native partitioning).
+-- p_start_partition should be the FLOOR month of the oldest existing row so
+-- the backfill has targets. Replace the literal below with:
+-- SELECT date_trunc('month', min("createdAt")) FROM "SourceEvidence_old";
+-- p_premake pre-creates 4 future months so live inserts never hit a gap.
+-- ---------------------------------------------------------------------------
+SELECT partman.create_parent(
+ p_parent_table => 'public.SourceEvidence',
+ p_control => 'createdAt',
+ p_type => 'native',
+ p_interval => 'monthly',
+ p_premake => 4,
+ p_start_partition => '2025-01-01' -- << REPLACE with date_trunc('month', min createdAt) from _old >>
+);
+
+-- Retention is intentionally NOT configured here — see the retention migration
+-- (20260901000100_sourceevidence_retention_policy). Provenance is protected.
+
+-- ---------------------------------------------------------------------------
+-- 4. BACKFILL existing rows in throttled batches (NOT one blocking rewrite).
+-- Run partition_data_proc REPEATEDLY until "SourceEvidence_old" is empty.
+-- Each call moves p_batch_count intervals' worth of rows, commits, sleeps.
+-- Operationally this is a loop run OUTSIDE this transaction (a procedure call
+-- per pass, or `CALL partman.run_maintenance_proc()` scheduling), because a
+-- single migration transaction cannot hold the whole 55 GB move.
+--
+-- >>> This step is a BABYSAT LOOP, not a fire-and-forget statement. <<<
+-- Reference loop (psql, run manually during the window):
+--
+-- DO $$
+-- DECLARE moved bigint; total bigint := 0;
+-- BEGIN
+-- LOOP
+-- CALL partman.partition_data_proc(
+-- p_parent_table => 'public.SourceEvidence',
+-- p_loop_count => 20, -- intervals per pass
+-- p_wait => 2 -- seconds between commits (throttle WAL/disk)
+-- );
+-- GET DIAGNOSTICS moved = ROW_COUNT; -- 0 when drained
+-- EXIT WHEN moved = 0;
+-- END LOOP;
+-- END $$;
+--
+-- After each pass, watch: df -h / AND
+-- SELECT count(*) FROM "SourceEvidence_old"; -- must trend to 0
+-- ---------------------------------------------------------------------------
+-- (The loop above is documented, not executed inside this migration file.)
+
+-- ---------------------------------------------------------------------------
+-- 5. CUTOVER VERIFY — do NOT drop _old until BOTH counts match.
+-- SELECT count(*) FROM "SourceEvidence"; -- new partitioned parent
+-- SELECT count(*) FROM "SourceEvidence_old"; -- MUST be 0
+-- And spot-check the partition set is contiguous with no gap at "now":
+-- SELECT partman.check_default('public.SourceEvidence');
+-- ---------------------------------------------------------------------------
+
+-- ---------------------------------------------------------------------------
+-- 6. FINAL DROP of the drained source (only after step 5 passes).
+-- Kept as a SEPARATE, explicit statement so it is never run accidentally.
+-- DROP TABLE "SourceEvidence_old";
+-- ---------------------------------------------------------------------------
+
+-- ============================================================================
+-- ROLLBACK / ABORT PATH
+-- ============================================================================
+-- BEFORE the drop in step 6 (i.e. any time during backfill), abort is trivial
+-- and lossless because "SourceEvidence_old" still holds every original row:
+--
+-- -- 1. Move any rows that already landed in the new parent back is NOT needed
+-- -- if you abort before deleting from _old; partition_data_proc DELETEs
+-- -- from _old as it copies, so rows are split between the two tables.
+-- -- Simplest safe abort = re-drain the new parent back into _old, OR:
+-- -- a) SET lock_timeout='5s';
+-- -- b) INSERT INTO "SourceEvidence_old"
+-- -- SELECT * FROM "SourceEvidence"
+-- -- ON CONFLICT ("id") DO NOTHING; -- restore any migrated rows
+-- -- c) DROP TABLE "SourceEvidence" CASCADE; -- the partitioned parent + children
+-- -- d) SELECT partman.undo_partition(...) is unnecessary since we drop.
+-- -- e) ALTER TABLE "SourceEvidence_old" RENAME TO "SourceEvidence";
+-- -- ALTER INDEX "SourceEvidence_old_pkey" RENAME TO "SourceEvidence_pkey";
+-- -- ALTER INDEX "SourceEvidence_old_entityType_entityId_idx" RENAME TO "SourceEvidence_entityType_entityId_idx";
+-- -- ALTER INDEX "SourceEvidence_old_stagedRecordId_idx" RENAME TO "SourceEvidence_stagedRecordId_idx";
+-- -- ALTER TABLE "SourceEvidence" RENAME CONSTRAINT "SourceEvidence_old_stagedRecordId_fkey" TO "SourceEvidence_stagedRecordId_fkey";
+-- -- f) partman.part_config row for this parent: DELETE where parent_table='public.SourceEvidence'.
+--
+-- AFTER step 6 (source dropped): rollback is a RESTORE-FROM-BACKUP, which is
+-- why the preflight requires a fresh, verified pg_dump/base-backup and why
+-- step 6 must not run until step 5 passes. There is no in-place undo once the
+-- original rows are gone.
+--
+-- Partman's own reverse tool (if you keep _old around) :
+-- SELECT partman.undo_partition(
+-- p_parent_table => 'public.SourceEvidence',
+-- p_target_table => 'public.SourceEvidence_unpart', -- collapses children back
+-- p_keep_table => false);
+-- ============================================================================
diff --git a/ops/phase2-drafts/20260901000100_sourceevidence_retention_policy/migration.sql b/ops/phase2-drafts/20260901000100_sourceevidence_retention_policy/migration.sql
new file mode 100644
index 00000000..990dcf80
--- /dev/null
+++ b/ops/phase2-drafts/20260901000100_sourceevidence_retention_policy/migration.sql
@@ -0,0 +1,98 @@
+-- ============================================================================
+-- TK-10878 PHASE 2 (b) — "SourceEvidence" retention policy. DRAFT — NOT APPLIED.
+-- Depends on 20260901000000_partition_sourceevidence_by_month having converted
+-- the table to monthly RANGE partitions. Run only in a maintenance window on
+-- the grown disk.
+-- ============================================================================
+--
+-- ┌────────────────────────────────────────────────────────────────────────┐
+-- │ HARD RAIL — SourceEvidence provenance is NOT PRUNABLE. │
+-- │ Per the memo (TK-10878, o3-partition-and-disk-plan.md): the 122M rows are │
+-- │ REAL, UNIQUE, and LIVE-READ — every published fact must stay traceable to │
+-- │ its SourceEvidence rows (schema.prisma line 9). Deleting an old partition │
+-- │ would silently orphan the citations behind older published entities. │
+-- │ Therefore this policy NEVER auto-DROPs a SourceEvidence partition. │
+-- └────────────────────────────────────────────────────────────────────────┘
+--
+-- RETENTION SCOPE DECISION:
+-- * SourceEvidence → PROTECTED. retention = DISABLED. No auto-drop, no
+-- auto-detach that discards data. If storage must be reclaimed, the ONLY
+-- sanctioned move is a DOCUMENTED, MANUALLY-REVIEWED detach of a fully-aged
+-- month to cheaper/cold storage (dump → verify → optionally detach), never
+-- an unattended DROP. That path is the review procedure at the bottom, and
+-- it stays Steve-gated.
+-- * The partitioning payoff for SourceEvidence is OPERATIONAL (per-month
+-- indexes, contiguous vacuum, DETACH-to-archive is possible if ever needed)
+-- — NOT space reclamation via deletion.
+--
+-- * ValidationEvent (the OTHER firehose, converted separately) is the table
+-- where genuine auto-retention COULD apply if Steve wants it: validation
+-- runs are REBUILDABLE from StagedRecord + the validator (they are derived,
+-- not source-of-truth provenance). If/when ValidationEvent is partitioned,
+-- a retention window there is defensible. It is left commented + DISABLED
+-- below pending Steve's explicit call — even "rebuildable" is his decision.
+--
+-- ============================================================================
+
+-- ---------------------------------------------------------------------------
+-- 1. SourceEvidence — explicitly assert NO auto-retention (belt-and-suspenders).
+-- part_config is created by create_parent(); we make the intent durable so a
+-- future run_maintenance() can never age these out.
+-- ---------------------------------------------------------------------------
+UPDATE partman.part_config
+ SET retention = NULL, -- no age threshold
+ retention_keep_table = true, -- if ever set, DETACH (keep table), never drop
+ retention_keep_index = true,
+ infinite_time_partitions = true -- always pre-make future months; never stop
+ WHERE parent_table = 'public.SourceEvidence';
+
+-- Provenance guardrail comment for the next operator.
+COMMENT ON TABLE "SourceEvidence" IS
+ 'PROVENANCE — NOT PRUNABLE (TK-10878). Partitioned monthly for operability only. '
+ 'Never DROP a partition: every published fact cites these rows. To reclaim space, '
+ 'use the manual detach-to-archive review procedure in '
+ 'ops/phase2-drafts/20260901000100_sourceevidence_retention_policy/migration.sql.';
+
+-- ---------------------------------------------------------------------------
+-- 2. (OPTIONAL, DISABLED) ValidationEvent auto-retention — Steve-gated.
+-- ValidationEvent rows are REBUILDABLE (derived from StagedRecord + validator
+-- version), so a retention window is defensible there. Left OFF; uncomment
+-- ONLY on Steve's explicit go, and only after ValidationEvent is partitioned.
+--
+-- -- Keep ~12 months of validation history, DETACH (not drop) older months:
+-- -- UPDATE partman.part_config
+-- -- SET retention = '12 months',
+-- -- retention_keep_table = true, -- DETACH to a standalone table, do NOT drop
+-- -- retention_keep_index = true
+-- -- WHERE parent_table = 'public.ValidationEvent';
+-- -- Detached months can then be pg_dump'd to cold storage and dropped by a
+-- -- human after review — never by run_maintenance().
+-- ---------------------------------------------------------------------------
+
+-- ============================================================================
+-- MANUAL DETACH-TO-ARCHIVE REVIEW PROCEDURE (SourceEvidence, Steve-gated)
+-- ============================================================================
+-- This is the ONLY sanctioned way to reclaim SourceEvidence space. It is a
+-- runbook, NOT an automated job. Every step is reversible until the final dump
+-- is verified and the operator chooses to drop the archived copy.
+--
+-- 1. Pick the oldest fully-aged month, e.g. "SourceEvidence_p2025_01".
+-- 2. Confirm NOTHING live still cites it:
+-- SELECT count(*) FROM "SourceEvidence_p2025_01" se
+-- WHERE se."entityId" IN (SELECT id FROM published entities still live);
+-- If citations exist → STOP, the month is not archivable.
+-- 3. DETACH the partition (keeps all rows in a standalone table):
+-- ALTER TABLE "SourceEvidence" DETACH PARTITION "SourceEvidence_p2025_01";
+-- 4. Dump + verify the detached table to cold storage:
+-- pg_dump -t "SourceEvidence_p2025_01" ... > archive/se_2025_01.sql
+-- (verify row count in the dump == table count BEFORE any drop)
+-- 5. ONLY after a verified archive, and ONLY on Steve's go, drop the copy:
+-- DROP TABLE "SourceEvidence_p2025_01";
+-- 6. To restore later: re-load the dump, then
+-- ALTER TABLE "SourceEvidence" ATTACH PARTITION ... FOR VALUES FROM (...) TO (...);
+--
+-- ROLLBACK for this migration itself (config change only, no data touched):
+-- UPDATE partman.part_config SET retention = <prior value>, ...
+-- WHERE parent_table = 'public.SourceEvidence';
+-- COMMENT ON TABLE "SourceEvidence" IS NULL;
+-- ============================================================================
diff --git a/ops/phase2-drafts/README.md b/ops/phase2-drafts/README.md
new file mode 100644
index 00000000..4e435178
--- /dev/null
+++ b/ops/phase2-drafts/README.md
@@ -0,0 +1,36 @@
+# TK-10878 Phase 2 — pg_partman conversion + retention (DRAFTS)
+
+These are **draft raw-SQL migrations**, not yet applied. They convert
+`SourceEvidence` (~55 GB / ~122M rows) to monthly RANGE partitions and set a
+provenance-safe retention policy. **Run only in a maintenance window on the
+grown disk (Phase 1 first).**
+
+## Files
+- `20260901000000_partition_sourceevidence_by_month/migration.sql` — pg_partman
+ conversion. Partition key `createdAt`, monthly. Rename-old → new partitioned
+ parent → `create_parent()` → **babysat batched backfill** → verify counts →
+ gated final drop. Includes a full abort/rollback path.
+- `20260901000100_sourceevidence_retention_policy/migration.sql` — retention.
+ **SourceEvidence auto-retention is DISABLED (provenance is not prunable).**
+ Reclaim only via the manual detach-to-archive review procedure. Optional
+ ValidationEvent retention is left commented + off, Steve-gated.
+
+## Promotion to a real Prisma migration
+When Steve approves, these move into
+`packages/database/prisma/migrations/<timestamp>_.../migration.sql` with a fresh
+timestamp. Because Prisma has **no declarative partitioning**:
+
+1. Keep `schema.prisma` `model SourceEvidence` **unchanged** — Prisma reads/writes
+ the partitioned parent as an ordinary table.
+2. After applying on prod, run `prisma migrate resolve --applied <migration>` so
+ Prisma records it without diffing, and a later `prisma migrate dev` never
+ tries to "fix drift" by un-partitioning the table.
+3. The widened PK `("id","createdAt")` is invisible to Prisma's `@id id` model —
+ confirm no code assumes `id`-only uniqueness at the DB layer (app-level cuid
+ uniqueness is unaffected).
+
+## The one data-model change to confirm
+PostgreSQL requires the partition key in every unique/PK constraint, so the PK
+becomes `("id","createdAt")` instead of `("id")`. Nothing FK-references
+`SourceEvidence`, so this breaks no referential integrity — but it is a real
+change Steve should sign off on before Phase 2 runs.
← 7114f6a3 fix(homesonspec): summit planName cuts at first '<' — no raw
·
back to Homesonspec
·
TK-10878 Phase 2 drafts: fix 3 fatal + 2 hardening review fi ddb43e67 →