Schema

Core

The core schema represents the User In Role schema derived from the EMIS source system.

Column Name	Data Type	Description	Example
user_in_role_id	bigint	The unique internal identifier for the user in role record within an organisation.	12345
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	CDB-50002
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-09-01 09:00:00.000000 UTC’
_ingest_time	varchar	The load_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	20240917084800
is_deleted	boolean	Indicates if this record should be considered soft deleted.	FALSE
user_in_role_guid	varchar	The GUID for the user in role record within an organisation.	B2709D32-A3DE-4D0F-A362-7309FA4A66E7
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_id	bigint	The unique internal identifier for the organisation record within an organisation.	12345
organisation_guid	varchar	The GUID for the organisation record within an organisation.	B2709D32-A3DE-4D0F-A362-7309FA4A66E7
organisation_uuid	varchar	The UUID derived from organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
organisation_specialities	varchar	A comma-separated list of specialty codes for the organisation that the user belongs to.	“100,200”
user_id	bigint	The unique internal identifier for the user record within an organisation.	6789
user_uuid	varchar	The UUID derived from user_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
title	varchar	The title for the user.	Mr
given_name	varchar	The first name of the user.	John
surname	varchar	The surname of the user.	Doe
healthcare_practitioner_type	varchar	The GPES healthcare practitioner type for the user.	A
job_category_code	varchar	The job category code for the user in role.	R5007
job_category_name	varchar	The job category name for the user in role.	System Administrator
contract_start_date	date	The contract start date for the user in role.	2020-01-01
contract_end_date	date	The contract end date for the user in role.	2023-12-01
sds_user_id	varchar	The user’s Spine Directory Service (SDS) user ID	555250704103
sds_user_uuid	varchar	The UUID derived from sds_user_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
general_medical_practitioner_number	varchar	The NHS Prescription Services identifier for a general medical practitioner.	G3371701
general_medical_council_number	varchar	The identifier for the user’s General Medical Council membership, if applicable.	7219735
general_dental_council_number	varchar	The identifier for the user’s General Dental Council membership, if applicable.	5689263
nursing_and_midwifery_council_number	varchar	The identifier for the user’s Nursing and Midwifery Council membership, if applicable.	6828373
identifier_issuing_body	varchar	The professional body (GMC, GDC, or NMC) issuing the professional identifier for the user.	03
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.	202401010000

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 Types	Data Data in V1	Data Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✓	✓	Renamed from emis_user_id in v1
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✗	✓
user_uuid	varchar	✗	✓
sds_user_id	varchar	✗	✓
sds_user_uuid	varchar	✗	✓
sds_job_role_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	job_category_name
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
organisation_specialities	varchar	✗	✓
healthcare_practitioner_type	varchar	✗	✓
general_medical_practitioner_number	varchar	✗	✓
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_userinrole_id	bigint	✓	✓	user_in_role_id
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✗	✓
user_uuid	varchar	✗	✓
sds_user_id	varchar	✗	✓
sds_user_uuid	varchar	✗	✓
sds_job_role_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	job_category_name
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
organisation_specialities	varchar	✗	✓
hcp_type	varchar	✗	✓	healthcare_practitioner_type
gmp_number	varchar	✗	✓	general_medical_practitioner_number
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✓	✓
user_uuid	varchar	✗	✓
sds_user_id	varchar	✗	✓
sds_user_uuid	varchar	✗	✓
sds_job_role_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	job_category_name	Renamed from emis_job_categoy_name in V1
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
organisation_specialities	varchar	✗	✓
hcp_type	varchar	✗	✓	healthcare_practitioner_type
gmp_number	varchar	✗	✓	general_medical_practitioner_number
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping	Comments
is_deleted	boolean
pseudo_user_in_role_uuid	varchar	user_in_role_uuid	Hashed
pseudo_organisation_uuid	varchar	organisation_uuid	Hashed
organisation_specialities	varchar
title	varchar
healthcare_practitioner_type	varchar
job_category_code	varchar
job_category_name	varchar
contract_start_date	date
contract_end_date	date
general_medical_practitioner_number	varchar
general_medical_council_number	varchar
nursing_and_midwifery_council_number	varchar
general_dental_council_number	varchar
identifier_issuing_body	varchar
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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✓	✓		Renamed from emis_user_id in v1
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✓	✓
user_uuid	varchar	✗	✓
sds_user_id	varchar	✗	✓
sds_user_uuid	varchar	✗	✓
sds_job_role_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	general_medical_practitioner_number	Renamed from emis_job_categoy_name in V1
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
organisation_specialities	varchar	✗	✓
hcp_type	varchar	✗	✓	healthcare_practitioner_type
gmp_number	varchar	✗	✓	general_medical_practitioner_number
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✗	✓
user_uuid	varchar	✗	✓
emis_job_category_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	job_category_name
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

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✗	✓
user_uuid	varchar	✗	✓
emis_job_category_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	job_category_name
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

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
user_in_role_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
user_id	bigint	✗	✓
user_uuid	varchar	✗	✓
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
job_category_code	varchar	✓	✓
job_category_name	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

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✓	✓
emis_userinrole_guid	varchar	✓	✓	user_in_role_guid
user_in_role_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
user_id	bigint	✗	✓
user_uuid	varchar	✗	✓
sds_user_id	varchar	✗	✓
sds_user_uuid	varchar	✗	✓
sds_job_role_code	varchar	✓	✓	job_category_code
emis_job_category_name	varchar	✓	✓	job_category_name
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
organisation_specialities	varchar	✗	✓
healthcare_practitioner_type	varchar	✗	✓
general_medical_practitioner_number	varchar	✗	✓
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema