Drugs

Overview

Code on GitHub · Installation, methods and configuration

The Drugs data mart turns claims-derived core.medication records into four workflows: Drug Episodes, Open Drug Gaps, Needs Refill, and Brand vs. Generic. It is a standalone dbt package that replaces the pharmacy mart included in Tuva Core through v0.18.0.

All four workflows run with open-source CodeRx terminology. Enterprise CodeRx adds regulatory marketing information and richer descriptions. Patient worklists include the underlying evidence and data freshness so care managers can distinguish current observations from old or incomplete feeds.

Drug Episodes

drug_episode groups successive dispensings by atomic RxNorm ingredient, independent of strength, form or brand. Combination pills contribute to each ingredient, so a switch between combination and separate pills can remain in the same ingredient episode. The long output includes episode_id, data_source, person_id, drug_code, drug_name, start_date, and end_date. Drug code is the RxNorm IN code; unmapped ingredients are excluded and counted in episode_mapping_quality.

The method draws on OHDSI drug eras and the published AdhereR methods. The default allows 30 uncovered days between fills. A 31-day gap starts a new episode. Set drugs_episode_gap_days to 0 for adjacent or overlapping coverage only, or 1 to tolerate a one-day hole.

Supply ends on dispensing date + days supply - 1. Overlapping fills are unioned without double counting. Early-fill stockpiling is disabled by default and can be enabled for sensitivity analysis within the same clinical product. Episode continuity never fills uncovered days or extends the last supply date. active_supply_flag requires actual estimated supply on the assessment date, independent of the permissible episode gap.

A January 1 fill with 30 days supply ends January 30. A January 31 refill has zero uncovered days; a February 1 refill has one. Both stay in one episode under the default, with the uncovered day still reported.

Missing or invalid days supply does not create exposure. Refill authorizations do not create additional supply. Episode dates are estimates from dispensing history, not proof of medication ingestion or confirmed discontinuation.

Open Drug Gaps

open_drug_gap lists patients with recent, unresolved evidence of one of these Tuva-grouper conditions in core.condition, and no qualifying medication supply active today:

• Type 2 Diabetes Mellitus
• Hypertension
• Coronary Artery Disease
• Chronic Obstructive Pulmonary Disease
• Asthma
• Heart Failure (CHF)
• Atrial Fibrillation and Flutter (AFib)

The default diagnosis lookback is 365 days. Versioned condition-to-ATC rules use every CodeRx class membership, including combination products. These are potential gaps for review. They identify absence of any related medication, not missing components of guideline-recommended treatment. For example, rescue-only asthma treatment counts as medication evidence but does not establish adequate controller therapy. OTC medications, samples, cash fills and appropriate nonpharmacologic management may not be visible in claims.

condition_summary provides counts by source and condition; filter open_drug_gap on the selected condition to drill into specific patients and their freshness/evidence. CHF and AFib rules identify any related therapy, not complete guideline treatment. In particular, AFib rate control does not establish adequate stroke prevention, and HF subtypes are not separated by ejection fraction.

condition_status retains patients with known active supply and patients who cannot be assessed because of missing duration, unmapped medication or a missing pharmacy feed. Unknown flags are null rather than falsely asserting a gap. condition_evidence and medication_condition provide the diagnosis and medication records behind the assessment.

Needs Refill

needs_refill identifies potential refill needs for those same seven conditions. It requires at least two maintenance dispensing dates in the past 365 days and estimated supply ending soon or recently exhausted. Defaults include fills due within 7 days or at most 30 days overdue.

The estimated due date is the day after supply ends. Current replacement therapy suppresses overdue recommendations. Rescue-only respiratory medications do not produce scheduled refill estimates; scheduled versus PRN use of other respiratory drugs still requires review. Patients with unknown exposure are not asserted to need a refill.

Use condition_summary.patients_needing_refill for the condition counts and needs_refill for the corresponding patient list.

This is a condition-level worklist, so it does not detect a missing component of a multidrug regimen while another qualifying medication covers the condition. Due-soon patients may have no open gap yet; recently overdue patients can appear in both lists. More distant gaps stay in Open Drug Gaps for clinical review.

Brand vs. Generic

brand_generic_opportunity identifies current branded clinical-product users, shows whether a similar generic exists, and estimates savings when comparable payer-paid claims support a price. Each source/patient/clinical product appears once, preventing repeated historical fills from multiplying the current opportunity.

