# 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