Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	varchar	The datetime that the record was ingested, format YYYYMMDDHHMMSS	’20230512143015’
is_deleted	boolean	If this record should be considered soft deleted	FALSE
organisation_id	bigint	The organisation id is the internal identifier that an organisation uses to identify itself	12345
organisation_guid	varchar	The organisation globally unique identifier	’550e8400-e29b-41d4-a716-446655440000’
session_id	bigint	PK Unique identifier for the session, only unique within the source database	67890
session_guid	varchar	The EMIS globally unique id for the session	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
session_description	varchar	Often used to summarise the purpose for the session	’Morning Surgery’
session_start_date	date	The date that the session will start	’2023-05-15’
session_start_time	varchar	The time that the session will start	’09:00:00’
session_end_date	date	The date that the session will end	’2023-05-15’
session_end_time	varchar	The time that the session will end	’12:30:00’
session_last_updated_datetime	timestamp(6) with time zone	The datetime that this session was last updated at source	’2023-05-10 14:25:36.000000 UTC’
is_private	boolean	Indicates if this is a private session, often used with administration style sessions	FALSE
session_type_description	varchar	Defines the behaviour of the session, whether it has slots, whether it can be booked etc.	‘Bookable’
session_category_display_name	varchar	Free text display name of the session category	’GP Appointments’
location_guid	varchar	The location globally unique identifier where the session takes place	’33eea400-f12c-41d4-b146-446255223344’
organisation	varchar	An identifier for the source of data (an organisation) this relates to a given GP practice	’CDB-12345’
extract_datetime	timestamp(6) with time zone	The datetime that 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 that 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	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	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	✓	✓
private_flag	boolean	✓	✓
session_type_description	varchar	✓	✓
session_category_display_name	varchar	✓	✓
emis_location_guid	varchar	✓	✓
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	✗	✓
session_id	bigint	✗	✓
emis_session_guid	varchar	✓	✓
session_description	varchar	✓	✓
session_type_description	varchar	✓	✓
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	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	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_type_description	varchar	✓	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓
emis_location_guid	varchar	✓	✓
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	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	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	✓	✓
private_flag	boolean	✓	✓
session_type_description	varchar	✓	✓
session_category_display_name	varchar	✓	✓
emis_location_guid	varchar	✓	✓
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	✗	✓
session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_session_organisation_guid	varchar	✓	✓
emis_location_guid	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
session_type_description	varchar	✓	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
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	✗	✓
session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_session_organisation_guid	varchar	✓	✓
emis_location_guid	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
session_type_description	varchar	✓	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

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	✗	✓
session_id	bigint	✓	✓
appointment_session_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
location_guid	varchar	✓	✓
session_start_date	date	✓	✓
session_start_time	varchar	✓	✓
session_end_date	date	✓	✓
session_end_time	varchar	✓	✓
session_type_description	varchar	✓	✓
private	boolean	✓	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

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	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
emis_location_guid	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_type_description	varchar	✓	✓
session_category_display_name	varchar	✓	✓
private_flag	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema