# Custom Fabric MCP

An MCP (Model Context Protocol) server for AI-powered, governed access to Microsoft Fabric data assets. Enables AI agents to query Fabric Warehouses and Lakehouses with full governance, validation, and explainability.

## Features

- **16 MCP tools** — query, schema discovery, knowledge retrieval, export, admin
- **Governance-first** — RBAC/ABAC, PII masking, rate limiting, SQL deny patterns
- **Validated SQL** — schema-aware validation, query optimization (NOLOCK, TOP), cost estimation
- **Grounded responses** — citations, confidence scoring, natural-language explanations
- **Knowledge retrieval** — business glossary, KPI dictionary, knowledge graph, query history
- **Caching** — Redis-backed query and result cache with semantic hashing
- **Export services** — charts (matplotlib), Excel (openpyxl), PDF/Word, email with confirmation gate

## Quick Start

```bash
# Install dependencies
uv sync

# Install with dev tools (ruff, pytest, pyright)
uv sync --group dev

# Install with optional services (matplotlib, openpyxl)
uv sync --extra services

# Run in development mode (MCP Inspector)
uv run mcp dev src/server/main.py

# Run with MCP inspector
uv run mcp inspector src/server/main.py

# Run directly (stdio transport)
uv run python -m src.server.main

# Lint and format
uv run ruff check src/
uv run ruff format src/

# Run tests
uv run pytest tests/ -x --timeout=30

# Seed sample schema for local dev
uv run python scripts/seed-schema.py
```

## Configuration

Copy `.env.example` to `.env` and fill in your values:

```bash
cp .env.example .env
```

### Required Configuration

| Variable | Description |
|----------|-------------|
| `FABRIC_SQL_ENDPOINT` | Fabric workspace SQL endpoint URL |
| `FABRIC_DATABASE_NAME` | Database name in Fabric |
| `AZURE_TENANT_ID` | Azure AD tenant ID |
| `AZURE_CLIENT_ID` | App registration client ID |
| `AZURE_CLIENT_SECRET` | App registration client secret |

### Optional Configuration

| Variable | Default | Description |
|----------|---------|-------------|
| `SERVER_TRANSPORT` | `stdio` | Transport: `stdio` or `sse` |
| `SERVER_PORT` | `8080` | Port for SSE transport |
| `LOG_LEVEL` | `INFO` | Log level |
| `LOG_FORMAT` | `json` | Log format: `json` or `console` |
| `REDIS_URL` | `redis://localhost:6379` | Redis for caching |
| `REDIS_CACHE_TTL_SECONDS` | `3600` | Cache TTL (1 hour) |
| `GOVERNANCE_MAX_ROWS_DEFAULT` | `1000` | Default row limit |
| `GOVERNANCE_RATE_LIMIT_PER_MINUTE` | `20` | Queries per minute |
| `GOVERNANCE_TOKEN_BUDGET` | `4000` | CU-second budget |

See `.env.example` for all available options.

## Architecture

```
User → LLM → AgentPlanner → [Retrieval → Governance → Validation → Execution → Masking → Response]
                                  ↓            ↓           ↓            ↓          ↓         ↓
                              Glossary     RBAC/PII    Optimizer     Fabric     PiiMasker  Assembler
                              KG/RAG      RateLimit   CostEstimate   pyodbc    (HMAC salt) Citations
                              History     DenyPatterns  Schema       Pool(5)    HideColumn  Confidence
```

### Pipeline (9 steps in AgentPlanner)

1. **Retrieve context** — glossary, KG, history, RAG, session memory
2. **Governance pre-check** — RBAC, rate limits, deny patterns
3. **Schema discovery** — INFORMATION_SCHEMA (cached with TTL)
4. **Validate + optimize** — table/column existence, NOLOCK, TOP injection, cost estimate
5. **Execute** — parameterized query against Fabric SQL Endpoint
6. **Post-query masking** — PII redaction/partial mask/tokenize based on role × sensitivity
7. **Assemble response** — citations, confidence (weighted), explanation, warnings
8. **Audit log** — structured entry with user, tables, governance decisions, latency
9. **Store** — query history + session memory for future reuse

## Deployment

### Docker

```bash
docker build -t custom-fabric-mcp .
docker run -p 8080:8080 --env-file .env custom-fabric-mcp
```

### Azure Container Apps (Bicep)

```bash
# Deploy to dev environment
./scripts/deploy.sh dev latest

# Or use az CLI directly
az deployment group create \
  --resource-group rg-fabric-mcp-dev \
  --template-file infra/main.bicep \
  --parameters infra/parameters.dev.json
```

Infrastructure includes: VNET, Key Vault, Container Registry, Redis Cache, Container App with auto-scaling (1-3 replicas).

## Project Structure

```
src/
├── common/       # Config, errors (6 types), logging (structlog)
├── server/       # MCP entry point (app factory pattern)
├── fabric/       # Auth, connection pool, query executor, schema
├── governance/   # RBAC, PII masking, policy engine, audit
├── validation/   # SQL validator, optimizer, cost estimator
├── retrieval/    # RAG, knowledge graph, glossary, history, memory
├── cache/        # Redis client, query cache, result cache
├── response/     # Citations, confidence, explainer, assembler
├── agents/       # Planner (pipeline), model router, workflow engine
├── tools/        # 16 MCP tools (the API surface)
└── services/     # Charts, Excel, documents, email, confirmation
```

## MCP Tools

| Tool | Description |
|------|-------------|
| `health_check` | Server health and configuration status |
| `get_available_tools` | Discover all available tools |
| `query_data` | Execute validated SQL against Fabric |
| `query_revenue` | Query revenue by year/region/grouping |
| `query_kpi` | Query specific KPI (total_revenue, customer_count, etc.) |
| `get_schema` | Get complete database schema |
| `get_tables` | List available tables |
| `get_columns` | Get column details for a table |
| `search_glossary` | Search business term definitions |
| `get_kpi_definition` | Get KPI calculation logic |
| `search_docs` | Search business documentation (RAG) |
| `export_excel` | Export results to .xlsx |
| `generate_chart` | Generate bar/line/pie chart (PNG) |
| `get_query_history` | Search past queries for reuse |
| `check_access` | Verify user access to tables |
| `get_data_freshness` | Check data refresh status |

## License

MIT