Write RLS policy integration tests
epic-admin-portal-foundation-task-010 — Create a comprehensive test suite that exercises all RLS policies defined in tasks 003 and 004. Tests must verify: org_admin cannot read data outside their subtree, super_admin can read cross-org data, unauthenticated requests are blocked, and recursive CTE subtree scoping returns correct child orgs for NHF's hierarchy depth.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Structure tests in test/rls/ directory with a shared fixtures.dart file for org/user seeding helpers. Use Supabase's service_role client (from environment variable SUPABASE_SERVICE_ROLE_KEY) for setup and teardown only. For role-specific client sessions, use signInWithPassword with pre-seeded test accounts (email/password) rather than minting raw JWTs — this is more stable across Supabase version updates. Create a helper class RlsTestClient with factory methods: RlsTestClient.asOrgAdmin(orgId), RlsTestClient.asSuperAdmin(), RlsTestClient.asAnon().
Each factory signs in and sets the org scope via set_config before returning. Tag all RLS tests with @Tags(['rls', 'integration']) to allow selective CI execution. Ensure the test org hierarchy IDs use a known UUID prefix (e.g., 00000000-test-...) to make cleanup safe and deterministic.
Testing Requirements
Integration test suite (dart test against local Supabase Docker). Test fixture setup: use service_role client to insert a 4-level org hierarchy (national → landsforening → region → lokallag), create one user per role per org level, insert sample activities and reimbursements for each org. Test teardown: delete all seeded rows by fixture ID prefix. Test categories: (A) SELECT boundary tests — 8 tests covering all 4 tables × 2 scenarios (in-scope/out-of-scope).
(B) Role escalation tests — verify org_admin cannot impersonate super_admin by modifying JWT locally. (C) Subtree depth tests — verify 4-level hierarchy traversal. (D) Negative tests — anon access, expired token, missing org_id claim. (E) Mutation tests — verify org_admin cannot INSERT records into out-of-scope org.
Minimum 20 test cases total.
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.
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.
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.