Schema

Core

The core schema represents the Consultation Section schema derived from EMIS source system.

Column Name	Data Type	Description	Example
consultation_section_id	bigint	The unique internal identifier for the consultation section within an organisation.	67890
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	‘CDB-001’
is_deleted	boolean	Indicates if this record should be considered soft deleted	FALSE
load_datetime	timestamp(6) with time zone	The datetime that the record was last upserted into the EXA data lake from EMIS source system with a relevant change from source system.	‘2023-05-12 14:30:15.000000 UTC’
_ingest_time	varchar	The load_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’
consultation_section_guid	varchar	The GUID for the consultation section within an organisation	‘B2C3D4E5-F6G7-8H9I-0J1K-L2M3N4O5P6Q7’
consultation_section_uuid	varchar	The UUID derived from consultation_section_id and organisation, providing a stable unique identifier.	‘660e8400-e29b-41d4-a716-446655440000’
consultation_section_category_code	varchar	The code indicating the category of consultation section.	‘OBS’
event_id	bigint	The unique internal identifier for the linked clinical event	12345
event_guid	varchar	The GUID for the linked clinical event within an organisation	‘C3D4E5F6-G7H8-9I0J-1K2L-M3N4O5P6Q7R8’
event_uuid	varchar	The UUID derived from event_id and organisation, providing a stable unique identifier.	‘770e8400-e29b-41d4-a716-446655440000’
consultation_id	bigint	The unique internal identifier for the consultation record within an organisation.	12345
consultation_guid	varchar	The GUID for the consultation record within an organisation	‘A1B2C3D4-E5F6-7G8H-9I0J-K1L2M3N4O5P6’
consultation_uuid	varchar	The UUID derived from consultation_id and organisation, providing a stable unique identifier.	‘550e8400-e29b-41d4-a716-446655440000’
patient_id	bigint	The unique internal identifier for the patient record within the organisation for whom the consultation was recorded.	12345
patient_guid	varchar	The GUID for the patient record within an organisation	‘b2c3d4e5-f6a7-8901-bcde-f12345678901’
patient_uuid	varchar	The UUID derived from patient_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
patient_organisation_id	bigint	The unique internal identifier for the organisation where the patient is registered	12345
patient_organisation_guid	varchar	The GUID of the organisation where the patient is registered	‘c9d0e1f2-a3b4-2c3d-7e8f-9a0b1c2d3e4f’
patient_organisation_uuid	varchar	The UUID derived from patient_organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
consultation_organisation_id	bigint	The unique internal identifier of the organisation where the consultation took place	12345
consultation_organisation_guid	varchar	The GUID of the organisation where the consultation was recorded	‘c9d0e1f2-a3b4-2c3d-7e8f-9a0b1c2d3e4f’
consultation_organisation_uuid	varchar	The UUID derived from consultation_organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
effective_date_precision	varchar	Precision indicator for effective date (hardcoded as YMDT)	‘YMDT’
consultation_section_heading_code_id	bigint	The unique EMIS code identifier for consultation section heading.	87901000000115
consultation_section_original_heading	varchar	The original heading for the consultation section.	‘Examination’
consultation_source_original_term	varchar	The EMIS term for the consultation source code	‘GP Surgery’
consultation_source_code_id	bigint	The identifier of the code describing the consultation source (e.g. GP Surgery, Telephone)	1672871000006114
availability_datetime	timestamp(6) with time zone	The date and time the consultation was recorded in the source system	‘2025-01-15 14:00:00.000000 UTC’
effective_datetime	timestamp(6) with time zone	The date and time when the consultation came into effect	‘2025-01-15 14:30:00.000000 UTC’
entered_by_user_in_role_id	bigint	The unique identifier of the user who entered the consultation record	445
entered_by_user_in_role_uuid	varchar	The UUID derived from entered_by_user_in_role_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
authorising_user_in_role_id	bigint	The unique identifier of the clinician who authorised or authored the consultation record	55
authorising_user_in_role_uuid	varchar	The UUID derived from authorising_user_in_role_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
location_id	bigint	The unique identifier of the location within an organisation	123
location_guid	varchar	The GUID of the location where the consultation was recorded	‘c3d4e5f6-a7b8-9012-cdef-123456789012’
location_uuid	varchar	The UUID derived from location_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
location_code_id	bigint	The unique EMIS location code identifier	1563411000006111
location_type_id	bigint	The unique identifier of the location type	96
location_type_description	varchar	The description of location type	‘GP Surgery’
appointment_slot_id	bigint	The unique internal identifier for the appointment slot	12345
appointment_slot_guid	varchar	The GUID of the appointment slot in which the consultation was booked	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
appointment_slot_uuid	varchar	The UUID derived from appointment_slot_id and organisation, providing a stable unique identifier	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
is_complete	boolean	Indicates whether the consultation has been completed	TRUE
is_confidential	boolean	Indicates whether the consultation record is confidential. This is based on confidentiality set against the patient and consultation.	FALSE
is_sensitive	boolean	Indicates whether the consultation section record is flagged as sensitive.	FALSE
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model.	‘2025-01-15 14:30:22.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20250115143022’

