Hospital Quality Explorer

The problem it exists to solve

CMS publishes hospital quality data as six differently-shaped files with hundreds of measure codes, “Not Available” sprinkled through the numbers, and benchmarks in separate downloads. Every hospital analytics team rebuilds the same thing from it: a scorecard, a benchmark comparison, a patient-experience view, and a caveat list. I built that end to end, in the open, for the 467 Texas hospitals and the 73 in the five-county Houston area.

Data and pipeline

1. Download. Six Care Compare datasets (Hospital General Information, Unplanned Hospital Visits, HCAHPS, Timely & Effective Care, Complications & Deaths, Healthcare-Associated Infections) resolved through the data.cms.gov metastore at run time, with a SHA-256 provenance manifest — 799,104 rows.
2. Clean. Six column layouts collapse into one tidy fact table: (facility, measure, period, value). “Not Available” becomes NULL, never zero. A documented higher_is_better heuristic per measure.
3. Load. A three-table star schema in Postgres (Supabase): hospitals, measures, measure_values, plus a latest-period Texas view. Benchmarks are deliberately not loaded — state and national averages and percentiles are computed in SQL with window functions.
4. Analyze. Twelve SQL analyses — joins, window functions, CTEs, conditional pivots — each saved with its question, approach, and result count.
5. Publish. SQL views → CSV extracts → a Tableau Public workbook and a self-contained Plotly page; and a static JSON + Parquet export that drives the interactive explorer below, with no server behind it.

The explorer

The dashboard answers the Houston question. The explorer answers yours: search any of the 5,419 hospitals in the country and get a report card that places every one of 33 key measures on the distribution of a peer group you define — the whole country, one state, the Houston area, or only hospitals with the same ownership or type. Every benchmark is recomputed in the browser from hospital-level rows, the same way the SQL does it in the warehouse.

• Report card — peer-group strips, “beats X% of peers” badges, shareable URL, CSV export.
• Compare — brush a distribution and the scorecard and HCAHPS heatmap follow; hover a row to find its dot.
• Measures — all 161 measures with national vs peer distributions, best and worst ten, and missingness stated up front.
• Map — state choropleth of medians; zoom into a state for hospital dots colored by peer percentile.
• Rankings — a weighted composite with five sliders that re-rank the list live (labeled as illustrative, not the CMS star method), plus the outcomes-versus-experience mismatch quadrant with a brush.
• SQL — the twelve analyses run in the browser on DuckDB-WASM over the project’s Parquet tables; edit and re-run anything. Every chart also has a “Show the SQL” drawer.

Open the explorer → Vanilla JavaScript and D3; ~3 MB of static data; no backend.

Two of the analyses

National percentile without a benchmark table. Latest value per hospital from all states, then PERCENT_RANK() over the national distribution. Low percentile on a mortality measure is good — Houston Methodist Hospital lands at 0.004.

WITH latest AS (
  SELECT DISTINCT ON (facility_id) facility_id, score
  FROM measure_values
  WHERE measure_id = 'MORT_30_HF' AND score IS NOT NULL
  ORDER BY facility_id, period_end DESC),
ranked AS (
  SELECT facility_id, score,
         PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
  FROM latest)
SELECT h.facility_name, r.score, round(r.pct_rank::numeric, 3) AS national_percentile
FROM ranked r JOIN hospitals h USING (facility_id)
WHERE h.is_houston_area
ORDER BY national_percentile;

The mismatch quadrant. Hospitals better than the Texas average on readmission but worse on “would definitely recommend.” Two CTEs for the measures, one for the averages, a cross join to compare. Sixty-six hospitals — the most discussable cell on the dashboard.

WITH r AS (SELECT facility_id, score AS readm     FROM v_tx_latest WHERE measure_id = 'READM_30_PN'),
     p AS (SELECT facility_id, score AS recommend FROM v_tx_latest WHERE measure_id = 'H_RECMND_DY'),
     a AS (SELECT (SELECT avg(readm) FROM r) AS avg_readm,
                  (SELECT avg(recommend) FROM p) AS avg_recommend)
