Write integration tests for Stats Repository against Supabase
epic-activity-statistics-dashboard-data-foundation-task-011 — Write integration tests that run against a Supabase local dev instance (supabase start) and verify: fetchSnapshot returns correct totals for a seeded dataset; RLS prevents cross-chapter access when authenticated as a restricted coordinator; date-range filters correctly exclude out-of-window rows; and empty results are handled without exceptions. Seed SQL scripts must insert activities across two chapters and two peer mentors to validate scoping.
Acceptance Criteria
Technical Requirements
Execution Context
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.
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.
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.
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.