Patient-Tracker: System Architecture, Database Design & Concurrency Deep Dive

Overview: Comprehensive technical breakdown of the patient-tracker platform. This document explores the end-to-end architecture, the complete Prisma/PostgreSQL schema design, real-time doctor-patient communication with Socket.IO, the PostgreSQL partial unique index concurrency guard, and an analysis of alternative concurrency control patterns.

Table of Contents

1. End-to-End System Architecture
   • High-Level Architecture Diagram
   • Tier Breakdown: Frontend, Backend, Database, Real-Time
   • Authentication & Authorization Model
2. Database Design & Schema Breakdown (Prisma & PostgreSQL)
   • Entity Relationship Diagram (ERD)
   • Model-by-Model Analysis: What, How & Why
   • Relational Modeling & Foreign Key Strategy
   • ID Strategy: Auto-Increment vs. UUID
3. Real-Time Doctor-Patient Communication with Socket.IO
   • What is Socket.IO? (Under the Hood)
   • Socket.IO vs. Raw WebSockets vs. HTTP Polling
   • How Doctor-Patient Chat Works (Step-by-Step Flow)
   • Dual-Write Pattern: REST Persistence + Socket Broadcast
   • Background Telemetry & Medication Alert Broadcasting
4. Concurrency Control: Solving Slot Double-Booking
   • The Problem: TOCTOU Race Condition
   • The Chosen Solution: PostgreSQL Partial Unique Index
   • How the Migration Was Executed (SQL Deep Dive)
   • Application-Level Error Handling (Prisma P2002)
5. Alternative Concurrency Solutions & Trade-off Matrix
   • Solution 1: Pessimistic Row-Level Locking (FOR UPDATE)
   • Solution 2: Optimistic Locking with Versioning (CAS)
   • Solution 3: Distributed Locks via Redis (Redlock / SET NX)
   • Solution 4: Serializable Transaction Isolation
   • Solution 5: Single-Threaded Job Queue / Actor Model
   • Comprehensive Trade-off Comparison Matrix

1. End-to-End System Architecture

High-Level Architecture Diagram

Tier Breakdown

1. Frontend Tier (React 18 + Vite + Tailwind CSS):

   • Single Page Application (SPA) architecture offering responsive views for both Patients and Doctors.
   • Centralized API communication via Axios instances configured with automatic JWT authorization header injection.
   • Real-time client via socket.io-client maintaining a persistent full-duplex socket for live chat rooms and medication notifications.

2. Application Tier (Node.js + Express):

   • Single-threaded, event-loop driven runtime (libuv) optimized for high I/O concurrency.
   • Dual-server architecture mounted on the same native Node HTTP server:
     • Express Application: Handles stateless request-response REST APIs.
     • Socket.IO Server: Manages stateful, persistent TCP/WebSocket connections.
   • Middleware pipeline for authentication (authenticate), CORS, and background scheduling triggers (triggerMedicationReminder, checkForMissedMedications).

3. Persistence Tier (PostgreSQL + Prisma ORM):

   • Relational DBMS providing strict ACID guarantees.
   • Prisma acts as the type-safe Data Access Layer (DAL), generating TypeScript/JavaScript query models, managing database connections, and running automated schema migrations.

Authentication & Authorization Model

• Stateless Tokens: Authentication is handled using JSON Web Tokens (JWT). When a user logs in via /auth/login, the server issues a signed JWT containing id, email, and role ("patient" or "doctor").
• Role-Based Access Control (RBAC): The authenticate middleware decodes the token from Authorization: Bearer <token>, attaching req.user to the request context. Route handlers enforce strict role guards (e.g., only patients can invoke createAppointment, and only doctors can create prescriptions).

2. Database Design & Schema Breakdown (Prisma & PostgreSQL)

Entity Relationship Diagram (ERD)

Model-by-Model Analysis: What, How & Why

1. Patient

