# datrics-ai/text2sql architecture

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

- Interactive diagram: https://gitdiagram.com/datrics-ai/text2sql
- Repository: https://github.com/datrics-ai/text2sql

## Overview

Based on an analysis of the file tree and README, this project is an open‐source Text-to-SQL engine that leverages natural language processing with advanced retrieval-augmented generation (RAG) techniques. The solution consists of a user interface built with Streamlit, a backend engine (packaged under “t2sql”) that processes natural language to generate SQL queries, and integration components for database connectivity, semantic documentation, and vector embeddings (using ChromaDB).

##### Main Components
- Frontend/UI Component:
- Represented by “main.py” and the “app” directory (including “app/app.py” and the “pages” subfolder). This is the Streamlit-based interface that users interact with.

- Backend Processing Engine:
- Represented by the “t2sql” package. This includes modules such as:
- “agent.py” – likely the gateway coordinating query processing.
- “controller” submodules (e.g., ingest_documentation.py and make_answer.py) – which handle documentation ingestion and query answer generation.
- “ingestors” (e.g., text_document_ingestor.py) – to ingest and process text documentation.
- “sql” submodule (including “client.py”) – for handling interactions with the database.
- “vectordb” modules (e.g., chromadb.py) – to store and retrieve vector embeddings.

- Configuration and Descriptor:
- “descriptors” directory (with the JSON configuration file “t2sql_descriptor.json”) that sets up LLM settings and database connectivity details (including potential SSH tunnel configuration).

- Deployment and Containerization:
- Docker-related files such as “Dockerfile”, “docker-compose.yml”, and shell scripts (“init-db.sh”, “run.sh”) that support deployment and local database setups.

##### Establish Interactions and Data Flow
- From the UI (Streamlit Application), user actions (like clicking “Run schema indexing” or submitting a query) are transmitted to the backend.
- The backend’s Agent passes the request to the appropriate Controller:
- For example, “ingest_documentation” may read database schema/documentation, while “make_answer” generates SQL from natural language queries.
- The Controllers leverage Ingestors (which parse text documents) and call LLM functions (configured via the descriptors JSON) to generate semantic understanding.
- Data flows from these backend processes to external components:
- The SQL Client connects to the target database.
- The Vector DB (integrated via the “chromadb” module) is utilized for storing and searching vector embeddings related to the semantic layer.

##### Architectural Patterns and Design Principles
- Separation of Concerns:
- Frontend components (Streamlit UI) are clearly separated from the backend processing (T2SQL Engine).
- Within the backend, use modular separation (Controllers, Ingestors, Vector DB, SQL client) to handle discrete parts of the processing.
- Extensibility:
- The project’s design allows for adding support for other LLMs and vector databases (hints are present in the README that support is coming soon).
- Containerization and Deployment:

##### Elements
- Clear labels for each component:
- “Streamlit UI (app/main.py, app/pages/)”
- “T2SQL Engine (agent, controllers, ingestors, sql client, vectordb)”
- “Configuration/Descriptors (JSON config for LLM and database)”
- “Deployment/Containerization (Docker, docker-compose, init scripts)”
- Data flow from UI to backend and vice versa.
- Dependencies between configuration settings and backend modules.
- Interactions between controllers, ingestors, and external services (LLM, database, vector DB).
- UI versus backend versus infrastructure components.
- External systems (e.g., database, third-party APIs) from internal modules.

##### Final Recommendations
- Drill down into the T2SQL Engine by splitting it into smaller modules (Agent, Controllers, Ingestors, etc.) and connect their interactions.

