General Design Conventions
- All tables have a primary key of
- Foreign keys are referred to via
sourcetable_idwith certain semantic exceptions, e.g. in a one-to-many relationship such as
rad_reportsthere are foreign keys such as
- All tables have an
updated_atcolumn, but this does not necessarily represent the time the table was actually updated. It reflects the most recent timestamp of the message which updated the row. By way of example, a
rad_examrow might have an
2013-07-01 16:20:00-0400, but the message with this content might have actually been processed at
2013-07-31 04:00:20-0400. This type of logic is how messages can arrive and be processed out-of-chronological order without breaking the data model. Additionally, this timestamp does not necessarily mean the row content was changed, only that it was verified.
Sites and Metasites
sites refer to a specific geographic boundary, usually a building or facility.
sites are typically hospitals or outpatient facilities in current configurations and used to determine location.
siteshave a foreign key to the
cms_gpcitable so that CMS rates can be accurately calculated. Additionally, there is a
cms_facilityboolean flag to indicate if a
siteis considered a CMS facility.
sitesdo not associate directly with a CMS place of service code since a given
sitemay have multiple POS codes (e.g. many large hospitals have POS codes for inpatient, outpatient, and emergency services).
siteshave additional properties for application convenience: the
namecolumn is a human friendly name and the
application_visibleboolean can be used to indicate a facility should not be shown in applications creating lists from the
metasites are arbitrary lists of sites used for a variety of purposes. The primary purpose in the data model is to provide groupings for assigning authorities (similar to the concept of XDS affinity domains) via
external_systems (detailed below). It also allows for groupings based on financial organization, geographic regions, political affiliation, etc.
external_systems are specific instances of clinical information systems that provide information to
Bridge. They are bounded by a
metasite to allow for grouping and filtering of records based on the authoritative issuing system (e.g. all the patient locations defined by a particular registration system or all the exam statuses used in a particular voice dictation system). Most all reference/dictionary-type tables have an
external_system_id foreign key so that applications can group/filter very specifically. Detailed examples of these are
external_system_statuses documented below.
external_system_role_mappings provide the option to additionally map the role of external systems for various purposes such as grouping and filtering. The list of
external_system_roles and the
external_system are the foreign keys that get mapped in this table. An example might be a cloud-based HI-IQ interventional radiology system mapped with roles
SaaS or an Epic system which may be mapped to many roles like
Finance. The full list of roles:
||Admission, discharges, transfers, locations and MRN providers|
||Hospital-wide order/result and patient care tracking|
||Enterprise distribution of patient data|
||Diagnostic imaging system|
||Used primarily for remote/outsourced care|
||Used primarily for inpatient care|
||Used primarily for outpatient care|
||"Billing, charges, collections"|
||Remote/cloud hosted solution|
||Locally hosted solution|
||Master patient index system to group and reconcile patient identifiers|
||Authentication and/or authorization|
||Provider identification and credentialing|
This list of the current external system roles is also available in
Service Tools >
Data Manager >
External System Roles.
reasons are common responses from an
external_system used to describe information about workflow, e.g. a reason for a patient merge or a reason for a procedure change.
External System Statuses and Universal Event Types
external_system_statuses is a dictionary/lookup table to map workflow steps from various
external_systems to a common, industry-defined lexicon based on the SIIM TRIP initiative SWIM. This allows for a patient arrived workflow step that uses the messaging mnemonic
AR in one system to map to the same workflow step as a system that would use the mnemonic
external_system_statuses record has
universal_event_type_id foreign keys.
external_system_status_id foreign keys are used in report and exam tables to indicate what status an exam or report is in, with the foreign key name being changed as appropriate to guide meaning, e.g.
rad_exams.current_exam_status_id is the up-to-date status of an exam versus
rad_reports.exam_status_id is the status for that particular report instance at a given time.
It is very important to build applications and logic around the
universal_event_types.event_type values, not hard-coded
external_system_status.status values because those values are malleable and inconsistent across platform instances, but the lexicon stored in
universal_event_types based on SWIM will persist and is consistent across instances.
||an electronic order is entered into the order placer system requesting an exam|
||patient check-in at the imaging facility|
||the time when the imaging device begins to collect data that will be used to create the first image|
||all images are acquired and reconstructed|
||a preliminary report was created|
||a final report was created|
||the final report is addended|
||the exam is cancelled|
||the exam process has been started|
||the exam has been scheduled with an appointment time|
||"the exam has been read and a report has been dictated, but not necessarily generally or elctronically available"|
||the charge has been sent to the billing company (but not necessarily coded or verified)|
||the claim has been sent to a carrier or individual|
||the claim has been denied|
||the claim denial has been appealed|
||a payment has been made on the claim|
||the claim has been fully collected|
||patient admitted to hospital|
||patient has been moved/transferred to another location|
||patient has been discharged|
||patient has been registered|
||patient is not yet admitted|
||patient admission has been cancelled|
||patient discharge has been cancelled|
||report is being revised for corrections|
||the claim has been adjusted|
||money being transferred in|
||money being transferred out|
This list of the current universal event types is also available in
Service Tools >
Data Manager >
Universal Event Types.
site_classes from an
external_system are an attribute of an visit that describe the patient class for a given
site. Properties of
patient_type(as a foreign key to the
patient_typestable) to indicate inpatient (
I) or outpatient (
O) status (the only 2 allowed values for the US per CMS)
- boolean flags to indicate if that class is for emergency department patients (
ed) or trauma patients (
cms_facility_type(as a foreign key to the
cms_facility_typestable) to associate the CMS POS code and
namea human-friendly more description of the
site_locations (wards / floors / units) and
site_sublocations (room and bed number) from an
external_system provide more location specificity than
site_sublocations must have a
site_location_id parent FK, so it is common to only see a
site_sublocation_id used as a foreign key reference in other tables, as all
site_locations have at least one
site_sublocation child record with null
bed columns. An expanded, optional free-text description of the location is in
procedures are unique within a given
external_system by the
code column. The optional
description provides more information, as do boolean flags indicating if a procedure is
reportable or currently
scheduled_duration (in minutes),
volume (the number of times a procedure should count for volume reporting), and
specialty (foreign key to the
specialties table) are additional optional attributes.
Procedures can have zero or more associated CMS HCPCS codes (a.k.a. CPT codes). These associations are made via the
procedure_hcpcs_mappings mapping table, which has each row associating a
procedure_id with a
cms_hcpcs_code_id. These associations can be used for determing CMS fees and RVU. For further details, see the section below on CMS HCPCS codes.
There are 2 types of RVU (relative value units) associated with a procedure:
pro_rvuis the professional component (e.g. reporting) RVU associated with a procedure, and is the summed value of the Work RVU for all the associated
cms_hcpcs_codesfor a given
tech_rvuis the technical component (e.g. performing) RVU associated with a procedure, and is set arbitrarily as there is no national standard for technical RVU.
Finally, there are currently 4 optional types of fees associated with a procedure:
practice_feerefers to a practice fee from a charge master
pro_feeis the professional fee component from a charge master
tech_feeis the technical fee component from a charge master
Patient attributes are stored in a variety of tables, the primary of which is the
patients table. The
gender demographics are updated to reflect the most recent messaging.
Other demographics are stored in the vertical table
patient_demos with the value being the
demographic column and the type of demographic coming from the FK relationship for
demographic_type_id (which links to
demographic_types, a table shared with
employee_demos as well). These demographics are potentially tied to the
visits table (see below for more information about visits). There is a parallel/clone table,
patient_demos_history that contains all historic and current patient demographics, whereas
patient_demos is only the most current information.
As the patient model evolves, we expect to eventually normalize certain common demographics (addresses, phone numbers, etc.) out of the vertical table.
Patient identifiers are tied to an
external_system and have an optional boolean
empi flag to indicate if a particular MRN within the set is an EMPI. MRNs are required to be unique to an
external_system or unexpected behavior will occur. The
patient_id FK links to the
MRNs can be merged by ADT messages and references to them in tables such as
rad_exams will get updated ID of the merge winner and the consumed MRN row will be deleted.
Tables such as
patient_events (more info below) link directly to
patient_mrns instead of
patient so it is possible to know which MRN was used for a particular exam. The reason is that displaying an MRN is a very common use case, but displaying a patient's name or other demographics is not always the case.
Patient events are a way of tracking certain visit information, such as admission, discharge, or transfer events. Each
patient_events row has the most recent event for a given visit, with the most recent as well as all historic events existing in a parallel/clone table
patient_events_history, similar to the demographics table.
Each record contains information about the type of event (linked to
external_system_statuses via the
external_system_status_id) and the time of the event (
event_time). There are many optional fields for the
- the reason for the event ,linked to the
- who performed the event, an
- the patient location (location, room, bed) via the
postevent_site_sublocation_id. The reason the columns are named as
postevent_is to clarify that for a transfer type event, this is the location after transfer, not before. Additionally, if a location is in the record, this implicitly gives
siteinformation (since locations are all bound to a
site_classof the patient after the event, via the
site_class_idforeign key, which allows inpatient-outpatient conversion, etc. to be tracked.
visit represents a single admission or outpatient encounter at a facility for a given time period. The unique identifier of a visit is the
visit_number. For inpatient visits, this number is important because it often links to billing/charges and orders. Visits are the parent relationship to one or more orders and one or more radiology exams. Additionally, patient events (like an admission, discharge, or transfer) are tied to visit, as are patient demographics (see above).
Visits also have two important child tables:
visit_diagnosesmaps ICD codes to a visit (see below for more detail about ICD codes)
visit_insurance_policiesmaps insurance information gathered during the visit (see below for more information about insurance)
An order represents a request by a provider for a service, procedure, or exam. The unique identifier of an order is the
order_number. Orders can be a child of a
visit to group them with a single admission/encounter, but this workflow is site-specific and not a universal guarantee. Additionally, orders can be linked together when placed at the same time via the so-called Master Order Number (
orders.master_order_id), which refers to another row in
orders for association. Orders additionally have
site_id foreign keys which map the order to the site and clinical department filling it (currently only suppporting
Radiology and not to be confused with ordering department). An order may create one or more child exams, but much like the relationship to visit, this is not necessarily a guaranteed relationship. Orders can be created without an associated exam and have their own status based on the universal event type; if an order has a NULL status, then it was created by an exam message.
Orders can also track a number of important appointment and pre-appointment attributes, primarily by foreign key, for site class (and thus patient type), procedure, resource, exam priority, ordering provider (by employee), ordering provider group, and ordering department. Additionally, values for
appointment timestamps are tracked and sync with any matching rad exam times and the scheduled
appointment_duration (in seconds) can also be set. Values for the
appointment_duration will cascade into rad_exam_facts for radiology orders.
The ordering department, often service or location-oriented, is the department that places an order and it has an optional description.
Health insurance information has several important tables that break down the relationships and instances of insurance information with the clinical data.
insurance_carriers is a simple list of different carriers. Each carrier has demographic data (e.g. address, phone number, etc.) associated with it through the
insurance_carrier_demographics table with associates an
insurance_carrier FK with a
demographic_type FK and a text value of the demographic (just like
insurance_carrier_plans are children of the
insurance_carrier and specify information about various plans: the
plan_name (mandatory) and an optional field for
plan_number. Plans can also be grouped with a foreign key to plan groupings.
insurance_carrier_plan_groupings are an arbitrary group of insurance plans that can be associated for analysis.
insurance_policy is an instance of an
insurance_plan that is associated with real events like a claim or a visit.
Although you can link back to the
insurance_carrier via the
insurance_carrier_plan FK, this is not mandatory. The only required fields are the
relationship_type between the policy-holder and the insured (this is an FK back to the
relationships table). Additional / optional information includes the
Insurance information is most commonly associated with visit-level events, so
HCPCS Codes (a.k.a. CPT codes) are a set of universal procedure codes required for billing in the US by HIPAA. Procedures are mapped to zero or more these codes via the
procedure_hcpcs_mappings table. The codes also provide professional RVU and the framework to calculate the CMS fee (based on the location and type of facility where the procedure performed).
Since the HCPCS codes get updated by CMS at least annually, there is a versioning system designed into the data model: the
cms_hcpcs_code_revisions table describes the
revision of a code set as well as if it is
active. There will be only 1
active version at any given time.
The majority of the data needed to use the codes is in the
cms_hcpcs_codes table, which has the tabular values from the CMS files, as well as the FK reference to the
cms_hcpcs_code_revision table and the
The most useful information is the code
hcpcs, the human-readable
description, and the work RVU (
work_rvu). There are additional boolean flags to indicate if a code is a
surgical type (the
modality FK is not currently populated).
cms_gpci table is also associated with a specific
cms_hcpcs_code_revision and is used to determine the geographic price cost index which adjust fees by locality.
Finally, there are additionally persistent CMS tables not associated with a particular HCPCS revision:
cms_modifiers: Table of code modifiers (e.g.
cms_facility_types: contains the place of service codes (
pos_code) as well as a human-friendly description (
facility_type) and boolean of the location is considered a
ICD codes are available as a reference table and are associated with visits and financial data. Both ICD-9-CM and ICD-10-CM code sets are loaded and supported in the system concurrently.
Similar to the HCPCS codes above, there are 2 primary tables for ICD codes:
icd_code_revisionsdefines a loaded code set
2014), as well as an
activeflag (allowing new code sets to be added in the future).
icd_codeswhich link back via FK to the
icd_code_revisionand contain the
codeand a human-friendly
Providers and Staff
There are several layers of abstraction used in the data model to represent clinical actors. These layers allow flexibility and time-based changes so that you can, for instance, have separate-but-linked records of a resident who becomes a fellow who becomes an attending, or someone who changes their name, or someone who has multiple clinical specialties and roles.
The core referenced element is an
employee, which is what exams, reports, and other clinical actions are linked to.
employee records have one or more
clinical_roles which define their job function(s) and hierarchy as well as one or more
specialty mappings which definite their clinical expertise. By way of example, an attending radiologist who specializes in neuroradiology would have the
radiologist mapped, as well as the
employee information such as phone number, email address, etc. are associated with
employee records via the
employee_demographics vertical table. Finally, there is a special table for resident metadata called
residents which specifies their PGY completion years.
The above is example is a nice, simple common case, however real life is often messier: people get married and change their name, residents become fellows, etc. and this requires you to either change a record and make historic data weird or create duplicate/unlinked records in other systems. The way these cases are handled in the data model are with the
people table: for the preceding examples, there is one
people record and multiple employee records for each phase of employment / demographics change.
Employee records link back to a common
people record and the
people record links to the current
employee. By way of example:
- Dr. Smith is a radiology resident with
people.current_employee_id=1, and the
- Dr. Smith continues his training and becomes a musculoskeletal fellow. A new
employee.id=2 is created with
people.id=1 record is updated to
people.current_employee_id=2, and the
acgme-fellow. Additionally, a radiology
Radiology Musculoskeletalis mapped to
Thus if you are interested in all things Dr. Smith ever did, you would want to query the
employees table with the matching
person_id for the current Dr. Smith, whereas if you were only interested in what Dr. Smith the fellow is doing, you could just use the
employee record directly.
Identifiers are how messages get linked to (or create)
identifier records are linked to an
employee record, which is how messaging from Hospital A using one set of mnemonics is linked to Hospital B using a different set of mnemonics. It is not uncommon for an
employee to have over a dozen
identifiers in many systems. By way of example:
- Dr. Smith signs a report at Hospital A, the HL7 messaging for this report has the
- Dr. Smith signs a report at Hospital B, the HL7 messaging for this report has the
Presuming the system is correctly configured (maintaining these mappings are arguably the biggest challenge in keeping good data quality), the
identifier.identifer=12345 record and the
identifier.identifer=JSMITH record both point to the same
employee.id If JSMITH wasn't already configured as an identifier for Dr. Smith, a new set of
employee records would be created.
identifiers are globally unique within the constraints of an
external_system: this effectively means you cannot have two JSMITH identifiers in the same namespace, but you can have overlapping identifiers in separate systems.
identifier_types are ways of differentiating between different identifiers linked in the same system. By way of example, some messaging from an LDAP source might send a common name property (e.g.
jsmith), but other messaging might use a name attribute (e.g.
John Smith, MD) of the same LDAP system. By assigning the common name the
Login and the name attribute an
Name associations within a namespace are possible, making management easier. These
identifier_types are configured at integration time.
external_systems are covered in the Data Model Basics documentation.
Finally, the way the SSO maps logins to
employee records is via a table called
configuration_variables which all platform instances have configured to specify a specific
external_system. This is all handled within the SDK automatically, but you can see the specific configuration in service tools.
clinical_roles are an designed to map the job functions of
employee. They can be also used by the SDK as authorization filters.
clinical_roles can be used not only to prevent/grant access to a specific page, but components within a page. For further details about how to use
clinical_roles as an authorization filter, see the SDK documentation.
clinical_roles are mapped to
people), and an employee can have zero or more roles, so this should be taken into account when designing your applications authentication and authorization. An example would be a technologist supervisor who would have the roles of
clinical_roles are specific to a
department and both
departments are currently "controlled" tables that are kept in sync across instances, so they cannot be configured uniquely. This ensures application compatibility and portability. Currently, the only
||clinical radiologist (including residents, attendings, etc.)|
||attending physician of any discipline (radiologist, ED doctor, pediatrician, etc)|
||fellow in an ACGME-accredited program|
||fellow in a non-ACGME-accredited program|
||technologists (modality techs, lab techs, etc)|
||non-physician administrative role for those who transcribe and edit reports, handle corrections, etc.|
||clinical administrative role for employees who check in patients, verify patient information, etc.|
||non-clinical role for employees working in billing, accounting, etc.|
||general administrative, non-clinical role|
||technical administrators who support clinical systems (PACS admins, RIS admins, etc.)|
||Those in a supervisory role in their position (tech supervisors, RIS/PACS manager, section chiefs)|
||Those in director positions for their given role (director titles)|
||Those in executive positions (Hospital C level, Department chair, department administrator)|
||Senior or lead team members who are not supervisors, but have extra system privileges or leadership roles|
||coders and others who work on billing, insurance, claims, etc.|
||staff who transport patients|
||senior technical administrators or vendor support staff (synonym of deprecated
||researchers and other informaticians|
employee records can have zero or more clinical
specialty mappings. There is also a boolean flag to indicate which
specialty record is the primary for the purposes of aggregate reporting and groupings, there can only be one mapping flagged as
specialty mappings are done for the purposes of identifying clinical expertise, so in radiology this is typically an anatomic or modality-oriented table. Additionally,
procedures can be mapped to
specialty for the purposes of building worklists, etc.
specialty table is not globally controlled. Like
clinical_roles, however, the
speciality table is also linked to the
Arbitrary groups of employees can be created to allow for grouping and filtering (e.g. to understand ordering patterns and performance). Groups can be created either via clinical data source messaging or manually in Service Tools. Groups have both a human-friendly name as well as a unique identifier constrained by an external system.