Shop Analytics — a text-to-SQL agent

English · Español

Ask a database a question in everyday language, and get a real answer back: a sentence, a chart, and the SQL query it used to get there.

What it does

You type "which product categories sell the most?" — no SQL, no dropdowns, no dashboard to configure. Behind the scenes an AI model writes the query, runs it against a PostgreSQL database of around 5,000 online store orders, and writes the answer back in plain words.

• It answers, instead of handing you a table. You get a sentence, not 500 rows to read yourself.
• It shows its work. The query it wrote is always there, one click away, so you can check it rather than take its word for it.
• It fixes its own mistakes. If the query fails, it reads the database's error message and tries again — up to three times.
• It draws the right chart for the shape of the answer: a trend over time becomes a line, a comparison between categories becomes bars.
• It answers in your language. Ask in Spanish and it replies in Spanish.

Some questions it can answer

There is no fixed list of questions — these are only examples. Anything the data can answer, you can ask, in your own words.

Which 5 product categories generated the most revenue?

How many orders were placed each month in 2025?

What is the average delivery time by country?

Which sellers have the best average review rating?

Compare revenue and number of orders per country

The database behind it

Eight tables from an online store: who bought, what they bought, what they paid, and what they thought of it afterwards. This is also, almost word for word, the description the model receives — the arrows are what let it get from a review back to the product and the customer behind it without being told how.

erDiagram
    categories ||--o{ products    : "groups"
    sellers    ||--o{ products    : "sells"
    customers  ||--o{ orders      : "places"
    orders     ||--o{ order_items : "contains"
    products   ||--o{ order_items : "appears in"
    orders     ||--o{ payments    : "is paid by"
    orders     ||--o{ reviews     : "is reviewed in"
    products   ||--o{ reviews     : "is rated in"
    customers  ||--o{ reviews     : "writes"

    categories {
        int  category_id PK
        text category_name UK
        text department
    }
    sellers {
        int     seller_id PK
        text    seller_name UK
        text    city
        text    state
        text    country
        date    joined_date
        numeric rating "1.0 to 5.0"
    }
    customers {
        int  customer_id PK
        text first_name
        text last_name
        text email UK
        text phone
        text city
        text state
        text country
        text postal_code
        date birth_date
        date signup_date
        text loyalty_tier "bronze, silver or gold"
    }
    products {
        int     product_id PK
        text    product_name
        int     category_id FK
        int     seller_id FK
        numeric price "what it costs today"
        numeric cost
        int     stock_quantity
        numeric weight_kg
        bool    is_active
        date    listed_date
    }
    orders {
        int       order_id PK
        int       customer_id FK
        timestamp order_date
        text      status "pending, paid, shipped, delivered, cancelled, returned"
        numeric   shipping_cost
        text      shipping_city
        text      shipping_state
        text      shipping_country
        timestamp delivered_date "empty until it arrives"
    }
    order_items {
        int     order_item_id PK
        int     order_id FK
        int     product_id FK
        int     quantity
        numeric unit_price "what was paid back then"
        numeric discount
    }
    payments {
        int       payment_id PK
        int       order_id FK
        text      payment_method "credit_card, debit_card, paypal, bank_transfer, gift_card"
        numeric   amount
        int       installments
        timestamp payment_date
    }
    reviews {
        int       review_id PK
        int       order_id FK
        int       product_id FK
        int       customer_id FK
        int       rating "1 to 5"
        text      title
        text      comment
        timestamp review_date
    }

Loading

There is no order total stored anywhere, and that is deliberate: revenue has to be worked out from the items, which is exactly the kind of question a dashboard cannot answer for you. In all: 13 categories, 40 sellers, 600 products, 2,000 customers, 5,258 orders, 10,381 items, 5,791 payments and 2,577 reviews.

How it works

flowchart LR
    Q([Your question]) --> W[Write the SQL]
    W --> R[Run it]
    R -->|it failed| W
    R -->|it worked| A[Write the answer]
    A --> O([Answer + chart])

Loading

1. Your question goes to the model along with a description of the database — its tables, and how they relate to each other.
2. The model replies with a SQL query.
3. Before running anything, the query is checked: it has to be a read.
4. If the database rejects it, the error message goes back to the model, and it writes a corrected query. That loop is the whole point — it is what makes this an agent rather than a one-shot translation.
5. Once the query works, a second, cheaper request turns the rows into a sentence, and a chart is drawn from the result.

The page shows each of those steps as it happens, retries included, instead of hiding everything behind a loading spinner.

Design decisions

The interesting part of this project is not that a model can write SQL. It is everything around it.

The model can never write to the database

It writes queries that really run, so the obvious question is what stops it from deleting something. Three things, and none of them depends on the others:

1. The query is inspected before it runs. Anything that is not a plain read is rejected — including the sneaky cases, like a deletion hidden inside a query that otherwise looks like a read.
2. The connection itself is read-only. Even if something got past the first check, PostgreSQL would refuse it.
3. The database user only has permission to read. No write access at all.

Asking the model nicely not to delete anything is not one of the layers. An instruction in a prompt is a request, not a control.

The database was built by hand, not downloaded

This started with a public e-commerce dataset, loaded from CSV files. Loading it that way produced flat tables with no relationships defined between them — and that turned out to matter a lot, because the description the model receives is generated from the database structure itself.

So the tables are now written by hand, with their relationships and their rules spelled out. The model can see that an order belongs to a customer, and that a review score only goes from 1 to 5, without anyone having to explain it. The data that fills them is generated by the project, and it is deliberately realistic: payments add up to the order total, only delivered orders have reviews, and November and December are the busiest months.

The chart is not chosen by the AI

It is decided from the answer's own shape: dates on one side means a trend, so a line; names and numbers means a comparison, so bars; a single number means no chart at all. It costs nothing, it is instant, and it always does the same thing for the same kind of answer — none of which is true of asking a model.

Two requests, not one

Writing the query and writing the answer are separate steps. The second one never sees the database structure, only the result, which makes it much cheaper and keeps it from second-guessing a query that already worked.

Built with

Python · PostgreSQL · LangChain and LangGraph · Google Gemini · FastAPI · matplotlib. The web page is plain HTML, CSS and JavaScript in a single file, with no framework and no build step.

Running it locally

Requires Python 3.14 and PostgreSQL.

python -m venv venv
venv\Scripts\activate          # Linux/macOS: source venv/bin/activate
pip install -r requirements.txt

Copy .env.example to .env and fill in three values: a database connection, an admin connection used only to create the database, and a Google AI Studio API key. .env.example explains each one, and includes the commands to create the read-only database user.

python create_database.py      # builds the database and fills it with data
python api.py                  # http://127.0.0.1:8000

It runs on your own machine only, on purpose — it is a demonstration project, and the API key lives in a local file.

There is also python main.py, which does the same thing in the terminal.

Tests

pip install -r requirements-dev.txt
pytest

61 tests, about a second, and they run automatically on every push. They cover the parts that can be checked with certainty — the read-only rules, the chart choice, and the retry logic — and need neither a database nor an API key to run.

Where things are

File	What it is
agent.py	The agent: the steps, the retry loop, the progress updates.
db.py	The database connection and the read-only rules.
chain.py	The instructions given to the model, and a simpler version of the agent kept for comparison.
answer.py	Turns the result into a sentence.
visualize.py	Chooses the chart and draws it.
api.py	The web server.
static/index.html	The page.
create_database.py · synthetic_data.py	The database structure and the data that fills it.