Database Schema & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• packages/db/prisma/schema.prisma
• packages/db/prisma/migrations/20261006184614_init/migration.sql
• packages/db/prisma/migrations/20261007120000_scheduling/migration.sql
• packages/db/prisma/migrations/20261007150000_appointments/migration.sql
• packages/db/prisma/migrations/20261007210000_consultations/migration.sql
• apps/api/test/tenant-isolation.int.test.ts
• apps/api/src/scripts/seed.ts

The database system manages multi-tenant clinical data, scheduling, and patient records using PostgreSQL and the Prisma ORM. It enforces strict data isolation through Row-Level Security (RLS) and maintains a comprehensive audit trail for all clinical actions.

Sources: packages/db/prisma/schema.prisma:1-12, README.md:28-28

Multi-Tenancy and Security Architecture

The platform uses a shared-database, shared-schema model where every tenant-specific table contains an organisation_id column. Access control is enforced at the database level using PostgreSQL Row-Level Security (RLS).

Row-Level Security (RLS) Implementation

The dhc_app database role executes application queries. A session-level variable, app_current_organisation_id(), restricts the visible rows to those matching the active tenant. Identity tables like users and sessions are managed via a separate system connection.

Core Identity Models

Model	Purpose	Key Fields
Organisation	Represents a clinic or healthcare provider group.	slug, status
User	Platform-level identity for staff and patients.	email, passwordHash, mfaSecret
Membership	Links a User to an Organisation with a specific Role.	role, status
Session	Tracks active login sessions and refresh tokens.	refreshTokenHash, expiresAt

Sources: packages/db/prisma/schema.prisma:98-175

Clinical Data Models

The schema categorizes clinical data into demographics, scheduling, and medical records. All clinical records are designed to be permanent or append-only to satisfy healthcare retention requirements.

Patient and Taxonomy

Patients are scoped to an organisation and identified by a Unique Health ID (UHID). The system supports patient tagging for prioritization (e.g., "Emergency", "VIP").

Sources: packages/db/prisma/schema.prisma:220-270, packages/db/prisma/migrations/20261007040000_patient_register/migration.sql

Scheduling and Appointments

The system manages doctor availability through versions and exceptions. Appointments track the patient's journey from booking to completion.

Medical Records and Encryption

Clinical notes are stored as encrypted JSON strings (notes_cipher) to ensure privacy. Only non-sensitive metadata like follow-up dates remain in plain text.

Sources: packages/db/prisma/schema.prisma:544-555, packages/db/prisma/migrations/20261007210000_consultations/migration.sql

Integrity Constraints and Guard Rails

The database enforces business rules via SQL CHECK constraints to ensure data quality before it reaches the application logic.

Table	Constraint	Rule
consultation_types	duration_check	Duration between 5 and 240 minutes.
vitals	ranges_check	Validates pulse (20-250), SpO2 (50-100), etc.
booking_rules	range_check	Horizon between 1 and 365 days.
appointments	times_check	end_at must be after start_at.
audit_logs	Trigger	Blocks all UPDATE and DELETE operations.

Sources: packages/db/prisma/migrations/20261007120000_scheduling/migration.sql:106-115, packages/db/prisma/migrations/20261007210000_consultations/migration.sql:117-127, apps/api/test/tenant-isolation.int.test.ts:114-118

Database Migration Flow

Migrations are managed via Prisma and applied sequentially to evolve the schema.

1. Init (20261006184614_init): Establishes core RLS functions, auth tables, and audit logs.
2. Patient Register (20261007040000): Adds demographics, UHID settings, and tags.
3. Scheduling (20261007120000): Implements clinics, consultation types, and availability.
4. Appointments (20261007150000): Creates appointment tracking and status history.
5. Consultations (20261007210000): Introduces vitals, allergies, and encrypted consultation notes.

Sources: packages/db/prisma/migrations/

Seeding and Development Data

The seed.ts script populates the database with a "Demo Clinic" and synthetic personas for testing. It initializes default tags, clinic locations, and a sample doctor schedule.

Sources: apps/api/src/scripts/seed.ts:1-150

The database architecture prioritizes tenant isolation and clinical data integrity. By combining Prisma for development productivity and native PostgreSQL RLS for security, the system provides a robust foundation for healthcare operations.