Role-Based Access Control: 9 Roles and Permission Matrix

[1. Role Permissions]
     — Does the user's assigned role include this permission_code?
          ↓
[2. user_permissions table (per-user overrides)]
     — Is there a row for (user_id, permission_id)?
       • granted = true  → access granted regardless of role
       • granted = false → access denied regardless of role
          ↓
[3. site_id restriction (single-site users)]
     — Does the resource belong to the user's assigned site?
       • Managers / Directors → cross-site, skip this check
       • All other roles     → restricted to their own site_id
          ↓
[Access Granted / Denied]

CREATE TABLE user_permissions (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid REFERENCES tenants(id) ON DELETE CASCADE,
    user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission_id uuid NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    granted      boolean NOT NULL DEFAULT true,
    assigned_at  timestamp NOT NULL DEFAULT NOW(),
    assigned_by  uuid REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT unique_user_permission UNIQUE (user_id, permission_id)
);