🏠 Overview › ✅ Implementation Tasks ›✅ epic-scenario-push-engagement-core-engine-task-011

Build pg_cron scheduler entry point Edge Function

epic-scenario-push-engagement-core-engine-task-011 — Implement the Scenario Edge Function Scheduler as a Supabase Edge Function designed to be invoked by a pg_cron job. The function queries all active peer mentors from the database and iterates over each, delegating evaluation to the Scenario Trigger Engine. Include pagination to handle large peer mentor rosters without timeouts.

critical priority medium complexity infrastructure pending infrastructure specialist Tier 7

Acceptance Criteria

The scheduler Edge Function accepts invocation from pg_cron via HTTP POST with a shared secret header for authorization — unauthenticated requests return 401
The function queries all peer mentors with status 'active' and assignment status that makes them eligible for scenario evaluation — paused or deactivated mentors are excluded
Pagination is implemented using keyset pagination (cursor-based on peer_mentor.id) with a configurable page size (default 100) — offset pagination is not used
For each page of peer mentors, the scheduler invokes the Scenario Trigger Engine for each mentor, either sequentially or with bounded concurrency (max 10 concurrent invocations)
The function completes processing all pages before returning a response — it does not fire-and-forget across HTTP response boundary
If the Edge Function approaches its timeout limit (configurable threshold, e.g. 25 seconds out of 30 second limit), it stops processing, logs remaining unprocessed mentor count, and returns a partial completion response
The scheduler returns a JSON summary response: { total_active_mentors, total_evaluated, total_dispatched, total_suppressed, total_errors, duration_ms, completed: boolean }
Individual mentor evaluation failures do not abort the batch — errors are caught per-mentor, counted, and processing continues to the next mentor
The scheduler handles the case of zero active peer mentors gracefully — logs and returns summary with all-zero counts

Technical Requirements

frameworks
Supabase Edge Functions (Deno)
Supabase PostgreSQL 15
apis
575-scenario-trigger-engine internal Edge Function API
Supabase REST API
pg_cron invocation via HTTP
data models
assignment
activity
performance requirements
Must handle at least 500 active peer mentors within a 30-second Edge Function timeout using pagination and bounded concurrency
Keyset pagination query must use index on peer_mentor.id — no sequential scans
Bounded concurrency of max 10 parallel trigger engine invocations to avoid overwhelming downstream function
security requirements
Shared secret header (X-Scheduler-Secret) validated against environment variable — prevents unauthorized trigger of batch evaluation
Service role key scoped to scheduler function only — never returned in responses
No PII in scheduler response body — only aggregate counts
pg_cron invocation authenticated via Postgres function with SECURITY DEFINER — no direct HTTP exposure

Execution Context

Execution Tier
Tier 7

Tier 7 - 84 tasks

Can start after Tier 6 completes

Implementation Notes

Implement keyset pagination as `SELECT id FROM peer_mentors WHERE status = 'active' AND id > $cursor ORDER BY id ASC LIMIT $pageSize`. Store the last ID from each page as the cursor for the next query. Use `Promise.all` with a semaphore (simple counter + awaited promises) for bounded concurrency rather than a third-party library — Deno's module ecosystem in Edge Functions can be unreliable. The timeout guard should check `Date.now() - startTime > TIMEOUT_THRESHOLD_MS` at the start of each page loop iteration, not mid-page.

Design the scheduler as stateless — it does not maintain any cross-invocation state; pg_cron handles scheduling. The shared secret should be a random 256-bit hex string stored as a Supabase Edge Function secret (environment variable), compared using `crypto.timingSafeEqual` to prevent timing attacks.

Testing Requirements

Unit tests: (1) pagination cursor advances correctly across multiple pages, (2) bounded concurrency limiter respects max parallel invocations, (3) timeout guard triggers at threshold and returns partial=true in response, (4) zero mentors handled without error, (5) per-mentor error caught and counted without aborting batch. Integration test with local Supabase: seed 50 active mentors, invoke scheduler, assert all 50 evaluation requests received by mock trigger engine. Pagination test: seed 250 mentors with page size 100, assert 3 pages queried. Load test: seed 500 mentors, assert function completes within 28 seconds.

Unauthorized request test: missing or wrong secret returns 401.

Component
Scenario Edge Function Scheduler
infrastructure medium
Dependencies (1)
Instrument the Scenario Trigger Engine Edge Function with structured JSON logging: log each evaluation run with peer_mentor_id, scenario_type, evaluation_result (triggered/suppressed/opted_out/cooldown), and relevant metric values. Include error logging with stack traces for unexpected failures. Ensure logs are compatible with Supabase log streaming for operational monitoring. epic-scenario-push-engagement-core-engine-task-010
Epic Risks (3)
high impact medium prob technical

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.

high impact low prob technical

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.

medium impact medium prob integration

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.

Quick Links
All Tasks Execution Plan