# kairav7220/text-to-sql-chatbot-main architecture

> Architecture diagram of the GitHub repository kairav7220/text-to-sql-chatbot-main, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-26). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/kairav7220/text-to-sql-chatbot-main
- Repository: https://github.com/kairav7220/text-to-sql-chatbot-main

## Overview

1. This repository is a small Python text-to-SQL application centered on a local SQLite-backed dataset. The main workflow is: user asks a question in plain English, the system generates SQL through an LLM chain, executes that SQL against the database, and returns the result.

2. The user-facing entry point is `app.py`, which the README describes as a Streamlit app. It provides two main UI surfaces: a chat interface for live Q&A and an evaluation tab/dashboard for running benchmark queries and displaying quality scores.

3. The orchestration layer is LangChain LCEL. The architecture is intentionally linear: schema/context selection, prompt construction, LLM invocation, and output parsing. This layer is the core boundary between the UI and the database, translating natural language into executable SQL.

4. The LLM layer is external and pluggable. The project supports Groq-hosted Llama 3.3 70B and Google Gemini 2.0/2.5 Flash. In architecture terms, these are remote inference services that receive prompts and return generated SQL or answers; the app itself does not host the model.

5. The data layer is a local SQLite database seeded from CSV files. `create_db.py` is the ingestion/migration step that loads the source files from `Data_CSV/` into the database. That makes the project effectively a “CSV → SQLite → LLM query” pipeline rather than a live operational database app.

6. The repository’s source data is organized as a small relational dataset: customers, products, regions, sales orders, state-region mappings, and budgets. The CSVs in `Data_CSV/` appear to define the schema the SQL generator reasons over, and `data_dump/` contains additional export copies or alternate versions of similar tables.

7. There are two smaller script entry points in addition to the Streamlit app. `1.py` is described as a minimal Gemini-based LCEL chain, likely for a single-query or demo mode. `2.py` is the Groq-based chain plus RAGAS evaluation, which suggests it focuses on automated scoring rather than interactive UI.

8. Evaluation is a separate subsystem from query execution. The README shows RAGAS being used to score generated answers on metrics such as Context Precision and a rubrics-based Helpfulness score. That means the repo supports both runtime answering and offline quality assessment of the text-to-SQL behavior.

9. The main architectural boundaries are therefore: presentation (`app.py`/Streamlit), LLM orchestration (LCEL chain), data access (SQLite), data preparation (`create_db.py` + CSVs), and evaluation (RAGAS). This separation is useful for diagramming because the chat UI does not talk to the database directly; it goes through the chain.

10. Operationally, the system depends on external API keys for Groq and Google Gemini, and on local CSV/SQLite files for data. The project is designed for prototyping and benchmarking on structured data, not for production-grade security, concurrency, or multi-tenant database access.

11. The repository is intentionally lightweight: no backend server separate from Streamlit, no ORM layer, and no cloud-hosted database. The app is essentially a local-first analytics assistant that couples a small relational dataset with remote LLM reasoning and optional evaluation tooling.

