# Supabase MCP Server

A **production-ready Model Context Protocol (MCP) server** that provides AI assistants with intelligent Supabase database access. Features dynamic schema discovery, complete user management, and secure operations using only your service key and URL.

## 🚀 Key Features

### **🔍 Intelligent Schema Discovery**
- **Dynamic table detection** - Automatically discovers all tables in your database
- **Complete column analysis** - PostgreSQL types, constraints, foreign keys, and precision
- **Empty table support** - Works with tables that have no data
- **OpenAPI-based discovery** - No hardcoded assumptions, purely dynamic

### **💾 Complete Database Operations**
- **Full CRUD** - Query, insert, update, delete with advanced filtering
- **PostgreSQL functions** - Execute stored procedures and custom functions
- **Advanced querying** - 23+ operators, embedded resources (joins), pagination, sorting
- **PostgREST operators** - Support for eq, neq, gt, gte, lt, lte, like, ilike, in, between, cs, cd, ov, and all negations

### **👥 Enterprise User Management**
- **Complete user objects** - All auth fields including confirmation timestamps
- **Advanced pagination** - Proper page counts, totals, and navigation
- **Flexible magic links** - All 6 link types with custom redirects and expiration
- **Admin operations** - Create, update, delete users with full metadata

### **📁 File Storage Operations**
- **Upload/Download** - Base64 file handling with type detection
- **Signed URLs** - Temporary access for private files
- **Bucket management** - List and organize storage

## 🎯 Quick Start

### 1. Installation
```bash
git clone https://github.com/sweir1/supabase-mcp-server.git
cd supabase-mcp-server
npm install
npm run build
```

### 2. Configuration
Create `.env`:
```env
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_KEY=your-service-role-key-here
```

### 3. Integration with Claude Code
Create `.mcp.json`:
```json
{
  "mcpServers": {
    "supabase": {
      "command": "node",
      "args": ["/path/to/supabase-mcp-server/dist/index.js"],
      "env": {
        "SUPABASE_URL": "https://your-project.supabase.co",
        "SUPABASE_SERVICE_KEY": "your-service-role-key-here"
      }
    }
  }
}
```

### 4. Start Using
```bash
npm start  # Or integrate directly with Claude Code
```

## 🔍 Schema Discovery Tools

The server provides **3 powerful discovery tools** that dynamically analyze your database:

### `supabase_discover_openapi`
Get a clean overview of your Supabase API:
```json
{
  "api_info": {
    "host": "your-project.supabase.co",
    "version": "13.0.5"
  },
  "available_tables": ["users", "posts", "comments"],
  "available_rpc_functions": ["get_user_stats"],
  "summary": {
    "total_tables": 3,
    "total_rpc_functions": 1
  }
}
```

### `supabase_discover_tables`
Discover all tables with data status and structure:
```json
{
  "discovered_tables": [
    {
      "table": "users",
      "columns": ["id", "email", "created_at", "user_metadata"],
      "has_data": true,
      "access_level": "full_crud_access"
    }
  ]
}
```

### `supabase_discover_columns`
Get detailed column information with PostgreSQL types and constraints:
```json
{
  "table": "users",
  "columns": [
    {
      "name": "email",
      "type": "character varying(255)",
      "base_type": "character varying",
      "required": true,
      "constraints": ["NOT NULL", "UNIQUE"],
      "max_length": 255
    },
    {
      "name": "profile_id",
      "type": "uuid",
      "foreign_key": {
        "references_table": "profiles",
        "references_column": "id"
      }
    }
  ]
}
```

## 📚 Available Tools (21 Total)

### **Database Operations (5 tools)**
- `supabase_query` - Advanced PostgreSQL querying with 23+ operators, embedded resources for joins, count aggregation, and comprehensive filtering
- `supabase_insert` - Single or batch inserts with upsert support
- `supabase_update` - Targeted updates with precise filters
- `supabase_delete` - Safe deletion with filter requirements
- `supabase_rpc` - Execute PostgreSQL functions

### **Schema Discovery (3 tools)**
- `supabase_discover_openapi` - API overview and capabilities
- `supabase_discover_tables` - Dynamic table discovery
- `supabase_discover_columns` - Detailed column analysis

### **User Management (6 tools)**
- `supabase_create_user` - Create users with complete metadata
- `supabase_list_users` - List with enhanced pagination
- `supabase_get_user` - Retrieve complete user objects
- `supabase_update_user` - Update any user attribute
- `supabase_delete_user` - Permanent user removal
- `supabase_generate_link` - Magic links with custom redirects

### **File Storage (7 tools)**
- `supabase_upload_file` - Upload with base64 encoding
- `supabase_download_file` - Download as base64
- `supabase_list_files` - Browse files and folders
- `supabase_delete_file` - Remove files safely
- `supabase_create_signed_url` - Temporary access URLs
- `supabase_get_public_url` - Public file URLs
- `supabase_list_buckets` - Storage bucket management

