Implement auto-pause logic and audit writing in CertificationExpiryJob

epic-peer-mentor-pause-core-logic-task-010 — Implement the core processing loop in CertificationExpiryJob that iterates over expired-certification mentors and for each: calls the update_mentor_status RPC to transition status to 'paused' with reason 'certification_expired', writes an audit history record including the certification_expiry_date and job run ID, and collects results for the notification dispatch phase. Handle individual mentor failures with per-record error logging so a single failure does not abort the entire batch.

critical priority medium complexity backend pending backend specialist Tier 4

Acceptance Criteria

The processing loop iterates over all active HLF peer mentors whose certification expiry date is on or before today's date

For each expired mentor, the update_mentor_status RPC is called with status='paused' and pause_reason='certification_expired'

A peer_mentor_status_history record is written for each auto-paused mentor, including: previous_status='active', new_status='paused', reason='certification_expired', certification_expiry_date, and job_run_id

A mentor who is already in 'paused' or 'inactive' status is skipped without error and without writing a duplicate audit record

If the RPC call fails for a single mentor, the error is logged with mentor_id and error details, and processing continues to the next mentor

All successfully paused mentors are collected into a result list available for the coordinator notification phase

The job emits a structured JSON log summary at completion: { job_run_id, processed_count, paused_count, skipped_count, error_count, mentor_ids_paused, mentor_ids_errored }

No unhandled exceptions propagate out of the batch loop; the job always completes with a final status log

The job uses the Supabase service-role client exclusively — it must never use a user-scoped JWT for status transitions

Processing is idempotent: running the job twice on the same day results in zero additional pauses on the second run

Technical Requirements

frameworks

Supabase Edge Functions (Deno)

Supabase PostgreSQL 15

apis

Supabase Edge Functions (Deno) REST API

update_mentor_status PostgreSQL RPC

data models

certification

assignment

activity

performance requirements

Batch processing must complete within the Deno Edge Function 30-second wall-clock limit for up to 500 mentors

RPC calls must be awaited sequentially per mentor to preserve per-record error isolation — avoid Promise.all on the full batch

Audit writes may be batched (up to 50 at a time) after the pause phase to reduce round-trips

security requirements

Service-role Supabase key must be read from Deno environment variables only — never hard-coded

Job must validate that it is operating in the correct organisation scope (HLF org_id from env) before executing any mutations

All database mutations execute via parameterised RPC or prepared statements — no string interpolation in SQL

Job run logs must not include full certification data or PII beyond mentor UUIDs

Execution Context

Execution Tier

Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

View Full Execution Plan

Implementation Notes

Implement the loop as an async generator or plain for-await loop over the mentor list retrieved in the previous task (task-009). Use a try/catch block per iteration and accumulate errors in an errors array. After the loop, perform a single batched upsert of audit records for all successfully paused mentors to minimise database round-trips. Use a unique job_run_id (crypto.randomUUID()) generated once at job startup and passed into every audit record — this enables log correlation across the audit table and the job output.

The RPC update_mentor_status should be the single point of truth for the transition; do not directly UPDATE the peer_mentors table from the job. Guard against clock skew by using the database server's NOW() for changed_at in the audit record rather than the Edge Function's Date.now().

Testing Requirements

Unit tests (Deno test runner): mock Supabase client to verify (1) the loop correctly skips non-active mentors, (2) a failed RPC increments error_count without throwing, (3) the result list only contains successfully paused mentor IDs, (4) the audit payload includes all required fields. Test idempotency by simulating a second run where all mentors are already 'paused' and asserting paused_count === 0. Test the structured log output shape against the expected JSON schema. All mocks must replace the Supabase client at the module boundary so real network calls are never made.

Component

Certification Expiry Auto-Pause Job

infrastructure medium

Dependencies (2)

Build the MentorStatusService class with methods pauseMentor(mentorId, reason, actorId), reactivateMentor(mentorId, reason, actorId), deactivateMentor(mentorId, reason, actorId), and getMentorStatus(mentorId). Each transition method must call the update_mentor_status RPC, handle the database-level rejection of invalid transitions gracefully, and return a typed result indicating success or a structured error. epic-peer-mentor-pause-core-logic-task-004 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. epic-peer-mentor-pause-core-logic-task-009

Epic Risks (3)

medium impact low prob technical

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.

high impact medium prob technical

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.

medium impact medium prob dependency

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.

Quick Links

All Tasks Execution Plan