adelelsayed1991/fhirsql-reasoning-sql-adapters
0
1-- FHIR-SQL fine-tuning study: frozen core clinical schema (Phase 0.3)2--3-- This is the benchmark-facing schema shown to models in every prompt (Phase 14-- benchmark authoring, Phase 4 SFT prompt format, Phase 5 RL reward execution).5-- It is a deliberately curated subset of the full flattened data -- see6-- METHODOLOGY_LOG.md for the two-layer rationale (full fidelity in the7-- database, curated scope in what models see) and the token-cost/scope reasoning.8--9-- Generated from the actual column types DuckDB inferred when loading10-- data/train.duckdb, not hand-assumed -- see scripts/flatten_to_duckdb.py for11-- the extraction logic that produces this shape.12--13-- Version stamp:14-- Date frozen: 2026-08-0215-- Synthea build: v3.4.0-18-ga07a65555 (git-describe string embedded in16-- generated Patient resources; downloaded from the17-- GitHub v4.0.0 release page -- see methodology log)18-- Train population: 18,999 patients (target was ~15,000; see log for19-- 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-out22--23-- Every number produced downstream (benchmark accuracy, cost tables, etc.) is24-- relative to this artifact. Do not modify this file without a new version25-- stamp and a note on what changed and why.26--27-- Revision 2026-08-02b: added practitioner_npi/practitioner_name to encounter28-- and medication_request (superseded by the rename in 2026-08-03, see below).29-- Root cause: Synthea DOES generate provider attribution30-- (Encounter.participant, MedicationRequest.requester), it was simply not31-- extracted in the initial flatten pass -- confirmed by inspecting raw NDJSON32-- directly, not assumed. Note: Synthea always generates exactly one33-- participant per encounter, typed "primary performer" only -- it does not34-- distinguish admitting/attending/consulting roles, so those remain35-- unanswerable regardless of schema design. Procedure.performer is never36-- populated by this Synthea version (confirmed: 0/29,947 in a full batch) --37-- procedure-level provider attribution is not available at all.38--39-- Revision 2026-08-03: FULL COLUMN RENAME to align with FHIR element names,40-- per explicit user request ("ensure llms have an easier task" mapping41-- clinical-language understanding to schema). Every core table's gold SQL42-- (2,514 unique statements) and all 10,056 training rows were regenerated43-- from scratch to match -- this was a deliberate, acknowledged-cost decision,44-- not an incremental patch. Naming convention adopted:45-- - Primary key: `id` (matches every FHIR resource's own `id` element).46-- - Foreign keys: `patient_id`, `encounter_id` (SQL join-key convention;47-- not itself a literal FHIR field name, since FHIR expresses this via48-- subject/patient/encounter *reference* elements, but resolving those49-- references to a flat join key needs a name, and `<type>_id` is the50-- clearest SQL-side compromise).51-- - Primary coding triple on each table: `code`, `system`, `display`52-- (matches FHIR Coding.code/.system/.display exactly).53-- - Where a resource's own field name differs from the generic "code"54-- (Encounter.type, Encounter.class, Immunization.vaccineCode,55-- CarePlan.category), the coding triple is prefixed with that field name56-- instead: `type_code/type_system/type_display`, `class_code`,57-- `vaccineCode/vaccineCode_system/vaccineCode_display`,58-- `category_code/category_system/category_display`.59-- - Status/descriptive fields: exact camelCase FHIR element names60-- (clinicalStatus, verificationStatus, intent, criticality).61-- - Dates: exact FHIR element names (birthDate, deceasedDateTime,62-- onsetDateTime, abatementDateTime, recordedDate, effectiveDateTime,63-- authoredOn, performedDateTime, occurrenceDateTime); Period-typed64-- start/end kept as `period_start`/`period_end` (Period.start/.end).65-- - value[x]: `valueQuantity`, `unit` (Quantity.unit), `valueCodeableConcept`66-- (+ `valueCodeableConcept_system`), `valueString`.67-- - Provider-reference columns renamed to match the FHIR field they were68-- extracted from: `requester_npi`/`requester_name` on medication_request69-- (from MedicationRequest.requester), `participant_npi`/`participant_name`70-- on encounter (from Encounter.participant).71-- - New: `race`/`ethnicity` on patient (US-Core extensions, previously72-- deferred as "untested SQL," now implemented and tested -- see73-- scripts/flatten_to_duckdb.py's us_core_ext_text macro).74-- - New table: `imaging_study` (ImagingStudy resource), promoted from the75-- non-benchmark-facing extra_ tables per explicit user request, to make76-- radiology-volume questions answerable (department-level radiology77-- questions remain unanswerable -- no department/service-line concept78-- exists anywhere in Synthea's FHIR output).79--80-- Revision 2026-08-05: secondary (ART) indexes added directly to train.duckdb and81-- heldout.duckdb (not a change to this file -- no column/table/logical change, only a82-- physical one) on the coding-triple columns (condition.code, observation.code,83-- medication_request.code, encounter.class_code, encounter.type_code, procedure.code,84-- immunization.vaccineCode, allergy.code, careplan.category_code, diagnostic_report.code,85-- imaging_study.procedureCode, imaging_study.modality), to support Phase 5's execution-86-- efficiency reward term. patient_id/encounter_id deliberately NOT indexed -- DuckDB's ART87-- index isn't used by the optimizer to accelerate joins, only point/highly-selective88-- (<0.1% of rows) filters, confirmed before deciding what to index. Re-verified all 2,70789-- unique gold SQL statements return identical results before/after on train.duckdb (2490-- structural smoke-tests on heldout.duckdb, which has no gold SQL of its own) -- see91-- METHODOLOGY_LOG.md, "Reinforcement learning setup" for the two real issues that92-- re-verification surfaced (a latent NULL-sort bug in the eval/reward comparison helper,93-- now fixed; non-deterministic tie-breaking in 7 archetype templates, not yet fixed).94 95CREATE TABLE patient (96 id VARCHAR PRIMARY KEY,97 gender VARCHAR,98 birthDate DATE,99 deceasedDateTime TIMESTAMP,100 maritalStatus VARCHAR,101 state VARCHAR, -- address[0].state102 city VARCHAR, -- address[0].city103 postalCode VARCHAR, -- address[0].postalCode104 race VARCHAR, -- US-Core race extension, ombCategory text105 ethnicity VARCHAR -- US-Core ethnicity extension, ombCategory text106);107 108CREATE TABLE condition (109 id VARCHAR PRIMARY KEY,110 patient_id VARCHAR, -- join key -> patient.id111 encounter_id VARCHAR, -- join key -> encounter.id112 code VARCHAR,113 system VARCHAR, -- kept alongside code deliberately: code-system confusion (SNOMED vs ICD-10 vs LOINC) is a failure mode to observe114 display VARCHAR,115 clinicalStatus VARCHAR,116 verificationStatus VARCHAR,117 onsetDateTime TIMESTAMP,118 abatementDateTime TIMESTAMP,119 recordedDate TIMESTAMP120);121 122CREATE TABLE observation (123 id VARCHAR PRIMARY KEY,124 patient_id VARCHAR, -- join key -> patient.id125 encounter_id VARCHAR, -- join key -> encounter.id126 code VARCHAR,127 system VARCHAR,128 display VARCHAR,129 category VARCHAR,130 status VARCHAR,131 effectiveDateTime TIMESTAMP,132 valueQuantity DOUBLE, -- value[x] flattened per plan design rule133 unit VARCHAR,134 valueCodeableConcept VARCHAR,135 valueCodeableConcept_system VARCHAR, -- code-system pairing for the value itself, when value[x] is coded136 valueString VARCHAR137);138 139CREATE TABLE medication_request (140 id VARCHAR PRIMARY KEY,141 patient_id VARCHAR, -- join key -> patient.id142 encounter_id VARCHAR, -- join key -> encounter.id143 code VARCHAR,144 system VARCHAR,145 display VARCHAR,146 status VARCHAR,147 intent VARCHAR,148 authoredOn TIMESTAMP,149 requester_npi VARCHAR, -- prescribing physician's NPI (from MedicationRequest.requester)150 requester_name VARCHAR151);152 153CREATE TABLE encounter (154 id VARCHAR PRIMARY KEY,155 patient_id VARCHAR, -- join key -> patient.id156 class_code VARCHAR, -- Encounter.class.code (AMB/EMER/IMP/HH/VR)157 type_code VARCHAR, -- Encounter.type[0].coding[0]158 type_system VARCHAR,159 type_display VARCHAR,160 status VARCHAR,161 period_start TIMESTAMP,162 period_end TIMESTAMP,163 reasonCode VARCHAR,164 participant_npi VARCHAR, -- primary-performer physician's NPI (Synthea models only one role per encounter, not admitting/attending/etc separately)165 participant_name VARCHAR166);167 168CREATE TABLE procedure (169 id VARCHAR PRIMARY KEY,170 patient_id VARCHAR, -- join key -> patient.id171 encounter_id VARCHAR, -- join key -> encounter.id172 code VARCHAR,173 system VARCHAR,174 display VARCHAR,175 status VARCHAR,176 performedDateTime TIMESTAMP177 -- Note: Procedure.performer (physician who performed it) is never populated178 -- by this Synthea version (confirmed 0/29,947) -- not available at all.179);180 181CREATE TABLE immunization (182 id VARCHAR PRIMARY KEY,183 patient_id VARCHAR, -- join key -> patient.id184 encounter_id VARCHAR, -- join key -> encounter.id185 vaccineCode VARCHAR,186 vaccineCode_system VARCHAR,187 vaccineCode_display VARCHAR,188 status VARCHAR,189 occurrenceDateTime TIMESTAMP190);191 192CREATE TABLE allergy (193 id VARCHAR PRIMARY KEY,194 patient_id VARCHAR, -- join key -> patient.id195 code VARCHAR,196 system VARCHAR,197 display VARCHAR,198 clinicalStatus VARCHAR,199 verificationStatus VARCHAR,200 category VARCHAR,201 criticality VARCHAR,202 recordedDate TIMESTAMP203);204 205CREATE TABLE careplan (206 id VARCHAR PRIMARY KEY,207 patient_id VARCHAR, -- join key -> patient.id208 encounter_id VARCHAR, -- join key -> encounter.id209 category_code VARCHAR, -- CarePlan.category[0].coding[0]210 category_system VARCHAR,211 category_display VARCHAR,212 status VARCHAR,213 period_start TIMESTAMP,214 period_end TIMESTAMP215);216 217CREATE TABLE diagnostic_report (218 id VARCHAR PRIMARY KEY,219 patient_id VARCHAR, -- join key -> patient.id220 encounter_id VARCHAR, -- join key -> encounter.id221 code VARCHAR,222 system VARCHAR,223 display VARCHAR,224 category VARCHAR,225 status VARCHAR,226 effectiveDateTime TIMESTAMP227);228 229CREATE TABLE imaging_study (230 id VARCHAR PRIMARY KEY,231 patient_id VARCHAR, -- join key -> patient.id232 encounter_id VARCHAR, -- join key -> encounter.id233 status VARCHAR,234 started TIMESTAMP,235 numberOfSeries INTEGER,236 numberOfInstances INTEGER,237 procedureCode VARCHAR, -- the imaging procedure performed, e.g. "Plain X-ray of ankle region"238 procedureCode_system VARCHAR,239 procedureCode_display VARCHAR,240 modality VARCHAR, -- DICOM modality code, e.g. "DX" = Digital Radiography (from series[0])241 modality_system VARCHAR,242 modality_display VARCHAR,243 bodySite VARCHAR, -- from series[0]244 bodySite_display VARCHAR245);246