Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	varchar	The datetime that the record was ingested, format YYYYMMDDHHMMSS	’20230512143015’
is_deleted	boolean	Indicates if this record should be considered soft deleted	FALSE
patient_id	bigint	Internal identifier used to identify the patient	12345
patient_guid	varchar	Unique identifier for the patient within the organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
person_guid	varchar	Globally unique identifier for the patient	’b2c3d4e5-f6a7-5b6c-0d1e-2f3a4b5c6d7e’
patient_number	bigint	Patient identification number	98765
nhs_number	varchar	National Health Service number	’1234567890’
chi_number	varchar	Community Health Index number	’CHI123456’
hc_number	varchar	Health and Care number	’HC789012’
hospital_number	varchar	Hospital identification number	’HOSP345678’
ssd_number	varchar	Social Services Department number	’SSD901234’
gha_number	varchar	Guernsey Health Authority number	’GHA567890’
registration_organisation_id	bigint	ID of the organisation where the patient is registered	54321
registration_organisation_guid	varchar	GUID of the organisation where the patient is registered	’c3d4e5f6-a7b8-6c7d-1e2f-3a4b5c6d7e8f’
patient_name	varchar	Full name of the patient	’John Smith’
surname	varchar	Patient surname	’Smith’
given_name	varchar	Patient given name	’John’
middle_names	varchar	Patient middle names	’Michael David’
sex	varchar	Patient sex	’Male’
sex_id	varchar	Sex identifier code	’M’
sex_description	varchar	Description of patient sex	’Male’
date_of_birth	date	Patient date of birth	’1980-05-15’
week_and_year_of_birth	varchar	Week and year of birth	’2019W20’
datetime_of_death	timestamp(6) with time zone	Date and time of death	’2023-05-10 14:25:36.000000 UTC’
week_and_year_of_death	varchar	Week and year of death	’2023W19’
age	bigint	Patient age in years	43
usual_gp_user_in_role_id	bigint	Unique identifier of the GP assigned to the patient when registering at a practice	11223
external_usual_gp_id	bigint	Unique identifier of the GP registered outside the local area of a patient	33445
registration_start_datetime	timestamp(6) with time zone	Date and time the patient registered with the practice	’2020-01-15 09:00:00.000000 UTC’
registration_end_datetime	timestamp(6) with time zone	Date and time the patient registration ended with the practice	’2023-12-31 23:59:59.000000 UTC’
residential_institute_code	varchar	Provides both current and legacy residential institute codes	’RI001’
has_carer	boolean	Indicates whether the patient has a carer	TRUE
email	varchar	Patient email address	’john.smith@email.com’
home_phone_number	varchar	Patient home phone number	’01234567890’
mobile_number	varchar	Patient mobile number	’07123456789’
address_guid	varchar	GUID of the patient address	’d4e5f6a7-b8c9-7d8e-2f3a-4b5c6d7e8f9a’
full_address	varchar	Complete address of the patient	’123 Main Street, London, SW1A 1AA’
postcode	varchar	Postcode with space	’SW1A 1AA’
postcode_no_space	varchar	Postcode without space	’SW1A1AA’
postcode_sector	varchar	Postcode sector	’SW1A 1’
house_name_flat_number	varchar	House name or flat number	’Flat 5’
number_and_street	varchar	Street number and name	’123 Main Street’
village	varchar	Village name	’Little Village’
town	varchar	Town name	’London’
county	varchar	County name	’Greater London’
hashed_address	varchar	Hashed version of the patient’s complete address	’e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0’
lower_level_super_output_area	varchar	LSOAs are made up of groups of Output Areas (OAs), usually four or five	’E01000001’
middle_level_super_output_area	varchar	MSOAs are made up of groups of Lower Layer Super Output Areas (LSOAs), usually four or five	’E02000001’
index_of_multiple_deprivation_decile	bigint	Indicates the official measure of relative deprivation in the UK	5
recorded_datetime	timestamp(6) with time zone	The date the patient was recorded in EMIS Web and will be updated when the patient status changes	’2023-05-12 10:15:30.000000 UTC’
patient_status_id	bigint	Unique identifier of the patient status during the registration cycle	1
patient_status_description	varchar	Description of the patient status	’Active’
patient_type_id	bigint	Unique identifier of the patient type	2
patient_type_description	varchar	Description of patient type	’Regular’
confidentiality_policy_id	bigint	Unique identifier denoting the level of confidentiality applied to a policy	3
is_interpreter_required	boolean	Indicates if an interpreter is required	FALSE
is_merged	boolean	Indicates if the patient record is merged	FALSE
is_regular	boolean	Indicates if the patient is regular	TRUE
is_dummy	boolean	Indicates if this is a dummy record	FALSE
is_registered	boolean	Indicates if the patient is registered	TRUE
is_active	boolean	Indicates if the patient record is active	TRUE
has_died	boolean	Indicates if the patient has died	FALSE
has_left	boolean	Indicates if the patient has left the practice	FALSE
is_confidential	boolean	Indicates if the patient record is confidential	FALSE
is_sensitive	boolean	Indicates if the patient record is sensitive	FALSE
is_national_data_opted_in	boolean	Indicates if the patient opted in to national data sharing	TRUE
is_consent_9nu0	boolean	Indicates whether a patient has consented for opt-out code 9nu0	TRUE
is_consent_93c1	boolean	Indicates whether a patient has consented for opt-out code 93c1	TRUE
is_consent_9nu4	boolean	Indicates whether a patient has consented for opt-out code 9nu4	TRUE
is_consent_9nd1	boolean	Indicates whether a patient has consented for opt-out code 9nd1	TRUE
is_consent_9nd1_and_9nu0	boolean	Indicates whether a patient has consented for opt-out codes 9nd1 and 9nu0	TRUE
is_consent_9nd1_and_9nu0_and_9nu4	boolean	Indicates whether a patient has consented for opt-out codes 9nd1, 9nu0 and 9nu4	TRUE
allow_online_appointments	boolean	Indicates whether to allow online appointment booking	TRUE
allow_online_repeat_prescriptions	boolean	Indicates whether to allow online repeat prescriptions	TRUE
allow_online_record_viewer	boolean	Indicates whether to allow online record viewing	TRUE
is_consent_sms	boolean	Consent for SMS communications	TRUE
is_consent_email	boolean	Consent for email communications	TRUE
preferred_language_code_id	bigint	ID of preferred language code	1
sexual_orientation_code_id	bigint	ID of sexual orientation code	2
ethnicity_code_id	bigint	ID of ethnicity code	3
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	’CDB-12345’
extract_datetime	timestamp(6) with time zone	The datetime the record was last extracted from the source database with a relevant change	’2023-05-10 09:30:45.000000 UTC’
load_datetime	timestamp(6) with time zone	Formatted ingest time for filtering	’2023-05-12 14:30:15.000000 UTC’
transform_datetime	timestamp(6) with time zone	The datetime the data was made available in the model with a relevant change	’2023-05-12 14:30:15.000000 UTC’
_execution_date	varchar	Formatted transform datetime for versioning	’20230512143015’

