# saiganesh4995/llm_sql_chatbot architecture

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

- Interactive diagram: https://gitdiagram.com/saiganesh4995/llm_sql_chatbot
- Repository: https://github.com/saiganesh4995/llm_sql_chatbot

## Overview

##### Project type and purpose
- This is a single‑page, full‑stack‑style data application built with Streamlit.
- Purpose: Let end users ask natural‑language questions in a browser, convert them to SQL via Groq LLM, run queries on a SQL Server database, and display both raw data and human‑friendly answers.

##### File structure
- All files live at the root level—no separate frontend/backend folders—but functionally you have:
- A Streamlit “frontend” (chatbot.py)
- Data‑processing logic in the same script (pandas, pypyodbc)
- External API integration (Groq LLM)
- SQL helper artifacts (SQLQuery1.sql, SQLQuery2.sql, SQL_Chatbot.ipynb)
- requirements.txt and .gitignore signal standard Python project conventions.

##### Key insights from the README
- Tech stack: Python 3.9+, Streamlit, pandas, pypyodbc, Groq LLM API
- Database schema: Four tables in SQL Server (`product_info`, `order_status`, `support_contacts`, `faq`)
- Workflow: User input → metadata extraction → prompt to Groq → execute SQL → fetch DataFrame → prompt to Groq for human answer → display results

##### Main components
a. User Interface
- Streamlit app (chatbot.py) in the user’s browser/host machine
b. LLM API
- Groq LLM service for:
- Generating SQL from natural language
- Generating human‑readable answers from query results
c. Database
- SQL Server instance with the four tables
d. Data Processing Layer
- Python modules: pypyodbc for ODBC connectivity, pandas for DataFrame handling
e. Retry & Error Handling
- Built‑in retry mechanism in chatbot.py

##### Relationships and interactions
- User → Streamlit UI: HTTP/WebSocket
- Streamlit UI → Groq API: REST calls carrying prompts
- Streamlit UI → SQL Server: ODBC calls to execute generated SQL
- SQL Server → Streamlit UI: Returns tabular data
- Streamlit UI → Groq API: Sends retrieved data context for answer generation
- Groq API → Streamlit UI → Browser: Final human‑readable answer plus raw data display

##### Architectural patterns & principles
- Layered architecture:
- Presentation (Streamlit)
- Application logic (prompt building, retry)
- Data access (ODBC)
- External services (LLM, Database)
- Single‑process, event‑driven flow (Streamlit reruns script on each user interaction)
- Clear separation of concerns: prompt generation, query execution, answer rendering

##### Technologies & frameworks
- Python 3.9+
- Streamlit for UI
- Groq LLM API (LLaMA‑3.1‑8b‑instant)
- pypyodbc (ODBC Driver 17) + pandas
- SQL Server `business_data` database

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% End User
    User(("End User")):::user

    %% Presentation Layer
    subgraph "Presentation Layer"
        Streamlit["Streamlit UI<br/>(chatbot.py)"]:::presentation
    end

    %% Application Layer
    subgraph "Application Layer"
        DataProc["Data Processing & DB Access<br/>(pandas, pypyodbc)"]:::processing
    end

    %% External Services
    subgraph "External Services"
        SQLGen["Groq LLM API<br/>(SQL Generation)"]:::api
        Summarizer["Groq LLM API<br/>(Summarize Results)"]:::api
    end

    %% Data Layer
    subgraph "Data Layer"
        SQLDB["SQL Server (business_data)<br/>Tables: product_info, order_status,<br/>support_contacts, faq"]:::db
    end

    %% Project Artifacts
    subgraph "Project Artifacts"
        SQLQuery1["SQLQuery1.sql"]:::artifact
        SQLQuery2["SQLQuery2.sql"]:::artifact
        Notebook["SQL_Chatbot.ipynb"]:::artifact
        Requirements["requirements.txt"]:::artifact
        README["README.md"]:::artifact
    end

    %% Connections
    User -->|"NL Query Input"| Streamlit
    Streamlit -->|"Generate SQL\n(JSON prompt)"| SQLGen
    SQLGen -->|"SQL text\n(JSON response)"| Streamlit
    Streamlit -->|"Execute SQL via ODBC\n(SQL text)"| SQLDB
    SQLDB -->|"DataFrame\n(tabular data)"| Streamlit
    Streamlit -->|"Summarize Results\n(DataFrame context)"| Summarizer
    Summarizer -->|"Human-readable Answer\n(JSON)"| Streamlit
    Streamlit -->|"Display Answer + Raw Data"| User

    %% Retry Loop Annotation
    SQLGen -. "retry on failure" .-> SQLGen

    %% Click Events
    click Streamlit "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/chatbot.py"
    click DataProc "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/chatbot.py"
    click SQLGen "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/chatbot.py"
    click Summarizer "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/chatbot.py"
    click SQLQuery1 "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/SQLQuery1.sql"
    click SQLQuery2 "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/SQLQuery2.sql"
    click Notebook "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/SQL_Chatbot.ipynb"
    click Requirements "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/requirements.txt"
    click README "https://github.com/saiganesh4995/llm_sql_chatbot/blob/main/README.md"

    %% Styles
    classDef presentation fill:#ADD8E6,stroke:#333,stroke-width:1px;
    classDef processing fill:#ADD8E6,stroke:#333,stroke-width:1px;
    classDef api fill:#D8BFD8,stroke:#333,stroke-width:1px;
    classDef db fill:#C3E2A3,stroke:#333,stroke-width:1px;
    classDef user fill:#FFFACD,stroke:#333,stroke-width:1px;
    classDef artifact fill:#F2F2F2,stroke:#333,stroke-width:1px;
```
