# MCP Analytics Server

[![Python](https://img.shields.io/badge/Python-3.11%2B-blue?logo=python&logoColor=white)](https://www.python.org/)
[![SDK](https://img.shields.io/badge/MCP%20Python%20SDK-2.x-purple)](https://modelcontextprotocol.io/)
[![Database](https://img.shields.io/badge/Database-DuckDB-yellow?logo=duckdb&logoColor=black)](https://duckdb.org/)
[![Validation](https://img.shields.io/badge/Validation-Pydantic%20v2-e92063?logo=pydantic&logoColor=white)](https://docs.pydantic.dev/)
[![Code Style](https://img.shields.io/badge/Code%20Style-Ruff-black?logo=ruff&logoColor=white)](https://docs.astral.sh/ruff/)
[![Type Checked](https://img.shields.io/badge/Type%20Checked-Mypy-blue)](https://mypy-lang.org/)
[![Spec-Driven](https://img.shields.io/badge/Development-OpenSpec-green)](https://github.com/Fission-AI/OpenSpec)
[![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)](LICENSE)

> A production-grade **Model Context Protocol (MCP)** server built in Python that exposes typed, deterministic, and security-guarded analytical tools over a business dataset stored in **DuckDB**.

An external AI agent (e.g. GPT through the OpenAI Agents SDK, Claude Desktop, or Cursor) can dynamically discover and execute analytical queries without needing direct database access or running unconstrained SQL.

---

## ✨ Key Highlights

- **Python-First MCP Server**: Fully compliant with the official Model Context Protocol standard over `stdio`.
- **Model-Agnostic Architecture**: The server contains **no LLM** inside. It exposes clean, deterministic tool contracts that any MCP-compatible agent can invoke.
- **Embedded Columnar Analytics**: Powered by **DuckDB** for fast, efficient columnar aggregations on normalized enterprise data.
- **AST-Based SQL Guard**: Uses `sqlglot` to parse and validate ad-hoc queries, strictly allowing read-only `SELECT` statements and eliminating SQL injection or mutation risks.
- **Strict Typed Contracts**: All responses are validated through **Pydantic v2** models before reaching the client.
- **Interactive GPT Demo Client**: Out-of-the-box demonstration agent leveraging the **OpenAI Agents SDK** and evidence-based reasoning prompts.
- **Spec-Driven Development**: Engineered incrementally using **OpenSpec** for complete requirements traceability.

---

## 🏛️ System Architecture

```mermaid
flowchart TD
    User([User]) <--> Agent[GPT Agent / OpenAI Agents SDK]
    Agent <-->|MCP Protocol / stdio| Server[MCP Analytics Server]

    subgraph Server_Internal [MCP Analytics Server Boundary]
        Server --> Tools[Tool Layer]
        Tools --> DataTools[Dataset Tools]
        Tools --> ChurnTools[Churn Analytics Tools]
        Tools --> SQLTool[Read-Only SQL Tool]

        SQLTool --> SQLGuard[SQL Guard Security Layer]
        DataTools --> AnalyticsSvc[AnalyticsService]
        ChurnTools --> AnalyticsSvc
        SQLGuard --> DBSvc[DatabaseService]
        AnalyticsSvc --> DBSvc

        DBSvc --> DuckDB[(DuckDB)]
    end

    DuckDB --> Table[(customers Table - Telco Dataset)]
```

---

## 🛡️ Safe SQL Execution & Security Boundaries

Any SQL input received from an AI agent is treated as **untrusted input**. The server enforces strict AST validation via `sqlglot` before query execution:

```
Allowed Operations:
  ✅ SELECT contract, AVG(monthly_charges) FROM customers GROUP BY contract
  ✅ WITH cohorts AS (SELECT * FROM customers WHERE tenure > 24) SELECT COUNT(*) FROM cohorts

Blocked Operations:
  ❌ DELETE FROM customers WHERE churn = true        (Mutation Rejected)
  ❌ DROP TABLE customers                             (DDL Rejected)
  ❌ SELECT * FROM customers; DROP TABLE customers    (Multi-statement Rejected)
  ❌ ATTACH 'external.db'                             (Engine I/O Rejected)
```

- **Row Limit Guard**: Ad-hoc queries are capped at `MAX_RESULT_ROWS = 100` to protect the agent's context window.
- **Table Allowlists**: Only authorized analytics tables (`customers`) can be queried.

---

## 🧰 MCP Tools Catalog

| Tool Name | Purpose | Key Parameters | Return Type |
|---|---|---|---|
| `get_dataset_info` | High-level dataset metadata, row and column counts, primary table name, target variable. | *None* | `DatasetInfo` |
| `list_columns` | Schema inspection returning all available columns and their database data types. | *None* | `list[ColumnInfo]` |
| `describe_column` | Statistical metrics (`min`, `max`, `mean`, `median`) for numeric columns, or category distributions for categorical columns. | `column: str` | `NumericColumnDescription` / `CategoricalColumnDescription` |
| `get_churn_summary` | Overall customer count, churned count, retained count, and historical churn rate in `[0.0, 1.0]`. | *None* | `ChurnSummary` |
| `get_churn_by_dimension` | Segmented churn metrics grouped by an approved dimension (`contract`, `internet_service`, `payment_method`, etc.). | `dimension: str` | `DimensionChurnResult` |
| `run_readonly_sql` | Guarded analytical SQL execution for complex custom calculations not covered by standard tools. | `query: str` | `SQLResult` |

---

## 🚀 Quickstart Guide

### 1. Prerequisites
- Python 3.11+
- Git

### 2. Installation
```bash
# Clone repository
git clone https://github.com/Jojeda96/mcp-analytics-server.git
cd mcp-analytics-server

# Create and activate virtual environment
python -m venv .venv
source .venv/bin/activate  # On Windows: .\.venv\Scripts\Activate.ps1

# Install in editable mode with development tools
pip install -e ".[dev]"
```

### 3. Build Analytics Database
```bash
# Ingest raw Telco CSV, validate schema, normalize, and build DuckDB
python scripts/build_database.py
```

### 4. Run the MCP Server
```bash
# Run server standalone over stdio
mcp-analytics
# or
python -m mcp_analytics.server
```

### 5. Run the Interactive GPT Demo Client
Configure your OpenAI API key in `.env`:
```bash
cp .env.example .env
# Edit .env and set OPENAI_API_KEY=sk-...
```

Run the interactive demo:
```bash
# Interactive REPL mode
python client/gpt_demo.py

# Or evaluate all 10 standard demonstration questions in batch
python client/gpt_demo.py --all-examples
```

---

## 🔌 Connecting to MCP Clients

### Claude Desktop / Cursor
Add the following configuration to your `claude_desktop_config.json` or Cursor MCP settings:

```json
{
  "mcpServers": {
    "telco-analytics": {
      "command": "python",
      "args": ["-m", "mcp_analytics.server"],
      "cwd": "/absolute/path/to/mcp-analytics-server",
      "env": {
        "DUCKDB_PATH": "data/processed/telco.duckdb",
        "LOG_LEVEL": "INFO",
        "MAX_RESULT_ROWS": "100"
      }
    }
  }
}
```

---

## 🧪 Testing & Quality Assurance

```bash
# Run complete test suite (Unit & Integration) with coverage
pytest --cov=src --cov-report=term-missing

# Run Ruff linter and formatter checks
ruff check .
ruff format --check .

# Run static type checking
mypy src client scripts tests
```

---

## 📐 Development Workflow (OpenSpec)

This project was developed following **Spec-Driven Development (SDD)** with [OpenSpec](https://github.com/Fission-AI/OpenSpec). Every capability is tracked through explicit proposals, delta specs, design documents, and verifiable tasks:

```text
openspec/
├── specs/                          # Consolidated capabilities
│   ├── project-foundation/
│   ├── telco-data-foundation/
│   ├── core-analytics-service/
│   ├── core-mcp-tools/
│   ├── safe-readonly-sql-tool/
│   ├── openai-gpt-demo-client/
│   └── portfolio-hardening/
└── changes/archive/                # Historical change audit trail
```

---

## 📂 Project Structure

```text
mcp-analytics-server/
├── .github/workflows/ci.yml       # GitHub Actions CI matrix pipeline
├── assets/                        # Diagrams and visual assets
├── client/
│   └── gpt_demo.py                # Interactive OpenAI Agents SDK demo client
├── data/
│   ├── raw/                       # Source CSV files
│   └── processed/                 # Generated DuckDB database
├── docs/
│   ├── architecture.md            # Deep-dive architecture and layers
│   ├── security.md                # Threat model and AST SQL Guard details
│   └── decisions.md               # Architecture Decision Records (ADRs)
├── examples/
│   ├── questions.md               # 10 evaluated demo business questions
│   └── mcp-config.example.json    # Standard client configuration
├── scripts/
│   ├── download_dataset.py        # Dataset provenance & download instructions
│   ├── validate_dataset.py        # Strict raw data schema & domain validator
│   └── build_database.py          # Data cleaner and DuckDB table builder
├── src/mcp_analytics/
│   ├── config.py                  # Pydantic Settings and environment config
│   ├── errors.py                  # Domain exception hierarchy
│   ├── server.py                  # MCP server lifecycle and CLI entrypoint
│   ├── schemas/                   # Pydantic response models
│   ├── security/                  # AST SQLGuard parser
│   ├── services/                  # DatabaseService & AnalyticsService
│   └── tools/                     # Dataset, Analytics & SQL MCP tools
├── tests/
│   ├── fixtures/                  # Curated sample CSV test fixtures
│   ├── unit/                      # Fast unit tests for logic and security
│   └── integration/               # Database and MCP tool integration tests
├── Dockerfile                     # Containerization recipe
├── pyproject.toml                 # Package definition & tool configs
├── CHANGELOG.md                   # Version release notes
├── LICENSE                        # MIT License
└── README.md
```

---

## 📄 License

This project is licensed under the MIT License — see the [LICENSE](LICENSE) file for details.