Vanilla

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
person_guid	varchar	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
emis_no	bigint	✓	✓
nhs_no	varchar	✓	✓
gha_no	varchar	✓	✓
chi_no	varchar	✗	✓
hc_no	varchar	✗	✓
hospital_no	varchar	✗	✓
ssd_no	varchar	✗	✓
registration_ods_code	varchar	✓	✓
patient_displayname	varchar	✓	✓
patient_surname	varchar	✓	✓
patient_givenname	varchar	✓	✓
patient_middlenames	varchar	✓	✓
sex	varchar	✗	✓
nhs_gender	varchar	✓	✓
emis_sex	varchar	✓	✓
date_of_birth	date	✓	✓
week_of_birth	varchar	✓	✓
date_of_death	timestamp(6) with time zone	✓	✓
week_of_death	varchar	✓	✓
age	bigint	✓	✓
usualgp_userinrole_id	bigint	✓	✓
external_usualgp_id	bigint	✗	✓
registration_date	timestamp(6) with time zone	✓	✓
registration_end_date	timestamp(6) with time zone	✓	✓
has_carer	boolean	✗	✓
email	varchar	✓	✓
home_phone_number	varchar	✓	✓
mobile_number	varchar	✓	✓
address_guid	varchar	✓	✓
full_address	varchar	✓	✓
postcode	varchar	✓	✓
postcode_sector	varchar	✓	✓
postcode_no_space	varchar	✗	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
address_line3	varchar	✓	✓
address_line4	varchar	✓	✓
address_line5	varchar	✓	✓
exa_household_key	varchar	✓	✓
lsoa	varchar	✓	✓
msoa	varchar	✓	✓
imd_decile	bigint	✓	✓
recorded_date	timestamp(6) with time zone	✓	✓
emis_registration_status_id	bigint	✓	✓
emis_registration_status_description	varchar	✓	✓
emis_registration_type_id	bigint	✓	✓
emis_registration_type_description	varchar	✓	✓
fhir_registration_type	varchar	✓	✓
fhir_patient_active	boolean	✓	✓
confidentiality_policy_id	bigint	✓	✓
interpreter_required_flag	boolean	✓	✓
is_merged	boolean	✗	✓
regular_patient_flag	boolean	✓	✓
dummy_patient_flag	boolean	✓	✓
is_registered	boolean	✗	✓
is_active	boolean	✗	✓
has_left	boolean	✗	✓
deceased_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✓
sensitive_patient_flag	boolean	✓	✓
online_appointment_flag	boolean	✓	✓
online_prescription_flag	boolean	✓	✓
online_record_flag	boolean	✓	✓
sms_preferred_flag	boolean	✓	✓
email_preferred	boolean	✓	✓
preferred_language_code_id	bigint	language_emis_code_id	✓	Exists in v1 with a different name
sexual_orientation_emis_code_id	bigint	✓	✓
ethnicity_emis_code_id	bigint	✓	✓
residential_institute_code	varchar	✓	✓
carer_name	varchar	✓	✓
carer_relation	varchar	✓	✓
external_usualgp_name	varchar	✓	✓
external_usualgp_guid	varchar	✓	✓
usualgp_displayname	varchar	✓	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
regular_and_current_active_flag	boolean	✓	✓
non_regular_and_current_active_flag	boolean	✓	✓
regular_current_active_and_inactive_flag	boolean	✓	✓
is_national_data_opted_in	boolean	✗	✓
opt_out_93c1_flag	boolean	✓	✓
opt_out_9nu0_flag	boolean	✓	✓
opt_out_9nd1_flag	boolean	✓	✓
opt_out_9nu4_flag	boolean	✓	✓
opt_out_9nd19nu0_flag	boolean	✓	✓
opt_out_9nd19nu09nu4_flag	boolean	✓	✓
ethnic_group_description	varchar	✓	✗	Refer definitions for reason
ethnic_group_id	bigint	✓	✗	Refer definitions for reason
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
ethnic_group_snomed_description	varchar	✓	✗	Refer definitions for reason
sexual_orientation_nationalcode	varchar	✓	✗	Refer definitions for reason
nhs_no_status	varchar	✓	✗	Refer definitions for reason
language_code	varchar	✓	✗	Refer definitions for reason
language_preferred_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
registration_guid	varchar	✓	✓
nhs_no	varchar	✓	✓
emis_no	bigint	✓	✓
registration_ods_code	varchar	✓	✓
patient_displayname	varchar	✓	✓
date_of_birth	date	✓	✓
nhs_gender	varchar	✓	✓
ethnicity_emis_code_id	bigint	✓	✓
usualgp_displayname	varchar	✓	✓
usual_gp_user_in_role_id	bigint	✗	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Artemis

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
person_guid	varchar	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
emis_no	bigint	✓	✓
nhs_no	varchar	✓	✓
registration_ods_code	varchar	✓	✓
patient_displayname	varchar	✓	✓
patient_surname	varchar	✓	✓
patient_givenname	varchar	✓	✓
patient_middlenames	varchar	✓	✓
nhs_gender	varchar	✓	✓
emis_sex	varchar	✓	✓
date_of_birth	date	✓	✓
week_of_birth	varchar	✓	✓
date_of_death	timestamp(6) with time zone	✓	✓
week_of_death	varchar	✓	✓
age	bigint	✓	✓
usualgp_userinrole_id	bigint	✓	✓
external_usualgp_id	bigint	✗	✓
registration_date	timestamp(6) with time zone	✓	✓
registration_end_date	timestamp(6) with time zone	✓	✓
has_carer	boolean	✗	✓
email	varchar	✓	✓
home_phone_number	varchar	✓	✓
mobile_number	varchar	✓	✓
address_guid	varchar	✓	✓
full_address	varchar	✓	✓
postcode	varchar	✓	✓
postcode_sector	varchar	✓	✓
postcode_no_space	varchar	✗	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
address_line3	varchar	✓	✓
address_line4	varchar	✓	✓
address_line5	varchar	✓	✓
exa_household_key	varchar	✓	✓
lsoa	varchar	✓	✓
msoa	varchar	✓	✓
imd_decile	bigint	✓	✓
recorded_date	timestamp(6) with time zone	✓	✓
emis_registration_status_id	bigint	✓	✓
emis_registration_status_description	varchar	✓	✓
emis_registration_type_id	bigint	✓	✓
emis_registration_type_description	varchar	✓	✓
fhir_registration_type	varchar	✓	✓
fhir_patient_active	boolean	✓	✓
confidentiality_policy_id	bigint	✓	✓
interpreter_required_flag	boolean	✓	✓
regular_patient_flag	boolean	✓	✓
dummy_patient_flag	boolean	✓	✓
is_active	boolean	✗	✓
deceased_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✓
sensitive_patient_flag	boolean	✓	✓
online_appointment_flag	boolean	✓	✓
online_prescription_flag	boolean	✓	✓
online_record_flag	boolean	✓	✓
sms_preferred_flag	boolean	✓	✓
email_preferred	boolean	✓	✓
preferred_language_code_id	bigint	language_emis_code_id	✓	Exists in v1 with a different name
sexual_orientation_emis_code_id	bigint	✓	✓
ethnicity_emis_code_id	bigint	✓	✓
residential_institute_code	varchar	✓	✓
carer_name	varchar	✓	✓
carer_relation	varchar	✓	✓
external_usualgp_name	varchar	✓	✓
external_usualgp_guid	varchar	✓	✓
usualgp_displayname	varchar	✓	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
regular_and_current_active_flag	boolean	✓	✓
non_regular_and_current_active_flag	boolean	✓	✓
regular_current_active_and_inactive_flag	boolean	✓	✓
opt_out_93c1_flag	boolean	✓	✓
opt_out_9nu0_flag	boolean	✓	✓
opt_out_9nd19nu0_flag	boolean	✓	✓
opt_out_9nd19nu09nu4_flag	boolean	✓	✓
ethnic_group_description	varchar	✓	✗	Refer definitions for reason
ethnic_group_id	bigint	✓	✗	Refer definitions for reason
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
ethnic_group_snomed_description	varchar	✓	✗	Refer definitions for reason
sexual_orientation_nationalcode	varchar	✓	✗	Refer definitions for reason
nhs_no_status	varchar	✓	✗	Refer definitions for reason
language_preferred_snomed_concept_id	bigint	✓	✗	Refer definitions for reasons
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hestia

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
nhs_no	varchar	✓	✓
emis_no	bigint	✓	✓
person_guid	varchar	✓	✓
patient_displayname	varchar	✓	✓
patient_givenname	varchar	✓	✓
patient_middlenames	varchar	✓	✓
patient_surname	varchar	✓	✓
date_of_birth	date	✓	✓
week_of_birth	varchar	✓	✓
age	bigint	✓	✓
recorded_date	timestamp(6) with time zone	✓	✓
registration_end_date	timestamp(6) with time zone	✓	✓
registration_date	timestamp(6) with time zone	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
registration_ods_code	varchar	✓	✓
usualgp_userinrole_id	bigint	✓	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
usualgp_displayname	varchar	✓	✓
external_usualgp_id	bigint	✗	✓
external_usualgp_guid	varchar	✓	✓
external_usualgp_name	varchar	✓	✓
deceased_flag	boolean	✓	✓
date_of_death	timestamp(6) with time zone	✓	✓
week_of_death	varchar	✓	✓
address_guid	varchar	✓	✓
full_address	varchar	✓	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
address_line3	varchar	✓	✓
address_line4	varchar	✓	✓
address_line5	varchar	✓	✓
postcode	varchar	✓	✓
postcode_sector	varchar	✓	✓
exa_household_key	varchar	✓	✓
lsoa	varchar	✓	✓
msoa	varchar	✓	✓
imd_decile	bigint	✓	✓
email	varchar	✓	✓
mobile_number	varchar	✓	✓
home_phone_number	varchar	✓	✓
sms_preferred_flag	boolean	✓	✓
emis_registration_type_id	bigint	✓	✓
emis_registration_type_description	varchar	✓	✓
active_registration_flag	boolean	✓	✓
emis_registration_status_id	bigint	✓	✓
emis_registration_status_description	bigint	✓	✓
confidentiality_policy_id	bigint	✓	✓
nhs_gender	varchar	✓	✓
emis_sex	varchar	✓	✓
sexual_orientation_emis_code_id	bigint	✓	✓
ethnicity_emis_code_id	bigint	✓	✓
interpreter_required_flag	boolean	✓	✓
online_appointment_flag	boolean	✓	✓
online_prescription_flag	boolean	✓	✓
online_record_flag	boolean	✓	✓
residential_institute_code	varchar	✓	✓
regular_current_active_and_inactive_flag	boolean	✓	✓
regular_patient_flag	boolean	✓	✓
regular_and_current_active_flag	boolean	✓	✓
non_regular_and_current_active_flag	boolean	✓	✓
sensitive_patient_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✓
fhir_registration_type	varchar	✓	✓
carer_name	varchar	✓	✓
carer_relation	varchar	✓	✓
has_carer	boolean	✗	✓
is_active	boolean	✗	✓
preferred_language_code_id	bigint	language_emis_code_id	✓	Exists in v1 with a different name
sexual_orientation_nationalcode	varchar	✓	✗	Refer definitions for reason
nhs_no_status	varchar	✓	✗	Refer definitions for reason
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
language_preferred_snomed_concept_id	varchar	✓	✗	Refer definitions for reason
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
pseudo_person_guid	varchar	✓	✓
pseudo_registration_guid	varchar	✓	✓
pseudo_emis_no	varchar	✓	✓
pseudo_nhs_no	varchar	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
nhs_number_status	varchar	✓	✗	Refer definitions for reason
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
ethnicity_emis_code_id	bigint	✓	✓
gender	varchar	✓	✓
date_of_birth	varchar	✓	✓
date_of_death	date	✓	✓
registration_date	date	✓	✓
registration_end_date	date	✓	✓
residential_institute_code	boolean	✓	✓
carer_name_flag	varchar	✓	✓
carer_relation_flag	varchar	✓	✓
has_carer	boolean	✗	✓
address_guid	varchar	✓	✓
imd_decile	bigint	✓	✓
emis_registration_type_description	varchar	✓	✓
is_active	boolean	✗	✓
dummy_patient_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✓
sensitive_patient_flag	boolean	✓	✓
email_flag	boolean	✓	✓
usualgp_userinrole_id	bigint	✗	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
external_usualgp_id	bigint	✗	✓
external_usualgp_guid	varchar	✓	✓
mobile_number_flag	boolean	✓	✓
home_phone_number_flag	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
processing_id	integer	✓	✗	Removed as it is deprecated in iPCV v1

