Database Schema & Relationships

A complete reference for CareNova's database — all tables organized by domain, column definitions, and the relationships between them.

Written By Dev010

Last updated 19 days ago

CareNova's entire database is defined in a single file — lib/db/schema.ts. This is the source of truth for every table, column, type, and relationship in the system. The master SQL installation file INSTALL.sql is generated from this schema and can be run directly in Supabase to create everything from scratch.

Entity Relationship Overview

The core of the data model revolves around four central entities. Everything else connects to one or more of them.

clinics
  └── landing_page_settings

departments
  ├── users (department_id)
  ├── patients (department_id)
  ├── services (department_id)
  ├── staff (department_id)
  ├── expenses (department_id)
  └── test_categories (department_id)

users (staff accounts / login identities)
  ├── patients (primary_doctor_id)
  ├── appointments (doctor_id)
  ├── medical_records (doctor_id)
  ├── clinical_notes (author_id)
  ├── diagnoses (doctor_id)
  ├── medical_record_vitals (recorded_by_id)
  ├── prescriptions (doctor_id)
  ├── test_reports (doctor_id)
  ├── invoices (doctor_id)
  ├── expenses (submitted_by)
  ├── odontograms (doctor_id)
  ├── blog_posts (author_id)
  ├── notifications (user_id)
  └── payroll (staff_id)

patients
  ├── appointments (patient_id)
  ├── medical_records (patient_id)
  ├── medical_record_vitals (patient_id)
  ├── clinical_notes (patient_id)
  ├── diagnoses (patient_id)
  ├── medical_attachments (patient_id)
  ├── prescriptions (patient_id)
  ├── test_reports (patient_id)
  ├── invoices (patient_id)
  ├── payments (patient_id)
  └── odontograms (patient_id)

Enum Types

CareNova defines five PostgreSQL enums:

Enum	Values
`user_role`	admin, doctor, receptionist, nurse
`appointment_status`	pending, confirmed, completed, cancelled
`invoice_status`	paid, unpaid, cancelled
`clinic_type`	general, dental, ophthalmology
`test_report_status`	verified, pending, recorded, delivered
`diagnosis_status`	active, resolved

Core Tables

clinics

Stores the clinic's identity and branding configuration. One row per installation — created automatically by INSTALL.sql.

Column	Type	Notes
`id`	uuid PK
`name`	text	Default: 'Dental Clinic'
`type`	clinic_type	general / dental / ophthalmology
`hero_tagline`	text
`hero_subtitle`	text
`logo_url`	text
`logo_dark_url`	text
`favicon_url`	text
`primary_color`	text	Hex value
`accent_color`	text	Hex value
`contact_email`	text
`contact_phone`	text
`contact_address`	text
`social_facebook`	text
`social_instagram`	text
`social_linkedin`	text
`social_twitter`	text
`social_youtube`	text
`created_at`	timestamptz
`updated_at`	timestamptz

users

Represents all staff accounts. The id column matches the auth.users.id from Supabase Auth — this is how the authenticated session maps to an application user record.

Column	Type	Notes
`id`	uuid PK	= auth.users.id
`full_name`	text
`email`	text UNIQUE
`role`	user_role	admin / doctor / receptionist / nurse
`department_id`	uuid FK	→ departments, SET NULL
`phone`	text
`specialization`	text
`hire_date`	date
`approved_at`	timestamptz	null = pending approval
`created_at`	timestamptz
`updated_at`	timestamptz

approved_at being null means the user has confirmed their email but has not yet been approved by an admin. The middleware blocks dashboard access until this is set.

departments

Organizational units within the clinic. Used to group staff, patients, services, and expenses.

Column	Type	Notes
`id`	uuid PK
`name`	text UNIQUE
`description`	text
`head_id`	uuid FK	→ users, SET NULL
`head_of_department`	text	Free text alternative
`code`	varchar(20)
`location`	text
`phone`	varchar(50)
`email`	varchar(255)
`budget`	numeric(12,2)
`annual_budget`	numeric(12,2)
`is_active`	integer	1 = active
`status`	text	Default: 'active'
`created_at`	timestamptz
`updated_at`	timestamptz

