Re-ingest Cutover Runbook (verified Preview branch → prod, in place)

Re-ingest Cutover Runbook — verified Preview branch → prod, in place

⚠️ SCHEMA + FRAMING DRIFT (S491, 22/07/2026) — mechanism stands, table sets do NOT. Two things changed after this runbook was authored:

1. DR-025 (S441) reframed ID-45 as a one-time onboarding ingest (ingest-once, no ongoing watch) — the “re-ingest” framing here is historical; the cutover mechanism (verified Preview branch → prod in place, hybrid (c)) is unchanged.
2. ID-131 retired content_items ({131.17} shipped S449) and re-parented the five extraction tables to source_document_id (L-records model). The write-target table enumerations in §2/§3.1/§3.2 (and the uuid5 linkage spot-checks in §4) reference the retired surface — re-derive the table set from the current schema (supabase/types/database.types.ts) before executing any dump/restore. Do not run the §3.1 commands as written.

Status: authored as the {64.8} CUTOVER-MECHANISM runbook, resolving OQ-45-2. Covers the mechanism only: how a verified fresh Supabase Preview-branch re-ingest (ID-45) is cut over onto the existing prod project in place (the ID-108 / S348 model ratified at Phase-A ITEM4). The full {64.8} readiness surface that aggregates ALL of the ID-45 A/B/C/D gates into one go/no-go board is a separate, LATER step — this runbook is the cutover lane it consumes, not that board (see §8).

De-identification: the V1 client is referred to only generically — the first client / the single-tenant V1 client / the client corpus / the handover DB. No client codename appears anywhere in this doc.

---

0. Scope, model, and the OQ-45-2 resolution

What this runbook is. The procedure for moving a verified fresh-Preview-branch re-ingest result onto the existing prod project in place, reversibly, with no silent data loss of retained tables. It begins after the ID-45 run has passed its pre-flight (group A), per-stage write-correctness (group B), and must-pass quality-gate (group C) invariants on the Preview branch, and it ends at a smoke-green, rollback-drilled prod.

The cutover model (ID-108 / S348 — ratified, do NOT re-litigate). Run the canonical cocoindex flow into a fresh non-prod Supabase Preview branch → verify (ID-45 groups A–C) → cut the verified result onto the existing prod project in place. Prod stays the first-client handover DB; its project ref and persistent staging branch are unchanged; the ID-64 retentions stay in place. This is NOT a project transfer/swap and NOT a Vercel-env-repoint (that whole approach is SUPERSEDED — see §7).

0.1 OQ-45-2 resolution: hybrid (c), NOT branch promotion

Option	Verdict	Why
(a) Pure data restore	partial	pg_dump --data-only moves DATA but assumes prod schema is already migration-complete.
(b) Branch promotion (Supabase merge-to-prod)	NON-VIABLE for the data move	Supabase merge-to-production deploys migrations / edge functions / storage buckets only. Per Supabase’s own docs, data does not move between branches and seed-data changes are not merged to prod. Task 82 proved Preview branches replay SCHEMA onto a dataless DB — reference data must be seeded; the corpus never travels with the branch. So promotion can carry the schema forward but cannot carry the re-ingested corpus.
(c) HYBRID — RECOMMENDED	ADOPTED	Schema via migration replay (already on prod — prod has had the guarded repo migrations pushed) + DATA via pg_dump --data-only from the verified Preview branch into prod, FK-aware-ordered. This is the cutover mechanism this runbook documents.

One-line statement of the mechanism: prod already holds the schema (migration replay); the cutover step restores only the data from the verified Preview branch into prod via pg_dump --data-only → psql/pg_restore, in an order that respects integrity, then verifies and (if needed) rolls back.

---

1. Preconditions (INV-29 gate — ALL must be green before any prod write)

Cutover onto prod may proceed only when the ID-45 verification surface is green on the Preview branch. This runbook treats that as a hard gate, not a soft checklist.

• INV-29 — groups A–C green. A recorded green status for every group-A pre-flight gate, every group-B per-target write-correctness invariant, and every must-pass group-C quality-gate invariant (INV-1..28) exists for the Preview branch. A single red blocks cutover. (This is the slice of the {64.8} readiness surface this lane consumes — §8.)
• Pre-walk zero-row baseline was honoured (INV-6). The Preview run started from a confirmed-empty write-target set, so the verified counts are not polluted by a prior partial run.
• Prod is itself migration-complete + parity-verified (INV-30 precondition). Prod migration list shows zero pending; information_schema.columns prod↔intended diff = 0 (S187 precedent: 44 tables / 605 columns / 0 diffs = green). If prod is behind, push migrations to prod FIRST (schema-via-replay half of hybrid (c)) — see §3.0.
• A current prod snapshot exists. Take/confirm a prod point-in-time snapshot immediately before cutover — this is the structural rollback anchor (§6, INV-33).
• Pipeline service-account health on prod (INV-3). SQL probe on a0000000-0000-4000-8000-000000000001 returns token_null = false AND identities = 1. (Probe + remediation: database-rebuild-runbook.md §7.)

If any box is unchecked, STOP. Cutover is irreversible-ish in operator-time even though the snapshot exists; do not start the restore with a red gate.

---

2. Integrity model the cutover must respect (READ THIS before ordering anything)

The post-S297 write surface dropped the 5 cross-target FK constraints (migration 20260602073942_id64_buge_drop_crosstarget_fks.sql):

• content_items → source_documents
• content_chunks → content_items
• entity_mentions → content_items
• q_a_extractions → source_content_item
• form_template_fields → form_templates

Consequences that shape the cutover:

1. Integrity is by uuid5 construction, not by constraint. Every child row’s FK-value column is a deterministic uuid5(_KH_PIPELINE_DOC_NS, "<kind>:<rel_path>…") equal to the parent’s PK. So a data restore does not fail on parent-before-child ordering for the 9 write targets — but you still order to keep the restore readable and to satisfy the FKs that do remain (the RETAIN/reference tables: user_profiles, workspaces, feed_sources, etc. still have live FKs and auth dependencies).
2. There is NO ON DELETE CASCADE anymore (the migration removed the three CASCADE pairs). A wipe must delete children explicitly. If you ever clear a write target on prod, you must delete its children first by hand — nothing cascades. This is the single most dangerous footgun in the whole cutover: a naive DELETE FROM content_items leaves orphaned content_chunks / entity_mentions rows that the app will read.
3. pg_dump --data-only emits no SET search_path. Prepend SET search_path = public, extensions; to every dump, or fully-qualify every object. Use the Postgres-17 binaries (/opt/homebrew/opt/postgresql@17/bin/pg_dump / pg_restore / psql) — the platform is PG-17.x and a version-mismatched pg_dump refuses the dump.

---

3. Cutover sequence (hybrid (c) — schema already on prod; restore DATA only)

Project-ref discipline — the load-bearing safety step. Before ANY prod push or restore, run cat supabase/.temp/project-ref and confirm it reads the prod ref (rovrymhhffssilaftdwd). A stale link lands a push/restore on the wrong project silently (supabase/CLAUDE.md). Relink to prod ONLY at this deliberate cutover step — keep the operator linked to the Preview branch for all of the verify phase, and flip to prod here and nowhere earlier. Re-cat after every relink.

3.0 (If prod schema is behind) — schema-via-replay half

If §1’s INV-30 precondition showed prod behind: push the guarded repo migrations to prod.

cat supabase/.temp/project-ref   # MUST read rovrymhhffssilaftdwd
/opt/homebrew/bin/supabase db push --linked

• Protected-branch reset is a silent no-op via MCP — never reset prod through the MCP tool; supabase db push --linked is the only path that takes.
• Any migration touching auth.* (triggers / ALTER TABLE auth.users) needs the session-mode pooler on port 5432, not the transaction-mode :6543 pooler. Export SUPABASE_DB_URL_DIRECT (port 5432) for that one invocation only, then unset it — never persist it (database-rebuild-runbook.md §4a).

Re-verify INV-30 (zero pending, 0-column-diff) before continuing.

3.1 Dump the verified data from the Preview branch (DATA only)

Dump in two layers so the order is explicit and the RETAIN tables never get clobbered:

Layer 1 — the 9 re-ingested write targets + the UC5 promotion target. These are the freshly re-ingested rows the cutover delivers:

source_documents, content_items, q_a_extractions, entity_mentions,
entity_relationships, form_templates, form_template_fields, content_chunks,
reference_items   (+ q_a_pairs once the UC5 promotion has run on the branch)

PG17=/opt/homebrew/opt/postgresql@17/bin
# DSN = the verified Preview branch (NOT prod). --data-only, explicit table set.
"$PG17/pg_dump" --data-only --no-owner --no-privileges \
  --table=public.source_documents --table=public.content_items \
  --table=public.q_a_extractions --table=public.entity_mentions \
  --table=public.entity_relationships --table=public.form_templates \
  --table=public.form_template_fields --table=public.content_chunks \
  --table=public.reference_items --table=public.q_a_pairs \
  "$PREVIEW_BRANCH_DSN" > /tmp/reingest-writetargets.sql

