Medicare STAR Ratings Guide

-- STAR Measure Reference
CREATE TABLE star_measure (
  id                  SERIAL PRIMARY KEY,
  measure_code        VARCHAR(20) UNIQUE NOT NULL,    -- e.g., C01, D08
  measure_name        VARCHAR(255) NOT NULL,
  domain              VARCHAR(50),                    -- Staying Healthy, Chronic Conditions, etc.
  part                VARCHAR(5),                     -- C, D, C_D
  data_source         VARCHAR(50),                    -- HEDIS, CAHPS, HOS, Admin
  weight              DECIMAL(4,2) DEFAULT 1.0,       -- CMS measure weight
  direction           VARCHAR(10) DEFAULT 'HIGHER',   -- HIGHER = better, LOWER = better
  is_active           BOOLEAN DEFAULT TRUE,
  measurement_year    INTEGER
);

-- Member-Level Measure Tracking
CREATE TABLE star_member_measure (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id           UUID NOT NULL,
  contract_id         VARCHAR(20),                    -- CMS contract (H-number)
  measure_code        VARCHAR(20) REFERENCES star_measure(measure_code),
  measurement_year    INTEGER NOT NULL,

  -- Eligibility
  in_denominator      BOOLEAN NOT NULL DEFAULT FALSE,
  exclusion_reason    VARCHAR(100),

  -- Performance
  in_numerator        BOOLEAN,
  numerator_dt        DATE,                           -- Date the numerator event occurred
  numerator_source    VARCHAR(50),                    -- CLAIM, EHR, SURVEY, MANUAL

  -- Raw measure value (for continuous measures like PDC)
  measure_value       DECIMAL(8,4),
  measure_threshold   DECIMAL(8,4),                   -- e.g., 0.80 for PDC

  -- Outreach
  outreach_attempted  BOOLEAN DEFAULT FALSE,
  outreach_dt         DATE,
  outreach_channel    VARCHAR(50),                    -- PHONE, MAIL, PORTAL, SMS

  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_star_member ON star_member_measure(member_id, measurement_year);
CREATE INDEX idx_star_measure ON star_member_measure(measure_code, measurement_year);
CREATE INDEX idx_star_denom ON star_member_measure(in_denominator, in_numerator);

-- Plan-Level STAR Performance Rollup
CREATE TABLE star_plan_performance (
  id                  SERIAL PRIMARY KEY,
  contract_id         VARCHAR(20) NOT NULL,
  measurement_year    INTEGER NOT NULL,
  measure_code        VARCHAR(20) NOT NULL,
  denominator_count   INTEGER,
  numerator_count     INTEGER,
  rate                DECIMAL(6,4),
  star_value          INTEGER,                        -- 1–5 star cut point achieved
  UNIQUE(contract_id, measurement_year, measure_code)
);