Skip to main content

Database Schema

This document provides the database schema design for iHospita HMS using Prisma ORM.

Schema Overview​

The database uses PostgreSQL with the following main entity groups:

  • Hospital & Organization - Tenant and clinic management
  • Users & Authentication - Staff accounts and roles
  • Patients & Families - Patient records and family memberships
  • Clinical - Consultations, prescriptions, lab orders
  • Financial - Invoices, payments, reconciliation
  • Queue - Appointments and queue entries

Core Entities​

Hospital​

model Hospital {
  id        String   @id @default(uuid())
  name      String
  prefix    String   @unique
  address   String?
  phone     String?
  email     String?
  theme     Json?
  isActive  Boolean  @default(true)

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  clinics   Clinic[]
  users     User[]
  patients  Patient[]

  @@map("hospitals")
}

Patient​

model Patient {
  id          String   @id @default(uuid())
  hospitalId  String   @map("hospital_id")
  patientNo   String   @map("patient_no")
  firstName   String   @map("first_name")
  lastName    String   @map("last_name")
  phone       String
  email       String?
  dateOfBirth DateTime @map("date_of_birth")
  gender      Gender
  address     String?

  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  hospital    Hospital @relation(fields: [hospitalId], references: [id])

  @@unique([hospitalId, patientNo])
  @@map("patients")
}

Invoice​

model Invoice {
  id          String        @id @default(uuid())
  hospitalId  String        @map("hospital_id")
  patientId   String        @map("patient_id")
  invoiceNo   String        @map("invoice_no")
  subtotal    Decimal       @db.Decimal(12, 2)
  discount    Decimal       @db.Decimal(12, 2)
  tax         Decimal       @db.Decimal(12, 2)
  total       Decimal       @db.Decimal(12, 2)
  status      InvoiceStatus @default(DRAFT)

  createdAt   DateTime      @default(now()) @map("created_at")
  updatedAt   DateTime      @updatedAt @map("updated_at")

  items       InvoiceItem[]
  payments    Payment[]

  @@unique([hospitalId, invoiceNo])
  @@map("invoices")
}

Enums​

enum Gender {
  MALE
  FEMALE
  OTHER
}

enum InvoiceStatus {
  DRAFT
  UNPAID
  PARTIAL
  PAID
  CANCELLED
}

enum PaymentMethod {
  CASH
  CARD
  BANK_TRANSFER
}

enum VipTier {
  BRONZE
  SILVER
  GOLD
  PLATINUM
}

Indexes​

All tables include indexes for:

  • hospitalId - Tenant isolation
  • Foreign key columns
  • Frequently queried fields