Medicaid Provider Spending Knowledge Graph

Interactive knowledge graph and geographic viewer for exploring the HHS T-MSIS Medicaid Provider Spending dataset (227M rows, $1.09T, Jan 2018 -- Dec 2024). Combines a NetworkX knowledge graph (~550K nodes, ~4.7M edges) with DuckDB-powered drill-down queries and Claude AI agent personas for natural language exploration.

Features

Knowledge Graph -- Entity-level graph with providers, HCPCS codes, states, drug classes, survivorship domains, and cancer types. React Flow canvas with 7 custom node types, community detection, centrality metrics, and shortest-path queries.
Geographic Map -- Deck.gl + MapLibre GL layers: state choropleth, provider scatter (35K+ pins), billing flow arcs, hexbin density heatmaps. 2018--2024 time slider animation.
39 Spending Categories -- Drill down by immunotherapy, targeted therapy, imaging, rehabilitation, behavioral health, chronic care management, and more.
AI Agent -- Natural language queries via Claude Agent SDK with 4 specialized personas (Medical Informaticist, Health Economist, Biostatistician, Clinician Advisor). Agent has tool access to execute DuckDB queries and search the knowledge graph.
Query Builder -- Flexible aggregation builder with group-by, filter, sort against the full 227M-row dataset.
Pre-computed Cache -- Warm cache script eliminates runtime parquet scans for common queries.

Architecture

FastAPI (DuckDB + NetworkX) ──── React / TypeScript (React Flow + Deck.gl)
         ↕                                    ↕
   227M-row Parquet              Zustand state management
   NPPES Provider Registry       7 custom graph node types
   NetworkX in-memory graph      4 Deck.gl map layers
   Claude Agent SDK              SSE streaming for AI chat

No external database server. DuckDB reads the parquet file directly. The knowledge graph lives in-memory as a NetworkX DiGraph, persisted as a pickle with HMAC integrity verification.

Quick Start

Prerequisites

Python 3.11+
Node.js 22+
Data files (see Data Requirements)

Local Development

# 1. Install Python dependencies
cd kg
pip install -r backend/requirements.txt

# 2. Install frontend dependencies
cd frontend && npm ci && cd ..

# 3. Download public data files (~15 GB total)
python -m scripts.download_data
# Downloads: Medicaid parquet (2.94 GB), NPPES registry (11 GB), US states GeoJSON
# Two small files must be provided manually -- see Data Requirements below.

# 4. Configure data paths
cp .env.example .env
# Edit .env if your data files are not in ./data/

# 5. Build the knowledge graph (~5 min on first run)
python -m scripts.build_graph

# 5. Pre-compute cache (optional but recommended, ~10 min)
python -m scripts.warm_cache

# 6. Start the backend (port 8000)
uvicorn backend.main:app --host 0.0.0.0 --port 8000

# 7. Start the frontend dev server (port 5173, separate terminal)
cd frontend && npm run dev

Open http://localhost:5173 in your browser.

Docker

# Place data files in ./data/ (or set KG_DATA_DIR to your data directory)
docker compose up --build

The Docker image builds the frontend, installs Python + Node.js dependencies, and serves everything on port 8000. See Docker Deployment for details.

Project Structure

