How kairav7220/text-to-sql-chatbot-main works
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 Data_CSV · app.py app.py · create_db.py create_db.py · 1.py 1.py
Components 16
Presentation
User-facing app entry points and interfaces
- app.py · Main user interface with chat and evaluation tabs
app.py
LLM Orchestration
Prompting and SQL-generation flow
- 1.py · Minimal LCEL-based text-to-SQL demo
1.py - LCEL chain · Linear orchestration from context selection to parsed SQL
- Schema context · Selects relevant tables and database context for the prompt
- Prompt builder · Constructs the text-to-SQL instruction prompt
- LLM gateway · Pluggable access point for external inference providers
- SQL parser · Converts model output into executable SQL or structured answer
- Groq · External Groq-hosted Llama inference service
- Gemini · External Google Gemini inference service
Data Layer
Local SQLite dataset and source CSVs
- SQLite DB · Local database queried by generated SQL
- create_db.py · Loads CSV sources into the SQLite database
create_db.py - Data_CSV · Canonical CSV inputs for customers, products, regions, orders, and budgets
Data_CSV - data_dump · Alternate or exported dataset copies
data_dump
Evaluation
Offline scoring and benchmark workflow
- 2.py · Groq-backed chain with RAGAS evaluation
2.py - RAGAS · Scores generated answers with context and helpfulness metrics
- Eval queries · Questions and reference cases used for scoring
Connections 19
- 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