VTION E-Commerce MCP Server

# 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 ```bash # Clone the repository git clone <your-repo-url> cd VTION-ECOM # Install dependencies pip install -r requirements.txt ``` ### 2. Configuration ```bash # Copy environment template cp .env.example .env # Edit .env with your database credentials nano .env ``` **Required Environment Variables:** ```env 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)** ```bash python vtion_ecom_mcp.py ``` **Option B: HTTP/SSE Mode (for web clients)** ```bash python server.py # Server runs on http://localhost:10000 ``` **Option C: Production Deployment** ```bash 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) ```python # 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. ```python list_available_datasets() ``` #### 3. `get_dataset_schema(dataset_id)` Get complete schema for a dataset (equivalent to `get_context(level=2)`). ```python get_dataset_schema(dataset_id=1) ``` #### 4. `query_dataset(dataset_id, query, response_format?)` Execute SQL SELECT queries on a dataset. ```python # 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! ```python # 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. ```python 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 ```bash curl http://localhost:10000/ # or curl http://localhost:10000/health ``` Response: ```json { "status": "ok", "service": "VTION E-Commerce MCP Server", "datasets": 1, "version": "1.0", "mcp_endpoint": "/mcp", "mcp_protocol_version": "2025-06-18" } ``` #### List Datasets ```bash curl http://localhost:10000/datasets ``` #### Execute Query ```bash 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 ```bash 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: 1. Add authentication middleware (JWT, API keys, OAuth) 2. Use environment-specific credentials 3. Enable database row-level security (RLS) 4. Run behind a reverse proxy (nginx, Cloudflare) ## Development ### Testing Connection ```bash # 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: ```env # 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](https://github.com/adityac7/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 1. Create new Web Service 2. Connect GitHub repository 3. Set build command: `pip install -r requirements.txt` 4. Set start command: `python server.py` 5. Add environment variables from `.env` ### Docker ```dockerfile 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"] ``` ```bash 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 ```bash # 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: ```bash 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 ```bash pip install --upgrade -r requirements.txt ``` ## Credits Based on [indian-analytics-mcp](https://github.com/adityac7/indian-analytics-mcp) by [@adityac7](https://github.com/adityac7). ## License MIT License - see LICENSE file for details ## Support For issues and questions: - GitHub Issues: <your-repo-url>/issues - Email: support@vtion.com