# Finance MCP Server

An [MCP](https://modelcontextprotocol.io) server that lets an LLM such as
Claude explore and query a synthetic financial SQLite database through two
controlled, read-only tools — schema discovery and validated SQL execution.

> **Note:** All financial/customer information in this repository is
> synthetic data generated for demonstration purposes and does not
> represent real customers or financial records.

## What is this?

A small, self-contained example of connecting an LLM to a real relational
database via MCP: the model can inspect the schema on its own, then write
and run SQL to answer natural-language questions like *"which branch has
the highest outstanding loan amount?"* — without the database ever being at
risk of a mutation, an injection, or a runaway result set.

## Features

- MCP server (`server.py`) built on the official `mcp` Python SDK
- SQLite database with a normalized, foreign-key-enforced schema
- Dynamic schema discovery (tables, columns, types, primary/foreign keys)
- Read-only SQL querying with multi-layer validation
- Relational financial dataset: branches → customers → loans → payments
- Synthetic data generation with internally consistent numbers
- Query resource limits (row count and output size)
- Clean, traceback-free error handling
- Example natural-language queries with real, executed output

## Architecture

```text
User
  │
  ▼
Claude / MCP Client
  │
  │ MCP
  ▼
Finance MCP Server
  │
  ├── Schema Discovery
  ├── Query Validation
  ├── Read-only Query Execution
  │
  ▼
SQLite Database
  │
  ├── customers
  ├── loans
  ├── payments
  └── branches
```

The client asks the server what tools are available, calls
`get_database_schema()` to learn the tables and relationships, then calls
`execute_query(sql)` with a SELECT statement to answer the user's question.
Full details in [`docs/architecture.md`](docs/architecture.md).

## Database Schema

```mermaid
erDiagram
    BRANCHES ||--o{ CUSTOMERS : serves
    CUSTOMERS ||--o{ LOANS : has
    LOANS ||--o{ PAYMENTS : receives
```

- **branches** — bank branches
- **customers** — each attached to a home branch
- **loans** — 1-4 loans per customer, with derived `outstanding_amount` and `status`
- **payments** — 1-10 payments per loan, chronologically consistent with the loan

Full column-level documentation: [`docs/database-schema.md`](docs/database-schema.md).

## Example natural-language queries

Once connected, you can ask Claude things like:

- "Show me the top 10 customers by annual income."
- "Which customers have overdue loans?"
- "What is the total outstanding loan amount?"
- "Which loan types have the highest average principal?"
- "Which branch has the highest total outstanding loan amount?"
- "Show customers who have both high income and overdue loans."

Claude answers each of these by calling `get_database_schema()` once, then
writing and running the appropriate `SELECT`/`JOIN` query through
`execute_query()`. Real output for each example (executed against the
shipped database) is in [`examples/sample-queries.md`](examples/sample-queries.md).

## Installation

```bash
git clone https://github.com/DanieloTony/finance-mcp-server.git
cd finance-mcp-server
pip install -r requirements.txt
```

### Regenerate the demo database (optional)

A `finance.db` is already included, but you can regenerate it (with a fresh
random seed's worth of consistent data) at any time:

```bash
python generate_database.py
```

### Run the server directly

```bash
python server.py
```

This starts the MCP server on stdio, which is how an MCP client such as
Claude Desktop launches it — you normally won't run this by hand except to
confirm it starts without errors.

### Run the tests

```bash
python -m pytest test.py -v
```

## Claude Desktop / MCP client configuration

Add the server to your MCP client's configuration, replacing the path with
your own local clone of this repository:

```json
{
  "mcpServers": {
    "finance-database": {
      "command": "python",
      "args": ["/absolute/path/to/finance-mcp-server/server.py"]
    }
  }
}
```

Replace `/absolute/path/to/finance-mcp-server` with wherever you cloned this
repository — do not hard-code another machine's path or username.

## Security considerations

- The SQLite database is opened in **read-only URI mode**
  (`file:...?mode=ro`), plus `PRAGMA query_only = ON`, so writes are refused
  at the database layer regardless of what SQL is submitted.
- SQL is validated before execution: only a single `SELECT`/`WITH` statement
  is allowed; `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `CREATE`,
  `REPLACE`, `ATTACH`, `PRAGMA` and similar mutating keywords are rejected,
  as are multiple statements in one call.
- Query results are capped (row count and output size) to protect against
  an unintentionally huge query.
- All demo data is synthetic (generated with Faker); no real personal or
  financial information is used anywhere in this project.
- **Limitations, stated plainly:** the SQL validator is a keyword/shape
  check, not a full SQL parser — it is a defense-in-depth layer on top of
  the read-only connection, not a standalone guarantee. This project is
  intended for local/demo use and is **not** a production banking system or
  a hardened database proxy.

## Project structure

```text
finance-mcp-server/
│
├── server.py               # MCP server: schema + query tools
├── generate_database.py    # Synthetic data generator
├── test.py                 # Test suite (database, schema, query tools)
├── finance.db              # Shipped demo database (synthetic data)
│
├── requirements.txt
├── README.md
├── LICENSE
├── .gitignore
│
├── docs/
│   ├── architecture.md
│   └── database-schema.md
│
└── examples/
    └── sample-queries.md
```

## License

MIT — see [LICENSE](LICENSE).