Pre-appointment reminder sent at configurable interval via scheduled job, not polling loop

A setInterval or while(true) polling loop that checks all bookings every N seconds keeps a database connection open and runs a full-table scan on every iteration — even when there are zero due reminders. Under load, this pattern burns CPU, database query budget, and queue worker threads continuously. CWE-770 (uncontrolled resource consumption) and ISO 25010 performance-efficiency.resource-utilization both capture this failure. A cron-based job queue fires only when reminders are actually due, scales horizontally without contention, and is restartable after a crash. Beyond efficiency, a polling loop running in a long-lived process cannot recover gracefully from crashes — in-flight checks are simply lost.

Replace any setInterval or setTimeout loop with a BullMQ repeatable cron job in src/jobs/reminders.ts. Store the reminder interval per service in the database so it can be adjusted without a code deploy:

const reminderQueue = new Queue('reminders', { connection: redis })

// Cron fires every 5 minutes — only does work when reminders are due
await reminderQueue.add('check-due-reminders', {}, { repeat: { pattern: '*/5 * * * *' } })

const worker = new Worker('reminders', async (job) => {
  if (job.name === 'check-due-reminders') {
    const dueBookings = await db.bookings.findMany({
      where: { reminder_due_at: { lte: new Date() }, reminder_sent: false, status: 'confirmed' },
    })
    for (const booking of dueBookings) {
      await reminderQueue.add('send-reminder', { bookingId: booking.id })
    }
  }
}, { connection: redis })

Add an index on (reminder_due_at, reminder_sent, status) to keep the due-bookings query fast as volume grows.

• ID: reminder-scheduled-job

• Severity: critical

• What to look for: Find the reminder scheduling mechanism. Search for: (1) Job queue with cron — BullMQ repeatable jobs, Agenda scheduled jobs, node-cron, or a cloud scheduler (AWS EventBridge, Vercel Cron). Quote the scheduler setup. (2) Verify it is NOT a polling loop — search for setInterval, setTimeout in a loop, or while(true) with sleep that checks all bookings. A cron job that queries due reminders is acceptable; a setInterval that polls every N seconds is not. (3) Check that reminder timing is configurable — look for a reminder_interval column in the database or a config setting per service/booking type. If hard-coded, count how many places the interval value appears.

• Pass criteria: Enumerate all scheduler mechanisms found. ALL of the following: (1) Reminders use a job queue cron or task scheduler — quote the scheduler setup code (cron pattern, queue name, or cloud scheduler config). A setInterval or setTimeout polling loop does NOT count as pass. (2) 0% of reminder checking uses polling loops (setInterval/setTimeout). (3) Reminder timing is configurable per booking or per service type (stored in database or config) — quote the configuration source. At least 1 configurable interval source must exist. A single hard-coded value appearing in 1 place is acceptable only if documented as a default.

• Fail criteria: setInterval or setTimeout loop used for reminder checking. No job queue or cron — reminders only work if a manual script is run. Reminder interval hard-coded in multiple places with no config. No reminder scheduling mechanism found at all.

• Skip (N/A) when: Reminders are not implemented — no reminder-related code found (searched for "reminder", "scheduled notification", "pre-appointment", cron patterns related to bookings).

• Detail on fail: Quote the reminder mechanism found (or state "none found"). If a polling loop is used, quote it. State the interval value and whether it is configurable. Example: "setInterval at src/lib/reminders.ts:5 runs every 60000ms to check all bookings. Interval is hard-coded to 24 hours at line 12 and again at line 45 — no configuration.".

• Remediation: Use a job queue with cron for efficient, reliable reminder scheduling:

  // src/jobs/reminders.ts — BullMQ cron job
  const reminderQueue = new Queue('reminders', { connection: redis })
  
  // Cron: check for due reminders every 5 minutes
  await reminderQueue.add('check-due-reminders', {}, {
    repeat: { pattern: '*/5 * * * *' },
  })
  
  // Worker
  const worker = new Worker('reminders', async (job) => {
    if (job.name === 'check-due-reminders') {
      const dueBookings = await db.bookings.findMany({
        where: {
          reminder_due_at: { lte: new Date() },
          reminder_sent: false,
          status: 'confirmed',
        }
      })
      for (const booking of dueBookings) {
        await reminderQueue.add('send-reminder', { bookingId: booking.id })
      }
    }
  }, { connection: redis })
  
  // src/lib/bookings.ts — configurable reminder interval
  async function confirmBooking(bookingId: string) {
    const booking = await db.bookings.findUnique({ where: { id: bookingId } })
    const service = await db.services.findUnique({ where: { id: booking.serviceId } })
    const reminderMs = service.reminder_interval_ms ?? 24 * 60 * 60 * 1000 // default 24h
    const reminderDueAt = new Date(booking.start_time.getTime() - reminderMs)
    await db.bookings.update({
      where: { id: bookingId },
      data: { status: 'confirmed', reminder_due_at: reminderDueAt }
    })
  }
  

• Cross-reference: Booking Flow & Lifecycle audit checks booking status transitions that trigger reminder scheduling. Database Audit checks for index on reminder_due_at column. Performance Audit checks cron job efficiency.