🏠 Overview › ✅ Implementation Tasks ›✅ epic-scenario-based-follow-up-prompts-infrastructure-task-005

Implement idempotency guard to prevent duplicate prompt scheduling

epic-scenario-based-follow-up-prompts-infrastructure-task-005 — Add an idempotency check at the start of each scheduler invocation cycle. Query the prompt_history table for entries already created within the current evaluation window. Skip any activity that already has a prompt record for the current window, ensuring repeated cron executions within the same hour produce no duplicate prompts.

high priority medium complexity backend pending backend specialist Tier 4

Acceptance Criteria

At the start of each invocation, the Edge Function queries `prompt_history` for all records with `created_at` falling within the current evaluation window (e.g., current UTC hour floor to ceiling)
The result is materialised into a `Set<string>` of activity_ids that already have a prompt in the window
Before calling the Scheduler Service for any activity, the guard checks if the activity_id is in the Set — if yes, the activity is pushed to the `skipped` array with reason `'already_scheduled_in_window'` and the loop continues
Running the Edge Function twice within the same evaluation window with identical activity data produces exactly zero new `prompt_history` rows on the second run
Running the Edge Function in two consecutive different evaluation windows produces prompt rows in both windows (idempotency does not bleed across windows)
The idempotency query is a single bulk SELECT before the loop — not one query per activity inside the loop
Idempotency check must handle an empty `prompt_history` table gracefully (no errors, all activities proceed)
Evaluation window boundaries are calculated from a deterministic formula based on `Date.now()` — not from any mutable state — so they are reproducible across retries

Technical Requirements

frameworks
Supabase Edge Functions (Deno/TypeScript)
Supabase JS client (supabase-js v2)
apis
Supabase PostgREST API — `prompt_history` table SELECT
data models
prompt_history
activities
performance requirements
Bulk idempotency SELECT must use an indexed column — ensure `prompt_history.activity_id` has a database index
The pre-loop query must complete within 2 seconds; add a query timeout guard
Set lookup O(1) — do not use Array.includes() for the per-activity check
security requirements
Idempotency query must be scoped to the service-role context — do not expose `prompt_history` via anon key
Evaluation window calculation must be server-side (Edge Function clock) — never trust a client-supplied window parameter

Execution Context

Execution Tier
Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

Implementation Notes

Define a `getEvaluationWindow()` helper that returns `{ windowStart: Date, windowEnd: Date }` computed as UTC hour floor/ceiling of the current timestamp. Query: `supabase.from('prompt_history').select('activity_id').gte('created_at', windowStart.toISOString()).lte('created_at', windowEnd.toISOString())`. Build a `const alreadyScheduled = new Set(rows.map(r => r.activity_id))`. In the per-activity loop (from task-004), check `if (alreadyScheduled.has(activity.id))` before the try/catch wrapper and throw a `SkipSignal('already_scheduled_in_window')` which the outer loop catches and routes to the `skipped` array.

Keep the window duration configurable via an environment variable `IDEMPOTENCY_WINDOW_HOURS` defaulting to `1` so it can be widened for hourly cron without code changes.

Testing Requirements

Unit tests: (1) given a Set containing activity_id 'act-1', processing 'act-1' returns skip signal immediately without calling Scheduler Service; (2) given an empty Set, processing 'act-1' calls Scheduler Service normally; (3) evaluation window boundaries are correct for known timestamps (midnight, 23:59, etc.). Integration tests: (1) run function once, assert N new rows in `prompt_history`; (2) run function again within same window, assert zero new rows; (3) advance mock clock past window boundary, run again, assert N new rows. Use Supabase local dev (supabase start) for integration tests against a real SQLite-backed PostgREST stub.

Component
Scenario Evaluation Edge Function
infrastructure medium
Dependencies (1)
Call the Scenario Prompt Scheduler Service from the Edge Function and wrap the per-activity processing loop in individual try/catch blocks so that a single bad activity record cannot abort the entire run. Aggregate per-record outcomes (success, skipped, error) into a summary object that is emitted in the final structured log entry. epic-scenario-based-follow-up-prompts-infrastructure-task-004
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