kg/
├── backend/
│   ├── main.py                    # FastAPI app, lifespan, CORS, SPA serving
│   ├── config.py                  # Paths, env vars, constants
│   ├── graph/
│   │   ├── models.py              # Entity/relation type enums, node models
│   │   ├── builder.py             # DuckDB aggregation → NetworkX graph
│   │   ├── store.py               # Thread-safe graph singleton, HMAC pickle
│   │   ├── serializers.py         # NetworkX → React Flow JSON
│   │   └── community.py           # Louvain clustering, centrality, search
│   ├── data/
│   │   ├── connection.py          # DuckDB connection manager (hardened)
│   │   ├── queries.py             # Parameterized drill-down queries
│   │   ├── taxonomies.py          # HCPCS codes, provider taxonomies, domains
│   │   ├── aggregations.py        # Graph-build aggregation queries
│   │   └── cache.py               # JSON cache loader
│   ├── api/
│   │   ├── graph_routes.py        # /api/graph/* endpoints
│   │   ├── map_routes.py          # /api/map/* endpoints
│   │   ├── drilldown_routes.py    # /api/drilldown/* endpoints
│   │   ├── query_routes.py        # /api/query/* (flexible aggregation)
│   │   ├── agent_routes.py        # /api/agent/* (NL query, pipelines)
│   │   └── stats_routes.py        # /api/stats/* (KPIs, typology)
│   ├── agents/
│   │   ├── personas.py            # 4 agent personas + system prompts
│   │   ├── nl_agent.py            # Claude Agent SDK client + SSE streaming
│   │   ├── sql_generator.py       # SQL validation pipeline (6-layer defense)
│   │   └── tools.py               # @tool-decorated MCP tools for agent
│   ├── pipelines/
│   │   ├── clustering.py          # PCA + HDBSCAN provider clustering
│   │   ├── trends.py              # Mann-Kendall trend detection
│   │   ├── equity.py              # Gini / HHI concentration scoring
│   │   └── geographic.py          # Service area / desert analysis
│   └── requirements.txt
├── frontend/
│   ├── src/
│   │   ├── App.tsx                # Main layout: header, view switcher, panels
│   │   ├── api/client.ts          # Typed API client
│   │   ├── stores/                # Zustand stores (graph, map, drilldown, agent)
│   │   ├── hooks/                 # useGraphData, useNeighborhood
│   │   ├── components/
│   │   │   ├── graph/             # React Flow canvas, 7 custom nodes, toolbar
│   │   │   ├── map/               # Deck.gl MapView, layers, time slider
│   │   │   ├── agent/             # AgentChat, PersonaSelector, QueryResults
│   │   │   ├── drilldown/         # Provider, State, Drug detail panels
│   │   │   ├── detail/            # Unified detail panel
│   │   │   ├── sidebar/           # Search, HCPCS tree browser
│   │   │   └── query/             # Query builder UI
│   │   └── lib/                   # Utilities (state abbreviations)
│   ├── package.json
│   ├── vite.config.ts
│   └── tsconfig.json
├── scripts/
│   ├── download_data.py           # Auto-download public data files
│   ├── build_graph.py             # One-shot graph construction
│   ├── geocode_providers.py       # NPPES → lat/lon via CBSA centroids
│   └── warm_cache.py              # Pre-compute JSON cache (~200 files)
├── Dockerfile                     # Multi-stage: Node build + Python runtime
├── docker-compose.yml
├── .env.example
└── .gitignore

Data Requirements

The application requires the following data files, configured via environment variables or placed in the data/ directory:

File	Env Var	Size	Description
Medicaid spending parquet	`KG_PARQUET_PATH`	~2.94 GB	T-MSIS provider spending (227M rows, 7 columns)
NPPES CSV	`KG_NPPES_PATH`	~11 GB	CMS National Provider Registry
Settings YAML	`KG_SETTINGS_YAML`	~20 KB	HCPCS code classifications (200+ codes)
Typology CSV	`KG_P3_TYPOLOGY_CSV`	~2 KB	State survivorship model classifications
US States GeoJSON	`KG_GEOJSON_PATH`	~800 KB	State boundaries for map choropleth

Automated Download

Three of the five files are publicly available and can be downloaded automatically:

python -m scripts.download_data              # Download all public files
python -m scripts.download_data --parquet    # Medicaid parquet only
python -m scripts.download_data --nppes      # NPPES registry only
python -m scripts.download_data --geojson    # US states GeoJSON only
python -m scripts.download_data --data-dir /path/to/data  # Custom output dir

The script downloads to ./data/ by default and skips files that already exist. The settings YAML and typology CSV are custom research files that must be provided manually.

Parquet schema (all columns UPPERCASE):

