Staging Refresh Runbook

Staging Refresh Runbook

Status: Stub (S4 close-out). Full handover-grade expansion in Wave 5 (WP-G3.4 / WP-G6.x). Owner: prod-readiness track. Audience: Liam + future client-onboarding operator. Pair with: docs/audits/kh-production-readiness-phase-1/research/10-migration-repair-investigation.md §8 (“Pattern guidance for future client deployments”).

---

§1. Purpose

Document the procedure to reset a persistent staging Supabase branch and re-seed it to a known-good state.

Which staging? The platform runs a four-DB topology (see reference/platform-context.md): Platform staging rbwqewalexrzgxtvcqrh (PLATFORM_PROJECT_REF — the local-dev + CI target since the staging-first cutover; a branch of Platform prod zjqbrdctesqvouboziae) and, per client, a persistent staging branch of the client’s prod project (current client: turayklvaunphgbgscat, STAGING_PROJECT_REF). The procedures below were written against the client staging branch and apply to any persistent protected branch; substitute the target ref. After any push to a non-default target, re-link back to rbwqewalexrzgxtvcqrh (cat supabase/.temp/project-ref before every push — drift hazard).

§2. When to refresh

• After any migration that touches existing data (review carefully — data-bearing migrations should live in seed.sql, not migrations).
• When staging accumulates test data that no longer reflects current prod schema/state.
• Monthly cadence (TBC W5) for routine drift correction.
• Emergency: when staging is in a stuck or corrupted state.

§3. Data architecture (S4 baseline)

Data class	Location	Loads when
Schema (tables, indexes, RLS, functions, triggers, constraints)	supabase/migrations/	At branch creation, sequential.
Application baseline (cross-client)	supabase/seed.sql	Once at branch creation, after migrations. Re-runs only on full branch reset.
Test users + their user_roles	scripts/seed-e2e-users.ts	Post-reset, manual run with staging service-role key.
Per-client baseline (e.g. for the current deployment: Phew Product Guides, Sector Guides, taxonomy customisations, MAT Auditing Guide)	TBD W5 — pattern likely supabase/seed.<client>.sql extension files OR runtime app-bootstrap script.	Per-client onboarding step.
Real client content (bid Q&A, content_items, company profiles, intelligence articles)	NOT in repo. Loaded via ingestion scripts (scripts/import_bid_library.py, scripts/ingest_markdown.py) OR pg_dump from a populated reference.	Per-client onboarding. Out of band.
PII / personal data	NEVER in seed.sql (committed to git). PII enters only via per-client population path with explicit consent + scrub policy per research/05 §6.7.	Per-client onboarding.

§4. Reset procedure

4.1 Pre-flight

• Confirm the working branch is up-to-date with main (git status clean). (Historical: this step referenced the retired production-readiness track branch.)
• Verify all 31 (or current count) migration files present locally: ls supabase/migrations/ | wc -l → matches mcp__supabase__list_migrations{project_id:"<prod-project-id>"} count.
• Capture pre-reset migration count on staging: mcp__supabase__list_migrations{project_id:"<staging-branch-project-ref>"} → log to STATUS.

4.2 Reset

Canonical mechanism (post-S4 empirical correction):

/opt/homebrew/bin/supabase link --project-ref turayklvaunphgbgscat
/opt/homebrew/bin/supabase db push --linked

supabase db push --linked applies any local migrations not yet present on the linked staging branch. Use this for routine drift correction and migration catch-up — it is the only reliable mechanism for protected branches (see §6 recovery pattern).

Why NOT mcp__supabase__reset_branch — discovered S4 empirically: on protected persistent branches (which staging is, per its persistence mode), mcp__supabase__reset_branch is a no-op. Branch-action logs record Skipping configuration for protected branch + Skipping seed data for protected branch; the destructive re-apply step never fires. The operation reports success but staging state is unchanged. This was the root cause of S4 WP-G3.1’ having to take a 12-migration db push recovery path after the initial reset returned “success” without effect.

If a true destructive re-apply is needed (rare — only after deliberate schema rollback), unprotect the branch via the Supabase dashboard FIRST, then run mcp__supabase__reset_branch, then re-protect. Default path is db push --linked.

