Schema

Core

The core schema represents the Family History schema derived from EMIS source systems.

Column Name	Data Type	Description	Example
load_datetime	timestamp(6) with time zone	The datetime that the record was ingested from source system	‘2023-09-01 10: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 observation record within an organisation.	987654
observation_guid	varchar	The GUID for the observation 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’
member_key	varchar	Stable key for the individual family-member entry within the same observation, so multiple related family-history entries can be distinguished reliably.	‘f8d7178eb78939e5bdca1b64a9beb9bc’
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice.	‘CDB-12345’
is_deleted	boolean	Indicates if this record should be considered soft deleted.	FALSE
family_member_is_deleted	boolean	Indicates whether the specific family member row has been soft deleted without losing the parent observation context.	FALSE
availability_datetime	timestamp(6) with time zone	The date and time the observation was recorded in the source system	‘2023-09-01 10:30:00.000000 UTC’
effective_datetime	timestamp(6) with time zone	The date and time when the observation came into effect	‘2023-09-01 10:00:00.000000 UTC’
effective_datetime_precision	varchar	Precision of the effective datetime; set to ‘YMDT’.	‘YMDT’
emis_code_category_id	bigint	EMIS internal identifier for code categories	8
code_id	bigint	The unique EMIS code identifier for the code associated with the observation	249995012
observation_code_category_id	bigint	The unique internal identifier for the category of the observation	8
original_term	varchar	The EMIS term used to record the observation. Will be set to NULL for uncoded observations.	‘FH: Diabetes mellitus’
observation_type_id	bigint	The unique internal identifier for the observation type	5
observation_type_description	varchar	The description of the observation type (e.g. OBS- observation, TR, INV, VAL, FH)	‘FH’
patient_id	bigint	The unique internal identifier for the patient record within the organisation for whom the observation 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.	‘d4e5f6a7-b8c9-7d0e-2f3a-4b5c6d7e8f9a’
patient_organisation_id	bigint	The unique internal identifier for the organisation where the patient is registered	11223
patient_organisation_guid	varchar	The GUID of the organisation where the patient is registered	‘e5f6a7b8-c9d0-8e1f-3a4b-5c6d7e8f9a0b’
patient_organisation_uuid	varchar	The UUID derived from patient_organisation_id and organisation, providing a stable unique identifier.	‘f6a7b8c9-d0e1-9f2a-4b5c-6d7e8f9a0b1c’
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	‘a7b8c9d0-e1f2-0a3b-5c6d-7e8f9a0b1c2d’
observation_organisation_uuid	varchar	The UUID derived from observation_organisation_id and organisation, providing a stable unique identifier.	‘b8c9d0e1-f2a3-1b4c-6d7e-8f9a0b1c2d3e’
family_member_description	varchar	Human-readable description of the related family member	‘Mother’
family_member_code_id	bigint	Internal identifier for the coded family-member concept	120778012
member_count	bigint	The number of family-member entries represented on the source observation	1
is_family_history	boolean	Indicates whether the record is currently classified as family history	TRUE
is_confidential	boolean	Indicates whether the observation record is confidential. This is based on confidentiality set against the patient/consultation/observation/parent observation	FALSE
is_sensitive	boolean	Indicates whether the observation 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
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	✗	✓
family_member_is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_observation_id	bigint	✓	✓	observation_id
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
member_key	varchar	✗	✓
observation_type_id	bigint	✗	✓
observation_type	varchar	✓	✓	observation_type_description
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_category_id	bigint	✗	✓
observation_code_category_id	bigint	✗	✓
observation_emis_code_id	bigint	✓	✓	code_id
observation_emis_original_term	varchar	✓	✓	original_term
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.
emis_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
exa_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
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
end_date	timestamp(6) with time zone	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
fhir_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
abnormal	boolean	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
cnt	bigint	✓	✓	member_count
qualifiers	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
familymember_description	varchar	✓	✓	family_member_description
familymember_emis_code_id	bigint	✓	✓	family_member_code_id
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
hestia_event_type	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
_record_version	bigint	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
familymember_other_code	varchar	✓	✗
familymember_other_code_system	varchar	✓	✗
familymember_other_display	varchar	✓	✗
familymember_readv2_code	varchar	✓	✗
familymember_snomed_concept_id	bigint	✓	✗
familymember_snomed_description_id	bigint	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
observation_other_code	varchar	✓	✗
observation_other_code_system	varchar	✓	✗
observation_other_display	varchar	✓	✗
observation_readv2_code	varchar	✓	✗
observation_snomed_concept_id	bigint	✓	✗
observation_snomed_description_id	bigint	✓	✗
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	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

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.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
family_member_is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
member_key	varchar	✗	✓
emis_observation_id	bigint	✓	✓	observation_id
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
observation_type_id	bigint	✗	✓
observation_type	varchar	✓	✓	observation_type_description
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_category_id	bigint	✗	✓
observation_code_category_id	bigint	✗	✓
observation_emis_code_id	bigint	✓	✓	code_id
observation_emis_original_term	varchar	✓	✓	original_term
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.
emis_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
exa_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
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
end_date	timestamp(6) with time zone	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity_description	varchar	✓	✗
fhir_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
abnormal	boolean	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
cnt	bigint	✓	✓	member_count
qualifiers	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
familymember_description	varchar	✓	✓	family_member_description
familymember_emis_code_id	bigint	✓	✓	family_member_code_id
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
hestia_event_type	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
_record_version	bigint	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
familymember_other_code	varchar	✓	✗
familymember_other_code_system	varchar	✓	✗
familymember_other_display	varchar	✓	✗
familymember_readv2_code	varchar	✓	✗
familymember_snomed_concept_id	bigint	✓	✗
familymember_snomed_description_id	bigint	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
observation_other_code	varchar	✓	✗
observation_other_code_system	varchar	✓	✗
observation_other_display	varchar	✓	✗
observation_readv2_code	varchar	✓	✗
observation_snomed_concept_id	bigint	✓	✗
observation_snomed_description_id	bigint	✓	✗
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	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