Happy diagramming!

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Frontend/UI Layer
    subgraph "Frontend/UI"
        UI1["main.py"]:::ui
        UI2["app/app.py & app/pages/"]:::ui
        UI_Combined["Streamlit UI"]:::ui
        UI1 --> UI_Combined
        UI2 --> UI_Combined
    end

    %% T2SQL Engine Layer
    subgraph "T2SQL Engine"
        A["Agent (t2sql/agent.py)"]:::backend
        C1["Controller (Ingest Documentation) (t2sql/controller/ingest_documentation.py)"]:::backend
        C2["Controller (Query Answer Generation) (t2sql/controller/make_answer.py)"]:::backend
        I["Ingestor (Text Document) (t2sql/ingestors/text_document_ingestor.py)"]:::backend
        S["SQL Client (t2sql/sql/client.py)"]:::backend
        V["Vector DB (ChromaDB) (t2sql/vectordb/chromadb.py)"]:::backend
    end

    %% Configuration Layer
    subgraph "Configuration/Descriptors"
        CONFIG["Descriptor (t2sql_descriptor.json)"]:::config
    end

    %% Deployment/Environment Layer
    subgraph "Deployment/Environment"
        D1["Dockerfile"]:::deploy
        D2["docker-compose.yml"]:::deploy
        D3["init-db.sh"]:::deploy
        D4["run.sh"]:::deploy
        D5["dvdrental.tar"]:::deploy
        DEPLOY["Deployment/Env"]:::deploy
        D1 --> DEPLOY
        D2 --> DEPLOY
        D3 --> DEPLOY
        D4 --> DEPLOY
        D5 --> DEPLOY
    end

    %% Data Flow Connections
    UI_Combined -->|"UserRequest"| A
    A -->|"IngestionRequest"| C1
    A -->|"AnswerRequest"| C2
    C1 -->|"ParseDocs"| I
    C2 -->|"SQLGeneration"| S
    C2 -->|"VectorLookup"| V
    CONFIG -->|"Configures"| A
    CONFIG -->|"Configures"| S
    CONFIG -->|"Configures"| V
    S -->|"DBConnection"| DEPLOY
    V -->|"Store/RetrieveVectors"| DEPLOY

    %% Click Events for Frontend/UI
    click UI1 "https://github.com/datrics-ai/text2sql/blob/main/main.py"
    click UI2 "https://github.com/datrics-ai/text2sql/tree/main/app/"

    %% Click Events for T2SQL Engine
    click A "https://github.com/datrics-ai/text2sql/blob/main/t2sql/agent.py"
    click C1 "https://github.com/datrics-ai/text2sql/blob/main/t2sql/controller/ingest_documentation.py"
    click C2 "https://github.com/datrics-ai/text2sql/blob/main/t2sql/controller/make_answer.py"
    click I "https://github.com/datrics-ai/text2sql/blob/main/t2sql/ingestors/text_document_ingestor.py"
    click S "https://github.com/datrics-ai/text2sql/blob/main/t2sql/sql/client.py"
    click V "https://github.com/datrics-ai/text2sql/blob/main/t2sql/vectordb/chromadb.py"

    %% Click Event for Configuration
    click CONFIG "https://github.com/datrics-ai/text2sql/blob/main/descriptors/default/t2sql_descriptor.json"

    %% Click Events for Deployment/Environment
    click D1 "https://github.com/datrics-ai/text2sql/tree/main/Dockerfile"
    click D2 "https://github.com/datrics-ai/text2sql/blob/main/docker-compose.yml"
    click D3 "https://github.com/datrics-ai/text2sql/blob/main/init-db.sh"
    click D4 "https://github.com/datrics-ai/text2sql/blob/main/run.sh"
    click D5 "https://github.com/datrics-ai/text2sql/blob/main/dvdrental.tar"

    %% Styles
    classDef ui fill:#AEDFF7,stroke:#1F78C1,stroke-width:2px;
    classDef backend fill:#C7F1C4,stroke:#33A02C,stroke-width:2px;
    classDef config fill:#F9E79F,stroke:#F39C12,stroke-width:2px;
    classDef deploy fill:#FADBD8,stroke:#E74C3C,stroke-width:2px;
```
