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?)
• 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)
• 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

.
├── 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:

   python -m venv .venv
   source .venv/bin/activate
   

2. Install dependencies:

   pip install -r requirements.txt
   

3. Create env file:

   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:

   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)

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.
• Configure your LangGraph runtime to connect to:
  • transport: streamable_http
  • url: http://<host>:8000/mcp

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)

6. Deployment steps (production-oriented)

1. Build image:

   docker build -t commerce-mcp-server:latest .
   

2. Push image to your registry:

   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.