<p align="center">
  <a href="https://linkedin.com/in/wisrovi-rodriguez"><img src="https://img.shields.io/badge/LinkedIn-0077B5?style=for-the-badge&logo=linkedin&logoColor=white" alt="LinkedIn" /></a>
  <a href="https://wisrovi.dev"><img src="https://img.shields.io/badge/Author-wisrovi.dev-111827?style=for-the-badge&logo=google-chrome&logoColor=white" alt="Portal" /></a>
  <a href="https://orcid.org/0009-0005-0710-1861"><img src="https://img.shields.io/badge/ORCID-0009--0005--0710--1861-A6CE39?style=for-the-badge&logo=orcid&logoColor=white" alt="ORCID" /></a>
  <a href="https://opensource.org/licenses/MIT"><img src="https://img.shields.io/badge/License-MIT-yellow.svg?style=for-the-badge" alt="License" /></a>
</p>

# wpostgresql-mcp

MCP Server for WPostgreSQL Architecting — teaches AI agents how to build high-performance PostgreSQL-backed systems with the **wpostgresql** library.

## Features

- **10 Architect Tools**: Blueprints, manual, catalog search, scaffolding, connection validation, pattern generation
- **Multi-Table Architecting**: Teaches `WPostgreSQL([User, Product, Order])` multi-table management, dictionary indexing `db[User]`, attribute access `db.product`, and auto-routing
- **24 Offline Patterns**: Core CRUD, async, multi-table, batch, transaction, pooling, schema sync, wsqlite backup, and Forensic Audit (`ForensicModel` soft delete & status=99)
- **Scaffolding Generator**: Deploys full project structure with config, models, repositories, migrations, main.py
- **Live Connection Validation**: Tests PostgreSQL connectivity via psycopg 3 and returns health status
- **Pattern-based Generation**: Generates starter projects from catalog patterns

## Relevant Technologies & Key Libraries

- **Model Context Protocol (MCP)**: `mcp[cli]>=1.0.0` for AI agent interaction
- **Python 3.10+**: Type hints and async I/O
- **wpostgresql**: Type-safe PostgreSQL ORM with Pydantic integration and `ForensicModel` support
- **Pydantic v2**: Data model validation and schema generation

## Installation

```bash
# Clone and install
cd wpostgresql-mcp
pip install -e .
```

Or use the installer:

```bash
chmod +x installer.sh
./installer.sh
```

## Quick Start

```bash
# Run the MCP server in stdio mode (for agent integration)
wpostgresql-mcp run

# Get configuration for your AI agent
wpostgresql-mcp config

# Start as background SSE server
wpostgresql-mcp start
```

## Available Tools

| Tool | Description |
|------|-------------|
| `get_wpostgresql_architect_blueprints` | Complete CRUD, async, batch, transaction, pool, sync, query builder, SQLite backup, and Forensic Audit (`ForensicModel`) examples |
| `get_wpostgresql_architect_manual` | Expert rules, module map, data structure selection guide |
| `search_wpostgresql_pattern` | Search 22+ PostgreSQL patterns in official/community catalogs |
| `deploy_wpostgresql_scaffolding` | Generate full project structure with config/models/repos/migrations |
| `validate_postgresql_connection` | Live health check against a running PostgreSQL instance |
| `generate_from_pattern` | Generate project + example from a specific catalog pattern |

## Agent Integration

### Gemini CLI

```bash
gemini mcp add wpostgresql-mcp python3 -m wpostgresql_mcp.server run
```

### Claude Desktop / Cursor

```json
{
  "mcpServers": {
    "wpostgresql-mcp": {
      "command": "python3",
      "args": ["-m", "wpostgresql_mcp.server", "run"]
    }
  }
}
```

### OpenCode

Add to `.opencode/config.json`:

```json
{
  "mcpServers": {
    "wpostgresql-mcp": {
      "command": "python3",
      "args": ["-m", "wpostgresql_mcp.server", "run"]
    }
  }
}
```

## Commands

| Command | Description |
|---------|-------------|
| `wpostgresql-mcp run` | Run in stdio mode (default) |
| `wpostgresql-mcp run-sse` | Run in SSE mode |
| `wpostgresql-mcp start` | Start as background SSE server |
| `wpostgresql-mcp stop` | Stop background server |
| `wpostgresql-mcp config` | Print/save JSON configuration |
| `wpostgresql-mcp help` | Show help |

---

*Generated by WPostgreSQL MCP by **wisrovi***

---

## 👤 Autor & Afiliación Oficial

* **William Steve Rodriguez Villamizar (Wisrovi)**
* **Cargo:** Principal AI Engineer & Applied AI Solutions Architect | Scientific Researcher
* 📧 **Email:** [wisrovi.rodriguez@gmail.com](mailto:wisrovi.rodriguez@gmail.com)
* 🌐 **Portal Oficial:** [wisrovi.dev](https://wisrovi.dev)
* 💼 **LinkedIn:** [wisrovi-rodriguez](https://www.linkedin.com/in/wisrovi-rodriguez/)
* 🆔 **ORCID:** [0009-0005-0710-1861](https://orcid.org/0009-0005-0710-1861)
* 📦 **PyPI:** [pypi.org/user/wisrovi/](https://pypi.org/user/wisrovi/)
* 🐙 **GitHub:** [@wisrovi](https://github.com/wisrovi)