Column	Type	Description
`BILLING_PROVIDER_NPI_NUM`	VARCHAR	Billing provider NPI
`SERVICING_PROVIDER_NPI_NUM`	VARCHAR	Servicing provider NPI
`HCPCS_CODE`	VARCHAR	Healthcare procedure code
`CLAIM_FROM_MONTH`	VARCHAR	`YYYY-MM` format (not a date)
`TOTAL_UNIQUE_BENEFICIARIES`	INTEGER	Unique patients
`TOTAL_CLAIMS`	INTEGER	Claim count
`TOTAL_PAID`	DECIMAL	Dollar amount paid

API Reference

Graph (`/api/graph/`)

Method	Endpoint	Description
GET	`/full`	Full graph in React Flow format. Filterable by `entity_types`, `min_spending`, `state`, `domain`. Paginated with `limit`/`offset`.
GET	`/neighborhood/{node_id}?depth=2`	N-hop subgraph around a node (max depth 4, max 2000 nodes).
GET	`/path/{source}/{target}`	Shortest path between two nodes.
GET	`/search?q=pembrolizumab&limit=20`	Full-text node search across all entity types.
GET	`/clusters?resolution=1.0`	Louvain community detection results.
GET	`/centrality?top_n=50`	Degree, betweenness, and PageRank centrality.
GET	`/metrics`	Graph-level statistics (nodes, edges, density, components).

Map (`/api/map/`)

Method	Endpoint	Description
GET	`/states?metric=spending`	State data with 39 spending categories for choropleth.
GET	`/categories`	Available spending metric categories (grouped).
GET	`/providers?state=TX&limit=5000`	Provider scatter data for ScatterplotLayer.
GET	`/arcs?drug_class=immunotherapy`	Billing flow arcs between states for ArcLayer.
GET	`/hexbin`	Provider locations for HexagonLayer density.
GET	`/timeline?state=TX&metric=spending`	Monthly time series for animation.

Drill-Down (`/api/drilldown/`)

These hit the full 227M-row parquet via DuckDB (2--6s without cache).

Method	Endpoint	Description
GET	`/provider/{npi}`	Full provider spending by HCPCS code.
GET	`/provider/{npi}/timeline`	Monthly time series for a provider.
GET	`/provider/{npi}/billing-flows?direction=outgoing`	Who this provider bills for / who bills for them.
GET	`/servicing/{npi}`	Servicing provider detail.
GET	`/hcpcs/{code}`	HCPCS code spending by state and year.
GET	`/hcpcs-tree?prefix=J9`	Hierarchical HCPCS code browser.
GET	`/top-providers?provider_type=Oncology&state=TX`	Top providers by spending.
GET	`/state/{abbrev}/domain/{domain}`	State x survivorship domain breakdown.

Query Builder (`/api/query/`)

Method	Endpoint	Description
POST	`/execute`	Flexible aggregation query with filters, group-by, sort.
GET	`/dimensions`	Available filter options (states, years, HCPCS prefixes).

Agent (`/api/agent/`)

Method	Endpoint	Description
POST	`/query`	Natural language query with persona selection. Returns SSE stream.
GET	`/personas`	List available agent personas with descriptions.
POST	`/statistical/{pipeline}`	Run clustering, trends, equity, or geographic pipeline.

Stats (`/api/stats/`)

Method	Endpoint	Description
GET	`/kpis`	Pre-computed KPIs (total providers, spending, states).
GET	`/typology`	P3 state typology summary and classifications.

Knowledge Graph Schema

Node Types (~550K total)

Type	Key Fields	Source
Provider (~35K)	NPI, name, state, city, taxonomy, spending	Parquet + NPPES
HCPCS Code (~1,500)	code, domain, drug_class, is_j9, spending	Parquet + settings.yaml
State (51)	name, expansion_status, typology, composite_score	NPPES + P3 typology
Survivorship Domain (12)	mammography, distress, rehabilitation, etc.	settings.yaml
Drug Therapy Class (6)	immunotherapy, targeted, chemo, hormonal, etc.	J9 classification
Cancer Type (~8)	breast, lung, colorectal, hematologic, etc.	Drug signature inference
Provider Type (4)	Oncology, Primary Care, Behavioral Health, Other	Taxonomy classification

