Schema

Core

Problem Drug Record

The Problem Drug Record Core model links patient problems to prescriptions. Each row is uniquely identified by problem_observation_id, drug_record_id, and organisation.

Column Name	Data Type	Description	Example / Values
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-09-01 09:00:00.000000 UTC’
_ingest_time	varchar	The load_datetime formatted as yyyyMMddHHmmss. This is a legacy column maintained for backward compatibility	‘20230512143015’
problem_observation_id	bigint	The unique internal identifier for the problem observation record linked to drug record, within an organisation.	123456789
drug_record_id	bigint	The unique internal identifier for the drug record linked to a problem, within an organisation.	123456789
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	‘CDB-1234’
is_deleted	boolean	Indicates whether a drug record has been deleted at source.	FALSE
problem_observation_guid	varchar	The GUID for the problem within an organisation linked to this drug record	‘f6a7b8c9-d0e1-9f2a-4b5c-6d7e8f9a0b1c’
problem_observation_uuid	varchar	The UUID derived from problem_observation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
drug_record_guid	varchar	The GUID for the drug record linked to problem, within an organisation.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
drug_record_uuid	varchar	The UUID derived from drug_record_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
patient_id	bigint	The unique internal identifier for the patient record within the organisation, for whom the drug record was prescribed.	123456
patient_guid	varchar	The unique GUID for the patient record within an organisation	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
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-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’
drug_record_organisation_id	bigint	The unique internal identifier of the organisation where the drug record was prescribed	12345
drug_record_organisation_guid	varchar	The GUID of the organisation where the drug record was prescribed.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
drug_record_organisation_uuid	varchar	The UUID derived from drug_record_organisation_id and organisation, providing a stable unique identifier	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
availability_datetime	timestamp(6) with time zone	The date and time the drug record was recorded in the source system	‘2023-09-01 10:30:00.000000 UTC’
drug_record_code_id	bigint	The unique EMIS code identifier for the drug record prescribed record	1505741000033119
observation_code_id	bigint	The unique EMIS code identifier for the clinical code associated with the problem	2716321000006116
confidentiality_policy_id	bigint	Identifier of the applied confidentiality policy	-1
is_sensitive	boolean	Indicates whether the drug 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 observation record is confidential. This is based on confidentiality policy set against the patient / drug record	TRUE
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.	‘2024-01-15 03:00:05.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This is a legacy column provided only for backward compatibility with.	‘20240115030005’

Problem Issue Record

The Problem Issue Record Core model links patient problems to prescription issues. Each row is uniquely identified by problem_observation_id, issue_record_id, and organisation.

Column Name	Data Type	Description	Example / Values
_ingest_time	varchar	The load_datetime formatted as yyyyMMddHHmmss. This is a legacy column maintained for backward compatibility	‘20230512143015’
problem_observation_id	bigint	The unique internal identifier for the problem observation record linked to issue record, within an organisation.	123456789
issue_record_id	bigint	The unique internal identifier for the issue record linked to a problem, within an organisation.	111222333
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	‘CDB-1234’
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-09-01 09:00:00.000000 UTC’
is_deleted	boolean	Indicates whether an issue record has been deleted at source.	FALSE
problem_observation_guid	varchar	The GUID for the problem within an organisation linked to this issue record	‘f6a7b8c9-d0e1-9f2a-4b5c-6d7e8f9a0b1c’
problem_observation_uuid	varchar	The UUID derived from problem_observation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
issue_record_guid	varchar	The GUID for the issue record linked to problem, within an organisation.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
issue_record_uuid	varchar	The UUID derived from issue_record_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
patient_id	bigint	The unique internal identifier for the patient record within the organisation, for whom the issue record was issued.	123456
patient_guid	varchar	The unique GUID for the patient record within an organisation	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
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-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’
issue_record_organisation_id	bigint	The unique internal identifier of the organisation where the issue record was issued	112233
issue_record_organisation_guid	varchar	The GUID of the organisation where the issue record was issued.	‘xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’
issue_record_organisation_uuid	varchar	The UUID derived from issue_record_organisation_id and organisation, providing a stable unique identifier	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
availability_datetime	timestamp(6) with time zone	The date and time the issue record was recorded in the source system	‘2023-09-01 10:30:00.000000 UTC’
issue_record_code_id	bigint	The unique EMIS code identifier for the issued record	578241000033111
observation_code_id	bigint	The unique EMIS code identifier for the clinical code associated with the problem	18423015
confidentiality_policy_id	bigint	Identifier of the applied confidentiality policy	-1
is_sensitive	boolean	Indicates whether the issue 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 observation record is confidential. This is based on confidentiality policy set against the patient / issue record	TRUE
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.	‘2024-01-15 03:00:05.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This is a legacy column provided only for backward compatibility with.	‘20240115030005’

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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_emis_observation_guid	varchar	✓	✓	problem_observation_guid
problem_exa_observation_guid	varchar	✓	✓	problem_observation_uuid
drug_record_id	bigint	✗	✓
emis_drug_guid	varchar	✓	✓	drug_record_guid
exa_drug_guid	varchar	✓	✓	drug_record_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	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
fhir_medication_intent	varchar	✓	✓		Always NULL in V2
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
drug_record_code_id	bigint	✓	✓		Renamed from emis_code_id in V1
observation_code_id	bigint	✗	✓
sensitive_flag	boolean	✓	✓	is_sensitive
confidentiality_policy_id	bigint	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_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	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_emis_observation_guid	varchar	✓	✓	problem_observation_guid
problem_exa_observation_guid	varchar	✓	✓	problem_observation_uuid
issue_record_id	bigint	✗	✓
emis_issue_guid	varchar	✓	✓	issue_record_guid
exa_issue_guid	varchar	✓	✓	issue_record_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	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
fhir_medication_intent	varchar	✓	✓		Always NULL in V2
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
issue_record_code_id	bigint	✓	✓		Renamed from emis_code_id in V1
observation_code_id	bigint	✗	✓
sensitive_flag	boolean	✓	✓	is_sensitive
confidentiality_policy_id	bigint	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_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	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗

Apollo

Problem Drug Record

Not used in this schema

Problem Issue Record

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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_emis_observation_guid	varchar	✓	✓	problem_observation_guid
problem_exa_observation_guid	varchar	✓	✓	problem_observation_uuid
drug_record_id	bigint	✗	✓
emis_drug_guid	varchar	✓	✓	drug_record_guid
exa_drug_guid	varchar	✓	✓	drug_record_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	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
fhir_medication_intent	varchar	✓	✓		Always NULL in V2
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
drug_record_code_id	bigint	✓	✓		Renamed from emis_code_id in V1
observation_code_id	bigint	✗	✓
sensitive_flag	boolean	✓	✓	is_sensitive
confidentiality_policy_id	bigint	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_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	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_emis_observation_guid	varchar	✓	✓	problem_observation_guid
problem_exa_observation_guid	varchar	✓	✓	problem_observation_uuid
issue_record_id	bigint	✗	✓
emis_issue_guid	varchar	✓	✓	issue_record_guid
exa_issue_guid	varchar	✓	✓	issue_record_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	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
fhir_medication_intent	varchar	✓	✓		Always NULL in V2
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
issue_record_code_id	bigint	✓	✓		Renamed from emis_code_id in V1
observation_code_id	bigint	✗	✓
sensitive_flag	boolean	✓	✓	is_sensitive
confidentiality_policy_id	bigint	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_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	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗

Hermes

The table below presents the flavour specific schema.

Drug Record Problem Link

Column Name	Data Type	Core Mapping
is_deleted	boolean
pseudo_problem_observation_uuid	varchar	problem_observation_uuid
pseudo_drug_record_uuid	varchar	drug_record_uuid
pseudo_patient_uuid	varchar	patient_uuid
pseudo_patient_organisation_uuid	varchar	patient_organisation_uuid
pseudo_drug_record_organisation_uuid	varchar	drug_record_organisation_uuid
availability_datetime	timestamp(6) with time zone
drug_record_code_id	bigint
observation_code_id	bigint
confidentiality_policy_id	bigint
is_sensitive	boolean
is_confidential	boolean
organisation	varchar
transform_datetime	timestamp(6) with time zone

Issue Record Problem Link

