README.md

---
title: Querymind Api
emoji: 📊
colorFrom: blue
colorTo: pink
sdk: docker
pinned: false
---

# Natural Language Data Analyst

## Overview
A powerful full-stack analytical web application that enables users to query structured databases using plain, conversational English. The system automatically converts natural language questions into highly optimized, dialect-specific SQL, executes it against an embedded SQLite database, renders beautiful responsive charts (pure SVG without third-party chart libraries), and synthesizes qualitative business insights from the results using generative AI.

## Features
- 🗣️ **Natural language to SQL conversion**: Translates conversational questions into accurate SQLite SELECT queries.
- 📊 **Auto-generated charts**: Renders pure-SVG Bar, Line, and Pie charts dynamically based on query results column metadata (completely zero-dependency).
- 💡 **AI-generated insights**: Summarizes query execution results into concise business insights in real time.
- 📁 **Upload your own CSV datasets**: Seamlessly upload custom CSV files which are automatically formatted, sanitized, and loaded into individual SQLite database files.
- 🗃️ **Built-in sample datasets**: Pre-loaded with Sales, Employees, and E-commerce sample data to start querying immediately.
- 🔄 **Auto-retry on SQL errors**: Automatically catches execution syntax/type failures and prompts the LLM to fix the query (up to 3 retries).
- 📥 **Export results as CSV**: Downloads query data instantly as CSV files named based on the source question.
- 💡 **Query Suggestions**: Generates and caches 6 interesting question suggestions using AI when a dataset is opened.
- 🕒 **Recent Queries Log**: Retains the last 20 queries run during a session for rapid re-execution.
- ⌨️ **Keyboard Shortcuts**: Focuses input with `Ctrl+K`, submits with `Ctrl+Enter`, and pulls the last query using the `Arrow Up` key when empty.

## Tech Stack

| Technology | Purpose |
|---|---|
| **Python 3.10+ / FastAPI** | Backend web server and REST API routing |
| **Groq (Llama 3.1 8B)** | Advanced SQL query generation, self-correction, suggestions, and text insights |
| **SQLite + Pandas** | SQL database storage, schema analysis, and query execution |
| **React + Vite** | High-performance, reactive single-page frontend application |
| **Pure CSS / HTML5** | Modern, premium glassmorphism dark-mode styling and UI aesthetics |
| **Pure SVG** | High-fidelity interactive charts and visual graphs (zero-dependency) |

---

## Project Structure

```text
nl-data-analyst/
├── backend/
│   ├── main.py                 # FastAPI routing, CORS middleware, and cache setup
│   ├── config.py               # Global server constants, LLM model selection, and API keys
│   ├── schemas.py              # Pydantic models validating REST request and response contracts
│   ├── requirements.txt        # Python dependency manifest (FastAPI, Groq, Pandas)
│   └── services/               # Core business logic handlers
│       ├── csv_service.py      # CSV loading, parsing, and SQLite DB generation
│       ├── sql_service.py      # SQLite execution, query sanitization, and safety audits
│       ├── llm_service.py      # SQL generation, self-repair pipelines, and suggestions
│       └── insight_service.py  # Text synthesis translating tabular data to business insights
├── databases/                  # Storage directory containing generated SQLite .db files
├── sample_data/                # Built-in sample CSV datasets (Sales, Employees, E-commerce)
├── frontend/
│   ├── src/
│   │   ├── components/         # Reusable React UI component layers
│   │   │   ├── Sidebar.jsx     # Side menu listing datasets, upload buttons, and recent logs
│   │   │   ├── UploadView.jsx  # Drag-and-drop CSV upload landing zone
│   │   │   ├── DataPreview.jsx # Overview statistics, column schema, and data grid preview
│   │   │   ├── QueryView.jsx   # Context bars, query log panels, and suggestions decks
│   │   │   ├── QueryInput.jsx  # Query textarea form with key shortcut binds
│   │   │   ├── QueryResult.jsx # Collapsible SQL, data table, and chart selector display
│   │   │   ├── ChartView.jsx   # SVG chart routing, scaling, grid plotting, and tooltips
│   │   │   ├── DataTable.jsx   # Interactive data matrix grid
│   │   │   └── LoadingResult.jsx # Dynamic shimmers skeleton loading placeholders
│   │   ├── api.js              # JavaScript fetch integration layer mapping REST endpoints
│   │   ├── App.jsx             # Top-level state coordinator, stats bar, and toast managers
│   │   ├── index.css           # Global custom CSS styles, animations, and responsive queries
│   │   └── main.jsx            # React client DOM mounter
│   ├── package.json            # Node.js dev server and build tool dependencies
│   ├── vite.config.js          # Vite custom proxy and build settings
│   └── index.html              # Main HTML skeleton file
├── .env                        # Environment credentials (API keys, ports)
└── README.md                   # Project documentation manual
```

