Text-to-SQL AI

main.py — FastAPI app with CORS middleware and 3 REST endpoints:

`POST /execute-query` — Accepts natural language question, calls LLM service to generate SQL (any operation type:...

`GET /schema` — Returns live database schema via get_live_schema() (queries sqlite_master for current table list, columns, types, foreign...

`GET /health` — Health check returning API status and OPENAI_API_KEY presence (never exposes actual key)

database.py — SQLite3 operations with full SQL support:

init_database() — Creates 4 tables (customers, products, orders, order_items) with foreign key constraints, seeds 84 rows of sample data (15...

execute_sql(sql: str) — Universal SQL execution function:

SELECT: Returns rows as list of dicts with column names

INSERT/UPDATE/DELETE/REPLACE: Returns rows affected count (cursor.rowcount)

CREATE/ALTER/DROP: Returns execution status ('Table created successfully', 'Table altered successfully', 'Table dropped successfully')

PRAGMA: Returns metadata results (table_info, foreign_key_list, index_list)

EXPLAIN: Returns query plan steps

get_live_schema() — Queries sqlite_master table for current database state:

`SELECT name, sql FROM sqlite_master WHERE type='table'` gets all table definitions

For each table, runs `PRAGMA table_info(table_name)` for column metadata

Runs `PRAGMA foreign_key_list(table_name)` for relationship info

Returns JSON with tables, columns, types, foreign keys — includes tables created during session

llm.py — OpenAI GPT-4o-mini integration:

generate_sql(question: str, schema: dict) — Constructs system prompt with live database schema (all tables including newly created ones, column...

System prompt template: "You are a SQL expert. Given this database schema: [live schema JSON with all current tables], convert the following natural...

Temperature 0.3 for balanced SQL generation (deterministic for SELECT/INSERT/UPDATE, slightly creative for CREATE TABLE schema design)

Supports all SQL operation types — no restrictions on query type

models.py — Pydantic models for request/response validation:

`QueryRequest` (question: str) for POST /execute-query

`QueryResponse` (sql: str, results: List[dict] | int | str, operation_type: str, execution_time: float, error: Optional[str])

results type varies: List[dict] for SELECT, int for DML rows affected, str for DDL status

operation_type: 'SELECT' | 'INSERT' | 'UPDATE' | 'DELETE' | 'CREATE' | 'ALTER' | 'DROP' | 'REPLACE' | 'PRAGMA' | 'EXPLAIN'

`SchemaResponse` (tables: dict) for GET /schema with live table info

App.jsx — Main UI component with modern responsive design:

Input field for natural language questions with submit button

12 pre-built suggestion buttons covering all operation types:

SELECT: "Show all customers from USA", "List all products in Electronics category"

INSERT: "Add a customer named Jane from London", "Insert a product named Wireless Mouse at $25"

UPDATE: "Update all pending orders to processing", "Change price of Laptop to $1200"

DELETE: "Delete all cancelled orders", "Remove products with zero stock"

REPLACE: "Upsert a product named Webcam HD at $79.99" (INSERT or UPDATE if exists)

CREATE TABLE: "Create a notes table with id, content, created_at", "Make a tags table with id and name"

ALTER TABLE: "Add a discount_percent column to products", "Add email column to customers"

DROP TABLE: "Drop the notes table", "Delete the tags table"

PRAGMA: "Show column info for the orders table", "List foreign keys in order_items"

EXPLAIN: "Show the query plan for selecting delivered orders", "Explain how to find top customers"

Query-type badges with color coding:

Blue: SELECT (read operations)

Green: INSERT (create new records)

Yellow: UPDATE (modify existing records)

Red: DELETE (remove records)

Purple: CREATE/ALTER/DROP (schema operations)

Teal: PRAGMA/EXPLAIN (metadata and analysis)

Intelligent result display:

SELECT: Data table with columns and rows (responsive, scrollable, sortable)

INSERT/UPDATE/DELETE/REPLACE: Rows affected badge ("3 rows affected", "1 row inserted")

CREATE/ALTER/DROP: Success status message ("Table 'notes' created successfully")

PRAGMA: Metadata table (column info, foreign keys, indexes)

EXPLAIN: Query plan visualization (step-by-step execution strategy)

Generated SQL display with syntax highlighting (color-coded keywords, tables, columns)

Execution time badge (milliseconds)

Error handling with user-friendly messages

api.js — Axios wrapper for backend communication:

`executeQuery(question)` — POST /execute-query with natural language question

`getSchema()` — GET /schema for live schema info (includes newly created tables)

Base URL from environment variable (VITE_API_URL, defaults to http://localhost:8000)

App.css — Modern responsive styling with dark theme, gradient backgrounds, glassmorphism effect on cards, color-coded badges, smooth transitions,...

Key innovation — newly created tables are immediately available to LLM:

User enters: "Create a notes table with id, content, created_at"

LLM generates: `CREATE TABLE notes (id INTEGER PRIMARY KEY, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`

execute_sql() runs CREATE TABLE, updates database

Next query: "Add a note saying 'Hello World'"

get_live_schema() queries sqlite_master, finds 'notes' table in current database state

LLM receives updated schema including 'notes' table in system prompt

LLM generates: `INSERT INTO notes (content) VALUES ('Hello World')`

Works immediately without application restart

4 tables with foreign key relationships (auto-created on startup):

customers (15 rows) — id, name, email, country, created_at

products (15 rows) — id, name, category, price, stock, created_at

orders (20 rows) — id, customer_id (FK → customers), order_date, status (pending/shipped/delivered), total_amount

order_items (34 rows) — id, order_id (FK → orders), product_id (FK → products), quantity, price

Relationship flow: customers → orders → order_items ← products (many-to-many via order_items junction table)

Users can create additional tables via natural language — all immediately available to LLM.

| Type | Keywords | Example Prompt | Generated SQL | |------|----------|----------------|---------------| | SELECT | `SELECT`, `WITH` | "Show all...

`make install` — Install Python dependencies (requirements.txt) + Node dependencies (frontend/package.json)

`make dev` — Run backend (uvicorn on port 8000) + frontend (Vite on port 5173) concurrently

`make backend` — Backend only (uvicorn main:app --reload --port 8000)

`make frontend` — Frontend only (cd frontend && npm run dev)

`make stop` — Kill dev server processes (backend/frontend)

`make clean` — Remove build artifacts (__pycache__, frontend/dist/, database.db, node_modules, .ruff_cache)

Runs on every push and PR:

Security Check — Scans for `.env`, `.db`, API keys in tracked files (fails if found)

Backend Tests — Python syntax compilation, Black formatting check, mypy type checking, Bandit security scan

Frontend Tests — ESLint code quality, Vite production build verification

Integration Check — Required files present (.env.example safety check, README exists, Makefile commands valid)

Backend → Railway / Render / GCP Cloud Run:

Set environment variable `OPENAI_API_KEY` via platform dashboard

Deploy from GitHub repo (auto-deploy on push to main)

Start command: `uvicorn main:app --host 0.0.0.0 --port $PORT`

Health check endpoint: `/health` for container readiness

Frontend → Vercel / Netlify:

Set root directory to `frontend`

Set environment variable `VITE_API_URL` to backend URL

Build command: `npm run build`

Output directory: `dist`

Deploy from GitHub repo (auto-deploy on push to main)

API key server-only: `OPENAI_API_KEY` loaded from environment on backend, never sent to frontend. /health returns 'OPENAI_API_KEY: set' or 'missing'...

.env gitignored: `.gitignore` blocks `.env`, `.env.local`, `.env.*`, `*.db`, `*.sqlite` ensuring secrets and database never committed. `.env.example`...

Database files gitignored: `database.db`, `*.sqlite` blocked to prevent committing production data

No SQL injection needed — LLM generates trusted SQL, application controls generation context (schema-aware prompts), regex validation optional (can...

CORS configuration: Backend explicitly allows frontend origin (localhost:5173 in dev, Vercel URL in prod)

GPT-4o-mini pricing (~$0.15 per 1M input tokens, ~$0.60 per 1M output tokens):

Average query: 500 input tokens (schema + question) + 50 output tokens (SQL)

Cost per query: ~$0.00008 (0.008 cents)

10,000 queries: ~$0.80

100,000 queries: ~$8.00

Extremely cost-efficient for production use.