Inventory MCP Server

A Model Context Protocol (MCP) server for managing inventory using Supabase (PostgreSQL) database.

Features

• Add Inventory: Add or update inventory items with product name, location, and quantity

• Remove Inventory: Remove items from inventory at specific locations

• Check Stock: Query stock levels for specific items at locations

• List Inventory: Get a complete list of all inventory items

Prerequisites

• Python 3.11 or higher

• Supabase account with a project

• Database password from Supabase Dashboard

Setup Instructions

1. Install Dependencies

pip install fastmcp psycopg2-binary supabase

Or using the project file:

pip install -e .

2. Set Up Supabase Database

Run the simplified database setup script:

python setup_database_simple.py

This will:

• Connect to your Supabase database (port 6543 - connection pooler)

• Create the inventory table

• Insert sample data (24 items across 4 locations)

You'll be prompted for:

• Your Supabase database password

Note: Your Supabase URL and configuration are already set in the script:

• Host: db.sitomhzoqsoqpkaglxzg.supabase.co

• Port: 6543 (connection pooler)

• Database: postgres

• User: postgres

3. Server Configuration

The server is already configured with your Supabase credentials:

• Supabase URL: https://sitomhzoqsoqpkaglxzg.supabase.co

• Database password is set to root (default)

• Port: 6543 (connection pooler)

If you need to change the password, edit inventory-mcp-server.py:

DB_PASSWORD = os.getenv("SUPABASE_DB_PASSWORD", "your_password")

Or set an environment variable:

set SUPABASE_DB_PASSWORD=your_password

4. Run the Server

python inventory-mcp-server.py

Database Schema

The inventory table has the following structure (PostgreSQL/Supabase):

• item_id (VARCHAR(50)) - Unique item identifier

• product_name (VARCHAR(100)) - Name of the product

• location (VARCHAR(50)) - Location/warehouse name

• quantity (INT) - Stock quantity

• Primary Key: (item_id, location)

Available Tools

add_inventory

Adds or updates inventory items.

Parameters:

• item_id (str): Unique identifier for the item

• product_name (str): Name of the product

• location (str): Location/warehouse name

• quantity (int): Quantity to add

Returns: Confirmation message

remove_inventory

Removes items from inventory.

Parameters:

• item_id (str): Item identifier

• location (str): Location/warehouse name

• quantity (int): Quantity to remove

Returns: Confirmation message

check_stock

Checks stock level for a specific item at a location.

Parameters:

• item_id (str): Item identifier

• location (str): Location/warehouse name

Returns: Item details with current stock level

list_inventory

Lists all inventory items.

Returns: List of all inventory items with their details

Sample Data

The setup script includes sample data for:

• Laptops (LAP-001 to LAP-006)

• Mobile phones (MOB-001 to MOB-006)

• Tablets (TAB-001 to TAB-006)

• Accessories (ACC-001 to ACC-006)

Items are distributed across locations: Bengaluru, Mumbai, Delhi, and Pune.

Troubleshooting

Supabase Connection Issues

1. Verify Database Password:

   • Go to: https://supabase.com/dashboard/project/sitomhzoqsoqpkaglxzg/settings/database

   • Check or reset your database password

2. Check Connection Settings:

   • Default port: 6543 (connection pooler - recommended)

   • Alternative: 5432 (direct connection)

   • Ensure SSL is enabled (sslmode=require)

3. IP Allowlist:

   • Check Supabase Dashboard > Settings > Database > Connection Pooling

   • Ensure your IP is allowed or use "Allow all IPs" for development

4. Test Connection:

   python test_connection.py

Import Errors

If you encounter import errors, ensure all dependencies are installed:

pip install --upgrade fastmcp psycopg2-binary supabase

License

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