Test RLS policies against simulated role sets
epic-organizational-hierarchy-management-core-services-task-014 — Write automated tests that simulate multiple role scenarios (coordinator of chapter A trying to read chapter B data, peer mentor reading another mentor's records, global admin bypass) using Supabase's set_config for JWT simulation. Confirm data isolation is enforced and no cross-tenant leakage is possible.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 4 - 323 tasks
Can start after Tier 3 completes
Implementation Notes
Supabase's set_config approach for JWT simulation in SQL tests: use SELECT set_config('request.jwt.claims', '{"sub":"user-uuid","app_metadata":{"unit_ids":["unit-a-uuid"],"role":"coordinator"}}', true); followed by SET ROLE authenticated; before each test query. Reset with RESET ROLE; and SELECT set_config('request.jwt.claims', '', true); after. pgTAP provides ok(), is(), isnt(), results_eq() for structured assertions. Example: SELECT ok((SELECT count(*) FROM activities WHERE organization_unit_id = 'chapter-b-unit-id') = 0, 'Coordinator A cannot read Chapter B activities'); Organise test scenarios into groups using pgTAP's subtest() blocks for readable output.
For the tampered JWT test, construct a JWT with a unit_ids array containing chapter-B's unit_id for a chapter-A coordinator, apply via set_config, and assert chapter-B data is still inaccessible. This verifies that the policy relies on server-injected claims from the hook (task-012) rather than raw JWT content that a client could forge. Use consistent UUID fixtures (hardcoded in seed.sql) for deterministic assertions.
Testing Requirements
This task IS the testing task. Deliverables: (1) A pgTAP test file (tests/rls/rls_policy_tests.sql) with plan(), ok(), is(), and finish() assertions covering all 12+ scenarios. (2) A seed data SQL file (tests/rls/seed.sql) creating 2 orgs, 3 chapters each, 2 coordinators, 5 peer mentors, 20 activities. (3) A teardown SQL file (tests/rls/teardown.sql).
(4) Integration into CI via supabase db test command. (5) A written test report template that CI populates with pass/fail counts per scenario category (read isolation, write prevention, admin bypass, unauthenticated). The test suite itself must achieve 100% scenario coverage of the RLS policy matrix defined in task-013's policy audit document.
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.
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.
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.
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.