• What it is: The central user entity for individuals receiving healthcare services.
• How it is used: Stores core demographic data, authentication credentials, and acts as the parent anchor for all clinical records, appointments, and telemetry.
• Why it is used: Isolates patient identity and privacy. Uses an auto-incrementing integer Int @id @default(autoincrement()) for compact indexing and fast joins across millions of health records.

2. Doctor

• What it is: Represents licensed medical practitioners on the platform.
• How it is used: Contains credentials, specialization, and relationships to assigned patients, issued prescriptions, and booked appointments.
• Why it is used: Uses UUID primary keys (String @id @default(uuid())) to prevent ID enumeration (preventing scrapers from guessing sequential doctor IDs). Maintains an explicit M:N relation with Patient (@relation("DoctorToPatient")) allowing doctors to access medical histories only for their assigned patients.

3. Appointment

• What it is: The temporal booking record binding a Patient, a Doctor, and a specific calendar time slot.
• How it is used: Stores appointmentDate, slotStart (normalized to exact hour boundaries), status ("scheduled", "completed", "cancelled", "missed"), and purpose.
• Why it is used: Serves as the critical scheduling engine. Employs a PostgreSQL Partial Unique Index on (doctorId, slotStart) where status <> 'cancelled' to provide hardware-level concurrency enforcement against double-booking.

4. Prescription & PrescribedMedicine (Parent-Child Pattern)

• What it is: A normalized master-detail relationship representing medical prescriptions.
• How it is used:
  • Prescription stores metadata: consultation date, condition/diagnosis, doctor ID, and patient ID.
  • PrescribedMedicine stores individual line items: dosage, instructions, duration, and a Json column for timing ({ morning: true, afternoon: false, evening: true }).
• Why it is used:
  • Normalizing medicines into a separate table allows querying which patients are taking a specific drug without regex parsing.
  • Using PostgreSQL Json for timing provides flexibility without requiring 3 extra boolean columns in the relational table.

5. Chat & Message

• What it is: The persistence backing for doctor-patient consultations.
• How it is used:
  • Chat defines a conversation channel between one doctor and one patient.
  • Message stores individual messages with senderId, senderType ("doctor" or "patient"), content, and readStatus.
• Why it is used: Decouples conversation threads from individual messages. Enables fast unread-badge calculation (WHERE chatId = ? AND readStatus = false AND senderType != role) and rapid query pagination (ORDER BY createdAt ASC).

6. MedicineAdherence & HealthMetrics

• What it is: Longitudinal telemetry and medication compliance tracking.
• How it is used:
  • MedicineAdherence records daily intake state ("Taken", "Missed", "Pending"), scheduling windows ("morning"), and reminder flags (reminderSent).
  • HealthMetrics records time-series values (e.g., Blood Pressure, Heart Rate, Glucose).
• Why it is used: Enables background cron workers to run daily queries, detect missed doses, update adherence scores, and dispatch automated real-time notifications.

ID Strategy: Auto-Increment vs. UUID

The schema applies a deliberate, mixed primary key strategy:

3. Real-Time Doctor-Patient Communication with Socket.IO

What is Socket.IO? (Under the Hood)

Many developers assume Socket.IO is simply a wrapper around WebSockets. In reality, Socket.IO is a specialized real-time transport and messaging framework built on top of an underlying engine called Engine.IO.

Core Architectural Mechanics:

1. Engine.IO Handshake & Transport Fallback: When a client initiates a connection, Socket.IO does not open a WebSocket immediately. It begins with HTTP Long-Polling (GET /socket.io/?EIO=4&transport=polling). Once the initial handshake succeeds, it probes whether the network/firewall supports the WebSocket protocol. If supported, it upgrades the connection via HTTP status code 101 Switching Protocols.
2. Heartbeats & Liveness Monitoring: Socket.IO continuously exchanges lightweight ping-pong packets (e.g., every 25 seconds). If a client drops offline without cleanly closing the TCP socket (e.g., phone enters airplane mode or loses Wi-Fi), the server automatically detects the timeout and fires the disconnect event.
3. Packet Framing & Buffering: Messages are framed with metadata headers (packet type, namespace, ack ID). If a connection drops temporarily, Socket.IO automatically buffers messages and attempts transparent reconnection.
4. Rooms and Namespaces: Server-side logical partitioning. A "room" is an in-memory channel on the server. Sockets can join or leave rooms dynamically without requiring separate TCP connections.

