Implement core Admin Repository query methods
epic-admin-portal-foundation-task-007 — Build AdminRepository with typed, RLS-enforced query methods for the primary admin use cases: getUsersInScope(), getActivitiesInScope(), getReimbursementsInScope(), and getOrganisationsInScope(). All methods must use the guarded Supabase client from AdminRlsGuard and return typed domain models from task-001.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 4 - 323 tasks
Can start after Tier 3 completes
Implementation Notes
Structure AdminRepository as a plain Dart class (not a Riverpod StateNotifier) provided via Provider
For pagination, add optional AdminQueryPage page parameter (with offset and limit) to each method signature now — even if the UI only uses the first page initially — to avoid breaking changes later. Align column names precisely with the Supabase schema established in task-003.
Testing Requirements
Unit tests (flutter_test with mocked AdminRlsGuard and mocked SupabaseClient): (1) Each method calls guardedClient exactly once per invocation. (2) Each method correctly maps raw Supabase JSON response to typed domain models. (3) Empty database response returns empty list. (4) Supabase error is wrapped in AdminRepositoryException with correct method name.
(5) Optional filters are applied correctly (verify query builder receives correct .eq()/.gte() calls). Integration tests against local Supabase: (6) org_admin user only sees users/activities within their org subtree. (7) super_admin sees cross-org data. (8) Date range filter on getActivitiesInScope returns only records in range.
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.