Implement milestone threshold evaluator in trigger engine

epic-scenario-push-engagement-core-engine-task-006 — Add the milestone scenario evaluator to the trigger engine: query cumulative session count for the current calendar year per peer mentor, compare against configurable milestone thresholds (e.g. 50 sessions), and trigger a congratulatory notification exactly once per threshold crossing. Store the triggered milestone in the scenario notification repository to prevent re-firing.

high priority medium complexity backend pending backend specialist Tier 3

Acceptance Criteria

Function `evaluateMilestones(userId, currentYearSessionCount, config, preferenceService, notificationRepo)` returns `TriggerDecision[]` — an array because a mentor may cross multiple thresholds since last evaluation

Milestone thresholds come from `config.milestoneThresholds` (e.g. [50, 100, 200]) — not hardcoded

For each threshold in config.milestoneThresholds where currentYearSessionCount >= threshold: check `notificationRepo.getMilestoneRecord(userId, threshold)` — if no prior record exists, include a TriggerDecision for that threshold with shouldTrigger=true

If notificationRepo.getMilestoneRecord returns an existing record for (userId, threshold), that threshold is skipped — exactly-once delivery guaranteed

Each returned TriggerDecision includes: shouldTrigger=true, scenarioType='milestone', milestoneValue (the crossed threshold integer), reason='milestone_threshold_crossed'

If preferenceService.isNotificationAllowed(userId, 'milestone') returns false, all milestone TriggerDecisions for this user have shouldTrigger=false with reason 'user_opted_out'

Session count query counts rows in `activity` table where peer_mentor_id = userId AND date falls within the current calendar year (Jan 1 – Dec 31 UTC)

Evaluator does NOT insert the milestone record — that is the responsibility of the dispatch layer after successful notification delivery

Edge case: mentor with 200 sessions who has never been notified returns TriggerDecisions for all three thresholds [50, 100, 200] in a single call

Year boundary: on January 1st, session count resets to 0 — previously crossed thresholds from prior year do not block new-year milestone triggers

Technical Requirements

frameworks

Supabase Edge Functions (Deno)

apis

Supabase PostgreSQL 15

data models

activity

annual_summary

performance requirements

Session count query uses COUNT(*) with index on (peer_mentor_id, date) — no full-table scan

For batch evaluation across all mentors: use a single GROUP BY query to get all session counts in one DB round trip

Milestone record lookups can be batched per user: fetch all milestone records for a user in one query and filter in-memory

security requirements

Session count must be scoped to organization_id — prevent cross-org count inflation

Milestone notification must only reference the count milestone (e.g. '50 sessions') — never include contact names or activity details in the notification payload

annual_summary table access via service role only — never exposed to mobile milestone query path

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

The milestone record in `scenario_notifications` table should store `milestone_value` (the integer threshold) and the year it was triggered. When checking for prior notifications, filter by BOTH userId AND year AND milestone_value — this ensures that achieving 50 sessions in 2026 does not block the 50-session milestone trigger in 2027. The evaluator should process thresholds in ascending order and short-circuit on the first uncrossed threshold — if a mentor has 75 sessions, only check 50 (crossed, notified?), 100 (not crossed — stop). This prevents false positives from evaluating thresholds the mentor cannot yet reach.

Consider using `annual_summary` if it already stores yearly session counts to avoid recounting raw activity rows on every engine run — this is a significant performance optimization for mentors with hundreds of activities. If using annual_summary, define a clear fallback to COUNT(activity) when the summary is not yet computed for the current year.

Testing Requirements

Unit tests: test that a mentor with exactly 49 sessions triggers no milestones; test mentor with exactly 50 sessions triggers milestone=50; test mentor with 201 sessions who has no prior notifications triggers all three thresholds [50,100,200]; test mentor with 201 sessions who has existing records for 50 and 100 triggers only 200; test opted-out mentor returns empty trigger list or all shouldTrigger=false; test year boundary — notifications from a prior year (different year column in milestone record) do not block current-year triggers if milestone records are year-scoped. Verify that evaluator returns an array even when zero milestones are triggered (empty array, not null).

Component

Scenario Trigger Engine

service high

Dependencies (1)

Within the Scenario Trigger Engine Supabase Edge Function, implement the inactivity scenario evaluator: query the most recent logged activity for a given peer mentor, compare against the configurable inactivity threshold in days, and return a trigger decision. Respect the preference gate and cooldown check before signalling dispatch. epic-scenario-push-engagement-core-engine-task-005

Epic Risks (3)

high impact medium prob technical

The scenario-edge-function-scheduler must evaluate all active peer mentors within the 30-second Supabase Edge Function timeout. For large organisations, a sequential evaluation loop may exceed this limit, causing partial runs and missed notifications.

Mitigation & Contingency

Mitigation: Design the trigger engine to batch mentor evaluations using database-side SQL queries (bulk inactivity check via a single query rather than per-mentor calls), and add a performance test against 500 mentors during development. Document the evaluated mentor count per scenario type in scenario-evaluation-config to allow selective scenario execution per run.

Contingency: If single-run execution is insufficient, split evaluation into per-scenario-type scheduled functions (inactivity check, milestone check, expiry check) on separate cron schedules, dividing the computational load across multiple invocations.

high impact low prob technical

A race condition between concurrent scheduler invocations or retried cron triggers could cause the same scenario notification to be dispatched multiple times to a mentor, severely degrading trust in the feature.

Mitigation & Contingency

Mitigation: Implement cooldown enforcement using a database-level upsert with a unique constraint on (user_id, scenario_type, cooldown_window_start) so that a second invocation within the same window is rejected at the persistence layer rather than the application layer.

Contingency: Add an idempotency key derived from (user_id, scenario_type, evaluation_date) to the notification record insert; if a duplicate key violation is caught, log it as a warning and skip dispatch without error.

medium impact medium prob integration

The trigger engine queries peer mentor activity history across potentially multiple organisations and chapters. RLS policies configured for app-user roles may block the Edge Function's service-role queries, or query performance may degrade on large activity tables.

Mitigation & Contingency

Mitigation: Confirm the Edge Function runs with the Supabase service role key (bypassing RLS) and add composite indexes on (user_id, activity_date) to the activity tables before implementing the inactivity detection query.

Contingency: If service-role access is restricted by organisational policy, implement a dedicated database function (SECURITY DEFINER) that performs the inactivity aggregation and is callable by the Edge Function with limited scope.

Quick Links

All Tasks Execution Plan