The fundamental link to all data about a radiology exam is the rad_exams record. It's unique composite key is the accession_numbers exam identifier and the external_system foreign key. A patient_mrn_id foreign key is also mandatory for an exam to exist, and this can be used to get other patient metadata. The last mandatory column is a foreign key link to external_system_statuses via current_exam_status_id which tracks the universal event type.

rad_exams can have both visits and orders, linked by foreign keys (see Data Model Basics for more info on these relationships), but neither are mandatory. Additionally, site_class, site_sublocation (and implicitly, site_location), and procedure are all optional (but expected) foreign keys.

There are five unique foreign key columns that link to rad_reports, as shortcuts to help find the most commonly needed report revisions (since an exam can have zero or more reports). In order for these columns to be maintained appropriately, it is essential to make sure all external_system_statuses for reports are mapped with a universal_event_type. See below for more details about rad_reports.

Finally, there are several additional tables with foreign keys in rad_exams that only are used in the Radiology context at this time: exam_priority_id, rad_exam_department_id, and resource_id.

Priority and Departments

exam_priority is a simple look-up table for the priority of an exam (e.g. STAT, ASAP, etc.)

rad_exam_departments are the performing department or service, often modality- and/or location-oriented. rad_exam_departments are constrained to a specific site and have an optional description to provide additional information.

Resource and Modality

resources represent the physical scanner that an exam was performed with. It is the only way to get the modality type of an exam. resources are constrained to be unique within an external_system domain. Each resource has a site and an optional name column for a more detailed description.

Additionally, resources can be associated with DICOM data via a foreign key to the PACS AE title table, which can be manually associated via a Service Tools edit or through both elements being sent from a clinical data source in the same DICOM or radiology exam message.

Personnel

A rad_exam always has exactly one rad_exam_personnel record which links back to the exam via the rad_exam_id foreign key. Each column is a foreign key to employees.

Exam Times

A rad_exam always has exactly one rad_exam_times record which links back to the exam via the rad_exam_id foreign key. Each column is a timestamp representing when a specific event in the exam lifecycle took place.

You can also see which metrics are derived from these timestamps visually in the metric-timestamp matrix.

Exam Details

A rad_exam always has exactly one rad_exam_details record which links back to the exam via the rad_exam_id foreign key. This table stores optional metadata about an exam.

Claims and Authorizations

The finance data model has two tables associated with rad_exams:

Additional documentation about insurance, claims, and authorizations can be found in the Core and Finance documentation.

DICOM

DICOM metadata about an exam is stored in a model following the hierarchy of the DICOM data model, starting with a dicom_studies table that has one or more child rows in the dicom_series table and finally one or more child rows in the dicom_instances table.

The only instance-level data captured (besides instance UID and FK to series) is the capture timestamp (timestamp from tags (0008,0022) and (0008,0032)).

At the series-level, there is a FK to the study and series UID, as well as FK to the DICOM modality, AE title, bodypart, manufacturer, model number, software version, serial number, and DICOM protocol. Other series-level attributes are the instance (image) count, laterality, and begin and end timestamps (automatically derived from the associated instance capture timestamps).

At the study-level, there is support for the accession number, study UID, and FK to associated rad_exam and procedure information. Other study-level attributes are the instance (image) count, series count, and begin and end timestamps (automatically derived from the associated instance capture timestamps).

Other Metadata

The rad_exam_metadata table is a vertical table for additional metadata about an exam that does not currently fit into the data model. An exam has zero or more records which link back to the exam via the rad_exam_id foreign key. The actual metadata value is in the metadata column and the exam_metadata_type and external_system have foreign key columns.

An example of real-world use might be tracking something like fluoroscopy duration, which could be displayed in an application view. In practice, this table can only be used reliably to display extra data or for stand-alone reporting, and it is not frequently configured at integration time.

PACS Metadata

A rad_exam always has exactly one rad_pacs_metadata record which links back to the exam via the rad_exam_id foreign key. This table stores metadata about what the PACS knows about an exam. study_comments is a free-text field of unformatted information that may be entered into the PACS. image_count is null by default, but can also be a -1 if an attempt to get the value from the PACS failed. Otherwise it will be 0 if there are no images in the PACS or >=1 depending on how many images reported in the PACS (not that this may vary from vendor to vendor based on how multiframe series and cine are handled). The external_system foreign key is important because it provides integration information for being able to launch an exam contextually.

