0012 — Use Supabase CLI for migrations

• Status: accepted
• Date: 2026-05-06
• Deciders: Derek

Context

Phase 0 of ark shipped with a bespoke migration system: SQL files in platform/sql/NNN_*.sql, applied via a custom pnpm migrate:apply CLI that tracked applied migrations in a public.ark_migrations table. The CLI also exposed an applyMigrations(pool, sqlDir, { tracking }) library for the test harness.

This worked, but it duplicated infrastructure that Supabase already provides:

• The Supabase CLI (supabase migration new, supabase db push) is the canonical migration tool for Supabase projects. Every Supabase team uses it.
• It tracks applied migrations in supabase_migrations.schema_migrations (Supabase’s own history table) — which the dashboard surfaces under Database → Migrations.
• It plays naturally with Supabase’s project lifecycle (preview environments, branching, supabase db diff, supabase db reset).

We initially set up ark-demo via the Supabase MCP apply_migration tool, which writes to schema_migrations correctly. So the project was already in good shape; the redundancy was on the local side — our custom CLI was tracking the same migrations a second time.

The question surfaced when the project owner noted: “I’ve used the Supabase CLI in another project — are we doing something different?” Yes; we shouldn’t be.

This decision is also a worked example of ADR 0011 applied to ourselves: don’t reinvent. Use the canonical tool.

Decision

Use the Supabase CLI for production migration application. Keep applyMigrations() as a hermetic test-harness primitive in @ark/db.

Concretely:

• Migration files live at supabase/migrations/<14-digit-timestamp>_<descriptive-name>.sql. This is the Supabase CLI convention. We retain the original NNN_ ordering inside the descriptive name (e.g., 20260507025630_001_helpers.sql) so human scanning is still easy.
• Production application uses supabase db push --linked against the linked remote project. History tracked in schema_migrations. Visible in the dashboard.
• Authoring uses supabase migration new <name> — creates the timestamped file. Edit the SQL. pnpm migrate:lint validates structural compliance with ADR 0002. supabase db push applies.
• The custom pnpm migrate:apply CLI is removed. So is migrate-cli.ts, migrate-cli.spec.ts, and public.ark_migrations.
• applyMigrations(pool, sqlDir) stays in @ark/db — simplified to a hermetic helper (no history tracking) used only by the RLS isolation test harness, which wipes the schema before each run and needs a fast in-process primitive that doesn’t need the Supabase CLI installed.
• supabase/config.toml is committed. Notably, the Custom Access Token Hook (ADR 0007 / migration 007) is declared there, so any future linked project picks up the hook configuration automatically.

Consequences

Easier:

• Standard tooling. Every Supabase developer (and AI agent) already knows the workflow. Less custom code to maintain.
• Dashboard integration: applied migrations show up in the Supabase UI without our help.
• Future Supabase features (branch-per-PR, preview deploys, supabase db diff) work out of the box.
• supabase/config.toml versions auth hooks, schemas, and other project settings — they were previously dashboard-only.

Harder:

• Filenames are timestamped (uglier than 001_*). Mitigation: keep the 001_ ordering inside the descriptive name for readability.
• Two paths to “apply migrations”: Supabase CLI for production, applyMigrations() for tests. Keep the boundary explicit (it’s documented in packages/db/CLAUDE.md).
• The Supabase CLI is now a development dependency. It’s already installed for everyone working on Supabase projects; not a new install for the existing engineer.

Migration journey (what was done)

The repo’s history reflects the bespoke system through Phase 1.5a; this ADR (and its sibling commits) document the switch. For agents reading older sessions or stale memory:

Memory and docs that referenced the old paths have been updated to point at the new layout.

Alternatives considered

• Stay with the bespoke CLI — keeps full control; loses canonical tooling, dashboard integration, and free Supabase features (preview branches, db diff). Adds maintenance debt. Rejected.
• Use Supabase CLI for everything, including tests — the CLI’s db reset can rebuild a local Supabase stack, but it’s slow (~30s per reset) and requires the full Supabase Docker stack. Our test harness uses a plain Postgres container and applies migrations in-process, which runs in ~500ms total. The split (CLI for prod, helper for tests) is a real optimization, not a hack.
• Use a third-party migration tool (knex, prisma-migrate, drizzle-kit) — pulls us out of Supabase’s tooling story. Each has its own format, its own history tracking. Worse fit. Rejected.

Trip-wires

We revisit this if:

• Supabase CLI ever becomes unsuitable for our project shape (e.g. drops support for hooks-via-config-toml, removes db push --linked, or significantly changes the migration format)
• A team member needs to apply migrations from an environment where installing Supabase CLI is painful (currently: npm install -g supabase or brew install supabase/tap/supabase)
• The bespoke approach becomes necessary again because we add a second database backend (e.g., a tenant on Postgres-but-not-Supabase) — at which point we’d write a new ADR for the multi-backend story