Database Rebuild Runbook

Database Rebuild Runbook

⚠️ PARTIAL DRIFT (S491, 22/07/2026) — gotcha sections current; rebuild flow historical. Still load-bearing and consumed by reingest-cutover-runbook.md: §4a (session-mode pooler for auth.* DDL), §6 (E2E user seed), §7 (pipeline service-account probe). Historical/stale elsewhere: env-var names predate the current scheme (SUPABASE_SECRET_KEY/ANON_KEY → now SUPABASE_SERVICE_ROLE_KEY / SUPABASE_PUBLISHABLE_KEY; .env retired — .env.local only); the §2 project table predates the four-DB topology (see reference/platform-context.md — rovrymhhffssilaftdwd is the client prod, and the S176 demo project may no longer exist); §8’s re-ingest pointers (ingest_markdown.py, blank-db-restore-matrix, two-stage runbook) predate the cocoindex pipeline + DR-025 onboarding-ingest reframe; type regen is now --schema public,api (the CI parity gate compares that form).

End-to-end procedure for rebuilding a Knowledge Hub Supabase database from scratch. Use this whenever you spin up a new project, recover from disaster, or reset local dev. It is the higher-level wrapper around e2e-test-setup.md §11 and the S156 corrective migration verification — written so an operator can take a project from “freshly created” to “smoke-tested and ready” without forgetting a step.

Note: Every Supabase CLI command in this runbook must be run with dangerouslyDisableSandbox: true. The CLI uses direct Postgres connections via aws-1-eu-west-2.pooler.supabase.com, which the Claude Code sandbox blocks. See CLAUDE.md → “Gotchas → Supabase → CLI in Claude Code sandbox” for the full explanation.

---

1. When to use this runbook

Scenario	What this achieves
Fresh demo DB (new project for a sales demo or pilot client)	Brings the project from empty to fully provisioned with schema, test users, and seed content.
Phew re-ingest into a fresh project	Rebuilds the Phew client database from migrations and re-ingests their markdown library against the latest pipeline.
Disaster recovery (production rebuild from migrations)	Restores the canonical schema and the pipeline service account after catastrophic data loss.
Local dev reset	Wipes local Postgres state and rebuilds from migrations so a developer can reproduce production behaviour against an empty DB.

If your scenario does not match one of these, stop and check with Liam before running anything destructive.

---

2. Prerequisites

Tooling

• Supabase CLI installed at /opt/homebrew/bin/supabase (macOS Homebrew path).
• bun installed (project standard — never npm or yarn).
• python3 installed if you intend to run the Phew re-ingest pipeline.

Environment variables

Must be set in .env/.env.local (or the new project’s secrets store):

• SUPABASE_DB_PASSWORD — required by the CLI for db push/db reset/ gen types. Source from .env before running any CLI command.
• NEXT_PUBLIC_SUPABASE_URL — the new project’s URL.
• NEXT_PUBLIC_SUPABASE_ANON_KEY — the new project’s anon key.
• SUPABASE_SECRET_KEY — service role key (NOT the anon key).
• TEST_USER_1_PASSWORD, TEST_USER_2_PASSWORD, TEST_USER_3_PASSWORD — passwords for the three E2E test users (admin/editor/viewer).

Project ID

You must know the Supabase project ID (project-ref) for the target:

• Production / live KB: rovrymhhffssilaftdwd
• New demo DB / Phew re-ingest: mgrmucazfiibsomdmndh (provisioned S176, eu-west-2). For the full re-ingestion procedure, follow the Blank-DB Restore Matrix and the Two-Stage Re-Ingestion Runbook.

Pre-flight checklist

Before you run a single command, confirm:

• You are pointing at the right project. Double-check the project ref in NEXT_PUBLIC_SUPABASE_URL. A db reset against the wrong project is catastrophic and irreversible.
• Latest main is checked out and migrations are up to date locally.
• All env vars above are set in your shell (echo $SUPABASE_DB_PASSWORD returns a value, etc.).
• Nobody else is actively using the target project.
• You have read this runbook end-to-end before running Step 1.

