Skip to main content
Glama
Loan Portfolio MCP Server

Loan Portfolio MCP Server

by fakfarrukh

Loan Portfolio MCP Server

CI License: MIT

A production-style Model Context Protocol server that lets an AI assistant such as Claude Desktop answer questions about a loan portfolio. It does this by calling safe, read-only, audited tools against a database. It never writes SQL itself.

All data is synthetic. A deterministic seed script generates 200 customers, 600 loans, about 5,200 payments and about 1,300 risk ratings. Names come from Faker and every e-mail address uses the reserved example.com domain. No real customer, company or employer data is used anywhere.

What this demonstrates: typed tool contracts that an LLM can use correctly, role-based PII masking enforced in the data layer, JWT/OAuth2 authentication, parameterized SQL only, an append-only audit trail, OpenTelemetry tracing, and 152 automated tests.


Contents


Related MCP server: lending-data-mcp-server

Try it in 2 minutes

You need Node.js 22 or 24 and Claude Desktop. You don't need a compiler or Docker.

1. Install, build and create the demo data.

git clone https://github.com/fakfarrukh/loan-portfolio-mcp.git
cd loan-portfolio-mcp
npm ci
npm run build
npm run seed:demo

seed:demo creates the database with a fixed date (31 October 2026). The answers below assume it.

2. Get the full path to the server. This prints it in quotes, ready to paste into JSON (on Windows the backslashes are already doubled):

node -p "JSON.stringify(require('path').resolve('dist/index.js'))"

3. Add the server to Claude Desktop. In Claude Desktop, open Settings → Developer → Edit Config (in newer versions: Settings → Desktop app → Extensions → Edit config). Paste this, and replace the placeholder path in args with the output of step 2. The placeholder will not work as written.

{
  "mcpServers": {
    "loan-portfolio": {
      "command": "node",
      "args": ["/full/path/to/loan-portfolio-mcp/dist/index.js"],
      "env": { "STDIO_ROLE": "analyst", "LOG_LEVEL": "warn", "OTEL_TRACES_EXPORTER": "none" }
    }
  }
}

4. Restart Claude Desktop and ask a question.

The config file lives at ~/Library/Application Support/Claude/claude_desktop_config.json on macOS and %APPDATA%\Claude\claude_desktop_config.json on Windows. The Microsoft Store version of Claude Desktop keeps it elsewhere, so open it with Edit config rather than by path. After editing, quit Claude Desktop from the system tray (closing the window is not enough) and reopen it. Use "STDIO_ROLE": "admin" to see real customer names instead of masked ones.

Five questions to try

The exact wording of Claude's answers will vary. The numbers should not.

  1. "Summarise the loan portfolio." Expect: 574 active loans, about $65.7M outstanding, a weighted average rate of 8.78%, and a delinquency rate of 5.75% (33 loans). Claude uses get_portfolio_summary.

  2. "Which region has the highest delinquency rate? Which has the lowest?" Expect: Southeast is highest at 9.35%, then Southwest at 8.33%. West is lowest at 3.45%. Claude uses get_portfolio_summary with a breakdown by region.

  3. "How has delinquency changed over the last 12 months?" Expect: it stays between 2% and 3.4% until July 2026. Then it rises every month, to 5.75% in October 2026. Claude uses get_delinquency_trend.

  4. "Show me the five largest defaulted loans, then explain what happened to the biggest one." Expect: a list headed by LN-000189, a West mortgage of about $473,000 that is 116 days past due. Customer names are masked, for example A*** B***. The borrower paid on time for ten months, then missed four payments in a row from July 2026. The risk grade went from C to D, then to E after an analyst review. Claude uses search_loans, then get_loan_details.

  5. "What exactly does 'delinquency rate' mean here?" Expect: loans 30 or more days past due, divided by active loans, and it includes defaulted loans. Claude uses explain_metric. The definition comes from the server, not from Claude's general knowledge.

For a longer run, pick the monthly_portfolio_review prompt in Claude Desktop. It produces a structured credit review that uses all five tools. For a screen-recorded walkthrough, see docs/demo-script.md.

What it does

Capability

Name

Purpose

Tool

get_portfolio_summary

Totals, weighted/simple average rate, delinquency and default rates. Filter by region or loan type, and break down by region, loan type or risk grade.

Tool

search_loans

Filtered, paginated loan search (status, region, type, grade, balance range, origination dates, days past due). Max 50 rows per call.

Tool

get_loan_details

One loan with its customer, risk-rating history and payment history.

Tool

get_delinquency_trend

Month-end delinquency rate for the last N months (max 36), recomputed from payment history.

Tool

explain_metric

Authoritative definition and formula for every metric, so the assistant doesn't guess.

Resource

portfolio://schema/data-dictionary

Tables, columns, enums, PII flags and metric formulas (Markdown).

Prompt

monthly_portfolio_review

A guided, multi-tool monthly credit review, for the whole portfolio or one region.

Each tool has a Zod input schema and a Zod output schema. Both are published to the client as JSON Schema, together with an LLM-oriented description that says when to use the tool and how to page through results. Every tool is annotated readOnlyHint: true.

Architecture

flowchart LR
    subgraph Clients
        CD["Claude Desktop<br/>(local)"]
        RC["Remote MCP client<br/>(agent, web app)"]
    end

    subgraph Server["loan-portfolio-mcp"]
        direction LR
        STDIO["stdio transport<br/>identity = configured local role"]
        HTTP["Streamable HTTP transport<br/>Express · body limit · stateless"]
        AUTH["Bearer JWT verification<br/>jose · JWKS or shared secret<br/>claim → role mapping"]
        MCP["MCP adapter<br/>tools · resource · prompt"]
        EXE["ToolExecutor pipeline<br/>1. identity  2. input size<br/>3. Zod validation  4. role check<br/>5. OTel span  6. timeout<br/>7. output allow-list  8. audit<br/>9. safe error mapping"]
        REPO["PortfolioRepository<br/>(interface)"]
        SQLITE["SqlitePortfolioRepository<br/>read-only · parameterized SQL<br/>PII masking by role"]
        AUD["AuditRepository<br/>append-only"]
    end

    IDP[("Identity provider<br/>Okta / Entra ID<br/>JWKS")]
    DB[("portfolio.db<br/>synthetic data")]
    ADB[("audit.db")]
    OTEL["OpenTelemetry<br/>console → OTLP collector"]

    CD -- "JSON-RPC over stdio" --> STDIO --> MCP
    RC -- "HTTPS + Authorization: Bearer" --> HTTP --> AUTH --> MCP
    AUTH -. "fetch & cache signing keys" .-> IDP
    MCP --> EXE --> REPO --> SQLITE --> DB
    EXE --> AUD --> ADB
    EXE -. spans .-> OTEL

What a single tool call goes through:

sequenceDiagram
    participant C as Client (LLM)
    participant T as Transport + Auth
    participant E as ToolExecutor
    participant R as Repository
    participant A as Audit
    C->>T: tools/call search_loans {region:"West", pageSize:20}
    T->>T: verify JWT (signature, iss, aud, exp) → Principal{sub, role}
    T->>E: execute(tool, args, principal)
    E->>E: size check → Zod parse → role allowed?
    E->>R: searchLoans(principal, validatedArgs)
    R->>R: static SQL + bound params, LIMIT ≤ 50
    R->>R: mask names unless role = admin
    R-->>E: rows
    E->>E: validate output (allow-list)
    E->>A: who, what, args, rowCount, durationMs, outcome
    E-->>C: structuredContent  (or {code, message, correlationId})

Resource reads and prompt reads use the same identity check, tracing and audit steps.

Other ways to run

Smoke test. This starts the built server over stdio, the way Claude Desktop does, and calls every tool, the resource and the prompt:

npm run smoke                 # as analyst
STDIO_ROLE=admin npm run smoke

Audit log. Show the latest audit records:

npm run audit:tail            # last 15 records; add -- --n=50 for more

HTTP mode (local).

cp .env.example .env          # set AUTH_JWT_SECRET to a random string of 32+ characters
npm run start:http            # http://127.0.0.1:3000/mcp  (.env is loaded automatically)
npm run token -- analyst      # mint a 1-hour dev token (or: admin)

