Definition

The Consultation Section data model in iPCV v2 represents the structured sections within a consultation encounter (for example, History, Examination, Plan, Medication Review). Each consultation section is linked back to its parent consultation and connects to underlying clinical event.

Consultation sections serve as the crucial linking table between consultations and specialized clinical data tables, implementing category-based deduplication that enables efficient downstream processing across multiple clinical satellites including observations, diary entries, issue records, test requests, reports, and care plan details.

Information

The data model captures consultation sections across organisations, with each section identified by a consultation_section_id and various GUIDs. It links to the parent consultation, patient, organisation and key users involved, along with attributes such as section category, headings, timing, location, completion and confidentiality status.

The model includes the following key identifiers:

• consultation_section_id - Unique identifier for each consultation section
• consultation_section_guid - EMIS consultation section GUID
• consultation_section_uuid - UUID for the consultation section record
• event_guid - Reference to the specific clinical event
• event_uuid - Globally unique identifier for the linked clinical event

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

• is_deleted - Indicates whether a section 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
• _execution_date - Date when the ETL pipeline execution occurred
• _ingest_time - Data ingestion timestamp

Overview

flowchart TD
    consultation[(consultation)] --> consultation_section[(consultation_section)]
    patient[(patient)] --> consultation_section
    organisation[(organisation)] --> consultation_section
    user_in_role[(user_in_role)] --> consultation_section
    appointment_slot[(appointment_slot)] --> consultation_section
    consultation_section --> observation[(observation)]
    consultation_section --> diary[(diary)]
    consultation_section --> issue_record[(issue_record)]
    consultation_section --> test_request[(test_request)]
    consultation_section --> report[(report)]
    consultation_section --> care_plan_detail[(care_plan_detail)]

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

Changes in iPCV v2

Consultation Section (encounter_section -> encounter_section_v2 / consultation_section -> consultation_section_v2)

Additional changes aligned with Consultation v2

The consultation_section model inherits several platform-wide schema decisions made for Consultation (encounter_v2). These changes align the section model with consultation-level improvements and make downstream joins and auditing more consistent.

1. Change: Removal of Columns

Several low-value or rarely-used MKB/reference columns that appeared in v1 models have been removed to simplify the model. Examples include:

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

Why?

To reduce surface area and complexity of the model and avoid duplication of code systems that are better handled by dedicated reference tables.

Customer benefit

• Simpler schema with fewer rarely-used columns
• Reduced confusion when joining to clinical reference data

Customer action

• Review and update any processes or integrations that relied on these MKB/reference columns and migrate mappings to reference tables if required.

2. Change: NULL-valued practitioner and organisation columns

Certain practitioner and organisation GUID columns are now presented as NULL in the section-level view and are expected to be resolved by joining to the canonical user/organisation models. Examples:

• emis_enteredby_userinrole_guid
• emis_authorising_userinrole_guid
• registration_ods_code
• sensitive_flag

Why?

To ensure NULL handling is consistent and to encourage joins to canonical user/organisation tables for reliable GUID and organisation attributes.

Customer benefit

• More predictable NULL semantics
• Encourages use of canonical join paths to get authoritative values

Customer action

• Join to user_in_role_v2, patient_v2 and organisation_v2 as needed to obtain the GUID or ODS values rather than relying on nullable fields in the section model.

3. Change: Addition of new technical/status columns

The v2 models introduce additional technical and status-tracking columns to support auditing and soft-deletion semantics. These are available on both consultation and consultation_section models:

• 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 in a consistent way across related models.

Customer benefit

• Easier detection of deleted or changed sections
• Consistent audit fields for joins and change-data-capture

Customer action

• Use entered_by_user_in_role_id / authorising_user_in_role_id to join with user_in_role_v2 for GUIDs
• Use is_deleted to include/exclude soft‑deleted rows in reporting
• Use transform_datetime for incremental delta extraction

4. Change: Removal of low-value or derived pipeline columns

Some pipeline metadata columns that tended to be redundant or unused have been removed from v2 section views, for example:

• _record_version
• _update_date
• _update_hour

Why?

To promote canonical sources for update timing (e.g., transform_datetime) and reduce confusion caused by multiple overlapping technical columns.

Customer benefit

• Cleaner model surface and fewer rarely-used fields to manage

Customer action

• Update any processes that use _update_* or _record_version columns according to your ETL or audit logic requirements.

Removal of Redundant or Deprecated Fields

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

Why?

This change simplifies the core consultation_section 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
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 consultation sections for a specific consultation

SELECT *
FROM hive.<flavour>.encounter_section_v2
WHERE consultation_guid = 'A1B2C3D4-E5F6-7G8H-9I0J-K1L2M3N4O5P6'

Find confidential consultation sections

SELECT *
FROM hive.<flavour>.encounter_section_v2
WHERE confidential_flag = TRUE

Join consultation sections with their parent consultations

SELECT consultation.*, section.*
FROM hive.<flavour>.encounter_v2 consultation
JOIN hive.<flavour>.encounter_section_v2 section
  ON consultation.consultation_guid = section.consultation_guid
  AND consultation.organisation = section.organisation
WHERE consultation.emis_patient_id = 123456789

Get sections by category type

SELECT *
FROM hive.<flavour>.encounter_section_v2
WHERE hestia_event_type = 'OBS'

Find sections marked as deleted

SELECT *
FROM hive.<flavour>.encounter_section_v2
WHERE is_deleted = TRUE