# radi07/sql_agent architecture

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

- Interactive diagram: https://gitdiagram.com/radi07/sql_agent
- Repository: https://github.com/radi07/sql_agent

## Overview

##### Project Type and Purpose
- This is a full-stack AI agent: a Streamlit frontend (app.py, dashboard.py, assets/style.css) backed by a Python package (“querymancer/”) that turns natural-language into SQL queries, routes them through multi-model inference, applies optimization strategies, and talks to a local SQLite database and remote LLM APIs.

##### File Structure
- Top-level entrypoints:
- app.py (Streamlit chat UI)
- dashboard.py (performance dashboard)
- Static assets: assets/style.css
- Executables/scripts: bin/create-database
- Data layer: data/ecommerce.sqlite (schema illustrated in README)
- Logs & metrics: logs/{calibration_history.json, model_performance.json}
- Core library: querymancer/
- agent.py – orchestrator
- config.py – loads .env and selects models
- logging.py – centralized logger setup
- models.py – wrappers for Groq & SambaNova API calls
- optimizations.py – complexity router, token optimizer, threshold calibrator
- tools.py – schema introspection & SQL builder
- Tests: test_*.py plus JSON snapshots for complexity & token optimization

##### Extract Architecture from README
- Presentation: Streamlit UI for conversation + dashboard
- Application logic: “Querymancer agent” that interprets user input, introspects schema, builds and optimizes SQL, then calls appropriate LLM
- Multi-model inference: Groq (“llama-3.3-70b-versatile”) vs. SambaNova (“DeepSeek-R1”)
- Optimization pipelines: routing by complexity, token reduction, parameter auto-tuning
- Monitoring & calibration: logs feed performance dashboard and automatically adjust thresholds