Docker.

cp .env.example .env && docker compose up --build

The container runs as a non-root user with a read-only root filesystem and all Linux capabilities dropped. It seeds the database on first start and exposes a /healthz probe. Mint a token with npm run token -- analyst (it reads the same .env) and call http://127.0.0.1:3000/mcp.

npm run seed (without :demo) uses the end of last month as the as-of date. A .env file in the project root is loaded automatically; values set in the real environment, including the env block in the Claude Desktop config, always take precedence.

Security design

Risk

Control

Where

SQL injection

Every statement is a fixed string with bound parameters. Optional filters use (@p IS NULL OR col = @p). ORDER BY and GROUP BY columns are picked from constant allow-list maps. A test scans the source code and fails the build if any prepare() or exec() call interpolates a value.

src/db/sqlite/queries.ts, tests/security/sql-injection.test.ts

Damage if a query is ever compromised

The portfolio database is opened readonly with PRAGMA query_only = ON. Audit records go to a separate database.

portfolio-repository.ts

Unauthenticated access (HTTP)

Bearer JWT verified with jose: signature, iss, aud, exp and sub are all required. Algorithms are pinned to the key type: RS/PS/ES/EdDSA for JWKS, HS for a shared secret. This blocks alg:none and algorithm-confusion attacks. Tokens over 8 KB are rejected. Responses are a standard 401 with WWW-Authenticate, or 403 when the token carries no role.

src/auth/jwt.ts, src/transport/http.ts

Over-exposure of PII

There are two roles. Analysts get masked customer names and e-mails (J*** S***, j***@example.com) and admins see full values. Masking happens inside the repository, keyed on the caller's identity, so no tool argument can turn it off.

src/db/masking.ts

Data leaking through new fields

Output schemas are strict and act as an allow-list. A field the data layer did not declare, such as an accidental ssn, causes the call to fail instead of leaking.

src/tools/executor.ts

Abuse and resource exhaustion

Hard limits: at most 50 rows per page, 120 payments per loan, 36 trend months and 1,000 pages. Arguments are capped at 4 KB, HTTP bodies at 64 KB, and each tool call has a 5 s timeout. Every limit is enforced in the schema and again in the repository.

src/schemas, repository

Leaking internals in errors

Clients receive only a closed set of error codes (INVALID_INPUT, NOT_FOUND, FORBIDDEN, TIMEOUT, INTERNAL_ERROR, …) plus a correlationId. Stack traces, SQL, file paths and driver messages stay in the server logs.

src/errors.ts

No accountability

Every tool call, resource read and prompt read is audited, including rejected and failed ones. Each record holds the subject, role, auth method, kind (tool/resource/prompt), name, validated arguments (size-capped), outcome, error code, row count and duration. The audit table is append-only, enforced by database triggers. Auditing fails closed: if the audit write fails, the data is not returned.

src/db/sqlite/audit-repository.ts

Corrupting the stdio protocol

Logs and traces go to stderr only. The smoke test checks this.

src/logger.ts, src/telemetry.ts

Misconfiguration

All configuration is validated with Zod at startup, and the server refuses to start on invalid values. HTTP mode requires exactly one key source plus an issuer and audience. stdio defaults to the least-privileged role.

src/config.ts

Production authentication (OAuth2 / Okta)

Status: documented, not tested against a live tenant. The JWKS path is tested with a local key server and real RS256 tokens. The Okta settings below are written from Okta's documented token format.

This server is an OAuth 2.0 resource server. It never sees passwords; it only validates access tokens issued by your identity provider (IdP). Mapping it to Okta:

Okta setting

Server setting

Authorization server issuer, e.g. https://your-org.okta.com/oauth2/default

AUTH_ISSUER

Keys endpoint …/oauth2/default/v1/keys

AUTH_JWKS_URL (keys are cached and rotated automatically by kid)

Authorization server Audience, e.g. api://loan-portfolio-mcp

AUTH_AUDIENCE

Access-token claim groups, filtered with the regex ^Portfolio-

AUTH_ROLE_CLAIM=groups

Groups Portfolio-Admins and Portfolio-Analysts

