Skip to main content

Knowledge > Runbooks > Technical Ops > Run a Database Schema Migration

Run a Database Schema Migration

Apply a schema change to the shared Supabase production database safely and repeatably.

Prerequisites​

  • Access to the Supabase SQL editor OR the mcp__plugin_supabase_supabase__execute_sql MCP tool
  • Migration SQL written and reviewed
  • Founder approval if the migration includes any DROP, large UPDATE, or column removal
  • Feature branch checked out in the relevant codebase

Steps​

  1. Create the migration file

    Name the file using the pattern YYYYMMDD_short_description.sql and place it in the shared migrations directory:

    pewsearch/migrations/20260325_add_index_churches_slug.sql

  2. Write safe SQL

    -- 20260325_add_index_churches_slug.sql
    -- Purpose: Speed up church detail page lookups by slug

    -- NOTE: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
    -- Run the index creation as a standalone statement OUTSIDE BEGIN/COMMIT.
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_churches_slug
      ON churches (slug)
      WHERE directory_visible = true;

    -- Verify: check index exists
    SELECT indexname FROM pg_indexes WHERE tablename = 'churches' AND indexname = 'idx_churches_slug';

    If you have multiple DDL changes (column additions, constraints, etc.), wrap them in BEGIN; ... COMMIT; — but keep any CREATE INDEX CONCURRENTLY statements outside that block.

    Rules for safe SQL:

    • Use IF NOT EXISTS and IF EXISTS guards
    • CREATE INDEX CONCURRENTLY cannot run inside a transaction block — run it as a standalone statement
    • Never use plain CREATE INDEX on large tables (blocks reads) — always use CONCURRENTLY
    • Never use DROP TABLE, TRUNCATE, or bulk DELETE without founder approval

  3. Test the query logic on a non-critical table first

    Before running on churches or unified_rag_content, run a SELECT version of your logic to confirm it targets the right rows:

    SELECT count(*) FROM churches WHERE directory_visible = true LIMIT 10;

  4. Get founder approval if required

    Require founder approval before proceeding if the migration includes:

    • Any DROP COLUMN, DROP TABLE, or DROP INDEX
    • Any UPDATE affecting more than ~10K rows
    • Any change to unified_rag_content (327K irreplaceable rows)
    • Any change to RLS policies or auth-related tables

  5. Run the migration

    Option A — Supabase MCP tool:

    mcp__plugin_supabase_supabase__execute_sql
    project_id: wrwkszmobuhvcfjipasi
    query: [paste migration SQL]

    Option B — Supabase SQL editor:

    • Go to https://supabase.com/dashboard/project/wrwkszmobuhvcfjipasi/sql
    • Paste the SQL and run

  6. Verify the migration applied

    Run a verification query to confirm the change is in place:

    -- Example: confirm index exists
    SELECT indexname, tablename FROM pg_indexes
    WHERE indexname = 'idx_churches_slug';

    -- Example: confirm new column exists
    SELECT column_name, data_type FROM information_schema.columns
    WHERE table_name = 'churches' AND column_name = 'new_column';

  7. Update knowledge docs if schema referenced

    Search C:\dev\knowledge\ for any document that references the changed table or column. Update the relevant doc in the same commit as the migration file.

  8. Commit the migration file to version control

    git add pewsearch/migrations/20260325_add_index_churches_slug.sql
    git commit -m "feat: add index on churches.slug for directory_visible rows"

Verification​

  • Query returns expected results without errors
  • Application routes that use the changed schema return correct data
  • No new errors in ops_error_reports after deploying dependent code

Rollback​

Write a rollback migration file (YYYYMMDD_rollback_description.sql) that reverses the change:

DROP INDEX CONCURRENTLY IF EXISTS idx_churches_slug;

Run it through the same process. Never reverse a migration by editing the original file.

See Also​