Not used in this schema

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	✗	✓
family_member_is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation	varchar	✓	✓
emis_observation_id	bigint	✓	✓	observation_id
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
member_key	varchar	✗	✓
registration_guid	varchar	✓	✓	patient_guid
emis_patient_id	bigint	✓	✓	patient_id
patient_uuid	varchar	✗	✓
emis_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
exa_encounter_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.
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
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.
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
emis_code_category_id	bigint	✗	✓
observation_code_category_id	bigint	✗	✓
observation_emis_original_term	varchar	✓	✓	original_term
observation_emis_code_id	bigint	✓	✓	code_id
familymember_description	varchar	✓	✓	family_member_description
familymember_emis_code_id	bigint	✓	✓	family_member_code_id
observation_type_id	bigint	✗	✓
observation_type	varchar	✓	✓	observation_type_description
hestia_event_type	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
abnormal	boolean	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
fhir_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
qualifiers	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
sensitive_flag	boolean	✓	✓	is_sensitive
confidential_flag	boolean	✓	✓	is_confidential
cnt	bigint	✓	✓	member_count
observation_snomed_concept_id	bigint	✓	✗
observation_snomed_description_id	bigint	✓	✗
observation_readv2_code	varchar	✓	✗
observation_other_code_system	varchar	✓	✗
observation_other_code	varchar	✓	✗
observation_other_display	varchar	✓	✗
familymember_snomed_concept_id	bigint	✓	✗
familymember_snomed_description_id	bigint	✓	✗
familymember_readv2_code	varchar	✓	✗
familymember_other_code_system	varchar	✓	✗
familymember_other_code	varchar	✓	✗
familymember_other_display	varchar	✓	✗
regular_patient_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
confidential_patient_flag	boolean	✓	✗
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

Not used in this schema

Prometheus

Not used in this schema

Pseudo_Anon

Not used in this schema

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	✗	✓
family_member_is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
member_key	varchar	✗	✓
emis_observation_id	bigint	✓	✓	observation_id
emis_observation_guid	varchar	✓	✓	observation_guid
exa_observation_guid	varchar	✓	✓	observation_uuid
observation_type_id	bigint	✗	✓
observation_type	varchar	✓	✓	observation_type_description
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_category_id	bigint	✗	✓
observation_code_category_id	bigint	✗	✓
observation_emis_code_id	bigint	✓	✓	code_id
observation_emis_original_term	varchar	✓	✓	original_term
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.
emis_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
exa_encounter_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
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
end_date	timestamp(6) with time zone	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_episodicity_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
fhir_episodicity	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
abnormal	boolean	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
cnt	bigint	✓	✓	member_count
qualifiers	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
familymember_description	varchar	✓	✓	family_member_description
familymember_emis_code_id	bigint	✓	✓	family_member_code_id
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
hestia_event_type	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
_record_version	bigint	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
familymember_other_code	varchar	✓	✗
familymember_other_code_system	varchar	✓	✗
familymember_other_display	varchar	✓	✗
familymember_readv2_code	varchar	✓	✗
familymember_snomed_concept_id	bigint	✓	✗
familymember_snomed_description_id	bigint	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
observation_other_code	varchar	✓	✗
observation_other_code_system	varchar	✓	✗
observation_other_display	varchar	✓	✗
observation_readv2_code	varchar	✓	✗
observation_snomed_concept_id	bigint	✓	✗
observation_snomed_description_id	bigint	✓	✗
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	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema