# mcp-enterprise-starter

A production-grade MCP (Model Context Protocol) server that gives AI agents safe, authenticated access to a PostgreSQL database. Built as a reference implementation for teams building custom MCP servers for enterprise workflows.

## Architecture

```
┌─────────────────┐     ┌──────────────────────────────────┐     ┌────────────┐
│  Claude Desktop  │     │       MCP Enterprise Server       │     │            │
│  VS Code         │────▶│                                  │────▶│ PostgreSQL │
│  Any MCP Client  │     │  Auth → Validation → Tool Logic  │     │            │
└─────────────────┘     └──────────────────────────────────┘     └────────────┘
```

**Security layers:**
- API key authentication on every request
- SQL query sandboxing (SELECT only, keyword blocklist)
- Parameterized queries (no SQL injection)
- Sensitive column masking (email, SSN)
- Row limit enforcement
- Per-key rate limiting
- Structured JSON audit logging

## Quick Start

### Option 1: Docker Compose (recommended)

```bash
git clone https://github.com/agrgroup/mcp-enterprise-starter.git
cd mcp-enterprise-starter
cp .env.example .env
docker compose up --build
```

PostgreSQL starts with seeded sample data. The MCP server connects automatically.

### Option 2: Local Development

```bash
git clone https://github.com/agrgroup/mcp-enterprise-starter.git
cd mcp-enterprise-starter
npm install
cp .env.example .env

# Start PostgreSQL separately, then seed it:
psql $DATABASE_URL < seed.sql

# Run the server
npm run dev
```

### Connect Claude Desktop

Copy the Claude Desktop config from `mcp-config.json` into your Claude Desktop configuration file:

**macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
**Linux:** `~/.config/Claude/claude_desktop_config.json`

```json
{
  "mcpServers": {
    "enterprise-db": {
      "command": "node",
      "args": ["dist/server.js"],
      "cwd": "/path/to/mcp-enterprise-starter",
      "env": {
        "DATABASE_URL": "postgres://mcp_user:mcp_password@localhost:5432/mcp_enterprise",
        "API_KEYS": "your-api-key",
        "ALLOWED_TABLES": "departments,users,projects",
        "SENSITIVE_COLUMNS": "email,ssn"
      }
    }
  }
}
```

Restart Claude Desktop. Ask: "What tables are available?" to verify the connection.

### Connect VS Code

Add to your `.vscode/settings.json` or user settings:

```json
{
  "mcp": {
    "servers": {
      "enterprise-db": {
        "command": "node",
        "args": ["dist/server.js"],
        "cwd": "${workspaceFolder}/../mcp-enterprise-starter",
        "env": {
          "DATABASE_URL": "postgres://mcp_user:mcp_password@localhost:5432/mcp_enterprise",
          "API_KEYS": "your-api-key",
          "ALLOWED_TABLES": "departments,users,projects",
          "SENSITIVE_COLUMNS": "email,ssn"
        }
      }
    }
  }
}
```

## Tools

| Tool | Description |
|------|-------------|
| `query_database` | Execute read-only SQL queries with automatic row limiting and sensitive column masking |
| `list_tables` | List all tables available for querying (from the configured allowlist) |
| `get_schema` | Get column definitions, types, and constraints for a specific table |

## Resources

| URI Pattern | Description |
|-------------|-------------|
| `db://schema/{table_name}` | Table schema as structured JSON |

## Configuration

| Variable | Default | Description |
|----------|---------|-------------|
| `DATABASE_URL` | — | PostgreSQL connection string |
| `API_KEYS` | — | Comma-separated list of valid API keys |
| `ALLOWED_TABLES` | `departments,users,projects` | Tables the agent can access |
| `SENSITIVE_COLUMNS` | `email,ssn` | Columns to mask in query results |
| `ROW_LIMIT` | `100` | Default row limit for queries |
| `MAX_ROW_LIMIT` | `1000` | Maximum row limit (even if query specifies higher) |
| `RATE_LIMIT_RPM` | `60` | Requests per minute per API key |
| `MCP_TRANSPORT` | `stdio` | Transport mode: `stdio` or `sse` |
| `LOG_LEVEL` | `info` | Logging level |

## Testing

```bash
npm test          # Run all tests
npm run test:watch  # Watch mode
```

Tests mock the PostgreSQL connection so no database is needed.

## Adapt for Your Own Database

1. **Update `ALLOWED_TABLES`** in `.env` to expose your tables
2. **Update `SENSITIVE_COLUMNS`** to mask your sensitive fields
3. **Update `seed.sql`** with your schema (or remove it and use an existing database)
4. **Add new tools** in `src/tools/` following the pattern in `query-database.ts`
5. **Update `src/server.ts`** to register your new tools
6. **Add write operations** cautiously — start read-only, add writes with explicit confirmation patterns

## Security Notes

- API keys are checked on every tool call. No key = no access.
- Only SELECT queries are allowed. DROP, DELETE, INSERT, UPDATE, and other write operations are blocked at the query level.
- Sensitive columns are masked before results reach the agent. The agent never sees raw PII.
- Row limits prevent accidental full-table scans on large tables.
- All requests are logged as structured JSON to stderr for audit trails.
- The production Docker image runs as a non-root user.

## License

MIT