# GCP Postgres MCP Tools

FastAPI app that connects to your GCP PostgreSQL database and exposes query tools over REST and MCP.

## Setup

```bash
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
cp .env.example .env
```

Put your Cloud SQL URL in `.env`. If you already use a local Cloud SQL Auth Proxy, point at that host and port:

```env
DATABASE_URL=postgresql+asyncpg://USERNAME:PASSWORD@127.0.0.1:5439/DATABASE_NAME
USERS_TABLE=tbl_users
USERS_ORDER_COLUMN=date_joined
```

This app is read-only against Cloud SQL. Every production session sets `default_transaction_read_only` and `SET TRANSACTION READ ONLY`. REST tools are GET-only. `POST` is allowed only on `/mcp` (protocol) and `/agent/chat` (the agent loop). Agent traces are written to a **separate local SQLite file**, never to `evolve_production`.

`get_last_10_users` uses `tbl_users`. `get_last_5_user_activities` uses `tbl_user_activities`. Password columns are never returned.

For production, also use a Cloud SQL user that has `SELECT` only.

## Run

```bash
uvicorn app.main:app --reload --port 8000
```

- Health: `GET /health`
- Docs: `http://127.0.0.1:8000/docs`
- MCP: `http://127.0.0.1:8000/mcp`
- Agent: `POST /agent/chat`
- Traces: `GET /agent/traces` and `GET /agent/traces/{trace_id}`

## Agentic loop and traces

The agent is a ReAct loop: think → call a read-only tool → observe → repeat → final answer.

```bash
curl -s http://127.0.0.1:8000/agent/chat \
  -H 'Content-Type: application/json' \
  -d '{"message":"Get the last 10 users"}'
```

Each run stores a structured trace (`user_input`, LLM tool choices, tool results, final answer) in `data/traces.db`.

The agent uses the **Gemini Developer API** (Google AI Studio key), not Vertex AI and not OpenAI.

```env
GEMINI_API_KEY=AIza...
GEMINI_MODEL=gemini-3.6-flash
```

Get a key at [aistudio.google.com/apikey](https://aistudio.google.com/apikey). `GOOGLE_API_KEY` also works.

These are **not** the same as a Vertex AI key. Gemini keys start with `AIza` and do not need a GCP project or region.

Optional: **LangSmith** via `LANGSMITH_TRACING=true` and `LANGSMITH_API_KEY`.

Without `GEMINI_API_KEY`, `/agent/chat` returns 503. REST and MCP read tools still work.

Each chat request is capped so the agent cannot loop or retry forever:

| Setting | Default | What it stops |
| --- | --- | --- |
| `AGENT_MAX_MODEL_RETRIES` | 2 | Gemini HTTP retries |
| `AGENT_MAX_TOOL_RETRIES` | 1 | Tool/DB retries |
| `AGENT_MAX_TOOL_CALLS` | 5 | Tools per request |
| `AGENT_MAX_MODEL_CALLS` | 6 | Model turns per request |
| `AGENT_RECURSION_LIMIT` | 12 | Hard LangGraph step stop |

## Tools

| REST | MCP tool | What it does |
| --- | --- | --- |
| `GET /tools/get_last_10_users` | `get_last_10_users` | Latest 10 users |
| `GET /tools/get_user_by_id/{user_id}` | `get_user_by_id` | One user by id |
| `GET /tools/get_last_5_user_activities/{user_id}` | `get_last_5_user_activities` | Latest 5 activities |
| `GET /agent/traces/{trace_id}` | `get_trace` | Full agent audit trace |
| `GET /agent/traces` | `get_recent_traces` | Recent agent traces |

Password and hash columns are never returned.

## Cursor MCP config

```json
{
  "mcpServers": {
    "gcp-postgres-tools": {
      "url": "http://127.0.0.1:8000/mcp"
    }
  }
}
```