Database schema

ER diagram (core entities)

erDiagram
    Tenant ||--o{ User : has
    Tenant ||--o{ Patient : has
    Tenant ||--o{ Department : has
    Department ||--o{ Patient : contains
    Patient ||--o{ Document : has
    Patient ||--o{ Prediction : has
    Patient ||--o{ DicomStudy : has
    User ||--o{ Document : uploads
    User }o--|| Department : belongs
    User ||--o{ ChatSession : has
    ChatSession ||--o{ ChatMessage : contains
    User ||--o{ Progress : tracks
    DicomStudy ||--o{ DicomSeries : contains
    DicomSeries ||--o{ DicomFrame : contains
    Document ||--o{ ParsedData : extracts
    Tenant ||--o{ Subscription : has
    User ||--o| UserPreference : has
    User ||--o| TelegramUser : links

Note

ClinicalCase and Test are scenario/exam catalogs (no required FK to User). The RAG vector store is Chroma (not a Postgres table).

Core tables

tenants

Column	Type	Description
id	INTEGER PK
name	VARCHAR	Clinic name
subdomain	VARCHAR UNIQUE	Login subdomain

users

Column	Type	Description
id	INTEGER PK
tenant_id	FK	Clinic
email	VARCHAR UNIQUE
role	ENUM	RBAC role (student, resident, doctor, …)
department_id	FK nullable	Department
password_hash	VARCHAR	bcrypt hash

patients

Column	Type	Description
id	INTEGER PK
tenant_id	FK
department_id	FK
full_name	VARCHAR	Encrypted when required
date_of_birth	DATE
attending_doctor_id	FK nullable

documents

Column	Type	Description
id	INTEGER PK
patient_id	FK
file_path	VARCHAR	Encrypted path
status	ENUM	uploaded/processing/parsed/failed
document_type	VARCHAR

dicom_studies / dicom_series / dicom_frames

DICOM hierarchy: Study → Series → Frame (PNG preview).

predictions

Column	Type	Description
readmission_risk	FLOAT	0–1
complication_risk	FLOAT	0–1
risk_level	VARCHAR	low/medium/high
gpt_explanation	TEXT

Learning

chat_sessions (ChatSession)

Column	Type	Description
id	INTEGER PK
public_id	UUID	Public identifier
user_id	FK → users	Session owner
title	VARCHAR nullable	Chat title
language	VARCHAR	Language (en / ru, …)
created_at / updated_at	DATETIME

chat_messages (ChatMessage)

Column	Type	Description
id	INTEGER PK
public_id	UUID
session_id	FK → chat_sessions	Cascade delete
role	VARCHAR	user / assistant / system
content	TEXT	Message body
sources	JSON nullable	RAG sources
created_at	DATETIME

clinical_cases (ClinicalCase)

Column	Type	Description
id	INTEGER PK
public_id	UUID
title	VARCHAR	Case title
description	TEXT	Description
specialty	VARCHAR nullable	Specialty
difficulty	VARCHAR nullable	Difficulty
steps	JSON nullable	Step-based scenario
created_at	DATETIME

tests (Test)

Column	Type	Description
id	INTEGER PK
public_id	UUID
title	VARCHAR	Exam / quiz title
description	TEXT nullable
category	VARCHAR nullable	Category
difficulty	VARCHAR nullable
questions	JSON nullable	Questions and options
created_at	DATETIME

progress (Progress)

Column	Type	Description
id	INTEGER PK
public_id	UUID
user_id	FK → users
module	VARCHAR	tutor / cases / exam (etc.)
score	FLOAT nullable	Score
time_spent	INTEGER nullable	Seconds
completed_at	DATETIME
extra_data	JSON nullable	Extra metadata

Migrations

Legacy: SQL/Python files in app/db/migrations/ (001–031) — frozen; applied via run_migrations() after create_all when ALEMBIC_ENABLED=false.

Alembic (recommended for prod): alembic/ directory, baseline 001_baseline. Set ALEMBIC_ENABLED=true — then deploy.sh runs alembic upgrade head (with pg_advisory_lock on PostgreSQL) instead of create_all.

docker compose exec app alembic upgrade head
python scripts/run_alembic_migrate.py

Schema inspection

grep "^class " app/models/*.py

Indexes

• patients(tenant_id, department_id)
• documents(patient_id, status)
• dicom_studies(patient_id, study_uid)
• chat_sessions(user_id), chat_messages(session_id)
• progress(user_id, module)

Anonymization (researcher)

In access.py, fields full_name, phone, email are replaced with P-{id} ANON on serialization.