Main entry points: [`Data_CSV`](https://github.com/kairav7220/text-to-sql-chatbot-main/tree/main/Data_CSV) (Data_CSV), [`app.py`](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/app.py) (app.py), [`create_db.py`](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/create_db.py) (create_db.py), [`1.py`](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/1.py) (1.py)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_presentation["Presentation"]
  node_app_py["app.py<br/>Streamlit app<br/>[app.py]"]
end

subgraph group_orchestration["LLM Orchestration"]
  node_script_1["1.py<br/>Gemini demo chain<br/>[1.py]"]
  node_chain_core{{"LCEL chain<br/>SQL pipeline"}}
  node_schema_ctx["Schema context<br/>context selection"]
  node_prompt_builder["Prompt builder<br/>prompt assembly"]
  node_llm_gateway(("LLM gateway<br/>remote model access"))
  node_output_parser["SQL parser<br/>response parsing"]
  node_groq_api(("Groq<br/>hosted LLM"))
  node_gemini_api(("Gemini<br/>hosted LLM"))
end

subgraph group_data["Data Layer"]
  node_db_runtime[("SQLite DB<br/>local database")]
  node_db_builder["create_db.py<br/>ingestion script<br/>[create_db.py]"]
  node_csv_sources["Data_CSV<br/>source tables"]
  node_dump_archive["data_dump<br/>export copies"]
end

subgraph group_evaluation["Evaluation"]
  node_script_2["2.py<br/>Groq eval chain<br/>[2.py]"]
  node_ragas_eval{{"RAGAS<br/>evaluation framework"}}
  node_benchmark_data["Eval queries<br/>benchmark set"]
end

node_app_py -->|"user query"| node_chain_core
node_app_py -->|"run evals"| node_ragas_eval
node_script_1 -->|"demo flow"| node_chain_core
node_script_2 -->|"score answers"| node_ragas_eval
node_chain_core -->|"select context"| node_schema_ctx
node_schema_ctx -->|"context bundle"| node_prompt_builder
node_prompt_builder -->|"prompt"| node_llm_gateway
node_llm_gateway -->|"provider"| node_groq_api
node_llm_gateway -->|"provider"| node_gemini_api
node_groq_api -->|"generated SQL"| node_output_parser
node_gemini_api -->|"generated SQL"| node_output_parser
node_output_parser -->|"execute query"| node_db_runtime
node_db_builder -->|"load data"| node_csv_sources
node_db_builder -->|"build db"| node_db_runtime
node_csv_sources -->|"seed schema"| node_db_runtime
node_dump_archive -.->|"reference data"| node_csv_sources
node_chain_core -->|"context from data"| node_db_runtime
node_ragas_eval -->|"evaluate on"| node_benchmark_data
node_ragas_eval -.->|"inspect outputs"| node_chain_core

click node_app_py "https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/app.py"
click node_script_1 "https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/1.py"
click node_script_2 "https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/2.py"
click node_db_builder "https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/create_db.py"
click node_csv_sources "https://github.com/kairav7220/text-to-sql-chatbot-main/tree/main/Data_CSV"
click node_dump_archive "https://github.com/kairav7220/text-to-sql-chatbot-main/tree/main/data_dump"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_app_py toneBlue
class node_script_1,node_chain_core,node_schema_ctx,node_prompt_builder,node_llm_gateway,node_output_parser,node_groq_api,node_gemini_api toneAmber
class node_db_runtime,node_db_builder,node_csv_sources,node_dump_archive toneMint
class node_script_2,node_ragas_eval,node_benchmark_data toneRose
```

## Components

### Presentation

User-facing app entry points and interfaces

- [app.py](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/app.py): Streamlit app · Main user interface with chat and evaluation tabs · `app.py`

### LLM Orchestration

Prompting and SQL-generation flow

- [1.py](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/1.py): Gemini demo chain · Minimal LCEL-based text-to-SQL demo · `1.py`
- **LCEL chain**: SQL pipeline · Linear orchestration from context selection to parsed SQL
- **Schema context**: context selection · Selects relevant tables and database context for the prompt
- **Prompt builder**: prompt assembly · Constructs the text-to-SQL instruction prompt
- **LLM gateway**: remote model access · Pluggable access point for external inference providers
- **SQL parser**: response parsing · Converts model output into executable SQL or structured answer
- **Groq**: hosted LLM · External Groq-hosted Llama inference service
- **Gemini**: hosted LLM · External Google Gemini inference service

### Data Layer

Local SQLite dataset and source CSVs

- **SQLite DB**: local database · Local database queried by generated SQL
- [create_db.py](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/create_db.py): ingestion script · Loads CSV sources into the SQLite database · `create_db.py`
- [Data_CSV](https://github.com/kairav7220/text-to-sql-chatbot-main/tree/main/Data_CSV): source tables · Canonical CSV inputs for customers, products, regions, orders, and budgets · `Data_CSV`
- [data_dump](https://github.com/kairav7220/text-to-sql-chatbot-main/tree/main/data_dump): export copies · Alternate or exported dataset copies · `data_dump`

### Evaluation

Offline scoring and benchmark workflow

- [2.py](https://github.com/kairav7220/text-to-sql-chatbot-main/blob/main/2.py): Groq eval chain · Groq-backed chain with RAGAS evaluation · `2.py`
- **RAGAS**: evaluation framework · Scores generated answers with context and helpfulness metrics
- **Eval queries**: benchmark set · Questions and reference cases used for scoring

## Connections

- app.py → LCEL chain: user query · Chat interface sends questions into the SQL generation flow
- app.py → RAGAS: run evals · Evaluation tab triggers offline scoring
- 1.py → LCEL chain: demo flow · Minimal Gemini script exercises the same orchestration pattern
- 2.py → RAGAS: score answers · Groq script combines generation with RAGAS assessment
- LCEL chain → Schema context: select context · Finds relevant schema and table context
- Schema context → Prompt builder: context bundle · Feeds selected schema into prompt assembly
- Prompt builder → LLM gateway: prompt · Sends assembled instruction to the model backend
- LLM gateway → Groq: provider · Can route to Groq-hosted models
- LLM gateway → Gemini: provider · Can route to Gemini-hosted models
- Groq → SQL parser: generated SQL · Model returns candidate SQL or response text
- Gemini → SQL parser: generated SQL · Model returns candidate SQL or response text
- SQL parser → SQLite DB: execute query · Parsed SQL is run against the local database
- create_db.py → Data_CSV: load data · Ingestion step reads the structured CSV inputs
- create_db.py → SQLite DB: build db · Creates or refreshes the SQLite database
- Data_CSV → SQLite DB: seed schema · Source tables define the relational dataset used by the app
- data_dump → Data_CSV: reference data · Archive provides alternate copies of the same dataset family
- LCEL chain → SQLite DB: context from data · Uses database schema and results to inform generation
- RAGAS → Eval queries: evaluate on · Runs benchmark questions through scoring metrics
- RAGAS → LCEL chain: inspect outputs · Evaluation reuses generated answers from the SQL pipeline