Designing a Medical Appointment Booking System: Time Slots and Concurrency Control

1. Overview

Booking a medical appointment sounds simple: the patient picks a doctor, picks a time, then clicks “Book”. The hard part lies elsewhere. Hundreds of people can click the same slot at 7:00 AM when the clinic opens slots. The system must ensure no two people get the same spot.

This article covers the complete design: requirements, data model, API, booking and cancellation flows. Then it focuses on the most important part: preventing double bookings and handling burst load. After reading, you can build the core booking logic yourself using Spring Boot and PostgreSQL. This core remains correct even under heavy concurrent traffic.

2. Requirements

2.1. Functional Requirements

• Doctors (or admins) create working schedules. The system generates fixed slots, for example 15 minutes per slot.
• Each slot has a capacity. A specialist consultation is usually 1. Vaccination might be 10 people per time window.
• Patients view available slots by doctor and date, then book or cancel.
• A patient cannot hold two valid appointments in the same slot.
• After successful booking, the system sends SMS or email confirmation.

2.2. Scale and Constraints

• 2,000 doctors. Each doctor has about 40 slots per day. Schedules open 14 days in advance, meaning roughly 1.1 million active slots.
• Read traffic far exceeds write traffic, ratio about 50:1.
• Burst load: a popular doctor’s schedule opens at 7:00. The first 30 seconds might see 5,000 requests/second hitting a few dozen slots.
• Strong consistency for booking operations. Never exceed capacity. The list of empty slots can be a few seconds stale.
• Clients may retry on network issues. Therefore, booking operations must be idempotent.

3. Data Model

A key decision: create slots in advance as separate rows instead of computing “available hours” on the fly. When each slot is a row, you have a concrete point to lock and check capacity. Conflicts are resolved at the row level.

CREATE TABLE time_slot (
    id            BIGSERIAL PRIMARY KEY,
    doctor_id     BIGINT      NOT NULL,
    start_at      TIMESTAMPTZ NOT NULL,
    end_at        TIMESTAMPTZ NOT NULL,
    capacity      INT         NOT NULL CHECK (capacity > 0),
    booked_count  INT         NOT NULL DEFAULT 0,
    CONSTRAINT chk_booked CHECK (booked_count BETWEEN 0 AND capacity),
    CONSTRAINT uq_doctor_start UNIQUE (doctor_id, start_at)
);