---

3. Step 1 — Reset the database

Warning: supabase db reset is destructive and irreversible. It drops every row in auth.users, public.user_roles, pipeline_runs, content_items, every embedding, every bid, every intelligence run, every storage object reference — everything. Confirm you are pointing at the right project before continuing. There is no undo.

# Run with dangerouslyDisableSandbox: true
/opt/homebrew/bin/supabase db reset --linked

The --linked flag targets the project linked via supabase link --project-ref <ref>. Run supabase link first (also with sandbox bypass) if the project is not yet linked. After the reset completes, the database is empty — schema and all.

---

4. Step 2 — Push migrations

# Run with dangerouslyDisableSandbox: true
/opt/homebrew/bin/supabase db push

Applies every migration in supabase/migrations/ in order. Two notes:

• The amended 20260406180000_create_pipeline_service_account.sql handles the pipeline service account correctly out of the box. It initialises every GoTrue token column to '' (not NULL) and inserts the matching auth.identities row, so a fresh rebuild produces a working service account on the first push.

4a. Migrations that touch auth.* (session-mode direct URL)

Most migrations target the public schema and apply cleanly through the default transaction-mode pooler at aws-1-eu-west-2.pooler.supabase.com:6543. Migrations that touch the auth schema (e.g. the S157 WP3 GoTrue NULL-token trigger at supabase/migrations/20260409110643_auth_users_token_column_null_guard.sql) cannot use the transaction-mode pooler — it has restricted DDL permissions on auth.*. They need the session-mode pooler on port 5432 instead.

When you need this: you are pushing a migration whose SQL contains CREATE TRIGGER ... ON auth.users, ALTER TABLE auth.users, or any auth.* DDL. db push will fail with a permissions error if you try to apply these through the default pooler.

How to enable it on demand (do NOT leave this variable set permanently — it expands the local attack surface if .env leaks):

1. Export the session-mode URL in your shell for the current terminal only:

   export SUPABASE_DB_URL_DIRECT="postgresql://postgres.rovrymhhffssilaftdwd:${SUPABASE_DB_PASSWORD}@aws-1-eu-west-2.pooler.supabase.com:5432/postgres"

   Note the port is 5432 (session mode), not 6543 (transaction mode). The password is interpolated from the existing SUPABASE_DB_PASSWORD env var so you are not typing or pasting the secret.

2. Run supabase db push with dangerouslyDisableSandbox: true. The CLI picks up SUPABASE_DB_URL_DIRECT automatically when present and uses it instead of the default pooler for the current invocation.

3. When the migration has applied, unset SUPABASE_DB_URL_DIRECT in your shell and close the terminal. Do NOT write this value into .env — keep it as a one-shot, per-invocation override.

Residual gap: even with session-mode access, the managed-platform postgres role cannot run ALTER TABLE auth.users ENABLE ALWAYS TRIGGER or COMMENT ON TRIGGER ... ON auth.users because those require ownership by supabase_auth_admin. The S157 WP3 trigger ships in default ENABLE ORIGIN mode; the pg_restore residual gap is covered at runtime by the verifyPipelineUserShape() probe wired into this runbook’s Step 5 (§7). File a Supabase support ticket if you need the ENABLE ALWAYS escalation — the April 2025 schema-access announcement says project owners should have this permission, but the managed platform does not expose it today. No manual fix-up required.

• If this is a fresh rebuild (not a snapshot clone), the 20260408134124_fix_pipeline_service_account_auth_shape.sql migration is a no-op — do not panic when you see it in the migrations list. Its UPDATE is a no-op against the already-correct row, and its INSERT ... ON CONFLICT DO NOTHING skips the existing identities row. The migration only does real work on snapshot clones that still carry the bad row.

Tip: If db push fails with “no migrations to apply” against a brand-new project, the project may not be linked. Run supabase link --project-ref <your-ref> (with sandbox bypass) and retry.

