Appointment Booking API

Difficulty: Intermediate–Advanced · Builds on: routing, MongoDB & Mongoose, validation, date handling

You are building the booking backend for a small clinic. Doctors publish their working hours, patients book 30-minute appointments, and the server must never let two patients hold the same slot. No auth, no reminders, no payments — just the scheduling core that every booking product is built around.

The point of this exercise is conflict detection under a time dimension. Slots are not stored anywhere — they are computed from working hours minus existing appointments, and every booking is guarded by one carefully written overlap query.

The Data Model (ERD)

How to read it:

• A doctor has many appointments; an appointment belongs to exactly one doctor.
• There is deliberately no Slot collection. A slot is a derived concept: the working day sliced into 30-minute windows, minus whatever is already booked. Storing slots as documents means pre-generating rows forever into the future — computing them is simpler and can't drift out of sync.
• endsAt is stored even though it's always startsAt + 30 min. Denormalizing it makes the overlap query a pure index-friendly comparison instead of date arithmetic inside the query.
• A cancelled appointment stays in the collection as history — it just stops counting as a conflict.

What You'll Practice

• Computing derived resources (available slots) instead of storing them
• The classic interval-overlap check as a Mongoose query
• Returning the right status code for a conflict (409, not 400)
• Validating dates: ISO 8601 parsing, boundary alignment, past-date rejection
• Soft state transitions (booked → cancelled) instead of deleting rows

Requirements

