General Design Conventions

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.

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

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 reasons and 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 RIS, Inpatient, and SaaS or an Epic system which may be mapped to many roles like Local, HIS, EMR, Inpatient, Outpatient, Registration, Finance. The full list of roles:

System Role Description
Registration Admission, discharges, transfers, locations and MRN providers
HIS Hospital-wide order/result and patient care tracking
EMR Enterprise distribution of patient data
PACS Diagnostic imaging system
RIS Radiology-specific orders/results/workflow
VR Dictation solution
Telemedicine Used primarily for remote/outsourced care
Inpatient Used primarily for inpatient care
Outpatient Used primarily for outpatient care
Financial "Billing, charges, collections"
SaaS Remote/cloud hosted solution
Local Locally hosted solution
Lab Lab-specific orders/results/workflow
Path Path-specific orders/results/workflow
Periop Perio-specific orders/results/workflow
EMPI Master patient index system to group and reconcile patient identifiers
Authentication Authentication and/or authorization
Credentialing Provider identification and credentialing

This list of the current external system roles is also available in Service Tools > Data Manager > Warehouse > External System Roles.

Reasons

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 arrived. Each external_system_statuses record has external_system_id and 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 .id or 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.

Event Type Description
order an electronic order is entered into the order placer system requesting an exam
arrived patient check-in at the imaging facility
first image the time when the imaging device begins to collect data that will be used to create the first image
complete all images are acquired and reconstructed
prelim a preliminary report was created
final a final report was created
addendum the final report is addended
cancelled the exam is cancelled
begin the exam process has been started
scheduled the exam has been scheduled with an appointment time
dictated "the exam has been read and a report has been dictated, but not necessarily generally or elctronically available"
charged the charge has been sent to the billing company (but not necessarily coded or verified)
claimed the claim has been sent to a carrier or individual
denied the claim has been denied
appealed the claim denial has been appealed
payment a payment has been made on the claim
zero balance the claim has been fully collected
admit patient admitted to hospital
transfer patient has been moved/transferred to another location
discharge patient has been discharged
register patient has been registered
pre-admit patient is not yet admitted
cancel admit patient admission has been cancelled
cancel discharge patient discharge has been cancelled
corrections report is being revised for corrections
adjustment the claim has been adjusted
transfer in money being transferred in
transfer out money being transferred out

This list of the current universal event types is also available in Service Tools > Data Manager > Warehouse > Universal Event Types.

Site Classes

site_classes from an external_system are an attribute of an visit that describe the patient class for a given site. Properties of site_class include:

Locations

site_locations (wards / floors / units) and site_sublocations (room and bed number) from an external_system provide more location specificity than site. All 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 room and bed columns. An expanded, optional free-text description of the location is in site_locations.name.

Procedures

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 active. 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:

Finally, there are currently 4 optional types of fees associated with a procedure:

Patients

Patient attributes are stored in a variety of tables, the primary of which is the patients table. The name, birthdate and gender demographics are updated to reflect the most recent messaging.

Patient Demographics

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 MRNs

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 patient record.

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 rad_exams or 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

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 patient_event:

Visits

A 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:

Orders

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 department_id and 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 order_arrival and 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.

Ordering Departments

The ordering department, often service or location-oriented, is the department that places an order and it has an optional description.

Insurance

Health insurance information has several important tables that break down the relationships and instances of insurance information with the clinical data.

Carriers

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 patient_demos).

Plans

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.

Plan Groupings

insurance_carrier_plan_groupings are an arbitrary group of insurance plans that can be associated for analysis.

Policies

An 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 policy_holder_name and relationship_type between the policy-holder and the insured (this is an FK back to the relationships table). Additional / optional information includes the employer, group_number and policy_number.

Insurance information is most commonly associated with visit-level events, so visit_insurance_policies maps insurance_policies to visits.

HCPCS Codes

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 cms_modifier FK.

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 supply or surgical type (the modality FK is not currently populated).

Thecms_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:

ICD Codes

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:

Providers and Staff

Employees

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 clinical_roles of attending and radiologist mapped, as well as the specialty of Neuroradiology mapped.

Additionally, 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.

People

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:

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

Identifiers are how messages get linked to (or create) employee records. 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:

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 person and employee records would be created.

identifiers are globally unique within the constraints of an identifier_type and 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 identifier_type of Login and the name attribute an identifier_type of 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 identifier_type and external_system. This is all handled within the SDK automatically, but you can see the specific configuration in service tools.

Clinical Roles

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 employees (not 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 technologist and supervisor.

Finally, clinical_roles are specific to a department and both clinical_roles and 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 department is Radiology.

Clinical Role Description
radiologist clinical radiologist (including residents, attendings, etc.)
resident resident
attending attending physician of any discipline (radiologist, ED doctor, pediatrician, etc)
fellow-acgme fellow in an ACGME-accredited program
fellow-non-acgme fellow in a non-ACGME-accredited program
technologist technologists (modality techs, lab techs, etc)
transcriptionist non-physician administrative role for those who transcribe and edit reports, handle corrections, etc.
scheduler-registrar clinical administrative role for employees who check in patients, verify patient information, etc.
finance non-clinical role for employees working in billing, accounting, etc.
admin-assistant general administrative, non-clinical role
it-staff technical administrators who support clinical systems (PACS admins, RIS admins, etc.)
supervisor Those in a supervisory role in their position (tech supervisors, RIS/PACS manager, section chiefs)
director Those in director positions for their given role (director titles)
executive Those in executive positions (Hospital C level, Department chair, department administrator)
power Senior or lead team members who are not supervisors, but have extra system privileges or leadership roles
billing-coding coders and others who work on billing, insurance, claims, etc.
nurse nursing staff
patient-transport staff who transport patients
ai-staff senior technical administrators or vendor support staff (synonym of deprecated support-staff)
researcher researchers and other informaticians

Specialties

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 primary. 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.

Unlike clinical_roles, the specialty table is not globally controlled. Like clinical_roles, however, the speciality table is also linked to the departments table.

Provider Groupings

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.