## 💡 Usage Examples

### Advanced Database Queries
Ask Claude: *"Show me all active users created in the last week with their profile data and order history"*

```json
{
  "table": "users",
  "select": "*, profiles(*), orders(*)",
  "filters": {
    "status": "eq.active",
    "created_at": "gte.2024-01-01"
  },
  "limit": 50
}
```

### Complex Filtering with PostgREST Operators
```json
{
  "table": "products",
  "select": "*, categories(*)",
  "filters": {
    "price": "between.(50,300)",
    "category_id": "in.(1,2,3)",
    "tags": 'ov.["featured","sale"]',
    "metadata": 'cs.{"type":"premium"}'
  }
}
```

### Count Queries and Aggregation
```json
{
  "table": "orders",
  "select": "count(*)",
  "filters": {
    "status": "not.eq.cancelled",
    "total": "gte.100"
  }
}
```

### Enhanced User Management
Ask Claude: *"Create a user with custom metadata and send them a magic link to our dashboard"*

Gets complete user objects:
```json
{
  "user": {
    "id": "uuid",
    "email": "user@example.com",
    "phone": null,
    "email_confirmed_at": "2024-01-01T10:00:00Z",
    "last_sign_in_at": null,
    "user_metadata": {"role": "admin"},
    "app_metadata": {"source": "api"}
  }
}
```

With enhanced magic links:
```json
{
  "link": "https://...",
  "redirect_to": "https://myapp.com/dashboard",
  "expires_at": "2024-01-01T11:00:00Z",
  "valid_for_minutes": 60
}
```

## ⚙️ Configuration

### Environment Variables
- `SUPABASE_URL` - Your project URL (required)
- `SUPABASE_SERVICE_KEY` - Service role key (required)

### Claude Code Integration
Add to your Claude Code MCP configuration to enable natural language database interactions.

## 🔒 Security & Best Practices

### **Service Key Protection**
- ✅ Store in environment variables only
- ❌ NEVER expose in client-side code
- ✅ Use on secure, trusted systems only

### **Access Control**
- Service key **bypasses all RLS policies**
- Grants **unrestricted access** to all data
- Implement additional authorization if needed

### **Production Readiness**
- Enable request logging for audit trails
- Consider rate limiting for high-traffic usage
- Regularly rotate service keys
- Monitor access patterns

## 🛠️ Development

### Project Structure
```
src/
├── index.ts              # MCP server with 21 tools
├── supabase-client.ts    # Authenticated client
├── tools/
│   ├── database.ts       # CRUD + schema discovery
│   ├── auth.ts           # Enhanced user management
│   └── storage.ts        # File operations
└── utils/
    ├── validation.ts     # Zod schemas
    └── error-handler.ts  # Error utilities
```

### Testing
```bash
npm test                  # Run comprehensive test suite
npm run build            # TypeScript compilation
npm run dev              # Development with auto-reload

# Test all 21 tools with your Supabase instance:
cp test-mcp-server.example.js test-mcp-server.js
# Edit test-mcp-server.js with your credentials
node test-mcp-server.js
```

### Contributing
1. Fork the repository
2. Create feature branch (`feature/amazing-feature`)
3. Add tests for new functionality
4. Submit pull request with clear description

## 🚧 Capabilities & Limitations

### **✅ What This Server Provides:**
- Dynamic schema discovery without hardcoding
- Complete CRUD operations on all tables with 23+ PostgREST operators
- Embedded resources for joining related tables (*, orders(*), profiles(*))
- Count aggregation queries with helpful error messages for unsupported aggregations
- Full admin user management capabilities
- Unrestricted file storage operations
- PostgreSQL function execution
- Production-ready error handling with clear guidance
- Type-safe operations with validation

### **❌ What It Cannot Do:**
- Complex aggregations (SUM, AVG, MAX, MIN) - requires RPC functions
- GROUP BY operations - requires stored procedures
- Modify database schema (CREATE/ALTER tables)
- Execute raw SQL queries directly
- Create database functions or triggers
- Access information_schema (PostgREST limitation)

### **🎯 Perfect For:**
- AI-assisted database operations
- Dynamic content management
- User administration interfaces
- File management systems
- Rapid prototyping and development

## 📖 Documentation

- **Full Tool Reference**: See `test-mcp-server.example.js` for comprehensive examples
- **Supabase Docs**: [https://supabase.com/docs](https://supabase.com/docs)
- **MCP Protocol**: [Model Context Protocol Specification](https://modelcontextprotocol.io/)

## 📄 License

MIT License - see LICENSE file for details