# text2sqlagent/text2sql-framework architecture

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

- Interactive diagram: https://gitdiagram.com/text2sqlagent/text2sql-framework
- Repository: https://github.com/text2sqlagent/text2sql-framework

## Overview

1. **What this repo is**
   - `text2sql` is a Python SDK that turns a natural-language question into a verified SQL query and result set.
   - It is built around an **agentic loop**: the LLM can inspect schema, try SQL, observe errors/results, and self-correct until it can answer.

2. **Primary entrypoint and public API**
   - `text2sql.core.TextSQL` is the main façade.
   - From the outside, callers give it a database connection string, optional model choice, optional scenario/examples file, and optional trace output path.
   - The returned result contains both the final SQL and the query data, so the architecture exposes the verified query as a first-class output, not just the answer text.

3. **Database boundary**
   - `text2sql.connection.Database` wraps SQLAlchemy and is the boundary to the real database.
   - The repo is database-agnostic at the API level, but dialect-specific behavior matters for schema discovery and introspection.
   - Supported backends include SQLite, PostgreSQL, MySQL, SQL Server, and Snowflake through their SQLAlchemy drivers.

4. **Dialect-aware schema exploration**
   - `text2sql.dialects` contains per-dialect guidance for discovering schema information.
   - This is important because the agent does not rely on precomputed schema embeddings or RAG; instead it directly queries the database metadata using the right dialect-specific mechanism.
   - Example strategies mentioned in the README include `PRAGMA` for SQLite and `information_schema`/system catalogs for other engines.

5. **Agent and SQL generation loop**
   - `text2sql.generate.SQLGenerator` assembles the agent, runs the iterative loop, and parses the final outcome.
   - The core behavior is exploratory: the LLM can issue `execute_sql` repeatedly, inspect table lists and column metadata, then refine the query.
   - This is the central control plane of the system and the main place where model behavior and tool availability shape the architecture.

6. **Tool boundary**
   - `text2sql.tools` defines the tools exposed to the agent, primarily:
     - `execute_sql` for querying the database
     - `lookup_example` for fetching relevant scenario guidance
   - These tools are the only capabilities the agent needs; the design intentionally avoids a larger tool surface to keep the loop focused on SQL synthesis and validation.

7. **Scenario/example knowledge layer**
   - `text2sql.examples.ExampleStore` loads markdown scenarios from a file like `scenarios.md`.
   - Scenarios are not dumped into context up front; they are indexed by heading and fetched on demand via `lookup_example`.
   - This layer injects domain-specific rules, naming translations, and join conventions that are not inferable from schema alone.

8. **Tracing and observability**
   - `text2sql.tracing` captures the full agentic interaction: tool calls, SQL attempts, errors, and corrections.
   - Traces are written to a file when configured, which makes the system auditable and also feeds the feedback loop for improving scenarios.
   - In architecture terms, this is an important side channel between runtime execution and later analysis.

9. **Analysis and feedback loop**
   - `text2sql.analyze` and `text2sql.models` support deterministic trace analysis and structured reporting.
   - The README describes a workflow where traces are analyzed to identify repeated failure patterns and then converted into better scenario guidance.
   - This creates a closed improvement loop: query execution produces traces, traces produce scenarios, scenarios improve future query success.

10. **CLI and packaging layer**
    - `text2sql.cli` provides command-line access for interactive asking and single-shot querying.
    - The package is configured in `pyproject.toml`, with optional extras for model providers such as Anthropic and OpenAI.
    - This is a thin shell over the same core engine, not a separate execution path.

11. **Testing and examples**
    - `tests/test_core.py` likely validates the public API and core behavior at the package boundary.
    - The `examples/` directory demonstrates real usage patterns, including dummy DB runs and scenario-driven workflows.
    - These are support artifacts, but they reflect the intended architecture: one core engine, multiple entry surfaces.

12. **Overall data flow**
    - A user question enters `TextSQL`.
    - The agent uses the database wrapper and dialect guidance to inspect schema.
    - It may fetch scenario help from the example store.
    - It executes SQL through the tool layer, reads results/errors, iterates, and emits a final verified SQL statement plus data.
    - If tracing is enabled, the entire loop is recorded for later analysis and scenario refinement.

