# PeopleSoft MCP Server

A minimal [Model Context Protocol](https://modelcontextprotocol.io) (MCP) server, built as a worked example of how an MCP server is constructed. It exposes a local SQLite contracts/billing database (`PROJECT`, `CA_DETAIL`, `CA_BILL_PLAN`) through a small set of tools, ranging from generic schema introspection to purpose-built semantic queries.

## Features

- **Schema introspection tools** — discover tables and columns without any prior knowledge of the schema
- **Semantic tools** — purpose-built queries for the contracts/billing domain (projects, contract lines, bill plans)
- **Direct SQL tool** — an escape hatch for arbitrary queries once the schema is known
- **Direct database access** via Python's stdlib `sqlite3`, wrapped in `asyncio.to_thread` for async compatibility

## Quick Start

### Prerequisites

- Python 3.11+
- [uv](https://github.com/astral-sh/uv) package manager (recommended)

### Installation

```bash
# Clone the repository
git clone <repo-url>
cd peoplesoft-mcp

# Install dependencies
uv sync

# Create finstg.db and load static data
uv run create_db.py
```

### Configuration

1. Copy the example environment file:
```bash
cp .env.example .env
```

2. Edit `.env` if you want to point at a different SQLite file (defaults to `finstg.db` in the repo root):
```bash
SQLITE_DB_PATH=finstg.db
```

3. Copy `.cursor/mcp.json.example` to `.cursor/mcp.json` and update the path to your installation.

### Running the Server

```bash
uv run peoplesoft_server.py
```

### Cursor IDE Integration

The MCP config (`.cursor/mcp.json`, copied from `.cursor/mcp.json.example`) should look like:

```json
{
  "mcpServers": {
    "peoplesoft": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/mcp_ps/",
        "run",
        "peoplesoft_server.py"
      ]
    }
  }
}
```

## Available Tools

### Schema Introspection (2 tools)
| Tool | Description |
|------|-------------|
| `list_tables` | List tables in the local database, optionally filtered by name |
| `describe_table` | Get table structure (columns, types, primary keys) |

### Contracts & Billing Module (4 tools)
| Tool | Description |
|------|-------------|
| `list_projects` | List projects, filterable by business unit/status/type |
| `get_project_contracts` | Get a project plus its contract lines and bill plans |
| `list_contracts` | List contract detail lines with their bill plan info |
| `get_bill_plan` | Get bill plan details for a specific contract line |

### Billing Analysis (2 tools)
| Tool | Description |
|------|-------------|
| `check_project_billing_setup` | Check each project's contract line(s), bill plan, bill plan type, and effective statuses, to spot billing setup problems |
| `cost_reimbursable_billable_transactions` | Summarize undistributed cost-reimbursable billable transactions (sums, counts, date range) per project |

### Direct Query (1 tool)
| Tool | Description |
|------|-------------|
| `query_peoplesoft_db` | Execute custom SQL queries against the local database |

## Project Structure

```
peoplesoft-mcp/
├── peoplesoft_server.py    # Main MCP server entry point
├── db.py                   # SQLite connection management
├── create_db.py            # Creates finstg.db and loads data/*.csv
├── db_structure.txt        # Schema definition source for create_db.py
├── data/                   # Static CSV data loaded into finstg.db
│   ├── project.csv
│   ├── ca_detail.csv
│   ├── ca_bill_plan.csv
│   └── proj_resource.csv
├── finstg.db               # Local SQLite database (generated)
├── tools/                  # Semantic tool modules
│   ├── introspection.py    # Schema discovery tools
│   ├── contracts.py        # Contracts/billing tools
│   └── billing.py          # Billing setup & cost-reimbursable analysis tools
├── tests/                  # Test suite
│   └── test_contracts.py
└── pyproject.toml          # Project configuration
```

## Running Tests

```bash
uv run pytest tests/ -v -s
```

## Example Queries

- "List all active projects for business unit UCD"
- "What contract lines and bill plans belong to project 25B1111?"
- "What's the bill plan status for contract FAKE4 line 2?"
- "What tables and columns are in finstg.db?"

## Development

### Adding New Tools

1. Create a new module in `tools/` or add to an existing module
2. Define async functions that use `db.execute_query()`
3. Add a `register_tools(mcp)` function
4. Import and register in `peoplesoft_server.py`

## License

MIT

## Changelog

### v0.3.0 (2026-07-15)
- Removed the legacy PeopleSoft HCM Oracle tool modules (`hr.py`, `payroll.py`, `benefits.py`, `performance.py`, `peopletools.py`), their docs, their gated/skipped tests, and the Cursor agent/skill configs built around them
- Removed the 4 MCP resources that served the now-deleted Oracle-schema documentation
- Repurposed the repo as a standalone worked example of MCP server construction (schema introspection + semantic tools + direct-SQL escape hatch) over a local SQLite database

### v0.2.x (2026-03-02 – earlier)
- Replaced Oracle (`oracledb`) backend with a local SQLite database (`finstg.db`)
- Rewrote schema introspection to use SQLite's own metadata (`sqlite_master`, `PRAGMA table_info`)
- Added `tools/contracts.py` with semantic tools for the local `PROJECT`/`CA_DETAIL`/`CA_BILL_PLAN` schema