SELECT h.facility_name, h.county, r.readm, p.recommend
FROM hospitals h JOIN r USING (facility_id) JOIN p USING (facility_id) CROSS JOIN a
WHERE r.readm < a.avg_readm AND p.recommend < a.avg_recommend
ORDER BY r.readm;

The dashboard

Four panels: a Houston-area scorecard with direction-aware color, pneumonia readmission against Texas and national reference lines, an HCAHPS heatmap across ten survey questions, and the share of Texas hospitals beating the national average per measure.

Open full-screen →

Limitations and what I’d do next

• This release carries one reporting period per measure, so the period-over-period analysis (LAG()) is a pattern waiting for the next quarterly refresh — the pipeline re-runs in one command.
• CMS suppresses small-denominator scores; 90 of 168 measures are missing for more than half of Texas hospitals. Coverage is reported on the page rather than hidden.
• “Higher is better” is a per-measure heuristic that needs a clinician’s review for the ambiguous HCAHPS answer rows.
• Next: denominator-weighted county roll-ups on the dashboard, a materialized scorecard view, and a scheduled refresh when CMS posts the October release.

Version 2: ask the data

What it is

Version 2 puts a question box on top of the same data. You type a question in plain English; a language model then either writes SQL against the hospital tables, finds the relevant passage in CMS documents (496 passages of measure documentation, and 833 passages from Medicare National Coverage Determinations added after launch), or declines. Every answer comes with its evidence: the SQL and the rows it returned, or quoted passages with the document, page and a link to the source. It is a public demo built as a portfolio project, not a product with users. I built it with AI coding agents under my direction: I chose what to build, what counted as a correct answer, and the calls described below.

Try it: ask a question → Runs on a small cloud service that sleeps when idle, so the first question can be slow.

How it works

1. Plan. One model call reads the question and picks a route: data, documentation, both, or refuse. For data it writes the SQL; for documentation it rewrites the question into a search query and picks which of the two document sets to search.
2. Evidence. SQL passes two safeguards before it touches anything. A validator checks that it is a single SELECT. Then it runs under a database login that can only read the hospital schema, in read-only transactions with a 5-second timeout; that login cannot read the documentation index, the request log or any other schema. Documentation questions use vector search over the chosen document set and keep the top 8; a search never mixes the two sets.
3. Answer. A second call writes a short answer from only the rows and passages it was handed. Citations are checked: one with an unknown chunk id, or a quote that is not in that chunk, is dropped, and a documentation answer left with no valid citation becomes a refusal. If there is no evidence on any path, it declines without writing an answer.

Declining is a designed outcome. The evaluation sets include questions about recipes, individual doctors, prices, patient records, medical advice, forecasts, system tables and prompt injection, and the system is expected to refuse every one.

How it was evaluated

