Your Medicare Advantage plan submitted risk scores to CMS. Three months later, CMS audits arrive. They're disallowing $4.2M in risk-adjusted payments. Chart documentation doesn't support the HCCs your warehouse reported. Diagnosis codes are missing. Provider attestations aren't linked.

Your risk adjustment warehouse just cost you millions.

The problem isn't your coders or your clinicians. It's your data model. You modeled diagnosis codes as attributes, not as evidence chains that require clinical validation, temporal tracking, and audit-ready provenance.

Risk adjustment isn't just storing ICD-10 codes. It's building a defensible evidence trail from encounter → diagnosis → HCC mapping → risk score → payment that survives CMS audits and RADV reviews.

Here's how to build logical data models for healthcare risk adjustment that maintain accuracy, support HCC workflows, and pass CMS reporting requirements.

Why Risk Adjustment Data Models Are Different

Risk adjustment models predict healthcare costs based on member health status. CMS uses these models to pay Medicare Advantage plans fairly—sicker populations get higher payments.

The core formula:

Payment = Base Rate × Risk Score × Plan Factors

Risk Score is calculated from Hierarchical Condition Categories (HCCs) derived from diagnosis codes documented in member encounters.

The catch: CMS doesn't just want your HCC list. They want proof:

Which encounter documented this diagnosis?
Which provider attested to it?
What's the chart evidence?
Was it recaptured this year?
Does it map to a valid HCC?

A simple diagnosis code table can't answer these questions.

The Naive Risk Adjustment Model (Fails CMS Audit)

CREATE TABLE member_diagnosis (
  mmbr_id        VARCHAR(50),
  diagnosis_cd   VARCHAR(10),
  diagnosis_dt   DATE,
  prvdr_id       VARCHAR(50)
);

Why this fails CMS audit:

Problem 1: No Encounter Linkage

CMS asks: "Show me the encounter where this HCC was documented."

Your table has diagnosis_dt, but which encounter?
Was it an office visit? Telehealth? Hospital admission?
Who was the attending physician?

Without encounter_id linking to a validated encounter, you can't prove the diagnosis.

Problem 2: No HCC Mapping Audit Trail

ICD-10 code E11.65 (Type 2 diabetes with hyperglycemia) maps to HCC 19 in CMS-HCC V24 model, but mapped to HCC 18 in V23.

When did you run the mapping? Which model version? Can you reproduce the historical mapping?

Problem 3: No Clinical Validation

Provider documents "suspected diabetes" vs. "diabetes mellitus." One qualifies for HCC payment, one doesn't.

How does your model track clinical validation?
Where's the chart review status?
Who validated the diagnosis severity?

Problem 4: No Recapture Tracking

CMS requires annual recapture. Member had HCC 85 (congestive heart failure) last year. Did they have a qualifying encounter this year?

Your model shows historical diagnosis codes, but can't identify gaps or trigger recapture workflows.

The Production Risk Adjustment Model

Risk adjustment requires six interconnected entity groups:

1. Encounter Master (Source of Truth)

CREATE TABLE encounter_master (
  encounter_id         VARCHAR(50) PRIMARY KEY,
  mmbr_id              VARCHAR(50),
  encounter_dt         DATE,
  encounter_type_cd    VARCHAR(20),
  place_of_service_cd  VARCHAR(10),
  
  attending_prvdr_id   VARCHAR(50),
  rendering_prvdr_id   VARCHAR(50),
  facility_id          VARCHAR(50),
  
  encounter_status_cd  VARCHAR(20),
  chart_available_flag CHAR(1),
  chart_reviewed_flag  CHAR(1),
  
  data_source_cd       VARCHAR(20),
  clm_id               VARCHAR(50),
  
  created_dttm         TIMESTAMP,
  
  FOREIGN KEY (mmbr_id) REFERENCES member_master(mmbr_id)
);

Key principle: Every diagnosis must trace to a validated encounter.

2. Diagnosis Documentation (Evidence)

CREATE TABLE encounter_diagnosis (
  encounter_diag_id    VARCHAR(50) PRIMARY KEY,
  encounter_id         VARCHAR(50),
  diagnosis_cd         VARCHAR(10),
  diagnosis_seq_nbr    INT,
  
  diagnosis_type_cd    VARCHAR(20),
  present_on_admit_cd  CHAR(1),
  
  diagnosis_status_cd  VARCHAR(20),
  
  documented_by_prvdr_id VARCHAR(50),
  documentation_dt     DATE,
  
  validated_flag       CHAR(1),
  validated_by         VARCHAR(100),
  validated_dt         DATE,
  
  suspecting_flag      CHAR(1),
  
  FOREIGN KEY (encounter_id) REFERENCES encounter_master(encounter_id)
);

