high priority medium complexity testing pending testing specialist Tier 5

Acceptance Criteria

Integration test file exists at `test/integration/stats_repository_integration_test.dart` and is excluded from standard `flutter test` (requires `--tags integration` or a separate test entry point)
Seed script `supabase/tests/seeds/stats_seed.sql` inserts: 2 chapters (`chapter-a`, `chapter-b`), 2 peer mentors per chapter, 10 activities in the current 30-day window for chapter-a, 5 activities outside the window, 3 activities in chapter-b
`fetchSnapshot` with `chapter-a` scope returns total_activities = 10 and total_hours matching the seeded values (no off-by-one from boundary dates)
`fetchSnapshot` with `chapter-b` scope returns total_activities = 3 — confirming chapter isolation
When authenticated as a coordinator with RLS scope restricted to `chapter-a`, calling `fetchPeerMentorRows` with `chapter-b` returns an empty list (not an error — RLS silently filters)
Date-range filter: `fetchSnapshot` with a window ending yesterday excludes today's activities — verified with a seeded activity timestamped today
Empty chapter (no activities): `fetchSnapshot` returns `StatsSnapshot` with all numeric fields = 0, not null or exception
Test setup and teardown run `supabase db reset` or truncate seed tables to ensure test isolation
All assertions use exact equality on numeric fields — no `greaterThan` for totals that should be deterministic
Tests complete in under 30 seconds on a local machine with `supabase start` running

Technical Requirements

frameworks
flutter_test
supabase_flutter
supabase-cli (local dev)
apis
Supabase local instance — mv_peer_mentor_stats
Supabase local instance — mv_activity_time_series
Supabase Auth (test user JWTs for RLS testing)
data models
StatsSnapshot
PeerMentorStatRow
activities (seed)
chapters (seed)
users (seed)
performance requirements
Full integration test suite must complete in under 30 seconds on local Supabase
Seed script must complete in under 5 seconds
security requirements
Use Supabase anon key for local tests — never commit service_role key to the test suite
RLS test JWTs must be generated for test-only users and never reuse production credentials
Seed data must use synthetic names only (e.g., 'Test User A') — no real user data

Execution Context

Execution Tier
Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

Implementation Notes

The key challenge is RLS testing: you need two Supabase JWT tokens with different `chapter_id` claims. Use Supabase's `supabase.auth.signInWithPassword` with two pre-seeded test users who have different role assignments in the database. After signing in as user-A (chapter-a scoped), all queries run through that user's RLS policies. Do not use the service role key to bypass RLS in these tests — that defeats the purpose.

For the seed script, use explicit `INSERT INTO activities (id, chapter_id, activity_date, ...)` with hardcoded UUIDs so the assertions can reference exact values. For CI, add a `supabase start` step before the test job and `supabase stop` after — document this in `supabase/README.md`. Use `flutter test integration_test/` or a dedicated test directory to keep integration tests separate from unit tests and avoid running them on every `flutter test` invocation.

Testing Requirements

This task IS the integration testing task. Structure with `setUpAll` that: (1) starts local Supabase (assume already running in CI), (2) runs seed SQL via `supabase.rpc('exec_sql', ...)` or the Supabase CLI, (3) authenticates test users via `supabase.auth.signInWithPassword`. Use `tearDownAll` to truncate seeded rows. Test groups: `group('fetchSnapshot', ...)` covering happy path, cross-chapter isolation, date boundary, empty chapter; `group('fetchPeerMentorRows', ...)` covering correct row count and sort order, RLS restriction; `group('fetchChartPoints', ...)` covering granularity bucketing.

CI must run `supabase start` before executing this test suite — document this in the test file header comment.

Component
Stats Repository
data medium
Epic Risks (3)
medium impact medium prob technical

Materialized views over large activity tables may have refresh latency exceeding the 2-second SLA under high insert load, causing stale data to appear on the dashboard immediately after a peer mentor registers an activity.

Mitigation & Contingency

Mitigation: Design the materialized view refresh trigger to run asynchronously via a Supabase Edge Function rather than a synchronous trigger, and set a maximum staleness tolerance of 5 seconds documented in the feature spec. Add a CONCURRENTLY refresh strategy so reads are never blocked.

Contingency: If refresh latency cannot meet SLA, fall back to a regular (non-materialized) view for the dashboard and accept slightly higher query cost per request. Revisit materialized approach once Supabase pg_cron or background workers are available.

high impact medium prob integration

The aggregation counting rules for the dashboard may diverge from those used in the Bufdir export pipeline (e.g., which activity types count, how duplicate registrations are handled), creating a reconciliation burden for coordinators at reporting time.

Mitigation & Contingency

Mitigation: Run the BufDir Alignment Validator against a shared reference dataset before any view is merged to main. Encode the counting rules as a shared Supabase function called by both the stats views and the export query builder so there is a single source of truth.

Contingency: If divergence is discovered post-launch, ship a visible banner on the dashboard stating that numbers are indicative and may differ from the export until the reconciliation fix is deployed. Prioritize the fix as a P0 defect.

high impact low prob security

Multi-chapter coordinators (up to 5 chapters per NHF requirement) require RLS policies that filter on an array of chapter IDs, which is more complex than single-value RLS and could be misconfigured, leaking data across chapters or blocking legitimate access.

Mitigation & Contingency

Mitigation: Write integration tests that verify cross-chapter isolation for a coordinator assigned to chapters A and B cannot see data from chapter C. Use parameterized RLS policies with auth.uid()-based chapter lookup to avoid hardcoded values.

Contingency: If RLS misconfiguration is detected in testing, temporarily restrict coordinator queries to single-chapter scope (coordinator's primary chapter) and ship multi-chapter support as a fast-follow patch once RLS logic is verified.