Permission System — Design Document

Companion to ADR-006 which contains the full research, findings, and implementation context.

Overview

Replace the DB-queried permission grammar system with JWT-embedded role checks. The database remains the source of truth for role assignments. The JWT carries a snapshot of those assignments. Application code checks roles directly against JWT claims — no DB round trips for authorization.

Scope boundary: This project replaces permission checks only. It does not change auth.userId semantics, ownership comparisons, or resolveAppUserId() — those are deferred to a follow-up UUID unification project that will make users.id = auth.users.id.

JWT Claims (new shape)

The Custom Access Token Hook enriches app_metadata at token issuance:

{
  "sub": "supabase-auth-uuid",
  "aud": "authenticated",
  "role": "authenticated",
  "email": "user@example.com",
  "app_metadata": {
    "provider": "email",
    "providers": ["email"],
    "roles": [
      { "role": "STAFF", "scope_type": null, "scope_id": null },
      { "role": "STAFF", "scope_type": "location", "scope_id": "loc-uuid-1" }
    ]
  }
}

Field	Source	Purpose
`app_metadata.roles`	New — from `user_roles` table	Full role assignments including scopes

Removed: app_metadata.role (the legacy single-role string) is dropped. The roles array is the single source of truth. All code that reads app_metadata.role is updated to derive role information from the roles array.

Not embedded: app_user_id is not included in this phase. The JWT sub claim (Supabase auth UUID) remains the primary user identifier. Embedding the app UUID is deferred to the UUID unification project.

Custom Access Token Hook

Postgres function. Runs once per token issuance/refresh (~1 hour). Queries users (to resolve auth UUID → app UUID for the join) and user_roles.

CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb LANGUAGE plpgsql STABLE AS $$
DECLARE
  claims jsonb;
  app_uid uuid;
  user_roles_json jsonb;
BEGIN
  claims := event->'claims';

  SELECT id INTO app_uid FROM public.users
  WHERE auth_user_id = (event->>'user_id')::uuid
  AND deleted_at IS NULL;

  IF app_uid IS NOT NULL THEN
    SELECT COALESCE(jsonb_agg(jsonb_build_object(
      'role', ur.role,
      'scope_type', ur.scope_type,
      'scope_id', ur.scope_id
    )), '[]'::jsonb) INTO user_roles_json
    FROM public.user_roles ur
    WHERE ur.user_id = app_uid AND ur.deleted_at IS NULL;

    claims := jsonb_set(claims, '{app_metadata,roles}', user_roles_json);
  END IF;

  event := jsonb_set(event, '{claims}', claims);
  RETURN event;
END;
$$;

The users lookup is necessary because user_roles.user_id references users.id (app UUID), but the hook receives the Supabase auth UUID. This indirection goes away after UUID unification.

Security grants:

GRANT EXECUTE ON FUNCTION public.custom_access_token_hook TO supabase_auth_admin;
REVOKE EXECUTE ON FUNCTION public.custom_access_token_hook FROM authenticated, anon, public;
GRANT SELECT ON TABLE public.users TO supabase_auth_admin;
GRANT SELECT ON TABLE public.user_roles TO supabase_auth_admin;

Local dev: uncomment [auth.hook.custom_access_token] in supabase/config.toml. Production: enable in Supabase Dashboard → Authentication → Hooks.

Role-Check API

New module: apps/api/src/utils/auth/role-check.ts

All functions are synchronous, pure, and take JWT claims as input. Zero DB queries.

type RoleClaims = {
  roles: Array<{ role: string; scope_type: string | null; scope_id: string | null }>
}

// Core check: does user have this role, optionally at this location?
function hasRole(claims: RoleClaims, role: Role, locationId?: string): boolean

// Convenience checks
function isAdmin(claims: RoleClaims): boolean
function isStaff(claims: RoleClaims): boolean
function isStaffAt(claims: RoleClaims, locationId: string): boolean
function isCommunityManager(claims: RoleClaims): boolean
function isCommunityManagerAt(claims: RoleClaims, locationId: string): boolean
function isStaffOrAbove(claims: RoleClaims): boolean // ADMIN || STAFF || CM
function isStaffOrAboveAt(claims: RoleClaims, locationId: string): boolean

