Schema

Core

The core schema represents the Appointment Session 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 record has been soft deleted.	FALSE
organisation_id	bigint	The unique internal identifier for the organisation where the session is recorded.	12345
organisation_guid	varchar	The GUID for the organisation where the session is recorded.	‘550e8400-e29b-41d4-a716-446655440000’
organisation_uuid	varchar	The UUID derived from organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
session_id	bigint	The unique internal identifier for the appointment session within an organisation.	67890
session_guid	varchar	The GUID for the appointment session within an organisation.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
session_uuid	varchar	The UUID derived from session_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
session_description	varchar	The display description used to summarise the purpose of the session.	‘Home Visits’
session_start_date	date	The date on which the appointment session starts.	‘2023-05-15’
session_start_time	varchar	The time at which the appointment session starts.	‘09:00:00’
session_end_date	date	The date on which the appointment session ends.	‘2023-05-15’
session_end_time	varchar	The time at which the appointment session ends.	‘12:30:00’
session_last_updated_datetime	timestamp(6) with time zone	The datetime when the session was last updated in the source system.	‘2023-05-10 14:25:36.000000 UTC’
is_private	boolean	Indicates whether the session is marked as private.	FALSE
session_type_id	bigint	The internal identifier for the session type.	1
session_type_description	varchar	The description of the session type which sets a high level default type for the slots it contains like 1 - Timed Appointments, 2 - Untimed Appointments and 3 - Non-Clinical Session.	‘Timed Appointments’
session_category_id	bigint	The internal identifier for the session category.	4
session_category_display_name	varchar	The display name for the session category.	‘Default Non-List Category’
location_id	bigint	The internal identifier for the location where the session takes place.	12345
location_guid	varchar	The GUID for the location where the session takes place.	‘33eea400-f12c-41d4-b146-446255223344’
location_uuid	varchar	The UUID derived from location_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
total_slots	bigint	The total number of slots configured for the session.	20
bookable_slots	bigint	The number of slots in the session that are available for booking.	18
booked_slots	bigint	The number of slots in the session that have been booked.	15
unfilled_slots	bigint	The number of bookable slots in the session that remain unfilled.	3
deleted_slots	bigint	The number of slots in the session that have been marked as deleted.	1
embargoed_slots	bigint	The number of slots in the session that are embargoed and not yet available for booking.	2
dna_appointments	bigint	The number of appointments in the session recorded as not attended.	1
cancelled_and_rebooked_appointments	bigint	The number of appointments in the session that were cancelled and later rebooked.	2
cancelled_and_not_rebooked_appointments	bigint	The number of appointments in the session that were cancelled and not rebooked.	1
same_day_appointments	bigint	The number of appointments in the session booked on the same day they were due to occur.	4
in_person_appointments	bigint	The number of appointments in the session delivered in person.	12
virtual_appointments	bigint	The number of appointments in the session delivered virtually.	3
patient_age_at_appointment	integer	The patient age recorded for the appointment activity.	45
registered_patient_count	bigint	The number of session appointments linked to patients registered at the organisation.	14
unregistered_patient_count	bigint	The number of session appointments linked to patients not registered at the organisation.	1
organisation	varchar	An identifier for the source organisation (GP practice) associated with the session 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

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	✗	✓
session_description	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
last_updated	timestamp(6) with time zone	✓	✓	session_last_updated_datetime
private_flag	boolean	✓	✓	is_private
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
session_category_id	bigint	✗	✓
session_category_display_name	varchar	✓	✓
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
session_id	bigint	✗	✓
emis_session_guid	varchar	✓	✓	session_guid
session_uuid	varchar	✗	✓
session_description	varchar	✓	✓
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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	✗	✓
session_emis_organisation_id	bigint	✓	✓	organisation_id
session_emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
session_description	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
last_updated	timestamp(6) with time zone	✓	✓	session_last_updated_datetime
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
session_category_id	bigint	✗	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓	is_private
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

The table below presents the flavour specific schema and maps columns between core and the flavour.

Column Name	Data Type	Core Mapping	Comments
is_deleted	boolean
pseudo_session_uuid	varchar	session_uuid	hashed
pseudo_organisation_uuid	varchar	organisation_uuid	hashed
session_description	varchar
session_start_date	date
session_start_time	varchar
session_end_date	date
session_end_time	varchar
session_last_updated_datetime	timestamp(6) with time zone
session_type_id	bigint
session_type_description	varchar
session_category_id	bigint
session_category_display_name	varchar
pseudo_location_uuid	varchar	location_uuid	hashed
is_private	boolean
total_slots	bigint
bookable_slots	bigint
booked_slots	bigint
unfilled_slots	bigint
deleted_slots	bigint
embargoed_slots	bigint
dna_appointments	bigint
cancelled_and_rebooked_appointments	bigint
cancelled_and_not_rebooked_appointments	bigint
same_day_appointments	bigint
in_person_appointments	bigint
virtual_appointments	bigint
patient_age_at_appointment	integer
registered_patient_count	bigint
unregistered_patient_count	bigint
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.

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	✗	✓
session_description	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
last_updated	timestamp(6) with time zone	✓	✓	session_last_updated_datetime
private_flag	boolean	✓	✓	is_private
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
session_category_id	bigint	✗	✓
session_category_display_name	varchar	✓	✓
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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	✗	✓
organisation_id	bigint	✗	✓
emis_session_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
session_category_id	bigint	✗	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in V2 for more details.
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	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.

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	✗	✓
organisation_id	bigint	✗	✓
emis_session_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
session_category_id	bigint	✗	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1

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	✓	✓
appointment_session_guid	varchar	✓	✓	session_guid
session_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✓	✓
location_uuid	varchar	✗	✓
start_date	date	✓	✓	session_start_date
start_time	varchar	✓	✓	session_start_time
end_date	date	✓	✓	session_end_date
end_time	varchar	✓	✓	session_end_time
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
private	boolean	✓	✓	is_private
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in V2 for more details.
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1

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	✗	✓
location_id	bigint	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
session_description	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
last_updated	timestamp(6) with time zone	✓	✓	session_last_updated_datetime
session_type_id	bigint	✗	✓
session_type_description	varchar	✓	✓
session_category_id	bigint	✗	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓	is_private
total_slots	bigint	✗	✓		New in v2
bookable_slots	bigint	✗	✓		New in v2
booked_slots	bigint	✗	✓		New in v2
unfilled_slots	bigint	✗	✓		New in v2
deleted_slots	bigint	✗	✓		New in v2
embargoed_slots	bigint	✗	✓		New in v2
dna_appointments	bigint	✗	✓		New in v2
cancelled_and_rebooked_appointments	bigint	✗	✓		New in v2
cancelled_and_not_rebooked_appointments	bigint	✗	✓		New in v2
same_day_appointments	bigint	✗	✓		New in v2
in_person_appointments	bigint	✗	✓		New in v2
virtual_appointments	bigint	✗	✓		New in v2
patient_age_at_appointment	integer	✗	✓		New in v2
registered_patient_count	bigint	✗	✓		New in v2
unregistered_patient_count	bigint	✗	✓		New in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema