Methods, Citations, and Reproducibility

This page maps public-facing claims to concrete metric outputs and the exact SQL source files that generate them.

Claim-to-Metric Citations

No Results

Policy Scenario Citation Map

No Results

Method Contracts and Readability Guardrails

The table below makes each public claim auditable by naming its measurement contract directly.

Contract ID	Metric	Definition	Timeframe	Caveat
`policy.national.beneficiaries_at_risk_m.base`	Estimated people at risk	`state_monthly_baseline touchpoint totals × adjustment factors`	`12 / months_in_scope` annualized context	Uses average monthly state touchpoints ÷ assumed person touchpoint factor
`policy.national.annual_paid_at_risk_b.base`	Annual paid at risk	`state paid / months_in_scope × 12 × adjusted_loss_pct`	`monthly_totals scope`	Uses `coverage_loss_pct` × risk score multiplier
`policy.national.annual_uncompensated_pressure_b.base`	Annual uncompensated pressure	`annual_paid_at_risk_b × uncompensated_conversion_pct`	Same as paid-at-risk	Applies one conversion assumption to estimate unpaid costs
`policy.state_access_risk.top_state`	Top risk index state	highest `risk_score_100` from normalized inputs	All available states in `spending_by_state`	Score is descriptive (composite), not forecasted
`headline.home_health_growth_pct.cutoff`	Home health growth	`(post - pre) / pre × 100`	`Pre-COVID` vs `Post-COVID` windows	Uses category-level average monthly paid dollars
`headline.er_share_2023plus_pct`	ER share of combined ER+PC spend	`AVG(er_share_of_combined_paid)`	`2023-01` to dataset end	Monthly aggregate of HCPCS-code mapped ER vs office visit services
`headline.top_hcpcs.*`	Top HCPCS benchmark	highest paid rows	`summary scope`	Stable ranking depends on included CPT/HCPCS set
`summary.*.cutoff`	Dataset snapshot contracts	payer totals and counts through cutoff	`<= 2024-10`	Uses static precomputed `summary` output

Reproduce the Results

Use these commands to reproduce the dashboard outputs from scratch.

1. Environment setup

cd dashboard
npm ci

2. Configure MotherDuck access

export EVIDENCE_SOURCE__medicaid__token="<your_motherduck_token>"
export EVIDENCE_SOURCE__medicaid__database="medicaid"

3. Rebuild source outputs

npm run sources
npm run build
python3 scripts/validate_methods_contracts.py

4. Optional full QA and citation snapshot workflow (repo root)

cd ..
python3 scripts/build_hcpcs_category_map.py
./scripts/validate_all.sh

SQL Snippets (Verbatim)

C-001: Home Health Growth (Pre-COVID vs Post-COVID)

WITH categorized AS (
    SELECT
        CASE
            WHEN p.CLAIM_FROM_MONTH < '2020-01' THEN 'Pre-COVID (2018-2019)'
            WHEN p.CLAIM_FROM_MONTH < '2022-01' THEN 'COVID (2020-2021)'
            ELSE 'Post-COVID (2022-2024)'
        END AS era,
        COALESCE(m.category, 'Other') AS category,
        p.TOTAL_PAID,
        p.TOTAL_CLAIMS,
        p.TOTAL_UNIQUE_BENEFICIARIES
    FROM provider_spending p
    LEFT JOIN hcpcs_category_map m ON p.HCPCS_CODE = m.HCPCS_CODE
    WHERE p.CLAIM_FROM_MONTH <= '2024-10'
),
era_months AS (
    SELECT 'Pre-COVID (2018-2019)' AS era, 24 AS months UNION ALL
    SELECT 'COVID (2020-2021)', 24 UNION ALL
    SELECT 'Post-COVID (2022-2024)', 34
)
SELECT
    c.era,
    c.category,
    SUM(c.TOTAL_PAID) / 1e9 AS total_paid_billions,
    SUM(c.TOTAL_PAID) / em.months / 1e9 AS avg_monthly_paid_billions,
    SUM(c.TOTAL_UNIQUE_BENEFICIARIES) / em.months / 1e6 AS avg_monthly_beneficiaries_millions
FROM categorized c
JOIN era_months em ON c.era = em.era
GROUP BY c.era, c.category, em.months
ORDER BY c.category, c.era

SELECT
    month,
    er_paid,
    er_beneficiaries,
    er_claims,
    pc_paid,
    pc_beneficiaries,
    pc_claims,
    ROUND(er_paid / NULLIF(er_paid + pc_paid, 0) * 100, 2) AS er_share_of_combined_paid,
    ROUND(er_beneficiaries / NULLIF(pc_beneficiaries, 0), 4) AS er_to_pc_beneficiary_ratio
FROM (
    SELECT
        CLAIM_FROM_MONTH AS month,
        SUM(CASE WHEN HCPCS_CODE IN ('99281','99282','99283','99284','99285') THEN TOTAL_PAID ELSE 0 END) AS er_paid,
        SUM(CASE WHEN HCPCS_CODE IN ('99281','99282','99283','99284','99285') THEN TOTAL_UNIQUE_BENEFICIARIES ELSE 0 END) AS er_beneficiaries,
        SUM(CASE WHEN HCPCS_CODE IN ('99281','99282','99283','99284','99285') THEN TOTAL_CLAIMS ELSE 0 END) AS er_claims,
        SUM(CASE WHEN HCPCS_CODE IN ('99201','99202','99203','99204','99205','99211','99212','99213','99214','99215') THEN TOTAL_PAID ELSE 0 END) AS pc_paid,
        SUM(CASE WHEN HCPCS_CODE IN ('99201','99202','99203','99204','99205','99211','99212','99213','99214','99215') THEN TOTAL_UNIQUE_BENEFICIARIES ELSE 0 END) AS pc_beneficiaries,
        SUM(CASE WHEN HCPCS_CODE IN ('99201','99202','99203','99204','99205','99211','99212','99213','99214','99215') THEN TOTAL_CLAIMS ELSE 0 END) AS pc_claims
    FROM provider_spending
    WHERE HCPCS_CODE IN ('99281','99282','99283','99284','99285','99201','99202','99203','99204','99205','99211','99212','99213','99214','99215')
      AND CLAIM_FROM_MONTH <= '2024-10'
    GROUP BY CLAIM_FROM_MONTH
) sub
ORDER BY month

C-003: Top HCPCS by Spend

SELECT
    HCPCS_CODE,
    SUM(TOTAL_PAID) / 1e9 AS paid_billions,
    SUM(TOTAL_CLAIMS) / 1e6 AS claims_millions,
    SUM(TOTAL_UNIQUE_BENEFICIARIES) / 1e6 AS beneficiaries_millions,
    COUNT(DISTINCT BILLING_PROVIDER_NPI_NUM) AS provider_count
FROM provider_spending
WHERE CLAIM_FROM_MONTH <= '2024-10'
GROUP BY HCPCS_CODE
ORDER BY SUM(TOTAL_PAID) DESC
LIMIT 25

C-004: Dataset Scope

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT BILLING_PROVIDER_NPI_NUM) AS unique_providers,
    COUNT(DISTINCT HCPCS_CODE) AS unique_procedures,
    SUM(TOTAL_PAID) / 1e9 AS total_paid_billions
FROM provider_spending
WHERE CLAIM_FROM_MONTH <= '2024-10'

Public Data Sources

HHS Medicaid Provider Utilization and Payment Data
CMS NPPES (NPI Registry)
U.S. Census Bureau ZCTA-to-County crosswalk (2020)