Definition

The Diary data model represents scheduled diary entries and reminders recorded for patients, for example, as reminders for a course of vaccinations or an annual health check for chronic conditions.

There are two types of diary schedule:

Clinical - These schedules look for clinical code(s) on the records for patients within a specified age range. A good example of this is for patients who have a chronic condition coded on their record like a diary entry may be created for Asthma patients aged 60–70. A clinical diary schedule will add a trigger code for a review of the condition(s) on to the patient record. A diary entry is added for the next review.

Registration - These schedules apply to all newly registered patients that meet the gender and age range defined in the diary schedule. For example, for vaccination clinics, an age or age range can be defined to cover childhood immunisations, flu vaccinations or vaccinations given to only one gender (e.g. the HPV vaccine to girls from age 12).

Diary entries can represent consultations, appointments, reviews, or other scheduled follow-ups. Diary records include structured details on the consultation, patient, clinical code, and users who entered and authorised the entry.

Information

The Diary data model provides a structured representation of patient diary information derived from primary care systems. It captures reminder and follow-up items recorded by clinicians, including clinical code, timing, and consultation context. This model supports understanding planned follow-up actions and tracking operational diary activity across healthcare services.

This model contains the following key information for diary:

Record and patient identifiers
Linking fields to consultation and user
Clinical coding and terms
Dairy entry and system dates
Confidentiality and sensitivity information

Grain and Scope

Each row is a unique referral record and each record is uniquely identified by either combining diary_id and organisation or the diary_uuid column.

This model includes the following key identifiers:

diary_id: The unique internal identifier for the diary record within an organisation.
diary_guid: The GUID for the diary record within an organisation.
diary_uuid: The UUID derived from diary_id and organisation, providing a stable unique identifier.

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

is_deleted: Indicates whether diary record has been deleted at source.
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 and updated in the data model. This field is crucial for understanding the current state of the data.

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

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
  patient_uuid = 'eb97f15f-268e-545e-ba64-7168dd5b08c0'
  AND NOT is_deleted;

Get diaries for a consultation

SELECT
  r.diary_id,
  r.emis_diary_guid,
  r.emis_patient_id,
  r.consultation_id,
  r.recorded_date,
  c.emis_original_term,
  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.emis_consultation_id
  AND r.organisation = c.organisation
WHERE
  r.exa_encounter_guid = 'eb97f28f-268e-545e-ba64-7168dd5b08c0'
  AND NOT r.is_deleted;

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
  NOT is_deleted
  AND (
    confidential_flag
    OR sensitive_flag
  );

Get diaries with patient opt-out filtering

If any filtering is applicable to any flavour, the diary model will filter out those patients that are not in scope. But for flavours that doesn’t have any filtering applied or want to filter on any other options available, join to the patient_v2 model as below:

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_dissent_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
  NOT r.is_deleted
  AND p.is_regular
  AND NOT p.is_dissent_9nu0;

Join to consultation, user_in_role and organisation

To access consultation, 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_encounter_guid,
  org.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.emis_consultation_id
  AND recall.organisation = consultation.organisation
  LEFT JOIN hive.explorer_ipcv_vanilla.organisation_v2 AS org ON recall.diary_organisation_id = org.emis_organisation_id
  AND recall.organisation = org.organisation
  LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 AS entered_by ON recall.entered_by_user_in_role_id = entered_by.emis_user_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.emis_user_id
  AND recall.organisation = authorised_by.organisation
WHERE
  NOT recall.is_deleted;