---

5. Step 3 — Regenerate types (local dev only)

Only required when rebuilding for local dev — production deployments use the types committed to git, generated against the live schema.

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

Substitute rovrymhhffssilaftdwd for <your-ref> against the live KB project. After regeneration, run bun run lint to confirm no type drift broke existing code. Never edit supabase/types/database.types.ts by hand.

---

6. Step 4 — Seed E2E users

bun run seed:e2e-users

Provisions test.user1@test-kb-aish.co.uk (admin), test.user2@test-kb-aish.co.uk (editor), and test.user3@test-kb-aish.co.uk (viewer) via supabase.auth.admin.createUser() — the same path GoTrue uses internally, so the resulting auth.users rows have the correct shape. The script is idempotent.

Two flag variants are available:

bun run seed:e2e-users --check       # verify-only, exits 1 on mismatch
bun run seed:e2e-users --dry-run     # preview without writing

Use --check after the rebuild as a smoke test. For full background and the manual dashboard fallback, see e2e-test-setup.md §11.

---

7. Step 5 — Verify the corrective migration is not needed

Why this step exists

S156 shipped a corrective migration (20260408134124_fix_pipeline_service_account_auth_shape.sql) that normalises 8 NULL token columns on the pipeline service account row and backfills the missing auth.identities row. That migration already ran against production. But snapshot-cloned environments — any project forked from a pre-S156 snapshot, including older local dev databases and any Supabase preview branches cut before 2026-04-08 — may still carry the broken row. If auth.admin.listUsers() or getUserById() ever touches it, GoTrue 500s and the Team Members UI goes dark. This step gives you two ways to detect that state before it bites.

Option A — SQL probe (Supabase dashboard or MCP execute_sql)

Run the read-only probe against the rebuilt project. On a clean rebuild it should report token_null=false and identities=1.

-- Read-only query — run via Supabase dashboard SQL editor or MCP execute_sql.
SELECT
  current_database(),
  email_change_token_new IS NULL AS token_null,
  (SELECT COUNT(*) FROM auth.identities i WHERE i.user_id = u.id) AS identities
FROM auth.users u
WHERE u.id = 'a0000000-0000-4000-8000-000000000001';
-- Expected on a clean rebuild: token_null=false, identities=1

Interpretation:

token_null	identities	Meaning	Fix
false	1	Healthy. Nothing to do.	—
true	any	8 GoTrue token columns are NULL — this is the exact S156 bad shape.	Apply the corrective migration (see “How to fix” below).
any	0	auth.identities row is missing. GoTrue getUserById still returns the user, but some downstream flows expect the identities row to exist.	Apply the corrective migration.
no rows	n/a	The pipeline service account row is missing entirely — the original migration has not been applied.	Run supabase db push to apply 20260406180000_create_pipeline_service_account.sql.

Option B — bun run seed:e2e-users --check

The seed script runs the S156 pre-flight probe on every invocation (not just --check — also --dry-run and normal seed mode). It talks to the GoTrue admin HTTP API, which bypasses PostgREST’s exposed-schema restriction so it does not need a SQL console or MCP tool to reach auth.users. Exit codes:

Exit code	Meaning	Operator action
0	Probe passed. Pipeline service account is healthy, E2E users are present and correctly roled.	Proceed to Step 6.
2	S156 pre-flight detected a broken or missing pipeline service account row.	Apply the corrective migration (see “How to fix” below). Re-run the script after applying.
1	Generic error — missing env var, DB unreachable, probe query itself failed, createUser() failed, role upsert failed, etc.	Inspect the error message. This is not an S156 drift signal; the probe never ran.

Note on --dry-run mode: --dry-run promises no side effects, which includes not exiting non-zero on recoverable drift. If the probe detects an S156 bad row during a --dry-run, the script reports the finding and keeps going. Re-run without --dry-run to get the exit code 2.

How to fix

If either probe reports token_null=true, identities=0, or the seed script exits with code 2, apply the S156 corrective migration:

