🏠 Overview › ✅ Implementation Tasks ›✅ epic-achievement-badges-evaluation-engine-task-006

Implement peer mentor stats aggregation for evaluation

epic-achievement-badges-evaluation-engine-task-006 — Implement the stats aggregation layer within the badge evaluation service that computes a peer mentor's current totals (activity counts per type, honorar counts, streak data, certifications) from the Supabase database. This aggregation feeds all three evaluator types and must be efficient enough to complete within edge function timeout limits.

critical priority high complexity backend pending backend specialist Tier 2

Acceptance Criteria

Aggregator returns a `PeerMentorStats` object containing: activityCountsByType (Map<activity_type, number>), totalActivities, honorarCount, currentStreakDays, longestStreakDays, completedCourseIds (string[]), lastActivityDate
Activity counts are computed from the `activities` table filtered by peer_mentor_id and org_id
Honorar count reflects only activities where `honorar = true` (or equivalent reimbursement flag), matching Blindeforbundet's 3rd/15th threshold logic
Streak calculation is based on consecutive calendar days with at least one registered activity
Certifications are read from `peer_mentor_certifications` with status filter: only 'completed' (not 'expired' or 'revoked') count toward completedCourseIds
All aggregation queries complete within 3 seconds total (Supabase edge function default timeout is 10 s — this component must not dominate it)
Aggregation uses a single database round-trip per data domain (activities, certifications) — maximum 3 queries total
Returns sensible zero-values (not null/undefined) for a peer mentor with no recorded activity
Aggregation is idempotent — repeated calls with the same peer_mentor_id return identical results for unchanged data
Streak of 0 is returned (not an error) when the peer mentor has never registered an activity

Technical Requirements

frameworks
Supabase Edge Functions (Deno runtime)
supabase-js v2
apis
Supabase PostgREST: `GET /rest/v1/activities?peer_mentor_id=eq.{id}&org_id=eq.{orgId}&select=activity_type,created_at,honorar`
Supabase PostgREST: `GET /rest/v1/peer_mentor_certifications?peer_mentor_id=eq.{id}&status=eq.completed&select=course_id`
Supabase RPC (optional): Consider a Postgres function `get_peer_mentor_stats(peer_mentor_id, org_id)` to reduce round-trips
data models
activities (peer_mentor_id, org_id, activity_type, created_at, honorar)
peer_mentor_certifications (peer_mentor_id, course_id, status, expires_at)
performance requirements
Total aggregation wall-clock time under 3 seconds on Supabase free tier
Activities query must hit index on (peer_mentor_id, org_id) — verify index exists
Streak calculation must be O(n log n) at worst (sort dates, scan once) — no nested loops over activities
Consider Postgres-side aggregation via RPC for mentors with >500 activities to avoid large payloads over the network
security requirements
Use service-role client — RLS not relied upon here, but ensure org_id filter is always explicitly applied in every query
Never expose raw activity records outside the aggregation layer — return only the computed `PeerMentorStats` struct

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

The streak calculation is the most algorithmically complex part. Approach: fetch all activity `created_at` timestamps, extract unique calendar dates (normalize to UTC date strings), sort ascending, then scan linearly counting consecutive days. Store both `currentStreakDays` (streak ending on today or yesterday) and `longestStreakDays` (all-time best). For Blindeforbundet's honorar milestone logic: count activities where `honorar = true` — the evaluator (task-004 is training, task-002/003 cover threshold/streak) will compare this count against the badge definition threshold (3 or 15).

Keep the aggregation layer agnostic of specific thresholds — it just counts. Consider a Postgres RPC function to push aggregation server-side if payload sizes become large, but start with client-side aggregation for simplicity and optimize if needed.

Testing Requirements

Unit tests for streak calculation logic are highest priority — test with: (1) activities on consecutive days, (2) gap of 1 day breaking the streak, (3) multiple activities on same day counting as 1 streak day, (4) activities in non-chronological insertion order, (5) single activity (streak = 1), (6) no activities (streak = 0). Integration tests (against a Supabase test project or local Supabase CLI) should verify correct counts for a seeded dataset with known totals. Use `deno test --allow-net` for integration tests gated behind an env flag.

Component
Badge Evaluation Service
service high
Dependencies (3)
Implement the StreakCriteriaEvaluator class that detects consecutive activity streaks within a defined time window. Must query peer mentor activity history and compute current streak length, comparing against the badge definition's required streak threshold. epic-achievement-badges-evaluation-engine-task-003 Implement the TrainingCompletionCriteriaEvaluator class that verifies whether a peer mentor has completed a required certification or training course. Reads completion status from the peer mentor's certification records and matches against badge definition requirements. epic-achievement-badges-evaluation-engine-task-004 Implement the ThresholdCriteriaEvaluator class in Deno/TypeScript that checks whether a peer mentor's aggregated activity count or honorar count meets or exceeds a configured numeric threshold. Must correctly handle the Blindeforbundet 3rd and 15th assignment honorar detection logic. epic-achievement-badges-evaluation-engine-task-002
Epic Risks (3)
medium impact medium prob technical

Supabase Edge Functions may experience cold start latency of 500ms–2s when they have not been invoked recently. If evaluation latency consistently exceeds the 2-second UI expectation, the celebration overlay timing SLA cannot be met without the optimistic UI fallback from the UI epic.

Mitigation & Contingency

Mitigation: Keep the edge function warm by scheduling a lightweight health-check invocation every 5 minutes in production. Optimise the function size to minimise Deno module load time. Implement the optimistic UI path in badge-bloc (from the UI epic) as the primary UX path so cold start only affects server-side reconciliation, not perceived responsiveness.

Contingency: If cold starts remain problematic, migrate badge evaluation to a Supabase database function (pl/pgsql) triggered directly by a database trigger on activity insert, eliminating the Edge Function overhead entirely for the evaluation logic while keeping Edge Function only for FCM notification dispatch.

high impact low prob integration

Supabase database webhooks can fail silently if the edge function returns a non-2xx response or times out. A missed webhook means a peer mentor does not receive a badge they earned, which is both a functional defect and a trust issue for organisations relying on milestone tracking.

Mitigation & Contingency

Mitigation: Implement idempotent webhook processing: the edge function reads the activity ID from the webhook payload and checks whether evaluation for this activity has already run (via an audit log query) before proceeding. Add Supabase webhook retry configuration (3 retries with exponential backoff). Monitor webhook failure rates via Supabase logs alert.

Contingency: Implement a nightly reconciliation job (Supabase scheduled function) that scans all activities from the past 24 hours, re-evaluates badge criteria for any peer mentor with no corresponding evaluation log entry, and awards any missing badges. Alert operations if reconciliation awards more than 5% of badges, indicating systematic webhook failure.

high impact low prob security

The evaluation service loads badge definitions per organisation, but a misconfigured RLS policy or incorrect organisation scoping in the edge function could cause one organisation's badge criteria to be evaluated against another organisation's peer mentor activity data, leading to incorrect or cross-contaminated badge awards.

Mitigation & Contingency

Mitigation: The edge function must extract organisation_id from the webhook payload activity record and pass it explicitly to every database query. Write a security test that seeds two organisations with distinct badge definitions and verifies that evaluating a peer mentor in org A never reads or awards org B definitions. Use Supabase service role key only within the edge function, never the anon key.

Contingency: If cross-org contamination is detected in audit logs, immediately disable the edge function webhook, run a targeted SQL query to identify and revoke incorrectly awarded badges, notify affected organisations, and perform a full security review of all RLS policies on badge-related tables before re-enabling.

Quick Links
All Tasks Execution Plan