Skip to content

Database overview (internal)

This page gives an internal overview of the SQLite database used inside a .pemr bundle.

It is intended for advanced users and developers who need to understand how CareFlow Kids and CareView Family store structured medical data.

The goal is not to document every implementation detail exhaustively, but to explain the main data domains, how they relate to each other, and which tables are most important.

General database characteristics

The database is a SQLite file stored as:

db.sqlite

It is used as the main structured storage layer for:

  • patient identity
  • visits
  • growth data
  • developmental data
  • AI inputs
  • attached-record references

Key technical properties include:

  • foreign keys enabled
  • WAL journal mode
  • schema versioning via PRAGMA user_version

This allows the bundle to remain portable while still supporting migrations and structured relationships between tables.

Main data domains

At a high level, the database is organized around the following domains:

flowchart TD
    P[patients]
    PERI[perinatal_history]
    PMH[past_medical_history]
    E[episodes<br>sick visits]
    W[well_visits]
    V[vitals]
    MG[manual_growth]
    WM[well_visit_milestones]
    WGE[well_visit_growth_eval]
    ADD[visit_addenda]
    AI[ai_inputs<br>well_ai_inputs]
    VAC[vaccinations]

    P --> PERI
    P --> PMH
    P --> E
    P --> W
    P --> V
    P --> MG
    P --> VAC
    W --> WM
    W --> WGE
    E --> AI
    W --> AI
    E --> ADD
    W --> ADD

The patient is the center of the model, with sick visits, well visits, growth data, history, and documents all attached to that patient.

Core identity tables

patients

This is the central patient table.

It stores:

  • first name
  • last name
  • date of birth
  • sex
  • MRN
  • vaccination status
  • parent notes
  • alias fields

This table defines the core identity record used throughout the bundle.

users

Stores clinician users.

At the moment, this is relatively simple and mainly supports attribution of episodes and visits.

Patient background tables

perinatal_history

Stores birth and early neonatal information such as:

  • pregnancy risks
  • birth mode
  • gestational age
  • NICU stay
  • birth anthropometrics
  • maternity discharge information
  • screening results

This table is important because some of this information may later be used in:

  • growth interpretation
  • AI input
  • clinical summaries

past_medical_history

Stores key background medical history, such as:

  • asthma
  • otitis
  • UTI
  • allergies
  • other history details

This supports longitudinal context for both clinician review and AI/guideline interpretation.

Sick visit tables

episodes

This is the main table for sick visits.

It stores a very large amount of structured visit information, including:

  • chief complaint
  • HPI fields
  • visit mode (in_person / telemedicine)
  • physical examination findings
  • telemedicine-specific fields
  • problem listing
  • investigations
  • diagnosis
  • ICD-10
  • medications
  • anticipatory guidance
  • AI-related notes

It also includes soft-delete fields:

  • is_deleted
  • deleted_at
  • deleted_reason

This allows visits to be hidden from normal UI/export logic without necessarily being permanently erased.

vitals

Stores vitals linked to a patient and optionally to an episode.

Typical fields include:

  • weight
  • height
  • head circumference
  • temperature
  • HR
  • RR
  • SpO₂
  • blood pressure
  • timestamp

Multiple vitals entries can exist for the same episode.

ai_inputs

Stores AI request/response history linked to sick visits.

This includes:

  • model
  • prompt
  • response
  • creation time

This is useful for traceability of AI usage.

Well visit tables

well_visits

This is the main table for well visits.

It stores preventive visit data such as:

  • visit type / age milestone
  • feeding and sleep
  • physical exam findings
  • developmental screening
  • M-CHAT / developmental test scores
  • conclusions and anticipatory guidance
  • snapshot problem listing
  • growth-related snapshot fields

Like episodes, it also supports soft-delete fields.

well_visit_milestones

Stores structured milestone results attached to a well visit.

Each row contains:

  • milestone code
  • label
  • status (achieved, not yet, uncertain)
  • optional note

This supports age-specific developmental tracking.

well_visit_growth_eval

Stores precomputed growth evaluation results for a well visit.

Instead of storing only final rendered text, it stores token payloads such as:

  • problem_tokens_json
  • measurement_tokens_json

This supports later rendering in other contexts, including the patient viewer.

well_ai_inputs

Equivalent of ai_inputs for well visits.

Growth-related tables and view

manual_growth

Stores manually entered growth measurements.

Typical fields:

  • patient
  • recorded date
  • weight
  • height
  • head circumference
  • source

This table is important because growth measurements may exist independently of any one visit.

growth_unified (view)

This is a database view, not a table.

It combines growth information from multiple sources into a single logical growth stream:

  • manual growth entries
  • birth anthropometrics from perinatal_history
  • discharge weight from perinatal_history

This allows the application to build a more complete growth history without duplicating raw data.

Addenda and follow-up

visit_addenda

Stores post-visit addenda.

An addendum can belong to either:

  • one sick visit (episode_id)
  • one well visit (well_visit_id)

but never both at once.

This table allows follow-up information to be attached later while preserving the original visit record.

Indexes and performance

The schema defines several indexes to improve lookup performance, for example:

  • patients by MRN
  • vitals by patient and time
  • episodes / well visits by patient
  • addenda by parent visit and time

These indexes help keep UI access responsive even as the bundle grows.

Migrations and schema evolution

The schema uses:

PRAGMA user_version = 3;

This allows the application to know which schema version a database uses.

Migration helper ALTER TABLE statements are also present to add newer fields safely to older databases.

This is important for bundle portability across app versions.

Important note

This page is an overview, not a replacement for the actual schema.

For development or debugging work, the SQLite schema remains the source of truth.

Also, the database inside a .pemr bundle should not be manually edited, because this may:

  • break encryption / integrity validation
  • create incompatible state
  • damage the medical record