Schema

Raw

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

Column Name	Data Type	Description	Example
consultation_id	bigint	Unique identifier for the consultation	12345
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_guid	varchar	EMIS consultation GUID	’A1B2C3D4-E5F6-7G8H-9I0J-K1L2M3N4O5P6’
consultation_uuid	varchar	Globally unique identifier (UUID5 from consultation_id + organisation)	‘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_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 consultation occurred	’2025-01-15 14:30:00’
entered_by_user_in_role_id	varchar	User who entered the consultation	’USER001’
authorising_user_in_role_id	varchar	User who authorised the consultation	’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
has_consultation_sections	boolean	Whether consultation has sections	true
is_confidential	boolean	Current confidentiality status	false
was_confidential	boolean	Historical confidentiality 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

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	✗	✓
emis_consultation_id	bigint	✓	✓
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	✓	✓
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	✓	✓
has_sections_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✗
dummy_patient_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	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
_execution_date	date	✓	✓

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	✗	✓
emis_consultation_id	bigint	✓	✓
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	✓	✓
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	✓	✓
has_sections_flag	boolean	✗	✓
confidential_patient_flag	boolean	✓	✗
dummy_patient_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	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
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	✗	✓
emis_encounter_id	bigint	✗	✓
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	✓	✓
has_sections_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	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
_execution_date	date	✓	✓

Themis

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	✗	✓
emis_consultation_id	bigint	✓	✓
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	✓	✓
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	✓	✓
has_sections_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_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	✓	✗
sensitive_flag	boolean	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
_execution_date	date	✓	✓

Pseudo_Anon

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
consultation_id	bigint	✗	✓
consultation_guid	varchar	✓	✓
id_type5	varchar	✓	✓
patient_guid	varchar	✓	✓
organisation_guid	varchar	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
entered_date	date	✓	✓
entered_time	time	✓	✓
clinician_user_in_role_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_user_in_role_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	✗	✓
appointment_slot_guid	varchar	✓	✓
consultation_source_term	varchar	✓	✓
consultation_source_code_id	bigint	✓	✓
complete	boolean	✓	✓
deleted	boolean	✓	✗
is_confidential	boolean	✓	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
execution_date	date	✓	✓

Prometheus

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
emis_consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓
exa_consultation_guid	varchar	✓	✓
pseudo_registration_guid	varchar	✓	✓	Anonymised using SECRET_HASHER
emis_registration_organisation_guid	varchar	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
entered_date	date	✓	✓
entered_time	time	✓	✓
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_enteredby_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	✗	✓
emis_appointment_slot_guid	varchar	✓	✓
emis_original_term	varchar	✓	✓
consultation_source_emis_code_id	bigint	✓	✓
complete	boolean	✓	✓
deleted	boolean	✓	✗
confidential_flag	boolean	✓	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
execution_date	date	✓	✓

Olympus

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	timestamp	✓	✓
is_deleted	boolean	✗	✓
emis_consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓
exa_consultation_guid	varchar	✓	✓
pseudo_registration_guid	varchar	✓	✓	Anonymised using SECRET_HASHER
emis_registration_organisation_guid	varchar	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
entered_date	date	✓	✓
entered_time	time	✓	✓
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_enteredby_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	✗	✓
emis_appointment_slot_guid	varchar	✓	✓
emis_original_term	varchar	✓	✓
consultation_source_emis_code_id	bigint	✓	✓
complete	boolean	✓	✓
deleted	boolean	✓	✗
confidential_flag	boolean	✓	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
organisation	varchar	✓	✓
transform_datetime	varchar	✗	✓
_execution_date	date	✓	✓

Zeus

Not used in this schema