MCP-PostgreSQL-Ops

# MCP PostgreSQL Operations Server - AI Coding Agent Instructions

## Architecture Overview

This is a **Model Context Protocol (MCP) server** built with **FastMCP** that provides PostgreSQL database monitoring and operations through natural language queries. The server acts as a safe, read-only bridge between AI assistants and PostgreSQL databases.

### Core Components
- **`mcp_main.py`**: Main MCP server with 24+ `@mcp.tool()` decorated functions
- **`functions.py`**: Database connection layer using `asyncpg` with multi-database support
- **`version_compat.py`**: PostgreSQL 12-17 version detection and adaptive feature handling
- **`prompt_template.md`**: Comprehensive prompt definitions loaded via `@mcp.prompt()` decorators
- **Docker stack**: PostgreSQL + MCP server + MCPO proxy + Open WebUI integration

### Key Patterns

**Multi-Database Architecture**: All tools accept optional `database_name` parameter to target specific databases while maintaining a default connection database from `POSTGRES_DB` env var.

**Extension Dependencies**: Core functionality requires `pg_stat_statements` extension; `pg_stat_monitor` is optional. Always check extension availability with `check_extension_exists()` before using related tools.

**Version-Aware Tools**: Use `version_compat.py` for PostgreSQL 12-17 compatibility. Tools auto-adapt features based on detected version. **PostgreSQL 18 is beta and not yet supported** - will be added once stable.

**Tool Structure**: Each MCP tool follows this pattern:
```python
@mcp.tool()
async def get_something(limit: int = 20, database_name: str = None) -> str:
    """Detailed docstring with [Tool Purpose], [Exact Functionality], [Required Use Cases], [Strictly Prohibited Use Cases]"""
    try:
        # Validate inputs (limit constraints: max 1-100)
        # Check extension dependencies if needed
        # Execute queries via functions.py
        # Return formatted table data
    except Exception as e:
        logger.error(f"Failed to...: {e}")
        return f"Error: {str(e)}"
```

**Version Compatibility Pattern**: Critical for PostgreSQL 12 support - many tools use version-aware query builders:
```python
# In functions.py
from .version_compat import get_pg_stat_statements_query

async def get_pg_stat_statements_data(limit: int = 20, database: str = None):
    base_query = await get_pg_stat_statements_query(database)  # Auto-adapts for PG12/13+
    query = f"{base_query} LIMIT $1"
    return await execute_query(query, [limit], database=database)
```

## Development Workflows

### Local Development
```bash
# Primary development command - loads .env, starts MCP Inspector
./scripts/run-mcp-inspector-local.sh

# Direct execution for debugging with custom log levels
python -m src.mcp_postgresql_ops.mcp_main --log-level DEBUG --type streamable-http
```

### Docker Development
```bash
# Full stack with PostgreSQL + test data
docker-compose up -d

# Test data generation (creates 4 databases with 83k+ records)
./scripts/create-test-data.sh
```

### Environment Configuration
- Copy `.env.example` to `.env` and modify connection parameters
- **Critical**: `POSTGRES_DB` serves dual purpose - default connection target AND Docker database creation name
- Use `host.docker.internal` for `POSTGRES_HOST` when connecting from containers to host PostgreSQL

## Code Conventions

### Database Connection Pattern
```python
# Multi-database support - database parameter overrides POSTGRES_CONFIG default
async def get_db_connection(database: str = None) -> asyncpg.Connection:
    config = POSTGRES_CONFIG.copy()
    if database:
        config["database"] = database  # Override default
    return await asyncpg.connect(**config)
```

### Error Handling
- All MCP tools must return `str` (never raise exceptions to MCP layer)
- Log errors with `logger.error()` then return user-friendly error messages
- Mask sensitive information in connection info with `sanitize_connection_info()`

### Query Formatting
- Use `format_table_data(results, title)` for consistent table output
- Apply `format_bytes()` and `format_duration()` for human-readable values
- Enforce limit constraints: `limit = max(1, min(limit, 100))`

### Tool Compatibility Matrix
When adding new tools, **must** update the compatibility matrix in `README.md`:
- Classify as Extension-Independent, Version-Aware, or Extension-Dependent
- Document PostgreSQL version support (12-17)
- List system views/tables used
- Update tool count statistics

### Recent Major Changes
- **PostgreSQL 18 Support Removed**: Now supports 12-17 range (18 is beta)
- **Comprehensive PG12 Compatibility**: All tools now work on PostgreSQL 12
- **Version-Aware Query Generation**: Automatic column mapping and NULL handling
- **Enhanced Schema Analysis**: New tools for table relationships and schema introspection

## Project-Specific Integrations

### Prompt Template System
The server loads prompts from `prompt_template.md` using a custom parsing system:
- `@mcp.prompt("prompt_template_full")` - complete template
- `@mcp.prompt("prompt_template_headings")` - section list only
- `get_prompt_template(section="specific_section")` - targeted content

### Docker Multi-Service Architecture
- **postgres**: Percona PostgreSQL (version from `PGSQL_VERSION` env var, default 13)
- **postgres-init-extensions**: One-shot container that installs extensions and creates comprehensive test data
- **mcp-server**: Main MCP server container
- **mcpo-proxy**: HTTP proxy for web-based MCP clients
- **open-webui**: Web interface for testing

### Critical Dependencies
```python
# Required for all database operations
import asyncpg  # Not psycopg2 - uses asyncpg exclusively

# MCP framework
from fastmcp import FastMCP

# Extension checks are mandatory for performance tools
await check_extension_exists("pg_stat_statements")
```

### PostgreSQL 12 Compatibility Patterns
The major recent work focused on PostgreSQL 12 compatibility. Key patterns:

**Column Mapping for pg_stat_statements/pg_stat_monitor**: PostgreSQL 12 uses `total_time`/`mean_time` while 13+ uses `total_exec_time`/`mean_exec_time`. Version-aware queries handle this:
```python
# In version_compat.py
if version.has_pg_stat_statements_exec_time:  # PG13+
    base_columns.extend(["total_exec_time", "mean_exec_time"])
else:  # PG12
    base_columns.extend(["total_time as total_exec_time", "mean_time as mean_exec_time"])
```

**NULL Column Handling**: Many PG13+ columns don't exist in PG12 (e.g., `wal_status`, `n_ins_since_vacuum`). Use NULL placeholders:
```python
if version.has_replication_slot_wal_status:
    base_columns.append("wal_status")
else:
    base_columns.append("NULL as wal_status")
```

## Testing Strategy

### Test Data Generation
- Run `./scripts/create-test-data.sh` to create 4 realistic databases:
  - `ecommerce`: E-commerce with products, orders, customers (~9k records)
  - `analytics`: Web analytics and sales data (~59k records) 
  - `inventory`: Warehouse management (~3k records)
  - `hr_system`: Employee and payroll data (~12k records)

### Natural Language Testing
Test tools with realistic prompts - never use function names directly:
- ✅ "Show me the slowest queries"
- ❌ "Run get_pg_stat_statements_top_queries"

### Configuration Files
- `mcp-config.json.stdio`: Standard CLI integration
- `mcp-config.json.http`: HTTP mode for web clients
- Both must have matching port configurations with `docker-compose.yml`

## Common Pitfalls

1. **Database Parameter Confusion**: `POSTGRES_DB` is the default connection database, not a constraint on which databases can be accessed
2. **Extension Assumptions**: Always check `pg_stat_statements`/`pg_stat_monitor` availability before using performance tools
3. **Port Misalignment**: `FASTMCP_PORT`, Docker external port, and MCP config files must all match
4. **Environment Loading**: Use `scripts/run-mcp-inspector-local.sh` which properly loads `.env` - direct Python execution won't load environment
5. **Query Limits**: All tools enforce 1-100 limits for performance; don't assume unlimited results
6. **asyncpg Parameter Binding**: Use `$1, $2, ...` format, not `%s` - all SQL queries must use asyncpg-compatible parameter binding
7. **PostgreSQL Version Support**: Currently supports 12-17; PostgreSQL 18 support pending stable release
8. **Version-Specific Features**: Always use `version_compat.py` patterns for new tools that query system catalogs