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):

{
  "mcpServers": {
    "vertica-local": {
      "command": "uvx",
      "args": ["mcp-vertica", "--transport", "stdio"]
    }
  }
}

For remote HTTP (beta):

{
  "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

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.