# Supabase MCP Server Setup

This workspace includes Model Context Protocol configurations that let GitHub Copilot (or any other MCP-compatible client) connect straight to the Supabase stack that you are running on the local network (192.168.0.150):

- `supabase-local` → direct Postgres connection (full SQL access).
- `supabase-rest` → Supabase REST/PostgREST API using the `service_role` key (ideal when you only have the Supabase keys and not the raw Postgres password).

## Prerequisites

1. **Supabase Docker stack is running** on the target host (192.168.0.150). The direct Postgres service is exposed on LAN port `6543` (it maps to container port `5432`).
2. You know the **Postgres connection password** (usually configured through the `POSTGRES_PASSWORD` environment variable in the Docker compose file). The default database created by Supabase is `postgres`.
3. Your development machine already has **Node.js ≥ 18** and can run `npx`.

## Connection string

Build a connection string in the format:

```
postgresql://postgres:<POSTGRES_PASSWORD>@192.168.0.150:6543/postgres
```

For example:

```
postgresql://postgres.chat-local:yourStrongPassword@192.168.0.150:5432/postgres
```

> ⚠️ Never commit a real password to version control. Store the connection string in an environment variable instead.

## Environment variable

Create (or add to) an `.env` file at the workspace root with:

```
SUPABASE_TENANT=chat-local
SUPABASE_DB_URL=postgresql://postgres.chat-local:<POSTGRES_PASSWORD>@192.168.0.150:5432/postgres
```

Then ensure the variable is loaded by your shell (e.g. `Get-Content .env | foreach { if($_ -match '^(.*?)=(.*)$'){ [System.Environment]::SetEnvironmentVariable($matches[1], $matches[2]) } }` in PowerShell, or use a dedicated dotenv loader).

Alternatively, set it directly in the shell session before launching VS Code:

```
$env:SUPABASE_DB_URL = "postgresql://postgres.chat-local:<POSTGRES_PASSWORD>@192.168.0.150:5432/postgres"
```

## MCP configuration

The workspace now contains `.github/copilot/mcp.json`:

```json
{
  "version": 1,
  "mcpServers": {
    "supabase-local": {
      "command": "npx",
      "args": [
            "--yes",
            "-p",
            "@modelcontextprotocol/server-postgres",
            "mcp-server-postgres",
            "${SUPABASE_DB_URL}"
          ],
          "env": {
            "SUPABASE_DB_URL": "${env:SUPABASE_DB_URL}",
            "npm_config_audit": "false"
          },
      "metadata": {
        "description": "Postgres access to the local Supabase instance running on 192.168.0.150",
        "docs": "See .github/copilot/supabase-mcp.md for setup instructions."
      }
    },
    "supabase-rest": {
      "command": "npx",
      "args": [
        "--yes",
        "@modelcontextprotocol/server-openapi",
        "--spec",
        "${SUPABASE_OPENAPI_SPEC}",
        "--server-url",
        "${SUPABASE_REST_URL}",
        "--header",
        "apikey: ${SUPABASE_SERVICE_ROLE_KEY}",
        "--header",
        "Authorization: Bearer ${SUPABASE_SERVICE_ROLE_KEY}"
      ],
      "env": {
        "SUPABASE_OPENAPI_SPEC": "${env:SUPABASE_OPENAPI_SPEC}",
        "SUPABASE_REST_URL": "${env:SUPABASE_REST_URL}",
        "SUPABASE_SERVICE_ROLE_KEY": "${env:SUPABASE_SERVICE_ROLE_KEY}"
      },
      "metadata": {
        "description": "Supabase REST API via service_role key for internal development",
        "docs": "See .github/copilot/supabase-mcp.md for instructions on generating the OpenAPI spec and required environment variables."
      }
    }
  }
}
```

When Copilot loads this configuration it will launch:

- The Postgres MCP server via `npx @modelcontextprotocol/server-postgres`, injecting your connection string through the `SUPABASE_DB_URL` environment variable. The server exposes SQL querying and management tools over MCP, enabling Copilot to run SQL commands directly against your Supabase database.
- The Supabase REST MCP server via `npx @modelcontextprotocol/server-openapi`. It uses the generated OpenAPI schema together with the `service_role` key to hit the PostgREST endpoints (tables, RPCs) exactly like `supabase-js` would.

You can enable one or both servers by setting the corresponding environment variables.

## Supabase REST (service_role) setup

1. **Generate OpenAPI schema** (once per Supabase instance; repeat if schema changes).

   - Quick one-liner:

     ```powershell
     $env:SUPABASE_SERVICE_ROLE_KEY = "<your-service-role-key>"
     Invoke-WebRequest `
       -Uri "http://192.168.0.150:8000/rest/v1/?apikey=$($env:SUPABASE_SERVICE_ROLE_KEY)" `
       -OutFile ".github/copilot/supabase-rest-openapi.json"
     ```

   - Or run the helper script (prompts above default):

     ```powershell
     pwsh scripts/fetch-supabase-openapi.ps1 -ServiceRoleKey "<your-service-role-key>"
     ```

   This downloads the PostgREST schema that reflects all tables, views, and RPC functions currently exposed by Supabase.

2. **Add/extend `.env`** with:

   ```
  SUPABASE_REST_URL=http://192.168.0.150:8000/rest/v1
   SUPABASE_SERVICE_ROLE_KEY=<your-service-role-key>
   SUPABASE_OPENAPI_SPEC=${workspaceFolder}/.github/copilot/supabase-rest-openapi.json
   ```

