Changes in iPCV V2

1.Enhancements / Changes to Existing Columns

a. Maintain Organisation of Merged Patients

For merged patients, patient_organisation_guid is resolved through the patient-organisation relationship and preserves the original organisation GUID. This keeps identifiers stable for downstream consumers and supports consistent tracking of records linked to that patient across merges.

Why?

Patient merges can otherwise change organisational context and create inconsistent identifiers across records. Preserving the original patient_organisation_guid maintains continuity and supports reliable joins.

Customer benefit

Stable patient organisation identifiers before and after merge events More reliable cross-model joins and lineage for merged patient records Reduced risk of duplicate or fragmented reporting caused by ID drift

Customer action

Continue joining on patient_organisation_guid for organisation-level reporting involving merged patients

b. Maintain Code Related Changes

Code changes are now better represented and processed in the observation, allergy, and immunisation models. This includes both source record updates and classification updates (for example, Allergy or Immunisation) after an MKB update.

Why?

Clinical coding and code classifications can change over time at source or after code-list updates. This ensures those changes are consistently reflected across the affected models.

Customer benefit

• More accurate and up-to-date code and classification values
• Consistent behaviour across observation, allergy, and immunisation outputs
• Reduced risk of mismatched coding between related care record models

Customer action

• No action needed

c. Problem Observation Bridge Data

The problem-observation data has been improved to include the first problem across all episodes (Associated and Episode Follow On). This ensures that clinically significant problem relationships are captured even when problems are recorded on follow-on episodes.

The prioritisation logic has also been updated to sequence records by effective_datetime, then availability_datetime, and finally observation_id. This ensures deterministic ordering when effective_datetime is missing or when multiple records share the same effective_datetime and availability_datetime.

Why?

The problem-observation data is now better represented and ensures the relevant problems are surfaced.

Customer benefit

• More complete problem data when analysing observation-problem relationships
• Reduced risk of missing clinically significant problem links

Customer action

• No action needed

2. Additions in V2

a. Addition of uncoded Observations

Uncoded observations are now included, as some parent/child observations are uncoded. These are included in observation, problem and referral models. Any observation without a code id will have original_term, associated_text and qualifiers columns set to NULL as they may contain sensitive data.

Why?

Ensures completeness of the dataset, including cases where clinicians record data without a formal code.

Customer benefit

• More complete records, including previously missing uncoded entries which were excluded and failed to link some parent/child observations when parent or child records were uncoded.

• The is_template_header/is_parent column against a parent observation and the parent observation details against a child observation is now better represented in observation, allergy and immunisation models. The is_template_header/is_parent columns will always be set to True/False values and will never be NULL.

• The consultation link is taken from the parent observation if it doesn’t exist against the child. Including uncoded ones, the consultation details are now better represented in observation, allergy and immunisation models.

• The confidentiality flag is derived from parent observations if it doesn’t exist against the child. Adopting confidentiality flag of the uncoded parent/child observations, confidentiality is now better represented for records that doesn’t have a linear relationship.

Customer action

• Update any processes that assume all records will have a clinical code to handle records where code fields may be null.

• If only coded observations are required, it can be filtered out using the code_id column.

b. Addition of text values for maximum and minimum range values

range_maximum_text and range_minimum_text have been added to capture non-numeric range values in the observation, allergy, and immunisation models.

Why?

Some clinical ranges are recorded as text rather than numeric values. These columns preserve those values so range information is retained in full.

Customer benefit

• More complete range representation, including textual limits
• Better clinical interpretation where numeric-only fields are not sufficient
• Reduced information loss in reporting and downstream analytics

Customer action

• Update range-related queries and extracts to use range_minimum_text and range_maximum_text

c. Addition of numeric measurements and range details

Numeric operators, values, units, and range boundary fields have been added to the allergy and immunisation models. These columns are provided in the Addition of new columns section.

Why?

Allergy and immunisation records can include quantitative clinical measurements. Adding structured numeric and range fields standardises how those measurements are captured and interpreted.

Customer benefit

• More consistent representation of numeric measurements across models
• Improved clinical interpretation through explicit operators, units, and range boundaries
• Better comparability and quality of analytics using quantitative values

Customer action

• Update relevant queries and extracts to include numeric measurements and range boundary fields where needed

d. Addition of new columns

New columns have been added to enhance auditability, relationships, and clinical measurements. The models now include the below columns:

User Tracking Columns:

Column Name	Observation	Allergy	Immunisation	Problem	Referral	Diary	Family History
entered_by_user_in_role_id	✓	✓	✓	✓	✓	✓	x
entered_by_user_in_role_uuid	✓	✓	✓	✓	✓	✓	x
authorising_user_in_role_id	✓	✓	✓	✓	✓	✓	x
authorising_user_in_role_uuid	✓	✓	✓	✓	✓	✓	x
last_review_user_in_role_id	x	x	x	✓	x	x	x
last_review_user_in_role_uuid	x	x	x	✓	x	x	x

Relationship Columns:

Column Name	Observation	Allergy	Immunisation	Problem	Referral	Diary	Family History
observation_id	✓	✓	✓	✓	x	x	✓
observation_guid	✓	✓	✓	✓	x	x	✓
observation_uuid	✓	✓	✓	✓	x	x	✓
patient_id	✓	✓	✓	✓	✓	✓	✓
patient_guid	✓	✓	✓	✓	✓	✓	✓
patient_uuid	✓	✓	✓	✓	✓	✓	✓
consultation_id	✓	✓	✓	✓	✓	✓	x
consultation_guid	✓	✓	✓	✓	✓	✓	x
consultation_uuid	✓	✓	✓	✓	✓	✓	x
consultation_section_id	✓	✓	✓	✓	✓	✓	x
consultation_section_uuid	✓	✓	✓	✓	✓	✓	x
problem_observation_id	✓	✓	✓	x	✓	x	x
problem_observation_guid	✓	✓	✓	x	✓	x	x
problem_observation_uuid	✓	✓	✓	x	✓	x	x
observation_organisation_id	✓	✓	✓	✓	✓	x	✓
observation_organisation_guid	✓	✓	✓	✓	✓	x	✓
observation_organisation_uuid	✓	✓	✓	✓	✓	x	✓
patient_organisation_id	✓	✓	✓	✓	✓	✓	✓
patient_organisation_guid	✓	✓	✓	✓	✓	✓	✓
patient_organisation_uuid	✓	✓	✓	✓	✓	✓	✓
is_parent	✓	✓	✓	x	x	x	x
parent_observation_id	✓	✓	✓	x	x	x	x
parent_observation_guid	✓	✓	✓	x	x	x	x
parent_observation_uuid	✓	✓	✓	x	x	x	x
parent_problem_id	x	x	x	✓	x	x	x
parent_problem_guid	x	x	x	✓	x	x	x
parent_problem_uuid	x	x	x	✓	x	x	x
referral_observation_id	x	x	x	x	✓	x	x
referral_observation_guid	x	x	x	x	✓	x	x
referral_observation_uuid	x	x	x	x	✓	x	x
target_organisation_id	x	x	x	x	✓	x	x
target_organisation_guid	x	x	x	x	✓	x	x
target_organisation_uuid	x	x	x	x	✓	x	x
diary_id	x	x	x	x	x	✓	x
diary_guid	x	x	x	x	x	✓	x
diary_uuid	x	x	x	x	x	✓	x
diary_organisation_id	x	x	x	x	x	✓	x
diary_organisation_guid	x	x	x	x	x	✓	x
diary_organisation_uuid	x	x	x	x	x	✓	x

Code Related Columns:

Column Name	Observation	Allergy	Immunisation	Problem	Referral	Diary	Family History
emis_code_category_id	✓	x	x	x	x	x	✓
observation_code_category_id	✓	x	x	x	x	x	✓
observation_type_id	✓	x	x	x	x	x	✓
consultation_source_original_term	✓	✓	✓	✓	✓	x	x
code_category	x	✓	x	x	x	x	x
qualifiers	✓	✓	✓	x	x	x	x
associated_text	✓	✓	✓	x	x	✓	x

Numeric and Range Measurement Columns:

Column Name	Observation	Allergy	Immunisation
numeric_operator	✓	✓	✓
numeric_value	✓	✓	✓
numeric_unit	✓	✓	✓
ucum_code	✓	✓	✓
unit_of_measure	✓	✓	✓
is_abnormal	✓	✓	✓
range_minimum_operator	✓	✓	✓
range_minimum	✓	✓	✓
range_minimum_text	✓	✓	✓
range_maximum_operator	✓	✓	✓
range_maximum	✓	✓	✓
range_maximum_text	✓	✓	✓
range_units	✓	✓	✓
range_qualifier_id	✓	✓	✓
range_qualifier_description	✓	✓	✓

Mapping Columns:

Column Name	Observation	Allergy	Immunisation	Problem	Referral	Diary
episodicity	✓	✓	✓	x	x	x
episodicity_description	✓	✓	✓	x	x	x
problem_significance_id	x	x	x	✓	x	x
problem_status_id	x	x	x	✓	x	x
parent_problem_relationship_id	x	x	x	✓	x	x
location_type_id	x	x	x	x	x	✓
referral_mode_id	x	x	x	x	✓	x
service_type_id	x	x	x	x	✓	x
urgency	x	x	x	x	✓	x
urgency_description	x	x	x	x	✓	x

Family History Columns:

Column Name
member_key
family_member_is_deleted

Why?

These columns have been added to enhance the data model:

• User tracking columns: Enable auditing and lineage tracking by providing standardized foreign keys to the user_in_role_v2 table, replacing the previously nullable GUID columns

• Relationship columns: Support hierarchical structures, link to other models like consultations and problems, and provide clear organisational context

• Code Related columns: Support with more details on the code category and description

• Numeric and Range measurement columns: Standardize how numeric values are represented with operators (e.g., <, >, =) and units of measure and provide comprehensive support for reference ranges and contextual information about measurements

• Mapping columns: Standardize lookup identifiers for coded state and relationship attributes (for example, problem significance/status, parent-problem relationship, referral mode, and location type), so the ids can be used instead of relying on text descriptions

• Family History Columns: These additions make the family-history model easier to use directly by giving customers a more stable row structure for linking family history information.

Customer benefit

• Improved data consistency through standardized foreign key relationships with user_in_role_v2
• Enhanced ability to track hierarchies and relationships (parent observations, linked problems)
• Better support for temporal analysis and consultation-based reporting
• Standardized representation of numeric values with operators and units
• Comprehensive reference range information for clinical decision support
• More flexible filtering and reporting on context and relationships
• Multiple family members recorded against the same observation can be distinguished more reliably using member_key
• Family-member level deletions can be identified separately from the parent observation using family_member_is_deleted

Customer action

Leverage the new columns to enhance reporting and analytics:

• Join entered_by_user_in_role_id with user_in_role_v2 to retrieve the relevant emis_enteredby_userinrole_guid for audit trails
• Join authorising_user_in_role_id with user_in_role_v2 to retrieve the relevant emis_authorising_userinrole_guid for authorization tracking
• Use the relationship id columns like:
  • parent_observation_id to build hierarchical observation structures and understand observation relationships
  • consultation_id to link observations to specific consultations for temporal and encounter-based analysis
  • problem_observation_id and problem_observation_uuid to identify observations linked to specific problems
• If required, mapping identifiers (problem_significance_id, problem_status_id, parent_problem_relationship_id, referral_mode_id, location_type_id) can be used for reference/lookup datasets.
• Incorporate numeric_operator and unit_of_measure when analysing numeric observations to ensure correct interpretation
• Utilize range columns (range_minimum_operator, range_maximum_operator, etc.) for clinical decision support and flagging abnormal values
• Use member_key to distinguish separate family-member entries under the same observation
• Use family_member_is_deleted where member-level deletion handling is needed

Across observation-derived models, shared structural and operational additions also include columns such as observation_type_id, load_datetime, and transform_datetime where applicable.

3. Renamed Columns

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

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
id	observation_guid	Renamed to make it consistent with all models
organisation_guid	patient_organisation_guid	Renamed to differentiate the organisation set against the patient and the care record.

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

4. Removal of Columns

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

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
other_code	code_id	codeable_concept_v2
other_code_system	code_id	codeable_concept_v2
other_display	code_id	codeable_concept_v2
readv2_code	code_id	codeable_concept_v2
snomed_concept_id	code_id	codeable_concept_v2
snomed_description_id	code_id	codeable_concept_v2
familymember_other_code	familymember_emis_code_id	codeable_concept_v2
familymember_other_code_system	familymember_emis_code_id	codeable_concept_v2
familymember_other_display	familymember_emis_code_id	codeable_concept_v2
familymember_readv2_code	familymember_emis_code_id	codeable_concept_v2
familymember_snomed_concept_id	familymember_emis_code_id	codeable_concept_v2
familymember_snomed_description_id	familymember_emis_code_id	codeable_concept_v2
observation_other_code	observation_emis_code_id	codeable_concept_v2
observation_other_code_system	observation_emis_code_id	codeable_concept_v2
observation_other_display	observation_emis_code_id	codeable_concept_v2
observation_readv2_code	observation_emis_code_id	codeable_concept_v2
observation_snomed_concept_id	observation_emis_code_id	codeable_concept_v2
observation_snomed_description_id	observation_emis_code_id	codeable_concept_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
non_regular_and_current_active_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
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_flag	patient_id	patient_v2
value_pq_2		No longer available in v2
user_selected		No longer available in v2
action_date		No longer available in v2
referral_source_organisation_guid		No longer available in v2
data_filter		No longer available in v2
_record_version		No longer available in v2
_update_date		No longer available in v2 as changes are tracked differently
_update_hour		No longer available in v2 as changes are tracked differently