Vanilla

Not used in this schema

Apollo

Not used in this schema

Artemis

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
consultation_section_id	bigint	✗	✓
emis_consultationsection_guid	varchar	✓	✓	consultation_section_guid
exa_consultationsection_guid	varchar	✓	✓	consultation_section_uuid
hestia_event_type	varchar	✓	✓	consultation_section_category_code
consultation_section_original_heading	varchar	✓	✓
event_id	bigint	✗	✓
emis_event_guid	varchar	✓	✓	event_guid
exa_event_guid	varchar	✓	✓	event_uuid
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
consultation_organisation_id	bigint	✗	✓
emis_consultation_organisation_guid	varchar	✓	✓	consultation_organisation_guid
consultation_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
location_type_emis_code_id	bigint	✓	✓	location_code_id
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓
is_complete	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓	is_sensitive
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping
is_deleted	boolean
load_datetime	timestamp(6) with time zone
pseudo_consultation_section_uuid	varchar	consultation_section_uuid
consultation_section_category_code	varchar
pseudo_event_uuid	varchar	event_uuid
pseudo_consultation_uuid	varchar	consultation_uuid
pseudo_patient_uuid	varchar	patient_uuid
pseudo_patient_organisation_uuid	varchar	patient_organisation_uuid
pseudo_consultation_organisation_uuid	varchar	consultation_organisation_uuid
consultation_section_heading_code_id	bigint
consultation_section_original_heading	varchar
consultation_source_original_term	varchar
consultation_source_code_id	bigint
availability_datetime	timestamp(6) with time zone
effective_datetime	timestamp(6) with time zone
effective_date_precision	varchar
pseudo_location_uuid	varchar	location_uuid
location_code_id	bigint
location_type_id	bigint
location_type_description	varchar
pseudo_appointment_slot_uuid	varchar	appointment_slot_uuid
is_complete	boolean
is_confidential	boolean
is_sensitive	boolean
organisation	varchar
transform_datetime	timestamp(6) with time zone

Hestia

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
consultation_section_id	bigint	✗	✓
emis_consultationsection_guid	varchar	✓	✓	consultation_section_guid
exa_consultationsection_guid	varchar	✓	✓	consultation_section_uuid
hestia_event_type	varchar	✓	✓	consultation_section_category_code
consultation_section_original_heading	varchar	✓	✓
event_id	bigint	✗	✓
emis_event_guid	varchar	✓	✓	event_guid
exa_event_guid	varchar	✓	✓	event_uuid
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
consultation_organisation_id	bigint	✗	✓
emis_consultation_organisation_guid	varchar	✓	✓	consultation_organisation_guid
consultation_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
location_type_emis_code_id	bigint	✓	✓	location_code_id
appointment_slot_id	bigint	✗	✓
emis_appointment_slot_guid	varchar	✓	✓	appointment_slot_guid	Renamed from emis_appoinment_slot_guid in V1
appointment_slot_uuid	varchar	✗	✓
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
consultation_source_emis_original_term	varchar	✓	✓	consultation_source_original_term
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓
complete	boolean	✓	✓	is_complete
confidential_flag	boolean	✓	✓	is_confidential
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓	is_sensitive
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_code	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

Not used in this schema

Prometheus

Not used in this schema

Pseudo_Anon

Not used in this schema

Themis

Not used in this schema

Zeus

Not used in this schema