Changes in iPCV V2

1. Removal of Columns

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

Affected columns:

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
`other_code`	`consultation_source_code_id`	`codeable_concept_v2`
`other_code_system`	`consultation_source_code_id`	`codeable_concept_v2`
`other_display`	`consultation_source_code_id`	`codeable_concept_v2`
`readv2_code`	`consultation_source_code_id`	`codeable_concept_v2`
`snomed_concept_id`	`consultation_source_code_id`	`codeable_concept_v2`
`snomed_description_id`	`consultation_source_code_id`	`codeable_concept_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`
`_record_version`	N/A	No longer available in v2
`_update_date`	N/A	No longer available in v2
`_update_hour`	N/A	No longer available in v2

Why?

These columns have been removed to streamline the data model and maintain a single source of truth for reference data. 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.

Customer Benefit

Ease of Use: Simplified data model with fewer (rarely-used) columns to manage.
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: Centralising specific attributes in their own models ensures a single source of truth.

Customer Action

Validate downstream systems or reports to ensure they can utilise the new columns effectively
No immediate action required if consuming the fields directly from the v2 model

2. NULL value columns

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

These columns apply to both consultation and consultation section with the exception of sensitive_flag that only applied to consultation.

These include:

Deprecated Column	Replacement 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`
`registration_ods_code`	`ods_code`	`organisation_v2`
`sensitive_flag`	N/A	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 are retained to maintain backward compatibility while returning NULL values to ensure consistent behaviour in downstream processes.

Customer Benefit

Reduced risk of unexpected behaviour in downstream processes
Clear distinction between deprecated columns and new standardised relationships

Customer Action

Validate downstream systems or reports to ensure they can utilise the new columns effectively
No immediate action required if consuming the fields directly from the v2 model

3. Additions in V2

New columns have been added to enhance auditability and relationships. Selected foreign key and identifier columns have been added directly to the consultation models, so contextual identifiers are available without relying on joins. In most cases below, the GUID column has been preserved.

The models now include the below columns:

Column Name	Consultation Model	Consultation Section Model
`consultation_id`	x	✓
`consultation_uuid`	✓	✓
`consultation_section_id`	x	✓
`consultation_section_uuid`	x	✓
`patient_uuid`	✓	✓
`patient_organisation_id`	✓	✓
`patient_organisation_uuid`	✓	✓
`consultation_organisation_id`	✓	✓
`consultation_organisation_uuid`	✓	✓
`location_id`	✓	✓
`location_uuid`	✓	✓
`location_type_id`	✓	✓
`appointment_slot_id`	✓	✓
`appointment_slot_uuid`	✓	✓
`entered_by_user_in_role_id`	✓	✓
`entered_by_user_in_role_uuid`	✓	✓
`authorising_user_in_role_id`	✓	✓
`authorising_user_in_role_uuid`	✓	✓
`event_uuid`	x	✓

Why?

To include related contextual identifiers, without requiring a join back to related entities.
To enable auditing and lineage tracking by providing standardised foreign keys

Customer Benefit

Improved data consistency through standardised foreign key relationships, making the model more self-contained.
More flexible filtering and reporting on context and relationships

Customer Action

Validate downstream systems or reports to ensure they can utilise the new columns effectively
No immediate action required if consuming the fields directly from the v2 model

4. Renamed Columns

The columns below have been renamed in the consultation model.

Renamed Column	Column Name in v2	Comment
`deleted`	`is_deleted`	Renamed to make it consistent with all models
`execution_date`	`_execution_date`	Renamed to make it consistent with all models
`organisation_guid`	`patient_organisation_guid`	Renamed to differentiate the organisation set against the patient and the consultation.
`emis_appoinment_slot_guid`	`emis_appointment_slot_guid`	Renamed to fix spelling

Why?

Column names were renamed to improve consistency and clarity across care record 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