Layer 2 — the {64.7} retained / keep-set + reference-data tables that were restored onto the branch and must land on prod intact. These already exist on prod as the handover DB’s retained data; dump them from the branch only if the branch’s verified copy is the source of truth for this cutover (otherwise prod’s own RETAIN rows are left untouched — see §5).

Decision gate: the branch copy is authoritative iff the {64.7} retention export deliberately re-exported the RETAIN tables onto the Preview branch; otherwise prod’s own RETAIN rows are authoritative and are left untouched (§5).

The RETAIN set is, by ID-45 PRODUCT INV-5/INV-32 / RESEARCH §2.1:

user_roles*, user_profiles, company_profiles, feed_sources, feed_prompts*,
entity_aliases, entity_pair_resolutions   (+ the 3 intelligence workspaces)

* user_roles (auth FK) and feed_prompts (created_by FK) are seeded separately — do NOT fold them into a blanket reference-data restore; they go in their own ordered step (see database-rebuild-runbook.md §6 + the reference-data list in RESEARCH §2.1).

Prepend the search_path to each dump file (pg_dump omits it):

sed -i '1i SET search_path = public, extensions;' /tmp/reingest-writetargets.sql

3.2 Restore order onto prod (children-aware; no CASCADE to lean on)

Because there are no cross-target FKs among the 9 write targets, the write-target restore order is integrity-neutral — but order it parent-first anyway for readability and to keep any retained FK (to user_profiles / workspaces) satisfiable:

1. RETAIN / reference + auth/user tables first (if being restored from the branch): user_profiles → user_roles → company_profiles → workspaces (3 intelligence) → feed_sources → feed_prompts → entity_aliases → entity_pair_resolutions → the named reference-data set (taxonomy_*, layer_vocabulary, guides, guide_sections, form_template_requirements (post-T2 name), feed_flags, tag_morphology_drift_flags). entity_pair_resolutions MUST be present and untruncated — truncating it breaks Stage-5 idempotency (RESEARCH §2.1).
2. Write-target parents: source_documents, form_templates.
3. Write-target children: content_items, content_chunks, entity_mentions, entity_relationships, q_a_extractions, form_template_fields, reference_items.
4. UC5 promotion target: q_a_pairs (only after q_a_extractions lands, and only after the bl-74 q_a_pair_history lineage cols are confirmed present on prod — OQ-45-3; history is append-only, late = lineage lost forever).

cat supabase/.temp/project-ref   # RE-CONFIRM rovrymhhffssilaftdwd
PG17=/opt/homebrew/opt/postgresql@17/bin
"$PG17/psql" "$PROD_DSN" -v ON_ERROR_STOP=1 -f /tmp/reingest-writetargets.sql

If you must CLEAR a prod write target before restore (e.g. prod carries stale legacy rows the branch supersedes): delete children before parents, explicitly — there is no CASCADE. E.g. DELETE FROM content_chunks; DELETE FROM entity_mentions; DELETE FROM entity_relationships; BEFORE DELETE FROM content_items;. Confirm zero orphans by uuid5 derivation afterwards.

3.3 Wipe-rebuild fallback ONLY (Preview branch not used — should not happen here)

This runbook’s path is Preview-branch-verified → restore. The destructive DROP SCHEMA public CASCADE rebuild is a fallback only and carries extra caveats if ever used:

• DROP SCHEMA public CASCADE does not drop extensions — move/drop vector first.
• Storage policies survive a schema drop — drop them via a DO-block.
• Config tables are pre-seeded by migrations — TRUNCATE … CASCADE them BEFORE any data restore or the restore conflicts on the seeded rows.

---

4. Post-cutover verification (INV-30, INV-31)

• INV-30 — prod migration-complete + parity-verified. Prod migration list zero pending; prod↔intended information_schema.columns diff = 0 (same 0-diff bar as the Preview branch).
• INV-31 — smoke-green across surfaces, NOT a landing-page-only check. Cutover exercises rarely-fired read paths, so triage with dev-tools console across 5–10 surfaces + a screenshot-parity browser baseline (old → new → diff). This method caught 2 real regressions at the first cutover (RESEARCH §6.4). Use the 4-agent browser baseline pattern; a clean landing page is necessary but nowhere near sufficient.
• Service-account re-probe. SQL on a0000000-0000-4000-8000-000000000001 → token_null = false, identities = 1 (the cutover must not have disturbed it).
• Spot-check uuid5 linkage by derivation, not by constraint (the FKs are gone): zero non-URL content_items whose derived sd: seed has no matching source_documents row; every content_items row has ≥1 derivable content_chunks row.

---

5. No silent data loss of RETAIN-class tables (INV-32)

