---
name: supabase-schema-discipline
description: Rules for Supabase schema changes, structure plus GRANTs plus RLS plus policies in one migration, idempotency keys on side-effect tables, and no destructive migration without explicit confirmation. Use whenever creating or altering tables, columns, policies, functions, or triggers. Not for the pre-ship diff gate (use rls-security-gate), money-flow review (use money-path-halt-go), or auditing an existing schema (use supabase-codebase-audit).
license: MIT
metadata:
  author: TechTide AI (Alex Cinovoj)
  provenance: original
  category: Quality & Security
---

# 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:

1. `CREATE TABLE` / `ALTER TABLE` (structure)
2. `GRANT` statements (the Data API does not grant by default)
3. `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`
4. `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:

```sql
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:

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