# riishi-01/text2sql-agent architecture

> Architecture diagram of the GitHub repository riishi-01/text2sql-agent, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-09-25). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/riishi-01/text2sql-agent
- Repository: https://github.com/riishi-01/text2sql-agent

## Overview

Olist NL2SQL accepts natural-language questions through a CLI or agent workflow, builds a dataset-aware prompt, generates SQL with an LLM, validates it, and either refuses or runs it read-only against PostgreSQL. A separate FastAPI endpoint accepts SQL directly and executes restricted SELECT queries. The evaluation harness is a major product capability: it runs curated cases, grades SQL across rubric dimensions, resumes runs with checkpoints, and reports results. Evaluation wiring is summarized from the README; its runner implementation was not sampled.

Main entry points: [`agent/graph.py`](https://github.com/riishi-01/text2sql-agent/blob/main/agent/graph.py) (Agent Workflow), [`evals/scripts/eval_runner.py`](https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/eval_runner.py) (Eval Runner), [`api/routes/query.py`](https://github.com/riishi-01/text2sql-agent/blob/main/api/routes/query.py) (Query Route), [`agent/main.py`](https://github.com/riishi-01/text2sql-agent/blob/main/agent/main.py) (CLI)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_agent["NL2SQL Agent"]
  node_cli["CLI<br/>[main.py]"]
  node_workflow["Agent Workflow<br/>[graph.py]"]
  node_prompt["Prompt Builder<br/>[builder.py]"]
  node_schema["Schema Metadata<br/>[loader.py]"]
  node_validator["SQL Validator<br/>[sql_validator.py]"]
end

subgraph group_api["Query API"]
  node_app["FastAPI App<br/>[app.py]"]
  node_queryroute["Query Route<br/>[query.py]"]
  node_models["Query Models<br/>[models.py]"]
  node_health["Health Route<br/>[health.py]"]
end

subgraph group_data["Database Access"]
  node_readonly["Read-only DB Access<br/>[connection.py]"]
end

subgraph group_eval["Evaluation Harness"]
  node_cases["Curated Cases<br/>[cases.yaml]"]
  node_runner["Eval Runner<br/>[eval_runner.py]"]
  node_rubric["Rubric Grader<br/>[rubric.py]"]
  node_checkpoint[("Run Checkpoints")]
  node_report["Report Generator"]
  node_evalresults["Reference Results"]
end

node_questioner(("Questioner"))
node_llm["OpenAI LLM"]
node_postgres[("PostgreSQL")]
node_answer["SQL and Results"]
node_apiuser(("API Client"))

node_questioner -->|"asks question"| node_cli
node_cli -->|"runs agent"| node_workflow
node_workflow -->|"builds prompt"| node_prompt
node_prompt -->|"gets dataset date"| node_schema
node_schema -->|"reads metadata and date"| node_postgres
node_workflow -->|"requests SQL"| node_llm
node_llm -->|"returns SQL"| node_workflow
node_workflow -->|"validates SQL"| node_validator
node_workflow -->|"executes valid SQL"| node_readonly
node_readonly -->|"queries safely"| node_postgres
node_workflow -->|"returns outcome"| node_answer
node_cli -->|"displays outcome"| node_answer
node_apiuser -->|"sends request"| node_app
node_app -->|"dispatches route"| node_queryroute
node_queryroute -->|"uses request and response"| node_models
node_queryroute -->|"executes restricted SELECT"| node_postgres
node_queryroute -->|"returns rows or error"| node_apiuser
node_cases -->|"provides cases"| node_runner
node_runner -->|"runs agent"| node_workflow
node_runner -->|"grades results"| node_rubric
node_runner -->|"saves progress"| node_checkpoint
node_runner -->|"provides run results"| node_report
node_report -->|"writes reports"| node_evalresults

click node_cli "https://github.com/riishi-01/text2sql-agent/blob/main/agent/main.py"
click node_workflow "https://github.com/riishi-01/text2sql-agent/blob/main/agent/graph.py"
click node_prompt "https://github.com/riishi-01/text2sql-agent/blob/main/agent/prompts/builder.py"
click node_schema "https://github.com/riishi-01/text2sql-agent/blob/main/agent/schema/loader.py"
click node_validator "https://github.com/riishi-01/text2sql-agent/blob/main/agent/validator/sql_validator.py"
click node_readonly "https://github.com/riishi-01/text2sql-agent/blob/main/agent/db/connection.py"
click node_app "https://github.com/riishi-01/text2sql-agent/blob/main/api/app.py"
click node_queryroute "https://github.com/riishi-01/text2sql-agent/blob/main/api/routes/query.py"
click node_models "https://github.com/riishi-01/text2sql-agent/blob/main/api/models.py"
click node_health "https://github.com/riishi-01/text2sql-agent/blob/main/api/routes/health.py"
click node_cases "https://github.com/riishi-01/text2sql-agent/blob/main/evals/cases.yaml"
click node_runner "https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/eval_runner.py"
click node_rubric "https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/rubric.py"
click node_checkpoint "https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/checkpoint_manager.py"
click node_report "https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/report_generator.py"
click node_evalresults "https://github.com/riishi-01/text2sql-agent/tree/main/evals/results/baseline"

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_cli,node_workflow,node_prompt,node_schema,node_validator,node_apiuser toneBlue
class node_app,node_queryroute,node_models,node_health,node_postgres toneAmber
class node_readonly toneMint
class node_cases,node_runner,node_rubric,node_checkpoint,node_report,node_evalresults toneRose
class node_questioner,node_llm,node_answer toneIndigo
```

## Components

### NL2SQL Agent

- [CLI](https://github.com/riishi-01/text2sql-agent/blob/main/agent/main.py): `agent/main.py`
- [Agent Workflow](https://github.com/riishi-01/text2sql-agent/blob/main/agent/graph.py): `agent/graph.py`
- [Prompt Builder](https://github.com/riishi-01/text2sql-agent/blob/main/agent/prompts/builder.py): `agent/prompts/builder.py`
- [Schema Metadata](https://github.com/riishi-01/text2sql-agent/blob/main/agent/schema/loader.py): `agent/schema/loader.py`
- [SQL Validator](https://github.com/riishi-01/text2sql-agent/blob/main/agent/validator/sql_validator.py): `agent/validator/sql_validator.py`

### Query API

- [FastAPI App](https://github.com/riishi-01/text2sql-agent/blob/main/api/app.py): `api/app.py`
- [Query Route](https://github.com/riishi-01/text2sql-agent/blob/main/api/routes/query.py): `api/routes/query.py`
- [Query Models](https://github.com/riishi-01/text2sql-agent/blob/main/api/models.py): `api/models.py`
- [Health Route](https://github.com/riishi-01/text2sql-agent/blob/main/api/routes/health.py): `api/routes/health.py`

### Database Access

- [Read-only DB Access](https://github.com/riishi-01/text2sql-agent/blob/main/agent/db/connection.py): `agent/db/connection.py`

### Evaluation Harness

- [Curated Cases](https://github.com/riishi-01/text2sql-agent/blob/main/evals/cases.yaml): `evals/cases.yaml`
- [Eval Runner](https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/eval_runner.py): `evals/scripts/eval_runner.py`
- [Rubric Grader](https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/rubric.py): `evals/scripts/rubric.py`
- [Run Checkpoints](https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/checkpoint_manager.py): `evals/scripts/checkpoint_manager.py`
- [Report Generator](https://github.com/riishi-01/text2sql-agent/blob/main/evals/scripts/report_generator.py): `evals/scripts/report_generator.py`
- [Reference Results](https://github.com/riishi-01/text2sql-agent/tree/main/evals/results/baseline): `evals/results/baseline`

### Other components

- **Questioner**
- **OpenAI LLM**
- **PostgreSQL**
- **SQL and Results**
- **API Client**

## Connections

- Questioner → CLI: asks question
- CLI → Agent Workflow: runs agent
- Agent Workflow → Prompt Builder: builds prompt
- Prompt Builder → Schema Metadata: gets dataset date
- Schema Metadata → PostgreSQL: reads metadata and date
- Agent Workflow → OpenAI LLM: requests SQL
- OpenAI LLM → Agent Workflow: returns SQL
- Agent Workflow → SQL Validator: validates SQL
- Agent Workflow → Read-only DB Access: executes valid SQL
- Read-only DB Access → PostgreSQL: queries safely
- Agent Workflow → SQL and Results: returns outcome
- CLI → SQL and Results: displays outcome
- API Client → FastAPI App: sends request
- FastAPI App → Query Route: dispatches route
- Query Route → Query Models: uses request and response
- Query Route → PostgreSQL: executes restricted SELECT
- Query Route → API Client: returns rows or error
- Curated Cases → Eval Runner: provides cases
- Eval Runner → Agent Workflow: runs agent
- Eval Runner → Rubric Grader: grades results
- Eval Runner → Run Checkpoints: saves progress
- Eval Runner → Report Generator: provides run results
- Report Generator → Reference Results: writes reports