Appointment Reminders Backend for Clinics: Scheduling, SMS and Retries

Missed appointments cost clinics an empty slot and the patient a delayed visit, and many clinics send a reminder a day before plus one a couple of hours before. The backend behind those messages is small, and it has to send each reminder close to the right time, never send the same one twice when a job overlaps or a provider call is retried, render the time in the clinic's time zone rather than the server's, and keep anything medical out of a text message that may show on a locked screen.

This guide designs that backend: an appointments table, a scheduled job that claims reminders as they come due, a queue that sends one SMS per reminder with retries, and the message content, first by hand on AWS and then in TypeScript with Encore.

The reminders backend at a glance

How do you build it by hand on AWS?

1. Appointments: a Postgres table with the patient's phone number, the clinic's name and IANA time zone, the start time in UTC, a status and one reminded_*_at column per reminder type.
2. Schedule: an EventBridge Scheduler rule invokes a Lambda function or ECS task every five minutes. It runs one UPDATE ... RETURNING per reminder type that marks every appointment entering the window and returns those rows, which is the claim. PostgreSQL's RETURNING clause makes the mark and the read a single statement.
3. Sending: the job puts one SQS message per claimed reminder. A worker renders the text and calls the SMS provider with an idempotency key, and a dead-letter queue catches numbers that keep failing.
4. Secrets and access: the provider key lives in Secrets Manager. The scheduled task needs the database and the queue, the worker needs the queue and the secret, and each gets an IAM role for that.

How do you build it with Encore?

Encore lets you declare the database, schedule, queue and secrets a backend needs in TypeScript or Go, and its platform creates them in your own AWS or GCP account. Here the whole reminder flow is one service, and on AWS the deploy creates RDS, SNS and SQS with a dead-letter queue and a Secrets Manager entry, per the AWS infrastructure docs. The schedule itself needs no cloud resource.

Set up the app

-- reminders/migrations/1_create_appointments.up.sql
CREATE TABLE appointments (
    id BIGSERIAL PRIMARY KEY,
    patient_phone TEXT NOT NULL,
    clinic_name TEXT NOT NULL,
    clinic_tz TEXT NOT NULL,            -- e.g. 'Europe/Vienna'
    starts_at TIMESTAMPTZ NOT NULL,
    status TEXT NOT NULL DEFAULT 'booked', -- booked, confirmed, cancelled
    reminded_24h_at TIMESTAMPTZ,
    reminded_2h_at TIMESTAMPTZ
);
CREATE INDEX appointments_upcoming ON appointments (starts_at) WHERE status <> 'cancelled';

// reminders/reminders.ts
import { api } from "encore.dev/api";
import { CronJob } from "encore.dev/cron";
import { Subscription, Topic } from "encore.dev/pubsub";
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { secret } from "encore.dev/config";

const db = new SQLDatabase("reminders", { migrations: "./migrations" });
const smsApiKey = secret("SmsApiKey");

interface ReminderDue {
  appointmentId: number;
  phone: string;
  clinicName: string;
  clinicTz: string;
  startsAt: string; // ISO timestamp, UTC
  kind: "24h" | "2h";
}

export const reminderDue = new Topic<ReminderDue>("reminder-due", {
  deliveryGuarantee: "at-least-once",
});