---

## Getting Started

### Prerequisites
- **Python 3.10+**
- **Node.js 18+**
- **Groq API Key** (obtain free from [console.groq.com](https://console.groq.com))

### Installation & Running

#### 1. Setup the Backend
Navigate to the root directory and create a virtual environment:
```bash
# Create python virtual environment
python -m venv .venv

# Activate the virtual environment
# On Windows (Command Prompt/PowerShell):
.venv\Scripts\activate
# On macOS/Linux:
source .venv/bin/activate

# Install backend dependencies
pip install -r backend/requirements.txt
```

Create a `.env` configuration file in the project root (using `.env.template` as a model) and fill in your Groq API Key:
```env
GROQ_API_KEY=gsk_your_actual_groq_api_key_here
PORT=8000
```

Start the backend FastAPI server:
```bash
# Run the backend using Uvicorn
python -m uvicorn backend.main:app --host 127.0.0.1 --port 8000 --reload
```
The API documentation will be available at `http://127.0.0.1:8000/docs`.

#### 2. Setup the Frontend
Open a new terminal session, navigate to the `frontend/` directory, install packages, and start the development server:
```bash
cd frontend

# Install client packages
npm install

# Start Vite dev environment
npm run dev -- --port 5173 --host 127.0.0.1
```
The web application is now active at `http://127.0.0.1:5173/`.

---

## How It Works

1. **Dataset Selection & Conversion**: The user selects a preloaded dataset or drops in a custom CSV file. The backend parses it using Pandas and maps datatypes to create an optimized SQLite database file inside the `databases/` folder.
2. **Dynamic Question Suggestions**: When a dataset is loaded, its schema is analysed and passed to the Llama model on Groq to compile 6 business-relevant query suggestions. Suggestions are cached in-memory.
3. **Natural Language Translation**: The user submits a conversational question. The LLM translates the query based on the table schema and SQL formatting system prompts.
4. **Execution & Self-Correction**: The SQLite query is verified against destructive SQL keywords (only SELECTs allowed) and executed. If SQLite throws a syntax error, the LLM-service receives the failing SQL and stacktrace to attempt self-correction.
5. **Visualization Layout**: The frontend parses the output matrix. If column headers contain labels + numbers, it charts the data:
   - **Pie Chart**: Used for $\le 6$ distinct labels (great for composition summaries).
   - **Bar Chart**: Generates dynamic scale sizes and plots vertical bars with custom hover tooltips.
   - **Line Chart**: Draws line series, highlights points, and adds linear gradients under the curves.
6. **Insight Synthesis**: Groq translates the final matrix rows and original question to output a concise 2-3 sentence business summary.

---

## Future Improvements
- **PostgreSQL / MySQL Connectors**: Run natural language analytics on enterprise production databases.
- **Multi-Table JOIN Support**: Incorporate relational schema graphs to query complex multi-table relationships.
- **Dashboard View**: Pin multiple query results cards and auto-refresh reports.
- **Interactive Visualizations**: Modify chart styles, axes, and colors using conversational commands.
- **PDF Exporter**: Export the full history log along with SVG charts and text insights into a PDF report document.

## License
MIT