critical priority medium complexity backend pending backend specialist Tier 3

Acceptance Criteria

CertificationReminderService exposes a filterAlreadyNotified(List<CertificationExpiryCandidate> candidates) async method that returns a filtered list excluding already-notified candidates
Deduplication key is the composite tuple (mentor_id, expiry_date, threshold_days); matching is exact (no fuzzy date matching)
Method queries cert_notification_log via CertificationRepository.getNotificationLog(mentorIds: List<String>) in a single batched call — not one query per candidate
A candidate is excluded only if a log entry exists with status='delivered' or status='queued'; failed entries (status='failed') allow a retry
Log entries older than 90 days are not considered for deduplication (they are stale and a fresh reminder may be warranted for re-certification cycles)
If the cert_notification_log query fails, the method throws a DeduplicationQueryException — it must NOT fall back to sending all candidates (fail-closed to prevent spam)
Filtered result preserves the original order of the input candidate list
When all candidates are already notified, returns an empty list with no side effects
Method is pure with respect to the log — it only reads, never writes to cert_notification_log; writing is handled downstream in task-011
Deduplication logic handles a mentor having multiple certifications of different cert_types correctly — each cert_type is a separate deduplication key

Technical Requirements

frameworks
Dart (no Flutter dependency)
Riverpod for service injection
apis
Supabase PostgreSQL — cert_notification_log table (SELECT WHERE mentor_id IN (...) AND sent_at > now()-90days)
Supabase Edge Functions (Deno) — this filter runs server-side as part of the reminder Edge Function
data models
cert_notification_log (id, mentor_id, expiry_date, threshold_days, status: enum(delivered, queued, failed), sent_at, cert_type)
CertificationExpiryCandidate (mentorId, expiryDate, thresholdBucket, certType — used as deduplication key source)
performance requirements
Single batch SQL query using IN clause for all candidate mentor IDs — no per-candidate queries
In-memory Set<String> built from log results for O(1) candidate lookup during filtering
Query must complete within 1 second for batches of up to 200 candidates
security requirements
RLS on cert_notification_log restricts reads to the authenticated organisation scope
Fail-closed behaviour: any query failure prevents dispatch (prevents accidental spam on DB outage)
Log data must not be returned to the mobile client — deduplication runs server-side in Edge Function or trusted backend context

Execution Context

Execution Tier
Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Implementation Notes

Build the in-memory lookup as a Set where each element is the composite key formatted as '${mentorId}|${expiryDate.toIso8601String().substring(0,10)}|${thresholdDays}|${certType.name}'. This O(n) build + O(1) lookup pattern is far more efficient than nested iteration for large batches. The 90-day cutoff should be a const at the top of the service (const _logRetentionDays = 90) applied in the SQL WHERE clause via CertificationRepository, not filtered in Dart memory. Implement fail-closed by wrapping the repository call in a try/catch that re-throws as DeduplicationQueryException — never silently return the full unfiltered list on error, as this would result in notification spam.

The separation of concerns here is critical: this method only reads and filters; it never writes. Writing to the log is task-011's responsibility, which prevents double-write races if the service is later parallelised.

Testing Requirements

Unit tests (flutter_test): (1) Seed mock log with a delivered entry for (mentor-A, expiry-date-X, 7 days); assert mentor-A excluded from result. (2) Seed mock log with a failed entry for the same tuple; assert mentor-A IS included (retry allowed). (3) Seed log with an entry older than 90 days; assert candidate is NOT excluded (stale log). (4) Assert two certifications of different cert_types for the same mentor are deduped independently.

(5) Mock CertificationRepository.getNotificationLog throwing an exception; assert DeduplicationQueryException is thrown and no candidates pass through. (6) Assert batched query is called once regardless of input list size (mock call count assertion). (7) Empty input list returns empty list without database call. Coverage target: 95% branch coverage.

Component
Certification Reminder Service
service medium
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.