Question sets. 75 questions with known-correct answers: 38 number questions (the generated SQL's rows are compared with a stored truth query), 22 definition questions (the answer must state listed points, judged by a larger model), 4 definition questions the documents cannot answer (must decline), and 11 off-topic or unsafe ones (must decline). 12 are a dev split used while writing the prompts; the other 63 are the test split, written after the prompts and never shown to the model. A second set of 26 questions, the holdout, was written after the retrieval settings and planner prompt were frozen, without running the system on them: 15 definition, 3 not-in-the-documents, 8 number. A third set of 25 was drafted by a second AI agent that had not built the system, and I reviewed and approved each question; the answer keys were added and the file was frozen before the service saw any of them. Fourteen of those 25 ask something close to an earlier question, and two were reworded with my approval before they were run because, as first written, one compared a group with no data and one left “nationwide” ambiguous.

Results. Start is the first configuration, run twice. Final is the configuration the live service uses, run three times. Each cell is questions answered correctly out of questions asked.

Start: plan_v2, schema_v1, answer_v3, hybrid retrieval. Final: plan_v4, schema_v2, answer_v4, vector retrieval, 8 passages. The model that answers is called gpt-6-luna; a larger model, gpt-6-sol, grades the definition answers. Every score is a single run, or the pair or triple shown, and repeat runs of one configuration differed by 1 to 3 questions (final definition scores: 13, 11, 14). Read the changes in numbers accordingly: the 23 to 26 move on number questions held in every later run; the definition scores did not move beyond that noise.

Added after launch: Medicare coverage documents

The second document set is the 345 current Medicare National Coverage Determinations, fetched from the CMS Coverage API. The service can now answer “is this covered, and under what conditions” from the text of a determination. A coverage answer names the determination, quotes it, and ends by saying it is national policy and not a decision about any person. Questions about one person’s claim, prices, other insurers or local coverage rules are declined.

It has its own set of 30 questions, written by an AI agent from the determinations and frozen before the service saw any of them. Eight were used to adjust the prompts (8 of 8). On the other 22 the service scored 21 of 22 in each of two runs: 16 of 16 on what a determination covers, 2 of 3 on questions no determination answers, and 3 of 3 on questions it should decline. Read that with care. The agent that wrote the questions also built the feature, and each question names its item or service, so finding the right document is easy; the score shows the path works, not that coverage answers are always right. The second run exists because review found the answer prompt used one determination as a formatting example and a test question was about that same determination; the example was replaced and the set was run again. On the original hospital-quality test split the changed prompts scored 56 of 63 in one run (26 of 26 number, 15 of 22 definition, 4 of 4 and 11 of 11 declined) and passed the release gate.

What broke

• A change of mine turned a wrong filter into a confident “none.” At first, a query that returned zero rows was refused. I changed that so the service answers “no records match,” because “none” is sometimes the true answer. The first evaluation then showed the cost: on two number questions the model filtered a name in mixed case against data stored in capitals, got zero rows, and reported “none” with full confidence. A third used a Texas-only view for a national question. What fixed it was telling the planner true facts measured from the database (how text is cased, which states each view covers): number questions went from 23 of 26 to 26 of 26 and stayed there. I also added a check that asks the planner to look again when a query returns nothing. It has not fired once in the runs since, so it is covered only by unit tests, and it cannot catch a count that comes back as 0, which is one row, not none. A holdout question failed exactly that way.
• The keyword half of hybrid search matched nothing, then hurt once it worked. Documentation search started as hybrid: vector search plus keyword search, merged. Run alone, the keyword half retrieved the expected passage for 0 of 22 definition questions and the system declined 21 of them for lack of evidence, so the first hybrid score (13 of 22) was probably the vector half doing all the work (12 of 22 on its own). After the keyword search was changed to return matches, the same prompts scored 11 of 22 on vector alone, 8 of 22 on hybrid and 5 of 22 on keyword alone (that keyword run also had five connection errors, so 5 is a floor). Running each half alone is what exposed it.
• The retrieval study measured the wrong input. I picked hybrid retrieval with 6 passages by scoring the raw question text. The system never sends raw text: the planner rewrites each question into a search query first. Re-scored on those rewritten queries, vector search got the expected passage in the top 6 for 17 of 22 questions; the hybrid setting I had picked got 15. The pre-declared selection rule then chose vector with 8 passages, and that is what runs now. The first choice was wrong because the study fed the search something the product does not.
• The release gate blocked a change, and I re-baselined on purpose. Every change to the service is checked against a stored baseline with a tolerance per question type. After the first round of changes, number questions rose to 26 of 26 but definition questions fell from 13 to 8 and 10 of 22 in two runs, so the gate failed and that version would not have shipped. The cause was the keyword search described above. With search back on vectors, definition questions returned to between 11 and 14 of 22, inside the 1 to 2 question spread I measured between identical runs, while number questions held at 26. I accepted that trade, set the definition tolerance to 2 from the measured spread, and re-baselined with the reason written into the baseline file. Later the not-in-the-documents tolerance went from 0 to 1 for a similar reason: with 4 questions, one answer that honestly said “the documentation does not give the criteria” failed a grader that only accepts an outright refusal. Off-topic and unsafe questions stay at zero tolerance, and any unsafe question that produces SQL fails the gate regardless.
• Some answer keys asked for more than the question did. One question asks which measure groups feed the star rating and how many stars a hospital can get; its key also requires “most hospitals display three stars,” which the question never asks. Two other keys add details the same way. All three failed in all nine test-split runs on file (repeats and the larger model’s run included), which is what pointed at the keys rather than the system. I have not rewritten them, so I treat the definition scores as rough rather than exact.
• The first deploy failed three ways. The setup script stopped because Windows PowerShell treats a harmless message from the Azure CLI as fatal. The evaluation gate then scored 0 of 63 because secrets piped into the GitHub CLI picked up a byte-order mark and a line ending, corrupting every one. And Azure sign-in failed because GitHub’s sign-in subject now carries numeric ids and my credential used the name-only form. Each was fixed at the cause and written into the runbook. The gate did its job on the second: a broken configuration could not ship.
• Two drills, to test the safety net itself. A pull request with a deliberately worse prompt (answers capped at eight words) was stopped by the evaluation gate: definition questions fell to 2 of 22 against a baseline of 13, while number questions stayed at 26 of 26 because they are graded on the query result, not the wording. Nothing was built or deployed. Separately, the live service was switched to the previous answer prompt with one command and was serving it about 45 seconds later, then switched back. That rollback path skips the gate on purpose, which is why the prompt versions in use are shown on the health endpoint and recorded with every request.
• New documents reached the live table before the code that separates them. The coverage passages were loaded into the same table the running service searches, while the running code still searched every row. Until the new release shipped, a live question could have pulled a coverage passage and been answered without the coverage wording rules. Review of the load step caught it, not a test. A check showed no coverage passage reached the top 8 for any of the 22 definition test questions, and the release that filters by document set closed the window. Loading into a separate table first would have avoided it.

Cost and speed

• Per question: about $0.0013 for the answering model on the test split ($0.00131 in the first final run, $0.00130 on the holdout). The service enforces a $0.50 daily budget and per-client and overall rate limits.
• Latency: median 10.4 seconds and 90th percentile 15.9 seconds on the test split; median 8.9 seconds on the holdout.
• Idle cost: the service scales to zero, so nothing is billed while nobody is asking. The price is a slower first question after a quiet period.
• The larger model, not adopted. Run once on the starting configuration, gpt-6-sol scored 26 of 26 on number questions (against 23 of 26) but 9 of 22 on definitions (against 13 of 22) and 50 of 63 overall (against 51). It cost $0.0218 per question against $0.0012, about 19 times as much, with a median of 14.2 seconds against 12.7. More cost, no better overall; the smaller model reached 26 of 26 on number questions after the prompt and schema changes.

Limits

• Scope. It answers from the hospital-level quality tables, the 496 measure-documentation passages and the 833 coverage passages, nothing else. It will not rank individual doctors, quote prices, discuss patients or give medical advice. This release has one reporting period per measure, so questions about change over time cannot be answered.
• Definition answers are the weak spot. 11 to 14 of 22 on the test split and 11 of 15 on the holdout. For some questions the right passage is never retrieved (6 or 7 of 22 test questions in the final runs), because the documentation is thin or phrased differently for those measures. Part of the gap is the answer-key problem above.
• Declining is graded by a blunt rule. A judge that checks whether an answer asserts anything the documents lack is planned and not built.
• Coverage is national policy text only. National Coverage Determinations as fetched on October 6, 2026, not refreshed automatically. No local coverage determinations, no billing articles, no Medicare Advantage, Medicaid or commercial plans. Twenty-nine of the 345 determinations are marked retired in their titles and can still be cited. An answer summarizes a document; it is not a coverage decision.
• Alerting is coarse. A scheduled check outside the service reads the request log every 30 minutes and raises an alert at 80% and 100% of the daily budget, or when at least 3 requests and a quarter of requests failed in the last hour. Each alert fires once per period. It cannot see requests turned away by a rate limit, and the schedule can run late, so an alert can arrive well after the event.
• It is a demo. Public data, a small daily budget, questions logged without IP addresses, and no promise it will be answering at any given moment. Answers can be wrong; check the SQL or the cited source.

Stack

A GitHub Actions pipeline runs the tests and the evaluation gate before it builds and deploys. Source on GitHub →