Back to Guides
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

1

Tier 1 - Generic Drugs

Lowest cost-sharing for members

Typical copay: $5-$15
2

Tier 2 - Preferred Brand

Preferred brand-name drugs

Typical copay: $25-$50
3

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_nm        VARCHAR(255),
  
  -- Drug
  ndc                   VARCHAR(11) NOT NULL,
  drug_nm             VARCHAR(500),
  rxcui                 VARCHAR(20),
  
  -- Tier & Coverage
  tier                  INTEGER,                       -- 1, 2, 3, 4
  tier_nm             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_amt          DECIMAL(10,2),
  coinsurance_percent   DECIMAL(5,2),
  
  -- Status
  effective_dt        DATE NOT NULL,
  termination_dt      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
  clm_number          VARCHAR(50) UNIQUE NOT NULL,
  patient_id            UUID NOT NULL,
  
  -- Prescription
  rx_number             VARCHAR(50),
  ndc                   VARCHAR(11) NOT NULL,
  drug_nm             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_dt             DATE NOT NULL,
  written_dt          DATE,
  
  -- Pricing
  ingredient_cost       DECIMAL(10,2),
  dispensing_fee        DECIMAL(10,2),
  total_cost            DECIMAL(10,2),
  
  -- Adjudication
  allowed_amt        DECIMAL(10,2),
  paid_amt           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_dt ON pharmacy_claim(fill_dt);

-- Drug Utilization Review (DUR)
CREATE TABLE dur_alert (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  clm_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_desc     TEXT,
  
  -- Drug Interaction
  drug1_ndc             VARCHAR(11),
  drug2_ndc             VARCHAR(11),
  interaction_type      VARCHAR(100),
  
  -- Resolution
  override_reason       TEXT,
  overridden_by         UUID,
  override_dt         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_amt_per_unit DECIMAL(10,4),
  
  -- Period
  contract_start_dt   DATE,
  contract_end_dt     DATE,
  
  -- Volume Requirements
  minimum_volume        INTEGER,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

Point-of-Sale Adjudication

Real-Time Claim Processing (NCPDP D.0)

1.
Patient presents prescription at pharmacy

Pharmacist enters NDC, quantity, days supply

2.
Eligibility verification

Check if patient covered on fill date

3.
Formulary check

Verify drug is covered, check tier and restrictions

4.
DUR screening

Check drug interactions, duplicate therapy, dosing

5.
Pricing & payment

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

SQL Query Examples

Check Drug Formulary Status

SELECT 
  f.ndc,
  f.drug_nm,
  f.tier,
  f.tier_nm,
  f.copay_amt,
  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_dt <= CURRENT_DATE
  AND (f.termination_dt IS NULL OR f.termination_dt >= CURRENT_DATE)
  AND f.covered = TRUE;

Find Drug Interactions for Patient

SELECT 
  d.alert_type,
  d.severity,
  d.alert_desc,
  pc1.drug_nm as drug1,
  pc2.drug_nm 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_dt 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 →NDC Codes →

Browse Related Definitions

pharmacy quantityPharmacy Robotpharmacy percentPharmacy and TherapeuticsPharmacy Internpharmacy currentpharmacy datepharmacy reference

From the Blog

Pharmacy

Pharmacy Data Compliance: HIPAA and FDA Requirements

10 min readRead

Browse all healthcare data guides

All guides