Supabase database migrations: the right way for production SaaS

Supabase has two ways to manage schema changes: clicking through the dashboard UI, and using SQL migration files. The dashboard is fine for exploration — but for a production SaaS, SQL migrations are non-negotiable. They give you a versioned history of every schema change, make it easy to reproduce your database in a new environment, and ensure your team's local and production databases stay in sync.

Here's how to set up a proper migration workflow for a Supabase + Next.js SaaS.

The baseline: your initial schema

Start with a SQL file that creates your tables from scratch. This becomes your migration baseline:

-- supabase/schema.sql
-- Run in Supabase SQL editor or via Supabase CLI

-- Users table (mirrors Clerk auth)
create table public.users (
  id uuid primary key default gen_random_uuid(),
  clerk_id text unique not null,
  email text not null,
  created_at timestamptz default now()
);

-- Subscriptions table (mirrors Stripe)
create table public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.users(id) on delete cascade,
  stripe_customer_id text unique,
  stripe_subscription_id text unique,
  status text,           -- 'active' | 'canceled' | 'past_due' | 'trialing'
  plan text,             -- 'free' | 'pro'
  current_period_end timestamptz,
  updated_at timestamptz default now()
);

-- Enable Row Level Security
alter table public.users enable row level security;
alter table public.subscriptions enable row level security;

-- No RLS policies needed for tables only accessed by the service role
-- (server-side admin client bypasses RLS)

Using Supabase CLI for migrations

The Supabase CLI manages migrations as numbered SQL files. Install it and link your project:

npm install -g supabase

# Log in and link to your project
supabase login
supabase link --project-ref your-project-ref

Your project ref is the string in your Supabase project URL:https://supabase.com/dashboard/project/YOUR_REF

Creating migrations

When you need to change the schema, create a new migration file:

supabase migration new add_projects_table

This creates supabase/migrations/20260411120000_add_projects_table.sql. Write your SQL changes in this file:

-- supabase/migrations/20260411120000_add_projects_table.sql

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.users(id) on delete cascade,
  name text not null,
  description text,
  created_at timestamptz default now()
);

alter table public.projects enable row level security;

-- Add a counter to users for quick project count
alter table public.users add column project_count integer default 0;

Applying migrations

Apply pending migrations to your remote database:

supabase db push

This runs all migration files that haven't been applied yet, in order. Supabase tracks which migrations have run in a supabase_migrations table in your database.

For local development, use the Supabase local stack:

supabase start  # starts local Postgres + Studio
supabase db reset  # applies all migrations from scratch

Safe migration patterns

Migrations run against a live database. Bad migrations can lock tables, corrupt data, or take your app down. Follow these rules:

• Add columns as nullable first. Adding a NOT NULL column to a table with existing rows requires a default value. Better: add it nullable, backfill, then add the constraint.
• Never DROP columns in the same migration that removes them from code.Deploy the code removal first, then drop the column in a separate migration.
• Create indexes concurrently. CREATE INDEX locks the table.CREATE INDEX CONCURRENTLYdoesn't — use it on large tables.
• Test migrations locally first. Run supabase db resetlocally to verify the migration applies cleanly from scratch.

-- ✅ Safe: add nullable column, backfill, add constraint
alter table projects add column status text;
update projects set status = 'active';
alter table projects alter column status set not null;
alter table projects alter column status set default 'active';

-- ❌ Unsafe: can fail on tables with existing rows
alter table projects add column status text not null;

Seeding test data

Create a seed file for local development:

-- supabase/seed.sql

-- Insert test users
insert into public.users (clerk_id, email) values
  ('user_test_1', 'test1@example.com'),
  ('user_test_2', 'test2@example.com');

-- Insert test subscriptions
insert into public.subscriptions (user_id, status, plan)
select id, 'active', 'pro' from public.users where clerk_id = 'user_test_1';

supabase db reset  # applies migrations + seed

Generating TypeScript types from your schema

Supabase can generate TypeScript types directly from your database schema:

supabase gen types typescript --linked > lib/supabase/database.types.ts

Use these types with the Supabase client for full type safety:

import type { Database } from "@/lib/supabase/database.types";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient<Database>(url, key);

// Now queries are fully typed
const { data } = await supabase
  .from("projects")  // autocompletes to your tables
  .select("id, name, created_at");  // autocompletes columns
// data is typed as Array<{ id: string; name: string; created_at: string }>

Run the type generation as part of your pre-commit hook or CI pipeline to keep types in sync with your schema automatically.

Migration strategy for the GetLaunchpad schema

GetLaunchpad ships with supabase/schema.sql as the baseline. If you need to extend the schema for your product:

1. Run supabase migration new your_change_name to create a migration file.
2. Write your SQL changes in the migration file.
3. Test with supabase db reset locally.
4. Apply with supabase db push to production.
5. Regenerate types with supabase gen types typescript.

Never make schema changes directly in the Supabase dashboard for production — always go through migration files so the change is tracked and reproducible.