##### Main Components
a. Frontend Layer
- Streamlit App (app.py)
- Static CSS (assets/style.css)
- Dashboard (dashboard.py)
b. Backend Core (querymancer package)
- Agent Orchestrator
- Schema Introspection (tools.py)
- SQL Generator (tools.py)
- Optimization Pipelines (optimizations.py):
- Complexity Router (with LRU cache)
- Token Optimization Pipeline
- Threshold Calibration
- Model Wrappers (models.py)
- Configuration Loader (config.py)
- Logging & Monitoring (logging.py)
c. Data Layer
- SQLite Database (data/ecommerce.sqlite)
d. External Services Layer
- Groq API
- SambaNova API
e. Cross-Cutting Concerns
- Environment Variables (.env for API keys)
- Logs & Metrics Store (logs/*.json)

##### Relationships and Data Flows
1. User enters query in Streamlit UI →
2. Agent receives NL input →
3. Agent calls Schema Introspector to fetch table/column metadata →
4. Agent generates raw SQL →
5. Raw SQL → Complexity Router → routes to appropriate model path →
6. Selected path triggers Token Optimization Pipeline →
7. Optimized SQL and context sent to Model Wrapper →
8. Wrapper invokes Groq or SambaNova REST API (with keys from config) →
9. LLM response → Agent post-processes and returns result to UI →
10. Throughout: Logging module records decisions, complexity scores, performance metrics → logs JSON → Dashboard reads logs

- Threshold Calibrator periodically reads historical logs → adjusts complexity thresholds stored in config or a local store → influences future routing decisions.

##### Architectural Patterns & Design Principles
- Layered Architecture (Presentation, Application, Domain, Infrastructure)
- Strategy Pattern for choosing optimization algorithms & model selection
- Pipeline Pattern for token optimization stages
- Caching (LRU) in Complexity Router to avoid repeated analyses
- Observer/Publisher-Subscriber for performance monitoring and calibration
- Single Responsibility: each module focuses on one concern (e.g., config, logging, optimization)

##### Relevant Technologies & Frameworks
- Python 3.12 (managed via uv)
- Streamlit (UI)
- SQLite (built-in DB)
- REST APIs (Groq, SambaNova)
- JSON for logs and calibration history
- Pre-commit, pyproject.toml for dependency & dev tooling

##### Putting It All Together
- Central block: Querymancer Core (sub-divide into orchestration, tools, optimizations, models, config, logging)
- Above: Streamlit UI → below: SQLite DB → to the right: External LLM APIs
- Side loop: logs → dashboard and calibrator → back into optimizations
- Final tip: break out each optimization strategy as its own sub-component to show granularity and extensibility.

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Frontend Layer
    subgraph "Frontend Layer"
        A1["Streamlit App\n(app.py)"]:::frontend
        A2["Dashboard UI\n(dashboard.py)"]:::frontend
        A3["Static Assets\n(style.css)"]:::frontend
    end

    %% Core Backend Layer
    subgraph "Core Backend (Querymancer)"
        O1["Agent Orchestrator\n(agent.py)"]:::backend
        O2["Schema Introspection &\nSQL Generator\n(tools.py)"]:::backend
        subgraph "Optimization Pipelines"
            O3["Complexity Router\n(LRU Cache)"]:::backend
            O4["Token Optimization"]:::backend
            O5["Threshold Calibration"]:::backend
        end
        O6["Model Wrappers\n(models.py)"]:::backend
    end

    %% Data Layer
    subgraph "Data Layer"
        DB["SQLite DB\n(ecommerce.sqlite)"]:::data
    end

    %% External Services
    subgraph "External Services"
        E1(("Groq API")):::external
        E2(("SambaNova API")):::external
    end

    %% Cross-Cutting Concerns
    subgraph "Cross-Cutting"
        C1["Env Variables\n(.env.example)"]:::cross
        C2["Logs Store\ncalibration_history.json\nmodel_performance.json"]:::cross
    end

    %% Interactions
    A1 -->|"NL Query"| O1
    O1 -->|"fetch schema metadata"| O2
    O2 -->|"raw SQL"| O3
    O3 -->|"route to model path"| O4
    O4 -->|"optimized SQL + context"| O6
    O6 -->|"LLM Request"| E1
    O6 -->|"LLM Request"| E2
    E1 -->|"LLM Response"| O1
    E2 -->|"LLM Response"| O1
    O1 -->|"Result"| A1

    O1 -->|"logs events"| C2
    C2 -->|"reads logs"| O5
    O5 -->|"adjust thresholds"| O3

    C2 -->|"feeds dashboard"| A2

    bincreate["DB Init Script\n(bin/create-database)"]:::backend
    bincreate --> DB

    %% Click Events
    click A1 "https://github.com/radi07/sql_agent/blob/master/app.py"
    click A2 "https://github.com/radi07/sql_agent/blob/master/dashboard.py"
    click A3 "https://github.com/radi07/sql_agent/blob/master/assets/style.css"
    click bincreate "https://github.com/radi07/sql_agent/tree/master/bin/create-database"
    click DB "https://github.com/radi07/sql_agent/blob/master/data/ecommerce.sqlite"
    click O1 "https://github.com/radi07/sql_agent/blob/master/querymancer/agent.py"
    click O2 "https://github.com/radi07/sql_agent/blob/master/querymancer/tools.py"
    click O3 "https://github.com/radi07/sql_agent/blob/master/querymancer/optimizations.py"
    click O4 "https://github.com/radi07/sql_agent/blob/master/querymancer/optimizations.py"
    click O5 "https://github.com/radi07/sql_agent/blob/master/querymancer/optimizations.py"
    click O6 "https://github.com/radi07/sql_agent/blob/master/querymancer/models.py"
    click C1 "https://github.com/radi07/sql_agent/blob/master/.env.example"
    click C2 "https://github.com/radi07/sql_agent/blob/master/logs/calibration_history.json"
    click C2 "https://github.com/radi07/sql_agent/blob/master/logs/model_performance.json"

    %% Styles
    classDef frontend fill:#E3F2FD,stroke:#90CAF9,stroke-width:2px
    classDef backend fill:#E8F5E9,stroke:#66BB6A,stroke-width:2px
    classDef data fill:#FFF3E0,stroke:#FFB74D,stroke-width:2px
    classDef external fill:#FFE0B2,stroke:#FB8C00,stroke-width:2px
    classDef cross fill:#F5F5F5,stroke:#BDBDBD,stroke-width:2px
```
