ClinicSphere

1. Context & Problem Statement

In outpatient clinic systems, appointment scheduling experiences intense bursts of concurrency when popular specialists release weekly schedules.

When hundreds of patient requests attempt to reserve the exact same 15-minute appointment slot simultaneously, naive read-then-write logic causes fatal double-booking anomalies: multiple patients receive confirmations for the same physical doctor and time slot.

ClinicSphere was engineered to solve this through rigorous transactional isolation and database-level concurrency control.

2. Requirements & Constraints

• Strict Non-Double Booking: Zero race conditions permitted for slot reservations.
• Predictable Latency: Request completion under 80ms even during slot opening spikes.
• Stateless Horizontal Scalability: Synchronization handled in the database layer rather than single-JVM in-memory mutexes.
• Audit Integrity: Complete lifecycle logging from AVAILABLE to RESERVED, CONFIRMED, and CANCELLED.

3. Concurrency Control: Why Pessimistic Locking?

We evaluated two primary concurrency strategies:

Optimistic Locking (@Version)

Optimistic locking detects collisions upon commit. While effective for low-contention environments, during flash bookings 50 patients hitting 1 slot results in 1 success and 49 OptimisticLockException errors. Retrying those 49 requests leads to database connection exhaustion and high latency.

Pessimistic Row-Level Locking (SELECT FOR UPDATE)

ClinicSphere utilizes database row-level locking directly inside PostgreSQL:

SELECT * FROM appointment_slots 
WHERE id = :slotId 
FOR UPDATE;

sequenceDiagram
    participant Patient1 as Patient 1 (HTTP Request)
    participant Patient2 as Patient 2 (HTTP Request)
    participant DB as Postgres (Row Lock)

    Patient1->>DB: BEGIN TX; SELECT ... FOR UPDATE (Acquires Row Lock)
    Patient2->>DB: BEGIN TX; SELECT ... FOR UPDATE (Blocked, waits)
    Patient1->>DB: Check slot status == AVAILABLE
    Patient1->>DB: UPDATE slot SET status = 'RESERVED'
    Patient1->>DB: INSERT INTO appointments (...)
    Patient1->>DB: COMMIT TX (Releases Row Lock)
    
    DB-->>Patient2: Lock Granted to Patient 2
    Patient2->>Patient2: Check slot status (Now 'RESERVED')
    Patient2->>DB: ROLLBACK TX
    Patient2-->>Patient2: Return 409 Conflict immediately

This ensures deterministic serial execution on disputed slot rows without thread contention on the web application tier.

4. Data Model & Schema Evolution

The data tier is structured across 11 normalized tables managed strictly through Flyway database migrations:

• doctors, doctor_specializations, clinic_branches
• schedules, appointment_slots (composite unique index on doctor_id, start_time)
• patients, appointments, appointment_status_history

5. Security & Authentication

• Secured using Spring Security with stateless JWT tokens.
• Server-side revocation implemented via a Redis-backed token blacklist, ensuring immediate invalidation upon patient or staff logout.
• Method-level authorization enforces role boundaries between doctors, receptionists, and patients.