# DP-MCP Server Architecture
This document provides a comprehensive overview of the DP-MCP Server architecture, design decisions, and technical implementation details.
## Table of Contents
- [Overview](#overview)
- [System Architecture](#system-architecture)
- [Component Design](#component-design)
- [Data Flow](#data-flow)
- [Technology Stack](#technology-stack)
- [Security Architecture](#security-architecture)
- [Performance Considerations](#performance-considerations)
- [Scalability Design](#scalability-design)
- [Error Handling Strategy](#error-handling-strategy)
- [Future Enhancements](#future-enhancements)
## Overview
The DP-MCP Server is a Model Context Protocol (MCP) server that bridges PostgreSQL databases and MinIO object storage systems. It's designed as a comprehensive data platform integration solution for AI applications, providing 11 specialized tools for database operations, object storage management, and cross-platform workflows.
### Design Goals
1. **Simplicity**: Easy to deploy, configure, and maintain
2. **Reliability**: Robust error handling and graceful degradation
3. **Performance**: Efficient resource usage and optimized operations
4. **Security**: Secure by default with comprehensive access controls
5. **Extensibility**: Modular design for easy feature additions
6. **Compatibility**: Full MCP 1.0 specification compliance
## System Architecture
### High-Level Architecture
```
┌─────────────────────────────────────────────────────────────────┐
│ AI Agent/Client │
└─────────────────────┬───────────────────────────────────────────┘
│ MCP Protocol (JSON-RPC 2.0)
│ HTTP Transport (SSE)
┌─────────────────────▼───────────────────────────────────────────┐
│ DP-MCP Server │
│ ┌─────────────────┬─────────────────┬─────────────────────────┐ │
│ │ FastMCP │ Tool Layer │ Configuration │ │
│ │ Framework │ │ Management │ │
│ └─────────────────┼─────────────────┼─────────────────────────┘ │
│ ┌─────────────────▼─────────────────▼─────────────────────────┐ │
│ │ Core Tools │ │
│ │ ┌─────────────┬─────────────┬──────────────────────────────┐ │ │
│ │ │ PostgreSQL │ MinIO │ Combined │ │ │
│ │ │ Tools │ Tools │ Operations │ │ │
│ │ └─────────────┴─────────────┴──────────────────────────────┘ │ │
│ └───────────────────────────────────────────────────────────── │ │
└─────────┬───────────────────────────────────────┬───────────────┘
│ │
┌─────────▼───────────────────┐ ┌─────────▼─────────────────┐
│ PostgreSQL Database │ │ MinIO Object Store │
│ │ │ │
│ ┌─────────────────────────┐ │ │ ┌───────────────────────┐ │
│ │ Tables │ │ │ │ Buckets │ │
│ │ - users │ │ │ │ - default-bucket │ │
│ │ - products │ │ │ │ - backups │ │
│ │ - orders │ │ │ │ - data-lake │ │
│ └─────────────────────────┘ │ │ └───────────────────────┘ │
└─────────────────────────────┘ └───────────────────────────┘
```
### Component Interaction
```mermaid
graph TB
A[AI Agent] -->|MCP Request| B[FastMCP Server]
B -->|Route Tool Call| C[Tool Router]
C -->|PostgreSQL Tool| D[PostgreSQL Handler]
C -->|MinIO Tool| E[MinIO Handler]
C -->|Combined Tool| F[Combined Operations]
D -->|SQL Query| G[PostgreSQL Database]
E -->|S3 API| H[MinIO Storage]
F -->|Export Data| D
F -->|Upload to Storage| E
G -->|Results| D
H -->|Objects| E
D -->|Formatted Response| C
E -->|Status/Data| C
F -->|Backup Status| C
C -->|MCP Response| B
B -->|JSON-RPC| A
```
## Component Design
### 1. FastMCP Server Core
**Purpose**: MCP protocol implementation and HTTP transport management
**Key Components**:
- Protocol handler for JSON-RPC 2.0
- HTTP server with Server-Sent Events (SSE) support
- Tool registration and routing
- Request/response serialization
**Implementation**:
```python
# server.py
mcp = FastMCP("dp-mcp")
@mcp.tool()
async def tool_function(param: str) -> str:
# Tool implementation
return result
```
### 2. PostgreSQL Tools Module
**Purpose**: Database operations and introspection
**Components**:
- Connection management with pooling
- Query execution with result formatting
- Schema introspection utilities
- CSV export functionality
**Key Functions**:
```python
# postgres_tools.py
async def execute_query(query: str, limit: int) -> str
async def list_tables(schema: str) -> str
async def describe_table(table_name: str, schema: str) -> str
async def export_table_to_csv(table_name: str, limit: int, where_clause: str) -> str
```
### 3. MinIO Tools Module
**Purpose**: Object storage operations and management
**Components**:
- S3-compatible API client
- Bucket management operations
- Object upload/download with streaming
- Metadata and listing operations
**Key Functions**:
```python
# minio_tools.py
async def list_buckets() -> str
async def upload_object(bucket: str, object_name: str, data: str) -> str
async def download_object(bucket: str, object_name: str) -> str
```
### 4. Configuration Management
**Purpose**: Environment-based configuration with validation
**Features**:
- Pydantic models for type safety
- Environment variable integration
- Configuration validation
- Default value management
**Structure**:
```python
# config.py
class PostgreSQLConfig(BaseModel):
host: str = Field(default="localhost")
port: int = Field(default=5432)
# ... other fields
class Config(BaseModel):
postgres: PostgreSQLConfig
minio: MinIOConfig
server: ServerConfig
```
### 5. Combined Operations
**Purpose**: Cross-platform workflows and data pipelines
**Key Operation**: `backup_table_to_minio`
1. Extract data from PostgreSQL table
2. Convert to CSV format
3. Generate timestamped object name
4. Upload to MinIO bucket
5. Return confirmation with details
## Data Flow
### 1. Request Flow
```
AI Agent Request
↓
FastMCP Protocol Handler
↓
Tool Router (based on tool name)
↓
Specific Tool Handler
↓
External Service (PostgreSQL/MinIO)
↓
Response Processing
↓
MCP Response to Agent
```
### 2. Database Query Flow
```
execute_sql_query Tool
↓
Connection Pool
↓
PostgreSQL Database
↓
Raw Results
↓
Result Formatter
↓
Formatted Table/Status
```
### 3. Object Storage Flow
```
MinIO Tool Call
↓
MinIO Client
↓
S3 API Call
↓
MinIO Server
↓
Response Processing
↓
Formatted Response
```
### 4. Backup Flow
```
backup_table_to_minio Tool
↓
PostgreSQL Query Execution
↓
CSV Generation
↓
Timestamp Generation
↓
MinIO Upload
↓
Confirmation Response
```
## Technology Stack
### Core Technologies
| Component | Technology | Version | Purpose |
|-----------|------------|---------|---------|
| **Language** | Python | 3.10+ | Main implementation language |
| **MCP Framework** | FastMCP | 2.0+ | MCP server implementation |
| **HTTP Server** | Uvicorn | 0.24+ | ASGI server for HTTP transport |
| **Database Driver** | psycopg2 | 2.9+ | PostgreSQL connectivity |
| **Object Storage** | MinIO Client | 7.2+ | S3-compatible operations |
| **Configuration** | Pydantic | 2.5+ | Settings validation |
| **Environment** | python-dotenv | 1.0+ | Environment variable management |
### Development Tools
| Tool | Purpose |
|------|---------|
| **uv** | Package management and virtual environments |
| **Black** | Code formatting |
| **isort** | Import sorting |
| **MyPy** | Static type checking |
| **Pytest** | Testing framework |
| **Docker** | Containerization |
### Infrastructure Dependencies
| Service | Purpose | Version |
|---------|---------|---------|
| **PostgreSQL** | Primary database | 12+ |
| **MinIO** | Object storage | Latest |
| **Docker Compose** | Development orchestration | 2.0+ |
## Security Architecture
### 1. Network Security
**Transport Security**:
- HTTPS/TLS support for all external communications
- Configurable SSL modes for PostgreSQL connections
- Secure MinIO connections with certificate validation
**Network Isolation**:
- Server binds to localhost by default
- Production deployment behind reverse proxy
- Firewall rules for port access control
### 2. Authentication & Authorization
**Database Security**:
- Parameterized queries prevent SQL injection
- Read-only database user recommendations
- Connection pooling with authenticated sessions
**Object Storage Security**:
- Access key-based authentication
- Bucket-level permission enforcement
- Object-level access control support
### 3. Data Protection
**In Transit**:
- TLS encryption for all network communications
- Secure credential transmission
- Certificate-based authentication
**At Rest**:
- Database encryption support
- Object storage server-side encryption
- Secure credential storage recommendations
**In Memory**:
- Credential scrubbing in logs
- Memory-safe string handling
- Garbage collection of sensitive data
### 4. Input Validation
**Request Validation**:
- Pydantic schema validation for all inputs
- Type checking and range validation
- SQL injection prevention
**Output Sanitization**:
- Controlled data exposure
- Error message sanitization
- Log data scrubbing
## Performance Considerations
### 1. Database Performance
**Connection Management**:
- Connection pooling for concurrent requests
- Configurable pool sizes
- Connection timeout handling
**Query Optimization**:
- Automatic LIMIT clause injection
- Result streaming for large datasets
- Query timeout configuration
### 2. Object Storage Performance
**Transfer Optimization**:
- Streaming uploads/downloads
- Configurable chunk sizes
- Parallel transfer support
**Caching Strategy**:
- Bucket listing caching
- Metadata caching for frequently accessed objects
- Connection reuse
### 3. Memory Management
**Efficient Processing**:
- Streaming CSV generation
- Chunked data processing
- Garbage collection optimization
**Resource Limits**:
- Configurable query result limits
- Memory usage monitoring
- Graceful degradation under load
## Scalability Design
### 1. Horizontal Scaling
**Load Balancing**:
- Stateless server design
- Session-independent operations
- Health check endpoints
**Multi-Instance Deployment**:
- Shared configuration management
- Database connection pooling
- Load balancer compatibility
### 2. Vertical Scaling
**Resource Optimization**:
- Configurable worker processes
- Memory allocation tuning
- CPU usage optimization
**Performance Monitoring**:
- Metrics collection endpoints
- Resource usage tracking
- Performance bottleneck identification
### 3. Service Isolation
**Microservice Architecture**:
- Modular tool design
- Independent service scaling
- Fault isolation
**Container Orchestration**:
- Docker container support
- Kubernetes deployment readiness
- Service mesh compatibility
## Error Handling Strategy
### 1. Error Classification
**Database Errors**:
- Connection failures
- Query syntax errors
- Permission denied
- Timeout errors
**Storage Errors**:
- Network connectivity issues
- Authentication failures
- Object not found
- Storage full conditions
**System Errors**:
- Memory allocation failures
- Network timeouts
- Configuration errors
### 2. Error Response Format
**Structured Error Messages**:
```json
{
"jsonrpc": "2.0",
"id": "request-id",
"error": {
"code": -32000,
"message": "Tool execution failed",
"data": {
"tool": "tool_name",
"error": "Detailed error message",
"parameters": "Request parameters"
}
}
}
```
### 3. Recovery Strategies
**Graceful Degradation**:
- Partial functionality maintenance
- Service health monitoring
- Automatic retry mechanisms
**Circuit Breaker Pattern**:
- Service failure detection
- Automatic failover
- Recovery state management
## Future Enhancements
### 1. Performance Improvements
**Planned Features**:
- Redis caching layer
- Connection pool optimization
- Query result caching
- Async/await optimization
### 2. Additional Integrations
**Database Support**:
- MySQL/MariaDB connector
- SQLite support
- MongoDB integration
- TimescaleDB support
**Storage Options**:
- AWS S3 direct integration
- Google Cloud Storage
- Azure Blob Storage
- Local filesystem storage
### 3. Advanced Features
**Analytics and Monitoring**:
- Prometheus metrics integration
- Grafana dashboard templates
- OpenTelemetry support
- Custom alerting rules
**Security Enhancements**:
- OAuth2/OIDC integration
- Role-based access control
- Audit logging
- Data encryption at rest
### 4. Developer Experience
**Tooling Improvements**:
- Interactive CLI interface
- Web-based management console
- Configuration wizard
- Health check dashboard
**Documentation Enhancements**:
- Interactive API explorer
- Video tutorials
- Best practices guide
- Performance tuning guide
## Conclusion
The DP-MCP Server architecture is designed for simplicity, reliability, and extensibility. The modular design allows for easy maintenance and feature additions while maintaining high performance and security standards. The use of modern Python technologies and adherence to MCP specifications ensures compatibility with AI applications and ease of integration.
For implementation details, see the source code in the `src/` directory and the API documentation in [`API_REFERENCE.md`](API_REFERENCE.md).