CREATE TABLE appointment (
    id               BIGSERIAL PRIMARY KEY,
    slot_id          BIGINT      NOT NULL REFERENCES time_slot(id),
    patient_id       BIGINT      NOT NULL,
    status           VARCHAR(20) NOT NULL,          -- BOOKED, CANCELLED
    idempotency_key  VARCHAR(64) NOT NULL UNIQUE,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uq_active_patient_slot
    ON appointment (slot_id, patient_id)
    WHERE status = 'BOOKED';

Each constraint in the schema plays its own role:

• chk_booked is the final safeguard. If code has a bug, the database still rejects booked_count exceeding capacity.
• uq_doctor_start prevents duplicate slots when the schedule-creation job reruns.
• uq_active_patient_slot is a PostgreSQL partial unique index. A patient can have at most one BOOKED appointment in each slot. Cancelled appointments don’t count, so the patient can rebook.
• idempotency_key UNIQUE prevents client retries from creating a second appointment.

The JPA entity for time_slot (getters omitted):

@Entity
@Table(name = "time_slot")
public class TimeSlot {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long doctorId;
    private Instant startAt;
    private Instant endAt;
    private int capacity;
    private int bookedCount;

    protected TimeSlot() {}

    public TimeSlot(Long doctorId, Instant startAt, int capacity) {
        this.doctorId = doctorId;
        this.startAt = startAt;
        this.endAt = startAt.plus(15, ChronoUnit.MINUTES);
        this.capacity = capacity;
    }
    // getters...
}

4. API Design

GET    /api/doctors/{doctorId}/slots?date=2026-10-12   -> list of available slots
POST   /api/appointments                               -> book an appointment
       Header: Idempotency-Key: 6f1c...
       Body:   { "slotId": 1001, "patientId": 77 }
DELETE /api/appointments/{id}                          -> cancel appointment

Response codes for POST /api/appointments:

201 Created   -> booking successful
200 OK        -> retry with same Idempotency-Key, return existing appointment
409 Conflict  -> slot is full or patient already has an appointment in this slot
429 Too Many Requests -> rate limit exceeded

The client generates Idempotency-Key (UUID) for each button click. On retry, the client sends the same key.

5. Preventing Double Bookings

5.1. Why “Check Then Write” Is Wrong

The first approach many developers think of:

TimeSlot slot = slotRepository.findById(slotId).orElseThrow();
if (slot.getBookedCount() < slot.getCapacity()) {   // (1) read
    slot.setBookedCount(slot.getBookedCount() + 1); // (2) write
    appointmentRepository.save(appointment);
}

Suppose threads A and B both read at step (1) and see booked_count = 0, capacity = 1. Both pass the condition and both write. Two appointments end up for the same spot. At PostgreSQL’s default READ COMMITTED isolation level, this is a lost update race.

5.2. Conditional Update: Check and Write in One Statement

The fix is to merge the check and write into a single UPDATE statement with a WHERE clause. The database locks the row during the update. The second thread waits, then re-evaluates the WHERE condition against the new value.

public interface TimeSlotRepository extends JpaRepository<TimeSlot, Long> {

    @Modifying
    @Query("""
        update TimeSlot s
           set s.bookedCount = s.bookedCount + 1
         where s.id = :id
           and s.bookedCount < s.capacity
           and s.startAt > :now
        """)
    int tryReserve(@Param("id") long id, @Param("now") Instant now);

    @Modifying
    @Query("update TimeSlot s set s.bookedCount = s.bookedCount - 1 where s.id = :id and s.bookedCount > 0")
    int release(@Param("id") long id);
}

tryReserve returns the number of rows affected. If the result is 1, you got the slot. If it’s 0, the slot is full, time passed, or doesn’t exist. No need for SELECT ... FOR UPDATE or optimistic locking with a version column. The lock is held only briefly.

The booking service:

@Service
public class BookingService {
    private final TimeSlotRepository slotRepository;
    private final AppointmentRepository appointmentRepository;
    private final Clock clock;

    public BookingService(TimeSlotRepository slotRepository,
                          AppointmentRepository appointmentRepository,
                          Clock clock) {
        this.slotRepository = slotRepository;
        this.appointmentRepository = appointmentRepository;
        this.clock = clock;
    }

    @Transactional
    public Appointment book(long slotId, long patientId, String idempotencyKey) {
        var existing = appointmentRepository.findByIdempotencyKey(idempotencyKey);
        if (existing.isPresent()) {
            return existing.get();                       // retry: return old result
        }
        if (slotRepository.tryReserve(slotId, clock.instant()) == 0) {
            throw new SlotFullException(slotId);
        }
        // saveAndFlush to make constraint violations fail immediately in this transaction
        return appointmentRepository.saveAndFlush(
                Appointment.booked(slotId, patientId, idempotencyKey));
    }
}

Clock should be a bean, e.g. Clock.systemUTC(), so tests can inject a fixed time. The code works as follows:

• The idempotency check runs first. If the client retries after the first request committed, the client gets back the exact same appointment.
• If tryReserve succeeds but saveAndFlush violates uq_active_patient_slot (patient already has an appointment), Spring throws DataIntegrityViolationException. The transaction rolls back entirely, including the increment to booked_count. The spot is automatically returned.
• Two retry requests can arrive nearly simultaneously and both miss the key. Then one will violate idempotency_key UNIQUE. In the controller, catch this error, fetch the appointment by key, and return 200.

@ExceptionHandler(DataIntegrityViolationException.class)
ResponseEntity<ProblemDetail> onConflict(DataIntegrityViolationException ex) {
    return ResponseEntity.status(HttpStatus.CONFLICT)
            .body(ProblemDetail.forStatusAndDetail(HttpStatus.CONFLICT,
                    "You already have an appointment in this time slot"));
}

Note: The example above returns 409 for any constraint violation to keep code short. In a real system, you should distinguish by constraint name. Violating uq_active_patient_slot returns 409. Violating idempotency_key uniqueness fetches the appointment by key and returns 200.

5.3. Cancellation Must Also Be Conditional

Cancellation also races. If a patient clicks “Cancel” twice, booked_count can be decremented twice. The fix is the same: only decrement when the state transition actually happens.

@Transactional
public void cancel(long appointmentId) {
    Appointment appt = appointmentRepository.findById(appointmentId).orElseThrow();
    int changed = appointmentRepository.markCancelled(appointmentId); // where status = 'BOOKED'
    if (changed == 1) {
        slotRepository.release(appt.getSlotId());
    }
}

markCancelled is a @Modifying query: update Appointment a set a.status = 'CANCELLED' where a.id = :id and a.status = 'BOOKED'. A second cancel request gets changed = 0 and does nothing extra.

5.4. Testing Under Concurrent Load

The test below puts 20 patients competing for one slot with capacity 1. You should run it with a real PostgreSQL via Testcontainers, since H2 doesn’t support partial indexes.

@SpringBootTest
class BookingServiceConcurrencyTest {

    @Autowired BookingService bookingService;
    @Autowired TimeSlotRepository slotRepository;

    @Test
    void onlyOnePatientGetsSingleCapacitySlot() throws Exception {
        TimeSlot slot = slotRepository.save(
                new TimeSlot(42L, Instant.now().plus(1, ChronoUnit.DAYS), 1));
        int patients = 20;
        var start = new CountDownLatch(1);
        var success = new AtomicInteger();

        try (ExecutorService pool = Executors.newFixedThreadPool(patients)) {
            for (int i = 0; i < patients; i++) {
                long patientId = i;
                pool.submit(() -> {
                    start.await();
                    try {
                        bookingService.book(slot.getId(), patientId, "key-" + patientId);
                        success.incrementAndGet();
                    } catch (SlotFullException ignored) {
                    }
                    return null;
                });
            }
            start.countDown();   // release all 20 threads at once
        }                        // close() waits for all tasks to finish

        assertThat(success.get()).isEqualTo(1);
        assertThat(slotRepository.findById(slot.getId()).orElseThrow().getBookedCount())
                .isEqualTo(1);
    }
}

CountDownLatch holds all threads until they start together, making race conditions more likely. Expected outcome:

success      = 1
booked_count = 1
19 other threads get SlotFullException

If you replace tryReserve with the “read then write” approach from section 5.1, this test usually fails with success greater than 1.

6. Handling Burst Load at Slot Release Time

Conditional update ensures correctness, but not yet performance. 5,000 requests/second all hitting a few rows will create lock queues in the database. The connection pool drains and other APIs slow down. There are three layers of defense.

6.1. Caching Empty Slot Lists

Most traffic is GET slots. You can cache the result by doctorId + date in Redis with a short TTL, say 3–5 seconds. When booking or cancellation succeeds, delete the corresponding key. The list can be slightly stale, but that’s okay: the final decision still comes from tryReserve on the database.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

6.2. Rate Limiting and Early Slot Rejection

@Component
public class BookingGate {
    private final StringRedisTemplate redis;

    public BookingGate(StringRedisTemplate redis) { this.redis = redis; }

    public boolean allowPatient(long patientId) {
        String key = "rl:booking:" + patientId;
        Long count = redis.opsForValue().increment(key);
        if (count != null && count == 1) {
            redis.expire(key, Duration.ofSeconds(10));
        }
        return count != null && count <= 5;          // max 5 booking attempts per 10 seconds
    }

    public boolean isKnownFull(long slotId) {
        return Boolean.TRUE.equals(redis.hasKey("slot:full:" + slotId));
    }

    public void markFull(long slotId) {
        redis.opsForValue().set("slot:full:" + slotId, "1", Duration.ofSeconds(30));
    }
}

The controller calls allowPatient first. If it returns false, the controller returns 429. When tryReserve returns 0, call markFull. Subsequent requests for the same slot are rejected at Redis without touching the database. The 30-second TTL lets the slot “reopen” if someone cancels.

Note: Don’t use Redis as the source of truth for capacity, e.g., DECR a counter and assume booking is done. When Redis and database diverge (Redis restart, transaction rollback), you’ll oversell or lose spots. Redis should only be a pre-filter before the database.

6.3. Asynchronous Notifications via Outbox

Don’t call the SMS service inside the booking transaction. Instead, write an extra row to an outbox_event table in the same transaction. A worker (or Debezium) will push the AppointmentBooked event to Kafka, and the notification service consumes it. The booking transaction thus stays short and doesn’t depend on external services. You also never send SMS for an appointment that rolled back.

7. When to Extend the Design

• Holding a spot during payment: add HELD status with expires_at column. tryReserve still increments booked_count. A periodic job moves expired HELD appointments to EXPIRED and calls release. Never let a held spot languish forever.
• Preventing a patient from booking two doctors at the same time: a unique index on slot_id isn’t enough. Store a tstzrange(start_at, end_at) in appointment, then use PostgreSQL’s exclusion constraint (EXCLUDE USING gist, requires btree_gist extension) on the pair (patient_id, time_range).
• Flexible-duration slots (doctor works 15, 30, or 45 minute sessions): the fixed-slot model no longer fits. You need an exclusion constraint on time range per doctor instead of counting capacity.
• Don’t use SERIALIZABLE isolation for the whole system just to prevent conflicts. It’s correct but causes many serialization failures under high load. Conditional update plus constraints solve this problem at lower cost.
• Don’t use distributed locks (Redisson, Zookeeper) when you only need to lock one row. The database already has locks; each additional component is another point of failure.

8. Conclusion

Designing a medical appointment booking system revolves around one principle: push conflict detection down to the layer with strong consistency, which is the database. Specifically:

• Model each slot as a row with capacity and booked_count.
• Use conditional update to check and write in one atomic step.
• Use CHECK constraints and partial unique indexes as final safeguards.
• Use Idempotency-Key for safe retries.

The layers above it (Redis caching, rate limiting, marking slots full, outbox with Kafka) help the system survive burst load at slot release. These layers don’t affect the correctness of booking. Finally, always have a multi-threaded test like section 5.4 running on a real database. That’s the most reliable way to prove your system never gives two people the same spot.