VTION E-Commerce MCP Server

A Model Context Protocol (MCP) server providing secure, read-only access to VTION e-commerce analytics data. Built with FastAPI and PostgreSQL, supporting both MCP native protocol and REST API.

Features

MCP Protocol Support: Full implementation of Model Context Protocol for AI agent integration
Multiple Transport Modes:
- FastMCP (stdio) for direct MCP client integration
- HTTP/SSE for web-based clients
- REST API for traditional HTTP clients
Secure by Design: Read-only access, query validation, connection pooling
Progressive Context Loading: Efficient data discovery with 4 context levels
Parallel Query Execution: Multiple queries execute concurrently for optimal performance
Auto-limiting: Raw queries limited to 5 rows, aggregated queries to 1,000 rows
Rich Query Tools: Schema inspection, sample data, flexible querying

Architecture

VTION-ECOM/ ├── vtion_ecom_mcp.py # Main MCP server with FastMCP ├── server.py # Standalone HTTP/SSE server ├── requirements.txt # Python dependencies ├── .env.example # Configuration template ├── .gitignore # Git ignore rules └── README.md # This file

Quick Start

1. Installation

# Clone the repository git clone <your-repo-url> cd VTION-ECOM # Install dependencies pip install -r requirements.txt

2. Configuration

# Copy environment template cp .env.example .env # Edit .env with your database credentials nano .env

Required Environment Variables:

DATASET_1_NAME=vtion_ecom DATASET_1_DESC=VTION E-commerce platform analytics data DATASET_1_CONNECTION=postgresql://postgres:PASSWORD@host:port/db?sslmode=require DATASET_1_DICTIONARY={"table1":"desc","table2":"desc"}

3. Run the Server

Option A: FastMCP Mode (for MCP clients)

python vtion_ecom_mcp.py

Option B: HTTP/SSE Mode (for web clients)

python server.py # Server runs on http://localhost:10000

Option C: Production Deployment

uvicorn server:app --host 0.0.0.0 --port 10000 --workers 4

Database Configuration

The MCP server connects to your Supabase PostgreSQL database. The connection string is already configured in .env.example:

postgresql://postgres:Vtion%402023%23@db.yjiotntmzaukbmgxeqvq.supabase.co:5432/postgres?sslmode=require