Edge Types (~4.7M total)

Type	Description
Provider → bills → HCPCS	Spending, claims, beneficiary counts
Provider → located_in → State	Geographic assignment
Provider → has_taxonomy → ProviderType	Classification
HCPCS → belongs_to → Domain	Survivorship domain mapping
HCPCS → classified_as → DrugClass	Drug therapy classification
DrugClass → treats → CancerType	Clinical association
State → has_typology → TypeClass	P3 model classification

AI Agent

The agent system uses the Claude Agent SDK with custom MCP tools defined in-process.

Personas

Persona	Focus	Temp
Medical Informaticist	HCPCS classification, data quality, taxonomy	0.1
Health Economist	Spending patterns, concentration, equity	0.2
Biostatistician	Trends, clustering, hypothesis testing	0.1
Clinician Advisor	Clinical context, survivorship guidelines	0.3

Agent Tools (MCP)

Tool	Description
`execute_query`	Run validated DuckDB SQL (SELECT only, 1000 row cap, function whitelist)
`graph_search`	Full-text search across all 550K+ graph nodes
`graph_neighborhood`	N-hop subgraph retrieval around any node

SQL Safety Pipeline

Agent-generated SQL passes through a 6-layer validation pipeline before execution:

Comment stripping -- Remove /* */ and -- comments
Statement whitelist -- Only SELECT or WITH ... SELECT
Dangerous token blocklist -- 30+ blocked tokens (file I/O, DDL, system tables)
Table whitelist -- Only medicaid and nppes
Function whitelist -- 55+ allowed functions (aggregates, string, date, window)
LIMIT injection -- Auto-append or cap at 1000 rows

Configuration

Environment Variables

Copy .env.example to .env and configure:

# Data paths (required)
KG_PARQUET_PATH=./data/medicaid_provider_spending.parquet
KG_NPPES_PATH=./data/nppes.csv
KG_SETTINGS_YAML=./data/settings.yaml
KG_P3_TYPOLOGY_CSV=./data/P3_typology_assignments.csv
KG_GEOJSON_PATH=./data/us-states.geojson

# Claude API (required for agent features)
ANTHROPIC_API_KEY=sk-ant-...
CLAUDE_MODEL=claude-sonnet-4-5-20250929

# DuckDB tuning
DUCKDB_THREADS=4
DUCKDB_MEMORY_LIMIT=4GB

# Server
KG_HOST=0.0.0.0
KG_PORT=8000
KG_CORS_ORIGINS=http://localhost:5173

DuckDB Hardening

The DuckDB connection is configured with security restrictions:

enable_external_access = false -- Blocks httpfs, S3, and remote file access
statement_timeout = 30s -- Prevents runaway queries

Scripts

`build_graph.py`

Builds the NetworkX knowledge graph from DuckDB aggregations. Run once after setting up data files.

python -m scripts.build_graph

Output: data/graph.pkl (~50--100 MB) + data/graph.sig (HMAC signature). Prints node/edge counts, community detection results, and top-10 PageRank nodes.

`warm_cache.py`

Pre-computes expensive DuckDB queries as JSON files in data/cache/. Eliminates runtime parquet scans for the most common API requests.

python -m scripts.warm_cache

Caches: state summaries, timelines (national + top 20 states), billing arcs (national + by drug class), top 50 HCPCS codes, provider scatter (top 10 states), state x domain breakdowns, top 100 provider details. Produces ~200 cache files, ~50 MB total.

`geocode_providers.py`

Maps provider locations to approximate lat/lon using CBSA (Core-Based Statistical Area) centroids. Generates data/provider_geocoded.csv for the hexbin map layer.

python -m scripts.geocode_providers

Docker Deployment

Build and Run

# Default: mount ./data/ into container
docker compose up --build

