Schema

Core

The core schema represents the Immunisation schema derived from EMIS source system.

Column Name	Data Type	Description	Example
is_deleted	boolean	Indicates if this record should be considered soft deleted. Also set to true when a record transitions away from being an immunisation	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’
observation_id	bigint	The unique internal identifier for the immunisation record within an organisation.	987654
observation_guid	varchar	The GUID for the immunisation record within an organisation.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
observation_uuid	varchar	The UUID derived from observation_id and organisation, providing a stable unique identifier.	‘b2c3d4e5-f6a7-5b6c-0d1e-2f3a4b5c6d7e’
patient_id	bigint	The unique internal identifier for the patient record within the organisation for whom the immunisation was recorded.	12345
patient_guid	varchar	The GUID for the patient record within an organisation	‘c3d4e5f6-a7b8-6c7d-1e2f-3a4b5c6d7e8f’
patient_uuid	varchar	The UUID derived from patient_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
consultation_id	bigint	The unique internal identifier for the consultation record within an organisation in which the immunisation was recorded. It will be derived from its parent observation if it doesn’t exist against the immunisation record	55678
consultation_guid	varchar	The GUID for the consultation record within an organisation	‘d4e5f6a7-b8c9-7d0e-2f3a-4b5c6d7e8f9a’
consultation_uuid	varchar	The UUID derived from consultation_id and organisation, providing a stable unique identifier.	‘e5f6a7b8-c9d0-8e1f-3a4b-5c6d7e8f9a0b’
consultation_section_id	bigint	The unique internal identifier for the consultation section in which the immunisation was recorded within an organisation.	12345
consultation_section_uuid	varchar	The UUID derived from consultation_section_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
problem_observation_id	bigint	The unique internal identifier for the first problem observation within an organisation linked to this immunisation across all episodes (Associated and Episode Follow On). The records are prioritised by effective_datetime, availability_datetime and observation_id.	33211
problem_observation_guid	varchar	The GUID for the first problem observation within an organisation linked to this immunisation	‘f6a7b8c9-d0e1-9f2a-4b5c-6d7e8f9a0b1c’
problem_observation_uuid	varchar	The UUID derived from problem_observation_id and organisation, providing a stable unique identifier.	‘a7b8c9d0-e1f2-0a3b-5c6d-7e8f9a0b1c2d’
parent_observation_id	bigint	The unique internal identifier for the parent observation within an organisation	22100
parent_observation_guid	varchar	The GUID for the parent observation within an organisation	‘b1c2d3e4-f5a6-4b5c-8d9e-0f1a2b3c4d5e’
parent_observation_uuid	varchar	The UUID derived from parent_observation_id and organisation, providing a stable unique identifier.	‘c2d3e4f5-a6b7-5c6d-9e0f-1a2b3c4d5e6f’
authorising_user_in_role_id	bigint	The unique identifier of the clinician who authorised or authored the immunisation record	11223
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’
entered_by_user_in_role_id	bigint	The unique identifier of the user who entered the immunisation record	33445
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’
observation_organisation_id	bigint	The unique internal identifier of the organisation where the observation was recorded	54321
observation_organisation_guid	varchar	The GUID of the organisation where the observation was recorded	‘b8c9d0e1-f2a3-1b4c-6d7e-8f9a0b1c2d3e’
observation_organisation_uuid	varchar	The UUID derived from observation_organisation_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-2c5d-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’
code_id	bigint	The unique EMIS code identifier for the clinical code associated with the immunisation	8085301000006112
consultation_source_code_id	bigint	The identifier of the code describing the consultation source (e.g. GP Surgery, Telephone)	1672851000006116
consultation_source_original_term	varchar	The EMIS term for the consultation source code	‘GP Surgery’
original_term	varchar	The EMIS term used to record the immunisation.	‘Influenza vaccination’
associated_text	varchar	Free text associated with the immunisation.	‘Patient tolerated well’
batch_number	array(varchar)	The batch or lot number of the vaccine administered. This is derived from the XML data in qualifiers.	[‘BN12345A’]
manufacturer	array(varchar)	The manufacturer of the vaccine. This is derived from the XML data in qualifiers.	[‘Pfizer’]
injection_site	array(varchar)	The anatomical site where the vaccine was administered. This is derived from the XML data in qualifiers.	[‘Left deltoid’]
expiry_date	array(varchar)	The expiry date of the vaccine batch. This is derived from the XML data in qualifiers.	[‘2024-12-31’]
gms	array(varchar)	General Medical Services indicator. This is derived from the XML data in qualifiers.	[‘GMS’]
qualifiers	varchar	Raw XML qualifier data associated with the immunisation. Qualifiers are extended data fields attached to a specific clinical or vaccination code that allow users to record highly detailed, granular information without needing to create separate, standalone clinical codes.	‘<qualifiers>…</qualifiers>’
numeric_unit	varchar	Unit for the numeric measurement value	‘mg’
unit_of_measure	varchar	Description of the unit of measure	‘milligrams’
numeric_operator	varchar	Operator for numeric value comparison (e.g. =, >, <)	’=’
numeric_value	decimal(19, 3)	Numeric measurement value associated with the observation	0.5
ucum_code	varchar	Unified Code for Units of Measure (UCUM) code for the measurement unit	‘mg’
range_minimum_operator	varchar	Comparison operator for the minimum range value	’>=’
range_minimum_text	varchar	Minimum value of the reference range which includes both numeric and text range values	‘0.0’
range_minimum	decimal(19, 3)	Derived from range_minimum_text column for numeric range values. This is provided only for backward compatibility.	0.0
range_maximum_operator	varchar	Comparison operator for the maximum range value	’<=’
range_maximum_text	varchar	Maximum value of the reference range which includes both numeric and text range values	‘1.0’
range_maximum	decimal(19, 3)	Derived from range_maximum_text column for numeric range. This is provided only for backward compatibility.	1.0
range_units	varchar	Units used for the reference range	‘mg’
range_qualifier_id	bigint	Internal identifier for the range qualifier	12345
range_qualifier_description	varchar	Description of the range qualifier (e.g Therapeutic Interval, Toxic Interval and Recommended Interval )	‘Normal’
is_abnormal	boolean	Indicates whether the observation value is outside the normal reference range	FALSE
effective_datetime	timestamp(6) with time zone	The date and time when the immunisation came into effect	‘2023-09-01 10:00:00.000000 UTC’
effective_datetime_precision	varchar	Precision of the effective datetime; set to ‘YMDT’.	‘YMDT’
availability_datetime	timestamp(6) with time zone	The date and time the immunisation was recorded in the source system	‘2023-09-01 10:30:00.000000 UTC’
problem_end_datetime	timestamp(6) with time zone	The date and time the immunisation which has been identified as a problem ended	‘2023-09-01 09:00:00.000000 UTC’
episodicity	bigint	Numeric code representing the episodicity of the observation	1
episodicity_description	varchar	Description of the episodicity (e.g. first, new, review, flare up, end)	‘First’
is_parent	boolean	Indicates whether this observation is a parent (template header) observation	FALSE
is_sensitive	boolean	Indicates whether the immunisation record is flagged as sensitive. The sensitive reference set codes are 999004351000000109( gender related issues), 999004371000000100(assisted fertilisation), 999004361000000107(termination of pregnancy) and 999004381000000103 (sexually transmitted disease)	FALSE
is_confidential	boolean	Indicates whether the immunisation record is confidential. This is based on confidentiality set against the patient/consultation/immunisation/parent observation	FALSE
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	‘CDB-12345’
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model.	‘2023-09-01 10:30:15.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230901103015’

