NL2SQL Clinic — AI-Powered Natural Language to SQL System

An AI-powered system that converts natural language questions into SQL queries for a clinic management database. Built with Vanna 2.0, Groq LLM, FastAPI, and SQLite.

---

Project Description

This system allows users to ask plain English questions about clinic data and get accurate SQL queries and results instantly. It uses Vanna 2.0's agent architecture with Groq's free LLM API to generate, validate, and execute SQL queries safely.

Example:

• "Which doctor has the most appointments?" → runs SQL → returns results
• "Show unpaid invoices" → runs SQL → returns results

---

Tech Stack

Component	Technology
LLM Provider	Groq (llama-3.3-70b-versatile)
NL2SQL Framework	Vanna 2.0
API Framework	FastAPI
Database	SQLite
Agent Memory	DemoAgentMemory

---

Project Structure

nl2sql-clinic/
  setup_database.py    # Creates schema + inserts dummy data
  seed_memory.py       # Seeds agent memory with 15 Q&A pairs
  vanna_setup.py       # Vanna 2.0 agent initialization
  main.py              # FastAPI application
  requirements.txt     # All dependencies
  README.md            # This file
  RESULTS.md           # Test results for 20 questions
  clinic.db            # Generated SQLite database
  .env.example         # Environment variable template
  .gitignore           # Git ignore rules

---

Database Schema

The clinic database has 5 tables:

• patients — 200 patients across 10 cities
• doctors — 15 doctors across 5 specializations
• appointments — 500+ appointments with varied statuses
• treatments — linked to completed appointments
• invoices — 300 invoices with Paid/Pending/Overdue status

---

Setup Instructions

Step 1 — Clone the repository

git clone https://github.com/Jayadev-cloud/nl2sql-clinic.git
cd nl2sql-clinic

Step 2 — Create and activate virtual environment

python -m venv venv

# Windows
venv\Scripts\activate

# Mac/Linux
source venv/bin/activate

Step 3 — Install dependencies

pip install -r requirements.txt

Step 4 — Set up environment variables

Create a .env file in the root folder:

GROQ_API_KEY=your-groq-api-key-here

Get your free Groq API key at: https://console.groq.com

Step 5 — Create the database

python setup_database.py

Expected output:

✅ Database created successfully!
Created 200 patients, 15 doctors, 850 appointments, 620 treatments, 300 invoices

---

How to Run the Memory Seeding Script

python seed_memory.py

Expected output:

Seeding agent memory with 15 Q&A pairs...
[1/15] Saved
[2/15] Saved
...
Memory seeding complete. Saved: 15/15

This pre-loads the agent with 15 known good question-SQL pairs so it performs better from the start.

---

How to Start the API Server

uvicorn main:app --port 8000

Or run everything at once:

pip install -r requirements.txt && python setup_database.py && python seed_memory.py && uvicorn main:app --port 8000

Server will start at:

http://localhost:8000

Vanna Web UI available at:

http://localhost:8000/vanna

---

API Documentation

POST /chat

Ask a natural language question about the clinic database.

Request:

{
  "question": "Which doctor has the most appointments?"
}

Response:

{
  "message": "Success",
  "sql_query": "SELECT d.name, COUNT(a.id) AS appointment_count FROM appointments a JOIN doctors d ON d.id = a.doctor_id GROUP BY d.name ORDER BY appointment_count DESC LIMIT 1",
  "columns": ["name", "appointment_count"],
  "rows": [["Dr. Arjun Sharma", 120]],
  "row_count": 1,
  "error": null
}

GET /health

Check if the API and database are running.

Request:

GET http://localhost:8000/health

Response:

{
  "status": "ok",
  "database": "connected",
  "agent_memory_items": 15
}

GET /

Root endpoint.

Response:

{
  "message": "NL2SQL API is running"
}

---

SQL Validation & Security

All AI-generated SQL is validated before execution:

• ✅ Only SELECT queries allowed
• ✅ Blocks INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE
• ✅ Blocks dangerous keywords: EXEC, xp_, sp_, GRANT, REVOKE, SHUTDOWN
• ✅ Blocks system table access: sqlite_master, sqlite_sequence

---

Architecture Overview

User Question
      │
      ▼
FastAPI /chat endpoint
      │
      ▼
Groq LLM (llama-3.3-70b)
generates SQL query
      │
      ▼
SQL Validation Layer
(blocks dangerous queries)
      │
      ▼
SQLite Database
(clinic.db)
      │
      ▼
Results returned as JSON
(columns, rows, row_count)

Components:

• Vanna 2.0 Agent — handles tool orchestration and memory
• DemoAgentMemory — stores successful Q&A pairs for future reference
• SqliteRunner — executes validated SQL on clinic.db
• VannaFastAPIServer — provides built-in web UI at /vanna
• Groq LLM — generates SQL from natural language (free tier)

---

Example Questions You Can Ask

Question	Type
How many patients do we have?	Count
Which doctor has the most appointments?	Aggregation
Show unpaid invoices	Filter
What is the total revenue?	SUM
Top 5 patients by spending	JOIN + ORDER
Show monthly appointment trends	Date grouping
What percentage of appointments are no-shows?	Percentage
Average treatment cost by specialization	AVG + GROUP BY

---

License

This project was built as part of an internship screening assignment for Cogninest AI.