Definition

The Patient Registration History data model exposes historical registration type and status entries for patients in OpenSafely. It complements the Patient model, which presents the deduplicated current-registration view.

Use this model when analysis needs to understand previous registration states, such as when a patient was registered, had left, had died, or moved through a non-regular registration type. Historical rows are retained where they relate to patients in the OpenSafely patient cohort.

Constraints and Notes

• Soft deletion: When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and lineage fields are preserved so deleted records can still be identified.
• Patient identifiers are pseudonymised. patient_id, patient_history_id, and nhs_number are stable within the permitted OpenSafely context.
• OpenSafely includes England GP organisations with an active sharing agreement.
• The model keeps historical rows by linking through the stable pseudonymised NHS number, so history can be retained across practice transfers.
• Patient merges are not resolved in this model. Sacrificed patient IDs can still appear where they exist in historical source data.
• Archived patients are retained in the source data. Archiving hides the patient for most purposes in EMIS Web but does not delete the underlying history.

Patient Registration Status

The is_registered flag is derived from both patient type and registration status. OpenSafely exposes the registration type and status descriptions for interpreting registration history.

• For regular patients, registered history covers the GP registration process from notification of registration through receipt of the medical record.
• For non-regular patients, registered history is represented by the correctly registered status.

The descriptions most commonly used to identify registered history are:

Patient Type	Status Description
Regular	Notification of registration
Regular	Medical record sent
Regular	Record Received
Non-regular	Correctly registered

Examples

Review a patient’s registration history

SELECT
  patient_history_id,
  patient_id,
  patient_status_description,
  patient_type_description,
  is_registered,
  has_left,
  has_died,
  recorded_datetime
FROM explorer_open_safely.patient_registration_history
WHERE patient_id = <patient_id>
ORDER BY recorded_datetime DESC;

Count registered history entries by year

SELECT
  recorded_year,
  patient_type_description,
  COUNT(*) AS history_rows
FROM explorer_open_safely.patient_registration_history
WHERE is_registered = TRUE
GROUP BY
  recorded_year,
  patient_type_description
ORDER BY recorded_year DESC;

Find patients with left or deceased history entries

SELECT
  patient_id,
  patient_status_description,
  patient_type_description,
  has_left,
  has_died,
  recorded_date
FROM explorer_open_safely.patient_registration_history
WHERE has_left = TRUE
   OR has_died = TRUE
ORDER BY recorded_date DESC;