Overview
The natural language to SQL query generator was built incrementally over multiple phases, with each new layer added to solve a specific accuracy or reliability problem. In its final form, the system is not just “AI that writes SQL.” It is a structured pipeline that first tries to understand the user’s question in business terms, then maps that understanding to the right part of the database, and only then generates SQL.
[v 0.1.0]
Core query pipeline:
User Query → Intent Detection → Domain Narrowing → Query Interpretation / Normalization → Entity Resolution → Business Concept Resolution → Schema Selection / Planner Augmentation → LLM Planning → LLM SQL Generation → SQL Validation → Execution
Intent Detection
At the front of the pipeline, the system takes the user’s natural language query and performs intent detection. This helps determine what kind of question is being asked, such as whether the user wants a count, a grouped report, a filtered list, or a more complex analysis. This was needed so the system could understand the shape of the request before trying to map it to SQL.
Domain Narrowing
After intent is identified, the system narrows the query to the most relevant product domain, such as appointments, claims, billing, insurance, payments, patients, organizations, or locations. This was needed so the system would not treat every question as if the entire database were equally relevant, which would make the results noisier and less accurate
Query Interpretation / Normalization
A major improvement was the addition of a query interpretation layer. This layer normalizes the query, extracts likely entity phrases, classifies words and phrases by meaning, and separates important terms from filler or descriptive language. For example, it helps the system understand that in a phrase like RegenLife practice, the important part is RegenLife, while practice is more descriptive. This layer was needed because users do not type queries in perfect database language, and a literal interpretation was making the system too brittle.
Entity Resolution
The interpretation layer was then strengthened with entity resolution. This allows the system to recognize and resolve real-world entities such as organizations, providers, patients, payers, and locations. Instead of only relying on query wording, it can compare phrases against known data and metadata to find the most likely intended entity. This was needed because many support queries refer to business entities in approximate or informal ways, and accuracy improves significantly when the system can ground those references to real values in the system.
Business Concept Resolution
Another important layer is business concept resolution. This was added because many user questions are really about business concepts, not just words. For example, “telemedicine appointments” or “submitted claims” may be represented in multiple places in the database, but only one path is the canonical one for reporting. The BusinessConceptRegistry and ConceptResolutionService exist to define those concepts explicitly, identify which concept the user is asking about, and tell the system which tables and columns should be preferred, which are secondary, and which should not be used by default. This was needed to stop the model from choosing SQL that was plausible but not aligned with the real business meaning of the question.
Schema Selection / Planner Augmentation
After the query has been interpreted and the relevant entities and concepts have been resolved, the system performs schema selection. Rather than exposing the full database, it narrows the schema down to the relevant tables and columns. This makes the downstream LLM steps more accurate and less likely to hallucinate tables or fields. It also keeps the prompts more focused. Planner augmentation also happens here, so the LLM receives not just schema metadata, but also guidance about the most relevant entities, concepts, and canonical data paths.
LLM Planning
The first LLM step creates a structured query plan rather than jumping straight to SQL. This plan captures the intended objects, filters, joins, aggregations, and overall shape of the query. This was needed because it is more reliable to first decide what the query should do before asking the model to write SQL.
LLM SQL Generation
Once the plan is prepared, a second LLM step generates the actual SQL. Because it works from a structured plan and a narrowed schema context, this step is much more accurate than a single prompt that tries to understand the question and write SQL all at once.
SQL Validation
Once SQL is generated, it goes through validation against authoritative schema metadata. This ensures that only real tables and columns are used and helps block hallucinated or stale schema references. Over time, this validation layer became increasingly important as the system grew more sophisticated, because it acts as a final safety check before execution.
Execution
After validation, the SQL is executed against the database to produce the result set returned to the user. By the time execution happens, the query has already passed through multiple interpretation, grounding, and validation layers, which greatly improves the quality and trustworthiness of the output.
Schema Truth Foundation
To support all of this, the product also evolved a stronger schema-truth foundation. Structured schema metadata, semantic overlay information, cached reference data, and schema-consistency checks were added so the system could stay accurate even as the underlying schema changed. This was needed to prevent drift between the real database and the logic used by the query generator.
Summary
In short, the final architecture combines multiple layers: intent detection, domain narrowing, query interpretation, entity resolution, concept resolution, schema selection, structured planning, SQL generation, schema validation, and execution. Each layer was added to solve a real problem that appeared as the system matured: overly literal interpretation, weak entity matching, semantically wrong data paths, hallucinated schema usage, and fragile SQL generation. Together, these layers turned the product from a simple prompt-based experiment into a much more reliable, business-aware natural language to SQL system.
[v 0.2.0]
Recovery flow:
Recovery Analysis / Warning Classification → Prompt Suggestion Generation → User Retry Flow
Prompt Suggestion Generation
After the first version of the natural language to SQL system was completed, we added prompt suggestion support to help users recover when a query was unclear, unsupported, failed to generate SQL, or returned no useful results. Instead of only showing warnings or empty results, the system can now generate a small set of better natural-language prompts that express the user’s intent more clearly. Each suggestion includes a short explanation of why it is likely to work better. This was needed because previously the system could indicate that something went wrong, but the user still had to figure out on their own how to rewrite the question.
Recovery Analysis / Warning Classification
Adding prompt suggestions also made it important to distinguish between harmless informational notes and real warning conditions. Not every message should trigger recovery. Some messages are only explanatory, such as wording variability across tenants or broad interpretation notes that do not materially change the result. Others indicate that the user’s intent may not have been captured correctly, such as entity ambiguity, unsupported requirements, or a query that returns no results. To handle this, the backend was improved so it now separates informational notes from actionable warnings and blocking issues. This was needed to make the system less noisy and ensure that prompt suggestions appear only when they are actually helpful.
Execution-Stage Recovery
We also extended recovery beyond SQL generation and into execution. Sometimes a query can be generated successfully, but the executed SQL returns no result set or an empty result set. In those cases, the problem is often not SQL syntax but that the original prompt was too narrow, mapped to the wrong entity, or otherwise missed the user’s intent. The system can now recognize those cases and offer better prompt suggestions after execution as well. This was needed so recovery could happen whenever the user failed to get a useful answer, not just when SQL generation failed.
Frontend Recovery Experience
The frontend Support Portal was updated to display these recovery suggestions directly in the query flow. When the backend signals that better prompts should be shown, the UI now presents a dedicated recovery section with up to five suggested prompts and a short explanation for each one. This works for both generation-stage and execution-stage recovery. When the user selects one of the suggestions, the input is updated and stale results are cleared so the user can quickly try again. This was needed to turn prompt recovery into a practical user-facing feature rather than only a backend capability.
[v 0.3.0]
Billing semantic hardening:
Billing Semantic Interpretation → Billing-Aware Schema Ranking → Billing Planning Audit → Billing Regression Coverage
Billing Semantic Interpretation
After the recovery flow was added, the next major improvement focused on billing. Populate’s billing model is unusually complex, and early NL-to-SQL behavior showed that even when the system generated valid SQL, it could still misunderstand the business meaning of billing-related questions. Terms such as EOB, ERA, billed amount, allowed amount, insurance paid, patient responsibility, CARC, RARC, claim-level CARC, and service-line CARC are easy to confuse if the system relies only on general language patterns. To address this, a billing-specific semantic interpretation layer was added ahead of SQL generation. This layer normalizes billing language, treats ERA and EOB as the same remittance concept, distinguishes claim-level versus service-line grain, applies explicit product rules such as adjustment meaning CARC and denial code meaning CARC, and records ambiguity or derived behavior where needed. This was needed because billing questions were too important and too domain-specific to leave to generic prompt interpretation alone.
Billing-Aware Schema Ranking
Once billing semantics were introduced, schema selection was strengthened so billing intent could influence which tables and columns were preferred. This was needed because many billing concepts have nearby but incorrect schema paths. For example, billed amount and allowed amount may both sound plausible in a query, but they are not interchangeable. Likewise, insurance payment totals and service-line payment breakdowns live at different levels of the model. The schema ranking layer was therefore updated to become grain-aware and billing-aware. It now prefers the correct canonical billing objects based on the interpreted meaning of the prompt, such as distinguishing between EOB-level totals and service-line breakdowns, between CARC and RARC, and between encounter-level patient responsibility and remittance service-line dues. This was needed to reduce semantic drift where the system chose a technically valid but business-incorrect query path.
Billing Planning Audit
A billing-focused semantic audit was also added after planning. This audit checks whether the query plan still matches the interpreted billing meaning before SQL is generated. It can detect problems such as billed amount drifting to allowed amount, claim-level CARC drifting to raw service-line rows without proper claim-scope handling, unsupported provider attribution on EOB/remittance queries, or remittance concepts collapsing into unrelated claim-only paths. This was needed because even with better interpretation and schema ranking, downstream planning could still produce queries that looked reasonable but violated important billing semantics. The audit acts as a trustworthiness layer that either warns, blocks, or preserves explicit unsupported output when the requested meaning cannot be safely satisfied.
Billing Regression Coverage
Because billing behavior became more explicit and more rule-driven, the system also gained significantly stronger regression coverage. Test packs were added for billing semantic interpretation, schema ranking, planning audit behavior, and final SQL handoff. These tests cover both canonical billing prompts and realistic operator phrasing, including ERA/EOB equivalence, service-line wording, adjustment and denial terminology, claim-level CARC derived behavior, and multiple grain-sensitive payment and responsibility concepts. This was needed to keep billing accuracy stable as the system evolved and to ensure that future changes would not silently reintroduce earlier billing-specific mistakes.
Summary
In short, version 0.3.0 introduced a billing-specific semantic layer on top of the general NL-to-SQL pipeline. Billing questions now pass through explicit interpretation, billing-aware schema ranking, planning audit checks, and targeted regression coverage before SQL is generated. This turned billing from a weak area of the system into one of the most structured and intentionally modeled parts of the product.
[v 0.4.0]
Suggestion quality hardening:
Suggestion Revalidation → Suggestion Scoring and Filtering → Semantic Deduplication → Suggestion Diversification
Suggestion Revalidation
After prompt recovery was introduced, another issue became visible: not all generated suggestions were actually good suggestions. Some were only superficial wording variations, and some still failed because they inherited stale unsupported conditions from the original query. To fix this, the suggestion flow was strengthened so candidate suggestions are no longer accepted at face value. Instead, each candidate suggestion is reinterpreted from scratch through the normal pipeline and rechecked for planning success, unsupported requirements, and SQL-generation eligibility. This was needed because previously a suggestion could look helpful in text while still failing in practice, which reduced trust in the recovery experience.
Suggestion Scoring and Filtering
The recovery system was also updated so it no longer optimizes for always showing five suggestions. Suggestions are now treated more like ranked alternatives, where quality matters more than count. The backend evaluates whether a suggestion is executable, how closely it preserves the original intent, whether it remains helpful, and whether it introduces unnecessary drift. Closest supported repairs are preferred over more creative rewrites, especially when the original query only needs a small fix such as removing an unsupported provider filter. This was needed because earlier versions could show too many weak suggestions, including suggestions that were executable but not faithful to what the user was actually asking.
Semantic Deduplication
A related improvement was semantic deduplication. Many earlier suggestions differed only in wording while still representing the same underlying query. To reduce this noise, suggestions are now compared by their semantic shape rather than only by text. Factors such as main object family, metric family, grain, grouping, and time scope are used to identify near-duplicates. This was needed because users benefit far more from one clear supported rewrite than from several paraphrases of the same idea.
Suggestion Diversification
Once weak and duplicate suggestions are filtered out, the system tries to return a small set of meaningfully different alternatives when they exist. These alternatives are diversified by analytical shape, not just phrasing. For example, suggestions may vary as a minimal repair, a supported grouping alternative, a supported time-summary alternative, or a narrowed supported scope. This was needed because a good recovery experience should not only repair the blocked query, but also offer other genuinely useful ways to reach the same business goal when the original formulation is not fully supportable.
Frontend Suggestion Experience Refinement
The user-facing recovery experience also became more disciplined as a result of these backend improvements. The system can now return fewer than five suggestions, or even none, when no strong executable alternatives exist. This makes the UI feel more trustworthy because every displayed suggestion has already passed a stronger quality bar. The result is that suggestion prompts are no longer just “possible rewrites,” but better-supported next steps the user can reasonably expect to work.
Summary
In short, version 0.4.0 improved the recovery experience by making prompt suggestions smarter, stricter, and more useful. Suggestions are now revalidated, ranked, deduplicated, and diversified before being shown. This changed recovery from a simple text-generation feature into a more disciplined recommendation layer that helps users reformulate failed queries in ways the system can actually support.
[v 0.5.0]
Billing entity resolution refinement and query-path performance hardening:
User-Role Entity Resolution → Canonical Primary/Secondary Claim Semantics → Query-Path Latency Reduction → Recovery Budgeting and Telemetry
User-Role Entity Resolution
As billing query coverage expanded, it became clear that some organization-scoped queries were still generating unnecessary unsupported warnings because the system was not resolving role language correctly. Terms such as doctor, provider, staff, MA, medical assistant, and biller were tightened so they resolve through the Users model in the intended organizational context. This was needed because queries like “organization where the doctor last name is Cohn” are supportable through Organizations and Users, and should not be treated as unsupported provider-to-claim attribution.
Canonical Primary/Secondary Claim Semantics
Claim interpretation was also refined for primary and secondary language. Secondary claims are now anchored to the canonical rule that a claim is secondary when Claims.ReferencedPrimaryClaimId has a value, rather than relying on weaker or less direct heuristics. Shorthand wording such as “secondaries” was also brought into the billing semantic path. This was needed so support-style claim queries would resolve to the correct claim semantics consistently and use the intended canonical field.
Query-Path Latency Reduction
After billing semantics and suggestion-quality improvements were added, the normal “Generate Query” path became too slow. A focused performance pass was done to reduce unnecessary work on successful requests. Recovery and suggestion logic was gated so it no longer runs on the happy path when SQL generation succeeds and execution returns a useful result. The SQL-generation prompt was also narrowed so only schema objects referenced by the finalized plan are sent downstream. This was needed to reduce end-to-end latency without removing the billing and safety improvements added in earlier versions.
Recovery Budgeting and Telemetry
The recovery and suggestion path was also tightened to reduce fan-out cost. Candidate suggestion budgets were lowered, early exits were added once enough strong distinct suggestions were found, and lightweight request-local reuse was introduced for repeated planning and validation work inside a single request. Additional telemetry was added around stage timing, candidate counts, and LLM call counts so latency regressions can be localized more precisely in the future. This was needed because recovery quality had improved, but its computational cost had become too high relative to the value it was providing on each request.