This document provides an overview of the database schema used by the DIBBs eCR Viewer, including both the core and extended schemas, and information about supported database types.
The DIBBs eCR Viewer supports the following relational database types for storing metadata:
The core schema contains essential data related to eCRs and their associated conditions and rule summaries. This schema is fundamental to the operation of the eCR Viewer.
ecr_data TableThis table stores the primary eCR data.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
eicr_id |
varchar(200) |
NOT NULL | Primary key, unique identifier for the eICR | |
set_id |
varchar(255) |
NULL | Identifier for a set of related eICRs | |
eicr_version_number |
varchar(50) |
NULL | Version number of the eICR | |
fhir_reference_link |
varchar(255) |
NULL | Reference link to the FHIR resource | |
last_name |
varchar(255) |
NOT NULL | Patient's last name | |
first_name |
varchar(255) |
NOT NULL | Patient's first name | |
birth_date |
date |
NOT NULL | Patient's birth date | |
encounter_start_date |
datetime |
NULL | Start date and time of the patient encounter | |
facility_name |
varchar(255) |
NULL | Organization associated with the encounter | |
date_created |
datetime |
NOT NULL | Current timestamp | Date and time when the record was created |
ecr_rr_conditions TableThis table stores conditions associated with eCRs.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Primary key, unique identifier for the condition record | |
eicr_id |
varchar(255) |
NOT NULL | Foreign key, references ecr_data.eicr_id |
|
condition |
varchar(max) |
NULL | Description of the condition |
ecr_rr_rule_summaries TableThis table stores rule summaries related to eCR conditions.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Primary key, unique identifier for the rule summary record | |
ecr_rr_conditions_id |
varchar(200) |
NOT NULL | Foreign key, references ecr_rr_conditions.uuid |
|
rule_summary |
varchar(max) |
NULL | Summary of the rule applied |
user TableThis table stores user information.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Primary key, unique identifier for the user | |
email |
varchar(200) |
NOT NULL | User's email address, must be unique | |
name |
varchar(200) |
NULL | User's full name | |
date_of_last_login |
datetime |
NULL | Date and time of user's last login | |
user_type |
varchar(12) |
NOT NULL | Type of user (e.g., admin, standard) | |
status |
varchar(12) |
NOT NULL | active | User's account status |
date_created |
datetime |
NOT NULL | Current timestamp | Date and time when the user record was created |
author_uuid |
varchar(200) |
NOT NULL | Foreign key, references user.uuid, creator of the user record |
program_area TableThis table stores information about program areas.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Primary key, unique identifier for the program area | |
name |
varchar(200) |
NOT NULL | Name of the program area, must be unique | |
date_created |
datetime |
NOT NULL | Current timestamp | Date and time when the program area record was created |
author_uuid |
varchar(200) |
NOT NULL | Foreign key, references user.uuid, creator of the program area record |
user_program_area TableThis table links users to program areas. Note that the primary key for this table is a compound primary key consisting of user_uuid and program_area_uuid.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
user_uuid |
varchar(200) |
NOT NULL | Part of the composite primary key [user_uuid, program_area_uuid], Foreign key, references user.uuid |
|
program_area_uuid |
varchar(200) |
NOT NULL | Part of the composite primary key [user_uuid, program_area_uuid], Foreign key, references program_area.uuid |
condition_reference TableThis table stores reference information for conditions.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
code |
varchar(20) |
NOT NULL | Primary key, unique code for the condition | |
concept_name |
varchar(200) |
NULL | Name of the concept | |
condition_name |
varchar(200) |
NOT NULL | Name of the condition | |
condition_category |
varchar(200) |
NULL | Category of the condition | |
program_area_uuid |
varchar(200) |
NULL | Foreign key, references program_area.uuid |
audit_log TableThis table stores data related to usage of the eCR Viewer.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Unique record identifier, part of the composite primary key [date, uuid] | |
subject |
varchar(200) |
NOT NULL | The type of record involved, such as ecr, user, or program_area |
|
action |
varchar(200) |
NOT NULL | The action performed, such as query, view, create, update, delete, signin, or signout |
|
actor |
varchar(max) |
NOT NULL | The user UUID associated with the request, or an available API/auth token identifier when a user is not available | |
date |
datetime |
NOT NULL | Current timestamp | The database-generated timestamp for the audit event, part of the composite primary key [date, uuid] |
parameter_json |
varchar(max) |
NOT NULL | The parameters passed to the audited workflow, such as an eICR ID, user UUID, or eCR search filters | |
metadata_json |
varchar(max) |
NOT NULL | Additional request metadata. The Viewer currently records the request User-Agent |
|
checksum |
varchar(max) |
NOT NULL | A SHA-256 checksum generated from the audit record contents |
The extended schema builds upon the core schema by adding additional demographic, clinical, and administrative data points to the ecr_data table, and introduces new tables for laboratory results and patient addresses. This schema provides a more comprehensive view of the eCR data.
ecr_data TableThe following columns are added to the ecr_data table in the extended schema:
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
gender |
varchar(100) |
NULL | Patient's gender | |
birth_sex |
varchar(255) |
NULL | Patient's birth sex | |
race |
varchar(255) |
NULL | Patient's race | |
ethnicity |
varchar(255) |
NULL | Patient's ethnicity | |
homelessness_status |
varchar(255) |
NULL | Patient's homelessness status | |
tribal_affiliation |
varchar(255) |
NULL | Patient's tribal affiliation | |
tribal_enrollment_status |
varchar(255) |
NULL | Patient's tribal enrollment status | |
current_job_title |
varchar(255) |
NULL | Patient's current job title | |
current_job_industry |
varchar(255) |
NULL | Patient's current job industry | |
usual_occupation |
varchar(255) |
NULL | Patient's usual occupation | |
usual_industry |
varchar(255) |
NULL | Patient's usual industry | |
preferred_language |
varchar(255) |
NULL | Patient's preferred language | |
pregnancy_status |
varchar(255) |
NULL | Patient's pregnancy status | |
processing_status |
varchar(255) |
NULL | Processing status of the eCR | |
authoring_date |
datetime |
NULL | Date of authoring | |
authoring_provider |
varchar(255) |
NULL | Authoring provider | |
ehr_manufacturer_model |
varchar(255) |
NULL | EHR manufacturer model | |
ehr_software |
varchar(255) |
NULL | EHR software | |
provider_id |
varchar(255) |
NULL | Provider ID | |
facility_id |
varchar(255) |
NULL | Facility ID | |
encounter_type |
varchar(255) |
NULL | Type of encounter | |
encounter_end_date |
datetime |
NULL | End date and time of the patient encounter | |
reason_for_visit |
varchar(max) |
NULL | Reason for visit (Multiple values are concatenated into a single comma-separated string) | |
active_problems |
varchar(max) |
NULL | Active problems (Multiple values are concatenated into a single comma-separated string) |
ecr_labs TableThis table stores laboratory results associated with eCRs. Note that the primary key for this table is a compound primary key consisting of uuid and eicr_id.
| Column Name | Data Type | Nullability | Default Value | Description ` |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Part of the composite primary key [uuid, eicr_id], unique identifier for the lab record | |
eicr_id |
varchar(200) |
NOT NULL | Part of the composite primary key [uuid, eicr_id], Foreign key, references ecr_data.eicr_id |
|
test_type |
varchar(255) |
NULL | Type of test performed | |
test_type_code |
varchar(255) |
NULL | Code for the test type | |
test_type_system |
varchar(255) |
NULL | Coding system for the test type | |
test_result_qualitative |
varchar(255) |
NULL | Qualitative test result | |
test_result_quantitative |
numeric |
NULL | Quantitative test result | |
test_result_units |
varchar(50) |
NULL | Units for the quantitative test result | |
test_result_code |
varchar(255) |
NULL | Code for the test result | |
test_result_code_display |
varchar(255) |
NULL | Display name for the test result code | |
test_result_code_system |
varchar(255) |
NULL | Coding system for the test result code | |
test_result_interpretation |
varchar(255) |
NULL | Interpretation of the test result | |
test_result_interpretation_code |
varchar(255) |
NULL | Code for the test result interpretation | |
test_result_interpretation_system |
varchar(255) |
NULL | Coding system for the test result interpretation | |
test_result_reference_range_low_value |
numeric |
NULL | Lower bound of the reference range | |
test_result_reference_range_low_units |
varchar(50) |
NULL | Units for the lower bound of the reference range | |
test_result_reference_range_high_value |
numeric |
NULL | Upper bound of the reference range | |
test_result_reference_range_high_units |
varchar(50) |
NULL | Units for the upper bound of the reference range | |
specimen_type |
varchar(255) |
NULL | Type of specimen | |
specimen_collection_date |
date |
NULL | Date of specimen collection | |
performing_lab |
varchar(255) |
NULL | Performing laboratory |
ecr_immunizations TableThis table stores immunization records associated with eCRs.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Part of the composite primary key [uuid, eicr_id], unique identifier for the immunization record | |
eicr_id |
varchar(200) |
NOT NULL | Part of the composite primary key [uuid, eicr_id], Foreign key, references ecr_data.eicr_id |
|
name |
varchar(255) |
NULL | Name of the vaccine administered | |
effective_date |
date |
NULL | Date the vaccine was administered or not given | |
status |
varchar(50) |
NULL | Was the vaccine was administered (true/false) | |
status_reason |
varchar(max) |
NULL | Reason the vaccine was not administered, if any |
patient_address TableThis table stores patient address information. Note that the primary key for this table is a compound primary key consisting of uuid and eicr_id.
| Column Name | Data Type | Nullability | Default Value | Description |
|---|---|---|---|---|
uuid |
varchar(200) |
NOT NULL | Part of the composite primary key [uuid, eicr_id], unique identifier for the address record | |
use |
varchar(50) |
NULL | Purpose of the address (e.g., "home", "work") | |
line |
varchar(255) |
NULL | Street address line | |
city |
varchar(100) |
NULL | City | |
district |
varchar(100) |
NULL | District | |
state |
varchar(100) |
NULL | State | |
postal_code |
varchar(20) |
NULL | Postal code | |
country |
varchar(100) |
NULL | Country | |
period_start |
datetime |
NULL | Start date of the address's validity period | |
period_end |
datetime |
NULL | End date of the address's validity period | |
eicr_id |
varchar(200) |
NOT NULL | Part of the composite primary key [uuid, eicr_id], Foreign key, references ecr_data.eicr_id |