Build sync trigger on status change events
epic-peer-mentor-pause-management-automated-expiry-task-007 — Wire HLFWebsiteSyncService to listen for mentor status change events (paused, expired_cert, reinstated). On each relevant transition, enqueue a sync operation to add or remove the mentor from the public chapter website listing. Implement an event-driven trigger using the existing status repository change hooks so sync happens automatically without manual invocation.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Prefer a Supabase database trigger over application-level event hooks for reliability — database triggers fire even if the Flutter app is offline or the backend service restarts. Use a sync_queue table as an outbox pattern (transactional outbox) to ensure the enqueue operation is atomic with the status update. The queue processor Edge Function should use SELECT ... FOR UPDATE SKIP LOCKED for concurrent-safe dequeue.
When checking 'reinstated' certification validity, query the certification table for the mentor's most recent cert with expires_at > NOW() — do not rely on the status field alone. Consider adding a triggered_by_event column to the sync_queue for traceability (e.g., 'status_change', 'manual_coordinator_action').
Testing Requirements
Unit tests: mock the status repository and assert that correct sync operations are enqueued for each status transition scenario (paused → remove, expired_cert → remove, reinstated+valid_cert → restore, reinstated+no_cert → no-op with warning). Integration tests against Supabase test instance: insert a mentor status row, assert sync_queue row is created with correct operation type; update status to reinstated, assert restore operation enqueued; verify idempotency guard prevents duplicate queue entries. Test the HLF-only filter: update an NHF mentor status and assert no sync_queue row is created.
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.