MCP for GBL

A secure, multi-tenant Model Context Protocol (MCP) server for accessing brand-specific Postgres databases. This server supports both local execution and authenticated remote access via HTTP/SSE.

🚀 Recent Updates & Enhancements (Past 10 Days)

🛡️ Infrastructure, Security & Scaling

• EC2 Stabilization & Capacity Planning: Successfully scaled the instance capacity and implemented swap memory to manage high concurrent loads. Optimized database connection pooling (min_size=3, max_size=7) to permanently resolve ECONNREFUSED timeout errors.

• Enhanced Proxy & SSL: Configured dual DuckDNS subdomains and optimized Nginx proxy settings to robustly support Server-Sent Events (SSE) for Claude connectivity with secure SSL certificate management.

• Multi-Tenant Key Management: Hardened the brand analytical server and configured lifecycle management for authentication across all active brand servers (Chumbak, Pepe, Cee18) to ensure consistent data reporting standards.

📊 Analytical Expansions (Chumbak, CEE18, Pepe)

• Amazon SP & Advertising Data: Integrated Amazon Seller Partner (SP) data (orders, traffic, FBA inventory). Mapped Amazon (SB/SD/SP) and Myntra CPC advertising reports into chumbak_config.json for granular performance insights across ad placement, product, and search-term levels.

• Quick-Commerce Strict Segmentation: Enforced immutable brand filters for Quick-Commerce feeds (Blinkit, Zepto, Instamart) and successfully routed Blinkit data via the DataWarehouse layer. Excluded Q-commerce D2C channels from primary sales orders to force the analytical engine to rely exclusively on dedicated raw portal feeds.

• B2B & EBO Channel Refinement: Updated B2B channel exclusion lists (ETRADE, R K WorldInfocom, SHANTI COSTUMES) and excluded WONDERSOFT/Mall-based EBO channels from generic saleorders to ensure reliance on the dedicated ebo_sales raw feed. Established standardized metric rules (e.g., online unit quantity as COUNT(*)).

• Logistics & Offline Mappings:

  • Integrated Bharatiya Mall offline data mapping via Frangipani EBO feeds.

  • Integrated Clickpost logistics tracking (orders and returns).

  • Addressed POS export formatting issues with dynamic EAN/MRP column extraction.

📝 Brand Configuration Structure

To add a new brand to the MCP server, create a JSON file inside src/brands/ (e.g., brand_config.json). The JSON file dictates the specific instructions, schema context, and access rules for the AI analyst.

General Structure (brand_config.json):

{
  "brand_id": "unique_brand_identifier",
  "display_name": "Brand Business Insights",
  "tool_name": "brand_insights",
  "allowed_db_user": [
    "brand_db_user",
    "mcp_superuser"
  ],
  "description": "Short description of the AI persona and what it analyzes.",
  "specific_instructions": "[[[ PROFESSIONAL COMMUNICATION GUIDELINES ]]]\n- Instructions on persona and tone.\n\n[[[ ANALYST LOGIC & CASTING RULES ]]]\n- Date parsing, casting rules, logic exceptions.\n\n[[[ MANDATORY FILTERS ]]]\n- Rules for excluding specific channels or testing environments.\n\n[[[ PER-TABLE COLUMN REFERENCE ]]]\n- Detailed descriptions of tables, join keys, and specific column logic.",
  "schema_details": "[[[ SCHEMA OVERVIEW ]]]\n- High-level list of tables and their business purpose to help the AI write accurate SQL.",
  "common_questions": [
    "Top 10 selling SKUs for last month?",
    "Offline vs Online revenue split?"
  ]
}

🔐 Authentication & Security

This server uses Bearer Token Authentication. Access to the /sse and /messages endpoints requires a valid API key with the gbl- prefix.

🛠️ Environment Activation

Before running any commands, ensure your virtual environment is active:

macOS / Linux / EC2:

source .venv/bin/activate

Windows (Local):

.\.venv\Scripts\activate

Key Management CLI

Manage your keys locally or on EC2 using the built-in auth module:

6. Manage Multi-Tenant API Keys (Admin UI & API)

To provision API keys and map them to brand permissions, we host an internal Admin Dashboard and REST API. This dashboard automatically fetches the underlying database credentials from the .env file on the server and attaches them to an end_user token.

This API handles sensitive token generation, so it should only be accessed from localhost on your EC2 instance (binds to :8001).

Option A: Access the UI securely via SSH Tunneling (Recommended)

You can build a secure tunnel from your local PC directly to your EC2 instance so you can interact with the Admin Dashboard UI in your local browser:

# Run this on your local Windows PC
ssh -i "path/to/your/key.pem" -L 8001:127.0.0.1:8001 ubuntu@YOUR_EC2_IP

Now, you can interact with the UI right from your local machine: http://127.0.0.1:8001/admin From the UI, you can select multiple brands at once to provision under a single end-user email! An email with the token will be automatically sent to the user.

