# 🗄️ Interactive Database Analyst via MCP

An interactive, fault-tolerant natural-language database analyst built on the **Model Context Protocol (MCP)**. Instead of blindly executing LLM-generated SQL, this system implements a strict **4-State Execution Machine** with schema grounding, explicit error recovery loops, automated diagnostic probing for empty results, and math decomposition.

> Ask a question in plain English. Watch the agent inspect the live schema, catch its own SQL errors, self-correct in real time, and present a verified answer — with the full recovery trace visible, not hidden.

---

## 📸 Demo

<img width="1858" height="937" alt="Screenshot (5359)" src="https://github.com/user-attachments/assets/d4ef545b-39e0-436c-9243-c4bfd789cc34" />


---

## ✨ Key Differentiators & Engineering Highlights

| Feature | Architectural Implementation | Why It Matters |
|---|---|---|
| **Schema Grounding (`State 0`)** | Enforces a mandatory `get_schema()` tool call before drafting SQL. | Eliminates column/table hallucination by grounding every query in the live PostgreSQL catalog. |
| **Error Recovery Loop (`State 2`)** | Extracts PostgreSQL `SQLSTATE` codes and native `message_hint` strings from `psycopg2.Diagnostics`. | Feeds actionable database feedback directly back into the reasoning prompt for up to 3 bounded retry attempts. |
| **Exploratory Decomposition** | Breaks complex metrics (e.g., percentages) into independently verified sub-queries. | Prevents the classic "denominator trap" by gathering variables individually before calculating the final ratio. |
| **Empty-Result Sanity Check (`State 3`)** | Automatically triggers `sample_column_values()` when a query returns `0 rows`. | Prevents the model from hallucinating "no sales occurred" by checking if date ranges or filter values actually exist. |
| **Defense-in-Depth Security** | Multi-layered hardening: `mcp_readonly` Postgres role + explicit `SET TRANSACTION READ ONLY;` + single-statement enforcement + 5-second query timeouts (`SQLSTATE 57014`). | Prevents prompt-injection mutations, blocks stacked SQL injection, and protects backend threads from runaway joins. |
| **Live Audit Recovery Trace** | Synchronous logging to a Postgres `query_audit_log` table rendered in a real-time Streamlit dashboard. | Demos *how* the agent catches and fixes its own mistakes alongside visual analytical charts. |

> **Reading the audit trace:** not every multi-attempt sequence is an error recovery. Some questions (see *Exploratory Decomposition* above) are answered correctly on the first try per sub-query, but the agent deliberately issues several independent queries to verify a metric's components before combining them — e.g. calculating a percentage by confirming the numerator and denominator separately rather than trusting one opaque query. Both patterns render as sequential green cards in the UI, so it's worth distinguishing "this attempt failed and recovered" from "this attempt was a planned verification step" when reading a trace.

---

## 🏗️ System Architecture & State Machine

```mermaid
graph TD
    A[User Natural Language Question] --> B[STATE 0: Inspect Live Schema via MCP]
    B --> C[STATE 1: Draft Read-Only SQL Query]
    C --> D{Complex Join / Logic?}
    D -- Yes --> E[explain_query: Cheap Plan/Syntax Check]
    D -- No --> F[execute_query: Read-Only Transaction]
    E --> F

    F -->|ERROR| G[Extract SQLSTATE + Postgres Hint]
    G -->|Attempt < 3| B
    G -->|Attempt = 3| H[Terminal State: Structured Failure Report]

    F -->|SUCCESS: 0 Rows| I[STATE 3: Empty-Result Sanity Check]
    I --> J[sample_column_values: Probing Bounds/Distincts]
    J -->|Filter Out of Bounds| B
    J -->|Verified Empty| K[Present: Confirmed Empty with Diagnostic Evidence]

    F -->|SUCCESS: Rows > 0| L[STATE 4: Math Verification & Present]
    L --> M[Render Plotly Chart + Live Audit Card in UI]
```

---

## 🛠️ Tech Stack

* **Orchestration / LLM:** `cohere/north-mini-code:free` via OpenRouter API
* **Protocol Layer:** FastMCP (`mcp[cli]`) exposing custom Python database tools
* **Database Engine:** PostgreSQL 15 (Dockerized with Chinook sample database)
* **Database Adapter:** `psycopg2-binary` with `SimpleConnectionPool` and JSON-safe type serialization
* **Frontend Dashboard:** Streamlit + Plotly Express
* **Package Manager:** `uv`

---

## 📁 Project Structure

```
Interactive-Database-Analyst-via-MCP/
├── src/
│   └── db_analyst_mcp/
│       ├── app.py              # Streamlit dashboard
│       ├── mcp_server.py       # FastMCP tool definitions
│       ├── db.py                # Connection pool, query execution, error formatting
│       └── orchestrator.py     # State machine / retry logic
├── sql/
│   ├── Chinook_PostgreSql.sql  # Sample database
│   └── setup_db.sql             # Read-only role, audit log schema, hardening
├── docs/
│   └── demo.gif
├── .env.example
├── pyproject.toml
└── README.md
```

