# stickman1257/nl2sql_1jo architecture

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

- Interactive diagram: https://gitdiagram.com/stickman1257/nl2sql_1jo
- Repository: https://github.com/stickman1257/nl2sql_1jo

## Overview

1. **Repository purpose**
   - This is a Python-based NL2SQL research workspace, organized as a template for building and comparing systems that convert natural language questions into SQL.
   - The code is split into reusable package modules under `src/nl2sql_agent/`, with surrounding folders for datasets, prompts, experiments, docs, and outputs.

2. **Top-level architectural boundaries**
   - The repo separates **code** (`src/`), **runtime inputs** (`data/`, `configs/`, `prompts/`), **evaluation and research artifacts** (`experiments/`, `notebooks/`), and **documentation/media** (`docs/`, `assets/`).
   - That split is important because most runtime behavior is driven by external configuration and prompt files rather than hard-coded values.

3. **Data layer**
   - `data/raw/` holds benchmark datasets such as **Spider** and **BIRD SQL**.
   - `data/samples/` contains a sample SQLite database and NL2SQL logs for local testing.
   - `data/schema/` stores structured schema knowledge, including `ecommerce_kb.json`, which appears to be a knowledge-base representation of database schema metadata.

4. **Schema knowledge subsystem**
   - `src/nl2sql_agent/schema/` contains the schema knowledge base layer:
     - `kb_store.py` for persistence/access,
     - `schema_kb.py` for KB structure and retrieval,
     - `diagnosis.py` for schema-related analysis.
   - This subsystem is a central boundary between raw database schemas and the downstream SQL-generation logic.

5. **Schema enricher pipeline**
   - `src/nl2sql_agent/schema_enricher/` is a separate pipeline that inspects databases, selects informative columns, calls an LLM, filters logs, and updates the schema KB.
   - Key roles:
     - `db_explorer.py` discovers schema/content signals from a database,
     - `column_selector.py` chooses columns to enrich,
     - `llm_caller.py` invokes the model,
     - `kb_updater.py` writes back enriched knowledge,
     - `pipeline.py` orchestrates the flow.
   - The presence of `configs/dataset/enricher_config*.yaml` and `scripts/run_enricher.py` indicates this is an executable preprocessing/enrichment step, not just library code.

6. **SQL generation agent**
   - `src/nl2sql_agent/generate_sql/` is the main NL2SQL inference path.
   - It includes:
     - `agent.py` for orchestration,
     - `db_search_tool.py` for schema/database lookup,
     - `sql_writing_tool.py` for SQL construction,
     - `llm_client.py` for model access,
     - `bird.py` and `evaluate_bird.py` for benchmark-specific handling/evaluation.
   - This module likely consumes schema KB context and a user question, then produces SQL and benchmark outputs.

7. **Answer-generation / execution-facing path**
   - `src/nl2sql_agent/generate_answer/` suggests a second stage that turns SQL or intermediate reasoning into final answers, likely including database execution and response formatting.
   - Files like `data_sql.py`, `llm_io.py`, `qwen_model.py`, and `run_answer_agent.py` indicate a model-facing runtime that may be distinct from pure SQL synthesis.
   - This creates a boundary between “generate SQL” and “generate answer from SQL/results.”

8. **Decomposition and query understanding**
   - `src/nl2sql_agent/decomposition/` and the prompt folder `prompts/subquery_decomposition/` indicate a decomposition-oriented architecture for breaking complex questions into subqueries or reasoning steps.
   - `src/nl2sql_agent/query_classifier/` contains training and feature extraction scripts, suggesting a separate analysis/training component for query-type classification or routing.

9. **Prompt and config management**
   - `prompts/` is organized by function: `system`, `schema_refinement`, `subquery_decomposition`, and `answer_generation`.
   - `configs/` separates dataset, model, and prompt configuration, which makes the system behavior reproducible across experiments and backends.
   - The repo includes both CUDA and MLX enricher configs, so the architecture supports multiple local inference runtimes.