Important: The password is URL-encoded (Vtion@2023# → Vtion%402023%23)

Expected Schema

The server works with any PostgreSQL schema. Common e-commerce tables include:

products - Product catalog with inventory
orders - Order history and transactions
customers - Customer profiles and demographics
cart_items - Shopping cart data
user_sessions - User engagement metrics

The server will automatically discover your schema at runtime.

Usage

MCP Tools

The server provides 5 MCP tools:

1. `get_context(level, dataset_id?)`

Progressive context loading:

Level 0: Global rules and guidelines
Level 1: List all datasets
Level 2: Schema for specific dataset (requires dataset_id)
Level 3: Full details with sample data (requires dataset_id)

# Get global rules get_context(level=0) # List all datasets get_context(level=1) # Get schema for dataset 1 get_context(level=2, dataset_id=1) # Get full details with samples get_context(level=3, dataset_id=1)

2. `list_available_datasets()`

List all configured datasets with metadata.

list_available_datasets()

3. `get_dataset_schema(dataset_id)`

Get complete schema for a dataset (equivalent to get_context(level=2)).

get_dataset_schema(dataset_id=1)

4. `query_dataset(dataset_id, query, response_format?)`

Execute SQL SELECT queries on a dataset.

# Simple query query_dataset( dataset_id=1, query="SELECT * FROM products WHERE category = 'Electronics' LIMIT 10" ) # Aggregated query query_dataset( dataset_id=1, query="SELECT category, COUNT(*) as count, AVG(price) as avg_price FROM products GROUP BY category" ) # JSON response format query_dataset( dataset_id=1, query="SELECT * FROM orders WHERE status = 'completed'", response_format="json" )

Parallel Execution: Call query_dataset() multiple times - they execute in parallel automatically!

# These three queries execute concurrently: query_dataset(1, "SELECT category, COUNT(*) FROM products GROUP BY category") query_dataset(1, "SELECT status, COUNT(*) FROM orders GROUP BY status") query_dataset(1, "SELECT gender, COUNT(*) FROM customers GROUP BY gender")

5. `get_dataset_sample(dataset_id, table_name, limit?)`

Get sample rows from a specific table.

get_dataset_sample( dataset_id=1, table_name="products", limit=20 )

REST API Endpoints

When running server.py, these HTTP endpoints are available:

Health Check

curl http://localhost:10000/ # or curl http://localhost:10000/health

Response:

{ "status": "ok", "service": "VTION E-Commerce MCP Server", "datasets": 1, "version": "1.0", "mcp_endpoint": "/mcp", "mcp_protocol_version": "2025-06-18" }

List Datasets

curl http://localhost:10000/datasets

Execute Query

curl -X POST http://localhost:10000/query \ -H "Content-Type: application/json" \ -d '{ "dataset_id": 1, "query": "SELECT * FROM products LIMIT 5" }'

MCP Protocol Endpoint

POST /mcp

Implements full MCP protocol over HTTP with JSON-RPC 2.0.

Security

Query Restrictions

Only SELECT allowed: INSERT, UPDATE, DELETE, DROP, etc. are blocked
Automatic limits: Raw queries max 5 rows, aggregated queries max 1,000 rows
Connection pooling: Prevents resource exhaustion
Timeout protection: 60-second query timeout

Authentication

⚠️ Important: This server does not include authentication. For production:

Add authentication middleware (JWT, API keys, OAuth)
Use environment-specific credentials
Enable database row-level security (RLS)
Run behind a reverse proxy (nginx, Cloudflare)

Development

Testing Connection

# Test database connectivity python -c " import asyncio import asyncpg async def test(): conn = await asyncpg.connect('postgresql://...') print('Connected!') tables = await conn.fetch('SELECT table_name FROM information_schema.tables WHERE table_schema = \\'public\\'') print('Tables:', [t['table_name'] for t in tables]) await conn.close() asyncio.run(test()) "

Adding Multiple Datasets

Edit .env to add more datasets:

# Dataset 1 DATASET_1_NAME=vtion_ecom DATASET_1_CONNECTION=postgresql://... DATASET_1_DESC=Main e-commerce data DATASET_1_DICTIONARY={"products":"Product catalog"} # Dataset 2 DATASET_2_NAME=analytics DATASET_2_CONNECTION=postgresql://... DATASET_2_DESC=Analytics warehouse DATASET_2_DICTIONARY={"events":"User events"}

Customizing Business Logic

The server inherits business logic from indian-analytics-mcp:

Query validation: Modify query_dataset() in vtion_ecom_mcp.py
Response formatting: Update format_markdown_table() helper
Add custom tools: Use @mcp.tool() decorator
Schema customization: Edit DATASET_1_DICTIONARY in .env

Deployment

Render

Create new Web Service
Connect GitHub repository
Set build command: pip install -r requirements.txt
Set start command: python server.py
Add environment variables from .env

Docker

FROM python:3.11-slim WORKDIR /app COPY requirements.txt . RUN pip install --no-cache-dir -r requirements.txt COPY . . ENV PORT=10000 EXPOSE 10000 CMD ["python", "server.py"]

docker build -t vtion-mcp . docker run -p 10000:10000 --env-file .env vtion-mcp

Railway / Fly.io

Both support automatic deployment from GitHub with environment variables.

Troubleshooting

Connection Issues

# Test database connection psql "postgresql://postgres:Vtion%402023%23@db.yjiotntmzaukbmgxeqvq.supabase.co:5432/postgres?sslmode=require"

No Datasets Found

Check environment variables are set:

env | grep DATASET_

Query Errors

Verify table names with get_dataset_schema()
Check column names match schema
Ensure query is valid SQL SELECT statement

Import Errors

pip install --upgrade -r requirements.txt

Credits

Based on indian-analytics-mcp by @adityac7.

License

MIT License - see LICENSE file for details

Support

For issues and questions:

GitHub Issues: /issues
Email: support@vtion.com