README.md

---
title: GenAI Text2SQL Analytics Assistant
emoji: 🤖
colorFrom: blue
colorTo: indigo
sdk: streamlit
sdk_version: "1.45.1"
app_file: app.py
pinned: false
license: mit
---


# 🤖 GenAI Text2SQL Analytics Assistant

An advanced Generative AI-powered analytics assistant that converts natural language questions into executable SQL queries and retrieves insights from a live relational database.

Built using LangChain, Groq LLMs, SQLAlchemy, SQLite, and Streamlit.

---

# 🚀 Features

- Natural Language to SQL Conversion
- Live Database Query Execution
- AI-Powered Business Analytics
- SQL Query Transparency
- Interactive Analytics Dashboard
- CSV Export Functionality
- Automatic SQL Cleaning & Validation
- Multi-table SQL Reasoning
- Conversational Analytics Experience
- Real-time Data Insights

---

# 🧠 How It Works

The application does not train a custom AI model.

Instead, it uses a Large Language Model (LLM) to:
1. Understand the user's business question
2. Generate optimized SQL queries dynamically
3. Execute queries on a live SQLite database
4. Return analytics results and business insights

---

# 🏗️ Architecture

```text

User Query

    ↓

LLM (Groq + LangChain)

    ↓

SQL Query Generation

    ↓

SQLite Database Execution

    ↓

Analytics Results

    ↓

AI Business Summary + Visualization

```

---

# 🛠️ Tech Stack

| Category | Technology |
|---|---|
| LLM Provider | Groq |
| Framework | LangChain |
| Database | SQLite |
| ORM | SQLAlchemy |
| Frontend | Streamlit |
| Data Generation | Faker |
| Data Processing | Pandas |
| Language | Python |

---

# 📂 Project Structure

```bash

TEXT2SQL-GENAI/

│

├── agents/

│   ├── sql_agent.py

│   └── langchain_sql_agent.py

│

├── database/

│   ├── create_db.py

│   ├── seed_data.py

│   └── ecommerce.db

│

├── prompts/

│   └── sql_prompt.py

│

├── utils/

│   ├── sql_utils.py

│   └── query_engine.py

│

├── app.py

├── requirements.txt

├── .env

└── README.md

```

---

# 🗄️ Database Schema

The project uses a relational e-commerce database containing:

- Customers
- Products
- Employees
- Orders
- Order Items

The database was populated with synthetic business data using Faker.

---

# 📊 Example Questions

```text

Top 5 products by revenue

```

```text

Which employee handled the most orders?

```

```text

Average order value by city

```

```text

Show revenue by product category

```

```text

Top customers by spending

```

---

# 🔥 Advanced Features Implemented

## ✅ Schema-Aware Prompting
Injected database schema directly into prompts to improve SQL accuracy.

## ✅ SQL Cleaning
Automatically removes markdown formatting and cleans generated SQL.

## ✅ Error Handling & Retry Logic
Detects invalid SQL queries and retries with correction prompts.

## ✅ LangChain SQL Agent
Implemented autonomous SQL reasoning using LangChain SQL Agent.

## ✅ AI Business Summaries
Generates human-readable business insights from query results.

## ✅ Interactive Analytics Dashboard
Built using Streamlit with:
- Data tables
- SQL visibility
- CSV export
- Charts and visualizations

---

# 📈 Sample Analytics Capabilities

- Revenue Analysis
- Customer Segmentation
- Product Performance
- Sales Trends
- Employee Performance Metrics
- Business Intelligence Reporting

---

# ⚙️ Installation

## 1️⃣ Clone Repository

```bash

git clone <your_repo_url>

cd TEXT2SQL-GENAI

```

---

## 2️⃣ Create Virtual Environment

### Windows

```bash

python -m venv venv

venv\Scripts\activate

```

---

## 3️⃣ Install Dependencies

```bash

pip install -r requirements.txt

```

---

## 4️⃣ Configure Environment Variables

Create `.env`

```env

GROQ_API_KEY=your_api_key_here

```

---

# ▶️ Running The Project

## Step 1 — Create Database

```bash

python database/create_db.py

```

---

## Step 2 — Seed Database

```bash

python database/seed_data.py

```

---

## Step 3 — Run Streamlit App

```bash

streamlit run app.py

```

---

# 🌟 Key Learning Outcomes

- Generative AI Application Development
- Prompt Engineering
- LangChain Agent Workflows
- SQL Query Generation
- Database Integration with LLMs
- Conversational Analytics Systems
- Streamlit Dashboard Development
- AI-powered Business Intelligence

---

# 🎯 Future Improvements

- PostgreSQL Support
- Authentication System
- Query History
- Role-Based Access Control
- Real-time Streaming Responses
- Data Upload & Auto Analysis
- Multi-turn Conversational Memory
- Advanced Chart Visualizations

---

# 📌 Important Note

This project does not fine-tune or train an LLM.

Instead, it demonstrates:
- LLM orchestration
- schema-aware prompting
- live SQL generation
- autonomous database querying
- AI analytics workflows

which closely reflects real-world enterprise GenAI systems.

---

# 👨‍💻 Author

Mohd Faizanullah