Implement recursive CTE for org subtree resolution
epic-admin-portal-foundation-task-005 — 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.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Use a standard PostgreSQL recursive CTE pattern: WITH RECURSIVE subtree AS (SELECT id FROM organisations WHERE id = root_org_id UNION ALL SELECT o.id FROM organisations o INNER JOIN subtree s ON o.parent_org_id = s.id) SELECT id FROM subtree. Add CYCLE detection using PostgreSQL 14+ CYCLE clause (CYCLE id SET is_cycle USING path) if the Supabase instance runs PG14+; otherwise use a visited-array guard. Deploy as a CREATE OR REPLACE FUNCTION in a migration file. Mark RETURNS TABLE(id UUID).
The function will be called both from within RLS policy expressions (performance-sensitive) and from Dart via supabase.rpc(). Avoid returning the full organisations row — only UUIDs to keep RLS policy expressions lean. Confirm the Supabase project's PostgreSQL version before choosing the CYCLE syntax.
Testing Requirements
Integration tests (Dart/flutter_test with Supabase test client or direct PostgreSQL psql scripts): (1) Leaf org returns only itself. (2) Root org with known hierarchy returns correct full set of descendant IDs. (3) Mid-level org returns only its subtree, not siblings or parent. (4) Non-existent UUID returns empty set without error.
(5) Performance test: call on NHF root org completes under 200ms. (6) Cycle protection: insert a temporary circular parent reference, verify function terminates. (7) Permission test: call as anon role returns permission denied error. All tests must be repeatable in a local Supabase Docker environment.
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.