Implement escalating coordinator reminder dispatch

epic-peer-mentor-pause-management-automated-expiry-task-004 — Add reminder notification dispatch logic to the CertificationExpiryChecker for the 30-day, 14-day, and 7-day warning thresholds. For each mentor whose certification falls within a threshold window, dispatch a coordinator reminder via the PauseNotificationService. Ensure idempotency so repeated nightly runs do not send duplicate reminders for the same threshold and mentor.

critical priority high complexity backend pending backend specialist Tier 3

Acceptance Criteria

CertificationExpiryChecker dispatches coordinator reminders at exactly three threshold windows: 30 days, 14 days, and 7 days before expiry

Each threshold check queries certifications whose expiry date falls within [today + threshold - 1, today + threshold] (1-day window per threshold) to avoid re-sending on subsequent nights

PauseNotificationService.dispatchCoordinatorReminder(mentorId, coordinatorId, threshold, certificationId) is called once per mentor-threshold combination per calendar day

Idempotency is enforced via a `reminder_dispatch_log` table keyed on (mentor_id, certification_id, threshold_days, date_sent); before dispatch, check if a row exists for today and skip if found

If PauseNotificationService throws, the error is caught, the failed dispatch is recorded in the dispatch log with status='failed', and processing continues for remaining mentors

A mentor with a certification expiring in exactly 7 days receives a 7-day reminder, NOT also a 14-day or 30-day reminder in the same run

Each threshold group is processed independently: a failure in the 30-day group does not block the 14-day or 7-day groups

ReminderDispatchSummary (dispatched_count, skipped_idempotent_count, failed_count per threshold) is returned and passed to the audit logger

Unit tests cover all three thresholds, idempotency skip, and partial failure scenarios

Technical Requirements

frameworks

Dart

Riverpod

Supabase Dart client

apis

PauseNotificationService.dispatchCoordinatorReminder()

CertificationStatusRepository.fetchExpiringSoon(withinDays: int) (from task-001)

Supabase reminder_dispatch_log table (upsert/select)

data models

reminder_dispatch_log (id, mentor_id, certification_id, threshold_days, date_sent, status, coordinator_id)

ReminderDispatchSummary (threshold, dispatched, skipped, failed)

CoordinatorReminderPayload (mentorId, coordinatorId, certificationId, daysUntilExpiry, certificationName)

performance requirements

Idempotency check must be a single bulk SELECT (not one query per mentor) using IN clause on (mentor_id, threshold, date) composite

Batch dispatch with concurrency cap of 10 parallel notification calls to avoid notification service rate limiting

reminder_dispatch_log table must have a composite index on (certification_id, threshold_days, date_sent)

security requirements

reminder_dispatch_log writes use service-role key; user JWT must not be used in batch context

CoordinatorReminderPayload must not include the mentor's full name or contact details in the notification body — use coordinator's own data portal for lookup

Notification dispatch must not expose certification details beyond expiry date and days remaining

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

Define the three thresholds as a constant list `const List kReminderThresholdDays = [30, 14, 7]` and iterate over it so adding future thresholds (e.g. 3 days) requires only a single change. For the 1-day window query: `expires_at = today + threshold days (UTC date)` using a date equality check, not a range, to pin each threshold to exactly one day. Implement the idempotency check as a bulk query: `SELECT mentor_id FROM reminder_dispatch_log WHERE threshold_days = ?

AND date_sent = today AND mentor_id IN (...)` then subtract the returned set from the to-dispatch set. Use `Future.wait(dispatches, eagerError: false)` to allow all dispatches to attempt even if some fail — collect results with `catchError` per future. The coordinator_id lookup (who to notify for a given mentor) should be resolved via a repository method before the dispatch loop to avoid per-mentor queries in the hot path.

Testing Requirements

Unit tests (flutter_test) with mocked PauseNotificationService and mocked reminder_dispatch_log repository: (1) certification expiring in 30 days → 30-day reminder dispatched, log entry written, (2) same certification next night → log entry found, dispatch skipped (idempotent), (3) certification expiring in 14 days → only 14-day reminder dispatched, not 30-day, (4) certification expiring in 7 days → only 7-day reminder, (5) PauseNotificationService throws → failed log entry written, other mentors still processed, (6) 30-day group partially fails → 14-day and 7-day groups still run. Integration test: seed 3 certifications at 30/14/7-day boundaries, run dispatcher, assert 3 log entries in reminder_dispatch_log, run again, assert no new entries (idempotency).

Component

Certification Expiry Checker Service

service high

Dependencies (1)

Implement the core scan loop inside CertificationExpiryChecker. For each certification that has passed its expiry date, auto-transition the linked mentor's status to 'expired_cert' via the PeerMentorStatusRepository. Wrap each record update in a try/catch to ensure one failed transition does not abort the entire batch, and collect results for audit logging. epic-peer-mentor-pause-management-automated-expiry-task-003

Epic Risks (4)

high impact medium prob technical

The nightly expiry checker may run multiple times due to scheduler retries or infrastructure issues, causing duplicate auto-transitions and duplicate coordinator notifications that erode trust in the notification system.

Mitigation & Contingency

Mitigation: Implement idempotency via a unique constraint on (mentor_id, threshold_day, certification_expiry_date) in the cert_expiry_reminders table. Auto-transitions should be wrapped in a Postgres RPC that checks current status before applying, making repeated invocations safe.

Contingency: Add a compensation query in the reconciliation log that detects duplicate log entries for the same certification period and alerts the operations team for manual review within 24 hours.

high impact medium prob integration

The HLF Dynamics portal API may have eventual-consistency behaviour or rate limits that cause website listing updates to lag behind status changes, leaving expired mentors visible on the public website for an unacceptable window.

Mitigation & Contingency

Mitigation: Design the sync service to be triggered immediately on status transitions (event-driven via database webhook) in addition to the nightly batch run. Implement a reconciliation job that verifies sync state against app state and re-triggers any divergent records.

Contingency: If real-time sync cannot be guaranteed, implement a manual 'force sync' action in the coordinator dashboard so coordinators can trigger an immediate re-sync for urgent cases. Document the expected sync lag in coordinator onboarding materials.

medium impact medium prob scope

Stakeholder requests to extend the expiry checker to handle additional certification types, grace periods, or organisation-specific threshold configurations may significantly increase scope beyond what is designed here, delaying delivery.

Mitigation & Contingency

Mitigation: Parameterise threshold day values (30, 14, 7) via configuration repository rather than hard-coding them, enabling per-organisation customisation without code changes. Document that grace period logic and additional cert types are out of scope for this epic and require a dedicated follow-up.

Contingency: Deliver the feature with hard-coded HLF-standard thresholds first and introduce the configuration repository as a follow-up task in the next sprint, using a feature flag to enable per-org threshold overrides.

high impact low prob security

Dynamics portal API credentials stored as environment secrets in Supabase Edge Function configuration may be rotated or invalidated by HLF IT without notice, causing silent sync failures that go undetected for multiple days.

Mitigation & Contingency

Mitigation: Implement credential health-check calls on each scheduler run and emit an immediate alert on auth failure rather than only alerting after N consecutive failures. Document the credential rotation procedure with HLF IT and establish a rotation notification protocol.

Contingency: Maintain a break-glass manual sync script accessible to HLF administrators that can re-execute the Dynamics sync with newly provided credentials while the automated system is restored.

Quick Links

All Tasks Execution Plan