# devbm7/text2sql architecture

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

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

## Overview

##### Project type and purpose
- Text2SQL is a full-stack, single-page interactive data-query application built with Streamlit (frontend + lightweight Python backend).
- Its purpose is to let users upload a CSV, ask questions in natural language, convert those questions to SQL via an LLM (Ollama), run the SQL against a SQLite database, and display results.

##### File structure
- Top level:
- README.md – project overview and usage
- main.py – entrypoint for the Streamlit app, orchestrates all stages
- data.csv – example or sample CSV data
- db.sqlite – generated SQLite database file
- notebook.ipynb – exploratory analysis or prototyping
- No deep subdirectories—logic is contained in main.py and imported libraries.

##### Key components from README
- Frontend: Streamlit interface for file upload, question input, result display
- CSV ingestion → automatic SQLite DB creation
- Question caching to avoid repeated LLM calls
- Ollama LLM service for NL→SQL
- Pandas for data manipulation, LangChain for prompt handling, scikit-learn (used for caching similarity)

##### Main components
a. Streamlit UI (frontend)
- File uploader widget
- Text input widget for natural-language questions
- Result display table
b. Backend application logic (main.py)
- CSV → SQLite ingestion module
- Cache layer (question → SQL)
- LLM interface module (calls Ollama)
- SQL execution module (runs against SQLite)
- Result formatting & return
c. Data storage
- SQLite database
d. External service
- Ollama LLM server (llama3.1:8b model)

##### Relationships and data flows
1) User uploads CSV via Streamlit → invoke ingestion module → populate SQLite
2) User enters NL question → cache lookup
- Hit → retrieve cached SQL
- Miss → send prompt to Ollama → receive SQL, store in cache
3) SQL → execute on SQLite → fetch result set
4) Results → format and send back to Streamlit UI → render table

##### Architectural patterns and important design principles
- Single-page interactive app (UI and backend in one process)
- Modular pipeline: ingestion, caching, LLM translation, query execution
- Separation of concerns: UI vs business logic vs data/persistence vs external LLM
- Use of external microservice (Ollama) for heavy LLM inference

##### Technologies, frameworks, and libraries
- Streamlit (web UI)
- Python (3.7+) as runtime
- Pandas for CSV handling
- SQLite3 for embedded database
- LangChain-community for prompt management
- Ollama LLM server
- scikit-learn for similarity search (cache)

## Diagram (Mermaid)

```mermaid
flowchart TD
    User["User"]:::user
    UI["Streamlit UI<br/>(Streamlit)"]:::frontend
    CSV["Sample CSV Data<br/>(data.csv)"]:::data
    DB["SQLite Database<br/>(db.sqlite)"]:::db
    LLMsvc["Ollama LLM Server<br/>(llama3.1:8b)"]:::external
    Doc["Project Documentation<br/>(README.md)"]:::doc
    NB["Exploratory Analysis Notebook<br/>(notebook.ipynb)"]:::doc

    subgraph "Application Server"
        Ingest["CSV Ingestion Module<br/>(Pandas → SQLite3)"]:::backend
        Cache["Cache Layer Module<br/>(scikit-learn)"]:::backend
        LLM["LLM Interface Module<br/>(Ollama API)"]:::backend
        Executor["SQL Execution Module<br/>(SQLite3)"]:::backend
        Formatter["Result Formatting & Return"]:::backend
    end

    User --> UI
    UI -->|1. Upload CSV| CSV
    CSV -->|Ingest| Ingest
    Ingest -->|Populate| DB
    UI -->|2. Enter NL Question| Cache
    Cache -->|Hit: SQL| Executor
    Cache -->|Miss: Prompt| LLM
    LLM -->|SQL Response| Cache
    LLM -->|API Call| LLMsvc
    Executor -->|Query| DB
    Executor -->|Results| Formatter
    Formatter -->|Display Results| UI

    click UI "https://github.com/devbm7/text2sql/blob/main/main.py"
    click Ingest "https://github.com/devbm7/text2sql/blob/main/main.py"
    click Cache "https://github.com/devbm7/text2sql/blob/main/main.py"
    click LLM "https://github.com/devbm7/text2sql/blob/main/main.py"
    click Executor "https://github.com/devbm7/text2sql/blob/main/main.py"
    click Formatter "https://github.com/devbm7/text2sql/blob/main/main.py"
    click DB "https://github.com/devbm7/text2sql/blob/main/db.sqlite"
    click CSV "https://github.com/devbm7/text2sql/blob/main/data.csv"
    click Doc "https://github.com/devbm7/text2sql/blob/main/README.md"
    click NB "https://github.com/devbm7/text2sql/blob/main/notebook.ipynb"

    classDef user fill:#eeeeee,stroke:#333,stroke-width:1px
    classDef frontend fill:#cce5ff,stroke:#0366d6,stroke-width:1px
    classDef backend fill:#d4edda,stroke:#28a745,stroke-width:1px
    classDef db fill:#fff3cd,stroke:#e0a800,stroke-width:2px
    classDef external fill:#f8d7da,stroke:#d73a49,stroke-width:1px
    classDef data fill:#e2e3e5,stroke:#6a737d,stroke-width:1px
    classDef doc fill:#e2e3e5,stroke:#6a737d,stroke-width:1px
```
