🏠 Overview › ✅ Implementation Tasks ›✅ epic-certification-management-core-logic-task-008

Build CertificationReminderService threshold evaluator

epic-certification-management-core-logic-task-008 — Implement CertificationReminderService with an evaluateUpcomingExpiries method that queries the certification repository for mentors whose certifications expire within 30, 14, and 7 days from today. For each threshold, return a typed list of CertificationExpiryCandidate objects containing mentor ID, coordinator ID, expiry date, and threshold bucket.

critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

CertificationReminderService is a standalone Dart class injectable via Riverpod with no dependency on Flutter UI layer
evaluateUpcomingExpiries() accepts a DateTime referenceDate parameter (not DateTime.now() hardcoded) to support deterministic testing
Method queries CertificationRepository.getExpiringSoon(withinDays: int) for thresholds [30, 14, 7] and aggregates results into a single List<CertificationExpiryCandidate>
CertificationExpiryCandidate is a typed immutable class with fields: mentorId (String), coordinatorId (String), expiryDate (DateTime), thresholdBucket (enum: days30, days14, days7), certType (enum)
Each candidate is assigned the tightest threshold bucket that applies (e.g., a cert expiring in 6 days appears only in days7, not days14 or days30)
Mentors whose certifications have already expired (expiryDate < referenceDate) are excluded from results
Mentors with status 'paused' in the assignment table are still included — coordinators need to be aware of expiring paused mentors
Method returns an empty list (not null, not exception) when no certifications are within any threshold window
Method completes within 3 seconds for organisations with up to 500 active mentors
All repository errors are caught, logged, and re-thrown as a typed CertificationReminderException so callers can distinguish network vs. data errors

Technical Requirements

frameworks
Dart (no Flutter dependency — pure service layer)
Riverpod for dependency injection
BLoC in the calling orchestration layer
apis
Supabase PostgreSQL — certification table (SELECT WHERE expires_at BETWEEN referenceDate AND referenceDate+30)
Supabase Edge Functions (Deno) — this method may be called from a scheduled Edge Function for server-side reminder dispatch
Supabase PostgreSQL — assignments table (to retrieve coordinatorId per mentor)
data models
certification (id, peer_mentor_id, organization_id, cert_type, issued_at, expires_at, status)
assignment (peer_mentor_id, contact_id, organization_id, status) — used to resolve coordinatorId
CertificationExpiryCandidate (value object, not persisted)
performance requirements
Single database query with JOIN to assignments preferred over N+1 coordinator lookups
Query must use index on certification.expires_at — confirm index exists before writing query
Result set limited to active organisation scope via RLS — no full-table scan across organisations
security requirements
RLS on certification table restricts results to the caller's organisation_id from JWT claims
Service role key used only in Edge Function context — mobile client calls this via authenticated Supabase client
coordinatorId resolved server-side to prevent client-side enumeration of other users

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Implement the threshold bucketing as a private _assignBucket(expiryDate, referenceDate) pure function — this is the core algorithmic piece and must be unit-tested in isolation. Use Dart's Duration arithmetic rather than manual day arithmetic to avoid timezone edge cases; use UTC throughout. The Supabase query should use a single SELECT with WHERE expires_at > referenceDate AND expires_at <= referenceDate + Duration(days: 30) with an inner JOIN on assignments to get coordinator_id in one round trip. Define thresholds as a const list [30, 14, 7] at the top of the service for easy reconfiguration.

CertificationExpiryCandidate should implement == and hashCode for deduplication safety in later pipeline steps. This method is designed to be called from a Supabase Edge Function cron schedule (daily at 08:00 org timezone), so keep it free of any Flutter/platform dependencies.

Testing Requirements

Unit tests (flutter_test): (1) Seed mock CertificationRepository with certifications expiring in 29, 14, 7, 6, and 0 days relative to referenceDate; assert correct thresholdBucket assignment for each. (2) Assert mentor expiring in 6 days appears only in days7 bucket, not days14. (3) Assert already-expired certification (expiryDate yesterday) is excluded. (4) Assert empty list returned when no certs within 30 days.

(5) Assert paused mentor is included. (6) Mock repository throwing a network exception; assert CertificationReminderException is re-thrown with correct type. (7) Assert method accepts injected referenceDate (not internal DateTime.now()) for deterministic tests. Performance test: load-test with 500 mock candidates; assert completion under 3 seconds.

Coverage target: 95% line coverage.

Component
Certification Reminder Service
service medium
Dependencies (1)
Implement the CertificationRepository using the Supabase Dart client. Cover CRUD operations for certification records: create, read by mentor ID, update renewal date, update expiry status, and paginated queries for certs expiring within N days. Apply RLS policies to scope queries to the authenticated user's organisation. epic-certification-management-core-logic-task-002
Epic Risks (2)
high impact medium prob technical

The auto-pause workflow requires CertificationManagementService to call PauseManagementService and HLFDynamicsSyncService in the same logical transaction. If PauseManagementService succeeds but the Dynamics webhook fails, the mentor is paused locally but remains visible on the HLF portal.

Mitigation & Contingency

Mitigation: Implement a saga pattern: write a pending sync event to the database before calling Dynamics, and have a background retry job consume pending events. This guarantees eventual consistency even if the webhook fails transiently.

Contingency: If the Dynamics sync fails after auto-pause, surface an explicit coordinator alert in the dashboard indicating 'Dynamics sync pending — mentor may still be visible on portal'. Allow manual retry from coordinator UI.

medium impact low prob technical

If the nightly cron job runs concurrently (e.g., due to infra retry), CertificationReminderService could dispatch duplicate notifications to mentors before the cert_notification_log insert is visible to the second invocation.

Mitigation & Contingency

Mitigation: Use Supabase's upsert with a unique constraint on (mentor_id, threshold_days, cert_id) in cert_notification_log. The second concurrent insert will fail gracefully and the duplicate dispatch will be skipped.

Contingency: If duplicate notifications do reach mentors, add a post-dispatch dedup check and include a 'you may receive this notification again' disclaimer until the constraint is deployed.

Quick Links
All Tasks Execution Plan