Bakery Data MCP Server

An MCP (Model Context Protocol) server that provides access to bakery POS (Point of Sale) data stored in SQLite. This server enables Claude and other MCP clients to query transaction data, product information, and generate sales analytics.

Overview

This project imports bakery sales data from CSV files into a SQLite database and exposes it through an MCP server with powerful querying capabilities.

Data Sources

• POS Transaction Journal (pos_journal_2023_2024.csv): Sales transactions from 2023-2024

• Product Master (商品マスタ.csv): Product catalog with pricing and cost data

• Product Master Extended (商品マスタ_タグ拡張版.csv): Product catalog with category tags

• Department Master (部門マスタ.csv): Department/category definitions

Setup

1. Install Dependencies

Or install in development mode:

2. Import Data into SQLite

Run the import script to create the database and load CSV data:

This will:

• Create bakery_data.db SQLite database

• Import all CSV files from the Data directory

• Create indexes for better query performance

• Display database statistics

3. Configure MCP Server

Add the server to your Claude Desktop configuration file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

Windows: %APPDATA%\Claude\claude_desktop_config.json

Replace /absolute/path/to/bakery_data_mcp with the actual path to this project directory.

4. Restart Claude Desktop

Restart Claude Desktop to load the new MCP server configuration.

Available Tools

The MCP server provides the following tools:

1. query_transactions

Query POS transaction data with various filters.

Parameters:

• start_date (optional): Start date (YYYY-MM-DD)

• end_date (optional): End date (YYYY-MM-DD)

• product_code (optional): Filter by product code

• product_name (optional): Search product name (partial match)

• payment_method (optional): Filter by payment method

• min_amount / max_amount (optional): Amount range filter

• limit (optional): Max results (default: 100)

2. query_products

Query product master data.

Parameters:

• plu_code (optional): Product PLU code

• product_name (optional): Search product name (partial match)

• department_id (optional): Filter by department

• min_price / max_price (optional): Price range filter

• tag (optional): Filter by product tag

• include_tags (optional): Include tag data in results

• limit (optional): Max results (default: 100)

3. query_departments

Query department master data.

Parameters:

• department_id (optional): Department ID

• department_name (optional): Search department name (partial match)

4. sales_summary

Get aggregated sales statistics.

Parameters:

• start_date / end_date (optional): Date range

• group_by (optional): Group by product, department, payment_method, date, or month

• department_id (optional): Filter by department

• limit (optional): Max results (default: 100)

5. top_products

Get top selling products.

Parameters:

• start_date / end_date (optional): Date range

• department_id (optional): Filter by department

• metric (optional): Rank by quantity or revenue (default: revenue)

• limit (optional): Number of top products (default: 10)

6. execute_sql

Execute custom SQL queries on the database.

Parameters:

• query: SQL query to execute

• params (optional): Query parameters for parameterized queries

⚠️ Use with caution: This allows arbitrary SQL execution. Use read-only queries when possible.

7. get_schema

Get database schema information including table structures and row counts.

Example Usage

Once configured, you can ask Claude questions like:

• "What were the top 10 selling products in January 2024?"

• "Show me all transactions paid with credit card over ¥1000"

• "What's the total revenue by department for 2023?"

• "Find all products tagged with '朝食向け' (breakfast)"

• "What are the sales trends by month?"

Database Schema

Tables

• departments: Department master data

  • department_id (PRIMARY KEY)

  • department_name

• products: Product master data

  • plu_code (PRIMARY KEY)

  • department_id (FOREIGN KEY)

  • product_name

  • price

  • cost

  • cost_rate

• products_extended: Product master with tags

  • Same as products plus:

  • tags (JSON array as text)

• transactions: POS transaction journal

  • id (PRIMARY KEY, auto-increment)

  • transaction_number

  • datetime

  • product_code

  • product_name

  • unit_price

  • quantity

  • amount

  • payment_method

Development

Project Structure

Running the Server

For testing, you can run the server directly:

The server communicates via stdio and expects MCP protocol messages.

License

MIT License