🏠 Overview › ✅ Implementation Tasks ›✅ epic-organizational-hierarchy-management-core-services-task-012

Implement JWT claim injection for RLS tenant scoping

epic-organizational-hierarchy-management-core-services-task-012 — Implement the server-side mechanism (Supabase Edge Function or database hook) that injects the authenticated user's organization unit IDs and role into the JWT as custom claims on each session. RLS USING clauses will reference auth.jwt() -> 'unit_ids' to enforce tenant isolation without application-layer filtering.

critical priority high complexity infrastructure pending infrastructure specialist Tier 2

Acceptance Criteria

A Supabase Auth hook (custom access token hook) is implemented as a PostgreSQL function or Edge Function that fires on every token refresh and initial sign-in
The hook queries the user_roles and unit_assignments tables to derive the authenticated user's unit_ids (array of UUIDs) and role (string enum: peer_mentor, coordinator, global_admin)
The hook injects unit_ids and role as top-level custom claims in the JWT payload under the app_metadata namespace
JWT custom claims are accessible via auth.jwt() -> 'app_metadata' -> 'unit_ids' and auth.jwt() -> 'app_metadata' -> 'role' in RLS USING expressions
For NHF users who belong to multiple chapters (up to 5), unit_ids contains all associated unit IDs as a JSON array
Global admin users receive unit_ids = [] (empty array) and role = 'global_admin'; RLS policies grant them unrestricted access based on role claim, not unit_ids
If the hook query fails (database error), the JWT is issued without custom claims and the user receives a 403 on first data access — the session is NOT silently granted full access
Hook execution completes within 500ms to avoid degrading login/refresh latency
The hook function is covered by a database-level unit test that asserts correct claim output for peer_mentor, coordinator, and global_admin fixtures
Hook code is version-controlled and deployed via Supabase CLI (not manually applied in dashboard)
Documentation describes the JWT claim schema, the hook trigger mechanism, and how to rotate or invalidate sessions when unit membership changes

Technical Requirements

frameworks
Supabase Auth (custom access token hook)
Supabase PostgreSQL 15
Supabase Edge Functions (Deno) — only if PostgreSQL hook insufficient
apis
Supabase Auth hook API
Supabase PostgreSQL 15 (user_roles and unit_assignments reads)
data models
assignment
contact_chapter
performance requirements
Hook query must complete within 500ms including database round-trip
Use a single JOIN query (user_roles JOIN unit_assignments) rather than multiple sequential queries to minimise latency
Add composite index on unit_assignments(user_id, unit_id) if not already present to support hook query speed
Cache invalidation strategy: when unit membership changes, trigger session refresh for affected users (document the invalidation approach)
security requirements
Hook function must run with SECURITY DEFINER and a minimal-privilege role that can only SELECT from user_roles and unit_assignments — no write access
Service role key used inside the hook is only available in the server-side function environment, never passed to mobile clients
JWT custom claims must be validated on each request — do not trust client-supplied unit_ids; always derive from server-side hook
Supabase Auth hook must be registered via the Supabase dashboard Auth settings (not via SQL trigger on auth.users) to ensure it fires for all auth methods including BankID OIDC and Vipps Login
National identity number (personnummer from BankID/Vipps) must not be included in JWT claims — only internal UUIDs
GDPR: JWT claims should contain only role and unit membership identifiers, not PII (names, emails, phone numbers)

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Supabase supports custom access token hooks as PostgreSQL functions registered in Auth settings (Dashboard → Authentication → Hooks). The function signature must match Supabase's expected hook interface: it receives a JSON event object and returns a JSON response with the modified claims. Write the hook as a SQL function: CREATE OR REPLACE FUNCTION auth.custom_access_token_hook(event jsonb) RETURNS jsonb. Inside the function, extract auth.uid() from event, query unit_assignments and user_roles, then return event with app_metadata claims merged.

Use jsonb_build_object and jsonb_set for claim manipulation. For NHF's multi-chapter support, use array_agg(ua.unit_id) in the query and cast to jsonb. Note: Supabase's custom access token hook was introduced in Supabase Auth v2.99+ — verify the project's Supabase version supports it before implementation. If not available, fall back to a Postgres trigger on auth.sessions or a Realtime-triggered Edge Function, but document the tradeoffs.

