# HAD Digital - Database Schema

**Version:** 1.0.0  
**Date:** 5 September 2026  
**Database:** SQLite (WAL mode)  
**File:** data/had.db

---

## Entity Relationship Diagram (Conceptual)

```text
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 |
| email | 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:

```sql
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

1. **Foreign Keys:** All foreign key constraints are enforced. Deleting a parent record will fail if child records exist.
2. **Check Constraints:** All CHECK constraints are enforced (roles, statuses, grades, etc.).
3. **Not Null:** Required fields are marked NOT NULL.
4. **Timestamps:** All tables use ISO 8601 format timestamps (UTC).
5. **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:

1. All INTEGER PRIMARY KEY AUTOINCREMENT can become SERIAL PRIMARY KEY in PostgreSQL.
2. All TEXT fields can become VARCHAR or TEXT in PostgreSQL.
3. All datetime('now') defaults can become NOW() in PostgreSQL.
4. CHECK constraints are compatible with PostgreSQL.
5. JSON fields (lab_values, drugs) can use PostgreSQL's JSONB type.