# Supabase Model Context Protocol (MCP) Integration with Cursor IDE — Puzzle #25

> Comprehensive architectural guide and production walkthrough for connecting Supabase's managed Model Context Protocol (MCP) server to Cursor IDE, enabling live database schema introspection, secure SQL assistance, and agentic workflows.

---

## 1. Executive Summary & Challenge Clue Deconstruction

In modern full-stack development, AI coding assistants have evolved beyond simple autocomplete into autonomous agentic pair programmers. However, their effectiveness has traditionally been throttled by **database blindness**. Developers had to manually export DDL files, copy-paste table definitions, or paste raw SQL queries into chat prompts—a brittle, repetitive workflow that leaks sensitive credentials and quickly leads to hallucinated schema modifications.

In **Puzzle #25**, we tackle this fundamental developer experience challenge:

> **Challenge Mandate**:
> 1. *Add the MCP URL in the Cursor configuration.*
> 2. *Authorize the MCP server.*
> 3. *Show that it works by asking the agent to list the table schemas.*
>
> **Challenge Clue**: *"Find the 'Connect' button in Supabase and navigate to MCP to find the direct connection option."*

The clue unlocks a native capability recently introduced into the Supabase platform: instead of requiring developers to run a local bridge proxy or node daemon, Supabase hosts a managed, multi-tenant Model Context Protocol endpoint accessible directly from the dashboard's **"Connect"** modal.

---

## 2. Theoretical Foundations: The Model Context Protocol (MCP)

### 2.1 The Paradigm Shift: Dynamic Tool Execution vs Static Context Dumping
Before MCP, passing database context to an LLM required one of two flawed approaches:
- **Static Schema Prompts**: Dumping thousands of lines of SQL schema into the prompt, exhausting context windows, increasing latency, and decaying as migrations occur.
- **Direct Database Passwords**: Providing the LLM with direct PostgreSQL connection strings (`postgres://...`), exposing master credentials and risking accidental table drops.

The **Model Context Protocol (MCP)**, open-sourced by Anthropic and rapidly adopted by tools like Cursor, standardizes a two-way JSON-RPC 2.0 contract. The AI agent does not receive raw database credentials. Instead, it interacts with an intermediate MCP server exposing specialized semantic tools (`list_tables`, `execute_sql`, `get_table_schema`).

```
┌─────────────────────────────────────────────────────────────┐
│                      Cursor IDE Agent                       │
│     "Can you show me the schema of tables in database?"     │
└──────────────────────────────┬──────────────────────────────┘
                               │
                               │ JSON-RPC 2.0: tools/call
                               ▼
┌─────────────────────────────────────────────────────────────┐
│                 Supabase Remote MCP Server                  │
│                (https://mcp.supabase.com/mcp)               │
│                                                             │
│   • OAuth 2.0 / PAT Token Validation                        │
│   • Scope Enforcement (Read-Only vs Read-Write)             │
│   • Schema & System Catalog Query Engine                    │
└──────────────────────────────┬──────────────────────────────┘
                               │
                               │ Authenticated Postgres Protocol
                               ▼
┌─────────────────────────────────────────────────────────────┐
│                 PostgreSQL Cloud Database                   │
│   • information_schema.tables / pg_catalog                  │
│   • Active Row Level Security (RLS) Enforcement             │
└─────────────────────────────────────────────────────────────┘
```

### 2.2 Wire Protocol: JSON-RPC 2.0 over Server-Sent Events (SSE)
Supabase’s hosted MCP uses **HTTP with Server-Sent Events (SSE)** as its primary transport mechanism. Unlike standard request-response REST APIs:
1. **Client Handshake (`initialize`)**: Cursor sends an initialization payload negotiating protocol version, supported client capabilities, and client metadata.
2. **Tools Discovery (`tools/list`)**: The Supabase MCP server responds with its active catalog of 20 tools, complete with JSON schema definitions for parameter validation.
3. **Execution Dispatch (`tools/call`)**: When the LLM decides to inspect tables, Cursor dispatches a tool call with parameters (e.g. `schemas: ["public"]`), and streams back structured JSON metadata.

