PostgreSQL Healthcare MCP Server

An example Model Context Protocol (MCP) server implementation for managing a healthcare database with PostgreSQL. This demo showcases patient management, provider scheduling, therapy sessions, and complex payment tracking including insurance claims and patient responsibilities.

Features

• Complete Healthcare Database Schema

  • Patients with insurance information
  • Healthcare providers with specialties
  • Therapy/consultation sessions
  • Complex payment tracking (copays, coinsurance, deductibles, cash pay, insurance reimbursements)
  • Payment statuses (pending, paid, denied, partially paid, appealed)

• Powerful MCP Tools

  • Patient and provider management
  • Session tracking and scheduling
  • Payment and claims processing
  • Financial reporting and analytics
  • Outstanding payments tracking
  • Denied claims management

• MCP Resources

  • System-wide statistics (patient, provider, session, and payment counts)

Tech Stack

• TypeScript - Type-safe development
• Prisma - Modern ORM for PostgreSQL
• Model Context Protocol (MCP) - AI assistant integration
• PostgreSQL 16 - Reliable database
• Docker Compose - Easy database setup
• Zod - Runtime type validation

Architecture

This project follows a modular architecture for maintainability and scalability:

• src/index.ts - Main MCP server setup, request handlers, and transport configuration
• src/definitions/ - MCP tool definitions with input schemas (the "what" tools do)
• src/schemas/ - Zod validation schemas for runtime type checking
• src/tools/ - Tool implementation handlers organized by domain (the "how" tools work)
  • Each file exports functions that interact with the database via Prisma
  • Handlers are registered in tools/index.ts
• src/resources/ - MCP resource definitions and read handlers for direct data access

This separation allows for:

• Easy addition of new tools (add definition + handler + schema)
• Clear separation of concerns
• Testable business logic
• Type safety throughout the stack

Prerequisites

• Node.js 18+
• Docker and Docker Compose (for database)
• npm or yarn

Quick Start

1. Clone and Install

cd postgres-mcp-server
npm install

2. Start PostgreSQL Database

npm run docker:up

This will start a PostgreSQL 16 container with the healthcare demo database.

3. Setup Database Schema and Seed Data

npm run db:setup

This command will:

• Generate Prisma Client
• Push the schema to the database
• Seed with realistic healthcare data (6 patients, 4 providers, 13 sessions, various payments)

4. Test the MCP Server (Optional)

npm test

This runs a comprehensive test suite that verifies all MCP tools and resources are working correctly.

5. Run the MCP Server

npm run dev

The server will start and listen on stdio for MCP protocol messages.

Database Management Scripts

• npm run docker:up - Start PostgreSQL container
• npm run docker:down - Stop PostgreSQL container
• npm run db:setup - Initial database setup (generate + migrate + seed)
• npm run db:seed - Re-seed database with sample data
• npm run db:reset - Clear and re-seed database
• npm run db:studio - Open Prisma Studio (visual database browser)
• npm run db:generate - Generate Prisma Client
• npm test - Run comprehensive MCP server tests

Database Schema

Patients

• Personal information (name, email, phone, DOB, address)
• Insurance details (provider name, policy ID)
• Relationship to sessions

Providers

• Professional information (name, email, phone)
• Specialty and license number
• Hourly rate
• Relationship to sessions

Sessions

• Patient and provider relationships
• Session details (date, duration, type)
• Status (scheduled, completed, cancelled, no-show)
• Clinical codes (ICD-10 diagnosis, CPT procedure)
• Total charges
• Related payments

Payments

• Session relationship
• Amount and payment type:
  • COPAY - Fixed patient responsibility
  • COINSURANCE - Percentage patient responsibility
  • DEDUCTIBLE - Patient deductible amount
  • CASH_PAY - Full self-pay amount
  • INSURANCE_REIMBURSEMENT - Insurance payments
