---
{
"title": "Calculating CMS Quality Measures as SQL on FHIR — No CQL Engine Required",
"description": "Run CMS/HEDIS eCQMs as SQL on FHIR — no CQL engine. ViewDefinitions and SQLQuery Libraries pack into one FHIR package you can move to any SQL-on-FHIR server, making measure calculation portable and standard.",
"date": "2026-07-20",
"author": "Aleksandr Kislitsyn",
"reading-time": "11 min read",
"tags": [
"SQL on FHIR",
"Analytics",
"Compliance"
],
"seo-tags": [
"SQL on FHIR",
"Quality Measures",
"eCQM"
],
"tldr": "CQL-authored CMS quality measures can be executed as plain SQL on Aidbox/PostgreSQL via SQL on FHIR — no CQL engine at runtime. ViewDefinitions flatten FHIR into tables, ValueSet membership becomes a JOIN, and each measure is one SQL query packaged as a SQLQuery Library and served through the standard Measure/$evaluate-measure API. A full working example (a dozen CMS measures) is on GitHub.",
"utm-campaign": "analytics",
"utm-content": "cms-measures-sql"
}
---
> For the complete documentation index, see [llms.txt](https://www.health-samurai.io/llms.txt).
> Use it to discover all available pages before guessing URLs.
---
## The problem with running eCQMs
Electronic Clinical Quality Measures (eCQMs) are how programs measure care quality — whether eligible patients got their colorectal screening, whether their hypertension is controlled. The CMS and HEDIS measures every value-based-care program reports on are authored in [CQL](https://cql.hl7.org/) (Clinical Quality Language), and the usual way to execute them is a dedicated **CQL engine**: a separate runtime that parses the measure, calls back into your FHIR server for terminology and patient data, and returns a `MeasureReport`.
This works well, but at a cost:
- **A separate compute tier.** A CQL engine is its own runtime — you deploy and scale it alongside your FHIR server, and it does the population computation outside the database. That is real infrastructure, and a scaling concern for large populations.
- **Hard to explain.** When a patient shows up as a care gap, can you show a clinician exactly *why*? That means tracing engine internals and value-set expansions — not reading a query.
So here is the question this post answers: **what if the measure logic were just SQL, running where your data already lives?**
It turns out it can be — and the result is more than a performance trick: with SQL on FHIR, the entire knowledge of how to calculate a measure becomes **portable, standard FHIR artifacts** you can pack into a FHIR package and install on another server. This is an engineering walkthrough of how, with a full working example you can clone and run.
## The idea: measures are set logic, and SQL is a set language
A quality measure is fundamentally set arithmetic over a patient population:
- **Initial Population** — who is eligible (age, encounters, a condition).
- **Denominator / Exclusions** — who is counted, minus who is carved out (hospice, palliative care, frailty…).
- **Numerator** — who met the measure (a screening, a controlled reading).
- **Score** — `numerator / (denominator − exclusions)`.
Every one of those is a set of patients. SQL is very good at sets. The only thing standing between FHIR data and a SQL query is that FHIR resources are deeply nested JSON, and terminology membership ("is this code in the value set?") is not a column you can filter on. [SQL on FHIR](https://www.health-samurai.io/docs/aidbox/modules/sql-on-fhir) solves both.
```mermaid
flowchart TD
A["Aidbox — FHIR JSONB"] -->|ViewDefinition +
$materialize| B["sof.* flat tables
patient, encounter, condition, observation…"]
C["ValueSet expansions"] -->|flatten| D["concepts table
(valueset_url, system, code)"]
B --> E["Measure SQL (CTEs)
IP → exclusions → numerator"]
D --> E
E --> F["MeasureReport"]
```
## Layer 1: flatten FHIR into tables with ViewDefinitions
A [ViewDefinition](/blog/what-is-a-viewdefinition) is a FHIR resource that describes how to flatten a resource type into a flat table. Point one at `Encounter`, materialize it, and you get a tidy `sof.encounter_flat` table with `patient_id`, `type_system`, `type_code`, `status`, `period_start`, and so on — your measure logic reads plain columns instead of digging through nested FHIR structures.
The example ships one ViewDefinition per resource type the measures touch — patient, encounter, condition, observation, procedure, and a few more — each materialized into the `sof.*` schema and exposed through a thin wrapper view. That shared flat layer is reused by every measure.
## Layer 2: terminology as a JOIN, not an $expand
The other hard part of a measure is code-set membership: "is this encounter one of the seven kinds of qualifying visit?" In a CQL-engine setup that is typically resolved at runtime against a terminology service. In SQL on FHIR you flatten the [ValueSet](https://www.health-samurai.io/docs/aidbox/terminology-module/fhir-terminology/valueset) expansions **once** into a `concepts` table — one row per `(valueset_url, system, code)` — and membership becomes an ordinary join:
```sql
JOIN concepts c
ON c.system = e.type_system
AND c.code = e.type_code
AND c.valueset_url = 'http://cts.nlm.nih.gov/fhir/ValueSet/…'
```
No network round-trip, no per-patient expansion. The value set is expanded offline and indexed, so the "does this patient's code count?" check is set-based and fast.
## Layer 3: the measure is one SQL query
With flat tables and a `concepts` join available, a whole measure becomes a single query made of CTEs — and it reads remarkably close to the measure's plain-English definition. Here is the Initial Population of **CMS130 (Colorectal Cancer Screening)** — patients aged 46–75 with a qualifying encounter during the measurement period:
```sql
WITH mp AS (
SELECT '2026-01-01'::timestamptz AS mp_start,
'2026-12-31'::timestamptz AS mp_end
),
qualifying_encounters AS (
SELECT DISTINCT e.patient_id
FROM encounter_flat e
JOIN concepts c
ON c.system = e.type_system
AND c.code = e.type_code
AND c.valueset_url IN (
-- ValueSet URLs shortened for readability; full URLs in the example repo
'http://cts.nlm.nih.gov/fhir/ValueSet/…', -- Office Visit
'http://cts.nlm.nih.gov/fhir/ValueSet/…', -- Annual Wellness Visit
'…' -- + 5 more
)
CROSS JOIN mp
WHERE e.status = 'finished'
AND e.period_start BETWEEN mp.mp_start AND mp.mp_end
),
initial_population AS (
SELECT p.id AS patient_id
FROM patient_flat p
CROSS JOIN mp
WHERE EXTRACT(YEAR FROM AGE(mp.mp_end, p.birth_date::date)) BETWEEN 46 AND 75
AND p.id IN (SELECT patient_id FROM qualifying_encounters)
)
-- … denominator exclusions, numerator, and the final score follow as more CTEs
```
The numerator adds a few more CTEs (colonoscopy within 9 years, FOBT during the period, and so on), exclusions union hospice/palliative/frailty, and a final `SELECT` computes the score. The point: **the measure is legible.** You can read it, diff it against the spec, and `SELECT * FROM initial_population` to see exactly who qualified.
### Shared logic stays shared
Exclusions like hospice, palliative care, and advanced-illness-with-frailty recur across many measures. In the example they are factored out into reusable pieces rather than copy-pasted, so fixing the hospice logic fixes it everywhere at once.
### But who writes all this SQL?
The obvious objection: hand-translating a measure's CQL into SQL sounds like a lot of careful, error-prone work — and there are dozens of eCQMs. In practice, this is exactly the kind of task modern AI coding assistants handle well. CQL and SQL are both structured, well-specified languages, and a measure's logic maps cleanly onto the CTE pattern shown above (initial population → exclusions → numerator → score).
That is not a hope — it is how the example itself was built. Each measure was translated from its published CQL to SQL with an AI assistant, then **verified against CMS's reference `MeasureReport` fixtures patient-by-patient** until the numbers matched exactly. The AI does the mechanical translation; the fixtures keep it honest.
{% hint style="info" %}
The reference fixtures are what make AI-assisted translation trustworthy: expected `MeasureReport` results for the test patients are published alongside the measure content in the [dqm-content-qicore-2025](https://github.com/cqframework/dqm-content-qicore-2025) repository, so every translated measure is checkable against ground truth — not accepted on faith.
{% endhint %}
## Making it standards-conformant: SQLQuery + Measure/$evaluate-measure
Running SQL is fine internally, but the goal is a conformant FHIR service, not a database script. Two SQL-on-FHIR pieces close that gap:
- Each measure's SQL is stored in Aidbox as a **SQLQuery `Library`** resource (a SQL-on-FHIR profile on `Library`) and invoked with the `$sqlquery-run` operation. The calculation logic lives *in the FHIR server*, as first-class resources — not in application code.
- The service answers the standard FHIR R4 [`Measure/$evaluate-measure`](https://hl7.org/fhir/R4/operation-measure-evaluate-measure.html) operation and returns a proper `MeasureReport` — so any FHIR client consumes it the same way it would a CQL-engine result.
Each SQLQuery Library also declares `depends-on` lineage to the ViewDefinitions it reads, giving you a queryable graph: **measure → views → resources.** Everything a measure needs — the flat views, the terminology, the calculation — is now expressed as standard FHIR resources living in the FHIR server. Which sets up the real payoff.
```mermaid
flowchart LR
Client -->|"POST /Measure/
$evaluate-measure"| Aidbox
Aidbox -->|routes to| App["evaluate-measure app"]
App -->|"$sqlquery-run"| Lib["SQLQuery Library
(the measure SQL)"]
Lib -->|reads| SOF["sof.* views + concepts"]
App -->|builds| MR["MeasureReport
→ back to the client"]
```
The evaluate-measure app in the middle holds no measure logic: it resolves the right SQLQuery Library, invokes `$sqlquery-run`, and shapes the returned rows into a `MeasureReport`. The computation itself runs in the database.
## The payoff: the whole measure suite is a portable FHIR package
Because every part of a measure is now a standard FHIR resource, the entire suite packs into **one FHIR NPM package**: terminology (CodeSystems + ValueSets), the ViewDefinitions that flatten the data, and the SQLQuery Libraries that hold the calculation logic. In the example that package carries 170 resources across a dozen measures — 10 CodeSystems, 107 ValueSets, 10 ViewDefinitions, and 43 SQLQuery Libraries.
The package format is standard — the same FHIR NPM format Implementation Guides ship in — so any FHIR server can load it with its own package-install mechanism; in Aidbox that is a [`$fhir-package-install`](https://www.health-samurai.io/docs/aidbox/reference/package-registry-api#fhir-package-install) call at server boot, and the definitions are simply *there*. And here is the part that matters: **that package is not tied to Aidbox.** It is standard SQL-on-FHIR building blocks — ViewDefinitions and SQLQuery Libraries defined by the [SQL on FHIR](https://build.fhir.org/ig/HL7/sql-on-fhir/) specification. Move the package to any FHIR server that implements these building blocks, install it, and the same measures compute the same way. How each server executes them is an implementation detail — some run a separate SQL-on-FHIR engine, others execute in-database.
```mermaid
flowchart LR
Pkg["FHIR package
terminology + ViewDefinitions + SQLQuery Libraries"]
Pkg -->|"package install"| S1["FHIR server A
(SQL on FHIR)"]
Pkg -->|"package install"| S2["FHIR server B
(SQL on FHIR)"]
Pkg -->|"package install"| S3["…any SQL-on-FHIR server"]
```
Aidbox takes the in-database path: the ViewDefinitions materialize into tables or views and the SQLQuery Libraries execute as native SQL, **directly in its PostgreSQL** — right where the data already lives. So the package installs and runs with nothing extra to stand up for the computation itself: no execution engine to deploy, scale, or keep in sync. The measure logic distributes the same way an Implementation Guide does — as standard, shareable FHIR artifacts.
## Investigating a result
Remember the "hard to explain" problem? It gets a direct answer here. Ask for a single patient, and the `MeasureReport` carries an `evaluatedResource` array: real FHIR resource references, each tagged with the population it satisfies via the standard `cqf-criteriaReference` extension.
```json
"evaluatedResource": [
{
"reference": "Encounter/abc",
"extension": [
{ "url": ".../cqf-criteriaReference", "valueString": "initial-population" },
{ "url": ".../cqf-criteriaReference", "valueString": "denominator" }
]
},
{
"reference": "Procedure/xyz",
"extension": [
{ "url": ".../cqf-criteriaReference", "valueString": "numerator" }
]
}
]
```
"Why is this patient in the numerator?" — *this* Procedure. "Why excluded?" — *this* hospice encounter. Every reference resolves to a resource that exists in the server, so the next step of the investigation is just a `GET`.
For population-scale investigation, each measure also ships an `-evidence` SQLQuery Library: one row per patient with the full decision chain — which pathway satisfied the numerator (colonoscopy vs. FOBT vs. none), the triggering resource with its code and date, and which exclusion fired. That is a care-gap worklist — *who is missing screening, and what exactly is missing* — as a single query. The example's demo app is these queries made clickable: a cross-measure worklist, a per-patient 360 view with evidence drill-down, and exportable outreach lists.
And when a number still looks wrong, every population is a named CTE: `SELECT * FROM initial_population` and narrow it down step by step — no engine internals to trace.
## Run it yourself
Everything above is a working, open-source example in the Aidbox examples repository — a dozen CMS measures (CMS130, CMS165, CMS125, CMS131, and more) with sample patient data and an interactive demo app.
**→ [github.com/Aidbox/examples · aidbox-custom-operations/measure-evaluate](https://github.com/Aidbox/examples/tree/main/aidbox-custom-operations/measure-evaluate)**
Follow the instructions in the README to run the whole stack locally: start Aidbox with the measure package installed at boot, load the sample dataset, calculate the measures through the standard `Measure/$evaluate-measure` operation, and explore each patient's evidence in the demo app UI. What comes back is a plain FHIR `MeasureReport` with the population counts and score — computed by SQL, inside Aidbox, with no CQL engine anywhere in the stack.
## Takeaway
CQL is a fine authoring language for quality measures. But it does not have to be your *execution* engine. When you flatten FHIR with ViewDefinitions, turn terminology into a join, and express each measure as a SQLQuery Library behind `Measure/$evaluate-measure`, the measure logic stops being locked inside an engine and becomes what SQL on FHIR promises: **standard FHIR artifacts you can package once and run anywhere.** Ship the whole suite as a FHIR package, install it on any SQL-on-FHIR server, and the same measures compute the same way — explainable down to the individual resource. On Aidbox, they run natively in PostgreSQL, so there is no separate execution engine to operate at all. And getting there is more approachable than it sounds: AI assistants translate the CQL to SQL, and CMS's own reference fixtures verify every measure against expected results.
> Interested in trying this measure-calculation approach on your own data? [Reach out to us](https://www.health-samurai.io/contacts?utm_source=article&utm_medium=blog&utm_campaign=cms-measures-sql) — we would be glad to walk you through it.