The cutover must preserve all RETAIN-class reference / workspace / user / curated data: user_roles, user_profiles, company_profiles, feed_sources / feed_prompts, entity_aliases, entity_pair_resolutions, the 3 intelligence workspaces, and the named reference-data set.

• Post-cutover row counts for each RETAIN table ≥ pre-cutover counts; a diff confirms no retained row was dropped.
• If RETAIN tables were NOT dumped from the branch (because prod’s own copies are authoritative — §3.1 Layer 2 note), confirm the write-target restore did not touch them: the restore SQL is a fixed, explicit table set, so a RETAIN table outside that set is untouched by construction — verify the count is unchanged anyway.

---

6. Rollback (INV-33 — a path per failure class, DRILLED not just documented)

A reversible rollback path must exist and have been drilled for each failure class. Pick by the kind of failure observed at §4:

Failure class	Rollback mechanism	Why it is safe
Structural (schema/restore/parity broke; orphaned rows; bad counts)	Restore from the pre-cutover prod snapshot (§1 anchor). For the cocoindex on-prem worker, image-tag rollback is burn-safe by construction: rollback = roll-forward — the deploy-cocoindex job (onprem-deploy.yml, branch-guarded to refs/heads/main) PATCHes COCOINDEX_IMAGE_TAG in place to a prior sha-<commit> and redeploys that pinned tag.	The LMDB memo is untouched; the rolled-back image boots lifespan-only and never walks (boot is decoupled — ID-83 / bl-221; the walk fires only via the on-demand bearer-gated POST /walk → update_blocking(live=False)). LMDB is a cache, not the system of record — Supabase is canonical (Inv 20), so an image-tag rollback is never KH data loss.
Content-quality (rows present + structurally sound, but classification/extraction quality regressed)	Mass-supersede the bad cohort (write a superseding version), preserving history.	History is append-only; supersede keeps lineage instead of destroying it — the reversible, audit-safe path for a quality regression.

Drill requirement: for each class, a recorded rollback drill must show the prior verified state restored with zero retained-table data loss. INV-33 fixes the outcome (a drilled, reversible path exists); the table above is the mechanism for each class.

---

7. SUPERSEDED — do NOT re-litigate

The project-transfer / Vercel-env-repoint cutover is SUPERSEDED (historic-reingest-doc-review.md §4). The old first-arc plan moved to a new Supabase project and repointed the Vercel env at it (Path A+). That model is dead: the cutover is in place onto the existing prod project (rovrymhhffssilaftdwd). Do NOT provision a new project, do NOT transfer/swap projects, do NOT repoint Vercel envs as the cutover. The branch-promotion data-move (§0.1 option b) is likewise non-viable. This note exists to kill re-litigation — the mechanism is hybrid (c), in place, full stop.

---

8. Relationship to the {64.8} readiness surface and the ID-45 quality gate

• The {64.8} readiness surface is the LAST {64.x} step: a single go/no-go board aggregating ALL of ID-45’s A (pre-flight) + B (per-target write) + C (quality gate) + D (cutover/reversibility) gates. This runbook is the cutover (D) lane that board consumes — it is NOT that board, and it does not re-implement groups A–C. INV-29 (§1) is the join point: the board’s green status is this runbook’s entry gate.
• The quality gate (ID-45 group C / D1–D11) is an ID-45 spec deliverable, defined for the new write surface (incl. the new tables reference_items, q_a_extractions, relationship triples). This runbook references the gate as a precondition (INV-29) and does not define it.

---

Cross-references

• ID-45 {45.1} RESEARCH §6 (cutover mechanics) + §3 (run mechanics) + §2.1 (keep-set / RETAIN dispositions) — ../specs/id-45-full-corpus-reingest/RESEARCH.md.
• ID-45 {45.2} PRODUCT group D (INV-29..33) + INV-3/5/6 — ../specs/id-45-full-corpus-reingest/PRODUCT.md.
• ID-64 phase-a-findings.md ITEM4 (hybrid (c) recommendation) + historic-reingest-doc-review.md §3 (carried gotchas) / §4 (superseded items) — ../specs/id-64-pre-reingest-readiness/.
• database-rebuild-runbook.md §4a (session-mode pooler / auth.*), §6 (auth-user seed), §7 (service-account probe) — operational gotchas harvested, NOT duplicated.
• blank-db-restore-matrix.md — the SUPERSEDED new-project FK-ordered restore (its restore discipline informs §3.2; its project-transfer model is dead — §7).
• supabase/CLAUDE.md (KH repo) — cat supabase/.temp/project-ref before any push gotcha.

---

Last verified: NOT YET — runbook is draft until the first end-to-end cutover rehearsal from a verified Preview branch into prod.