Build expiry scan and auto-transition logic

epic-peer-mentor-pause-management-automated-expiry-task-003 — 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.

critical priority high complexity backend pending backend specialist Tier 2

Acceptance Criteria

CertificationExpiryChecker.runExpiryScan() fetches all certifications with expires_at < today (UTC) and current status != 'expired_cert'

For each expired certification, PeerMentorStatusRepository.transitionStatus(mentorId, newStatus: 'expired_cert', reason: 'certification_expired', certificationId: id) is called

Each transition call is individually wrapped in try/catch; a failure on one mentor does not prevent processing of subsequent mentors

All successful transitions are collected in a BatchTransitionResult object with counts: total_scanned, transitioned, skipped, failed

BatchTransitionResult is returned from runExpiryScan() and passed to the audit logger (task-005)

Mentors whose status is already 'expired_cert', 'inactive', or 'deleted' are skipped and counted in the 'skipped' bucket — no redundant writes

The scan is idempotent: running it twice on the same day produces the same final state with 0 new transitions on the second run

A mentor with multiple expired certifications is only transitioned once (deduplicate by mentor_id before iterating)

Unit tests achieve ≥90% branch coverage on the scan loop and status-skip logic

The method signature is async and returns a Future<BatchTransitionResult>

Technical Requirements

frameworks

Dart

Riverpod (CertificationExpiryChecker as a provider)

Supabase Dart client

apis

PeerMentorStatusRepository.transitionStatus()

CertificationStatusRepository.fetchExpiredToday() (from task-001)

data models

CertificationExpiryRecord

PeerMentorStatus (enum: active, paused, expired_cert, inactive, deleted)

BatchTransitionResult (total_scanned, transitioned, skipped, failed, failed_mentor_ids)

StatusTransitionRecord (mentor_id, old_status, new_status, reason, certification_id, timestamp)

performance requirements

Batch loop must process up to 500 expired certifications in under 10 seconds

Use Future.wait() with a concurrency cap (e.g. max 10 parallel transitions) to avoid Supabase rate limiting

Deduplicate mentor IDs before loop to prevent redundant DB writes

security requirements

Status transition must use service-role Supabase client — user JWT would be rejected by RLS for batch writes

Reason field written to status history must be a fixed enum value, not a free-text string constructed from external input

Failed mentor IDs in BatchTransitionResult must not include PII beyond the UUID — no names or contact details

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Model the scan loop as a pure function taking repository dependencies as constructor-injected interfaces — this makes unit testing straightforward without needing a live Supabase instance. For concurrency control, use a package like `pool` or implement a simple semaphore with `Completer` to cap parallel Supabase writes at 10. The deduplication step should be `certifications.map((c) => c.mentorId).toSet()` before iterating — process unique mentor IDs, not raw certification rows. Use a sealed class or `Either` to accumulate per-record results before building the final BatchTransitionResult.

Guard against timezone bugs: compare dates as UTC; `DateTime.now().toUtc()` not `DateTime.now()`. The `transitionStatus` call should write to the peer_mentor_status_history table atomically — use a Supabase RPC (Postgres function) wrapping the UPDATE + INSERT in a single transaction to ensure the history row always accompanies the status change.

Testing Requirements

Unit tests (flutter_test) with mocked repository dependencies: (1) 0 expired certifications → returns BatchTransitionResult with all zeros, (2) 5 expired certifications → all 5 transitioned, result shows transitioned=5, (3) 1 of 5 transitions throws exception → result shows transitioned=4, failed=1, failed_mentor_ids contains the failing ID, other 4 completed, (4) mentor already in 'expired_cert' status → skipped, no write call made, (5) same mentor appears twice in expired list (two certs) → transitioned only once. Integration test against local Supabase: seed 10 expired certs, run scan, assert 10 status rows updated in DB. Idempotency test: run scan twice in same test, assert second run makes 0 DB writes.

Component

Certification Expiry Checker Service

service high

Dependencies (2)

Design and implement the data access layer for the CertificationExpiryChecker service. Create query methods on the certification status repository to fetch all certifications expiring within 30 days, identify those expiring today, and retrieve mentor status records. Ensure queries are optimised for nightly batch execution and include indexes on expiry date fields. epic-peer-mentor-pause-management-automated-expiry-task-001 Wire the CertificationExpiryChecker into the nightly job scheduler (394-nightly-scheduler). Configure the cron trigger to invoke the expiry checker each night at a defined off-peak time, implement graceful error handling so a scheduler failure does not silently skip the run, and add a run-lock mechanism to prevent concurrent executions. epic-peer-mentor-pause-management-automated-expiry-task-002

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