// Utility
function getScopedLocationIds(claims: RoleClaims): string[] // extract location IDs from scoped roles
function hasGlobalRole(claims: RoleClaims): boolean // any role with scope_type === null

hasRole semantics:

ADMIN matches everything (global wildcard)
Without locationId: checks for a global role (scope_type === null)
With locationId: checks for a scoped role at that location OR a global role (global STAFF can do anything a location-scoped STAFF can)

PARTNER role is handled by hasRole — no convenience function needed since no route gates on PARTNER specifically. PARTNER users fall through to ownership checks like USER.

Auth flow (new)

Request arrives
  → validateAuthentication(req)
    → verify JWT signature via Supabase SDK (0ms, cached JWKS)
    → extract claims.app_metadata.roles from JWT (0ms)
    → resolveAppUserId(authUserId) → DB lookup (40ms, stays until UUID unification)
    → store audit context with app_user_id
    → return { userId: authUserId, email, claims, isAuthenticated }

Route handler
  → read claims from auth result (0ms)
  → role check: isAdmin(claims) or isStaffOrAboveAt(claims, locationId) (0ms)
  → if own-vs-any: ownership check uses auth.userId === entity.user.authUserId (unchanged)
  → business logic

Key change: validateAuthentication returns claims alongside userId. Routes pass claims to role-check functions instead of passing userId to hasPermission(). The auth.userId value and ownership check mechanism stay unchanged.

Route patterns (before → after)

Admin-only route:

// Before
const canDo = await hasPermission(auth.userId || '', 'credits.adjust.any', reqPrisma)
if (!canDo) return forbidden()

// After
if (!isAdmin(auth.claims)) return forbidden()

Staff route:

// Before
const canDo = await hasPermission(auth.userId || '', 'users.list.any', reqPrisma)

// After
if (!isStaffOrAbove(auth.claims)) return forbidden()

Location-scoped route:

// Before
const canCancel = await checkEntityPermission(
  auth.userId || '',
  'reservations.cancel.any',
  { locationId: reservation.resource?.locationId },
  tx
)

// After
if (!isStaffOrAboveAt(auth.claims, reservation.resource.locationId)) return forbidden()

Own-vs-any route:

// Before
const isOwn = reservation.user?.authUserId === auth.userId
const perm = isOwn ? 'reservations.cancel.own' : 'reservations.cancel.any'
const canCancel = await checkEntityPermission(auth.userId || '', perm, { locationId, resourceId }, tx)

// After
const isOwn = reservation.user?.authUserId === auth.userId // unchanged — still auth UUID comparison
if (!isOwn && !isStaffOrAboveAt(auth.claims, reservation.resource.locationId)) return forbidden()

Dashboard route (CM-specific):

// Before
const canDo = await hasPermission(auth.userId || '', 'dashboard.stats.view.any', reqPrisma)

// After
if (!isAdmin(auth.claims) && !isCommunityManager(auth.claims)) return forbidden()
// Note: STAFF does NOT have dashboard access

Staleness

Event	Staleness	Mitigation
Normal requests	None	Read claims
Admin changes user's role	≤1 hour	Force token refresh via `supabase.auth.admin.updateUserById()` (already called on role change)
User deleted/banned	≤1 hour	Can force session invalidation if needed

What gets removed

Component	Reason
`PermissionCatalog.ts`	Grammar, parsing, validation — replaced by direct role checks
`entity-permission-check.ts`	`checkEntityPermission`, `checkOwnershipPermission` — replaced by `isStaffOrAboveAt()` + ownership check
`require-permission.ts` middleware	Dead code, never used
`permission-check.ts`	`hasPermission()`, role maps, wildcard matching — replaced by `role-check.ts`
`permission-helpers.ts`	Dead code, never used
`PermissionService` (entire file)	`processPurchasePermissions`, `grantResourcePermission`, etc. — all redundant
`user_permissions` writes from Stripe webhooks	Grants are redundant with USER role; `resources.view.any` is an antipattern
`app_metadata.role` field	Replaced by `roles` array; no backward compatibility maintained
`users/[id]/role/route.ts` (PATCH)	Dead endpoint — zero frontend callers, writes the removed `app_metadata.role` field
`users/[id]/permissions/**` (entire directory)	Admin grant/revoke/view — no longer meaningful without the permission system
Frontend permission management UI	`UserPermissionManager`, `GrantPermissionModal`, `useUserPermissions` hook — never used, endpoints removed
JWT utility functions for `app_metadata.role`	`hasAppMetadataRole()`, `extractRoleFromClaims()`, `validateJwtClaims()` role check — removed or updated

