KeywordSkeletonExtractor (LLM, Gemini Flash T=0.2)\n |\n +--> literals --> CellRetriever (k=5, cosine threshold 0.8)\n `--> skeleton --> ExampleRetriever (k=15, threshold 1.5)\n<\/code><\/pre>\n\n\n\nCell retrieval searches a per-DB Chroma collection built from TEXT columns in the source database, filtered to exclude primary keys, IDs, emails, and strings over 256 characters. Example retrieval searches a global collection of training-set question skeletons to find structurally similar past questions with known-correct SQL.<\/p>\n\n\n\n
Both retrievers return typed Pydantic objects (CellHit<\/code>, ExampleHit<\/code>) that the downstream generators consume directly \u2014 no string concatenation, no ad-hoc dicts.<\/p>\n\n\n\n<\/p>\n\n\n\n
L2: SQL Generation Scaling<\/h2>\n\n\n\n This is where the diversity-then-refinement strategy plays out.<\/p>\n\n\n\n
Diverse Synthesis<\/h3>\n\n\n\n Two generator families run in parallel, each with a different schema view and a different prompting approach:<\/p>\n\n\n\n
ReasoningGenerator<\/strong> \u2014 sees the DDL schema (CREATE TABLE<\/code> with inline comments), no examples. Uses chain-of-thought inside <think>\u2026<\/think><\/code> tags, then emits SQL inside <sql>\u2026<\/sql><\/code>. Designed to slot in RL-trained reasoning checkpoints when available; falls back to DeepSeek-Coder-V2 or GPT-5 at high effort today.<\/p>\n\n\n\nICLGenerator<\/strong> \u2014 sees the light markdown schema plus retrieved few-shot examples. Runs three sub-prompts in parallel to maximise diversity:<\/p>\n\n\n\n\nDirect<\/strong>: few-shot SQL completion.<\/li>\n\n\n\nChain-of-Thought<\/strong>: reason in natural language, then emit SQL.<\/li>\n\n\n\nQuestion Decomposition<\/strong>: split into sub-questions, solve bottom-up, compose the final SQL.<\/li>\n<\/ul>\n\n\n\nEach of these generators outputs the commented SQL along with the reasoning in bullet points. Default configuration: 2 reasoning candidates + 2 \u00d7 3 ICL candidates = 8 total. Every candidate is executed immediately in the sandbox and tagged with its ExecutionResult<\/code>.<\/p>\n\n\n\nIterative Refinement<\/h3>\n\n\n\n The refinement loop composes three agentic patterns so it learns within a single query<\/em> from every generator’s reasoning, rather than treating each candidate as an independent draft:<\/p>\n\n\n\n\nReflection<\/strong> \u2014 a dedicated CritiqueAgent<\/code> separates generation from evaluation; structured feedback drives the next edit.<\/li>\n\n\n\nMemory Management<\/strong> \u2014 a task-scoped ReasoningTrace<\/code> acts as an episodic scratchpad, recording every generator’s rationale, execution result, and critique for the duration of one query.<\/li>\n\n\n\nLearning and Adaptation<\/strong> \u2014 when the critique signals a framing problem that revision alone can’t fix, the orchestrator re-invokes the generators with accumulated lessons injected into their prompts.<\/li>\n<\/ul>\n\n\n\nThe four components of the loop, in order:<\/p>\n\n\n\n
ExecutionBucketer<\/strong> (no LLM) groups all candidates by ExecutionResult.hash<\/code>. Candidates with errors or zero rows are flagged suspicious. Bucket metadata \u2014 size, producer mix, mean rationale length \u2014 is written to ReasoningTrace<\/code> so downstream agents know which generators agreed.<\/p>\n\n\n\nSQLFixer<\/strong> (conditional) repairs candidates that errored. It reads ReasoningTrace.recent_fixes(producer)<\/code> to avoid repeating a repair strategy that already failed on a sibling candidate from the same generator. Bounded at 2 passes; failed candidates are dropped from the tournament but kept in the trace.<\/p>\n\n\n\nCritiqueAgent<\/strong> (new) runs once per non-error bucket, before<\/em> the revisor. It reads the bucket representative, its reasoning trace, and the rationales of every other generator \u2014 so it can contrast hypotheses, not just evaluate one candidate in isolation. It emits a typed Critique<\/code> with:<\/p>\n\n\n\n\nerror_class<\/code> \u2014 a closed enum (wrong_join<\/code>, missing_filter<\/code>, wrong_aggregation<\/code>, value_mismatch<\/code>, evidence_ignored<\/code>, other<\/code>) so critiques are aggregatable across buckets and queries.<\/li>\n\n\n\ncomplexity<\/code> \u2014 an integer 1\u201310 rating of how convoluted the reasoning required to answer the question is, based on the spread of generator rationales and the bucket representative’s structure. Anything ? 7 means editing alone is unlikely to converge.<\/li>\n\n\n\nsuggested_fix<\/code> \u2014 an actionable rewrite hint for the revisor.<\/li>\n\n\n\ngenerator_feedback<\/code> \u2014 corrective material (correction<\/code> or warning<\/code>) for the generators on a re-synthesis pass, distinct from suggested_fix<\/code> because it needs to be generic enough to seed multiple new candidates from different prompting strategies.<\/li>\n<\/ul>\n\n\n\nThe retry<\/code> verdict is derived by the orchestrator<\/strong>, not chosen by the LLM: if complexity >= 7<\/code>, the orchestrator forces verdict = \"retry\"<\/code> regardless of what the model wrote. This keeps the threshold a single source of truth in code and makes it unambiguously trace-grounded.<\/p>\n\n\n\nSQLRevisor<\/strong> runs on bucket representatives when the verdict is edit<\/code>. It now receives the Critique<\/code> (what is wrong) and a cross-bucket summary from ReasoningTrace<\/code> (what other generators answered instead), so it is no longer reasoning blind about a single candidate. Bounded at 1\u20132 passes; any new SQL re-enters the loop from execution onward.<\/p>\n\n\n\nCritique-Conditioned Re-Synthesis<\/h3>\n\n\n\n After each revisor pass, the orchestrator checks whether re-synthesis is warranted. It triggers a new generation round when either:<\/p>\n\n\n\n
\nAny bucket’s critique has complexity >= 7<\/code> (a retry<\/code> verdict) \u2014 high-complexity questions are where framing errors happen at synthesis time, not editing time.<\/li>\n\n\n\nNo bucket was accepted and<\/em> at least one error_class<\/code> is shared across two or more buckets \u2014 meaning the generators systematically misread the same aspect of the question.<\/li>\n<\/ol>\n\n\n\nWhen triggered, the generators are re-invoked with a LessonsBlock<\/code> injected into their prompts, assembled from ReasoningTrace<\/code>:<\/p>\n\n\n\nAvoid: wrong_join \u2014 the join through order_items produces duplicate rows.\nPrefer: join through orders directly.\n\n## Corrections\n- The join must go through orders, not order_items. [all generators]\n\n## Warnings\n- Be careful with NULL handling on customer.region. [icl\/direct only]\n<\/code><\/pre>\n\n\n\nNew candidates re-enter the full loop (execution ? bucketing ? critique ? revision). Re-synthesis is capped at 2 passes<\/strong> (resynth_max_passes<\/code>) and generates k_resynth = max(1, k_icl \/\/ 2)<\/code> candidates per generator per pass. After 2 passes, L3 runs on whatever pool exists \u2014 the cap is non-negotiable to prevent reflection-loop runaway.<\/p>\n\n\n\nThe ReasoningTrace<\/code> itself is task-scoped and ephemeral: it lives for the duration of one query and is serialised into the final TaskContext<\/code> for L3 and post-hoc analysis, but it is not long-term memory. Recurring lessons (distilled (skeleton, error_class, fix)<\/code> triples) can optionally be written to a cross-task lesson store that augments L1 example retrieval on future queries \u2014 but that path is offline and does not affect the serving pipeline’s statefulness invariant.<\/p>\n\n\n\n<\/p>\n\n\n\n
L3: SQL Selection Scaling<\/h2>\n\n\n\n After refinement, you have a pool of candidates grouped by execution result hash. The tournament works like this:<\/p>\n\n\n\n
\nTake one representative per non-error bucket ? set C'<\/code>.<\/li>\n\n\n\nIf |C'| == 1<\/code>, return it immediately.<\/li>\n\n\n\nOtherwise run pairwise round-robin: for every unordered pair (c_i, c_j)<\/code>, ask the ReasoningSelector<\/code> which SQL better answers the question. It sees the question, the light schema, both SQL strings, and both execution previews.<\/li>\n\n\n\nRank by win count. Tie-break: (a) higher win count, (b) larger bucket (more generators agreed), (c) prefer the reasoning generator (empirically higher precision on simple\/moderate questions).<\/li>\n<\/ol>\n\n\n\nComplexity is O(|C'|\u00b2)<\/code> selector calls. In practice |C'|<\/code> is 2\u20135 after bucketing, so the tournament costs 1\u201310 calls.<\/p>\n\n\n\n<\/p>\n\n\n\n
The Orchestrator: LangGraph as the Graph Runtime<\/h2>\n\n\n\n The orchestrator is the only place where scaling lives as code. Agents are stateless; they have no idea about retries, fan-out width, or loop depth. The orchestrator’s ScalingConfig<\/code> owns all of that:<\/p>\n\n\n\nclass ScalingConfig(Frozen):\n k_reasoning: int = 2\n k_icl: int = 2 # per sub-prompt\n k_resynth: int = 1 # candidates per generator on re-synthesis (default max(1, k_icl\/\/2))\n fixer_max_passes: int = 2\n refinement_max_passes: int = 1\n resynth_max_passes: int = 2 # critique-conditioned re-synthesis depth\n complexity_retry_threshold: int = 7 # Critique.complexity at or above which verdict is forced to \"retry\"\n tournament_enabled: bool = True\n global_timeout_s: int = 120\n token_budget: int = 200_000\n<\/code><\/pre>\n\n\n\nI chose LangGraph over CrewAI, smolagents, Google ADK, and plain asyncio<\/code> for one concrete reason: the pipeline’s structure \u2014 two fan-outs, two bounded loops, one tournament \u2014 maps directly onto LangGraph primitives. The Send<\/code> primitive expresses per-candidate execution, per-bucket revision, and per-pair selector calls as first-class graph nodes rather than hand-rolled asyncio.gather<\/code> blocks. Conditional edges express exec_result.error? ? fixer<\/code> and refinement_passes_left? ? revisor<\/code> without hand-rolled while<\/code> loops. Checkpointers give us replay for free.<\/p>\n\n\n\nWhat we deliberately don’t<\/em> take from LangGraph: its model wrappers. Every LLM call goes through our own model-adapter<\/code>, and our AgentEvent<\/code> schema is the authoritative lineage format \u2014 LangSmith and JSONL are sinks, not owners.<\/p>\n\n\n\nData Contracts: Pydantic Frozen Models<\/h2>\n\n\n\n Every type that crosses a module boundary is an immutable Pydantic v2 model. No dicts, no prompt strings, no ad-hoc tuples.<\/p>\n\n\n\n
class Frozen(BaseModel):\n model_config = ConfigDict(frozen=True, extra=\"forbid\", str_strip_whitespace=True)\n\nclass CandidateSQL(Frozen):\n sql: str\n producer: str # \"reasoning\/T=0.7\", \"icl\/cot\/T=1.0\"\n rationale: Optional[str] = None\n exec_result: Optional[ExecutionResult] = None\n lineage: list[str] = Field(default_factory=list) # [\"gen\", \"fix\", \"revise:pass2\"]\n\nclass ExecutionResult(Frozen):\n rows: tuple[tuple, ...]\n columns: tuple[str, ...]\n row_count: int = Field(ge=0)\n error: Optional[str] = None\n hash: str\n elapsed_ms: int = Field(ge=0)\n<\/code><\/pre>\n\n\n\nfrozen=True<\/code> makes every contract hashable and safe to share across async tasks. extra=\"forbid\"<\/code> catches typos. ExecutionResult.hash<\/code> is computed over a canonical form of the result set \u2014 sorted rows, normalised timestamps, float rounding to 6 decimal places \u2014 so that two syntactically different SQLs that return the same answer collapse into the same bucket.<\/p>\n\n\n\nThe Execution Sandbox<\/h2>\n\n\n\n Every candidate SQL is executed in a read-only sandbox before any LLM refinement or selection decision. This is load-bearing: bucketing, revision, and tournament selection all depend on the canonical hash.<\/p>\n\n\n\n
Read-only enforcement is multi-layered:<\/p>\n\n\n\n
\nSQLite: PRAGMA query_only = ON<\/code>, opened with mode=ro<\/code> URI.<\/li>\n\n\n\nA lightweight AST check rejects INSERT | UPDATE | DELETE | DROP | ALTER | ATTACH<\/code> before execution reaches the driver.<\/li>\n\n\n\nWrite violations raise ReadOnlyViolation<\/code> synchronously; the connection is discarded, not returned to the pool.<\/li>\n<\/ul>\n\n\n\nThe canonicalisation pipeline:<\/p>\n\n\n\n
raw rows\n ? sort in-memory (stable tuple comparator; do NOT add ORDER BY to the SQL)\n ? normalise per cell (rstrip strings, round floats to 6dp, ISO-8601 timestamps, null sentinel)\n ? BLAKE2b hash over canonical JSON\n<\/code><\/pre>\n\n\n\nError results get an ERR:<\/code> prefixed hash so they can never hash-collide with a successful result, and identical syntax errors across multiple candidates collapse into the same error bucket.<\/p>\n\n\n\nObservability: Three-Surface Tracing<\/h2>\n\n\n\n Every agent call emits a typed AgentEvent<\/code> following OpenTelemetry GenAI semantic conventions. Events are tagged with one of three surfaces:<\/p>\n\n\n\nSurface<\/th> What it measures<\/th> Example agents<\/th><\/tr><\/thead> cognitive<\/code><\/td>Plan quality, reasoning correctness<\/td> Keyword extractor, generators, selector<\/td><\/tr> operational<\/code><\/td>Tool-call reliability<\/td> SQL executor, fixer<\/td><\/tr> contextual<\/code><\/td>Retrieval precision<\/td> Cell retriever, example retriever<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nThe evaluation harness derives per-surface metrics from this stream: Plan Adherence Rate<\/strong>, Tool Call Success Rate<\/strong>, Retrieval Precision<\/strong>, and Critic Adherence Rate<\/strong> (how often the Revisor actually adopts its own critique).<\/p>\n\n\n\nThe Revisor’s reflection loop is a first-class event:<\/p>\n\n\n\n
async with tracer.span(\"l2.sql_revisor\", surface=\"cognitive\", node=\"l2.revise\") as span:\n verdict = await self._critique(candidate)\n await tracer.critique(\n \"l2.sql_revisor\",\n RevisionDelta(\n before_hash=candidate.exec_result.hash,\n after_hash=new_result.hash,\n verdict=\"revise\",\n rationale=\"...\",\n ),\n )\n span.set_output(revised_candidate)\n<\/code><\/pre>\n\n\n\nThis design lets you compute Critic Adherence Rate offline: for every case where the revisor said “this needs a change,” did the new candidate actually produce a different result?<\/p>\n\n\n\n
Events fan out to LangSmith (default) and a local JSONL file in parallel. If LangSmith is unreachable, the pipeline continues with the JSONL sink and logs a single warning.<\/p>\n\n\n\n
DSPy for Prompt Compilation<\/h2>\n\n\n\n Agents that the pipeline actually cares about optimising \u2014 the keyword extractor, the three ICL generator sub-prompts, the CritiqueAgent<\/code>, the revisor, and the selector \u2014 are expressed as DSPy Signature<\/code>s compiled against execution-grounded metrics.<\/p>\n\n\n\nThe Revisor’s compilation metric is approximately:<\/p>\n\n\n\n
def revisor_metric(example, pred, trace=None):\n result = sandbox.execute(pred.sql, db_id=example.db_id)\n if result.hash == example.gold_hash:\n return 1.0\n elif result.error is None:\n return 0.1\n return 0.0\n<\/code><\/pre>\n\n\n\nThe metric mirrors the same execution-grounded reward used to train RL-tuned reasoning models. When a stronger checkpoint becomes available, it replaces the compiled prompt behind the same DSPy Signature<\/code> with zero orchestration changes \u2014 the signature is the contract, and either side (compiled prompt or RL model) can fulfill it.<\/p>\n\n\n\nGraceful Degradation<\/h2>\n\n\n\n The pipeline fails hard in exactly one scenario: every generator produced zero candidates. Otherwise degradation is priority-ordered and monotonic:<\/p>\n\n\n\n
\nKeywordSkeletonExtractor<\/code> fails –> continue with empty Understanding<\/code>.<\/li>\n\n\n\nCellRetriever<\/code> or ExampleRetriever<\/code> fails –> continue with empty list.<\/li>\n\n\n\nOne ICL sub-prompt fails –> continue with remaining generators.<\/li>\n\n\n\n SQLFixer<\/code> \/ SQLRevisor<\/code> fails –> skip that pass, keep pre-refinement candidates.<\/li>\n\n\n\nReasoningSelector<\/code> fails mid-tournament –> fall back to majority vote over bucket sizes.<\/li>\n\n\n\nBudget or timeout exceeded –> return best-so-far candidate tagged degraded=True<\/code>.<\/li>\n<\/ol>\n\n\n\nEvery degradation fires a DecisionEvent<\/code> with a label, confidence, and feature dict. The evaluation harness computes degraded_rate<\/code> from the trace \u2014 so you can see whether degraded runs score proportionally worse and by how much.<\/p>\n\n\n\nEvaluation<\/h2>\n\n\n\n The evaluation harness runs on a proprietary set of commonly asked questions in the healthcare domain about claims data. Primary metric is execution accuracy \u2014 does the candidate SQL produce the same result set as the gold SQL? Secondary is reward-based valid efficiency score, which weights correct queries by execution-time ratio vs gold.<\/p>\n\n\n\n
The ablation table covers: drop the reasoning generator, drop ICL, drop the tournament (? self-consistency), drop refinement. Any fork of this codebase can compare directly.<\/p>\n\n\n\n
What’s Left<\/h2>\n\n\n\n Two RL-trained checkpoints \u2014 a 32B reasoning generator and a 32B selector \u2014 would push accuracy higher but are not yet available. The pipeline runs on zero-shot frontier LLMs today (GPT-5, Gemini 2.5 Pro, Claude as the selector); those slots are designed to accept stronger models as drop-in replacements.<\/p>\n\n\n\n
A difficulty router that bumps k_icl<\/code> and refinement_max_passes<\/code> for questions tagged challenging<\/code> is designed but not yet wired. Pass@k gains are largest on the challenging bucket, so targeted scaling pays off more than uniform scaling across easy questions.<\/p>\n\n\n\nReferences<\/h2>\n\n\n\n\nSutton (2019). The Bitter Lesson.<\/em> incompleteideas.net<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"Natural language to SQL is one of those widely applicable problems that has not yet been solved with AI. On BIRD, the hardest public text-to-SQL benchmark, state-of-the-art single-pass systems top out around 75% execution accuracy. Human experts score 92.96%. The gap sits almost entirely on complex, multi-join, domain-rich questions where schema ambiguity and missing literals […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-1501","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/posts\/1501","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/comments?post=1501"}],"version-history":[{"count":7,"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/posts\/1501\/revisions"}],"predecessor-version":[{"id":1520,"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/posts\/1501\/revisions\/1520"}],"wp:attachment":[{"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/media?parent=1501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/categories?post=1501"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/yuguangzhang.com\/blog\/wp-json\/wp\/v2\/tags?post=1501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"http:\/\/yuguangzhang.com\/blog\/wp-content\/uploads\/2026\/07\/overall-architecture-1024x554.png\")
<\/figure>\n\n\n\n![\"\"](\"http:\/\/yuguangzhang.com\/blog\/wp-content\/uploads\/2026\/07\/l0-offline-prep-1024x580.png\")
<\/figure>\n\n\n\n![\"\"](\"http:\/\/yuguangzhang.com\/blog\/wp-content\/uploads\/2026\/07\/l1-task-understanding.png\")
<\/figure>\n\n\n\n![\"\"](\"http:\/\/yuguangzhang.com\/blog\/wp-content\/uploads\/2026\/07\/l2-generation-scaling-663x1024.png\")
<\/figure>\n\n\n\n![\"\"](\"http:\/\/yuguangzhang.com\/blog\/wp-content\/uploads\/2026\/07\/l3-selection-scaling-368x1024.png\")
<\/figure>\n\n\n\n