🏠 Overview › ✅ Implementation Tasks ›✅ epic-certificate-expiry-notifications-orchestration-services-task-015

Configure Supabase cron schedule for expiry check function

epic-certificate-expiry-notifications-orchestration-services-task-015 — Register the expiry check edge function on the Supabase pg_cron schedule to run daily at a defined UTC time (e.g. 02:00 UTC). Document the cron expression, set up environment variable injection for service-role keys, and configure execution timeout limits. Verify the function is listed in Supabase's scheduled functions dashboard.

critical priority low complexity deployment pending devops specialist Tier 4

Acceptance Criteria

A pg_cron job named `daily-expiry-check` is registered in Supabase with the expression `0 2 * * *` (02:00 UTC daily) that invokes the `expiry-check` edge function
The cron registration SQL is committed to the repository as a migration file at `supabase/migrations/<timestamp>_add_expiry_check_cron.sql` so it is version-controlled and reproducible
The environment variable `SUPABASE_SERVICE_ROLE_KEY` is set in the Supabase project's Edge Function secrets via `supabase secrets set` — the value is NOT committed to source control
A CI/CD secrets management note (in the migration file header comment) documents which secrets must be set before deploying to a new environment
The Supabase scheduled functions dashboard confirms `expiry-check` appears in the cron job list with the correct next-run timestamp
An execution timeout of 55 seconds is configured for the function (leaving 5 s buffer under the 60 s Supabase limit) in `supabase/config.toml` or equivalent project config
The cron job is also registered for the staging environment and verified to fire at least once before production deployment

Technical Requirements

frameworks
Supabase CLI (for secrets management and function deployment)
pg_cron PostgreSQL extension (Supabase managed)
apis
Supabase Management API (implicit — used by Supabase CLI)
pg_cron `cron.schedule()` SQL function
performance requirements
Cron trigger overhead must be negligible — pg_cron fires the function via HTTP; no polling loop
Edge Function timeout set to 55 seconds to allow full processing within Supabase's 60-second wall-clock limit
security requirements
Service-role key stored exclusively in Supabase secrets vault — never in `.env` files committed to the repository
Migration SQL file must not contain any credential values — only structural registration commands
Separate secrets configured per environment (development, staging, production) to prevent cross-environment key reuse
pg_cron job execution runs under the `postgres` role internally — confirm the Edge Function invocation path does not bypass RLS

Execution Context

Execution Tier
Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

Implementation Notes

Use the Supabase CLI's `supabase secrets set SUPABASE_SERVICE_ROLE_KEY=` command to inject secrets — do not use `.env` files for production secrets. The pg_cron registration SQL should follow this pattern: `SELECT cron.schedule('daily-expiry-check', '0 2 * * *', $$SELECT net.http_post(url:='', headers:='...')$$)` — or use Supabase's native `pg_cron` + `supabase_functions` integration if available in the project's Supabase version. Check `supabase/config.toml` for the `[functions.expiry-check]` section and set `verify_jwt = true` and `timeout = 55` there. Add a `README` block inside the migration file header comment that lists all required `supabase secrets set` commands so a new team member or CI pipeline can reproduce the setup from scratch.

Testing Requirements

Manual verification checklist (no automated tests for deployment config): (1) run `supabase functions deploy expiry-check` on staging and confirm zero-error output; (2) execute the cron registration migration on staging and verify `cron.job` table contains the `daily-expiry-check` entry; (3) trigger a manual one-off execution via `supabase functions invoke expiry-check` and confirm HTTP 200 response with valid JSON summary; (4) wait for the next scheduled 02:00 UTC fire on staging and confirm execution in Supabase dashboard logs; (5) verify the function appears with correct next-run time in the Supabase scheduled functions UI. Document all steps and outcomes in the PR description.

Component
Certificate Expiry Check Edge Function
infrastructure medium
Dependencies (1)
Create the Supabase Edge Function that runs on the daily cron schedule. The function queries the certification expiry repository for all mentors with certificates expiring within 60 days or already lapsed, partitions results by threshold tier, and passes each partition to the downstream services: orchestrator for notification dispatch and visibility suppressor for lapsed mentors. epic-certificate-expiry-notifications-orchestration-services-task-014
Epic Risks (4)
high impact medium prob technical

If the daily edge function runs more than once in a 24-hour window due to a Supabase scheduling anomaly or manual re-trigger, the orchestrator could dispatch duplicate push notifications to the same mentor and coordinator for the same threshold, eroding user trust.

Mitigation & Contingency

Mitigation: Implement idempotency at the notification record level using a unique constraint on (mentor_id, threshold_days, certification_id). The orchestrator checks for an existing record before dispatching. Use a database-level upsert with ON CONFLICT DO NOTHING.

Contingency: If duplicate notifications are reported in production, add a rate-limiting guard in the edge function that aborts if a notification for the same mentor and threshold was created within the last 20 hours, and add an alerting rule to Supabase logs for duplicate dispatch attempts.

medium impact medium prob scope

The mentor visibility suppressor relies on the daily edge function to detect expiry and update suppression_status. A mentor whose certificate expires at midnight may remain visible for up to 24 hours if the cron runs at a fixed time, violating HLF's requirement that expired mentors disappear promptly.

Mitigation & Contingency

Mitigation: Schedule the edge function to run at 00:05 UTC to minimise lag after midnight transitions. Additionally, the RLS policy can include a direct date comparison (certification_expiry_date < now()) as a secondary predicate that does not rely on suppression_status, providing real-time enforcement at the database level.

Contingency: If the cron lag is unacceptable after launch, implement a Supabase database trigger on the certifications table that fires on UPDATE of expiry_date and calls the suppressor immediately, reducing lag to near-zero for renewal and expiry events.

medium impact low prob integration

The orchestrator needs to resolve the coordinator assigned to a specific peer mentor to dispatch coordinator-side notifications. If the assignment relationship is not normalised or is missing for some mentors, coordinator notifications will silently fail.

Mitigation & Contingency

Mitigation: Query the coordinator assignment from the existing assignments or user_roles table before dispatch. Log a structured warning (missing_coordinator_assignment: mentor_id) when no coordinator is found. Add a data quality check in the edge function that reports mentors without coordinators.

Contingency: If coordinator assignments are missing at scale, fall back to notifying the chapter-level admin role for the mentor's chapter, and surface a data quality report to the admin dashboard showing mentors without assigned coordinators.

medium impact low prob dependency

The course enrollment prompt service generates deep-link URLs targeting the course administration feature. If the course administration feature changes its deep-link schema or the Dynamics portal URL structure changes, enrollment prompts will navigate to broken destinations.

Mitigation & Contingency

Mitigation: Define the deep-link contract between the certificate expiry feature and the course administration feature as a shared constant in a cross-feature navigation config. Version the deep-link schema and validate the generated URL format in unit tests.

Contingency: If the deep-link breaks in production, the course enrollment prompt service should gracefully fall back to opening the course administration feature root screen with a query parameter indicating the notification context, allowing the user to manually locate the correct course.

Quick Links
All Tasks Execution Plan