# ivoryrabbit/sql-helper architecture

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

- Interactive diagram: https://gitdiagram.com/ivoryrabbit/sql-helper
- Repository: https://github.com/ivoryrabbit/sql-helper

## Overview

##### Project type and purpose
- From the README (“sql-helper: Text-to-SQL using LLM and RAG”) and the presence of both a Streamlit app (app.py, .streamlit/) and a standalone API + worker (api/), this is a full-stack, service-oriented application.
- Purpose: Allow users to enter natural language questions via a UI, invoke a retrieval-augmented generation (RAG) pipeline and LLMs to produce SQL queries, execute them against a Postgres database (and optionally vector stores), and return results.

##### File structure
- Top-level:
- app.py + .streamlit/ → Streamlit frontend
- api/ → Python API + Temporal workflows + worker
- src/ → core libraries: LLM adapters, vector store abstractions, SQL agent logic, client-side controller/page code
- docker-related files & docker-compose.yaml → local dev environment for Postgres and Temporal
- resource/sql & resource/config → sample schemas and dev/prod configuration
- bin/ → start-up scripts for local and production
- Architectural clues:
- Separation of UI (app.py + src/client) from backend logic (api/src, core modules)
- Use of Temporal (api/src/component/{workflow,activities}.py + api/worker.py) implies an orchestration service pattern
- Multiple vector store implementations (chromadb, pgvector, qdrant) under src/core/vector_store suggest a plugin architecture

