🛒 ShopMind AI — E-Commerce Operations Agent

Ask your e-commerce database a question in plain English. Get a real, data-backed answer.

ShopMind AI is a conversational analytics agent for an e-commerce business. Instead of writing SQL or building dashboards, you type a question like "What are the top 5 best-selling products?" or "How many users signed up last month?" — the agent figures out the right query, runs it safely against the real database, and answers in plain business language.

Why this project exists

Most small e-commerce teams have the data (orders, products, users, reviews, events) but not the SQL skills to dig into it on demand. This project is an exploration of agentic tool-use with the Model Context Protocol (MCP): rather than letting an LLM touch the database directly, the LLM only ever sees a single, narrow, read-only tool — it never gets raw database credentials or write access. That separation is the actual point of the project, not just the chat UI on top of it.

Related MCP server: ECommerce MCP Server

How it works

┌─────────────┐      question       ┌──────────────────┐
│  Streamlit  │ ──────────────────▶ │   Gemini model    │
│   Chat UI   │                     │ (gemini-3.6-flash)│
│  (app.py)   │ ◀────────────────── │                    │
└─────────────┘   final answer      └─────────┬──────────┘
                                                │ decides it needs data,
                                                │ requests a tool call
                                                ▼
                                     ┌──────────────────────┐
                                     │   MCP Server          │
                                     │  (mcp_server.py)      │
                                     │  tool: execute_       │
                                     │  readonly_sql(query)  │
                                     └──────────┬─────────────┘
                                                │ validated SELECT only
                                                ▼
                                     ┌──────────────────────┐
                                     │   MySQL database       │
                                     │   (database.py)        │
                                     │   users, products,     │
                                     │   orders, order_items, │
                                     │   reviews, events       │
                                     └──────────────────────┘

1. You type a question into the Streamlit chat box.

2. app.py sends the question to Gemini, along with the database schema and a set of ground rules (only SELECT, only the given tables, never invent data, exclude cancelled/returned orders from revenue, etc.).

3. Gemini decides it needs data and calls the one tool it's been given: execute_readonly_sql, generating the SQL itself.

4. That tool call goes over MCP (not a direct function call) to a separate MCP server process (mcp_server.py), which is the only thing with a database connection.

5. The MCP server validates the query (must start with SELECT, only one statement allowed, no destructive keywords), runs it, and returns the rows as JSON.

6. That result is fed back to Gemini, which turns raw rows into a plain-English, business-friendly answer.

7. The agent can loop this up to 5 times if a query needs correcting — e.g. if the first attempt references a column that doesn't exist.

The AI never sees your database password and can never run INSERT/UPDATE/DELETE/DROP — it only ever gets to ask the MCP server to run one SELECT at a time, and the server enforces that.

Tech stack

Features

• 💬 Natural-language querying — no SQL knowledge needed to explore the data.

• 🔒 Read-only by design — the MCP tool rejects anything that isn't a single SELECT statement.

• 🔁 Self-correcting agent loop — if a generated query is wrong, the agent sees the error and tries again (up to 5 rounds).

• 🧠 Schema-aware prompting — the model is given the exact table/column names up front, so it doesn't guess.

• 📊 Business-sensible answers — rules baked into the prompt handle real analytics nuance, like excluding cancelled/returned orders from revenue figures.

• 🧩 Decoupled architecture — the LLM, the tool server, and the database are three separate processes talking over well-defined protocols, not one tangled script.

Project structure

MCP/
├── app.py              # Streamlit chat app — the main entry point
├── mcp_server.py        # MCP server exposing the execute_readonly_sql tool
├── mcp_client.py         # CLI version of the full agent loop (for debugging without the UI)
├── agent.py              # Minimal CLI version — no MCP, calls the DB function directly
├── database.py            # MySQL connection handling
├── test_database.py        # Sanity check: run a query directly against the DB
├── test_mcp.py               # Sanity check: spin up the MCP server and call the tool
├── requirements.txt            # Python dependencies
├── .env                          # API key + database credentials (not committed in real projects)
└── data/
    ├── schema.sql                # Table definitions matching the CSVs below
    ├── users.csv, products.csv, orders.csv,
    │   order_items.csv, reviews.csv, events.csv   # Source data
    └── MCP.ipynb                                   # Exploratory notebook used while building this

Database schema

order_status values: processing, completed, cancelled, returned, shipped.

Setup

1. Install dependencies

pip install -r requirements.txt

2. Set up MySQL

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mcp CHARACTER SET utf8mb4;"
mysql -u root -p mcp < data/schema.sql

Then load the CSVs (adjust paths to absolute; enable local_infile if disabled):

SET GLOBAL local_infile=1;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/users.csv'
  INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/products.csv'
  INTO TABLE products FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/orders.csv'
  INTO TABLE orders FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS
  (order_id, user_id, @order_date, order_status, total_amount)
  SET order_date = STR_TO_DATE(@order_date, '%Y-%m-%dT%H:%i:%s.%f');

LOAD DATA LOCAL INFILE '/absolute/path/to/data/order_items.csv'
  INTO TABLE order_items FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS;

LOAD DATA LOCAL INFILE '/absolute/path/to/data/reviews.csv'
  INTO TABLE reviews FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS
  (review_id, order_id, product_id, user_id, rating, review_text, @review_date)
  SET review_date = STR_TO_DATE(@review_date, '%Y-%m-%dT%H:%i:%s.%f');

LOAD DATA LOCAL INFILE '/absolute/path/to/data/events.csv'
  INTO TABLE events FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 ROWS
  (event_id, user_id, product_id, event_type, @event_timestamp)
  SET event_timestamp = STR_TO_DATE(@event_timestamp, '%Y-%m-%dT%H:%i:%s.%f');

3. Configure environment variables

Create/edit .env:

GEMINI_API_KEY=your_key_here

DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=your_mysql_password
DB_NAME=mcp

4. Sanity-check the pieces

python test_database.py   # confirms the DB connection + a real query work
python test_mcp.py        # confirms the MCP server starts and the tool responds

5. Run the app

streamlit run app.py

Open the local URL Streamlit prints, and start asking questions.

Example questions to try

• "What are the top 5 best-selling products?"

• "How many orders were cancelled last quarter?"

• "Which city has the most signed-up users?"

• "What's the average rating for the Electronics category?"

• "What's our total revenue excluding cancelled and returned orders?"

Design decisions & guardrails

• Least privilege: the database user the app connects as should only have SELECT on the mcp schema — never grant it write access.

• Defense in depth on SQL: the tool itself rejects anything that isn't a single SELECT statement, independent of whatever the model was told to do in the prompt — a compromised or confused model still can't do damage.

• Schema-first prompting: the model is always given the real column names, so answers are grounded instead of guessed.

• Graceful degradation: if the model's answer doesn't land in the expected place, or a query fails, the agent retries or returns a clear message — never a silent blank response.

Known limitations

• There's no dedicated "profit" column in the data (no cost-of-goods figures), so profit-style questions are answered using revenue instead, with that substitution stated explicitly in the answer.

• The agent caps itself at 5 tool-call rounds per question to avoid runaway loops on an unanswerable question.

• This is a single-user local tool, not a multi-tenant production service — there's no authentication layer on the Streamlit app itself.

Possible next steps

• Add a chat history so follow-up questions ("...and for last month specifically?") retain context.

• Cache repeated queries to cut down on Gemini calls.

• Add a lightweight cost/COGS table so real profit questions can be answered.

• Deploy the MCP server as a long-lived process instead of spawning a new one per question.