---
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.