pravinbaste009/NL2SQL_Assistant

NL2SQL POC


Live demo: https://huggingface.co/spaces/pravinbaste009/NL2SQL_Assistant

Ask questions about a company database in plain English. The system retrieves the relevant table schemas from a local vector store, uses an LLM (Groq) to write a SQL query, runs it against a local SQLite database, and turns the results back into a natural-language answer.

Architecture

flowchart LR
    Q[User question] --> R[Retriever - ChromaDB]
    R --> G[SQL Generator - Groq]
    G --> X[SQL Executor - SQLite]
    X --> A[Answer Generator - Groq]
    A --> O[Natural-language answer]

• —Database: SQLite (data/company.db) with 4 related tables: departments, employees (HR), projects, finance (50+ rows each).
• —Schema doc: schema/schema.md.
• —Embeddings: local sentence-transformers (all-MiniLM-L6-v2) stored in ChromaDB (chroma_db/).
• —LLM: Groq (llama-3.3-70b-versatile by default).
• —UI: Gradio chat app (app.py).

Setup (Windows / PowerShell)

python -m venv .venv
.venv\Scripts\Activate.ps1
pip install -r requirements.txt

# Add your Groq key
Copy-Item .env.example .env
# then edit .env and set GROQ_API_KEY

Build the data, docs, and embeddings (run once)

python src\db_setup.py        # create + seed the SQLite database
python src\schema_docs.py     # write schema/schema.md
python src\embed_schema.py    # embed table schemas into ChromaDB

Run the app

python app.py

Query logging

Every question is logged by src/query_logger.py:

• —Console: a single concise line per query (Q: ... | rows=N | SQL: ...).
• —File: a structured JSON-lines record appended to logs/query_log.jsonl with timestamp, question, sql, row_count, answer, error, and duration_ms.

Evaluate against the golden query set

python eval\evaluate.py

The golden set (eval/golden_queries.json) contains, for each case: the user question, the expected SQL, and the expected natural-language answer. The evaluator reports SQL validity, execution accuracy (generated vs. expected result rows), and answer similarity.

Deployment & CI/CD

The app is deployed on Hugging Face Spaces and redeploys automatically on every push to main via GitHub Actions (.github/workflows/deploy-hf.yml):

1. 1.validate job: rebuilds the database and runs eval/validate_golden.py, which checks that every golden query's expected SQL executes successfully (no API key needed). Deployment is gated on this passing.
2. 2.deploy job: force-pushes the repo to the Hugging Face Space, which then rebuilds and runs app.py (the DB and embeddings self-build on first start).

Required GitHub configuration (Settings -> Secrets and variables -> Actions):

• —Secret HF_TOKEN: a Hugging Face write token.
• —Variables HF_USERNAME and HF_SPACE: the Space owner and name.

The GROQ_API_KEY is set as a secret on the Hugging Face Space itself (not in GitHub), so it is never committed.

Project structure

NL2SQL_POC/
  data/company.db            # SQLite database
  chroma_db/                 # persisted vector store
  schema/schema.md           # human-readable schema
  src/
    config.py                # paths, model names, env loading
    schema_definitions.py    # single source of truth for the schema
    db_setup.py              # create + seed tables
    schema_docs.py           # generate schema.md
    embed_schema.py          # embed schemas into ChromaDB
    retriever.py             # semantic schema retrieval
    sql_generator.py         # Groq: question + context -> SQL
    sql_executor.py          # run SELECT-only SQL on SQLite
    answer_generator.py      # Groq: rows -> natural-language answer
    query_logger.py          # logs each query + SQL + answer to logs/query_log.jsonl
    pipeline.py              # orchestrates the full flow
  logs/query_log.jsonl       # per-query log (question, SQL, answer, timing)
  eval/
    golden_queries.json      # golden set
    evaluate.py              # evaluation script
  app.py                     # Gradio chat UI
  requirements.txt
  .env.example