Skip to navigation

Patient 360

Each patient’s current demographics, plus the history of their coverage (including non-insurance payers and clinical trials) and notes, and their appointments, referrals, authorizations and record merges. Every table joins to export_patient on patient_id.

Tables

TableOne row perPrimary keyShape
export_patientPatientpatient_idCurrent state
export_patient_insurance_historyPatient coverage versionpatient_insurance_history_pkVersion history
export_patient_non_insurance_payer_historyPatient version and non-insurance payerpatient_non_insurance_payer_history_pkVersion history
export_patient_note_historyNote versionpatient_note_history_pkVersion history
export_patient_appointmentsAppointment, including cancelled onesappointment_idCurrent state
export_patient_referralsReferralreferral_idCurrent state
export_patient_authorizationsAuthorizationauthorization_idCurrent state
export_patient_mergePatient record mergepatient_merge_idCurrent state

Notes

  • Patients to encounters: match export_patient.mrn to export_encounter.patient_external_id within the same organization_id.
  • History tables have one row per version. Filter is_latest_record = TRUE for current state (is_latest_record_for_patient on the non-insurance payer history). That table also has valid_from and valid_to for as-of queries.
  • Merges: alternative_patient_mrn is the patient that was merged away, and primary_patient_mrn is the patient that received its records. All of the alternative patient’s encounters and data move to the primary patient.

Common queries

Current coverage per patient

SELECT
p.patient_id,
p.mrn,
ih.primary_coverage_payer_name,
ih.primary_coverage_member_id,
ih.secondary_coverage_payer_name
FROM export_patient p
JOIN export_patient_insurance_history ih
ON ih.patient_id = p.patient_id
AND ih.is_latest_record = TRUE
WHERE p.deactivated = FALSE

Upcoming appointments

SELECT
a.appointment_id,
a.patient_id,
a.start_timestamp,
a.status,
a.service_facility_name
FROM export_patient_appointments a
WHERE a.deactivated = FALSE
AND a.start_timestamp >= CURRENT_TIMESTAMP()