speakql

AI-Powered SQL Optimization & Agentic Text-to-SQL
Analyzes real PostgreSQL query plans, detects bottlenecks, recommends indexes, rewrites SQL, and predicts performance impact.

Quick Start • Key Features • The Secret Sauce • Architecture • Monitoring

---

💡 Why speakql?

Traditional Text-to-SQL solutions fail on enterprise schemas because they lack structural awareness and performance intuition. speakql is an agentic SQL engine designed to bridge the gap between natural language intent and production-grade, optimized SQL.

It doesn't just generate queries; it understands them by analyzing real PostgreSQL execution plans, identifying anti-patterns, and suggesting structural improvements.

🌟 The "Winning Pitch"

"speakql analyzes real PostgreSQL query plans, detects bottlenecks, recommends indexes, rewrites SQL, and predicts performance impact before deployment."

• Agentic SQL Generation: Handles 5+ table JOINs automatically using Graph theory.
• Deep Query Analysis: Built-in SQL Optimizer with Index Advisor and Query Rewriter.
• Self-Healing Loop: Automatically fixes failed queries by analyzing DB errors.
• Production Ready: Includes row-caps, timeouts, and read-only enforcement.

---

📸 See It In Action

Optimize SQL — Bottleneck Analysis & Risk-Aware Index Recommendations

Paste any SQL query → speakql parses the real PostgreSQL execution plan, detects bottlenecks, and recommends indexes with production-safety flags.

Agentic Text-to-SQL with Graph-Based Join Discovery

Natural-language queries are answered using vector retrieval over schema + graph pathfinding for multi-table joins. The agent streams progress as it plans, generates, and validates.

---

✨ Key Features

📊 Performance & Optimization

• Index Advisor: Suggests missing indexes based on the generated query plan.
• Risk-aware safety signals: each index recommendation flags estimated_storage_impact (small/medium/large), may_slow_writes, and a safe_for_production boolean — so DBAs can review before applying.
• SQL Smell Detector: Identifies anti-patterns like SELECT *, missing JOIN conditions, or non-SARGable predicates.
• Query Rewriter: Optimizes SQL for performance using sqlglot.
• EXPLAIN Analysis: Parses PostgreSQL execution plans to identify bottlenecks.

🤖 Agentic SQL Generation

• Self-Correction Loop: Automatically retries failed SQL (up to 3x) by feeding database errors back into the LLM.
• Multi-LLM Support: Native support for Groq (Llama 3), Google Gemini and OpenAI.
• Streaming SSE: Real-time updates as the agent plans, bridges, generates, and validates.

🕸️ Schema Intelligence

• Graph-Based Pathfinding: Uses networkx to discover optimal JOIN paths between disconnected tables.
• Hybrid Context: Combines Qdrant vector search (semantic) with Graph traversal (structural).
• Automated Inspection: One-click schema indexing into Qdrant using FastEmbed.

📈 UI & Visualization

• Auto-Charts: Automatically suggests and renders Bar, Line, or Pie charts using Recharts.
• Schema Explorer: Visualize your database structure and table relationships.
• Progress Timeline: Track every step of the agent's thought process.

---

🛠 The Secret Sauce

The Agentic Loop

Unlike static one-shot generators, speakql uses a sophisticated feedback loop:

1. Plan: LLM identifies user intent and high-level required tables.
2. Bridge: The Graph Engine finds the shortest path between tables that have no direct FK relationship.
3. Generate: LLM produces SQL with injected "Join Hints" from the graph.
4. Validate: System checks SQL syntax and schema validity using sqlglot.
5. Execute: Runs against the live DB. If it fails, the error is sent back to Step 3.

Graph-Based JOIN Discovery

When RAG retrieves payment and category, they have no direct FK. The Graph Engine finds the path: payment → rental → inventory → film → film_category → category

This path is injected into the prompt, ensuring the LLM never hallucinates a relationship or misses a bridge table.

---

🏗 Architecture

