Implement aggregation Supabase RPC edge functions
epic-organizational-hierarchy-management-assignment-aggregation-task-017 — Deploy Supabase edge functions implementing server-side aggregation RPCs for the heaviest subtree rollups. Move recursive CTE execution and participant deduplication logic to the database tier to minimize data transfer volume. Edge functions should accept unit ID and time-window parameters and return pre-aggregated JSON payloads compatible with the Bufdir breakdown API contract.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 6 - 158 tasks
Can start after Tier 5 completes
Implementation Notes
The recursive CTE pattern for adjacency-list trees in PostgreSQL is: WITH RECURSIVE subtree AS (SELECT id FROM organization_units WHERE id = $unit_id UNION ALL SELECT u.id FROM organization_units u JOIN subtree s ON u.parent_id = s.id) SELECT ... FROM activities WHERE unit_id IN (SELECT id FROM subtree). Deduplication: wrap in SELECT DISTINCT ON (member_id) to count unique participants. For NHF's 1,400-chapter tree, depth can exceed 4 levels — ensure the CTE has no artificial depth cap.
Deploy via supabase functions deploy aggregate_subtree_participants. The Deno runtime has no pg driver; use the Supabase client initialized with the service role key from environment variables (never hardcode). Return Content-Type: application/json in all cases. Cache the aggregated result in the database (a materialized view or a cache table refreshed on activity write) if response times exceed the 800ms SLA in load testing.
Testing Requirements
Write integration tests using the Supabase local development stack (supabase start). Test scenarios: (1) valid unit_id with a 3-level subtree returns correct total and per-subunit breakdown; (2) unit_id with no descendants returns { total_participants: 0, breakdown_by_subunit: [] }; (3) invalid date string returns HTTP 400 with correct error message; (4) calling with a unit_id the authenticated user does not own returns HTTP 403. Use seed SQL fixtures to populate test hierarchy data. Run tests in CI using the Supabase CLI (supabase db reset && supabase test db).
Also write a manual smoke test script that calls the deployed staging function end-to-end and validates response shape against the Bufdir contract JSON schema.
Recursive aggregation queries across four hierarchy levels (national → region → local) with 1,400 leaf nodes may be too slow for real-time dashboard requests, exceeding the 200ms target and causing spinner timeouts.
Mitigation & Contingency
Mitigation: Implement aggregation as a Supabase RPC using a single recursive CTE rather than multiple round-trip queries. Pre-compute aggregations nightly via a scheduled Edge Function and cache results. For real-time needs, aggregate only the immediate subtree on demand.
Contingency: Surface a 'Refreshing...' indicator and serve stale cached aggregations immediately. Queue an async recalculation and push updated data via Supabase Realtime when ready, avoiding blocking the admin dashboard.
The 5-chapter limit and primary-assignment constraint are NHF-specific. Applying these rules globally may break HLF and Blindeforbundet configurations where different limits apply, requiring per-organization configuration that was not initially scoped.
Mitigation & Contingency
Mitigation: Make the maximum assignment count a configurable value stored in the organization's feature-flag or settings table rather than a hardcoded constant. Design the assignment service to read this limit at runtime per organization.
Contingency: Default the limit to a high value (e.g., 100) for organizations other than NHF, effectively making it non-restrictive, while keeping the enforcement logic intact for when per-org configuration is fully implemented.
The searchable parent dropdown in HierarchyNodeEditor must search across up to 1,400 units efficiently. Client-side filtering of the full hierarchy may be slow; server-side search adds complexity and latency.
Mitigation & Contingency
Mitigation: Use the in-memory hierarchy cache as the search corpus — since the cache already holds the flat unit list, client-side filtering with a debounced input is sufficient and avoids extra Supabase calls. Pre-build a search index on cache load.
Contingency: Cap the dropdown to showing the 50 most recently accessed units by default, with a 'search all' option that triggers a server-side full-text query. This keeps the common case fast while supporting edge cases.