Pet Grooming Analytics MCP

A read-only Model Context Protocol server that connects Claude Desktop (or any MCP client) to a Supabase / PostgreSQL pet-grooming database. It exposes secure, tool-based analytics over customers, pets, appointments, services, and payments — Claude calls clearly-defined tools instead of generating unrestricted SQL.

The server runs locally over STDIO and is launched directly by Claude Desktop.

Why read-only tools instead of raw SQL

• No arbitrary SQL from the model. Every tool issues a fixed, parameterised query. The client only supplies typed arguments (dates, names, limits).
• Read-only at the connection layer. Pooled connections are pinned to READ ONLY transactions with default_transaction_read_only=on and a bounded statement_timeout.
• Bounded results. Row limits are clamped server-side (MAX_ROW_LIMIT).
• Defence in depth. You are encouraged to point DATABASE_URL at a dedicated read-only database role (see sql/schema.sql).

Tools

Overview

Tool	Description
get_business_overview	Headline counts (users, pets, appointments, services) and total revenue.
get_user_statistics	Active/inactive customers, users created in a date range, avg pets per customer.
get_pet_statistics	Pet counts by species, breed, and size category.

Appointments

Tool	Description
get_appointment_statistics	Aggregate metrics with optional start_date, end_date, status, species filters.
get_appointments_by_status	Count of appointments grouped by status.
get_upcoming_appointments	Upcoming non-cancelled appointments with pet, owner, and services.

Search & customers

Tool	Description
search_users	Find customers by partial name / email / phone.
search_pets	Find pets by pet_name / owner_name / species / breed.
get_user_details	Full customer profile: pets, appointment count, lifetime spend.
get_top_customers	Rank customers by lifetime spend or appointment count.
get_pet_appointment_history	A pet's appointment history including booked services.

Services

Tool	Description
get_service_statistics	Catalogue with pricing, duration, and lifetime booking counts.
get_popular_services	Most-booked services over the last N days.
get_service_revenue	Realised revenue attributed to each service.

Payments

Tool	Description
get_payment_statistics	Totals, realised revenue, breakdowns by status and method.
get_revenue_summary	Realised-revenue time series bucketed by day/week/month/year.

Revenue definition: "realised revenue" sums payments whose status is one of completed, paid, succeeded, captured, settled (see config.py). Adjust that list to match your payment_status enum.

Setup

1. Install

# with uv (recommended)
uv venv
uv pip install -e ".[dev]"

# or with pip
python -m venv .venv
.venv\Scripts\activate        # Windows
# source .venv/bin/activate    # macOS/Linux
pip install -e ".[dev]"

2. Configure

cp .env.example .env

Set DATABASE_URL to your Supabase Postgres connection string (Supabase → Project Settings → Database → Connection string → URI). The real .env is git-ignored.

If you don't have a database yet, run sql/schema.sql in the Supabase SQL editor to create the schema.

How to run

An MCP server isn't a web app — there's no URL to open. It talks JSON-RPC over stdin/stdout and is normally launched by an MCP client (Claude Desktop). There are three ways to run it, depending on what you want to do.

A. Through Claude Desktop (the real use case)

Configure it once (see Connect to Claude Desktop below), then fully quit and restart Claude Desktop. Claude launches the server for you — you don't run anything manually. Check Settings → Developer; the server should show as connected.

B. Manually in a terminal (to see it start / debug)

uv run mcp_server.py

This is the exact command Claude Desktop uses. It reads your .env, connects to Supabase, then waits silently for input on stdin — that is correct behaviour for an MCP server. If nothing errors, it's working. Press Ctrl+C to stop.

On Windows, launch with uv run (or the project's venv) rather than a bare python mcp_server.py, so the server's async database driver uses a compatible event loop.

C. Interactive testing with the MCP Inspector (recommended)

A browser UI to click each tool and see live results from your database:

npx @modelcontextprotocol/inspector uv run mcp_server.py

Run the tests (no database required)

uv run pytest

The tests use a FakeDatabase that returns canned rows, so they verify each tool's output shape and JSON serialization without a live Postgres instance.

Connect to Claude Desktop

Edit your Claude Desktop config (%APPDATA%\Claude\claude_desktop_config.json on Windows, ~/Library/Application Support/Claude/claude_desktop_config.json on macOS) and add the server. Point --directory at this project folder:

{
  "mcpServers": {
    "pet-grooming-analytics": {
      "command": "uv",
      "args": [
        "--directory",
        "C:\\dev\\pet_grooming_mcp\\pet_grooming_mcp",
        "run",
        "mcp_server.py"
      ],
      "env": {
        "DATABASE_URL": "postgresql://postgres.your-ref:your-password@aws-0-region.pooler.supabase.com:5432/postgres?sslmode=require"
      }
    }
  }
}

Notes:

• Using uv --directory ... run mcp_server.py avoids PATH problems — you don't need the project's virtual environment to be active or on PATH.
• If your password contains a %, percent-encode it as %25 in the URL (other reserved characters likewise, e.g. @ → %40).
• The DATABASE_URL in env can be omitted if it is already set in .env.

Then fully quit and restart Claude Desktop and try:

• "Give me a business overview."
• "Find all dogs owned by customers named Johnson."
• "Show Bella's appointment history."
• "Which services have been used most during the last 90 days?"
• "What was our revenue by month this year?"

Web dashboard (optional)

Alongside the MCP server, this repo ships a browser dashboard so you can see the analytics: a FastAPI backend (src/pet_grooming_mcp/web/) that reuses the exact same read-only Database and query tools, and a Next.js frontend (../frontend/). Nothing about the security model changes — the HTTP layer inherits the READ ONLY connection pool and bounded statement_timeout, and the ad-hoc SQL paths reject anything that isn't a single SELECT.

Tabs

• Statistics Snapshot — headline KPIs, revenue trend, appointments by status, pets by species, top customers. Export the whole view as JPEG or the metrics as CSV.
• Data Quality Snapshot — completeness/integrity checks with a 0-100 health score. Export as JPEG or CSV.
• Analyze (Prompt) — ask a question in plain English; Claude (claude-opus-4-8) writes a read-only SQL query, the backend runs it, and the result is charted alongside the generated SQL.
• SQL Query Maker — write your own SELECT, run it, browse the schema, chart the result, and export to CSV.

1. Backend

uv pip install -e ".[web]"      # Think of this like a npm install for a json package you run once to activate the dependencies --> adds fastapi, uvicorn, anthropic
# Set ANTHROPIC_API_KEY in .env to enable the "Analyze (Prompt)" tab.
uv run pet-grooming-web         # serves http://127.0.0.1:8000

Windows: launch via pet-grooming-web (or python -m pet_grooming_mcp.web.app), not the bare uvicorn CLI — the async Postgres driver needs the selector event loop, which the entry point sets up. Host/port/CORS are configurable via WEB_HOST, WEB_PORT, WEB_CORS_ORIGINS.

2. Frontend

cd ../frontend
npm install
npm run dev                     # serves http://localhost:3000

Point the UI at the backend with NEXT_PUBLIC_API_BASE (defaults to http://127.0.0.1:8000); see frontend/.env.local.example.

Project layout

mcp_server.py      # entry point: `uv run mcp_server.py`
src/pet_grooming_mcp/
  server.py        # FastMCP server: registers tools, manages the pool lifespan
  config.py        # environment configuration
  database.py      # read-only async connection pool
  tools/           # query logic (overview, users, pets, appointments, services, payments)
  models/          # JSON serialization helpers
sql/schema.sql     # reference schema + read-only role
tests/             # offline tests

License

MIT — see LICENSE.