There are 2 timestamp columns to track when the first and last images in the exam were available in PACS, first_image and last_image, respectively. Depending on the vendor configuration and site workflow, the last image time may be skewed if an image is added to the exam from 3-D/post-processing or Q/A edits affect a split/merge. There are also 2 timestamp columns to track when the first and last images in the exam were acquired by the modality, dicom_begin and dicom_end, respectively.

The remaining columns are foreign keys to rad_pacs_* child lookup tables:

There are also FK to shared DICOM metadata tables as well for the DICOM device manufacturer, model, serial number, and software version of the modality.

External System Statuses

rad_exam_external_system_statuses is a vertical table for tracking external_system_status changes over time in an external_system that is not the external_system which owns the exam (e.g. a PACS or VR system vs the RIS or HIS). There are zero or more records which link back to the exam via the rad_exam_id foreign key. The only value is the column external_system_status_id which links back to the external_system_statuses table.

Metrics

rad_exam_metrics are pre-calculated, commonly used KPIs related to exam and report performance. A rad_exam always has exactly one rad_exam_metrics record which links back to the exam via the rad_exam_id foreign key. The columns are all integers representing number of seconds duration between two timestamps related to the exam, and they are updated every message automatically so you don't need to manually get the timestamps and do the math for common performance metrics (and you can also query for metrics within a certain threshold while still being very performant).

You can also see which timestamps are used to calculate these metrics visually in the metric-timestamp matrix.

Metric Description Definition
access_wait how quickly an appointment can be scheduled rad_exam_times.appointment - rad_exam_times.schedule_event
transport how quickly a patient can be transported from the floor to radiology rad_exam_times.begin_exam - rad_exam_times.schedule_event
reg_wait registration delay between patient arrival and starting registration rad_exam_times.begin_reg - rad_exam_times.sign_in
reg_duration registration duration rad_exam_times.check_in - rad_exam_times.begin_reg
exam_wait how delayed (positive numbers) or early (negative numbers) an exam started compared to the appointment time rad_exam_times.begin_exam - rad_exam_times.appointment
exam_duration length of procedure rad_exam_times.end_exam - rad_exam_times.begin_exam
total_wait total time patient waited for exam to start after patient arrival rad_exam_times.begin_exam - rad_exam_times.sign_in
order_complete how long it takes to fulfill an order with a completed exam rad_exam_times.end_exam - rad_exam_times.order_arrival
first_report_turn_around how long after imaging is completed for the first preliminary or final report to be rendered (the report_event time is from the first_report_id) rad_reports.report_event - rad_exam_times.end_exam
prelim_turn_around how long after imaging is completed for a preliminary report to be rendered (the report_event time is from the first_prelim_report_id) rad_reports.report_event - rad_exam_times.end_exam
turn_around how long after imaging is completed for a final report to be rendered (the report_event time is from the first_final_report_id) rad_reports.report_event - rad_exam_times.end_exam
order_first_report how long after an order is placed for the first preliminary or final report to be rendered (the report_event time is from the first_report_id) rad_reports.report_event - rad_exam_times.order_arrival
order_first_prelim how long after an order is placed for a preliminary report to be rendered (the report_event time is from the first_prelim_report_id) rad_reports.report_event - rad_exam_times.order_arrival
order_first_final how long after an order is placed for a final report to be rendered (the report_event time is from the first_final_report_id) rad_reports.report_event - rad_exam_times.order_arrival
schedule_first_report how long after an exam is scheduled for the first preliminary or final report to be rendered (the report_event time is from the first_report_id) rad_reports.report_event - rad_exam_times.schedule_event
schedule_first_prelim how long after an exam is scheduled for a preliminary report to be rendered (the report_event time is from the first_prelim_report_id) rad_reports.report_event - rad_exam_times.schedule_event
schedule_first_final how long after an exam is scheduled for a final report to be rendered (the report_event time is from the first_final_report_id) rad_reports.report_event - rad_exam_times.schedule_event
order_first_image how long it took for the first image to be available after the order was placed rad_pacs_metadata.first_image - rad_exam_times.order_arrival
order_last_image how long it took for the entire study to be available after the order was placed rad_pacs_metadata.last_image - rad_exam_times.order_arrival
first_image_prelim how long after the first image was available for a preliminary report to be rendered (the report_event time is from the first_prelim_report_id) rad_reports.report_event - rad_pacs_metadata.first_image
last_image_prelim how long after the entire study was available for a preliminary report to be rendered (the report_event time is from the first_prelim_report_id) rad_reports.report_event - rad_pacs_metadata.last_image
first_image_final how long after the first image was available for a final report to be rendered (the report_event time is from the first_final_report_id) rad_reports.report_event - rad_pacs_metadata.first_image
last_image_final how long after the entire study was available for a final report to be rendered (the report_event time is from the first_final_report_id) rad_reports.report_event - rad_pacs_metadata.last_image
end_exam_first_image delay from when study was completed to when image transmission began rad_pacs_metadata.first_image - rad_exam_times.end_exam
end_exam_last_image delay from when study was completed to when entire exam was available for interpretation rad_pacs_metadata.last_image - rad_exam_times.end_exam
send_duration duration of transmitting the entire study to PACS (implicitly omits send time for first image) rad_pacs_metadata.last_image - rad_pacs_metadata.first_image
send_delay delay from image acquisition to start of transmission to PACS rad_pacs_metadata.first_image - rad_pacs_metadata.dicom_end
acquisition_duration duration of image acquisition rad_pacs_metadata.dicom_end - rad_pacs_metadata.dicom_begin
order_begin_acquisition how long it took for the first image to be acquired after the order was placed rad_pacs_metadata.dicom_begin - rad_exam_times.order_arrival
order_end_acquisition how long it took for the entire study to be acquired after the order was placed rad_pacs_metadata.dicom_end - rad_exam_times.order_arrival
acquisition_wait total time patient waited for image acquisition to start after patient arrival rad_pacs_metadata.dicom_begin - rad_exam_times.sign_in
acquisition_delay how delayed image acquisition started compared to the appointment time rad_pacs_metadata.dicom_begin - rad_exam_times.appointment
acquisition_ready how long it took from image acquisition to end of transmission to PACS rad_pacs_metadata.last_image - rad_pacs_metadata.dicom_end
acquisition_first_report how long after image acquisition is complete for the first preliminary or final report to be rendered (the report_event time is from the first_report_id) rad_reports.report_event - rad_pacs_metadata.dicom_end
acquisition_first_prelim how long after image acquisition is complete for a preliminary report to be rendered (the report_event time is from the first_prelim_report_id) rad_reports.report_event - rad_pacs_metadata.dicom_end
acquisition_first_final how long after image acquisition is complete for a final report to be rendered (the report_event time is from the first_final_report_id) rad_reports.report_event - rad_pacs_metadata.dicom_end
acquisition_series_duration total duration of scans in an exam, based on the non-overlapping series timestamps sum of all (dicom_series.series_end - dicom_series.series_begin) durations
acquisition_interseries_delay total delay between scans in an exam, based on the non-overlapping series timestamps dicom_study.study_end - dicom_study.study_begin - acquisition_series_duration
image_count number of instances (images) in the associated DICOM study for an exam matches dicom_studies value
series_count number of series in the associated DICOM study for an exam matches dicom_studies value

Metric-Timestamp Matrix

This matrix shows which timestamps are used to calculated metrics to allow easy understanding of the required data elements for any given measurement. The X indicates that a timestamp is not used for any pre-computed metric today.

Timestamps order_arrival schedule_event reschedule_event sign_in begin_reg check_in appointment begin_exam begin_exam_event dicom_begin dicom_end end_exam end_exam_event report_event first_image last_image cancelled pacs_read_time
order_arrival X X X order_begin_acquisition, order_end_acquisition order_complete X order_first_report, order_first_prelim, order_first_final order_first_image order_last_image X X
schedule_event X X access_wait transport X X schedule_first_report, schedule_first_prelim, schedule_first_final X X
reschedule_event X X X X X X X X X X X X X X X X X X
sign_in X X reg_wait total_wait X acquisition_wait X X X
begin_reg X reg_wait X reg_duration X X X X
check_in X reg_duration X X X X X
appointment access_wait X X exam_wait X acquisition_delay X X X
begin_exam transport X total_wait exam_wait X X exam_duration X X X
begin_exam_event X X X X X X X X X X X X X X X X X X
dicom_begin order_begin_acquisition X acquisition_wait acquisition_delay X X acquisition_duration X X X
dicom_end order_end_acquisition X X acquisition_duration X X acquisition_first_report, acquisition_first_prelim, acquisition_first_final send_delay acquisition_ready X X
end_exam order_complete X exam_duration X X X first_report_turn_around, prelim_turn_around, turn_around end_exam_first_image end_exam_last_image X X
end_exam_event X X X X X X X X X X X X X X X X X X
report_event order_first_report, order_first_prelim, order_first_final schedule_first_report, schedule_first_prelim, schedule_first_final X X acquisition_first_report, acquisition_first_prelim, acquisition_first_final first_report_turn_around, prelim_turn_around, turn_around X X first_image_prelim, first_image_final last_image_prelim, last_image_final X X
first_image order_first_image X X send_delay end_exam_first_image X first_image_prelim, first_image_final X send_duration X X
last_image order_last_image X X acquisition_ready end_exam_last_image X last_image_prelim, last_image_final send_duration X X X
cancelled X X X X X X X X X X X X X X X X X X
pacs_read_time X X X X X X X X X X X X X X X X X X

Reports

An exam has zero or more rad_reports which link back to the exam via the rad_exam_id foreign key. The complete, free-text of the result is in the report_body and the time that text was updated or signed or transcribed is the report_event timestamp. The impression-only section of the report text is available in the report_impression column and indexed separately from the report_body. The type of report event (preliminary, draft, edit, final, etc.) maps back to an external_system_status so the universal_event_type can be derived.

There is an optional transcriptionist_id foreign key back to employees if someone manually transcribed or edited the report. The other 4 foreign keys linking back to employees are the rad1_id through rad4_id columns: these represent contributing personnel to the report. rad1_id is considered to be the signer or approver (usually an attending-status radiologist), with rad2_id through rad4_id being contributors (usually residents or fellows).

Staff Scheduling

Staff scheduling for technologists and radiologists are supported to understand what shift (or task) someone was assigned to work during a given period of time. The utility of this model today is to allow for technologists or radiologists who cover shifts across locations, rad_exam_departments, or specialties to have the related attributes available for use in the rad_exam_facts table (e.g. a radiologist from specialty X read an exam for the specialty Y shift).

Recurring shifts (or task groupings) are defined in in the scheduling_task_groupings table, defined by a unique task_grouping_identifier and an external_system. There are both human-friendly and abbreviated task grouping names supported, as well as attributes that define the specific focus of a shift: a site and technologist rad_exam_department or radiologist specialty foreign key references are all carried forward into the rad_exam_facts table (below).

Instances of a shift (or task) are defined in the scheduling_tasks table, which has a FK back to the scheduling_task_groupings table, as well as its own unique task_identifier and external_system combination. The duration of the shift is delineated by the task_start and task_stop timestamps, the object of the task (currently only employees) is defined by a polymorphic relationship (task_entity_id and task_entity_table_name), with optional attributes to define if a shift is a holiday shift or deleted (will ignore or remove associated attributes for associated exams during this shift).

Rad Exam Facts

The rad_exam_facts table is a third normal form representation of common metrics and dimensions (filters) for radiology exam data. Large, aggregate queries against rad_exams directly in conjunction with information from joined tables can sometimes perform poorly. rad_exam_facts is built specifically to be performant for reporting/analytical queries.

The first thing to note is that not every row in rad_exams will exist in rad_exam_facts. Rows will be excluded for the following reasons:

The table is kept up to date in real-time by the data manager. This means whenever an exam is updated with new information, the corresponding rad_exam_fact row will also be updated with the same information. In the case of an update to a parent table of rad_exams (e.g. procedures) then all the matching rad_exams with that procedure will be updated appropriately. The caveat is that this will take some small period time (usually minutes) for the table to catch up to the change because could be millions of rows that need to be updated. An update (such as a change to cancelled status) that fits the exclusion logic listed above will result in entries being removed (or added) to the table.

For the following four timestamps:

the component values values get extracted to integers for use in filtering/searching, with a column name in the format of timestamp_component, so for the hour component of the end_exam timestamp, the field name would be end_exam_hour. The full list of components is:

Details for the other field in rad_exam_facts: