Fixing Supabase Migration Conflicts: The 'History Table' Mismatch

"It Works Locally, Why Not Deploy?"

I added a nickname column to the profiles table for a new feature. I generated the migration file locally using supabase db diff and verified it worked.

Confidently, I ran the deploy command: supabase db push

But the terminal spat out a red error:

Error: migration 20251212000000_add_nickname.sql mismatch
Remote history is different from local history.

"Wait, remote history is different? I just wrote this!" It turned out a teammate (or past me) had touched the server DB directly.

What Confused Me Initially? (Thought it was Git)

I thought migration conflicts were like Git Merge Conflicts. Can't I just merge the SQL files like code?

No. Databases have State. In Git, if code conflicts, you rewrite it. In DB, ALTER TABLE must run in strict order. If order is messed up, data is lost or tables break.

Supabase (Postgres) keeps a strict record of "List of executed migration files" in the supabase_migrations table. If this History differs from my local file list by even one bit, it refuses deployment for safety.

The 'Aha!' Moment (Blockchain Analogy)

I understood it when comparing it to a "Blockchain Ledger."

• Migration History: An immutable ledger. Once written, it's set.
• Local Migration Files: New transactions I want to add.

The Server Ledger says [A -> B -> C]. My Local Files say [A -> B -> D]. The server says, "Hey, you skipped C! Or did you tamper with the ledger? Transaction rejected!"

This happens because I ignored C (remote change) and tried to push D, or modified C locally.

The Fix: Repair and Manual Sync

Scenario: Server has 2025..._team_change.sql, but my local doesn't.

Method 1: Pull Remote Changes (Standard)

The standard way is to sync local with remote.

supabase db pull

This brings schema changes from the server to my local DB. Then run diff again, and only my changes will be generated into a new file.

Method 2: Manual History Repair (Repair)

What if "Does the server change was a mistake? I want to overwrite it with mine!" (e.g., Development environment).

Use migration repair. This asks the server to force-edit the ledger.

# Mark a specific version as 'applied' on server (Skip actual SQL execution)
supabase migration repair --status applied 20251212000000

Conversely, if it says applied but wasn't:

# Delete a specific version from server history (reverted)
supabase migration repair --status reverted 20251212000000

Use this to align local and remote "Version History", then push.

Deep Dive: Safe Team Workflow

Developing alone? db push is fine. But teams need rules.

1. Branching Database: Supabase offers Branching. Each Git branch gets a separate DB instance.
2. No Dashboard Edits: This is critical.
   • NEVER edit tables in the Supabase Web Dashboard.
   • ONLY modify via migrations files.
   • Dashboard edits cause "Drift" (uncharted changes) which leads to pain later.

Recommended Workflow:

1. Local supabase start.
2. Edit tables in Local Dashboard (localhost:54323).
3. supabase db diff -f add_users -> Generate migration file.
4. Git Commit & Push.
5. CI/CD (GitHub Actions) runs supabase db push.

6. Deep Dive: Down Migration Strategy

What if you deploy a migration and it breaks production? You need to undo it. In Git, git revert works. In DB, you can't just delete the file. You must run an Inverse Migration. If you added a table, you must DROP it.

Supabase flows are typically "Forward Only". To rollback, you actually create a New Migration that undoes the previous one, and push that forward.

The best rollback is No Rollback. Use the Expand and Contract pattern.

7. Case Study: The Renaming Disaster (Expand and Contract)

I wanted to rename username to email. I ran ALTER TABLE users RENAME COLUMN username TO email.

Result: Downtime. My API servers (running the old code for a few minutes during deploy) tried to SELECT username. Postgres said "Column does not exist". 500 Error.

The Fix: 4-Phase Deploy

1. Expand: Add email column. Keep username. Write to both triggers.
2. Migrate: Copy data from username -> email.
3. Shift: Update API code to use email. Deploy.
4. Contract: Drop username column.

This is Zero Downtime Migration. Database changes must happen SLOWER than Code changes.

9. Deep Dive: Environment Management (Staging vs Prod)

"It works on Staging, fails on Prod." This happens when schemas drift apart.

Supabase CLI relies on the linked Project ID. NEVER link your local CLI to Production directly (supabase link). One wrong db reset and you are fired.

Use GitHub Actions to switch targets dynamically:

- run: supabase db push --project-ref $PROJECT_ID
  env:
    PROJECT_ID: ${{ github.ref == 'refs/heads/main' && secrets.PROD_ID || secrets.STAGING_ID }}

develop branch -> Staging DB. main branch -> Production DB. Keep your local strictly for local development.

10. Case Study: Managing Seed Data

Migrations handle "Structure". What about "Data" (e.g., List of Countries, Default Categories)?

seed.sql is great but only runs on db reset (fresh start). To add data to a live database, use Data Migrations.

-- 20251214_add_countries.sql
INSERT INTO countries (code, name) VALUES ('KR', 'South Korea')
ON CONFLICT (code) DO NOTHING;

Always use ON CONFLICT DO NOTHING or DO UPDATE to make your data migration idempotent. Running it twice shouldn't break anything.

11. Troubleshooting: Lock Timeouts & Deadlocks

Ever seen a migration hang forever? It's likely a Locking Issue.

ALTER TABLE requires an Access Exclusive Lock. It waits until all other queries on that table finish. If you have a long-running report query running, your deployment will freeze.

The Fix:

1. Set Lock Timeout:

   SET lock_timeout = '2s'; -- Fail fast if lock not acquired in 2s
   ALTER TABLE orders ADD COLUMN status TEXT;
   

2. Deploy Off-peak: Do not run migrations during Black Friday sales.
3. Kill Blocking Queries: Check pg_stat_activity and terminate stuck sessions.

Safety first. Don't let a deploy bring down your production DB.

12. Application: CI/CD Setup

Automating deploy with GitHub Actions prevents conflicts.

name: Deploy Migrations

on:
  push:
    branches:
      - main

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: supabase/setup-cli@v1
      - run: supabase db push
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
          SUPABASE_DB_PASSWORD: ${{ secrets.SUPABASE_DB_PASSWORD }}
          SUPABASE_PROJECT_ID: ${{ secrets.SUPABASE_PROJECT_ID }}

Now, as long as code is merged to main, the DB stays up to date.

One-Line Summary

Migration conflicts are 'Ledger Mismatches'. Never edit schemas in the Web Dashboard. Use local db diff to generate files and manage them with Git. Use db pull or migration repair to fix the ledger.