Skip to content

raw.tpp schema🔗

Available on backends: TPP

This schema defines the data (both primary care and externally linked) available in the OpenSAFELY-TPP backend. For more information about this backend, see "SystmOne Primary Care".

The data provided by this schema are minimally transformed. They are very close to the data provided by the underlying database tables. They are provided for data development and data curation purposes.

To use this schema in an ehrQL file:
from ehrql.tables.raw.tpp import (
    apcs_cost_historical,
    apcs_historical,
    isaric,
    medications,
    ons_deaths,
    repeat_medications,
    wl_clockstops,
    wl_openpathways,
)

many rows per patient

apcs_cost_historical🔗

This table contains some historical APCS cost data.

It has been exposed to users for data exploration, and may be removed in future.

Columns

apcs_ident 🔗 integer

  • Never NULL

grand_total_payment_mff 🔗 float

tariff_initial_amount 🔗 float

tariff_total_payment 🔗 float

admission_date 🔗 date

discharge_date 🔗 date

many rows per patient

apcs_historical🔗

This table contains some historical APCS data.

It has been exposed to users for data exploration, and may be removed in future.

Columns

apcs_ident 🔗 integer

  • Never NULL

admission_date 🔗 date

discharge_date 🔗 date

spell_core_hrg_sus 🔗 string

many rows per patient

isaric🔗

Access to this table requires the isaric permission

Access to ISARIC data is usually agreed at the project application stage. If you're unsure as to whether you do or should have access please speak to your co-pilot or to OpenSAFELY support.

ISARIC is a dataset of COVID-19-related hospital admissions, with coverage across the majority of hospitals across the UK, including much richer clinical information than collected in national Hospital Episode Statistics datasets.

The data in this table covers a subset of the ISARIC data columns available in TPP, sourced from the ISARIC COVID-19 Clinical Database.

All columns included have deliberately been taken as strings while in a preliminary phase.

Descriptions taken from CCP_REDCap_ISARIC_data_dictionary_codebook.pdf which also has information on the data expected for each column.

Warning

ISARIC data can only be used in collaboration with ISARIC researchers who must be involved in working on the study and writing it up.

Refer to the OpenSAFELY database build report to see when this data was last updated.

Columns

age 🔗 string

Age

age_factor 🔗 string

TODO

calc_age 🔗 string

Calculated age (comparing date of birth with date of enrolment). May be inaccurate if a date of February 29 is used.

sex 🔗 string

Sex at birth.

ethnic___1 🔗 string

Ethnic group: Arab.

ethnic___2 🔗 string

Ethnic group: Black.

ethnic___3 🔗 string

Ethnic group: East Asian.

ethnic___4 🔗 string

Ethnic group: South Asian.

ethnic___5 🔗 string

Ethnic group: West Asian.

ethnic___6 🔗 string

Ethnic group: Latin American.

ethnic___7 🔗 string

Ethnic group: White.

ethnic___8 🔗 string

Ethnic group: Aboriginal/First Nations.

ethnic___9 🔗 string

Ethnic group: Other.

ethnic___10 🔗 string

Ethnic group: N/A.

covid19_vaccine 🔗 string

Has the patient received a Covid-19 vaccine (open label licenced product)?

covid19_vaccined 🔗 date

Date first vaccine given (Covid-19) if known.

covid19_vaccine2d 🔗 date

Date second vaccine given (Covid-19) if known.

covid19_vaccined_nk 🔗 string

First vaccine given (Covid-19) but date not known.

corona_ieorres 🔗 string

Suspected or proven infection with pathogen of public health interest.

coriona_ieorres2 🔗 string

Proven or high likelihood of infection with pathogen of public health interest.

coriona_ieorres3 🔗 string

Proven infection with pathogen of public health interest.

inflammatory_mss 🔗 string

Adult or child who meets case definition for inflammatory multi-system syndrome (MIS-C/MIS-A).

cestdat 🔗 date

Onset date of first/earliest symptom.

chrincard 🔗 string

Chronic cardiac disease, including congenital heart disease (not hypertension).

  • Possible values: YES, NO, Unknown

hypertension_mhyn 🔗 string

Hypertension (physician diagnosed).

  • Possible values: YES, NO, Unknown

chronicpul_mhyn 🔗 string

Chronic pulmonary disease (not asthma).

  • Possible values: YES, NO, Unknown

asthma_mhyn 🔗 string

Asthma (physician diagnosed).

  • Possible values: YES, NO, Unknown

