Register daily cron schedule in Supabase
epic-assignment-follow-up-reminders-cron-infrastructure-task-001 — Configure and register the pg_cron or Edge Function cron schedule that triggers the daily reminder evaluation cycle. Define the cron expression (e.g., daily at 06:00 UTC), register it in Supabase infrastructure, and wire it to invoke the ReminderSchedulerService endpoint. Ensure the schedule is idempotent and can survive restarts without creating duplicate registrations.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 18 - 1 tasks
Can start after Tier 17 completes
Implementation Notes
Prefer pg_cron over Edge Function cron triggers for this use case — pg_cron is natively supported in Supabase and does not count against Edge Function invocation limits. Use the pattern: SELECT cron.unschedule('daily-reminder-eval'); then SELECT cron.schedule('daily-reminder-eval', '0 6 * * *', $$SELECT net.http_post(...)$$); inside the migration to guarantee idempotency. Use the pg_net extension (net.http_post) to call the Edge Function HTTP endpoint from within PostgreSQL — this keeps the call non-blocking. Store the service-role key in Supabase Vault and retrieve it with vault.decrypted_secret('reminder_service_role_key') inside the SQL call.
Create the cron_execution_log table in a prior migration (or in the same one) with a TRIGGER on pg_cron's job run results if using pg_cron 1.5+. Always test with supabase db reset followed by supabase db push locally before deploying to staging. Include a comment block at the top of the migration file: -- Emergency pause: SELECT cron.unschedule('daily-reminder-eval'); -- Resume: re-run this migration.
Testing Requirements
Write a Supabase migration integration test (or a local supabase test suite entry) that: (1) applies the migration against a local Supabase instance (supabase start); (2) asserts the cron job exists in cron.job with the correct schedule and command; (3) manually triggers the job via SELECT cron.schedule() with a 1-minute window and asserts a row appears in cron_execution_log within 70 seconds. Also write a unit test for the idempotency script: apply it twice and assert cron.job contains exactly one row for 'daily-reminder-eval'. Document manual verification steps for the TestFlight / staging environment in the PR description.
If the daily cron job takes longer than 24 hours to complete (due to a large dataset or a slow query), a second instance will start while the first is still running, causing duplicate reminder dispatch for assignments processed twice.
Mitigation & Contingency
Mitigation: Implement an advisory lock that prevents a second run from starting if the first is still active. Monitor run duration via the execution log table and alert if any run exceeds 30 minutes. The 10,000-assignment load test should verify the run completes in under 5 minutes.
Contingency: If a double-run occurs, the idempotency guard in ReminderDispatchService prevents duplicate notifications from being sent. The execution log identifies the overlap and allows the ops team to investigate the root cause.
If the activity registration hook that resets last_contact_date is implemented incorrectly or not triggered for all activity types (e.g., proxy registrations, bulk registrations), peer mentors will continue receiving reminders even after logging contact, damaging user trust.
Mitigation & Contingency
Mitigation: Audit all code paths that create activity records (direct registration, proxy registration, bulk registration, coordinator proxy) and ensure each path calls the assignment contact update. Write integration tests for each registration path asserting that last_contact_date is updated.
Contingency: Provide an authenticated admin endpoint that allows manual correction of last_contact_date for a specific assignment, enabling ops to resolve individual cases while the bug is fixed and deployed.