Drugs

Overview

Code on GitHub

Pharmacy claims record medications dispensed to patients. The Drugs data mart connects these fills with diagnoses and patient histories to identify potential refill needs, treatment gaps, and generic savings, and to measure periods of treatment.

The mart runs on core.medication, with other Tuva Core tables providing diagnoses, patient information, and amounts paid by the payer, such as an insurer. It is a standalone dbt package: SQL transformations that build the tables below.

CodeRx Enterprise expands drug coverage from about 21,000 products to 66,000, adding historical and inactive products that help identify drugs in older claims. It also supplies ingredients and dosage forms, such as tablets or inhaled solutions, used to build drug episodes. Open covers active drug products available for prescribing in its snapshot; these approximate counts reflect the catalogs used in testing. See CodeRx Drug Database for what each version includes and how to obtain it.

Use case	What it answers	CodeRx Open	CodeRx Enterprise
Brand vs. Generic	Who could switch to a generic, and what could the payer save?	Yes	Yes
Needs Refill	Who may need a refill soon or is recently overdue?	Yes	Yes
Open Drug Gaps	Who has a condition but no related dispensing in the past year?	Yes	Yes
Drug Episodes	When did a course of treatment start and end?	No	Yes

Installation

Add Drugs alongside Tuva Core in the dbt project that maps your claims data to the Tuva Input Layer.

Use the Tuva Core commit from main pinned below; it includes a National Drug Code (NDC) mapping fix that prevents duplicate medication records and is not present in the v1.0.0 release tag.

packages.yml

packages:
  - git: https://github.com/tuva-health/tuva-core.git
    revision: 599e953e10fba61352bfb6e7d64c2a9b58335fb1
  - git: https://github.com/tuva-health/drugs.git
    revision: f519999e37ced738348da967f1acc0e7672809e1

Enable claims and choose a CodeRx version in your root project's configuration:

dbt_project.yml

vars:
  claims_enabled: true
  use_coderx_enterprise: false

Open terminology loads with Tuva Core. To use Enterprise, purchase access and follow the CodeRx setup instructions to load the full coderx.packages, coderx.drugs, and coderx.classes tables in the target database. Then set use_coderx_enterprise: true. Write true or false without quotation marks. Missing required Enterprise tables or columns produce a setup error.

Install the dependencies and build the mart:

dbt deps
dbt build --select +package:drugs --indirect-selection cautious

The examples use drugs.table_name. The package requests the schema drugs, or <tuva_schema_prefix>_drugs when a Tuva schema prefix is configured. Your root project's schema naming policy determines the final name; dbt's default also adds target.schema. Adjust the schema in the queries if needed.

Configuration and data freshness

The assessment date is the date the mart uses to decide what is current. It defaults to the day the mart runs. Set drugs_as_of_date to a fixed date when you need a reproducible comparison.

Variable	Default	Purpose
use_coderx_enterprise	false	Use purchased terminology and enable episodes
drugs_as_of_date	Current date	Set the assessment date
drugs_episode_gap_days	30	Allow this many uncovered days between fills in one episode
drugs_stockpile_early_refills	false	Carry unused supply forward when the same clinical product is refilled early
drugs_refill_min_fills	2	Require this many distinct maintenance fill dates
drugs_refill_lookahead_days	7	Include refills due in the next seven days
drugs_refill_max_overdue_days	30	Include refills up to thirty days overdue
drugs_generic_lookback_days	90	Use this many days of generic claims to estimate prices
drugs_generic_min_claims / drugs_generic_min_people	3 / 2	Require enough claims and patients to support a price estimate
drugs_freshness_days	45	Flag older source files or long delays since the latest dispensing

A clinical product is a specific combination of ingredients, strength, and dosage form, such as a 20 mg atorvastatin oral tablet. Days supply is the number of days a dispensing is expected to last. A maintenance medication is used on a regular schedule, rather than only as needed.

Before contacting a patient, review the age of the underlying data. The patient-level output tables retain file dates and ingestion timestamps—the times records were loaded. File date takes precedence, so reloading an old file does not make it appear current. The refill table also preserves the dates of the specific fill used to calculate the refill due date. Freshness flags help prioritize record review; they do not establish that all claims have arrived.

Rebuild after new data deliveries. Changing the assessment date does not reconstruct the data or terminology that was available on that historical date.

Use Cases

The examples below use synthetic data with CodeRx Enterprise. The first three queries also work with Open; episodes require Enterprise.

Queries combine all data sources. A patient is identified by both data_source and person_id, so the same person represented in two sources can be counted twice. Patients can also appear under more than one condition. Deceased patients are excluded from the current patient lists; enrollment uncertainty remains visible in the underlying records.

Brand vs. Generic

A brand drug and a generic can contain the same ingredients in the same strength and dosage form while costing the payer different amounts. Identifying patients with a matching generic gives a care or pharmacy team a starting point for reviewing lower-cost options. An estimated opportunity still requires clinical and insurance-coverage review before a switch.

Start with drugs.brand_generic_summary. It reports the brand, matching generic, number of patients who could switch, and estimated annual savings. To identify the patients behind a result, query drugs.brand_generic_opportunity with generic_available_flag = 1. The detail table also retains brand users without a matching generic, so that filter matters.

The estimate uses payer-paid amounts: what the insurer paid on observed claims. For each patient, the mart compares the brand cost with the median generic cost per unit over the prior 90 days. The median is the middle price after sorting, averaging the two middle prices when needed. Comparisons match the data source, payer, insurance plan, clinical product—the same ingredients, strength, and form—and quantity per supply day. At least three generic claims from two patients are required by default.

Question: Which brand-to-generic opportunities have the largest supported savings?

select
    brand_drug,
    generic_alternative,
    sum(patients_who_could_switch) as patients_who_could_switch,
    sum(patients_with_savings_estimate)
        as patients_with_savings_estimate,
    sum(annual_savings_for_priced_patients)
        as annual_savings_for_priced_patients,
    case when sum(patients_without_savings_estimate) = 0
         then sum(annual_savings_for_priced_patients)
    end as annual_savings_if_all_switched
from drugs.brand_generic_summary
group by brand_drug, generic_alternative
order by annual_savings_for_priced_patients desc nulls last, brand_drug;

Example: Lipitor 20 mg oral tablet → atorvastatin 20 mg oral tablet

Result	Value
Patients who could switch	3
Patients with a supported savings estimate	2
Estimated annual savings for those two patients	$2,336
Estimated annual savings if all three switched	Not estimated

The third patient lacks enough comparable generic claims to estimate savings. The $2,336 subtotal is useful, but it should not be extended to that patient. This is why the query keeps the all-patient estimate empty when any candidate lacks a price. Annual estimates assume continued treatment at current utilization and prices; they exclude rebates and switching costs and are not realized savings.

For historical analysis, drugs.medication retains dispensing dates and payer-paid amounts. Use those records to trend observed spending; use the opportunity tables to estimate prospective savings.

How generic alternatives are identified

Alternatives share the same CodeRx clinical product: ingredients, strength, and dosage form. Package-level availability and price matching require an eleven-digit National Drug Code (NDC). A product identified through RxNorm, a standardized medication vocabulary, can still support class or episode analysis without establishing a package-level price comparison.

Enterprise uses product and package availability fields and marketing dates. It also includes an authorized generic, a product explicitly identified by the vendor as a generic marketed under the original brand's approval. The vendor category NDA AUTHORIZED GENERIC is recorded separately in drugs.generic_alternative using alternative_basis = 'active_authorized_generic_same_clinical_drug'. These records still need to pass the availability and same-product checks.

Current use and pricing require usable days supply. A matching ingredient, strength, and form identifies an alternative for review; it does not by itself establish interchangeability or insurance coverage.

Needs Refill

Many long-term conditions require medication taken on a regular schedule. A patient approaching the end of a dispensing's expected supply may need help obtaining the next fill. Identifying these patients gives a care team a timely list to review, rather than waiting for a longer interruption in treatment.

Start with drugs.condition_summary to compare refill needs across conditions. Then use drugs.needs_refill for the patient list, estimated due date, supporting medication, and data freshness. The screen covers Type 2 Diabetes Mellitus, Hypertension, Coronary Artery Disease, Chronic Obstructive Pulmonary Disease, Asthma, Heart Failure, and Atrial Fibrillation and Flutter.

The mart requires at least two dispensing dates for the same maintenance product in the past year, with usable days supply: the number of days the medication is expected to last. Maintenance means regular scheduled treatment. The estimated refill due date is the day after that supply ends. Defaults include fills due within 7 days or no more than 30 days overdue. Rescue medication, taken only when symptoms require it, does not establish a scheduled refill need on its own.

Question: What share of each condition population may need a refill?

select
    condition_name,
    sum(patients_needing_refill) as patients_needing_refill,
    sum(refill_denominator_patients) as patients_with_condition,
    round(
        100.0 * sum(patients_needing_refill)
        / nullif(sum(refill_denominator_patients), 0), 2
    ) as percent_needing_refill
from drugs.condition_summary
where refill_condition_eligible_flag = 1
group by condition_name
order by percent_needing_refill desc nulls last, condition_name;

For asthma, two of six patients are flagged, giving 33.33%. The denominator includes living patients with condition evidence recorded in the past 365 days, in a source with observed pharmacy data. It includes patients with no previous fills or uncertain supply; it is not limited to people whose refill need can be established. refill_unassessable_patients reports patients whose status cannot be determined.

Sum counts before dividing, as the query does; averaging percentages from different sources can give the wrong result. A condition with no denominator returns an empty percentage rather than zero.

Interpreting the patient list

A pharmacy claim shows dispensing, not whether the patient took the medication. A potential refill need may reflect a stopped prescription, a dose change, an outside fill, or delayed claims. Review the medication history and the dates of the evidence before outreach.

The result is one patient per condition and source. Current maintenance supply can suppress an overdue component of a regimen containing several drugs; this screen does not assess every individual prescription. Some drug classes have multiple uses. refill_class_confidence = 'possible' identifies records where the care team should confirm the condition being treated and whether use is scheduled or as needed.

Open Drug Gaps

A diagnosis establishes that a condition was documented; it does not show whether related medication was dispensed. Bringing these two types of evidence together helps a care team find patients whose treatment history may warrant review.

Start with drugs.condition_summary for counts by condition. drugs.open_drug_gap contains the patients with potential gaps. A patient qualifies when a supported condition was recorded during the past 365 days and no related medication dispensing was observed during that same 365-day window, including the assessment date.

The screen covers 68 conditions. It starts with diagnoses in core.condition, uses Tuva's condition grouper to collect related diagnoses, and maps each supported condition to one or more drug classes. A drug class is a group of related medications; the mapping uses the Anatomical Therapeutic Chemical (ATC) classification. The condition-to-class rules define the full scope.

Question: Which conditions have the most patients without related dispensing?

select
    condition_name,
    sum(patients_with_open_gaps) as patients_with_potential_gaps,
    sum(gap_unassessable_patients) as patients_not_assessable
from drugs.condition_summary
group by condition_name
having sum(patients_with_open_gaps) > 0
order by patients_with_potential_gaps desc, condition_name
limit 5;

Condition	Potential gaps	Not assessable
Asthma	2	0
Atrial Fibrillation and Flutter	2	3
Chronic Obstructive Pulmonary Disease	2	0
Coronary Artery Disease	2	3
Heart Failure	2	3

The second row shows two patients with a potential gap and three whose status cannot be determined. Those three are not counted as gaps. Missing pharmacy data, unidentified medications, undated records, or a class that only possibly treats the condition can leave the result uncertain. drugs.condition_status preserves those distinctions; drugs.condition_evidence and drugs.medication_condition retain supporting records.

Any qualifying class counts as related dispensing. The patient does not need a drug from every mapped class, and a completed course within the year still counts. Days supply and active treatment today are not required. Consequently, someone who needs a refill can have medication evidence in the past year and have no annual drug gap.

These are candidates for review, not proof that a medication is missing or required. Non-drug management, over-the-counter products, samples, and cash purchases can explain an absent claim. Conversely, one related dispensing does not establish that treatment is sufficient.

Drug Episodes

Individual fills describe events. An episode connects those events into a period of treatment, making it possible to study how long a patient has been on a drug, interruptions in supply, and subsequent restarts. This use case requires CodeRx Enterprise.

Query drugs.drug_episode. Each row represents one episode for a patient, data source, complete ingredient combination, and dosage form. Dosage form describes how a drug is supplied, such as an oral tablet or inhaled solution. Strength and brand changes do not start a separate treatment track. A combination inhaler stays one combination; changing its ingredients or form creates a different track.

The episode starts with the first dispensing. By default, expected supply ends on dispensing date plus days supply minus one. For example, a January 1 fill with thirty days supply lasts through January 30. A January 31 refill leaves no uncovered day; a February 1 refill leaves one.

By default, up to 30 uncovered days can connect fills within an episode. A 31-day gap starts a new episode. This allowance links the records; it does not add medication supply to the uncovered days. Overlapping fills are counted once. Unused supply from an early refill is not automatically carried forward; this optional behavior is called stockpiling.

Question: What treatment periods and interruptions appear in each patient's history?

select
    data_source,
    person_id,
    episode_id,
    drug_code,
    drug_name,
    dose_form_name,
    start_date,
    end_date,
    covered_days,
    uncovered_days
from drugs.drug_episode
order by data_source, person_id, drug_name, dose_form_name, start_date;

The following excerpt shows lisinopril oral tablet for one synthetic patient:

Episode	Start	End	Covered days	Uncovered days
1	Jan 1, 2026	Apr 19, 2026	79	30
2	May 21, 2026	Jun 19, 2026	30	0

This distinction matters when analyzing continuity: an episode's calendar span is not the number of days with medication available. Missing days supply cannot establish exposure, and a refill authorization does not establish that another fill occurred. Claims-based episodes estimate treatment history; they do not prove medication was taken or that a clinician discontinued it.

Episode identifiers and method

drug_code identifies the ingredient or complete ingredient combination in CodeRx. It is usually an RxNorm ingredient or multiple-ingredient identifier. Vendor-specific pack or group identifiers are not treated as a single RxNorm concept. drug_name names the ingredient combination, while dose_form_name identifies its form. drugs.episode_mapping_quality reports records lacking a usable ingredient or form mapping.

The method draws on OHDSI drug eras, which group drug exposure into periods of continuous use, and AdhereR, a published framework for estimating medication use from dispensing histories. Set drugs_episode_gap_days: 0 to require adjacent or overlapping supply. Optional early-refill stockpiling carries unused supply forward within the same clinical product.

With CodeRx Open, episode tables keep their columns but contain no rows. drugs.terminology_status records which capabilities are enabled, so an empty episode table should not be interpreted as an absence of medication use.