🏠 Overview › ✅ Implementation Tasks ›✅ epic-admin-portal-foundation-task-006

Build Admin RLS Guard middleware

epic-admin-portal-foundation-task-006 — Implement the AdminRlsGuard service that intercepts every Supabase client session for admin-role users and injects org-scope JWT claims before each request. The guard must read the authenticated user's org_id from the session store, call the Supabase set_config RPC to inject the claim, and expose a guardedClient getter used by all admin repository methods.

critical priority high complexity backend pending backend specialist Tier 3

Acceptance Criteria

AdminRlsGuard is a Dart class injectable via Riverpod provider
guardedClient getter returns a SupabaseClient that has had set_config('app.current_org_id', orgId) called before use
org_id is read from the authenticated session — never from client-supplied parameters
Guard throws an AuthException (or equivalent typed error) if the current session has no org_id claim
Guard throws an AuthException if the session user does not hold an admin or org_admin role
set_config RPC call is awaited and confirmed successful before the guardedClient is returned
All admin repository methods that use guardedClient receive a correctly scoped client on every invocation, not just the first
Guard does not store a stale client — re-injects org_id on every call to guardedClient to handle session refresh
Unit tests confirm guard rejects unauthenticated callers with a clear error message
Unit tests confirm guard rejects authenticated non-admin users with a clear error message

Technical Requirements

frameworks
Flutter
Riverpod
Supabase
apis
supabase.rpc('set_config', { key: 'app.current_org_id', value: orgId })
supabase.auth.currentSession for org_id extraction
Supabase JWT custom claims (app_metadata.org_id)
data models
AdminUser
UserSession
Organisation
performance requirements
set_config RPC round-trip must not add more than 50ms to any admin query
Guard must cache the org_id within a single request scope to avoid redundant RPC calls on batch operations
security requirements
org_id must be sourced exclusively from the server-signed JWT — never from local storage or widget state
Guard must validate the user's role claim (org_admin or super_admin) before injecting org scope
Session token must not be logged or exposed in error messages

Execution Context

Execution Tier
Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Implementation Notes

In Flutter/Riverpod, implement AdminRlsGuard as a class provided by a Riverpod Provider (not StateNotifier — it holds no mutable state). The guardedClient getter should be an async method: (1) fetch supabase.auth.currentSession, (2) extract org_id from session.user.appMetadata['org_id'], (3) validate role from appMetadata['role'], (4) call await supabase.rpc('set_config', params: {'key': 'app.current_org_id', 'value': orgId}), (5) return the SupabaseClient instance. Because set_config is session-scoped in PostgreSQL (not transaction-scoped), it persists for the connection lifetime — but Supabase uses connection pooling so always re-inject on each logical request. Consider wrapping in a helper executeWithGuard(Future Function(SupabaseClient) query) to ensure set_config always precedes the query atomically.

Testing Requirements

Unit tests (flutter_test with mocked SupabaseClient): (1) guardedClient with valid org_admin session calls set_config with correct org_id. (2) guardedClient with super_admin session injects global scope sentinel or skips scoping as per design. (3) Unauthenticated session throws AuthException. (4) Session with non-admin role throws AuthException.

(5) set_config RPC failure (network error) propagates as a typed exception rather than silent failure. (6) Second call to guardedClient within same session still calls set_config (no stale caching). Integration test: confirm an actual Supabase query through guardedClient is restricted to the injected org's subtree.

Component
Admin Row-Level Security Guard
service high
Dependencies (3)
Implement Supabase row-level security policies scoped to the org_admin role across users, activities, reimbursements, and organisations tables. Policies must restrict data access to the authenticated admin's organisation subtree using the JWT org_id claim. Include recursive CTE helper function for subtree resolution. epic-admin-portal-foundation-task-003 Implement Supabase row-level security policies for the super_admin role granting cross-organisation read access and restricted write access across all relevant tables. Policies must be additive to org_admin policies without conflict. Validate that super_admin cannot bypass audit trail writes. epic-admin-portal-foundation-task-004 Write a PostgreSQL recursive CTE function get_org_subtree(root_org_id UUID) that returns all child organisation IDs within a hierarchy. Deploy as a Supabase database function. This function is used by RLS policies and the admin repository to enforce NHF's 1,400-chapter hierarchy scoping correctly. epic-admin-portal-foundation-task-005
Epic Risks (3)
high impact medium prob security

Missing RLS policies on one or more tables (e.g., a newly added join table or a Supabase view) could expose cross-org data to org_admin queries, creating a GDPR-reportable data breach.

Mitigation & Contingency

Mitigation: Enumerate all tables and views accessed by admin queries before writing any policy. Create an automated test that attempts a cross-org query for each table from an org_admin JWT and asserts an empty result set.

Contingency: If a gap is discovered post-deployment, immediately disable the affected query surface and deploy a hotfix policy before re-enabling. Log the incident and notify DPO if any cross-org data was returned.

high impact medium prob technical

The recursive CTE for NHF's deeply nested org tree (up to 5 levels, 1,400 local chapters) may exceed the 2-second dashboard load target when resolving large subtrees on every request.

Mitigation & Contingency

Mitigation: Benchmark the recursive CTE against a synthetic NHF-scale dataset during development. Introduce a short-TTL server-side cache for subtree resolution results. Index the parent_id column on the organisations table.

Contingency: If CTE performance remains insufficient, materialise the org subtree as a precomputed closure table updated on org structure changes, and switch the RLS guard to query the closure table instead.

high impact low prob security

Incorrect JWT claim injection in AdminRlsGuard (e.g., wrong claim key name or missing refresh on org switch) could silently apply the wrong org scope, causing org_admin to see a different organisation's data without an explicit error.

Mitigation & Contingency

Mitigation: Write unit tests for the guard that verify the injected claim value against the authenticated user's org_id for every admin route. Add a server-side assertion that the claim matches the user's database record before executing any query.

Contingency: Roll back the guard to a deny-all fallback, invalidate active admin sessions, and re-issue corrected JWTs. Audit query logs to identify any sessions that received incorrect scope.

Quick Links
All Tasks Execution Plan