Vanilla

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
injection_site	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
gms	array(varchar)	✓	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
problem_end_datetime	timestamp(6) with time zone	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
consultation_source_original_term	varchar	✗	✓
associated_text	varchar	✗	✓
qualifiers	varchar	✓	✓
uom	varchar	✓	✓	numeric_unit
uom_dmd	varchar	✓	✓	unit_of_measure
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
ucum_code	varchar	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_abnormal	boolean	✗	✓
episodicity	bigint	✗	✓
episodicity_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗

Apollo

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
patient_id	bigint	✗	✓
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
patient_organisation_guid	varchar	✗	✓
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
injection_site	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
gms	array(varchar)	✓	✓
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
numeric_unit	varchar	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
snomed_concept_id	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_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	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
injection_site	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
gms	array(varchar)	✓	✓
qualifiers	varchar	✓	✓
uom	varchar	✓	✓	numeric_unit
uom_dmd	varchar	✓	✓	unit_of_measure
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping
is_deleted	boolean
pseudo_observation_uuid	varchar	observation_uuid
pseudo_patient_uuid	varchar	patient_uuid
pseudo_consultation_uuid	varchar	consultation_uuid
pseudo_consultation_section_uuid	varchar	consultation_section_uuid
pseudo_problem_observation_uuid	varchar	problem_observation_uuid
pseudo_observation_organisation_uuid	varchar	observation_organisation_uuid
pseudo_patient_organisation_uuid	varchar	patient_organisation_uuid
pseudo_parent_observation_uuid	varchar	parent_observation_uuid
batch_number	array(varchar)
manufacturer	array(varchar)
expiry_date	array(varchar)
injection_site	array(varchar)
gms	array(varchar)
effective_datetime	timestamp(6) with time zone
effective_datetime_precision	varchar
availability_datetime	timestamp(6) with time zone
problem_end_datetime	timestamp(6) with time zone
code_id	bigint
consultation_source_code_id	bigint
consultation_source_original_term	varchar
original_term	varchar
numeric_operator	varchar
numeric_value	decimal(19, 3)
numeric_unit	varchar
ucum_code	varchar
unit_of_measure	varchar
range_minimum_operator	varchar
range_minimum_text	varchar
range_maximum_operator	varchar
range_maximum_text	varchar
range_units	varchar
range_qualifier_id	bigint
range_qualifier_description	varchar
episodicity	bigint
episodicity_description	varchar
is_abnormal	boolean
is_parent	boolean
is_sensitive	boolean
is_confidential	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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_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	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
injection_site	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
gms	array(varchar)	✓	✓
qualifiers	varchar	✓	✓
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
numeric_unit	varchar	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓	consultation_guid
exa_consultation_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
pseudo_registration_guid	varchar	✓	✓	patient_guid
pseudo_patient_uuid	varchar	✗	✓	patient_uuid
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_enteredby_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	✗	✓
effective_date	date	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
entered_date	date	✓	✓	availability_datetime
entered_time	varchar	✓	✓	availability_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
numeric_unit	varchar	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
transform_datetime	timestamp(6) with time zone	✗	✓
organisation	varchar	✓	✓
_execution_date	varchar	✓	✓

