Skip to content

07 Database and Data Model

Jump to bottom
Anubha Parashar edited this page Oct 10, 2026 · 1 revision

HealthBook AI (internal code name: BabyCare) keeps every record in one relational schema, managed by SQLAlchemy and versioned by Alembic. The same schema runs on SQLite for local and self-hosted use and on PostgreSQL in the cloud deployment. Every clinical row is scoped to a profile (patients.id). Free-text clinical fields are encrypted at the application layer. Structured records point back to the original document and page they were transcribed from. This page describes the conceptual model. The diagrams show representative columns only; they are not a full DDL.

Responsibilities

  • Model accounts, family profiles and caregiver grants so that profile isolation can be enforced on every query.
  • Keep prescribed, scheduled and administered facts in separate tables: prescribed ≠ scheduled ≠ given/administered.
  • Keep provenance (source document, page, bundle reference, origin, verification status) on every structured record.
  • Keep history: corrections write the previous values to revision tables, and journal rows are withdrawn rather than deleted.
  • Encrypt clinical free text with the EncryptedText column type.

Inputs and outputs

Producer Tables written Consumer
Upload and OCR review documents, extraction_reviews, then medicines, vaccines, observations, episodes History, export, share
Caregiver workflows medication_events, treatment_administrations, hospital_visits, admissions, growth Timeline, Ask, PDF export
Daily Care journal *_entries, care_days, stool_photos, stool_intake, vitals_entries Calendar, analytics
Lifetime Health fever_episodes, antibiotic_courses, antibiotic_administrations, lifetime_evidence, parent_health_entries Lifetime dashboard
Reminder worker reminders, notifications, worker_heartbeat Push delivery
Share with Doctor share_packages, share_access_log Public doctor link
Every write audit, record_revisions, medicine_revisions Audit view, export

Accounts, profiles and documents

erDiagram
    USERS ||--o{ SESSIONS : "signs in with"
    USERS ||--o{ PATIENTS : "owns"
    USERS ||--o{ CAREGIVER_ACCESS : "is granted"
    PATIENTS ||--o{ CAREGIVER_ACCESS : "shared via"
    PATIENTS ||--o{ INVITATIONS : "invites"
    USERS ||--o{ PASSWORD_RESET_TOKENS : "requests"
    PATIENTS ||--o{ DOCUMENTS : "holds"
    DOCUMENTS ||--o| EXTRACTION_REVIEWS : "reviewed in"
    USERS ||--o{ PUSH_DEVICES : "registers"
    PATIENTS ||--o{ REMINDERS : "has"
    REMINDERS ||--o{ NOTIFICATIONS : "delivered as"
    PUSH_DEVICES ||--o{ NOTIFICATIONS : "targets"
    PATIENTS ||--o{ AUDIT : "logged in"
    PATIENTS ||--o{ RECORD_REVISIONS : "keeps"
    PATIENTS ||--o{ SHARE_PACKAGES : "shared as"
    SHARE_PACKAGES ||--o{ SHARE_ACCESS_LOG : "records"
    USERS {
        int id PK
        string email
        string password_salt
        string password_hash
    }
    SESSIONS {
        int id PK
        int user_id FK
        string token_hash
        datetime expires_at
    }
    PATIENTS {
        int id PK
        int user_id FK
        text name "encrypted"
        date dob
        string tz
        string relationship
        text modules_json
    }
    CAREGIVER_ACCESS {
        int patient_id FK
        int user_id FK
        string role "viewer or editor"
    }
    DOCUMENTS {
        int id PK
        int patient_id FK
        text title "encrypted"
        string category
        string storage_name
        string sha256
        text extracted_cipher
        string extraction_status
        int page_count
        string source_ref
        string import_batch
    }
    EXTRACTION_REVIEWS {
        int document_id FK
        text payload "encrypted"
        string status
        int version
    }
    SHARE_PACKAGES {
        int id PK
        text scope_json "encrypted"
        string token_hash
        string pin_hash
        datetime expires_at
        datetime revoked_at
    }
    SHARE_ACCESS_LOG {
        int package_id FK
        string event
        string ip_hash
    }
Loading

A "family profile" is still a patients row; the profile layer (sex, relationship, modules_json) was added in 0009. Module switches only hide features; they never delete data. caregiver_access has a unique (patient_id, user_id) constraint and a viewer or editor role. Invitations store only token_hash and expire after 3 days.

Clinical records

erDiagram
    PATIENTS ||--o{ MEDICINES : "prescribed"
    MEDICINES ||--o{ MEDICINE_REVISIONS : "versioned"
    MEDICINES ||--o{ MEDICATION_EVENTS : "reported given or skipped"
    PATIENTS ||--o{ VACCINES : "has"
    PATIENTS ||--o{ OBSERVATIONS : "lab results"
    PATIENTS ||--o{ ADMISSIONS : "hospital stays"
    ADMISSIONS ||--o{ HOSPITAL_VISITS : "groups"
    HOSPITAL_VISITS ||--o{ TREATMENT_ADMINISTRATIONS : "documents"
    PATIENTS ||--o{ EPISODES : "illness episodes"
    PATIENTS ||--o{ GROWTH : "measured"
    DOCUMENTS ||--o{ MEDICINES : "source of"
    DOCUMENTS ||--o{ OBSERVATIONS : "source of"
    DOCUMENTS ||--o{ TREATMENT_ADMINISTRATIONS : "required source"
    MEDICINES {
        int id PK
        text name "encrypted"
        text instructions "encrypted"
        string course_type
        string status "draft verified discontinued"
        date actual_start_date
        date actual_stop_date
        string schedule_times
        int source_document_id FK
        int source_page
        int version
    }
    MEDICINE_REVISIONS {
        int medicine_id FK
        int version
        text snapshot_json "encrypted"
    }
    MEDICATION_EVENTS {
        int medicine_id FK
        string event_type "given skipped stopped"
        datetime occurred_at
        string source
        string idempotency_key
        int medicine_version
    }
    VACCINES {
        int id PK
        text name "encrypted"
        string dose_label
        date due_date
        date given_date
        string source_type
        bool confirmed
        string reminder_days
    }
    OBSERVATIONS {
        int id PK
        text test_name "encrypted"
        text value "encrypted"
        text unit "encrypted"
        text specimen "encrypted"
        text organism "encrypted"
        text susceptibility "encrypted"
        bool verified
        date record_date
    }
    ADMISSIONS {
        int id PK
        text facility "encrypted"
        text diagnosis "encrypted"
        date admitted_on
        date discharged_on
        int discharge_document_id FK
    }
    HOSPITAL_VISITS {
        int id PK
        string kind
        datetime planned_at
        string status "planned attended postponed cancelled"
        bool confirmed
        int admission_id FK
    }
    TREATMENT_ADMINISTRATIONS {
        int visit_id FK
        text medication "encrypted"
        datetime occurred_at
        int source_document_id FK
        string idempotency_key
    }
    EPISODES {
        int id PK
        text title "encrypted"
        date started_on
        text documented_diagnosis "encrypted"
        text links_json "encrypted"
    }
    GROWTH {
        date record_date
        text weight_kg "encrypted"
        text length_cm "encrypted"
        text head_cm "encrypted"
    }
Loading

These separations are structural. hospital_visits.status = attended records that a facility visit happened; only a treatment_administrations row, which requires a source document, records that treatment was administered (appointment ≠ treatment administered). vaccines.due_date and vaccines.given_date are separate columns (due ≠ administered). observations.susceptibility is lab text and never creates a medicine (susceptibility ≠ antibiotic received). medication_events can only be written against a verified medicine, and each event stores the medicine_version it applied to.

Daily Care, vitals and camera

erDiagram
    PATIENTS ||--o{ CARE_DAYS : "day notes"
    PATIENTS ||--o| CARE_SETTINGS : "configures"
    PATIENTS ||--o{ FLUID_ENTRIES : "logs"
    PATIENTS ||--o{ DIAPER_ENTRIES : "logs"
    PATIENTS ||--o{ STOOL_ENTRIES : "logs"
    STOOL_ENTRIES ||--o{ STOOL_PHOTOS : "photographed"
    STOOL_ENTRIES ||--o| DIAPER_ENTRIES : "paired with"
    PATIENTS ||--o{ STOOL_INTAKE : "pending uploads"
    PATIENTS ||--o{ MEDICATION_ADMINISTRATIONS : "logs"
    MEDICINES ||--o{ MEDICATION_ADMINISTRATIONS : "given against"
    PATIENTS ||--o{ SYMPTOM_ENTRIES : "logs"
    PATIENTS ||--o{ ACTIVITY_ENTRIES : "logs"
    PATIENTS ||--o{ SLEEP_ENTRIES : "logs"
    PATIENTS ||--o{ VITALS_ENTRIES : "logs"
    PATIENTS ||--o{ CAMERAS : "configures"
    CAMERAS ||--o{ CAMERA_OBSERVATIONS : "emits"
    CAMERA_OBSERVATIONS ||--o| SLEEP_ENTRIES : "confirmed into"
    STOOL_ENTRIES {
        int id PK
        datetime at
        date local_date
        string colour
        string consistency
        string ai_review_status
        string time_source
        text ai_suggestion_json "encrypted"
        text parent_values_json "encrypted"
        string status "active or withdrawn"
        string client_key
    }
    STOOL_PHOTOS {
        string storage_name
        string sha256
        datetime taken_at
        string taken_at_source
        text metadata_json "encrypted"
        datetime deleted_at
    }
    MEDICATION_ADMINISTRATIONS {
        text medicine_name "encrypted"
        text dose "encrypted"
        datetime scheduled_for
        string administration_status
        int antibiotic_course_id FK
    }
    VITALS_ENTRIES {
        int systolic
        int diastolic
        int heart_rate
        int spo2
        float glucose_mg_dl
        float weight_kg
    }
    CAMERAS {
        text stream_url "encrypted"
        text password "encrypted"
        string worker_key_hash
        bool enabled
    }
Loading

Every journal table shares the _CareEntry columns: patient_id, recorded_by, at (timezone-aware), local_date (computed in the profile's timezone at write time), note (encrypted), source (parent, camera_confirmed or import), status (active or withdrawn), version and client_key. A unique (patient_id, client_key) constraint makes offline resubmission idempotent. Stool-image output is observational only, never a diagnosis, and ai_suggestion_json and parent_values_json keep what the app suggested apart from what the parent confirmed.

Lifetime Health

erDiagram
    PATIENTS ||--o{ FEVER_EPISODES : "documented"
    PATIENTS ||--o{ ANTIBIOTIC_COURSES : "documented"
    ANTIBIOTIC_COURSES ||--o{ ANTIBIOTIC_ADMINISTRATIONS : "given"
    ANTIBIOTIC_COURSES ||--o| ANTIBIOTIC_COURSES : "continuation of"
    FEVER_EPISODES ||--o{ ANTIBIOTIC_COURSES : "linked"
    EPISODES ||--o{ FEVER_EPISODES : "part of"
    ADMISSIONS ||--o{ ANTIBIOTIC_COURSES : "during"
    PATIENTS ||--o{ PARENT_HEALTH_ENTRIES : "reports"
    PARENT_HEALTH_ENTRIES ||--o{ ANTIBIOTIC_ADMINISTRATIONS : "derives"
    PATIENTS ||--o{ LIFETIME_EVIDENCE : "cites"
    FEVER_EPISODES {
        text title "encrypted"
        date first_documented_on
        int documented_fever_days
        string date_certainty
        string origin
        string verification_status
        string import_key
    }
    ANTIBIOTIC_COURSES {
        text generic_name "encrypted"
        string drug_class
        date prescribed_on
        date actual_start
        date actual_stop
        string administration_status
        string origin
        string verification_status
    }
    ANTIBIOTIC_ADMINISTRATIONS {
        date administered_on
        string source
        string idempotency_key
    }
    LIFETIME_EVIDENCE {
        string entity
        int entity_id
        string source_ref
        text quoted_text "encrypted"
        string certainty
    }
Loading

Notifications, audit and revisions

  • reminders has a unique key, a kind (medicine, vaccine, or the appointment's kind), an item_id, due_at, status and snoozed_until.
  • notifications has a unique dedupe_key, a channel (in_app or a push channel per enabled device), status, attempts, next_attempt_at, receipt_id and error_code. Acknowledging a notification is never an administration record.
  • push_devices stores the token encrypted plus a token_hash used for lookups.
  • audit records user_id, patient_id, action, entity, entity_id and at for every write.
  • record_revisions records entity, entity_id and an encrypted snapshot_json of the previous values, written by history.remember() in the same transaction as the change.
  • Operational tables: rate_windows (hashed key, window start, count) and worker_heartbeat (single row).

Encrypted columns

EncryptedText (api/crypto.py) stores 'enc:' + Fernet(value) and decrypts on read. It covers names, titles, instructions, doses, routes, prescriber and facility names, lab values, units, organisms, susceptibility text, notes, symptoms, JSON snapshots, review payloads, share scopes, camera URLs and credentials, and push tokens. documents.extracted_cipher is plain Text that holds a Fernet token the code encrypts explicitly.

These columns are not encrypted: dates, timestamps, ids, statuses, categories, MIME types, vaccines.dose_label, medicines.schedule_times, numeric vitals and fluid amounts, and stool colour or consistency enums. The type's own docstring says that dates and ids still need disk encryption.

Provenance columns

Column Meaning
source_document_id FK to documents, ON DELETE SET NULL. It is mandatory on treatment_administrations and required before a medicine can be confirmed.
source_page 1 to 35. safe_doc() rejects a page beyond documents.page_count (422) and a document from another profile (400).
documents.source_ref, import_batch The import bundle's own page id (pattern [A-Za-z0-9_.-]) and batch label. A duplicate source_ref per profile gets 409.
origin clinician_document or parent_report on lifetime rows, so parent-entered and clinician-documented records stay distinguishable.
verification_status Defaults to needs_verification. An edit resets it, and verified_by/verified_at record the guardian.
import_key Unique per lifetime row, which makes transcription imports idempotent.
lifetime_evidence.quoted_text, certainty The quoted text and certainty for each metric's evidence.
documents.extraction_status not_extracted, then needs_review, then reviewed. OCR-extracted ≠ clinically verified.

Migration history

Revision Adds
0001 legacy Adopts the starter tables (users, sessions, patients, documents, medicines, medicine_revisions, medication_events, hospital_visits, vaccines, observations, audit) without data loss.
0002 care platform caregiver_access, invitations, extraction_reviews, admissions, treatment_administrations, episodes, growth, reminders, push_devices, notifications, worker_heartbeat, rate_windows, plus the missing columns. Encrypts legacy clinical text in place and converts encrypted columns to TEXT on PostgreSQL.
0003 record revisions record_revisions
0004 import provenance documents.source_ref, import_batch, notes; medicines.course_type
0005 lifetime health fever_episodes, antibiotic_courses, parent_health_entries, antibiotic_administrations, lifetime_evidence
0006 share packages share_packages, share_access_log
0007 daily care and camera care_days, care_settings, cameras, camera_observations, stool_entries, stool_photos, fluid_entries, diaper_entries, medication_administrations, symptom_entries, activity_entries, sleep_entries
0008 stool intake Capture-time provenance on stool_photos; time_source, ai_suggestion_json, parent_values_json on stool_entries; the stool_intake table
0009 family profiles and vitals patients.sex, relationship, modules_json; vitals_entries
0010 password reset password_reset_tokens (hashed, single use, expiring)

The downgrades for 0001 to 0008 raise errors ("restore an encrypted backup"); only 0009 and 0010 can be dropped. Startup migration and the encrypted pre-migration snapshot are described in Backend Architecture.

Implementation

api/models.py, api/care_models.py, api/lifetime_models.py, api/share_models.py, api/vitals_models.py, api/password_reset.py, api/crypto.py, api/history.py, migrations/versions/*

Related pages

Encryption and Private Storage · Medicines and Administration Model · Vaccination Model · Illness History and Lifetime Health · Daily Health and Care Calendar · Family Profile Isolation

HealthBook AI

Overview

Platform

Identity and access

Medical records

Daily care

Output and sharing

Security

Engineering

Repository · Portfolio

Clone this wiki locally