# Pattern 13 — Custom role from JWT claims

## What this is

Extracting a custom per-user role from JWT `app_metadata` to gate write operations (viewer, editor, admin). The key detail: inline JWT extraction with type casts is the most common source of auth-layer runtime errors in Supabase apps.

## The unsafe cast (what AI tools generate)

```sql
-- Common AI-generated pattern — throws if role is absent or not castable
using (
  (auth.jwt()->>'role')::text = 'editor'
);

-- Also fails if app_metadata is not present in the token
using (
  (auth.jwt()->'app_metadata'->>'role')::text = 'editor'
);
```

When the token has no `app_metadata`, `auth.jwt()->'app_metadata'` returns `null`. The `->>` operator on a null jsonb value returns null. The cast to text on null returns null. The equality check returns null (not false). PostgreSQL treats null as "deny" — so the policy silently blocks the user rather than erroring — but the dependency on null-propagation is fragile and hard to reason about.

The safer pattern wraps this in a stable function with an explicit `coalesce`:

```sql
create or replace function get_user_role()
returns text language sql stable as $$
  select coalesce(
    auth.jwt() -> 'app_metadata' ->> 'role',
    'viewer'
  );
$$;
```

## Why a function, not inline SQL

- **Readable policies**: `get_user_role() = 'admin'` vs `(auth.jwt()->'app_metadata'->>'role')::text = 'admin'`
- **Single point of change**: update the extraction logic in one place
- **Testable in isolation**: call the function directly in SQL to debug claim extraction
- **`STABLE` marking**: PostgreSQL can cache the result within a statement

## app_metadata vs user_metadata

```sql
-- WRONG: user can set their own role by updating their profile
auth.jwt()->'user_metadata'->>'role'

-- CORRECT: requires service_role key to set
auth.jwt()->'app_metadata'->>'role'
```

## Testing

```sql
-- Viewer cannot insert
set request.jwt.claims = '{"sub": "<uid>", "app_metadata": {"role": "viewer", "org_id": "<org>"}}';
insert into documents (org_id, title) values ('<org-id>', 'test');
-- Expected: ERROR: new row violates row-level security policy

-- Editor can insert
set request.jwt.claims = '{"sub": "<uid>", "app_metadata": {"role": "editor", "org_id": "<org>"}}';
insert into documents (org_id, title) values ('<org-id>', 'test');
-- Expected: success

-- Missing role defaults to viewer
set request.jwt.claims = '{"sub": "<uid>", "app_metadata": {}}';
select get_user_role();
-- Expected: 'viewer'
```