---

## 3. Step-by-Step Implementation Walkthrough

### Step 1: Discovering the MCP Endpoint via the "Connect" Button
1. Open your project dashboard in **Supabase**.
2. In the top navigation header, locate and click the **Connect** button (positioned next to project settings and status badges).
3. In the resulting modal (*"Connect to your project — Get the connection strings and environment variables for your app"*), navigate across the top tabs past Connection String, App Frameworks, and ORMs to select the **MCP** tab.

```
+-------------------------------------------------------------------------+
| Connect to your project                                                 |
| Get the connection strings and environment variables for your app.     |
|                                                                         |
| Connection String | App Frameworks | Mobile | ORMs | API Keys | [ MCP ] |
+-------------------------------------------------------------------------+
```

### Step 2: Configuring Feature Scopes & Options
The MCP configuration panel exposes several vital options:
- **Read-only Toggle**: For standard coding and query synthesis, toggle **Read-only ON** to prevent the agent from executing destructive DDL (`DROP TABLE`, `ALTER TABLE`) or unauthorized DML (`DELETE`, `UPDATE`).
- **Feature Groups**: Select which subsystems are exposed. By default, Database, Auth, and Edge Functions are enabled, while large storage bucket inspection can be optionally excluded.
- **Server URL**: Supabase generates a project-specific endpoint:
  ```
  https://mcp.supabase.com/mcp?project_ref=pr-fintech-prod-8821
  ```

### Step 3: Registering the Server in Cursor
You can register the server using either the automated one-click launcher or manual JSON configuration:

#### Method A: One-Click Integration ("Add to Cursor")
Clicking the **[Add to Cursor]** button dispatches a custom OS protocol handler (`cursor://...`). Cursor opens a native confirmation prompt:
> *"Add MCP Server: supabase (https://mcp.supabase.com/mcp?project_ref=...)"*

Clicking confirm registers the server into Cursor’s global tools registry immediately.

#### Method B: Project Configuration (`.cursor/mcp.json`)
For team collaboration and repository-level reproducibility, place the configuration in `.cursor/mcp.json` at the root of your project:

```json
{
  "mcpServers": {
    "supabase": {
      "url": "https://mcp.supabase.com/mcp?project_ref=pr-fintech-prod-8821"
    }
  }
}
```

### Step 4: Authorizing the Connection in Cursor Settings
1. Open Cursor and navigate to **Settings** (`Cmd + ,` on macOS or `Ctrl + ,` on Windows).
2. Under **Cloud Agents**, select **Tools & MCP**.
3. Under **Installed MCP Servers**, verify that `supabase` is listed.
4. If an authorization prompt appears, click **Authorize**. This redirects to Supabase's OAuth authorization gateway in your browser, granting Cursor temporary scoped access tokens to your project.
5. Once authorized, the status updates with a green toggle and confirms: **"20 tools enabled"**.

---

## 4. Verification: Schema Introspection in Cursor Agent

To prove that the MCP server is operational, open Cursor’s AI Composer or Agent Chat (`Cmd + I` or `Cmd + L`):

### User Prompt:
```text
can you show me the scheme of the tabes in the database
```

### Agent Reasoning & Execution Trace:
1. **Model Thought**:
   ```text
   Thought (2s): The user wants an overview of the database tables and schema.
   The Supabase MCP server is connected and provides the `list_tables` tool.
   ```
2. **Tool Invocation**:
   ```json
   Ran list_tables:supabase
   schemas: ["public"]
   ```
3. **Model Thought**:
   ```text
   Thought (1s): Received table schema for 16 tables. Primary target: `credit_products`.
   Formatting schema attributes, primary keys, and RLS policies into structured markdown.
   ```

### Synthesized Response Output:
The agent returns structured, accurate schema documentation directly from live PostgreSQL system tables:

#### 1. `credit_products`
- **RLS**: enabled
- **Primary key**: `id`
- **Row count**: `16`

