tasq/supabase/DEPLOYMENT.md

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://.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.