# Run with dangerouslyDisableSandbox: true — see CLAUDE.md →
# "Gotchas → Supabase → CLI in Claude Code sandbox" for why the CLI
# cannot run inside the Claude Code sandbox.
/opt/homebrew/bin/supabase db push

db push picks up any unapplied migrations, including 20260408134124_fix_pipeline_service_account_auth_shape.sql. The corrective migration is idempotent — its UPDATE COALESCEs NULL token columns to '', and its identities INSERT uses ON CONFLICT DO NOTHING. Safe to re-run.

After the fix, re-run the SQL probe or bun run seed:e2e-users --check and confirm the healthy outcome before proceeding.

If the row is missing entirely

If the SQL probe returns zero rows, or the seed script reports pipeline service account row (...) does not exist, the original migration has not been applied. Run supabase db push (with sandbox bypass) to apply 20260406180000_create_pipeline_service_account.sql and every following migration. A fresh db reset && db push produces the correct shape from scratch — see Steps 1-2.

---

8. Step 6 — Seed application data

This step varies by scenario.

For demo DB

The demo bootstrap loads a curated content set, sample bids, and example intelligence runs so the demo DB feels populated rather than empty. The full seed-script list and ordering lives in the demo bootstrap spec — follow that document. Do not enumerate the seed scripts here; the demo bootstrap workflow is deferred and may shift before the first real rollout.

For Phew re-ingest

For comprehensive re-ingestion into a fresh project, use the dedicated operational documents instead of this section:

• Restore matrix: docs/operations/blank-db-restore-matrix.md — classifies every table and entry point by restore category with FK-ordered restore steps.
• Two-stage runbook: docs/operations/two-stage-re-ingestion-runbook.md — step-by-step from Stage 0 (snapshot) through Stage 2 (conditional client markdown), with quality decision gates.
• Guide regeneration: docs/operations/guide-regeneration-prompts.md — reconstructed prompts for MCP-created guides.

For a quick single-step markdown re-ingest (without full quality protocol):

python3 scripts/ingest_markdown.py /path/to/phew-markdown-dir

Useful flags: --dry-run (preview without writing), --skip-existing (only ingest new documents), --tag phew-2026-04-rebuild (label the batch), --author <name> (set author on ingested records). Run with --dry-run first to verify the file list before committing.

For disaster recovery

No application data seed is required at this stage — the data was lost. Coordinate with Liam on whether to restore from a snapshot before re-ingesting from sources.

For local dev reset

No seeding required. Local dev runs against an empty schema by design.

---

9. Step 7 — Smoke test

After every rebuild, walk through this checklist in a browser pointed at the rebuilt environment:

• Sign in at /login as test.user1@test-kb-aish.co.uk — login succeeds and redirects to the dashboard.
• /settings → Team Members: three test users (admin/editor/viewer) appear, no pipeline service account is visible. The pipeline UUID (a0000000-0000-4000-8000-000000000001) is filtered out of the API response by design — if you see it, the filter has regressed.
• /dashboard loads without errors. Pipeline-runs tile shows “no runs yet” (expected on a freshly rebuilt DB).
• /content-owners renders even if empty (no rows, no error banners).
• Optional — if Sector Intelligence is enabled: /intelligence/workspaces loads. Empty state is fine; a 500 is not.

If any step fails, jump to Section 10 (Troubleshooting) before continuing.

---

10. Troubleshooting

Missing env vars

• Symptoms: seed:e2e-users exits with Missing NEXT_PUBLIC_SUPABASE_URL/SUPABASE_URL or SUPABASE_SECRET_KEY, or supabase db push complains about a missing password.
• Fix: Confirm .env/.env.local are populated and sourced into your shell. echo $SUPABASE_DB_PASSWORD should return a value; if not, set -a; source .env; set +a and retry.

Expired anon key

