Definition

The Consultation data model in iPCV v2 is made up of two closely related tables:

Consultation – represents the overall encounter between a patient and a healthcare provider or organisation (for example, a GP appointment, telephone consultation or review). It captures the high‑level context such as consultation type, dates, location and key participants.

Consultation Section – represents the structured sections within a consultation (for example, History, Examination, Plan, Medication Review). Each section is linked back to its parent consultation and, where applicable, to the underlying clinical event data.

Together, these models provide a comprehensive view of a consultation, from the top‑level encounter down to the individual sections of the clinical record.

Information

The data model captures consultations and their sections across organisations. Each consultation is identified by a consultation ID and GUIDs, and links to the organisation, patient and key users involved, along with attributes such as consultation type, dates, location, completion and confidentiality status. Consultation sections provide more detailed structure within each consultation, identifying individual sections and their headings, linking to underlying events where relevant, and carrying their own timing, location, confidentiality, sensitivity and lifecycle metadata.

The model includes the following key identifiers:

• consultation_id - Unique identifier for the consultation
• consultation_guid - GUID for the consultation record
• consultation_uuid - UUID for the consultation record

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

• is_deleted - Indicates whether a session record has been marked as deleted in the source system.
• transform_datetime - Timestamp when the record was last processed and updated in the data warehouse
• extract_datetime - Timestamp when the data was extracted from the source system
• _execution_date - Date when the ETL pipeline execution occurred

Overview

flowchart TD
    patient[(patient)] --> consultation[(consultation)]
    organisation[(organisation)] --> consultation
    user_in_role[(user_in_role)] --> consultation
    appointment_slot[(appointment_slot)] --> consultation
    consultation --> consultation_section[(consultation_section)]

    classDef nodeStyle stroke:#9961a4;
    class patient,organisation,user_in_role,appointment_slot nodeStyle;

Changes in iPCV v2

Consultation (encounter -> encounter_v2 / consultation -> consultation_v2 )

1. Change: Removal of Columns

Several columns previously available directly in the encounter (v1) table have been removed from the encounter_v2 (v2) table. These include:

MKB columns:

• other_code
• other_code_system
• other_display
• readv2_code
• snomed_concept_id
• snomed_description_id

Why?

The mkb reference columns are no longer included to reduce complexity of the model.

Customer benefit

• Simplified data model with fewer columns to manage
• Reduced risk of errors and data inconsistencies between encounters and other clinical data
• Easier to understand and work with the consultation data model

Customer action

• Review and update any processes or integrations that relied on the removed MKB columns
• Ensure that any necessary mappings or transformations are adjusted accordingly

2. Change: NULL value columns

Practitioner and organisation-level details:

• emis_enteredby_userinrole_guid
• emis_authorising_userinrole_guid
• registration_ods_code
• sensitive_flag

Why?

To ensure that NULL values are handled consistently and do not lead to unexpected behaviour in downstream processes.

Customer benefit

• The userinrole_guid value can instead be picked from user_in_role_v2 by joining on the userinrole_ids.
• Reduced risk of unexpected behaviour in downstream processes

Customer action

• Review and update any processes or integrations that relied on the NULL columns

• To get the relevant registration_ods_code, join with patient and organisation.

  Example:

  SELECT e.emis_consultation_id, p.emis_patient_id, p.registration_organisation_id, o.ods_code
  FROM hive.explorer_ipcv_vanilla.encounter_v2 e
  JOIN hive.explorer_ipcv_vanilla.patient_v2 p
    ON e.emis_patient_id = p.emis_patient_id
  JOIN hive.explorer_ipcv_vanilla.organisation_v2 o
    ON p.registration_organisation_id = o.organisation_id
    and p.organisation = o.organisation;

3. Change: Addition of new columns

The v2 consultation model introduces additional technical and status‑tracking columns, including:

• entered_by_user_in_role_id
• authorising_user_in_role_id
• is_deleted
• transform_datetime

Why?

To support downstream auditing, temporal analysis and soft‑deletion semantics.

