Schema

Core

The core schema represents the Sharing Agreement schema derived from the EMIS source system.

Column Name	Data Type	Description	Example
is_deleted	boolean	Indicates if this record should be considered soft deleted.	FALSE
agreement_guid	varchar	The GUID for the sharing agreement	D0AD76B9-200B-4D52-8199-8D8F485EB1D7
requester_organisation_guid	varchar	The GUID for the requester organisation	A1B2C3D4-1234-5678-9ABC-DEF012345678
sharer_organisation_guid	varchar	The GUID for the sharer organisation	E5F60718-9012-3456-7890-ABCDEF123456
emis_index_schema_id	integer	The unique internal identifier for the EMIS Index schema.	1
user_id	bigint	The unique internal identifier for the user assigned to the agreement.	12345
can_process	boolean	Indicates whether data can currently be processed under this agreement (NULL if deleted).	TRUE
requester_status	varchar	The status of the requester organisation in the agreement (NULL if deleted).	Activated
requester_is_activated	boolean	Indicates whether the requester organisation is currently activated.	TRUE
requester_was_activated	boolean	Indicates whether the requester organisation was previously activated.	FALSE
requester_cdb	bigint	The requester organisation CDB number (NULL if deleted).	50001
requester_name	varchar	The requester organisation name (NULL if deleted).	Requester Health Trust
agreement_status	varchar	The enabled or disabled status of the agreement (NULL if deleted).	Enabled
agreement_type_id	bigint	The unique internal identifier for the agreement type (NULL if deleted).	5
agreement_type_description	varchar	The description of the agreement type (NULL if deleted).	Standard Data Sharing
agreement_id	bigint	The unique internal identifier for the agreement (NULL if deleted).	1001
agreement_name	varchar	The agreement name (NULL if deleted).	Regional Care Agreement
agreement_purpose	varchar	The agreement purpose description (NULL if deleted).	To facilitate integrated care
sharer_status	varchar	The status of the sharer organisation in the agreement (NULL if deleted).	02. Activated
sharer_is_activated	boolean	Indicates whether the sharer organisation is currently activated (NULL if deleted).	TRUE
sharer_was_activated	boolean	Indicates whether the sharer organisation was previously activated (NULL if deleted).	FALSE
org_type	varchar	The organisation type classification for the sharer (NULL if deleted).	GP Practice
sharer_ods_code	varchar	The sharer organisation ODS code (NULL if deleted).	Y12345
sharer_cdb	bigint	The sharer organisation CDB number (NULL if deleted).	50002
sharer_name	varchar	The sharer organisation name (NULL if deleted).	Example GP Surgery
sharer_close_datetime	timestamp(6) with time zone	The date and time when the sharer organisation closed (NULL if deleted or not closed).	‘2023-09-01 09:00:00.000000 UTC’
patient_count	bigint	The total patient count for the sharer organisation (NULL if deleted).	8500
active_patient_count	bigint	The active patient count for the sharer organisation (NULL if deleted).	7800
active_user_count	bigint	The active user count for the sharer organisation (NULL if deleted).	25
sharer_environment_id	bigint	The unique internal identifier for the sharer environment.	235
sharer_environment	varchar	The environment description for the sharer organisation (NULL if deleted).	AWS_ENG_GP12
last_modified_datetime	timestamp(6) with time zone	The date and time when the agreement was last modified (NULL if deleted).	‘2023-09-01 09:00:00.000000 UTC’
last_modified_date	date	The date when the agreement was last modified (NULL if deleted).	2024-09-17
extract_date	date	The data extract date (NULL if deleted).	2024-09-17
organisation	varchar	The organisation value for the record: EMIS for deleted records, otherwise the sharer’s customer database number.	CDB-50002
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model.	‘2023-09-01 09:00:00.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	20240917084800

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
emis_index_schema_id	integer	✗	✓
user_id	bigint	✗	✓
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping	Comments
is_deleted	boolean
pseudo_agreement_guid	varchar	agreement_guid	Hashed
pseudo_requester_organisation_guid	varchar	requester_organisation_guid	Hashed
pseudo_sharer_organisation_guid	varchar	sharer_organisation_guid	Hashed
emis_index_schema_id	integer
user_id	bigint
agreement_name	varchar
agreement_purpose	varchar
agreement_type_id	bigint
agreement_type_description	varchar
agreement_status	varchar
can_process	boolean
org_type	varchar
requester_status	varchar
requester_is_activated	boolean
requester_was_activated	boolean
requester_cdb	bigint
sharer_status	varchar
sharer_is_activated	boolean
sharer_was_activated	boolean
sharer_ods_code	varchar
sharer_cdb	bigint
sharer_close_datetime	timestamp(6) with time zone
patient_count	bigint
active_patient_count	bigint
active_user_count	bigint
sharer_environment	varchar
last_modified_datetime	timestamp(6) with time zone
last_modified_date	date
extract_date	date
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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2

Zeus

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
is_deleted	boolean	✓	✓		Renamed from deleted in v1
agreement_guid	varchar	✓	✓
requester_organisation_guid	varchar	✗	✓
sharer_organisation_guid	varchar	✓	✓		Renamed from emis_organisation_guid in v1
can_process	boolean	✗	✓
requester_status	varchar	✗	✓
requester_is_activated	boolean	✗	✓
requester_was_activated	boolean	✗	✓
requester_cdb	bigint	✗	✓
requester_name	varchar	✗	✓
agreement_status	varchar	✗	✓
agreement_id	bigint	✗	✓
agreement_name	varchar	✗	✓
agreement_purpose	varchar	✗	✓
agreement_type_id	bigint	✗	✓
agreement_type_description	varchar	✗	✓
sharer_status	varchar	✗	✓
sharer_is_activated	boolean	✓	✓		Renamed from is_activated_flag in v1
sharer_was_activated	boolean	✗	✓
org_type	varchar	✗	✓
sharer_ods_code	varchar	✓	✓		Renamed from ods_code in v1
sharer_cdb	bigint	✓	✓		Renamed from organisation_cdb in v1
sharer_name	varchar	✓	✓		Renamed from name in v1
sharer_close_datetime	timestamp(6) with time zone	✗	✓
active_patient_count	bigint	✗	✓
active_user_count	bigint	✗	✓
sharer_environment_id	bigint	✗	✓
sharer_environment	varchar	✗	✓
last_modified_datetime	timestamp(6) with time zone	✓	✓		Replaced last_modified_date column in v1 to include the timestamp
extract_date	date	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_ingest_time	varchar	✓	✗		Removed in v2
disabled	boolean	✓	✗		Removed in v2