Schema

Schema Overview

Column Name	Data Type	Description	Example
is_deleted	boolean	If this record should be considered soft deleted; all columns excluding primary keys will be NULL if TRUE	false
referral_observation_id	varbinary	Unique identifier for the referral observation record	F422B34E0AC397B6A8C9D0E1F2345678
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
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
patient_id	varbinary	Unique patient identifier	F422B34E0AC397B6A8C9D0E1F2345678
consultation_id	varbinary	Unique identifier for the linked consultation for this observation entry	F422B34E0AC397B6A8C9D0E1F2345678
consultation_source_code_id	bigint	EMIS code ID representing the consultation purpose; used for categorisation	1672871000006114
code_id	bigint	EMIS code ID - internal identifier	348898019
effective_datetime	timestamp(6) with time zone	Date and time when the observation became clinically effective	2023-05-12 14:30:15.000000 UTC
referral_made_datetime	timestamp(6) with time zone	Date that the referral was made	2023-05-12 00:00:00.000000 UTC
ended_datetime	timestamp(6) with time zone	Date that the referral was ended, e.g. when the patient was seen or the referral was cancelled	2023-05-12 00:00:00.000000 UTC
direction	varchar	The direction of the referral (in or out)	in
urgency	bigint	The urgency of the referral (0, 1, 2, 3, 4 or NULL)	0
urgency_description	varchar	The human readable description of the urgency (Routine, Soon, Urgent, Dated or 2 Week Wait)	Routine
referral_mode_description	varchar	The description for how the referral was made (e.g. Telephone, NHS e-Referral, Verbal, Written)	Written
service_type_id	bigint	EMIS code ID representing the service type, e.g. 1775591000006112 - Referred to School Nursing Service	1775301000006110

Example Query

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

SELECT *
FROM explorer_open_safely.referral
LIMIT 100;