# beachwang/dail-sql architecture

> Architecture diagram of the GitHub repository beachwang/dail-sql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-07-03). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/beachwang/dail-sql
- Repository: https://github.com/beachwang/dail-sql

## Overview

1. **Repository purpose**
   - This repo implements **DAIL-SQL**, a text-to-SQL prompt-engineering pipeline for Spider/BIRD-style benchmarks.
   - The architecture is not a model trainer; it is an **orchestration and prompt-construction system** around external LLMs such as GPT-4, GPT-3.5, text-davinci-003, and Vicuna.

2. **Top-level pipeline**
   - The flow is: **raw dataset → preprocessing → schema/question linking → example selection → prompt building → LLM call → post-processing / evaluation**.
   - The key architectural choice is that the repo prepares highly structured prompts and then delegates generation to the LLM API or local model endpoint.

3. **Dataset and benchmark boundary**
   - The main data boundary is the **Spider dataset** under `./dataset/spider`, with support for Spider-dev/test and BIRD-related output formatting.
   - `utils/datasets/spider.py` and `utils/data_builder.py` indicate a dataset abstraction layer that normalizes benchmark records into prompt-ready examples and database metadata.

4. **Preprocessing subsystem**
   - `data_preprocess.py` is the offline preparation step that cleans and enriches data before prompt generation.
   - This stage likely produces cached artifacts used later for schema linking, question matching, and example retrieval, reducing runtime overhead during LLM calls.

5. **Schema linking and lexical analysis subsystem**
   - The `utils/linking_process.py` and `utils/linking_utils/` package form a distinct **schema-linking boundary**.
   - This subsystem depends on **Stanford CoreNLP** (`nltk_downloader.py`, CoreNLP setup in README) and contains RAT-SQL-inspired utilities for tokenization, parsing, serialization, and Spider-specific matching.
   - Its job is to connect natural-language question tokens to database schema elements, which is important for compact prompt representations.

6. **Question representation subsystem**
   - The `prompt/` package contains multiple templates for how a question can be represented in the prompt:
     - `PromptICLTemplate.py`
     - `PromptReprTemplate.py`
     - `ExampleFormatTemplate.py`
     - `ExampleSelectorTemplate.py`
     - `prompt_builder.py`
   - This is a central architectural layer: it turns the same underlying example into different prompt forms such as SQL-only, QA pairs, or richer representations.

7. **Example selection subsystem**
   - `generate_question.py` is the main entry point for **few-shot example retrieval and prompt assembly**.
   - The README shows several selector strategies, including question similarity, masked question similarity, and combined question+query similarity.
   - This layer depends on precomputed similarity signals and ranking logic to choose k-shot demonstrations under token limits.

8. **Query-similarity and masking logic**
   - The repository distinguishes between plain question similarity and SQL-skeleton-aware selection.
   - Evidence from filenames and README suggests the system uses **masked question forms** plus pre-generated SQL results to capture structural similarity while minimizing domain-specific token cost.
   - This is a key design boundary: selecting examples is not just nearest-neighbor text retrieval; it is structure-aware retrieval.

9. **LLM invocation subsystem**
   - `ask_llm.py` is the runtime execution entry point that sends prompts to an LLM and collects answers.
   - The README shows support for both single-pass generation and **self-consistency voting** via multiple samples (`n > 1`, higher temperature), which is an important runtime branch.
   - The LLM layer is externalized through `llm/chatgpt.py`, which likely wraps OpenAI-style API calls.

10. **Post-processing and output formatting**
   - `utils/post_process.py` and `to_bird_output.py` form the output normalization layer.
   - This layer is responsible for converting raw model completions into executable SQL or benchmark-specific submission formats.
   - It is also the place where any cleanup, extraction, or result alignment with database instances would happen.

11. **Scripts, experiments, and reproducibility**
   - The `run_dail_sql*.sh` scripts are the operational wrappers for standard runs and self-consistency runs.
   - `results/` contains stored outputs from the paper’s experiments, which makes the repo partly reproducibility-focused rather than purely code-focused.
   - `img/` stores evaluation figures used in the README, supporting the experimental narrative.

12. **External dependencies and infrastructure**
   - Two external systems materially affect the architecture:
     - **OpenAI API** for GPT-based execution.
     - **Stanford CoreNLP server** for linguistic preprocessing/schema linking.
   - The repo also depends on the **Spider database directory** and benchmark assets, which are treated as local filesystem inputs rather than embedded resources.

