🏠 Overview › ✅ Implementation Tasks ›✅ epic-peer-mentor-pause-management-foundation-task-007

Provision nightly scheduler via Supabase Edge Function and pg_cron

epic-peer-mentor-pause-management-foundation-task-007 — Create a Supabase Edge Function that acts as the nightly job scheduler entry point. Configure pg_cron to trigger the function on a nightly schedule. Implement job dispatch logic that routes to registered job handlers (certification expiry, HLF sync). Include error capture and execution logging.

high priority medium complexity infrastructure pending infrastructure specialist Tier 0

Acceptance Criteria

A Supabase Edge Function named nightly-scheduler exists and is deployable via supabase functions deploy nightly-scheduler.
pg_cron job is configured to invoke the Edge Function via a net.http_post() call (or pg_net) at 02:00 UTC daily.
The function accepts a JSON body with a job_name field and dispatches to the corresponding handler (e.g., 'certification_expiry_check', 'hlf_dynamics_sync').
Each job handler is registered in a handler registry (plain Deno Map or object) — adding a new job requires only adding an entry to the registry, no structural changes.
Execution result (job_name, started_at, finished_at, status, error_message) is written to a scheduler_execution_log table after each job run.
If a job handler throws, the error is caught, logged to scheduler_execution_log with status='failed', and the function returns HTTP 200 (not 500) so pg_cron does not retry infinitely.
The function validates the Authorization header against a shared secret (stored in Supabase Vault) to prevent unauthorized invocation.
A manual trigger endpoint (POST /nightly-scheduler with job_name in body) allows developers to run individual jobs on demand in non-production environments.
pg_cron job creation is scripted in a SQL migration file so it is reproducible across environments.
Function cold start time is under 2 seconds; individual job dispatch (excluding job execution) adds less than 50ms overhead.

Technical Requirements

frameworks
Supabase Edge Functions (Deno)
pg_cron
pg_net (or net extension for HTTP calls from Postgres)
apis
Supabase Management API (function deployment)
Supabase Vault (secret retrieval)
scheduler_execution_log table (INSERT)
data models
certification
bufdir_export_audit_log
performance requirements
Function must complete dispatch and log write within 500ms excluding job execution time.
pg_cron job must not accumulate stale runs — set max_run_count or ensure the previous run completes before the next fires.
security requirements
Service role key used inside the Edge Function must be loaded from Deno.env (Supabase Vault secret injection) — never hardcoded in source.
Shared secret for Authorization header validation must be rotatable without redeploying the function (read from Vault at runtime).
Function must not expose job execution details (stack traces) in HTTP responses — log internally only.
pg_net credentials for calling the Edge Function must be stored in Supabase Vault, not in the pg_cron job definition.

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Structure the Edge Function as: (1) validate Authorization header, (2) parse job_name from request body, (3) look up handler in registry, (4) record start time, (5) execute handler inside try/catch, (6) write execution log. Use Supabase's supabaseAdmin client (service role) inside the function for database writes — never pass the service role key to job handlers that call external APIs. For pg_cron setup, use: SELECT cron.schedule('nightly-scheduler', '0 2 * * *', $$SELECT net.http_post(url:='https://.supabase.co/functions/v1/nightly-scheduler', headers:='{}', body:='{}')$$); — parameterize the URL from a Postgres config variable to avoid hardcoding per environment. Keep each job handler in its own file (e.g., handlers/certification_expiry.ts) and import into the main dispatcher to keep the entry point small and testable.

Consider using a Deno KV or Postgres advisory lock to prevent duplicate concurrent runs if the function is ever invoked twice in quick succession.

Testing Requirements

Write Deno unit tests (Deno.test) covering: (1) known job_name dispatches to correct handler, (2) unknown job_name returns a structured error and logs failure, (3) handler that throws is caught and logged without propagating, (4) missing or invalid Authorization header returns 401 before any job runs, (5) execution log entry contains correct started_at/finished_at timestamps. Integration test: deploy to a Supabase local dev stack (supabase start), invoke the function via curl with a valid secret, and verify the scheduler_execution_log row is inserted. Verify pg_cron job fires correctly by checking cron.job_run_details in the local Postgres instance after manual trigger. Document a manual smoke-test checklist for production deployment validation.

Component
Nightly Job Scheduler
infrastructure medium
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for coordinator-scoped status queries may be difficult to express correctly, especially for peer mentors assigned to multiple coordinators or chapters, leading to data leakage or overly restrictive access blocking valid queries.

Mitigation & Contingency

Mitigation: Design RLS policies using security-definer RPCs rather than table-level policies for complex multi-coordinator scenarios. Write a comprehensive RLS test matrix covering all role and assignment permutations before marking complete.

Contingency: Fall back to application-level filtering in the repository layer with explicit coordinator_id parameter checks if RLS proves intractable, and document the trade-off for security review.

high impact medium prob dependency

The HLF Dynamics portal API contract may be undocumented or subject to change, causing the DynamicsPortalClient to break during development or production rollout.

Mitigation & Contingency

Mitigation: Obtain the full Dynamics portal API specification and credentials early in the sprint. Build the client behind a well-defined interface so the HLF-specific implementation can be swapped without affecting upstream services.

Contingency: If the Dynamics API is unavailable or unstable, stub the client with a feature-flag-guarded no-op implementation so all other epics can proceed to completion independently.

medium impact low prob technical

Supabase Edge Functions used as the nightly scheduler host may have cold-start latency or execution time limits that prevent reliable nightly certification checks on large mentor rosters.

Mitigation & Contingency

Mitigation: Benchmark Edge Function execution time against the expected roster size. Design the expiry check to process in paginated batches to stay within execution limits. Use pg_cron with a direct database function as an alternative trigger if Edge Functions prove unreliable.

Contingency: Migrate the scheduler trigger to pg_cron invoking a Postgres function directly, removing the Edge Function dependency entirely for the scheduling layer.

Quick Links
All Tasks Execution Plan