AUTH_ADMIN_VALUES=Portfolio-Admins, AUTH_ANALYST_VALUES=Portfolio-Analysts

  • Interactive users sign in through an OAuth app (Authorization Code + PKCE) in the MCP client.

  • Service-to-service calls use Client Credentials, with a group or role assigned to the service app.

  • Discovery: setting PUBLIC_URL publishes RFC 9728 protected-resource metadata at /.well-known/oauth-protected-resource/.... 401 responses then point to it, so MCP clients that implement the MCP authorization spec can find the IdP on their own.

  • Microsoft Entra ID follows the same pattern: use app roles with AUTH_ROLE_CLAIM=roles and the JWKS at https://login.microsoftonline.com/<tenant>/discovery/v2.0/keys. Also untested against a live tenant.

  • Keycloak: use AUTH_ROLE_CLAIM=realm_access.roles. Dotted claim paths are supported.

  • Shared-secret mode (AUTH_JWT_SECRET, HS256) exists for local development and the Docker demo only.

In production, terminate TLS and apply rate limiting at a gateway or ingress in front of the service. The HTTP transport is stateless, so it scales horizontally without sticky sessions.

Observability

  • Tracing: each tool call creates an OpenTelemetry span, mcp.tool <name>, with these attributes: enduser.id, enduser.role, mcp.tool.outcome, mcp.tool.row_count and mcp.correlation_id. Resource and prompt reads create mcp.resource <uri> and mcp.prompt <name> spans. By default spans are printed to stderr as JSON lines. To send them to a collector, swap the exporter in src/telemetry.ts for @opentelemetry/exporter-trace-otlp-http; tool code doesn't change.

  • Logging: structured JSON via pino on stderr. Bearer tokens and secrets are redacted.

  • Correlation: the same correlationId appears in the client error, the log line, the span and the audit record.

Testing

npm test              # 152 tests, about 7 s
npm run check         # typecheck + lint + format check + tests (same steps as CI)
npm run test:coverage

Suite

Covers

