Implement Stats Repository with date-range and role filters

epic-activity-statistics-dashboard-data-foundation-task-008 — Build the Stats Repository Dart class that queries the Supabase materialized views. It must expose: fetchSnapshot(TimeWindow, chapterIds) → StatsSnapshot, fetchPeerMentorRows(TimeWindow, chapterId) → List<PeerMentorStatRow>, and fetchChartPoints(TimeWindow, chapterId, granularity) → List<ChartDataPoint>. All methods accept a DateTimeRange (from TimeWindowService) and a List<String> chapterIds (from ChapterScopeResolver). Use Supabase postgrest-dart client with .gte/.lte date filters. Return typed models from task-001.

critical priority medium complexity backend pending backend specialist Tier 4

Acceptance Criteria

`StatsRepository` is an abstract class with a `SupabaseStatsRepository` concrete implementation, enabling mock injection in tests

`fetchSnapshot(DateTimeRange range, List<String> chapterIds)` queries `mv_peer_mentor_stats`, applies `.gte('activity_date', range.start.toIso8601String())` and `.lte('activity_date', range.end.toIso8601String())`, and returns a `StatsSnapshot`

`fetchPeerMentorRows(DateTimeRange range, String chapterId)` queries `mv_peer_mentor_stats` filtered by `chapter_id` and date range, returning `List<PeerMentorStatRow>` sorted by total_hours descending

`fetchChartPoints(DateTimeRange range, String chapterId, Granularity granularity)` queries `mv_activity_time_series` and maps rows to `List<ChartDataPoint>`

When `chapterIds` contains multiple values, the query uses `.in_('chapter_id', chapterIds)` — not separate requests

All three methods throw a typed `StatsRepositoryException` (wrapping `PostgrestException`) on network or query failure — never raw exceptions

Empty result sets return empty lists / zero-value `StatsSnapshot`, not null or exceptions

Repository is registered as a Riverpod provider (`statsRepositoryProvider`) and is overridable in tests

All database column names are referenced via constants in a `StatsViewColumns` class — no magic strings in query calls

Method signatures match the interfaces expected by the Stats BLoC defined in the broader epic

Technical Requirements

frameworks

Flutter

Riverpod

supabase_flutter (postgrest-dart)

apis

Supabase PostgREST — mv_peer_mentor_stats view

Supabase PostgREST — mv_activity_time_series view

data models

StatsSnapshot

PeerMentorStatRow

ChartDataPoint

TimeWindow

Granularity

performance requirements

fetchSnapshot must complete in under 500 ms for a single chapter over a 12-month window

fetchPeerMentorRows must handle up to 500 peer mentor rows without pagination for now (add TODO for cursor pagination)

Queries must use PostgREST column selection (`select: 'col1,col2'`) to avoid fetching unused columns

security requirements

Repository relies on Supabase RLS — never bypass with service role key in client code

chapterIds list must be validated as non-empty before sending to PostgREST to prevent unbounded queries

DateTimeRange values must be converted to UTC before being passed to ISO 8601 strings to avoid timezone-related data leakage across chapters

Execution Context

Execution Tier

Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

View Full Execution Plan

Implementation Notes

Define `StatsRepository` as an abstract class (interface) first — this is critical for BLoC testability. The Riverpod provider should be a `Provider` that returns `SupabaseStatsRepository(supabase: ref.watch(supabaseClientProvider))`. Use `select:` to list exact columns in each query rather than `select: '*'` — this reduces payload size and makes schema changes explicit. For multi-chapter queries, PostgREST `.in_()` translates to `chapter_id=in.(a,b,c)` which is efficient if the view is indexed on `chapter_id`.

Add a `StatsViewColumns` constants class early to centralise column name strings — this prevents silent breakage when views are renamed. Convert all `DateTime` to UTC via `.toUtc().toIso8601String()` before passing to filters. Wrap the entire query in a try/catch that catches `PostgrestException` and rethrows as `StatsRepositoryException(code, message, originalException)`.

Testing Requirements

Unit tests: mock the Supabase client using `mocktail`, verify that correct PostgREST filter chains (`.gte`, `.lte`, `.in_`) are called for each method; verify that `PostgrestException` is wrapped into `StatsRepositoryException`; verify empty list handling. Integration tests (against local Supabase): seed two chapters with known activity counts; assert `fetchSnapshot` totals match seed data; assert RLS blocks cross-chapter access when authenticated as a restricted coordinator. Minimum 90% branch coverage on `SupabaseStatsRepository`.

Component

Stats Repository

data medium

Dependencies (3)

Create the typed Dart data models required by the statistics dashboard: StatsSnapshot (aggregate totals for a time window), PeerMentorStatRow (per-mentor row data), ChartDataPoint (x/y pair for chart rendering), and TimeWindow enum (week, month, quarter, year, custom). All models must be immutable, support JSON serialisation via fromJson/toJson, and include equality/hashCode. Field names must exactly mirror the Supabase view column names to eliminate mapping bugs. epic-activity-statistics-dashboard-data-foundation-task-001 Convert the SQL views from task-004 into materialized views (mv_peer_mentor_stats, mv_chapter_stats) with REFRESH CONCURRENTLY support. Add GIN indexes on chapter_id and date columns. Create a materialized view for chart data (mv_activity_time_series) with daily buckets suitable for bar-chart rendering. Include EXPLAIN ANALYZE targets: queries on 12-month windows must execute under 200 ms on a 100k-activity dataset. epic-activity-statistics-dashboard-data-foundation-task-005 Write and apply Row-Level Security policies on mv_peer_mentor_stats and mv_chapter_stats so that a coordinator can only read rows where chapter_id is within their authorized set (resolved via the coordinator_chapter_memberships join table). Peer mentors may only read their own rows. Service-role bypass must be explicitly locked to the Supabase edge function service key. Policies must be idempotent (CREATE POLICY IF NOT EXISTS). Include regression SQL that verifies cross-chapter reads are blocked. epic-activity-statistics-dashboard-data-foundation-task-006

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.

Quick Links

All Tasks Execution Plan