CLINSQL: Clinical Text-to-SQL Systems

• CLINSQL is a clinical text-to-SQL paradigm that translates complex natural language queries into SQL using schema-aware decomposition and domain-specific benchmarks.
• It employs modular pipelines with retrieval-augmented generation and iterative feedback loops to ensure accurate and clinically plausible query outputs.
• Comprehensive evaluation metrics like ExecutionScore and AST similarity underscore its robustness while highlighting areas for continuous self-improvement.

CLINSQL refers to both a family of advanced clinical text-to-SQL systems and a set of benchmarks and methodologies that formalize and evaluate the process of mapping complex, clinically grounded natural language (NL) queries into executable SQL over heterogeneous biomedical databases. The CLINSQL paradigm encompasses schema-aware decomposition, dense retrieval from domain-specific exemplars, modular neural and neuro-symbolic reasoning, iterative self-improving feedback loops, and auditable intermediate representations. This approach enables clinicians, researchers, and intelligent agents to formulate, audit, and refine expressive, multi-constraint, and cohort-centric queries over electronic health records (EHR), pathology features, and oncology trial databases.

1. Conceptual and Benchmark Foundations

CLINSQL crystallizes the requirements for real-world clinical NL-to-SQL through benchmark design and task formalization. In "Patient-Similarity Cohort Reasoning in Clinical Text-to-SQL," Wang et al. define the CLINSQL benchmark: a suite of 633 hand-annotated NL-to-SQL tasks spanning six clinical scenarios on the MIMIC-IV v3.1 database, designed explicitly to tax three core competencies—multi-table joins over complex EHR schemas, fine-grained temporal logic, and data-driven cohort construction based on patient-similarity strata (age, gender, ICD-9/10 phenotypes, risk scores) (Shen et al., 14 Jan 2026).

The benchmark structure enforces realistic schema navigation, precise coding-system mappings, and the encoding of nuanced clinical logic (insurance flags, outcome time-windows, therapy classes, etc.) within deeply nested SQL. Evaluation is rubric-based, scoring queries across cohort construction, concept implementation, schema integration, and analytics execution. ExecutionScore, the primary metric, measures directly the fraction of model-generated queries that execute correctly on live databases and produce clinically plausible results.

2. System Architectures and Modular Pipelines

CLINSQL systems adopt modular, pipelined architectures to maximize robustness and flexibility across clinical applications. "Generating patient cohorts from electronic health records using two-step retrieval-augmented text-to-SQL generation" details a five-stage pipeline for OMOP-CDM EHRs (Ziletti et al., 28 Feb 2025):

• Criteria Parsing: LLMs convert free-text eligibility documents into structured JSON of inclusions, exclusions, and index-date rules, segmenting and de-ambiguating complex criteria.
• Retrieval-Augmented Generation (RAG+A and RAG+C): Criteria and full-cohort embeddings (e.g., bge-large-en-v1.5) retrieve top-k similar question–SQL and cohort–SQL pairs from curated knowledge bases (EpiAskKB, EpiCohoKB) using cosine similarity for in-context learning.
• LLM-based SQL Generation: LLMs (e.g., Claude 3.5 Sonnet, Gemini 2.0 Flash) synthesize SQL over OMOP tables, conditioned on structured criteria, retrieved exemplars, and schema documentation, iteratively healing invalid drafts.
• Medical Concept Standardization: SQL placeholders for clinical concepts (e.g., condition@hypertension) are mapped to OMOP CONCEPT_IDs using dictionary lookups, embedding similarity, and GPT-4o verification.
• Execution and Postprocessing: Final SQL is executed on EHR databases, returning (person_id, index_date) pairs matching specified criteria.

A similar feedback-driven design underlies FD-NL2SQL (CLINSQL), which addresses oncology trial databases by decomposing queries into atomic predicates, retrieving expert-verified NL–SQL exemplars via SBERT embeddings, synthesizing candidate SQL under schema constraints, and enforcing robust post-hoc validity checks. Continuous improvement is achieved via both clinician-edit incorporation and logic-based mutation with automatic NL/SQL back-translation for exemplar expansion (Chowdhury et al., 17 Apr 2026).

3. Chain-of-Thought, Self-Refinement, and Feedback Loops

Modern CLINSQL instantiations leverage Chain-of-Thought (CoT) prompting, multi-pass LLM self-refinement, and interactive feedback to ensure correctness and maintainability in clinical SQL generation.

For the CLINSQL benchmark, LLMs undergo up to two self-refinement passes in response to execution errors on BigQuery, allowing models like GPT-5-mini and DeepSeek-R1 to approach 74.7% and 69.2% execution correctness, respectively, on test splits—though significant reliability gaps persist on difficult, multi-step analytics (Shen et al., 14 Jan 2026).

The feedback-driven loop in FD-NL2SQL further incorporates two dynamic update signals:

• Direct clinician-edit approval, appending corrected NL–SQL pairs to the exemplar bank.
• Logic-based atomic mutation (column/operator/value), executed and preserved only if syntactically and semantically valid, with LLM-generated NL back-translation and predicate decomposition.

These approaches result in a "living" system whose retrieval base grows weekly in both size and clinical diversity, improving exact-match performance (eEM), AST similarity, and downstream utility for small and large LLMs alike.

4. Auditability, Neuro-Symbolic Reasoning, and Traceability

CLINSQL advances the case for neuro-symbolic and fully auditable clinical reasoning. Cao et al. formalize a CLINSQL architecture wherein every clinical inference—e.g., classification of pathology imagery—yields not only a softmax probability but a complete SQL trace as an explicit evidence chain (Cao et al., 5 Jan 2026). The system operates as follows:

• Feature Extraction: Structured feature tables are generated from images (via HoVer-Net and others) storing per-nucleus, per-structure, and global patch descriptors.
• Feature Reasoning Agents: LLM-backed agents synthesize granular SQL queries (e.g., GROUP BY/HAVING over NucleiFeatures), with automated repair and schema-alignment.
• Knowledge Comparison Agent: Numerical outputs are benchmarked against empirical and LLM-derived reference ranges, mapped to categorical diagnoses and confidence weights using explicit mathematical rules.

A Report Agent fuses the SQL-derived evidence chain and the CNN’s predictions, emitting a structured reasoning trail and a final clinical assertion. Such transparency enables clinicians to inspect every sub-query, threshold, and aggregation, and to update reference logic without system retraining. Discrepancies between neuro-symbolic and conventional outputs flag ambiguous or unreliable findings for further review.

5. Example SQL Patterns, Schema Integration, and Query Templates

CLINSQL systems ingest and operate over a diversity of biomedical schemas:

• OMOP-CDM (EHR): Tables include PERSON, CONDITION_OCCURRENCE, DRUG_EXPOSURE, MEASUREMENT, with join keys, temporal constraints, and standardized semantic layers (ICD, SNOMED).
• MIMIC-IV (benchmarking): admissions, diagnoses_icd, labevents, chartevents, d_labitems, etc., requiring window functions, CTEs, and cohort isolation.
• Oncology Trials (FD-NL2SQL): Predicate-level decomposition aligns with SQLite-based investigator and result tables, biomarker columns, and curated endpoints.

SQL examples from CLINSQL literature demonstrate sophisticated patterns—multi-table joins, scalar/nested subqueries, CTE-based cohort isolation, APPROX_QUANTILES for risk-stratified statistics, and strict implementation of temporal and logical criteria (AND, OR, NOT EXISTS). Medical concept placeholders are present for deferred semantic mapping. Postprocessing ensures constraint satisfaction and result plausibility (column checks, data-type enforcement, range assertions).

6. Quantitative Evaluation, Metrics, and Limits

CLINSQL systems and benchmarks prioritize metrics tailored for the clinical context:

Metric	Description	Typical Results
Valid SQL %	Syntactic correctness of generated SQL	99–100% (Claude 3.5 Sonnet)
eEM, eF1, chrF	Exact-match, F1, and char-n-gram row overlap	eEM: 40.9% (CLINSQL, (Chowdhury et al., 17 Apr 2026))
AST similarity	Clause-weighted match to gold SQL AST	≥86% (FD-NL2SQL/CLINSQL)
ExecutionScore	Fraction of correct, clinically plausible outputs	74.7% (GPT-5-mini, (Shen et al., 14 Jan 2026))
F₁ (cohort)	Patient-level F-score, date-level alignment	up to 0.75 (RAG+A+C, (Ziletti et al., 28 Feb 2025))

Performance typically degrades on high-complexity, multi-window, exclusion-rich queries. Available evidence demonstrates that feedback-driven CLINSQL approaches substantially outperform zero- and few-shot LLMs, especially in schema fidelity and continuous improvement (Chowdhury et al., 17 Apr 2026).

Limitations include modest dataset sizes, incomplete coverage of real-world complexity, black-box LLM behaviors, schema and coding system specificity, and the lack of formal query-level uncertainty estimates.

7. Future Directions and Impact

CLINSQL architectures and benchmarks have accelerated progress toward clinically reliable data analytics and decision support. Key directions include the introduction of uncertainty quantification over LLM-generated queries, extension from OMOP-CDM to additional data models (e.g., i2b2), development of interactive graphical refinement interfaces, and federated retrieval for multi-site consortium research (Ziletti et al., 28 Feb 2025).

A plausible implication is that CLINSQL’s auditability and neuro-symbolic traceability offer enhanced interpretability, regulatory compliance, and user trust compared to purely neural or string-matching baselines. The paradigm also provides a blueprint for other biomedical domains, unifying domain knowledge, database engineering, and modern LLMs under auditable, self-improving, and clinically rigorous querying workflows.