Appointment reminders: email 15 minutes before the start
Python · Django · intermediate · greenfield
Adds the 15-minute reminder job as a management command, with the logic in `appointments/reminders.py`. Each run selects booked appointments whose `starts_at` falls in the window (now, now + 15 minutes] using aware datetimes from `timezone.now()`, then claims each row with a conditional `UPDATE` of `reminder_sent_at` before it sends, so an overlapping run skips rows another run already took. If the SMTP send fails, the claim is released and the failure is logged and counted; a later run that starts before the appointment picks the row up again. The email shows the start in the clinic's local time. No migration: the columns already exist.
Django 5.1 on PostgreSQL 16 (default READ COMMITTED), `USE_TZ = True`, `TIME_ZONE` is the clinic city's zone (Europe/Berlin), so local times need no zone label. Existing model `appointments.Appointment`: `user` (FK), `starts_at` (aware datetime), `status` (`Appointment.BOOKED` or `Appointment.CANCELLED`) and `reminder_sent_at` (nullable aware datetime; nothing reads it except as a sent/unsent flag). The existing reschedule code sets `starts_at` and clears `reminder_sent_at`; cancelling sets `status`. `appointments/management/commands/` already exists with other commands, and `appointments` is in `INSTALLED_APPS`. Cron mails any output on stderr to the on-call address. Every account has a verified, non-empty `email`; `EMAIL_TIMEOUT = 10` and `DEFAULT_FROM_EMAIL` are set. A window never holds more than a few hundred appointments.
Requirements
- Add a management command `send_appointment_reminders` that emails a reminder for every booked appointment starting within the next 15 minutes. Cron runs it every 5 minutes; a run can be late, skipped or overlap another run (a slow SMTP relay, a manual invocation).
- Selection: `status` is `booked`, `reminder_sent_at` is null, and `starts_at` is after the current time and at most 15 minutes ahead (an appointment exactly 15 minutes away is included, one that is starting right now or already started is not). Booking, rescheduling and cancelling close 30 minutes before the start, so every appointment enters the window unchanged and while it is still ahead; a late run still reminds an appointment that is now closer than 15 minutes. Time arithmetic must hold across daylight-saving changes.
- At most one reminder per booking, even when two runs overlap. If the send raises (SMTP error or connection failure), the booking is not marked as reminded, so a later run that starts before the appointment retries it; a rare duplicate after a failure the relay may have accepted is acceptable. One failure must not stop the other reminders in the run.
- The email goes to the account's address with a constant subject, states the date and the time of day in the clinic's local time, and the command prints one summary line, `Reminders sent N, failed M`.
Files touched
- appointments/reminders.py
- appointments/management/commands/send_appointment_reminders.py
--- appointments/reminders.py
+import logging
+import smtplib
+from datetime import timedelta
+
+from django.core.mail import send_mail
+from django.utils import timezone
+
+from .models import Appointment
+
+log = logging.getLogger(__name__)
+
+REMINDER_LEAD = timedelta(minutes=15)
+
+
+def send_due_reminders():
+ now = timezone.now()
+ due = Appointment.objects.filter(
+ status=Appointment.BOOKED,
+ reminder_sent_at__isnull=True,
+ starts_at__gt=now,
+ starts_at__lte=now + REMINDER_LEAD,
+ ).select_related("user")
+
+ sent = failed = 0
+ for appointment in due:
+ # Claim the row before sending, so an overlapping run skips it.
+ claimed = Appointment.objects.filter(
+ pk=appointment.pk,
+ status=Appointment.BOOKED,