Socket.IO vs. Raw WebSockets vs. HTTP Polling

How Doctor-Patient Chat Works (Step-by-Step Flow)

Dual-Write Pattern: REST Persistence + Socket Broadcast

In frontend/src/Components/Chat/ChatComponent.jsx, message dispatch uses a Dual-Write Architecture:

Why use this Dual-Write pattern instead of sending messages purely over WebSockets?

1. Reliability & Idempotency: If the WebSocket connection blips or is reconnecting, the REST call ensures the message is safely committed to disk in PostgreSQL.
2. Security & Auditing: The REST controller (backend/chat/messageController.js) verifies JWT claims against the database, ensuring a malicious user cannot emit messages into rooms they don't belong to:
   const isPatient = decoded.role === 'patient' && chat.patientId === parseInt(decoded.id); const isDoctor = decoded.role === 'doctor' && chat.doctorId === decoded.id; if (!isPatient && !isDoctor) { return res.status(403).json({ error: 'Forbidden' }); }
3. Server-Side Room Dispatch: The Socket server (backend/chat/chatSocket.js) simply broadcasts the verified payload to all sockets joined to that room:
   socket.on("send-message", (messageData) => { io.to(messageData.chatId).emit("receive-message", messageData); });

Background Telemetry & Medication Alert Broadcasting

Socket.IO is also integrated with the platform's automated healthcare monitors.

In backend/app.js:

When scheduled tasks in backend/scheduleTasks.js or middleware detect that a patient missed a dose or that new adherence telemetry has been logged, the server accesses global.io to emit:

The patient's dashboard (frontend/src/Pages/PatientDashboard.jsx) listens for medications-updated and dynamically refreshes the medication checklist without requiring a page reload.

4. Concurrency Control: Solving Slot Double-Booking

The Problem: TOCTOU Race Condition

Consider Dr. Sharma who has an open slot at 10:00 AM on Monday. Two patients, Patient A and Patient B, open the booking page simultaneously.

This is a classic Time-of-Check to Time-of-Use (TOCTOU) race condition:

• The check (findFirst) and the action (create) are two separate, non-atomic database operations.
• Pure application-level checks (if (!conflictingAppointment)) are completely blind to concurrent requests executing on other Node.js event-loop ticks or horizontal server instances.

The Chosen Solution: PostgreSQL Partial Unique Index

Instead of adding heavy locking infrastructure, the system utilizes PostgreSQL's native write-ahead logging and B+ Tree uniqueness guarantees via a Partial Unique Index.

The Core Concept

A standard unique index enforces uniqueness across all rows in a table. A Partial Unique Index enforces uniqueness only on a subset of rows that satisfy a specific SQL WHERE predicate.

Why is this design brilliant?

1. Hardware-Enforced Atomicity: PostgreSQL's storage engine locks the index leaf node during insertion. Two concurrent transactions can never both insert active bookings for the same doctor and slot. Exactly one succeeds; the second is aborted by PostgreSQL with a unique constraint violation error.
2. Slot Re-bookability without Data Loss: In healthcare systems, you cannot delete records when an appointment is cancelled (audit trail and legal compliance require retaining cancelled rows). If we had used a normal unique index on (doctorId, slotStart), cancelling an appointment would permanently block that slot forever! The WHERE status <> 'cancelled' clause leaves cancelled rows in the table while freeing the slot in the index for new bookings.
3. Zero Runtime Overhead: No external cache checks, no distributed lock managers, and no database table locks.

How the Migration Was Executed (SQL Deep Dive)

In backend/prisma/migrations/20250806120000_add_appointment_slot_unique_constraint/migration.sql:

Application-Level Error Handling (Prisma P2002)

In backend/appointment/createAppointment.js:

• If two concurrent requests pass findFirst, the first insert wins.
• The losing insert fails at the PostgreSQL index level. Prisma intercepts the PostgreSQL constraint violation (SQLSTATE 23505) and exposes it as code P2002.
• The controller catches P2002 and returns a clean, user-friendly HTTP 409 Conflict, completely preventing data corruption.

5. Alternative Concurrency Solutions & Trade-off Matrix

While the Partial Unique Index is the optimal solution for this architecture, interviewers (especially at Juspay) love testing your ability to evaluate alternative concurrency control patterns.

Solution 1: Pessimistic Row-Level Locking (FOR UPDATE)

How it works:

Before inserting or modifying, lock the resource explicitly at the database level inside an active transaction:

Why it was not chosen here:

• The Phantom Read Dilemma: If no row exists yet for that slot, SELECT ... FOR UPDATE has no row to lock! Under standard Read Committed isolation, two concurrent transactions will both see 0 rows and both proceed to insert (unless you use Predicate Locking or Gap Locking, which are expensive and complex).
• Throughput Bottleneck: Acquiring database row/table locks blocks concurrent worker threads, increasing latency and connection pool pressure.

Solution 2: Optimistic Locking with Versioning (CAS)

How it works:

Treat the doctor's calendar schedule as an explicit entity with a version integer:

If rows_affected == 0, another transaction updated the schedule first; abort and retry.

Why it was not chosen here:

• Requires maintaining an artificial DoctorSchedule or slot inventory table with pre-allocated slots for every doctor and hour of the year.
• High abort-and-retry rate under peak traffic (e.g., popular doctor slot opening).

Solution 3: Distributed Locks via Redis (Redlock / SET NX)

How it works:

Acquire an exclusive lock across application servers using an in-memory Redis cluster before touching the database:

Trade-offs:

• Pros: Fast in-memory acquisition; prevents requests from even hitting the relational database.
• Cons: Introduces an external infrastructure dependency (Redis). If the Redis cluster crashes or experiences clock drift/network partitions, locks can be prematurely released, leading to race conditions. Overkill when PostgreSQL can enforce it natively.

Solution 4: Serializable Transaction Isolation

How it works:

Run the booking query inside a transaction with isolation set to SERIALIZABLE:

Trade-offs:

• Pros: Pure application logic without custom indexes; mathematically guarantees serial execution.
• Cons: PostgreSQL enforces Serializable isolation using SSI (Serializable Snapshot Isolation) which tracks read/write dependency locks (SIREAD locks). Under concurrent bookings, PostgreSQL will frequently throw serialization failure errors (40001: could not serialize access), requiring complex retry logic and wasting CPU cycles.

Solution 5: Single-Threaded Job Queue / Actor Model

How it works:

Enqueue all booking requests into a message queue (e.g., BullMQ / RabbitMQ) partitioned by doctorId. A single worker process handles bookings for a given doctor sequentially.

Trade-offs:

• Pros: Completely eliminates database race conditions by eliminating concurrency altogether at the execution layer.
• Cons: Converts asynchronous HTTP requests into asynchronous polling jobs (the client must wait or poll for job completion). Dramatically increases architectural complexity.

Comprehensive Trade-off Comparison Matrix

6. Key Takeaways for Senior Engineering Interviews

1. Architecture is defined by its boundaries: Synchronous REST is for the critical path where the client requires immediate consistency; asynchronous queues and WebSockets are for decoupled side-effects, real-time broadcasts, and background telemetry.
2. Socket.IO is not just WebSockets: It is an entire real-time orchestration system with Engine.IO HTTP handshakes, automatic transport upgrades, heartbeat timeout detection, transparent reconnection, and in-memory room multiplexing.
3. Always solve concurrency at the lowest viable layer: Application-level checks are vulnerable to TOCTOU races. Solving slot double-booking at the database engine level using a Partial Unique Index provides bulletproof concurrency guarantees with zero additional network hops or infrastructure dependencies.
4. Partial indexes provide business agility: By scoping the unique index with WHERE status <> 'cancelled', the business retains historical and audit records of cancelled appointments while immediately restoring slot availability to other patients.