Schema

Raw

The raw consultation section schema represents the core consultation section data extracted from the serving layer of the iPCV pipeline:

Column Name	Data Type	Description	Example
consultation_section_id	bigint	Unique identifier for the consultation section	67890
organisation	varchar	Organisation identifier where consultation occurred	’CDB-001’
is_deleted	boolean	Soft deletion flag	false
_ingest_time	timestamp	Data ingestion timestamp	’2025-01-15 14:30:22.123456’
consultation_section_guid	varchar	EMIS consultation section GUID	’B2C3D4E5-F6G7-8H9I-0J1K-L2M3N4O5P6Q7’
consultation_section_uuid	varchar	Deterministic UUID5 generated from consultation_section_id + organisation	’660e8400-e29b-41d4-a716-446655440000’
consultation_section_category_code	varchar	Short code indicating section type	’OBS’
event_id	bigint	internal identifier for the linked clinical event	12345
event_guid	varchar	Reference to the specific clinical event	’C3D4E5F6-G7H8-9I0J-1K2L-M3N4O5P6Q7R8’
event_uuid	varchar	Deterministic UUID5 generated from event_id + organisation	’770e8400-e29b-41d4-a716-446655440000’
consultation_id	bigint	internal identifier for parent consultation	12345
consultation_guid	varchar	EMIS consultation GUID	’A1B2C3D4-E5F6-7G8H-9I0J-K1L2M3N4O5P6’
consultation_uuid	varchar	Deterministic UUID5 generated from consultation_id + organisation	’550e8400-e29b-41d4-a716-446655440000’
patient_id	varchar	Patient identifier	’P001’
patient_guid	varchar	Patient GUID	’PAT-GUID-001’
patient_uuid	varchar	Deterministic UUID5 generated from patient_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
patient_organisation_id	bigint	Patient’s organisation internal identifier	12345
patient_organisation_guid	varchar	Patient’s organisation GUID	’ORG-GUID-001’
patient_organisation_uuid	varchar	Deterministic UUID5 generated from patient_organisation_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
consultation_organisation_id	bigint	Consultation organisation internal identifier	12345
consultation_organisation_guid	varchar	Consultation organisation GUID	’CONS-ORG-GUID-001’
consultation_organisation_uuid	varchar	Deterministic UUID5 generated from consultation_organisation_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
effective_date_precision	varchar	Precision indicator for effective date (hardcoded as YMDT)	‘YMDT’
consultation_section_heading_code_id	bigint	Internal identifier for the consultation section heading code	12345
consultation_section_original_heading	varchar	Original heading/title for the consultation section	’Examination’
consultation_source_original_term	varchar	Original term for consultation source	’GP Surgery’
consultation_source_code_id	bigint	Code for consultation source location	1572871000006117
availability_datetime	timestamp	When consultation slot became available	’2025-01-15 14:00:00’
effective_datetime	timestamp	When the consultation section was recorded	’2025-01-15 14:30:00’
entered_by_user_in_role_id	varchar	User who entered the section	’USER001’
entered_by_user_in_role_uuid	varchar	Deterministic UUID5 generated from entered_by_user_in_role_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
authorising_user_in_role_id	varchar	User who authorised the section	’USER002’
authorising_user_in_role_uuid	varchar	Deterministic UUID5 generated from authorising_user_in_role_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
location_id	bigint	Internal identifier for the location	12345
location_guid	varchar	Physical location identifier	’LOC001’
location_uuid	varchar	Deterministic UUID5 generated from location_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
location_code_id	bigint	Location code identifier	123456
location_type_id	bigint	Internal identifier for the location type	12345
location_type_description	varchar	Description of location type	’GP Surgery’
appointment_slot_id	bigint	Internal identifier for the appointment slot	12345
appointment_slot_guid	varchar	Reference to scheduled appointment slot	’SLOT001’
appointment_slot_uuid	varchar	Deterministic UUID5 generated from appointment_slot_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
is_complete	boolean	Whether consultation is complete	true
is_confidential	boolean	Current confidentiality status	false
is_sensitive	boolean	Current sensitivity flag	false
was_confidential	boolean	Historical confidentiality state for tracking transitions	false
was_sensitive	boolean	Historical sensitivity state for tracking transitions	false
transform_datetime	timestamp	Transform processing timestamp	’2025-01-15 14:30:22’
_execution_date	varchar	Pipeline execution date (yyyyMMdd format)	‘20250115’

Vanilla

Not used in this schema

Apollo

Not used in this schema

Artemis

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	✗	✓
consultation_section_id	bigint	✗	✓
emis_consultationsection_guid	varchar	✓	✓
exa_consultationsection_guid	varchar	✓	✓
hestia_event_type	varchar	✓	✓
consultation_section_original_heading	varchar	✓	✓
event_id	bigint	✗	✓
emis_event_guid	varchar	✓	✓
exa_event_guid	varchar	✓	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
emis_patient_id	varchar	✓	✓
registration_guid	varchar	✓	✓
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
patient_organisation_uuid	varchar	✗	✓
consultation_organisation_id	bigint	✗	✓
emis_consultation_organisation_guid	varchar	✓	✓
consultation_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓
location_uuid	varchar	✗	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
location_type_emis_code_id	bigint	✓	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_user_in_role_id	varchar	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
authorising_user_in_role_id	varchar	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
recorded_date	timestamp	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
complete	boolean	✓	✓
confidential_flag	boolean	✓	✓
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	✓	✓
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
_execution_date	date	✓	✓

Hestia

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	✗	✓
consultation_section_id	bigint	✗	✓
emis_consultationsection_guid	varchar	✓	✓
exa_consultationsection_guid	varchar	✓	✓
hestia_event_type	varchar	✓	✓
consultation_section_original_heading	varchar	✓	✓
event_id	bigint	✗	✓
emis_event_guid	varchar	✓	✓
exa_event_guid	varchar	✓	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
emis_patient_id	varchar	✓	✓
registration_guid	varchar	✓	✓
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
patient_organisation_uuid	varchar	✗	✓
consultation_organisation_id	bigint	✗	✓
emis_consultation_organisation_guid	varchar	✓	✓
consultation_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓
location_uuid	varchar	✗	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
location_type_emis_code_id	bigint	✓	✓
appointment_slot_id	bigint	✗	✓
emis_appointment_slot_guid	varchar	emis_appoinment_slot_guid	✓
appointment_slot_uuid	varchar	✗	✓
consultation_source_emis_code_id	bigint	✓	✓
consultation_source_emis_original_term	varchar	✓	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_user_in_role_id	varchar	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
authorising_user_in_role_id	varchar	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
recorded_date	timestamp	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
complete	boolean	✓	✓
confidential_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	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓
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	varchar	✗	✓
_execution_date	date	✓	✓

Themis

Not used in this schema

Zeus

Not used in this schema

Pseudo_Anon

Not used in this schema

Prometheus

Not used in this schema

Olympus

Not used in this schema