Assignment Reminder Cron Trigger
Component Detail
Description
Supabase pg_cron scheduled job or Edge Function cron trigger that invokes the reminder scheduler service on a daily schedule. Handles execution logging, error capture, and ensures only one instance runs concurrently to prevent duplicate dispatches.
assignment-reminder-cron-trigger
Summaries
This component is what makes the reminder system operate autonomously — without it, coordinators would need to manually review assignments every day to identify who needs a follow-up. By running on a reliable daily schedule, the trigger ensures the platform proactively surfaces at-risk assignments without any human initiation, reducing coordinator workload and eliminating the risk of oversight due to staff absence or busy periods. Automated reminders directly support programme consistency and participant outcomes, which are the core value propositions of the platform. The concurrency protection built into this component also ensures organisations never receive duplicate notifications that could confuse or frustrate users, protecting the quality of the communication experience.
Medium-complexity infrastructure component with a hard dependency on the reminder scheduler service being completed and stable before end-to-end testing can begin. The cron registration approach — Supabase pg_cron versus an Edge Function trigger — must be decided early as it affects both the deployment process and the testing strategy; pg_cron requires database-level access for setup whereas Edge Function triggers are managed through the Supabase dashboard. Concurrency control via advisory locks or idempotency checks adds implementation complexity that should be scoped explicitly. Execution logging and failure alerting (notifyOnFailure) should be confirmed with the operations team: where do alerts go (Slack, email, PagerDuty)?
Testing this component in staging requires triggering the cron manually or reducing the schedule temporarily — ensure the environment supports this. Budget for one sprint including integration testing with the scheduler service and a staging dry run.
Infrastructure component that bridges Supabase's scheduling primitives to the reminder scheduler service. For pg_cron, registration happens via SQL: SELECT cron.schedule('reminder-daily', '0 8 * * *', 'SELECT trigger_reminders()') where trigger_reminders() is a Postgres function that invokes the Edge Function or calls the scheduler directly. For Edge Function triggers, the cron expression is defined in the function's config.toml. Concurrency control should use a Postgres advisory lock (pg_try_advisory_lock) acquired at the start of handleCronTrigger() and released in a finally block to guarantee release on error.
logExecution() should write to a dedicated cron_execution_log table with columns: run_at, completed_at, status, error_message — this provides an audit trail and enables consecutive failure detection for notifyOnFailure(). Idempotency: if the lock cannot be acquired, log a SKIPPED status and return cleanly. The scheduler service call should be wrapped in try/catch to ensure lock release even on unhandled errors.
Responsibilities
- Register daily cron schedule in Supabase pg_cron
- Invoke reminder scheduler service on schedule
- Log execution start, completion, and errors
- Prevent concurrent duplicate runs via advisory lock or idempotency check
- Alert on consecutive failures
Interfaces
registerCronJob(schedule: string)
handleCronTrigger(event)
acquireExecutionLock(): boolean
releaseExecutionLock()
logExecution(result)
notifyOnFailure(error)
Relationships
Used Integrations (1)
External integrations and APIs this component relies on