Walmart Text-to-SQL Studio

Natural-language analytics assistant for a Walmart-style retail warehouse.

The app converts business questions into SQL, executes read-only queries on SQLite, and returns SQL + tabular results in an interactive dark UI.

Author

Karan Badlani

What This Project Does

• Accepts plain-English analytics questions
• Retrieves relevant schema context with RAG (ChromaDB)
• Generates SQL using OpenAI
• Enforces execution safety (read-only guard + approval flow)
• Executes SQL and returns results to the frontend
• Logs query activity in query_log

Dataset

This project is configured for the Walmart synthetic dataset in data/raw/.

Core tables include:

• states, stores, customers, customer_addresses
• categories, subcategories, brands, products, product_listings
• offers, offer_products
• orders, order_items, payments, shipments, returns
• inventory_snapshots

Schema metadata is read from:

• data/raw/schema.sql
• data/raw/data_dictionary.csv

Architecture

• frontend/: React + Vite UI
• api/: FastAPI routes and app bootstrap
• agent/: SQL generation chain, semantic schema, retriever, index builder
• data/seed.py: Loads Walmart CSVs into SQLite from schema.sql
• model/: DB connection + query_log model

Local Setup

1. Install dependencies

python3.11 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

cd frontend
npm install
cd ..

2. Configure environment

cp .env.example .env
# set OPENAI_API_KEY in .env

Default database:

• DATABASE_URL=sqlite:///./data/walmart.db

3. Seed database

source .venv/bin/activate
python -m data.seed

4. Build schema vector index

source .venv/bin/activate
python -m agent.build_index

5. Run backend

source .venv/bin/activate
uvicorn api.main:app --reload --port 8000

6. Run frontend

cd frontend
npm run dev

Open:

• Frontend: http://127.0.0.1:5173
• API docs: http://127.0.0.1:8000/docs
• Health: http://127.0.0.1:8000/api/health

Environment Variables

• OPENAI_API_KEY (required)
• OPENAI_MODEL (default: gpt-4o)
• DATABASE_URL (default: sqlite:///./data/walmart.db)
• CHROMA_PERSIST_DIR (default: ./chroma_store)
• EMBEDDING_MODEL (default: text-embedding-3-small)
• LOG_LEVEL (default: INFO)
• ALLOWED_ORIGINS (default: * for local dev)

Safety

• Query execution allows only single-statement SELECT/WITH
• Mutating SQL is blocked unless explicitly approved via approval endpoint
• All executions are logged to query_log