Skip to main content
Glama
Retail-MCP-Siting-Engine

Retail-MCP-Siting-Engine

by Gyanankur23

Smart Retail Siting & Data Engine

A production-grade, local implementation of an AI-powered retail location intelligence system. The system runs completely locally with zero cloud billing dependencies, using open-source infrastructure to simulate enterprise spatial data capabilities.

๐Ÿ—๏ธ Architecture

Technical Stack

  • Backend: Python 3.11+ with FastAPI (asynchronous execution)

  • AI Orchestration: LangChain for LLM integration

  • Database: SQLite with SpatiaLite extension for spatial operations

  • MCP Server: Local Model Context Protocol implementation for database tool contracts

  • LLM Core: Ollama (local) or Google Gemini API (free tier)

  • Frontend: Responsive dark-mode dashboard with Tailwind CSS and Chart.js

  • Real-time: WebSocket/SSE for live log streaming

Core Components

1. Local Database & Seed Pipeline

  • SQLite database with SpatiaLite extension for geospatial operations

  • Automated ingestion pipeline for mock spatial datasets

  • Tables: transit stations, commerce points, storefront locations

  • Spatial queries using Haversine formula for distance calculations

2. Local MCP Server

  • get_schema: Returns database schema and table structures

  • execute_query: Executes SQL queries with security validation

  • get_spatial_examples: Provides example spatial analysis queries

  • get_table_schema: Detailed schema for specific tables

3. Agentic Execution Controller

Four-phase execution loop:

  • Phase A: Schema inspection and database learning

  • Phase B: LLM generates optimized SQL queries

  • Phase C: Query execution with raw data capture

  • Phase D: LLM generates strategic business insights

4. Real-time Frontend

  • Live WebSocket connection for streaming logs

  • Interactive dashboard with phase progress tracking

  • Chart.js visualization of spatial analysis results

  • Database statistics and monitoring

Related MCP server: Business MCP Server

๐Ÿš€ Quick Start

Prerequisites

  • Python 3.11 or higher

  • Ollama (recommended) or Google Gemini API key

  • Git

Installation

Windows

# Run the setup script
setup.bat

# Or manually
python -m venv venv
venv\Scripts\activate
pip install -r requirements.txt
python run.py setup

Linux/Mac

# Make setup script executable
chmod +x setup.sh

# Run setup
./setup.sh

# Or manually
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
python3 run.py setup

Configuration

Edit the .env file to configure your settings:

# LLM Configuration
LLM_PROVIDER=OLLAMA  # or GEMINI

# Ollama Configuration (recommended)
OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama3

# Google Gemini Configuration (alternative)
GEMINI_API_KEY=your_api_key_here
GEMINI_MODEL=gemini-pro

# Database Configuration
DATABASE_PATH=data/retail_siting.db

# Server Configuration
HOST=0.0.0.0
PORT=8000
LOG_LEVEL=INFO
  1. Install Ollama from ollama.ai

  2. Start the Ollama service:

    ollama serve
  3. Pull the model:

    ollama pull llama3

Starting the Application

# Development mode (setup + run)
python run.py dev

# Or run directly
python run.py run

The application will be available at:

๐Ÿ“Š Usage

Running Analysis

  1. Open the frontend dashboard in your browser

  2. Enter your analysis request in the text area, for example:

    "Identify the top 3 high-volume cyclist transit points for a new coffee shop store layout expansion"

  3. Select your LLM provider (Ollama or Gemini)

  4. Click "Run Analysis"

  5. Watch the real-time execution progress in the console

  6. View the results and business insights

API Endpoints

  • GET /health - Health check

  • GET /api/schema - Get database schema

  • POST /api/analyze - Run analysis request

  • GET /api/status - Get execution status

  • POST /api/ingest-data - Trigger data ingestion

  • WS /ws - WebSocket for real-time logs

  • GET /api/logs - Get recent logs

  • GET /api/logs/stream - SSE log streaming

Example API Usage

# Get database schema
curl http://localhost:8000/api/schema

# Run analysis
curl -X POST http://localhost:8000/api/analyze \
  -H "Content-Type: application/json" \
  -d '{"prompt": "Find top 5 high-traffic transit stations", "llm_provider": "ollama"}'

# Check execution status
curl http://localhost:8000/api/status

๐Ÿงช Testing

Run the test suite:

# Run all tests
pytest tests/

# Run specific test file
pytest tests/test_database.py

# Run with coverage
pytest tests/ --cov=backend --cov-report=html

๐Ÿ“ Project Structure

