MCP Analytics Server

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

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

# 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

# Ingest raw Telco CSV, validate schema, normalize, and build DuckDB
python scripts/build_database.py

4. Run the MCP Server

# 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:

cp .env.example .env
# Edit .env and set OPENAI_API_KEY=sk-...

Run the interactive demo:

# 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:

{
  "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

# 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. Every capability is tracked through explicit proposals, delta specs, design documents, and verifiable tasks:

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

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 file for details.