Implement Org Hierarchy Roll-Up for Region and National Scopes
epic-bufdir-report-export-core-backend-task-003 — Extend the activity aggregation service to support region and national scope_level values. When scope is region, aggregate across all chapters under the region; when national, aggregate across all chapters under the organisation. Use the org hierarchy resolver to enumerate subordinate unit IDs and execute multi-unit queries with correct data isolation per org. Ensure RLS policies are respected and cross-org data leakage is impossible.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Implement the org hierarchy resolver as a separate Dart class (`OrgHierarchyResolver`) with a single method `getSubordinateOrgIds({required String rootOrgId, required ScopeLevel scope})`. This makes it independently testable and reusable by other features. For the hierarchy query, use a recursive SQL CTE (`WITH RECURSIVE`) in a Supabase RPC function rather than loading the entire org table and traversing it in Dart — this is far more efficient for NHF's 1,400-chapter structure. The Dart service class calls the RPC to get the ID list, then calls the aggregation RPC with the list.
Keep RPC payloads small: pass only org_id lists, not full org objects. For the JWT intersection security check, extract `allowed_org_ids` from the JWT claims (or query the user's org memberships) and compute the intersection with the hierarchy resolver's output using a Dart Set operation before proceeding. Log any excluded IDs at warning level for audit purposes.
Testing Requirements
Unit tests: mock the org hierarchy resolver to return a controlled set of chapter IDs and verify the aggregation query is built with the correct IN clause. Test all three scope levels in a single test suite. Test the security boundary: pass a chapter-level user's JWT and request region scope — confirm the method only aggregates chapters the JWT allows. Integration tests (local Supabase): seed three chapters under one region, one chapter under a different region, and a national org containing both regions.
Assert that a region-scope query returns only the three chapters, not the isolated one, and national returns all four. Performance test: seed 1,400 chapter IDs and 10,000 activity rows across them; assert the national-scope query returns within the 10-second threshold.
Supabase Edge Functions have a default execution timeout. For large national-scope exports aggregating tens of thousands of activities across 1,400 chapters, the edge function may time out before completing, leaving coordinators with a failed export and no partial output.
Mitigation & Contingency
Mitigation: Optimise the aggregation SQL using pre-materialised aggregation views or RPC functions that run inside the database rather than iterating records in Deno. Profile query execution time against realistic production data volumes early. Request an elevated timeout limit from Supabase if needed. Implement progress checkpointing so the export can be resumed from the last completed aggregation batch.
Contingency: For organisations exceeding a configurable threshold (e.g. >5,000 activities), switch to an asynchronous export pattern: the edge function writes a 'pending' audit record and enqueues the job; the client polls for completion and is notified via Supabase Realtime when the file is ready.
Server-side PDF generation in a Deno Edge Function environment restricts library choices. Many popular PDF libraries require Node.js APIs not available in Deno, or produce large bundle sizes that exceed edge function limits. Choosing the wrong library could block the entire PDF generation path.
Mitigation & Contingency
Mitigation: Spike PDF library selection as the first task of this epic, evaluating at least two Deno-compatible options (e.g. pdf-lib, jsPDF with Deno compatibility shim). Test bundle size and basic rendering before committing to an implementation. Document the chosen library's constraints.
Contingency: If no suitable Deno-native PDF library is found, generate a well-structured HTML report from the edge function and use a headless Chromium service (e.g. Browserless, Gotenberg) for HTML-to-PDF conversion, or temporarily ship CSV-only export while the PDF path is resolved.
Peer mentors affiliated with multiple chapters (a documented NHF scenario) must not be double-counted in participant totals. Incorrect deduplication logic would overreport participation figures to Bufdir, which could be discovered during audit and damage organisational credibility.
Mitigation & Contingency
Mitigation: Define and document the deduplication contract explicitly before coding: deduplication is per-person per-period, not per-activity. Build dedicated unit tests with fixtures containing the exact multi-chapter membership patterns described in NHF's documentation. Have a NHF representative validate test fixture outputs against known-good manual counts.
Contingency: If deduplication logic produces results that cannot be verified against manual counts before launch, surface a deduplication warning in the export preview listing the affected peer mentor IDs, and require explicit coordinator acknowledgement before finalising the export.