# Database Architecture — Milestone 1
## AI-Guided CT Surgery Patient Intake System

**Version:** 2.0
**Date:** 2026-05-11
**Status:** Draft

---

## 1. Overview

Two data stores are used:

- **PostgreSQL** — structured, persistent data: staff accounts, departments, protocols, sessions, responses, summaries
- **Redis** — ephemeral, high-speed data: active session lookup, patient locks, concurrency enforcement

Patient-related data (patients, sessions, responses, summaries) is temporary by design — wiped after the configured retention window (`DATA_RETENTION_HOURS` env var, default 48 hours). Staff accounts, departments, and protocols are permanent.

---

## 2. PostgreSQL Schema

### Entity Relationship Diagram

```mermaid
erDiagram
    departments ||--o{ staff : "has"
    departments ||--o{ protocols : "has"
    departments ||--o{ patients : "has"
    departments ||--o{ intake_sessions : "has"

    staff ||--o{ session_contributors : "contributes to"
    staff ||--o{ question_responses : "records"

    patients ||--|| intake_sessions : "has one"
    intake_sessions ||--o{ session_contributors : "has"
    intake_sessions ||--o{ question_responses : "has"
    intake_sessions ||--o| patient_summaries : "produces"
    protocols ||--o{ intake_sessions : "guides"
```

---

### Table: `departments`

Stores each clinical department. In M1 only CT Surgery exists. Adding a new department in future milestones is a data insert — no code change needed.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `name` | VARCHAR(100) | NOT NULL, UNIQUE | e.g. "Cardio Thoracic Surgery" |
| `llm_system_prompt` | TEXT | NOT NULL | Intake questioning prompt for this department |
| `llm_summary_prompt` | TEXT | NOT NULL | Summary generation prompt for this department |
| `is_active` | BOOLEAN | NOT NULL, DEFAULT true | |
| `created_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |

---

### Table: `staff`

All user accounts — Medical Staff, Cardiologists, and Admins. Role is stored but not enforced in M1 — RBAC enforcement is M2.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `name` | VARCHAR(100) | NOT NULL | |
| `role` | VARCHAR(30) | NOT NULL | `MEDICAL_STAFF`, `CARDIOLOGIST`, `ADMIN` |
| `department_id` | UUID | FK → departments, NOT NULL | |
| `email` | VARCHAR(255) | NOT NULL, UNIQUE | Login identifier |
| `pin_hash` | VARCHAR(255) | NOT NULL | bcrypt hash of 6-digit PIN set by Admin |
| `failed_pin_attempts` | SMALLINT | NOT NULL, DEFAULT 0 | Reset to 0 on successful login |
| `locked_at` | TIMESTAMPTZ | NULL | Set when failed_pin_attempts reaches 5 |
| `is_active` | BOOLEAN | NOT NULL, DEFAULT true | Admin can deactivate |
| `created_by` | UUID | FK → staff ON DELETE SET NULL, NULL | Admin who created this account |
| `created_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |

**Indexes:**
- `staff_email_idx` UNIQUE on `email`
- `staff_department_idx` on `department_id`

**Note for interns:**
- `created_by` uses ON DELETE SET NULL so deactivating an Admin account does not block deletion due to FK constraint on the staff they created.
- To lock an account: set `locked_at = now()` when `failed_pin_attempts` reaches 5.
- To unlock an account (Admin action): set `locked_at = NULL` and `failed_pin_attempts = 0`.

---

### Table: `protocols`