graph TB
    subgraph Browser["Client Side (React 19)"]
        UI["Dashboard UI"]
        SSE["SSE Stream Handler"]
        Charts["Recharts Viz"]
    end

    subgraph Backend["FastAPI Service"]
        Agent["Agentic Controller"]
        Graph["NetworkX Graph Engine"]
        Optimizer["SQL Optimizer (Index/Smells)"]
        RAG["Qdrant + FastEmbed"]
        OptAPI["POST /optimize"]
        Explain["EXPLAIN Parser"]
        IdxAdv["Index Advisor"]
        SmellD["Smell Detector"]
        Rewriter["LLM Rewriter"]
    end

    subgraph Data["Storage Layer"]
        TargetDB[("User Database (PostgreSQL)")]
        VectorDB[("Qdrant Store")]
        Cache[("Redis History & Cache")]
    end

    UI -->|"NL Question"| Agent
    UI -->|"Slow SQL"| OptAPI
    Agent --> RAG
    Agent --> Graph
    Agent --> Optimizer
    Agent --> TargetDB
    Agent --> Cache
    Agent -->|"Step Events"| SSE
    OptAPI --> Explain
    OptAPI --> IdxAdv
    OptAPI --> SmellD
    OptAPI --> Rewriter
    Explain --> TargetDB
    Rewriter --> UI
    SSE --> UI
    TargetDB --> UI

Loading

---

🚀 Quick Start

speakql runs against cloud-managed Postgres (Neon), Redis (Upstash), Qdrant Cloud, and Groq. You only install Python, Node, and uv locally.

Prerequisites

• Python 3.11+
• Node.js 20+
• uv — Python package manager (install)
• Postgres CLI tools (psql, pg_restore) — needed once to load the sample database into your cloud Postgres. Available via Postgres.app or brew install libpq on macOS.

1. Clone & install

git clone https://github.com/yourusername/speakql.git
cd speakql
make install

2. Provision free cloud services

• Postgres: sign up at https://neon.tech → create a project → grab the connection string
• Redis: sign up at https://upstash.com → create a Redis database → grab the rediss:// URL
• Qdrant: sign up at https://cloud.qdrant.io → create a cluster → grab URL + API key
• LLM: sign up at https://console.groq.com → grab an API key

3. Configure

cp .env.example .env
# Edit .env with the four sets of credentials from step 2

4. Load the dvdrental sample data into Neon

The dvdrental.tar file is in the repo root. Use your Neon connection string (psql format, with sslmode=require):

pg_restore --no-owner --no-privileges \
  -d "postgresql://USER:PASSWORD@HOST.neon.tech/neondb?sslmode=require" \
  ./dvdrental.tar

You'll see harmless warnings about public schema exists and extension plpgsql — ignore them, the data loads.

5. Start the app

make backend     # terminal 1 — FastAPI on :8000
make frontend    # terminal 2 — Vite dev server on :5173

6. Index the schema (required once per environment)

Open http://localhost:5173, click the settings/connect icon, and trigger the schema indexing flow. Or run:

curl -X POST http://localhost:8000/schema/index/stream \
  -H 'Content-Type: application/json' \
  -d '{}' --no-buffer

Wait for the stream to complete (~10 seconds). Then restart the backend so it picks up the indexed-schema state.

Note: Use the /schema/index/stream (streaming) endpoint here, not the plain /schema/index one. The non-streaming endpoint indexes into Qdrant but does not activate the live connection state (app.state.active_db_url); the streaming variant does. This is a known limitation tracked in the roadmap below.

7. Use it

• Optimize SQL tab: paste a slow query, get bottlenecks + index recommendations + a rewrite
• Ask Data tab: ask natural-language questions, get SQL + results + auto-charts

---

Alternative: local Postgres + Redis

If you prefer not to use cloud services, install PostgreSQL 14+ and Redis locally (e.g. brew install postgresql@16 redis), update .env to point at localhost:

DB_URL=postgresql+asyncpg://USER@localhost:5432/dvdrental
REDIS_URL=redis://localhost:6379/0
QDRANT_URL=http://localhost:6333
QDRANT_API_KEY=