• Payment status:
  • PENDING - Awaiting payment
  • PAID - Payment received
  • DENIED - Insurance claim denied
  • PARTIALLY_PAID - Partial payment received
  • APPEALED - Claim under appeal
• Insurance claim tracking
• Denial reasons and notes

Available MCP Tools

Query Tools

get_patients

Search and retrieve patient records.

{
  "searchTerm": "john",      // Optional: search by name or email
  "hasInsurance": true,       // Optional: filter by insurance status
  "limit": 10                 // Optional: max results (default: 10)
}

get_patient_details

Get comprehensive patient information including all sessions and payments.

{
  "patientId": "uuid"         // Required: patient ID
}

get_providers

Retrieve provider records.

{
  "specialty": "Psychology",  // Optional: filter by specialty
  "limit": 10                 // Optional: max results (default: 10)
}

get_sessions

Flexible session querying with multiple filters.

{
  "patientId": "uuid",        // Optional: filter by patient
  "providerId": "uuid",       // Optional: filter by provider
  "status": "completed",      // Optional: scheduled|completed|cancelled|no-show
  "startDate": "2024-09-01",  // Optional: ISO date
  "endDate": "2024-09-30",    // Optional: ISO date
  "limit": 20                 // Optional: max results (default: 20)
}

get_payments

Retrieve payment records with filtering.

{
  "sessionId": "uuid",        // Optional: filter by session
  "type": "COPAY",            // Optional: payment type
  "status": "PENDING",        // Optional: payment status
  "limit": 20                 // Optional: max results (default: 20)
}

get_outstanding_payments

Get all outstanding payments (pending, partially paid, denied).

{
  "patientId": "uuid"         // Optional: filter by patient
}

get_denied_claims

Retrieve denied insurance claims with reasons.

{
  "includeAppealed": false    // Optional: include appealed claims (default: false)
}

get_revenue_report

Generate comprehensive revenue analytics.

{
  "startDate": "2024-09-01",  // Optional: ISO date
  "endDate": "2024-09-30"     // Optional: ISO date
}

Mutation Tools

create_patient

Create a new patient record.

{
  "firstName": "John",
  "lastName": "Doe",
  "email": "john.doe@email.com",
  "phone": "(555) 123-4567",
  "dateOfBirth": "1990-01-15",
  "address": "123 Main St",
  "city": "Boston",
  "state": "MA",
  "zipCode": "02108",
  "insuranceName": "Blue Cross",     // Optional
  "insuranceId": "BCBS-123456"       // Optional
}

create_session

Schedule or record a new session.

{
  "patientId": "uuid",
  "providerId": "uuid",
  "sessionDate": "2024-10-15T10:00:00Z",
  "durationMins": 60,
  "sessionType": "Therapy",
  "status": "scheduled",
  "notes": "Initial consultation",    // Optional
  "diagnosisCode": "F41.1",          // Optional: ICD-10
  "procedureCode": "90834",          // Optional: CPT
  "totalCharge": 175.00
}

update_payment_status

Update payment status and details.

{
  "paymentId": "uuid",
  "status": "PAID",
  "paymentDate": "2024-10-08",       // Optional: ISO date
  "denialReason": "...",             // Optional
  "notes": "..."                     // Optional
}

Available MCP Resources

• healthcare://stats/overview - System-wide statistics including patient, provider, session, and payment counts

Sample Data

The seed script creates realistic healthcare data:

• 6 Patients: Mix of insured and cash-pay patients
• 4 Providers: Various specialties (Psychology, Psychiatry, Family Therapy, Addiction Counseling)
• 13 Sessions: Past completed sessions and future scheduled appointments
• Multiple Payment Scenarios:
  • Fully paid sessions with insurance + copay
  • Pending insurance reimbursements
  • Denied claims with reasons
  • Partially paid deductibles
  • Cash pay patients
  • Appealed claims
  • No-show fees

Using with Claude Desktop

Add this server to your Claude Desktop configuration:

MacOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "postgres-healthcare": {
      "command": "node",
      "args": [
        "/absolute/path/to/postgres-mcp-server/dist/index.js"
      ],
      "env": {
        "DATABASE_URL": "postgresql://postgres:postgres@localhost:5432/healthcare_demo?schema=public"
      }
    }
  }
}

Or for development (using tsx):

{
  "mcpServers": {
    "postgres-healthcare": {
      "command": "npx",
      "args": [
        "tsx",
        "/absolute/path/to/postgres-mcp-server/src/index.ts"
      ],
      "env": {
        "DATABASE_URL": "postgresql://postgres:postgres@localhost:5432/healthcare_demo?schema=public"
      }
    }
  }
}

After configuration, restart Claude Desktop and you'll see the PostgreSQL Healthcare server available.

Example Queries to Try

Once connected, you can ask Claude:

• "Show me all patients with insurance"
• "What are the outstanding payments?"
• "Show me all denied insurance claims and why they were denied"
• "Generate a revenue report for September 2024"
• "Which patients have sessions scheduled in the future?"
• "Show me all therapy sessions for John Smith including payment details"
• "What's the total amount in pending insurance reimbursements?"
• "Create a new patient named Jane Wilson with email jane@email.com"
• "Show me the system statistics overview"

See EXAMPLE_QUERIES.md for more comprehensive examples and use cases.

Development

Build for Production

npm run build
npm start

Database Management

View and edit data visually with Prisma Studio:

npm run db:studio

Reset database to fresh sample data:

npm run db:reset

Project Structure

postgres-mcp-server/
├── src/
│   ├── index.ts                    # MCP server entry point
│   ├── definitions/
│   │   └── tools.ts                # Tool definitions and schemas
│   ├── schemas/
│   │   └── index.ts                # Zod validation schemas
│   ├── tools/
│   │   ├── index.ts                # Tool handler registry
│   │   ├── patients.ts             # Patient management tools
│   │   ├── providers.ts            # Provider query tools
│   │   ├── sessions.ts             # Session management tools
│   │   ├── payments.ts             # Payment and claims tools
│   │   └── reports.ts              # Financial reporting tools
│   └── resources/
│       └── index.ts                # MCP resource definitions and handlers
├── prisma/
│   └── schema.prisma               # Database schema
├── scripts/
│   ├── seed.ts                     # Database seeding script
│   ├── setup-db.ts                 # Initial setup script
│   ├── reset-db.ts                 # Database reset script
│   └── test-server.ts              # MCP server test suite
├── docker-compose.yml              # PostgreSQL container config
├── claude_desktop_config.example.json
├── EXAMPLE_QUERIES.md              # Example queries for Claude
├── QUICK_START.md
├── PROJECT_OVERVIEW.md
├── package.json
├── tsconfig.json
└── README.md

Environment Variables

Create a .env file (already created with defaults):

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/healthcare_demo?schema=public"

For production, use a secure password and proper connection string.

Troubleshooting

Database Connection Issues

If you can't connect to the database:

1. Ensure Docker is running: docker ps
2. Check if PostgreSQL container is up: docker ps | grep healthcare-postgres
3. Restart the container: npm run docker:down && npm run docker:up
4. Verify DATABASE_URL in .env matches your setup

Port Already in Use

If port 5432 is already taken:

1. Stop the conflicting service or
2. Modify docker-compose.yml to use a different port:

   ports:
     - "5433:5432"  # Use 5433 on host instead

3. Update DATABASE_URL to: postgresql://postgres:postgres@localhost:5433/...

Reset Everything

To start completely fresh:

npm run docker:down
docker volume rm postgres-mcp-server_postgres-data  # Remove old data
npm run docker:up
npm run db:setup

License

MIT

Contributing

This is a demo project showcasing MCP server capabilities with PostgreSQL. Feel free to use it as a starting point for your own healthcare or database-backed MCP servers!