Schema
| Column Name | Data Type | Description | Example |
|---|---|---|---|
| patient_id | bigint | Unique identifier for the patient | 123456 |
| organisation | varchar | Identifier for the data source organization | ’EMIS’ |
| load_datetime | timestamp(6) with time zone | Timestamp when the record was loaded | ’2023-05-12 14:30:15+00’ |
| extract_datetime | timestamp(6) with time zone | Timestamp when the data was extracted from source | ’2023-05-12 14:00:00+00’ |
| is_deleted | boolean | Flag indicating if the record is deleted | false |
| patient_guid | varchar | Globally unique identifier for the patient | ’a1b2c3d4-e5f6-…‘ |
| registration_organisation_id | bigint | ID of the organization where patient is registered | 789012 |
| registration_organisation_guid | varchar | GUID of the organization where patient is registered | ’x1y2z3a4-b5c6-…‘ |
| is_regular | boolean | Current status: patient is a regular patient | true |
| was_regular | boolean | Historical status: patient was a regular patient | true |
| is_dummy | boolean | Current status: patient is a dummy/test record | false |
| was_dummy | boolean | Historical status: patient was a dummy/test record | false |
| is_registered | boolean | Current status: patient is registered | true |
| was_registered | boolean | Historical status: patient was registered | true |
| is_active | boolean | Current status: patient is active | true |
| was_active | boolean | Historical status: patient was active | true |
| has_left | boolean | Current status: patient has left the practice | false |
| had_left | boolean | Historical status: patient had left the practice | false |
| has_died | boolean | Current status: patient has died | false |
| had_died | boolean | Historical status: patient had died | false |
| is_confidential | boolean | Current status: patient record is confidential | false |
| was_confidential | boolean | Historical status: patient record was confidential | false |
| is_sensitive | boolean | Current status: patient record is sensitive | false |
| was_sensitive | boolean | Historical status: patient record was sensitive | false |
| is_national_data_opted_in | boolean | Current status: patient opted in for national data sharing | true |
| was_national_data_opted_in | boolean | Historical status: patient was opted in for national data sharing | true |
| is_consent_9nu0 | boolean | Current status: patient has consent type 9nu0 | true |
| was_consent_9nu0 | boolean | Historical status: patient had consent type 9nu0 | true |
| is_consent_93c1 | boolean | Current status: patient has consent type 93c1 | false |
| was_consent_93c1 | boolean | Historical status: patient had consent type 93c1 | false |
| is_consent_9nu4 | boolean | Current status: patient has consent type 9nu4 | true |
| was_consent_9nu4 | boolean | Historical status: patient had consent type 9nu4 | true |
| is_consent_9nd1 | boolean | Current status: patient has consent type 9nd1 | true |
| was_consent_9nd1 | boolean | Historical status: patient had consent type 9nd1 | true |
| is_consent_9nd1_and_9nu0 | boolean | Current status: patient has both 9nd1 and 9nu0 consents | true |
| was_consent_9nd1_and_9nu0 | boolean | Historical status: patient had both 9nd1 and 9nu0 consents | true |
| is_consent_9nd1_and_9nu0_and_9nu4 | boolean | Current status: patient has 9nd1, 9nu0, and 9nu4 consents | true |
| was_consent_9nd1_and_9nu0_and_9nu4 | boolean | Historical status: patient had 9nd1, 9nu0, and 9nu4 consents | true |
| transform_datetime | timestamp(6) with time zone | Timestamp when the record was last transformed | ’2023-05-12 14:30:15+00’ |
| _execution_date | varchar | Formatted transform datetime for versioning | ’20230512143015’ |
Vanilla
Section titled “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 |
|---|---|---|---|---|
| is_deleted | boolean | ✗ | ✓ | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| patientid | bigint | ✓ | ✓ | |
| registration_id | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| emis_registration_organisation_guid | varchar | ✓ | ✓ | |
| is_regular | boolean | ✓ | ✓ | |
| is_dummy | boolean | ✓ | ✓ | |
| is_not_registered | boolean | ✓ | ✓ | |
| is_active | boolean | ✓ | ✓ | |
| has_left | boolean | ✓ | ✓ | |
| has_died | boolean | ✓ | ✓ | |
| is_confidential | boolean | ✓ | ✓ | |
| is_sensitive | boolean | ✓ | ✓ | |
| is_ndop | boolean | ✓ | ✓ | |
| has_9nu0 | boolean | ✓ | ✓ | |
| has_93c1 | boolean | ✓ | ✓ | |
| has_9nu4 | boolean | ✓ | ✓ | |
| has_9nd1 | boolean | ✓ | ✓ | |
| has_9nd1_or_9nu0 | boolean | ✓ | ✓ | |
| has_9nd1_9nu0_or_9nu4 | boolean | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ | |
| _record_version | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| _update_date | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| _ingest_time | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| _update_hour | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
Apollo
Section titled “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 |
|---|---|---|---|---|
| emis_patient_id | bigint | ✓ | ✓ | |
| registration_guid | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| emis_registration_organisation_guid | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
Artemis
Section titled “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 |
|---|---|---|---|---|
| emis_patient_id | bigint | ✓ | ✓ | |
| registration_guid | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
Hestia
Section titled “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 |
|---|---|---|---|---|
| emis_patient_id | bigint | ✓ | ✓ | |
| registration_guid | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
Olympus
Section titled “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 |
|---|---|---|---|---|
| patient_id | bigint | ✗ | ✓ | |
| pseudo_registration_guid | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| emis_registration_organisation_guid | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
Prometheus
Section titled “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 |
|---|---|---|---|---|
| patient_id | bigint | ✗ | ✓ | |
| pseudo_registration_guid | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| emis_registration_organisation_guid | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
Pseudo_Anon
Section titled “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 |
|---|---|---|---|---|
| emis_patient_id | bigint | ✓ | ✓ | |
| patient_guid | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| organisation_guid | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
Themis
Section titled “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 |
|---|---|---|---|---|
| _record_version | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| _update_date | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| _ingest_time | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| _update_hour | varchar | ✓ | ✗ | Removed as it is deprecated in iPCV v2 |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| patient_id | bigint | patientid | ✓ | Exists in v1 with a different name |
| registration_id | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| emis_registration_organisation_guid | varchar | ✓ | ✓ | |
| is_regular | boolean | ✓ | ✓ | |
| is_dummy | boolean | ✓ | ✓ | |
| is_not_registered | boolean | ✓ | ✓ | |
| is_active | boolean | ✓ | ✓ | |
| has_left | boolean | ✓ | ✓ | |
| has_died | boolean | ✓ | ✓ | |
| is_confidential | boolean | ✓ | ✓ | |
| is_sensitive | boolean | ✓ | ✓ | |
| is_ndop | boolean | ✓ | ✓ | |
| has_9nu0 | boolean | ✓ | ✓ | |
| has_93c1 | boolean | ✓ | ✓ | |
| has_9nu4 | boolean | ✓ | ✓ | |
| has_9nd1 | boolean | ✓ | ✓ | |
| has_9nd1_or_9nu0 | boolean | ✓ | ✓ | |
| has_9nd1_9nu0_or_9nu4 | boolean | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |
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 |
|---|---|---|---|---|
| registration_guid | varchar | ✓ | ✗ | Use registration_organisation_id to get the necessary details from organisation model |
| data_filter | integer | ✓ | ✗ | Removed as it is deprecated in v2 |
| is_deleted | boolean | ✗ | ✓ | |
| patient_id | bigint | emis_patient_id | ✓ | Exists in v1 with a different name |
| pseudo_registration_guid_ourea | varchar | ✓ | ✓ | |
| action_to_take | varchar | ✓ | ✓ | |
| registration_organisation_id | bigint | ✗ | ✓ | |
| emis_registration_organisation_guid | varchar | ✓ | ✓ | |
| organisation | varchar | ✓ | ✓ | |
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | |
| _execution_date | varchar | ✓ | ✓ |