supabase-postgres-patterns v1.0.0

name: "supabase-postgres-patterns" description: "Production-grade Supabase + Postgres discipline: RLS policies done right, migration workflow via the CLI, edge function hygiene, and backup/recovery basics. Triggers when an agent sets up a Supabase database, writes policies, changes schema, deploys edge functions, or reviews a Supabase project's health."

Supabase + Postgres Patterns

Purpose

Supabase is Postgres with batteries. Most projects fail not on Postgres itself but on the Supabase-specific surface: tables shipped without RLS, schema edited in the dashboard with no migration trail, secrets in client bundles, service_role keyed functions written where RLS would do, and no backup story until something breaks. This skill is the production checklist and the correct commands for each of those.

Workflow

1. Harden access before shipping any table

RLS on, explicit policies, explicit grants. Never rely on client-side filtering.

sql
-- New table, full lockdown pattern
create table todos (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id),
  title text not null,
  done boolean not null default false,
  created_at timestamptz not null default now()
);

alter table todos enable row level security;

-- Opt-in grants (new projects since 2026-05-30 require explicit GRANTs;
-- enforced on all projects 2026-10-30). Old default: new public tables were
-- auto-granted to anon/authenticated. Don't rely on the old behavior.
alter default privileges for role postgres in schema public
  revoke select, insert, update, delete on tables from anon, authenticated, service_role;
grant select, insert, update, delete on todos to authenticated;

One policy per operation. SELECT gets using, INSERT gets with check, UPDATE gets both (using gates the existing row, with check gates the resulting row so a user cannot reassign user_id to someone else), DELETE gets using. Never use for all just to save lines; it hides which rule covers which operation.

sql
create policy "Users see their own todos"
  on todos for select
  to authenticated
  using ( (select auth.uid()) = user_id );

create policy "Users create their own todos"
  on todos for insert
  to authenticated
  with check ( (select auth.uid()) = user_id );

create policy "Users update their own todos"
  on todos for update
  to authenticated
  using ( (select auth.uid()) = user_id )
  with check ( (select auth.uid()) = user_id );

create policy "Users delete their own todos"
  on todos for delete
  to authenticated
  using ( (select auth.uid()) = user_id );

The (select auth.uid()) wrapper matters: it lets the planner cache the value once per statement instead of evaluating per row. Scope every policy to authenticated (or the right role) so anon callers short-circuit before the predicate runs. Index every column a policy predicate filters (create index on todos (user_id);); policy predicates are query predicates. auth.uid() is null when unauthenticated, so policies fail closed.

Verify with a query, not a feeling. See references/rls-patterns.md.

2. Run schema through migrations, never the dashboard

The dashboard SQL editor is an emergency fallback, not a workflow. Migrations are the source of truth.

bash
supabase login
supabase link --project-ref <project-ref>

supabase migration list                 # local vs remote state, always first
supabase migration new add_due_date_to_todos
# write the SQL in supabase/migrations/<timestamp>_add_due_date_to_todos.sql
supabase db push --dry-run              # preview exactly what will run
supabase db push                        # apply pending migrations
supabase migration list                 # confirm local and remote agree
supabase db lint --linked               # lint the linked remote schema

Rules: one migration does one thing (a column, an index, a backfill, never all three). Migrations are append-only and transactional; never edit or rename an applied migration unless you are repairing drift, and then rename the file to match the remote version exactly. Name migrations by what they do (add_due_date_to_todos), not migration_42_final. Destructive changes (drop column, change type, not null without a default) are breaking changes for old clients; they go in a planned multi-step sequence, never a routine push. If you must capture dashboard changes, supabase db diff -f <name> turns them into a file; supabase db reset replays everything locally plus supabase/seed.sql.

3. Put privileged logic in edge functions, keep the secrets there

Edge functions run Deno server-side. They are the only place for third-party keys and service_role work. The client gets the publishable/anon key (RLS-gated); it never sees service_role.

Available automatically in every function: SUPABASE_URL, SUPABASE_ANON_KEY, SUPABASE_SERVICE_ROLE_KEY (bypasses RLS, server only), SUPABASE_DB_URL. Set your own via supabase secrets set NAME=value (values land immediately, no redeploy; limit 100 secrets, names cannot start with SUPABASE_). Locally: supabase/functions/.env is auto-loaded by supabase start, or supabase functions serve --env-file .env.local.

Two client patterns inside a function:

typescript
import { createClient } from "npm:@supabase/supabase-js@2";

Deno.serve(async (req) => {
  // Acts AS the caller: RLS still applies. Default path for reads/writes.
  const userClient = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_ANON_KEY")!,
    { global: { headers: { Authorization: req.headers.get("Authorization")! } } },
  );

  // Acts AS ADMIN: bypasses RLS. Only after you verify the caller yourself.
  const adminClient = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
  );

  const { data: { user } } = await userClient.auth.getUser();
  if (!user) return new Response("Unauthorized", { status: 401 });
  // ... privileged work with adminClient here ...
  return Response.json({ ok: true });
});

Defaults: verify_jwt = true means the platform already validated the caller's JWT; set verify_jwt = false in config.toml (or --no-verify-jwt at deploy) only for public endpoints like webhooks, and then verify the webhook signature yourself. Handle the OPTIONS preflight for browser calls; the full CORS template lives in references/edge-function-template.md.

4. Have a backup story before launch

Paid plans: enable Point-in-Time Recovery in Project Settings -> Database -> Backups. Pro retains 7 days, Team 14, Enterprise up to 30. PITR restores spin up a NEW instance (the original is untouched; compare before you cut over), then update connection strings. RPO is seconds thanks to continuous WAL archiving.

Free tier or belt-and-suspenders: logical dumps.

bash
pg_dump "$DATABASE_URL" > backup_$(date +%Y%m%d_%H%M%S).sql
pg_dump --schema-only "$DATABASE_URL" > schema_$(date +%Y%m%d).sql
pg_dump --data-only "$DATABASE_URL" > data_$(date +%Y%m%d).sql
pg_dump "$DATABASE_URL" --exclude-table-data=big_log_table > lean.sql

Take a manual dump before every risky migration, name it with the migration, and confirm a restore once by loading it into a scratch database. An untested backup is a rumor. See references/backup-runbook.md.

Output Contract

• Given a new Supabase project: tables created with RLS enabled, per-operation policies using (select auth.uid()), role-scoped, opt-in grants, indexed policy columns; all schema changes as CLI migrations, none via dashboard; secrets only in edge functions; backup plan documented (PITR enabled or dump cadence in place).
• Given an existing project: a gap report listing tables without RLS, policies missing with check, service_role usage reachable from client code, dashboard-made schema drift (migration list disagrees), and missing backup config. Each gap gets the exact fix.

Operating Rules

1. RLS before client code. No table exposed to the client ships without enable row level security plus policies first. A table with RLS on and zero policies denies everything; that is the safe default, not a bug.
2. Never invent policy semantics. using filters existing rows, with check validates proposed rows. If you are unsure which one a write path needs, write both.
3. Service role never leaves the server. Not in a frontend bundle, not in a public repo, not in a client-side env file. If you need elevated logic clients can call, it goes in an edge function (verify the caller, then use admin) or a security definer Postgres function with a locked-down search_path.
4. Migrations are the audit trail. Every schema change is a file in supabase/migrations/ with a descriptive name. db push --dry-run before every push.
5. Keep policy SQL and function code in the skill's sources; summarize, don't paste docs. Every claim in this skill was verified against Supabase docs or production runbooks, with URLs in the sources file.