Building an MCP server for finance: read-only AI access to your accounting data

create role mcp_finance login password '...';
grant usage on schema reporting to mcp_finance;
grant select on all tables in schema reporting to mcp_finance;
alter default privileges in schema reporting grant select on tables to mcp_finance;

-- belt and braces: every session starts read-only
alter role mcp_finance set default_transaction_read_only = on;
alter role mcp_finance set statement_timeout = '10s';

mkdir finance-mcp && cd finance-mcp
npm init -y
npm install @modelcontextprotocol/sdk@1 zod pg express
npm install -D typescript @types/node @types/pg @types/express tsx

import { McpServer } from '@modelcontextprotocol/sdk/server/mcp.js';
import { z } from 'zod';
import { Pool, types } from 'pg';
import { requireScope, audit } from './guards.js';

// Return SQL dates as plain 'YYYY-MM-DD' strings instead of shifting them into the server's time zone.
types.setTypeParser(1082, value => value);

const db = new Pool({ connectionString: process.env.REPORTING_DATABASE_URL });

// A fresh server per request keeps stateless HTTP requests fully isolated.
export function createServer() {
  const server = new McpServer({ name: 'finance', version: '1.0.0' });

  server.registerTool(
    'get_overdue_invoices',
    {
      title: 'Overdue invoices',
      description:
        'Unpaid customer invoices more than N days past their due date, oldest first. ' +
        'Amounts are in the invoice currency. Use this for accounts receivable questions.',
      inputSchema: {
        minDaysOverdue: z.number().int().min(1).max(365).default(30),
        customer: z.string().max(100).optional()
      },
      // Tell clients this tool only reads: no confirmation prompts, safe to retry.
      annotations: { readOnlyHint: true, destructiveHint: false, idempotentHint: true, openWorldHint: false }
    },
    async ({ minDaysOverdue, customer }, extra) => {
      requireScope(extra, 'finance:ar:read');

      const { rows } = await db.query(
        `select i.invoice_number, c.name as customer, i.currency, i.amount_due,
                i.due_date, (current_date - i.due_date) as days_overdue
           from reporting.invoices i
           join reporting.customers c on c.id = i.customer_id
          where i.status = 'AUTHORISED'
            and i.amount_due > 0
            and i.due_date < current_date - $1::int
            and ($2::text is null or c.name ilike '%' || $2 || '%')
          order by days_overdue desc
          limit 200`,
        [minDaysOverdue, customer ?? null]
      );

      await audit(extra, 'get_overdue_invoices', { minDaysOverdue, customer }, rows.length);

      return {
        content: [{ type: 'text', text: JSON.stringify({ asOf: new Date().toISOString(), rows }, null, 2) }]
      };
    }
  );

  // ...more tools here
  return server;
}

import type { RequestHandlerExtra } from '@modelcontextprotocol/sdk/shared/protocol.js';
import { Pool } from 'pg';

type Extra = RequestHandlerExtra<any, any>;

// Separate pool with insert-only rights on the audit schema.
const auditDb = new Pool({ connectionString: process.env.AUDIT_DATABASE_URL });

export function requireScope(extra: Extra, scope: string) {
  const scopes = extra.authInfo?.scopes ?? [];
  if (!scopes.includes(scope)) {
    throw new Error(`Not allowed: this tool needs the ${scope} permission.`);
  }
}

export async function audit(extra: Extra, tool: string, input: unknown, rowCount: number) {
  await auditDb.query(
    `insert into audit.mcp_calls (client_id, tool, input, row_count, called_at)
     values ($1, $2, $3, $4, now())`,
    [extra.authInfo?.clientId ?? 'unknown', tool, JSON.stringify(input), rowCount]
  );
}

import express from 'express';
import { StreamableHTTPServerTransport } from '@modelcontextprotocol/sdk/server/streamableHttp.js';
import { createServer } from './server.js';
import { verifyBearerToken } from './auth.js'; // e.g. the SDK's requireBearerAuth middleware

const app = express();
app.use(express.json());

app.post('/mcp', verifyBearerToken, async (req, res) => {
  const server = createServer();
  const transport = new StreamableHTTPServerTransport({ sessionIdGenerator: undefined });
  res.on('close', () => { transport.close(); server.close(); });
  await server.connect(transport);
  await transport.handleRequest(req, res, req.body);
});

app.listen(3000, () => console.log('finance MCP server on :3000'));

npx @modelcontextprotocol/inspector
# connect to http://localhost:3000/mcp with a test token,
# then call get_overdue_invoices with { "minDaysOverdue": 60 }