Two-Stage Re-Ingestion Runbook

Two-Stage Re-Ingestion Runbook

Status: DRAFT — Stage 0–2 body is S176-era and not reconciled to the on-prem cocoindex topology (full reconciliation is ID-62/64 cutover-execution scope). The “Prerequisites → Re-ingest target” section below is current + verified (Task 82, S306) and supersedes the stale per-step mgrmucazfiibsomdmndh / .env.local placeholders. Last updated: 2026-06-03 (S306) Owner: Knowledge Hub operators

---

Purpose

This runbook is the step-by-step operator guide for re-ingesting the Knowledge Hub content corpus into a fresh Supabase project. It covers the full lifecycle from snapshotting the current production state through to post-ingestion quality validation.

The re-ingestion is split into two stages:

• Stage 1 re-ingests the Phew .docx corpus, existing markdown files, URL-sourced items, and RSS-promoted articles via the canonical pipeline into a blank database.
• Stage 2 (conditional, only if Stage 1 passes the quality gate) ingests the client’s new markdown files.

The old Supabase project is left untouched throughout. Rollback at any point means continuing to use the old project.

Read alongside:

• blank-db-restore-matrix.md — restore category per entry point, 25-step restore order (Steps 0–24), pre-export checklist
• re-ingestion-quality-protocol.md — 6-step quality measurement protocol, thresholds, DOCX pre-processing checklist
• database-rebuild-runbook.md — pre-existing rebuild runbook for same-project scenarios
• data-entry-points.md — canonical inventory of all 11 entry points

---

Snapshot naming convention

All snapshots, exports, and reports follow this naming pattern. Use the date of the operation in YYYY-MM-DD format.

Artefact	Filename pattern	Location
Pre-re-ingestion quality snapshot	pre-reingest-YYYY-MM-DD.jsonl	data/snapshots/
Post-Stage-1 quality snapshot	post-stage1-YYYY-MM-DD.jsonl	data/snapshots/
Post-Stage-2 quality snapshot	post-stage2-YYYY-MM-DD.jsonl	data/snapshots/
Stage 1 quality report	stage1-quality-report-YYYY-MM-DD.md	data/reports/
Stage 2 quality report	stage2-quality-report-YYYY-MM-DD.md	data/reports/
URL inventory export	url-inventory-pre-reingest.txt	data/
RSS promoted URLs export	rss-promoted-urls.txt	data/
Feed config SQL dump	feed-config-dump-YYYY-MM-DD.sql	data/exports/
Static config SQL dump	static-config-dump-YYYY-MM-DD.sql	data/exports/
Guides SQL backup	guides-backup-YYYY-MM-DD.sql	data/exports/
Storage bucket files	storage-backup-YYYY-MM-DD/	data/exports/
Extraction results	reingest-YYYY-MM-DD.jsonl	data/extractions/

All data/ paths are gitignored. Confirm with git check-ignore data/ before writing.

---

Prerequisites

⚠ S306 / Task 82 — READ THIS FIRST. The S176-era “provision a brand-new Supabase project (mgrmucazfiibsomdmndh) and repoint .env.local” model throughout this runbook is historical. The current re-ingest target model and its hard prerequisites are the two sections immediately below; the per-step project refs further down are stale placeholders pending the ID-62/64 reconciliation.

Re-ingest target: git-integrated preview branch (Task 82, S306)

