Definition

The Recall data model provides a comprehensive record of patient recall and diary information derived from primary care systems. It captures structured reminder and follow-up items recorded by clinicians, including the associated clinical code, timing, and contextual consultation information. This model is essential for understanding planned follow-up actions and supporting operational diary activity across healthcare services.

Information

The Recall data model is a structured representation of diary entries recorded against patients in EMIS Web. Each recall record captures operational diary activity used to track planned follow-up actions and clinically relevant reminders.

Each recall record is uniquely identified by a combination of diary_id and organisation, ensuring a distinct entry for every recall within a specific healthcare organisation.

The model includes several key data points to provide a complete clinical picture of patient recalls:

This model includes some of the key identifiers:

diary_id: The unique internal identifier for the diary entry.
diary_guid: GUID for the diary record.
patient_id: Foreign key linking to the patient record.

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

is_deleted: Indicates whether the recall record has been soft-deleted in the source system.
is_sensitive: Indicates whether the record is flagged as sensitive.
is_confidential: Indicates whether the record is confidential.
transform_datetime: The timestamp indicating when the record was last processed in the data warehouse.

Overview

flowchart TD
    patient["Patient"]
    consultation["Consultation"]
    organisation["Organisation"]
    user_in_role["User in Role"]
    diary[["Diary Model"]]

    patient --> diary
    consultation --> diary
    organisation --> diary
    user_in_role --> diary

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

    linkStyle default stroke:#117abf,fill:none

Changes in iPCV v2

Addition of New Columns

The v2 recall model introduces additional columns to enhance auditability, relationships, and clinical context. These include:

User tracking columns:

entered_by_user_in_role_id
authorising_user_in_role_id

Relationship and context columns:

diary_id
diary_organisation_id
consultation_id

Lifecycle tracking columns:

is_deleted
transform_datetime

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
Relationship and context columns: Support links to consultations and organisations, providing better contextual information
Lifecycle tracking columns: Support soft-deletion and change tracking for better data quality and incremental processing

Customer benefit

Improved data consistency through standardized foreign key relationships
Enhanced ability to track recall relationships with consultations and organisations
Better support for temporal analysis and consultation-based reporting
Soft-deletion support allows for historical tracking without data loss

Customer action

Join entered_by_user_in_role_id and authorising_user_in_role_id with user_in_role_v2 for audit and authorization tracking
Use consultation_id to link recalls to specific consultations
Use diary_organisation_id to join with organisation reference data
Include is_deleted and transform_datetime in filtering and incremental-load rules

Removal of Redundant or Deprecated Fields

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

Why?

This change simplifies the core recall 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
`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`
`emis_authorising_userinrole_guid`	`authorising_user_in_role_id`	`user_in_role_v2`
`emis_enteredby_userinrole_guid`	`entered_by_user_in_role_id`	`user_in_role_v2`
`clinician_user_in_role_guid`	`authorising_user_in_role_id`	`user_in_role_v2`
`entered_by_userinrole_guid`	`entered_by_user_in_role_id`	`user_in_role_v2`
`sensitive_patient_flag`	`patient_id`	`patient_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`
`processing_id`		Always NULL as it is deprecated in v2
`deleted`		Replaced by `is_deleted` in v2
`data_filter`		Removed as it is deprecated in v2
`_record_version`		No longer available in v2
`_update_date`		Changes tracked differently in v2
`_update_hour`		Changes tracked differently in v2

Customer benefit

Improved Performance: A leaner recall 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 diaries for a specific patient

SELECT
        diary_id,
        emis_diary_guid,
        consultation_id,
        recorded_date,
        is_active_flag,
        is_complete_flag,
        organisation
FROM hive.explorer_ipcv_vanilla.recall_v2
WHERE emis_patient_id = 123456789
    AND is_deleted = FALSE;

Get diaries for a consultation

SELECT
        r.diary_id,
        r.emis_diary_guid,
        r.emis_patient_id,
        r.consultation_id,
        r.recorded_date,
        c.consultation_type_description,
        r.organisation
FROM hive.explorer_ipcv_vanilla.recall_v2 AS r
LEFT JOIN hive.explorer_ipcv_vanilla.encounter_v2 AS c
        ON r.consultation_id = c.consultation_id
        AND r.organisation = c.organisation
WHERE r.consultation_id = 987654321
    AND r.is_deleted = FALSE;

Get recently changed diary records

SELECT
        diary_id,
        emis_diary_guid,
        emis_patient_id,
        transform_datetime,
        is_deleted,
        organisation
FROM hive.explorer_ipcv_vanilla.recall_v2
WHERE transform_datetime >= TIMESTAMP '2026-02-01 00:00:00'
ORDER BY transform_datetime DESC;

Get confidential or sensitive diary records

SELECT
        diary_id,
        emis_diary_guid,
        emis_patient_id,
        consultation_id,
        confidential_flag,
        sensitive_flag,
        recorded_date,
        organisation
FROM hive.explorer_ipcv_vanilla.recall_v2
WHERE (confidential_flag = TRUE OR sensitive_flag = TRUE)
    AND is_deleted = FALSE;

Get diaries with patient opt-out filtering

To filter recall records based on patient opt-out status, join to the patient_v2 table:

SELECT
        r.diary_id,
        r.emis_diary_guid,
        r.emis_patient_id,
        r.consultation_id,
        r.recorded_date,
        r.is_active_flag,
        p.is_regular,
        p.is_opt_out_9nu0,
        p.is_dummy,
        r.organisation
FROM hive.explorer_ipcv_vanilla.recall_v2 AS r
LEFT JOIN hive.explorer_ipcv_vanilla.patient_v2 AS p
        ON r.emis_patient_id = p.emis_patient_id
        AND r.organisation = p.organisation
WHERE r.is_deleted = FALSE
    AND p.is_regular = TRUE
    AND COALESCE(p.is_opt_out_9nu0, FALSE) = FALSE;

Join to user_in_role and organisation

To access user and organisation details using the new ID-based joins:

SELECT
    recall.diary_id,
    recall.diary_organisation_id,
    recall.consultation_id,
    recall.entered_by_user_in_role_id,
    entered_by.emis_userinrole_guid AS entered_by_user_in_role_guid,
    recall.authorising_user_in_role_id,
    authorised_by.emis_userinrole_guid AS authorising_user_in_role_guid,
    recall.is_deleted,
    recall.transform_datetime,
    recall.organisation,
    consultation.emis_consultation_id,
    organisation.ods_code
FROM hive.explorer_ipcv_vanilla.recall_v2 AS recall
LEFT JOIN hive.explorer_ipcv_vanilla.encounter_v2 AS consultation
    ON recall.consultation_id = consultation.consultation_id
    AND recall.organisation = consultation.organisation
LEFT JOIN hive.explorer_ipcv_vanilla.organisation_v2 AS organisation
    ON recall.diary_organisation_id = organisation.organisation_id
    AND recall.organisation = organisation.organisation
LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 AS entered_by
    ON recall.entered_by_user_in_role_id = entered_by.user_in_role_id
    AND recall.organisation = entered_by.organisation
LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 AS authorised_by
    ON recall.authorising_user_in_role_id = authorised_by.user_in_role_id
    AND recall.organisation = authorised_by.organisation
WHERE recall.is_deleted = FALSE;