critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

Function `evaluateInactivity(userId, config, preferenceService, notificationRepo)` returns `TriggerDecision` with fields: shouldTrigger (bool), reason (string), scenarioType ('inactivity')
Query retrieves the MAX(date) activity row for the given peer_mentor_id from the `activity` table — single aggregation query, not a full record fetch
If the most recent activity date is more than `config.inactivityWindowDays` days ago (using current UTC time), the evaluator proceeds to preference and cooldown checks
If preferenceService.isNotificationAllowed(userId, 'inactivity') returns false, shouldTrigger is false with reason 'user_opted_out'
If notificationRepo.hasBeenSentSinceThreshold(userId, 'inactivity', cooldownCutoff) returns true, shouldTrigger is false with reason 'cooldown_active'
If all checks pass and the user is inactive, shouldTrigger is true with reason 'inactivity_threshold_exceeded'
If no activity record exists for the user at all, the evaluator treats the user as inactive since account creation date — still subject to preference and cooldown checks
Peer mentors with status 'paused' or 'inactive' in the assignment table are skipped — evaluator returns shouldTrigger=false with reason 'mentor_paused'
Function does not perform any side effects (no inserts) — it only evaluates and returns a decision; dispatch is handled by a separate layer
Evaluation logic is covered by unit tests achieving 100% branch coverage on the decision tree

Technical Requirements

frameworks
Supabase Edge Functions (Deno)
apis
Supabase PostgreSQL 15
Supabase Auth
data models
activity
assignment
performance requirements
Most-recent-activity query uses MAX(date) aggregation with index on (peer_mentor_id, date DESC) — must not perform sequential scan on full activity table
Total evaluation time for one user must not exceed 200ms including preference and cooldown sub-queries
Batch evaluation across all mentors in an organization must use a single SQL query with GROUP BY peer_mentor_id rather than N individual queries
security requirements
Edge Function accesses activity table via service role — RLS bypassed server-side only; never relay raw activity records to mobile
Query must include organization_id filter to prevent cross-organization activity reads
Evaluation result (TriggerDecision) must not include any activity content — only the boolean trigger decision and reason string

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

The boundary condition for 'inactive' should be: `daysSinceLastActivity > config.inactivityWindowDays` (strictly greater than, not >=). This prevents triggering on the exact day the threshold is met, giving the mentor one full day buffer. For the batch case where the engine evaluates all mentors in an organization, use a single SQL query: `SELECT peer_mentor_id, MAX(date) as last_active FROM activity WHERE organization_id = $1 GROUP BY peer_mentor_id` — then join with assignment statuses in the same query to filter paused mentors. This reduces N+1 database calls to a single round trip.

Keep the evaluator function pure (no side effects) — this is critical for the architecture because it allows the engine to evaluate all scenarios for all users and then batch-dispatch decisions in one pass, rather than interleaving evaluation and dispatch. Use UTC timestamps throughout — Norwegian users are in CET/CEST (UTC+1/+2), but inactivity windows should be based on elapsed wall-clock days relative to the server's UTC clock.

Testing Requirements

Unit tests with mocked repositories and preference service: test that a user whose last activity is exactly at the threshold boundary (equal to inactivityWindowDays) does NOT trigger (boundary condition — inactive means strictly greater than); test user with activity 1 day beyond threshold triggers; test that opted-out user with stale activity returns shouldTrigger=false; test that a user within cooldown returns shouldTrigger=false even if inactive; test that a paused mentor returns shouldTrigger=false; test new user with no activity record returns a trigger decision (not an exception). Integration test: insert activity rows in test DB, invoke evaluator, assert correct trigger decisions. Verify the SQL aggregation query plan uses an index via EXPLAIN.

Component
Scenario Trigger Engine
service high
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.