| Column | Type | Nullable | Default / Notes |
| :--- | :--- | :--- | :--- |
| `id` | `uuid` | No | `gen_random_uuid()` |
| `category` | `text` | No | Check `'personal' \| 'business' \| 'mortgage'` |
| `icon` | `text` | No | `'lucide:credit-card'` |
| `title` | `text` | No | — |
| `apr` | `numeric` | No | — |
| `min_credit_score` | `integer` | No | `600` |
| `max_amount` | `numeric` | No | — |
| `created_at` | `timestamptz` | No | `now()` |

---

## 5. Complete 20-Tool Supabase MCP Catalog

When enabled, the Supabase MCP server equips the Cursor agent with a specialized toolkit:

| Tool Name | Scope | Description |
| :--- | :--- | :--- |
| `list_tables` | Database | Lists all public and custom schema tables with row counts and RLS status |
| `get_table_schema` | Database | Inspects columns, data types, foreign keys, and nullability for a specific table |
| `execute_sql` | Database | Executes custom SQL statements (read-only or read-write based on config) |
| `list_views` | Database | Returns database views and materialized views |
| `list_extensions` | Database | Displays installed PostgreSQL extensions (`uuid-ossp`, `pgcrypto`, `vector`) |
| `list_indexes` | Performance | Returns table indexes, index types (B-tree, GIN, IVFFlat), and sizes |
| `list_policies` | Security | Inspects active Row Level Security (RLS) policies across all tables |
| `get_advisors` | Security | Fetches Supabase Security Advisor and Performance Advisor recommendations |
| `list_migrations` | Database | Lists applied schema migrations and tracking state |
| `explain_query` | Performance | Runs `EXPLAIN (ANALYZE, BUFFERS)` on queries to identify performance bottlenecks |
| `list_auth_users` | Auth | Reads user records (sanitized, excluding credential hashes) |
| `list_storage_buckets` | Storage | Lists object storage buckets, public permissions, and size limits |
| `list_edge_functions` | Serverless | Lists deployed Deno edge functions and invocation stats |
| `get_logs` | Observability | Queries Postgres, API gateway, Auth, and Edge Function log streams |
| `search_docs` | Reference | Performs semantic search across official Supabase documentation |
| `generate_typescript_types` | Tooling | Generates end-to-end type-safe TypeScript interfaces from Postgres tables |
| `list_foreign_keys` | Schema | Maps relational graph dependencies and cascade rules |
| `list_triggers` | Database | Lists database triggers and associated stored procedures |
| `list_functions` | Database | Lists Postgres PL/pgSQL stored procedures and RPC functions |
| `get_database_stats` | Telemetry | Reports active connections, cache hit ratio, and table bloat |

---

## 6. Security, Row Level Security (RLS) & Best Practices

1. **Enforce Read-Only Mode**: In day-to-day coding, keep the MCP server configured in Read-Only mode. This allows the AI to inspect schemas and generate code without accidental data deletion.
2. **RLS Protects Real Data**: When `execute_sql` is invoked, queries execute under authenticated roles. RLS policies guarantee tenant isolation even if an AI prompt crafts an overly broad query.
3. **No Hardcoded Tokens**: Never commit Supabase service role keys to `.cursor/mcp.json`. The remote URL uses project references and browser OAuth flows, ensuring individual developer tokens remain compartmentalized.
4. **Environment Segregation**: Always connect your local Cursor client to `development` or `staging` project references. Never expose production database references to unchecked automated agent runs.

---

## 7. Verification & Diagnostic Checklist

- [x] Located **Connect** button in Supabase header.
- [x] Switched to **MCP** tab and captured `https://mcp.supabase.com/mcp?project_ref=...`.
- [x] Added configuration to `.cursor/mcp.json` or initiated one-click Cursor launcher.
- [x] Verified Cursor Settings -> Tools & MCP displays green toggle with 20 tools enabled.
- [x] Successfully dispatched `can you show me the scheme of the tabes in the database` in Composer.
- [x] Verified agent executed `list_tables:supabase` and produced structured markdown schema.