Wait for branch status FUNCTIONS_DEPLOYED via mcp__supabase__list_branches.

4.3 Post-reset sequence

1. Apply the auth hook on staging (for the current deployment, the Phew domain hook — per WP-ENV.1 spec §C.1):

   • Supabase dashboard → Branches → staging → Authentication → Hooks
   • Enable “Before User Created”
   • URI: pg-functions://postgres/public/<hook-function-name> (current deployment: hook_restrict_signup_to_phew_domain)
   • Note: config.toml [remotes.staging.auth.hook.before_user_created] captures this as code for replay durability, but the dashboard toggle is required for first activation.

2. Seed test users via the canonical script:

   SUPABASE_URL="<staging-branch-url>" \
   SUPABASE_SERVICE_ROLE_KEY="<staging-service-role>" \
   bun run scripts/seed-e2e-users.ts

   Creates TEST_USER_1/2/3 in auth.users + their user_roles (admin/editor/viewer). The pipeline service account (a0000000-0000-4000-8000-000000000001) is created by migration 20260416122127 (belt-and-suspenders also in seed.sql).

   Scope narrowed at the DR-096 cutover (S500): the CI E2E lanes no longer read these staging users — both lanes seed their own users per-run onto a runner-local stack (seed:e2e-users against 127.0.0.1:54321). Staging test users now serve the integration lane (ci.yml Integration tests + integration-nightly.yml, still staging-targeted by design) and manual testing only.

3. Reference data needs no separate step. supabase/seed.sql §4 seeds the core ontology (taxonomy_domains, taxonomy_subtopics, taxonomy_sync_state, layer_vocabulary, entity_aliases) directly as part of the reset, so a fresh/reset DB already carries the client-agnostic ontology CI depends on. form_requirement_templates is populated by ingest, not by seed.

   The reference-refresh workflow that used to run here is RETIRED — see §4.4.

4. Verify:

   • SELECT count(*) FROM auth.users → 4 (pipeline + 3 test users)
   • SELECT user_id, role FROM user_roles ORDER BY role → 4 rows
   • SELECT count(*) FROM taxonomy_domains → > 0 (reference data present)
   • Core application_types ontology — SELECT count(*) FROM application_types → 6 (procurement / intelligence / sales_proposal / product_guide / competitor_research / training_onboarding, each with a PLURAL label_plural + non-null description). A fresh branch where the 20260617130000 squash baseline ran but seed.sql did NOT (or ran the pre-S408 single-row §2·0) shows 1 row with a singular Procurement label → the /workspaces launcher renders no cards and e2e/tests/workspaces.spec.ts @smoke fails. Fix: supabase db push --include-seed --linked — seed.sql §2·0 now seeds all 6 (idempotent DO UPDATE, so it also self-corrects a stale singular label). [S408]
   • anon-EXECUTE parity, BOTH schemas — exactly 2 functions may carry anon EXECUTE across api + public: api.set_config and public.set_config (ID-115 INV-20 / DR-035). This matches what check-api-view-coverage.ts INV-20 gates on in CI; the older api-only form of this check passed while public drifted, so run it per-schema:

     SELECT n.nspname, count(*)
       FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE n.nspname IN ('api','public')
        AND has_function_privilege('anon', p.oid, 'EXECUTE')
      GROUP BY 1 ORDER BY 1;
     -- expect exactly two rows: api|1 and public|1, both `set_config`

     A branch on which ONLY the squash baseline has run shows ~72/73 api RPCs anon-EXECUTE — the baseline omits the ID-115 function-ACL REVOKEs. This is an intermediate state, not a standing gap: supabase db push --linked applies 20260624120000_id115_api_schema_anon_revoke and the born-locked trigger from 20260707190500_id61_dr035_default_privileges, and a complete from-zero replay lands the invariant unaided (S493 — no manual ACL sweep needed; DR-049’s mandatory-sweep step is superseded by that evidence, formal retirement pending the decision-register review). Verify here as a spot-check that the chain fully applied. Do NOT substitute the dashboard’s “X of 179 functions exposed” tile for this query. That tile counts anon-accessible objects, not the invariant: PostgREST filters its OpenAPI document by the requesting role’s grants, so post-id-347 (0 anon table grants) it renders near-empty on a correct project — Platform staging and a healthy branch return byte-identical output. It cannot distinguish a correct project from a broken one. [S408, corrected S493/S494]
   • Hit /api/health on the latest preview deploy → 200

