Build StatsRepository with period and activity-type filters

epic-coordinator-statistics-dashboard-foundation-task-006 — Implement the StatsRepository class that queries the pre-aggregated Supabase views with period and activity-type filters. Expose methods: getCoordinatorStats(orgId, coordinatorId, period, activityType), getPeerMentorStats(orgId, coordinatorId, period), and getOrgStats(orgId, period). Integrate RoleAccessValidator before any query execution. Return typed Dart model objects.

critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

StatsRepository exposes three typed async methods: getCoordinatorStats, getPeerMentorStats, and getOrgStats — all returning sealed Result<T, StatsError> types

getCoordinatorStats filters by org_id, coordinator_id, date range (period.start to period.end), and optionally by activity_type_id

getPeerMentorStats returns a list of per-peer-mentor aggregates for all mentors under a given coordinator within the period

getOrgStats returns org-wide aggregates without coordinator scoping — accessible only to org-admin role

RoleAccessValidator.assertCanViewStats(actor, orgId, targetId) is called before every query; unauthorized calls throw AccessDeniedException without touching Supabase

All three methods accept an optional activityTypeId filter (null = all types)

Returned Dart model objects are immutable (final fields, const constructors) and include: totalActivities, totalDurationMinutes, uniqueContacts, activitiesByType Map, and period metadata

Supabase query errors are caught and mapped to typed StatsError variants (networkError, notFound, permissionDenied)

Repository is fully injectable via Riverpod Provider and has no static state

Null org_id or empty period range throws ArgumentError before any network call

Technical Requirements

frameworks

Flutter

Dart

Riverpod

apis

Supabase PostgreSQL 15

data models

activity

activity_type

annual_summary

assignment

performance requirements

Each repository method must complete in <2 s on a 4G connection for datasets up to 1,000 activities

Supabase queries must use pre-aggregated views — no client-side aggregation of raw activity rows

Period filter must be applied server-side using indexed date columns

security requirements

org_id must always be sourced from the authenticated JWT claims, not a client parameter, to prevent horizontal privilege escalation

RLS on Supabase views provides the last line of defence — repository must not assume RLS is sufficient and must still call RoleAccessValidator

No raw SQL strings constructed from user input — use Supabase SDK parameterized queries only

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Define a StatsPeriod value object with start and end DateTime fields validated on construction (start must be before end). Use Supabase's .from('coordinator_stats_view').select().eq('org_id', orgId).eq('coordinator_id', coordinatorId).gte('date', period.start.toIso8601String()).lte('date', period.end.toIso8601String()) pattern. Model the return types with freezed or manual immutable classes — do not return raw Map. Structure: abstract class StatsRepository with a SupabaseStatsRepository implementation for easy mocking in tests.

Register via Riverpod: final statsRepositoryProvider = Provider((ref) => SupabaseStatsRepository(ref.watch(supabaseClientProvider), ref.watch(roleAccessValidatorProvider)));

Testing Requirements

Unit tests with mocked SupabaseClient covering: (1) correct query parameters forwarded for each method; (2) RoleAccessValidator called before Supabase client for each method; (3) AccessDeniedException thrown when validator rejects; (4) network error mapped to StatsError.networkError; (5) empty result set returns empty model, not null or exception; (6) null activityType returns unfiltered results. Integration tests against a local Supabase instance verifying RLS blocks cross-org access even with direct client credentials. Target 90%+ branch coverage.

Component

Stats Repository

data medium

Dependencies (2)

Implement the RoleAccessValidator class that enforces coordinator-scoped vs org-admin-scoped query boundaries as a defence-in-depth complement to Supabase RLS. Validate that coordinators can only query their own chapter's data and that org-admins can query across their organization. Throw typed access denial exceptions for out-of-scope queries. epic-coordinator-statistics-dashboard-foundation-task-003 Write the database migration that creates pre-aggregated Supabase views keyed by org_id + coordinator_id + month. Views must replace real-time aggregation of activity rows with sub-100ms indexed lookups. Include indexes on the composite key, period filtering columns, and activity_type columns. Sequence and test the migration against a staging Supabase instance. epic-coordinator-statistics-dashboard-foundation-task-002

Epic Risks (3)

high impact medium prob technical

Pre-aggregated Supabase views may still be slow for orgs with very large activity datasets (NHF with 1,400 chapters). If the view query plan performs sequential scans, dashboard load times could exceed acceptable thresholds and degrade the perceived value of the feature.

Mitigation & Contingency

Mitigation: Design views with composite indexes on (org_id, coordinator_id, month) from the start. Run EXPLAIN ANALYZE during development against a seeded dataset of realistic scale. Add materialized view refresh strategy if needed.

Contingency: If live view performance is insufficient, convert to materialized views refreshed on a schedule or on activity-write triggers. Expose the refresh delay transparently in the UI with a 'last updated' timestamp.

high impact low prob security

Supabase RLS policies for the stats views may not be configured correctly during initial migration, potentially allowing cross-coordinator data leakage before the RoleAccessValidator layer is reached. This is a security and compliance risk.

Mitigation & Contingency

Mitigation: Write RLS integration tests as part of this epic that explicitly verify a coordinator JWT cannot read another coordinator's stats rows. Apply RLS policies in the migration script itself, not as a manual step.

Contingency: If an RLS gap is discovered post-deployment, immediately disable the stats screen via a feature flag, apply the corrected RLS migration, and re-enable after verification. Log and audit all queries that ran during the gap window.

medium impact medium prob integration

Cache invalidation logic may not be triggered correctly when a new activity is registered by a peer mentor or when an expense approval is granted. Stale data could cause coordinators to make decisions based on outdated KPIs, undermining trust in the dashboard.

Mitigation & Contingency

Mitigation: Define explicit invalidation event contracts with the activity registration and expense approval pipelines. Implement an event bus subscription within StatsCacheManager. Document the invalidation contract in code.

Contingency: If event-driven invalidation proves unreliable, add a manual 'Refresh' pull-to-refresh gesture on the dashboard and reduce TTL to 5 minutes as a fallback degradation strategy.

Quick Links

All Tasks Execution Plan