Schema

Schema Overview

Column Name	Data Type	Description	Example
patient_history_id	varbinary	Pseudonymised unique identifier for the patient history record	F422B34E0AC397B6A8C9D0E1F2345678
organisation	varchar	The unique EXA identifier for the data source organisation	CDB-1234
load_datetime	timestamp(6) with time zone	Date and time that the record was last upserted into the EXA data lake from EMIS Web with a relevant source change	2023-05-12 14:30:15.000000 UTC
is_deleted	boolean	If this record should be considered soft deleted; data fields excluding primary keys and lineage fields will be NULL if TRUE	false
transform_datetime	timestamp(6) with time zone	Date and time the data was made available in the model with a relevant change	2023-05-12 14:30:15.000000 UTC
patient_id	varbinary	Pseudonymised unique patient identifier	F422B34E0AC397B6A8C9D0E1F2345678
nhs_number	varbinary	Pseudonymised National Health Service number. This is stable across organisation transfers and is used to retain history for deduplicated patients	F422B34E0AC397B6A8C9D0E1F2345678
is_valid_hc_number	boolean	Indicates whether the patient’s Health and Care number is valid where present	true
has_died	boolean	Indicates whether the patient has died. True when the patient is recorded as deceased, has a recorded date of death, or their caseload patient status is ‘Dead’	false
has_left	boolean	Indicates whether the patient has left the organisation. True when the patient is not deceased, has no recorded date of death, and their caseload patient status is ‘Left’	false
patient_status_description	varchar	Description of the patient’s status in the registration cycle	Record Received
is_registered	boolean	Indicates whether the history row is considered fully registered. Regular patients are registered through the GP registration lifecycle; non-regular patients are registered when correctly registered	true
patient_type_description	varchar	Description of the patient type, such as Regular, Temporary, Emergency, Immediately Necessary, Private, or Dummy	Regular
recorded_datetime	timestamp(6) with time zone	Date and time that the registration history entry was recorded in EMIS Web	2023-05-12 10:15:30.000000 UTC
recorded_date	date	Date that the registration history entry was recorded in EMIS Web	2023-05-12
recorded_year	bigint	Year that the registration history entry was recorded in EMIS Web	2023
model_updated_datetime	timestamp(6) with time zone	Date and time the model last updated this record	2023-05-12 14:30:15.000000 UTC

Registered Status Values

Use is_registered for filtering registered history rows. OpenSafely exposes the registration status and patient type descriptions that customers can use to understand which registration entries are included.

Patient Type	Status Description	Description
Regular	Notification of registration	The organisation has received notification that the patient is registering
Regular	Medical record sent	The patient’s medical record has been sent
Regular	Record Received	The patient’s medical record has been received
Non-regular	Correctly registered	The patient is correctly registered for the relevant non-regular context

Example Query

The following query filters to the registered status descriptions exposed by the model:

SELECT
  patient_id,
  patient_status_description,
  patient_type_description,
  recorded_datetime
FROM explorer_open_safely.patient_registration_history
WHERE patient_status_description IN (
  'Notification of registration',
  'Medical record sent',
  'Record Received',
  'Correctly registered'
)
ORDER BY recorded_datetime DESC;

Use this query to preview recent rows and validate expected columns:

SELECT *
FROM explorer_open_safely.patient_registration_history
ORDER BY recorded_datetime DESC
LIMIT 100;