Build a SQL Analyst Agent That Answers Questions Over Postgres with Groq

What We're Building

We're shipping a natural language SQL analyst agent. You ask a question in plain English—"Which customers spent more than $500 last month?"—and the agent converts it to SQL, fires it against a live Postgres database, and returns a human-readable explanation of the results.

Core feature list:

• Accepts free-text questions about your database
• Generates safe, read-only SQL using Groq's Llama-3 70B (free tier)
• Executes queries against Supabase Postgres (free tier)
• Returns results as formatted tables plus a plain-English summary
• Runs entirely on free infrastructure with no credit card required to start

This pattern is the foundation for internal analytics bots, customer-facing data copilots, and demo scaffolding—exactly the kind of thing an FDE ships in week one of a customer engagement. If you're ramping up on the FDE toolchain, this stack mirrors what we cover in The Tools an FDE Ships With: Data Wrangling, Integrations, and Demo Scaffolding.

Architecture Overview

The flow is dead simple: user question in, prompt assembly with full schema context, SQL generation via Groq's absurdly fast inference, a safety gate that rejects anything that isn't a SELECT, execution against Supabase, and a second LLM call to explain what the numbers actually mean. No vector databases, no embedding pipelines, no RAG overhead—just prompt engineering and a tight execution loop.

Prerequisites

Everything here runs on free tiers. No credit card hard stops.

You'll also need pip install streamlit groq supabase python-dotenv.

Step 1: Set Up the Postgres Database

Head to supabase.com, create a free project, and note your database credentials (host, port, database name, password). We'll stuff these in a .env file.

For a real demo, create a sample sales table. Run this in the Supabase SQL editor:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    product TEXT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (customer_name, product, amount, sale_date) VALUES
('Acme Corp', 'Widget A', 1200.00, '2025-02-10'),
('Globex', 'Widget B', 850.50, '2025-02-12'),
('Acme Corp', 'Widget C', 430.00, '2025-03-01'),
('Initech', 'Widget A', 2100.00, '2025-03-05'),
('Globex', 'Widget B', 675.25, '2025-03-10');

This gives the agent something real to chew on. The schema—table names, column names, data types—becomes the critical context we inject into every prompt.

Step 2: Configure the Groq LLM Client

Groq's API is OpenAI-compatible, so the client pattern is familiar. Create a groq_client.py:

import os
from groq import Groq
from dotenv import load_dotenv

load_dotenv()

client = Groq(api_key=os.getenv("GROQ_API_KEY"))

def generate_sql(question: str, schema: str) -> str:
    """Generate a read-only SQL query from a natural language question."""
    system_prompt = (
        "You are a precise SQL generator. Given a PostgreSQL schema and a user question, "
        "produce a single valid SELECT query. Do NOT generate INSERT, UPDATE, DELETE, DROP, "
        "or any DDL statements. Return ONLY the SQL, no markdown fences, no commentary. "
        "If the question cannot be answered with a SELECT, respond with: UNANSWERABLE"
    )
    
    response = client.chat.completions.create(
        model="llama-3.3-70b-versatile",
        messages=[
            {"role": "system", "content": system_prompt},
            {"role": "user", "content": f"Schema:\n{schema}\n\nQuestion: {question}"}
        ],
        temperature=0.0,
        max_tokens=500
    )
    
    return response.choices[0].message.content.strip()

Temperature 0.0 is intentional—we want deterministic SQL, not creative writing. The schema string should include table names, columns with types, and a few sample rows so the model understands the data shape. We'll build that schema string next.

Step 3: Build the SQL Generation and Execution Engine

The core engine does three things: introspects the database for schema context, calls Groq for SQL generation, and safely executes the query. Create engine.py:

import os
from supabase import create_client
from groq_client import generate_sql
from dotenv import load_dotenv

load_dotenv()

supabase = create_client(
    os.getenv("SUPABASE_URL"),
    os.getenv("SUPABASE_SERVICE_KEY")  # service_role key for schema introspection
)

def get_schema() -> str:
    """Introspect the public schema and return a compact representation."""
    # Query information_schema for tables and columns
    result = supabase.rpc(
        "get_schema_info",  # custom function, see below
        {}
    ).execute()
    
    if not result.data:
        # Fallback: hard-code schema for known tables
        return """
        Table: sales
        Columns: id (SERIAL), customer_name (TEXT), product (TEXT), amount (DECIMAL), sale_date (DATE)
        Sample rows:
        (1, 'Acme Corp', 'Widget A', 1200.00, '2025-02-10')
        (2, 'Globex', 'Widget B', 850.50, '2025-02-12')
        """
    
    return result.data

