Implement KPI aggregate query methods in Admin Repository
epic-admin-portal-foundation-task-008 — Extend AdminRepository with KPI-focused aggregate methods: getActiveUserCount(), getTotalActivitiesCount(), getPendingReimbursementsCount(), and getOrgHealthScore(). These are optimised Supabase RPC calls or aggregate queries that respect RLS scoping. Results feed the AdminStatWidget.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 5 - 253 tasks
Can start after Tier 4 completes
Implementation Notes
Prefer server-side Supabase RPC functions for count and health score operations — this ensures RLS is applied at the database level and avoids streaming large result sets to the client just to count them. Use Supabase's count: CountOption.exact with head: true on .select() for simple counts. For getOrgHealthScore, define a PostgreSQL function get_org_health_score(root_org_id UUID) that computes: active_users/total_users * 0.4 + recent_activities_ratio * 0.4 + (1 - pending_reimbursements_ratio) * 0.2 (or similar weighted formula agreed with the team). Store the formula weights as named constants in the SQL function for easy tuning.
The AdminStatDto should be an immutable Dart class with copyWith for BLoC state updates.
Testing Requirements
Unit tests (flutter_test with mocked Supabase): (1) getActiveUserCount returns correct integer from mocked count response. (2) getPendingReimbursementsCount returns 0 when no pending records. (3) getOrgHealthScore returns a value between 0.0 and 1.0. (4) Any method throws AdminRepositoryException on network error.
Integration tests: (5) Seed test database with known counts and verify each method returns exact expected values. (6) Verify org_admin's counts exclude records from outside their subtree. (7) Performance: all four method calls complete within 300ms against a Supabase instance with 10,000+ seeded rows.
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.