Main entry points: [`text2sql/core.py`](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/core.py) (TextSQL), [`text2sql/generate.py`](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/generate.py) (SQL Generator), [`text2sql/agent.py`](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/agent.py) (Agent), [`text2sql/tools.py`](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/tools.py) (Tools)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_core["Core engine"]
  node_n1["TextSQL<br/>façade API<br/>[core.py]"]
  node_n2["SQL Generator<br/>agent loop<br/>[generate.py]"]
  node_n3["Agent<br/>orchestration<br/>[agent.py]"]
  node_n6["Tools<br/>[tools.py]"]
end

subgraph group_data["Data access"]
  node_n4[("Database<br/>SQLAlchemy boundary<br/>[connection.py]")]
  node_n5["Dialects<br/>schema guidance<br/>[dialects.py]"]
end

subgraph group_knowledge["Scenario knowledge"]
  node_n7["Example Store<br/>scenario index<br/>[examples.py]"]
  node_n8["Scenarios<br/>markdown knowledge<br/>[scenarios.md]"]
end

subgraph group_ops["Observability"]
  node_n9["Tracing<br/>runtime log<br/>[tracing.py]"]
  node_n10["Analyze<br/>trace analysis<br/>[analyze.py]"]
  node_n11["Models<br/>report types<br/>[models.py]"]
end

subgraph group_interface["Interfaces"]
  node_n12["CLI<br/>command shell<br/>[cli.py]"]
  node_n13["Package Init<br/>exports<br/>[__init__.py]"]
  node_n14["README<br/>usage guide<br/>[README.md]"]
  node_n15["Examples<br/>demo usage"]
  node_n16["Tests<br/>[test_core.py]"]
end

node_n1 -->|"invokes"| node_n2
node_n2 -->|"assembles"| node_n3
node_n3 -->|"uses tools"| node_n6
node_n6 -->|"executes SQL"| node_n4
node_n3 -->|"inspects schema"| node_n5
node_n5 -->|"introspects"| node_n4
node_n6 -->|"looks up"| node_n7
node_n7 -->|"loads"| node_n8
node_n2 -->|"records"| node_n9
node_n9 -->|"feeds analysis"| node_n10
node_n10 -->|"structures"| node_n11
node_n10 -.->|"improves"| node_n8
node_n12 -->|"calls"| node_n1
node_n13 -->|"exports"| node_n1
node_n14 -.->|"documents"| node_n1
node_n15 -.->|"demonstrates"| node_n1
node_n16 -.->|"verifies"| node_n1