def execute_query(sql: str) -> list:
    """Execute a read-only SQL query via Supabase REST."""
    # Safety check: only SELECT statements
    cleaned = sql.strip().upper()
    if not cleaned.startswith("SELECT"):
        raise ValueError(f"Query rejected: only SELECT statements allowed. Got: {sql[:50]}")
    
    # Use Supabase's SQL execution (requires service_role key)
    result = supabase.rpc("execute_sql", {"query": sql}).execute()
    return result.data if result.data else []

You'll need two Postgres functions in Supabase. Run these in the SQL editor:

-- Function to introspect schema
CREATE OR REPLACE FUNCTION get_schema_info()
RETURNS TEXT AS $$
DECLARE
    schema_text TEXT := '';
    table_record RECORD;
    column_record RECORD;
    sample_record RECORD;
BEGIN
    FOR table_record IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
    LOOP
        schema_text := schema_text || 'Table: ' || table_record.table_name || E'\nColumns: ';
        
        FOR column_record IN
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = table_record.table_name
            ORDER BY ordinal_position
        LOOP
            schema_text := schema_text || column_record.column_name || ' (' || column_record.data_type || '), ';
        END LOOP;
        
        schema_text := schema_text || E'\nSample rows:\n';
        
        -- Grab up to 3 sample rows
        FOR sample_record IN
            EXECUTE format('SELECT * FROM %I LIMIT 3', table_record.table_name)
        LOOP
            schema_text := schema_text || sample_record::TEXT || E'\n';
        END LOOP;
        
        schema_text := schema_text || E'\n';
    END LOOP;
    
    RETURN schema_text;
END;
$$ LANGUAGE plpgsql;

-- Function to safely execute SQL (service_role only)
CREATE OR REPLACE FUNCTION execute_sql(query TEXT)
RETURNS SETOF JSON AS $$
BEGIN
    RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

The schema introspection function is the magic that makes this agent generalize across databases. It dynamically pulls table structures and sample rows, so the LLM always has current context. No manual schema maintenance.

Step 4: Build the Streamlit Interface

Create app.py. This is the user-facing layer—question input, result display, and the explanation call.

import streamlit as st
from engine import get_schema, execute_query
from groq_client import generate_sql
import pandas as pd

st.set_page_config(page_title="SQL Analyst Agent", layout="wide")
st.title("📊 SQL Analyst Agent")
st.caption("Ask questions about your database in plain English. Powered by Groq + Supabase.")

# Initialize session state
if "history" not in st.session_state:
    st.session_state.history = []

# Question input
question = st.text_input(
    "What would you like to know?",
    placeholder="e.g., Which customer spent the most in March 2025?"
)

if st.button("Ask") and question:
    with st.spinner("Generating SQL..."):
        schema = get_schema()
        sql = generate_sql(question, schema)
    
    if sql == "UNANSWERABLE":
        st.warning("I couldn't generate a safe query for that question. Try rephrasing.")
    else:
        st.code(sql, language="sql")
        
        with st.spinner("Running query..."):
            try:
                rows = execute_query(sql)
            except ValueError as e:
                st.error(str(e))
                rows = []
        
        if rows:
            df = pd.DataFrame(rows)
            st.dataframe(df, use_container_width=True)
            
            # Generate explanation
            with st.spinner("Explaining results..."):
                explanation_prompt = (
                    f"The user asked: '{question}'\n"
                    f"The SQL executed was: {sql}\n"
                    f"The results are: {rows[:20]}"  # Truncate for token limits
                    f"\n\nSummarize these results in 2-3 plain-English sentences. Be concise."
                )
                explanation = generate_sql(explanation_prompt, schema="")  # Reuse client, schema not needed
                st.success(explanation)
            
            # Store in history
            st.session_state.history.append({
                "question": question,
                "sql": sql,
                "rows": rows,
                "explanation": explanation
            })
        else:
            st.info("Query returned no results.")

# Show history
if st.session_state.history:
    with st.expander("Query History", expanded=False):
        for i, entry in enumerate(reversed(st.session_state.history)):
            st.markdown(f"**Q{i+1}:** {entry['question']}")
            st.code(entry["sql"], language="sql")
            st.caption(entry["explanation"])
            st.divider()