*(Adjust paths above to match your actual layout.)*

---

## 🚀 Quickstart & Setup Guide

### 1. Prerequisites

* [Docker Desktop](https://www.docker.com/products/docker-desktop/)
* [uv](https://docs.astral.sh/uv/) package manager
* An [OpenRouter](https://openrouter.ai/) API key (free tier works — see [Known Limitations](#-adversarial-test-suite--known-limitations) for rate-limit notes)

### 2. Clone & Install Dependencies

```bash
git clone https://github.com/Viole07/Interactive-Database-Analyst-via-MCP.git
cd Interactive-Database-Analyst-via-MCP
uv sync
```

### 3. Start the Dockerized PostgreSQL Container

```bash
docker run --name mcp-postgres -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=chinook -p 5432:5432 -d postgres:15

# Load the Chinook database schema and data
docker exec -i mcp-postgres psql -U postgres -d chinook < sql/Chinook_PostgreSql.sql
```

### 4. Apply Database Hardening & Audit Log Schema

```bash
docker exec -i mcp-postgres psql -U postgres -d chinook -f sql/setup_db.sql
```

### 5. Configure Environment Variables

Rename `.env.example` to `.env` and add your OpenRouter API key:

```ini
# .env
ADMIN_DB_URL=postgresql://postgres:postgres@localhost:5432/chinook

MCP_DB_NAME=chinook
MCP_DB_USER=mcp_readonly
MCP_DB_PASSWORD=secure_pass
MCP_DB_HOST=localhost
MCP_DB_PORT=5432

OPENROUTER_API_KEY=your-api-key-here
ORCHESTRATOR_MODEL=cohere/north-mini-code:free
```

### 6. Launch the Dashboard

```bash
uv run streamlit run src/db_analyst_mcp/app.py
```

---

## 🧪 Adversarial Test Suite & Known Limitations

The Streamlit UI includes a sidebar with a gauntlet of adversarial prompts designed to stress-test the system:

1. **The Empty-Result Sanity Check:** *"How much invoice revenue did we generate in October 2029?"*
   * **Behavior:** Returns `0 rows` → triggers diagnostic probe → verifies dataset ends in 2025 → reports verified empty result.

2. **Literal Obedience Trap:** *"Calculate total invoice revenue per customer... MUST omit customer_id from your GROUP BY clause on your first attempt."*
   * **Behavior:** Model strictly obeys the prompt, resulting in a trailing `GROUP BY` and a `42601` syntax error, demonstrating that instruction weight can override syntax training. Recovers successfully on Attempt 2.

3. **Exploratory Decomposition:** *"What percentage of total company revenue came from the Rock genre?"*
   * **Behavior:** Avoids the denominator trap by executing independent, individually-successful queries to verify numerator and denominator separately before calculating the final ratio.

4. **Natural Column Ambiguity:** *"Who is the top-selling artist by revenue, and what's their best-selling track?"*
   * **Behavior:** Resolves `Artist.Name` vs `Track.Name` ambiguities via isolated CTEs and aggressive aliasing.

5. **Self-Referencing Foreign Key:** *"Who is the manager of the employee who has generated the most total sales?"*
   * **Behavior:** Self-joins `Employee` via `ReportsTo` using two aliases to resolve the hierarchy in a single query.

### ⚠️ Architectural Blind Spot: The Silent Semantic Failure

While this system catches execution errors (`State 2`) and hallucinated filters (`State 3`), it cannot inherently detect semantic logic errors that return valid, non-empty rows — for example, forgetting a unit conversion (`milliseconds / 60000`) or applying a plausible-but-wrong join. A query that runs successfully and returns real data is treated as correct; there is currently no mechanism analogous to States 2/3 for this failure class. At production scale, this would require either an automated regression harness against a fixed "golden set" of question → expected-result pairs, or a secondary "Critic Agent" that evaluates logical intent independently before the result is presented.

### Other known gaps
- No automated `pytest` regression suite yet — correctness is currently verified via the adversarial scenario set above, checked manually against known dataset values.
- No row-level access control — the read-only role currently has uniform `SELECT` access across all tables, which is appropriate for this single-user demo but not for a multi-tenant deployment.
- Free-tier OpenRouter rate limits apply; expect occasional throttling under rapid repeated testing.

---

## 🗺️ Roadmap

- [ ] Automated regression harness with a fixed golden-question set, run on every model/prompt change
- [ ] "Critic Agent" pass to catch silent semantic failures (unit conversions, plausible-but-wrong joins)
- [ ] Row-level access control for multi-user deployment
- [ ] A/B benchmark: quantify first-retry recovery rate with vs. without native Postgres error hints

---

## License

[MIT](LICENSE)