CREATE INDEX idx_diag_member_date 
  ON encounter_diagnosis(encounter_id, diagnosis_cd, documentation_dt);

Why this works:

✅ Links to specific encounter
✅ Tracks documentation date vs. encounter date
✅ Captures provider who documented
✅ Records validation status
✅ Flags "suspected" vs. "confirmed" diagnoses

3. HCC Mapping (Version-Controlled)

CREATE TABLE hcc_mapping (
  mapping_id           VARCHAR(50) PRIMARY KEY,
  icd10_cd             VARCHAR(10),
  cms_hcc_model_cd     VARCHAR(20),
  hcc_nbr              INT,
  hcc_desc             VARCHAR(200),
  hcc_category         VARCHAR(50),
  coefficient_value    DECIMAL(10,6),
  
  effective_dt         DATE,
  end_dt               DATE,
  is_current_flag      CHAR(1),
  
  mapping_source_cd    VARCHAR(50),
  mapping_version      VARCHAR(20)
);

CREATE INDEX idx_hcc_icd10 
  ON hcc_mapping(icd10_cd, cms_hcc_model_cd, effective_dt);

Example mappings:

INSERT INTO hcc_mapping VALUES
('MAP_001', 'E11.65', 'V24', 19, 'Diabetes with Acute Complications', 'DIABETES', 0.302, 
 '2024-01-01', '9999-12-31', 'Y', 'CMS_V24', 'V24_2024'),
 
('MAP_002', 'I50.9', 'V24', 85, 'Congestive Heart Failure', 'HEART', 0.323,
 '2024-01-01', '9999-12-31', 'Y', 'CMS_V24', 'V24_2024');

4. Member HCC Attribution (Annual Calculation)

CREATE TABLE member_hcc (
  mmbr_hcc_id          VARCHAR(50) PRIMARY KEY,
  mmbr_id              VARCHAR(50),
  payment_year         INT,
  hcc_nbr              INT,
  
  first_captured_dt    DATE,
  last_captured_dt     DATE,
  capture_count        INT,
  
  qualifying_encounter_id VARCHAR(50),
  qualifying_diagnosis_cd VARCHAR(10),
  
  deleted_flag         CHAR(1),
  deletion_reason_cd   VARCHAR(50),
  
  risk_score_contribution DECIMAL(10,6),
  
  created_dttm         TIMESTAMP,
  updated_dttm         TIMESTAMP,
  
  FOREIGN KEY (mmbr_id) REFERENCES member_master(mmbr_id)
);

CREATE INDEX idx_member_hcc_year 
  ON member_hcc(mmbr_id, payment_year, hcc_nbr);

Key fields:

first_captured_dt: When first documented (baseline)
last_captured_dt: Most recent encounter (recapture tracking)
capture_count: How many encounters documented this HCC
qualifying_encounter_id: Which encounter CMS should audit
deleted_flag: Track deleted HCCs (for CMS submission corrections)

5. Risk Score Calculation (Point-in-Time)

CREATE TABLE member_risk_score (
  risk_score_id        VARCHAR(50) PRIMARY KEY,
  mmbr_id              VARCHAR(50),
  payment_year         INT,
  calculation_dt       DATE,
  
  model_version_cd     VARCHAR(20),
  demographic_score    DECIMAL(10,6),
  disease_score        DECIMAL(10,6),
  total_risk_score     DECIMAL(10,6),
  
  hcc_count            INT,
  
  submitted_to_cms_flag CHAR(1),
  submission_dt        DATE,
  submission_file_id   VARCHAR(100),
  
  calculation_status_cd VARCHAR(20),
  
  FOREIGN KEY (mmbr_id) REFERENCES member_master(mmbr_id)
);

Example calculation:

WITH member_hccs AS (
  SELECT 
    mmbr_id,
    payment_year,
    hcc_nbr,
    risk_score_contribution
  FROM member_hcc
  WHERE payment_year = 2024
    AND deleted_flag = 'N'
)
INSERT INTO member_risk_score
SELECT 
  gen_random_uuid(),
  mh.mmbr_id,
  2024,
  CURRENT_DATE,
  'V24',
  0.450,  -- Demographic score (age/sex)
  SUM(mh.risk_score_contribution),  -- Sum of HCC coefficients
  0.450 + SUM(mh.risk_score_contribution),  -- Total
  COUNT(DISTINCT mh.hcc_nbr),
  'N',
  NULL,
  NULL,
  'CALCULATED'
FROM member_hccs mh
GROUP BY mh.mmbr_id;

6. CMS Submission Tracking (RAPS/EDPS)

