Definition

The Problem model represents data that are clinically important observation promoted to the problem list for ongoing tracking of the patient’s health history, sharing the same core observation details and enriched with problem-specific lifecycle and relationship fields. This allows to track active/inactive conditions over time while preserving linkage to the original clinical event.

A Problem can have a parent/child relationship with another problem.

Information

The Problem data model provides a view of all problems associated with a patient. Problems are derived from the observation when flagged as problems in a patient’s record and are linked to the consultation in which they were recorded.

Problem extends an Observation event; the core observation could be in one of Observation, Allergy or Referral models. A GP can mark an observation as no longer a problem after further analysis.

This model contains the following key information for problems:

• Record and patient identifiers

• Linking fields to consultation, observation and user

• Clinical coding and terms

• Problem and system dates

• Problem hierarchy, significance, episodicity and status

• Confidentiality and sensitivity information

Grain and Scope

Each row is a unique problem record and each record is uniquely identified by either combining observation_id and organisation or the observation_uuid column.

This model includes the following key identifiers:

• observation_id: The unique internal identifier for the observation record that is set as a problem within an organisation.

• observation_guid: The GUID for the problem record within an organisation.

• observation_uuid: The UUID derived from observation_id and organisation, providing a stable unique identifier.

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

• is_deleted: Indicates whether the problem record has been deleted at source or transitioned from being classified as a problem.

• 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 --> prob1["Problem: Hypertension"]
  patA --> prob2["Problem: Diabetes"]
  patB --> prob3["Problem: Asthma"]
  patC --> prob4["Problem: COPD"]

  prob1 --> gather["Gather Problem Data"]
  prob2 --> gather
  prob3 --> gather
  prob4 --> gather
  gather -->|Unique IDs: id + organisation| etl["ETL"]
  etl -->final["Final Consolidated Data"]

  classDef nodeStyle stroke:#9961a4;
  class org1,patA,patB,org2,patC,prob1,prob2,prob3,prob4,gather,etl,final nodeStyle;

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

Examples

Retrieve all active problems for a patient

SELECT
  emis_observation_guid,
  emis_patient_id,
  emis_original_term,
  effective_date,
  problem_status_description,
  emis_problem_significance
FROM
  hive.explorer_ipcv_vanilla.problem_v2
WHERE
  patient_uuid = 'eb97f15f-268e-545e-ba64-7168dd5b08c0'
  AND problem_status_description = 'Active Problem'
ORDER BY
  effective_date DESC;

Find problems linked to a specific consultation

SELECT
  emis_observation_guid,
  emis_original_term,
  effective_date,
  problem_status_description
FROM
  hive.explorer_ipcv_vanilla.problem_v2
WHERE
  emis_encounter_guid = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND NOT is_deleted;