renal_mhyn 🔗 string

Chronic kidney disease.

  • Possible values: YES, NO, Unknown

mildliver 🔗 string

Mild liver disease.

  • Possible values: YES, NO, Unknown

modliv 🔗 string

Moderate or severe liver disease

  • Possible values: YES, NO, Unknown

chronicneu_mhyn 🔗 string

Chronic neurological disorder.

  • Possible values: YES, NO, Unknown

malignantneo_mhyn 🔗 string

Malignant neoplasm.

  • Possible values: YES, NO, Unknown

chronichaemo_mhyn 🔗 string

Chronic haematologic disease.

  • Possible values: YES, NO, Unknown

aidshiv_mhyn 🔗 string

AIDS/HIV.

  • Possible values: YES, NO, Unknown

obesity_mhyn 🔗 string

Obesity (as defined by clinical staff).

  • Possible values: YES, NO, Unknown

diabetes_type_mhyn 🔗 string

Diabetes and type.

  • Possible values: NO, 1, 2, N/K

diabetescom_mhyn 🔗 string

Diabetes with complications.

  • Possible values: YES, NO, Unknown

diabetes_mhyn 🔗 string

Diabetes without complications.

  • Possible values: YES, NO, Unknown

rheumatologic_mhyn 🔗 string

Rheumatologic disorder.

  • Possible values: YES, NO, Unknown

dementia_mhyn 🔗 string

Dementia.

  • Possible values: YES, NO, Unknown

malnutrition_mhyn 🔗 string

Malnutrition.

  • Possible values: YES, NO, Unknown

smoking_mhyn 🔗 string

Smoking.

  • Possible values: Yes, Never Smoked, Former Smoker, N/K

hostdat 🔗 date

Admission date at this facility.

hooccur 🔗 string

Transfer from other facility?

hostdat_transfer 🔗 date

Admission date at previous facility.

hostdat_transfernk 🔗 string

Admission date at previous facility not known.

readm_cov19 🔗 string

Is the patient being readmitted with Covid-19?

dsstdat 🔗 date

Date of enrolment.

dsstdtc 🔗 date

Outcome date.

many rows per patient

medications🔗

This table is an extension of the tpp.medications table.

It contains additional fields whose contents are not yet well understood, with the aim of facilitating exploratory analysis for data development and data curation purposes.

Columns

date 🔗 date

Date of the consultation associated with this event

consultation_id 🔗 integer

ID of the consultation associated with this event

medication_status 🔗 integer

Medication status. The values might map to the descriptions below from the data dictionary. Note that this still needs to be confirmed.

  • 0 - Normal
  • 4 - Historical
  • 5 - Blue script
  • 6 - Private
  • 7 - Not in possession
  • 8 - Repeat dispensed
  • 9 - In possession
  • 10 - Dental
  • 11 - Hospital
  • 12 - Problem substance
  • 13 - From patient group direction
  • 14 - To take out
  • 15 - On admission
  • 16 - Regular medication
  • 17 - As required medication
  • 18 - Variable dose medication
  • 19 - Rate-controlled single regular
  • 20 - Only once
  • 21 - Outpatient
  • 22 - Rate-controlled multiple regular
  • 23 - Rate-controlled multiple only once
  • 24 - Rate-controlled single only once
  • 25 - Placeholder
  • 26 - Unconfirmed
  • 27 - Infusion

  • 28 - Reducing dose blue script

  • Always >= 0 and <= 28

quantity 🔗 string

Quantity as structured text. The precise structure is yet to be determined and it may be that historical records are less well structured than more recent ones. Examples of the kinds of value you might find are: 

10ml - 0.5%
100 mililitres
1 pack of 28 capsule(s)
63 tablet
21 tablet(s) - 400mg
1 op - 8.75 cm x 1 m (e)

repeat_medication_id 🔗 integer

ID of the associated repeat medication record (zero if none exists)

many rows per patient

ons_deaths🔗

Registered deaths

Date and cause of death based on information recorded when deaths are certified and registered in England and Wales from February 2019 onwards. The data provider is the Office for National Statistics (ONS). This table is updated approximately weekly in OpenSAFELY.

This table includes the underlying cause of death, place of death, and up to 15 medical conditions mentioned on the death certificate. These codes (cause_of_death_01 to cause_of_death_15) are not ordered meaningfully.

More information about this table can be found in following documents provided by the ONS:

