Supabase Migrations

Supabase migrations

Self-hosted Supabase fails differently from managed Supabase. The failures are quiet: the migration succeeds, the API returns an empty array, and nothing anywhere reports an error. This skill is the checklist that catches those before they ship.

When to use this

Writing a migration, reviewing one, or debugging "the table exists but the API says it does not".

The order that matters

1. Take the next free number. Never reuse one, never renumber an applied migration.

   ls migrations/ | sort -V | tail -1
   

2. Write the DDL idempotently. create table if not exists, add column if not exists, drop policy if exists before create policy. A migration that cannot be run twice will eventually be run twice.

3. Grant, then policy, then enable. Row Level Security without a grant returns permission-denied; a grant without RLS exposes every row. Both, in that order.

4. End with the schema reload.

   notify pgrst, 'reload schema';
   

   This is the one that gets forgotten. PostgREST caches the schema at boot. Without this line the table exists in Postgres and does not exist over the API, and every symptom points at your client code.

5. Verify against the database, not against the file.

   bash scripts/verify.sh <table_name>
   

Template

-- 00NN_<what_it_does>.sql
-- <one line: why this exists>

create table if not exists public.thing (
  id          uuid primary key default gen_random_uuid(),
  slug        text unique not null,
  status      text not null default 'draft'
                check (status in ('draft', 'published', 'archived')),
  payload     jsonb not null default '{}'::jsonb,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

create index if not exists thing_status_idx on public.thing (status)
  where status = 'published';

alter table public.thing enable row level security;

grant select on public.thing to anon, authenticated;
grant all    on public.thing to service_role;

drop policy if exists "thing: public reads published" on public.thing;
create policy "thing: public reads published"
  on public.thing for select
  to anon, authenticated
  using (status = 'published');

drop policy if exists "thing: service_role full access" on public.thing;
create policy "thing: service_role full access"
  on public.thing for all
  to service_role
  using (true) with check (true);

notify pgrst, 'reload schema';

RPCs

A function the API can call needs security definer only when it must bypass RLS, and then it needs a pinned search_path or it is an escalation path:

create or replace function public.increment_counter(p_slug text, p_kind text)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  update public.thing
     set payload = jsonb_set(
           payload, array['counts', p_kind],
           to_jsonb(coalesce((payload->'counts'->>p_kind)::int, 0) + 1)),
         updated_at = now()
   where slug = p_slug;
end;
$$;

revoke all on function public.increment_counter(text, text) from public;
grant execute on function public.increment_counter(text, text) to anon, authenticated;

revoke ... from public first, then grant to the roles that need it. Creating a function grants execute to public by default, which is almost never what you want for a security definer.

Failure modes, and what they actually mean

Before it runs against production

• Read the whole file top to bottom, out of the diff.
• Confirm nothing is destructive: no bare drop table, no alter column ... type without a using, no delete from without a where.
• Confirm it ends with the notify line.
• Run it against a scratch database first if one exists.
• Never run it as part of the same step that writes it.

Files

• scripts/verify.sh — post-migration verification: table exists, RLS on, policies present, and the anon role can actually read what it should.