MedLens stores everything in a local SQLite database (expo-sqlite). All dates are ISO 8601 strings and all primary keys are UUIDs generated by expo-crypto. The schema is built and evolved by a migration runner that executes sequential migrations inside an exclusive transaction against a version table.
For how this layer is consumed, see Architecture; for how records are produced, see OCR Pipeline.
Tables
- patients — the core record; the schema supports tracking multiple family members.
- photos — source images, decoupled so that any record can optionally link back to the artifact it came from.
- medications — the prescription or standing order (what is prescribed).
- med_logs — individual administrations (what was actually given).
- vitals — vital-sign readings, with a secondary value column for blood pressure’s diastolic.
- lab_panels — a group of tests from a single blood draw.
- lab_results — individual test results within a panel, with reference range and flag.
- events — free-form timeline entries (visits, symptoms, procedures, notes).
- schema_version — migration bookkeeping.
Performance indexes cover the common access patterns: photos and records by patient and time, vitals by patient and type, lab results by test name across panels, and medications by patient and status.
Key schema decisions
- Photos are decoupled. Records link back to a source photo optionally, via a nullable
photo_id. One photo can produce several records — an IV bag label might yield a medication and a vital. - Panels contain results. A panel (for example a CBC) holds many results (WBC, RBC, hemoglobin, and so on). Trend queries scan across panels by test name; single-report views show a whole panel together.
- Medications are separate from their logs. The prescription and the actual administrations are different tables, because tracking adherence both in hospital and at home depends on the distinction.
- Recorded time is separate from created time. Every record stores when the event medically occurred as well as when it was entered. You can enter yesterday’s vitals today, and charts plot them at the correct clinical time.
- Photo paths are relative. The database stores paths relative to the document directory, so backups and device migrations stay portable. Paths resolve to absolute at read time.
Patient-delete invariants
Deleting a patient is the most dangerous operation in the schema, and it is deliberately not a single DELETE. Most patient-scoped child tables cascade on delete, which makes a raw delete look safe — but two foreign keys intentionally remain non-cascading and will reject a raw delete:
- Medication administration history must not vanish silently. The link from a medication to a patient, and from a log to its medication, are both non-cascading so that a patient delete fails loudly at the patient row rather than quietly erasing adherence history partway through a cascade.
- Pending outbound sync items block the delete on purpose. If there is unsent shared data, the constraint error is meant to bubble all the way to the UI as a “you have pending items” warning, forcing the user to resolve it first.
The only safe path to remove a patient is the patient repository’s delete(), which performs an ordered sweep inside a single exclusive transaction. Any new repository, migration, or debug snippet that removes a patient must go through that method or replicate the same ordered sweep.
Constants and reference data
The app ships a dictionary of common lab tests (CBC, metabolic panel, and tumor markers) with canonical units and reference ranges. It drives autocomplete during manual entry and validation during extraction, and it reduces OCR false positives by cross-referencing extracted test names against known tests. Vital units, medication routes, and event categories are likewise defined as shared constants so entry, display, and extraction all agree on the same vocabulary.