# HR MCP — HR Analytics MCP Server

A small, extensible [MCP](https://modelcontextprotocol.io) server that exposes an
HR analytics database (`hr_db`) to Claude / any MCP client. Ask questions in plain
English; the client reads the schema, writes SQL, and runs it.

This is the **base layer** — two foundational tools that make every HR question
answerable today, plus a structure built for adding more tools tomorrow.

## What's in the database

`hr_db` (MySQL 8.4, AWS RDS) — loaded from `excel_files/` by `load_data.py`:

| Table | Grain | Rows |
|---|---|---|
| `employees` | one per employee (the hub) | 100 |
| `attendance_leave` | employee × month | 540 |
| `payroll` | employee × month | 540 |
| `performance_reviews` | employee × review period | 100 |
| `attrition_exit` | one exit event per employee | 12 |

All five link to `employees.employee_id`; `employees.reporting_manager_id`
self-references for the org chart. Every join/filter/group column is indexed.

## Tools

| Tool | Purpose |
|---|---|
| `get_db_schema` | Returns the full schema, join keys, and data pitfalls. Call once. |
| `run_db_query` | Runs a read-only `SELECT`/`WITH` query (writes blocked, 500-row cap). |

Together these answer anything — headcount, attrition by department, salary
trends, attendance vs. performance, org hierarchy, etc.

## Setup

```bash
pip install -r requirements.txt        # or reuse an existing venv that has `mcp`
cp .env.example .env                    # then fill in DB creds (see below)
```

`.env`:
```
DB_HOST=<rds-endpoint>
DB_USER=<user>
DB_PASSWORD=<password>
DB_NAME=hr_db
DB_PORT=3306
```

## Load / reload the data

```bash
python load_data.py
```
Idempotent — creates `hr_db` if needed, drops & recreates the 5 tables, and
reloads them from `excel_files/`.

## Run the server

```bash
python server.py                 # stdio — for Claude Desktop
python -m mcp dev server.py      # dev inspector — browser testing
```

### Claude Desktop config

```json
{
  "mcpServers": {
    "hr": {
      "command": "C:/path/to/python.exe",
      "args": ["C:/Users/sathv/Desktop/HR_MCP/server.py"]
    }
  }
}
```

## Project layout

```
HR_MCP/
├── excel_files/          source spreadsheets (system of record for load_data.py)
├── load_data.py          Excel -> hr_db loader (idempotent)
├── config.py             loads .env, builds the shared SQLAlchemy engine
├── adapters/
│   └── query.py          schema text + run_query() (SELECT-only, serialisation, logging)
├── tools/
│   └── query.py          get_db_schema + run_db_query (registered on the server)
├── server.py             FastMCP server; registers tool groups
└── requirements.txt
```

## Adding a new tool (the "tomorrow" path)

The server is built so new capabilities slot in without touching existing code:

1. **Business logic** → add a function in `adapters/` (or a new adapter module)
   that calls `adapters.query.run_query(...)` or `config.engine` directly.
2. **Expose it** → create `tools/<name>.py`:
   ```python
   from mcp.server.fastmcp import FastMCP
   import adapters.mymodule as _adapter

   def register(mcp: FastMCP) -> None:
       @mcp.tool()
       def my_tool(arg: str) -> dict:
           """One-line description the model reads to decide when to call this."""
           return _adapter.do_something(arg)
   ```
3. **Wire it** → in `server.py`, `from tools import <name>` and call
   `<name>.register(mcp)`.

Ideas for the next layer: `get_hr_dashboard` (headcount / attrition / payroll
KPIs), `generate_excel_report`, `attrition_risk` scoring, a `dim_date` +
`month_date` upgrade for faster time-series, and enforced foreign keys.

## Making queries faster

- Every common filter/join/group column is already indexed (see `load_data.py`).
- Results are capped at 500 rows to keep payloads small.
- Add composite indexes for specific hot query shapes as they emerge, e.g.
  `INDEX (department, month)` patterns via a covering table/view.