Add advisory lock to prevent overlapping runs
epic-assignment-follow-up-reminders-cron-infrastructure-task-003 — Implement a PostgreSQL advisory lock or idempotency guard on the cron trigger to prevent concurrent overlapping executions. If a previous run is still in progress when the next cron fires, the new invocation should detect the lock, log a skipped-run event, and exit gracefully without invoking ReminderSchedulerService. Release the lock reliably on both success and error paths.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 20 - 2 tasks
Can start after Tier 19 completes
Implementation Notes
Expose pg_try_advisory_lock and pg_advisory_unlock as Supabase RPC functions so they can be called from the Edge Function via the Supabase client. Define a single LOCK_KEY constant (e.g., `12345678`) at the top of the Edge Function file — document why this specific value was chosen. Use a try/finally pattern: acquire lock → try { invoke service } finally { release lock }. Note: PostgreSQL session-level advisory locks are tied to the database connection session.
Supabase Edge Functions use connection pooling; prefer transaction-level advisory locks (`pg_try_advisory_xact_lock`) if the connection may be reused, as transaction-level locks are automatically released at transaction end, eliminating orphan risk. Evaluate which lock scope is appropriate based on Supabase's pooler configuration.
Testing Requirements
Integration tests: spawn two concurrent invocations of the cron Edge Function against a test Supabase instance. Assert that exactly one run completes with status='success' and the other records status='skipped'. Test crash recovery: forcibly terminate a running function mid-execution and verify the subsequent invocation succeeds (no orphaned lock). Unit tests: mock the Supabase RPC to return false from pg_try_advisory_lock and assert that ReminderSchedulerService is never called and the skipped log row is written.
Verify the finally block releases the lock even when ReminderSchedulerService throws an exception.
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.