Input Layer
The Input Layer is like the API for the Tuva data model. Once raw data sources (e.g. claims and medical records) are mapped to the Input Layer code automatically transforms that data into the Tuva data model (i.e. core data model and all the data marts).
The Input Layer is designed to accomodate both claims and clinical data sources.
A patient identifier field named person_id has been added to the Tuva data model for both claims and clinical sources. This is a required field and cannot be null. If you bought the Tuva MPI Engine or have your own patient matching solution, this field should be populated with the UUID (Universally Unique Identifier). If you do not have a UUID, we recommend mapping the source patient identifier to this field (member_id for claims, patient_id for clinical).
Claims Input
eligibility
The eligibility table includes information about a patient's health insurance coverage and demographics (note: we use the word patient as a synonym for member). Every claims dataset should include some sort of eligibility data, otherwise it's impossible to calculate member months, which are needed to calculate measures like PMPM. Each record in the table is intended to represent a unique eligibility (i.e. enrollment) span for a patient with a specific health plan.
Primary Key:
- person_id
- member_id
- enrollment_start_date
- enrollment_end_date
- payer
- plan
- data_source
| Column | Data Type | Description |
|---|
| Concept Name | Concept Type |
|---|---|
| person_id | person_id is a required (string) field that ideally contains a person-level UUID (Universally Unique Identifier), if available. This can be populated from th... |
| member_id | This field is a string; member_id is specific to a health insurer or health plan, and is assigned by the insurance company to uniquely identify a specific in... |
| subscriber_id | This field is a string; subscriber_id is specific to a health insurer or health plan, and is assigned by the insurance company to uniquely identify a specifi... |
| gender | This field is a string that represents the biological sex of a member. It must take one of three accepted values according to the Tuva Project: male, female,... |
| race | This field is a string and corresponds to a members race. |
| birth_date | This field is a date representing the birth date of a member. |
| death_date | This field is a date containing the day a member died. If the member is alive, this field is null. |
| death_flag | death_flag contains a flag indicating if a member has died; 1 for yes 0 for no. death_flag should be 1 if a death_date is populated. death_flag can be 1 and... |
| enrollment_start_date, enrollment_end_date | These fields are dates representing when a member became enrolled in coverage and when their coverage lapsed. The grain of the source table will affect how t... |
| payer | payer contains the name of the health insurance payer for the claim (Aetna, Blue Cross Blue Shield, etc). The source data may not contain this field. In that... |
| payer_type | This field is a string that contains the type of insurance provided by the payer. It must take one of the values contained in the payer_type terminology file... |
| plan | This field is a string that links every row to the the specific health insurance plan or sub-contract specific to a members enrollment (e.g. Aetna Gold, BCBS... |
| original_reason_entitlement_code | This field is a single-character string that contains a members original reason for Medicare entitlement. When available, mapping this field is particularly... |
| dual_status_code | This field is a string that indicates whether a member is enrolled in both Medicare and Medicaid. Knowing whether a member is enrolled in both Medicare and M... |
| medicare_status_code | This field is a two-character string that indicates how a member currently qualifies for Medicare, and is used in risk score calculations when original_reaso... |
| group_id | This field is a string that can correspond to the particular group under which multiple members are enrolled for health coverage. This could be an identifier... |
| group_name | This field is a string that corresponds to the group name under which multiple members are enrolled for health coverage. |
| first_name | This field is a string that corresponds to the members first name. |
| last_name | This field is a string that corresponds to the members last name. |
| social_security_number | This field is a string that corresponds to the members Social Security Number. |
| subscriber_relation | This field is a string that represents a members relationship to the policyholder. This field may be highly variable across data sources. |
| address | This field is a string that represents the members address on file. |
| city | This field is a string that represents the city where the member resides. |
| state | This field is a string that represents the state where the member resides. |
| zip_code | This field is a string that represents the members zip code. |
| phone | This field is a string that represents the members phone number. |
| data_source | This field is a user-defined string that indicates the data source. This string is typically named after the payer and state, for example, "BCBS Tennessee".... |
| file_name | This field is a string that corresponds to the name of the specific file that a particular row came from. Claims sources are different: some carriers may sha... |
| file_date | This field is a date, corresponding to the date associated with a particular claims file and typically representing the reporting period of the claims data.... |
| ingest_datetime | This field is a timestamp that corresponds to the date and time a source file landed in the data warehouse or cloud storage. If this information is available... |
medical_claim
The medical_claim table contains information on healthcare services and supplies provided to patients, billed by providers, and paid for by health insurers. It includes information on the provider who rendered the service, the amount paid for the service by the health insurer, and the underlying reason for the service (i.e. diagnosis). Each record in the table is intended to represent a unique claim line.
Primary Key:
- claim_id
- claim_line_number
- data_source
Foreign Keys:
- person_id
- member_id
| Column | Data Type | Description |
|---|
| Concept Name | Concept Type |
|---|---|
| claim_id | This is a string that links each row in the table to the unique claim to which it belongs. Keep in mind that the medical_claim table is at the claim line gra... |
| claim_line_number | This is a positive integer that identifies the claim line that a given row on the table represents. The values of claim_line_number for a given claim_id must... |
| claim_type | This field is a string that describes the type of claim and must have one of the following 3 values: institutional, professional, or undetermined. This is a... |
| person_id | person_id is a required (string) field that ideally contains a person-level UUID (Universally Unique Identifier), if available. This can be populated from th... |
| member_id | This field is a string that links each row to a given member. This field should be populated for every row in the medical_claim table. It is a header-level f... |
| payer | payer contains the name of the health insurance payer for the claim (Aetna, Blue Cross Blue Shield, etc). The source data may not contain this field. In that... |
| plan | This field is a string that links every row to the the specific health insurance plan or sub-contract specific to a members enrollment (e.g. Aetna Gold, BCBS... |
| claim_start_date, claim_end_date | These fields are dates formatted in the form YYYY-MM-DD. They represent the start and end dates for the claim. They should be populated for every row in the... |
| claim_line_start_date, claim_line_end_date | These fields are dates formatted YYYY-MM-DD. They correspond to the start and end dates for a given claim line, respectively. claim_start_date and claim_end_... |
| admission_date, discharge_date | These fields are dates formatted in the form YYYY-MM-DD. They represent the dates a patient was first admitted (admission_date) or discharged (discharge_date... |
| admit_source_code | This field is a single-character string that indicates a patients location prior to admission, and exists only in institutional claims. This is another heade... |
| admit_type_code | This field is a string that indicates the priority of admission (e.g. Urgent, Emergent, Elective, etc.) This is another header-level value, which means there... |
| discharge_disposition_code | This field is a two-character string that represents one of the standard discharge_disposition_code values. This field should be populated for all institutio... |
| place_of_service_code | This field is a two-character string that represents one of the standard place_of_service_code values, which represent a specific location where a medical se... |
| bill_type_code | This field is a three- or four-character string that represents one of the standard bill type code values. In the case that this code is three characters, we... |
| drg_code | This field is a three-character string that can contain two different types of DRG (Diagnosis Related Groups) codes: 1. MS-DRGs are a classification system u... |
| drg_code_type | This is a string that specifies the DRG code system associated with a given drg_code. There are two accepted values for this field: ms-drg and apr-drg. There... |
| revenue_center_code | This field is a four-character string that represents one of the standard revenue center code values. These codes are used to account for services and suppli... |
| service_unit_quantity | This is a numeric that corresponds to the number of units associated with a particular revenue_center_code. In source data, this value can be negative. Appro... |
| hcpcs_code | This field is a string that represents procedures, services and supplies rendered by providers to patients. These codes exist at the line level, and there ca... |
| hcpcs_modifier_1, ... hcpcs_modifier_5 | This field is a string. HCPCS modifiers can provide more information about a particular hcpcs_code and the circumstances or details relating to the service.... |
| rendering_npi | This field is a string that contains NPI (National Provider Identifier) values. rendering_npi represents the practitioner who performed or rendered the speci... |
| rendering_tin | This field is a string that contains TIN (Tax Identification Number) information for the practitioner who performed or rendered the specific service and can... |
| billing_npi | This field is a string that contains NPI (National Provider Identifier) values. billing_npi typically represents the entity (organization or individual) resp... |
| billing_tin | This field is a string that contains TIN (Tax Identification Number) information for the healthcare facility or institution where the specific service was re... |
| facility_npi | This field is a string that contains NPI (National Provider Identifier) values. facility_npi typically represents the location where specific services were d... |
| facility_tin | This field is a string that contains TIN (Tax Identification Number) information for the entity (organization or individual) responsible for billing and rece... |
| paid_date | The date the payer paid the claim, formatted YYYY-MM-DD. This date could be any date after claim_end_date and often falls a couple of weeks after claim_end_d... |
| paid_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the dollar amount paid by the health insurer for the covered service.... |
| allowed_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total amount allowed, including dollars paid by both the payer and... |
| charge_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total amount charged for a service before any adjustments. This ma... |
| coinsurance_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the dollar amount a member has paid for a covered service as part of c... |
| copayment_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total copayment charged on the claim by a provider. In source data... |
| deductible_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the dollar amount a member has paid for a covered service before the h... |
| total_cost_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total amount for a member’s cost of care. Based on the source data... |
| diagnosis_code_type | This field is a string that describes the type of ICD diagnosis codes used on this claim. It must have one of the following two values: icd-9-cm or icd-10-cm... |
| diagnosis_code_1, diagnosis_code_2, ... , diagnosis_code_25 | These fields are strings with the standard ICD diagnosis codes representing the diagnoses present on the claim. These values are header-level fields, so they... |
| diagnosis_poa_1, diagnosis_poa_2, ... , diagnosis_poa_25 | This field is a single-character string that denotes a patients condition at the time of admission. There may be up to 25 to describe each diagnosis_code. Ge... |
| procedure_code_type | This field is a string that describes the type of ICD procedure codes used on this claim. It must have one of the following two values: icd-9-pcs or icd-10-p... |
| procedure_code_1, procedure_code_2, ... , procedure_code_25 | These fields are strings with the standard ICD procedure codes representing the procedures present on the claim. Removing decimal points from ICD procedures... |
| procedure_date_1, procedure_date_2, ... , procedure_date_25 | These dates represent the dates specific ICD procedures occurred, and there can be up to 25 (procedure_date_1 corresponds to procedure_code_1, etc). These fi... |
| in_network_flag | This field is an integer indicating whether a claim was in or out of network. It can take on values of 0 (claim was out of network) or 1 (claim was in networ... |
| data_source | This field is a user-defined string that indicates the data source. This string is typically named after the payer and state, for example, "BCBS Tennessee".... |
| file_name | This field is a string that corresponds to the name of the specific file that a particular row came from. Claims sources are different: some carriers may sha... |
| file_date | This field is a date, corresponding to the date associated with a particular claims file and typically representing the reporting period of the claims data.... |
| ingest_datetime | This field is a timestamp that corresponds to the date and time a source file landed in the data warehouse or cloud storage. If this information is available... |
pharmacy_claim
The pharmacy_claim table includes information about retail and specialty drug prescriptions that have been filled by a patient, billed by a pharmacy, and paid by an insurer. Each record in the table is intended to represent a unique claim line.
Primary Key:
- claim_id
- claim_line_number
- data_source
Foreign Keys:
- person_id
- member_id
| Column | Data Type | Description |
|---|
| Concept Name | Concept Type |
|---|---|
| claim_id | This is a string that links each row in the table to the unique claim to which it belongs. Keep in mind that the pharmacy_claim table is at the claim line gr... |
| claim_line_number | This is a positive integer that identifies the claim line that a given row on the table represents. The values of claim_line_number for a given claim_id must... |
| person_id | person_id is a required (string) field that ideally contains a person-level UUID (Universally Unique Identifier), if available. This can be populated from th... |
| member_id | This field is a string that links each row to a given member. This field should be populated for every row in the pharmacy_claim table. It is a header-level... |
| plan | This field is a string that links every row to the the specific health insurance plan or sub-contract specific to a members enrollment (e.g. Aetna Gold, BCBS... |
| prescribing_provider_npi | This field is a string that contains NPI (National Provider Identifier) values. precribing_provider_npi is populated with the NPI of the provider who prescri... |
| dispensing_provider_npi | This field is a string that contains NPI (National Provider Identifier) values. dispensing_provider_npi is populated with the NPI of the provider who dispens... |
| dispensing_date | This field is a date that corresponds to the date a medication was given to the patient. This may also be denoted as fill_date depending on the data source. |
| ndc_code | This field represents the National Drug Code (NDC) for the actual drug being dispensed. Each line on a pharmacy claim represents a drug that was dispensed, s... |
| quantity | This field is a positive integer that represents the number of doses of the medication. Before the application of Adjustments, Denials, and Reversals (ADR),... |
| days_supply | This field is a positive integer that represents the number of days supply for the medication. Before the application of Adjustments, Denials, and Reversals... |
| refills | This field is a positive integer that represents the number of refills for the prescription. Before the application iof Adjustments, Denials, and Reversals (... |
| paid_date | This field is a date that corresponds to when the health insurer processed the claim for payment, and in the context of pharmacy claims should coincide with... |
| paid_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the dollar amount paid by the health insurer for the covered medicatio... |
| allowed_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total amount allowed for a medication, including dollars paid by b... |
| charge_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total amount charged for a medication before any adjustments. This... |
| coinsurance_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the dollar amount a member has paid for a covered service as part of c... |
| copayment_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the total copayment charged on the claim by a provider. In source data... |
| deductible_amount | This field is numeric with two decimal points (e.g. numeric(38,2)) that corresponds to the dollar amount a member has paid for a covered medication before th... |
| in_network_flag | This field is an integer indicating whether a claim was in or out of network. It can take on values of 0 (claim was out of network) or 1 (claim was in networ... |
| data_source | This field is a user-defined string that indicates the data source. This string is typically named after the payer and state, for example, "BCBS Tennessee".... |
| file_name | This field is a string that corresponds to the name of the specific file that a particular row came from. Claims sources are different: some carriers may sha... |
| file_date | This field is a date, corresponding to the date associated with a particular claims file and typically representing the reporting period of the claims data.... |
| ingest_datetime | This field is a timestamp that corresponds to the date and time a source file landed in the data warehouse or cloud storage. If this information is available... |
Clinical Input
appointment
The appointment table contains information related to appointments at a healthcare facility. This table may include canceled, completed, or scheduled events.
Primary Key:
- appointment_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
| Column | Data Type | Description |
|---|
condition
The condition table contains information related to medical conditions patients have, including problems and billable diagnosis codes. Each record in the table is intended to document the occurrence of a unique condition for a specific patient at a specific point in time.
Primary Key:
- condition_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
| Column | Data Type | Description |
|---|
encounter
The encounter table contains information about patients visits (i.e. encounters). This includes office visits from clinical sources. Each record in the encounter table is intended to represent a unique patient visit with a healthcare provider of a unique type (e.g. acute inpatient).
Primary Key:
- encounter_id
Foreign Keys:
- patient_id
- person_id
| Column | Data Type | Description |
|---|
lab_result
The lab result table contains information about lab test results, including the LOINC code and description, units, reference range, and result. Each record in the table is intended to represent a unique lab result.
Primary Key:
- lab_result_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
| Column | Data Type | Description |
|---|
immunization
The immunization table contains information on immunizations administered to patients, including the vaccine code, description, and administration date.
Primary Key:
- immunization_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
- location_id
- practitioner_id
| Column | Data Type | Description |
|---|
location
The location table contains information on practice and facility locations where patients receive medical care. Each record in the table is intended to represent a unique location.
Primary Keys:
- location_id
| Column | Data Type | Description |
|---|
medication
The medication table contains information on medications ordered and/or administered during a patient encounter. Each record in the table is intended to represent a unique order or administration of a medication to a patient.
Primary Key:
- medication_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
| Column | Data Type | Description |
|---|
observation
The observation table contains information on measurements other than lab tests e.g. blood pressure, height, and weight. Each record in the table is intended to represent a unique observation.
Primary Keys:
- observation_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
| Column | Data Type | Description |
|---|
patient
The patient table contains demographic and geographic information on patients. Each record in the table is intended to represent a unique patient.
Primary Key:
- patient_id
- person_id
| Column | Data Type | Description |
|---|
practitioner
The practitioner table contains information on the providers in the dataset (e.g. physicians, physicians assistants, etc.). Each record in the table is intended to represent a unique provider.
Primary Key:
- practitioner_id
| Column | Data Type | Description |
|---|
procedure
The procedure table contains information on procedures that were performed on patients in the dataset. Each record in the table is intended to represent a unique procedure performed on a unique patient by a unique provider at a unique time.
Primary Key:
- procedure_id
Foreign Keys:
- patient_id
- person_id
- encounter_id
- practitioner_id
| Column | Data Type | Description |
|---|