Implement Supabase queries for annual stats aggregation

epic-annual-impact-summary-core-services-task-004 — Write the Supabase RPC calls and PostgREST queries that retrieve activity records filtered by peer mentor user ID and a rolling date window. Queries must compute total session hours, count distinct contacts helped, and return per-activity-type counts. Include proper RLS policy compliance and ensure queries work against both online Supabase and can be adapted for offline cache reads.

critical priority medium complexity database pending database specialist Tier 2

Acceptance Criteria

A Supabase SQL function (RPC) named get_annual_stats is created and deployed, accepting parameters: p_user_id (uuid), p_start_date (timestamptz), p_end_date (timestamptz), and returning a single JSON object with fields: total_hours (numeric), unique_contacts (integer), activity_type_counts (jsonb key-value of activity_type → count)

The RPC function is defined with SECURITY DEFINER and includes an explicit check that auth.uid() == p_user_id — returning null rather than raising an exception to avoid information leakage

The function filters on the activities table using created_at BETWEEN p_start_date AND p_end_date and peer_mentor_id = p_user_id

total_hours is computed as SUM(duration_minutes) / 60.0 — not a simple row count

unique_contacts is COUNT(DISTINCT contact_id) excluding NULL contact_id rows

activity_type_counts is produced with jsonb_object_agg(activity_type, cnt) from a subquery grouping by activity_type

A corresponding Dart repository method fetchAnnualStats(String userId, SummaryPeriod period) wraps the RPC call using supabase.rpc('get_annual_stats', params: {...}) and deserialises the response into a raw Map<String, dynamic>

The repository method throws a typed AnnualStatsException (with a reason enum: networkError, authError, parseError) instead of raw PostgrestException so callers are shielded from Supabase internals

Integration test (against a local Supabase instance or a test project) verifies the RPC returns correct totals for a seeded dataset of 5 activities with known hours and contacts

Technical Requirements

frameworks

Flutter

Supabase Dart client

apis

Supabase RPC (get_annual_stats)

Supabase PostgREST REST API

data models

AnnualStatsResult

SummaryPeriod

AnnualStatsException

performance requirements

RPC execution time must be under 500ms for a user with up to 500 activity records — add a composite index on (peer_mentor_id, created_at) if not already present

The Dart repository method must time out after 10 seconds and throw AnnualStatsException(reason: networkError)

security requirements

RLS policies on the activities table must ensure users can only SELECT their own rows — the RPC adds a belt-and-suspenders auth.uid() check on top

p_user_id must be validated as a valid UUID format before the query runs — invalid format returns null

No raw SQL is constructed in Dart — all parameterisation happens server-side in the SQL function to prevent injection

The RPC must not expose other users' data under any input combination — test with a mismatched user ID to confirm null return

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Write the SQL function in a migration file under supabase/migrations/ so it is version-controlled and reproducible. Use LANGUAGE plpgsql for the function body to allow the auth.uid() guard check before the main query. The Dart repository class (AnnualStatsRepository) should accept a SupabaseClient via constructor injection — this makes it straightforward to substitute a mock client in unit tests. Define AnnualStatsException as a sealed class with a reason enum (networkError, authError, parseError, unexpectedError) and an optional message field — this gives BLoC/Riverpod consumers a clean error discrimination surface without importing Supabase types.

Cache the raw Map response (not the parsed model) so the offline cache layer (task-006) can store and replay it without needing to re-serialise the domain model.

Testing Requirements

Two test layers: (1) Unit tests — mock the Supabase client using a fake implementation; test that fetchAnnualStats passes correct parameters to supabase.rpc(); test that a successful response is deserialised to Map without error; test that a PostgrestException is caught and re-thrown as AnnualStatsException with the correct reason enum; test that a timeout triggers networkError. (2) Integration tests — requires a local Supabase instance (supabase start); seed the activities table with 3 activities of known durations and 2 unique contacts; call the RPC and assert returned total_hours, unique_contacts, and activity_type_counts match expected values; run with flutter test --tags integration so they are excluded from CI unit-test runs by default.

Component

Annual Stats Aggregation Service

service high

Dependencies (1)

Define the Dart data classes for AnnualStatsResult (total_hours, unique_contacts_helped, activity_type_distribution as Map<String,double>, streak_days, period_start, period_end), MilestoneDefinition (id, threshold, metric_key, significance_rank, description_template), and MilestoneResult (definition, is_newly_unlocked, formatted_description). These models are the shared contracts between the aggregation service, detection service, and BLoC. epic-annual-impact-summary-core-services-task-003

Epic Risks (3)

high impact medium prob integration

Activity records may contain duplicate entries (as evidenced by the duplicate-detection feature dependency) or proxy-registered activities that should be attributed differently. Including duplicates or mis-attributed records would produce inflated stats, undermining trust in the summary.

Mitigation & Contingency

Mitigation: Implement the aggregation query to join against the deduplication-reviewed-flag on activity records and filter out unresolved duplicates. Coordinate with the duplicate-detection feature team to confirm the authoritative flag field before implementing the RPC. Include a data-quality warning in the summary when unresolved duplicates are detected.

Contingency: If deduplication state is unreliable at release time, add a prominent disclaimer in the summary UI noting that figures reflect all registered activities and may include duplicates pending review. Track a follow-up task to re-aggregate after deduplication runs.

medium impact high prob scope

Each organisation wants to define their own milestone thresholds (e.g., NHF's counting model differs from HLF's certification model). Implementing configurable thresholds may expand scope significantly if the configuration UI is expected in this epic.

Mitigation & Contingency

Mitigation: Scope this epic strictly to the evaluation engine and a hardcoded default threshold set. Define the MilestoneDefinition interface with an organisation_id discriminator so per-org configs can be loaded from the database in a later sprint. Build the admin configuration UI as a separate follow-on task outside this epic.

Contingency: If stakeholders require per-org milestone configuration before launch, deliver a JSON-based configuration file per org as an interim solution, loaded from Supabase storage, until a full admin UI is built.

medium impact medium prob technical

Android 13+ restricts access to media collections and requires READ_MEDIA_IMAGES permission for gallery saves, while older Android versions use WRITE_EXTERNAL_STORAGE. Handling both permission models correctly across the device matrix is error-prone.

Mitigation & Contingency

Mitigation: Use the permission_handler Flutter package with version-aware permission requests abstracted behind the summary-share-service interface. Write platform-specific unit tests for both Android API levels in the test harness. Test on a minimum of three Android versions (API 29, 32, 34) in CI.

Contingency: If gallery save is broken on specific Android versions at launch, disable the 'Save to gallery' option on affected API levels and surface only clipboard and system share sheet, which require no media permissions.

Quick Links

All Tasks Execution Plan