Troubleshooting: Local migration tests don't catch CI failures¶

Problem¶

CI ran migrations from scratch on a truly empty database and failed because a migration referenced a table (collection) that only existed on dev databases with prior state. make test-migrations passed locally because supabase db reset preserved leftover tables.

Environment¶

Stack: FastAPI + SQLAlchemy + Alembic + Supabase PostgreSQL
Date: 2026-02-24

Symptoms¶

CI Postgres job fails: relation "collection" does not exist
make test-migrations passes locally
Migration works on any database that previously had the collection table

What Didn't Work¶

Direct solution: Root cause was identified from CI logs on first investigation.

Solution¶

Two fixes:

1. Guard references to tables that may not exist on fresh DBs:

# Before (broken on fresh DB):
op.execute("""
    UPDATE plates p SET ... FROM collection c WHERE ...
""")

# After (safe):
op.execute("""
    DO $$ BEGIN
        IF EXISTS (SELECT 1 FROM information_schema.tables
                   WHERE table_schema='public' AND table_name='collection')
        THEN UPDATE plates p SET ... FROM collection c WHERE ...;
        END IF;
    END $$;
""")

2. Make make test-migrations match CI exactly: - Drop & recreate public schema (truly empty, no leftovers from supabase db reset) - Idempotent re-run (upgrade head twice) - Resilience check (downgrade 2 + re-upgrade) - Schema check

Bonus fix found during resilience testing: Downgrade path also failed because plates_with_current_valuation view blocked column drops. Must drop view before altering plates columns.

Why This Works¶

supabase db reset resets the DB but may preserve schema artifacts from Supabase's internal state or prior migrations. CI's supabase start on a fresh container gives a truly empty public schema. Dropping and recreating the schema locally simulates this.

The resilience check (downgrade + re-upgrade) catches view dependency issues that only surface when running migrations in both directions.

Prevention¶

Always run make test-migrations (now includes all 5 CI-matching steps)
When a migration references a table from a previous era (not created by the migration chain), wrap in IF EXISTS
When altering plates columns, drop plates_with_current_valuation view first, recreate after
Both rules are now in CLAUDE.md under "Migration requirements"

No related issues documented yet.