🏠 Overview › ✅ Implementation Tasks ›✅ epic-assignment-follow-up-reminders-cron-infrastructure-task-002

Implement cron execution logging

epic-assignment-follow-up-reminders-cron-infrastructure-task-002 — Add structured execution logging to the cron trigger that captures start time, completion time, total duration, number of assignments evaluated, number of reminders dispatched, and any errors encountered. Store logs in a dedicated cron_execution_logs table in Supabase to support monitoring, auditing, and debugging of the reminder pipeline.

high priority low complexity database pending database specialist Tier 19

Acceptance Criteria

A `cron_execution_logs` table exists in Supabase with columns: id (uuid PK), started_at (timestamptz), completed_at (timestamptz nullable), duration_ms (integer nullable), assignments_evaluated (integer default 0), reminders_dispatched (integer default 0), status (text: 'running'|'success'|'failed'|'skipped'), error_message (text nullable), consecutive_failure_count (integer default 0), created_at (timestamptz default now())
Every cron trigger invocation inserts a log row with status='running' before invoking ReminderSchedulerService
On successful completion, the log row is updated with completed_at, duration_ms, assignments_evaluated, reminders_dispatched, and status='success'
On any uncaught error, the log row is updated with status='failed', error_message populated, and consecutive_failure_count incremented
On a skipped run (lock held), a row is inserted with status='skipped' and completed immediately — no duration or counts set
Querying the 10 most recent rows returns accurate data reflecting actual cron behavior
The table has a Supabase RLS policy that allows read-only access to authenticated admins and full write access only from the service role
A database index exists on started_at DESC to support efficient recent-log queries

Technical Requirements

frameworks
Supabase Edge Functions (Deno/TypeScript)
PostgreSQL
apis
Supabase Database REST API
Supabase service_role key
data models
cron_execution_logs
assignments
performance requirements
Log insert must complete within 100ms to avoid delaying cron execution
Log update on completion must be a single UPDATE by primary key — no scans
Index on started_at ensures log queries complete under 10ms for recent rows
security requirements
Service role key must never be exposed to client; only used server-side in Edge Function
RLS must prevent peer mentors or coordinators from writing to cron_execution_logs
Log rows must not contain PII — error messages should reference assignment IDs only, not user names

Execution Context

Execution Tier
Tier 19

Tier 19 - 2 tasks

Can start after Tier 18 completes

Implementation Notes

Create the table via a Supabase migration file (not ad-hoc SQL) so it is version-controlled. Use a two-step write pattern: INSERT at start with status='running', then UPDATE by the returned row ID at the end. Wrap the UPDATE in a finally block in the Edge Function to guarantee it runs even if ReminderSchedulerService throws. Store the inserted row ID in a local variable at function scope so the finally block can reference it.

Use `Date.now()` timestamps in the Edge Function and store as ISO-8601 strings mapped to timestamptz. Avoid using Supabase Realtime on this table — it is a write-heavy audit log and does not need broadcast.

Testing Requirements

Unit tests: verify the log insert/update SQL statements produce correct column values for success, failure, and skipped scenarios. Integration tests: invoke the cron Edge Function in a test Supabase environment and assert that cron_execution_logs contains exactly one row per invocation with correct status transitions. Test that a simulated ReminderSchedulerService error results in status='failed' and error_message is non-null. Validate the consecutive_failure_count increments on repeated failures and resets to 0 on a success.

Use flutter_test with Supabase test utilities for database state assertions.

Component
Assignment Reminder Cron Trigger
infrastructure medium
Dependencies (1)
Configure and register the pg_cron or Edge Function cron schedule that triggers the daily reminder evaluation cycle. Define the cron expression (e.g., daily at 06:00 UTC), register it in Supabase infrastructure, and wire it to invoke the ReminderSchedulerService endpoint. Ensure the schedule is idempotent and can survive restarts without creating duplicate registrations. epic-assignment-follow-up-reminders-cron-infrastructure-task-001
Epic Risks (2)
high impact low prob technical

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.

high impact medium prob integration

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.

Quick Links
All Tasks Execution Plan