Definition

The Referral data model represents clinical referral events recorded in the source, covering both outbound referrals (from a practice to an external service) and inbound referrals (from an external service to a practice). Referrals can be accepted or rejected by the receiving organisation and are tracked through their lifecycle as they are managed across organisations.

Care record observations classified as referrals are surfaced exclusively in this model and are not included in the Observation model.

Referrals can be linked to a problem as a patient can be referred to other services for a new or ongoing problem through a consultation with their GP practice.

Information

The Referral data model provides a view of all referrals associated with a patient. Referral records are derived from observation data as well as referral specific source tables.

This model contains the following key information for referrals:

• Record and patient identifiers

• Linking fields to consultation and problem

• Clinical coding and terms

• Referral and system dates

• Referral classification about urgency, service type etc.

• Confidentiality and sensitivity information

Grain and Scope

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

This model includes the following key identifiers:

• referral_observation_id: The unique internal identifier for the referral record within an organisation.

• referral_observation_guid: The GUID for the referral observation within an organisation.

• referral_observation_uuid: The UUID derived from referral_observation_id and organisation, providing a stable unique identifier.

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

• is_deleted: Indicates whether referral 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
  org1["Organisation 1"] --> patA["Patient A"]
  org1 --> patB["Patient B"]
  org2["Organisation 2"] --> patC["Patient C"]

  patA --> ref1["Referral: Cardiology (Urgent)"]
  patA --> ref2["Referral: Physiotherapy (Routine)"]
  patB --> ref3["Referral: Ophthalmology (Soon)"]
  patC --> ref4["Referral: Respiratory (Urgent)"]

  ref1 --> gather["Gather Referral Data"]
  ref2 --> gather
  ref3 --> gather
  ref4 --> gather
  gather -->|Unique IDs: referral_observation_id + organisation| etl["ETL"]
  etl --> final["Final Consolidated Data"]

  classDef nodeStyle stroke:#9961a4;
  class org1,patA,patB,org2,patC,ref1,ref2,ref3,ref4,gather,etl,final nodeStyle;

  linkStyle default stroke:#117abf,fill:none;

Examples

Retrieve all active referrals for a patient

SELECT
  referral_observation_id,
  emis_patient_id,
  emis_original_term,
  effective_date,
  emis_urgency_description,
  emis_direction_description,
  service_type_requested_description,
  is_deleted
FROM
  hive.explorer_ipcv_vanilla.referral_v2
WHERE
  patient_uuid = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND is_deleted = FALSE
ORDER BY
  effective_date DESC;

Find referrals linked to a specific consultation

SELECT
  referral_observation_id,
  emis_original_term,
  effective_date,
  emis_urgency_description,
  emis_direction_description,
  service_type_requested_description
FROM
  hive.explorer_ipcv_vanilla.referral_v2
WHERE
  emis_encounter_guid = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND is_deleted = FALSE;