AI-Powered SQL Business Intelligence Assistant

The core problem with business intelligence tools has always been the same: the people who need the answers can't write SQL, and the people who can write SQL are busy with other things. This project is my attempt to close that gap — a Streamlit app where you type a question in plain English, Gemini AI generates the SQL, the app runs it against a real database, and you get back a table, a chart, and a plain-English business insight. No SQL knowledge required.

🔗 Live Streamlit App: bit.ly/4cUtPwB
📂 GitHub Repository: bit.ly/42df5sT

The Dataset

The app runs against the Superstore Sales dataset — 9,994 orders from 2020–2023 across 16 columns including order details, shipping mode, customer segment, region, product category, sales, quantity, discount, and profit. It's the standard BI benchmark dataset for a reason: it's rich enough to support complex queries but clean enough that data quality doesn't obscure the analytical patterns.

Rather than connecting to a cloud database (which would require credentials and network setup for every user), the dataset is loaded into a local SQLite database at startup. SQLite's full SQL support — including window functions, CTEs, and aggregations — makes it perfectly adequate for this use case, and the app starts in under two seconds on any machine.

The Architecture

The flow for every query is five steps:

User types a question in plain English — e.g. "Which region has the highest profit margin?"
The question + database schema are sent to Gemini 2.5 Flash as a structured prompt
Gemini returns valid SQL which the app extracts and sanitizes
The SQL runs against SQLite — results returned as a Pandas DataFrame
The DataFrame + original question go back to Gemini for a plain-English business insight

The two-call pattern — one for SQL generation, one for insight generation — is deliberate. Combining them into a single prompt degrades both outputs. Separating them lets each call be optimized independently.

The Prompt Engineering

Getting Gemini to reliably generate correct, safe SQL requires careful prompt construction. The system prompt includes the full schema with column descriptions, explicit instructions to use only SELECT statements, examples of complex queries (CTEs, window functions), and a formatting directive to return only the SQL with no explanation.

def build_sql_prompt(user_question: str, schema: str) -> str:
    return f"""You are an expert SQL analyst working with a SQLite database.

Database Schema:
{schema}

Rules:
- Generate ONLY a SELECT statement. No INSERT, UPDATE, DELETE, DROP.
- Use SQLite-compatible syntax only.
- Return ONLY the SQL query, no explanation, no markdown fences.
- Use appropriate aggregations, JOINs, and window functions as needed.
- Column names are case-sensitive — use them exactly as shown in the schema.

Question: {user_question}

SQL Query:"""

The schema string is generated dynamically from the live database using PRAGMA table_info(), which means the prompt stays accurate even if the underlying table changes — an important robustness property for any app someone else might deploy with their own database.

SQL Safety

Even with the prompt instructing SELECT-only, LLMs can occasionally produce unexpected output. The app adds a hard validation layer on top of the prompt-level guardrail:

import re
import sqlite3

FORBIDDEN = ['insert', 'update', 'delete', 'drop', 'alter', 'create', 'truncate']

def validate_and_run(sql: str, conn: sqlite3.Connection) -> pd.DataFrame:
    sql_lower = sql.lower().strip()

    # Must start with SELECT
    if not sql_lower.startswith('select'):
        raise ValueError("Only SELECT queries are permitted.")

    # No data-modification keywords anywhere in the query
    for keyword in FORBIDDEN:
        if re.search(rf'\b{keyword}\b', sql_lower):
            raise ValueError(f"Forbidden keyword detected: {keyword}")

    return pd.read_sql_query(sql, conn)

Two layers — prompt-level and code-level — gives much higher confidence than either alone. The prompt prevents most bad outputs. The code-level check catches anything that slips through.

The Four App Pages

The app is structured across four Streamlit pages. Home shows key metrics and a quick query launcher. AI Query Assistant is the main NL-to-SQL interface. Sales Dashboard has pre-built interactive charts with region and category filters. SQL Explorer lets users write and run their own SQL directly. There's also an AI Insights page that generates a full business intelligence report on demand.

The SQL Window Functions

One of the things that makes this project technically interesting is that Gemini reliably generates complex SQL when the schema context is good. Here are examples of queries it produces correctly from natural language:

-- "Show year-over-year revenue growth"
SELECT
  STRFTIME('%Y', order_date) AS year,
  ROUND(SUM(sales), 2) AS total_sales,
  COUNT(DISTINCT order_id) AS orders
FROM sales GROUP BY year ORDER BY year;

-- "Profit margin by category"
SELECT category,
  ROUND(SUM(profit) / SUM(sales) * 100, 2) AS margin_pct
FROM sales GROUP BY category;

-- "Running total of sales by order date"
SELECT order_date, sales,
  SUM(sales) OVER (ORDER BY order_date) AS running_total
FROM sales ORDER BY order_date;

What I'd Do Differently

The current implementation re-sends the full schema with every API call, which is wasteful for a large schema. A production version would cache a schema embedding and do retrieval-augmented generation to include only the relevant tables. The insight generation prompt also needs more structure — right now it produces variable-length outputs, and for a BI context you want a consistent format: one headline number, one trend observation, one recommended action.

That said, for the scope of this project the app works reliably and handles a wide range of real analytical questions correctly. It's a solid proof of concept for what AI-augmented BI tooling looks like at the small-team scale.