Supabase Migrations — Numbering and Ordering

Why Migration Order Matters in Team Development

Supabase migrations run sequentially by timestamp. If two developers create migrations at nearly the same time, Postgres executes them in lexicographic order by filename, not creation order. This causes foreign key violations, missing columns, and index failures when migration A depends on a table created in migration B.

In AI agent systems with Claude and Next.js, your database schema often changes weekly—adding vector columns, audit tables, or embedding storage. Without strict ordering conventions, your staging and production environments diverge, and deployments fail at 2 AM.

Timestamp-Based Numbering: The Standard Approach

Supabase uses the format `YYYYMMDDHHMMSS_descriptive_name.sql`. The timestamp prefix ensures chronological execution. Generate this in your migration script: `date +%Y%m%d%H%M%S` on Unix, or use your migration tool's built-in generator.

Example filenames: `20250115143022_create_agents_table.sql`, `20250115143045_add_embeddings_column.sql`. The second migration runs after the first, every time, across every environment.

-- Migration: 20250115143022_create_agents_table.sql
CREATE TABLE agents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  model TEXT DEFAULT 'claude-opus',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Migration: 20250115143045_add_embeddings_column.sql
ALTER TABLE agents ADD COLUMN embeddings vector(1536);

Preventing Conflicts with Strict Ordering Rules

Never manually edit timestamps. Always generate new ones via your migration CLI. In Next.js projects using Supabase, use `supabase migration new <name>` to auto-timestamp.

If migrations are created seconds apart, their timestamps naturally order correctly. If a developer creates two migrations offline, timestamps may collide—use a unique suffix: `20250115143022_01_schema.sql`, `20250115143022_02_permissions.sql`.

Handling Out-of-Order Execution

If you discover a migration ran in the wrong order (rare, but possible with version control conflicts), don't re-order—create a new migration that fixes the schema state. Supabase tracks executed migrations in `schema_migrations` table; re-running old migrations causes errors.

For AI agent databases, this means if migration A adds a permissions table and migration B references it, never swap their order retroactively. Write migration C to handle the dependency cleanup if needed.

Testing Migrations Locally Before Deployment

Use `supabase start` to spin up a local Postgres instance and run migrations in real order. This catches dependency issues before they hit staging or production. Reset and re-run migrations during development with `supabase db reset`.

In CI/CD pipelines for Claude-powered systems, validate migration syntax and ordering before merging to main: `supabase migration list` shows execution order, `supabase db push` applies them safely.

Open-Source Implementation: Pantheon Repo

The Pantheon repository at github.com/lewisallena17/pantheon demonstrates production-grade migration structuring for AI agent backends. It enforces timestamp-based ordering, includes rollback strategies, and shows how to version schema alongside Claude integrations.

Clone it to see real migrations for agent state tables, vector storage, and audit logging—complete with Next.js API routes that depend on specific schema versions.

Open-source implementation

Everything in this article runs in pantheon — a production-ready Next.js + Supabase + Claude starter. Clone it, deploy to Vercel, run PM2. The dashboard auto-commits every agent edit and reverts itself if TypeScript breaks.