Implement MentorActivityLogRepository aggregated statistics fetch

epic-peer-mentor-detail-screen-data-layer-task-006 — Implement fetchActivitySummary(String mentorId, ActivityPeriod period) in MentorActivityLogRepository. The method must query aggregated activity statistics from the mentor_activity_logs table (total sessions, total hours, unique contacts reached), apply period filters (last 30 days, last 90 days, year-to-date, all time), and return a typed MentorActivitySummary. Use Supabase aggregate functions or a dedicated RPC call to minimize data transfer.

critical priority low complexity backend pending backend specialist Tier 1

Acceptance Criteria

MentorActivityLogRepository class exists under lib/features/peer_mentor/data/repositories/ and is registered as a Riverpod provider

fetchActivitySummary(String mentorId, ActivityPeriod period) is an async method returning Future<MentorActivitySummary>

Method correctly translates ActivityPeriod.last30Days to a date range of now minus 30 days, last90Days to now minus 90 days, yearToDate to January 1st of the current year, allTime to no date filter

Statistics returned include: total_sessions (count of log rows), total_hours (sum of duration_hours), unique_contacts_reached (count distinct contact_id)

Aggregation is performed server-side: either via a Supabase RPC function (preferred) or via PostgREST aggregate query — client receives pre-aggregated numbers, not raw log rows

Response is mapped to MentorActivitySummary via MentorActivitySummary.fromJson()

When no activity logs exist for the given period, method returns MentorActivitySummary with all numeric fields set to zero — not an exception

Throws RepositoryException for Supabase errors

mentorId is validated as non-empty before the Supabase call

Unit tests pass for: allTime with records, last30Days with date filter applied, empty result mapped to zero-value summary, RepositoryException on Supabase error

Technical Requirements

frameworks

Flutter

Riverpod

Dart

apis

Supabase RPC — preferred: call a Postgres function get_mentor_activity_summary(p_mentor_id, p_start_date, p_end_date) that returns aggregated JSON

Supabase PostgREST aggregate fallback — if RPC not available, use count/sum via PostgREST aggregate syntax

data models

MentorActivitySummary

ActivityPeriod (enum)

performance requirements

Server-side aggregation is mandatory — do NOT fetch all log rows and aggregate client-side; this would break for mentors with 100+ annual sessions

Date range calculation must use UTC to avoid timezone edge cases affecting period boundaries

security requirements

The RPC function (if used) must execute with row-level security context of the calling user — use SECURITY INVOKER, not SECURITY DEFINER, unless the function is explicitly reviewed for privilege escalation

Only the authenticated mentor or their coordinator/admin should be able to query this data — enforce via RLS on mentor_activity_logs or via function parameter validation

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Prefer a Supabase Postgres RPC function for aggregation: CREATE OR REPLACE FUNCTION get_mentor_activity_summary(p_mentor_id uuid, p_start_date timestamptz DEFAULT NULL, p_end_date timestamptz DEFAULT NULL) RETURNS json. This keeps aggregation logic in the database, avoids large data transfers, and can be optimized with indexes independently of the Flutter app. Call via _supabase.rpc('get_mentor_activity_summary', params: {'p_mentor_id': mentorId, 'p_start_date': startDate?.toIso8601String(), 'p_end_date': endDate?.toIso8601String()}). For date range computation: use DateTime.now().toUtc() as the reference point; for yearToDate, compute DateTime(now.year, 1, 1, 0, 0, 0, 0, 0) in UTC.

If the RPC approach is not yet available (function not created), implement a fallback that fetches raw rows with date filters and aggregates in Dart — clearly mark this as a temporary workaround with a TODO and the RPC ticket reference. The zero-value fallback for empty periods should be implemented in fromJson by defaulting all numeric fields to 0 when the JSON values are null.

Testing Requirements

Unit tests using flutter_test with mocked SupabaseClient. Test file: test/features/peer_mentor/data/repositories/mentor_activity_log_repository_test.dart. Required cases: (1) allTime period produces call without date filter and maps to populated MentorActivitySummary, (2) last30Days period passes correct start_date (today minus 30 days in UTC) and end_date (today in UTC) to the query or RPC, (3) last90Days period passes correct start_date (today minus 90 days), (4) yearToDate period passes January 1st of current year as start_date, (5) Supabase returns empty/zero aggregates and result maps to MentorActivitySummary with all zeros, (6) RepositoryException thrown on PostgrestException. For date assertions, freeze the clock using a Clock abstraction or pass DateTime as parameter to allow deterministic testing.

Component

Mentor Activity Log Repository

data low

Dependencies (1)

Create the four core Dart model classes: PeerMentorProfile, CertificationRecord, MentorActivitySummary, and AssignedContact. Each model must include fromJson/toJson factory constructors for Supabase JSON mapping, null-safe fields, and copyWith methods. Models must accurately represent the Supabase table schemas including all nullable fields for multi-tenant branching. epic-peer-mentor-detail-screen-data-layer-task-001

Epic Risks (3)

high impact medium prob security

Supabase RLS policies for peer mentor data may block coordinator queries if the RLS rules are written for peer-mentor-self access only, requiring policy updates that affect other features sharing the same tables.

Mitigation & Contingency

Mitigation: Review existing RLS policies on peer_mentors, certification_records, and activity_log tables before writing repository queries. Coordinate with the database team to add coordinator-role predicates without weakening existing mentor-self policies.

Contingency: If policy changes are blocked, implement a Supabase Edge Function as a secure query proxy that enforces authorization server-side, avoiding direct RLS policy modification.

medium impact medium prob technical

The activity log table schema may not have a mentor_id foreign key column or may require a JOIN through an intermediate table, making the aggregation query significantly more complex than anticipated.

Mitigation & Contingency

Mitigation: Inspect the actual Supabase activity_log table schema before starting the MentorActivityLogRepository implementation. Document the exact JOIN path needed and validate it returns correct results for a known mentor.

Contingency: If schema requires complex multi-table aggregation, implement a Supabase database function (RPC) and expose it via the repository's fetchSummary method to keep Dart code clean.

high impact low prob dependency

The Blindeforbundet assignment table may not yet exist in the shared Supabase schema or may have a different structure than assumed, blocking the AssignmentHistoryRepository implementation.

Mitigation & Contingency

Mitigation: Verify the assignments table exists and confirm its column structure with the Contact Detail & Edit Screen team which also depends on assignment data (assignment-repository in that feature).

Contingency: If the assignments table is not yet available, implement the AssignmentHistoryRepository with a stub returning empty list and a TODO marker, unblocking the aggregation service while the schema is finalized.

Quick Links

All Tasks Execution Plan