Create CertificationExpiryJob Edge Function scaffold

epic-peer-mentor-pause-core-logic-task-008 — Scaffold the CertificationExpiryJob as a Supabase Edge Function (Deno/TypeScript). Set up the function entry point, environment variable access for Supabase service role key, and the pg_cron trigger configuration to invoke the function nightly at 02:00 UTC. Establish the logging pattern that outputs structured JSON logs for each job run including start time, mentor count queried, and completion status.

critical priority medium complexity infrastructure pending infrastructure specialist Tier 2

Acceptance Criteria

Edge Function file exists at supabase/functions/certification-expiry-job/index.ts with a valid Deno serve() entry point

Function reads SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY from Deno.env — never hardcodes credentials

pg_cron job is defined in a migration file (not ad-hoc SQL) with schedule '0 2 * * *' targeting the Edge Function HTTP endpoint

Function outputs a structured JSON log at job start: { event: 'job_start', timestamp: ISO8601, job_id: uuid }

Function outputs a structured JSON log at job end: { event: 'job_complete', timestamp: ISO8601, job_id: uuid, mentors_queried: number, mentors_paused: number, duration_ms: number }

Function outputs a structured JSON log on error: { event: 'job_error', timestamp: ISO8601, job_id: uuid, error_message: string, stack: string }

Function returns HTTP 200 on success and HTTP 500 on unhandled error — pg_cron can use the response code for alerting

The pg_cron schedule is idempotent — running the migration twice does not create duplicate cron jobs

Function scaffold includes placeholder stubs for the mentor query and status transition calls (to be implemented in task-009)

Deno.env access is wrapped in a typed config object to catch missing env vars at startup rather than at first use

Technical Requirements

frameworks

Deno

TypeScript

apis

Supabase Edge Functions (Deno)

Supabase PostgreSQL RPC

pg_cron extension

data models

certification

performance requirements

Function cold start must not exceed 2 seconds

Structured log lines must be emitted synchronously before any async operation to ensure they are captured even on crash

security requirements

Service role key accessed only via Deno.env.get() — never interpolated into source code or committed to version control

Function endpoint must validate that the caller is pg_cron (internal) or a service account — reject unauthenticated HTTP requests with 401

All environment variables validated at startup — function must fail fast with a clear error if any required env var is missing

No PII logged in structured logs — only counts and UUIDs

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Use the @supabase/supabase-js npm import compatible with Deno (esm.sh or deno.land/x). Generate a unique job_id (crypto.randomUUID()) at function entry and thread it through all log lines for correlation. Use a try/catch at the top level of serve() to ensure the job_error log is always emitted on unhandled exceptions. Define a JobLogger class or plain functions (logStart, logComplete, logError) to enforce consistent log shape — prevents ad-hoc console.log calls diverging from the schema.

For pg_cron configuration, use the cron.schedule() function in a migration file rather than the pg_cron.job table directly to ensure reproducibility. The placeholder stubs should call a noop function and return { mentors_queried: 0, mentors_paused: 0 } so the scaffold is end-to-end testable before task-009 is complete.

Testing Requirements

Manual smoke test: deploy function to Supabase staging environment, invoke via curl, verify HTTP 200 response and structured JSON log output in Supabase Edge Function logs dashboard. Verify pg_cron job appears in cron.job table after migration. Verify that calling the function without the service role key env var returns HTTP 500 with a clear missing-config error. Write a Deno unit test (using Deno.test) for the config validation and log formatting logic.

Component

Certification Expiry Auto-Pause Job

infrastructure medium

Dependencies (1)

Implement the update_mentor_status PostgreSQL function as a Supabase RPC. The function must validate the requested transition against the state machine, reject invalid transitions with a descriptive error, atomically update the mentor's status in the peer_mentors table, and write an audit history record to mentor_status_history with timestamp, old status, new status, actor, and reason fields. epic-peer-mentor-pause-core-logic-task-002

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