The CT Surgery question bank. Pre-loaded at deployment in M1. Only one active protocol per department at any time — enforced at DB level.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `department_id` | UUID | FK → departments, NOT NULL | |
| `version` | SMALLINT | NOT NULL, DEFAULT 1 | Always 1 in M1 |
| `question_bank` | JSONB | NOT NULL | Array of question objects — see structure below |
| `is_active` | BOOLEAN | NOT NULL, DEFAULT true | Only one active protocol per department |
| `created_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |

**`question_bank` JSONB structure:**
```json
[
  {
    "id": "q_chief_complaint",
    "text": "What brings the patient in today?",
    "category": "CHIEF_COMPLAINT",
    "required": true
  },
  {
    "id": "q_chest_pain",
    "text": "Does the patient have chest pain?",
    "category": "CARDIAC",
    "required": true
  }
]
```

The branching logic (follow-up decisions) is handled by Claude at runtime — not stored as decision trees. The `question_bank` is the list of base questions Claude must cover, passed as context on every LLM call.

**Indexes:**
- `protocols_department_active_idx` on `(department_id, is_active)`

**Partial unique index — enforced at DB level (only one active protocol per department):**
```sql
CREATE UNIQUE INDEX one_active_protocol_per_dept
ON protocols (department_id) WHERE is_active = true;
```

---

### Table: `patients`

Minimum patient identification for a session. Wiped after the retention window.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `daily_id` | VARCHAR(50) | NOT NULL | Manually entered by staff (visit number, bed number, etc.) |
| `department_id` | UUID | FK → departments, NOT NULL | |
| `name` | VARCHAR(100) | NOT NULL | |
| `age` | SMALLINT | NOT NULL | |
| `gender` | VARCHAR(20) | NOT NULL | |
| `intake_date` | DATE | NOT NULL, DEFAULT CURRENT_DATE | |
| `created_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |
| `expires_at` | TIMESTAMPTZ | NOT NULL | Computed and passed by FastAPI at insert time: `now() + DATA_RETENTION_HOURS hours`; no DB-level default possible for env vars; used by APScheduler task to purge |

**Constraints:**
- UNIQUE on `(daily_id, department_id, intake_date)` — prevents the same patient ID being entered twice in the same department on the same day

**Indexes:**
- `patients_daily_lookup_idx` UNIQUE on `(daily_id, department_id, intake_date)`
- `patients_expires_idx` on `expires_at` — for efficient cron purge query

**Note for interns:** When staff enters a patient ID on the new intake form, the backend checks this table before inserting. If a row exists for the same `(daily_id, department_id, intake_date)`, return an error and direct staff to the existing session — do not create a duplicate.

---

### Table: `intake_sessions`

One session per patient per retention window. Tracks lifecycle. Status can cycle ACTIVE → COMPLETED → ACTIVE when "Ask More?" is used.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `patient_id` | UUID | FK → patients ON DELETE CASCADE, NOT NULL | |
| `department_id` | UUID | FK → departments, NOT NULL | Denormalized for fast query |
| `protocol_id` | UUID | FK → protocols, NOT NULL | Protocol active at session start |
| `status` | VARCHAR(20) | NOT NULL, DEFAULT 'ACTIVE' | `ACTIVE` or `COMPLETED` |
| `started_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |
| `completed_at` | TIMESTAMPTZ | NULL | Set when status = COMPLETED; cleared back to NULL if Ask More resumes session |
| `created_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |

**Constraints:**
- UNIQUE on `patient_id` — one session per patient enforced at DB level

**Indexes:**
- `sessions_patient_idx` UNIQUE on `patient_id`
- `sessions_status_idx` on `(department_id, status)`

**Note for interns:** When "Ask More?" is clicked after summary, update `status` back to `ACTIVE` and set `completed_at` to NULL. When staff submits again, set `status = COMPLETED` and `completed_at = now()`. This allows the intake list to show correct status at all times.

---

### Table: `session_contributors`

