Create deployment script and cron trigger configuration

epic-scenario-based-follow-up-prompts-infrastructure-task-007 — Write the Supabase Edge Function deployment script (deploy.sh or Makefile target) that deploys the function with correct environment variable injection. Configure the pg_cron or Supabase cron trigger for default hourly invocation with a configurable schedule. Document the deployment steps, required secrets setup, and how to adjust the cron frequency per environment.

high priority medium complexity deployment pending devops specialist Tier 5

Acceptance Criteria

A `deploy.sh` script (or equivalent Makefile target `make deploy-scenario-eval`) deploys the Edge Function to Supabase using `supabase functions deploy scenario-evaluation-edge-function`

The script accepts a `--env` flag with values `staging` and `production` and selects the correct Supabase project reference accordingly

All required environment secrets (`SCENARIO_SCHEDULER_URL`, `KILL_SWITCH_ENABLED`, `IDEMPOTENCY_WINDOW_HOURS`, `SUPABASE_SERVICE_ROLE_KEY`) are documented with descriptions in a `.env.example` file — no secrets are committed to the repository

A `supabase/migrations/YYYYMMDDHHMMSS_add_scenario_cron_trigger.sql` migration file creates the pg_cron job with default schedule `0 * * * *` (hourly on the hour)

The cron schedule is parameterised via a Postgres configuration table row or a documented SQL replacement comment so it can be changed per environment without redeploying code

The deployment script verifies the function deployed successfully by calling `supabase functions list` and grepping for the function name — exits with code 1 if not found

A `docs/deployment/scenario-evaluation-edge-function.md` file documents: prerequisites, step-by-step deployment, secrets setup, cron adjustment, and rollback procedure

Running the script against staging does not affect the production project reference

Technical Requirements

frameworks

Supabase CLI

pg_cron (Postgres extension)

bash

apis

Supabase Management API (via supabase CLI)

Supabase Edge Functions runtime

performance requirements

Deployment script must complete within 60 seconds for a cold deploy

Cron trigger must have a maximum jitter of 1 minute relative to scheduled time

security requirements

Service role key must be injected via `supabase secrets set` — never written to any file in the repository

The `.env.example` file must contain only placeholder values (e.g., `SUPABASE_SERVICE_ROLE_KEY=your-key-here`), not real credentials

The pg_cron job must invoke the Edge Function via the Supabase internal `net.http_post` function using the service role key stored as a Postgres secret, not hardcoded in SQL

Execution Context

Execution Tier

Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

View Full Execution Plan

Implementation Notes

Use the Supabase CLI `supabase functions deploy` command with `--project-ref` flag controlled by the `--env` argument. Store project refs in the script as constants (`STAGING_REF`, `PRODUCTION_REF`). For the pg_cron trigger, create a migration that: (1) enables `pg_cron` extension if not already enabled; (2) creates a `scheduler_config` table with a `cron_schedule` column defaulting to `'0 * * * *'`; (3) creates the cron job reading its schedule from this table. This pattern allows ops to `UPDATE scheduler_config SET cron_schedule = '*/30 * * * *'` without running a migration.

Use `supabase secrets set --env-file .env.staging` for staging secret injection. Include a `--dry-run` flag that prints all steps without executing them.

Testing Requirements

Manual validation checklist (to be consumed by task-008): (1) run `deploy.sh --env staging` on a clean machine with only supabase CLI installed — confirm it deploys without errors; (2) confirm the pg_cron job appears in `cron.job` table with correct schedule; (3) manually trigger the cron job with `SELECT cron.run_job(job_id)` and confirm the Edge Function logs appear in Supabase Dashboard → Edge Functions → Logs; (4) change the schedule in the configuration table and confirm the next execution uses the new schedule without redeployment. No automated tests are written for the deployment script itself — coverage is provided by the integration test suite in task-008.

Component

Scenario Evaluation Edge Function

infrastructure medium

Dependencies (1)

Instrument the Edge Function to emit structured JSON log entries at key execution points: function invoked, kill switch status, scheduler start, per-activity outcome (success/skip/error with reason), and final run summary with counts and duration. Log format must include timestamp, run_id, activity_id where applicable, and outcome fields to support log-based monitoring and alerting. epic-scenario-based-follow-up-prompts-infrastructure-task-006

Epic Risks (2)

medium impact low prob technical

Supabase Edge Functions on Deno can have cold-start latency of 500ms–2s. If the evaluation window contains many activities (e.g., post-holiday catch-up), the function may approach the 60-second invocation timeout before completing all evaluations.

Mitigation & Contingency

Mitigation: Implement pagination in the activity fetch query with a configurable page size; process pages sequentially and commit history records per page so partial runs are recoverable on the next invocation.

Contingency: If timeout remains an issue at scale, split the evaluation into per-chapter invocations triggered by a fan-out pattern using Supabase Realtime or a lightweight queue.

medium impact low prob dependency

Supabase cron triggers (pg_cron or Edge Function schedules) may miss invocations during platform maintenance windows, causing evaluation gaps that delay time-sensitive prompts beyond their intended delivery window.

Mitigation & Contingency

Mitigation: Configure the look-back window to be 2× the cron interval (e.g., 2-hour look-back for hourly cron) so a single missed invocation does not result in missed prompts; log each run's look-back range for auditability.

Contingency: If missed invocations are detected via monitoring alerts, implement a manual re-trigger endpoint accessible to admins that runs the evaluation for a specified time range.

Quick Links

All Tasks Execution Plan