##### Key insights from README
- Runtime stack: Python 3.9, Streamlit for UI
- LLM platforms: OpenAI, Cohere, Claude, Meta Llama
- RAG backends: ChromaDB, Postgres PGVector
- Workflow: ingest question → fetch context (vector store) → call LLM → synthesize SQL → run against Postgres → return results

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Presentation Layer
    subgraph "Presentation Layer"
        UI["Streamlit App"]:::frontend
        UIcfg[".streamlit/config.toml"]:::config
        subgraph "Client Controller"
            CtrlBoost["bootstrap.py"]:::frontend
            CtrlChat["chat.py"]:::frontend
            CtrlData["data.py"]:::frontend
            CtrlSetting["setting.py"]:::frontend
        end
        subgraph "Client Pages"
            PageHome["home.py"]:::frontend
            PageChat["chat.py"]:::frontend
            PageData["data.py"]:::frontend
            PageSetting["setting.py"]:::frontend
        end
    end

    %% API Layer
    subgraph "API Layer"
        API["api/src/main.py"]:::backend
        Req["api/requirements.txt"]:::config
        subgraph "Models"
            MReq["question.py"]:::model
            MResp["sql_query.py"]:::model
            MBase["base.py"]:::model
            MDdl["ddl_collection.py"]:::model
        end
        subgraph "API Clients"
            COpenAI["openai_async.py"]:::client
            CDatabase["database.py"]:::client
            CTemporalClient["temporal.py"]:::client
        end
    end

    %% Orchestration Layer
    subgraph "Orchestration Layer"
        Worker["api/worker.py"]:::backend
        subgraph "Workflow Components"
            WF["workflow.py"]:::backend
            Act["activities.py"]:::backend
        end
        TempClient["api/src/client/temporal.py"]:::client
    end

    %% Domain Layer
    subgraph "Domain Layer"
        SQLAgent["src/core/sql_agent.py"]:::domain
        subgraph "Vector Store Plugins"
            VsIfc["vector_store.py"]:::domain
            VsChroma["chromadb.py"]:::domain
            VsPg["pgvector.py"]:::domain
            VsQdrant["qdrant.py"]:::domain
        end
        subgraph "LLM Adapters"
            AsIfc["assistant.py"]:::domain
            AsOpenAI["openai.py"]:::domain
            AsCohere["cohere.py"]:::domain
            AsLlama["llama.py"]:::domain
            AsMock["mock.py"]:::domain
        end
    end

    %% Data Layer
    subgraph "Data Layer"
        Postgres["Postgres DB"]:::database
        ChromaDB["ChromaDB"]:::database
        QdrantDB["Qdrant"]:::database
    end

    %% External Services
    subgraph "External Services"
        ExtOpenAI["OpenAI"]:::external
        ExtCohere["Cohere"]:::external
        ExtClaude["Claude"]:::external
        ExtLlama["Meta LLaMA"]:::external
    end

    %% Configurations & Scripts
    subgraph "Configurations"
        LocalCfg["project-local.toml"]:::config
        ProdCfg["project-prod.toml"]:::config
    end

    %% Flows
    UI -->|"HTTP request"| API
    API -->|"start workflow"| TempClient
    TempClient -->|"Start Workflow"| Worker
    Worker -->|"invoke fetch embeddings"| VsIfc
    VsChroma -->|"uses"| ChromaDB
    VsPg -->|"uses"| Postgres
    VsQdrant -->|"uses"| QdrantDB
    Worker -->|"invoke LLM"| AsIfc
    AsOpenAI -->|"calls API"| ExtOpenAI
    AsCohere -->|"calls API"| ExtCohere
    AsLlama -->|"calls API"| ExtLlama
    Worker -->|"compose SQL"| SQLAgent
    Worker -->|"execute SQL"| Postgres
    Worker -->|"return result"| API
    API -->|"response"| UI
    LocalCfg --- API
    LocalCfg --- UI
    LocalCfg --- Worker
    ProdCfg -.-> API
    ProdCfg -.-> UI
    ProdCfg -.-> Worker

    %% Click Events
    click UI "https://github.com/ivoryrabbit/sql-helper/blob/master/app.py"
    click UIcfg "https://github.com/ivoryrabbit/sql-helper/blob/master/.streamlit/config.toml"
    click CtrlBoost "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/controller/bootstrap.py"
    click CtrlChat "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/controller/chat.py"
    click CtrlData "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/controller/data.py"
    click CtrlSetting "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/controller/setting.py"
    click PageHome "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/page/home.py"
    click PageChat "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/page/chat.py"
    click PageData "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/page/data.py"
    click PageSetting "https://github.com/ivoryrabbit/sql-helper/blob/master/src/client/page/setting.py"
    click API "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/main.py"
    click Req "https://github.com/ivoryrabbit/sql-helper/blob/master/api/requirements.txt"
    click MReq "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/model/request/question.py"
    click MResp "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/model/response/sql_query.py"
    click MBase "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/model/base.py"
    click MDdl "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/model/ddl_collection.py"
    click COpenAI "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/client/openai_async.py"
    click CDatabase "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/client/database.py"
    click CTemporalClient "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/client/temporal.py"
    click Worker "https://github.com/ivoryrabbit/sql-helper/blob/master/api/worker.py"
    click Act "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/component/activities.py"
    click WF "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/component/workflow.py"
    click TempClient "https://github.com/ivoryrabbit/sql-helper/blob/master/api/src/client/temporal.py"
    click SQLAgent "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/sql_agent.py"
    click VsIfc "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/interface/vector_store.py"
    click VsChroma "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/vector_store/chromadb.py"
    click VsPg "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/vector_store/pgvector.py"
    click VsQdrant "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/vector_store/qdrant.py"
    click AsIfc "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/interface/assistant.py"
    click AsOpenAI "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/assistant/openai.py"
    click AsCohere "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/assistant/cohere.py"
    click AsLlama "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/assistant/llama.py"
    click AsMock "https://github.com/ivoryrabbit/sql-helper/blob/master/src/core/assistant/mock.py"
    click LocalCfg "https://github.com/ivoryrabbit/sql-helper/blob/master/resource/config/project-local.toml"
    click ProdCfg "https://github.com/ivoryrabbit/sql-helper/blob/master/resource/config/project-prod.toml"

    %% Styles
    classDef frontend fill:#ADD8E6,stroke:#000
    classDef backend fill:#1E90FF,stroke:#000
    classDef domain fill:#FFD700,stroke:#000
    classDef database fill:#90EE90,stroke:#000
    classDef external fill:#FFA500,stroke:#000
    classDef model fill:#FFE4E1,stroke:#000
    classDef client fill:#E6E6FA,stroke:#000
    classDef config fill:#F0E68C,stroke:#000
```
