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	Globally unique identifier (UUID5 from consultation_section_id + organisation)	‘660e8400-e29b-41d4-a716-446655440000’
consultation_section_category_code	varchar	Short code indicating section type	’OBS’
event_guid	varchar	Reference to the specific clinical event	’C3D4E5F6-G7H8-9I0J-1K2L-M3N4O5P6Q7R8’
event_uuid	varchar	Globally unique identifier for the linked clinical event	’770e8400-e29b-41d4-a716-446655440000’
consultation_guid	varchar	EMIS consultation GUID	’A1B2C3D4-E5F6-7G8H-9I0J-K1L2M3N4O5P6’
consultation_uuid	varchar	Globally unique identifier for parent consultation	’550e8400-e29b-41d4-a716-446655440000’
patient_id	varchar	Patient identifier	’P001’
patient_guid	varchar	Patient GUID	’PAT-GUID-001’
patient_organisation_guid	varchar	Patient’s organisation GUID	’ORG-GUID-001’
consultation_organisation_guid	varchar	Consultation organisation GUID	’CONS-ORG-GUID-001’
effective_date_precision	varchar	Precision indicator for effective date (hardcoded as YMDT)	‘YMDT’
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’
authorising_user_in_role_id	varchar	User who authorised the section	’USER002’
location_guid	varchar	Physical location identifier	’LOC001’
location_code_id	bigint	Location code identifier	123456
location_type_description	varchar	Description of location type	’GP Surgery’
appointment_slot_guid	varchar	Reference to scheduled appointment slot	’SLOT001’
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	✓	✓
emis_event_guid	varchar	✓	✓
exa_event_guid	varchar	✓	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
emis_patient_id	varchar	✓	✓
registration_guid	varchar	✓	✓
emis_registration_organisation_guid	varchar	✓	✓
emis_consultation_organisation_guid	varchar	✓	✓
registration_ods_code	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_location_guid	varchar	✓	✓
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	✗	✓
authorising_user_in_role_id	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	✓	✓
emis_event_guid	varchar	✓	✓
exa_event_guid	varchar	✓	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
emis_patient_id	varchar	✓	✓
registration_guid	varchar	✓	✓
emis_registration_organisation_guid	varchar	✓	✓
emis_consultation_organisation_guid	varchar	✓	✓
registration_ods_code	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_location_guid	varchar	✓	✓
location_type_description	varchar	✓	✓
location_type_emis_code_id	bigint	✓	✓
emis_appointment_slot_guid	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	✗	✓
authorising_user_in_role_id	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