Definition

The appointment_session_user model maintains the mapping between healthcare appointments and the users/roles associated with each appointment session, providing visibility into who was involved in specific appointment interactions.

Information

The appointment_session_user model provides a view of the relationships between appointment sessions and healthcare users. It captures the many-to-many mapping between appointment sessions and healthcare staff users.

The data model maintains the linkage between appointment sessions and users, allowing users to track which healthcare professionals were involved in specific appointment interactions. Each appointment_session_user record is uniquely identified by the combination of session_id, user_in_role_id, and organisation, enabling detailed tracking of appointment participation across different healthcare providers.

The model includes the following key identifiers:

• session_id and session_guid - Numeric and string identifiers for the appointment session
• user_in_role_id and user_in_role_guid - Numeric and string identifiers for the user in a specific role
• organisation and organisation_id - Different formats of organization identifiers

In the appointment_session_user model, the following datetime fields are important for tracking data lineage and freshness:

• transform_datetime - Shows when the record was last transformed in the data pipeline, which can help determine the currency of the relationship information.
• extract_datetime - Indicates when the data was extracted from the source system.
• _execution_date - A string representation of when the processing job was executed, useful for debugging and auditing purposes.

Overview

flowchart TB
    subgraph container["Data Collection"]
        n10["Session Identifier"]
        n11["User Information"]
        n12["Organisation Data"]
    end

    n17["Organisation 1"] --> n7
    n17["Organisation 1"] --> n5
    n18["Organisation 2"] --> n6
    n18["Organisation 2"] --> n8
    n18["Organisation 2"] --> n4

    n7["User Session 123"] --> container
    n5["User Session 98"] --> container
    n6["User Session 456"] --> container
    n4["User Session 20"] --> container
    n8["User Session 47"] --> container

    container --> n13["Gather User Sessions"]
    n13 --> |"Unique Session IDs"|n16["123, 98, 456, 20, 47"]
    n16 --> n14["ETL"]
    n14 --> n15["Appointment Session User Model"]

    n19["User Roles"] --> n14
    n20["Appointment Data"] --> n14

    n7["User Session 123"]:::rect
    n5["User Session 98"]:::rect
    n6["User Session 456"]:::rect
    n4["User Session 20"]:::rect
    n8["User Session 47"]:::rect

    n10:::rect
    n11:::rect
    n12:::rect
    n13:::extract
    n14:::event
    n15:::database
    n16:::rect
    n19:::rect
    n20:::rect

    classDef rect rect
    classDef extract circle
    classDef event path
    classDef database cylinder

Changes in iPCV v2

Streamlined ingest_time calculation

We have updated how the ingest time is calculated.

Previously it was calculated using the latest value across related tables like appointment session i.e. GREATEST(su._ingest_time, uir._ingest_time, s._ingest_time).

Now it is calculated using the ingest time from core appointment_session_user table only i.e. session_user._ingest_time.

Why?

This change ensures that the ingest time reflects only when the core record changed, rather than being influenced by updates in related tables. It provides a cleaner, more consistent timestamp for changes to session user records

Benefits

This change provides the following benefits:

• Greater clarity and consistency in ingest timestamps
• Improved change tracking: Only updates to the appointment session user will reflect the ingestion time

Action required

No customer action is required unless your logic depends on the value of ingest time field.

Examples

Finding users for specific sessions

SELECT
    asu.session_id,
    asu.user_in_role_id,
    asu.organisation,
    asu.transform_datetime,
    asu.extract_datetime
FROM hive.explorer_ipcv.srv_appointment_session_user asu
WHERE
    asu.session_id IN (123456, 789012)
ORDER BY
    asu.session_id, asu.user_in_role_id

Counting sessions per user in last month

SELECT
    asu.user_in_role_id,
    asu.organisation,
    COUNT(DISTINCT asu.session_id) AS total_sessions
FROM hive.explorer_ipcv.srv_appointment_session_user asu
WHERE
    asu.transform_datetime > DATE_ADD('month', -1, CURRENT_TIMESTAMP)
GROUP BY
    asu.user_in_role_id, asu.organisation
ORDER BY
    total_sessions DESC

Showing recently added session user relationships

SELECT
    asu.session_id,
    asu.user_in_role_id,
    asu.organisation,
    asu.transform_datetime,
    asu.extract_datetime,
    asu._execution_date
FROM hive.explorer_ipcv.srv_appointment_session_user asu
WHERE
    asu.transform_datetime > DATE_ADD('day', -7, CURRENT_TIMESTAMP)
ORDER BY
    asu.transform_datetime DESC,
    asu.session_id
LIMIT 100