# contextualai/bird-sql architecture

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

- Interactive diagram: https://gitdiagram.com/contextualai/bird-sql
- Repository: https://github.com/contextualai/bird-sql

## Overview

##### Project type and purpose
- This is a staged, full-pipeline text-to-SQL system built in Python. It ingests the BIRD benchmark, prepares schemas, generates SQL candidates via a large language model, executes those candidates on a database, scores them with a second “reward” model, and finally performs analysis to choose the best SQL.

##### Main components
a. Data & Schema Preparation
- Script: src/prep_data.py
- Inputs: Raw BIRD JSON, few_shots/mschema definitions
- Outputs: data/ directory with processed JSONL and schema files
b. Generator Inference
- Script: src/generate.py
- Outputs: SQL candidate files under output/generations/
c. SQL Execution
- Script: src/process_sqls.py & helper src/process_sqls_helper.py
- External Service: SQL database instance (e.g., Postgres or SQLite)
- Outputs: Results + filtered candidates under output/with_results/
d. Reward Scoring
- Script: src/reward.py
- Model: models/reward (ctx-bird-reward)
- Outputs: Scored candidates in output/with_rewards/
e. Analysis & Selection
- Script: src/analysis.py
- Inputs: Scored candidates + gold SQLs
- Outputs: Final analysis report under output/analysis/

##### Relationships & Data Flow
- Data Prep → Generator → SQL DB → Reward → Analysis
- The generator stage reads processed JSONL and schema files, writes SQL candidates.
- The SQL execution stage reads those candidates and writes execution outputs.
- The reward stage reads execution outputs and computes a score per candidate.
- The analysis stage reads scores and ground-truth SQL to pick winners.

##### Architectural patterns & principles
- Pipeline (linear, stage-based) with explicit data hand-off via JSONL files.
- Modular scripts grouped under src/ for single responsibility.
- External dependencies (HuggingFace model store, SQL database) are decoupled from core code.
- Parallelism hints: generator & execution can run concurrently; reward uses multi-GPU; analysis uses multi-CPU.

##### Technologies & frameworks
- Python 3.10 scripts
- HuggingFace CLI & transformers models
- VLLM or native GPU model inference
- JSONL for intermediate data serialization
- Relational database (Postgres, SQLite) for SQL execution
- Requirements managed via requirements.txt

##### Essential details to capture
- Input directory layouts (data/, few_shots/)
- models/generator vs. models/reward separation
- use of JSONL files as contract between stages
- timeout tuning parameter at SQL execution
- final output directory structure under output/

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Data & Schema Preparation
    subgraph "Data & Schema Prep"
        Prep["Data & Schema Prep\n(src/prep_data.py)"]:::compute
        RawData["Raw BIRD JSON"]:::dataStorage
        InputSchema["Input schemas\n(few_shots/mschema/)"]:::dataStorage
        ProcessedData["Processed JSONL & schemas\n(data/)"]:::dataStorage
    end

    %% Generator Inference
    subgraph "Generator Inference"
        GenScript["Generator Inference\n(src/generate.py)"]:::compute
        ModelGen["Generator Model\n(models/generator)"]:::external
        GenOutput["SQL candidates\n(output/generations/)"]:::dataStorage
    end

    %% SQL Execution
    subgraph "SQL Execution"
        ProcSQL["Process SQLs\n(src/process_sqls.py)"]:::compute
        Helper["Helper Module\n(src/process_sqls_helper.py)"]:::compute
        SQLDB[(SQL Database)]:::external
        ExecOutput["Results & filtered\n(output/with_results/)"]:::dataStorage
    end

    %% Reward Scoring
    subgraph "Reward Scoring"
        Reward["Reward Scoring\n(src/reward.py)"]:::compute
        ModelReward["Reward Model\n(models/reward)"]:::external
        RewardOutput["Scored candidates\n(output/with_rewards/)"]:::dataStorage
    end

    %% Analysis & Selection
    subgraph "Analysis & Selection"
        Analysis["Analysis & Selection\n(src/analysis.py)"]:::compute
        GoldSQL["Gold SQLs\n(data/ or few_shots/)"]:::dataStorage
        FinalReport["Final report\n(output/analysis/)"]:::dataStorage
    end

    %% Data Flow
    RawData --> Prep
    InputSchema --> Prep
    Prep --> ProcessedData

    ProcessedData --> GenScript
    ModelGen --> GenScript
    GenScript --> GenOutput

    GenOutput --> ProcSQL
    Helper --> ProcSQL
    SQLDB --> ProcSQL
    ProcSQL --> ExecOutput

    ExecOutput --> Reward
    ModelReward --> Reward
    Reward --> RewardOutput

    RewardOutput --> Analysis
    GoldSQL --> Analysis
    Analysis --> FinalReport

    %% Click Events
    click Prep "https://github.com/contextualai/bird-sql/blob/main/src/prep_data.py"
    click InputSchema "https://github.com/contextualai/bird-sql/tree/main/few_shots/mschema/"
    click ProcessedData "https://github.com/contextualai/bird-sql/tree/main/data/"
    click GenScript "https://github.com/contextualai/bird-sql/blob/main/src/generate.py"
    click ModelGen "https://github.com/contextualai/bird-sql/tree/main/models/generator"
    click GenOutput "https://github.com/contextualai/bird-sql/tree/main/output/generations/"
    click ProcSQL "https://github.com/contextualai/bird-sql/blob/main/src/process_sqls.py"
    click Helper "https://github.com/contextualai/bird-sql/blob/main/src/process_sqls_helper.py"
    click ExecOutput "https://github.com/contextualai/bird-sql/tree/main/output/with_results/"
    click Reward "https://github.com/contextualai/bird-sql/blob/main/src/reward.py"
    click ModelReward "https://github.com/contextualai/bird-sql/tree/main/models/reward"
    click RewardOutput "https://github.com/contextualai/bird-sql/tree/main/output/with_rewards/"
    click Analysis "https://github.com/contextualai/bird-sql/blob/main/src/analysis.py"
    click FinalReport "https://github.com/contextualai/bird-sql/tree/main/output/analysis/"

    %% Styles
    classDef dataStorage fill:#ADD8E6,stroke:#333,stroke-width:1px
    classDef compute fill:#90EE90,stroke:#333,stroke-width:1px
    classDef external fill:#FFA500,stroke:#333,stroke-width:1px
```