Prometheus

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓	consultation_guid
exa_consultation_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
pseudo_registration_guid	varchar	✓	✓	patient_guid
pseudo_patient_uuid	varchar	✗	✓	patient_uuid
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
effective_date	date	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
entered_date	date	✓	✓	availability_datetime
entered_time	varchar	✓	✓	availability_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
numeric_unit	varchar	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓		Exists in v1 with a different name

Pseudo_Anon

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	✗	✓
observation_id	bigint	✗	✓
id_type5	varchar	✓	✓	observation_uuid
observation_guid	varchar	✓	✓		Renamed from id in V1
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
snomed_concept_id	varchar	✓	✗
snomed_description_id	varchar	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
code_id	bigint	✓	✓
patient_guid	varchar	✓	✓
patient_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
practitioner_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
consultation_id	bigint	✗	✓
consultation_guid	varchar	✓	✓
consultation_uuid	varchar	✗	✓
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
patient_organisation_guid	varchar	✗	✓
patient_organisation_uuid	varchar	✗	✓
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
effective_date_precision	varchar	✗	✓	effective_datetime_precision
rubric	varchar	✓	✓	original_term
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
numeric_unit	varchar	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
is_confidential	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

Themis

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_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	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
injection_site	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
gms	array(varchar)	✓	✓
qualifiers	varchar	✓	✓
uom	varchar	✓	✓	numeric_unit
uom_dmd	varchar	✓	✓	unit_of_measure
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗

Zeus

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	✗	✓
observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
parent_observation_id	bigint	✗	✓
parent_observation_guid	varchar	✗	✓
parent_observation_uuid	varchar	✗	✓
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✗	✓
problem_observation_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
pseudo_registration_guid_ourea	varchar	✓	✓	patient_guid
emis_patient_id	bigint	✓	✗	patient_id
pseudo_patient_uuid	varchar	✗	✓	patient_uuid
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_uuid	varchar	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
batch_number	array(varchar)	✓	✓
manufacturer	array(varchar)	✓	✓
injection_site	array(varchar)	✓	✓
expiry_date	array(varchar)	✓	✓
gms	array(varchar)	✓	✓
qualifiers	varchar	✓	✓
uom	varchar	✓	✓	numeric_unit
uom_dmd	varchar	✓	✓	unit_of_measure
numeric_operator	varchar	✗	✓
numeric_value	decimal(19, 3)	✗	✓
range_minimum_operator	varchar	✗	✓
range_minimum_text	varchar	✗	✓
range_minimum	decimal(19, 3)	✗	✓
range_maximum_operator	varchar	✗	✓
range_maximum_text	varchar	✗	✓
range_maximum	decimal(19, 3)	✗	✓
range_units	varchar	✗	✓
range_qualifier_id	bigint	✗	✓
range_qualifier_description	varchar	✗	✓
is_parent	boolean	✗	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
registration_guid	varchar	✓	✗	patient_guid
data_filter	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