# HAD Digital - Database Schema
Version: 1.0.0 Date: 5 September 2026 Database: SQLite (WAL mode) File: data/had.db
Entity Relationship Diagram (Conceptual)
patients (1) ──── (N) episodes
│ │
│ │
├── (N) treatment_plans
│ │
├── (N) toxicity_reports ──── (N) toxicity_grades ──── (N) alerts
│ │
├── (N) messages
│ │
└── (N) timeline_events
users ──── (N) toxicity_reports (as reporter)
users ──── (N) alerts (as assigned_to/acknowledged_by)
users ──── (N) messages (as sender/recipient)Tables
users
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique user ID |
| username | TEXT | UNIQUE NOT NULL | Login username |
| password_hash | TEXT | NOT NULL | scrypt hash of password |
| salt | TEXT | NOT NULL | Salt for password hashing |
| role | TEXT | NOT NULL, CHECK | User role (see below) |
| display_name | TEXT | NOT NULL | Full display name |
| TEXT | Email address | ||
| patient_id | INTEGER | FOREIGN KEY → patients(id) | Linked patient (for patient/caregiver roles) |
| active | INTEGER | NOT NULL DEFAULT 1 | Account active flag |
| failed_attempts | INTEGER | NOT NULL DEFAULT 0 | Failed login attempts |
| locked_until | TEXT | Account lock expiry timestamp | |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Valid Roles:
- oncologist - Hospital oncologist / referring physician
- had_nurse - HAD coordinating nurse
- community_nurse - Community / HAD nurse
- gp - General practitioner
- pharmacist - Hospital / HAD pharmacist
- patient - Patient
- caregiver - Caregiver / family member
- dmin - System administrator
patients
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique patient ID |
| mrn | TEXT | UNIQUE NOT NULL | Medical Record Number |
| first_name | TEXT | NOT NULL | First name |
| last_name | TEXT | NOT NULL | Last name |
| date_of_birth | TEXT | NOT NULL | Date of birth (YYYY-MM-DD) |
| gender | TEXT | CHECK ('M', 'F', 'X') | Gender |
| phone | TEXT | Phone number | |
| address | TEXT | Home address | |
| emergency_contact | TEXT | Emergency contact info | |
| primary_oncologist_id | INTEGER | FOREIGN KEY → users(id) | Primary oncologist |
| cancer_type | TEXT | Cancer diagnosis | |
| cancer_stage | TEXT | Cancer stage | |
| diagnosis_date | TEXT | Date of diagnosis | |
| status | TEXT | NOT NULL DEFAULT 'active', CHECK | Patient status |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Valid Statuses:
- ctive - Currently under care
- discharged - Discharged from HAD
- deceased - Deceased
episodes
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique episode ID |
| patient_id | INTEGER | NOT NULL, FOREIGN KEY → patients(id) | Patient reference |
| episode_type | TEXT | NOT NULL, CHECK | Type of care episode |
| start_date | TEXT | NOT NULL | Episode start date |
| end_date | TEXT | Episode end date | |
| status | TEXT | NOT NULL DEFAULT 'active', CHECK | Episode status |
| notes | TEXT | Additional notes | |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Valid Episode Types:
- chemotherapy
- adiotherapy
- immunotherapy
- argeted_therapy
- surgery
- ollowup
Valid Statuses:
- ctive - Currently active
- completed - Episode completed
- cancelled - Episode cancelled
treatment_plans
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique plan ID |
| patient_id | INTEGER | NOT NULL, FOREIGN KEY → patients(id) | Patient reference |
| episode_id | INTEGER | FOREIGN KEY → episodes(id) | Linked episode |
| protocol_name | TEXT | NOT NULL | Treatment protocol name |
| regimen | TEXT | NOT NULL | Regimen description |
| cycle_count | INTEGER | Total number of cycles | |
| current_cycle | INTEGER | DEFAULT 0 | Current cycle number |
| cycle_length_days | INTEGER | Days per cycle | |
| drugs | TEXT | NOT NULL | JSON array of drugs |
| start_date | TEXT | NOT NULL | Treatment start date |
| end_date | TEXT | Treatment end date | |
| status | TEXT | NOT NULL DEFAULT 'active', CHECK | Plan status |
| notes | TEXT | Additional notes | |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Valid Statuses:
- ctive - Currently active
- completed - Treatment completed
- modified - Plan modified
- discontinued - Treatment discontinued
toxicity_reports
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique report ID |
| patient_id | INTEGER | NOT NULL, FOREIGN KEY → patients(id) | Patient reference |
| episode_id | INTEGER | FOREIGN KEY → episodes(id) | Linked episode |
| treatment_plan_id | INTEGER | FOREIGN KEY → treatment_plans(id) | Linked treatment plan |
| reporter_id | INTEGER | NOT NULL, FOREIGN KEY → users(id) | User who submitted report |
| symptom_id | TEXT | NOT NULL | CTCAE symptom term |
| symptom_category | TEXT | NOT NULL | CTCAE domain (GI, hematologic, etc.) |
| report_date | TEXT | NOT NULL DEFAULT datetime('now') | Report submission date |
| severity_score | REAL | Raw severity score | |
| lab_values | TEXT | JSON of lab values | |
| notes | TEXT | Additional notes | |
| status | TEXT | NOT NULL DEFAULT 'pending', CHECK | Report status |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Valid Statuses:
- pending - Awaiting grading
- graded - Graded by engine
- confirmed - Clinician confirmed
- esolved - Issue resolved
toxicity_grades
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique grade ID |
| report_id | INTEGER | NOT NULL, FOREIGN KEY → toxicity_reports(id) | Linked report |
| patient_id | INTEGER | NOT NULL, FOREIGN KEY → patients(id) | Patient reference |
| symptom_id | TEXT | NOT NULL | CTCAE symptom term |
| grade | INTEGER | NOT NULL, CHECK (1-5) | CTCAE grade |
| criteria | TEXT | NOT NULL | Grading criteria applied |
| intervention | TEXT | Recommended intervention | |
| provisional | INTEGER | NOT NULL DEFAULT 1 | Provisional flag (1=yes, 0=no) |
| confirmed_by | INTEGER | FOREIGN KEY → users(id) | Clinician who confirmed |
| confirmed_at | TEXT | Confirmation timestamp | |
| grading_engine_version | TEXT | Engine version used | |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Grade Meanings:
- 1: Mild; asymptomatic or mild symptoms; clinical or diagnostic observations only; intervention not indicated
- 2: Moderate; minimal, local or noninvasive intervention indicated; limiting age-appropriate instrumental ADL
- 3: Severe or medically significant but not immediately life-threatening; hospitalization or prolongation of hospitalization indicated; disabling; limiting self-care ADL
- 4: Life-threatening consequences; urgent intervention indicated
- 5: Death related to AE
alerts
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique alert ID |
| patient_id | INTEGER | NOT NULL, FOREIGN KEY → patients(id) | Patient reference |
| grade_id | INTEGER | FOREIGN KEY → toxicity_grades(id) | Linked grade |
| report_id | INTEGER | FOREIGN KEY → toxicity_reports(id) | Linked report |
| alert_type | TEXT | NOT NULL, CHECK | Alert tier |
| severity | TEXT | NOT NULL, CHECK | Alert severity level |
| title | TEXT | NOT NULL | Alert title |
| message | TEXT | NOT NULL | Alert message |
| assigned_to | INTEGER | FOREIGN KEY → users(id) | Assigned user |
| assigned_role | TEXT | Assigned role | |
| status | TEXT | NOT NULL DEFAULT 'pending', CHECK | Alert status |
| acknowledged_by | INTEGER | FOREIGN KEY → users(id) | User who acknowledged |
| acknowledged_at | TEXT | Acknowledgment timestamp | |
| resolved_at | TEXT | Resolution timestamp | |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
| updated_at | TEXT | NOT NULL DEFAULT datetime('now') | Last update timestamp |
Valid Alert Types:
- outine - Routine follow-up (timeline only)
- urgent - Urgent callback (notification to HAD nurse)
- emergency - Emergency escalation (immediate notification to oncologist)
Valid Severities:
- low
- medium
- high
- critical
Valid Statuses:
- pending - Awaiting acknowledgment
- cknowledged - Acknowledged by clinician
- esolved - Issue resolved
- escalated - Escalated to higher level
messages
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique message ID |
| sender_id | INTEGER | NOT NULL, FOREIGN KEY → users(id) | Message sender |
| recipient_id | INTEGER | FOREIGN KEY → users(id) | Direct recipient |
| recipient_role | TEXT | Role-based recipient | |
| patient_id | INTEGER | FOREIGN KEY → patients(id) | Linked patient |
| subject | TEXT | Message subject | |
| body | TEXT | NOT NULL | Message body |
| message_type | TEXT | NOT NULL DEFAULT 'direct', CHECK | Message type |
| read | INTEGER | NOT NULL DEFAULT 0 | Read flag (1=yes, 0=no) |
| read_at | TEXT | Read timestamp | |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
Valid Message Types:
- direct - Direct message between users
- lert_notification - Alert notification
- system - System message
- care_team - Care team message
timeline_events
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique event ID |
| patient_id | INTEGER | NOT NULL, FOREIGN KEY → patients(id) | Patient reference |
| episode_id | INTEGER | FOREIGN KEY → episodes(id) | Linked episode |
| event_type | TEXT | NOT NULL, CHECK | Event type |
| event_date | TEXT | NOT NULL DEFAULT datetime('now') | Event date/time |
| title | TEXT | NOT NULL | Event title |
| description | TEXT | Event description | |
| reference_id | INTEGER | ID of referenced entity | |
| reference_table | TEXT | Table of referenced entity | |
| created_by | INTEGER | FOREIGN KEY → users(id) | User who created event |
| created_at | TEXT | NOT NULL DEFAULT datetime('now') | Creation timestamp |
Valid Event Types:
- eport - Toxicity report
- grade - CTCAE grade
- lert - Clinical alert
- message - Care team message
- reatment - Treatment event
- ppointment - Appointment
ote - Clinical note
- system - System event
audit_log
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique entry ID |
| timestamp | TEXT | NOT NULL DEFAULT datetime('now') | Action timestamp |
| user_id | INTEGER | FOREIGN KEY → users(id) | User who performed action |
| username | TEXT | Username (for deleted users) | |
| action | TEXT | NOT NULL | Action performed |
| resource_type | TEXT | Type of resource affected | |
| resource_id | INTEGER | ID of resource affected | |
| details | TEXT | Additional details | |
| ip_address | TEXT | Client IP address | |
| user_agent | TEXT | Client user agent |
Common Actions:
- login - User login
- logout - User logout
- submit_report - Toxicity report submitted
- grade_assigned - CTCAE grade assigned
- lert_created - Alert created
- lert_acknowledged - Alert acknowledged
- message_sent - Message sent
- iew_patient - Patient record viewed
- export_summary - Toxicity summary exported
Indexes
The following indexes are recommended for production:
CREATE INDEX idx_patients_mrn ON patients(mrn);
CREATE INDEX idx_patients_status ON patients(status);
CREATE INDEX idx_episodes_patient ON episodes(patient_id);
CREATE INDEX idx_episodes_status ON episodes(status);
CREATE INDEX idx_treatment_plans_patient ON treatment_plans(patient_id);
CREATE INDEX idx_treatment_plans_status ON treatment_plans(status);
CREATE INDEX idx_toxicity_reports_patient ON toxicity_reports(patient_id);
CREATE INDEX idx_toxicity_reports_date ON toxicity_reports(report_date);
CREATE INDEX idx_toxicity_grades_report ON toxicity_grades(report_id);
CREATE INDEX idx_toxicity_grades_patient ON toxicity_grades(patient_id);
CREATE INDEX idx_alerts_patient ON alerts(patient_id);
CREATE INDEX idx_alerts_status ON alerts(status);
CREATE INDEX idx_alerts_type ON alerts(alert_type);
CREATE INDEX idx_messages_sender ON messages(sender_id);
CREATE INDEX idx_messages_recipient ON messages(recipient_id);
CREATE INDEX idx_messages_patient ON messages(patient_id);
CREATE INDEX idx_timeline_patient ON timeline_events(patient_id);
CREATE INDEX idx_timeline_date ON timeline_events(event_date);
CREATE INDEX idx_timeline_type ON timeline_events(event_type);
CREATE INDEX idx_audit_log_user ON audit_log(user_id);
CREATE INDEX idx_audit_log_action ON audit_log(action);
CREATE INDEX idx_audit_log_timestamp ON audit_log(timestamp);Data Integrity
- Foreign Keys: All foreign key constraints are enforced. Deleting a parent record will fail if child records exist.
- Check Constraints: All CHECK constraints are enforced (roles, statuses, grades, etc.).
- Not Null: Required fields are marked NOT NULL.
- Timestamps: All tables use ISO 8601 format timestamps (UTC).
- Audit Trail: The audit_log table is append-only and cannot be modified or deleted through the API.
Migration Notes
The current schema is designed for SQLite but is structured for future PostgreSQL migration:
- All INTEGER PRIMARY KEY AUTOINCREMENT can become SERIAL PRIMARY KEY in PostgreSQL.
- All TEXT fields can become VARCHAR or TEXT in PostgreSQL.
- All datetime('now') defaults can become NOW() in PostgreSQL.
- CHECK constraints are compatible with PostgreSQL.
- JSON fields (lab_values, drugs) can use PostgreSQL's JSONB type.