Notice we're reusing generate_sql for the explanation step. It's a chat completion function—the system prompt doesn't matter when we're just asking for a summary. In production you'd split this into a dedicated explanation function, but for a free-tier build, reuse keeps the codebase tight.

Running the Agent

Your .env file should look like this:

GROQ_API_KEY=gsk_your_key_here
SUPABASE_URL=https://yourproject.supabase.co
SUPABASE_SERVICE_KEY=eyJhbGciOi...  # service_role key, not anon key

Launch locally:

streamlit run app.py

The app opens at http://localhost:8501. Type a question and watch it generate SQL, execute, and explain.

For deployment, push to GitHub and connect the repo to Streamlit Community Cloud. Add your .env variables in the Streamlit secrets manager (Settings → Secrets). The free tier handles moderate traffic fine—just stay under Groq's rate limits.

Extensions to Level Up the Agent

This is a solid v0. Here's how to make it production-grade:

Add query caching. Wrap execute_query with an in-memory cache (or Redis free tier on Upstash). If the same SQL is generated twice, return cached results instantly and skip the Groq call.

Implement a feedback loop. Add thumbs-up/thumbs-down buttons on each result. Store rejected queries with the correct SQL in a Supabase table. Fine-tune nothing—just inject the last N corrections into the system prompt as few-shot examples. This is the same pattern we use in customer-facing agents, covered in Writing Customer-Facing Technical Docs That Actually Get Read by Non-Engineers.

Multi-turn conversations. Track the last question and result in session state. If the next question contains pronouns ("What about Globex?"), include the prior context in the prompt so the LLM can resolve references.

Query cost estimation. Before execution, run EXPLAIN on the generated SQL and surface the estimated cost. Warn the user if a full table scan is coming.

Slack/Teams integration. Wrap the engine in a simple FastAPI endpoint and connect it to a Slack slash command. Same architecture, different UI surface.

Common Pitfalls and How to Avoid Them

The LLM generates non-SELECT queries. Even with a strict system prompt, Llama-3 occasionally outputs UPDATE or DELETE. The safety check in execute_query catches this, but you should also add a regex filter in generate_sql that rejects anything not starting with SELECT or WITH.

Schema drift. If you add columns or tables, the introspection function picks them up automatically. But if you rename a column, the LLM might still generate SQL with the old name if it's been cached in the prompt context. Restart the app to clear any in-memory schema cache.

Groq rate limiting. The free tier is generous but not infinite. If you hit 429 errors, implement exponential backoff with a 1-second initial delay. Better yet, cache LLM responses keyed on (question, schema_hash).

Large result sets. Streaming 10,000 rows to Streamlit will freeze the UI. Cap results at 100 rows in execute_query with a LIMIT clause injection. The explanation step already truncates to 20 rows, but the dataframe display needs the same guard.

Service role key exposure. The SUPABASE_SERVICE_KEY bypasses Row Level Security. Never expose it client-side. In Streamlit, it stays in secrets. If you build an API wrapper, the service key lives server-side only. This is the kind of security hygiene that separates production FDE work from demo-ware—covered in depth in Your Open-Source Model Could Have a Hidden Time-Release Backdoor: How to Audit.

FAQ

Why Groq instead of OpenAI?
Groq's inference speed is ridiculous—Llama-3 70B responses in under a second. For an interactive SQL agent, latency matters. The free tier is also more generous than OpenAI's trial credits for sustained use.

Can I use this with a different database?
Yes. Swap the Supabase client for psycopg2 or any DB-API connector. The schema introspection functions will need equivalent queries against information_schema, which is standard SQL.

What if the LLM generates invalid SQL?
Wrap execute_query in a try/except for psycopg2.Error (or Supabase's error type). Catch syntax errors and feed them back to the LLM in a second attempt: "The previous query failed with error X. Fix it." This self-correction loop works surprisingly well with Llama-3.

Is this pattern safe for production?
With the SELECT-only gate and service role isolation, yes—for internal tools. For customer-facing deployments, add Row Level Security in Supabase so users can only query rows they own, and move the execution to a read-replica to avoid impacting production performance.

How do I handle complex joins?
The schema introspection already captures foreign key relationships if you define them. Add a WHERE clause to the information_schema query for table_constraints and include FK info in the schema string. The LLM will naturally generate joins when it sees the relationships.