patients

Central patient record. The most referenced table in the system — almost every clinical and financial table has a patient_id foreign key.

Column	Type	Notes
`id`	uuid PK
`full_name`	text	Indexed
`date_of_birth`	date
`phone`	text	Indexed
`email`	text
`gender`	text
`blood_group`	text
`height`	text
`weight`	text
`address`	text
`medical_history`	text
`allergies`	text
`emergency_contact_name`	text
`emergency_contact_phone`	text
`emergency_contact_relation`	text
`primary_doctor_id`	uuid FK	→ users, SET NULL
`department_id`	uuid FK	→ departments, SET NULL
`created_at`	timestamptz
`updated_at`	timestamptz

Scheduling

appointments

Links a patient to a doctor and service for a specific time slot.

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`doctor_id`	uuid FK	→ users
`service_id`	uuid FK	→ services, SET NULL
`start_time`	timestamptz	Indexed
`end_time`	timestamptz
`status`	appointment_status	pending / confirmed / completed / cancelled
`notes`	text
`reminder_sent`	integer	0 = not sent
`reminder_sent_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

services

Defines the treatments and services the clinic offers.

Column	Type	Notes
`id`	uuid PK
`name`	text	Indexed
`description`	text
`price`	numeric(12,2)
`duration`	integer	Minutes, default 30
`department_id`	uuid FK	→ departments, SET NULL
`is_active`	integer	1 = active
`status`	text
`category`	varchar(100)
`max_bookings_per_day`	integer	Default 20
`follow_up_required`	boolean
`prerequisites`	text
`special_instructions`	text
`created_at`	timestamptz
`updated_at`	timestamptz

Clinical Records

medical_records

A visit record that groups clinical data for one patient encounter. Acts as a parent for vitals, notes, diagnoses, and attachments.

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`doctor_id`	uuid FK	→ users, SET NULL
`appointment_id`	uuid FK	→ appointments, SET NULL
`visit_date`	timestamptz
`status`	text	Default: 'open'
`created_at`	timestamptz
`updated_at`	timestamptz

medical_record_vitals

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`appointment_id`	uuid FK	→ appointments, SET NULL
`recorded_at`	timestamptz
`recorded_by_id`	uuid FK	→ users, SET NULL
`blood_pressure_systolic`	integer
`blood_pressure_diastolic`	integer
`heart_rate`	integer
`temperature`	numeric(4,1)
`weight`	numeric(5,2)
`height`	numeric(5,2)
`bmi`	numeric(4,2)	Auto-calculated
`created_at`	timestamptz
`updated_at`	timestamptz

clinical_notes

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`author_id`	uuid FK	→ users
`appointment_id`	uuid FK	→ appointments, SET NULL
`content`	text
`created_at`	timestamptz
`updated_at`	timestamptz

diagnoses

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`doctor_id`	uuid FK	→ users, SET NULL
`title`	text
`icd_code`	text
`status`	diagnosis_status	active / resolved
`diagnosed_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

medical_attachments

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`appointment_id`	uuid FK	→ appointments, SET NULL
`file_name`	text
`file_url`	text	Supabase Storage URL
`file_type`	text	MIME type
`created_at`	timestamptz