Why?

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

• MKB reference columns: No longer included to reduce complexity and avoid duplication, as this data is now available through dedicated reference tables
• Patient filter columns: Removed as these flags are maintained in the patient table, providing a single source of truth
• Numeric measurement columns: Removed as these columns doesn’t provide any data currently and is not available in source
• GUID columns: Removed as this data is now provided with a different column name

Customer benefit

• Single source of truth for patient flags through the patient dimension table
• Reduced risk of data inconsistencies and synchronization issues
• Improved data lineage tracking with centralized reference tables

Customer action

• Review and update any processes or integrations that relied on the removed columns
• Use dedicated MKB reference tables for code mappings instead of inline columns
• Query the patient dimension table for patient flags and status information
• Ensure that any necessary mappings or transformations are adjusted accordingly

4. NULL value columns

Several columns in the observation table 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_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
clinician_user_in_role_guid	authorising_user_in_role_id	user_in_role_v2
practitioner_id	authorising_user_in_role_id	user_in_role_v2
emis_last_review_userinrole_guid	last_review_user_in_role_id	user_in_role_v2
last_review_user_in_role_guid	last_review_user_in_role_id	user_in_role_v2
received_date	referral_made_datetime	referral_v2 (referral_made_datetime column is populated only in inbound referrals)
comparator	numeric_operator	Replacement column exists in the model itself
deleted	is_deleted	Replacement column exists in the model itself
registration_ods_code	ods_code	organisation_v2
document_guid		Always NULL as it is deprecated in v2
processing_id		Always NULL as it is deprecated in v2
fhir_episodicity		Always NULL as it is deprecated in v2
fhir_interpretation_code		Always NULL as it is deprecated in v2
fhir_category		Always NULL as it is deprecated in v2
fhir_status		Always NULL as it is deprecated in v2
status		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 behavior in downstream processes.

Customer benefit

• User in role information is now standardized through entered_by_user_in_role_id and authorising_user_in_role_id foreign keys joined with user_in_role_v2
• Enhanced clinical measurement representation through improved numeric operators and qualifier mapping
• Reduced risk of unexpected behavior in downstream processes
• Clear distinction between deprecated columns and new standardized relationships

Customer action

• Identify any queries or reports that use the deprecated columns
• Update processes to use entered_by_user_in_role_id and authorising_user_in_role_id, joining with user_in_role_v2 to retrieve user in role details
• Replace usage of deprecated comparator with the new numeric_operator column for enhanced clinical decision support
• Replace usage of deprecated received_date with the new referral_made_datetime column for inbound referral made datetime
• Use episodicity instead of fhir_episodicity for standardized episodicity representation
• Use code_category instead of fhir_category for code category representation representation

Family History Model - NULL value for columns available in observation model

Several columns previously available both in family-history model and observation model in v1 return NULL values in the family history model in v2. These remain available for backward compatibility but will be removed in a future release.

Why?

These columns have been removed to streamline the family-history model and maintain a clearer separation of responsibilities:

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	Available in observation_v2 model. Use it to fetch the guid from user_in_role_v2 model.
emis_authorising_userinrole_guid	authorising_user_in_role_id	Available in observation_v2 model. Use it to fetch the guid from user_in_role_v2 model.user_in_role_v2
emis_encounter_guid	emis_encounter_guid	Available in observation_v2 model.
exa_encounter_guid	exa_encounter_guid	Available in observation_v2 model.
end_date	end_date	Available in observation_v2 model.
emis_episodicity	emis_episodicity	Available in observation_v2 model.
emis_episodicity_description	emis_episodicity_description	Available in observation_v2 model.
fhir_episodicity		Always NULL as it is deprecated in v2
abnormal	abnormal	Available in observation_v2 model.
qualifiers	qualifiers	Available in observation_v2 model.
hestia_event_type	observation_type_description	Available in observation_fh_v2 model itself

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

Why?

These columns are retained to avoid breaking downstream consumers immediately, while signalling that they are deprecated and should no longer be relied on for new development.

Customer benefit

• Backward compatibility is preserved while consumers transition away from deprecated fields
• User-in-role relationships are clearer when using ID-based joins
• Deprecated columns are easier to identify and phase out safely

Customer action

• Update downstream logic to avoid depending on columns that now return NULL
• Use entered_by_user_in_role_id and authorising_user_in_role_id with user_in_role_v2 where user attribution is required
• Treat the remaining NULL-only columns as deprecated and plan to remove them from customer-owned logic over time