# FIS IntelliMatch MCP Server for MS SQL

A **Model Context Protocol (MCP)** server that connects AI assistants directly to your **FIS IntelliMatch** SQL Server database for day-to-day BAU (Business As Usual) operations.

> **Security first:** All queries run as parameterized statements. Only `SELECT` is permitted — write operations, DDL, and SQL comments are blocked at the guard layer. Zero npm vulnerabilities.

---

## Table of Contents

- [What It Does](#what-it-does)
- [Prerequisites](#prerequisites)
- [Installation](#installation)
- [Configuration](#configuration)
- [Build & Run](#build--run)
- [VS Code Integration — Cline Plugin](#vs-code-integration--cline-plugin)
- [VS Code Integration — Roo Code Plugin](#vs-code-integration--roo-code-plugin)
- [Claude Desktop Integration](#claude-desktop-integration)
- [Available Tools (19)](#available-tools-19)
- [Table Name Customisation](#table-name-customisation)
- [Troubleshooting](#troubleshooting)
- [BAU Use Cases & Example Prompts](#bau-use-cases--example-prompts)
- [Security Notes](#security-notes)

---

## What It Does

Gives your AI assistant (via Cline, Roo Code, or Claude Desktop) direct, read-only access to IntelliMatch data so you can ask questions like:

- *"Show me all aged breaks older than 5 days in USD"*
- *"What is the match rate for today's recon?"*
- *"Did all overnight jobs complete successfully?"*
- *"Find the item with reference TXN-20240613-001"*
- *"Generate a daily BAU summary for today"*

---

## Prerequisites

| Requirement | Minimum Version | Notes |
|---|---|---|
| Node.js | 20.x LTS | [nodejs.org](https://nodejs.org) |
| npm | 10.x | Bundled with Node 20 |
| TypeScript | 5.x | Installed as dev dependency |
| SQL Server | 2016+ | IntelliMatch database |
| VS Code | 1.85+ | For Cline / Roo Code |

Check your Node version:

```bash
node --version   # should print v20.x.x or higher
npm --version    # should print 10.x.x or higher
```

---

## Installation

### 1. Clone or copy the project

```bash
# If you received this as a zip, extract to your preferred location, e.g.:
C:\NewInitiatives\mcp\mcp-mssql\
```

### 2. Install dependencies

Open a terminal in the project folder and run:

```bash
npm install
```

Expected output:
```
added 167 packages, and audited 168 packages
found 0 vulnerabilities
```

---

## Configuration

### 1. Create your `.env` file

Copy the example file and fill in your credentials:

```bash
# Windows
copy .env.example .env

# PowerShell
Copy-Item .env.example .env
```

Open `.env` and set your values:

```env
# ── SQL Server Connection ──────────────────────────────────────────────────────
MSSQL_SERVER=your-sql-server-hostname
MSSQL_DATABASE=IntelliMatch
MSSQL_USER=im_readonly_user
MSSQL_PASSWORD=your_secure_password
MSSQL_PORT=1433

# Encryption (recommended: true for production)
MSSQL_ENCRYPT=true

# Set true only for self-signed / dev certificates
MSSQL_TRUST_CERT=false

# ── Table Name Overrides (only change if your schema differs) ──────────────────
IM_TBL_ITEM=dbo.ITEM
IM_TBL_MATCH=dbo.MATCH_RESULT
IM_TBL_EXCEPTION=dbo.EXCEPTION
IM_TBL_RECON_GROUP=dbo.RECON_GROUP
IM_TBL_JOB_LOG=dbo.JOB_LOG
IM_TBL_ERROR_LOG=dbo.ERROR_LOG
IM_TBL_DATA_LOAD=dbo.DATA_LOAD_LOG
IM_TBL_AUDIT_LOG=dbo.AUDIT_LOG

# ── Query Safety ───────────────────────────────────────────────────────────────
MAX_ROWS=500
```

> **Tip:** The `.env` file is never committed. Keep credentials out of source control.

### 2. Verify your SQL Server connectivity

```bash
# Quick connectivity test using sqlcmd (if installed)
sqlcmd -S your-server -d IntelliMatch -U im_readonly_user -Q "SELECT @@VERSION"
```

### 3. Recommended SQL Server permissions

Grant your service account the minimum required permissions:

```sql
-- Run as DBA on the IntelliMatch database
CREATE LOGIN im_readonly_user WITH PASSWORD = 'your_secure_password';
USE IntelliMatch;
CREATE USER im_readonly_user FOR LOGIN im_readonly_user;

-- Grant read-only access to the IntelliMatch schema
ALTER ROLE db_datareader ADD MEMBER im_readonly_user;

-- Optional: allow viewing execution plans
GRANT SHOWPLAN TO im_readonly_user;
```

---

## Build & Run

### Build (compile TypeScript → JavaScript)

```bash
npm run build
```

Output is written to the `dist/` folder. Compiled once; run repeatedly without rebuilding unless source changes.

### Test the server manually

```bash
npm start
```

The server starts and listens on **stdin/stdout** (MCP protocol). You will see:

```
FIS IntelliMatch MCP server started (19 tools available)
```

Press `Ctrl+C` to stop.

### Development mode (no build step required)

```bash
npm run dev
```

Uses `tsx` to run TypeScript directly — useful during development.

---

## VS Code Integration — Cline Plugin

[Cline](https://marketplace.visualstudio.com/items?itemName=saoudrizwan.claude-dev) is an AI coding assistant for VS Code that supports MCP servers.

### Step 1 — Install Cline

1. Open VS Code
2. Press `Ctrl+Shift+X` to open Extensions
3. Search for **Cline** (publisher: `saoudrizwan`)
4. Click **Install**

### Step 2 — Open Cline MCP Settings

**Option A — Via the Cline UI:**

1. Click the Cline icon in the VS Code sidebar
2. Click the **MCP Servers** icon (plug icon) in the Cline panel
3. Click **Edit MCP Settings**

**Option B — Edit the settings file directly:**

Open this file in VS Code:

```
%APPDATA%\Code\User\globalStorage\saoudrizwan.claude-dev\settings\cline_mcp_settings.json
```

PowerShell shortcut:
```powershell
code "$env:APPDATA\Code\User\globalStorage\saoudrizwan.claude-dev\settings\cline_mcp_settings.json"
```

### Step 3 — Add the IntelliMatch MCP Server

Add the following entry inside the `mcpServers` object. Replace the path and credentials:

```json
{
  "mcpServers": {
    "intellimatch-mssql": {
      "command": "node",
      "args": ["C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js"],
      "env": {
        "MSSQL_SERVER": "your-sql-server-hostname",
        "MSSQL_DATABASE": "IntelliMatch",
        "MSSQL_USER": "im_readonly_user",
        "MSSQL_PASSWORD": "your_secure_password",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false",
        "MAX_ROWS": "500"
      },
      "disabled": false,
      "alwaysAllow": []
    }
  }
}
```

> **Note:** Use double backslashes `\\` in JSON path strings on Windows.

### Step 4 — Verify in Cline

1. Reload VS Code (`Ctrl+Shift+P` → **Developer: Reload Window**)
2. Open Cline panel → MCP Servers
3. You should see **intellimatch-mssql** listed with a green indicator
4. Click the server name to see all 19 available tools

### Step 5 — Test in Cline chat

In the Cline chat input, type:

```
Use the im_test_connection tool to check the IntelliMatch database connection
```

---

## VS Code Integration — Roo Code Plugin

[Roo Code](https://marketplace.visualstudio.com/items?itemName=RooVeterinaryInc.roo-cline) (formerly Roo-Cline) is a Cline fork with additional features and also supports MCP servers.

### Step 1 — Install Roo Code

1. Open VS Code
2. Press `Ctrl+Shift+X`
3. Search for **Roo Code** (publisher: `RooVeterinaryInc`)
4. Click **Install**

### Step 2 — Open Roo Code MCP Settings

**Option A — Via Roo Code UI:**

1. Click the Roo Code icon in the VS Code sidebar
2. Click the **Settings** (gear) icon
3. Select **MCP Servers** → **Edit MCP Settings**

**Option B — Edit the settings file directly:**

```
%APPDATA%\Code\User\globalStorage\rooveterinaryinc.roo-cline\settings\cline_mcp_settings.json
```

PowerShell shortcut:
```powershell
code "$env:APPDATA\Code\User\globalStorage\rooveterinaryinc.roo-cline\settings\cline_mcp_settings.json"
```

### Step 3 — Add the IntelliMatch MCP Server

The JSON format is identical to Cline:

```json
{
  "mcpServers": {
    "intellimatch-mssql": {
      "command": "node",
      "args": ["C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js"],
      "env": {
        "MSSQL_SERVER": "your-sql-server-hostname",
        "MSSQL_DATABASE": "IntelliMatch",
        "MSSQL_USER": "im_readonly_user",
        "MSSQL_PASSWORD": "your_secure_password",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false",
        "MAX_ROWS": "500"
      },
      "disabled": false,
      "alwaysAllow": []
    }
  }
}
```

### Step 4 — Configure Roo Code Modes (Optional)

Roo Code supports custom AI modes. You can create an **IntelliMatch BAU** mode:

1. Open Roo Code settings → **Modes**
2. Click **Add Custom Mode**
3. Fill in:
   - **Name:** `IntelliMatch BAU`
   - **System Prompt:** 
     ```
     You are an IntelliMatch reconciliation analyst assistant. 
     You have access to the IntelliMatch MSSQL database via MCP tools.
     Always use parameterised tool calls. Never guess data — query it.
     For break investigation, start with im_get_break_details, 
     then cross-reference with im_search_items.
     ```
4. Enable the **intellimatch-mssql** MCP server for this mode

### Step 5 — Verify in Roo Code

1. Reload VS Code
2. Open Roo Code panel → check MCP server status shows green
3. Test with: *"Run im_test_connection to check the IntelliMatch DB"*

---

## Claude Desktop Integration

If you use Claude Desktop alongside VS Code:

Edit `%APPDATA%\Claude\claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "intellimatch-mssql": {
      "command": "node",
      "args": ["C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js"],
      "env": {
        "MSSQL_SERVER": "your-sql-server-hostname",
        "MSSQL_DATABASE": "IntelliMatch",
        "MSSQL_USER": "im_readonly_user",
        "MSSQL_PASSWORD": "your_secure_password",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false"
      }
    }
  }
}
```

Restart Claude Desktop after saving.

---

## Available Tools (19)

### Connection & Discovery

| Tool | Description |
|---|---|
| `im_test_connection` | Test DB connectivity; returns server name, DB name, SQL version |
| `im_get_configuration` | Show current config (table mappings, row limits — no password) |
| `im_list_tables` | List all tables in a schema (default: dbo) |
| `im_describe_table` | Show columns, types, and nullability of any table |

### Reconciliation

| Tool | Description |
|---|---|
| `im_get_recon_groups` | List all recon groups with type and active status |
| `im_get_recon_summary` | Match/unmatched/exception counts with match % for a date |
| `im_get_match_statistics` | Match rates and break amounts by currency for a date range |

### Breaks & Exceptions

| Tool | Description |
|---|---|
| `im_get_breaks` | Get open breaks filtered by date, entity, currency, status |
| `im_get_aged_breaks` | Breaks older than N days — for escalation reviews |
| `im_get_break_details` | Full details of a specific break by exception ID |
| `im_break_aging_report` | Break count by age bucket (0-2, 3-5, 6-10, 11-30, 30+ days) |

### Items & Transactions

| Tool | Description |
|---|---|
| `im_search_items` | Search items by reference, date, entity, currency, or status |
| `im_get_item_details` | Full item details including match result and linked break |
| `im_get_unmatched_items` | All unmatched items for a date with optional filters |
| `im_get_daily_summary` | Complete BAU daily summary: recon status + open breaks overview |

### Job Monitoring

| Tool | Description |
|---|---|
| `im_get_job_status` | Batch job status for a date — verify overnight runs completed |
| `im_get_load_status` | Data load/file ingestion status with record counts |
| `im_get_error_log` | Recent errors/warnings from the error log |
| `im_get_pending_processes` | Jobs currently running or waiting |

### Ad-hoc Query

| Tool | Description |
|---|---|
| `im_execute_select` | Run any custom SELECT query with row cap and SELECT-only guard |

---

## Table Name Customisation

IntelliMatch deployments vary. If your schema uses different table names, override them in `.env` (or in the `env` block of your MCP config):

```env
# Example: if your items table is called dbo.IM_ITEMS
IM_TBL_ITEM=dbo.IM_ITEMS

# Example: if breaks are in a different schema
IM_TBL_EXCEPTION=reconciliation.BREAKS
```

Use `im_list_tables` to discover what tables exist in your database, then `im_describe_table` to inspect columns before configuring overrides.

---

## Troubleshooting

### MCP server not appearing in Cline / Roo Code

- Confirm the `dist/index.js` path exists: run `npm run build` first
- Use forward-slash paths in JSON on Windows: `C:/NewInitiatives/mcp/mcp-mssql/dist/index.js`
  or escape backslashes: `C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js`
- Reload VS Code after editing settings JSON

### Connection failed / timeout

```
Error executing "im_test_connection": ConnectionError: Failed to connect
```

- Verify the SQL Server is reachable: `Test-NetConnection your-server -Port 1433`
- Check firewall rules allow port 1433 from your machine
- Confirm `MSSQL_USER` and `MSSQL_PASSWORD` are correct
- If using Windows Auth (no user/password), remove `MSSQL_USER`/`MSSQL_PASSWORD` from env — `mssql` will use the process identity

### Self-signed certificate error

```
Error: The certificate chain was issued by an authority that is not trusted
```

Set in your env:
```env
MSSQL_TRUST_CERT=true
```

### Table not found / invalid object name

```
Invalid object name 'dbo.ITEM'
```

Your IntelliMatch deployment uses different table names. Run:
```
im_list_tables          → discover tables in dbo schema
im_describe_table       → verify column names
```
Then set the correct names via `IM_TBL_*` environment variables.

### Query returns no rows but data exists

- Check `VALUE_DATE` format — must be `YYYY-MM-DD`
- Verify the date column matches your IntelliMatch schema (some use `SETTLE_DATE` or `TXN_DATE`)
- Use `im_execute_select` to run a direct `SELECT TOP 5 * FROM dbo.ITEM` to inspect actual data

### Node version errors

```
SyntaxError: Cannot use import statement
```

Upgrade Node.js to v20 LTS or later. The server uses ES modules which require Node 20+.

---

## Project Structure

```
mcp-mssql/
├── src/
│   ├── index.ts              MCP server entry point
│   ├── config.ts             DB config + table name resolution
│   ├── database.ts           Connection pool singleton
│   ├── guard.ts              SQL safety: SELECT-only + input sanitisation
│   └── tools/
│       ├── index.ts          Tool registry + dispatcher
│       ├── connection.ts     Connection & schema discovery tools
│       ├── recon.ts          Reconciliation tools
│       ├── breaks.ts         Break & exception tools
│       ├── items.ts          Item & transaction tools
│       ├── jobs.ts           Job monitoring tools
│       └── query.ts          Ad-hoc SELECT tool
├── dist/                     Compiled JavaScript (generated by npm run build)
├── .env.example              Environment variable template
├── .env                      Your local config (never commit this)
├── package.json
├── tsconfig.json
└── README.md
```

---

## BAU Use Cases & Example Prompts

Real-world scenarios and the prompts you can type directly into Cline or Roo Code chat.

---

### Connecting to the Database

Before anything else, verify the connection is healthy:

**Prompt:**
```
Test the IntelliMatch database connection and show me the server version.
```
*Calls `im_test_connection` — confirms SQL Server is reachable and returns the database name and version.*

**Prompt:**
```
Show me the current MCP configuration — what tables is it using and what is the row limit?
```
*Calls `im_get_configuration` — displays all table name mappings and settings without exposing the password.*

---

### Discover the Schema (First-time Setup)

If you are unsure whether the default table names match your IntelliMatch deployment:

**Prompt:**
```
List all tables in the dbo schema so I can see what IntelliMatch tables exist.
```
*Calls `im_list_tables` — shows every table in the dbo schema.*

**Prompt:**
```
Describe the ITEM table so I can see its column names and data types.
```
*Calls `im_describe_table` with `table_name: "ITEM"` — shows all columns, types, and nullability.*

**Prompt:**
```
Describe the EXCEPTION table and tell me which columns hold the break amount and age.
```
*Helps identify the right column names before customising your `.env` table overrides.*

---

### Morning BAU Checks

The most common start-of-day workflow:

**Prompt:**
```
Give me a full daily BAU summary for today including recon status and open breaks.
```
*Calls `im_get_daily_summary` — one-shot morning health check covering match rates and break overview.*

**Prompt:**
```
Did all overnight batch jobs complete successfully? Show me today's job status.
```
*Calls `im_get_job_status` for today — highlights any FAILED or still-RUNNING jobs.*

**Prompt:**
```
Check if all data loads completed for today. Flag any that failed or have errors.
```
*Calls `im_get_load_status` — shows records received vs loaded and highlights failures.*

**Prompt:**
```
Show me any ERROR-level log entries from today's processing.
```
*Calls `im_get_error_log` with severity ERROR and today's date.*

**Prompt:**
```
Are there any jobs still running or waiting right now?
```
*Calls `im_get_pending_processes` — shows live job status.*

---

### Reconciliation Status

**Prompt:**
```
What is today's match rate across all recon groups? Show matched vs unmatched counts.
```
*Calls `im_get_recon_summary` for today's date.*

**Prompt:**
```
Show me the match statistics for the past week broken down by currency, 
including the total break amount for each currency.
```
*Calls `im_get_match_statistics` with a 7-day date range.*

**Prompt:**
```
List all active reconciliation groups and their types.
```
*Calls `im_get_recon_groups` with `active_only: true`.*

**Prompt:**
```
What is the match rate for the NOSTRO recon group for today?
```
*Calls `im_get_recon_summary` filtered to the NOSTRO recon group.*

---

### Break & Exception Investigation

**Prompt:**
```
Show me all open breaks in USD for today sorted by largest amount first.
```
*Calls `im_get_breaks` with `currency: "USD"` and today's date.*

**Prompt:**
```
Find all open breaks older than 5 days — I need to escalate the aged items.
```
*Calls `im_get_aged_breaks` with `age_days: 5`.*

**Prompt:**
```
Show me the break aging report — how many breaks are in each age bucket 
and what is the total break amount?
```
*Calls `im_break_aging_report` — gives a bucket summary (0-2, 3-5, 6-10, 11-30, 30+ days).*

**Prompt:**
```
Get me the full details of exception EXC-2024-00123 including both sides of the break.
```
*Calls `im_get_break_details` with `exception_id: "EXC-2024-00123"`.*

**Prompt:**
```
I need to review all open GBP breaks for the entity "LONDON-DESK" 
older than 3 days — can you pull those up?
```
*Calls `im_get_aged_breaks` with `age_days: 3`, `currency: "GBP"`, `entity: "LONDON-DESK"`.*

---

### Item & Transaction Investigation

**Prompt:**
```
Find the item with reference TXN-20240613-4521 and show me its full details 
including whether it matched.
```
*Calls `im_search_items` with `item_ref: "TXN-20240613-4521"`, then `im_get_item_details` for the result.*

**Prompt:**
```
Show me all unmatched items for today in EUR — I want to investigate why they didn't match.
```
*Calls `im_get_unmatched_items` with `currency: "EUR"` and today's date.*

**Prompt:**
```
Search for all EXCEPTION items from the PRIME-BROKER recon group for 2024-06-12.
```
*Calls `im_search_items` with `match_status: "EXCEPTION"`, `recon_group: "PRIME-BROKER"`, `value_date: "2024-06-12"`.*

**Prompt:**
```
Item ITEM-98765 is showing as unmatched. Get its full details including 
any linked match or exception.
```
*Calls `im_get_item_details` with `item_id: "ITEM-98765"` — shows match result and linked exception if any.*

---

### Ad-hoc SQL Investigation

When the built-in tools don't cover your specific query, use `im_execute_select`:

**Prompt:**
```
Run this query for me:
SELECT VALUE_DATE, CURRENCY, COUNT(*) AS item_count, SUM(AMOUNT) AS total_amount
FROM dbo.ITEM
WHERE VALUE_DATE >= '2024-06-01'
GROUP BY VALUE_DATE, CURRENCY
ORDER BY VALUE_DATE DESC, total_amount DESC
```
*Calls `im_execute_select` — executes any SELECT safely with row capping.*

**Prompt:**
```
Query the top 20 largest unmatched items by amount across all currencies for this month.
```
*Claude will write the SELECT and call `im_execute_select` on your behalf.*

**Prompt:**
```
Show me items from the SWIFT source system that arrived today but have no match yet, 
ordered by amount descending. Max 50 rows.
```
*Claude constructs and executes a targeted SELECT query.*

---

### Multi-step Investigation Workflow

Cline and Roo Code can chain multiple tool calls in a single conversation:

**Prompt:**
```
I need to investigate today's reconciliation health. 
Please:
1. Check the connection is live
2. Show today's recon summary
3. List any failed jobs
4. Pull the top 10 aged breaks over 3 days
5. Give me your assessment of what needs attention today
```
*The AI calls `im_test_connection` → `im_get_recon_summary` → `im_get_job_status` (filtered FAILED) → `im_get_aged_breaks` and then summarises the findings.*

**Prompt:**
```
A trader is querying an item with reference "BOND-EUR-20240613-0042". 
Find it, show its match status, and if it has a break get the full break details.
```
*Multi-step: `im_search_items` → `im_get_item_details` → `im_get_break_details` (if needed).*

---

## Security Notes

- **Read-only by design:** `guard.ts` blocks all write keywords before any query executes
- **No raw string SQL:** every user-supplied value goes through a parameterised `request.input()` call
- **Row cap:** all tools enforce a `TOP N` limit (default 500, max 2000) to prevent large data dumps
- **No vulnerable packages:** `npm audit` reports 0 vulnerabilities at install time
- **Credentials in env only:** the `dist/` output contains no credentials; they are injected at runtime via environment variables