Records which staff contributed to a session and in what order — used to display the intake history log.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `session_id` | UUID | FK → intake_sessions ON DELETE CASCADE, NOT NULL | |
| `staff_id` | UUID | FK → staff, NOT NULL | |
| `sequence_number` | SMALLINT | NOT NULL | Order they joined — 1 = first, 2 = second, etc. |
| `joined_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |

**Constraints:**
- UNIQUE on `(session_id, staff_id)` — DB-level enforcement that the same staff member cannot have two contributor rows for the same session

**Indexes:**
- `contributors_session_idx` on `(session_id, sequence_number)`

**Note for interns:** Insert a new row only when a staff member who does NOT already have a row in this session opens it. The UNIQUE constraint on `(session_id, staff_id)` will reject a duplicate at DB level — the application should check first and skip the insert if a row already exists. Sequence number is the count of existing contributors + 1.

---

### Table: `question_responses`

Every question asked and answer given during a session, in order. Includes flagged responses for the completion screen.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `session_id` | UUID | FK → intake_sessions ON DELETE CASCADE, NOT NULL | |
| `question_id` | VARCHAR(100) | NULL | References `question_bank[].id` for standard questions; NULL for LLM-generated follow-up questions that have no protocol ID |
| `question_text` | TEXT | NOT NULL | Denormalized — protocol may change; record what was actually asked |
| `transcribed_text` | TEXT | NOT NULL | The answer text — whatever staff spoke or typed; stored as-is |
| `input_type` | VARCHAR(10) | NOT NULL | `VOICE` (Web Speech API), `TEXT` (manually typed) |
| `input_language` | VARCHAR(20) | NOT NULL, DEFAULT 'ENGLISH' | Language staff used: `ENGLISH`, `HINDI`, `TELUGU` |
| `is_flagged` | BOOLEAN | NOT NULL, DEFAULT false | True if answer was non-substantive ("no", "I don't know", "NA", empty, or equivalent) |
| `sequence_number` | SMALLINT | NOT NULL | Order within the session |
| `responded_by_staff_id` | UUID | FK → staff, NOT NULL | Which staff member recorded this response |
| `created_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | |

**Indexes:**
- `responses_session_idx` on `(session_id, sequence_number)`

**Note for interns:**
- `transcribed_text` stores whatever staff spoke or typed — "NA", "patient doesn't recall", "refused to answer", etc. There is no skip action; staff conveys the situation in their own words.
- `is_flagged` is set by the backend at INTAKE_COMPLETE time by scanning all `question_responses` and checking `transcribed_text` against non-substantive criteria: "no", "i don't know", "na", empty/null, or equivalent short dismissals. The LLM does not flag these — the backend does.
- All flagged responses (`is_flagged = true`) are returned to the frontend to show on the completion screen.

---

### Table: `patient_summaries`

One summary per session. Replaced (UPDATE, not INSERT) when the session is resumed via "Ask More?" and a new summary is generated.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | UUID | PK, DEFAULT gen_random_uuid() | |
| `session_id` | UUID | FK → intake_sessions ON DELETE CASCADE, NOT NULL, UNIQUE | One summary record per session |
| `summary_text` | TEXT | NOT NULL | Structured markdown output from Claude |
| `generated_at` | TIMESTAMPTZ | NOT NULL, DEFAULT now() | Updated on each regeneration |
| `generation_latency_ms` | INT | NOT NULL | Time taken for Claude to generate; used for SLA monitoring |

**Note for interns:** When "Ask More?" leads to a new summary, do an `UPDATE patient_summaries SET summary_text = ..., generated_at = now(), generation_latency_ms = ...` — not a new INSERT. The UNIQUE constraint on `session_id` enforces this.

---

## 3. Redis Key Structures

| Key | Type | Value | TTL | Purpose |
|---|---|---|---|---|
| `patient:{deptId}:{dailyId}:sessionId` | String | PostgreSQL session UUID | `DATA_RETENTION_HOURS × 3600` seconds | Fast lookup — does a session exist for this patient today? Namespaced by department to avoid cross-department collision |
| `patient_lock:{deptId}:{dailyId}` | String | staffId of current writer | 30 min — refreshed on every `/respond` call | Prevents two staff writing to the same session at the same time |
| `staff:{staffId}:session` | String | Active session UUID | 30 min — refreshed on every `/respond` call | Tracks active staff; count of these keys = number of concurrent sessions |

**Concurrency enforcement:** Max 5 concurrent sessions is enforced by counting how many `staff:*:session` keys exist in Redis at login time. No separate counter needed — when a key expires (inactivity) or is deleted (logout), the slot frees automatically.

**Patient data wipe:** When `patient:{deptId}:{dailyId}:sessionId` expires, a FastAPI APScheduler task (hourly) deletes the corresponding PostgreSQL rows via `DELETE FROM patients WHERE expires_at < NOW()`. CASCADE handles all child rows automatically.

**Auto-save:** No separate mechanism needed. Every POST `/sessions/{id}/respond` call writes the response to `question_responses` immediately. The DB is always current — auto-save is a side effect of normal operation.

