Format specification and data dictionary for Electronic Health Information (EHI) exports produced by subQdocs, per ONC 21st Century Cures Act certification criterion §170.315(b)(10).
Schema version: 1.0.0Data files: 46Categories: 11Format: ZIP + JSON
Scope
Electronic Health Information in the HIPAA Designated Record Set stored by subQdocs. See the published data dictionary for the full entity/field inventory.
An export is one of two kinds: a single-patient export (one patient’s complete record set) or a patient-population export (every patient in the organization). Soft-deleted patients and visits are excluded, and children of a soft-deleted parent are never exported.
Access. EHI export is gated and logged independently of report viewing. Single-patient exports can be requested by the Practice Manager, Administrator, Organization Admin, and Biller roles; population (whole-organization) exports are restricted to Practice Manager, Administrator, and Organization Admin. Every export request, download-link issuance, and denied attempt is written to an append-only audit trail.
Archive structure
Each export is a single ZIP archive. Every entity is written as a UTF-8 JSON file containing an array of objects (one object per record). Binary attachments are included as-is under attachments/; documents referenced by other record types (patient photos and ID documents, lab requisition and result PDFs, oncology treatment files, billing attachments, insurance-card images, claim-form PDFs) are included under files/<entity>/. A manifest.json describing the export is written last.
ehi-export-<exportId>.zip
├── patients.json ← one JSON array per entity (see Data dictionary)
├── patient-visits.json
├── ... (46 entity files total)
├── attachments/ ← original binary files
│ ├── <id>-<filename>
│ └── ...
├── attachments.json ← metadata for every attachment (sha256, byteSize)
├── files/ ← documents referenced by other record types
│ ├── <entity>/<id>-<field>-<filename>
│ └── ...
├── files.json ← metadata for every file under files/ (sha256, byteSize)
└── manifest.json ← export summary, entity counts, warnings & errors
Reading the data files. Each *.json file is a JSON array. Fields are exactly those listed in the data dictionary below; null is used for absent values. Timestamps are ISO-8601 strings. Numeric primary keys are integers; business identifiers (e.g. MRN-style ids) are strings. The manifest.json entityCounts map gives the exact row count for every entity file, so completeness can be verified.
manifest.json
The manifest is the machine-readable summary of the export.
| Field |
Type |
Description |
exportId • |
string |
Unique id of this export job. |
exportType • |
string |
Either single-patient or patient-population (whole organization). |
generatedAt • |
string (date-time) |
ISO-8601 timestamp when the archive was generated. |
schemaVersion • |
string |
Version of this export format package (ZIP layout + entity schemas + manifest). |
productName • |
string |
Source product name (subQdocs). |
productVersion • |
string |
subQdocs application version that produced the export. |
organizationId • |
string |
Identifier of the exporting organization. |
patientId • |
string | null |
The single patient exported, or null for a population export. |
ehiScopeDescription • |
string |
Plain-language description of the EHI / Designated Record Set covered. |
documentationUrl • |
string (uri) |
Link to this published data dictionary. |
entityCounts • |
object |
Map of entity key to the exact number of rows written for that entity. |
attachmentCount • |
integer |
Number of binary attachment files included under attachments/. |
fileCount • |
integer |
Number of binaries included under files/ (documents referenced by other record types). |
warnings • |
array |
Non-fatal notes (e.g. an attachment stored as an external pointer, not fetched). |
errors • |
array |
Item-level failures (e.g. a referenced attachment file was missing or inaccessible). |
• required field. Each entry in warnings / errors is an object with code, message, and optionally entity and id.
Attachments
Every in-scope attachment’s binary is included under attachments/<id>-<sanitized-filename>, and its metadata (including a sha256 checksum and byteSize) is recorded in attachments.json. Three cases are recorded rather than silently dropped:
| External pointer |
A file stored as an external URL (not in subQdocs storage) is not fetched; its metadata is included with fileIncluded: false and a warning (ATTACHMENT_EXTERNAL_POINTER). The recorded storedRef is the URL’s origin and path only — query strings (which may carry third-party signatures) are stripped. |
| Missing / inaccessible |
A referenced file that cannot be read is recorded with fileIncluded: false and an error (ATTACHMENT_MISSING). |
| Multi-patient fax copy |
An unsplit full copy of a multi-patient fax co-mingles other patients’ information, so its binary is never exported; the row is recorded with fileIncluded: false and a warning (ATTACHMENT_MULTI_PATIENT). The patient’s own pages are exported via their properly split attachment rows. For the same reason, efax records (efaxes / e-fax-data) are only included when reachable through a properly split attachment. |
Files
Documents referenced by other record types are included under files/<entity>/<id>-<field>-<sanitized-filename> and indexed in files.json (entity, record id, source field, sha256, byteSize). Covered sources: patient profile photos and ID documents, lab requisition and label PDFs (lab-orders), lab result reports (order-results), lab order files, oncology treatment files, billing attachments, insurance-card images (policy-card-snaps), and generated claim-form PDFs. The same three recorded cases apply, with codes FILE_EXTERNAL_POINTER and FILE_MISSING. Two deliberate exclusions: visit audio recordings are not part of the export (the transcript content itself is, via patient-transcript.json), and the full fax PDF is not exported as a whole (see the multi-patient note above).
Data dictionary
The 46 entity files, organized into 11 categories. Each file is a JSON array of the record type below. The set of files here is the Designated Record Set covered by the export.
- Patient Demographics (1)
- Visits & Encounters (4)
- Clinical Notes & Transcripts (6)
- Labs (5)
- Medications & Prescriptions (3)
- Clinical History (5)
- Oncology (4)
- Consents (2)
- Billing (8)
- Insurance & Administrative (4)
- Files & Referrals (4)
Patient Demographics 1 file
Core patient identity and demographic record.
patients.json entity key patient · 61 fields
| Field |
Type |
Description |
third_party_id |
string | null |
External EMA/third-party patient identifier; part of unique index uniq_third_party_org and used as sourceKey by Condition. EHI identifier. |
id |
integer |
Primary key (auto-increment); the patient identifier used for scoping and child FKs. |
patient_id |
string | null |
Human/business patient identifier (MRN-style). EHI identifier. |
organization_id |
integer | null |
Tenant/org scope FK (part of uniq_third_party_org). Scoping metadata; identifies the patient’s org. |
first_name |
string |
Demographic name. |
last_name |
string |
Demographic name. |
preferred_name |
string | null |
Demographic name. |
suffix |
string | null |
Name suffix (Jr., Sr., III). Demographic. |
prefix |
string | null |
Name prefix (Mr, Ms, Mrs). Demographic. |
preferred_contact_method |
string | null |
Patient contact preference. Demographic/contact. |
middle_name |
string | null |
Demographic name. |
date_of_birth |
string | null (date) |
Date of birth (custom setter normalizes to UTC YYYY-MM-DD). Core demographic. |
email |
string | null |
Contact email. Demographic/contact. |
gender |
string | null |
Administrative gender. Demographic. |
age |
integer | null |
Patient age. Demographic. |
appointment_time |
string | null (date-time) |
Appointment time associated with patient. Clinical/scheduling metadata. |
address |
string | null |
Patient address (free text). Demographic/contact. |
contact_no |
string | null |
Contact phone. Demographic/contact. |
is_detailed_message_ok |
boolean |
Contact preference (OK to leave detailed voicemail/messages). Patient communication preference. |
street_address |
string | null |
Street address. Demographic/contact. |
city |
string | null |
Address city. Demographic/contact. |
state |
string | null |
Address state. Demographic/contact. |
state_code |
string | null |
Address state code. Demographic/contact. |
country |
string | null |
Address country. Demographic/contact. |
zipcode |
string | null |
Address ZIP/postal code. Demographic/contact. |
home_phone |
string | null |
Home phone. Demographic/contact. |
cellphone |
string | null |
Cell phone. Demographic/contact. |
visit_history |
string | null |
Free-text visit history. Clinical content. |
optum_person_id |
string | null |
External Optum person identifier. EHI identifier. |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt). Metadata, not clinical content. |
updated_at |
string (date-time) |
Record update timestamp (@UpdatedAt). Metadata, not clinical content. |
created_by |
integer | null |
Audit FK -> User. Metadata, not clinical content. |
updated_by |
integer | null |
Audit FK -> User. Metadata, not clinical content. |
third_party_sync_date |
string | null (date-time) |
EMA/third-party sync timestamp. Integration/operational metadata; retained for provenance. |
data_source |
string |
Record provenance (local vs third_party). Operational/provenance metadata; retained for DRS completeness. |
marital_status |
string | null |
Marital status. Demographic. |
is_deceased |
boolean | null |
Deceased indicator. Clinical demographic. |
is_active |
boolean |
Patient active status (from EMA FHIR Patient.active). Patient status; operational-leaning metadata but retained. |
third_party_last_updated |
string | null (date-time) |
Third-party source last-updated timestamp. Integration/operational metadata; retained for provenance. |
identifiers |
— |
JSON (JSONB) |
existing_patient |
boolean |
Existing vs new patient flag. Operational workflow flag; retained for DRS completeness, candidate for reviewer downgrade. |
is_self_guarantor |
boolean |
Adult patient is own financial guarantor. Billing/financial record attribute. |
default_office_location_id |
integer | null |
FK -> OfficeLocation; patient’s default office location. Care/administrative metadata. |
race |
string | null |
Race. Demographic. |
ethnicity |
string | null |
Ethnicity. Demographic. |
gender_identity |
string | null |
Gender identity. Demographic. |
sexual_orientation |
string | null |
Sexual orientation. Demographic/clinical. |
secondary_email |
string | null |
Secondary contact email. Demographic/contact. |
birth_city |
string | null |
Birth city. Demographic. |
birth_state |
string | null |
Birth state. Demographic. |
birth_country |
string | null |
Birth country. Demographic. |
birth_zipcode |
string | null |
Birth ZIP/postal code. Demographic. |
phi_authorizations |
— |
JSON (JSONB) |
ssn |
string | null |
Social Security Number. Sensitive PII but a core demographic identifier within the DRS; not a secret/token. Handle with care on export. |
signature_data |
string | null |
Electronic signature payload (inline base64 / data URL). Consent content; needs binary handling but stored inline, not a file path. |
signature_signed_at |
string | null (date-time) |
Timestamp the electronic signature was signed. Consent metadata. |
electronic_signature_signer |
string | null |
Who signed intake consent (self | guardian). Consent metadata. |
electronic_signature_guardian_name |
string | null |
Guardian name on consent. Consent content. |
electronic_signature_guardian_relationship |
string | null |
Guardian relationship on consent. Consent content. |
reviewed_at |
string | null (date-time) |
Latest medical-history review timestamp (patient-level). Clinical metadata. |
reviewed_by |
integer | null |
Audit FK -> User (who reviewed). Metadata, not clinical content. |
Visits & Encounters 4 files
Visits, encounters, vitals, and visit-status history.
patient-visits.json entity key patient_visits · 34 fields
| Field |
Type |
Description |
third_party_id |
string | null |
External EMA visit identifier; EHI cross-reference metadata |
id |
integer |
Primary key of the visit |
patient_id |
integer |
Patient FK; primary scope key |
room_no_id |
integer | null |
FK to org_rooms; visit location metadata |
visit_status |
string | null |
Clinical/workflow visit status; drives note finalization gate |
physical_status |
string |
Front-desk physical presence status; encounter metadata |
visit_date |
string (date-time) |
Date of encounter; core EHI |
visit_time |
string |
Scheduled time of encounter |
end_time |
string | null |
Encounter end time |
visit_type |
string | null |
Free-text visit type; clinical context |
visit_notes |
string | null |
Clinical/scheduling notes for the visit |
visit_details |
— |
JSON (JSONB) |
third_party_visit_status |
string | null |
External EMA appointment status mirror; sync metadata |
doctor_id |
integer | null |
Rendering provider FK; metadata |
organization_id |
integer | null |
Tenant/org FK; primary org scope key (part of unique index uniq_third_party_vist_org) |
medical_assistant_id |
integer | null |
MA FK; metadata |
visit_type_id |
integer | null |
FK to visit_type catalog; clinical context |
payment_method |
string | null |
Payment method for visit; billing-related EHI |
reportable_reason_for_visit |
string | null |
Reason-for-visit category; clinical/reporting content |
office_location_id |
integer | null |
FK to office_location (service location); metadata |
created_at |
string (date-time) |
Record creation timestamp; metadata |
updated_at |
string (date-time) |
Record update timestamp; metadata |
third_party_sync_date |
string | null (date-time) |
Last EMA sync timestamp; provenance metadata |
data_source |
string |
Record provenance (local vs EMA); EHI metadata |
third_party_last_updated |
string | null (date-time) |
Timestamp of last external update; provenance metadata |
finalized_at |
string | null (date-time) |
Note finalization timestamp; clinically significant |
finalized_by |
integer | null |
Audit FK (user who finalized); metadata not clinical content |
reviewed_at |
string | null (date-time) |
Medical-history ‘mark as reviewed’ timestamp; metadata |
reviewed_by |
integer | null |
Audit FK (user who reviewed); metadata not clinical content |
sms_confirmed_at |
string | null (date-time) |
Patient SMS confirmation timestamp; appointment metadata |
source_schedule |
string |
How the visit was booked; metadata |
approved_at |
string | null (date-time) |
Front-desk approval timestamp for self-scheduled visit; metadata |
approved_by |
integer | null |
Audit FK (user who approved); metadata not clinical content |
patient_policy_id |
integer | null |
FK to patient_policies (insurance policy chosen for visit); EHI |
encounters.json entity key encounters · 14 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
encounter_id |
string |
External/FHIR encounter identifier; EHI cross-reference |
patient_id |
integer |
Patient FK; primary scope key |
doctor_id |
integer |
Provider FK; metadata |
appointment_id |
string |
External appointment id; also links to patient_visits.id |
location_third_id |
string |
External location identifier; encounter metadata |
status |
string |
FHIR encounter status; clinical workflow state |
type |
string |
FHIR encounter type; clinical classification |
class |
string |
FHIR encounter class (ambulatory/inpatient/etc.); clinical classification |
organization_id |
integer | null |
Tenant/org FK; primary org scope key |
start_time |
string (date-time) |
Encounter start; core EHI |
end_time |
string | null (date-time) |
Encounter end; core EHI |
createdAt |
string (date-time) |
Sequelize-managed creation timestamp (not declared in model); metadata — verify DB casing |
updatedAt |
string (date-time) |
Sequelize-managed update timestamp (not declared in model); metadata — verify DB casing |
visit-vitals.json entity key visit_vitals · 15 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
patient_id |
integer |
Patient FK; primary scope key |
visit_id |
integer | null |
FK to patient_visits; links vitals to encounter (nullable = standalone vitals allowed) |
recorded_at |
string (date-time) |
When vitals were recorded; clinical timestamp |
height |
string | null |
Vital sign: height; clinical EHI |
weight |
number | null |
Vital sign: weight; clinical EHI |
bmi |
number | null |
Vital sign: BMI; clinical EHI |
heart_rate |
integer | null |
Vital sign: heart rate; clinical EHI |
temperature |
number | null |
Vital sign: temperature; clinical EHI |
blood_pressure |
string | null |
Vital sign: blood pressure; clinical EHI |
respiratory_rate |
integer | null |
Vital sign: respiratory rate; clinical EHI |
oxygen_saturation |
integer | null |
Vital sign: SpO2; clinical EHI |
measurement_units |
— |
JSON (JSONB) |
created_at |
string (date-time) |
Record creation timestamp; metadata |
updated_at |
string (date-time) |
Record update timestamp; metadata |
visit-status-management.json entity key visit_status_management · 8 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
visit_id |
integer |
FK to patient_visits; join key for patient scoping |
organization_id |
integer |
Tenant/org FK; primary org scope key |
status |
string |
Recorded visit status at this point in the trail (appointment lifecycle: Scheduled, Checked In, In Room, No Show, Cancelled, Finalized, …). Internal recording/AI-pipeline transitions are excluded from the exported history. |
status_updated_at |
string (date-time) |
When this status transition occurred; clinical/audit timestamp |
is_current |
boolean |
Versioning marker for the active status row; metadata (all-versions export retains history) |
created_at |
string (date-time) |
Record creation timestamp; metadata |
updated_at |
string (date-time) |
Record update timestamp; metadata |
Clinical Notes & Transcripts 6 files
Finalized notes, amendments, compositions, and visit transcripts.
full-note.json entity key full_note · 12 fields
| Field |
Type |
Description |
id |
integer |
PK identifier (metadata) |
patient_id |
integer |
FK patients; scope key |
visit_id |
integer |
FK patient_visits; scope + finalized gate key |
visit_date |
string (date-time) |
clinical encounter date |
full_note_details |
— |
JSON (JSON) |
ma_summary |
string | null |
medical-assistant summary content (finalized carrier) |
version |
integer |
version number; default 1 |
is_current |
boolean |
current-version flag; drives current-only filter |
parent_id |
integer | null |
FK self (prior version pointer) |
version_type |
string | null |
version type discriminator |
created_at |
string (date-time) |
audit timestamp (metadata) |
updated_at |
string (date-time) |
audit timestamp (metadata) |
chart-notes.json entity key chart_notes · 14 fields
| Field |
Type |
Description |
id |
integer |
PK identifier (metadata) |
organization_id |
integer |
FK organizations; org scope key |
patient_id |
integer |
FK patients; scope key |
visit_id |
integer | null |
FK patient_visits; optional linkage |
note_type |
string | null |
note classification value |
title |
string | null |
note title content |
communication_type |
string | null |
communication classification value |
add_to |
string | null |
note routing/target content |
assign_to |
integer | null |
FK users (assignee); workflow metadata |
note |
string |
chart note body (core EHI) |
created_by |
integer |
audit FK (user/author); metadata, not clinical content |
archived_at |
string | null (date-time) |
archive timestamp; operational metadata (not soft-delete) |
created_at |
string (date-time) |
audit timestamp (metadata) |
updated_at |
string (date-time) |
audit timestamp (metadata) |
patient-visit-amend-notes.json entity key patient_visit_amend_notes · 14 fields
| Field |
Type |
Description |
id |
integer |
PK identifier (metadata) |
visit_id |
integer |
FK patient_visits; only scope key present |
amendment_number |
integer |
amendment sequence number |
amendment_content |
string |
amendment text (core EHI) |
amendment_status |
string |
amendment status; default DRAFT; drives finalized-only filter |
original_note_date |
string (date-time) |
date of the note being amended |
amendment_date |
string (date-time) |
date amendment was made |
provider_id |
integer |
FK users (authoring provider); attribution metadata |
signed_by |
integer | null |
FK users (signer); attestation metadata |
signed_at |
string | null (date-time) |
signature timestamp; attestation metadata |
created_by |
integer | null |
audit FK (user); metadata, not clinical content |
updated_by |
integer | null |
audit FK (user); metadata, not clinical content |
created_at |
string (date-time) |
audit timestamp (metadata) |
updated_at |
string (date-time) |
audit timestamp (metadata) |
compositions.json entity key compositions · 11 fields
| Field |
Type |
Description |
id |
integer |
PK identifier (metadata) |
composition_id |
integer |
external/logical composition identifier |
patient_id |
integer |
patient identifier; scope key (note: no FK decorator/association) |
doctor_id |
integer |
authoring doctor identifier; attribution metadata |
encounter_id |
string |
encounter identifier (string, not an FK) |
treatment_title |
string |
treatment section title (clinical content) |
treatment_body |
string |
treatment section body (core EHI) |
instruction_title |
string |
instruction section title (clinical content) |
instruction_body |
string |
patient instruction body (core EHI) |
createdAt |
string (date-time) |
record creation timestamp (timestamps: true, camelCase column); metadata |
updatedAt |
string (date-time) |
record update timestamp (timestamps: true, camelCase column); metadata |
patient-transcript.json entity key patient_transcript · 8 fields
| Field |
Type |
Description |
id |
integer |
PK identifier (metadata) |
visit_id |
integer | null |
FK patient_visits; scope key (nullable) |
file_id |
integer | null |
FK transcript_file; links to source audio/file |
transcript_data |
— |
JSON (JSONB) |
cleaned_transcript |
— |
JSON (JSONB) |
merged_transcript |
— |
JSON (JSONB) |
created_at |
string (date-time) |
audit timestamp (metadata) |
updated_at |
string (date-time) |
audit timestamp (metadata) |
transcript-file.json entity key transcript_file · 9 fields
| Field |
Type |
Description |
id |
integer |
PK identifier (metadata) |
visit_id |
integer | null |
FK patient_visits; scope key (nullable) |
file_name |
string |
original file name (metadata) |
file_type |
string |
MIME/type of file (metadata) |
file_size |
integer |
file size in bytes (metadata) |
file_duration |
number | null |
audio duration (metadata) |
uploaded_at |
string | null (date-time) |
upload timestamp (metadata) |
created_at |
string (date-time) |
audit timestamp (metadata) |
updated_at |
string (date-time) |
audit timestamp (metadata) |
Labs 5 files
Lab orders, results, and lab-order files.
lab-orders.json entity key lab_orders · 42 fields
| Field |
Type |
Description |
id |
integer |
Primary key (autoincrement). |
optum_order_id |
string | null |
External Optum lab order identifier; order provenance. |
placer_order_number |
string | null |
HL7 placer order number; clinical order identifier. |
order_number |
string | null |
Unique human-facing order number. |
efax_id |
integer | null |
FK linkage to efax record (delivery of order/result); metadata. |
patient_id |
integer | null |
Patient scope key (FK to patient). |
visit_id |
integer | null |
FK to patient_visits; visit linkage for the order. |
request_date |
string | null (date-time) |
Clinical date the order was requested. |
expected_coll_date |
string | null (date-time) |
Expected specimen collection date; clinical. |
collection_date |
string | null (date-time) |
Specimen collection date; clinical. |
tests |
— |
JSON (JSONB) |
aoe_questions |
— |
JSON (JSONB) |
order_diagnosis |
— |
JSON (JSONB) |
facility |
string | null |
Performing/collection facility name. |
is_general_lab |
boolean |
Order-placement routing flag (shared General Lab flow); operational/provenance metadata, not clinical content. |
order_status |
string | null |
Order status; workflow field describing the order record. |
bill_type |
string | null |
Billing type for the order. |
order_type |
string | null |
Order type classifier. |
report_status |
string |
Report arrival status; workflow field for the record. |
lab_id |
integer | null |
FK to lab (performing lab catalog). |
slide_url |
string | null |
URL to pathology slide image; binary/attachment reference. |
status |
string | null |
Secondary status field for the order. |
result |
string | null |
Free-text lab/pathology result; clinical content. |
plan |
string | null |
Free-text clinical plan tied to the order/result. |
action |
string | null |
Free-text clinical action/follow-up. |
result_flag |
string | null |
Abnormal/critical result flag; clinical. |
patient_notified |
boolean |
Whether the patient was notified of results; record-of-communication metadata. |
location |
string | null |
Location associated with the order. |
procedure |
string | null |
Procedure descriptor; clinical. |
specimen_collected_date |
string | null (date-time) |
Date specimen was collected; clinical. |
comment |
string | null |
Free-text clinical comment on the order. |
ordering_provider_id |
integer | null |
FK to ordering provider (user); care-team metadata. |
organization_id |
integer | null |
Organization scope key (FK to organization). |
priority |
string |
Clinical order priority. |
reviewed_by |
integer | null |
FK to reviewing user; audit/review metadata (not clinical content). |
reviewed_at |
string | null (date-time) |
Timestamp the result was reviewed; review metadata. |
report_date |
string | null (date-time) |
Report date; clinical. |
is_urgent |
boolean | null |
Urgency flag; clinical priority indicator. |
is_unsolicited |
boolean |
Whether the result was unsolicited; order provenance. |
ema_service_request_id |
string | null |
External EMA (ModMed) service request identifier; integration linkage. |
created_at |
string (date-time) |
Row creation timestamp (@CreatedAt, field: created_at); record metadata. |
updated_at |
string (date-time) |
Row update timestamp (@UpdatedAt, field: updated_at); record metadata. |
lab-results.json entity key lab_results · 9 fields
| Field |
Type |
Description |
id |
integer |
Primary key (autoincrement). |
order_id |
integer |
FK to lab_orders; the transitive scope key. |
clinical_id |
integer | null |
External clinical result identifier; provenance. |
place_order_id |
integer | null |
Placer order identifier linkage. |
hl7 |
string | null |
Raw HL7 result message; clinical content (contains full result data). |
test_code |
string | null |
Test/LOINC code; clinical. |
test_name |
string | null |
Test name; clinical. |
created_at |
string (date-time) |
Row creation timestamp (@CreatedAt); record metadata. |
updated_at |
string (date-time) |
Row update timestamp (@UpdatedAt); record metadata. |
test-results.json entity key test_results · 13 fields
| Field |
Type |
Description |
id |
integer |
Primary key (autoincrement). |
lab_result_id |
integer |
FK to lab_results; the transitive scope key. |
observation_name |
string | null |
OBX observation/analyte name; clinical. |
obx_set_id |
string | null |
HL7 OBX set identifier; clinical ordering within result. |
result |
string | null |
Observation result value; clinical content. |
unit |
string | null |
Result unit of measure; clinical. |
limit |
string | null |
Reference range / limit for the observation; clinical. |
label |
string | null |
Result label/abnormal flag; clinical. |
date_performed |
string | null (date-time) |
Date the observation was performed; clinical. |
notes |
string | null |
Observation notes; clinical. |
status |
string | null |
Observation result status; clinical/workflow. |
created_at |
string (date-time) |
Row creation timestamp (@CreatedAt); record metadata. |
updated_at |
string (date-time) |
Row update timestamp (@UpdatedAt); record metadata. |
order-results.json entity key order_results · 12 fields
| Field |
Type |
Description |
id |
integer |
Primary key (autoincrement). |
order_id |
integer |
FK to lab_orders; primary transitive scope key. |
patient_id |
integer | null |
Direct (nullable) FK to patient; secondary scope key. |
lab_id |
integer | null |
FK to lab (performing lab). |
optum_doc_id |
string | null |
External Optum document identifier; provenance. |
optum_report_id |
integer | null |
External Optum report identifier; provenance. |
updated_on |
string | null (date-time) |
Report update/effective date; clinical. |
report_status |
string | null |
Report status; workflow field for the record. |
ema_diagnostic_report_id |
string | null |
External EMA diagnostic report identifier; integration linkage. |
report_type |
string | null |
Report type classifier; clinical/metadata. |
created_at |
string (date-time) |
Row creation timestamp (@CreatedAt); record metadata. |
updated_at |
string (date-time) |
Row update timestamp (@UpdatedAt); record metadata. |
lab-order-files.json entity key lab_order_files · 9 fields
| Field |
Type |
Description |
id |
integer |
Primary key (autoincrement). |
lab_orders_id |
integer |
FK to lab_orders; the transitive scope key. |
file_name |
string |
Original file name of the attachment. |
file_type |
string | null |
MIME/type of the attachment. |
file_size |
integer | null |
File size in bytes; attachment metadata. |
description |
string | null |
Free-text description of the attached file; may carry clinical context. |
uploaded_by |
integer | null |
FK to uploading user; audit metadata (not clinical content). |
created_at |
string (date-time) |
Row creation timestamp (@CreatedAt, field: created_at); record metadata. |
updated_at |
string (date-time) |
Row update timestamp (@UpdatedAt, field: updated_at); record metadata. |
Medications & Prescriptions 3 files
Prescriptions, medications, and pharmacies.
prescriptions.json entity key prescriptions · 36 fields
| Field |
Type |
Description |
id |
integer |
Primary key / record identifier. |
patient_id |
integer | null |
Patient scope key (FK -> patients). |
visit_id |
integer | null |
FK -> patient_visits; links rx to the encounter. |
organization_id |
integer | null |
Org scope key (FK -> organizations). |
doctor_id |
integer | null |
FK -> users (ordering doctor); authorship metadata, clinically meaningful. |
drug_fdb_id |
integer | null |
First Databank drug identifier; clinical drug reference. |
drug_name |
string | null |
Prescribed drug name; clinical content. |
sig |
string | null |
Directions for use (sig); clinical content. |
day_supply |
string | null |
Days supply; clinical content. |
quantity |
string | null |
Dispense quantity; clinical content. |
refill |
string | null |
Refills authorized; clinical content. |
comments |
string | null |
Free-text prescriber comments; clinical content. |
pharmacy_id |
string | null |
Destination pharmacy identifier; clinical/fulfillment content. |
pharmacy_name |
string | null |
Destination pharmacy name; fulfillment content. |
daw |
string | null |
Dispense-as-written indicator; clinical content. |
icd_code |
string | null |
Associated diagnosis (ICD) code; clinical content. |
units_of_measure |
string | null |
NCPDP quantity unit-of-measure code; clinical content. |
patient_wt |
string | null |
Patient weight captured at rx; clinical content. |
patient_ht |
string | null |
Patient height captured at rx; clinical content. |
patient_ht_uom |
string | null |
Height unit of measure; clinical content. |
patient_wt_uom |
string | null |
Weight unit of measure; clinical content. |
prescription_data |
— |
JSON (JSONB) |
parent_prescription_id |
integer | null |
Self-referential provenance/revision link; needed to reconstruct rx history. |
status |
string |
Prescription lifecycle status (default NEEDS_REVIEW); clinical/workflow content. |
searchaccount_id |
string | null |
External e-prescribe integration account id; metadata, kept include. |
third_party_id |
string | null |
External system rx identifier; metadata. |
prescriber_id |
integer | null |
FK -> users (prescriber); authorship metadata. |
identifier |
string | null |
External/business identifier; metadata. |
controlled_substances |
string | null |
Controlled-substance schedule/flag; clinical content. |
last_printed_at |
string | null (date-time) |
When rx was last printed; workflow/audit metadata. |
last_printed_by |
integer | null |
FK -> users; audit metadata (who printed). |
released_at |
string | null (date-time) |
When rx was released; workflow/audit metadata. |
cancelled_at |
string | null (date-time) |
When rx was cancelled; workflow/audit metadata. |
cancelled_by |
integer | null |
FK -> users; audit metadata (who cancelled). |
created_at |
string (date-time) |
Record creation timestamp; metadata. |
updated_at |
string (date-time) |
Record update timestamp; metadata. |
patient-medications.json entity key patient_medications · 15 fields
| Field |
Type |
Description |
id |
integer |
Primary key / record identifier. |
patient_id |
integer |
Patient scope key (FK -> patients, indexed). |
visit_id |
integer | null |
FK -> patient_visits; encounter link, enables transitive org scope. |
optum_drug_id |
string | null |
Optum drug identifier; clinical drug reference/metadata. |
third_party_id |
string | null |
External system identifier; metadata. |
medication_name |
string |
Medication name; clinical content. |
generic_name |
string | null |
Generic drug name; clinical content. |
dose_form |
string | null |
Dosage form; clinical content. |
route |
string | null |
Route of administration; clinical content. |
dosage |
string | null |
Dosage; clinical content. |
notes |
string | null |
Free-text medication notes; clinical content. |
created_by |
integer | null |
FK -> users; audit metadata (author), not clinical content. |
updated_by |
integer | null |
FK -> users; audit metadata (editor), not clinical content. |
created_at |
string (date-time) |
Record creation timestamp; metadata. |
updated_at |
string (date-time) |
Record update timestamp; metadata. |
patient-pharmacies.json entity key patient_pharmacies · 17 fields
| Field |
Type |
Description |
id |
integer |
Primary key / record identifier. |
patient_id |
integer |
Patient scope key (model patientId; FK -> patients, indexed). |
optum_pharmacy_id |
string | null |
Optum/NCPDP pharmacy id (model optumPharmacyId), leading zeros significant; identifier/metadata. |
npi |
integer | null |
Pharmacy NPI; identifier for the pharmacy. |
name |
string |
Pharmacy name; patient contact/fulfillment content. |
address_line1 |
string |
Pharmacy address line 1 (model addressLine1); contact content. |
address_line2 |
string | null |
Pharmacy address line 2 (model addressLine2); contact content. |
city |
string |
Pharmacy city; contact content. |
state |
string |
Pharmacy state; contact content. |
zip |
string |
Pharmacy postal code; contact content. |
phone |
string | null |
Pharmacy phone; contact content. |
fax |
string | null |
Pharmacy fax; contact content. |
is_primary |
boolean |
Primary-pharmacy indicator (model isPrimary, default false); patient preference, relevant. |
created_by |
integer | null |
FK -> users (model createdBy); audit metadata. |
updated_by |
integer | null |
FK -> users (model updatedBy); audit metadata. |
created_at |
string (date-time) |
Record creation timestamp (model createdAt); metadata. |
updated_at |
string (date-time) |
Record update timestamp (model updatedAt); metadata. |
Clinical History 5 files
Allergies, conditions, family history, medical history, and diagnosis timeline.
allergies.json entity key allergies · 15 fields
| Field |
Type |
Description |
id |
integer |
primary key identifier |
third_party_id |
string | null |
external/source-system record id |
third_party_patient_id |
string | null |
external/source-system patient id |
internal_patient_id |
integer | null |
patient FK; scope column |
visit_id |
integer | null |
visit FK; scope column |
clinical_status |
string |
clinical status of allergy |
allergen |
string | null |
clinical – allergen name |
allergen_code |
string | null |
clinical – coded allergen |
allergy_manifestation |
string | null |
clinical – manifestation |
on_set_date |
string | null (date-time) |
clinical – onset date |
severity |
string | null |
clinical severity |
reactions |
string | null |
clinical – array of reactions |
notes |
string | null |
clinical free-text notes |
created_at |
string (date-time) |
metadata timestamp |
updated_at |
string (date-time) |
metadata timestamp |
conditions.json entity key conditions · 6 fields
| Field |
Type |
Description |
id |
integer |
primary key identifier |
patient_id |
string | null |
patient FK (string-typed, no AllowNull decorator so nullable); scope column |
code |
string |
clinical – condition code |
value |
string |
clinical – condition value/description |
createdAt |
string (date-time) |
implicit timestamp metadata from timestamps:true (not declared in model) |
updatedAt |
string (date-time) |
implicit timestamp metadata from timestamps:true (not declared in model) |
family-histories.json entity key family_histories · 11 fields
| Field |
Type |
Description |
id |
integer |
primary key identifier |
third_party_id |
string |
external/source-system record id (unique) |
third_party_patient_id |
string | null |
external/source-system patient id |
internal_patient_id |
integer | null |
patient FK; scope column |
relationship_type |
string |
clinical – relative relationship |
condition_name |
string |
clinical – condition name |
condition_code |
string |
clinical – coded condition |
condition_status |
string |
clinical – condition status |
note |
string |
clinical free-text note |
created_at |
string (date-time) |
metadata timestamp |
updated_at |
string (date-time) |
metadata timestamp |
patient-medical-history.json entity key patient_medical_history · 42 fields
| Field |
Type |
Description |
id |
integer |
primary key identifier |
patient_id |
integer |
patient FK; scope column |
visit_id |
integer | null |
visit FK; scope column |
current_conditions |
— |
JSON (JSONB) |
past_surgeries |
— |
JSON (JSONB) |
skin_conditions |
— |
JSON (JSONB) |
wear_sunscreen |
boolean | null |
clinical – sun protection |
spf |
integer | null |
clinical – sunscreen SPF |
use_tanning_salon |
boolean | null |
clinical – tanning salon use |
family_history_melanoma |
— |
JSON (JSONB (RelationshipEnum[])) |
tobacco_usage |
string | null |
clinical – tobacco usage |
start_smoking |
string | null (date) |
clinical – smoking start date |
quit_smoking |
string | null (date) |
clinical – smoking quit date |
number_packs_day |
integer | null |
clinical – packs per day |
total_smoking_years |
integer | null |
clinical – total smoking years |
smoking_habits_notes |
string | null |
clinical free-text |
more_four_drinks_past_year |
integer | null |
clinical – alcohol screening |
alcohol_usage |
boolean | null |
clinical – alcohol use |
alcohol_usage_notes |
string | null |
clinical free-text |
recreational_drug_usage |
boolean | null |
clinical – drug use |
vaping_products_usage |
boolean | null |
clinical – vaping use |
smokeless_tobacco_usage |
string | null |
clinical – smokeless tobacco |
other_smokeless_tobacco_usage |
string | null |
clinical – other smokeless tobacco |
alcohol_binge_drink |
boolean | null |
clinical – binge drinking |
alcohol_additional_notes |
string | null |
clinical free-text |
drug_use_starting |
boolean | null |
clinical – drug use starting |
recreational_substances |
string | null |
clinical – substances |
smoking_frequency |
string | null |
clinical – smoking frequency |
quit_details |
string | null |
clinical free-text |
uses_e_cigarettes |
boolean | null |
clinical – e-cigarette use |
uses_chewing_tobacco |
boolean | null |
clinical – chewing tobacco use |
drug_notes |
string | null |
clinical free-text |
film_histories |
— |
JSON (JSONB) |
medical_conditions |
— |
JSON (JSONB) |
past_surgeries_extended |
— |
JSON (JSONB) |
immunizations |
— |
JSON (JSONB) |
family_history_entries |
— |
JSON (JSONB) |
alerts |
— |
JSON (JSONB) |
created_by |
integer | null |
audit FK to users (metadata, not clinical) |
updated_by |
integer | null |
audit FK to users (metadata, not clinical) |
created_at |
string (date-time) |
metadata timestamp |
updated_at |
string (date-time) |
metadata timestamp |
patient-diagnosis-timeline.json entity key patient_diagnosis_timeline · 11 fields
| Field |
Type |
Description |
id |
integer |
primary key identifier |
patient_id |
integer |
patient FK (unique); scope column |
diagnosis_timeline |
— |
JSON (JSON (default [])) |
chief_complaint_hpi |
— |
JSON (JSON) |
alerts |
— |
JSON (JSON) |
allergies |
— |
JSON (JSON) |
personal_note |
— |
JSON (JSON) |
past_prescriptions |
— |
JSON (JSON) |
past_diagnosis |
— |
JSON (JSON) |
created_at |
string (date-time) |
metadata timestamp |
updated_at |
string (date-time) |
metadata timestamp |
Oncology 4 files
Cancer logs, clinical and treatment details, and treatment files.
cancer-log.json entity key cancer_log · 7 fields
| Field |
Type |
Description |
id |
integer |
Primary key; parent identifier referenced by cancer_log_id on all child tables. |
order_id |
integer | null |
FK to orders; links this cancer log to the originating order. Clinical linkage/metadata. |
patient_id |
integer |
FK to patients; patient scope key and clinical linkage. |
visit_id |
integer | null |
FK to patient_visits; encounter linkage for the diagnosis. |
organization_id |
integer |
FK to organizations; tenant scope key. Metadata, not clinical content, but required for scoping. |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt). Metadata. |
updated_at |
string (date-time) |
Record update timestamp (@UpdatedAt). Metadata. |
cancer-clinical-details.json entity key cancer_clinical_details · 11 fields
| Field |
Type |
Description |
id |
integer |
Primary key; row identifier. |
cancer_log_id |
integer |
FK to cancer_log; parent linkage and the only path to patient scope. |
biopsy_date |
string | null (date-time) |
Clinical content: date of biopsy. |
diagnostic_date |
string | null (date-time) |
Clinical content: date of diagnosis. |
diagnosis |
string | null |
Clinical content: cancer diagnosis text. |
breslow_depth |
string | null |
Clinical content: melanoma Breslow depth measurement. |
ulceration |
string | null |
Clinical content: ulceration status/finding. |
comment |
string | null |
Clinical content: free-text clinical comment. |
organization_id |
integer |
FK to organizations; tenant scope key. Metadata used for scoping. |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt). Metadata. |
updated_at |
string (date-time) |
Record update timestamp (@UpdatedAt). Metadata. |
cancer-treatment-details.json entity key cancer_treatment_details · 10 fields
| Field |
Type |
Description |
id |
integer |
Primary key; row identifier. |
cancer_log_id |
integer |
FK to cancer_log; parent linkage and the only path to org + patient scope. |
treatment_status |
string | null |
Clinical content: treatment status. |
treatment_date |
string | null (date-time) |
Clinical content: date of treatment. |
treatment_location |
string | null |
Clinical content: location where treatment was performed. |
treatment_by |
integer | null |
FK to users; clinician who performed the treatment. Clinical attribution/metadata (reference to a user, not free clinical text). |
treatment_method |
string | null |
Clinical content: method/modality of treatment. |
refer_to_provider |
string | null |
Clinical content: provider the patient is referred to. |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt). Metadata. |
updated_at |
string (date-time) |
Record update timestamp (@UpdatedAt). Metadata. |
cancer-treatment-files.json entity key cancer_treatment_files · 8 fields
| Field |
Type |
Description |
id |
integer |
Primary key; row identifier. |
cancer_log_id |
integer |
FK to cancer_log; parent linkage and the only path to org + patient scope. |
file_name |
string |
Attachment metadata: original file name. |
file_type |
string |
Attachment metadata: MIME/file type. |
file_size |
string |
Attachment metadata: file size. |
updated_on |
string | null (date-time) |
Attachment metadata: application-managed last-updated timestamp (distinct from updated_at). |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt). Metadata. |
updated_at |
string (date-time) |
Record update timestamp (@UpdatedAt). Metadata. |
Consents 2 files
Patient- and visit-level consent signatures.
patient-consent-signatures.json entity key patient_consent_signatures · 15 fields
| Field |
Type |
Description |
id |
integer |
Primary key / row identifier (AutoIncrement). |
patient_id |
integer |
FK to patients; the patient scope key for this table. |
organization_consent_form_id |
integer | null |
FK to organization_consent_forms (which template was signed); identifies the consent — patient-level reference, include. Nullable for ad-hoc/deleted templates. |
consent_form_type |
string |
Type/category of the consent form signed; clinical-legal content. |
consent_scope |
string |
Scope of consent (default INTAKE); clinical-legal content. |
is_signed |
boolean |
Legal signing status of the consent. |
signed_at |
string | null (date-time) |
Timestamp the consent was signed; clinical-legal fact. |
form_title_snapshot |
string | null |
Point-in-time snapshot of the consent form title as presented to the patient. |
form_description_snapshot |
string | null |
Point-in-time snapshot of the form description shown to the patient. |
form_value_snapshot |
string | null |
Point-in-time snapshot of the form body/values presented at signing. |
attachment_id |
integer | null |
FK to attachments table holding the signed-document binary; drives attachment/binary export. |
created_by |
integer | null |
Audit FK to users (creator); metadata, not clinical content. |
updated_by |
integer | null |
Audit FK to users (last updater); metadata, not clinical content. |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt); metadata. |
updated_at |
string (date-time) |
Record last-update timestamp (@UpdatedAt); metadata. |
visit-consent-signatures.json entity key visit_consent_signatures · 22 fields
| Field |
Type |
Description |
id |
integer |
Primary key / row identifier (AutoIncrement). |
visit_id |
integer |
FK to patient_visits; visit scope key linking the consent to an encounter. |
patient_id |
integer |
FK to patients; the patient scope key for this table. |
organization_consent_form_id |
integer | null |
FK to organization_consent_forms (which template was signed); patient-level reference, include. Nullable for ad-hoc/deleted templates. |
consent_form_type |
string |
Type/category of the consent form; clinical-legal content. |
consent_scope |
string |
Scope of consent (default IN_ROOM); clinical-legal content. |
form_title_snapshot |
string | null |
Point-in-time snapshot of the consent form title presented at the visit. |
form_description_snapshot |
string | null |
Point-in-time snapshot of the form description shown to the patient. |
form_value_snapshot |
string | null |
Point-in-time snapshot of the form body/values presented at signing. |
patient_signature_data |
string | null |
Captured patient signature payload (image/vector data); PHI, core consent evidence. |
patient_signature_signed_at |
string | null (date-time) |
Timestamp the patient signed; clinical-legal fact. |
patient_signer_type |
string | null |
Who signed on the patient’s behalf (‘self’ | ‘guardian’); consent-legal content. |
patient_signature_guardian_name |
string | null |
Name of the signing guardian; PHI when guardian consents for the patient. |
patient_signature_guardian_relationship |
string | null |
Guardian’s relationship to the patient; consent-legal PHI. |
provider_id |
integer | null |
FK to users — provider who co-signed/witnessed; metadata FK, include. |
provider_signature_data |
string | null |
Captured provider signature payload; consent evidence. |
provider_signed_at |
string | null (date-time) |
Timestamp the provider signed; clinical-legal fact. |
attachment_id |
integer | null |
FK to attachments table holding the signed-document binary; drives attachment/binary export. |
created_by |
integer | null |
Audit FK to users (creator); metadata, not clinical content. |
updated_by |
integer | null |
Audit FK to users (last updater); metadata, not clinical content. |
created_at |
string (date-time) |
Record creation timestamp (@CreatedAt); metadata. |
updated_at |
string (date-time) |
Record last-update timestamp (@UpdatedAt); metadata. |
Billing 8 files
Bills, services, notes, attachments, claims, ledger transactions, balances, and charges.
bills.json entity key bills · 25 fields
| Field |
Type |
Description |
id |
integer |
PK |
billReadableId |
string |
Human-facing bill identifier (unique) |
externalId |
string | null |
External system bill id |
organizationId |
integer |
Org scope FK |
patientId |
integer |
Patient scope FK |
visitId |
integer | null |
FK to patient_visits (encounter) |
payerId |
string | null |
Payer identifier |
payerName |
string | null |
Payer name |
policyId |
integer | null |
FK to patient_policy |
primaryProviderId |
integer | null |
FK to user (rendering provider); metadata |
primaryBillerId |
integer | null |
FK to user (biller); metadata |
referringProvider |
string | null |
Referring provider name |
patientReferralId |
integer | null |
FK to patient_referral |
policyAuthorizationId |
integer | null |
FK to policy_authorization |
locationId |
integer | null |
FK to office_location |
dateOfService |
string (date) |
Clinical/financial DOS |
placeOfService |
string |
POS code (default ’11’) |
status |
string |
Bill lifecycle status |
totalCharges |
number |
Financial total |
balance |
number |
Outstanding balance |
notes |
string | null |
Free-text billing notes |
diagnoses |
— |
JSON (JSONB) |
createdBy |
integer |
Audit FK to user; metadata not clinical content |
createdAt |
string (date-time) |
Record timestamp; metadata |
updatedAt |
string (date-time) |
Record timestamp; metadata |
bill-services.json entity key bill_services · 18 fields
| Field |
Type |
Description |
id |
integer |
PK |
billId |
integer |
Parent bill FK (transitive scope) |
cptCode |
string | null |
CPT/HCPCS procedure code — clinical/financial |
visitCustomCodeId |
integer | null |
FK to visit_custom_code |
productId |
integer | null |
FK to product |
professionalServiceId |
integer | null |
FK to professional_service |
modifiers |
— |
JSON (JSONB) |
description |
string | null |
Line description |
isCustomCode |
boolean |
Flags custom vs standard code (declared AllowNull(false)) |
diagnosisPointers |
— |
JSON (JSONB) |
units |
integer |
Billed units |
unitCharge |
number |
Per-unit charge |
totalCharge |
number |
Line total |
status |
string |
Line posting status |
sortOrder |
integer | null |
Display ordering; operational metadata but retained |
taxAmount |
number | null |
Tax on line |
createdAt |
string (date-time) |
Record timestamp; metadata |
updatedAt |
string (date-time) |
Record timestamp; metadata |
bill-notes.json entity key bill_notes · 7 fields
| Field |
Type |
Description |
id |
integer |
PK |
billId |
integer |
Parent bill FK (transitive scope) |
note |
string |
Free-text note content |
noteType |
string |
Note category |
createdBy |
integer |
Audit FK to user; metadata |
createdAt |
string (date-time) |
Record timestamp; metadata |
updatedAt |
string (date-time) |
Record timestamp; metadata |
bill-attachments.json entity key bill_attachments · 9 fields
| Field |
Type |
Description |
id |
integer |
PK |
billId |
integer |
Parent bill FK (transitive scope) |
fileName |
string |
Original file name |
fileType |
string | null |
MIME/file type |
fileSize |
integer | null |
File size bytes |
reportType |
string | null |
Report/document category |
isAutoAttached |
boolean |
Operational flag (auto vs manual attach); retained as record metadata |
shouldSubmit |
boolean |
Operational flag (include in claim submission); retained as record metadata |
createdAt |
string (date-time) |
Record timestamp; metadata |
claims.json entity key claims · 22 fields
| Field |
Type |
Description |
id |
integer |
PK |
organization_id |
integer |
Org scope FK |
patient_id |
integer |
Patient scope FK |
bill_id |
integer | null |
FK to bills |
claim_charge_amount |
number |
Claim total charge |
claim_frequency_code |
string |
X12 claim frequency code |
claim_filing_code |
string |
Claim filing indicator |
place_of_service_code |
string |
POS code |
plan_participation_code |
string |
Plan participation code |
signature_indicator |
string |
Signature-on-file indicator |
billing_provider_npi |
string | null |
Billing provider NPI |
billing_taxonomy_code |
string |
Billing taxonomy code |
stedi_claim_number |
string | null |
Clearinghouse claim number |
control_number |
string |
X12 control number |
payer_id |
string | null |
Payer id |
payer_name |
string | null |
Payer name |
status |
string |
Claim status |
submission_format |
string |
ELECTRONIC/PAPER (default ELECTRONIC) |
source |
string |
Claim source (default stedi) |
response_received_at |
string (date-time) |
Payer response timestamp |
created_at |
string | null (date-time) |
Record timestamp; metadata |
updated_at |
string | null (date-time) |
Record timestamp; metadata |
patient-ledger-transactions.json entity key patient_ledger_transactions · 32 fields
| Field |
Type |
Description |
id |
integer |
PK |
organizationId |
integer |
Org scope FK |
patientId |
integer |
Patient scope FK |
visitId |
integer | null |
FK to patient_visits |
batchId |
integer | null |
FK to payment_batch |
chargeBatchId |
integer | null |
FK to charge_batch |
billId |
integer | null |
FK to bills |
externalId |
string | null |
External system id |
productId |
integer | null |
FK to product |
professionalServiceId |
integer | null |
FK to professional_service |
cptCode |
string | null |
CPT/HCPCS code for the line |
transactionType |
string |
Charge/payment/adjustment type |
paymentMethod |
string | null |
Payment method |
paymentCategory |
string | null |
Payment category |
paymentSource |
string | null |
Payment source |
amount |
number |
Transaction amount |
unitPrice |
number | null |
Unit price |
quantity |
integer |
Quantity (default 1) |
discount |
number | null |
Discount amount |
discountType |
string | null |
Discount type |
adjustmentCode |
string | null |
Adjustment reason code |
adjustmentType |
string | null |
Adjustment type (AdjustmentType) |
taxAmount |
number | null |
Tax amount |
status |
string |
Transaction status (default POSTED) |
transactionDate |
string (date-time) |
Transaction date |
note |
string | null |
Free-text note |
createdBy |
integer |
Audit FK to user; metadata |
voidedAt |
string | null (date-time) |
Void timestamp; part of financial record |
voidedBy |
integer | null |
Audit FK to user; metadata |
voidReason |
string | null |
Void reason text |
isRefundCredit |
boolean |
Refund-credit flag; financial semantics |
createdAt |
string (date-time) |
Record timestamp; metadata |
patient-balances.json entity key patient_balances · 17 fields
| Field |
Type |
Description |
id |
integer |
PK |
patientId |
integer |
Patient scope FK (unique) |
totalCharges |
number |
Aggregate charges |
totalPayments |
number |
Aggregate payments |
totalPatientPayments |
number |
Aggregate patient payments |
totalInsurancePayments |
number |
Aggregate insurance payments |
totalAdjustments |
number |
Aggregate adjustments |
totalCopay |
number |
Aggregate copay |
totalGeneral |
number |
Aggregate general payments |
totalDeposit |
number |
Aggregate deposits |
unallocatedCopay |
number |
Unallocated copay |
unallocatedGeneral |
number |
Unallocated general |
unallocatedDeposit |
number |
Unallocated deposit |
lastPaymentDate |
string | null (date) |
Last payment date |
lastPaymentAmount |
number | null |
Last payment amount |
createdAt |
string (date-time) |
Record timestamp; metadata |
updatedAt |
string (date-time) |
Record timestamp; metadata |
charge-items.json entity key charge_items · 8 fields
| Field |
Type |
Description |
id |
integer |
PK |
patient_id |
integer |
Patient scope column |
doctor_id |
integer |
Rendering provider; metadata |
encounter_id |
integer |
Encounter/visit reference |
bill_id |
string |
Bill reference (declared STRING, not a numeric FK) |
total |
integer |
Charge total amount |
createdAt |
string (date-time) |
Implied by timestamps:true (camelCase, no @CreatedAt decorator); metadata |
updatedAt |
string (date-time) |
Implied by timestamps:true (camelCase, no @UpdatedAt decorator); metadata |
Insurance & Administrative 4 files
Policies, insurance-card snapshots, guarantors, and emergency contacts.
patient-policies.json entity key patient_policies · 20 fields
| Field |
Type |
Description |
id |
integer |
Primary key / policy identifier. |
payer_id |
integer | null |
FK to insurance_payers; identifies the insurance carrier (EHI). No @AllowNull decorator so Sequelize default nullable=true. |
patient_id |
integer | null |
FK to patients; scope key. No @AllowNull decorator so Sequelize default nullable=true. |
payer_address_id |
integer | null |
FK to payer_addresses; payer mailing/claims address (EHI). |
person_id |
integer | null |
FK to persons; policy subscriber/holder (EHI). |
plan_name |
string | null |
Insurance plan name (EHI). |
member_id |
string | null |
Subscriber/member ID on the insurance card (EHI). |
group_number |
string | null |
Insurance group number (EHI). |
effective_date |
string | null (date-time) |
Policy effective date (EHI). |
policy_type |
string | null |
Policy type (e.g. primary/secondary/plan category) (EHI). |
claim_filing_code |
string | null |
Claim filing indicator code used for billing (EHI). |
notes |
string | null |
Free-text notes on the policy (EHI). |
policy_benefits |
— |
JSON (JSONB) |
third_party_coverage_id |
string | null |
Third-party coverage identifier (EHI). |
coverage_order |
integer | null |
Coordination-of-benefits ordering (primary=1, etc.) (EHI). |
meta |
— |
JSON (JSONB) |
status |
string | null |
Active/Inactive coverage state; default Inactive (EHI record state). |
is_archived |
boolean |
Archived flag (default false). Operational state of the policy record; retained as metadata, not internal soft-delete bookkeeping. |
created_at |
string (date-time) |
Record creation timestamp; metadata, not clinical content. |
updated_at |
string (date-time) |
Record update timestamp; metadata, not clinical content. |
policy-card-snaps.json entity key policy_card_snaps · 5 fields
| Field |
Type |
Description |
id |
integer |
Primary key. |
policy_id |
integer | null |
FK to patient_policies; the only scope path (transitive to patient). No @AllowNull decorator so Sequelize default nullable=true. |
uploaded_by |
integer | null |
Audit FK to users (uploader); metadata, not clinical content. No @AllowNull decorator so Sequelize default nullable=true. |
created_at |
string (date-time) |
Record creation timestamp; metadata. |
updated_at |
string (date-time) |
Record update timestamp; metadata. |
patient-guarantors.json entity key patient_guarantors · 16 fields
| Field |
Type |
Description |
id |
integer |
Primary key. |
patient_id |
integer |
FK to patients; scope key (attribute patientId, underscored to patient_id). |
first_name |
string | null |
Guarantor first name (attribute firstName) (EHI). |
last_name |
string | null |
Guarantor last name (attribute lastName) (EHI). |
phone |
string | null |
Guarantor phone number (EHI). |
relationship |
string | null |
Guarantor relationship to patient (RelationshipEnum stored as STRING(100)) (EHI). |
address |
string | null |
Guarantor street address (EHI). |
city |
string | null |
Guarantor city (EHI). |
state |
string | null |
Guarantor state (EHI). |
zip |
string | null |
Guarantor postal code (EHI). |
country |
string | null |
Guarantor country (EHI). |
optum_guarantor_id |
string | null |
External Optum guarantor identifier; integration metadata (EHI-adjacent identifier). |
birth_date |
string | null (date-time) |
Guarantor date of birth (EHI). |
is_guardian |
boolean | null |
Whether the guarantor is the patient’s guardian (EHI record state). |
created_at |
string | null (date-time) |
Record creation timestamp (attribute createdAt); metadata. |
updated_at |
string | null (date-time) |
Record update timestamp (attribute updatedAt); metadata. |
patient-emergency-contacts.json entity key patient_emergency_contacts · 11 fields
| Field |
Type |
Description |
id |
integer |
Primary key. |
patient_id |
integer |
FK to patients; scope key (indexed). |
name |
string |
Emergency contact name (EHI). |
phone |
string |
Emergency contact primary phone (EHI). |
alternate_phone |
string | null |
Emergency contact alternate phone (EHI). |
relationship |
string |
Relationship of contact to patient; DB ENUM over RelationshipEnum (EHI). |
is_primary |
boolean |
Designates the primary emergency contact (default false); meaningful record state, not internal bookkeeping. |
created_by |
integer | null |
Audit FK to users (creator); metadata, not clinical content. |
updated_by |
integer | null |
Audit FK to users (last updater); metadata, not clinical content. |
created_at |
string | null (date-time) |
Record creation timestamp; metadata. |
updated_at |
string | null (date-time) |
Record update timestamp; metadata. |
Files & Referrals 4 files
Claim-form PDFs, e-fax records, and referrals. (Binary attachments themselves are described under Attachments.)
claim-form-pdfs.json entity key claim_form_pdfs · 9 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
organization_id |
integer |
Tenant scope column |
patient_id |
integer |
Patient scope column |
bill_id |
integer |
FK to bill (billing linkage metadata) |
claim_id |
integer |
FK to claim (billing linkage metadata) |
trace_id |
string |
Internal correlation/trace id (metadata) |
transaction_id |
string | null |
Clearinghouse transaction id (metadata) |
created_at |
string | null (date-time) |
Record creation timestamp (metadata) |
updated_at |
string | null (date-time) |
Record update timestamp (metadata) |
efaxes.json entity key efaxes · 14 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
organization_id |
integer | null |
Tenant scope column (nullable) |
from_email |
string | null |
Sender email (communication metadata) |
to_email |
string | null |
Recipient email (communication metadata) |
subject |
string | null |
Fax/email subject line |
received_at |
string (date-time) |
Receipt timestamp (metadata) |
message_id |
string |
External message identifier (interop metadata) |
filename |
string | null |
Fax document file name |
size |
integer |
File size metadata |
status |
string |
Fax disposition/processing state; retained as record disposition |
direction |
string |
inbound/outbound direction of the fax (communication metadata) |
body_text |
string | null |
Fax body/extracted text — potentially clinical content |
created_at |
string (date-time) |
Record creation timestamp (metadata) |
updated_at |
string (date-time) |
Record update timestamp (metadata) |
e-fax-data.json entity key e_fax_data · 9 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
organization_id |
integer |
Tenant scope column |
efax_id |
integer |
FK to efaxes — scope path to patient (via attachments reverse-join) |
raw_content |
string | null |
Raw extracted fax content — potentially clinical PHI |
processed_json |
— |
JSON (JSON) |
content_type |
string | null |
Document content-type classification (metadata) |
sub_category |
string | null |
Document sub-category classification (metadata) |
created_at |
string (date-time) |
Record creation timestamp (metadata) |
updated_at |
string (date-time) |
Record update timestamp (metadata) |
patient-referrals.json entity key patient_referrals · 21 fields
| Field |
Type |
Description |
id |
integer |
Primary key |
patient_id |
integer |
Patient scope column |
policy_id |
integer | null |
Optional FK to patient_policies (linkage metadata) |
organization_id |
integer |
Tenant scope column |
referral_number |
string |
Referral identifier |
referring_provider_npi |
string | null |
Referring provider NPI (clinical/administrative) |
referring_provider_first_name |
string | null |
Referring provider first name |
referring_provider_last_name |
string | null |
Referring provider last name |
referred_to_provider_npi |
string | null |
Referred-to provider NPI |
referred_to_provider_name |
string | null |
Referred-to provider name |
member_id |
string | null |
Insurance member id (PHI) |
effective_from |
string | null (date) |
Referral effective start date |
effective_to |
string | null (date) |
Referral effective end date |
visits_authorized |
integer | null |
Number of authorized visits (clinical/administrative) |
visits_used |
integer |
Number of visits used against the referral |
status |
string |
Referral status (active/etc.) — record state |
source |
string |
Provenance of the referral (manual/etc.) — metadata |
notes |
string | null |
Free-text referral notes — potentially clinical content |
created_by |
integer | null |
Audit FK to user (metadata, not clinical content) |
created_at |
string (date-time) |
Record creation timestamp (metadata) |
updated_at |
string (date-time) |
Record update timestamp (metadata) |
Versioning
This document, the ZIP layout, the per-entity field sets, and the manifest are versioned together under a single schemaVersion (also present in every manifest.json). A change that adds fields or entities is a minor revision; a breaking change to existing field meaning or the archive layout raises the major version.
1.0.0 — Initial published format.
