MCP Analytics Schema Server

This server exposes the Dear Analytics database schema via the Model Context Protocol (MCP), enabling LLM clients to build accurate SQL queries.

This allows your LLM to directly query the analytics database schema to design queries. Zoho Analytics has its own MCP server; the GrowthPath server has more knowledge about Dear (Cin7 Core) specifics and more semantic knowledge. However, it is not designed to execute queries directly, just to help you build them (in SQL).

MCP Endpoint: http://test-dear.growthpath.com.au/mcp

What This MCP Server Provides

The Analytics MCP server exposes 39 database tables from the Dear Analytics data warehouse, including:

Master Data Tables

dear_customer - Customer records with addresses and contacts
dear_product - Product catalog with pricing and availability
dear_supplier - Supplier information
dear_location - Warehouse locations

Transaction/Fact Tables

sales_fact - Revenue transactions with COGS analysis
purchases_fact - Purchase transactions
product_movements - Stock movements
sales_order_lines - Sales order line items

Available Resources

Resource URI	Description
`analytics://tables`	List all 39 analytics tables with descriptions
`analytics://schema/{table_name}`	Get detailed schema for a specific table (fields, types, foreign keys, indexes)
`analytics://relationships`	Text diagram showing foreign key relationships between tables
`analytics://database-info`	Database conventions (timezones, decimal precision, multi-tenancy patterns)

Configuring Claude Code

Option 1: Project Configuration (Recommended for Teams)

Create a .mcp.json file in your project root:

{
  "mcpServers": {
    "analytics": {
      "type": "http",
      "url": "http://test-dear.growthpath.com.au/mcp"
    }
  }
}

This file can be committed to git so all team members share the same configuration.

Option 2: CLI Command

# Add the MCP server
claude mcp add --transport http analytics http://test-dear.growthpath.com.au/mcp

# Verify it's configured
claude mcp list

# Check connection status (within Claude Code)
/mcp

Scope Options

Scope	Command	Use Case
`project`	`claude mcp add --scope project ...`	Shared with team via `.mcp.json` in git
`user`	`claude mcp add --scope user ...`	Available across all your projects
`local`	`claude mcp add --scope local ...`	Private, current project only (default)

Practical Examples

Discovering Tables

Ask Claude to list available tables:

What analytics tables are available?

@analytics:analytics://tables

Understanding Table Structure

Get the schema for a specific table before writing queries:

Show me the structure of the sales_fact table.

@analytics:analytics://schema/sales_fact

Building JOIN Queries

Use the relationships resource to understand how tables connect:

How do I join sales data to customer information?

@analytics:analytics://relationships
@analytics:analytics://schema/sales_fact
@analytics:analytics://schema/dear_customer

Writing Complex Queries

Provide context and ask Claude to write the query:

Using the analytics schema, write a SQL query to find the top 10 customers
by total revenue in 2024, including their company name and total order count.

@analytics:analytics://schema/sales_fact
@analytics:analytics://schema/dear_customer
@analytics:analytics://database-info

Exploring Product Data

I need to analyze product availability across locations.
What tables should I use and how do they relate?

@analytics:analytics://schema/dear_product
@analytics:analytics://schema/dear_product_availability
@analytics:analytics://schema/dear_location
@analytics:analytics://relationships

Deployment Notes

Kubernetes Deployment

The MCP server is integrated into Django's ASGI application and served at the /mcp path. No separate server or port is required.

The MCP endpoint is available at:

https://<your-ingress-hostname>/mcp

This integration means:

No additional Kubernetes deployment or service needed
MCP requests are handled by the same pods as the Django application
Authentication and TLS are handled by your existing ingress configuration

Local Development

For local development, you have two options:

Option A: Via Django (ASGI with Uvicorn)

# Start Django with ASGI server
uvicorn project.asgi:application --host 0.0.0.0 --port 8000

# MCP available at: http://localhost:8000/mcp

Option B: Standalone MCP Server

# Start dedicated MCP server on a different port
python manage.py mcp_server --port 8090

# MCP available at: http://localhost:8090/mcp

Available Tables

The following 39 tables are exposed via this MCP server:

Table Name	Database Table	Description
`avg_ex_rate`	`avg_ex_rate`	Monthly average exchange rates from the ECB.
`dear_customer`	`dear_customer`	dear customer table
`dear_customer_addresses`	`dear_customer_addresses`	Addresses associated with Customers
`dear_customer_contacts`	`dear_customer_contacts`	Contacts associated with Customers
`dear_customer_tag`	`dear_customer_tag`	Tags associated with Customers
`dear_financial_transactions`	`dear_financial_transactions`	Financial transactions from Cin7 Core (Dear). These are not auto updated.
`dear_finished_goods_header`	`dear_finished_goods_header`	Finished Goods Header (Not fully implemented)
`dear_location`	`dear_location`	Dear Locations (that is, warehouses, not bins)
`dear_metadata`	`dear_metadata`	Dear Company Name: this table is useful if you have multiple Dear instances in the GrowthPath Analytics Connector
`dear_po_header`	`dear_po_header`	Purchase Order header
`dear_product`	`dear_product`	Product details from Cin7 Core (Dear)
`dear_product_availability`	`dear_product_availability`	Product availability from Dear, plus hard allocations (qty reserved for a specfic order)
`dear_product_custom_prices`	`dear_product_custom_prices`	Custom prices for products per customer.
`dear_product_family`	`dear_product_family`	Product Family details
`dear_product_tag`	`dear_product_tag`	Tags associated with Products
`dear_sales_header`	`sales_header`	Sales Header data
`dear_sales_invoice`	`sales_invoice`	Sales Invoices and Credit notes header: invoices and credit notes.
`dear_stock_adjustment_header`	`dear_stock_adjustment_header`	Stock Adjustment header
`dear_stock_adjustment_lines`	`dear_stock_adjustment_lines`	Stock Adjustment Lines
`dear_stock_transfer_header`	`dear_stock_transfer_header`	Stock Transfer
`dear_stock_transfer_lines`	`dear_stock_transfer_lines`	Stock Transfer Lines
`dear_supplier`	`dear_supplier`	Supplier details from Cin7 Core (Dear)
`dear_supplier_product`	`dear_supplier_product`	Products linked to Suppliers
`mainfreight_events`	`dear_zoho_analytics_mainfreight_events`	Mainfreight events
`product_movements`	`product_movements`	All stock movements with quantity and date
`purchase_order_lines`	`purchase_order_lines`	PO lines includng draft and not yet invoiced POs
`purchase_order_payments`	`purchase_order_payments`	Payments relating to POs, including deposits and invoice payments
`purchases_fact`	`purchases_fact`	based on authorised PO invoice lines
`purchases_lines_lifecycle`	`purchases_lines_lifecycle`	Shows the status of a PO line (what's been invoiced, received etc)
`sales_fact`	`sales_fact`	Based on authorised invoice lines; the revenue figures in this table should agree with the data in Xero or QBO and are the same as the Cin7 Core Report 'Sale Invoices & Credit Notes' (they should be the same to the cent). Margins in this table are for management accounting purposes: Actual COGS from shipments is used if available, but if it not available (a line on the invoice which has not yet shipped), the margin figure falls back to average cost or supplier price as a place holder margin. Each row shows the COGS source.
`sales_lines_lifecycle`	`sales_lines_lifecycle`	Shows the lifecycle status of lines on the sales order: quantity and value picked, shipped, invoiced etc
`sales_order_inventory_transactions`	`sales_order_inventory_transactions`	Inventory movements by date and value associated with the sales order, for detailed revenue and cogs analysis. Covers orders, shipments, credit notes and restocks
`sales_order_lines`	`sales_order_lines`	Sales order lines, including for draft orders and orders which are not yet invoiced
`sales_order_payments`	`sales_order_payments`	Payments and deposits (prepayments) known to Dear for sales orders
`sales_shipments`	`sales_shipments`	Information specifically about shipments, similar to the sales_order_inventory_transactions table, but only for shipments
`shipping_subscriptions`	`shipping_subscriptions`	OceanInsights Project44 data for containers on ships
`shippit_transactions`	`shippit_transactions`	This table supports Shippit transactional data, but this must be separately enabled.
`table_update_status`	`table_update_status`	During a data update with the Zoho connector, this table will show that a data update is in progress
`threepl_stock_analysis`	`threepl_stock_analysis`	Stores information about the stock levels of 3PLs. The table is unique by source_dear, stock_date, dear_location_od and product_guid