Changes in iPCV V2

1. Additions in V2

The following columns are introduced across the appointment models. They are included only where relevant model to the specific flavour as presented in the schema.

The table below compares where each added column exists across the three appointment models.

Column Name	Appointment session	Appointment session user	Appointment slot
Session-level identifiers and classification
`session_id`	✓	✗	✓
`session_uuid`	✓	✓	✓
`session_type_id`	✓	✗	✗
`session_category_id`	✓	✗	✗
Organisation and location identifiers
`organisation_id`	✓	✗	✗
`organisation_uuid`	✓	✓	✗
`session_organisation_id`	✗	✗	✓
`session_organisation_uuid`	✗	✗	✓
`location_id`	✓	✓	✗
`location_guid`	✗	✓	✗
`location_uuid`	✓	✓	✗
Session user identifiers
`user_in_role_uuid`	✗	✓	✗
Session utilisation and attendance metrics
`total_slots`	✓	✗	✗
`bookable_slots`	✓	✗	✗
`booked_slots`	✓	✗	✗
`unfilled_slots`	✓	✗	✗
`deleted_slots`	✓	✗	✗
`embargoed_slots`	✓	✗	✗
`dna_appointments`	✓	✗	✗
`cancelled_and_rebooked_appointments`	✓	✗	✗
`cancelled_and_not_rebooked_appointments`	✓	✗	✗
`same_day_appointments`	✓	✗	✗
`in_person_appointments`	✓	✗	✗
`virtual_appointments`	✓	✗	✗
`patient_age_at_appointment`	✓	✗	✓
`registered_patient_count`	✓	✗	✗
`unregistered_patient_count`	✓	✗	✗
Slot identity and patient linkage
`appointment_slot_uuid`	✗	✗	✓
`patient_uuid`	✗	✗	✓
`patient_organisation_uuid`	✗	✗	✓
`external_patient_organisation_uuid`	✗	✗	✓
`appointment_slot_id`	✗	✗	✓
`patient_id`	✗	✗	✓
`patient_organisation_id`	✗	✗	✓
`external_patient_organisation_id`	✗	✗	✓
`external_patient_guid`	✗	✗	✓
`patient_organisation_guid`	✗	✗	✓
Slot timing, status, type, and workflow
`call_started_datetime`	✗	✗	✓
`call_ended_datetime`	✗	✗	✓
`appointment_lead_time_in_minutes`	✗	✗	✓
`current_status_datetime`	✗	✗	✓
`current_status_type_id`	✗	✗	✓
`current_status_last_set_by_global_user_in_role_id`	✗	✗	✓
`current_status_last_set_by_global_user_in_role_uuid`	✗	✗	✓
`slot_type_id`	✗	✗	✓
`slot_type_national_category_name`	✗	✗	✓
`slot_type_national_category_description`	✗	✗	✓
`context_type`	✗	✗	✓
`service_setting`	✗	✗	✓
`booked_by_external_organisation_id`	✗	✗	✓
`booked_by_external_organisation_uuid`	✗	✗	✓
`booking_method_id`	✗	✗	✓
`did_not_attend_reason_id`	✗	✗	✓
`embargo_type_id`	✗	✗	✓
`embargo_days`	✗	✗	✓
`embargo_minutes`	✗	✗	✓
`embargo_time`	✗	✗	✓
`embargo_is_relative`	✗	✗	✓

Why?

These additions provide more granular details for the record. The new ID-based and UUID-based columns in the Appointment session and Appointment session user models align those models with modern identifier practices, paving the way for deprecating older fields and improving system consistency.

The Appointment slot model additions provide durable identity, operational lineage, and clearer booking and attendance context, improving traceability, relationship mapping, and performance analysis across sessions, patients, and external booking sources.

Customer benefit

Enhanced consistency: Standardizes the use of identifiers and status flags across different model flavours
New Appointment session metric columns (e.g. total_slots, booked_slots, dna_appointments) enable session-level utilisation and attendance reporting without needing to aggregate from other models
New location_id, location_guid, and location_uuid columns in the Appointment session user model enable records to be linked directly to the location of the appointment session without needing to join to other models
Better visibility of appointment flow, booking source, and booking outcome
Improved analysis of waiting time, attendance, status changes, and embargo logic
Clearer support for in-person versus virtual, booked versus blocked, and patient journey reporting

Customer action

Review logic that handles patient or session-user data to incorporate the new fields and IDs for more accurate reporting
Begin planning the migration from the Appointment session model’s older identifiers (e.g. registration_organisation_guid / organisation_guid) to their new ID-based counterparts (e.g. registration_organisation_id / organisation_id) in custom queries or downstream systems
Use the v2 identity, status, and booking fields from the Appointment slot model when building new joins and reports
Validate downstream systems or reports to ensure they can utilize the new columns effectively
Review downstream logic that still depends on older slot-level indicators or manual status interpretation
No immediate action required if consuming the fields directly from the v2 model

2. Removal of columns

Several columns as detailed below have been decommissioned in the appointment models.

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
`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
`non_regular_and_current_active`	`patient_id`	patient_v2
`regular_and_current_active`	`patient_id`	patient_v2
`regular_current_active_and_inactive`	`patient_id`	patient_v2
`opt_out_9nd19nu09nu4_flag`	`patient_id`	patient_v2
`opt_out_9nd19nu0_flag`	`patient_id`	patient_v2
`session_category_display_name`	`session_id`	appointment_session_v2
`session_deleted`	`session_id`	appointment_session_v2
`session_location`	`session_id`	appointment_session_v2
`session_start_date_time`	`session_id`	appointment_session_v2
`session_end_date_time`	`session_id`	appointment_session_v2
`session_lead_hcp_type`	`session_id`	appointment_session_v2
`_record_version`		No longer available in v2
`slot_release_date`		Removed as it is deprecated in v2
`slot_release_date_time`		Removed as it is deprecated in v2
`slot_release_time`		Removed as it is deprecated in v2

Why?

These columns have been removed to streamline the data model and maintain a single source of truth for reference data:

Patient filter columns: The Appointment slot model’s patient opt-out and registration flags are maintained in patient_v2.
Appointment session context columns: Session context fields (category, location, and legacy session state/timing fields) are represented through the standardised appointment_session_v2 model.
Legacy slot audit columns: Slot-level legacy audit/release fields are deprecated in v2 and replaced by standardised state and lineage fields such as transform_datetime.

Customer benefit

Single source of truth for patient flags through the patient scope dimension table
Reduced risk of data inconsistencies and synchronisation issues
Cleaner separation of session-level and slot-level attributes
Improved data lineage tracking with centralised reference tables

Customer action

Review and update any processes that relied on the removed columns
Query the patient scope dimension table or patient_v2 model for the patient registration flags that replaced the Appointment slot model’s patient flag columns
Use the appointment_session_v2 model for the Appointment session model’s attributes such as location and category

3. NULL value columns

Several columns in the appointment models now consistently return NULL values and have been retained for backward compatibility.

These include:

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
`emis_consultation_authorising_userinrole_guid`	`appointment_slot_id`	`authorising_user_in_role_id` in `consultation_v2`
`emis_lead_session_userinrole_guid`	`session_id`	`appointment_session_user_v2`
`external_patient_flag`	`external_patient_guid`	Available in the `appointment_slot` model itself
`emis_bookingsource_organisation_guid`		Always NULL as it is deprecated in v2
`did_not_attend_flag`		Always NULL as it is deprecated in v2
`processing_id`		Always NULL as it is deprecated in v2

These decommissioned NULL value columns will be removed in a future iPCV release; plan migrations accordingly.

Why?

These columns needs to be fetched from are retained to maintain backward compatibility while returning NULL values to ensure consistent behavior in downstream processes.

Customer benefit

Improved data lineage tracking with centralised reference tables
External patient identification is now handled through the Appointment slot model’s external_patient_guid and external_patient_organisation_id

Customer action

Review and update any processes that relied on these columns
Use the Appointment slot model’s external_patient_guid to identify external patients instead of external_patient_flag

4. Renamed Columns

Several columns as detailed below have been renamed in the appointment models.

Renamed Column	Column Name in v2	Comment
`session_deleted_flag`	`is_deleted`	Renamed to make it consistent with all models
`session_deleted`	`is_deleted`	Renamed to make it consistent with all models
`session_user_deleted_flag`	`is_deleted`	Renamed to make it consistent with all models
`slot_deleted`	`is_deleted`	Renamed to make it consistent with all models
`deleted`	`is_deleted`	Renamed to make it consistent with all models
`sendin_time`	`sent_in_datetime`	Renamed to make it consistent with other status datetime columns
`left_time`	`left_datetime`	Renamed to make it consistent with other status datetime columns
`time_booked`	`booked_datetime`	Renamed to make it consistent with other datetime columns
`patient_wait_in_minutes`	`waiting_time_in_minutes`	Renamed to make it consistent with other datetime columns
`organisation_guid`	`session_organisation_guid`	Renamed to differentiate the organisation set against the patient and the session.
`execution_date`	`_execution_date`	Renamed to make it consistent with all models

Why?

Column names were renamed to improve consistency and clarity across appointment models and flavour outputs.

Customer benefit

More consistent naming patterns across models, reducing ambiguity in joins and transformations
Lower risk of mapping errors during migration from v1 naming conventions

Customer action

Review any processes or integrations that relied on the renamed columns
Ensure that any necessary mappings or transformations are adjusted accordingly