Database Design — Deep Dive

Schema Overview

PostgreSQL (hosted on Neon), accessed via Prisma ORM with Accelerate connection pooling. 10 models, 17 migrations, 1 implicit M:N join table.

ER Diagram

┌──────────────────────┐         ┌──────────────────────┐
│       Doctor         │         │       Patient        │
│──────────────────────│         │──────────────────────│
│ id       String (PK) │◄──────┐ │ id       Int (PK)    │
│          UUID        │       │ │          autoincrement│
│ name     String      │       │ │ name     String       │
│ email    String (UQ) │       │ │ email    String (UQ)  │
│ special  String      │       │ │ password String?      │
│ password String      │       │ │ age      Int?         │
│                      │       │ │ gender   String?      │
└──────┬───┬───┬───┬───┘       │ │ status   String      │
       │   │   │   │           │ │          "pending"|   │
       │   │   │   │           │ │          "active"     │
       │   │   │   │           │ └──┬───┬───┬───┬───┬───┘
       │   │   │   │           │    │   │   │   │   │
       │   │   │   │    ┌──────┴────┘   │   │   │   │
       │   │   │   │    │ _DoctorToPatient  │   │   │
       │   │   │   │    │ (implicit M:N)    │   │   │
       │   │   │   │    └───────────────┘   │   │   │
       │   │   │   │                        │   │   │
       │   │   │   └────────────┐           │   │   │
       │   │   │                │           │   │   │
       │   │   │  ┌─────────────┴───────────┴─┐ │   │
       │   │   │  │     Prescription          │ │   │
       │   │   │  │───────────────────────────│ │   │
       │   │   │  │ id         String (PK,UUID│ │   │
       │   │   │  │ date       DateTime       │ │   │
       │   │   │  │ patientId  Int (FK)       │ │   │
       │   │   │  │ doctorId   String (FK)    │ │   │
       │   │   │  │ condition  String         │ │   │
       │   │   │  │ createdAt  DateTime       │ │   │
       │   │   │  │ updatedAt  DateTime       │ │   │
       │   │   │  └────────────┬──────────────┘ │   │
       │   │   │               │                │   │
       │   │   │    ┌──────────┴──────────┐     │   │
       │   │   │    │ PrescribedMedicine  │     │   │
       │   │   │    │─────────────────────│     │   │
       │   │   │    │ id            String│     │   │
       │   │   │    │ medicineId    String│     │   │
       │   │   │    │ medicineName  String│     │   │
       │   │   │    │ dosage        String│     │   │
       │   │   │    │ timing        Json  │     │   │
       │   │   │    │ instructions  String│     │   │
       │   │   │    │ duration      String│     │   │
       │   │   │    │ prescriptionId FK   │     │   │
       │   │   │    └─────────────────────┘     │   │
       │   │   │                                │   │
       │   │   └───────────────┐                │   │
       │   │                   │                │   │
       │   │    ┌──────────────┴────────────────┴─┐ │
       │   │    │     Appointment                 │ │
       │   │    │─────────────────────────────────│ │
       │   │    │ id              String (PK,UUID)│ │
       │   │    │ patientId       Int (FK)        │ │
       │   │    │ doctorId        String (FK)     │ │
       │   │    │ appointmentDate DateTime        │ │
       │   │    │ slotStart       DateTime        │ │
       │   │    │ status          String          │ │
       │   │    │ purpose         String?         │ │
       │   │    │ createdAt       DateTime        │ │
       │   │    │ updatedAt       DateTime        │ │
       │   │    │                                 │ │
       │   │    │ PARTIAL UNIQUE INDEX:           │ │
       │   │    │ (doctorId, slotStart)           │ │
       │   │    │ WHERE status <> 'cancelled'     │ │
       │   │    └─────────────────────────────────┘ │
       │   │                                        │
       │   └────────────────────┐                   │
       │                        │                   │
       │     ┌──────────────────┴───────────────────┴─┐
       │     │     Chat                               │
       │     │────────────────────────────────────────│
       │     │ id        String (PK, UUID)            │
       │     │ patientId Int (FK)                     │
       │     │ doctorId  String (FK)                  │
       │     │ createdAt DateTime                     │
       │     │ updatedAt DateTime                     │
       │     └────────────────┬───────────────────────┘
       │                      │
       │           ┌──────────┴──────────┐
       │           │     Message         │
       │           │────────────────────│
       │           │ id         String  │
       │           │ content    String  │
       │           │ chatId     FK      │
       │           │ senderId   String  │
       │           │ senderType String  │
       │           │ readStatus Boolean │
       │           │ createdAt  DateTime│
       │           └────────────────────┘
       │
  ┌────┴─────────────────────────────────┐
  │  MedicineAdherence                   │
  │──────────────────────────────────────│
  │ id              String (PK, UUID)    │
  │ patientId       Int (FK)             │
  │ medication      String               │
  │ adherenceStatus String               │
  │ missedDoses     Int                  │
  │ reminderSent    Boolean              │
  │ prescriptionId  String? (no FK)      │
  │ medicineId      String? (no FK)      │
  │ scheduledTime   String?              │
  │ scheduledDate   String?              │
  │ createdAt       DateTime             │
  │ updatedAt       DateTime             │
  └──────────────────────────────────────┘

  ┌──────────────────────────────┐    ┌───────────────────────┐
  │     MedicalRecord            │    │   HealthMetrics       │
  │──────────────────────────────│    │───────────────────────│
  │ id        String (PK, UUID)  │    │ id         String(UUID│
  │ patientId Int (FK)           │    │ patientId  Int (FK)   │
  │ diagnosis String             │    │ metricName String     │
  │ medication String            │    │ metricValue Float     │
  │ createdAt DateTime           │    │ recordedAt DateTime   │
  └──────────────────────────────┘    └───────────────────────┘

Every Table Explained

Patient

Field	Type	Purpose
`id`	`Int @id @default(autoincrement())`	Primary key. Integer because it was defined first in early migrations.
`name`	`String`	Display name.
`email`	`String @unique`	Login identifier. Unique constraint prevents duplicate accounts.
`password`	`String?`	Nullable — `null` when patient is invited but hasn't set password yet.
`age`	`Int?`	Optional demographic data.
`gender`	`String?`	Optional. Stored as free-text (no enum).
`status`	`String @default("pending")`	`"pending"` = invited but no password; `"active"` = can log in. Controls login flow.

Why password is nullable: The invite flow creates a patient without a password. The patient sets it later via the /auth/set-password route. This two-phase creation avoids generating temporary passwords.

Doctor

Field	Type	Purpose
`id`	`String @id @default(uuid())`	UUID primary key. Different from Patient's integer — this asymmetry causes type coercion throughout.
`name`	`String`	Display name.
`email`	`String @unique`	Login identifier.
`specialization`	`String`	e.g., "Cardiology", "General Medicine". Used in prescription display and appointment info.
`password`	`String`	Required (not nullable) — doctors always register with a password.

Why Patient.id is Int and Doctor.id is UUID: Early design used autoincrement() for patients (simpler). Doctors were added later with UUIDs (more standard). This inconsistency propagates everywhere — parseInt(patientId) vs String(doctorId).

_DoctorToPatient (Implicit Join Table)

Prisma creates this automatically from:

model Patient {
  doctors Doctor[] @relation("DoctorToPatient")
}
model Doctor {
  patients Patient[] @relation("DoctorToPatient")
}

This is a many-to-many: one doctor can have many patients, one patient can have many doctors. The join table has columns A (Doctor.id) and B (Patient.id).

Prescription

Field	Type	Purpose
`id`	`String @default(uuid()) @map("_id")`	UUID. `@map("_id")` maps to `_id` column — leftover from a MongoDB-to-PostgreSQL migration.
`date`	`DateTime @default(now())`	When the prescription was issued. Used for duration calculations.
`patientId`	`Int`	FK to Patient.
`doctorId`	`String`	FK to Doctor.
`condition`	`String @default("General")`	What the prescription is for (e.g., "Hypertension").
`createdAt` / `updatedAt`	`DateTime`	Audit timestamps.

PrescribedMedicine

Field	Type	Purpose
`id`	`String @default(uuid()) @map("_id")`	UUID with MongoDB-era column mapping.
`medicineId`	`String`	References the CSV dataset's `id` field (not a DB FK — CSV isn't in the DB).
`medicineName`	`String`	Denormalized medicine name. Stored here so prescriptions work even if CSV changes.
`dosage`	`String`	Free-text (e.g., "5mg", "10ml").
`timing`	`Json`	`{ morning: true, afternoon: false, evening: true }` — when to take this medicine.
`instructions`	`String`	Free-text (e.g., "After food", "With water").
`duration`	`String`	Free-text (e.g., "7 days", "2 weeks", "1 month"). Parsed by `isStillActive()`.
`prescriptionId`	`String`	FK to Prescription. Cascade deletes handled manually in `deletePrescription.js`.

Why timing is JSON: Allows flexible time-of-day assignment without separate boolean columns. Trade-off: can't query "find all morning medicines" efficiently at the DB level.

MedicineAdherence

Field	Type	Purpose
`id`	`String @default(uuid())`	UUID.
`patientId`	`Int`	FK to Patient.
`medication`	`String`	Denormalized medicine name (stored at creation time).
`adherenceStatus`	`String`	`"Taken"`, `"Missed"`, or `"Pending"`. Central state machine.
`missedDoses`	`Int`	Counter incremented each time a dose is marked as missed.
`reminderSent`	`Boolean @default(false)`	Whether an email reminder was sent for this specific entry.
`prescriptionId`	`String?`	Not a Prisma FK — just a stored reference. Nullable for legacy entries.
`medicineId`	`String?`	Not a Prisma FK. References PrescribedMedicine.id. Used for dedup.
`scheduledTime`	`String?`	`"morning"`, `"afternoon"`, or `"evening"`.
`scheduledDate`	`String?`	`"YYYY-MM-DD"` format. Stored as String, not DateTime — queried via exact string match.

Why scheduledDate is a String: Added in migration 20250311093124_add_schedule_fields as a String for simplicity. Querying WHERE scheduledDate = '2025-08-12' works for exact-day lookups but prevents range queries, timezone handling, or date arithmetic at the DB level.

Appointment

Field	Type	Purpose
`id`	`String @default(uuid())`	UUID.
`patientId`	`Int`	FK to Patient.
`doctorId`	`String`	FK to Doctor.
`appointmentDate`	`DateTime`	Original appointment time (normalized to slot start after migration).
`slotStart`	`DateTime`	Hour-boundary normalized time — minutes/seconds/ms zeroed. Added in migration `20250806120000`.
`status`	`String`	`"scheduled"`, `"completed"`, `"cancelled"`, `"missed"`. No enum — free-text string validated in code.
`purpose`	`String?`	Optional description (e.g., "General checkup").

The partial unique index:

CREATE UNIQUE INDEX "Appointment_doctorId_slotStart_active_key"
ON "Appointment" ("doctorId", "slotStart")
WHERE status <> 'cancelled'

This is the core concurrency guard. It lives outside Prisma's schema (raw SQL migration) because Prisma doesn't support partial unique indexes declaratively.

Chat & Message

Standard 1:N chat model. Chat between one doctor and one patient, contains many messages.

Chat Field	Purpose
`patientId` + `doctorId`	Uniqueness isn't enforced at DB level — handled in code via `findFirst` before create

Message Field	Purpose
`senderId`	String — can be doctor UUID or patient integer (cast to string)
`senderType`	`"doctor"` or `"patient"` — because `senderId` alone is ambiguous
`readStatus`	`Boolean @default(false)` — flipped when the other party fetches messages

MedicalRecord & HealthMetrics

Both exist in the schema but are not used in any route handler. They were defined as future expansion points.

Indexes

Index	Table	Fields	Type	Why
Primary key	All tables	`id`	B-tree (auto)	Standard PK lookup
Unique	Patient	`email`	B-tree unique	Prevent duplicate patient accounts, enable `findUnique`
Unique	Doctor	`email`	B-tree unique	Prevent duplicate doctor accounts
Partial unique	Appointment	`(doctorId, slotStart)` WHERE status ≠ 'cancelled'	B-tree partial	Concurrency guard — prevents double-booking
Implicit FK indexes	All FK columns	Foreign keys	B-tree	Prisma auto-creates for relations

Missing indexes that would help at scale:

MedicineAdherence(patientId, scheduledDate) — hot query for daily medication list
Appointment(doctorId, appointmentDate) — hot query for doctor's daily schedule
Chat(patientId, doctorId) — uniqueness check on chat creation
Message(chatId, createdAt) — fetching ordered messages

Hot Query Paths

Query	Frequency	Tables Hit	Current Efficiency
Get today's medications for patient	Every patient page load	MedicineAdherence + Prescription + PrescribedMedicine	3 queries minimum — no composite index
Get available appointment slots	Every appointment booking attempt	Appointment	Single query, filtered by date range
Get doctor's patients	Every doctor page load	Patient (through implicit join)	Single query with M:N join
Send medication reminders	3x daily per patient	Patient + Prescription + PrescribedMedicine + MedicineAdherence	4 queries per patient — N+1 pattern across all patients
Missed medication check	Every minute (cron)	MedicineAdherence	Single query, filtered by date + status

Data Growth Patterns

Table	Growth Rate	Size Concern
Patient / Doctor	Slow (user registrations)	Negligible
Prescription	~1-5 per patient per visit	Moderate — grows with patient count
PrescribedMedicine	1-10 per prescription	Moderate
MedicineAdherence	3 records per medicine per day per patient	Fastest growing — at 10 medicines × 3 times/day = 30 rows/patient/day = ~11,000 rows/patient/year
Appointment	1-4 per patient per month	Slow
Message	Variable — depends on chat activity	Potentially large with active users

The MedicineAdherence table is the ticking time bomb. At 1,000 patients with 5 medicines each: 15,000 rows/day = 5.5M rows/year. Without partitioning or archival, queries on this table will degrade.

Data Integrity

Mechanism	Where	What It Prevents
`@unique` on email	Patient, Doctor	Duplicate accounts
Partial unique index	Appointment	Double-booking (even under concurrency)
Prisma `$transaction`	`deletePrescription.js`	Orphaned records on cascading delete
`bcrypt.hash`	signup, setPassword, resetPassword	Plain-text password storage
Role checks in handlers	Every protected route	Unauthorized access (doctor-only, patient-only)
Existence checks	Most handlers	Operating on non-existent records

What's NOT prevented:

No unique constraint on Chat(patientId, doctorId) — relies on application-level findFirst check
No foreign key on MedicineAdherence.prescriptionId — it's a plain String, not a Prisma relation
No enum types — status fields accept any string value (validated in application code only)
No ON DELETE CASCADE in Prisma relations — cascading deletes handled manually in code

Schema Migration History

Migration	What Changed
`20250301055021_init`	Initial Patient + MedicalRecord
`20250305_*` (4 migrations)	Doctor model iterations — added fields, fixed types
`20250307_doctor_patient`	M:N relationship between Doctor and Patient
`20250310_add_patient`	Patient invite flow fields
`20250310_add_patient_status`	`status` field ("pending"/"active")
`20250311_prescription`	Prescription + PrescribedMedicine models
`20250311_add_schedule_fields`	MedicineAdherence: scheduledTime, scheduledDate
`20250412_status`	Appointment model with status
`20250413_newchat`	Chat + Message models
`20250414_*` (2 migrations)	Added/tweaked `condition` field on Prescription
`20250806_slot_unique`	slotStart column + partial unique index for concurrency

03-database-design.md