ADR-0022: Multi-Role User Support — roles[], user_roles, and AuthPrincipal

Status

Accepted

Context

A user can have multiple roles simultaneously (e.g., a parent who is also a CHAMADOR, or a teacher who is also a VOLUNTARIO). The original system (AuthPrincipal, JWT payload, database schema, middleware) was designed for a single role per user.

Issues #357 and #362 surfaced the need for multi-role support. Issue #356 (single-role scope integration via ADR-0016) landed first, establishing the ScopeFilter infrastructure. Multi-role builds on that infrastructure.

Key changes needed

1. Database: users.role (single TEXT column) → user_roles join table
2. Type system: AuthPrincipal.role: UserRole → AuthPrincipal.roles: UserRole[] + primaryRole: UserRole
3. JWT: Single role claim → roles[] array claim
4. Middleware: requireRole() checks against principal.roles (any match)
5. Scope resolution: resolveScope() takes array of roles, returns merged ScopeFilter (union)
6. Sanitization: sanitizeForRole() applies most-restrictive across all roles
7. Frontend: AuthContext, LocalUser, usePermissions adapted for multi-role

Alternatives considered

Alternative	Reasoning	Rejected because
Keep single-role, create composite roles	e.g., CHAMADOR_RESPONSAVEL as a new role	Explodes the role matrix (N roles × M combinations). Does not scale.
JSON array in users.role column	Keep flat table, just change column type	Anti-pattern — harder to query, no FK, no referential integrity.
Dual-claim JWT (keep role + add roles)	Graceful transition	Decision: breaking change is acceptable at MVP stage. Simpler to just replace.
Intersection of scopes (most restrictive)	PAIS + VOLUNTARIO: only own children in assigned classes	Contradicts user expectation that more roles = more access. Union selected.

Decision

1. Database — user_roles join table

