Configure pg_cron schedule and scheduler deployment
epic-scenario-push-engagement-core-engine-task-012 — Register the pg_cron job in the Supabase database to invoke the Scenario Edge Function Scheduler on the desired daily cadence (e.g. 06:00 UTC). Configure the Edge Function deployment with appropriate environment variables, service role key scoping, and timeout settings. Document the cron expression and deployment configuration.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 8 - 48 tasks
Can start after Tier 7 completes
Implementation Notes
The pg_cron registration SQL should use the `cron.schedule` function: `SELECT cron.schedule('scenario-scheduler-daily', '0 6 * * *', $$SELECT net.http_post(url := '${EDGE_FUNCTION_URL}', headers := '{"X-Scheduler-Secret": "${SECRET}"}')$$)`. However, the secret must not be hardcoded in the migration — use a Postgres configuration parameter or trigger the cron job to call a Postgres function that reads the secret from `vault.secrets` (Supabase Vault).
This is the secure pattern for pg_cron + Edge Function integration. The `supabase/config.toml` timeout configuration uses the `[functions.scenario-scheduler]` section with `verify_jwt = false` (scheduler uses shared secret not JWT) and `max_duration_secs = 30`. Document the UTC timezone assumption prominently — Norwegian organizations operate in CET/CEST (UTC+1/+2), so 06:00 UTC = 07:00 CET / 08:00 CEST in local time.
Testing Requirements
Deployment verification checklist: (1) query `SELECT * FROM cron.job WHERE jobname = 'scenario-scheduler-daily'` and assert schedule = '0 6 * * *', (2) manually invoke scheduler via curl with correct secret and assert 200 response with valid JSON, (3) manually invoke without secret and assert 401, (4) verify all environment variables are set by checking Edge Function logs on invocation, (5) run migration in a staging Supabase project and confirm idempotency by running it twice. Document the test results in the deployment verification section of the configuration documentation.
The scenario-edge-function-scheduler must evaluate all active peer mentors within the 30-second Supabase Edge Function timeout. For large organisations, a sequential evaluation loop may exceed this limit, causing partial runs and missed notifications.
Mitigation & Contingency
Mitigation: Design the trigger engine to batch mentor evaluations using database-side SQL queries (bulk inactivity check via a single query rather than per-mentor calls), and add a performance test against 500 mentors during development. Document the evaluated mentor count per scenario type in scenario-evaluation-config to allow selective scenario execution per run.
Contingency: If single-run execution is insufficient, split evaluation into per-scenario-type scheduled functions (inactivity check, milestone check, expiry check) on separate cron schedules, dividing the computational load across multiple invocations.
A race condition between concurrent scheduler invocations or retried cron triggers could cause the same scenario notification to be dispatched multiple times to a mentor, severely degrading trust in the feature.
Mitigation & Contingency
Mitigation: Implement cooldown enforcement using a database-level upsert with a unique constraint on (user_id, scenario_type, cooldown_window_start) so that a second invocation within the same window is rejected at the persistence layer rather than the application layer.
Contingency: Add an idempotency key derived from (user_id, scenario_type, evaluation_date) to the notification record insert; if a duplicate key violation is caught, log it as a warning and skip dispatch without error.
The trigger engine queries peer mentor activity history across potentially multiple organisations and chapters. RLS policies configured for app-user roles may block the Edge Function's service-role queries, or query performance may degrade on large activity tables.
Mitigation & Contingency
Mitigation: Confirm the Edge Function runs with the Supabase service role key (bypassing RLS) and add composite indexes on (user_id, activity_date) to the activity tables before implementing the inactivity detection query.
Contingency: If service-role access is restricted by organisational policy, implement a dedicated database function (SECURITY DEFINER) that performs the inactivity aggregation and is callable by the Edge Function with limited scope.