jquad-group/jq-supabase-mcp-server

Supabase MCP Server

A production-ready Model Context Protocol (MCP) server for secure Supabase database integration. Enables Claude and other AI assistants to interact with Supabase databases through high-level tools with built-in Row Level Security and comprehensive validation.

? Key Features

• ?? Secure Database Access: Built-in Row Level Security (RLS) and SQL injection protection
• ?? Dual Transport Modes: STDIO for local use and HTTP for remote deployment
• ??? 5 Core Database Tools: List tables, query data, describe schemas, insert/update records
• ? Type-Safe: Comprehensive Pydantic validation and type hints
• ?? Real-time Streaming: Server-Sent Events (SSE) support in HTTP mode
• ?? Fully Tested: Extensive test suite with mocked Supabase dependencies

Demo

?? Table of Contents

• Quick Start
• Installation
• Configuration
• Transport Modes
• Available Tools
• Security Features
• Claude Desktop Integration
• Development
• Testing
• Troubleshooting
• Project Structure

?? Quick Start

Prerequisites

• Python 3.13+ (required for latest type hints and performance)
• uv - Fast Python package manager
• Supabase account with a project and database access
• Row Level Security (RLS) policies configured (see RLS Setup)

Installation

# Clone the repository
git clone <repository-url>
cd jq-supabase-mcp-server

# Install dependencies
uv sync

# Copy environment template and configure
cp .env.example .env
# Edit .env with your Supabase credentials

Quick Test

# Test the server in STDIO mode
uv run src/mcp_server.py

# Test with MCP Inspector (interactive testing)
uv run mcp dev src/mcp_server.py

# Test HTTP mode
uv run src/mcp_server.py --mode=http --port=8000

?? Configuration

Environment Variables

Create a .env file from .env.example:

Getting Supabase Credentials

1. Go to supabase.com and create/open your project
2. Navigate to Settings ? API
3. Copy your Project URL and anon public key
4. For admin operations, copy the service_role key (?? keep this secure!)

?? Transport Modes

The server supports two transport modes for different deployment scenarios:

STDIO Mode (Default)

Traditional stdin/stdout communication for local Claude Desktop integration.

# Start server in STDIO mode
uv run src/mcp_server.py

# Or explicitly specify
uv run src/mcp_server.py --mode=stdio

Use Cases:

• Local Claude Desktop integration
• Direct MCP client communication
• Development and testing

HTTP Mode

Streamable HTTP transport with Server-Sent Events for remote deployment.

# Start server in HTTP mode (default: localhost:8000)
uv run src/mcp_server.py --mode=http

# Custom host and port
uv run src/mcp_server.py --mode=http --host=0.0.0.0 --port=9000

# With custom CORS origins
uv run src/mcp_server.py --mode=http --cors-origins="https://example.com,https://app.example.com"

Use Cases:

• Remote deployment on cloud infrastructure
• Multiple Claude Desktop instances
• Web application integration
• Real-time data streaming

HTTP Endpoints:

• POST /mcp - Main MCP endpoint (JSON-RPC 2.0)
• Supports Server-Sent Events (SSE) for streaming
• Session management via Mcp-Session-Id header

??? Available Tools

The MCP server provides 5 core database tools:

1. list_tables

Lists all accessible tables in the Supabase database.

Usage in Claude:

"Show me all tables in the database"
"What tables are available?"

Returns: List of tables with names, types, and schemas

2. query_table

Query a specific table with optional filters and pagination.

Usage in Claude:

"Show me the first 10 users"
"Find all active users"
"Get orders from the last week with status 'pending'"

Parameters:

• table_name (required): Name of the table to query
• limit (optional): Maximum results (default: 100, max: 1000)
• filters (optional): Key-value pairs for filtering (e.g., {"status": "active"})

Returns: JSON formatted query results

3. describe_table

Get detailed schema information for a specific table.

Usage in Claude:

"What's the schema of the users table?"
"Show me the structure of the orders table"
"Describe the products table"

Parameters:

• table_name (required): Name of the table to describe

Returns: Table schema with columns, types, constraints, and relationships

4. insert_record

Insert a new record into a table with validation.

Usage in Claude:

"Add a new user with name 'John Doe' and email 'john@example.com'"
"Create a new order for user ID 123"
"Insert a product with name 'Widget' and price 29.99"

Parameters:

• table_name (required): Table to insert into
• data (required): Record data as key-value pairs

Returns: Inserted record details

Security: Validates column names, prevents SQL injection, enforces size limits

5. update_record

Update existing records based on filter conditions.

Usage in Claude:

"Update the user with ID 123 to set status as 'inactive'"
"Change all pending orders to 'processing'"
"Set price to 24.99 for product ID 456"

