# hemanthvarmakonduru/texttosqlagent architecture

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

- Interactive diagram: https://gitdiagram.com/hemanthvarmakonduru/texttosqlagent
- Repository: https://github.com/hemanthvarmakonduru/texttosqlagent

## Overview

##### Project type and purpose
- This is a full-stack interactive data-analysis agent built in Python.

##### File structure
- Top-level files:
- app.py — Streamlit entry point (frontend)
- database_setup.py — DB schema and seed logic
- requirements.txt / setup.py — dependency definitions
- config/ and src/config/ — environment and application settings
- src/
- core/text_to_sql_agent.py — agent logic (transforms NL → SQL)
- utils/database.py — DB connection & query execution
- utils/visualization.py — charting & plots
- data/ — CSV files used to initialize the database
- docs/ — user and development documentation
- tests/ — unit & integration tests validating components and DB interactions

##### README for additional insights
- Tech Stack: Python, Streamlit (frontend), OpenAI API (external NL model), PostgreSQL (DB), pandas/visualization libs
- Workflow described: install → set env vars → setup DB → run Streamlit

a. Main components
1. Streamlit UI (app.py)
2. Text-to-SQL Agent (core/text_to_sql_agent.py)
3. Database Layer (PostgreSQL + src/utils/database.py)
4. Visualization Module (src/utils/visualization.py)
5. External OpenAI API
6. Configuration Management (config/settings.py, .env)
b. Relationships & interactions
- User → Streamlit UI: inputs natural-language query
- UI → Agent: sends NL text for conversion
- Agent → OpenAI API: issues API call to produce SQL
- Agent → Database Layer: executes SQL and fetches results
- Results → Visualization Module: generates charts & tables
- UI ← Visualization: displays output
- Config service provides API keys and DB credentials to all modules
c. Architectural patterns & design principles
- Clear separation of concerns:
– Presentation layer (UI),
– Business logic (Agent),
– Data access layer
- Modularity: utils, core, config packages
- Single-responsibility classes/functions in utils and core
- Dependency injection of settings from config modules
d. Key technologies & frameworks
- Python 3.x
- Streamlit for frontend
- OpenAI Python SDK
- psycopg2 or SQLAlchemy (via src/utils/database.py)
- pandas / matplotlib or altair (via visualization.py)
- PostgreSQL

1. Sketch high-level layers: Presentation → Business Logic → Data
2. Populate each layer with the actual components from the file tree
5. Surround environment/config files in a boundary labeled “Configuration”

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Actors
    User[(User)]:::external

    %% Presentation Layer
    subgraph "Presentation Layer"
        UI["Streamlit UI\n(app.py)"]:::internal
    end

    %% Business Logic
    subgraph "Business Logic Layer"
        Agent["Text-to-SQL Agent\n(src/core/text_to_sql_agent.py)"]:::internal
    end

    %% Data Layer
    subgraph "Data Layer"
        DBModule["DB Connection & Query Execution\n(src/utils/database.py)"]:::internal
        Viz["Visualization Module\n(src/utils/visualization.py)"]:::internal
        Schema["DB Schema & Seed Logic\n(database_setup.py)"]:::internal
        CSV1["CSV Data Source\n(airlines_flights_data.csv)"]:::data
        CSV2["CSV Data Source\n(bike_information.csv)"]:::data
        Postgres[(PostgreSQL DB)]:::db
    end

    %% External Services
    subgraph "External Services"
        OpenAI["OpenAI API"]:::external
    end

    %% Configuration
    subgraph "Configuration"
        CFG1["App Settings\n(config/settings.py)"]:::config
        CFG2["App Defaults\n(src/config/settings.py)"]:::config
        ENV["Env Example\n(env.example)"]:::config
    end

    %% Dependencies
    subgraph "Dependency Definitions"
        REQ["requirements.txt"]:::config
        SETUP["setup.py"]:::config
    end

    %% Testing
    subgraph "Tests"
        TU["Unit Tests\ntests/unit"]:::test
        TI["Integration Tests\ntests/integration"]:::test
    end

    %% Documentation
    subgraph "Documentation"
        DU["User Guide\ndocs/user_guide/SETUP_GUIDE.md"]:::doc
        DD["Dev Docs\ndocs/development/PROJECT_SUMMARY.md"]:::doc
    end

    %% Data Flows
    User -->|NL query| UI
    UI -->|send query| Agent
    Agent -->|HTTP API call| OpenAI
    OpenAI -->|response| Agent
    Agent -->|execute SQL| Postgres
    Postgres -->|return rows| Agent
    Agent -->|data frames| Viz
    Viz -->|render visuals| UI

    %% Configuration Flows
    ENV --> CFG1
    ENV --> CFG2
    CFG1 --> UI
    CFG1 --> Agent
    CFG1 --> DBModule
    CFG2 --> UI
    CFG2 --> Agent
    CFG2 --> DBModule
    CFG2 --> Viz

    %% Schema Initialization
    CSV1 -->|seed data| Schema
    CSV2 -->|seed data| Schema
    Schema -->|initialize| Postgres

    %% Testing Connections
    TU --> Agent
    TU --> DBModule
    TI --> DBModule

    %% Click Events
    click UI "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/app.py"
    click Agent "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/src/core/text_to_sql_agent.py"
    click Schema "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/database_setup.py"
    click DBModule "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/src/utils/database.py"
    click Viz "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/src/utils/visualization.py"
    click CFG1 "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/config/settings.py"
    click CFG2 "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/src/config/settings.py"
    click ENV "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/env.example"
    click CSV1 "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/data/airlines_flights_data.csv"
    click CSV2 "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/data/bike_information.csv"
    click REQ "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/requirements.txt"
    click SETUP "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/setup.py"
    click TU "https://github.com/hemanthvarmakonduru/texttosqlagent/tree/main/tests/unit"
    click TI "https://github.com/hemanthvarmakonduru/texttosqlagent/tree/main/tests/integration"
    click DU "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/docs/user_guide/SETUP_GUIDE.md"
    click DD "https://github.com/hemanthvarmakonduru/texttosqlagent/blob/main/docs/development/PROJECT_SUMMARY.md"

    %% Styles
    classDef internal fill:#add8e6,stroke:#333,stroke-width:1px
    classDef data fill:#9f6,stroke:#333,stroke-width:1px
    classDef db fill:#9f6,stroke:#333,stroke-width:1px
    classDef external fill:#f96,stroke:#333,stroke-width:1px
    classDef config fill:#ffeb99,stroke:#333,stroke-width:1px,stroke-dasharray:3 3
    classDef test fill:#eeeeee,stroke:#333,stroke-width:1px,stroke-dasharray:2 2
    classDef doc fill:#dddddd,stroke:#333,stroke-width:1px

    class Postgres db
    class OpenAI external
    class User external
```
