# MCP Commerce Server (PostgreSQL + Hugging Face)

This project provides a **deployment-ready MCP server** that can be invoked from a LangGraph orchestration flow.  
It accepts natural-language input, uses an open-source Hugging Face model to derive query intent, and safely fetches data from PostgreSQL.

## What is implemented

- MCP tools:
  - `query_commerce(query, user_context?, limit?)`
  - `get_product_price(product_name, limit?)`
  - `get_user_orders(user_id?, email?, limit?)`
  - `calculate_two_numbers(a, b, operation?)` where operation supports add/subtract
- PostgreSQL schema for:
  - Product prices (`products`)
  - Users (`users`)
  - Orders (`orders`, `order_items`)
- Hugging Face model integration (open-source text-to-text model default: `google/flan-t5-base`)
- SQL-injection-safe query layer (no model SQL execution; only parameterized, pre-approved SQL templates)
- OpenTelemetry tracing and structured logging for MCP tool flow, intent parsing, DB connection, and query execution
- Docker + docker-compose setup for deployment

## Why this is SQL-injection safe

1. LLM output is treated as **intent only**, not executable SQL.
2. The server maps intent to a **fixed set of SQL templates**.
3. All runtime values are passed as **query parameters** via psycopg (`%s` placeholders).
4. `limit` is bounded server-side to prevent abuse.

## Project structure

```text
.
├── db/init.sql
├── docker-compose.yml
├── Dockerfile
├── examples/langgraph_client.py
├── pyproject.toml
├── requirements.txt
└── src/mcp_commerce_server
    ├── config.py
    ├── db.py
    ├── hf_intent.py
    ├── main.py
    ├── query_service.py
    └── server.py
```

## 1. Local development setup

1. Create and activate a virtual environment:
   ```bash
   python -m venv .venv
   source .venv/bin/activate
   ```
2. Install dependencies:
   ```bash
   pip install -r requirements.txt
   ```
3. Create env file:
   ```bash
   cp .env.example .env
   ```
4. Start PostgreSQL and seed schema:
   - Option A: Use your own DB and run `db/init.sql`.
   - Option B: Use docker-compose (recommended below).

## 2. Run with docker-compose (deployment-ready local)

1. Ensure `.env` exists and has `HF_API_KEY`.
2. Start services:
   ```bash
   docker compose up --build
   ```
3. MCP server is exposed at:
   - `http://localhost:8000/mcp` (streamable-http transport)

## 3. Run MCP server directly (stdio for local MCP clients)

```bash
python -m mcp_commerce_server.main
```

Set `MCP_TRANSPORT=stdio` in `.env` for this mode.

## 4. LangGraph orchestration usage

- See `examples/langgraph_client.py` for end-to-end invocation.
- The example now connects to two MCP servers:
  - `commerce` at `MCP_COMMERCE_URL` (default `http://localhost:8000/mcp`)
  - `knowledge` at `MCP_KNOWLEDGE_URL` (default `http://localhost:8001/mcp`)
- It sends different prompts so the model routes to different tools (commerce query tools, `calculate_two_numbers`, and knowledge tools).

## 5. Environment variables

| Variable | Description | Required |
|---|---|---|
| `POSTGRES_HOST` | PostgreSQL host | Yes |
| `POSTGRES_PORT` | PostgreSQL port | Yes |
| `POSTGRES_DB` | Database name | Yes |
| `POSTGRES_USER` | Database user | Yes |
| `POSTGRES_PASSWORD` | Database password | Yes |
| `HF_API_KEY` | Hugging Face API key | Yes (for model inference) |
| `HF_MODEL_ID` | Open-source HF model id | No (`google/flan-t5-base`) |
| `MCP_TRANSPORT` | `stdio` or `streamable-http` | No (`stdio`) |
| `MCP_HOST` | HTTP host for streamable MCP | No (`0.0.0.0`) |
| `MCP_PORT` | HTTP port for streamable MCP | No (`8000`) |
| `LOG_LEVEL` | Python logging level | No (`INFO`) |
| `OTEL_SERVICE_NAME` | OpenTelemetry service name | No (`commerce-postgres-mcp`) |

## 6. Deployment steps (production-oriented)

1. Build image:
   ```bash
   docker build -t commerce-mcp-server:latest .
   ```
2. Push image to your registry:
   ```bash
   docker tag commerce-mcp-server:latest <registry>/<namespace>/commerce-mcp-server:latest
   docker push <registry>/<namespace>/commerce-mcp-server:latest
   ```
3. Provision managed PostgreSQL (AWS RDS / Azure Database / Cloud SQL).
4. Apply `db/init.sql` once (or convert to migration pipeline).
5. Deploy container to your platform (Kubernetes, ECS, App Service, Cloud Run, etc.) with:
   - `MCP_TRANSPORT=streamable-http`
   - DB connection variables
   - `HF_API_KEY` secret
6. Expose HTTP endpoint and allow only trusted callers (VPC/ingress ACL, auth gateway, or service mesh policy).
7. Configure LangGraph to call deployed MCP URL.

## 7. Hardening recommendations before go-live

1. Use DB least-privilege user (read-only if writes are unnecessary).
2. Add request auth in front of MCP endpoint (API gateway/JWT/mTLS).
3. Add query logging + tracing.
4. Add circuit breakers/timeouts for HF and DB.
5. Add schema migrations (Alembic/Flyway/liquibase) for controlled releases.