The existing users.role column is dropped immediately (pre-MVP — no production data to lose). 6 D1 queries are updated as part of the same PR (see Issue #357 spec REQ-357-009).

CREATE TABLE user_roles (
  user_id TEXT NOT NULL REFERENCES users(user_id),
  role TEXT NOT NULL CHECK(role IN ('ADMIN', 'CHAMADOR', 'RELATORIOS', 'CADASTRO', 'VOLUNTEER', 'RESPONSAVEL', 'VOLUNTARIO', 'COORDENACAO_KIDS', 'ADMINISTRATIVO_KIDS')),
  PRIMARY KEY (user_id, role)
);

Migration: one-time INSERT INTO user_roles(user_id, role) SELECT user_id, role FROM users WHERE status = 'ACTIVE', then ALTER TABLE users DROP COLUMN role. Breaking change — all code querying users.role errors immediately. Acceptable at pre-MVP stage.

2. Type system — AuthPrincipal

type AuthPrincipal = {
  userId: string;
  roles: UserRole[];         // all roles
  primaryRole: UserRole;     // highest-ranked role for display
  tokenExpiresAt?: string;
  sessionId?: string;
  csrfToken?: string;
};

primaryRole is resolved from the role hierarchy (configured at role creation):

ADMIN > ADMINISTRATIVO_KIDS > COORDENACAO_KIDS > CADASTRO >
CHAMADOR = RELATORIOS > VOLUNTEER > RESPONSAVEL

3. JWT payload

Replace single role with roles[]:

{ sub: userId, roles: ["CHAMADOR", "RESPONSAVEL"], primary_role: "CHAMADOR", ... }

Breaking change — all users must re-authenticate after deploy. Acceptable at MVP stage.

4. Middleware — requireRole()

Uses any() match: user passes if at least one of their roles is in the allowed list.

function requireRole(allowedRoles: UserRole[]) {
  return (principal: AuthPrincipal) => {
    if (!principal.roles.some(r => allowedRoles.includes(r))) {
      throw new HttpError(403, "FORBIDDEN_ROLE", ...);
    }
  };
}

5. Scope resolution — union of scopes

resolveScope(roles, userId) resolves each role to its ScopeFilter and produces a compound union:

• If any role maps to { type: "all" } → result is all
• Otherwise produces { type: "multi", filters: [...] } for the repository to interpret

Example: user with [RESPONSAVEL, VOLUNTARIO] → union of own_children and assigned_classes → compound filter including both.

6. Sanitization — most restrictive

sanitizeForRole(record, roles) applies sanitization if any role is in SANITIZED_ROLES:

function sanitizeForRole<T>(record: T, roles: UserRole[]): T {
  if (roles.some(r => SANITIZED_ROLES.includes(r))) {
    // strip sensitive fields
  }
  return record;
}

7. Dev mode tokens

Kept single-role-per-token for now. Each env var maps to AuthPrincipal { roles: [singleRole] }. Multi-role dev mode deferred to post-MVP.

8. Frontend scope

All frontend changes (AuthContext, LocalUser, usePermissions, session hydration, role display) land in the same PR as the backend changes — single atomic deployment.

Consequences

Positive

• Correct relational model for user-role mapping
• Union of permissions — more roles = more access (user-intuitive)
• Compound scope — repositories can handle multi-role WHERE clauses via the "multi" type
• Display hierarchy — clear which role badge to show in the UI
• Breaking change is acceptable at MVP — no legacy token rotation needed

Negative

• Database migration — user_roles table needed, existing users.role column deprecated
• All users must re-login after deploy (JWT format changed from role to roles[])
• Compound scope complexity — repositories need to handle { type: "multi" } queries
• 97 callers of principal.role across the codebase — mechanical but widespread change

Risks mitigated

Risk	Mitigation
Scope leak (union giving too much access)	sanitizeForRole() still applies most-restrictive field stripping
Migration breaks 6 queries immediately	Same PR fixes all 6 — no deployment with broken queries
Compound scope queries are slow	Scope IDs are resolved per-request, union is simple OR in WHERE clause

Relationship to other ADRs

• ADR-0016 (RBAC Scope System): establishes ScopeFilter, resolveScope(), sanitizeForRole(). This ADR adapts them for multi-role arrays.
• ADR-0008 (Auth JWT): superseded for JWT format (single role → roles[])
• ADR-0017 (Infrastructure Migrations): this ADR adds a D1 migration for user_roles

Decisions from Grill (2026-07-05)

#	Decision
Q1	Union of scopes on role accumulation. PAIS + VOLUNTARIO = own_children ∪ assigned_classes.
Q2	Breaking JWT change — replace role with roles[] (MVP, no dual-claims needed).
Q3	user_roles join table — correct relational model, not JSON column.
Q4	primaryRole from explicit hierarchy — ADMIN > ADMINISTRATIVO_KIDS > COORDENACAO_KIDS > CADASTRO > CHAMADOR = RELATORIOS > VOLUNTEER > RESPONSAVEL.
Q5	requireRole() uses any() — pass if at least one user role matches.
Q6	Compound "multi" scope type — union of scopes stored as { type: "multi", filters: [...] }.
Q7	Sanitize most-restrictive — if ANY role requires sanitization, apply it.
Q8	Dev tokens keep single-role — multi-role dev mode deferred post-MVP.
Q9	Single PR — backend + frontend land together.
Q10	Migration script — INSERT INTO user_roles SELECT user_id, role FROM users in the deploy.

References

• Issue #357 — Multi-role user support
• Issue #362 — VOLUNTEER / VOLUNTARIO overlap investigation
• packages/permissions/index.ts — Permission matrix (source of truth for roles)
• packages/schemas/src/utils.ts — AuthPrincipal type definition
• workers/src/middleware/auth.ts — requireAuth, requireRole, dev tokens
• workers/src/modules/auth/sessionTokens.ts — JWT sign/verify
• packages/permissions/scope.ts — resolveScope, sanitizeForRole