Definition

Purpose

The allergy model extracts allergy-related data for patients included in specific recruit studies. It captures coded allergy entries recorded by clinicians in EMIS Web, including associated clinical codes and consultation context with personally identifiable information preserved.

Information

This patient-linked model provides a comprehensive record of patient allergy information derived from observation data. Each allergy record is uniquely identified by a combination of emis_observation_guid and organisation, ensuring a distinct entry for every allergy within a specific healthcare organisation.

• emis_observation_guid (PK): The unique identifier for the allergy observation record
• emis_registration_guid (FK): Hashed registration identifier - links to patient registration
• patient_id (FK): Patient identifier with PII preserved
• emis_registration_organisation_guid (FK): Organisation the registration belongs to
• emis_authorising_userinrole_guid (FK): Clinician who authorised the allergy record
• emis_consultation_guid (FK): The consultation during which the allergy was recorded
• emis_code_id (FK): The clinical code for the allergy
• study_id (FK): Study cohort identifier

Examples

Get all allergies for a patient

SELECT *
FROM hive.explorer_recruit.allergy
WHERE patient_id = 123456789
LIMIT 100;

Find confidential or sensitive allergies

SELECT *
FROM hive.explorer_recruit.allergy
WHERE confidential_flag = TRUE
   OR fhir_status = 'sensitive'
LIMIT 100;

Get allergies with SNOMED mapping for interoperability

SELECT
  a.emis_observation_guid AS observation_id,
  a.emis_code_id AS code_id,
  cc.snomed_concept_id,
  cc.snomed_description_id
FROM hive.explorer_recruit.allergy a
LEFT JOIN hive.explorer_recruit.codeable_concept cc
  ON a.emis_code_id = cc.emis_code_id
  AND a.organisation = cc.organisation
WHERE cc.snomed_concept_id IS NOT NULL
LIMIT 100;