Column Name	Data Type	Core Mapping
is_deleted	boolean
pseudo_problem_observation_uuid	varchar	problem_observation_uuid
pseudo_issue_record_uuid	varchar	issue_record_uuid
pseudo_patient_uuid	varchar	patient_uuid
pseudo_patient_organisation_uuid	varchar	patient_organisation_uuid
pseudo_issue_record_organisation_uuid	varchar	issue_record_organisation_uuid
availability_datetime	timestamp(6) with time zone
issue_record_code_id	bigint
observation_code_id	bigint
confidentiality_policy_id	bigint
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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_id	varchar	✓	✓	problem_observation_guid
problem_id_type5	varchar	✓	✓	problem_observation_uuid
drug_record_id	bigint	✗	✓
drug_record_guid	varchar	✓	✓		Renamed from medication_id in V1
drug_record_uuid	varchar	✓	✓		Renamed from exa_medication_guid in V1
patientid	bigint	✓	✓	patient_id
registration_id	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
hestia_event_type	varchar	✓	✗
regular_patient_flag	boolean	✓	✗
regular_current_active_and_inactive	varchar	✓	✗
regular_and_current_active	varchar	✓	✗
non_regular_and_current_active	varchar	✓	✗
sensitive_patient_flag	boolean	✓	✗
confidential_patient_flag	boolean	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_id	varchar	✓	✓	problem_observation_guid
problem_id_type5	varchar	✓	✓	problem_observation_uuid
issue_record_id	bigint	✗	✓
issue_record_guid	varchar	✓	✓		Renamed from medication_id in V1
issue_record_uuid	varchar	✓	✓		Renamed from exa_medication_guid in V1
patientid	bigint	✓	✓	patient_id
registration_id	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
hestia_event_type	varchar	✓	✗
regular_patient_flag	boolean	✓	✗
regular_current_active_and_inactive	varchar	✓	✗
regular_and_current_active	varchar	✓	✗
non_regular_and_current_active	varchar	✓	✗
sensitive_patient_flag	boolean	✓	✗
confidential_patient_flag	boolean	✓	✗

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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	problem_observation_guid
exa_observation_guid	varchar	✓	✓	problem_observation_uuid
drug_record_id	bigint	✗	✓
emis_drug_guid	varchar	✓	✓	drug_record_guid
exa_drug_guid	varchar	✓	✓	drug_record_uuid
pseudo_registration_guid	varchar	✓	✓	patient_guid	hashed
pseudo_patient_uuid	varchar	✗	✓	patient_uuid	hashed
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
entered_date	date	✓	✓	availability_datetime
entered_time	timestamp(6) with time zone	✓	✓	availability_datetime
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
intent	varchar	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	problem_observation_guid
exa_observation_guid	varchar	✓	✓	problem_observation_uuid
issue_record_id	bigint	✗	✓
emis_issue_guid	varchar	✓	✓	issue_record_guid
exa_issue_guid	varchar	✓	✓	issue_record_uuid
pseudo_registration_guid	varchar	✓	✓	patient_guid	hashed
pseudo_patient_uuid	varchar	✗	✓	patient_uuid	hashed
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
entered_date	date	✓	✓	availability_datetime
entered_time	timestamp(6) with time zone	✓	✓	availability_datetime
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
intent	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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	problem_observation_guid
exa_observation_guid	varchar	✓	✓	problem_observation_uuid
drug_record_id	bigint	✗	✓
emis_drug_guid	varchar	✓	✓	drug_record_guid
exa_drug_guid	varchar	✓	✓	drug_record_uuid
pseudo_registration_guid	varchar	✓	✓	patient_guid	hashed
pseudo_patient_uuid	varchar	✗	✓	patient_uuid	hashed
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
entered_date	date	✓	✓	availability_datetime
entered_time	timestamp(6) with time zone	✓	✓	availability_datetime
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1
intent	varchar	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	problem_observation_guid
exa_observation_guid	varchar	✓	✓	problem_observation_uuid
issue_record_id	bigint	✗	✓
emis_issue_guid	varchar	✓	✓	issue_record_guid
exa_issue_guid	varchar	✓	✓	issue_record_uuid
pseudo_registration_guid	varchar	✓	✓	patient_guid	hashed
pseudo_patient_uuid	varchar	✗	✓	patient_uuid	hashed
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
entered_date	date	✓	✓	availability_datetime
entered_time	timestamp(6) with time zone	✓	✓	availability_datetime
confidential_flag	boolean	✓	✓	is_confidential
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1
intent	varchar	✓	✗

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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✓	✓
problem_observation_uuid	varchar	✗	✓
drug_record_id	bigint	✗	✓
drug_record_guid	varchar	✓	✓
drug_record_uuid	varchar	✗	✓
patient_guid	varchar	✓	✓
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
entered_date	date	✓	✓	availability_datetime
entered_time	timestamp(6) with time zone	✓	✓	availability_datetime
is_confidential	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1
intent	varchar	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_observation_guid	varchar	✓	✓
problem_observation_uuid	varchar	✗	✓
issue_record_id	bigint	✗	✓
issue_record_guid	varchar	✓	✓
issue_record_uuid	varchar	✗	✓
patient_guid	varchar	✓	✓
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
entered_date	date	✓	✓	availability_datetime
entered_time	timestamp(6) with time zone	✓	✓	availability_datetime
is_confidential	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1
intent	varchar	✓	✗

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.

