Supabase Migrations: Eight Files, and Four of Them Are Fixes

A Supabase migration is a plain .sql file in supabase/migrations/, applied in filename order and recorded in supabase_migrations.schema_migrations so each one runs exactly once per database. This storefront's commerce schema is eight of them. Three create things; four exist only because something already shipped was wrong — which is the half of migration practice the CLI reference doesn't cover.

Here is the actual directory, what each file was for, and the three habits that made a mid-flight schema change safe to run against a live project.

The census: eight files, four categories

$ ls supabase/migrations/
0001_commerce_init.sql              0005_retain_anonymized_download_audit.sql
0002_display_name_allowlist.sql     0006_pricing_v2_slot_kinds.sql
0003_commerce_rpcs.sql              0007_atomic_download_rate_limit.sql
0004_lock_down_rate_limit_fn.sql    0008_refund_requests.sql

Sorted by why it exists rather than by number, the shape of a real migration history shows up immediately:

Two things are worth reading off that table before any code. The first is the line counts: the three "create" migrations are 170 lines between them, and the five that follow are 101. Schema work does not stop when the schema is right — it continues as long as the application keeps learning what the schema got wrong.

The second is that the smallest file is a security fix. 0004 is four lines, one of which is SQL.

Why filename order is the whole contract

Supabase applies migrations in lexicographic filename order and records each applied version in a table it manages, supabase_migrations.schema_migrations. supabase db push diffs the local directory against that table and runs only what is missing, in order. That gives you two guarantees and one obligation.

The guarantees: a migration runs once per database, and it runs after everything with a lower number. The obligation: a migration may never assume anything that a later migration establishes. 0006 can swap a constraint that 0001 created. 0001 can say nothing about 0006.

This is why the ordering shows up inside the files as much as in their names. 0006 is a constraint swap that landed after the pricing model changed, and its header records the reasoning that made it safe to run against a live project:

-- Pricing v2 (grant-by-template): update the entitlement_slots.kind allowlist.
--
-- Safe to run: the CHECK is a constraint swap only; the create_order_with_slots
-- RPC is kind-agnostic (inserts one row per element of p_slot_kinds), so it
-- needs no change. No existing rows use the removed kind (commerce is not live).
alter table public.entitlement_slots
  drop constraint if exists entitlement_slots_kind_check;

Three separate claims there, and each one is checkable: the RPC from 0003 does not need editing, no live row uses the dropped value, and the change is a constraint swap rather than a data migration. A migration whose header cannot make claims like those is a migration you are guessing about.

The three habits that make a migration re-runnable

Every file in this directory can be replayed against a database that already has it applied without erroring. That is not what the migration runner requires — it tracks versions, so it will not re-run anything — but it is what makes a migration safe to apply by hand, to a branch database, or to a project restored from a backup.

if exists / if not exists on every drop and create. 0006 opens with drop constraint if exists rather than drop constraint, so the file is idempotent whether or not the constraint is there.

create or replace for functions. 0003 declares its RPCs with create or replace function, so 0008 can extend refund_order() later by redeclaring it rather than dropping and recreating it — which would have dropped its grants along with it.

on conflict do nothing for inserts that a retry could repeat. The order-creation RPC relies on it:

insert into public.orders (ls_order_id, ls_customer_id, email, tier, ...)
values (p_ls_order_id, p_ls_customer_id, p_email::extensions.citext, p_tier, ...)
on conflict (ls_order_id) do nothing returning id into v_order_id;
if v_order_id is null then

That if v_order_id is null branch is the payment webhook being delivered twice, which LemonSqueezy will do. The migration is where idempotency gets enforced, not the handler — the same reasoning behind a signed webhook that can be replayed safely.

The interesting migration is a race-condition fix

0007 is the longest non-creating migration here, and it is worth quoting its header because it is the clearest example of what migrations are for:

-- F-1 fix: atomic per-user download rate-limit + audit insert (R-13).
--
-- Root cause (CWE-367 TOCTOU / OWASP A04): the download endpoint enforced the
-- hourly limit with a non-atomic check-then-act — read downloads_in_last_hour(),
-- then a SEPARATE insert of the audit row. Under concurrency, N requests could
-- all read the same count, all pass the `< limit` check, and all insert, so a
-- burst of parallel requests bypassed the cap.
--
-- This RPC does the count + conditional insert in ONE call, serialized per user
-- with a transaction-scoped advisory lock, so concurrent requests for the same
-- user queue instead of racing.