• Symptoms: Browser smoke tests return Invalid JWT or JWS signature verification failed from every API call after sign-in.
• Fix: Rotate the anon key in the Supabase dashboard, update NEXT_PUBLIC_SUPABASE_ANON_KEY in .env.local, restart bun dev.

RLS denying access

• Symptoms: Every page returns 403 after a successful login, or Team Members shows “No team members found” despite the test users existing.

• Fix: Confirm public.user_roles has the expected three rows:

  SELECT u.email, r.role
  FROM public.user_roles r
  JOIN auth.users u ON u.id = r.user_id
  WHERE u.email LIKE 'test.user%@test-kb-aish.co.uk'
  ORDER BY u.email;

  If the query returns fewer than three rows, re-run bun run seed:e2e-users.

Missing user_roles entry

• Symptoms: seed:e2e-users reports success but the user cannot access anything after login.

• Fix: The handle_new_user trigger may not be firing. Verify it exists:

  SELECT tgname FROM pg_trigger
  WHERE tgrelid = 'auth.users'::regclass
    AND tgname LIKE '%handle_new_user%';

  If the trigger is missing, the migrations did not fully apply — re-run supabase db push (with sandbox bypass) and check for errors.

S156 bad row on a snapshot clone

• Symptoms: /api/admin/users returns HTTP 500 with body Database error finding users. Team Members shows “No team members found” despite the test users existing in auth.users.

• Fix: The project carries the pre-S156 broken row for the pipeline service account. Apply the corrective migration:

  # Run with dangerouslyDisableSandbox: true
  /opt/homebrew/bin/supabase db push

  The 20260408134124_fix_pipeline_service_account_auth_shape.sql migration normalises the NULL token columns and backfills the missing auth.identities row. Re-run the probe in Section 7 to confirm.

Sandbox errors on CLI commands

• Symptoms: supabase db push / db reset / gen types fail with Operation not permitted or a network connection error pointing at aws-1-eu-west-2.pooler.supabase.com.
• Fix: Re-run with dangerouslyDisableSandbox: true — the standard CLAUDE.md gotcha for the Supabase CLI in the Claude Code sandbox.

---

11. Cleanup

When a demo DB is no longer needed (pilot ended, demo wrapped, client churned), nuke the entire project rather than leaving it costing money indefinitely:

# Run with dangerouslyDisableSandbox: true
/opt/homebrew/bin/supabase projects delete <project-ref>

Warning: Project deletion is irreversible. Once the command returns, the database, storage buckets, edge functions, and project URL are gone permanently. Triple-check the project ref before pressing return.

Orphaned resources to clean up separately

Project deletion handles the database, storage objects, and edge functions. A few peripheral things do not sweep up on their own: external backups / S3 mirrors, CDN caches pointing at deleted edge functions, custom domain mappings, and any references to the deleted project URL in .env files on developer machines. Clean each up by hand after the delete completes.

---

12. Quality gate

Before cutting over from the old project to the new one (Section 9) — and certainly before running the irreversible delete in Section 11 — run the re-ingestion quality protocol against a paired pre/post snapshot. See re-ingestion-quality-protocol.md for the six-step protocol (snapshot, re-ingest, compare, report, review) plus the embedding smoke test, DOCX pre-processing checklist, and source-document inventory template. The protocol is deliberately kept as a separate document so it can be invoked independently of a full rebuild, for example after a format or taxonomy change that only touches a subset of the corpus.

---

Cross-references

• e2e-test-setup.md §11 — E2E user seeding flow (seed:e2e-users, manual fallback, idempotency notes).
• CLAUDE.md → “Gotchas → Supabase → CLI in Claude Code sandbox” — the authoritative explanation of why every CLI command in this runbook needs dangerouslyDisableSandbox: true.
• demo-bootstrap-spec.md — demo DB seed data spec (deferred; consult before running Section 8 for a demo rebuild).
• re-ingestion-quality-protocol.md — quality gate for any re-ingestion; see §12 above.

---

Last verified: NOT YET — runbook is draft until first end-to-end rehearsal against a Supabase preview branch.