3. **Load variables in your shell** before launching VS Code / Copilot:

   ```powershell
  $env:SUPABASE_REST_URL = "http://192.168.0.150:8000/rest/v1"
   $env:SUPABASE_SERVICE_ROLE_KEY = "<your-service-role-key>"
   $env:SUPABASE_OPENAPI_SPEC = "$PWD/.github/copilot/supabase-rest-openapi.json"
   ```

4. **Verify**: In the Copilot MCP panel, you should see both `supabase-local` and `supabase-rest`. Either can be disabled by omitting the required environment variables.

## Usage

1. Start VS Code (or the Copilot agent) *after* the environment variable is set.
2. The MCP server will be launched automatically the first time Copilot needs database access. You can verify in Copilot’s “MCP Servers” panel that `supabase-local` is running.
3. Ask Copilot to run SQL, inspect tables, or perform migrations; it will route the requests through the MCP server to Supabase/Postgres.
4. For REST workflows (e.g., hitting tables as REST endpoints, calling RPCs with structured payloads), Copilot can use `supabase-rest` to craft HTTP requests automatically based on the OpenAPI schema.

## One-click install for all VS Code workspaces

If you want Supabase MCP servers to be available in every VS Code workspace (without copying `.github/copilot/mcp.json` around), run the helper script once from this repository. It will:

- Persist the required `SUPABASE_*` environment variables for your Windows user profile using `setx`.
- Merge the Supabase MCP servers into VS Code’s global Copilot config at `%APPDATA%\Code\User\globalStorage\GitHub.copilot\mcp.json`.

```powershell
pwsh scripts\install-supabase-mcp.ps1 -ConnectionString "postgresql://postgres.chat-local:<POSTGRES_PASSWORD>@192.168.0.150:6543/postgres"
```

Add `-IncludeRestServer -ServiceRoleKey "<service-role-key>" -OpenApiSpec "C:\\path\\to\\supabase-rest-openapi.json"` if you also want the REST server registered globally. The script prompts for any missing arguments.

After the script finishes:

1. Restart VS Code (or run **Developer: Reload Window**) so Copilot reloads the global MCP configuration.
2. In the MCP view you should now see `supabase-local` (and optionally `supabase-rest`) even in projects that do not ship their own `mcp.json`.
3. Remember that VS Code inherits environment variables only on launch. If you change the connection string later, rerun the script and restart VS Code.

### Manual launch (optional)

To launch the server manually for debugging:

```powershell
$env:SUPABASE_DB_URL = "postgresql://postgres.chat-local:yourStrongPassword@192.168.0.150:5432/postgres"
npx --yes --no-audit -p @modelcontextprotocol/server-postgres mcp-server-postgres $env:SUPABASE_DB_URL
```

The package publishes the executable under the name `mcp-server-postgres`, so `npx` needs the `-p` flag to install the scoped package and then run that binary. The connection string must be the first positional argument.

> Tip: if your npm version doesn’t support the `--no-audit` switch, set the environment variable `npm_config_audit=false` (already included in `mcp.json`). This avoids `npm exec` aborting when a mirror registry doesn’t implement the audit API.

> Note: launching the server manually in a console will exit as soon as a line like `select 1;` hits stdin. That’s expected because the MCP transport treats console keystrokes as malformed JSON. When Copilot runs the same command there’s no human typing, so the process stays up.

## Resetting the MCP setup

If you need to wipe the previous configuration and start from scratch:

1. **Remove cached servers in VS Code** – open the Copilot MCP panel, stop `supabase-local`/`supabase-rest`, then click “Forget server”.
2. **Reload the environment variables** in the shell you will launch VS Code from:
  ```powershell
  .\scripts\load-supabase-env.ps1
  ```
3. **Verify the tenant DSN** contains `.chat-local`:
  ```powershell
  Get-ChildItem Env:SUPABASE_DB_URL
  ```
4. **Reopen VS Code** (or run `Developer: Reload Window`) so Copilot ingests the fresh `.github/copilot/mcp.json`.
5. **Run a smoke test**:
  ```powershell
  python scripts\test_supabase_connection.py
  ```
  Once this prints `Query result: 1`, trigger `SELECT 1;` from Copilot—the MCP server should respond with `[{"?column?":1}]`.

The server will listen for MCP connections on stdin/stdout; you’ll see logs confirming connection success.

## Security notes

- The configuration uses a **service-level Postgres connection** (superuser). Treat the password as a secret; rotate it regularly.
- Consider creating a dedicated Postgres role with least privilege for MCP usage if you don’t need full superuser access.
- Restrict network access so only trusted machines can reach `192.168.0.150:5432`.
- On shared repositories, provide a template `.env.example` rather than real credentials.

## Troubleshooting

- **Connection refused**: ensure Docker stack exposes port 5432 to your LAN and that firewalls allow the traffic.
- **Authentication failed**: double-check the password in `.env` matches the one set in your Supabase Docker environment.
- **TLS/SSL errors**: the default local Supabase Postgres runs without TLS. If you enable TLS, update the connection string with `?sslmode=require` and provide the certificate parameters if needed.
- **Package not found**: run `npm install --save-dev @modelcontextprotocol/server-postgres` locally to cache the package, or allow `npx` to download it on first use.

With this configuration in place, Copilot can manage your Supabase database over MCP without additional manual wiring.