# bank-postgres-mcp

HTTP MCP server that reads from and writes to a **PostgreSQL** database (`bankdb`).
Deployed and accessible at: `https://armored-backspace-angles.ngrok-free.dev/mcp`

## Endpoints

| Path | Method | Purpose |
|---|---|---|
| `/mcp` | POST | MCP Streamable HTTP endpoint |
| `/health` | GET | Liveness check |

## Tools

### Read tools (query `bankdb` directly)

| Tool | Description |
|---|---|
| `list_customers` | List all customers in the database |
| `get_customer_by_id` | Get customer profile + all accounts by customer_id |
| `get_customer_by_account` | Look up customer + account by account number (e.g. `DB5194542004`) |
| `get_transactions` | Get recent transactions by account_id or account_number |

### Write tools (upsert into MCP dump tables)

| Tool | Action |
|---|---|
| `dump_customer_data` | Upsert a fetched customer record into `customers` |
| `dump_created_customer` | Insert a newly created customer into `customers` |
| `dump_bank_branch` | Upsert a bank branch record into `bank_branches` |

Tables are **auto-created** on first use.

---

## Setup

### 1. Prerequisites
- Node.js ≥ 18
- PostgreSQL running locally

### 2. Install & build

```bash
cd bank-postgres-mcp
npm install
npm run build
```

### 3. Configure environment

```bash
cp env.example .env
# .env is gitignored — edit credentials there
```

Provided credentials:

```
DATABASE_URL=postgresql://bankuser:bankpassword@localhost:5432/bankdb
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=bankdb
POSTGRES_USER=bankuser
POSTGRES_PASSWORD=bankpassword
```

### 4. Start the server

```bash
# Export env vars, then start
export DATABASE_URL=postgresql://bankuser:bankpassword@localhost:5432/bankdb
node build/index.js

# Or with all vars:
export POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_DB=bankdb \
       POSTGRES_USER=bankuser POSTGRES_PASSWORD=bankpassword
node build/index.js
```

Server starts on **http://localhost:3100** by default. Override with `PORT=XXXX`.

### 5. Verify

```bash
curl http://localhost:3100/health
# → {"status":"ok","server":"bank-postgres-mcp","version":"1.0.0"}
```

---

## Registering in watsonx Orchestrate

In Orchestrate, add a new **MCP Server** tool integration:

| Field | Value |
|---|---|
| Transport | Streamable HTTP |
| URL | `http://localhost:3100/mcp` |

> If Orchestrate is running remotely (not on your laptop), expose the server with a tunnel:
> ```bash
> npx localtunnel --port 3100
> # Use the returned https URL instead of localhost
> ```

---

## Registering in Bob (`.bob/mcp.json`)

```json
{
  "mcpServers": {
    "bank-postgres-mcp": {
      "url": "http://localhost:3100/mcp"
    }
  }
}
```

---

## Database schema

### `customers`

| Column | Type | Notes |
|---|---|---|
| `customer_id` | TEXT PK | |
| `full_name` | TEXT | |
| `date_of_birth` | TEXT | |
| `phone` | TEXT | |
| `email` | TEXT | |
| `pan` | TEXT | PII |
| `aadhaar` | TEXT | PII |
| `account_number` | TEXT | PII |
| `ifsc` | TEXT | |
| `address_*` | TEXT | line1, line2, city, state, postal_code, country |
| `account_type` | TEXT | |
| `kyc_status` | TEXT | |
| `available_balance` | NUMERIC | |
| `credit_score` | INT | |
| `nominee` | TEXT | |
| `source` | TEXT | `fetch` or `create` |
| `inserted_at` | TIMESTAMPTZ | auto |
| `updated_at` | TIMESTAMPTZ | auto |

### `bank_branches`

| Column | Type | Notes |
|---|---|---|
| `ifsc` | TEXT PK | |
| `branch_name` | TEXT | |
| `bank_name` | TEXT | |
| `city` | TEXT | |
| `state` | TEXT | |
| `services` | TEXT[] | |
| `inserted_at` | TIMESTAMPTZ | auto |
| `updated_at` | TIMESTAMPTZ | auto |