Customer benefit

• To improve data consistency join with user_in_role_v2 to get the guid value on entered_by_user_in_role_id / authorising_user_in_role_id
• Ability to identify consultations that have been deleted or changed since initial creation
• More flexible filtering and reporting on “current vs historical” state

Customer action

Optionally enhance existing reporting to:

• include entered_by_user_in_role_id to join with user_in_role_v2 to get the relevant emis_enteredby_userinrole_guid
• include authorising_user_in_role_id to join with user_in_role_v2 to get the relevant emis_authorising_userinrole_guid
• exclude or explicitly include is_deleted = TRUE rows
• use transform_datetime for change‑data‑capture and refresh logic

Example:

SELECT
  encounter.is_deleted, encounter.emis_consultation_id, encounter.emis_patient_id, encounter.entered_by_user_in_role_id, user_in_role.emis_userinrole_guid
FROM hive.explorer_ipcv_vanilla.encounter_v2 AS encounter
LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 AS user_in_role
  ON user_in_role.emis_user_id = encounter.entered_by_user_in_role_id
  AND user_in_role.organisation = encounter.organisation

SELECT
  encounter.is_deleted, encounter.emis_consultation_id, encounter.emis_patient_id, encounter.authorising_user_in_role_id, user_in_role.emis_userinrole_guid
FROM hive.explorer_ipcv_vanilla.encounter_v2 AS encounter
LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 AS user_in_role
  ON user_in_role.emis_user_id = encounter.authorising_user_in_role_id
  AND user_in_role.organisation = encounter.organisation

4. Change: Removal of Low‑Value or Derived Columns

Some columns that were redundant, consistently null, or better sourced from other tables have been removed from v2 consultation, for example:

• _record_version
• _update_date
• _update_hour

Why?

To simplify the schema and promote consistent use of canonical sources (e.g. organisation tables for practice attributes, pipeline metadata for update timing).

Customer benefit

• A cleaner consultation table with fewer rarely‑used or confusing technical columns
• Reduced storage and processing overhead
• More predictable semantics for remaining columns

Customer action

If you previously used these fields:

• Update any processes that assume all consultation records will have certain practitioner or patient attributes directly available

Removal of Redundant or Deprecated Fields

In iPCV v1, several attributes of other entities were directly included in the consultation model. In iPCV v2, the below fields have been removed from the consultation data model, and relevant IDs are provided to retrieve this information from dedicated extended data models.

Why?

This change simplifies the core consultation model, making it lighter and easier to manage. By moving specific attributes to their own models, data redundancy is reduced, and maintainability is improved. This allows for more focused information within the extended models, that increases the performance of iPCV v2.

Note: These decommissioned columns will be removed in a future iPCV release; plan migrations accordingly

Decommissioned Column	Relevant ID/ Column	Reason / Extended Data Model to be Referred
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
sensitive_flag		Always NULL as it is deprecated in v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2

Customer benefit

• Improved Performance: A leaner consultation model can lead to faster query performance.
• Enhanced Scalability: Decoupled models are easier to maintain and extend independently.
• Data Consistency: Centralizing specific attributes in their own models ensures a single source of truth.

Customer action

• Review all reports and queries that rely on the decommissioned fields listed above
• Update them to join with the appropriate extended data models using the new IDs provided in the table
• Test thoroughly to ensure data accuracy and query performance with the new model structure

Examples

Get all consultations for a patient

SELECT *
FROM hive.explorer_ipcv_vanilla.encounter_v2
WHERE emis_patient_id = 123456789

Find confidential consultations

SELECT *
FROM hive.explorer_ipcv_vanilla.encounter_v2
WHERE confidential_flag = TRUE

Get consultations marked as deleted

SELECT *
FROM hive.explorer_ipcv_vanilla.encounter_v2
WHERE is_deleted = TRUE

Find consultations for a specific organisation

SELECT *
FROM hive.explorer_ipcv_vanilla.encounter_v2
WHERE organisation = 'CDB-50002'