Implement Chapter Scope Resolver with chapter ID caching

epic-activity-statistics-dashboard-data-foundation-task-003 — Build the Chapter Scope Resolver that loads and caches a coordinator's authorized chapter IDs from Supabase. The resolver must query the organization_units / chapter membership tables using the authenticated user's JWT, cache results in-memory with a 5-minute TTL, and expose a getAuthorizedChapterIds() Future that throws an AuthorizationException if the caller is not a coordinator. Invalidation must occur on session change. This component gates all coordinator-scoped stats queries.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

getAuthorizedChapterIds() returns a Future<List<String>> containing the chapter IDs the current coordinator is authorized for, queried from Supabase using the active session JWT

Result is cached in-memory; a second call within 5 minutes does NOT trigger a new Supabase network request (verified by mock: Supabase client called exactly once)

Cache expires after 5 minutes: a call made after TTL expiry triggers a fresh Supabase query

getAuthorizedChapterIds() throws AuthorizationException (custom typed exception) if the current user's role is not 'coordinator' or 'org_admin'

Cache is invalidated and cleared when the Supabase auth session changes (sign-in, sign-out, token refresh that changes user)

If the Supabase query fails (network error), the exception propagates as-is (no silent empty list fallback); callers must handle errors

The resolver is injectable via Riverpod provider so it can be replaced with a mock in tests

Integration test (with Supabase test instance or mock) verifies that a coordinator receives their chapters and a non-coordinator receives AuthorizationException

Technical Requirements

frameworks

Flutter

Dart

Riverpod

apis

Supabase PostgREST API — coordinator_chapter_memberships table

Supabase Auth — session stream

data models

coordinator_chapter_memberships

user_roles

performance requirements

Cache hit path must be synchronous-equivalent — no async overhead beyond a Future.value()

Cache miss (first load) must complete within 500 ms on a standard mobile connection

security requirements

JWT used for the Supabase query is the Supabase SDK's active session token — never stored or logged

Cache must be invalidated on sign-out to prevent stale cross-user data leaks

AuthorizationException must not reveal role details in its message (no 'user is role X' strings)

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Place at lib/features/statistics/data/services/chapter_scope_resolver.dart. Expose as a Riverpod Provider so the stats repository can depend on it. For TTL tracking, store the cache timestamp alongside the data (DateTime cacheTime) and compare against DateTime.now() on each call — avoid Timer-based expiry to keep the class testable. Listen to Supabase.instance.client.auth.onAuthStateChange stream in the constructor (or via a Riverpod ref.listen) to trigger invalidation; dispose the subscription in a dispose() method or via Riverpod's autoDispose.

The Supabase query should use .select('chapter_id').eq('coordinator_id', userId) on the coordinator_chapter_memberships table — confirm exact table/column names with the DB migration before implementing. Throw a domain-level AuthorizationException rather than a generic Exception so BLoC/Riverpod layers can catch it specifically and show a proper access-denied UI state.

Testing Requirements

Unit tests using flutter_test with a mocked Supabase client (Mockito or manual stub): (1) first call performs network request and caches result; (2) second call within TTL returns cache without network; (3) call after TTL expiry re-fetches; (4) non-coordinator role throws AuthorizationException; (5) session change clears cache (simulate by calling the session-change callback and verifying next call re-fetches); (6) Supabase network error propagates correctly. Integration test against a real Supabase test project (or local Supabase CLI instance) verifying end-to-end chapter ID retrieval for a seeded coordinator account.

Component

Chapter Scope Resolver

service medium

Dependencies (1)

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

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