prescriptions

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`doctor_id`	uuid FK	→ users
`appointment_id`	uuid FK	→ appointments, SET NULL
`medication`	text
`dosage`	text
`inventory_item_id`	uuid FK	→ inventory, SET NULL
`instructions`	text
`frequency`	text
`duration`	text
`drug_interactions`	text
`pharmacy_name`	text
`pharmacy_address`	text
`issued_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

odontograms

Dental charting — dental clinic type only.

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`doctor_id`	uuid FK	→ users, SET NULL
`status`	text	Default: 'active'
`version`	integer	Increments per new examination
`examined_at`	timestamptz
`tooth_data`	jsonb	Array of tooth condition objects
`diagnosis`	text
`notes`	text
`treatments`	jsonb	Array of treatment objects
`created_at`	timestamptz
`updated_at`	timestamptz

Laboratory

lab_vendors

External laboratory suppliers.

Column	Type	Notes
`id`	uuid PK
`name`	text
`code`	text
`lab_type`	text
`contact_person`	text
`email`	text
`phone`	text
`rating`	numeric(2,1)	0.0 – 5.0
`tier`	text	Primary / Secondary / etc.
`contract_start_date`	date
`contract_end_date`	date
`is_active`	integer
`status`	text
`created_at`	timestamptz
`updated_at`	timestamptz

test_categories → test_methodologies → laboratory_tests

Lab tests follow a hierarchy:

test_categories
  ├── test_methodologies (category_id)
  ├── turnaround_times (category_id)
  ├── sample_types (category_id)
  └── laboratory_tests (category_id)
        ├── sample_type_id → sample_types
        ├── methodology_id → test_methodologies
        └── turnaround_time_id → turnaround_times

test_reports

Patient-specific lab results.

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`doctor_id`	uuid FK	→ users
`appointment_id`	uuid FK	→ appointments, SET NULL
`lab_vendor_id`	uuid FK	→ lab_vendors, SET NULL
`test_id`	uuid FK	→ laboratory_tests, SET NULL
`test_type`	text
`results`	text
`reference_values`	text
`clinical_interpretation`	text
`abnormal_findings`	text
`recommendations`	text
`report_date`	date
`status`	text	pending / recorded / verified / delivered
`attachments`	jsonb	Array of file URLs
`created_at`	timestamptz
`updated_at`	timestamptz

Financial

invoices

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, SET NULL
`appointment_id`	uuid FK	→ appointments, SET NULL UNIQUE
`doctor_id`	uuid FK	→ users, SET NULL
`service_id`	uuid FK	→ services, SET NULL
`invoice_number`	text UNIQUE	Auto-generated
`total_amount`	numeric(12,2)
`status`	invoice_status	paid / unpaid / cancelled
`discount`	numeric(12,2)	Flat deduction
`tax_percent`	numeric(5,2)	Applied post-discount
`insurance_provider`	text
`insurance_policy_number`	text
`payment_method`	text
`paid_at`	timestamptz
`issued_at`	timestamptz
`due_at`	timestamptz
`created_at`	timestamptz

The UNIQUE constraint on appointment_id enforces one invoice per appointment.

invoice_items

Line items belonging to an invoice.

Column	Type	Notes
`id`	uuid PK
`invoice_id`	uuid FK	→ invoices, CASCADE
`description`	text
`item_type`	text
`quantity`	integer
`unit_price`	numeric(12,2)
`created_at`	timestamptz

payments

Column	Type	Notes
`id`	uuid PK
`patient_id`	uuid FK	→ patients, CASCADE
`invoice_id`	uuid FK	→ invoices, SET NULL
`amount`	numeric(12,2)
`payment_method`	text
`transaction_id`	text
`description`	text
`status`	text	completed / pending / failed / refunded
`created_at`	timestamptz
`updated_at`	timestamptz

expenses

Column	Type	Notes
`id`	uuid PK
`title`	text
`amount`	numeric(12,2)
`category`	text
`payment_method`	text
`status`	text	pending / approved / paid / rejected / cancelled
`date`	timestamptz
`vendor`	text	Free text
`receipt_url`	text
`department_id`	uuid FK	→ departments, SET NULL
`submitted_by`	uuid FK	→ users, SET NULL
`vendor_id`	uuid FK	→ lab_vendors, SET NULL
`inventory_item_id`	uuid FK	→ inventory, SET NULL
`created_at`	timestamptz
`updated_at`	timestamptz

Operations

inventory

Column	Type	Notes
`id`	uuid PK
`name`	text
`category`	text
`manufacturer`	varchar(255)
`batch_number`	varchar(100)
`quantity`	integer
`unit`	text	Default: 'unit'
`min_stock`	integer	Low stock threshold
`price`	numeric(12,2)
`supplier`	text	Free text
`supplier_id`	uuid FK	→ lab_vendors, SET NULL
`expiry_date`	date
`status`	text
`created_at`	timestamptz
`updated_at`	timestamptz

staff

HR records — separate from users.

Column	Type	Notes
`id`	uuid PK
`full_name`	text
`first_name`	varchar(100)
`last_name`	varchar(100)
`role`	text	Free text — not restricted to user_role enum
`department_id`	uuid FK	→ departments, SET NULL
`phone`	text
`email`	text
`salary`	numeric(10,2)
`qualifications`	text
`work_schedule`	jsonb	{day: {start, end}}
`status`	text
`joined_date`	date
`created_at`	timestamptz
`updated_at`	timestamptz

payroll

Column	Type	Notes
`id`	uuid PK
`staff_id`	uuid FK	→ users
`period_start`	date
`period_end`	date
`base_salary`	numeric(12,2)
`bonuses`	numeric(12,2)
`deductions`	numeric(12,2)
`net_amount`	numeric(12,2)
`status`	text	pending / paid
`paid_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

