🏠 Overview › ✅ Implementation Tasks ›✅ epic-organizational-hierarchy-management-admin-portal-task-006

Build RLS Policy Manager

epic-organizational-hierarchy-management-admin-portal-task-006 — Implement the infrastructure layer that manages Supabase Row Level Security policy configuration and JWT claims injection. Generate and update JWT claims with user unit assignments for RLS enforcement, provide policy validation utilities, and expose helpers to sync assignment changes with active JWT session claims.

critical priority high complexity infrastructure pending infrastructure specialist Tier 2

Acceptance Criteria

JWT claims include a unit_ids claim (List<String> of UUID strings) reflecting all organization_unit_ids assigned to the current user
After an assignment change (add/remove unit), calling refreshSessionClaims() triggers a Supabase Auth session refresh and the new JWT contains updated unit_ids within 2 seconds
RLS policies on all multi-tenant tables filter rows where organization_unit_id = ANY(auth.jwt() -> 'unit_ids') OR the user holds a national admin role
Policy validation utility canUserAccessUnit(userId, unitId) returns true only when unitId is present in the user's current JWT claims or the user is a national admin
Service role key is never referenced in Flutter client code; all privileged claim updates occur via Supabase Edge Function invoked over REST
Coordinator JWT claims include descendant unit IDs computed from the hierarchy (not just directly assigned units)
Session-level cache of unit_ids is invalidated and refreshed whenever assignment data changes
All JWT claim mutation operations are idempotent — calling refreshSessionClaims() twice produces the same result as calling it once

Technical Requirements

frameworks
Flutter
Dart
Supabase Edge Functions (Deno)
apis
Supabase Auth API
Supabase Edge Functions REST API
data models
assignment
contact_chapter
performance requirements
Session claim refresh must complete within 2 seconds on a standard mobile network
Cached unit_ids list must be read from memory on every RLS-gated query — no redundant network calls during a single session
security requirements
Service role key stored exclusively in Edge Function environment variables — never in Flutter app binary or dart-define
JWT tokens stored in flutter_secure_storage (iOS Keychain / Android Keystore), never in SharedPreferences
PKCE flow used for all OAuth redirects per Supabase Auth security requirements
Refresh tokens rotated on every use; stale refresh tokens invalidated server-side
unit_ids claim is computed server-side in the Edge Function and signed by Supabase — client cannot self-issue claims
All privileged operations scoped by organization_id extracted from the verified JWT — cross-org access is structurally impossible

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Split into two layers: (a) RlsPolicyManager Dart class in Flutter responsible for cache management and triggering claim refresh; (b) a Deno Edge Function (update-jwt-claims) that receives a userId, queries assignments + hierarchy descendants, and calls supabaseAdmin.auth.admin.updateUserById() to set custom claims. The Flutter class should expose: Future refreshSessionClaims(), bool isUnitInClaims(String unitId), and Stream> watchUnitIds(). Implement session cache as an in-memory List populated on login and updated after refreshSessionClaims(). The Edge Function must validate that the caller's JWT organization_id matches the userId's organization to prevent cross-org claim tampering.

Use Supabase Realtime or a simple post-assignment hook to auto-trigger refreshSessionClaims() so callers do not need to remember to call it manually. NHF's deep hierarchy (up to 4 levels, 1400 chapters) means the descendant computation in the Edge Function must use a recursive CTE SQL query for performance, not application-level recursion.

Testing Requirements

Integration tests using a Supabase test project (or local Supabase CLI instance) to verify: (1) JWT contains correct unit_ids after assignment; (2) RLS blocks a query for a unit not in claims; (3) refreshSessionClaims() propagates assignment changes within the SLA. Unit tests for the Flutter RlsPolicyManager class using mocked SupabaseClient and mocked Edge Function responses. Test scenarios: admin adds unit to user → claims updated; admin removes unit → claims updated; coordinator scope includes descendants; national admin bypasses unit filter. Use flutter_test.

Minimum 90% branch coverage on RlsPolicyManager.

Component
Access Scope Service
service high
Dependencies (1)
Implement the Supabase-backed repository for unit assignment CRUD operations. Include methods for fetching assignments by user, by unit, setting primary unit, and batch assignment updates. Integrate with the hierarchy cache for read operations and ensure proper RLS policy compliance for data isolation. epic-organizational-hierarchy-management-admin-portal-task-002
Epic Risks (3)
high impact medium prob security

If the AccessScopeService and the Supabase RLS policies use different logic to determine accessible units, a coordinator could see data in the client that RLS blocks server-side, causing confusing empty states, or worse, RLS could block data the scope service declares accessible.

Mitigation & Contingency

Mitigation: Define the canonical scope computation in a single Supabase Postgres function shared by both the RLS policies and the RPC endpoint called by AccessScopeService. The client-side service calls this RPC rather than reimplementing the logic, ensuring a single source of truth.

Contingency: Add integration tests that execute the same access decision through both the RLS policy path and the AccessScopeService path and assert identical results. Use these as regression guards in the CI pipeline.

medium impact medium prob integration

When a user switches active chapter via the ChapterSwitcher, widgets that are already built may not receive the context-change event if they subscribe incorrectly to the ActiveChapterState BLoC, leading to stale data being displayed under the new chapter context.

Mitigation & Contingency

Mitigation: Use Riverpod's ref.watch on the active chapter provider at the root of each scoped data subtree rather than at individual leaf widgets. Trigger a global data refresh by invalidating all scoped providers when the chapter changes.

Contingency: Add an app-level chapter-change listener that forces a full navigation stack reset to the home screen on chapter switch, guaranteeing all widgets rebuild from scratch with the new context. Accept the UX cost of navigation reset for correctness.

medium impact medium prob scope

Non-technical organization administrators may find the hierarchy management interface too complex for the structural changes they need to make frequently (e.g., chapter renaming, coordinator reassignment), leading to low adoption and continued reliance on manual processes.

Mitigation & Contingency

Mitigation: Conduct usability testing with at least one NHF administrator before finalizing the admin portal screen layout. Prioritize the most common operations (rename, reparent, add child) as primary actions in the UI. Include inline help text and confirmation dialogs with plain-language descriptions of consequences.

Contingency: Provide a simplified 'quick edit' mode that exposes only the three most common operations (rename, deactivate, add child) and hides advanced structural operations behind an 'Advanced' toggle.

Quick Links
All Tasks Execution Plan