SKILL.md

Supabase Schema Discipline

A schema change without its policy is a security incident waiting to happen. A migration without a GRANT is a runtime error. Both ship together or neither ships.

The paired-change rule

Every migration that creates or alters a public.* table includes, in this order, in the same file:

CREATE TABLE / ALTER TABLE (structure)
GRANT statements (the Data API does not grant by default)
ALTER TABLE ... ENABLE ROW LEVEL SECURITY
CREATE POLICY covering every access mode the app uses

Splitting these across migrations is forbidden. A table existing briefly without RLS is a leak window.

Default GRANT block, tuned to the policies:

GRANT SELECT, INSERT, UPDATE, DELETE ON public.<table> TO authenticated;
GRANT ALL ON public.<table> TO service_role;
-- Add ONLY if a policy intentionally allows anon reads:
-- GRANT SELECT ON public.<table> TO anon;

Money and PII tables get no anon grant, ever.

Policy authoring

auth.uid() for self-scoped rows.
public.has_role(auth.uid(), 'admin') for role checks. Never query a table inside its own policy, that recurses.
Roles live in a separate user_roles table with an app_role enum.
One policy per (operation, audience). No FOR ALL to save lines, it costs granularity.

Idempotency on side-effect tables

Any table recording money movement (charges, payouts, refunds, ledger entries) or external side effects (sent emails, queued jobs, webhook deliveries, third-party calls) gets:

idempotency_key text NOT NULL UNIQUE

or a composite UNIQUE constraint capturing the dedup key. Server code derives the key deterministically (stripe_event_id, or ${user_id}:${order_id}:${action}) and writes with INSERT ... ON CONFLICT DO NOTHING, so retries are safe.

Destructive migration rule

Destructive: DROP TABLE, DROP COLUMN, lossy ALTER COLUMN ... TYPE, TRUNCATE, renames that break existing reads, dropping a production index.

Do not run one without explicit user confirmation in chat that names the exact object, states what data is lost, and confirms a backup exists or the loss is accepted. Unconfirmed: propose and wait. Never assume.

For renames take the safe path: add new column, dual-write, backfill, switch reads, drop later after a confirmed quiet period.

Data vs schema

Schema (CREATE, ALTER, DROP, policies, functions, triggers): migrations.
Data (INSERT, UPDATE, DELETE of rows): the data path, never migrations.

Mixing them breaks reproducibility and confuses rollback.

Function and trigger rules

SECURITY DEFINER functions used in policies set search_path = public (or explicit), blocking search-path attacks.
Triggers touching money or sensitive tables get the same RLS and idempotency review as direct writes.
Secrets live in Vault, never in function bodies.

Good vs bad

Good: one migration file with CREATE TABLE public.invoices ..., the GRANT block, ENABLE ROW LEVEL SECURITY, and four policies, one per operation the app performs.

Bad: migration 0041 creates the table "to unblock the frontend", migration 0042 "adds security tomorrow". Between deploys, every row is exposed. Tomorrow also never comes.

Verification

Do this: before submitting, re-read the migration and confirm each CREATE TABLE block is followed in-file by GRANT, ENABLE RLS, and policies. Expect every table to have all four, side-effect tables to carry a UNIQUE dedup key, and zero data DML. If anything is missing, fix the migration before applying. After applying, regenerate types if the project uses generated Supabase types and update server functions that referenced the changed shape.

Completion checklist

[ ] Structure, GRANTs, RLS, and policies in one migration file, in order
[ ] Side-effect tables have idempotency_key UNIQUE or equivalent
[ ] No destructive op without logged user confirmation
[ ] No data DML in the migration
[ ] SECURITY DEFINER functions pin search_path
[ ] Types regenerated and dependents updated after apply

Any box unchecked: not done. Fix or say so.

Footguns

Forgetting GRANTs because "RLS is the security": without GRANTs the Data API returns permission errors even for allowed rows. GRANT and policy answer different questions.
Policy checking a role column on the same table: recursion at query time or a self-serve privilege escalation. Separate roles table, has_role helper.
Backfilling data inside the migration: replays and rollbacks now mutate rows. Keep DML out.
Idempotency key generated randomly per call: retries pass the UNIQUE check and duplicate the side effect. Derive it from stable inputs.

Red Flags: stop if you think or read any of these

"Ship the table now, policies in the next migration"
"FOR ALL is cleaner"
"It's a tiny rename, nobody reads that column"
"I'll confirm the drop with the user after it runs"
"One quick UPDATE in the migration won't hurt"

Each one means: apply the paired-change rule or the destructive rule in full. There is no table too small and no deadline too tight for the four-part migration.

supabase-schema-discipline

SKILL.md