critical priority high complexity testing pending testing specialist Tier 5

Acceptance Criteria

Integration tests run against a local Supabase instance (supabase start) seeded with at least 3 organisations and 10+ users per org
All AdminRepository query methods return zero records from other organisations when authenticated as org_admin of Org A
KPI aggregate values (active peer mentor count, activity totals, certification counts) match independently computed SQL assertions on seed data
All typed Dart model classes (AdminUser, OrgKpi, etc.) deserialise without runtime errors from real Supabase JSON responses
Pagination: fetching page 1 of 5 users and page 2 of 5 users returns non-overlapping, correctly ordered records
Test for org_admin attempting to query another org's data — must return empty result set, not an error (RLS silently filters)
Test for super_admin role — must be able to query across all orgs without RLS restriction
All tests pass in CI with `supabase db reset` + seed script executed before the test run
No hardcoded UUIDs in test assertions — seed data IDs are resolved at runtime

Technical Requirements

frameworks
Flutter
flutter_test
Supabase CLI (local dev stack)
apis
Supabase PostgREST REST API (real, local instance)
Supabase Auth (local instance with test users)
Supabase set_claim RPC (local instance)
data models
AdminUser
OrgKpi
Organisation
PeerMentor
Activity
Certification
performance requirements
Each integration test completes within 3 seconds against local Supabase
Seed script executes in under 30 seconds
security requirements
Test credentials must never be committed — use .env.test loaded via flutter_dotenv or equivalent
Local Supabase JWT secret must differ from production

Execution Context

Execution Tier
Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

Implementation Notes

Create a `test/integration/admin_repository/` directory separate from unit tests. Use a `seed.sql` file checked into the repo under `supabase/seed/admin_integration_test_seed.sql`. The seed must be deterministic (fixed UUIDs via gen_random_uuid() seeded, or explicit UUIDs). AdminRepository should accept a SupabaseClient parameter for dependency injection — the test passes the locally-connected client directly.

Do not use AdminRlsGuard in integration tests; test the repository in isolation with a pre-authenticated client that already has org_id claims set via SQL (`select set_config('request.jwt.claims', ...)` in the seed). This avoids coupling two components under test simultaneously. Write a `README` in the test directory explaining how to spin up the local stack.

Testing Requirements

Integration tests using flutter_test against a real local Supabase instance. Tests are NOT unit tests — no mocking of the database layer. Seed the database with: 3 organisations (org_a, org_b, org_c), 10 users in org_a, 5 in org_b, 3 in org_c, each with varying certification states and activity counts. Test groups: (1) org_admin scope isolation — all list/aggregate methods return only org_a data when authed as org_a admin; (2) KPI accuracy — assert exact integer counts against known seed values; (3) model deserialisation — assert every field in every returned typed model is non-null where required; (4) pagination correctness — cursor-based page boundaries are stable; (5) role escalation — super_admin can read all orgs.

Use setUpAll to sign in once per group, tearDownAll to sign out.

Component
Admin Data Repository
data high
Epic Risks (3)
high impact medium prob security

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.

high impact medium prob technical

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.

high impact low prob security

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.