• All appointment times are sent and stored as ISO 8601 UTC (2026-08-03T09:30:00.000Z) — comparing a client's local wall-clock time against a doctor's working hours without a fixed timezone is the classic bug this rule prevents (the clinic's hours are treated as UTC for this exercise).
• Every appointment is exactly 30 minutes and must start on a 30-minute boundary (:00 or :30, zero seconds)
• The appointment must fit inside the doctor's working hours — a 16:30 booking is the last one for a 17:00 close
• No double-booking: if any booked appointment for that doctor overlaps the requested window, respond 409 and create nothing
• Bookings in the past are rejected with 400
• Cancelling sets status: "cancelled" — the slot immediately shows as available again
• The slots endpoint returns only free, future slots for the requested date

API Contract

Method	Endpoint	Request	Success Response
POST	/api/doctors	Body: name, specialty, workingHours (start, end)	201 — created doctor
GET	/api/doctors	Optional query: specialty	200 — array of doctors
GET	/api/doctors/:id/slots	Query: date (YYYY-MM-DD)	200 — array of free slot start times (UTC)
POST	/api/appointments	Body: doctorId, patientName, patientEmail, startsAt	201 — booked appointment
PATCH	/api/appointments/:id/cancel	—	200 — appointment with status: "cancelled"
GET	/api/appointments	Query: email	200 — that patient's appointments, soonest first

Edge Cases to Handle

Request	Expected result
Booking a slot that overlaps an existing booked one	409 — slot already taken, nothing created
startsAt of 09:15 or with non-zero seconds	400 — must align to a 30-minute boundary
Booking at 08:30 when hours start at 09:00	400 — outside working hours
Booking at 17:00 when hours end at 17:00	400 — the slot would end at 17:30, past closing time
startsAt in the past	400 — cannot book in the past
Cancelling an already-cancelled appointment	400 — the client's view is stale; fail loudly so it refetches instead of silently "succeeding" on a no-op
Malformed doctorId (not a valid ObjectId)	400 — rejected by validation, before any query
Well-formed doctorId that matches no doctor	404 — doctor not found
GET /api/doctors/:id/slots without a date query	400 — date is required

Build Guide

Step 1 — Project setup

Same skeleton as the E-commerce API: npm install express mongoose, an express.json() app with /api/doctors and /api/appointments routers, a central error handler, and a Mongoose connection.

Step 2 — Models straight from the ERD

models/doctor.js

import mongoose from "mongoose";

const TIME_24H = /^([01]\d|2[0-3]):[0-5]\d$/;

const doctorSchema = new mongoose.Schema(
  {
    name: { type: String, required: true, trim: true, maxlength: 120 },
    specialty: { type: String, required: true, trim: true, lowercase: true },
    workingHours: {
      start: { type: String, required: true, match: TIME_24H },
      end: { type: String, required: true, match: TIME_24H },
    },
  },
  { timestamps: true },
);

export default mongoose.model("Doctor", doctorSchema);

models/appointment.js

import mongoose from "mongoose";

const appointmentSchema = new mongoose.Schema(
  {
    doctor: { type: mongoose.Schema.Types.ObjectId, ref: "Doctor", required: true },
    patientName: { type: String, required: true, trim: true },
    patientEmail: { type: String, required: true, trim: true, lowercase: true },
    startsAt: { type: Date, required: true },
    endsAt: { type: Date, required: true },
    status: { type: String, enum: ["booked", "cancelled"], default: "booked" },
  },
  { timestamps: true },
);

appointmentSchema.index({ doctor: 1, status: 1, startsAt: 1 });
appointmentSchema.index({ patientEmail: 1, startsAt: 1 });

export default mongoose.model("Appointment", appointmentSchema);

The compound index on { doctor, status, startsAt } exists for exactly one query: the conflict check in Step 4.

Step 3 — Doctor routes and the slots endpoint

POST /api/doctors and GET /api/doctors are plain CRUD — destructure an allowlist of fields, validate that workingHours.start is before workingHours.end, filter the list by specialty if present.

GET /api/doctors/:id/slots?date=YYYY-MM-DD is the first interesting piece. The algorithm:

1. Validate — the id is a real doctor (404 otherwise), the date query matches YYYY-MM-DD (400 otherwise).
2. Generate candidate slots — combine the date with the doctor's working hours to get the day's opening and closing instants in UTC, then walk from open to close in 30-minute steps. Each step is a candidate slot [start, start + 30 min); stop when a slot would end after closing time. For 09:00–17:00 that yields 16 candidates.
3. Load that day's bookings in one query — all appointments for this doctor with status: "booked" and startsAt inside the day. Put their startsAt timestamps in a Set (use getTime() — two Date objects are never equal by reference).
4. Subtract and filter — drop candidates whose start time is in the Set, and drop candidates already in the past. Return the survivors as ISO strings.

Cancelled appointments never enter the Set, so cancelling frees the slot with no extra code.

Step 4 — Booking with conflict detection

This is the heart of the exercise. POST /api/appointments should follow this exact sequence:

1. Validate the shape — doctorId a valid ObjectId, patient name and email present, startsAt parses to a real date. Reject with 400 before touching the database.
2. Check the clock rules — startsAt must be in the future, and its minutes must be 0 or 30 with zero seconds. Compute endsAt = startsAt + 30 min on the server; the client never sends it.
3. Load the doctor — 404 if missing. Convert the doctor's working hours to that day's opening and closing instants, then require startsAt >= open and endsAt <= close. Anything outside is 400.
4. Run the overlap query. Two intervals overlap exactly when each one starts before the other ends: existing.startsAt < requested.endsAt and existing.endsAt > requested.startsAt. As a Mongoose call, that is one findOne for this doctor with status: "booked", startsAt: { $lt: endsAt }, and endsAt: { $gt: startsAt }. Note both comparisons are strict — an appointment ending at 10:00 does not conflict with one starting at 10:00, which is what makes back-to-back bookings legal. If the query finds a document, respond 409 with the conflicting slot's time.
5. Create the appointment with status: "booked" and return 201.

Work through why the two-condition check covers every case: an existing booking that starts during, ends during, or completely surrounds the requested window all satisfy both inequalities, and everything else fails at least one. Most people's first instinct — checking only startsAt equality — misses nothing here because all appointments are the same length, but the interval form is the one that still works when Stretch Goal 2 introduces variable durations. Write the general query now.

There is still a small race: two identical requests can both pass the findOne before either writes. The fix (a partial unique index or a transaction) is Stretch Goal 4 — get the single-request logic airtight first.

Step 5 — Cancel and patient history

PATCH /api/appointments/:id/cancel loads the appointment (404 if missing), rejects with 400 if it is already cancelled, otherwise flips status and saves. GET /api/appointments?email= requires the query param and returns that patient's appointments sorted by startsAt ascending — the second index from Step 2 serves it directly.

Test It with curl

Create a doctor

curl -s -X POST http://localhost:3000/api/doctors \
  -H "Content-Type: application/json" \
  -d '{"name": "Dr. Ayesha", "specialty": "dermatology", "workingHours": {"start": "09:00", "end": "17:00"}}'

See the free slots, then book one (use the real doctor ID)

curl -s "http://localhost:3000/api/doctors/DOCTOR_ID/slots?date=2026-08-03"

curl -s -X POST http://localhost:3000/api/appointments \
  -H "Content-Type: application/json" \
  -d '{"doctorId": "DOCTOR_ID", "patientName": "Zakariya", "patientEmail": "zakariya@example.com", "startsAt": "2026-08-03T09:30:00.000Z"}'

Fetch the slots again — 09:30 should be gone and 15 slots remain.

Failure paths

# same slot for another patient -> 409
curl -s -X POST http://localhost:3000/api/appointments \
  -H "Content-Type: application/json" \
  -d '{"doctorId": "DOCTOR_ID", "patientName": "Ibrahim", "patientEmail": "ibrahim@example.com", "startsAt": "2026-08-03T09:30:00.000Z"}'

# before opening time -> 400
curl -s -X POST http://localhost:3000/api/appointments \
  -H "Content-Type: application/json" \
  -d '{"doctorId": "DOCTOR_ID", "patientName": "Ibrahim", "patientEmail": "ibrahim@example.com", "startsAt": "2026-08-03T08:30:00.000Z"}'

Cancel, then rebook the freed slot (use the real appointment ID)

curl -s -X PATCH http://localhost:3000/api/appointments/APPOINTMENT_ID/cancel

curl -s -X POST http://localhost:3000/api/appointments \
  -H "Content-Type: application/json" \
  -d '{"doctorId": "DOCTOR_ID", "patientName": "Ibrahim", "patientEmail": "ibrahim@example.com", "startsAt": "2026-08-03T09:30:00.000Z"}'

The rebooking should succeed with 201, and GET /api/appointments?email=zakariya@example.com should show Zakariya's appointment as cancelled.

Stretch Goals

1. Per-weekday working hours — Dr. Ayesha works 09:00–17:00 on weekdays but 10:00–13:00 on Saturday; make workingHours an array keyed by weekday
2. Variable appointment durations — a 60-minute consultation type; your interval-overlap query from Step 4 already handles it, the slot generator needs the work
3. Real timezones — store the clinic's IANA timezone on the doctor and convert working hours properly instead of assuming UTC
4. Close the booking race — wrap the conflict check and insert in a MongoDB transaction so two simultaneous requests for the same slot can't both succeed
5. Add auth from the Authentication API — patients cancel only their own appointments, and only admins create doctors