Problem Drug Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_emis_observation_guid	varchar	✗	✓	problem_observation_guid
problem_exa_observation_guid	varchar	✗	✓	problem_observation_uuid
drug_record_id	bigint	✗	✓
emis_drug_guid	varchar	✗	✓	drug_record_guid
exa_drug_guid	varchar	✗	✓	drug_record_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	✗	✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✗	✓	availability_datetime
drug_record_code_id	bigint	✗	✓		Renamed from emis_code_id in V1
observation_code_id	bigint	✗	✓
sensitive_flag	boolean	✗	✓	is_sensitive
confidentiality_policy_id	bigint	✗	✓
confidential_flag	boolean	✗	✓	is_confidential
organisation	varchar	✗	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✗	✓
customer_account	varchar	✓	✗

Problem Issue Record

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	✗	✓
problem_observation_id	bigint	✗	✓
problem_emis_observation_guid	varchar	✗	✓	problem_observation_guid
problem_exa_observation_guid	varchar	✗	✓	problem_observation_uuid
issue_record_id	bigint	✗	✓
emis_issue_guid	varchar	✗	✓	issue_record_guid
exa_issue_guid	varchar	✗	✓	issue_record_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	✗	✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✗	✓	availability_datetime
issue_record_code_id	bigint	✓	✓		Renamed from emis_code_id in V1
observation_code_id	bigint	✗	✓
sensitive_flag	boolean	✗	✓	is_sensitive
confidentiality_policy_id	bigint	✗	✓
confidential_flag	boolean	✗	✓	is_confidential
organisation	varchar	✗	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✗	✓
customer_account	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.

Problem Drug Record

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		✓
problem_observation_id	bigint		✓
problem_emis_observation_guid	varchar		✓	problem_observation_guid
problem_exa_observation_guid	varchar		✓	problem_observation_uuid
drug_record_id	bigint		✓
emis_drug_guid	varchar		✓	drug_record_guid
exa_drug_guid	varchar		✓	drug_record_uuid
pseudo_registration_guid_ourea	varchar		✓	patient_guid	hashed
pseudo_patient_uuid	varchar		✓	patient_uuid	hashed
patient_organisation_id	bigint		✓
emis_registration_organisation_guid	varchar		✓	patient_organisation_guid
patient_organisation_uuid	varchar		✓
drug_record_organisation_id	bigint	✗	✓
drug_record_organisation_guid	varchar	✗	✓
drug_record_organisation_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone		✓	availability_datetime
drug_record_code_id	bigint		✓		Renamed from emis_code_id in V1
observation_code_id	bigint		✓
sensitive_flag	boolean		✓	is_sensitive
confidentiality_policy_id	bigint		✓
confidential_flag	boolean		✓	is_confidential
organisation	varchar		✓
transform_datetime	timestamp(6) with time zone		✓
_execution_date	varchar		✓

Problem Issue Record

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		✓
problem_observation_id	bigint		✓
problem_emis_observation_guid	varchar		✓	problem_observation_guid
problem_exa_observation_guid	varchar		✓	problem_observation_uuid
issue_record_id	bigint		✓
emis_issue_guid	varchar		✓	issue_record_guid
exa_issue_guid	varchar		✓	issue_record_uuid
pseudo_registration_guid_ourea	varchar		✓	patient_guid	hashed
pseudo_patient_uuid	varchar		✓	patient_uuid	hashed
patient_organisation_id	bigint		✓
emis_registration_organisation_guid	varchar		✓	patient_organisation_guid
patient_organisation_uuid	varchar		✓
issue_record_organisation_id	bigint	✗	✓
issue_record_organisation_guid	varchar	✗	✓
issue_record_organisation_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone		✓	availability_datetime
issue_record_code_id	bigint		✓		Renamed from emis_code_id in V1
observation_code_id	bigint		✓
sensitive_flag	boolean		✓	is_sensitive
confidentiality_policy_id	bigint		✓
confidential_flag	boolean		✓	is_confidential
organisation	varchar		✓
transform_datetime	timestamp(6) with time zone		✓
_execution_date	varchar		✓