In the associated database table ONS_Deaths, a small number of patients have multiple registered deaths. This table contains all registered deaths. The ehrql.tables.ons_deaths table contains the earliest registered death.

Tip

To return one row per patient from ehrql.tables.raw.tpp.ons_deaths, for example the latest registered death, you can use:

ons_deaths.sort_by(ons_deaths.date).last_for_patient()
Columns

date 🔗 date

Patient's date of death.

place 🔗 string

Patient's place of death.

  • Possible values: Care Home, Elsewhere, Home, Hospice, Hospital, Other communal establishment

underlying_cause_of_death 🔗 ICD-10 code

cause_of_death_01 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_02 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_03 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_04 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_05 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_06 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_07 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_08 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_09 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_10 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_11 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_12 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_13 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_14 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

cause_of_death_15 🔗 ICD-10 code

Medical condition mentioned on the death certificate.

many rows per patient

repeat_medications🔗

This table is exposed for data development and data curation purposes. Its contents and not yet well understood and so it should not yet be used for research.

Columns

date 🔗 date

Date of the consultation associated with this event

consultation_id 🔗 integer

ID of the consultation associated with this event

repeat_medication_id 🔗 integer

medication_status 🔗 integer

Medication status. The values might map to the descriptions below from the data dictionary. Note that this still needs to be confirmed.

  • 0 - Normal
  • 4 - Historical
  • 5 - Blue script
  • 6 - Private
  • 7 - Not in possession
  • 8 - Repeat dispensed
  • 9 - In possession
  • 10 - Dental
  • 11 - Hospital
  • 12 - Problem substance
  • 13 - From patient group direction
  • 14 - To take out
  • 15 - On admission
  • 16 - Regular medication
  • 17 - As required medication
  • 18 - Variable dose medication
  • 19 - Rate-controlled single regular
  • 20 - Only once
  • 21 - Outpatient
  • 22 - Rate-controlled multiple regular
  • 23 - Rate-controlled multiple only once
  • 24 - Rate-controlled single only once
  • 25 - Placeholder
  • 26 - Unconfirmed
  • 27 - Infusion

  • 28 - Reducing dose blue script

  • Always >= 0 and <= 28

quantity 🔗 string

Quantity as structured text. The precise structure is yet to be determined and it may be that historical records are less well structured than more recent ones. Examples of the kinds of value you might find are: 

10ml - 0.5%
100 mililitres
1 pack of 28 capsule(s)
63 tablet
21 tablet(s) - 400mg
1 op - 8.75 cm x 1 m (e)

start_date 🔗 date

end_date 🔗 date

many rows per patient

wl_clockstops🔗

National Waiting List Clock Stops

Access to this table requires the waiting_list permission

Access to Waiting List data is usually agreed at the project application stage. If you're unsure as to whether you do or should have access please speak to your co-pilot or to OpenSAFELY support.

The columns in this table have the same data types as the columns in the associated database table. The three "pseudo" columns are small exceptions, as they are converted from binary columns to string columns.

Columns

activity_treatment_function_code 🔗 string

priority_type_code 🔗 string

pseudo_organisation_code_patient_pathway_identifier_issuer 🔗 string

pseudo_patient_pathway_identifier 🔗 string

pseudo_referral_identifier 🔗 string

referral_request_received_date 🔗 string

referral_to_treatment_period_end_date 🔗 string

referral_to_treatment_period_start_date 🔗 string

source_of_referral_for_outpatients 🔗 string

waiting_list_type 🔗 string

week_ending_date 🔗 string

many rows per patient

wl_openpathways🔗

National Waiting List Open Pathways

Access to this table requires the waiting_list permission

Access to Waiting List data is usually agreed at the project application stage. If you're unsure as to whether you do or should have access please speak to your co-pilot or to OpenSAFELY support.

The columns in this table have the same data types as the columns in the associated database table. The three "pseudo" columns are small exceptions, as they are converted from binary columns to string columns.

Columns

activity_treatment_function_code 🔗 string

current_pathway_period_start_date 🔗 string

priority_type_code 🔗 string

pseudo_organisation_code_patient_pathway_identifier_issuer 🔗 string

pseudo_patient_pathway_identifier 🔗 string

pseudo_referral_identifier 🔗 string

referral_request_received_date 🔗 string

referral_to_treatment_period_end_date 🔗 string

referral_to_treatment_period_start_date 🔗 string

source_of_referral 🔗 string

waiting_list_type 🔗 string

week_ending_date 🔗 string