Use when applying Cloudflare D1 migrations, fighting Supabase migration history vs SQL execution, choosing direct psql over CLI, designing idempotent migrations, debugging schema drift between local and remote, or recovering after a half-applied migration. Triggers: supabase migration repair instructions appearing, table missing after "applied" status, "Tenant or user not found" when running psql, --remote vs --local D1 confusion, NOT NULL on a populated column, foreign key constraint failures, drift between staging and prod schemas. NOT for Mongo/document migrations, ORM-managed migrations specifically (Prisma/Drizzle have their own conventions), or pure data backfills.
Use when applying Cloudflare D1 migrations, fighting Supabase migration history vs SQL execution, choosing direct psql over CLI, designing idempotent migrations, debugging schema drift between local and remote, or recovering after a half-applied migration. Triggers: supabase migration repair instructions appearing, table missing after "applied" status, "Tenant or user not found" when running psql, --remote vs --local D1 confusion, NOT NULL on a populated column, foreign key constraint failures, drift between staging and prod schemas. NOT for Mongo/document migrations, ORM-managed migrations specifically (Prisma/Drizzle have their own conventions), or pure data backfills.
metadata
{"category":"Backend & Infrastructure","tags":["database","migrations","sqlite","postgres","supabase","d1","schema"],"pairs-with":[{"skill":"sqlite-durable-agent-state","reason":"D1 is SQLite; that skill owns the durable-state schema patterns whose evolution this skill migrates safely."},{"skill":"ideal-web-app-builder","reason":"Supabase/D1-backed apps built there need this skill's repair-vs-execute and --remote discipline the first time the schema changes."}],"provenance":{"kind":"first-party","owners":["port-daddy"]},"io-contract":{"kind":"deliverable","consumes":["[Truncated]","[Truncated]"],"produces":["[Truncated]","[Truncated]"]}}
D1 and Supabase Migrations Done Right
Migrations are state changes the database remembers. The most expensive bug in this domain is "the history says applied but the SQL never ran" — Supabase's migration repair makes this trivially possible. This skill catalogs the traps and the safe patterns.
When to use
Adding a column, table, or index to D1 or Supabase.
A migration appears to have run but the schema doesn't reflect it.
Wrangler suggests migration repair; pause before running it.
Direct psql connection needed because the CLI can't authenticate.
Schema drift between two environments.
A migration on a populated table needs a backfill or default.
Core capabilities
The repair-vs-execute trap (Supabase)
Read this twice:
supabase migration repair --status applied 042 ONLY updates supabase_migrations.schema_migrations. It does NOT execute the SQL.
If the database doesn't have the table or column the migration creates, repair --status applied will leave you with the history claiming "applied" and the schema saying nothing. Subsequent supabase db push will skip the migration because the history says it ran.
Always run the SQL first. Then repair.
Direct psql for Supabase
Build the connection URL yourself when the CLI fails:
PROJECT_REF is the substring before .supabase.co in NEXT_PUBLIC_SUPABASE_URL.
Port 5432 is the direct connection. Port 6543 is the pgbouncer pooler.
Pooler URLs return Tenant or user not found for migration scripts because pgbouncer's transaction-pooling mode doesn't support all features migrations need (advisory locks, prepared statements).
After the SQL has actually run, mark the history:
supabase migration repair --status applied 042
Verify with a real query, not history:
psql ... -c
"SELECT count(*) FROM information_schema.columns WHERE table_name = 'audit_log';"
D1 migrations
D1 has a single migration command and no separate "repair":
wrangler d1 migrations create windags-telemetry add_cascade_scores
# Edit the generated SQL file under migrations/
wrangler d1 migrations list windags-telemetry --remote
wrangler d1 migrations apply windags-telemetry --remote
The d1_migrations table is updated only on successful apply. There's no "mark applied without running" footgun in D1.
--local and --remote are different databases. Local uses .wrangler/state/v3/d1/. A common bug: applying locally, declaring victory, then prod has the old schema.
For exploratory queries, use --json for parseable output.
Idempotent migrations
Both Postgres and SQLite (D1) accept IF NOT EXISTS on most things:
-- Both DBsCREATE TABLE IF NOTEXISTS audit_log (
id INTEGERPRIMARY KEY,
ts INTEGERNOT NULL
);
CREATE INDEX IF NOTEXISTS idx_audit_ts ON audit_log(ts);
-- Postgres-onlyALTER TABLE audit_log ADDCOLUMN IF NOTEXISTS actor_id TEXT;
-- SQLite (D1) — no IF NOT EXISTS on ADD COLUMN. Check first:SELECTcount(*) AS has_col
FROM pragma_table_info('audit_log')
WHERE name ='actor_id';
-- Then conditionally apply via app code, or use a defensive approach:-- ALTER TABLE audit_log RENAME COLUMN ... can also fail if you re-run.
A simple alternative for SQLite: write each ALTER as a separate migration file, never edit a migration after it's been applied anywhere.
Foreign keys
SQLite (D1) needs PRAGMA foreign_keys = ON per connection. D1's runtime enables it for you on every query, but wrangler d1 execute may not — verify:
Postgres always enforces foreign keys. The bug is usually the other way: a deferred constraint isn't being deferred, blocking a delete.
Adding a NOT NULL column to a populated table
Three steps, three migrations:
-- 1. Add the column nullable.ALTER TABLE orders ADDCOLUMN region TEXT;
-- 2. Backfill (separate migration, possibly batched).UPDATE orders SET region ='us-west'WHERE region ISNULL;
-- 3. Make it NOT NULL.ALTER TABLE orders ALTERCOLUMN region SETNOT NULL; -- Postgres-- SQLite: requires table rebuild. Acceptable for small tables; use `ALTER TABLE ... RENAME` + new table for big ones.
Single-migration NOT NULL with default is fine for small tables (ALTER TABLE … ADD COLUMN region TEXT NOT NULL DEFAULT 'us-west'). On large tables this rewrites the heap on Postgres pre-11; check size first.
D1: there's no built-in dump. Query sqlite_master:
wrangler d1 execute mydb --remote --command="SELECT sql FROM sqlite_master ORDER BY type, name;" --json > /tmp/remote.json
wrangler d1 execute mydb --local --command="SELECT sql FROM sqlite_master ORDER BY type, name;" --json > /tmp/local.json
diff /tmp/local.json /tmp/remote.json
Verification pattern
After every migration, run a query that asserts presence:
const { results } = await env.DB.prepare(
"SELECT count(*) AS n FROM pragma_table_info('audit_log') WHERE name = 'actor_id'"
).first();
if (results.n === 0) thrownewError('migration did not apply');
Symptom: Wrangler/Supabase CLI suggests repair; you run it; the table is still missing.
Diagnosis: Repair updates history, not schema. SQL was never executed.
Fix: Run the SQL via psql or the Supabase SQL editor first. Verify the schema. Then repair if needed.
Pooler URL for migrations
Symptom:psql -f migration.sql returns Tenant or user not found or hangs.
Diagnosis: You're using port 6543 (pgbouncer transaction pool), which doesn't support migration features.
Fix: Direct connection on port 5432: db.<ref>.supabase.co:5432.
Local-only D1 apply, ship to prod
Symptom: Code deploys, throws "no such column" on first request.
Diagnosis: Migrations applied with default --local flag; remote DB unchanged.
Fix: Always --remote for production migrations. Add a CI check that the remote schema matches the local one before deploy.
NOT NULL without default on a populated table
Symptom: Migration fails: "column contains null values".
Diagnosis: Adding NOT NULL without first backfilling existing rows.
Fix: Three-step: nullable → backfill → NOT NULL. Or single ADD COLUMN with NOT NULL DEFAULT.
Hardcoding migration IDs in app code
Symptom: App refuses to start because "expected migration 042 applied".
Diagnosis: Coupling app version to a specific migration number; works for a sprint, breaks on the first squash/cherry-pick.
Fix: Feature checks against the schema (information_schema.columns / pragma_table_info), not migration history.
Long migrations on a hot table
Symptom: Production locks up for minutes during a deploy.
Diagnosis: Single transaction rewriting a 100M-row heap (full-table ALTER, big index build without CONCURRENTLY).
Fix: Postgres: CREATE INDEX CONCURRENTLY, batched UPDATE in chunks of 10k with sleep, online DDL via pg_repack. SQLite: shadow table + atomic rename.
Quality gates
Every Supabase migration verified by SELECT against the new shape, not history.
Every D1 migration applied with --remote to production.
CI check: local schema (pg_dump --schema-only or sqlite_master) matches expected baseline.
repair --status applied never run unless the SQL has been executed and verified.
Migrations are forward-only, or have a tested down-script.
No NOT NULL without default on tables with existing rows.
Long migrations on hot tables batched (chunks of 10k or fewer).
Migration files immutable once applied anywhere; new changes get new files.
Deterministic Audit
Before applying (or reviewing) a migration, write the plan as JSON matching
schemas/migration-plan.schema.json and run the auditor:
auditMigrationPlan(plan) (in scripts/migration_plan_audit.mjs) turns this
skill's core rule — history rows lie, only the schema tells the truth — and its
Quality Gates into machine-checkable rules over structured fields: repair
planned before the SQL has executed (critical), no schema-asserting
verification query, a Supabase migration routed through the pgbouncer pooler
(critical), a production D1 apply targeting --local (critical), NOT NULL on a
populated table without default/backfill (critical), an applied migration file
being edited, an unbatched hot-table rewrite, and an untested down-script. It
returns { pass, score, findings, recommendations }.
examples/sample-input.json is a correctly-ordered execute-verify-repair plan
(pass: true, zero findings).
NOT for
Mongo/document migrations — different paradigm; pair with a document-DB skill.
Prisma / Drizzle migration tooling — they each have specific conventions; use the matching skill.
Pure data backfills — schema is locked; you're moving data. Different operational concerns.
MySQL/Aurora migrations — different DDL behavior, different lock semantics.
Multi-region replication / failover — operational, not schema. No dedicated skill.
Wrangler / platform-level errors (auth, binding, deploy) on Cloudflare side — → cloudflare-workers-debugging.
Hono route handlers reading the migrated tables — different concern. → hono-patterns.