Auth & Security Tables

role_permissions

Stores the admin-configurable permission matrix.

Column	Type	Notes
`id`	uuid PK
`role`	varchar(50)	admin / doctor / receptionist / nurse
`permission_key`	varchar(100)	e.g. patients.view
`granted`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz

Unique constraint on (role, permission_key) prevents duplicate entries.

auth_audit_log

Records every auth event for security auditing.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid	Not a FK — preserves log if user deleted
`email`	varchar(255)
`event`	varchar(50)	login_success, login_failed, logout, etc.
`ip_address`	varchar(45)
`user_agent`	text
`metadata`	jsonb
`created_at`	timestamptz

login_attempts

Used for rate limiting — tracks failed login attempts by email and IP address.

Column	Type	Notes
`id`	uuid PK
`email`	varchar(255)	Indexed
`ip_address`	varchar(45)	Indexed
`attempted_at`	timestamptz
`success`	boolean

user_sessions

Tracks active sessions for visibility and revocation.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid	Indexed
`session_token`	varchar(255) UNIQUE
`ip_address`	varchar(45)
`user_agent`	text
`last_active_at`	timestamptz
`expires_at`	timestamptz
`is_revoked`	boolean
`created_at`	timestamptz

Blog

blog_categories → blog_posts → blog_comments

blog_categories
  └── blog_posts (category_id, SET NULL)
        └── blog_comments (post_id, CASCADE)

Content & Notifications

landing_page_settings

One row per clinic — all jsonb columns store structured settings objects per tab (branding, colors, content, seo, contact, social).

notifications

Per-user notification records used by the notification drawer.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid FK	→ users, CASCADE
`type`	text
`title`	text
`message`	text
`is_read`	boolean	Default false
`link`	text	Optional deep link
`created_at`	timestamptz

Cascade Behavior Reference

Understanding what happens to related records when a parent is deleted is critical before removing any record in production.

When you delete...	Effect on related records
A patient	Cascades to appointments, medical records, vitals, clinical notes, diagnoses, attachments, prescriptions, test reports, invoices, payments, odontograms
An appointment	Sets appointment_id to null on medical records, vitals, clinical notes, prescriptions, invoices, test reports
A user (staff)	Sets doctor_id / author_id / submitted_by to null on most tables; cascades on notifications
A department	Sets department_id to null on users, patients, services, staff, expenses
An invoice	Cascades to invoice_items; sets invoice_id to null on payments
A blog post	Cascades to all blog_comments for that post

Before deleting any core record in production, query related tables first to understand the full impact. Cascade deletes are immediate and cannot be undone.