32 lines
2.0 KiB
Markdown
32 lines
2.0 KiB
Markdown
Postgres + pg_cron scheduled shift reminders
|
|
|
|
1) Run migrations
|
|
- Apply `supabase/migrations/20260318_add_scheduled_notifications.sql` (and other pending migrations) to your Supabase DB.
|
|
|
|
2) Enable pg_cron (requires Supabase project admin)
|
|
- If your Supabase tier supports it, enable the `pg_cron` extension.
|
|
- Example (run as a privileged role):
|
|
CREATE EXTENSION IF NOT EXISTS pg_cron;
|
|
SELECT cron.schedule('shift_reminders_every_min', '*/1 * * * *', $$SELECT public.enqueue_due_shift_notifications();$$);
|
|
|
|
3) Deploy processor Edge Function
|
|
- Add `supabase/functions/process_scheduled_notifications/` to your Supabase functions and deploy with the following required env vars:
|
|
- `SUPABASE_URL`
|
|
- `SUPABASE_SERVICE_ROLE_KEY`
|
|
- `SEND_FCM_URL` (the HTTP URL for your existing `send_fcm` function, e.g., https://<project>.functions.supabase.co/send_fcm)
|
|
- Optional: `PROCESSOR_BATCH_SIZE` (default 50)
|
|
|
|
- You can deploy via `supabase functions deploy process_scheduled_notifications` or using your CI.
|
|
|
|
4) Scheduling / triggering processor
|
|
- Option A (recommended): Use `pg_cron` to only enqueue rows, and configure a small interval GitHub Actions or Cloud Scheduler to call the Edge Function endpoint (POST) every minute. This keeps sending out of the DB.
|
|
- Option B: Use `pg_cron` to call the Edge Function HTTP endpoint directly if `pg_http` is available (not recommended unless approved).
|
|
|
|
5) Verification
|
|
- Insert a test `duty_schedules` record 15 minutes ahead and run `SELECT public.enqueue_due_shift_notifications();` manually — confirm `scheduled_notifications` row appears.
|
|
- Call the Edge Function (`supabase functions invoke process_scheduled_notifications --project <id>`) or POST to its URL — confirm `send_fcm` receives payload and `scheduled_notifications` row becomes processed.
|
|
|
|
6) Monitoring
|
|
- Watch `cron.job_run_details` for cron runs and `scheduled_notifications` rows with `retry_count` > 3.
|
|
- Inspect `notification_pushes` table to ensure deduplication works.
|