CREATE TABLE cms_submission (
  submission_id        VARCHAR(50) PRIMARY KEY,
  submission_file_nm   VARCHAR(200),
  submission_type_cd   VARCHAR(20),
  submission_dt        DATE,
  payment_year         INT,
  
  member_count         INT,
  diagnosis_count      INT,
  total_risk_score     DECIMAL(12,6),
  
  cms_acknowledgment_dt DATE,
  cms_status_cd        VARCHAR(20),
  
  accepted_count       INT,
  rejected_count       INT,
  
  file_location        VARCHAR(500)
);

CREATE TABLE cms_submission_detail (
  submission_detail_id VARCHAR(50) PRIMARY KEY,
  submission_id        VARCHAR(50),
  mmbr_id              VARCHAR(50),
  encounter_id         VARCHAR(50),
  diagnosis_cd         VARCHAR(10),
  hcc_nbr              INT,
  
  submission_status_cd VARCHAR(20),
  rejection_reason_cd  VARCHAR(50),
  
  FOREIGN KEY (submission_id) REFERENCES cms_submission(submission_id)
);

Critical Queries for Risk Adjustment

Query 1: Member HCC List (Audit-Ready)

SELECT 
  m.mmbr_id,
  m.golden_first_nm,
  m.golden_last_nm,
  mh.payment_year,
  mh.hcc_nbr,
  hm.hcc_desc,
  
  e.encounter_dt,
  e.encounter_type_cd,
  p.npi_id as provider_npi,
  p.last_nm as provider_name,
  
  ed.diagnosis_cd,
  ed.validated_flag,
  ed.validated_dt
  
FROM member_hcc mh
JOIN member_master m ON mh.mmbr_id = m.mmbr_id
JOIN hcc_mapping hm ON mh.hcc_nbr = hm.hcc_nbr
JOIN encounter_diagnosis ed ON mh.qualifying_encounter_id = ed.encounter_id
  AND mh.qualifying_diagnosis_cd = ed.diagnosis_cd
JOIN encounter_master e ON ed.encounter_id = e.encounter_id
JOIN provider_master p ON e.attending_prvdr_id = p.prvdr_id

WHERE mh.payment_year = 2024
  AND mh.deleted_flag = 'N'
  AND hm.is_current_flag = 'Y'
  
ORDER BY m.mmbr_id, mh.hcc_nbr;

This query gives CMS everything they need:

Member identification
HCC number and description
Supporting encounter date
Provider who documented
Diagnosis code
Validation status

Query 2: HCC Recapture Gaps

WITH last_year_hccs AS (
  SELECT DISTINCT mmbr_id, hcc_nbr
  FROM member_hcc
  WHERE payment_year = 2023
    AND deleted_flag = 'N'
),
this_year_hccs AS (
  SELECT DISTINCT mmbr_id, hcc_nbr
  FROM member_hcc
  WHERE payment_year = 2024
    AND deleted_flag = 'N'
)
SELECT 
  ly.mmbr_id,
  m.golden_first_nm,
  m.golden_last_nm,
  ly.hcc_nbr,
  hm.hcc_desc,
  'NOT RECAPTURED' as status
  
FROM last_year_hccs ly
LEFT JOIN this_year_hccs ty 
  ON ly.mmbr_id = ty.mmbr_id 
  AND ly.hcc_nbr = ty.hcc_nbr
JOIN member_master m ON ly.mmbr_id = m.mmbr_id
JOIN hcc_mapping hm ON ly.hcc_nbr = hm.hcc_nbr
  AND hm.is_current_flag = 'Y'

WHERE ty.hcc_nbr IS NULL
ORDER BY ly.mmbr_id, ly.hcc_nbr;

Use this to trigger:

Provider outreach campaigns
Chart review requests
Care management follow-up

Query 3: RADV Audit Sample Preparation

CMS randomly selects members for Risk Adjustment Data Validation (RADV) audits.

SELECT 
  m.mmbr_id,
  m.golden_first_nm,
  m.golden_last_nm,
  mh.hcc_nbr,
  hm.hcc_desc,
  
  e.encounter_id,
  e.encounter_dt,
  e.encounter_type_cd,
  e.chart_available_flag,
  
  p.npi_id,
  p.organization_nm,
  
  ed.diagnosis_cd,
  ed.diagnosis_seq_nbr,
  ed.validated_flag
  
FROM member_hcc mh
JOIN member_master m ON mh.mmbr_id = m.mmbr_id
JOIN hcc_mapping hm ON mh.hcc_nbr = hm.hcc_nbr
JOIN encounter_diagnosis ed ON mh.qualifying_encounter_id = ed.encounter_id
JOIN encounter_master e ON ed.encounter_id = e.encounter_id
JOIN provider_master p ON e.attending_prvdr_id = p.prvdr_id

