# MCP Demo — GitHub Copilot + Your Database

> **What if you could ask Copilot a question and it queried your database to answer it?**
> That's what this demo shows. Clone it, run two commands, and watch it work.

---

## What is MCP?

**Model Context Protocol (MCP)** is an open standard that lets AI assistants like GitHub Copilot connect directly to your tools and data — in real time, with guardrails you control.

Instead of copy-pasting data into chat, Copilot just *asks* your server:

| You type in Copilot Chat | What happens |
|---|---|
| `What tables are in the database?` | Copilot calls `get_schema`, returns your full schema |
| `Show me all products under $200` | Copilot writes and runs the SQL query live |
| `Which users have the admin role?` | Instant answer — emails auto-redacted |

**MCP isn't AI-only.** The same server can be called by scripts, pipelines, or any client. The LLM is just the first killer app.

---

## See It In Action

Real responses from this running server:

**"Show me all products under $200"**

| Product | Price | Stock |
|---|---|---|
| Wireless Mouse | $49.99 | 120 |
| USB-C Hub | $79.99 | 60 |
| External SSD (1TB) | $129.99 | 35 |
| Mechanical Keyboard | $149.99 | 45 |
| Webcam (4K) | $199.99 | 12 |

**Guardrail in action — PII is auto-redacted:**
```json
{
  "rows": [{"id": 1, "name": "Alice Johnson", "email": "[REDACTED]", "role": "admin"}],
  "pii_notice": "The following field(s) were redacted to protect PII: email"
}
```

**Dangerous query? Blocked before it touches the database:**
```json
{ "success": false, "error": "Query contains a blocked pattern and was rejected for security reasons." }
```

---

## Get Running in 3 Steps

**Prerequisites:** Python 3.12+, VS Code, GitHub Copilot extension

```bash
# 1. Install
git clone https://github.com/coderkanasu/mcp-demo && cd mcp-demo
pip install -r requirements.txt   # simple install
# or: pip install -e .            # editable install (for development)

# 2. Set up the database
python mcp_server/db/init_db.py

# 3. Verify it works
python mcp_server/tools/sql_query_tool.py
```

**Connect to Copilot:** A `.vscode/mcp.json` is already included — open this repo in VS Code and Copilot Agent mode picks it up automatically. No manual config needed.

```json
{
  "servers": {
    "demo-sql-server": {
      "type": "stdio",
      "command": "python3",
      "args": ["-m", "mcp_server.server"],
      "env": { "PYTHONPATH": "${workspaceFolder}" }
    }
  }
}
```

Then open **Copilot Chat → Agent mode** and start asking questions.

---

## Built-in Guardrails

This is a demo — but it ships with real security controls, not placeholders:

| Guardrail | Detail |
|---|---|
| **PII auto-redaction** | `email`, `phone`, `address`, `password`, and 9 other field patterns scrubbed from all results |
| **SQL injection blocking** | 17 patterns blocked: `UNION SELECT`, `OR 1=1`, stacked queries, `DROP`, `--` comments, and more |
| **Allowed operations only** | `SELECT`, `INSERT`, `UPDATE` only — `DROP`, `DELETE`, `TRUNCATE` rejected |
| **Row cap** | 100 rows max per query — prevents bulk extraction |
| **Rate limiting** | 30 calls per 60-second window |
| **Audit logging** | Every query logged with timestamp and outcome |
| **Sanitised errors** | Stack traces never returned to the LLM |

**Not included (needed for production):** user authentication, role-based access control, write-operation approval gates. See [PRODUCTION_READINESS.md](docs/guides/PRODUCTION_READINESS.md).

---

## What's Inside

```
mcp_server/
├── server.py               # MCP server — rate limiting, routing, error handling
├── tools/
│   └── sql_query_tool.py   # PII scrubbing, injection blocking, query execution
└── db/
    ├── init_db.py          # Seeds sample e-commerce data
    └── demo.db             # SQLite database (users, products, orders)
.vscode/
└── mcp.json                # Plug-and-play Copilot config
requirements.txt            # pip install -r requirements.txt
pyproject.toml              # package metadata + dev dependencies
```

---

## How It Works

```
You (Copilot Chat)
      │  "Show me all products under $200"
      ▼
GitHub Copilot (Agent mode)
      │  calls get_schema, then execute_query
      ▼
MCP Server  ←── validates, rate-limits, scrubs PII
      │
      ▼
SQLite Database  ←── returns rows
      │
      ▼
Copilot formats and presents the answer
```

---

**Want to extend it?**
- Add a new tool: create a class in `mcp_server/tools/`, register it in `server.py`
- Use a real database: swap SQLite for PostgreSQL/MySQL in `sql_query_tool.py`
- Add auth: wrap `execute_query` with a token check

---

## Requirements

- Python 3.12+
- VS Code with GitHub Copilot (Agent mode)
- macOS / Linux / Windows

---

## License

MIT — free to use, fork, and adapt.