mcp-db-agent: Instructions file for Codex

AGENTS.md

This file provides guidance to Codex (Codex.ai/code) when working with code in this repository.

What This Project Is

An MCP (Model Context Protocol) server that exposes PostgreSQL databases as natural-language queryable endpoints. Any MCP client (Codex Desktop, Cursor, VS Code Copilot) can connect and ask questions in plain English. The server introspects the schema, generates SQL via an LLM, validates it for safety, executes it, and returns structured JSON — with a self-correction retry loop if execution fails.

Two deployment modes:

• Stdio / single-user (uv run src/server.py): local dev, Codex Desktop, VS Code Copilot.
• Hosted / multi-tenant (uvicorn src.app:app): multiple users register their own database, receive an API key, and connect to a shared HTTP endpoint.

Commands

Package manager: uv only — do not use pip or Poetry.

# Install all dependencies (including dev)
uv sync

# Run all unit tests (no LLM calls, no DB required)
uv run pytest -m "not integration"

# Run a single test file
uv run pytest tests/test_sql_validator.py

# Run a single test by name
uv run pytest tests/test_sql_executor.py::test_execute_aggregation_count

# Run integration tests (needs API keys in .env and a seeded demo.db)
uv run pytest -m integration

# Lint and format
uv run ruff check .
uv run ruff format .

# Type check
uv run mypy src --ignore-missing-imports

# Run the MCP server (stdio transport for Codex Desktop)
uv run src/server.py

# Run hosted HTTP server (multi-tenant)
uv run uvicorn src.app:app --reload

# Debug with MCP Inspector (requires Node.js)
npx @modelcontextprotocol/inspector uv run src/server.py

# Seed the local demo database
uv run scripts/seed_demo_db.py

# Database migrations (Alembic)
uv run alembic upgrade head          # apply all pending migrations
uv run alembic check                 # verify schema is at head
uv run alembic downgrade -1          # roll back one migration

Architecture

Stdio mode (single-user, local)