adelelsayed1991/fhirsql-reasoning-sql
FHIR-to-SQL with Database-Resolved Clinical Terminology Natural-language hospital questions paired with a structured JSON query plan and compiled DuckDB SQL, over a FHIR-derived clinical schema. Built entirely from synthetic Synthea patients — no real patient data. Companion to the paper Plan-Then-Compile: Turning a General-Purpose Coder Model into a FHIR Data Analyst. Code & paper: https://github.com/adelelsayed/fhirsql-reasoning-sql Adapters:… See the full description on the dataset page: https://huggingface.co/datasets/adelelsayed1991/fhirsql-reasoning-sql.
0107
1-- FHIR-SQL fine-tuning study: frozen core clinical schema.
2--
3-- This is the benchmark-facing schema shown to models in every prompt (benchmark
4-- authoring, SFT prompt format, RL reward execution). It is a deliberately curated
5-- subset of the full flattened data -- see METHODOLOGY_LOG.md for the two-layer
6-- rationale (full fidelity in the database, curated scope in what models see) and
7-- the token-cost/scope reasoning.
8--
9-- Generated from the actual column types DuckDB inferred when loading
10-- data/train.duckdb, not hand-assumed -- see scripts/flatten_to_duckdb.py for
11-- the extraction logic that produces this shape.
12--
13-- Version stamp:
14-- Date frozen: 2026-08-02
15-- Synthea build: v3.4.0-18-ga07a65555 (git-describe string embedded in
16-- generated Patient resources; downloaded from the
17-- GitHub v4.0.0 release page -- see methodology log)
18-- Train population: 18,999 patients (target was ~15,000; see log for
19-- per-batch seed/state/age-bracket design)
20-- Held-out population: 6,383 patients (target was ~5,000)
21-- Populations verified disjoint: 0 patient_id overlap between train and held-out
22--
23-- Every number produced downstream (benchmark accuracy, cost tables, etc.) is
24-- relative to this artifact. Do not modify this file without a note on what
25-- changed and why.
26--
27-- Provider attribution: Synthea generates exactly one participant per encounter,
28-- typed "primary performer" only -- it does not distinguish admitting/attending/
29-- consulting roles, so those remain unanswerable regardless of schema design.
30-- Procedure.performer is never populated by this Synthea version (0/29,947 in a
31-- full batch) -- procedure-level provider attribution is not available at all.
32--
33-- Naming convention: columns are named to match FHIR element names directly,
34-- so a model's clinical-language understanding maps onto the schema with as
35-- little translation as possible:
36-- - Primary key: `id` (matches every FHIR resource's own `id` element).
37-- - Foreign keys: `patient_id`, `encounter_id` (SQL join-key convention;
38-- not itself a literal FHIR field name, since FHIR expresses this via
39-- subject/patient/encounter *reference* elements, but resolving those
40-- references to a flat join key needs a name, and `<type>_id` is the
41-- clearest SQL-side compromise).
42-- - Primary coding triple on each table: `code`, `system`, `display`
43-- (matches FHIR Coding.code/.system/.display exactly).
44-- - Where a resource's own field name differs from the generic "code"
45-- (Encounter.type, Encounter.class, Immunization.vaccineCode,
46-- CarePlan.category), the coding triple is prefixed with that field name
47-- instead: `type_code/type_system/type_display`, `class_code`,
48-- `vaccineCode/vaccineCode_system/vaccineCode_display`,
49-- `category_code/category_system/category_display`.
50-- - Status/descriptive fields: exact camelCase FHIR element names
51-- (clinicalStatus, verificationStatus, intent, criticality).
52-- - Dates: exact FHIR element names (birthDate, deceasedDateTime,
53-- onsetDateTime, abatementDateTime, recordedDate, effectiveDateTime,
54-- authoredOn, performedDateTime, occurrenceDateTime); Period-typed
55-- start/end kept as `period_start`/`period_end` (Period.start/.end).
56-- - value[x]: `valueQuantity`, `unit` (Quantity.unit), `valueCodeableConcept`
57-- (+ `valueCodeableConcept_system`), `valueString`.
58-- - Provider-reference columns match the FHIR field they were extracted
59-- from: `requester_npi`/`requester_name` on medication_request (from
60-- MedicationRequest.requester), `participant_npi`/`participant_name` on
61-- encounter (from Encounter.participant).
62-- - `race`/`ethnicity` on patient (US-Core extensions -- see
63-- scripts/flatten_to_duckdb.py's us_core_ext_text macro).
64-- - `imaging_study` (ImagingStudy resource) makes radiology-volume questions
65-- answerable (department-level radiology questions remain unanswerable --
66-- no department/service-line concept exists anywhere in Synthea's FHIR
67-- output).
68--
69-- Indexes: secondary (ART) indexes are added directly to train.duckdb and
70-- heldout.duckdb (not a change to this file -- no column/table/logical change,
71-- only a physical one) on the coding-triple columns (condition.code,
72-- observation.code, medication_request.code, encounter.class_code,
73-- encounter.type_code, procedure.code, immunization.vaccineCode, allergy.code,
74-- careplan.category_code, diagnostic_report.code, imaging_study.procedureCode,
75-- imaging_study.modality), to support the RL execution-efficiency reward term.
76-- patient_id/encounter_id deliberately NOT indexed -- DuckDB's ART index isn't
77-- used by the optimizer to accelerate joins, only point/highly-selective
78-- (<0.1% of rows) filters. See METHODOLOGY_LOG.md for the full verification
79-- history of this schema and its indexes.
80
81CREATE TABLE patient (
82 id VARCHAR PRIMARY KEY,
83 gender VARCHAR,
84 birthDate DATE,
85 deceasedDateTime TIMESTAMP,
86 maritalStatus VARCHAR,
87 state VARCHAR, -- address[0].state
88 city VARCHAR, -- address[0].city
89 postalCode VARCHAR, -- address[0].postalCode
90 race VARCHAR, -- US-Core race extension, ombCategory text
91 ethnicity VARCHAR -- US-Core ethnicity extension, ombCategory text
92);
93
94CREATE TABLE condition (
95 id VARCHAR PRIMARY KEY,
96 patient_id VARCHAR, -- join key -> patient.id
97 encounter_id VARCHAR, -- join key -> encounter.id
98 code VARCHAR,
99 system VARCHAR, -- kept alongside code deliberately: code-system confusion (SNOMED vs ICD-10 vs LOINC) is a failure mode to observe
100 display VARCHAR,
101 clinicalStatus VARCHAR,
102 verificationStatus VARCHAR,
103 onsetDateTime TIMESTAMP,
104 abatementDateTime TIMESTAMP,
105 recordedDate TIMESTAMP
106);
107
108CREATE TABLE observation (
109 id VARCHAR PRIMARY KEY,
110 patient_id VARCHAR, -- join key -> patient.id
111 encounter_id VARCHAR, -- join key -> encounter.id
112 code VARCHAR,
113 system VARCHAR,
114 display VARCHAR,
115 category VARCHAR,
116 status VARCHAR,
117 effectiveDateTime TIMESTAMP,
118 valueQuantity DOUBLE, -- value[x] flattened per plan design rule
119 unit VARCHAR,
120 valueCodeableConcept VARCHAR,
121 valueCodeableConcept_system VARCHAR, -- code-system pairing for the value itself, when value[x] is coded
122 valueString VARCHAR
123);
124
125CREATE TABLE medication_request (
126 id VARCHAR PRIMARY KEY,
127 patient_id VARCHAR, -- join key -> patient.id
128 encounter_id VARCHAR, -- join key -> encounter.id
129 code VARCHAR,
130 system VARCHAR,
131 display VARCHAR,
132 status VARCHAR,
133 intent VARCHAR,
134 authoredOn TIMESTAMP,
135 requester_npi VARCHAR, -- prescribing physician's NPI (from MedicationRequest.requester)
136 requester_name VARCHAR
137);
138
139CREATE TABLE encounter (
140 id VARCHAR PRIMARY KEY,
141 patient_id VARCHAR, -- join key -> patient.id
142 class_code VARCHAR, -- Encounter.class.code (AMB/EMER/IMP/HH/VR)
143 type_code VARCHAR, -- Encounter.type[0].coding[0]
144 type_system VARCHAR,
145 type_display VARCHAR,
146 status VARCHAR,
147 period_start TIMESTAMP,
148 period_end TIMESTAMP,
149 reasonCode VARCHAR,
150 participant_npi VARCHAR, -- primary-performer physician's NPI (Synthea models only one role per encounter, not admitting/attending/etc separately)
151 participant_name VARCHAR
152);
153
154CREATE TABLE procedure (
155 id VARCHAR PRIMARY KEY,
156 patient_id VARCHAR, -- join key -> patient.id
157 encounter_id VARCHAR, -- join key -> encounter.id
158 code VARCHAR,
159 system VARCHAR,
160 display VARCHAR,
161 status VARCHAR,
162 performedDateTime TIMESTAMP
163 -- Note: Procedure.performer (physician who performed it) is never populated
164 -- by this Synthea version (confirmed 0/29,947) -- not available at all.
165);
166
167CREATE TABLE immunization (
168 id VARCHAR PRIMARY KEY,
169 patient_id VARCHAR, -- join key -> patient.id
170 encounter_id VARCHAR, -- join key -> encounter.id
171 vaccineCode VARCHAR,
172 vaccineCode_system VARCHAR,
173 vaccineCode_display VARCHAR,
174 status VARCHAR,
175 occurrenceDateTime TIMESTAMP
176);
177
178CREATE TABLE allergy (
179 id VARCHAR PRIMARY KEY,
180 patient_id VARCHAR, -- join key -> patient.id
181 code VARCHAR,
182 system VARCHAR,
183 display VARCHAR,
184 clinicalStatus VARCHAR,
185 verificationStatus VARCHAR,
186 category VARCHAR,
187 criticality VARCHAR,
188 recordedDate TIMESTAMP
189);
190
191CREATE TABLE careplan (
192 id VARCHAR PRIMARY KEY,
193 patient_id VARCHAR, -- join key -> patient.id
194 encounter_id VARCHAR, -- join key -> encounter.id
195 category_code VARCHAR, -- CarePlan.category[0].coding[0]
196 category_system VARCHAR,
197 category_display VARCHAR,
198 status VARCHAR,
199 period_start TIMESTAMP,
200 period_end TIMESTAMP
201);
202
203CREATE TABLE diagnostic_report (
204 id VARCHAR PRIMARY KEY,
205 patient_id VARCHAR, -- join key -> patient.id
206 encounter_id VARCHAR, -- join key -> encounter.id
207 code VARCHAR,
208 system VARCHAR,
209 display VARCHAR,
210 category VARCHAR,
211 status VARCHAR,
212 effectiveDateTime TIMESTAMP
213);
214
215CREATE TABLE imaging_study (
216 id VARCHAR PRIMARY KEY,
217 patient_id VARCHAR, -- join key -> patient.id
218 encounter_id VARCHAR, -- join key -> encounter.id
219 status VARCHAR,
220 started TIMESTAMP,
221 numberOfSeries INTEGER,
222 numberOfInstances INTEGER,
223 procedureCode VARCHAR, -- the imaging procedure performed, e.g. "Plain X-ray of ankle region"
224 procedureCode_system VARCHAR,
225 procedureCode_display VARCHAR,
226 modality VARCHAR, -- DICOM modality code, e.g. "DX" = Digital Radiography (from series[0])
227 modality_system VARCHAR,
228 modality_display VARCHAR,
229 bodySite VARCHAR, -- from series[0]
230 bodySite_display VARCHAR
231);
232