# Vertica MCP on AWS — Two-stack CI/CD
This repository provisions two isolated stacks on AWS:
* **DB stack** (`deploy/db/**`) — Spot `t3.xlarge` Amazon Linux 2023 instance running Vertica CE via Docker (port 5433).
* **MCP stack** (`deploy/mcp/**`, `src/**`, `tests/**`, `Dockerfile.mcp`) — Spot `t3.small` instance that pulls the MCP FastAPI server image from ECR and exposes port 8000.
Each stack has its own GitHub Actions workflow with dedicated remote Terraform state, fail-fast credential checks, and post-deploy smoke tests via AWS Systems Manager. Pushes scoped to one stack never trigger the other.
## Repository layout
```
.
├─ deploy/
│ ├─ db/
│ │ ├─ README.md
│ │ └─ terraform/
│ │ ├─ backend-bootstrap.sh
│ │ ├─ main.tf
│ │ ├─ outputs.tf
│ │ ├─ user_data_db.sh
│ │ └─ variables.tf
│ └─ mcp/
│ ├─ README.md
│ └─ terraform/
│ ├─ backend-bootstrap.sh
│ ├─ main.tf
│ ├─ outputs.tf
│ ├─ user_data_mcp.sh
│ └─ variables.tf
├─ .github/workflows/
│ ├─ db-apply-destroy.yml
│ └─ mcp-apply-destroy.yml
├─ src/mcp_vertica/
│ ├─ __init__.py
│ └─ server.py
├─ Dockerfile.mcp
├─ tests/
│ ├─ test_health.py
│ └─ test_sql_rendering.py
├─ PROMPTS.md
├─ pyproject.toml
├─ uv.lock
└─ docker-compose.yml
```
## Required repository secrets
Set these under **Settings → Secrets and variables → Actions** before running any workflow:
* `AWS_REGION` (default `ap-south-1`)
* `AWS_ACCOUNT_ID`
* Either OIDC: `AWS_ROLE_TO_ASSUME` and `AWS_OIDC_ROLE_SESSION_NAME`, or static keys: `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY`
Optional:
* `ALLOWED_CIDRS` — comma-separated IPv4 CIDRs (e.g. `"49.37.x.x/32","122.166.x.x/32"`) to open ports 5433/8000 only to those networks
* `MCP_HTTP_TOKEN` — if set, the MCP HTTP server requires `Authorization: Bearer <token>`
## Workflows
### DB Stack (apply/destroy)
* Triggered by pushes to `deploy/db/**` or manual `workflow_dispatch`.
* Bootstraps the Terraform backend (`vertica-mcp-tf-<account>-<region>` bucket + DynamoDB lock table).
* Applies Terraform with defaults: Spot `t3.xlarge`, 50 GiB gp3 volume, Vertica CE image `957650740525.dkr.ecr.ap-south-1.amazonaws.com/vertica-ce:v1.0`.
* Runs `/usr/local/bin/db-smoke.sh` through SSM (executes `SELECT NOW();` via `vsql`).
* Job summary prints the public IP and a copy/paste connection string (`HOST=<ip> PORT=5433 USER=dbadmin DB=VMart`).
Destroy by dispatching the workflow with `action=destroy`.
### MCP Stack (apply/destroy + build/push)
* Triggered by pushes to `deploy/mcp/**`, `src/**`, `tests/**`, or `Dockerfile.mcp`.
* Runs `uv sync --frozen`, `ruff`, and `pytest` before touching AWS.
* Builds `Dockerfile.mcp`, pushes to `mcp-vertica` ECR repo, then applies Terraform for the MCP EC2 instance.
* Terraform reads the DB stack’s remote state to populate `DB_HOST` and writes `/opt/mcp.env` for the container.
* Smoke test hits `GET /healthz` via SSM; summary prints the MCP URL (`http://<ip>:8000`).
Destroy by dispatching with `action=destroy`.
## MCP server
The MCP FastAPI server (`src/mcp_vertica/server.py`) supports both stdio and HTTP transports. Environment variables at startup:
* `DB_HOST`, `DB_PORT` (default `5433`), `DB_USER`, `DB_PASSWORD`, `DB_NAME`
* Optional `MCP_HTTP_TOKEN` enabling bearer-token auth
Endpoints:
* `GET /healthz`
* `POST /api/render`
* `POST /api/query`
For Claude Desktop (local stdio):
```json
{
"mcpServers": {
"vertica-local": {
"command": "uvx",
"args": ["mcp-vertica", "--transport", "stdio"]
}
}
}
```
For remote HTTP (beta):
```json
{
"mcpServers": {
"vertica-remote": {
"command": "npx",
"args": ["-y", "mcp-remote", "http://<MCP-PUBLIC-IP>:8000/sse"],
"env": {
"AUTH_HEADER": "Authorization: Bearer <MCP_HTTP_TOKEN>"
}
}
}
}
```
## Local development
```bash
uv sync --frozen
uv run ruff check
uv run pytest -q
MCP_HTTP_TOKEN=local DB_HOST=localhost DB_PORT=5433 DB_USER=dbadmin DB_NAME=VMart \
docker compose up --build
./scripts/wait-for-port.py localhost 8000 --timeout 120
curl -H "Authorization: Bearer local" http://127.0.0.1:8000/healthz
```
Destroy AWS resources when idle to minimize costs; both stacks default to Spot instances with security-group ingress restricted to `ALLOWED_CIDRS`.