10. **Evaluation and experimentation**
   - `src/nl2sql_agent/evaluation/` plus `experiments/` show that the repo is designed for systematic comparison of methods.
   - `experiments/baseline/` and `experiments/ablation/` track variants such as:
     - few-shot prompting,
     - chain-of-thought prompting,
     - removing decomposition,
     - removing execution feedback,
     - removing schema refinement.
   - `experiments/results/` stores benchmark outcomes for Spider and BIRD SQL.

11. **Documentation and research workflow**
   - `docs/` contains flow descriptions and integration notes, including schema KB feedback and local MLX validation.
   - `assets/` stores diagrams, figures, and presentation materials, which reinforces that this repo is both a working codebase and a research/reporting workspace.

12. **Tests and quality boundary**
   - `tests/test_kb_store.py` and `tests/test_schema_refinement.py` show that coverage focuses on the schema KB and refinement pipeline, which are core architectural seams.
   - This implies the most critical correctness risks are in schema persistence and schema-to-prompt refinement logic, not just SQL string generation.

13. **Likely end-to-end data flow**
   - Raw benchmark DBs and sample DBs are enriched into schema KB artifacts.
   - The NL2SQL agent reads the KB plus prompt/config context, optionally decomposes the query, searches relevant schema pieces, and generates SQL.
   - SQL may then be executed or transformed into a final answer, with logs/results feeding evaluation and ablation experiments.

