Supabase Backend skill for Claude Code

Supabase exposes your Postgres schema directly to clients, so the database is the security boundary. A sound project has RLS enabled on every table in exposed schemas, policies that are indexed and tested, secret keys used only on servers, schema changes in versioned migrations, and types regenerated after every migration.

Workflow

- [ ] 1. Inspect: supabase/ folder, config.toml, migrations, which schemas are exposed, client key usage
- [ ] 2. Run locally: supabase start (Docker); link the remote project
- [ ] 3. Schema change as a migration; RLS and policies in the same migration
- [ ] 4. Test policies as anon, as user A, as user B
- [ ] 5. Regenerate types; update client code
- [ ] 6. Run advisors (security and performance); push migrations
- [ ] 7. Edge Functions and storage policies as needed

CLI basics

supabase init
supabase start                                  # local Postgres, Auth, Storage, Studio
supabase link --project-ref <ref>
supabase migration new add_projects             # creates supabase/migrations/<ts>_add_projects.sql
supabase db reset                               # rebuild local DB from migrations + seed.sql
supabase db diff -f add_projects                # generate a migration from changes made in local Studio
supabase db push                                # apply pending migrations to the linked project
supabase gen types typescript --local > src/lib/database.types.ts
supabase db lint                                # schema linter (plpgsql issues)

Never edit the production schema in the dashboard without capturing it as a migration (supabase db pull then commit); environments drift otherwise. db push against production requires user confirmation.

RLS: the core of Supabase security

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users (id) on delete cascade default auth.uid(),
  org_id uuid not null references public.orgs (id),
  name text not null check (char_length(name) between 1 and 200),
  created_at timestamptz not null default now()
);

alter table public.projects enable row level security;

create index projects_owner_id_idx on public.projects (owner_id);
create index projects_org_id_idx on public.projects (org_id);

create policy "owners read their projects" on public.projects
  for select to authenticated
  using ((select auth.uid()) = owner_id);

create policy "owners insert projects for themselves" on public.projects
  for insert to authenticated
  with check ((select auth.uid()) = owner_id);

create policy "owners update their projects" on public.projects
  for update to authenticated
  using ((select auth.uid()) = owner_id)
  with check ((select auth.uid()) = owner_id);

create policy "owners delete their projects" on public.projects
  for delete to authenticated
  using ((select auth.uid()) = owner_id);

Rules:

• enable row level security on every table in public (and any schema exposed via the Data API). RLS enabled with no policies means no access, which is a safe default.
• One policy per operation (select, insert, update, delete) with an explicit role (to authenticated); avoid for all except for simple cases.
• using filters existing rows; with check validates new row values. Updates need both, or users can move rows to another owner.
• Wrap auth.uid() and auth.jwt() in (select ...) so Postgres evaluates them once per query instead of per row, and index every column used in policies.
• Team/org membership: check through a security definer function in a non-exposed schema (e.g. private.is_org_member(org_id)), marked stable, with set search_path = '', to avoid recursive policy evaluation on the membership table.
• Never base authorization on raw_user_meta_data (users can edit it); use raw_app_meta_data or your own tables, or custom claims via an auth hook.
• Views bypass RLS by default; create them with (security_invoker = true) (Postgres 15+) or keep them out of exposed schemas.
• security definer functions in exposed schemas run with owner rights for anyone who can call them; revoke execute from anon/authenticated unless intended.

Deeper multi-tenant patterns and RLS performance analysis: see the row-level-security skill.

Testing policies

-- in psql or the SQL editor against local
begin;
set local role authenticated;
select set_config('request.jwt.claims', json_build_object('sub', '<user-a-uuid>', 'role', 'authenticated')::text, true);
select count(*) from public.projects;      -- only user A's rows
insert into public.projects (name, org_id, owner_id) values ('x', '<org>', '<user-b-uuid>');   -- must fail
rollback;

Automate with pgTAP (supabase test db runs tests in supabase/tests/) or integration tests that sign in two users with the JS client and assert cross-access fails.

API keys

Key	Where	Bypasses RLS
Publishable key (sb_publishable_..., formerly anon)	Browsers, mobile apps	No
Secret key (sb_secret_..., formerly service_role)	Servers, Edge Functions, CI only	Yes

Never ship the secret key to clients or NEXT_PUBLIC_* variables. Server code acting on behalf of a user should use the user’s session (RLS applies), not the secret key.

Auth in server frameworks

// Next.js App Router: lib/supabase/server.ts
import { createServerClient } from "@supabase/ssr";
import { cookies } from "next/headers";

export async function createClient() {
  const cookieStore = await cookies();
  return createServerClient(process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!, {
    cookies: {
      getAll: () => cookieStore.getAll(),
      setAll: (toSet) => { try { toSet.forEach(({ name, value, options }) => cookieStore.set(name, value, options)); } catch {} },
    },
  });
}

• Refresh sessions in middleware with the same getAll/setAll pattern.
• On the server, trust supabase.auth.getUser() (verifies with Auth) or getClaims() (verifies the JWT signature with asymmetric signing keys), not getSession() alone, which reads unverified cookie data.
• Configure redirect URLs and email templates per environment; enable leaked-password protection and MFA where appropriate.

Storage

insert into storage.buckets (id, name, public) values ('avatars', 'avatars', false);

create policy "users manage files in their folder" on storage.objects
  for all to authenticated
  using (bucket_id = 'avatars' and (storage.foldername(name))[1] = (select auth.uid())::text)
  with check (bucket_id = 'avatars' and (storage.foldername(name))[1] = (select auth.uid())::text);

Upload to avatars/<user-id>/<file>; serve private files with createSignedUrl(path, 60). Set bucket file size limits and allowed MIME types in bucket settings.

Edge Functions

// supabase/functions/stripe-webhook/index.ts
Deno.serve(async (req) => {
  const body = await req.text();             // raw body for signature verification
  // verify signature, then use a secret-key client for privileged writes
  return new Response("ok");
});

supabase functions new stripe-webhook
supabase functions serve                         # local, with .env via --env-file
supabase secrets set STRIPE_WEBHOOK_SECRET=whsec_...
supabase functions deploy stripe-webhook --no-verify-jwt   # only for webhooks that sign requests themselves

Functions verify the caller’s JWT by default; disable that only when the function authenticates requests another way.

Gotchas

• Tables created via SQL in public without RLS are readable and writable by anyone with the publishable key. The security advisor flags this; act on it.
• on delete cascade from auth.users deletes user data when an account is removed; decide deliberately.
• PostgREST returns at most the configured max rows (default 1000); paginate with range().
• Realtime respects RLS for Postgres Changes only on tables added to the publication with RLS enabled; test with a second user.
• Connection strings: use the pooler (transaction mode) for serverless clients and session mode or direct connections for migrations.

Verification

1. supabase db reset rebuilds locally from migrations without errors.
2. Policy tests pass: anon sees nothing private, user A cannot read or write user B’s rows or storage objects.
3. Security and performance advisors (dashboard Advisors, or supabase db lint) show no tables without RLS and no unindexed policy columns.
4. Regenerated types compile with the app (tsc --noEmit).