# Custom data directory
KG_DATA_DIR=/path/to/data docker compose up --build

# With API key for agent features
ANTHROPIC_API_KEY=sk-ant-... docker compose up --build

Multi-Stage Dockerfile

Stage 1 (node:22-slim): Builds the Vite frontend (npm run build)
Stage 2 (python:3.12-slim): Installs Python deps, copies Node.js for Claude Agent SDK, installs Claude Code CLI, copies built frontend to /app/static

The container serves both the API and the built frontend SPA on port 8000.

Data Volume

Mount your data directory to /app/data:

volumes:
  - /path/to/your/data:/app/data

Expected contents:

medicaid_provider_spending.parquet
nppes.csv
settings.yaml
P3_typology_assignments.csv
us-states.geojson (optional, for map)
graph.pkl + graph.sig (auto-generated on first run)
cache/ directory (auto-generated by warm_cache)

Health Check

curl http://localhost:8000/health
# {"status": "ok"}

The Docker container includes a built-in healthcheck polling /health every 30 seconds (120s startup grace period for graph loading).

Security

Parameterized SQL -- All drill-down and map queries use $1, $2, ... positional parameters. Zero string interpolation.
Agent SQL validation -- 6-layer defense pipeline: statement whitelist, dangerous token blocklist, table whitelist, function whitelist, LIMIT cap, error sanitization.
Input validation -- All API endpoints validate NPI (10-digit), state codes (2 uppercase), HCPCS codes (1--5 alphanumeric), years (4-digit).
Error sanitization -- 500-level responses return generic messages. Full errors logged server-side only.
DuckDB hardening -- External access disabled, 30s query timeout.
Pickle integrity -- HMAC-SHA256 signature on graph pickle. Tampered files trigger rebuild.
Path traversal protection -- SPA file serving validates resolved paths stay within static directory.
Pipeline param validation -- Pydantic models validate all statistical pipeline parameters before execution.
Neighborhood cap -- BFS limited to 2000 nodes to prevent memory exhaustion.
GZip compression -- All responses > 500 bytes are compressed.

Tech Stack

Backend

Component	Technology
API Framework	FastAPI
Data Engine	DuckDB (in-process, no server)
Knowledge Graph	NetworkX (DiGraph, in-memory)
AI Agent	Claude Agent SDK + custom MCP tools
Graph Analysis	Louvain community detection, PageRank, betweenness centrality
ML Pipelines	scikit-learn, HDBSCAN, SciPy (Mann-Kendall)

Frontend

Component	Technology
Framework	React 18 + TypeScript
Build Tool	Vite
Graph Visualization	React Flow
Map Visualization	Deck.gl + MapLibre GL
State Management	Zustand
Styling	Tailwind CSS

License

MIT

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
backend		backend
frontend		frontend
scripts		scripts
.dockerignore		.dockerignore
.env.example		.env.example
.gitignore		.gitignore
CONTRIBUTING.md		CONTRIBUTING.md
Dockerfile		Dockerfile
README.md		README.md
docker-compose.yml		docker-compose.yml
start.sh		start.sh
stop.sh		stop.sh

Folders and files

Latest commit

History

Repository files navigation

Medicaid Provider Spending Knowledge Graph

Features

Architecture

Quick Start

Prerequisites

Local Development

Docker

Project Structure

Data Requirements

Automated Download

API Reference

Graph (/api/graph/)

Map (/api/map/)

Drill-Down (/api/drilldown/)

Query Builder (/api/query/)

Agent (/api/agent/)

Stats (/api/stats/)

Knowledge Graph Schema

Node Types (~550K total)

Edge Types (~4.7M total)

AI Agent

Personas

Agent Tools (MCP)

SQL Safety Pipeline

Configuration

Environment Variables

DuckDB Hardening

Scripts

build_graph.py

warm_cache.py

geocode_providers.py

Docker Deployment

Build and Run

Multi-Stage Dockerfile

Data Volume

Health Check

Security

Tech Stack

Backend

Frontend

License

About

Resources

Contributing

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages