Define certification expiry data schema and queries
epic-peer-mentor-pause-management-automated-expiry-task-001 — 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.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use Supabase's `.lte('expires_at', futureDate).gte('expires_at', today)` filter chain for the range query. Represent dates as ISO-8601 UTC strings when passing to PostgREST to avoid timezone edge cases — always normalise to UTC midnight. The index migration should be a separate numbered migration file (e.g. `20260001_idx_certification_expires_at.sql`) so it can be reviewed independently.
For the Dart model, use `DateTime.parse()` with UTC enforcement. Consider a sealed class `CertificationExpiryWindow { case expiredToday; case expiringSoon(int daysLeft); }` to make the caller's intent explicit. The service-role client instance should be a singleton injected via Riverpod `Provider`, separate from the user-scoped Supabase client used in the rest of the app.
Testing Requirements
Unit tests (flutter_test) for all three repository methods using a mocked Supabase client. Test matrix: (1) happy path returns correctly typed Dart objects, (2) empty result returns empty list not null, (3) date boundary conditions (expiry = today, expiry = today+30, expiry = today+31), (4) Supabase network error throws typed RepositoryException. Integration test against a local Supabase instance (via supabase CLI) seeded with known fixture data to confirm index usage and correct row counts. No UI tests required.
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.
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.
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.
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.