Pharmacy

Pharmacy Benefits Guide

Complete guide to Pharmacy Benefit Management (PBM) systems. Learn formulary management, drug utilization review, rebate processing, and pharmacy claims adjudication.

Drug Formulary Tiers

Tier 1 - Generic Drugs

Lowest cost-sharing for members

Typical copay: $5-$15

Tier 2 - Preferred Brand

Preferred brand-name drugs

Typical copay: $25-$50

Tier 3 - Non-Preferred Brand

Non-preferred or specialty drugs

Typical copay: $50-$100+

PBM Database Schema

-- Drug Formulary
CREATE TABLE formulary (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  plan_id               UUID NOT NULL,
  formulary_name        VARCHAR(255),
  
  -- Drug
  ndc                   VARCHAR(11) NOT NULL,
  drug_name             VARCHAR(500),
  rxcui                 VARCHAR(20),
  
  -- Tier & Coverage
  tier                  INTEGER,                       -- 1, 2, 3, 4
  tier_name             VARCHAR(50),
  covered               BOOLEAN DEFAULT TRUE,
  
  -- Restrictions
  prior_auth_required   BOOLEAN DEFAULT FALSE,
  step_therapy_required BOOLEAN DEFAULT FALSE,
  quantity_limit        INTEGER,
  quantity_limit_days   INTEGER,
  
  -- Cost Sharing
  copay_amount          DECIMAL(10,2),
  coinsurance_percent   DECIMAL(5,2),
  
  -- Status
  effective_date        DATE NOT NULL,
  termination_date      DATE,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_formulary_ndc ON formulary(ndc);
CREATE INDEX idx_formulary_plan ON formulary(plan_id);
CREATE INDEX idx_formulary_tier ON formulary(tier);

-- Pharmacy Claims
CREATE TABLE pharmacy_claim (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identifiers
  claim_number          VARCHAR(50) UNIQUE NOT NULL,
  patient_id            UUID NOT NULL,
  
  -- Prescription
  rx_number             VARCHAR(50),
  ndc                   VARCHAR(11) NOT NULL,
  drug_name             VARCHAR(500),
  quantity              DECIMAL(10,2),
  days_supply           INTEGER,
  refills_authorized    INTEGER,
  
  -- Pharmacy
  pharmacy_ncpdp        VARCHAR(7),
  pharmacy_npi          VARCHAR(10),
  
  -- Prescriber
  prescriber_npi        VARCHAR(10),
  prescriber_dea        VARCHAR(20),
  
  -- Dates
  fill_date             DATE NOT NULL,
  written_date          DATE,
  
  -- Pricing
  ingredient_cost       DECIMAL(10,2),
  dispensing_fee        DECIMAL(10,2),
  total_cost            DECIMAL(10,2),
  
  -- Adjudication
  allowed_amount        DECIMAL(10,2),
  paid_amount           DECIMAL(10,2),
  patient_copay         DECIMAL(10,2),
  
  -- Status
  status                VARCHAR(50),
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_pharm_claim_patient ON pharmacy_claim(patient_id);
CREATE INDEX idx_pharm_claim_ndc ON pharmacy_claim(ndc);
CREATE INDEX idx_pharm_claim_fill_date ON pharmacy_claim(fill_date);

-- Drug Utilization Review (DUR)
CREATE TABLE dur_alert (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  claim_id              UUID REFERENCES pharmacy_claim(id),
  patient_id            UUID NOT NULL,
  
  -- Alert Details
  alert_type            VARCHAR(100),                  -- interaction, duplicate, dose
  severity              VARCHAR(20),                   -- high, medium, low
  alert_code            VARCHAR(20),
  alert_description     TEXT,
  
  -- Drug Interaction
  drug1_ndc             VARCHAR(11),
  drug2_ndc             VARCHAR(11),
  interaction_type      VARCHAR(100),
  
  -- Resolution
  override_reason       TEXT,
  overridden_by         UUID,
  override_date         TIMESTAMP,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_dur_patient ON dur_alert(patient_id);
CREATE INDEX idx_dur_severity ON dur_alert(severity);

-- Rebates
CREATE TABLE manufacturer_rebate (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  manufacturer_id       UUID,
  ndc                   VARCHAR(11) NOT NULL,
  
  -- Rebate Terms
  rebate_percent        DECIMAL(5,2),
  rebate_amount_per_unit DECIMAL(10,4),
  
  -- Period
  contract_start_date   DATE,
  contract_end_date     DATE,
  
  -- Volume Requirements
  minimum_volume        INTEGER,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

Point-of-Sale Adjudication

Real-Time Claim Processing (NCPDP D.0)

Patient presents prescription at pharmacy

Pharmacist enters NDC, quantity, days supply

Eligibility verification

Check if patient covered on fill date

Formulary check

Verify drug is covered, check tier and restrictions

DUR screening

Check drug interactions, duplicate therapy, dosing

Pricing & payment

Calculate plan pays, patient copay, return response (~2 seconds)

SQL Query Examples

Check Drug Formulary Status

SELECT 
  f.ndc,
  f.drug_name,
  f.tier,
  f.tier_name,
  f.copay_amount,
  f.prior_auth_required,
  f.step_therapy_required,
  f.quantity_limit
FROM formulary f
WHERE f.plan_id = 'plan-uuid'
  AND f.ndc = '00002-7510-02'
  AND f.effective_date <= CURRENT_DATE
  AND (f.termination_date IS NULL OR f.termination_date >= CURRENT_DATE)
  AND f.covered = TRUE;

Find Drug Interactions for Patient

SELECT 
  d.alert_type,
  d.severity,
  d.alert_description,
  pc1.drug_name as drug1,
  pc2.drug_name as drug2
FROM dur_alert d
JOIN pharmacy_claim pc1 ON d.drug1_ndc = pc1.ndc
JOIN pharmacy_claim pc2 ON d.drug2_ndc = pc2.ndc
WHERE d.patient_id = 'patient-uuid'
  AND d.severity IN ('high', 'medium')
  AND d.override_date IS NULL
ORDER BY 
  CASE d.severity WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END;

Related Healthcare Guides

RxNorm →Claims Adjudication →Prior Authorization →Healthcare Glossary →

mdatool.