What stays

Component	Reason
`user_roles` table	Source of truth for role assignments
`user_permissions` table (schema only)	No schema migration to drop it — stop reading/writing
Admin role management UI + API (`users/[id]/roles`)	Roles still assigned via `POST /api/users/[id]/roles`
Impersonation flow	Stays DB-based (must verify session validity)
Test mode (`API_TEST_MODE`, `x-test-*` headers)	Still needed for dev/test, updated to work with claims
`resolveAppUserId()`	Still needed for audit context (deferred to UUID unification)
`auth.userId` as Supabase auth UUID	Unchanged (deferred to UUID unification)
Ownership checks via `entity.user.authUserId === auth.userId`	Unchanged (deferred to UUID unification)

Edge cases

Impersonation: The impersonation flow resolves the target user from DB (needs to verify session, look up target). After resolution, the impersonated user's roles are loaded from user_roles in DB since we don't have their JWT. The permission gate (security.impersonate.any) changes to isAdmin(claims) || isCommunityManager(claims). The target-is-admin check (currently a no-op bug) is fixed to query user_roles instead of user_permissions.

Multiple roles: A user can have multiple role assignments (e.g., global STAFF + location-scoped COMMUNITY_MANAGER at location X). hasRole checks all assignments — if any match, it returns true. ADMIN always wins.

PARTNER role: PARTNER users have a narrow permission set (view own profile, update own, view own transactions). No route gates on PARTNER specifically — PARTNER users access their own data through ownership checks, same as USER. The role is included in the JWT roles array and handled by hasRole.

No roles in claims (pre-hook token): During the transition period before the hook is active, JWTs won't have the roles array. Fall back to DB-based role lookup. After the hook is deployed and users refresh their tokens, this fallback is no longer exercised.

User with no roles: Returns false for all role checks. Effectively read-only (can view own data via ownership checks, nothing more).

New user signup: The public.users record is created by a synchronous Postgres trigger (on_auth_user_created) within the same transaction as the auth.users INSERT. By the time a token is issued, the user row exists. The hook will find the user and embed their roles (typically empty for a new user until a role is assigned).

Frontend impact

The web app derives roles from the /profile API endpoint (isStaff, isAdmin flags), not from JWT decoding. No frontend authorization logic changes. However, 6 files read app_metadata.role directly and must be updated:

File	Usage	Update
`AuthForm.tsx`	Post-login redirect (`ADMIN`/`STAFF` → staff overview)	Read from `roles` array or use `/profile` API
`CompleteProfileContent.tsx`	Post-profile-completion redirect	Same
`impersonation-server.ts`	Checkout context role field	Derive from `roles` array
`ResourceDetailClient.tsx`	Informational role for resource display	Derive from `roles` array
`ResourceDataSection.tsx`	Same	Derive from `roles` array
`apps/api/src/lib/auth/jwt/jwt-utils.ts`	4 functions validate/extract single role	Update to work with `roles` array

Deferred: UUID unification

A follow-up project will unify users.id with auth.users.id, eliminating:

resolveAppUserId() (~40ms per request)
The authUserId column on users
~35 where: { authUserId: auth.userId } Prisma lookups (become where: { id: auth.userId })
~17 entity.user.authUserId === auth.userId ownership comparisons (become entity.userId === auth.userId)
The users lookup inside the Custom Access Token Hook (the hook's event.user_id would directly match user_roles.user_id)

This is a large data migration touching every FK in the system and is explicitly out of scope for the permission redesign.