Calculating CMS Quality Measures as SQL on FHIR — No CQL Engine Required

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 (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 solves both.

Layer 1: flatten FHIR into tables with ViewDefinitions

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 expansions once into a concepts table — one row per (valueset_url, system, code) — and membership becomes an ordinary join:

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:

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.

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 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.

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 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 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.

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.

"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

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 — we would be glad to walk you through it.