---

## 4. Data Lifecycle

```
Staff enters patient info (daily_id, name, age, gender)
      ↓
System checks: does this daily_id already have an active session?
  Yes → return error, direct to existing session
  No  → continue
      ↓
patients row created — expires_at = now() + DATA_RETENTION_HOURS
intake_sessions row created (status = ACTIVE)
session_contributors row created (sequence_number = 1)
Redis: SET patient:{deptId}:{dailyId}:sessionId (TTL = DATA_RETENTION_HOURS × 3600 seconds)
Redis: SET patient_lock:{deptId}:{dailyId} = staffId (TTL = 30min)
      ↓
Each response → question_responses row inserted immediately
      ↓
LLM signals INTAKE_COMPLETE
  → Flagged responses marked with is_flagged = true
      ↓
Staff submits → POST /sessions/{id}/complete
  → intake_sessions.status = COMPLETED, completed_at = now()
  → patient_summaries row inserted with summary text
      ↓
[Optional] Staff clicks "Ask More?"
  → intake_sessions.status = ACTIVE, completed_at = NULL
  → session_contributors row added if different staff
  → more question_responses inserted
  → Staff submits again → status = COMPLETED
  → patient_summaries record UPDATED (not new row)
      ↓
DATA_RETENTION_HOURS passes
  → Redis keys self-expire
  → FastAPI APScheduler (hourly): DELETE FROM patients WHERE expires_at < NOW()
  → CASCADE deletes: intake_sessions, session_contributors,
                     question_responses, patient_summaries
```

**Cascade delete chain:**
```
patients
  └── intake_sessions (ON DELETE CASCADE)
        ├── session_contributors (ON DELETE CASCADE)
        ├── question_responses (ON DELETE CASCADE)
        └── patient_summaries (ON DELETE CASCADE)
```

Staff accounts, departments, and protocols are **never deleted** by the cron job.

---

## 5. What Is Not in This Schema (M1)

| Table | Reason deferred |
|---|---|
| `audit_logs` | Deferred to M2 |
| `alert_patterns` | Senior consultation alerts deferred to M2 |
| `ai_agent_sequences` | Doctor training module deferred to M3 |
| `regional_languages` | Language config UI deferred to M3; supported languages (English, Hindi, Telugu) are hardcoded in M1 |

---

## 6. Key Design Decisions

| Decision | Reason |
|---|---|
| All tables have `department_id` | Adding a new department needs only a data insert — no schema change |
| `question_text` denormalized onto `question_responses` | Records exactly what was asked at the time — protocol may change later |
| `expires_at` on `patients` only, computed from `DATA_RETENTION_HOURS` | Cron queries one table; cascade handles all child rows automatically |
| `question_bank` stored as JSONB | Protocol structure can evolve without a schema migration |
| Role stored as VARCHAR not ENUM | New roles can be added in M2 without a database migration |
| `session_contributors.sequence_number` | Keeps intake history in correct display order regardless of query sort |
| Single active protocol per department via partial unique index | Prevents two active protocols even if application code has a bug |
| Redis keys namespaced by `deptId` | Prevents collision when departments share patient daily IDs like "P001" |
| No `current_writer_staff_id` in `intake_sessions` | Redis `patient_lock` is authoritative — duplicating it in PostgreSQL causes sync issues on TTL expiry |
| No `left_at` in `session_contributors` | `joined_at` and `sequence_number` are sufficient for intake history display |
| `staff.created_by` ON DELETE SET NULL | Deactivating an Admin does not break FK references on staff they created |
| `is_flagged` on `question_responses` | Completion screen queries this column directly — no need to scan answer text at runtime |
| `input_language` on `question_responses` | Records language used per response for context and future analytics |
| `input_type` only VOICE or TEXT | No skip action — staff conveys unanswered questions in their own words via normal response |
| `patient_summaries` UPDATEd not INSERTed on Ask More | UNIQUE on `session_id` enforces one summary per session; Ask More replaces it |
| `intake_sessions.completed_at` cleared on Ask More | Intake list shows correct In Progress status when a session re-opens |