---
description: "Write complete Supabase Row Level Security policies for a table from its schema. Generates ENABLE ROW LEVEL SECURITY plus all CRUD policies using the correct auth.uid() patterns, role scoping, and WITH CHECK clauses."
alwaysApply: true
---

# Supabase RLS Policy Builder

> **Purpose:** Given a table schema and description of who can access what, write the complete set of production-ready Supabase RLS policies. The output is paste-ready SQL — no gaps, no USING (true) shortcuts on sensitive data.

---

## Invocation

```
/rls-build <table-schema> [access-rules]
```

**Examples:**
- `/rls-build CREATE TABLE skill_reports (id uuid, skill_id uuid, reporter_id uuid, reason text, status text) -- Users can create reports on any active skill; only admins can read all reports; reporters can see their own`
- `/rls-build skill_collections table -- users own their collections; collections with is_public=true are readable by anyone`

---

## Access Pattern Library

Use these patterns as building blocks. Pick the right one for each operation:

### Pattern A: User owns row (most common)
```sql
-- User sees only their own rows
CREATE POLICY "[table]_select_own" ON public.[table]
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

-- User can only insert rows they own
CREATE POLICY "[table]_insert_own" ON public.[table]
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- User can only update their own rows
CREATE POLICY "[table]_update_own" ON public.[table]
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- User can only delete their own rows
CREATE POLICY "[table]_delete_own" ON public.[table]
  FOR DELETE TO authenticated
  USING (auth.uid() = user_id);
```

### Pattern B: Public read, owner write
```sql
-- Anyone (including anon) can read active rows
CREATE POLICY "[table]_select_public" ON public.[table]
  FOR SELECT
  USING (status = 'active');

-- Only the owner can update/delete
CREATE POLICY "[table]_update_own" ON public.[table]
  FOR UPDATE TO authenticated
  USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);
```

### Pattern C: Read own + read public
```sql
-- Users see their own rows OR public rows
CREATE POLICY "[table]_select" ON public.[table]
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id OR is_public = true);
```

### Pattern D: Write to parent-owned resource
Used when a child row's ownership is determined via a parent JOIN (e.g., inserting a comment on a skill you own):
```sql
CREATE POLICY "[table]_insert_to_own_skill" ON public.[table]
  FOR INSERT TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.skills
      WHERE id = skill_id AND author_id = auth.uid()
    )
  );
```

### Pattern E: Authenticated users can create, see their own
Common for reports, notifications, submissions:
```sql
CREATE POLICY "[table]_insert" ON public.[table]
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = submitter_id);

CREATE POLICY "[table]_select_own" ON public.[table]
  FOR SELECT TO authenticated
  USING (auth.uid() = submitter_id);
-- No UPDATE or DELETE — immutable after submission
```

### Pattern F: Service role for server-side operations
Use when the server (not the user) writes to a table:
```sql
-- Server can do anything (bypasses RLS via service role key)
CREATE POLICY "[table]_service" ON public.[table]
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');
-- Document why service role is needed above this policy
```

### Pattern G: Admin-only table
```sql
-- Read: only if user is in admins list (example: users.is_admin column)
CREATE POLICY "[table]_admin_select" ON public.[table]
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE id = auth.uid() AND is_admin = true
    )
  );
```

---

## Full Template (User-Owned Table)

```sql
-- RLS for [table]
ALTER TABLE public.[table] ENABLE ROW LEVEL SECURITY;

-- [Brief description of who accesses what]

CREATE POLICY "[table]_select_own" ON public.[table]
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "[table]_insert_own" ON public.[table]
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "[table]_update_own" ON public.[table]
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "[table]_delete_own" ON public.[table]
  FOR DELETE TO authenticated
  USING (auth.uid() = user_id);
```

---

## Critical Rules

1. **Never use `USING (true)` on INSERT** — INSERT policies need `WITH CHECK`, not `USING`
2. **UPDATE requires BOTH `USING` and `WITH CHECK`** — `USING` checks old row, `WITH CHECK` checks new row
3. **`auth.uid()` vs `auth.role()`** — uid() returns the user's UUID, role() returns 'anon'/'authenticated'/'service_role'
4. **Don't mix ALL with specific operations** — use `FOR SELECT`, `FOR INSERT` etc. individually for clarity
5. **Test anon access** — if anon role should NOT see data, add explicit denial or ensure no policy grants anon access

---

## Output Format

1. **Complete SQL block** — `ENABLE ROW LEVEL SECURITY` + all policies
2. **Policy rationale** — one-line comment above each policy explaining WHY
3. **Gaps identified** — call out any operations not covered (e.g., "No DELETE policy = users cannot delete their reports")