Definition

Purpose

The patient model provides current patient demographic records for patients included in recruit studies from the anon extract. The view is scoped to fully anonymised patients via the included patients view. Sensitive contact details are replaced with boolean presence flags.

Information

The Patient model provides current patient demographic records for patients included in recruit studies. It captures the key identifiers and foreign key relationships to organisations, user roles, and related entities. All data is fully anonymised through the recruit included patients view.

Each patient record is uniquely identified by pseudo_registration_guid within a specific healthcare organisation.

• pseudo_registration_guid (PK): Hashed registration identifier - unique within an organisation
• pseudo_patient_id (FK): Hashed patient identifier
• pseudo_nhs_no (FK): Hashed NHS number
• pseudo_person_guid (FK): Hashed person GUID
• legacy_pseudo_person_guid (FK): Hashed legacy person GUID
• emis_registration_organisation_guid (FK): Links to organisation
• emis_usualgp_userinrole_guid (FK): Links to user_in_role for usual GP
• ethnic_category_snomed_concept_id (FK): Links to snomed_concept
• address_guid (FK): Links to address entity
• study_id (FK): Links to user_studies

Examples

Get patient records at an organisation (patient_id and nhs_no are hashed)

SELECT *
FROM hive.explorer_recruit_anon.patient
WHERE organisation = 'CDB-0001'
LIMIT 100;

Find patients registered for a study

SELECT
  registration_guid,
  patient_id,
  gender,
  date_of_birth,
  study_id
FROM hive.explorer_recruit_anon.patient
WHERE study_id = 'STUDY001'
LIMIT 100;

Find patients with sensitive or confidential flags

SELECT
  registration_guid,
  patient_id,
  sensitive_patient_flag,
  confidential_patient_flag
FROM hive.explorer_recruit_anon.patient
WHERE sensitive_patient_flag = TRUE
   OR confidential_patient_flag = TRUE
LIMIT 100;