Implement certification expiry lead-time evaluator
epic-scenario-push-engagement-core-engine-task-007 — Add the certification expiry scenario evaluator to the trigger engine: query certification expiry dates for the peer mentor, identify certifications expiring within the configurable lead time (e.g. 30 days), and trigger a renewal-reminder notification. Cross-check against the notification repository to enforce cooldown and avoid duplicate expiry alerts.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
The renewal check SQL pattern: `WHERE NOT EXISTS (SELECT 1 FROM certification c2 WHERE c2.peer_mentor_id = c1.peer_mentor_id AND c2.cert_type = c1.cert_type AND c2.expires_at > c1.expires_at AND c2.expires_at > NOW())` — this excludes certs the mentor has already renewed. HLF-specific context from the workshop notes: the physical certification card is considered an 'adelsmerke' (badge of honor), so the reminder notification should be encouraging rather than alarming in tone — the notification copy layer (separate concern) should receive the cert_type to customize the message. The cooldown check for certification expiry should use a per-certification-ID cooldown if the same cert can trigger multiple reminders (e.g. 30-day and 7-day reminders) — consider storing cert_id in the notification repository `metadata` column and including it in the cooldown query filter.
This prevents the cooldown for one cert from blocking the reminder for a different cert. When Microsoft Dynamics sync is active (HLF), treat the Dynamics cert record as the authoritative source — the local `certification` table row is a cached copy; the evaluator should prefer the Dynamics-synced expiry date when available.
Testing Requirements
Unit tests with mocked Supabase client and repositories: test mentor with no expiring certs returns empty array; test mentor with one cert expiring within lead time and no cooldown returns one TriggerDecision; test mentor with cert expiring within lead time but within cooldown returns shouldTrigger=false; test mentor with already-expired cert (expires_at in the past) returns empty array; test mentor with two expiring certs where one is within cooldown returns exactly one TriggerDecision; test opted-out mentor returns all shouldTrigger=false decisions; test renewal check — mentor with an older expiring cert and a newer valid cert of the same type returns empty array (renewed). Verify ISO8601 format of expiresAt in metadata. All tests must run without network access.
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.
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.
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.