Parameters:

• table_name (required): Table to update
• filters (required): Conditions to identify records (prevents mass updates)
• updates (required): New values to set

Returns: Updated record details

Security: Requires filters, protects primary keys and timestamps, validates all inputs

?? Security Features

Input Validation

• SQL Injection Protection: All inputs validated against dangerous SQL patterns
• Table Name Validation: Only alphanumeric and underscore characters allowed
• System Table Protection: Blocks access to PostgreSQL and Supabase system tables
• Column Validation: Validates column names in filters and data
• Size Limits: Prevents excessively large data values (10KB max per field)

Row Level Security (RLS)

• Leverages Supabase's built-in Row Level Security
• User context automatically applied to all operations
• No direct SQL queries exposed to clients
• All data access controlled by RLS policies

Protected Operations

• Primary Key Protection: Prevents updating id, created_at, updated_at columns
• Mass Update Prevention: Requires filters for all update operations
• Query Limits: Enforces reasonable pagination limits (default: 100, max: 1000)
• Error Messages: Detailed but secure error reporting (no sensitive data leaked)

RLS Setup

Why RLS is Required

This MCP server relies on Supabase's Row Level Security to ensure secure database access. Without properly configured RLS policies, you may encounter:

• "No rows returned" errors even when data exists
• Permission denied errors
• Inability to insert or update records

Basic RLS Configuration

1. Enable RLS on your tables:

ALTER TABLE your_table_name ENABLE ROW LEVEL SECURITY;

2. Create policies for your use case:

For development/testing (anon key access):

-- Allow anon users to read all rows
CREATE POLICY "Enable read for anon" ON your_table_name
FOR SELECT TO anon USING (true);

-- Allow anon users to insert records
CREATE POLICY "Enable insert for anon" ON your_table_name
FOR INSERT TO anon WITH CHECK (true);

-- Allow anon users to update records
CREATE POLICY "Enable update for anon" ON your_table_name
FOR UPDATE TO anon USING (true) WITH CHECK (true);

For production (authenticated users):

-- Users can only read their own data
CREATE POLICY "Users read own data" ON your_table_name
FOR SELECT TO authenticated 
USING (auth.uid() = user_id);

-- Users can only insert their own data
CREATE POLICY "Users insert own data" ON your_table_name
FOR INSERT TO authenticated 
WITH CHECK (auth.uid() = user_id);

Testing RLS Policies

-- Test if you can read data
SELECT * FROM your_table_name LIMIT 5;

-- Test if you can insert data
INSERT INTO your_table_name (column1, column2) 
VALUES ('test_value1', 'test_value2');

Learn More: Supabase RLS Documentation

?? Claude Desktop Integration

Option 1: Automatic Installation (STDIO Mode)

uv run mcp install src/mcp_server.py --name "Supabase Database"

Option 2: Manual Configuration

Configuration File Location:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%/Claude/claude_desktop_config.json
• Linux: ~/.config/Claude/claude_desktop_config.json

STDIO Mode (Local)

{
  "mcpServers": {
    "supabase-database": {
      "command": "uv",
      "args": [
        "--directory",
        "/absolute/path/to/jq-supabase-mcp-server",
        "run",
        "src/mcp_server.py"
      ],
      "env": {
        "SUPABASE_URL": "https://your-project.supabase.co",
        "SUPABASE_ANON_KEY": "your-anon-key-here"
      }
    }
  }
}

?? Important: Use absolute paths and forward slashes / even on Windows

HTTP Mode (Remote)

See examples/claude_desktop_http_config.json for a complete example.

{
  "mcpServers": {
    "supabase-http": {
      "url": "http://localhost:8000/mcp",
      "env": {
        "SUPABASE_URL": "https://your-project.supabase.co",
        "SUPABASE_ANON_KEY": "your-anon-key-here"
      }
    }
  }
}

Note: Server must be started separately: uv run src/mcp_server.py --mode=http

Verification

1. Restart Claude Desktop
2. Check for the tools icon (??) in the interface
3. Try a natural language query: "Show me all tables in my database"

?? Development

Local Development Workflow

# Install dependencies
uv sync

# Run linting and type checking
uv run ruff check src/ --fix
uv run mypy src/

# Run tests
uv run pytest tests/ -v

# Start MCP server for testing (STDIO mode)
uv run src/mcp_server.py

# Start in HTTP mode
uv run src/mcp_server.py --mode=http --port=8000

# Test with MCP Inspector (interactive)
uv run mcp dev src/mcp_server.py

Recent Improvements

? Asyncio Event Loop Fix (Latest - 2025-10-12)

Fixed the "Already running asyncio in this thread" error by:

• Making main() function synchronous for STDIO mode
• Creating isolated async context for HTTP mode
• Proper event loop handling for both transport modes
• No external dependencies needed (removed nest-asyncio)

Before:

async def main():
    ...
    mcp.run()  # Error: Already running asyncio

if __name__ == "__main__":
    asyncio.run(main())  # Creates event loop conflict

After:

def main():  # Now synchronous
    ...
    if args.mode == "stdio":
        mcp.run()  # Creates its own loop cleanly
    elif args.mode == "http":
        async def run_http_server():
            ...  # Isolated async context
        asyncio.run(run_http_server())

if __name__ == "__main__":
    main()  # Direct call, no asyncio.run()

? Dual Transport Support

• STDIO: Traditional stdin/stdout for local Claude Desktop integration
• HTTP: Streamable HTTP with Server-Sent Events for remote deployment
• Seamless switching via --mode argument
• Session management in HTTP mode
• CORS configuration for web clients

Adding New Features

1. Follow the patterns in existing code (src/mcp_server.py)
2. Add comprehensive tests in tests/
3. Update documentation (README.md, docstrings)
4. Run validation suite before committing

Development Guidelines

• Use Python 3.13+ with strict type hints
• Follow FastMCP patterns for tool registration
• Add comprehensive error handling with detailed messages
• Include both positive and negative test cases
• Document all public functions with Google-style docstrings
• Never use print() statements (use logger to stderr instead)

?? Testing

Running Tests

# Run all tests
uv run pytest tests/ -v

# Run specific test file
uv run pytest tests/test_database.py -v

# Run with coverage
uv run pytest tests/ --cov=src --cov-report=html

# Run tests with detailed output
uv run pytest tests/ -v -s

# Run only MCP server tests
uv run pytest tests/test_mcp_server.py -v

Test Structure

• Unit Tests: Test individual functions and classes
• Integration Tests: Test MCP tool integration with mocked Supabase
• HTTP Transport Tests: Test HTTP mode functionality
• Validation Tests: Test security and input validation
• End-to-End Tests: Test complete client workflows

Example Test Session

$ uv run pytest tests/ -v

tests/test_database.py::TestSupabaseManager::test_initialization PASSED
tests/test_database.py::TestSupabaseManager::test_connection_test PASSED
tests/test_database.py::TestValidation::test_table_name_validation PASSED
tests/test_mcp_server.py::TestListTables::test_list_tables_success PASSED
tests/test_mcp_server.py::TestQueryTable::test_query_with_filters PASSED
tests/test_http_transport.py::TestHttpTransport::test_http_server_start PASSED

======================== 24 passed in 2.34s ========================

?? Troubleshooting

Common Issues

Server Won't Start (Asyncio Error)

Error: Already running asyncio in this thread

Solution: This has been fixed in the latest version (2025-10-12). The server now properly handles event loops in both STDIO and HTTP modes.

# Ensure you're using the latest version
git pull origin main
uv sync

# Test the fix
uv run src/mcp_server.py

If you still encounter this error, you may be running an older version. The fix involves:

• main() is now synchronous for STDIO mode
• HTTP mode creates its own async context internally

Environment Variable Issues

# Check environment variables are loaded
cat .env

# Verify Supabase credentials
echo $SUPABASE_URL
echo $SUPABASE_ANON_KEY

# Test connection manually
uv run python -c "
from src.database import SupabaseManager
import os
from dotenv import load_dotenv

load_dotenv()
manager = SupabaseManager(
    os.getenv('SUPABASE_URL'),
    os.getenv('SUPABASE_ANON_KEY')
)
print(manager.test_connection())
"

Solution: Ensure all required environment variables are set in .env file.

No Tables Visible

# Test with MCP inspector
uv run mcp dev src/mcp_server.py

# Manually call list_tables

Possible causes:

• Database permissions issue
• RLS policies blocking access (most common - see RLS Setup)
• Empty database
• Network connectivity issues

RLS Policy Issues (Most Common)

If you can connect but get "No data" or permission errors:

-- Check if RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE tablename = 'your_table_name';

-- List existing policies
SELECT * FROM pg_policies 
WHERE tablename = 'your_table_name';

-- Test with service role key (bypasses RLS) to confirm data exists
-- Temporarily update .env with SUPABASE_SERVICE_ROLE_KEY and test

Common Solutions:

1. Missing policies: Add basic RLS policies (see RLS Setup)
2. Wrong key type: Use anon policies for SUPABASE_ANON_KEY
3. Overly restrictive policies: Start with USING (true) for testing
4. RLS not enabled: Run ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

Claude Desktop Integration Issues

STDIO Mode:

1. Tools not appearing: Check Claude Desktop logs at ~/Library/Logs/Claude/mcp*.log
2. Permission errors: Verify absolute paths in configuration
3. Environment variables: Ensure credentials are in Claude Desktop config

HTTP Mode:

1. Connection timeouts: Verify server is running and accessible
2. CORS errors: Add allowed origins with --cors-origins
3. Port already in use: Try different port with --port

# Check if server is accessible
curl http://localhost:8000/health

# Test MCP endpoint  
curl -X POST http://localhost:8000/mcp \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","method":"tools/list","id":1}'

Validation Errors

# Run tests to identify issues
uv run pytest tests/test_database.py -v

# Check input validation
uv run python -c "
from src.database import validate_table_name
print(validate_table_name('valid_table'))
print(validate_table_name('invalid-table'))
"

Debug Mode

Enable detailed logging:

# In .env file
DEBUG=true
LOG_LEVEL=DEBUG

# Or set environment variable
export DEBUG=true
export LOG_LEVEL=DEBUG
uv run src/mcp_server.py

Performance Issues

If queries are slow:

1. Check Supabase performance metrics in dashboard
2. Add database indexes for frequently queried columns
3. Reduce query limits in requests
4. Review and optimize RLS policies
5. Consider using service role key for admin operations

?? Project Structure

jq-supabase-mcp-server/
??? src/                        # Source code
?   ??? mcp_server.py          # Main FastMCP server with 5 database tools
?   ??? database.py            # Supabase client wrapper with validation
?   ??? http_transport.py      # HTTP transport implementation (SSE)
?   ??? transport_base.py      # Abstract transport base class
??? tests/                      # Test suite
?   ??? test_mcp_server.py     # MCP server integration tests
?   ??? test_database.py       # Database module unit tests
?   ??? test_http_transport.py # HTTP transport tests
?   ??? test_client.py         # End-to-end client tests
??? examples/                   # Example implementations
?   ??? claude_desktop_http_config.json
?   ??? README.md
??? specs/                      # Project specifications
?   ??? initial/
?       ??? THIS_PROJECT.md    # Project documentation
??? .env.example               # Environment variables template
??? pyproject.toml             # Python dependencies (uv)
??? README.md                  # This file
??? demo.png                   # Demo screenshot

?? Monitoring

Logs

• Server logs: Written to stderr (visible in terminal)
• Claude Desktop logs: ~/Library/Logs/Claude/mcp*.log (macOS)
• Debug information: Enable with DEBUG=true in .env

Health Checks

# Test connection health
uv run python -c "
from src.database import SupabaseManager
import os
from dotenv import load_dotenv

load_dotenv()
manager = SupabaseManager(
    os.getenv('SUPABASE_URL'),
    os.getenv('SUPABASE_ANON_KEY')
)
print(manager.test_connection())
"

# For HTTP mode
curl http://localhost:8000/health

?? Contributing

1. Follow Code Style: Use ruff for formatting and mypy for type checking
2. Write Tests: Add tests for new features in tests/
3. Update Documentation: Keep README and docstrings current
4. Security First: Follow security best practices for database access

Development Guidelines

• Use Python 3.13+ with strict type hints
• Follow the patterns established in existing code
• Add comprehensive error handling
• Include both positive and negative test cases
• Document all public functions with Google-style docstrings
• Never use print() in MCP server code (corrupts JSON-RPC)

??? Architecture

Core Components

1. FastMCP Server (): Main server with tool implementations
2. Database Manager (): Supabase client wrapper with validation
3. HTTP Transport (): HTTP/SSE transport layer
4. Transport Base (): Abstract transport interface
5. Pydantic Models: Type-safe request/response validation
6. Security Layer: Input validation and SQL injection prevention

Design Principles

• Security First: Multiple layers of validation and protection
• Type Safety: Comprehensive type hints and Pydantic validation
• Error Handling: Graceful degradation with informative error messages
• Performance: Efficient queries with reasonable limits
• Usability: Natural language interface through Claude integration
• Flexibility: Support for both local and remote deployment

?? License

This project follows the license specified in the repository.

?? Support

For issues and questions:

1. Check this README for common solutions
2. Review the Troubleshooting section
3. Examine the test files for usage examples
4. Check Supabase documentation for database-specific issues
5. Review FastMCP documentation for MCP issues

?? Links

• FastMCP Documentation - MCP Python SDK
• Supabase Documentation - Supabase guides and API reference
• Model Context Protocol - MCP specification
• uv Documentation - Modern Python package manager
• Pydantic Documentation - Data validation library

Built with ?? using FastMCP, Supabase, and Pydantic

Last Updated: 2025-10-12 - Fixed asyncio event loop handling, updated documentation