# Lexiguard HR — Local MySQL MCP Server for Cursor

A small **Model Context Protocol (MCP)** demo that lets [Cursor](https://cursor.com) query a local **MySQL** HR database in natural language—without pasting credentials or schema into every chat.

```text
Cursor (AI)  ←── MCP (stdio) ──→  server.py  ←── SQL ──→  MySQL (lexiguard)
```

## What it does

| MCP tool | Description |
|----------|-------------|
| `get_db_schema` | Lists tables, columns, types, and keys so the AI knows what exists |
| `query_hr_db` | Runs **read-only** SQL (`SELECT`, `SHOW`, `DESCRIBE`, `EXPLAIN`, `WITH`) and returns formatted text |

Example questions in Cursor chat:

- *“Use get_db_schema to show all tables.”*
- *“Who works in Engineering?”*
- *“List employees with their department names.”*
- *“Average salary by department.”*

## Tech stack

- **Python 3.10+**
- [MCP Python SDK](https://github.com/modelcontextprotocol/python-sdk) (`mcp`, FastMCP)
- [mysql-connector-python](https://pypi.org/project/mysql-connector-python/)
- **Cursor** with project MCP config (`.cursor/mcp.json`)

## Prerequisites

- [MySQL](https://dev.mysql.com/downloads/) running locally
- [Cursor](https://cursor.com) IDE
- Python 3.10+

## Quick start

### 1. Create database and sample data

In MySQL Workbench or the CLI:

```sql
CREATE DATABASE IF NOT EXISTS lexiguard;
USE lexiguard;

CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO departments (name, location) VALUES
('Engineering', 'London'),
('HR', 'Manchester'),
('Finance', 'Birmingham');

INSERT INTO employees (first_name, last_name, email, department_id, hire_date, salary) VALUES
('Alice', 'Smith', 'alice.smith@lexiguard.com', 1, '2022-03-15', 65000.00),
('Bob', 'Jones', 'bob.jones@lexiguard.com', 1, '2021-07-01', 72000.00),
('Carol', 'Brown', 'carol.brown@lexiguard.com', 2, '2020-11-20', 48000.00),
('David', 'Taylor', 'david.taylor@lexiguard.com', 3, '2023-01-10', 55000.00);
```

### 2. Install Python dependencies

**Windows (PowerShell):**

```powershell
cd db-mcp-server
python -m venv .venv
.\.venv\Scripts\pip install -r requirements.txt
```

**macOS / Linux:**

```bash
cd db-mcp-server
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

### 3. Configure environment

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

Edit `.env` with your MySQL settings (especially `MYSQL_PASSWORD` if root has a password).

### 4. Connect Cursor

This repo includes **project-level** MCP config at `.cursor/mcp.json`.

1. Open this folder as a Cursor workspace.
2. Go to **Cursor Settings → Models → MCP**.
3. Enable **`lexiguard-hr`** (should show **2 tools**).
4. Restart Cursor if the server does not appear.

**macOS / Linux:** Update `.cursor/mcp.json` so `command` points to your venv Python, for example:

```json
"command": "${workspaceFolder}/.venv/bin/python"
```

**Windows** (default in this repo):

```json
"command": "${workspaceFolder}/.venv/Scripts/python.exe"
```

Credentials are loaded from `.env` via `envFile`—do not commit `.env` (it is in `.gitignore`).

### 5. Try it

Open chat (`Ctrl+L` / `Cmd+L`), use **Agent** mode, and allow MCP tool runs when prompted.

## Project layout

```text
db-mcp-server/
├── .cursor/
│   └── mcp.json          # Cursor MCP launch config
├── server.py             # MCP server (FastMCP + MySQL)
├── requirements.txt
├── .env.example          # Copy to .env locally
├── .gitignore
├── README.md             # This file (demo / setup)
└── MCP-HOW-IT-WORKS.md   # Short technical notes on MCP
```

## Security notes

- **Read-only queries only** in `query_hr_db` (no `INSERT` / `UPDATE` / `DELETE`).
- Keep `.env` local; use `.env.example` as a template for collaborators.
- Intended for **local development** demos, not production exposure of a database.

## Troubleshooting

| Issue | What to check |
|-------|----------------|
| MCP server red / disconnected | MySQL running; `.env` password correct; re-run `pip install` |
| “Connection refused” | Start MySQL service; confirm `MYSQL_HOST` / `MYSQL_PORT` |
| Tools not listed | Reopen project folder; toggle MCP server; restart Cursor |
| GitKraken MCP error | Unrelated third-party config in global `~/.cursor/mcp.json` |

## Learn more

See **[MCP-HOW-IT-WORKS.md](./MCP-HOW-IT-WORKS.md)** for a concise technical explanation of MCP and how this repo implements it.

## License

Use and adapt freely for learning and demos.