Supabase Backend skill for Codex

Done well means: every schema change is a migration in supabase/migrations, every table in an exposed schema has RLS enabled with policies tested as real users, server code authorizes from a verified token (never an unverified cookie session), and the secret key exists only in server environments.

Inspect first

1. Read AGENTS.md; check for supabase/config.toml, supabase/migrations/, supabase/functions/ and supabase/seed.sql.
2. Find client creation: @supabase/supabase-js (createClient) and, for SSR frameworks, @supabase/ssr (createServerClient, createBrowserClient).
3. Note which keys are in use: newer projects use publishable (sb_publishable_...) and secret (sb_secret_...) keys; older ones use the anon and service_role JWT keys. Publishable/anon keys are safe in browsers only because RLS protects the data.
4. Grep for the secret or service role key in client bundles: rg -n "service_role|sb_secret_|SUPABASE_SERVICE" --glob '!supabase/**'.

Local workflow

supabase start                       # local stack in Docker (needs Docker; first run pulls images)
supabase migration new add_projects  # empty SQL file in supabase/migrations
supabase db reset                    # rebuild local DB from migrations + seed.sql
supabase db diff -f add_projects     # alternative: diff schema changes made in Studio into a migration
supabase gen types typescript --local > src/lib/database.types.ts
supabase test db                     # pgTAP tests in supabase/tests

• Pulling images and supabase link/db push/functions deploy need network; in a sandbox without it, ask the user to run them.
• supabase db push applies migrations to the linked remote project, and supabase db reset --linked wipes it: both require explicit user confirmation, naming the project ref.
• Regenerate types after every migration and commit them.

Schema and RLS

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 120),
  created_at timestamptz not null default now()
);
create index on public.projects (owner_id);
create index on public.projects (org_id);

alter table public.projects enable row level security;

create policy "members read org projects" on public.projects
  for select to authenticated
  using (org_id in (select private.user_org_ids()));

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

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

create schema if not exists private;
create or replace function private.user_org_ids()
returns setof uuid
language sql stable security definer set search_path = ''
as $$ select org_id from public.org_members where user_id = (select auth.uid()) $$;

Rules:

• Enable RLS on every table in public (or any schema exposed through the Data API). A table without RLS is readable and writable with the publishable key.
• Wrap auth.uid() and auth.jwt() in (select ...) so Postgres evaluates them once per query instead of per row. Index every column a policy filters on.
• Specify the role (to authenticated) and the command on each policy; avoid for all.
• update needs both using and with check, or users can move rows to another owner.
• Membership lookups across tables go through security definer functions in a schema not exposed by the API, with set search_path = '' and fully qualified names.
• Never base authorization on raw_user_meta_data (users can edit it); use raw_app_meta_data claims set server-side, or a membership table.
• Views bypass RLS by default; create them with (security_invoker = true) (Postgres 15+).
• Deep RLS design and performance work: $postgres-row-level-security.

Auth in SSR apps (Next.js example)

// 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: (list) => {
        try { list.forEach(({ name, value, options }) => cookieStore.set(name, value, options)); }
        catch { /* called from a Server Component; middleware refreshes the session */ }
      },
    },
  });
}

• Use only getAll/setAll cookie methods with current @supabase/ssr.
• Refresh sessions in middleware (or the framework’s equivalent) on every request.
• Authorize on the server with supabase.auth.getClaims() (verifies the JWT, fast with asymmetric signing keys) or supabase.auth.getUser() (round trip to Auth, freshest user state). Never trust getSession() on the server: it reads cookies without verification.
• Use environment variable names the project already has; the publishable key may be named ..._ANON_KEY in older setups.
• OAuth and magic links need redirect URLs allow-listed in the dashboard or config.toml.

Storage

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

create policy "users manage own avatar 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);

• Store objects under <user_id>/... or <org_id>/... so policies can check the path.
• Private buckets plus signed URLs (createSignedUrl(path, 3600)) for user content; public buckets only for truly public assets.
• Set bucket file size limits and allowed MIME types.

Edge Functions

// supabase/functions/send-invite/index.ts
import { createClient } from "npm:@supabase/supabase-js@2";

Deno.serve(async (req) => {
  const authHeader = req.headers.get("Authorization") ?? "";
  const userClient = createClient(Deno.env.get("SUPABASE_URL")!, Deno.env.get("SUPABASE_ANON_KEY")!, {
    global: { headers: { Authorization: authHeader } },
  });
  const { data: { user }, error } = await userClient.auth.getUser();
  if (error || !user) return new Response("Unauthorized", { status: 401 });
  // ... validate input, act as the user (RLS applies) ...
  return Response.json({ ok: true });
});

supabase functions new send-invite
supabase functions serve                     # local
supabase secrets set RESEND_API_KEY=...      # user runs this; never echo secrets in chat
supabase functions deploy send-invite        # confirm with the user before deploying

• Act as the calling user (forward the Authorization header) so RLS applies. Use the secret/service key only for admin tasks, after your own authorization check.
• Check the injected environment variable names for your project’s key type; default names have changed across key systems.
• Handle CORS (OPTIONS preflight) for browser callers.
• Webhooks: verify signatures; Edge Functions with JWT verification disabled are public.

Audit checklist

• select tablename from pg_tables where schemaname = 'public' and not rowsecurity; returns nothing.
• No policy uses using (true) on tables with private data, for any role.
• Secret/service key absent from client code and NEXT_PUBLIC_*/VITE_* variables.
• Security definer functions set search_path and live outside exposed schemas.
• Storage buckets with user content are private.
• Supabase advisors (Dashboard, or supabase db lint locally) show no security warnings.

Verification

• pgTAP or integration tests sign in as two different users and assert each sees only its own rows and cannot insert into the other’s org.
• supabase db reset rebuilds cleanly from migrations; generated types match.
• Edge Functions tested locally with supabase functions serve and a real user token.

Output

**Migrations:** supabase/migrations/<files>
**RLS:** <table: policies (role, command, rule)>
**Auth:** <server check used, middleware refresh>
**Storage:** <buckets, public/private, path policy>
**Functions:** <name, auth mode, secrets needed (names only)>
**Tests:** <two-user isolation tests, results>