5. Verify user_profiles mirror parity (catches backfill skew, dashboard-edit drift, or trigger failure on ongoing signups — see docs/audits/kh-production-readiness-phase-1/specs/wp-g3.4-user-profiles-spec-v1.md):

   SUPABASE_URL="<staging-branch-url>" \
   SUPABASE_SERVICE_ROLE_KEY="<staging-service-role>" \
   bun run scripts/verify-user-profiles-parity.ts --env=staging

   Exit codes (per spec §5.2): 0 = pass; 1 = count mismatch or column drift (re-run supabase db push --linked to absorb idempotent backfill, then re-run probe); 2 = query failure (probe could not run — typically the count_auth_users() RPC is missing because the WP-G3.4 migration has not been applied to this branch).

   This step MUST run AFTER step 2 (seed-e2e-users.ts) so the test users exist before the probe samples; it MUST run BEFORE step 6 (Vercel redeploy) so a mirror-drift failure cannot be masked by a Vercel-side issue surfacing later.

6. Trigger a Vercel preview redeploy (per WP-ENV.1 §B.5) to verify the redeploy now sees the green staging.

4.4 Reference data refresh (WP-CI.RES.7) — retired

⚠️ RETIRED (S529). .github/workflows/staging-reference-refresh.yml and scripts/staging-reference-refresh.sh are DELETED. Reference data now comes from supabase/seed.sql §4, which seeds the core ontology directly on reset.

Why it went, measured on the live DBs rather than argued:

• It copied nothing. Platform prod and staging held the same reference rows (taxonomy_domains 7/7, taxonomy_subtopics 34/34, taxonomy_sync_state 1/1, layer_vocabulary 4/4) — everything seed.sql §4 already seeds. Prod carried fewer entity_aliases (14) than staging (15).
• It destroyed data. The weekly DELETE step wiped that staging-only alias row, and every other staging-only reference row, each Monday.
• It reported green regardless. The DELETE loop swallowed every error with || true, and the verify step only spot-checked four tables that seed.sql §4 repopulates anyway.
• Its sixth table never existed. public.template_requirements appears in no migration in this schema’s history. The table is public.form_requirement_templates (renamed from form_template_requirements at 20260712062000_id145_w1c_rename_reshape.sql:115), and it holds 0 rows in both prod and staging — so even spelled correctly it would copy nothing.

The historical exclusion rationale (tables skipped because production data references prod-only user UUIDs and fails FK checks on restore) is preserved in git history alongside the deleted script; it constrains nothing now.

4.4-legacy: Live mirror refresh (retired)

⚠️ RETIRED. The full live-mirror approach (staging-live-mirror.yml / scripts/staging-mirror-and-scrub.ts) was retired in the WP-CI.RES.7 architectural pivot. The --disable-triggers + PGOPTIONS approach for auth.* tables was blocked by Supabase’s SUSET gate (session_replication_role). The reference-refresh approach (§4.4 above) replaces it by only refreshing reference/lookup tables that have no auth FK dependencies.

§5. Per-client seeding

Knowledge Hub is one Supabase project per client (CLAUDE.md “Supabase & Schema” bullet 5). The “per-client baseline” is the content that distinguishes one client deployment from another (taxonomy customisation, domain-specific guides, sector-specific templates). This is distinct from both the application baseline (cross-client; lives in supabase/seed.sql) and real client content (PII-bearing; never committed). This section covers the two patterns for shipping per-client baseline cleanly.

§5.1 Per-client baseline data architecture

Per-client baseline content sits between the cross-client seed.sql (which runs once at branch creation) and the runtime ingestion pipelines (which load real client content out of band). For the current deployment (Phew) the baseline includes Product Guides, Sector Guides, the MAT Auditing Guide, and client-specific taxonomy customisations. The two patterns below differ on content shape: small, static, schema-shaped data fits Pattern A; large or API-fetching content fits Pattern B.