Main entry points: [`src/nl2sql_agent/generate_sql`](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/generate_sql) (SQL agent), [`src/nl2sql_agent/schema_enricher`](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/schema_enricher) (Enricher), [`src/nl2sql_agent/schema`](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/schema) (Schema KB), [`prompts`](https://github.com/stickman1257/nl2sql_1jo/tree/main/prompts) (Prompts)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_inputs["Runtime inputs"]
  node_data_raw[("Raw data<br/>datasets")]
  node_data_samples[("Sample DBs<br/>local sample")]
  node_schema_kb_data["Schema KB<br/>kb artifact"]
  node_configs_root["Configs<br/>runtime config"]
  node_prompts_root["Prompts<br/>prompt library"]
end

subgraph group_code["Core code"]
  node_schema_enricher["Enricher<br/>preprocess pipeline"]
  node_schema_kb_code["Schema KB<br/>schema layer"]
  node_generate_sql["SQL agent<br/>nl2sql inference"]
  node_generate_answer["Answer agent<br/>answering runtime"]
  node_decomposition["Decomposition<br/>query reasoning"]
  node_query_classifier["Query classifier<br/>routing model"]
  node_evaluation["Evaluation<br/>metrics layer"]
  node_agent_entry["Run agent<br/>entrypoint"]
  node_enricher_entry["Run enricher<br/>entrypoint<br/>[run_enricher.py]"]
  node_tests_root["Tests<br/>quality gate"]
end

subgraph group_research["Research workflow"]
  node_baseline_experiments["Baselines<br/>experiment variants"]
  node_ablation_experiments["Ablations<br/>experiment variants"]
  node_results["Results<br/>benchmark outputs"]
end

subgraph group_docs["Docs and media"]
  node_docs_root["Docs<br/>documentation"]
  node_assets_root["Assets<br/>media"]
end

node_data_raw -->|"schemas to enrich"| node_schema_enricher
node_data_samples -->|"local validation"| node_schema_enricher
node_schema_enricher -->|"writes KB"| node_schema_kb_data
node_configs_root -->|"runtime config"| node_schema_enricher
node_prompts_root -->|"prompting"| node_schema_enricher
node_schema_kb_data -->|"loads KB"| node_schema_kb_code
node_schema_kb_code -->|"schema context"| node_generate_sql
node_decomposition -->|"subqueries"| node_generate_sql
node_query_classifier -->|"routes"| node_generate_sql
node_prompts_root -->|"prompting"| node_generate_sql
node_configs_root -->|"model config"| node_generate_sql
node_generate_sql -->|"benchmarks"| node_evaluation
node_generate_sql -->|"SQL/results"| node_generate_answer
node_prompts_root -->|"answer prompts"| node_generate_answer
node_tests_root -->|"validates"| node_schema_kb_code
node_baseline_experiments -->|"compares"| node_results
node_ablation_experiments -->|"compares"| node_results
node_docs_root -.->|"setup notes"| node_schema_enricher
node_docs_root -.->|"integration notes"| node_schema_kb_code
node_assets_root -.->|"figures"| node_docs_root
node_agent_entry -->|"starts"| node_generate_sql
node_enricher_entry -->|"starts"| node_schema_enricher

click node_data_raw "https://github.com/stickman1257/nl2sql_1jo/tree/main/data/raw"
click node_data_samples "https://github.com/stickman1257/nl2sql_1jo/tree/main/data/samples"
click node_schema_kb_data "https://github.com/stickman1257/nl2sql_1jo/tree/main/data/schema"
click node_configs_root "https://github.com/stickman1257/nl2sql_1jo/tree/main/configs"
click node_prompts_root "https://github.com/stickman1257/nl2sql_1jo/tree/main/prompts"
click node_schema_enricher "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/schema_enricher"
click node_schema_kb_code "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/schema"
click node_generate_sql "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/generate_sql"
click node_generate_answer "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/generate_answer"
click node_decomposition "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/decomposition"
click node_query_classifier "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/query_classifier"
click node_evaluation "https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/evaluation"
click node_agent_entry "https://github.com/stickman1257/nl2sql_1jo/blob/main/src/nl2sql_agent/run_generate_sql.py"
click node_enricher_entry "https://github.com/stickman1257/nl2sql_1jo/blob/main/scripts/run_enricher.py"
click node_baseline_experiments "https://github.com/stickman1257/nl2sql_1jo/tree/main/experiments/baseline"
click node_ablation_experiments "https://github.com/stickman1257/nl2sql_1jo/tree/main/experiments/ablation"
click node_results "https://github.com/stickman1257/nl2sql_1jo/tree/main/experiments/results"
click node_docs_root "https://github.com/stickman1257/nl2sql_1jo/tree/main/docs"
click node_assets_root "https://github.com/stickman1257/nl2sql_1jo/tree/main/assets"
click node_tests_root "https://github.com/stickman1257/nl2sql_1jo/tree/main/tests"

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_data_raw,node_data_samples,node_schema_kb_data,node_configs_root,node_prompts_root toneBlue
class node_schema_enricher,node_schema_kb_code,node_generate_sql,node_generate_answer,node_decomposition,node_query_classifier,node_evaluation,node_agent_entry,node_enricher_entry,node_tests_root toneAmber
class node_baseline_experiments,node_ablation_experiments,node_results toneMint
class node_docs_root,node_assets_root toneRose
```

## Components

### Runtime inputs

Datasets, schemas, prompts, and configs that steer the system at runtime.

- [Raw data](https://github.com/stickman1257/nl2sql_1jo/tree/main/data/raw): datasets · Benchmark corpora such as Spider and BIRD SQL. · `data/raw`
- [Sample DBs](https://github.com/stickman1257/nl2sql_1jo/tree/main/data/samples): local sample · Local SQLite database and NL2SQL logs for quick validation. · `data/samples`
- [Schema KB](https://github.com/stickman1257/nl2sql_1jo/tree/main/data/schema): kb artifact · Persisted schema knowledge base, including ecommerce_kb.json. · `data/schema`
- [Configs](https://github.com/stickman1257/nl2sql_1jo/tree/main/configs): runtime config · Dataset, model, and prompt configuration surfaces. · `configs`
- [Prompts](https://github.com/stickman1257/nl2sql_1jo/tree/main/prompts): prompt library · System, decomposition, schema refinement, and answer-generation prompts. · `prompts`

### Core code

Reusable Python package modules implementing enrichment, generation, and evaluation.

- [Enricher](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/schema_enricher): preprocess pipeline · Database inspection and KB update pipeline. · `src/nl2sql_agent/schema_enricher`
- [Schema KB](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/schema): schema layer · KB storage, retrieval, and diagnosis boundary. · `src/nl2sql_agent/schema`
- [SQL agent](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/generate_sql): nl2sql inference · Main path for question-to-SQL generation. · `src/nl2sql_agent/generate_sql`
- [Answer agent](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/generate_answer): answering runtime · Executes or post-processes SQL/results into final answers. · `src/nl2sql_agent/generate_answer`
- [Decomposition](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/decomposition): query reasoning · Breaks complex questions into subqueries or steps. · `src/nl2sql_agent/decomposition`
- [Query classifier](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/query_classifier): routing model · Training and feature extraction for query-type routing. · `src/nl2sql_agent/query_classifier`
- [Evaluation](https://github.com/stickman1257/nl2sql_1jo/tree/main/src/nl2sql_agent/evaluation): metrics layer · Benchmark evaluation helpers and reporting hooks. · `src/nl2sql_agent/evaluation`
- [Run agent](https://github.com/stickman1257/nl2sql_1jo/blob/main/src/nl2sql_agent/run_generate_sql.py): entrypoint · CLI entrypoint for SQL generation workflows. · `src/nl2sql_agent/run_generate_sql.py`
- [Run enricher](https://github.com/stickman1257/nl2sql_1jo/blob/main/scripts/run_enricher.py): entrypoint · CLI entrypoint for schema enrichment. · `scripts/run_enricher.py`
- [Tests](https://github.com/stickman1257/nl2sql_1jo/tree/main/tests): quality gate · Focused tests for KB storage and schema refinement. · `tests`

### Research workflow

Experiments, notebooks, and outputs used to compare variants and record results.

- [Baselines](https://github.com/stickman1257/nl2sql_1jo/tree/main/experiments/baseline): experiment variants · Few-shot and chain-of-thought comparison runs. · `experiments/baseline`
- [Ablations](https://github.com/stickman1257/nl2sql_1jo/tree/main/experiments/ablation): experiment variants · Variants removing decomposition, execution feedback, or schema refinement. · `experiments/ablation`
- [Results](https://github.com/stickman1257/nl2sql_1jo/tree/main/experiments/results): benchmark outputs · Stored outcomes for Spider and BIRD SQL runs. · `experiments/results`

### Docs and media

Repository documentation, meeting notes, and presentation assets.

- [Docs](https://github.com/stickman1257/nl2sql_1jo/tree/main/docs): documentation · Flow notes, setup guides, and integration documentation. · `docs`
- [Assets](https://github.com/stickman1257/nl2sql_1jo/tree/main/assets): media · Figures, diagrams, and presentation materials. · `assets`

## Connections

- Raw data → Enricher: schemas to enrich · Raw benchmark databases are inspected to build richer schema knowledge.
- Sample DBs → Enricher: local validation · Sample DBs and logs support local enrichment and debugging.
- Enricher → Schema KB: writes KB · Enrichment updates the persisted schema knowledge base artifacts.
- Configs → Enricher: runtime config · Dataset/runtime options control enrichment behavior.
- Prompts → Enricher: prompting · Prompt templates guide model-based schema enrichment.
- Schema KB → Schema KB: loads KB · The code layer reads and interprets persisted schema knowledge.
- Schema KB → SQL agent: schema context · Generated SQL relies on KB retrieval and schema diagnosis.
- Decomposition → SQL agent: subqueries · Decomposed questions can feed the SQL synthesis path.
- Query classifier → SQL agent: routes · Classification can steer the generation strategy or prompt path.
- Prompts → SQL agent: prompting · System and schema-refinement prompts shape SQL generation.
- Configs → SQL agent: model config · Model and dataset settings parameterize inference.
- SQL agent → Evaluation: benchmarks · Produced SQL is scored against benchmark suites.
- SQL agent → Answer agent: SQL/results · SQL output or intermediate reasoning can feed answer generation.
- Prompts → Answer agent: answer prompts · Answer-generation prompts shape the response stage.
- Tests → Schema KB: validates · Tests focus on KB storage and schema refinement correctness.
- Baselines → Results: compares · Baseline prompt strategies are recorded in benchmark results.
- Ablations → Results: compares · Ablation variants are tracked against benchmark outputs.
- Docs → Enricher: setup notes · Documentation captures enrichment setup and validation flows.
- Docs → Schema KB: integration notes · Docs explain schema KB feedback and integration details.
- Assets → Docs: figures · Assets supply diagrams and presentation media used by docs.
- Run agent → SQL agent: starts · CLI entrypoint launches the SQL generation workflow.
- Run enricher → Enricher: starts · CLI entrypoint launches schema enrichment.