Schema

Core

The core schema represents the Patient Registration History model schema. It is derived from EMIS Web patient history records and enriched with patient type, patient status, current patient identifiers, and lifecycle flags.

Please note that how much of this model is visible varies between flavours.

In the event of a record deletion, patient-identifiable and descriptive columns will be nulled in customer-facing outputs, while key and lineage fields are retained to support deltas and troubleshooting.

Column Name	Data Type	Description	Example / Values	Value Retained
patient_history_id	bigint	The unique internal identifier for the patient history record within an organisation	987654	✓
patient_history_uuid	varchar	The UUID derived from `patient_history_id` and `organisation`, providing a stable unique identifier	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’	✓
organisation	varchar	The unique EXA identifier for the data source organisation	‘CDB-1234’	✓
load_datetime	timestamp(6) with time zone	The datetime that the record was last upserted into the EXA data lake from EMIS source system with a relevant change from source system	‘2023-05-12 14:30:15+00’	✓
is_deleted	boolean	Indicates whether the patient history record has been marked as deleted in the source system	FALSE	✓
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model	‘2023-05-12 14:30:15+00’	✓
patient_id	bigint	The unique internal identifier for the patient record within an organisation	123456	✓
patient_guid	varchar	The unique GUID for the patient record within an organisation	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’	✓
patient_uuid	varchar	The UUID derived from `patient_id` and `organisation`, providing a stable unique identifier	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’	✓
person_guid	varchar	The globally unique identifier across EMIS organisations for the person associated with this patient record	‘b2c3d4e5-f6a7-5b6c-0d1e-2f3a4b5c6d7e’
nhs_number	varchar	The patient’s NHS number, selected from their health service numbers	‘9435797881’
chi_number	varchar	The patient’s Community Health Index number, selected from their health service numbers	‘0101201044’
hc_number	varchar	The patient’s Health and Care number, selected from their health service numbers	‘1234567890’
hospital_number	varchar	The patient’s hospital number, selected from their health service numbers	‘HOSP345678’
ssd_number	varchar	The patient’s Social Services Department number, selected from their health service numbers	‘SSD901234’
gha_number	varchar	The patient’s Gibraltar Health Authority number, selected from their health service numbers	‘GHA567890’
is_valid_nhs_number	boolean	Indicates whether the patient’s NHS number is valid	TRUE
is_valid_chi_number	boolean	Indicates whether the patient’s Community Health Index number is valid	TRUE
is_valid_hc_number	boolean	Indicates whether the patient’s Health and Care number is valid	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_id	bigint	The unique identifier for the patient’s status during the registration cycle	6
patient_status_description	varchar	The description of the patient’s status in the registration cycle	‘Record Received’
caseload_patient_status_id	bigint	The administrative caseload status linked to the patient status: Registered (1), Left (2), or Died (3)	1
is_registered	boolean	Indicates whether the history row is considered fully registered: * For regular patients (patient type 4), true when the registration status is between 4 and 7, covering notification of registration through receipt of the medical record. * For all other patient types, true only when registration status is 8.	TRUE
patient_type_id	bigint	The unique identifier for the type of patient this history row relates to	4
patient_type_description	varchar	The description of the patient type, such as Regular, Temporary, Emergency, Immediately Necessary, Private, or Dummy	‘Regular’
recorded_datetime	timestamp(6) with time zone	The date and time that the registration history entry was recorded in EMIS Web	‘2023-05-12 10:15:30+00’
recorded_date	date	The date that the registration history entry was recorded in EMIS Web	‘2023-05-12’
recorded_year	bigint	The year that the registration history entry was recorded in EMIS Web	2023
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This is a legacy column provided only for backward compatibility	‘20230512143015’	✓

Vanilla

The table below presents the expected flavour specific schema for the Vanilla V2 model and maps columns to the core model.

Column Name	Data Type	Core Mapping
patient_history_id	bigint	patient_history_id
patient_history_uuid	varchar	patient_history_uuid
organisation	varchar	organisation
load_datetime	timestamp(6) with time zone	load_datetime
is_deleted	boolean	is_deleted
transform_datetime	timestamp(6) with time zone	transform_datetime
emis_patient_id	bigint	patient_id
registration_guid	varchar	patient_guid
patient_uuid	varchar	patient_uuid
person_guid	varchar	person_guid
nhs_no	varchar	nhs_number
chi_number	varchar	chi_number
hc_number	varchar	hc_number
hospital_number	varchar	hospital_number
ssd_number	varchar	ssd_number
gha_no	varchar	gha_number
is_valid_nhs_number	boolean	is_valid_nhs_number
is_valid_chi_number	boolean	is_valid_chi_number
is_valid_hc_number	boolean	is_valid_hc_number
has_died	boolean	has_died
has_left	boolean	has_left
emis_registration_status_id	bigint	patient_status_id
registration_status_description	varchar	patient_status_description
caseload_patient_status_id	bigint	caseload_patient_status_id
is_registered	boolean	is_registered
emis_registration_type_id	bigint	patient_type_id
registration_type_description	varchar	patient_type_description
recorded_datetime	timestamp(6) with time zone	recorded_datetime
recorded_date	date	recorded_date
recorded_year	bigint	recorded_year
_execution_date	varchar	_execution_date

Example Query

The following query uses the customer-facing status descriptions that normally represent registered rows:

SELECT
  emis_patient_id,
  registration_status_description,
  registration_type_description,
  recorded_datetime,
  organisation
FROM hive.explorer_ipcv_vanilla.patient_registration_history
WHERE registration_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 hive.explorer_ipcv_vanilla.patient_registration_history
ORDER BY recorded_datetime DESC
LIMIT 100;