Option B: Use the CLI on EC2

To start the Admin API on EC2:

python3 -m src.admin_api

With the Admin API running, you can create a new key via a direct curl request:

curl -X POST http://127.0.0.1:8001/keys/generate \
     -H "Content-Type: application/json" \
     -d '{"end_user": "analyst@example.com", "brand_names": ["chumbak", "imara"]}'

(The response will contain the unified api_key assigned to analyst@example.com.)

Other utility endpoints:

• List tenants: curl http://127.0.0.1:8001/keys

• Revoke tenant access for a specific brand: curl -X DELETE http://127.0.0.1:8001/keys?end_user=analyst@example.com&db_user=chumbak_user

🖥️ EC2 Deployment (Persistence with systemd)

SSH Access

To access the EC2 instance remotely:

ssh ubuntu@13.234.225.106 -i mcp_server_gbl.pem

For production, we use systemd to ensure the server starts automatically on reboot and restarts if it crashes.

1. Project Setup

git clone https://github.com/intern-analytics/MCPforGBL.git
cd MCPforGBL
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

2. Configuration

Create a .env file for database credentials (gitignored):

nano .env
# Add DB_USER, DB_PASS, DB_HOST, etc.

3. Create the System Service

Create a service file:

sudo nano /etc/systemd/system/mcp-server.service

Paste the following (adjust paths if necessary):

[Unit]
Description=Brand MCP FastAPI Server
After=network.target

[Service]
User=ubuntu
WorkingDirectory=/home/ubuntu/projects/MCPforGBL
ExecStart=/home/ubuntu/projects/MCPforGBL/.venv/bin/python -m src.server2
Restart=always
RestartSec=3

[Install]
WantedBy=multi-user.target

4. Enable and Start

sudo systemctl daemon-reload
sudo systemctl enable mcp-server
sudo systemctl start mcp-server
sudo systemctl status mcp-server

🤖 Connecting to Claude

Option A: Using Claude.ai (Web App)

Because this server is publicly exposed via Nginx and protected with a Let's Encrypt HTTPS certificate, you can connect directly in your browser.

1. Go to Claude.ai Settings -> Developer / Connectors.

2. Click Add custom connector.

3. Paste your secure URL: https://mcpforgbl.duckdns.org/sse?token=gbl-YOUR_KEY_HERE

4. Connect and query!

Option B: Using Claude Desktop App (HTTPS)

For Windows:

Update your %APPDATA%\Claude\claude_desktop_config.json.

{
  "mcpServers": {
    "gbl-data-lake": {
      "command": "C:\\PROGRA~1\\nodejs\\npx.cmd",
      "args": [
        "-y",
        "mcp-remote",
        "https://mcpforgbl.duckdns.org/sse?token=gbl-YOUR_KEY_HERE"
      ]
    }
  }
}

For macOS:

Update your ~/Library/Application Support/Claude/claude_desktop_config.json.

{
  "mcpServers": {
    "gbl-data-lake": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://mcpforgbl.duckdns.org/sse?token=gbl-YOUR_KEY_HERE"
      ]
    }
  }
}

Option C: Using Claude Desktop App (HTTP IP Address & Bearer Header)

If you prefer to connect directly to the EC2 instance's IP without using the DuckDNS URL or want to pass the token as a header instead of a URL parameter, use this configuration:

{
  "mcpServers": {
    "gbl-data-lake": {
      "command": "C:\\PROGRA~1\\nodejs\\npx.cmd",
      "args": [
        "-y",
        "mcp-remote",
        "http://YOUR-EC2-PUBLIC-IP:8000/sse",
        "--allow-http",
        "--header",
        "Authorization: Bearer gbl-YOUR_KEY_HERE"
      ]
    }
  }
}

📈 Multi-Brand Scalability & Architecture

Our platform is designed to scale securely and efficiently through a unified, token-per-user model.

Instead of an analyst managing multiple tokens for different brands, one user receives one API key. Within our api_keys.json registry, that token securely maps to any number of database credentials. At runtime, the MCP server dynamically retrieves the correct underlying database login from the EC2 .env file based on which tool the AI calls, ensuring rigorous multi-tenant data isolation.

🔄 Dual-Push GitHub Configuration

If you are migrating the codebase between organizations (e.g., from intern-analytics to GOATBrandLabsTech) but want to keep both repositories strictly synchronized from your local machine, you can configure git to push to both simultaneously with a single git push command.

Run these commands in your local terminal:

git remote set-url --add --push origin https://github.com/intern-analytics/MCPforGBL.git
git remote set-url --add --push origin https://github.com/GOATBrandLabsTech/MCPforGBL.git

Once configured, any git push executed locally will automatically upload your code to both organization repositories at the exact same time.

🛠️ Development

• Local Server: python -m src.server (Standard stdio)

• SSE Server: python -m src.server2 (HTTP/SSE via FastAPI)

• Auth Utils: src/auth.py

• Database Logic: src/db.py