WHERE mh.mmbr_id IN ('M001', 'M002', 'M003')  -- CMS audit sample
  AND mh.payment_year = 2024
  AND mh.deleted_flag = 'N'
  
ORDER BY m.mmbr_id, e.encounter_dt;

Data Quality Rules for Risk Adjustment

CREATE TABLE risk_adjustment_validation_rule (
  rule_id              VARCHAR(50) PRIMARY KEY,
  rule_nm              VARCHAR(200),
  rule_desc            TEXT,
  severity_cd          VARCHAR(20)
);

INSERT INTO risk_adjustment_validation_rule VALUES
('RA_VAL_01', 'HCC without encounter',
 'Member HCC must link to valid encounter',
 'ERROR'),
 
('RA_VAL_02', 'Diagnosis not validated',
 'Qualifying diagnosis must be validated',
 'WARNING'),
 
('RA_VAL_03', 'Encounter missing chart',
 'Encounter marked as chart not available',
 'ERROR'),
 
('RA_VAL_04', 'Suspecting diagnosis used',
 'Diagnosis marked as suspected, not confirmed',
 'ERROR'),
 
('RA_VAL_05', 'HCC mapping outdated',
 'Using deprecated HCC model version',
 'ERROR');

HCC Hierarchy and Interaction Rules

HCCs have hierarchical relationships. If a member has both HCC 18 (Diabetes without complications) and HCC 19 (Diabetes with complications), only HCC 19 counts.

CREATE TABLE hcc_hierarchy (
  hierarchy_id         VARCHAR(50) PRIMARY KEY,
  parent_hcc_nbr       INT,
  child_hcc_nbr        INT,
  model_version_cd     VARCHAR(20),
  
  hierarchy_rule       VARCHAR(500)
);

INSERT INTO hcc_hierarchy VALUES
('HIER_001', 19, 18, 'V24', 'HCC 19 supersedes HCC 18'),
('HIER_002', 85, 86, 'V24', 'HCC 85 (CHF) supersedes HCC 86 (Acute Heart Failure)');

-- Apply hierarchy when calculating risk scores
DELETE FROM member_hcc mh1
USING member_hcc mh2
JOIN hcc_hierarchy hh ON mh1.hcc_nbr = hh.child_hcc_nbr
  AND mh2.hcc_nbr = hh.parent_hcc_nbr
WHERE mh1.mmbr_id = mh2.mmbr_id
  AND mh1.payment_year = mh2.payment_year
  AND hh.model_version_cd = 'V24';

Temporal Tracking: When Was the HCC True?

CREATE TABLE member_hcc_history (
  mmbr_hcc_history_id  VARCHAR(50) PRIMARY KEY,
  mmbr_hcc_id          VARCHAR(50),
  version_nbr          INT,
  
  hcc_nbr              INT,
  payment_year         INT,
  
  effective_dttm       TIMESTAMP,
  end_dttm             TIMESTAMP,
  is_current_flag      CHAR(1),
  
  change_reason_cd     VARCHAR(50),
  
  FOREIGN KEY (mmbr_hcc_id) REFERENCES member_hcc(mmbr_hcc_id)
);

Use case: "Show me this member's HCC 85 as it existed on September 15, 2024 when we submitted to CMS."

The Complete Risk Adjustment Data Model Checklist

✅ Encounter Master

Every diagnosis links to validated encounter
Chart availability tracked
Provider attribution documented

✅ Diagnosis Documentation

Diagnosis sequence preserved
Validation status tracked
Suspected vs. confirmed flagged

✅ HCC Mapping

Version-controlled
Model-specific
Temporal validity

✅ Member HCC Attribution

Annual recapture tracked
Qualifying encounter identified
Deletion audit trail

✅ Risk Score Calculation

Point-in-time reproducibility
Demographic + disease components
Hierarchy rules applied

✅ CMS Submission

File-level tracking
Detail-level status
Rejection handling

Conclusion

Risk adjustment isn't storing ICD-10 codes. It's building an audit-ready evidence chain from encounter to payment that survives CMS scrutiny.

Model for:

Encounter linkage (proof of documentation)
Clinical validation (confirmed diagnoses)
Temporal tracking (point-in-time accuracy)
Recapture workflows (annual requirements)
CMS submission (RAPS/EDPS files)
RADV audit defense (complete evidence trail)

Your risk adjustment warehouse determines whether CMS pays you fairly—or claws back millions.

Fix the model. Protect the revenue.

mdatool.

Logical Data Models for Healthcare Risk Adjustment: Accuracy, HCCs, and CMS Reporting