The shipped bug was in application code: two sequential database calls with a decision in between. The fix was not in application code. It could not be, because no amount of care in TypeScript makes two round trips atomic. The limit had to move into one statement inside the database, holding a lock, and that is a schema change — so it is a migration.

The return contract is the part to copy: the RPC returns the post-insert count when it recorded the download, and -1 when the caller was over the limit, in which case nothing is inserted. A rejected download does not get audited as a download. The application's job shrinks to interpreting one integer, which is small enough to test exhaustively without a live database — how this suite proves an authorization gate without a server covers that side of it.

What a migration cannot express

The last line of 0001 is a comment, and it is the most useful line in the file for anyone porting this pattern:

-- Storage: private bucket 'releases' created separately (storage.buckets insert, public=false).

The private bucket that every paid download is served from is not in the migration history. It was created as a row in storage.buckets, out of band. So the schema is reproducible from supabase/migrations/ and the storage configuration is not, and anyone rebuilding this project from the directory alone gets a database whose download route resolves to a bucket that does not exist.

That comment is the fix that was available: state the out-of-band step in the migration that depends on it. Bucket creation, auth provider settings, SMTP configuration, and scheduled jobs all live in the dashboard or in the management API rather than in DDL, and every one of them is a thing a migration-only rebuild silently misses — the SMTP one loudly, since the built-in mailer's caps are what a rate-limit error is actually telling you. The alternative — writing bucket inserts into a migration — works but puts storage's internal tables under your version control, which Supabase does not recommend.

Policies belong in migrations, and only there

RLS policies are schema, so they are in the migration files and in no other place. The whole policy set for this project, which 0001 and 0008 create between them:

$ grep -n "create policy" supabase/migrations/*.sql
0001_commerce_init.sql:65:create policy "own profile"   on public.profiles          for select using ((select auth.uid()) = id);
0001_commerce_init.sql:66:create policy "own orders"    on public.orders            for select using ((select auth.uid()) = user_id);
0001_commerce_init.sql:67:create policy "own slots"     on public.entitlement_slots for select using ((select auth.uid()) = user_id);
0001_commerce_init.sql:68:create policy "own downloads" on public.download_events   for select using ((select auth.uid()) = user_id);
0008_refund_requests.sql:38:create policy "own deliveries"       on public.download_deliveries for select using ((select auth.uid()) = user_id);
0008_refund_requests.sql:39:create policy "own refund requests"  on public.refund_requests    for select using ((select auth.uid()) = user_id);

Six policies, all for select, all read-own. Zero INSERT policies anywhere — every write goes through server code holding the service-role key, which bypasses RLS entirely. Writing a policy in the dashboard's SQL editor instead would work identically on that one project and be absent from every other environment, which is the whole argument for keeping policies in files. What the service role key actually does covers the other half of that split.

One detail from 0002 is easy to get backwards: CHECK constraints apply to service-role writes too. Only RLS is bypassed. That is precisely why the display-name allowlist is worth having as a constraint rather than only as application-layer validation — it is the backstop that still holds when the write arrives through the admin client.

Troubleshooting

Frequently asked questions

Should I edit an existing migration or add a new one? Add a new one, always, once the original has been applied anywhere but your own machine. The runner records versions, not checksums, so an edit to an applied file changes nothing on databases that already ran it and silently produces two different schemas that claim the same history.

How do I create a migration? supabase migration new <name> writes an empty timestamped file into supabase/migrations/, which you then fill with DDL. The four-digit sequential names in this project are a hand-maintained convention for a small, single-author history; timestamps are the better default as soon as two people might create migrations the same day.

Do RLS policies go in migrations? Yes. Policies are schema, and a policy written only in the dashboard exists on exactly one database. Every policy in this project is created by a numbered file, which is why the whole set can be listed with one grep.

What about the things that aren't SQL — buckets, auth settings, SMTP? None of those are migrations, and pretending otherwise is how a rebuild breaks. Keep them in a provisioning checklist, and leave a comment in the migration that depends on one — the way 0001 names the private releases bucket it cannot create.

Templates in this post

ASoc Pip is a forex-trading marketing site with a live-rates dashboard preview and a three-tier pricing table. ASoc Press is a news and magazine template with editor's picks, galleries, and video and audio feeds. ASoc Quest is a dark-theme games-storefront landing page with deals, top sellers and platform rows. Each is the marketing half of a product whose commerce schema would look much like the eight files above.

Browse the full sets: Next.js landing page templates, Tailwind landing page templates.