tests/tools/*

Every tool: valid calls, invalid input (types, ranges, unknown keys, malformed dates and IDs), pagination limits, permission masking, audit records. Also end-to-end tests over the real MCP protocol (SDK client ↔ server), including the audit records for resource and prompt reads.

tests/tools/executor.test.ts

Unauthenticated calls, unknown tools, oversized input, role restrictions, no stack traces in errors, output allow-list, timeouts, client cancellation, fail-closed audit (tools and resources), tracing spans

tests/security/sql-injection.test.ts

Injection payloads at three layers: rejected by validation; inert as bound parameters when validation is bypassed (data unchanged, integrity check passes); a static scan for string-built SQL

tests/auth/*

JWT: valid, expired, wrong aud/iss/key, alg:none, algorithm confusion, oversized token, missing role, JWKS with RS256, Okta/Keycloak claim shapes. HTTP: 401/403/405/413, malformed JSON, analyst vs admin end to end, security headers

tests/db/*

Seed determinism and realism, status/days-past-due consistency, repository filters and sorting, trend values agreeing with the summary, the read-only connection, append-only audit, audit-store migration and concurrent startup

CI (.github/workflows/ci.yml) runs all of this on Node 22 and 24 on every push and pull request, then builds and smoke-tests the stdio server, audits production dependencies, and builds the Docker image and checks that it answers /healthz and returns 401 without a token.

Project layout

src/
  index.ts            entry point: picks the transport
  app.ts              composition root (the only place that names concrete implementations)
  server.ts           MCP adapter: tools, resource and prompt → executor and repository
  config.ts           Zod-validated environment configuration
  errors.ts           closed error-code set and safe error mapping
  logger.ts           pino → stderr, with redaction
  telemetry.ts        OpenTelemetry setup
  auth/               principal and roles, JWT verification (jose)
  db/                 repository interfaces, masking, seed, dates
    sqlite/           SQLite schema, static queries, portfolio and audit repositories
  schemas/            domain enums, metric registry, tool input/output contracts
  tools/              one file per tool + ToolExecutor (the guardrail pipeline)
  transport/          stdio and Streamable HTTP
  resources/          data dictionary
  prompts/            monthly portfolio review
tests/                Vitest suites (see above)
scripts/              smoke test, audit tail, dev token minting, Docker entrypoint
docs/                 demo script

See DECISIONS.md for why it is built this way.

How I would adapt this to your data

The demo is deliberately built so that the parts that change from client to client are isolated:

  1. Agree the questions and the metric definitions first. In a short workshop with risk, finance and the people who will use the assistant, we list the questions that matter and agree exactly how each metric is defined: delinquency basis, treatment of charge-offs, as-of conventions. Those definitions go into the metric registry (src/schemas/metrics.ts), so the assistant explains your definitions rather than textbook ones.

  2. Connect to your data through a read-only contract. I would write a new PortfolioRepository for your platform (PostgreSQL, SQL Server, Snowflake, etc.). It would query a small set of views owned by your data team, using a read-only database user. The tools, auth, auditing and tests stay as they are. Native controls such as row-level security or dynamic data masking can be layered underneath the application-level masking.

  3. Plug into your identity provider. Point AUTH_JWKS_URL, AUTH_ISSUER and AUTH_AUDIENCE at Okta or Entra ID, and map your existing groups to roles. Add roles if needed, for example regional analysts who only see their own region. That is one new rule in Permissions and one parameter in the repository's SQL.

  4. Classify PII with your data owners. Decide field by field what each role may see, and enforce it in the data layer. The output allow-list guarantees nothing extra slips through.

  5. Route audit and telemetry into your existing tools. Send audit events to your SIEM (Splunk, Sentinel) and traces to your observability platform over OTLP. Audit retention follows your policy.

  6. Test against a golden question set. Build a set of real business questions with known answers, and run them through the assistant on every change. This catches regressions in tool descriptions as well as in code.

  7. Deploy on your infrastructure. The container runs behind your gateway, which handles TLS and rate limiting. Data stays inside your network. Only tool results, limited to 50 rows per call and masked by role, reach the model.

Known limitations

  • Okta mapping: documented, not tested against a live tenant. JWT verification is tested with shared secrets and with a local JWKS server issuing RS256 tokens. The same applies to the Entra ID and Keycloak notes.

  • No rate limiting. This is the gateway's responsibility (API gateway, ingress or WAF) in front of the HTTP transport. Per-user limits could be added to the executor if a deployment has no gateway.

  • Timeouts cannot stop a running SQLite query. better-sqlite3 is synchronous, so the 5-second timeout bounds the response but cannot interrupt a query that is already running. Query cost is bounded by indexes, row limits and page ceilings instead. A networked driver (PostgreSQL, SQL Server) would add a server-side statement timeout; see DECISIONS.md.

  • Offset pagination. It is simple and LLM-friendly, but not stable while the underlying data is changing. Keyset or cursor pagination is the upgrade path for live data.

  • Listing tools, resources and prompts is not audited. Only reads that return data are: tool calls, resource reads and prompt reads.

  • Synthetic data only, with a deliberately small and simplified credit model: no charge-offs, prepayment curves or collateral.

  • Manually tested in Claude Desktop on Windows only. Automated end-to-end tests use the official MCP SDK client over stdio, which speaks the same protocol. macOS has not been tried by hand.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables natural language portfolio monitoring, risk analysis, and compliance checking with tools for real-time portfolio status, risk metrics, anomaly detection, SEC filings search, market KPIs, and compliance validation.
      MIT
    • F
      license
      Not graded
      quality
      D
      maintenance
      Exposes a governed lending portfolio (loans, customers, risk-tier history) to any MCP-compatible AI client via read-only tools, schema resources, and analysis prompts, wrapping an existing API gateway instead of connecting directly to the database.
      -
    • F
      license
      Not graded
      quality
      C
      maintenance
      Enables a Digital Banking Assistant to calculate debt-to-income ratios, generate amortization schedules, and flag explainable, rules-based risk factors during conversations.
      -
    • F
      license
      Not graded
      quality
      C
      maintenance
      Enables any LLM to query financial data through predefined, read-only tools, such as retrieving credit scores, preapproved limits, risk profiles, and consolidated customer profiles from a mapped database.
      3
      -