File Structure
Each query will be saved as a CSV file
The CSV file will be named like coded_entry.csv
The CSV is comma-separated, uses " for enclosure and \ for escaping and uses the end-of-line character \n
The first row of the CSV file will contain column headers based on the field names selected in the query
Each CSV file will then be added to a ZIP file - we support any reasonable naming conventions for the ZIP file (e.g. {odsCode}}-medicus-test-extract-{{yyyy-mm-dd}}.zip)
“Delta” Extracts
“Delta” extracts are popular as they help to minimise the amount of data transfer and processing needed to keep the “target system” synchronised.
Delta Specific Columns
The initial extract will be a "full" data extract. This means there is no column called change_type in the CSV.
Subsequent extracts will be a "delta" data extract. This means that Medicus calculates the changes since the previous daily extract and only outputs those changes.
The values for change_type are:
added
updated
removed
If the query contains a uid column this will be used as a unique identifier to determine if the row is added, updated or removed. If there is no uid column, Medicus calculates if added or removed based on all of the column values.
If there are no changes for a given reporting query, Medicus will still add a CSV file with the column headers, but there will be no data in the CSV file.
Calculation of Delta Extracts
The “safest” and “simplest” way to calculate a “delta” extract is for Medicus to perform a “full” extract again, retrieve the previous day’s “full” extract (not the previous day’s “delta”), compare the rows and then for any row added, updated or deleted, indicate this in the delta file.
Medicus stores the last “full” data extract in order to be able to calculate the next “delta” extract. (it then store the next “full” data extract, and delete the previous “full” data extract).
The primary key for each row is a unique record identifier (UUID).
If the definition of a data extract changes, then Medicus needs to start with a fresh Full Initial Extract.
Tables
Patient
Filename: YGMYW-patient.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
patient_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A unique identifier for the patient. |
nhs_number |
String - no spaces |
The patient’s NHS number. |
guernsey_social_insurance_number |
String - no spaces |
Commonly referred to as “GY Number”. |
date_of_birth |
YYYY-MM-DD |
The patient’s date of birth. |
official_name_prefix |
String |
The patient’s prefix e.g. Mr |
official_given_name |
String |
The patient’s first name. |
official_family_name |
String |
The patient’s surname. |
preferred_given_name |
String |
The patient’s preferred first name. |
clinical_sex |
String Possible values: male , female, unknown |
Derived - sex at birth, otherwise gender identity. |
named_gp_name |
String |
The name of the patient’s named GP. |
named_gp_identifier_type |
String Possible values: medicus-staff-id , medicus-referral-provider-practitioner-id , gb-nhs-gp-connect-practitioner-id , gb-gmc-number , gb-gphc-number , gb-hcpc-number , gb-nmc-pin , sds-user-id |
The type of identifier provided for the patient’s named GP (most common will be GMC number). |
named_gp_identifier_value |
String |
The patient’s named GP identifier. |
communication_email_address |
String |
The patient’s email address for communicating with them. |
communication_home_phone_number |
String |
The patient’s home phone number for communicating with them. |
communication_mobile_phone_number |
String |
The patient’s mobile phone number for communicating with them. |
home_address_line_1 |
String |
The patient’s home address. |
home_address_line_2 |
String | |
home_address_line_3 |
String | |
home_address_locality |
String | |
home_address_administrative_area |
String | |
home_address_postal_code |
String | |
home_address_country |
String | |
deceased |
Boolean: true , false |
A boolean flag to indicate whether the patient is deceased or not. |
date_of_death |
YYYY-MM-DD |
If the patient is deceased, their date of death. |
Patient Registration
Filename: YGMYW-patient-registration.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A unique identifier for the patient registration record. |
patient_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
Foreign key for the patient that this relates to. |
start_date |
YYYY-MM-DD |
|
end_date |
YYYY-MM-DD |
|
registration_type |
String - e.g. immediate-necessary-treatment Note: Can be customised by the organisation. |
Default:
|
prescriptions_dispensed_by_practice |
Boolean: true , false |
A boolean flag to indicate whether the patient has medication dispensed by the practice or not. |
care_home_resident |
Boolean: true , false |
A boolean flag to indicate whether the patient is a care home resident or not. [Note - this information may also be stored as coded date in addition to their registration information] |
Coded Entry
Filename: YGMYW-coded-entry.csv [where YGMYW is the ODS code of the organisation]
Note on Blood Pressures: The only known use case for multiple values is blood pressures.
Therefore, consumers need to expect and be able to handle blood pressures that are stored as a combined systolic and diastolic element.
The most common codes will be:
75367002 | Blood pressure (observable entity) (including descendents)
163020007 | On examination - blood pressure reading (finding)
Unit of measure will always be 259018001 | Millimetre of Mercury (mmHg)
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
patient_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A unique identifier for the patient. |
entity_type |
String |
One of:
Generally not used but sometimes useful to apply specific filter queries e.g. medications vs allergies. Caution about simplistic queries that assume coded data will always be in neat buckets (this is historically not true in all clinical systems). |
code |
String |
The SNOMED CT concept ID for the record. |
code_description |
String |
The SNOMED CT description. |
clinically_relevant_date |
YYYY-MM-DD |
The record entry date (e.g. observation date). |
created_date_time |
YYYY-MM-DD HH:MM:SS |
Created in original system date time. |
encounter_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
Useful for de-duplicating codes in the same encounter e.g. for the purpose of activity counts. |
encounter_type_code |
String |
The type of encounter. Most commonly one of:
But this ia a SNOMED CT property and could have other values that were imported from other systems. |
encounter_type_description |
String |
|
value_1 |
Number (incl. decimals) 120 80.5 |
An optional value used for numeric record entries e.g. Height. |
value_1_unit_code |
String |
SNOMED CT concept id. |
value_1_unit_description |
String |
|
value_2 |
Number (incl. decimals) 120 80.5 |
[Only populated for blood pressures] |
value_2_unit_code |
String |
SNOMED CT concept ID. |
value_2_unit_description |
String |
|
body_site_code |
String |
An optional property to store the SNOMED CT concept ID of the body site e.g. in the case of an immunisation or procedure. |
body_site_description |
String |
The SNOMED CT description. |
route_code |
String |
An optional property to store the SNOMED CT concept ID of the route e.g. in the case of an immunisation this might be 255560000 (Intravenous). |
route_description |
String |
The SNOMED CT description. |
batch_number |
String |
Usually only populated for immunisations. |
expiry_date |
YYYY-MM-DD |
Usually only populated for immunisations. |
status |
String |
Active | Ended Only populated for certain records e.g. allergies. |
end_date |
YYYY-MM-DD |
Only populated for certain records e.g. allergies. |
practitioner_name |
String |
The name of the responsible practitioner for the record (e.g. the person who performed the procedure) |
practitioner_identifier_type |
String Possible values: medicus-staff-id , medicus-referral-provider-practitioner-id , gb-nhs-gp-connect-practitioner-id , gb-gmc-number , gb-gphc-number , gb-hcpc-number , gb-nmc-pin , sds-user-id |
The type of identifier provided for the responsible practitioner. |
practitioner_identifier_value |
String |
The responsible practitioner identifier. |
organisation_name |
String |
The name of the responsible organisation for the record (e.g. the organisation that performed the procedure) |
organisation_identifier_type |
String Possible values: medicus-tenant-id, nhs-england-ods-code, third-party-system-organisation-id, unknown-identifier-type |
The type of identifier provided for the responsible organisation. |
organisation_identifier_value |
String |
The responsible organisation identifier. |
performed_at_local_organisation |
Boolean: true , false |
A boolean property to indicate whether the activity was performed at the local organisation or not. This is used for some payment related extracts to filter out activity done at another healthcare provider. |
Prescription
Filename: YGMYW-prescription.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
patient_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A unique identifier for the patient. |
type |
String Possible values: acute , repeat-prescribing , repeat-dispensing , variable-repeat |
|
status |
String Possible values: authorised , reauthorised , discontinued , requires-reauthorisation |
|
product_code |
String |
The SNOMED CT / dm+d concept ID for the drug product. |
product_description |
String |
The SNOMED CT description. |
dosage_instruction |
String |
Free text dosage instructions for the prescription. |
start_date |
YYYY-MM-DD |
The date that the prescription is authorised to start from. |
expected_end_date |
YYYY-MM-DD |
A derived property that estimates when the supply ends. |
authorised_date |
YYYY-MM-DD |
The date the prescription was authorised on. |
authorised_by_practitioner_name |
String |
The name of the responsible practitioner for the record (e.g. the person who performed the procedure) |
authorised_by_practitioner_identifier_type |
String Possible values: medicus-staff-id , medicus-referral-provider-practitioner-id , gb-nhs-gp-connect-practitioner-id , gb-gmc-number , gb-gphc-number , gb-hcpc-number , gb-nmc-pin , sds-user-id |
The type of identifier provided for the responsible practitioner. |
authorised_by_practitioner_identifier_value |
String |
The responsible practitioner identifier. |
last_issue_date |
YYYY-MM-DD |
The date the prescription was last issued. |
discontinued_date |
YYYY-MM-DD |
If discontinued, the date that the prescription was discontinued/ended. |
Prescription Issue
Filename: YGMYW-prescription-issue.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
prescription_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A unique identifier for the parent prescription. |
issue_number |
Integer e.g 3 |
The issue number in the total authorised sequence. E.g 3 of 12. |
expected_days_supply |
Integer e.g. 28 |
The number of days the supply is expected to last the patient. |
quantity_value |
Number (incl. decimals) 28 12.5 |
The value element of the quantity issued to the patient. |
quantity_unit_code |
String e.g. 428673006 |
The SNOMED CT concept ID for quantiy unit of measure. |
quantity_unit_description |
String e.g. tablet |
The SNOMED CT description. |
issue_status |
String Possible values: not-issued , issued |
The status of the individual prescription issue. |
issue_date |
YYYY-MM-DD |
The date that the prescription issue was “issued” to the patient. |
issue_method |
String Possible values: electronic-prescription-service , fp10ss-printed-script , handwritten-script , private-handwritten-script , private-printed-script , fp10-personally-administered , gg-ps2016-printed-script , gg-private-printed-script , unknown |
The method used to issue the prescription issue. |
Appointment
Filename: YGMYW-appointment.csv [where YGMYW is the ODS code of the organisation]
Note: Medicus does not currently provide “slots” to consumers. They are not a persisted entity but instead virtual/derived on the fly.
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
patient_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A unique identifier for the patient. |
status |
String Possible values: pending , started , seen , rescheduled , cancelled , did-not-attend |
The status of the appointment. Before anything has happened the status will be “pending”. |
appointment_service |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A practice defined appointment service. |
appointment_type |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
A practice defined appointment type. |
nhs_national_slot_type_service_setting |
String |
The service setting used in GPAD reporting. E.g. “General Practice“ https://www.england.nhs.uk/publication/gpad-appointment-categorisation-guidance-2021-22/ |
nhs_national_slot_type_context_type |
String |
The context type used in GPAD reporting. E.g. “Care related encounter“ https://www.england.nhs.uk/publication/gpad-appointment-categorisation-guidance-2021-22/ |
nhs_national_slot_type_category |
String |
The category used in GPAD reporting. E.g. “General Consultation Acute” https://www.england.nhs.uk/publication/gpad-appointment-categorisation-guidance-2021-22/ |
intended_start_date_time |
YYYY-MM-DD |
The date & time the appointment is planned to start. |
intended_end_date_time |
YYYY-MM-DD |
The date & time the appointment is planned to end. |
intended_duration_minutes |
Number (normally integer but could be decimal). |
The planned duration of the appointment in minutes. |
delivery_mode |
String Possible values: face-to-face , home-visit , video , phone |
The mode of appointment. |
site |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
|
room |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
|
linked_encounter |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
|
arrived_date_time |
YYYY-MM-DD |
The date & time the patient was marked as having arrived. |
booked_date_time |
YYYY-MM-DD |
The date & time the appointment was booked. |
cancelled_date_time |
YYYY-MM-DD |
The date & time the appointment was cancelled. |
rescheduled_date_time |
YYYY-MM-DD |
The date & time the appointment was rescheduled. |
Appointment Practitioner
Filename: YGMYW-appointment-practitioner.csv [where YGMYW is the ODS code of the organisation]
Appointments can have 0 or many practitioners.
Note: Practitioner references represent the name etc. of the practitioner at the time the appointment was booked and could also represent historical practitioners (e.g. staff members who do not necessarily have a user account in Medicus).
Column |
Format |
Description |
appointment_id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
The ID of the appointment. |
practitioner_name |
String |
The name of the practitioner. |
practitioner_identifier_type |
String Possible values: medicus-staff-id , medicus-referral-provider-practitioner-id , gb-nhs-gp-connect-practitioner-id , gb-gmc-number , gb-gphc-number , gb-hcpc-number , gb-nmc-pin , sds-user-id |
The type of identifier provided for the practitioner. |
practitioner_identifier_value |
String |
The practitioner’s identifier. |
Appointment Service
Filename: YGMYW-appointment-service.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
name |
String |
The name of the service. |
Appointment Type
Filename: YGMYW-appointment-type.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
name |
String |
The name of the appointment type. |
Site
Filename: YGMYW-site.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
name |
String |
The name of the site. |
Room
Filename: YGMYW-room.csv [where YGMYW is the ODS code of the organisation]
Column |
Format |
Description |
id |
UUID e.g. ac552fad-8100-4044-90b9-e39930ac7d85 |
a unique ID for the record. |
name |
String |
The name of the room. |