Standard path — create the blank re-ingest target as a GIT-INTEGRATED Supabase preview branch. A git-integrated branch replays the guarded repo migration files (supabase/migrations/*.sql), so the schema it builds is exactly what supabase db reset builds locally.

Why this matters (bl-223 root cause). A non-git preview branch instead replays prod’s stored migration history (supabase_migrations.schema_migrations.statements), not the repo files. Prod’s stored statement for 20260422174117 (add_research_feed_to_product_guides) was the pre-2026-04-27 UNGUARDED version — three INSERT INTO guide_sections … SELECT … WHERE NOT EXISTS (guide_sections …) with no WHERE EXISTS (guides …) parent guard. On a dataless branch (guides is empty — guides are runtime-created by lib/intelligence/guide-generator.ts, never migration-seeded) that INSERT fires and FK-violates against the empty guides table, aborting branch creation. Only this one migration failed; the sibling 20260422174420 (wire_product_guide_sections, 19 UPDATEs) no-ops on an empty table and is benign-divergent. Stored-statement drift is invisible to schema-parity.yml.

Fix applied (S306, {82.2}). Prod’s stored statement for 20260422174117 was rewritten in place to the guarded version (direct supabase_migrations.schema_migrations UPDATE on prod rovrymhhffssilaftdwd; byte-verified md5 = 557be2d1e6a0a2d1a11c83dc26da4035 against the local / staging guarded parse). So non-git branches off prod are now also safe — but the git-integrated path remains the standard because it is immune to stored-statement drift by construction. Staging (turayklvaunphgbgscat) is a live git-integrated branch and stores the guarded statement (same md5) — the reference proof.

Create procedure (git-integrated):

1. Ensure the branch tracks the current repo supabase/migrations/ (the guarded files). Persistent-branch config lives in supabase/config.toml under [remotes.<branch>] — see [remotes.staging.*] for the pattern (db.seed, the auth.hook.before_user_created domain hook).
2. Create the preview branch via the git-integrated branching flow (Supabase applies the repo migration files in order). Do not use the non-git “branch off prod’s recorded history” path for a fresh re-ingest target.
3. Confirm the branch applied 20260422174117 cleanly (no guide_sections FK error in the branch-create logs).

Reference-data seed step (Task 82, {82.4}) — REQUIRED on every fresh branch

A fresh branch has the schema (migrations) but none of the runtime/seed- created reference data. guides, per-client guide_sections, taxonomy customisations, and templates are not migration-seeded. Seed in this order after migrations apply, before any content ingest:

1. supabase/seed.sql runs automatically on branch creation / db reset (schema-only fixtures: pipeline service account, CI workspace via an application_type_id key-subquery, CI guide/section/feed fixtures). Repaired S306 ({82.1}) — previously failed on the dropped workspaces.type column.
2. bun run seed:e2e-users — three E2E auth accounts + roles.
3. bun run sync:taxonomy — taxonomy_domains + taxonomy_subtopics.
4. staging-reference-refresh workflow — the 6 lookup tables (taxonomy_domains, taxonomy_subtopics, layer_vocabulary, entity_aliases, template_requirements, taxonomy_sync_state). See staging-refresh.md.
5. Guides / guide_sections are created at runtime by the app (lib/intelligence/guide-generator.ts) / MCP guide regeneration (Stage 1 Step 18), not seeded. The 20260422174117 guarded INSERT only fires (non-no-op) once the parent guides rows exist — by design, it is a safety-net conditional INSERT.

Migration squash (S176 pre-requisite)

Before provisioning the new project, squash the current 40 migrations into 1. This is a mandatory pre-step because:

1. Surfaces DDL gaps. The S118 squash (100→1) found ~20 missing columns, placeholder function stubs, and tables created via MCP execute_sql but never captured in migration files. The same pattern produced the entity_aliases gap flagged below.
2. Simplifies restore. A single migration is faster and eliminates ordering or idempotency issues between incremental migrations.
3. Acts as a dry run. If the squash fails against an empty local DB, the same failure would hit db push on the new project.

S118 lessons (see project_session118.md): Docker Desktop must be running. Expect issues with: placeholder function stubs, missing LANGUAGE clauses, columns added via MCP but not in migration files, views blocking column type changes, extensions.vector type availability, and storage bucket inserts needing idempotency.

• Migration squash completed and tested locally (S176 — 31dd72b8)
• All DDL gaps fixed (including entity_aliases CREATE TABLE) — 8 gap categories, see schema-reconciliation-report-s176.md
• Squashed migration pushed to production (db push confirmed no-op on rovrymhhffssilaftdwd)
• Types regenerated and committed (3,444 lines)

New Supabase project

• Provisioned in eu-west-2 (London) region (S176)
• pgvector 0.8.0 enabled (S176 dry run confirmed)
• Project ID: mgrmucazfiibsomdmndh
• Project URL: https://mgrmucazfiibsomdmndh.supabase.co

Environment variables

Update .env.local to point at the new project for all re-ingestion steps. Keep a copy of the old .env.local so you can switch back for rollback.

Variable	Value	Notes
NEXT_PUBLIC_SUPABASE_URL	https://mgrmucazfiibsomdmndh.supabase.co	New project URL
NEXT_PUBLIC_SUPABASE_ANON_KEY	From new project dashboard	Anon key
SUPABASE_SECRET_KEY	From new project dashboard	Service role key
SUPABASE_URL	https://mgrmucazfiibsomdmndh.supabase.co	Same as above
SUPABASE_ANON_KEY	From new project dashboard	Same anon key
SUPABASE_DB_PASSWORD	From new project dashboard	Required for CLI
ANTHROPIC_API_KEY	Unchanged	Classification API
OPENAI_API_KEY	Unchanged	Embedding API
TEST_USER_1_PASSWORD	Unchanged	E2E admin user
TEST_USER_2_PASSWORD	Unchanged	E2E editor user
TEST_USER_3_PASSWORD	Unchanged	E2E viewer user

Tooling

• Supabase CLI: /opt/homebrew/bin/supabase
• bun installed (project standard)
• python3 with pip install -r requirements.txt complete
• pandoc installed (brew install pandoc) — for Track Changes resolution
• Latest main checked out, bun install complete
• At least 2 GB free disk in repository root (snapshots include embeddings)
• BLOCKING pre-execution TODO: entity_aliases table — RESOLVED in S176 migration squash. CREATE TABLE now included in the squashed migration (20260416102457_pre_squash_reconciliation.sql). See docs/operations/schema-reconciliation-report-s176.md for details.

Source artefacts

These must be available locally before starting Stage 1:

• Phew .docx bid library files (for EP8 Q&A import)
• Phew markdown files directory (for EP2 markdown ingestion)
• URL inventory file data/url-inventory-pre-reingest.txt (exported in Stage 0)
• RSS promoted URLs file data/rss-promoted-urls.txt (exported in Stage 0)

---

Stage 0 — Snapshot current production state

Goal: Capture a complete quality baseline and export all artefacts needed to restore the new database. Nothing is written to the old project — this stage is read-only.

Important: All Stage 0 commands run against the old project (rovrymhhffssilaftdwd). Do NOT update env vars yet.

Step 0.1 — Quality snapshot (Quality Protocol Step 1)

Capture the full content state for later A/B comparison.

bun run scripts/snapshot-content-state.ts \
  --output data/snapshots/pre-reingest-YYYY-MM-DD.jsonl

This captures every content_items row with heading counts, chunk counts, entity canonical names, and 1024-dim embedding vectors. See quality protocol Step 1 for flags and options.

Step 0.2 — Pre-re-ingestion export checklist (Restore Matrix)

Work through each item in the restore matrix pre-export checklist. The full list:

0.2a — URL inventory (Restore Matrix EP1/EP4)

-- Run via Supabase dashboard SQL editor against old project
SELECT source_url
FROM content_items
WHERE source_url IS NOT NULL
  AND source_file IS NULL
  AND file_path IS NULL
  AND metadata->>'source' IS NULL;

Save output to data/url-inventory-pre-reingest.txt (one URL per line).

0.2b — RSS promoted URLs (Restore Matrix EP11)

SELECT fa.external_url
FROM feed_articles fa
WHERE fa.content_item_id IS NOT NULL;

Save output to data/rss-promoted-urls.txt (one URL per line).

0.2c — Feed config dump (Restore Matrix EP11)

Export feed_sources (25 rows), feed_prompts (3 rows), and feed_flags (1 row) as SQL INSERT statements. Save to data/exports/feed-config-dump-YYYY-MM-DD.sql.

0.2d — Feed articles snapshot (Restore Matrix EP11)

Export promoted feed_articles rows (75 rows where content_item_id IS NOT NULL), but set content_item_id to NULL in the dump (it will be re-linked in Stage 1 Step 14). Save to the same feed config dump file or a separate file.

0.2e — Static config dump (Restore Matrix side tables)

Export these tables with UUID preservation. Workspaces must be in a separate dump file because they are restored at a different step (Step 7) from the other config tables (Step 6):

• entity_aliases (24 rows), layer_vocabulary (4 rows), company_profiles (1 row), template_requirements (96 rows) → data/exports/static-config-dump-YYYY-MM-DD.sql
• workspaces (4 rows — UUIDs must be preserved for FK dependencies) → data/exports/workspaces-dump-YYYY-MM-DD.sql

0.2f — Storage bucket files (Restore Matrix EP3)

Download all 20 files from the old project’s Storage buckets:

• documents bucket (13 files)
• tender-documents bucket (1 file)
• templates bucket (6 files)

Save to data/exports/storage-backup-YYYY-MM-DD/ with the original directory structure preserved.

0.2g — Guides backup (Restore Matrix EP9)

Export guides (9 rows) and guide_sections (117 rows) as SQL INSERT statements. This is the fallback if MCP recreation prompts are unrecoverable. Save to data/exports/guides-backup-YYYY-MM-DD.sql.

0.2h — MCP-created items inventory (Restore Matrix EP9)

Document the 13 content_items + 9 guides that were created via Claude Desktop MCP. This inventory is needed for prompt-based recreation in Stage 1 Step 18/19.

Step 0.3 — DOCX pre-processing checklist (Quality Protocol Section 5)

Before Stage 1, run through the DOCX pre-processing checklist against the Phew .docx corpus:

• Track Changes detection via scripts/docx_utils.has_tracked_changes()
• Embedded objects noted
• Character encoding verified
• Password protection checked
• Filenames standardised

Step 0.4 — Stage Phew corpus for ingestion

The two Python ingestion scripts accept a directory, not individual files. Stage the 7 production Q&A .docx files separately from the 2 non-Q&A briefs (Product_KB_Dev_Brief.docx and Sector-Intelligence-Brief-Liam-Final.docx) which are explicitly excluded from re-ingestion.

mkdir -p data/phew-docx-reingest
cp "docs/client-documentation/DRAFT 2026 Tender and Bid Library Template for Phew - FAQs - Copy (1).docx" data/phew-docx-reingest/
cp "docs/client-documentation/DRAFT 2026 Tender and Bid Library Template for Phew - Security and Compliance  - Copy.docx" data/phew-docx-reingest/
cp "docs/client-documentation/DRAFT 2026 Phew - Tender and Bid Library - Implementation & Support .docx" data/phew-docx-reingest/
cp "docs/client-documentation/2026 Audit - Tender and Bid Library Template - FAQs .docx" data/phew-docx-reingest/
cp "docs/client-documentation/2026 Audit - Tender and Bid Library Template - Security & Compliance.docx" data/phew-docx-reingest/
cp "docs/client-documentation/2026 Audit - Tender and Bid Library Template - Implementation & Support.docx" data/phew-docx-reingest/
cp "docs/client-documentation/2026 Audit - Tender and Bid Library Template - Funtionality.docx" data/phew-docx-reingest/
ls data/phew-docx-reingest/ | wc -l   # expect: 7

Verify the Phew markdown corpus has been exported from production (S177 prep — originals lost):

bun run scripts/export-phew-articles.ts --dry-run    # confirm 13 files
bun run scripts/export-phew-articles.ts              # writes data/phew-markdown-reingest/ (checked in)

Expected target totals for Stage 1: ~222 Q&A pairs (all 7 .docx) and 13 markdown articles (Phew website scrape).

Step 0.5 — Verify exports

Before proceeding, confirm all artefacts exist:

• data/snapshots/pre-reingest-YYYY-MM-DD.jsonl — quality baseline
• data/url-inventory-pre-reingest.txt — URL list
• data/rss-promoted-urls.txt — RSS promoted URLs
• data/exports/feed-config-dump-YYYY-MM-DD.sql — feed config
• data/exports/static-config-dump-YYYY-MM-DD.sql — static config
• data/exports/storage-backup-YYYY-MM-DD/ — storage files (20 files)
• data/exports/guides-backup-YYYY-MM-DD.sql — guides backup
• data/phew-docx-reingest/ — 7 Q&A .docx staged
• data/phew-markdown-reingest/ — 13 Phew articles regenerated (checked in)
• MCP-created items inventory documented

---

Stage 1 — Phew corpus re-ingestion into blank DB

Goal: Bring the new Supabase project from empty to a fully populated Knowledge Hub with the Phew client’s content. This follows the restore matrix restore order steps 0—20.

Switch env vars now. Update .env.local to point at mgrmucazfiibsomdmndh / https://mgrmucazfiibsomdmndh.supabase.co. Every command from here targets the new project.

⚠ S306 / Task 82 — superseded target model. mgrmucazfiibsomdmndh is a dead S176-era placeholder. The current target is a git-integrated preview branch (see Prerequisites → “Re-ingest target”), which replays the guarded repo migration files directly — so Step 1’s link + db push against a fresh project is replaced by the branch-create flow, and the bl-223 guide_sections FK failure does not occur (git-integrated path; prod stored statement also fixed in {82.2}). Run the reference-data seed step (Prerequisites) after migrations, before Steps 11+ content ingest.

Step 1 — Apply migrations (Restore Matrix Step 1)

# Run with dangerouslyDisableSandbox: true
/opt/homebrew/bin/supabase link --project-ref mgrmucazfiibsomdmndh
/opt/homebrew/bin/supabase db push

This applies every migration in supabase/migrations/ in order, creating the full schema including the pipeline service account. See database-rebuild-runbook.md Section 4 for details on auth-schema migrations that may need the session-mode pooler (port 5432).

Step 2 — Regenerate types (Restore Matrix Step 2)

# Run with dangerouslyDisableSandbox: true
/opt/homebrew/bin/supabase gen types typescript \
  --project-id mgrmucazfiibsomdmndh \
  --schema public \
  > supabase/types/database.types.ts

Run bun lint afterwards to confirm no type drift.

Step 3 — Seed auth users (Restore Matrix Step 3)

bun run seed:e2e-users

Provisions the three E2E test users (admin/editor/viewer). Use --check to verify the pipeline service account is healthy (see database-rebuild-runbook.md Section 7).

Step 4 — Seed production user roles (Restore Matrix Step 4)

Manually INSERT or dump-restore user_roles for production users. Liam’s account must have the admin role.

Step 5 — Seed taxonomy (Restore Matrix Step 5)

bun run sync:taxonomy

Populates taxonomy_domains (15 rows) and taxonomy_subtopics (57 rows) and regenerates the classification prompt and plugin files. The Python pipeline reads taxonomy from lib/taxonomy/taxonomy.ts — sync:taxonomy keeps both sources in sync.

Step 6 — Seed static config (Restore Matrix Step 6)

Apply the SQL dump exported in Stage 0 Step 0.2e:

• entity_aliases (24 rows)
• layer_vocabulary (4 rows)
• company_profiles (1 row)
• template_requirements (96 rows)

Run via the Supabase dashboard SQL editor or MCP execute_sql against the new project.

Step 7 — Restore workspaces (Restore Matrix Step 7)

Restore the 4 workspace rows from the static config dump. UUIDs must be preserved because content_item_workspaces and feed_sources/feed_prompts have FK dependencies.

Step 8 — Restore feed config (Restore Matrix Step 8)

Apply the feed config dump from Stage 0 Step 0.2c:

• feed_sources (25 rows) — FK to workspaces.id
• feed_prompts (3 rows) — FK to workspaces.id

Step 9 — Restore feed articles snapshot (Restore Matrix Step 9)

Apply the promoted feed_articles snapshot (75 rows) from Stage 0 Step 0.2d. The content_item_id column is NULL at this point — it will be re-linked in Step 14 after the corresponding content items are re-ingested.

Step 9b — Restore feed_flags (Restore Matrix Step 9b)

Restore feed_flags (1 row) from the static config dump. FK dependencies: feed_article_id → feed_articles(id), prompt_version_id → feed_prompts(id). Must run after Step 8 (feed_prompts) and Step 9 (feed_articles).

Step 10 — Storage buckets (Restore Matrix Step 10) — SKIPPED

S176 decision: SKIP preservation. All 3 storage buckets (documents, tender-documents, templates) contain only E2E test residue and manual testing artifacts. No production files exist. Buckets are auto-created by the squashed migration (empty). No upload needed.

Step 11 — Re-ingest Phew .docx corpus (Restore Matrix Step 11 / EP8)

This is the largest ingestion step (~222 Q&A pairs across 7 files staged in Step 0.4).

PYTHONUNBUFFERED=1 python3 scripts/import_bid_library.py data/phew-docx-reingest \
  --batch-tag phew-reingest-2026

Use --dry-run first to preview the file list and Q&A pair count. The script runs the keyword classifier (not AI classification) — full AI classification can be added post-ingestion via batch reclassification if needed.

From S177 onwards the script also calls store_chunks on each Q&A at ingest time (see docs/specs/ep2-ep8-chunk-at-ingest-spec.md), so no separate chunk step is required for this run.

Mixing DRAFT + final .docx variants is safe and intended (S180 WP1). Ingest all 7 staged files together. The per-file-loop dedup in dedup_across_files_by_title (scripts/dedup.py) keeps the first-seen body for each question title and skips duplicate titles in later files. Filename sort order (ASCII) puts 2026 Audit final files ahead of DRAFT 2026 files, so final bodies win and DRAFT contributes its non-overlapping questions. Expect ~31 cross-file title skips and ~222 stored items. Do NOT archive DRAFTs before ingestion — they contain ~147 unique questions not present in the final templates (not a superset; see S179 drift-check note).

Estimated time: ~3 minutes for ~222 items (includes embedding generation

• chunking; S180 WP2 actual: 169s).

Step 12 — Re-ingest markdown files (Restore Matrix Step 12 / EP2)

Uses the Phew markdown corpus regenerated from production in S177 (see scripts/export-phew-articles.ts + data/phew-markdown-reingest/INDEX.md).

PYTHONUNBUFFERED=1 python3 scripts/ingest_markdown.py data/phew-markdown-reingest \
  --tag phew-markdown-reingest \
  --author "Phew Design"

Use --dry-run first. Use --skip-existing if you need to re-run after a partial failure (checks source_file column). Do not pass the client-facing docs/client-documentation/markdown/ directory — those files are derivative of the Q&A .docx already ingested in Step 11.

From S177 onwards ingest_markdown.py chunks at ingest time too, so no separate chunk step is required for this run.

Estimated time: ~5 minutes for 13 items.

Step 13 — Re-ingest URL inventory (Restore Matrix Step 13 / EP1)

PYTHONUNBUFFERED=1 python3 scripts/ingest.py \
  --file data/url-inventory-pre-reingest.txt

This re-ingests the ~13 URL-sourced items via the Python pipeline (full AI classification, entity extraction, embedding, summarisation).

Estimated time: ~10 minutes.

Step 14 — Re-link feed articles (Restore Matrix Step 14)

After Steps 11—13, the re-ingested content items have new UUIDs. The promoted feed_articles rows (restored in Step 9) need their content_item_id updated to point at the new UUIDs.

Write a URL-match script or SQL UPDATE:

-- Match feed_articles to content_items via URL
UPDATE feed_articles fa
SET content_item_id = ci.id
FROM content_items ci
WHERE fa.external_url = ci.source_url
  AND fa.content_item_id IS NULL;

Verify: the number of rows updated should match the count of promoted articles (75).

Step 15 — Backfill chunks (Restore Matrix Step 15)

Status as of S177: legacy safety net. EP2 (ingest_markdown.py) and EP8 (import_bid_library.py) now call store_chunks at ingest time (spec docs/specs/ep2-ep8-chunk-at-ingest-spec.md), so new items in Stage 1 already have chunk rows by this point. Run the backfill anyway as an idempotent safety net — store_chunks deletes-then-reinserts, so re-running produces identical state.

bun run scripts/backfill-chunks.ts

Estimated time: ~15 minutes (most items will be no-ops on re-ingest from S177 onwards; full time only applies when backfilling pre-S177 historical data).

Step 16 — Re-ingest RSS-promoted URLs (Restore Matrix Step 16 / EP11 items via EP1)

Extract URLs from the preserved feed_articles and re-ingest via the Python pipeline:

PYTHONUNBUFFERED=1 python3 scripts/ingest.py \
  --file data/rss-promoted-urls.txt

This re-ingests the ~75 RSS-promoted articles with full AI classification and embedding.

Estimated time: ~15 minutes.

After ingestion, re-run the feed article re-link (Step 14 SQL) to pick up any newly created content items from RSS URLs.

Step 17 — Re-link workspace assignments (Restore Matrix Step 17)

Re-create content_item_workspaces rows to link intelligence workspace items to their workspaces. This requires matching the new content item UUIDs to the preserved workspace UUIDs.

-- Example: link RSS-promoted items to intelligence workspace
INSERT INTO content_item_workspaces (workspace_id, content_item_id)
SELECT
  ws.id,
  ci.id
FROM content_items ci
JOIN feed_articles fa ON fa.external_url = ci.source_url
JOIN feed_sources fs ON fs.id = fa.feed_source_id
JOIN workspaces ws ON ws.id = fs.workspace_id
WHERE fa.content_item_id IS NOT NULL
ON CONFLICT DO NOTHING;

Step 17b — MCP re-authentication

Before Steps 18-19, ensure Claude Desktop is authenticated against the new Supabase project. After switching env vars (Step 1) and deploying to Vercel (Post-6), existing OAuth tokens from the old project become invalid.

1. In Claude Desktop: Settings → Connectors (or equivalent)
2. Disconnect the Knowledge Hub connector
3. Reconnect — the connector URL remains the same (https://knowledge-hub-seven-kappa.vercel.app/api/mcp/mcp)
4. Complete the OAuth flow — this authenticates against the new project’s Supabase Auth
5. Verify connectivity: run search_knowledge_base to confirm access

Note: The .well-known/oauth-protected-resource endpoint automatically advertises the new Supabase Auth URL (read from NEXT_PUBLIC_SUPABASE_URL at runtime). No URL changes needed in Claude Desktop.

Step 18 — MCP guide regeneration (Restore Matrix Step 18 / EP9)

Re-create the 9 guides and 117 guide sections via Claude Desktop MCP prompts. This is a manual step — use the reconstructed prompts (or original Claude Desktop chat transcripts if available from Liam) against the new project’s MCP server. See docs/operations/guide-regeneration-prompts.md for all prompts.

If prompts are unrecoverable, restore from the SQL backup (data/exports/guides-backup-YYYY-MM-DD.sql).

A/B compare the recreated guides against the old project to verify content quality.

Estimated time: 1—2 hours (manual).

Step 19 — MCP content item recreation (Restore Matrix Step 19 / EP9)

Re-create the ~13 MCP-authored content items (7 consolidation-phew company documents, 3 pigeon-post demo items, etc.) via Claude Desktop MCP prompts.

Known limitation: MCP-created items use a 5,000-char embedding truncation ceiling vs 24,000 chars for pipeline-ingested items (Divergence 5 in the quality protocol, tracked as MCP-EMBED-1). This affects ~13 items and is acceptable for re-ingestion.

The 3 pigeon-post demo items are test content and can be skipped (Lost-acceptable per restore matrix).

Estimated time: ~30 minutes (manual).

---

Stage 1 decision gate (Restore Matrix Step 20)

This is the critical quality checkpoint. Do NOT proceed to Stage 2 until all thresholds below are met.

Gate Step A — Post-Stage-1 quality snapshot

Capture the new project’s state:

bun run scripts/snapshot-content-state.ts \
  --output data/snapshots/post-stage1-YYYY-MM-DD.jsonl

Gate Step B — Quality comparison (Quality Protocol Step 4)

Compare pre-re-ingestion baseline against post-Stage-1 state:

bun run scripts/compare-quality.ts \
  --old data/snapshots/pre-reingest-YYYY-MM-DD.jsonl \
  --new data/snapshots/post-stage1-YYYY-MM-DD.jsonl \
  --output data/reports/stage1-quality-report-YYYY-MM-DD.md

Gate Step C — Metric thresholds

All 8 quality dimensions must meet or exceed these thresholds. Threshold values are from quality protocol Section 3 Step 4; the MUST PASS / SHOULD PASS gate classification is defined by this runbook:

#	Dimension	Metric	Threshold	Gate
1	Structural fidelity	Heading-count ratio (new / old)	>= 90% of items at ratio >= 0.9	MUST PASS
2	Content completeness	Character-count ratio (new / old)	Median 0.95—1.10	MUST PASS
3	Embedding stability	Cosine similarity (old vs new)	Median > 0.95, none < 0.90	MUST PASS
4	Classification stability	Primary domain match	>= 95% match	MUST PASS
5	Entity extraction stability	Jaccard similarity of canonical names	Mean > 0.90	MUST PASS
6	Coverage equivalence	Per-domain item counts	No domain loses > 10% of items	MUST PASS
7	Chunk quality	Chunks per document	Articles 3—20 avg; Q&A 1	SHOULD PASS
8	Body-text completeness	Word-count ratio (new / old)	>= 90% of items at ratio >= 0.9	SHOULD PASS

Note on embedding stability (Dimension 3): Items originally ingested via the Python pipeline had embeddings built from only the first 1,500 characters (pre-S168 truncation limit). Re-ingestion uses the corrected 24,000-character ceiling. These items will show low cosine similarity by design — the new embedding is a better representation. Flag them separately per the quality protocol bonus step caveat.

Gate Step D — Embedding smoke test

bun run scripts/embedding-smoke-test.ts

Pass criteria: Median cosine similarity > 0.95, no individual item below 0.90 (excluding Python-pipeline items per the caveat above).

Gate Step E — Human review (Quality Protocol Step 6)

The product owner visually reviews 10 sampled items end-to-end:

• 2 articles — heading structure preserved, paragraphs intact
• 2 PDFs — page boundaries visible, tables rendered
• 2 Q&A pairs — question/answer separation maintained
• 2 blogs — formatting preserved, code blocks intact
• 2 policy documents — numbered lists and regulatory references intact

Compare old rendered output against new rendered output for each item. Capture screenshots of any visual regressions.

Gate Step F — Decision

Outcome	Action
All MUST PASS thresholds met, human review acceptable	Proceed to Stage 2
Any MUST PASS threshold failed	Investigate root cause, fix pipeline, re-run Stage 1
SHOULD PASS thresholds failed but MUST PASS all pass	Proceed with documented exceptions
Unrecoverable quality regression	Rollback (continue using old project)

Record the decision and the full quality report in the session handoff.

---

Stage 2 — Client new markdown files (conditional)

Prerequisite: Stage 1 decision gate passed.

Goal: Ingest the client’s new markdown files into the same new project and validate that quality remains stable.

Step 2.1 — DOCX pre-processing (if applicable)

If the new files include any .docx, run through the DOCX pre-processing checklist before ingestion.

Step 2.2 — Ingest new markdown

PYTHONUNBUFFERED=1 python3 scripts/ingest_markdown.py <path-to-new-markdown-dir> \
  --tag client-new-markdown-2026 \
  --author "Client Name"

Use --dry-run first. Use --skip-existing if re-running after a partial failure.

Step 2.3 — Backfill chunks (legacy safety net)

From S177 onwards EP2 (ingest_markdown.py) chunks at ingest, so Step 2.2 already produced content_chunks rows. Running the backfill is still safe (idempotent no-op for already-chunked items) and is recommended as a defence in depth:

bun run scripts/backfill-chunks.ts

Step 2.4 — Post-Stage-2 quality snapshot

bun run scripts/snapshot-content-state.ts \
  --output data/snapshots/post-stage2-YYYY-MM-DD.jsonl

Step 2.5 — Quality comparison (A/B against Stage 0 + Stage 1)

Run two comparisons:

Against pre-re-ingestion baseline (Stage 0):

bun run scripts/compare-quality.ts \
  --old data/snapshots/pre-reingest-YYYY-MM-DD.jsonl \
  --new data/snapshots/post-stage2-YYYY-MM-DD.jsonl \
  --output data/reports/stage2-vs-baseline-YYYY-MM-DD.md

Against post-Stage-1 snapshot:

bun run scripts/compare-quality.ts \
  --old data/snapshots/post-stage1-YYYY-MM-DD.jsonl \
  --new data/snapshots/post-stage2-YYYY-MM-DD.jsonl \
  --output data/reports/stage2-vs-stage1-YYYY-MM-DD.md

The Stage-2-vs-Stage-1 comparison should show only additive changes (new items) with no quality regression on existing items. The same thresholds from the Stage 1 decision gate apply to the paired (existing) items.

Step 2.6 — Embedding smoke test

bun run scripts/embedding-smoke-test.ts

Same pass criteria as Stage 1 Gate Step D.

---

Post-Stage 2

After both stages are complete and quality validated, complete these follow-up tasks.

Post-1 — MCP guide regeneration (EP9)

If not already done in Stage 1 Step 18, regenerate all 9 guides and 117 guide sections via Claude Desktop MCP. The guides reference KB content — they should be regenerated after the full corpus is present.

Post-2 — Bid outcome re-integration (EP10)

No bid data currently exists (0 bids in production). If real bids accumulate before re-ingestion, re-run POST /api/bids/:id/outcome/integrate for each won bid. No batch endpoint exists — per-bid only.

Post-3 — Feed re-link verification (EP11)

Verify all promoted feed_articles are correctly linked:

SELECT COUNT(*) AS linked
FROM feed_articles
WHERE content_item_id IS NOT NULL;
-- Expected: 75 (or more if new articles were promoted during re-ingestion)

Resume cron polling — new articles flow normally once feed_sources and feed_prompts are seeded and the cron is active.

Post-4 — Final snapshot

Capture the definitive post-re-ingestion state:

bun run scripts/snapshot-content-state.ts \
  --output data/snapshots/final-YYYY-MM-DD.jsonl

Post-5 — Browser smoke test

Walk through the database-rebuild-runbook.md Section 9 smoke test checklist:

• Sign in at /login as test.user1@test-kb-aish.co.uk
• /settings > Team Members: three test users visible, no pipeline service account
• /dashboard loads without errors
• /content-owners renders
• /intelligence/workspaces loads (if Sector Intelligence enabled)

Post-6 — Update project references

After confirming the new project is stable:

• Update CLAUDE.md Supabase project ID (replace rovrymhhffssilaftdwd with mgrmucazfiibsomdmndh)
• Update Vercel environment variables to point at the new project (NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY, SUPABASE_SECRET_KEY, SUPABASE_URL, SUPABASE_ANON_KEY)
• All Claude Desktop / Claude.ai users disconnect and reconnect their MCP connectors (OAuth tokens from old project are invalid against new Supabase Auth)
• Update any CI/CD configurations

---

Rollback

The old Supabase project (rovrymhhffssilaftdwd) is untouched throughout this entire procedure. Rollback = continue using the old project.

To rollback:

1. Restore the old .env.local (pointing at rovrymhhffssilaftdwd)
2. Revert any Vercel environment variable changes
3. Confirm the old project is still operational (dashboard loads, content renders)
4. Delete or pause the new project to stop billing

No data migration is needed in the rollback direction — the old project was never modified.

---

Estimated timeline

Phase	Steps	Estimated time
Stage 0 (snapshot + export)	0.1—0.4	1—2 hours
Stage 1 setup (migrations, seeds, config)	Steps 1—10	30—45 minutes
Stage 1 ingestion	Steps 11—16	1.5—2 hours
Stage 1 MCP recreation (manual)	Steps 18—19	1.5—2.5 hours
Stage 1 decision gate	Gate Steps A—F	1—1.5 hours
Stage 2 (conditional)	Steps 2.1—2.6	1—2 hours
Post-Stage 2	Posts 1—6	1—2 hours
Total (excluding wait time)		7—12 hours

This can be spread across multiple sessions. The new project is stable between sessions — no time pressure to complete in one sitting.

---

Cross-references

Document	Location	Relevance
Blank-DB Restore Matrix	docs/operations/blank-db-restore-matrix.md	25-step restore order (Steps 0–24), per-entry-point categories, pre-export checklist, FK dependency chain
Re-Ingestion Quality Protocol	docs/operations/re-ingestion-quality-protocol.md	6-step quality measurement, 8 quality dimensions with thresholds, DOCX pre-processing checklist, embedding smoke test
Database Rebuild Runbook	docs/operations/database-rebuild-runbook.md	Same-project rebuild procedure, auth migration notes, troubleshooting, browser smoke test checklist
Data Entry Points Reference	docs/reference/data-entry-points.md	Canonical inventory of all 11 entry points, processing pipeline per entry point, compliance matrix
Scripts referenced	scripts/snapshot-content-state.ts, scripts/compare-quality.ts, scripts/embedding-smoke-test.ts, scripts/ingest_markdown.py, scripts/import_bid_library.py, scripts/backfill-chunks.ts, scripts/ingest.py

---

Lessons learned — the S175 → S182 re-ingestion + cutover arc

This runbook was executed end-to-end against mgrmucazfiibsomdmndh across sessions 175–182, with a final cutover completing in S182. The following traps surfaced during that run and are captured here so the next client onboarding (or the next time this process runs against the same client) can skip straight past them. The full session-by-session narrative lives in docs/continuation-prompts/continuation-prompt-kh-s*.md and docs/operations/cutover-report-s182.md.

Squash drift is pervasive — advisors do not catch it

The migration squash at 20260416102457_pre_squash_reconciliation.sql (43 → 1, 8,600 lines) is pg_dump-shaped and drops business-logic content even though Supabase advisors pass it clean:

• ~28 NOT NULL constraints silently dropped on columns that still had 100% populated rows in practice. Run a nullability diff (information_schema.columns) old-vs-new before cutover; do not rely on advisor output.
• Column renames fall through: pipeline_runs.error_log → error_message and processing_queue.task_type → job_type arrived in application code but the squash captured the pre-rename shape.
• Stubbed function bodies: eleven PL/pgSQL functions were replaced with RETURN; stubs after the squash (original bodies >1.7k chars each). Quick detector: length(pg_get_functiondef(p.oid)) — anything that dropped more than 30% versus the retiring project is stub-suspect.
• One RPC missing entirely (get_bid_question_stats) — production code calls it; test suite caught this.
• CHECK constraint drift: pipeline_runs_status_check captured the pre-widening shape (only running/completed/failed). Application code had emitted completed_with_errors since S152B but writes were silently rejected. Run pg_get_constraintdef against every CHECK constraint the application writes to.
• Type drift on one column: pipeline_runs.items_created was integer on the squash baseline but uuid[] in production — a shape-changing drift that the TypeScript types hid because both deserialise compatibly in supabase-js.

Fold a “column + function diff before cutover” step into the runbook: dump information_schema.columns and pg_proc from the retiring project and the new project via the pooler, diff, and treat anything material (renames, NOT NULL, type drift, stub-suspect functions) as a blocker until reconciled with a migration.

MCP access token is organisation-scoped

Supabase MCP tools (list_projects, execute_sql, apply_migration) see only projects in the organisation that owns the MCP token. While the new project lived in a separate organisation (the default before transfer), the entire MCP toolset was invisible to it. Fallback path:

• Direct psql via the pooler at aws-1-eu-west-2.pooler.supabase.com:6543 with PGPASSWORD from .env, under dangerouslyDisableSandbox: true.
• Supabase dashboard paste-in for advisor runs.

After a project transfer into the MCP-visible organisation, full MCP tooling resumes. If the transfer is delayed, plan for a multi-day period of direct-psql DDL work and accept that migration history will need re-linking at cutover.

Bun + HTTP 204 hang through the sandbox proxy

supabase-js .update() / .insert() / .upsert() / .delete() without a trailing .select() returns HTTP 204. Bun’s fetch hangs on 204 when routed through the Claude Code sandbox proxy (production is unaffected). Any Supabase-writing script must run with dangerouslyDisableSandbox: true — do not paper over the problem by adding .select() calls in production code.

supabase gen types leaks its CLI version notice to stdout

supabase gen types typescript --project-id X --schema public > file.ts writes a CLI version notice to the bottom of the output file if stderr is captured. Always redirect stderr to /dev/null:

supabase gen types typescript --project-id X --schema public 2>/dev/null > file.ts

Without the redirect, the next bun run build fails instantly with a TypeScript parse error on the embedded notice.

Migration history requires explicit re-link after direct-psql DDL

supabase migration new only writes the local file; it does not register anything with the remote. Any migration applied through direct psql (see above) needs supabase migration repair --status applied <version> at cutover to sync the supabase_migrations.schema_migrations ledger. Capture the full list of psql-applied migration timestamps during the re-ingestion arc so the repair command at cutover is deterministic, not a detective exercise.

apply_migration assigns its own timestamp. When using the MCP apply_migration tool, the registered version on the remote uses the tool’s apply-time timestamp, not the timestamp on the local filename. Rename the local file to match the remote version after applying (or accept drift in supabase migration list output).

Cutover direction — Supabase project transfer beats env-var repoint

The original plan assumed a Vercel env-var repoint at cutover (OLD → NEW URL + keys). A cleaner alternative surfaced once billing constraints became visible: transfer the new project into the Pro-tier organisation.

Pre-requirements to verify up-front:

• Owner role in source organisation.
• Member role in target organisation.
• No active GitHub integration on the source project.
• No log drains on the source project.
• No project-scoped roles on the source project.
• Target organisation not managed by Vercel Marketplace.

Transfer completes with zero downtime for paid-to-paid moves in the same region. Project ref is preserved, so Vercel env vars + local .env remain correct. Billing inherits the target organisation’s plan at the end of the current cycle. This is materially simpler than swapping env vars and triggering a fresh Vercel deploy.

Capture screenshot baselines before the cutover, not after

Before swapping .env.local to point at the new project, screenshot every surface that depends on data: the Coverage Dashboard tabs (Priority Gaps, Domain Coverage, Templates per template selection, Guides), the Intelligence landing, the Configuration Categories / Tags / Depth Levels / Entities tabs, and any domain-specific views. Twelve screenshots covered Knowledge Hub comfortably.

Post-cutover, partition the screenshots across ~4 agent-browser --session parity-{A,B,C,D} sub-agents in parallel. Each agent logs in as the same admin user, navigates the assigned surfaces, diffs current state vs baseline, and reports MATCH / EXPECTED-DELTA / UNEXPECTED / BROKEN per surface. Keep each agent brief tight (3 screenshots, exact URLs, known expected deltas) — agents have a 200K context budget and screenshots are image-expensive.

Guide domain_filter reclassification side-effect

Any guide with a domain_filter that points at a category the re-ingested content no longer populates will render 0/N sections — the guide structure is intact, but the filter no longer matches. In Knowledge Hub’s case three Product Guides pointed at products-services (the old Client-tagged shape) instead of product-feature (the active post-reingest shape). One UPDATE guides per drifted guide closes the regression. Screenshot parity caught this instantly; a DB-only audit would have missed it.

Guide rows sometimes disappear between refreshes

The MAT Auditing Intelligence Guide row was missing on the new project at cutover time. No audit log explained the deletion; the most likely cause is a manual guide-refresh pass that deleted-and-recreated subset guides and missed one. Before sunset, diff the guides and guide_sections tables row-for-row between source and target. Restore any missing row verbatim (UUIDs, display_order, section-id references preserved) so downstream code referencing the ids continues to work.

Watch for pre-existing regressions surfaced by the cutover

Cutover traffic — especially admin actions like delete — exercises code paths that rarely fire in day-to-day use. During the S182 cutover a GET /api/read-marks 400 surfaced on a single-UUID query string (the client sends ?item_ids=<uuid> without a comma, and the schema required an array). Fix is one-line; the signal is that cutover is a good moment to triage the dev-tools console across 5–10 common surfaces, not just check for 5xx on the landing page.

Re-ingestion doubles as practice for client onboarding

The S175 → S182 arc was driven by existing-client re-ingestion but the runbook, restore matrix, quality protocol, and rebuild runbook generalise to “spin up a blank Supabase project for a new client and ingest their corpus.” Treat every session in this arc as a dress rehearsal for that path. The gotchas above are the compact lessons-learned; the full narrative lives in the S175 → S182 continuation prompts and the S182 cutover report.

---

Document lifecycle

• S175 WP2: DRAFT — authored from restore matrix, quality protocol, and rebuild runbook.
• Post-provisioning (S176 — DONE): Project ID mgrmucazfiibsomdmndh and URL https://mgrmucazfiibsomdmndh.supabase.co are confirmed actual values (not placeholders). Dry-run verified: db push clean, pipeline service account healthy, 3 E2E test users seeded.
• Post-Stage-1 execution (S179–S180): Stage 1 executed against the new project. Path A defects surfaced and closed (entity-extraction backfill, taxonomy case normalisation). Q&A cross-file dedup landed. Stage 2 ingested via scripts/ingest_stage2_markdown.py. Partial post-squash reconciliation migrations applied.
• Post-Stage-2 execution (S181): Full post-squash reconciliation consolidated (20260419134609). Stage 2 chunk backfill run (scripts/backfill_chunks_stage2.py, 240 chunks). Cutover corpus decision: Union Stage 1 + Stage 2 (546 items).
• Cutover complete (S182 — VALIDATED): Supabase project transfer into Proto-1; MCP visibility restored; migration history re-linked; screenshot parity audit PASS after one residual-fix migration (20260419212103). Retiring project (rovrymhhffssilaftdwd) sunset plan captured in docs/operations/cutover-report-s182.md §7.