click node_n1 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/core.py"
click node_n2 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/generate.py"
click node_n3 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/agent.py"
click node_n4 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/connection.py"
click node_n5 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/dialects.py"
click node_n6 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/tools.py"
click node_n7 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/examples.py"
click node_n8 "https://github.com/text2sqlagent/text2sql-framework/blob/main/examples/scenarios.md"
click node_n9 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/tracing.py"
click node_n10 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/analyze.py"
click node_n11 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/models.py"
click node_n12 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/cli.py"
click node_n13 "https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/__init__.py"
click node_n14 "https://github.com/text2sqlagent/text2sql-framework/blob/main/README.md"
click node_n15 "https://github.com/text2sqlagent/text2sql-framework/blob/main/examples/demo_with_dummy_db.py"
click node_n16 "https://github.com/text2sqlagent/text2sql-framework/blob/main/tests/test_core.py"

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_n1,node_n2,node_n3,node_n6 toneBlue
class node_n4,node_n5 toneAmber
class node_n7,node_n8 toneMint
class node_n9,node_n10,node_n11 toneRose
class node_n12,node_n13,node_n14,node_n15,node_n16 toneIndigo
```

## Components

### Core engine

Public API and agentic SQL synthesis loop.

- [TextSQL](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/core.py): façade API · Main public entrypoint for asking questions and getting verified SQL plus results. · `text2sql/core.py`
- [SQL Generator](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/generate.py): agent loop · Builds the agent, runs iterative SQL execution, and returns the final outcome. · `text2sql/generate.py`
- [Agent](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/agent.py): orchestration · Coordinates tool use, schema inspection, retries, and self-correction. · `text2sql/agent.py`
- [Tools](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/tools.py): agent tools · Defines execute_sql and lookup_example as the agent's limited action surface. · `text2sql/tools.py`

### Data access

Database boundary and dialect-specific introspection.

- [Database](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/connection.py): SQLAlchemy boundary · Wraps the live database connection and provides the execution boundary. · `text2sql/connection.py`
- [Dialects](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/dialects.py): schema guidance · Dialect-aware schema discovery and metadata access strategies. · `text2sql/dialects.py`

### Scenario knowledge

On-demand examples and domain guidance.

- [Example Store](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/examples.py): scenario index · Loads markdown scenarios and retrieves relevant guidance on demand. · `text2sql/examples.py`
- [Scenarios](https://github.com/text2sqlagent/text2sql-framework/blob/main/examples/scenarios.md): markdown knowledge · Human-authored scenario and domain rule content used by the example store. · `examples/scenarios.md`

### Observability

Tracing, analysis, and reporting feedback loop.

- [Tracing](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/tracing.py): runtime log · Captures tool calls, SQL attempts, errors, and corrections for later analysis. · `text2sql/tracing.py`
- [Analyze](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/analyze.py): trace analysis · Deterministic inspection of traces to surface repeated failure patterns. · `text2sql/analyze.py`
- [Models](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/models.py): report types · Structured data models used for analysis and reporting. · `text2sql/models.py`

### Interfaces

CLI and package entry surface.

- [CLI](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/cli.py): command shell · Command-line entry surface for interactive and single-shot querying. · `text2sql/cli.py`
- [Package Init](https://github.com/text2sqlagent/text2sql-framework/blob/main/text2sql/__init__.py): exports · Package-level exports that expose the public SDK surface. · `text2sql/__init__.py`
- [README](https://github.com/text2sqlagent/text2sql-framework/blob/main/README.md): usage guide · Documents the agentic workflow, supported backends, and operational loop. · `README.md`
- [Examples](https://github.com/text2sqlagent/text2sql-framework/blob/main/examples/demo_with_dummy_db.py): demo usage · Runnable demonstrations of the SDK and scenario-driven workflows. · `examples/demo_with_dummy_db.py`
- [Tests](https://github.com/text2sqlagent/text2sql-framework/blob/main/tests/test_core.py): boundary tests · Validates the public API and core behavior at the package boundary. · `tests/test_core.py`

## Connections

- TextSQL → SQL Generator: invokes · TextSQL delegates query solving to the generator loop.
- SQL Generator → Agent: assembles · The generator constructs and runs the agent orchestration.
- Agent → Tools: uses tools · The agent can only act through the exposed SQL and lookup tools.
- Tools → Database: executes SQL · execute_sql routes queries through the database boundary.
- Agent → Dialects: inspects schema · The agent relies on dialect-specific discovery guidance.
- Dialects → Database: introspects · Dialect strategies target the underlying connection and metadata APIs.
- Tools → Example Store: looks up · lookup_example fetches relevant scenario guidance on demand.
- Example Store → Scenarios: loads · Scenario content is stored in markdown files.
- SQL Generator → Tracing: records · Execution attempts and outcomes can be traced end-to-end.
- Tracing → Analyze: feeds analysis · Recorded traces are later analyzed for failure patterns.
- Analyze → Models: structures · Analysis results are represented with structured models.
- Analyze → Scenarios: improves · Analysis can inform better scenario guidance.
- CLI → TextSQL: calls · The CLI is a thin shell over the core façade.
- Package Init → TextSQL: exports · Package init exposes the public SDK entrypoint.
- README → TextSQL: documents · The README describes the primary runtime flow and API.
- Examples → TextSQL: demonstrates · Examples show how callers use the same core engine.
- Tests → TextSQL: verifies · Tests exercise the public boundary and main behavior.
