---
name: supabase-schema-and-migrations
description: >-
  Supabase schema and migrations: migration files as the source of truth,
  keeping generated TypeScript types in sync, reversible and deploy-safe
  destructive migrations, and enforcing rules with constraints. Use when
  changing the database schema.
argument-hint: "[table or migration]"
license: MIT
metadata:
  author: John Felix Lim
  version: "1.0.0"
---
# Schema & Migrations

Apply when making any schema change. Before touching the dashboard directly, write the migration file first — the dashboard edit, if you make one, should be the migration running, not a substitute for it.

## Rules

1. **Every schema change is a migration file, not a dashboard edit** — A change made through the Supabase Studio UI and never captured in a migration file exists only in one environment. The next `db reset` or fresh environment silently doesn't have it.
2. **Generated types are a build step, not a manual sync** — `supabase gen types typescript` regenerated after every migration keeps the TypeScript types honest. Hand-editing the generated file, or letting it go stale, reintroduces exactly the drift types exist to prevent.
3. **A migration that isn't reversible is a one-way door** — A schema change should pair with a clear rollback story. A migration that drops a column with no way back turns a mistake into a data-loss incident instead of a `db reset`.
4. **Foreign keys and check constraints are enforcement, not documentation** — A comment saying 'status should be one of...' enforces nothing. A `check` constraint or a foreign key to a lookup table makes the invalid state actually impossible to insert.
5. **Destructive migrations need a deploy-time plan, not just a local test** — Dropping or renaming a column that's still read by the currently-running application version causes errors in production during the deploy window. Sequence schema changes so old and new code can both run against the schema briefly.

## Patterns

### Schema change made only through the dashboard

**Avoid** — The change exists in exactly one environment's actual database and nowhere in version control — it's invisible to everyone else.

```sql
-- A column added via Supabase Studio's table editor in production.
-- No migration file exists anywhere in the repo.
-- The next teammate who runs `supabase db reset` locally
-- doesn't have the column, and staging never gets it either.
```

**Prefer** — The change is captured as a file every environment applies identically, and it's reviewable in the same PR as the code that uses it.

```sql
-- supabase/migrations/20240115120000_add_status_to_orders.sql
alter table public.orders
  add column status text not null default 'pending';
```

### Generated types left stale

**Avoid** — The types no longer describe the actual schema, so the compiler stops being able to catch a typo'd or missing column.

```ts
// database.types.ts — last regenerated three migrations ago.
// The `status` column added since then doesn't exist in the type,
// so order.status silently types as `any` via a workaround cast.
const order = data as any;
```

**Prefer** — Types are regenerated in lockstep with schema changes, so the compiler's picture of the database stays accurate.

```ts
// Run as part of the same commit that adds the migration:
// supabase gen types typescript --local > database.types.ts
```

### Enforcing a rule with a comment instead of a constraint

**Avoid** — Nothing stops an application bug — or a manual dashboard edit — from writing 'shipped' or an empty string into status.

```sql
-- status should only ever be 'pending', 'paid', or 'refunded'
create table public.orders (
  status text
);
```

**Prefer** — The invalid state is impossible to insert, regardless of which application code path or manual edit produced the write.

```sql
create table public.orders (
  status text not null check (status in ('pending', 'paid', 'refunded'))
);
```

## Review checklist

- [ ] Every schema change exists as a migration file in version control, with no dashboard-only edits in any environment.
- [ ] `supabase gen types typescript` is regenerated as part of the same change as the migration that prompted it.
- [ ] Destructive migrations (drop column, drop table, rename) have a documented rollback or are sequenced to avoid a deploy-window outage.
- [ ] Business rules that must always hold are enforced with `check` constraints or foreign keys, not comments or application-only validation.
- [ ] Migrations are tested against a fresh `supabase db reset`, not only applied incrementally to an already-correct local database.
- [ ] Renaming or removing a column accounts for the currently-deployed application version still reading the old shape during rollout.

---

From the John Felix Lim Frontend Engineering Playbook — https://github.com/JohnFelixLim