Schema
The core schema represents the Appointment Session User schema derived from EMIS source system.
| Column Name | Data Type | Description | Example |
|---|---|---|---|
| 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’ |
| is_deleted | boolean | Indicates whether the appointment session user record has been soft deleted. | TRUE |
| session_id | bigint | The unique internal identifier for the appointment session record within an organisation. | 123456 |
| session_guid | varchar | The GUID for the appointment session record within an organisation. | ‘12a3b456-7c89-0d1e-234f-5gh6i789jk01’ |
| session_uuid | varchar | The UUID derived from session_id and organisation, providing a stable unique identifier. | ‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’ |
| organisation_id | bigint | The unique internal identifier for the organisation where the session user record is recorded. | 9876 |
| organisation_guid | varchar | The GUID for the organisation where the session user record is recorded. | ‘4a3b156c-7d89-0e1f-234a-5bc6d789ef01’ |
| organisation_uuid | varchar | The UUID derived from organisation_id and organisation, providing a stable unique identifier. | ‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’ |
| location_id | bigint | The unique internal identifier for the location associated with the session user record. | 12345 |
| location_guid | varchar | The GUID for the location associated with the session user record. | ‘b2c3d4e5-f6a7-5b6c-0d1e-2f3a4b5c6d7e’ |
| location_uuid | varchar | The UUID derived from location_id and organisation, providing a stable unique identifier. | ‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’ |
| user_in_role_id | bigint | The unique internal identifier for the user in role associated with the session. | 456 |
| user_in_role_guid | varchar | The GUID for the user in role associated with the session. | ‘9a8b7c6d-5e4f-3g2h-1i0j-kl9m8n7o6p5q’ |
| user_in_role_uuid | varchar | The UUID derived from user_in_role_id and organisation, providing a stable unique identifier. | ‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’ |
| organisation | varchar | An identifier for the source organisation (GP practice) associated with the session user record. | ‘CDB-12345’ |
| transform_datetime | timestamp(6) with time zone | The datetime when the record was last processed and updated in the data model. | ‘2023-05-12 14:30:15.000000 UTC’ |
| _execution_date | varchar | The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility. | ‘20230512143015’ |
Vanilla
Section titled “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 | ✓ | ✓ | Renamed from session_deleted_flag in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| emis_session_id | bigint | ✓ | ✓ | session_id | |
| emis_session_guid | varchar | ✓ | ✓ | session_guid | |
| session_uuid | varchar | ✗ | ✓ | ||
| session_emis_organisation_id | bigint | ✓ | ✓ | organisation_id | |
| session_emis_organisation_guid | varchar | ✓ | ✓ | organisation_guid | |
| organisation_uuid | varchar | ✗ | ✓ | ||
| emis_session_userinrole_id | bigint | ✓ | ✓ | user_in_role_id | |
| emis_session_userinrole_guid | varchar | ✓ | ✓ | user_in_role_guid | |
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| location_id | bigint | ✗ | ✓ | ||
| location_guid | varchar | ✗ | ✓ | ||
| location_uuid | varchar | ✗ | ✓ | ||
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Apollo
Section titled “Apollo”Not used in this schema
Artemis
Section titled “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 | ✓ | ✓ | Renamed from session_deleted_flag in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| emis_session_id | bigint | ✓ | ✓ | session_id | |
| emis_session_guid | varchar | ✓ | ✓ | session_guid | |
| session_uuid | varchar | ✗ | ✓ | ||
| emis_session_userinrole_id | bigint | ✓ | ✓ | user_in_role_id | |
| emis_session_userinrole_guid | varchar | ✓ | ✓ | user_in_role_guid | |
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| session_emis_organisation_id | bigint | ✓ | ✓ | organisation_id | |
| session_emis_organisation_guid | varchar | ✓ | ✓ | organisation_guid | |
| organisation_uuid | varchar | ✗ | ✓ | ||
| location_id | bigint | ✗ | ✓ | ||
| location_guid | varchar | ✗ | ✓ | ||
| location_uuid | varchar | ✗ | ✓ | ||
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Hermes
Section titled “Hermes”The table below presents the flavour specific schema and maps columns between core and flavour (V2).
| Column Name | Data Type | Core Mapping | Comments |
|---|---|---|---|
| is_deleted | boolean | ||
| pseudo_session_uuid | varchar | session_uuid | hashed |
| pseudo_user_in_role_uuid | varchar | user_in_role_uuid | hashed |
| pseudo_organisation_uuid | varchar | organisation_uuid | hashed |
| pseudo_location_uuid | varchar | location_uuid | hashed |
| organisation | varchar | ||
| transform_datetime | timestamp(6) with time zone |
Hestia
Section titled “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 | ✓ | ✓ | Renamed from session_user_deleted_flag in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| emis_session_id | bigint | ✓ | ✓ | session_id | |
| emis_session_guid | varchar | ✓ | ✓ | session_guid | |
| session_uuid | varchar | ✗ | ✓ | ||
| emis_session_userinrole_id | bigint | ✓ | ✓ | user_in_role_id | |
| emis_session_userinrole_guid | varchar | ✓ | ✓ | user_in_role_guid | |
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| session_emis_organisation_id | bigint | ✓ | ✓ | organisation_id | |
| session_emis_organisation_guid | varchar | ✓ | ✓ | organisation_guid | |
| organisation_uuid | varchar | ✗ | ✓ | ||
| location_id | bigint | ✗ | ✓ | ||
| location_guid | varchar | ✗ | ✓ | ||
| location_uuid | varchar | ✗ | ✓ | ||
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Olympus
Section titled “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 | ✓ | ✓ | Renamed from deleted in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| session_id | bigint | ✓ | ✓ | ||
| emis_session_guid | varchar | ✓ | ✓ | session_guid | |
| session_uuid | varchar | ✗ | ✓ | ||
| user_in_role_id | bigint | ✓ | ✓ | ||
| emis_session_userinrole_guid | varchar | ✓ | ✓ | user_in_role_guid | |
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| processing_id | bigint | ✓ | ✓ | Always NULL as it is deprecated in v2. Refer changes in v2 for more details. | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Prometheus
Section titled “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 | ✓ | ✓ | Renamed from deleted in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| session_id | bigint | ✓ | ✓ | ||
| emis_session_guid | varchar | ✓ | ✓ | session_guid | |
| session_uuid | varchar | ✗ | ✓ | ||
| user_in_role_id | bigint | ✓ | ✓ | ||
| emis_session_userinrole_guid | varchar | ✓ | ✓ | user_in_role_guid | |
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| processing_id | bigint | ✓ | ✓ | Always NULL as it is deprecated in v2. Refer changes in v2 for more details. | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ | Renamed from execution_date in V1 |
Pseudo_Anon
Section titled “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 | ✓ | ✓ | Renamed from deleted in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| session_id | bigint | ✓ | ✓ | ||
| session_guid | varchar | ✓ | ✓ | ||
| session_uuid | varchar | ✗ | ✓ | ||
| user_in_role_id | bigint | ✓ | ✓ | ||
| user_in_role_guid | varchar | ✓ | ✓ | ||
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| processing_id | bigint | ✓ | ✓ | Always NULL as it is deprecated in v2. Refer changes in v2 for more details. | |
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | execution_date | ✓ |
Themis
Section titled “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 | ✓ | ✓ | Renamed from session_deleted_flag in V1 | |
| load_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| emis_session_id | bigint | ✓ | ✓ | session_id | |
| emis_session_guid | varchar | ✓ | ✓ | session_guid | |
| session_uuid | varchar | ✗ | ✓ | ||
| session_emis_organisation_id | bigint | ✓ | ✓ | organisation_id | |
| session_emis_organisation_guid | varchar | ✓ | ✓ | organisation_guid | |
| organisation_uuid | varchar | ✗ | ✓ | ||
| emis_session_userinrole_id | bigint | ✓ | ✓ | user_in_role_id | |
| emis_session_userinrole_guid | varchar | ✓ | ✓ | user_in_role_guid | |
| user_in_role_uuid | varchar | ✗ | ✓ | ||
| location_id | bigint | ✗ | ✓ | ||
| location_guid | varchar | ✗ | ✓ | ||
| location_uuid | varchar | ✗ | ✓ | ||
| organisation | varchar | ✓ | ✓ | ||
| transform_datetime | timestamp(6) with time zone | ✗ | ✓ | ||
| _execution_date | varchar | ✓ | ✓ |
Not used in this schema