Retail-MCP-Siting-Engine/
โ”œโ”€โ”€ backend/
โ”‚   โ”œโ”€โ”€ app.py              # FastAPI server with WebSocket
โ”‚   โ”œโ”€โ”€ database.py         # SQLite + SpatiaLite database
โ”‚   โ”œโ”€โ”€ data_ingestion.py   # Mock data generation pipeline
โ”‚   โ”œโ”€โ”€ mcp_server.py       # MCP server implementation
โ”‚   โ””โ”€โ”€ agent_controller.py # Agentic execution controller
โ”œโ”€โ”€ data/
โ”‚   โ””โ”€โ”€ retail_siting.db    # Local database (created on init)
โ”œโ”€โ”€ frontend/
โ”‚   โ”œโ”€โ”€ index.html          # Main dashboard
โ”‚   โ””โ”€โ”€ app.js              # Frontend JavaScript
โ”œโ”€โ”€ tests/
โ”‚   โ”œโ”€โ”€ test_database.py    # Database tests
โ”‚   โ””โ”€โ”€ test_mcp_server.py  # MCP server tests
โ”œโ”€โ”€ requirements.txt        # Python dependencies
โ”œโ”€โ”€ run.py                 # Main setup and run script
โ”œโ”€โ”€ setup.bat              # Windows setup script
โ”œโ”€โ”€ setup.sh               # Linux/Mac setup script
โ”œโ”€โ”€ .env.example           # Environment configuration template
โ””โ”€โ”€ README.md              # This file

๏ฟฝ Features

Database Capabilities

  • Transit Stations: Bike transit routes with capacity scores

  • Commerce Points: Business locations with revenue metrics

  • Storefronts: Potential retail locations with accessibility scores

  • Spatial Queries: Distance calculations, nearby points, spatial joins

AI/LLM Integration

  • Multi-Provider Support: Ollama (local) or Google Gemini

  • Dynamic SQL Generation: LLM writes optimized queries based on schema

  • Business Intelligence: Strategic insights from query results

  • Error Handling: Graceful failure with informative messages

Real-time Monitoring

  • Live Logs: WebSocket streaming of application logs

  • Phase Tracking: Visual progress through execution phases

  • Status Updates: Real-time execution status and progress

  • Error Reporting: Immediate feedback on failures

๐Ÿ›ก๏ธ Security

  • SQL injection protection via query validation

  • Dangerous SQL keyword blocking (DROP, DELETE, etc.)

  • Environment variable configuration for sensitive data

  • No cloud dependencies - everything runs locally

  • No data leaves your machine

โš™๏ธ Configuration Options

LLM Providers

Ollama (Recommended)

  • Completely free and local

  • Requires Ollama installation

  • Supports multiple models (llama3, mistral, etc.)

  • No API keys needed

Google Gemini

  • Free tier available

  • Requires API key

  • Good alternative if Ollama is not available

  • Configure via .env file

Database Options

SQLite + SpatiaLite (Default)

  • Lightweight, serverless

  • Built-in spatial extensions

  • Portable database file

  • No additional setup required

DuckDB (Optional)

  • Alternative database engine

  • Included in requirements

  • Can be configured in code

๐Ÿ› Troubleshooting

Ollama Connection Issues

# Check if Ollama is running
curl http://localhost:11434/api/tags

# Start Ollama service
ollama serve

# Pull required model
ollama pull llama3

Database Issues

# Reinitialize database
rm data/retail_siting.db
python run.py setup

Port Already in Use

# Change port in .env
PORT=8001

# Or kill the process using the port
# Windows
netstat -ano | findstr :8000
taskkill /PID <PID> /F

# Linux/Mac
lsof -ti:8000 | xargs kill -9

Dependency Issues

# Create fresh virtual environment
python -m venv venv
source venv/bin/activate  # or venv\Scripts\activate on Windows
pip install --upgrade pip
pip install -r requirements.txt

๐Ÿ“ˆ Performance

  • Database Query: < 100ms for typical spatial queries

  • LLM Generation: 2-10 seconds depending on query complexity

  • Total Analysis: 5-30 seconds for complete 4-phase execution

  • Memory Usage: ~500MB for Python + LLM

  • Disk Usage: ~10MB for database with mock data

๐Ÿค Contributing

This is a demonstration project for local AI-powered spatial analysis. Feel free to extend it with:

  • Additional spatial analysis functions

  • More sophisticated mock data generation

  • Support for other LLM providers

  • Enhanced frontend visualizations

  • Additional database backends

๐Ÿ“„ License

This project is provided as-is for educational and demonstration purposes.

๐Ÿ™ Acknowledgments

  • FastAPI - Modern, fast web framework

  • LangChain - LLM orchestration framework

  • Ollama - Local LLM runtime

  • SpatiaLite - Spatial extensions for SQLite

  • Chart.js - Data visualization library

  • Tailwind CSS - Utility-first CSS framework

๐Ÿ“ž Support

For issues and questions:

  1. Check the troubleshooting section

  2. Review the logs in the frontend console

  3. Ensure all prerequisites are installed

  4. Verify configuration in .env file


Built with โค๏ธ for local, privacy-first AI applications

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelogโ€ฆ

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables natural language and geospatial queries on PostGIS databases, with 32 tools for spatial analysis, geometry operations, and database management.
      5
      MIT
    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI to query a business database for customers, orders, and revenue using natural language through safe, well-defined tools.
      -
    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI assistants like Claude to query, analyze, and summarize local SQLite e-commerce databases using natural language through 11 tools, schema resources, and pre-built analytical prompts.
      -
    • F
      license
      Not graded
      quality
      B
      maintenance
      Enables conversational spatial SQL queries in plain English, turning natural language questions into validated PostGIS operations and rendering results as GeoJSON on an interactive map.
      -