Implement expired certification mentor query in CertificationExpiryJob
epic-peer-mentor-pause-core-logic-task-009 — Implement the database query within CertificationExpiryJob that retrieves all HLF peer mentors whose certification_expiry_date is in the past and whose current status is 'active'. The query must be scoped to HLF organization only (using org_id filtering), must exclude already-paused mentors to prevent duplicate processing, and must return mentor_id, coordinator_id, certification_expiry_date, and chapter_id for each result.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Use supabase.from('certifications').select('mentor_id, coordinator_id, certification_expiry_date, chapter_id').eq('organization_id', hlfOrgId).eq('status', 'active').lt('certification_expiry_date', new Date().toISOString()) as the base query shape. Note that the certification table may store status separately from the peer mentor's profile status — confirm the schema design before writing the query. If the active/paused status is on a separate mentor_status table, the query may need a join or an additional .not('id', 'in', (subquery for paused mentors)). Define the CertificationExpiryRecord TypeScript interface in a shared types file within the function directory.
Log mentors_queried: result.length immediately after the query for the structured job log. The downstream processing (calling pauseMentor for each result) is handled by a subsequent task — this task only needs to return the correctly typed result array from a named function getExpiredCertificationMentors() called from the main job flow.
Testing Requirements
Deno unit tests using a mocked Supabase client: verify correct filter parameters (.eq('status', 'active'), .eq('organization_id', HLF_ORG_ID), .lt('certification_expiry_date', NOW())) are applied. Verify empty result returns mentors_queried: 0. Verify result rows are typed as CertificationExpiryRecord[]. Integration test on staging Supabase: seed test mentor with expired certification, run function, verify mentor appears in results.
Seed test mentor with paused status + expired cert — verify they do NOT appear. Verify index exists using EXPLAIN ANALYZE on staging database.
The status state machine must handle race conditions where two concurrent callers (e.g., a mentor self-pausing and a coordinator force-pausing simultaneously) attempt to update the same mentor's status. Without a concurrency guard, both writes could succeed, leaving the audit log in an inconsistent state.
Mitigation & Contingency
Mitigation: Use a Supabase RPC with a row-level lock (SELECT FOR UPDATE) inside a transaction so only one transition wins. Return a clear error to the losing caller. Test with concurrent requests in the integration test suite.
Contingency: If row-level locking proves unreliable in the Supabase environment, add an optimistic-locking version field to peer_mentors and have the service retry up to three times on version conflict before surfacing an error to the caller.
If the CertificationExpiryJob Edge Function fails silently (network timeout, Supabase cold start), HLF mentors with expired certifications could remain in active status and continue appearing on the chapter website, creating a compliance breach.
Mitigation & Contingency
Mitigation: Implement structured error logging inside the Edge Function, write a monitoring query that checks for mentors with expired certifications still in active status, and set up an alert if any are detected 30 minutes after the scheduled nightly run.
Contingency: Provide a coordinator-accessible manual trigger for the expiry check that can be invoked via the admin interface if the scheduled job is known to have failed. Document the manual recovery procedure for HLF coordinators.
pg_cron registration in Supabase requires superuser-level access that may not be available in all environments (local dev, staging, CI). If the cron job cannot be registered automatically, the Edge Function will never execute on schedule, breaking the HLF certification expiry workflow.
Mitigation & Contingency
Mitigation: Use Supabase's recommended pg_cron setup via the SQL editor migration script and document the exact commands. Validate cron registration in the staging environment as part of the epic's deployment checklist.
Contingency: If pg_cron is unavailable, switch to a Supabase scheduled Edge Function invocation via an external cron service (e.g., a GitHub Actions scheduled workflow calling the Edge Function endpoint with a service-role key) until the pg_cron approach is resolved.