# MCP Database Tools Server

## 📋 Table of Contents
- [Overview](#overview)
- [Architecture](#architecture)
- [Project Structure](#project-structure)
- [Component Flow](#component-flow)
- [Installation & Setup](#installation--setup)
- [Usage](#usage)
- [Configuration](#configuration)
- [Troubleshooting](#troubleshooting)

---

## 🎯 Overview

This project is a **Model Context Protocol (MCP) Server** that automates Django database setup and management tasks. It provides tools to:
- Create PostgreSQL databases
- Enable PostgreSQL extensions (hstore)
- Update Django .env configuration files
- Execute Django management commands

The server integrates with VS Code Copilot and can be accessed via:
- VS Code MCP integration
- Automated workflow scripts

---

## 🏗️ Architecture

```
┌─────────────────────────────────────────────────────────────────┐
│                        MCP Client Layer                          │
│  ┌──────────────┐  ┌──────────────┐                           │
│  │  VS Code     │  │   Workflow   │                           │
│  │  Copilot     │  │   Scripts    │                           │
│  └──────┬───────┘  └──────┬───────┘                           │
└─────────┼──────────────────┼──────────────────┼──────────────────┘
          │                  │                  │
          └──────────────────┼──────────────────┘
                             │
                    ┌────────▼────────┐
                    │   MCP Server    │
                    │   (server.py)   │
                    │                 │
                    │  - list_tools() │
                    │  - call_tool()  │
                    └────────┬────────┘
                             │
          ┌──────────────────┼──────────────────┐
          │                  │                  │
    ┌─────▼─────┐     ┌─────▼─────┐     ┌─────▼─────┐
    │PostgreSQL │     │  Django   │     │   .env    │
    │ Database  │     │  Backend  │     │   File    │
    └───────────┘     └───────────┘     └───────────┘
```

---

## 📁 Project Structure

```
MCP_project/
│
├── server.py                  # Main MCP server implementation
├── mcp.json                   # MCP server metadata
├── requirements.txt           # Python dependencies
│
* Web and CLI clients removed: The project no longer includes web_client.py or test_client.py files.
├── run_workflow.py           # Automated workflow executor
│
├── tools/                    # Utility modules (legacy/reference)
│   ├── __init__.py
│   ├── db_tools.py          # PostgreSQL database operations
│   ├── env_tools.py         # Environment file management
│   └── django_tools.py      # Django command execution
│
├── templates/               # Web UI templates
│   └── index.html          # Main web interface
│
└── venv/                   # Python virtual environment
```

---

## 🔄 Component Flow

### 1. **Core Server (server.py)**

The heart of the system, implementing the MCP protocol:

```python
┌─────────────────────────────────────────────────────────┐
│                    server.py                             │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  Configuration:                                          │
│  ├─ PG_USER, PG_PASSWORD, PG_HOST                       │
│  ├─ DB_NAME (default: sample_project_db)                │
│  ├─ ENV_PATH (Django .env location)                     │
│  ├─ MANAGE_PY (Django manage.py location)               │
│  └─ PYTHON_EXEC (Virtual environment Python)            │
│                                                          │
│  MCP Server Decorators:                                 │
│  ├─ @server.list_tools() → Returns available tools      │
│  └─ @server.call_tool() → Executes tool operations      │
│                                                          │
│  Tools Implemented:                                      │
│  ├─ create_database(db_name)                            │
│  ├─ enable_hstore(db_name)                              │
│  ├─ update_env(db_name)                                 │
│  └─ django(cmd)                                          │
└─────────────────────────────────────────────────────────┘
```

**Key Features:**
- **Async/await architecture** for MCP protocol compliance
- **stdio communication** (not HTTP) for MCP client integration
- **Automatic lowercase conversion** for PostgreSQL database names
- **Environment variable loading** from .env files for Django commands
- **Virtual environment Python** execution to ensure dependencies

---

### 2. **Tool: create_database**

```
Input: { db_name: "mydb" }
│
├─ Converts db_name to lowercase ("mydb")
│
├─ Connects to PostgreSQL server (postgres database)
│  └─ Uses: PG_USER, PG_PASSWORD, PG_HOST
│
├─ Executes: CREATE DATABASE mydb;
│
└─ Returns: "Database mydb created."
```

**PostgreSQL Connection:**
```python
psycopg2.connect(
    dbname="postgres",
    user=PG_USER, 
    password=PG_PASSWORD,
    host=PG_HOST
)
```

---

### 3. **Tool: enable_hstore**

```
Input: { db_name: "mydb" }
│
├─ Converts db_name to lowercase
│
├─ Connects to the specified database
│
├─ Executes: CREATE EXTENSION IF NOT EXISTS hstore;
│
└─ Returns: "hstore extension enabled in mydb."
```

**Purpose:** Enables PostgreSQL's hstore extension for key-value pair storage.

---

### 4. **Tool: update_env**

```
Input: { db_name: "mydb" }
│
├─ Converts db_name to lowercase
│
├─ Reads ENV_PATH file
│
├─ Finds line: POSTGRES_DB_NAME=old_value
│  └─ Skips commented lines (#)
│
├─ Replaces with: POSTGRES_DB_NAME=mydb
│
└─ Returns: ".env updated: POSTGRES_DB_NAME=mydb"
```

**File Operations:**
- Preserves all other .env content
- Only updates non-commented POSTGRES_DB_NAME lines
- Maintains file structure and formatting

---

### 5. **Tool: django**

```
Input: { cmd: "migrate" }
│
├─ Loads environment from ENV_PATH using dotenv
│  └─ Merges with os.environ
│
├─ Executes: PYTHON_EXEC MANAGE_PY migrate
│  └─ In working directory: dirname(MANAGE_PY)
│  └─ With loaded environment variables
│
├─ Captures stdout and stderr
│
└─ Returns: Command output with exit code
```

**Execution Flow:**
```python
subprocess.run(
    [PYTHON_EXEC, MANAGE_PY] + cmd.split(),
    cwd=workdir,
    env=env,  # Loaded from .env
    capture_output=True,
    text=True
)
```

**Why Virtual Environment Python?**
- Django and dependencies installed in virtual environment
- System Python lacks required packages
- Ensures consistent execution environment

---

## Client Interfaces

Access to the server is primarily via VS Code MCP integration and the automated workflow script.

### C. **Workflow Automation (run_workflow.py)**

```
Complete Database Setup Workflow
┌─────────────────────────────────────────┐
│  Step 1: Create database                 │
│  Step 2: Enable hstore extension         │
│  Step 3: Update .env file                │
│  Step 4: Run create_text_search_config   │
│  Step 5: Run migrations                  │
│  Step 6: Run update_fixtures             │
└─────────────────────────────────────────┘
```

**Usage:**
```bash
python run_workflow.py mydb
```

**What It Does:**
1. Creates PostgreSQL database "mydb"
2. Enables hstore extension
3. Updates .env with POSTGRES_DB_NAME=mydb
4. Runs Django setup commands in sequence
5. Reports success/failure for each step

---

## 🛠️ Installation & Setup

### Prerequisites
- Python 3.12+
- PostgreSQL server running
- Django project (optional, for Django commands)

### Step 1: Clone/Setup Project
```bash
cd /home/chaitanyaphani/MCP_project
```

### Step 2: Create Virtual Environment
```bash
python3 -m venv venv
source venv/bin/activate  # Linux/Mac
# or
venv\Scripts\activate  # Windows
```

### Step 3: Install Dependencies
```bash
pip install -r requirements.txt
```

**Dependencies:**
- `mcp` - Model Context Protocol SDK
- `Flask` - Web UI framework
- `psycopg2-binary` - PostgreSQL adapter
- `python-dotenv` - Environment file support

### Step 4: Configure PostgreSQL
Edit `server.py`:
```python
PG_USER = "postgres"
PG_PASSWORD = "your_password"  # Update this!
PG_HOST = "localhost"
```

### Step 5: Configure Django Paths
Edit `server.py`:
```python
ENV_PATH = "/path/to/your/django/.env"
MANAGE_PY = "/path/to/your/django/manage.py"
PYTHON_EXEC = "/path/to/your/django/venv/bin/python"
```

### Step 6: Configure VS Code (Optional)
Edit VS Code settings (`settings.json`):
```json
{
  "mcpServers": {
    "dbtools": {
      "command": "python",
      "args": ["server.py"],
      "cwd": "/home/chaitanyaphani/MCP_project"
    }
  }
}
```

---

## 🚀 Usage

### Method 1: Automated Workflow
```bash
python run_workflow.py database_name
```

### Method 2: VS Code Copilot
Once configured, simply ask:
Once configured, simply ask:
```
"Create a database named myproject, enable hstore, 
update the .env file, and run migrations"
```

---

## ⚙️ Configuration

### Environment Variables
The server uses these configuration constants:

| Variable | Purpose | Default |
|----------|---------|---------|
| `PG_USER` | PostgreSQL username | `postgres` |
| `PG_PASSWORD` | PostgreSQL password | `root` |
| `PG_HOST` | PostgreSQL host | `localhost` |
| `PG_PORT` | PostgreSQL port | `5432` |
| `DB_NAME` | Default database name | `sample_project_db` |
| `ENV_PATH` | Django .env file path | `/path/to/.env` |
| `MANAGE_PY` | Django manage.py path | `/path/to/manage.py` |
| `PYTHON_EXEC` | Virtual env Python | `/path/to/venv/bin/python` |

### Django .env File Format
Expected format:
```env
POSTGRES_DB_HOST=localhost
POSTGRES_DB_PORT=5432
POSTGRES_DB_NAME=mydb
POSTGRES_DB_USER=postgres
POSTGRES_DB_PASSWORD=password
```

---

## 🔍 Troubleshooting

### Issue 1: "AttributeError: 'Server' object has no attribute 'define_tool'"
**Cause:** Using incorrect MCP decorator syntax.  
**Solution:** Use `@server.list_tools()` and `@server.call_tool()` instead of `@server.define_tool`.

### Issue 2: "password authentication failed for user 'postgres'"
**Cause:** Incorrect PostgreSQL password.  
**Solution:** Update `PG_PASSWORD` in `server.py` with your actual PostgreSQL password.

### Issue 3: "ModuleNotFoundError: No module named 'django'"
**Cause:** Using system Python instead of virtual environment Python.  
**Solution:** Ensure `PYTHON_EXEC` points to your Django project's virtual environment Python.

### Issue 4: "database 'XX' does not exist" (uppercase names)
**Cause:** PostgreSQL converts unquoted identifiers to lowercase.  
**Solution:** Server now automatically converts database names to lowercase.

### Issue 5: ".env updated but database name not changed"
**Cause:** Looking for wrong variable name in .env file.  
**Solution:** Ensure your .env uses `POSTGRES_DB_NAME=` (not `POSTGRES_DB=`).

### Issue 6: "Tables not created after migrate"
**Cause:** Environment variables not loaded, or wrong Python executable.  
**Solutions:**
- Verify `PYTHON_EXEC` points to correct virtual environment
- Check `.env` file is loaded and contains correct database name
- Run migrate manually to see detailed errors

---

## 📊 Data Flow Diagram

### Complete Workflow Example

```
User Request: "Create database 'myapp'"
│
├─ VS Code Copilot/Web UI/CLI
│  └─ Sends MCP request to server.py
│
├─ server.py receives call_tool("create_database", {"db_name": "myapp"})
│  │
│  ├─ Step 1: create_database
│  │  ├─ Convert "myapp" → "myapp" (lowercase)
│  │  ├─ Connect to PostgreSQL
│  │  ├─ Execute: CREATE DATABASE myapp;
│  │  └─ Return: "Database myapp created."
│  │
│  ├─ Step 2: enable_hstore
│  │  ├─ Connect to "myapp" database
│  │  ├─ Execute: CREATE EXTENSION IF NOT EXISTS hstore;
│  │  └─ Return: "hstore extension enabled in myapp."
│  │
│  ├─ Step 3: update_env
│  │  ├─ Read /path/to/.env
│  │  ├─ Find: POSTGRES_DB_NAME=olddb
│  │  ├─ Replace with: POSTGRES_DB_NAME=myapp
│  │  ├─ Write back to file
│  │  └─ Return: ".env updated: POSTGRES_DB_NAME=myapp"
│  │
│  └─ Step 4: django("migrate")
│     ├─ Load .env into environment
│     ├─ Execute: /venv/bin/python manage.py migrate
│     │  └─ Django reads POSTGRES_DB_NAME=myapp from env
│     │  └─ Connects to "myapp" database
│     │  └─ Applies migrations
│     └─ Return: Migration output
│
└─ Result returned to user
```

---

## 🎓 Key Concepts

### Model Context Protocol (MCP)
- **Protocol** for AI assistants to interact with tools
- **stdio-based** communication (not HTTP)
- **Async/await** pattern required
- **Tool registration** via `list_tools()`
- **Tool execution** via `call_tool()`

### Why This Architecture?
1. **Separation of Concerns**: Server logic separate from client interfaces
2. **Multiple Interfaces**: Same server, different access methods
3. **Type Safety**: MCP protocol with schema validation
4. **Error Handling**: Comprehensive error reporting
5. **Environment Isolation**: Uses virtual environment Python

### PostgreSQL Naming Rules
- Unquoted identifiers converted to lowercase
- `CREATE DATABASE MyDB` creates `mydb`
- Server automatically handles this conversion

---

## 📝 Tool Reference

### create_database
```json
{
  "name": "create_database",
  "arguments": {
    "db_name": "string (optional, default: sample_project_db)"
  },
  "returns": "Database {db_name} created."
}
```

### enable_hstore
```json
{
  "name": "enable_hstore",
  "arguments": {
    "db_name": "string (optional, default: sample_project_db)"
  },
  "returns": "hstore extension enabled in {db_name}."
}
```

### update_env
```json
{
  "name": "update_env",
  "arguments": {
    "db_name": "string (optional, default: sample_project_db)"
  },
  "returns": ".env updated: POSTGRES_DB_NAME={db_name}"
}
```

### django
```json
{
  "name": "django",
  "arguments": {
    "cmd": "string (required) - Django management command"
  },
  "returns": "Command output (stdout/stderr)"
}
```

**Common Django Commands:**
- `migrate` - Apply database migrations
- `makemigrations` - Create new migrations
- `create_text_search_config` - Custom command
- `update_fixtures` - Custom fixture management
- `runserver` - Start development server

---

## 🤝 Contributing

To extend this server with new tools:

1. Add tool definition in `list_tools()`:
```python
Tool(
    name="my_new_tool",
    description="What it does",
    inputSchema={
        "type": "object",
        "properties": {
            "param1": {"type": "string", "description": "..."}
        },
        "required": ["param1"]
    }
)
```

2. Add tool implementation in `call_tool()`:
```python
elif name == "my_new_tool":
    param1 = arguments.get("param1")
    # Your logic here
    return [TextContent(type="text", text="Result")]
```

---

## 📞 Support

For issues or questions:
1. Check the Troubleshooting section
2. Verify configuration in `server.py`
3. Test with `run_workflow.py` for debugging/automation
4. Check PostgreSQL logs for database issues
5. Check Django logs for Django command issues

---

## 📄 License

This project is part of the Altiushub backend infrastructure.

---

**Last Updated:** December 12, 2025  
**Version:** 1.0.0  
**MCP Protocol Version:** Compatible with MCP SDK latest