Definition

The User In Role model describes the people who work at an organisation and the roles they hold there. It brings together the person — their name, title and national identifiers — with the post they occupy — their job category, contract dates and employing organisation — so that activity recorded elsewhere in iPCV can be attributed to a named individual in a known role.

Almost every clinical model in iPCV carries a user in role identifier: the person who entered a record, the person who authorised it, the patient’s usual GP, the clinician an appointment session is booked with. This model is how you turn those identifiers into meaningful information.

Information

This model is used to resolve the user in role identifiers that appear throughout iPCV models. It turns those identifiers into usable person and role-level information for reporting, attribution and joining across models.

The model includes users in role belonging to organisations under your DSA; closed ones are excluded, so users in role from closed practices are not returned.

It also includes the practitioner information that was previously held in a separate view. In iPCV, a user in role and a practitioner are the same entity, so there is no separate practitioner population to reconcile.

Grain and Scope

Each record is uniquely identified by user_in_role_id and organisation columns. A single person can hold multiple roles, and can hold roles at more than one organisation, so the same user_id will appear on multiple rows. If you are counting people, count distinct user_id; if you are counting posts, count distinct user_in_role_id.

This model includes the following key identifiers:

• user_in_role_id : The unique internal identifier for the user in role record within an organisation.
• user_in_role_guid: The GUID for the user in role within an organisation.
• user_in_role_uuid: The UUID derived from consultation_id and organisation, providing a stable unique identifier.
• user_id: The unique internal identifier for the user within an organisation.
• user_uuid: The UUID derived from user_id and organisation, providing a stable unique identifier.
• sds_user_id : The user’s Spine Directory Service identifier, where one is recorded.
• sds_user_uuid: The UUID derived from sds_user_id and organisation, providing a stable unique identifier.

And the following regulatory identifiers:

• general_medical_council_number:
  • Regulator - General Medical Council
  • Issuing body code - 03
• general_dental_council_number:
  • Regulator - General Dental Council
  • Issuing body code - 02
• nursing and_midwifery_council_number:
  • Regulator - Nursing and Midwifery Council
  • Issuing body code - 09
• general_medical_practitioner_number:
  • Regulator - National GP
  • Issuing body code - N/A

identifier_issuing_body records which regulator supplied the value held against the person, using the codes above. All of these identifiers are sparsely populated. Do not treat a NULL as evidence that a person is unregistered.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the user in role has been deleted at source.
• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• The same person appears once per role. Aggregating without deduplicating on user_id will overcount staff.
• organisation_specialities belongs to the organisation rather than the person, and is a comma-separated string.
• Job category names are entered locally and vary between organisations.
• Clinical models reference the post, not the person. Columns such as entered_by_user_in_role_id and authorising_user_in_role_id join to user_in_role_id. To roll activity up to a person, join to this model first and then group by user_id.

Overview

flowchart TD
    n01["Organisation 1001"]@{ shape: rect}
    n02["Organisation 1002"]@{ shape: rect}
    n1["User 123"]@{ shape: rect}
    n11["UserInRole 1231"]@{ shape: rect}
    n2["User 456"]@{ shape: rect}
    n21["UserInRole 4561"]@{ shape: rect}
    n22["UserInRole 4562"]@{ shape: rect}
    n3["User 789"]@{ shape: rect}
    n31["UserInRole 7891"]@{ shape: rect}
    n91["UserInRoles"]@{ shape: extract}
    n92["ETL"]@{ shape: event}
    n93["User In Role Model"]@{ shape: internal-storage}

    n1 --> n11 --> n91
    n2 --> n21 --> n91
    n2 --> n22 --> n91
    n3 --> n31 --> n91
    n01 --> n1
    n02 --> n2
    n02 --> n3
    n91 --> n92
    n92 --> n93

    classDef nodeStyle stroke:#9961a4;
    class n1,n2,n3,n01,n02,n11,n21,n22,n31,n91,n92,n93 nodeStyle;

    linkStyle default stroke:#117abf,fill:none;

Examples

Get active user-in-roles

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.user_in_role_v2
WHERE
  contract_end_date IS NULL;

Get all users in a specific role

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.user_in_role_v2
WHERE
  emis_job_category_name = 'Example Role';

Get all roles pertaining to a single user

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.user_in_role_v2
WHERE
  user_id = 123;