Compass 2 Schema Mappings

Table: appointment

Column name	Comments	ExpandProviding publisher
id	Unique Id of the appointment (specific to the database)
organization_id	Owning organisation (i.e. publisher)	EMIS
patient_id	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times
person_id	Unique individual across all organisations
practitioner_id	The clinician the activity is recorded against	EMIS
schedule_id	The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule	EMIS
start_date	The start date of the appointment	EMIS
planned_duration	The time allocated for the appointment, not necessarily the actual duration always in minutes	EMIS
actual_duration	Time between sent in and left always in minutes	EMIS
appointment_status_concept_id	The status of the appointment e.g. arrived/sent in/left/DNA	EMIS
patient_wait	How long the patient waited from being marked as arrived to being sent in	EMIS
patient_delay	How long the patient was delayed for	EMIS
date_time_sent_in	Date and time the patient was sent into the practitioner	EMIS
date_time_left	Date and time the patient left the practitioner	EMIS
source_id	Unique reference to the source of the appointment	EMIS
cancelled_date	The date the appointment was cancelled	EMIS

Table: episode_of_care

Column name	Comments	ExpandProviding publisher
id	Unique Id of the episode of care
organization_id	Owning organisation (i.e. publisher)
patient_id	The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times
person_id	Unique individual across all organisations
registration_type_concept_id	Reference to the registration type of the patient
registration_status_concept_id	Reference to the registration status of the patient
date_registered	The date the registration was started  for this episode of care
date_registered_end	The date the registration was ended  for this episode of care
usual_gp_practitioner_id	Reference to the usual GP for this episode of care

Table: patient

Column name	Comments	ExpandProviding publisher
id	Unique Id of the patient	–
organization_id	Owning organisation (i.e. publisher)	EMIS, ADT, BHRUT
person_id	Unique individual across all organisations
title	The title of the patient	EMIS, ADT, BHRUT
first_names	The first names of the patient	EMIS, ADT, BHRUT
last_name	The last name of the patient	EMIS, ADT, BHRUT
gender_concept_id	Reference to the gender of the patient	EMIS, ADT, BHRUT
nhs_number	The NHS number of the patient	EMIS, ADT, BHRUT
date_of_birth	The date of birth of the patient	EMIS, ADT, BHRUT
date_of_death	The date of death of the patient	EMIS, ADT, BHRUT
current_address_id	Reference to the current address of the patient	EMIS, ADT, BHRUT
ethnic_code_concept_id	Reference to the ethnicity of the patient	EMIS, ADT, BHRUT
registered_practice_organization_id	Reference to the organisation the patient is registered at	EMIS, ADT, BHRUT
birth_year		EMIS, ADT, BHRUT
birth_month		EMIS, ADT, BHRUT
birth_week		EMIS, ADT, BHRUT

Table: patient_additional

Column name	Comments	ExpandProviding publisher
id	Unique Id of the patient	–
property_id	IM reference (e.g. cause of death)	BHRUT
value_id	IM reference (e.g. COVID)	BHRUT
json_value	Where there is no mapped value_id, just raw JSON	–
text_value	Where there is no mapped value_id or raw JSON, just a basic text value	–