// Claims a reminder once the appointment is at most 24 hours or 2 hours away,
// and not already inside the next, tighter window. A missed run is picked up
// on the next pass without sending a 24-hour reminder a day early. The job
// runs every five minutes, and the mark in the same statement keeps overlapping
// runs from picking the same appointment twice.
export const queueReminders = api({ expose: false }, async (): Promise<{ queued: number }> => {
  let queued = 0;
  for (const kind of ["24h", "2h"] as const) {
    const col = kind === "24h" ? "reminded_24h_at" : "reminded_2h_at";
    const rows = await db.rawQueryAll<{
      id: number;
      patient_phone: string;
      clinic_name: string;
      clinic_tz: string;
      starts_at: Date;
    }>(
      `UPDATE appointments SET ${col} = now()
       WHERE status <> 'cancelled' AND ${col} IS NULL
         AND starts_at <= now() + $1::interval + interval '5 minutes'
         AND starts_at > now() + $2::interval
       RETURNING id, patient_phone, clinic_name, clinic_tz, starts_at`,
      kind === "24h" ? "24 hours" : "2 hours",
      kind === "24h" ? "2 hours" : "0 minutes",
    );
    for (const r of rows) {
      await reminderDue.publish({
        appointmentId: r.id,
        phone: r.patient_phone,
        clinicName: r.clinic_name,
        clinicTz: r.clinic_tz,
        startsAt: r.starts_at.toISOString(),
        kind,
      });
      queued++;
    }
  }
  return { queued };
});

// Managed by the Encore platform; does not run locally or in preview environments.
const _ = new CronJob("queue-reminders", {
  title: "Queue appointment reminders",
  every: "5m",
  endpoint: queueReminders,
});

// One SMS per message. No diagnosis or visit reason in the text: only time and place.
const __ = new Subscription(reminderDue, "send-sms", {
  retryPolicy: { minBackoff: "30s", maxBackoff: "10m", maxRetries: 8 },
  handler: async (r) => {
    const when = new Date(r.startsAt).toLocaleString("en-GB", {
      timeZone: r.clinicTz,
      weekday: "short",
      hour: "2-digit",
      minute: "2-digit",
    });
    const resp = await fetch("https://sms.example.com/v1/messages", {
      method: "POST",
      headers: {
        Authorization: `Bearer ${smsApiKey()}`,
        "Content-Type": "application/json",
        "Idempotency-Key": `appt-${r.appointmentId}-${r.kind}`,
      },
      body: JSON.stringify({
        to: r.phone,
        body: `Reminder: your appointment at ${r.clinicName} is ${when}. Reply C to confirm or X to cancel.`,
      }),
    });
    if (!resp.ok) throw new Error(`sms provider returned ${resp.status}`); // retried
  },
});

The claim and the send are separate steps: queueReminders marks rows and publishes, so a run that overlaps with the previous one finds nothing left to claim, and a slow SMS provider never holds up the scan. The subscriber retries with backoff and moves a reminder to the dead-letter queue after the last attempt, as the Pub/Sub docs describe. The idempotency key covers the case where the provider accepted a message but the response was lost.

If publish fails after the UPDATE commits, that reminder is marked but never sent. For reminders that is usually acceptable, and the fix if it is not is an outbox table that the job writes in the same transaction and a second step that publishes from it.

Cron jobs run on the Encore platform's schedule in the cloud and do not run locally or in preview environments, per the cron jobs docs. With encore run, which starts Postgres locally and runs the topic, you call queueReminders from the local development dashboard and watch each published reminder reach the subscriber. The SMS key is set per environment, and its value stays out of the repository, where a coding agent working in the code sees the secret's name but not its value, as covered in the secrets docs.

Best fit: Healthcare and clinic software teams who want reminders, the queue and the provider key in one TypeScript service running in their own AWS or GCP account.

Things to decide early

• Time zones. Store starts_at in UTC and the clinic's IANA zone next to it, and format with that zone at send time. toLocaleString with a timeZone option handles daylight saving changes for you.
• Quiet hours. A 2-hour reminder for an 8:00 appointment goes out at 6:00. Decide whether to send early-morning reminders the evening before instead.
• Replies. Confirm and cancel replies come back through the provider's inbound webhook, which needs its own signed endpoint and should update status with a conditional update, like the claim.
• Consent and content. Record the patient's consent to SMS and keep the message to time, place and how to respond. Anything beyond that belongs behind a login.

Related guides

• Background jobs in Go
• How to host a webhook receiver and worker on AWS
• Cron jobs on AWS without Terraform
• How to add cron jobs to an Echo app
• AWS Secrets Manager with Terraform