Hosted Branching CLI rejects schema_migrations state that local CLI 2.95.2 accepts after `migration repair --status applied`

[melihzafer]

Summary

There is a behavioural divergence between local supabase db push --linked (CLI 2.95.2) and the hosted Supabase Branching CLI run by the Supabase GitHub App (slug supabase, client ID Iv1.b91a6d8eaa272168) on push to a production branch.

After fully canonicalizing supabase_migrations.schema_migrations on the remote project using supabase migration repair --linked --status applied <version> for every local migration version:

• supabase migration list --linked shows every row with Local + Remote matched.
• supabase db push --linked --dry-run prints Remote database is up to date.
• The schema_migrations table holds 136 rows, each with version, name, and a non-null statements array (5–36 statements per row depending on migration).

…the hosted check still fails with:

ERROR: duplicate key value violates unique constraint "schema_migrations_pkey" (SQLSTATE 23505)
Key (version)=(001) already exists.
At statement: 0
INSERT INTO supabase_migrations.schema_migrations(version, name, statements) VALUES($1, $2, $3)

i.e. the hosted CLI is attempting to INSERT a tracking row for migration version 001 despite the canonical row already existing — it is not skipping already-applied migrations the way the local CLI does.

Reproduction

1. Repo has 136 migration files in supabase/migrations/:
   • 81 in legacy format 001_*.sql … 081_*.sql
   • 55 in canonical timestamp format 20260301000000_*.sql … 20260364000000_*.sql
2. On the remote project, run supabase migration repair --linked --status applied <every version>.
3. Confirm locally:

   $ supabase migration list --linked
   # All rows show Local | Remote populated.
   $ supabase db push --linked --dry-run
   Remote database is up to date.

4. Push any commit to the production branch on GitHub.
5. The Supabase GitHub App's "Supabase Preview" check fails with the INSERT collision above.

Expected

Hosted CLI consults schema_migrations and skips already-applied versions exactly like local db push does. After migration repair --status applied, no further apply work should be needed.

Actual

Hosted CLI appears to either (a) bypass the "is this version already applied?" check, or (b) interpret the row's statements column or some other field differently than the local CLI. It then attempts a plain INSERT (no ON CONFLICT) for version = '001' and aborts on the duplicate-key error.

Evidence the local state is canonical

SELECT version, name, array_length(statements, 1) AS stmt_count
FROM supabase_migrations.schema_migrations
WHERE version = '001';
-- version: '001'
-- name:    'initial_schema'
-- stmt_count: 21

All 136 rows have name populated and statements as a non-null text[] of parsed SQL.

$ supabase migration list --linked
   Local          | Remote         | Time (UTC)
  ----------------|----------------|---------------------
   001            | 001            | 001
   …
   20260363000000 | 20260363000000 | 20260363000000
   20260364000000 | 20260364000000 | 20260364000000

$ supabase db push --linked --dry-run
DRY RUN: migrations will *not* be pushed to the database.
Remote database is up to date.

What we've tried

1. Manual SQL canonicalize. DELETE FROM schema_migrations followed by INSERT (version, name, statements) VALUES ('001', 'initial_schema', NULL), … for every local file. Hosted CLI failed with the same INSERT collision. (We suspected statements=NULL triggered re-apply.)
2. supabase migration repair --linked --status applied <version> for all 136 versions. This populated statements correctly. Local CLI reports in sync. Hosted CLI still fails with the same INSERT collision. ← current state
3. Empty commit re-push to retrigger the check. No change.

Prior to canonicalize, the same project was failing with Remote migration versions not found in local migrations directory — different error pattern from when the tracking table had legacy mixed-format rows (suffixed-only version='019_rls_policies' instead of canonical version='019', name='rls_policies'). The repair fixed that error but uncovered this second one.

Ask

1. Confirm the exact command sequence the GitHub App runs on push to a production branch (so we can reproduce locally).
2. Identify what about our schema_migrations content the hosted CLI considers "needs re-apply" despite migration repair having marked everything applied.
3. Provide a documented procedure for "I have manually rebuilt schema_migrations and need the hosted CLI to accept it as ground truth" — currently the local CLI honours migration repair while the hosted one does not.

If this is a known issue, a workaround we can apply repo-side (config flag, file content marker, etc.) would also unblock us.

Environment

• Local OS: Windows 11 Pro 26200, bash via Git Bash
• Node 20.x, pnpm
• Supabase CLI: npx supabase@2.95.2
• Not using Supabase Preview Branches (single-DB-per-project); failing check fires on direct production-branch push.

[melihzafer]

Update: reproducible across 3 separate Supabase projects in our org.

After applying the full canonicalize procedure (delete legacy/polluted rows where version ~ '_', then supabase migration repair --linked --status applied <every-version>) to all three environments, every project reports an identical, clean local state:

• supabase migration list --linked → 136 rows, every one Local + Remote matched
• supabase db push --linked --dry-run → Remote database is up to date.
• SELECT count(*) FILTER (WHERE statements IS NULL) AS null_stmts, count(*) FILTER (WHERE version ~ '_') AS polluted FROM supabase_migrations.schema_migrations → null_stmts=0, polluted=0, total=136

The hosted Supabase Branching auto-deploy still fails with the original schema_migrations_pkey collision on push.

Diagnostic finding: prior to canonicalize, two of the three envs had 265 rows = 136 canonical + 129 polluted-format duplicates, where each migration had both a canonical row (version='001', name='initial_schema', statements=[...]) and a polluted-format sibling (version='001_initial_schema', name='001_initial_schema.sql', statements=[...]). We don't know which CLI/integration version originally wrote the polluted rows; the current local CLI 2.95.2 does not produce that format. We've removed them.

Local CLI's migration list and db push --dry-run both treat the post-canonicalize state as fully in sync. The hosted CLI does not — it still attempts an INSERT on version='001' and aborts on the duplicate-key error.

Strongly suggests the hosted CLI is operating from different state-comparison logic than supabase db push --linked does, and is unaffected by removing the polluted rows.

[melihzafer]

Update 2: hosted check now passing on develop after the cleanup.

Commit 8defca06 (no migration-file changes — only doc + helper script additions) just got a green "Supabase Preview" check on develop, where every previous push for weeks had failed.

The state difference: the prior failing pushes ran against a tracking table that already had polluted-format rows (or had had them at some point). After deleting all rows where version ~ '_' plus rerunning supabase migration repair --linked --status applied <every-version>, the next push passed.

So the bug seems to be: the hosted Branching CLI's diff-against-remote logic gets stuck if the tracking table has ever contained legacy polluted-format rows (version='001_initial_schema' style). Even after the polluted rows are deleted via direct SQL and the canonical rows are repaired, the hosted CLI continues to behave as if it's reconciling against the polluted state — until something (we don't know what specifically — possibly a no-migration-change push) re-syncs its internal state.

If this resonates with maintainers, a documented "how to reset hosted-CLI state for a project whose tracking table was manually rebuilt" recipe would unblock other users in the same situation.

Workaround for anyone hitting this:

-- 1. Remove polluted suffixed-format rows
DELETE FROM supabase_migrations.schema_migrations WHERE version ~ '_';

# 2. Repair canonical rows (populates statements column properly)
versions=$(ls supabase/migrations | sed -E 's/^([0-9]+)_.*\.sql$/\1/' | sort -u | tr '\n' ' ')
supabase migration repair --linked --status applied $versions

# 3. Push any commit (even an empty / non-migration one) — appears to nudge the
#    hosted CLI to re-evaluate. After that, the check goes green.
git commit --allow-empty -m "trigger supabase repair re-eval"
git push