Run reports, dashboards, and ML feature pipelines in plain SQL — no ETL, no Spark, no separate warehouse. SQL-on-FHIR v2 ViewDefinitions materialize flat, queryable tables that stay fresh on commit, with row- and column-level access control on every query.
Define a SQL-on-FHIR v2 ViewDefinition that flattens the resources you care about into a flat table, and query that table in plain SQL. bonfireDB materializes the view in Postgres and keeps it fresh on commit — so dashboards, BI, and ML feature pipelines read live data with no Spark, no nightly ETL, and no separate warehouse.
A ViewDefinition turns nested FHIR into named columns — patient_id, phq9_score, recorded_at — that a SQL engine and a BI tool already understand.
Views are incrementally maintained inside the write transaction. When a note signs or a score lands, the view reflects it — no batch job to wait on.
The same policy that gates clinical reads applies to analytics: row-level by patient and tenant, column-level by minimum-necessary role.
A FHIR resource is a deeply nested document with repeating arrays, references to other resources, and choice-of-type fields. The moment you flatten it for analytics, the structure fights back.
So teams either write fragile flattening SQL by hand or stand up a parallel analytics stack. Both are wrong jobs for a four-person team shipping a product.
// the value you want is buried… { "resourceType": "Observation", "code": { "coding": [ { "system": "http://loinc.org", "code": "44261-6" } ] }, "valueQuantity": { "value": 14 }, "subject": { "reference": "Patient/123" } } // …so the query becomes JSON archaeology SELECT resource->'valueQuantity'->>'value' FROM fhir WHERE resource#>>'{code,coding,0,code}' = '44261-6' // repeat for every metric, pray the path holds
SQL-on-FHIR v2 ViewDefinitions are an HL7® standard: you declare which resource to flatten, which rows to keep, and which columns to project — using FHIRPath. bonfireDB compiles that spec into a materialized Postgres view. Write it once; query it like any table.
export const phq9View = defineView({ name: "phq9_scores", resource: "Observation", // keep only the rows we care about — no Cartesian blowup where: [{ path: "code.coding.where(system='http://loinc.org' and code='44261-6').exists()" }], // project nested FHIR into flat, typed columns select: [{ column: [ { name: "patient_id", path: "subject.getReferenceKey('Patient')" }, { name: "phq9_score", path: "valueQuantity.value", type: "decimal" }, { name: "recorded_at", path: "effectiveDateTime", type: "dateTime" }, ], }], // kept fresh on commit via incremental view maintenance refresh: "on-commit", });
The where clause filters before projection, so repeating arrays never multiply your rows. The spec is the standard's portable JSON — the same ViewDefinition runs on any conformant SQL-on-FHIR engine.
Once the view is materialized, phq9_scores is an ordinary Postgres table. Aggregate it, window over it, join it to your other views — with the SQL you already know and the BI tools you already use.
No $export to a bucket, no Spark cluster, no warehouse sync. The analytics surface lives inside the same store as the operational data.
-- mean PHQ-9 change per clinician, last 90 days SELECT c.clinician_id, count(DISTINCT p.patient_id) AS patients, avg(p.last_score - p.first_score) AS mean_change FROM ( SELECT patient_id, first_value(phq9_score) OVER w AS first_score, last_value(phq9_score) OVER w AS last_score FROM phq9_scores WHERE recorded_at > now() - '90 days'::interval WINDOW w AS (PARTITION BY patient_id ORDER BY recorded_at) ) p JOIN assignments_view c USING (patient_id) GROUP BY c.clinician_id;
There is no copy step, no scheduler, no second datastore. The view is part of the database that already holds your FHIR.
Write a SQL-on-FHIR v2 ViewDefinition declaring the resource, the filter, and the flat columns you want.
bonfire compiles it into a Postgres materialized view, indexed and typed — no Spark, no external job.
Incremental view maintenance updates the view inside the write transaction, so it's fresh on commit.
Run SQL — or point a BI tool or notebook at it. Every query is filtered by the access policy automatically.
A warehouse export is where authorization usually dies — once FHIR lands in a flat table, the patient scoping and minimum-necessary rules are gone. In bonfire, the policy follows the data into the view: row-level by patient and tenant, column-level by role.
Pairs with clinical authorization & audit — the policy is defined once and enforced on the analytics surface too. Every analytics query is auditable like any other read.
clinical.views.policy("phq9_scores", { // row-level: scope every query to the caller's patients + tenant rowFilter: (ctx) => ({ tenant_id: ctx.orgId, patient_id: clinical.assignments.visibleTo(ctx.userId), }), // column-level: which columns each role may project columns: { front_desk: ["patient_id", "recorded_at"], clinician: ["patient_id", "recorded_at", "phq9_score"], }, }); // the same SELECT returns different rows + columns per caller await clinical.sql(`SELECT * FROM phq9_scores`, ctx);
SQL-on-FHIR isn't new — Aidbox and Pathling each ship pieces of it. What no one ships is all three at once: views that stay fresh on commit, are authorized row- and column-level per query, and need no separate Spark or warehouse. That combination is the moat.
| Capability | HealthLake + Athena | Pathling (Spark) | Aidbox | bonfireDB |
|---|---|---|---|---|
| SQL-on-FHIR v2 ViewDefinitions | No (raw $export) | Yes | Yes | Yes |
| No separate Spark / warehouse | Glue + Athena | Needs Spark cluster | In-DB | In-Postgres, no Spark |
| Fresh on commit (incremental) | Batch $export | Batch / re-extract | Refresh-driven | Maintained in the write txn |
| Row-level access on the view | IAM, not patient-scoped | Your job | Configurable | Per-patient / per-tenant |
| Column-level (minimum-necessary) | Manual | Manual | Configurable | Role-scoped, built in |
| Pre-seed / indie footprint | Heavy AWS stack | JVM + Spark ops | Server to run | one SDK, Postgres-first |
bonfireDB is early-stage; this page describes product design and positioning. Comparisons reflect each system's stated SQL-on-FHIR and access-control model, not a benchmark. SQL-on-FHIR and FHIR are HL7® specifications; "FHIR" is used descriptively. See the full comparisons for where dedicated FHIR servers and analytics engines fit.
Incremental view maintenance is the same engine that keeps operational reads fresh on commit.
Explore →Views compile from the FHIR R4 stored beneath your typed clinical functions.
Explore →Declare a ViewDefinition, query flat SQL, ship the dashboard. No ETL, no Spark, no separate warehouse — and authorization the warehouse export would have lost.
Define a SQL-on-FHIR v2 ViewDefinition that flattens the FHIR resources you care about into named columns, and query that as a plain Postgres table. bonfireDB is designed to materialize the view in-database and keep it fresh on commit, so there's no Spark, no nightly ETL, and no separate warehouse to copy data into.
A ViewDefinition is an HL7 SQL-on-FHIR v2 standard: a declarative JSON spec that says which resource to flatten, which rows to keep, and which columns to project using FHIRPath. It's portable across conformant engines. bonfireDB compiles it into a materialized Postgres view you query like any table.
FHIR is a nested graph, so querying it directly means fragile JSON traversal and Cartesian row explosions from repeating arrays. bonfireDB's approach keeps Postgres as the source of truth, with FHIR R4 generated underneath, and projects flat tables via ViewDefinitions via ViewDefinitions so you analyze with ordinary SQL, joins, and window functions.
No. The common pattern is a Spark/Pathling ETL job that copies FHIR into a separate warehouse on a schedule, adding latency, a second system, and a second BAA. bonfireDB is designed to materialize views inside the same Postgres store, incrementally maintained in the write transaction — no Spark cluster and no batch export.
Yes, by design. A warehouse export usually loses patient scoping, but bonfireDB applies the same policy to views as to clinical reads: row-level by patient and tenant, column-level by minimum-necessary role. The same SELECT returns different rows and columns per caller, and every query is auditable.