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

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

• 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

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.