Then run make demo to load dvdrental into your local Postgres and index the schema (requires the backend to be running first in a separate terminal with make backend).

---

⚙️ Environment Variables

Variable	Description
DB_URL	Async database connection URL (postgresql+asyncpg, etc.)
LLM_PROVIDER	Active provider: openai, groq, or gemini
GROQ_API_KEY	Groq API key
GROQ_MODEL	Groq model identifier (default: llama-3.3-70b-versatile)
GEMINI_API_KEY	Google Gemini API key
GEMINI_MODEL	Gemini model identifier (default: gemini-2.0-flash)
OPENAI_API_KEY	OpenAI API key
OPENAI_MODEL	OpenAI model identifier (default: gpt-4o-mini)
REDIS_URL	Redis connection URL for caching and session state
QDRANT_URL	Qdrant vector store base URL
QDRANT_COLLECTION	Qdrant collection name for schema embeddings
QDRANT_API_KEY	Qdrant API key (required for Qdrant Cloud)
VIRTUAL_KEYS_PATH	Path to virtual_keys.json for non-FK table relationships
HUB_DEGREE_THRESHOLD	Nodes with degree above this are excluded from path traversal
GRAPH_CUTOFF	Maximum path length for bridge table discovery
GRAPH_HOPS	Hop radius for single-table neighbor expansion
GRAPH_MAX_PATHS	Circuit-breaker limit on paths evaluated between any table pair
RAG_SCORE_THRESHOLD	Minimum cosine-similarity score for a table to be included in RAG results
QUERY_CACHE_TTL	Query result cache TTL in seconds (0 = disabled)

See .env.example for the full list with defaults.

---

📊 Monitoring

speakql exposes real-time metrics for Prometheus:

• Endpoint: GET /metrics
• Tracked Metrics:
  • speakql_queries_total: Success/Failure counts.
  • speakql_query_duration_seconds: E2E latency.
  • speakql_sql_attempts_total: Number of retries per query.
  • speakql_bridge_tables_discovered_total: Impact of the Graph Engine.
  • speakql_cache_hits_total: Redis performance.

Use make metrics to see a snapshot of current stats.

---

🛠 Tech Stack

Backend

• Framework: FastAPI
• ORM: SQLAlchemy (Async)
• Graph: NetworkX
• Vector DB: Qdrant
• Embeddings: FastEmbed
• SQL Parsing: sqlglot
• LLM Clients: groq, google-genai, openai

Frontend

• Framework: React 19
• Build Tool: Vite
• Styling: Tailwind CSS 4
• Charts: Recharts
• Icons: Lucide React

---

🗺 Known Limitations & Roadmap

We ship honest limits, not handwaving:

• Schema-indexing activation: The non-streaming POST /schema/index endpoint indexes the schema into Qdrant but does not activate app.state.active_db_url. The streaming variant /schema/index/stream does. This will be unified in a follow-up.
• Schema retrieval tuning: get_where_chain_tables uses fixed 3-hop expansion from all seed tables, which over-includes lookup tables on small schemas. A schema-size-aware expander would reduce LLM input tokens by an additional 30–50% on dvdrental-sized databases.
• Index advisor coverage: Recommendations cover WHERE predicates, ORDER BY, and GROUP BY columns. Foreign-key join columns are not currently surfaced separately because most production schemas already index them; explicit detection is planned.
• LLM observability: Per-request token counting and cost tracking are planned (llm_tokens_total, llm_request_duration_seconds). Provider responses already include the data; the metric pipeline doesn't yet consume it.
• Stage-level latency: speakql_query_duration_seconds covers end-to-end pipeline wall time. Per-stage breakdown (retrieval / graph / LLM / SQL execution) is planned.

---

🧪 Testing & Quality

# Run backend tests
make test

# Run linting (Ruff)
make lint

# Clean build artifacts
make clean

---

📄 License

Distributed under the MIT License. See LICENSE for more information.

Built with ❤️ by the speakql team.