generic_alternative lists active, prescribable generic NDCs with the same clinical drug. Enterprise adds NDA-to-ANDA and marketing-date checks. Open terminology uses brand/nonbrand status and clinical-product identity. These relationships require therapeutic-equivalence, formulary and clinical review before switching.

The generic price is the median payer-paid price per unit among qualifying generic claims in the previous 90 days. Comparators must match source, payer, plan, clinical product and quantity per supply day; the default minimum is three claims and two patients. Savings use payer-paid amount only.

brand_generic_summary reports the brand drug, generic clinical alternative, number of patients who could switch, and annual savings. annual_savings_if_all_switched is only populated when every candidate has a supported price; annual_savings_for_priced_patients is the known subtotal. Priced and unpriced counts remain explicit.

The mart reports savings per 30 days and an annualized estimate assuming continued treatment at current utilization and prices. Unpriced opportunities remain visible with null savings. Rebates and switching costs are not modeled.

select data_source, clinical_drug_name,
       count(*) as current_brand_patients,
       sum(estimated_savings_per_30_days) as estimated_savings_per_30_days,
       sum(estimated_annual_savings) as estimated_annual_savings
from drugs.brand_generic_opportunity
where pricing_status = 'priced_for_review'
group by data_source, clinical_drug_name
order by estimated_annual_savings desc;

Aggregate savings before joining the one-to-many generic NDC list. Report the unpriced population separately rather than treating missing estimates as zero.

Inputs and Freshness

Input	Purpose
core.medication	Claims-derived dispensing dates, drug codes, quantity and days supply; root medication fact
core.pharmacy_claim	Payer-paid amount, payer/plan, reported refill metadata and file/ingestion provenance
core.condition	Tuva-grouper condition names and diagnosis dates
core.medical_claim	Recover missing diagnosis freshness through source/person/claim linkage
core.patient, core.eligibility	Death status, current enrollment and eligibility freshness
CodeRx drugs, packages, classes	Drug identity, generic alternatives and condition-to-class evidence
Public RxNorm drug_ingredient bridge	Atomic ingredient relationships in both terminology modes
Enterprise CodeRx ingredients	Additional historical ingredient coverage when Enterprise is enabled

Patient and claim keys always include data_source. Deceased patients are excluded from current worklists. Patients without confirmed current enrollment remain visible with a confirmation status.

Each worklist retains patient, condition and medication evidence freshness. Prefer file_date for the freshness date, retaining ingest_datetime separately so reloading an old file does not make it appear current. Source-level latest file and dispensing dates are also exposed. Source age or clinical lag above 45 days prompts a lagged-data status; no timestamp certifies complete claims capture.

All current assessments default to today's warehouse date. Rebuild after new deliveries or daily to keep materialized outputs current. drugs_as_of_date supports reproducible clinical-date assessment but does not recreate historical warehouse or terminology snapshots.

Installation and Configuration

# packages.yml
packages:
  - git: https://github.com/tuva-health/drugs.git
    revision: v0.2.0

# dbt_project.yml
vars:
  drugs_core_database: YOUR_CATALOG
  drugs_core_schema: core
  drugs_terminology_schema: terminology
  use_coderx_enterprise: false
models:
  drugs:
    +schema: drugs
seeds:
  drugs:
    +schema: drugs

The standard dbt schema macro appends drugs to the target schema. Configure your project's schema macro when the exact drugs schema is required. A standalone checkout can simply target that schema.

dbt deps
dbt build --select package:drugs

Enterprise sources default to the Core database's terminology.coderx_enterprise_* tables. Override drugs_coderx_database, drugs_coderx_schema and drugs_coderx_table_prefix as needed. Both modes also require terminology.rxnorm_drug_ingredient, built from NLM’s public-domain Current Prescribable Content with the package loader. Enterprise uses its ingredients table to extend historical coverage. Enterprise indications are not required for condition mappings.

See the package README for all variables, method assumptions and primary clinical references. Initial warehouse execution is validated on Databricks; other adapters need their own validation. General PDC/adherence scoring, drug interactions, polypharmacy and spend decomposition are outside this initial release.

Simple SQL Examples

The Databricks notebook shows the episode long table, gap/refill counts by condition with patient drilldowns, and the brand-to-generic report. Add one Text / String input named catalog using Edit → Add parameter. Enter the catalog containing the mart, then select Run all. The setup cell selects the catalog with USE CATALOG; subsequent queries use drugs.table references. The schema is fixed to drugs, and every query includes all data sources. There is no schema or data-source input. The selected_condition variable in the setup cell defaults to Heart Failure for the patient drilldowns.