§5.2 Pattern A — supabase/seed.<client>.sql extension

When to use: per-client baseline that is small, static, and schema-shaped — e.g. taxonomy domain rows, default guide skeletons, client-specific configuration rows (pre-seeded client_features flags).

How to wire: create supabase/seed.<client>.sql (e.g. supabase/seed.phew.sql) and either (a) extend the canonical supabase/seed.sql with a \ir seed.<client>.sql directive when running supabase db reset locally, or (b) move the extension into Supabase dashboard under the [remotes.<branch>.db.seed] config block in supabase/config.toml. Per-client extensions inherit the same idempotency contract as seed.sql itself: INSERT … ON CONFLICT DO NOTHING for any data that may already exist. PII MUST NOT appear in any committed seed file (CLAUDE.md “Schema” bullet — single source of truth lives in lib/validation/schemas.ts).

§5.3 Pattern B — runtime bootstrap script

When to use: per-client baseline that is large, requires API calls (e.g. content ingestion via Firecrawl), depends on external secrets, or is naturally produced by an existing pipeline (scripts/ingest_markdown.py, scripts/import_bid_library.py).

How to wire: ship scripts/bootstrap-<client>-data.ts (or a generic scripts/bootstrap-client-data.ts parameterised by --client=<name>), expose --env/--dry-run flags per wp-s5.3-d21-flag-rollout-spec.md §7.1 template, and document invocation in docs/runbooks/local-development.md. The script reads its API keys + service role from env vars, runs against the target Supabase URL, and is idempotent (every insert uses ON CONFLICT semantics or pre-existence checks).

§5.4 First concrete instance — Phew

For the current deployment, the Phew-specific baseline (Product Guides, Sector Guides, taxonomy customisations, MAT Auditing Guide) is currently NOT in the repo — it was loaded out-of-band via ingestion at the original cutover. When the persistent staging branch was provisioned in S3, the Phew baseline was NOT re-seeded; staging intentionally starts data-empty per feedback_supabase_branch_data_empty.md. The Phew baseline becomes a Pattern B candidate when (a) onboarding a second client demands repeatable per-client provisioning, OR (b) a staging refresh needs Phew content for E2E testing of content-dependent flows.

§5.5 Order of operations

The full per-client onboarding sequence (cross-references §4.3):

1. Branch reset (or fresh project provisioning).
2. Migrations apply (supabase db push --linked).
3. seed.sql runs (cross-client baseline).
4. (If Pattern A:) seed.<client>.sql runs.
5. seed-e2e-users.ts runs (test users + roles).
6. verify-user-profiles-parity.ts runs (validates the mirror trigger fired correctly during steps 2-5; catches backfill skew before any client-content path can mask a mirror-drift failure).
7. (If Pattern B:) bootstrap-<client>-data.ts runs.
8. Vercel preview redeploy.
9. Smoke-test /api/health + at least one client-flow E2E.

Critical ordering note: step 6 (parity probe) MUST run AFTER step 5 (test users seeded) so the probe has a non-empty sample but BEFORE step 7 (per-client content), so a per-client-content failure cannot mask a mirror-drift failure.

§5.6 Cross-references

• docs/runbooks/staging-refresh.md §3 (data architecture matrix).
• docs/runbooks/staging-refresh.md §4 (reset procedure).
• docs/runbooks/local-development.md (env + invocation patterns).
• docs/audits/kh-production-readiness-phase-1/specs/wp-g3.4-user-profiles-spec-v1.md (the mirror table + parity probe spec).
• feedback_supabase_branch_data_empty.md (why staging starts empty).

§6. Recovery patterns

6.1 Protected-branch reset returned “success” but nothing changed

Symptom: mcp__supabase__reset_branch completes without error; branch status returns to FUNCTIONS_DEPLOYED; but mcp__supabase__list_migrations shows the same set as before. No tables were re-created; no data re-seeded.

Cause: the branch is protected. Branch-action logs in the Supabase dashboard show:

Skipping configuration for protected branch
Skipping seed data for protected branch

Fix: use supabase db push --linked (per §4.2 canonical mechanism) for migration catch-up. If a destructive re-apply is genuinely required:

1. Supabase dashboard → Branches → staging → Settings → unprotect the branch.
2. Run mcp__supabase__reset_branch.
3. Verify the reset took effect via mcp__supabase__list_migrations.
4. Re-protect the branch.

Now graduated to CLAUDE.md Gotchas (kh-prod-readiness-S6). Source memory: feedback_protected_branch_reset_noop.md.

6.2 General reset failures

If reset itself fails for non-protection reasons: see research/10-migration-repair-investigation.md §8.5 (recovery patterns). Common path: read dashboard logs (Branches → staging → View Logs) — mcp__supabase__get_logs postgres feed may not surface migration errors reliably.

6.3 FK migration corrective story (S25 + S30)

The persistent staging branch (turayklvaunphgbgscat) carries two self-healing migrations that resolved cross-branch FK drift between auth.users and public.user_profiles:

• 20260503225703_migrate_auth_user_fks_to_user_profiles.sql — S25 W1 shipped the FK migration from auth.users (an Auth-owned table) to the user_profiles mirror, removing prod-staging UUID-mismatch failures across every FK-bearing public table.
• 20260504125137_backfill_user_profiles_before_fk_retry.sql — S25/S30 corrective. The first pass left a small set of FK-source rows whose mirror rows had not yet been backfilled at apply time; this corrective backfills them inside a guarded DO block before the FK retry. Idempotent — re-applies cleanly on any branch.
• 20260504225603_align_auth_user_indexes.sql — companion no-op ledger entry. Auth-owned indexes cannot be created from the Supabase migration role (production rejected with SQLSTATE 42501 “must be owner of table users”); the migration emits a NOTICE so production can advance past the version that originally attempted unsupported auth.users DDL. Staging-only index drift must be removed via an elevated Supabase/Auth-owner action, not duplicated to production for parity.

When refreshing the persistent staging branch via reference-refresh (§4.4) or a full pg_dump-based replay, these migrations re-apply cleanly. They are idempotent by construction and are safe to re-run on any branch state.

6.4 Persistent-branch refresh requires explicit pre-restore TRUNCATE

Persistent Supabase branches retain rows across refreshes — mcp__supabase__reset_branch is a no-op on protected branches (§6.1) and a pg_dump | pg_restore pipe without a pre-restore clear hits unique-constraint violations. The reference- refresh orchestrator (§4.4) handles this by issuing FK-aware DELETE FROM <table> per reference table before the restore pipe runs.

For a full-replay scenario (rare; only after deliberate schema rollback or when staging needs to mirror prod content shape), the safer path is a DO-block that enumerates pg_tables + truncates with CASCADE ahead of the COPY-mode restore. Direct pg_dump -Fp | psql is unsafe on PG 17.6 + Supavisor pooler: the \restrict meta-command + an aborted txn breaks COPY-mode mid-stream. Switch to pg_dump -Fc | pg_restore. Reference: feedback_persistent_branch_pre_restore_truncate, feedback_pg17_pgdump_psql_pooler_restrict.

§7. Cross-references

• research/10-migration-repair-investigation.md (root cause + fix rationale; this runbook is the operational counterpart).
• research/11-supabase-vercel-integration-sync-behaviour.md (Vercel env-var sync model — informs what survives a reset).
• research/05-staging-db-strategy.md (D-1 persistent-branch decision rationale).
• research/07-staging-preflight-resolutions.md §6.4 (data-empty branches finding).
• research/09-vercel-supabase-env-mapping-spec.md §8 + §C.1 (auth hook on staging).
• /runbooks/client-app-deploy/ §3A (client STAGING tier — the §4.3 parity checks here run against the client’s own staging Supabase, and the client staging pipeline’s run-record webhook model).
• /runbooks/per-client-release-pins/ §4A (per-client staging → prod promotion lane — staging is continuous on the shared staging branch and only prod carries a release/vX.Y.Z pin; the §4.3 parity checks gate the promotion).