JWT claim changes only take effect on the NEXT token refresh — document that unit membership changes require the affected user to refresh their session (or implement server-side session invalidation via Supabase Admin API).

Testing Requirements

Integration tests using a local Supabase instance: (1) Sign in as a peer_mentor user and verify decoded JWT contains app_metadata.unit_ids array with exactly the assigned unit IDs and app_metadata.role = 'peer_mentor'. (2) Sign in as a coordinator and verify unit_ids includes all units in their chapter subtree. (3) Sign in as a global_admin and verify unit_ids = [] and role = 'global_admin'. (4) Test NHF multi-chapter scenario: user assigned to 3 chapters has all 3 unit IDs in the array.

(5) Test hook failure scenario: temporarily break the hook query and verify the resulting JWT triggers 403 (not silent full-access). (6) Performance test: measure hook execution time under load (10 concurrent refreshes) and assert P95 < 500ms. (7) Verify that changing unit membership and forcing a token refresh produces an updated JWT with new unit_ids. All tests must be automated and runnable via supabase db test or a dedicated integration test suite.

Component
RLS Policy Manager
infrastructure high
Dependencies (1)
Write Supabase migration SQL files that enable Row Level Security on all organization-unit-scoped tables (organization_units, unit_assignments, activities, contacts, user_roles). Each table requires ENABLE ROW LEVEL SECURITY and initial USING policies that restrict read access to rows matching the user's unit membership, derived from JWT claims. epic-organizational-hierarchy-management-core-services-task-011
Epic Risks (4)
high impact medium prob security

Injecting all unit assignment IDs into JWT claims for users assigned to many units (up to 5 for NHF peer mentors, many more for national coordinators) may exceed JWT size limits, causing authentication failures.

Mitigation & Contingency

Mitigation: Store unit IDs in a Supabase session variable or a dedicated Postgres function rather than embedding them directly in the JWT payload. Use set_config('app.unit_ids', ...) within RLS helper functions querying the assignments table at policy evaluation time.

Contingency: Fall back to querying the unit_assignments table directly within RLS policies using the authenticated user ID, accepting a small per-query overhead in exchange for removing the JWT size constraint.

medium impact medium prob technical

Rendering 1,400+ nodes in a recursive Flutter tree widget may cause jank or memory pressure on lower-end devices used by field peer mentors, degrading the admin experience.

Mitigation & Contingency

Mitigation: Implement lazy tree expansion — only the root level is rendered on initial load. Child nodes are rendered on demand when the parent is expanded. Use const constructors and ListView.builder for all node lists to minimize rebuild scope.

Contingency: Add a search/filter bar that scopes the visible tree to matching nodes, reducing the visible node count. Provide a 'flat list' fallback view for administrators who prefer searching over browsing the tree.

medium impact medium prob scope

Requirements for what constitutes a valid hierarchy structure may expand during NHF sign-off (e.g., mandatory coordinator assignments per chapter, minimum member counts per region), requiring repeated validator redesign.

Mitigation & Contingency

Mitigation: Design the validator as a pluggable rule engine where each check is a discrete, independently testable function. New rules can be added without changing the core validation orchestration. Surface all rules in a configuration table per organization.

Contingency: Defer non-blocking validation rules to warning-level feedback rather than hard blocks, allowing structural changes to proceed while flagging potential issues for admin review.

high impact low prob integration

Deploying RLS policy migrations to a shared Supabase project used by multiple organizations simultaneously could lock tables or interrupt active sessions, causing downtime during production migration.

Mitigation & Contingency

Mitigation: Write all RLS policies as CREATE POLICY IF NOT EXISTS statements. Schedule migrations during off-peak hours. Use Supabase's migration preview environment to validate policies against production data shapes before applying.

Contingency: Prepare rollback migration scripts for every RLS policy. If a migration causes issues, execute the rollback immediately and re-test the policy logic in staging before reattempting.

Quick Links
All Tasks Execution Plan