Repository query: peer mentor statistics feed methods
epic-bulk-and-proxy-registration-services-task-004 — Add query methods that aggregate activities by peer_mentor_id regardless of who recorded them, so peer mentor statistics correctly credit the attributed mentor. Implement getActivitiesAttributedToMentor(mentorId, {dateRange}) and getActivityCountsByMentor(chapterId, period) for chapter-scoped statistics. These views must exclude the coordinator identity to prevent double-counting in Bufdir reports.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
For getActivityCountsByMentor, use Supabase's `.select('peer_mentor_id, count', { count: 'exact' }).eq('organization_id', orgId)` with group-by via a Postgres view or RPC if Supabase's PostgREST client cannot express GROUP BY directly. Consider a Supabase Edge Function or a DB view `mentor_activity_counts` if complex grouping is needed. For getActivitiesAttributedToMentor, a simple `.eq('peer_mentor_id', mentorId)` suffices. The statistics model should be a separate `MentorActivityStats` class distinct from `ProxyAuditRecord` to prevent accidental coordinator identity leakage.
Coordinate with the annual_summary table — these methods feed the data that eventually populates annual summaries.
Testing Requirements
Unit tests with flutter_test and mocked Supabase: (1) proxy-recorded activity appears in mentor's statistics, (2) coordinator identity absent from stats model, (3) getActivityCountsByMentor sums correctly across mixed self/proxy rows, (4) period filter boundaries are inclusive on both ends, (5) mentor with zero activities returns count 0 not absent, (6) invalid chapterId returns UnauthorizedFailure, (7) Supabase error maps to typed failure. Integration test: seed three mentors in one chapter — two with self-recorded, one with proxy-recorded activities — verify counts match expected totals.
The Proxy Registration Service must verify that the coordinator has a legitimate assignment relationship with the target peer mentor before creating a record. If this check is implemented only in application code and not enforced at the DB/RLS level, a compromised or buggy client could bypass it by calling the Supabase endpoint directly, creating fraudulent proxy records for arbitrary peer mentors.
Mitigation & Contingency
Mitigation: Implement permission validation at two levels: (1) application-layer check in Proxy Registration Service that queries the assignments table before constructing the payload, and (2) RLS policy on the activities table that restricts INSERT to rows where recorded_by_user_id matches the authenticated user AND peer_mentor_id is in the set of peer mentors assigned to that coordinator. The RLS policy is the authoritative guard; the service-layer check provides early user-facing feedback.
Contingency: If RLS policy implementation is blocked by Supabase plan constraints, implement a Supabase Edge Function as a proxy endpoint that enforces the permission check server-side before forwarding to the DB. Disable direct client inserts entirely for proxy activities.
For a bulk session with 30 selected peer mentors, the Proxy Duplicate Detector must query existing activities for each mentor. If implemented as 30 sequential Supabase queries, round-trip latency could make the bulk confirmation screen feel slow (>3s), degrading coordinator experience and potentially causing timeouts.
Mitigation & Contingency
Mitigation: Implement the duplicate check as a single Supabase query using an IN clause on peer_mentor_id combined with the activity_type and date filters, returning all potential duplicates for the entire batch in one network round-trip. Group results client-side by mentor ID to produce the per-mentor warning structure.
Contingency: If the single-query approach returns too much data for very large chapters, add a database index on (peer_mentor_id, activity_type, date) and profile query time. If still insufficient, accept a short loading state on the confirmation screen with a progress indicator rather than pre-loading duplicates before navigation.