muddasser/Retail_stores_Queues

Text-to-SQL Trace RAG

A retrieval-augmented Text-to-SQL prototype for retail BI questions. Instead of asking an LLM to generate SQL cold, it first retrieves verified solution traces from similar past questions (via FAISS semantic search) and uses them to ground a structured trace, which is then translated into DuckDB SQL and executed.

Built as a single-file FastAPI app (app.py) serving both the JSON API and a plain HTML/JS frontend — no Gradio, no build step, no frontend framework.

Status: prototype. There is no SQL execution guardrail (any statement the model returns is run directly against DuckDB), no persistence (the trace corpus and vector index live in memory and reset on restart), and no auth. Do not point this at anything you care about losing.

Pipeline

Question → Retrieve similar verified traces (FAISS, cosine ≥ 0.55)
         → Generate structured trace (Groq / llama-3.3-70b-versatile)
         → Generate SQL from the trace
         → Execute against an in-memory DuckDB "sales" table

Retrieved traces below the similarity threshold are dropped rather than injected into the prompt — a weak match is treated as noise, not evidence, so the model falls back to zero-shot generation instead of being anchored to an unrelated example.

Running locally with Docker

docker build -t text2sql-trace-rag .
docker run -p 7860:7860 -e GROQ_API_KEY=your_key_here text2sql-trace-rag

Then open http://localhost:7860.

Running locally without Docker

pip install -r requirements.txt
export GROQ_API_KEY=your_key_here
python app.py

Configuration

Deploying to Hugging Face Spaces

1. 1.Create a new Space, SDK = Docker.
2. 2.Push Dockerfile, requirements.txt, app.py, and this README.md (the YAML block above is what Spaces reads to configure the build — don't remove it).
3. 3.Add GROQ_API_KEY under Settings → Repository secrets. Never commit it to the repo or bake it into the Dockerfile.
4. 4.Push. Spaces builds the image and routes traffic to port 7860 as declared in app_port above.

Using the app

• —Ask Question tab — type a retail BI question, generates and runs SQL against the sample sales table.
• —Add Verified Trace tab — after manually checking a trace + SQL pair is correct, add it to the corpus so future similar questions retrieve it. This rebuilds the FAISS index in memory; it is not saved to disk, so it will not survive a container restart.

API

Interactive API docs are auto-generated by FastAPI at /docs.

Known limitations (read before extending)

• —No validation that generated SQL is read-only — treat this as an internal/dev tool, not something to expose publicly as-is.
• —Trace corpus and FAISS index are process-global and in-memory only; single-worker only (uvicorn default). Running with multiple workers will give each worker its own copy of the trace corpus and index, so added traces won't be visible across workers.
• —No check that generated SQL actually reflects the structured trace it was conditioned on.
• —Sample sales data is hardcoded for demo purposes.
• —groq is pinned to 1.6.0 specifically because groq<1.x breaks against httpx>=0.28 (Client.__init__() got an unexpected keyword argument 'proxies' — the old SDK's internal HTTP client wrapper still passes an argument httpx removed). If you ever bump groq down for any reason, re-check this.