# Supabase RLS Policy & Schema Pattern Library

**18 production-ready patterns. Special focus: the 8 RLS mistakes AI tools get wrong.**

---

## What's in this pack

```
ai-gaps/        8 patterns where Claude, ChatGPT, and Copilot produce structurally wrong code
multi-tenant/   4 patterns for row-level org/team isolation
jwt-claims/     2 patterns for custom JWT claim–based access
storage/        4 patterns for Supabase Storage bucket policies
```

### Multi-tenant patterns (multi-tenant/)

| # | Pattern | What it solves |
|---|---------|----------------|
| 01 | Tenant SELECT isolation | Basic org-scoped read — the foundational pattern |
| 02 | Tenant INSERT enforcement | WITH CHECK gate for writes; blocks cross-tenant inserts |
| 03 | Admin cross-tenant access | app_metadata role grants visibility across all orgs |
| 04 | Soft delete visibility | deleted_at + partial index + restore vulnerability closed |

### JWT custom claims patterns (jwt-claims/)

| # | Pattern | What it solves |
|---|---------|----------------|
| 01 | Custom role from JWT | Safe extraction via coalesce helper; viewer/editor/admin gating |
| 02 | Org-scoped access via JWT | Embed org_id in token; no DB lookup per RLS eval |

### Supabase Storage patterns (storage/)

| # | Pattern | What it solves |
|---|---------|----------------|
| 01 | User private bucket | Per-user prefix isolation; all four CRUD policies |
| 02 | Team shared storage | Org-scoped files; member-upload ownership on DELETE |
| 03 | Public read, private write | Role-gated upload to a public CDN bucket |
| 04 | Signed URL access | Server-side time-limited links; RLS scope explained |

Each pattern includes:
- A `.sql` file — drop-in policy, tested on PostgreSQL 15+ / Supabase
- A `.md` file — what the pattern does, why the AI gets it wrong, and what to watch for

---

## The AI-gap patterns (ai-gaps/)

These are the 8 cases where AI code generation produces code that looks plausible but is structurally incorrect. The failures are subtle: they compile without errors, return data (or nothing) without throwing, and only reveal themselves in production when a real security audit or an edge-case user exposes them.

| # | Pattern | AI failure mode |
|---|---------|----------------|
| 01 | Anon-role omission | Policy applies to all roles silently; anon behaviour is undefined |
| 02 | SECURITY DEFINER bypass | Helper function runs as superuser, RLS is skipped entirely |
| 03 | Permissive policy broadening | Second policy widens access instead of narrowing it |
| 04 | NULL equality for shared resources | `= NULL` always evaluates to NULL, never true |
| 05 | INSERT without WITH CHECK | USING is ignored for INSERT; any user_id can be written |
| 06 | UPDATE without WITH CHECK | Row ownership can be transferred to any user |
| 07 | EXISTS without composite index | Correct logic, catastrophic performance at scale |
| 08 | JWT claim extraction (unsafe) | Missing claim throws cast error or evaluates unpredictably |

---

## Quick start

1. Copy the `.sql` file for the pattern you need.
2. Replace table name, column names, and role references to match your schema.
3. Run in Supabase SQL editor or via `supabase db push` with a migration.
4. Read the companion `.md` for caveats and testing guidance.

All patterns assume:
- Supabase Auth (`auth.uid()`, `auth.jwt()`, `auth.role()`)
- PostgreSQL 15+
- RLS enabled on the table (`ALTER TABLE your_table ENABLE ROW LEVEL SECURITY`)

---

## License

Single-user license. Use in your own projects, including commercial projects. Do not resell or redistribute the files themselves.