Pseudo_Anon

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
patient_guid	varchar	✓	✓
registration_organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
person_guid	varchar	✓	✓
patient_number	bigint	✓	✓
pseudo_nhs_number	varchar	✓	✓
usual_gp_user_in_role_id	bigint	✗	✓
usual_gp_user_in_role	varchar	✓	✓
external_usualgp_id	bigint	✗	✓
external_usual_gp	varchar	✓	✓
sex	varchar	✓	✓
date_of_birth	date	✓	✓
date_of_death	date	✓	✓
date_of_registration	date	✓	✓
date_of_deactivation	date	✓	✓
residential_institute_code	varchar	✓	✓
address_id	varchar	✓	✓
patient_type_description	varchar	✓	✓
dummytype	boolean	✓	✓
is_confidential	boolean	✓	✓
spine_sensitive	boolean	✓	✓
nhs_number_status	varchar	✓	✗	Refer definitions for reason
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
_execution_date	varchar	execution_date	✓	Exists in v1 with a different name

Themis

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
nhs_no	varchar	✓	✓
emis_no	bigint	✓	✓
person_guid	varchar	✓	✓
patient_displayname	varchar	✓	✓
patient_givenname	varchar	✓	✓
patient_middlenames	varchar	✓	✓
patient_surname	varchar	✓	✓
date_of_birth	date	✓	✓
week_of_birth	varchar	✓	✓
age	bigint	✓	✓
recorded_date	timestamp(6) with time zone	✓	✓
registration_end_date	timestamp(6) with time zone	✓	✓
registration_date	timestamp(6) with time zone	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
registration_ods_code	varchar	✓	✓
usualgp_userinrole_id	bigint	✓	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
usualgp_displayname	varchar	✓	✓
external_usualgp_id	bigint	✗	✓
external_usualgp_guid	varchar	✓	✓
external_usualgp_name	varchar	✓	✓
deceased_flag	boolean	✓	✓
date_of_death	timestamp(6) with time zone	✓	✓
week_of_death	varchar	✓	✓
address_guid	varchar	✓	✓
full_address	varchar	✓	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
address_line3	varchar	✓	✓
address_line4	varchar	✓	✓
address_line5	varchar	✓	✓
postcode	varchar	✓	✓
postcode_sector	varchar	✓	✓
exa_household_key	varchar	✓	✓
lsoa	varchar	✓	✓
msoa	varchar	✓	✓
imd_decile	bigint	✓	✓
email	varchar	✓	✓
mobile_number	varchar	✓	✓
home_phone_number	varchar	✓	✓
sms_preferred_flag	boolean	✓	✓
email_preferred	boolean	✓	✓
emis_registration_type_id	bigint	✓	✓
emis_registration_type_description	varchar	✓	✓
emis_registration_status_id	bigint	✓	✓
emis_registration_status_description	bigint	✓	✓
confidentiality_policy_id	bigint	✓	✓
nhs_gender	varchar	✓	✓
emis_sex	varchar	✓	✓
sexual_orientation_emis_code_id	bigint	✓	✓
ethnicity_emis_code_id	bigint	✓	✓
interpreter_required_flag	boolean	✓	✓
online_appointment_flag	boolean	✓	✓
online_prescription_flag	boolean	✓	✓
online_record_flag	boolean	✓	✓
residential_institute_code	varchar	✓	✓
regular_current_active_and_inactive_flag	boolean	✓	✓
regular_patient_flag	boolean	✓	✓
regular_and_current_active_flag	boolean	✓	✓
non_regular_and_current_active_flag	boolean	✓	✓
opt_out_93c1_flag	boolean	✓	✓
opt_out_9nu0_flag	boolean	✓	✓
opt_out_9nd19nu0_flag	boolean	✓	✓
opt_out_9nd19nu09nu4_flag	boolean	✓	✓
sensitive_patient_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✓
fhir_registration_type	varchar	✓	✓
fhir_patient_active	varchar	✓	✓
carer_name	varchar	✓	✓
carer_relation	varchar	✓	✓
has_carer	boolean	✗	✓
is_active	boolean	✗	✓
dummy_patient_flag	boolean	✓	✓
is_registered	boolean	✗	✓
preferred_language_code_id	bigint	language_emis_code_id	✓	Exists in v1 with a different name
sexual_orientation_nationalcode	varchar	✓	✗	Refer definitions for reason
nhs_no_status	varchar	✓	✗	Refer definitions for reason
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
language_preferred_snomed_concept_id	varchar	✓	✗	Refer definitions for reason
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Prometheus

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
pseudo_person_guid	varchar	✓	✓
pseudo_registration_guid	varchar	✓	✓
pseudo_emis_no	varchar	✓	✓
pseudo_nhs_no	varchar	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
nhs_number_status	varchar	✓	✗	Refer definitions for reason
ethnic_category_snomed_concept_id	bigint	✓	✗	Refer definitions for reason
ethnicity_emis_code_id	bigint	✓	✓
gender	varchar	✓	✓
date_of_birth	varchar	✓	✓
date_of_death	date	✓	✓
registration_date	date	✓	✓
registration_end_date	date	✓	✓
residential_institute_code	boolean	✓	✓
carer_name_flag	varchar	✓	✓
carer_relation_flag	varchar	✓	✓
has_carer	boolean	✗	✓
address_guid	varchar	✓	✓
imd_decile	bigint	✓	✓
emis_registration_type_description	varchar	✓	✓
is_active	boolean	✗	✓
dummy_patient_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✓
sensitive_patient_flag	boolean	✓	✓
email_flag	boolean	✓	✓
usualgp_userinrole_id	bigint	✗	✓
emis_usualgp_userinrole_guid	varchar	✓	✓
external_usualgp_id	bigint	✗	✓
external_usualgp_guid	varchar	✓	✓
mobile_number_flag	boolean	✓	✓
home_phone_number_flag	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
processing_id	integer	✓	✗	Removed as it is deprecated in iPCV v1

Zeus

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
pseudo_registration_guid_ourea	varchar	✓	✓
registration_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
date_of_birth_part	varchar	✓	✓
deceased_flag	boolean	✓	✓
dummy_patient_flag	boolean	✓	✓
nhs_gender	varchar	✓	✓
opt_out_9nd19nu0_flag	boolean	✓	✓
opt_out_93c1_flag	boolean	✓	✓
opt_out_9nu0_flag	boolean	✓	✓
opt_out_9nd19nu09nu4_flag	boolean	✓	✓
non_regular_and_current_active_flag	boolean	✓	✓
regular_current_active_and_inactive_flag	boolean	✓	✓
regular_and_current_active_flag	boolean	✓	✓
regular_patient_flag	boolean	✓	✓
registration_ods_code	varchar	✓	✓
is_active	boolean	✗	✓
sensitive_patient_flag	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
data_filter	integer	✓	✗	Removed as it is deprecated in v2