13. **Functional boundary summary**
   - In architectural terms, the repo separates into:
     - **offline preparation**: dataset cleaning, linking, and caching
     - **prompt construction**: representation + retrieval + formatting
     - **inference orchestration**: LLM request/response handling
     - **result normalization**: post-processing and benchmark output conversion
   - That separation is what enables the same system to support zero-shot, few-shot, and self-consistency variants across multiple models.

Main entry points: [`prompt`](https://github.com/beachwang/dail-sql/tree/main/prompt) (Prompt templates), [`utils/linking_utils`](https://github.com/beachwang/dail-sql/tree/main/utils/linking_utils) (Link utils), [`generate_question.py`](https://github.com/beachwang/dail-sql/blob/main/generate_question.py) (Generate question), [`ask_llm.py`](https://github.com/beachwang/dail-sql/blob/main/ask_llm.py) (Ask LLM)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_g_data["Data boundary"]
  node_dataset_spider[("Spider data<br/>benchmark dataset")]
  node_spider_dataset_adapter["Spider adapter<br/>dataset loader<br/>[spider.py]"]
  node_data_builder["Data builder<br/>data assembly<br/>[data_builder.py]"]
end

subgraph group_g_link["Linking pipeline"]
  node_preprocess["Preprocess<br/>offline prep<br/>[data_preprocess.py]"]
  node_nltk_setup["CoreNLP setup<br/>linguistic setup<br/>[nltk_downloader.py]"]
  node_link_process["Link process<br/>schema linking<br/>[linking_process.py]"]
  node_link_utils["Link utils<br/>linking toolkit"]
  node_corenlp["CoreNLP client<br/>nlp runtime<br/>[corenlp.py]"]
  node_serialization["Serialization<br/>[serialization.py]"]
  node_spider_match["Spider match<br/>matching rules"]
end

subgraph group_g_prompt["Prompt pipeline"]
  node_prompt_templates["Prompt templates<br/>prompt forms"]
  node_prompt_icl["ICL template<br/>prompt template"]
  node_prompt_repr["Repr template<br/>prompt template"]
  node_example_format["Example format<br/>format template"]
  node_example_select["Example select<br/>retrieval template"]
  node_prompt_builder["Prompt builder<br/>assembly engine<br/>[prompt_builder.py]"]
  node_generate_question["Generate question<br/>retrieval orchestrator"]
end

subgraph group_g_infer["Inference pipeline"]
  node_ask_llm["Ask LLM<br/>inference runner<br/>[ask_llm.py]"]
  node_chatgpt["ChatGPT API<br/>model wrapper<br/>[chatgpt.py]"]
  node_post_process["Post process<br/>output cleanup<br/>[post_process.py]"]
  node_bird_output["BIRD output<br/>submission formatter<br/>[to_bird_output.py]"]
  node_run_scripts["Run scripts<br/>ops wrappers<br/>[run_dail_sql.sh]"]
end

node_dataset_spider -->|"raw records"| node_spider_dataset_adapter
node_spider_dataset_adapter -->|"normalized examples"| node_data_builder
node_data_builder -->|"prep input"| node_preprocess
node_preprocess -->|"schema context"| node_link_process
node_nltk_setup -->|"bootstraps"| node_corenlp
node_link_process -->|"uses"| node_link_utils
node_link_utils -->|"parse/tokenize"| node_corenlp
node_link_utils -->|"serialize"| node_serialization
node_link_utils -->|"match"| node_spider_match
node_data_builder -->|"prompt data"| node_generate_question
node_generate_question -->|"rank/select"| node_example_select
node_generate_question -->|"formatting"| node_prompt_templates
node_prompt_templates -->|"includes"| node_prompt_icl
node_prompt_templates -->|"includes"| node_prompt_repr
node_prompt_templates -->|"includes"| node_example_format
node_prompt_templates -->|"includes"| node_prompt_builder
node_example_select -->|"chosen demos"| node_prompt_builder
node_prompt_builder -->|"final prompt"| node_ask_llm
node_ask_llm -->|"api call"| node_chatgpt
node_ask_llm -->|"raw completion"| node_post_process
node_post_process -->|"format submit"| node_bird_output
node_run_scripts -->|"launch retrieval"| node_generate_question
node_run_scripts -->|"launch inference"| node_ask_llm

click node_spider_dataset_adapter "https://github.com/beachwang/dail-sql/blob/main/utils/datasets/spider.py"
click node_data_builder "https://github.com/beachwang/dail-sql/blob/main/utils/data_builder.py"
click node_preprocess "https://github.com/beachwang/dail-sql/blob/main/data_preprocess.py"
click node_nltk_setup "https://github.com/beachwang/dail-sql/blob/main/nltk_downloader.py"
click node_link_process "https://github.com/beachwang/dail-sql/blob/main/utils/linking_process.py"
click node_link_utils "https://github.com/beachwang/dail-sql/tree/main/utils/linking_utils"
click node_corenlp "https://github.com/beachwang/dail-sql/blob/main/utils/linking_utils/corenlp.py"
click node_serialization "https://github.com/beachwang/dail-sql/blob/main/utils/linking_utils/serialization.py"
click node_spider_match "https://github.com/beachwang/dail-sql/blob/main/utils/linking_utils/spider_match_utils.py"
click node_prompt_templates "https://github.com/beachwang/dail-sql/tree/main/prompt"
click node_prompt_icl "https://github.com/beachwang/dail-sql/blob/main/prompt/PromptICLTemplate.py"
click node_prompt_repr "https://github.com/beachwang/dail-sql/blob/main/prompt/PromptReprTemplate.py"
click node_example_format "https://github.com/beachwang/dail-sql/blob/main/prompt/ExampleFormatTemplate.py"
click node_example_select "https://github.com/beachwang/dail-sql/blob/main/prompt/ExampleSelectorTemplate.py"
click node_prompt_builder "https://github.com/beachwang/dail-sql/blob/main/prompt/prompt_builder.py"
click node_generate_question "https://github.com/beachwang/dail-sql/blob/main/generate_question.py"
click node_ask_llm "https://github.com/beachwang/dail-sql/blob/main/ask_llm.py"
click node_chatgpt "https://github.com/beachwang/dail-sql/blob/main/llm/chatgpt.py"
click node_post_process "https://github.com/beachwang/dail-sql/blob/main/utils/post_process.py"
click node_bird_output "https://github.com/beachwang/dail-sql/blob/main/to_bird_output.py"
click node_run_scripts "https://github.com/beachwang/dail-sql/blob/main/run_dail_sql.sh"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_dataset_spider,node_spider_dataset_adapter,node_data_builder toneBlue
class node_preprocess,node_nltk_setup,node_link_process,node_link_utils,node_corenlp,node_serialization,node_spider_match toneAmber
class node_prompt_templates,node_prompt_icl,node_prompt_repr,node_example_format,node_example_select,node_prompt_builder,node_generate_question toneMint
class node_ask_llm,node_chatgpt,node_post_process,node_bird_output,node_run_scripts toneRose
```

## Components

### Data boundary

Benchmark assets and dataset normalization layer.

- **Spider data**: benchmark dataset · Spider/BIRD-style benchmark files and database assets used as the main input boundary.
- [Spider adapter](https://github.com/beachwang/dail-sql/blob/main/utils/datasets/spider.py): dataset loader · Normalizes Spider records into repo-internal examples and metadata. · `utils/datasets/spider.py`
- [Data builder](https://github.com/beachwang/dail-sql/blob/main/utils/data_builder.py): data assembly · Builds prompt-ready data structures from benchmark records and database context. · `utils/data_builder.py`

### Linking pipeline

Offline preprocessing and schema-question linking.

- [Preprocess](https://github.com/beachwang/dail-sql/blob/main/data_preprocess.py): offline prep · Offline cleaning, enrichment, and cache preparation before prompt generation. · `data_preprocess.py`
- [CoreNLP setup](https://github.com/beachwang/dail-sql/blob/main/nltk_downloader.py): linguistic setup · Environment/bootstrap helper for CoreNLP-backed tokenization and parsing. · `nltk_downloader.py`
- [Link process](https://github.com/beachwang/dail-sql/blob/main/utils/linking_process.py): schema linking · Main schema-linking orchestration across question tokens and database schema items. · `utils/linking_process.py`
- [Link utils](https://github.com/beachwang/dail-sql/tree/main/utils/linking_utils): linking toolkit · RAT-SQL-inspired helpers for tokenization, parsing, serialization, and matching. · `utils/linking_utils`
- [CoreNLP client](https://github.com/beachwang/dail-sql/blob/main/utils/linking_utils/corenlp.py): nlp runtime · Wrapper around Stanford CoreNLP used during linguistic preprocessing. · `utils/linking_utils/corenlp.py`
- [Serialization](https://github.com/beachwang/dail-sql/blob/main/utils/linking_utils/serialization.py): schema serialization · Converts schema and linking structures into compact representations. · `utils/linking_utils/serialization.py`
- [Spider match](https://github.com/beachwang/dail-sql/blob/main/utils/linking_utils/spider_match_utils.py): matching rules · Spider-specific lexical and structural matching utilities. · `utils/linking_utils/spider_match_utils.py`

### Prompt pipeline

Example selection and prompt construction.

- [Prompt templates](https://github.com/beachwang/dail-sql/tree/main/prompt): prompt forms · Template layer for ICL, representation variants, and example formatting. · `prompt`
- [ICL template](https://github.com/beachwang/dail-sql/blob/main/prompt/PromptICLTemplate.py): prompt template · Few-shot in-context learning template for assembling demonstrations. · `prompt/PromptICLTemplate.py`
- [Repr template](https://github.com/beachwang/dail-sql/blob/main/prompt/PromptReprTemplate.py): prompt template · Question representation template used to vary prompt structure. · `prompt/PromptReprTemplate.py`
- [Example format](https://github.com/beachwang/dail-sql/blob/main/prompt/ExampleFormatTemplate.py): format template · Defines how selected examples are rendered inside the final prompt. · `prompt/ExampleFormatTemplate.py`
- [Example select](https://github.com/beachwang/dail-sql/blob/main/prompt/ExampleSelectorTemplate.py): retrieval template · Template and logic boundary for selecting demonstrations under token limits. · `prompt/ExampleSelectorTemplate.py`
- [Prompt builder](https://github.com/beachwang/dail-sql/blob/main/prompt/prompt_builder.py): assembly engine · Combines inputs, selected examples, and templates into the final LLM prompt. · `prompt/prompt_builder.py`
- [Generate question](https://github.com/beachwang/dail-sql/blob/main/generate_question.py): retrieval orchestrator · Main entry point for few-shot retrieval and prompt assembly. · `generate_question.py`

### Inference pipeline

LLM invocation and output normalization.

- [Ask LLM](https://github.com/beachwang/dail-sql/blob/main/ask_llm.py): inference runner · Runtime entry point that sends prompts to a model and collects completions. · `ask_llm.py`
- [ChatGPT API](https://github.com/beachwang/dail-sql/blob/main/llm/chatgpt.py): model wrapper · OpenAI-style API wrapper used for GPT-based inference. · `llm/chatgpt.py`
- [Post process](https://github.com/beachwang/dail-sql/blob/main/utils/post_process.py): output cleanup · Cleans and aligns raw model output into executable SQL or normalized text. · `utils/post_process.py`
- [BIRD output](https://github.com/beachwang/dail-sql/blob/main/to_bird_output.py): submission formatter · Converts predictions into BIRD-style benchmark submission format. · `to_bird_output.py`
- [Run scripts](https://github.com/beachwang/dail-sql/blob/main/run_dail_sql.sh): ops wrappers · Shell entry points for standard runs and self-consistency variants. · `run_dail_sql.sh`

## Connections

- Spider data → Spider adapter: raw records · Benchmark assets are loaded into the repo's dataset abstraction.
- Spider adapter → Data builder: normalized examples · Adapter feeds normalized examples and metadata into assembly.
- Data builder → Preprocess: prep input · Prepared records are enriched and cached offline.
- Preprocess → Link process: schema context · Preprocessed data is used for schema linking and question analysis.
- CoreNLP setup → CoreNLP client: bootstraps · Environment setup enables CoreNLP-backed processing.
- Link process → Link utils: uses · Linking orchestration depends on utility modules.
- Link utils → CoreNLP client: parse/tokenize · Utilities call into CoreNLP for linguistic analysis.
- Link utils → Serialization: serialize · Schema and linking structures are compacted for later use.
- Link utils → Spider match: match · Spider-specific matching rules support schema/question linking.
- Data builder → Generate question: prompt data · Built examples and metadata become retrieval inputs.
- Generate question → Example select: rank/select · Retrieval logic chooses k-shot demonstrations.
- Generate question → Prompt templates: formatting · The selector and builder consume prompt templates.
- Prompt templates → ICL template: includes · Template package contains the ICL form.
- Prompt templates → Repr template: includes · Template package contains representation variants.
- Prompt templates → Example format: includes · Template package defines example rendering.
- Prompt templates → Prompt builder: includes · Builder composes the final prompt from templates.
- Example select → Prompt builder: chosen demos · Selected examples are inserted into the final prompt.
- Prompt builder → Ask LLM: final prompt · Assembled prompt is sent to the inference runner.
- Ask LLM → ChatGPT API: api call · Inference runner delegates to the external model wrapper.
- Ask LLM → Post process: raw completion · Model output is cleaned and normalized after generation.
- Post process → BIRD output: format submit · Normalized results can be converted to benchmark submission format.
- Run scripts → Generate question: launch retrieval · Operational scripts start prompt construction jobs.
- Run scripts → Ask LLM: launch inference · Operational scripts also start model execution jobs.
