p24-infra Docs

supabase-management-api-sql-windows

4 min read

Playbook: Supabase SQL via Management API from Windows

When to use

Use the Supabase Management API for SQL execution when:

  • psql from Windows fails with “server closed the connection unexpectedly” (even via the bms-4 socat proxy at 54.36.123.110:15432)
  • You need to apply a migration without SSH access to a VPS running psql
  • You are running from the local Windows dev machine and want a direct path to Supabase

The bms-4 socat proxy (54.36.123.110:15432 → Supabase DB) reliably fails from Windows psql due to TLS negotiation differences. The Management API is the correct alternative.

Prerequisites

Token: ROLE_SECRET_MANAGER_SUPABASE_ACCESS_TOKEN — a personal access token (sbp_...) from the Supabase dashboard.

  • Primary source: secrets/role-secret-manager.env.sops (SOPS+age)
  • Do NOT rely on .env.local — it may be stale (401 will result)

Corrected 2026-07-20 (#4387). This playbook previously named SUPABASE_ACCESS_TOKEN in secrets/monitoring.env.sops. That key does not exist in monitoring.env.sops (verified by key-name scan across every secrets/*.env.sops); the only Management API token in the repo is ROLE_SECRET_MANAGER_SUPABASE_ACCESS_TOKEN in secrets/role-secret-manager.env.sops. Following the old instructions yielded an empty token and an unexplained 401.

Endpoint: POST https://api.supabase.com/v1/projects/{ref}/database/query

  • {ref} = mwkqmgadqnkkihjdeqsi for the p24-infra Supabase project

Standard pattern

# Always extract the token fresh from SOPS in the SAME PowerShell call that uses it.
# Shell state (including $env: vars) does NOT persist between Claude Code PowerShell tool calls.
 
$env:SOPS_AGE_KEY_FILE = "C:\Users\konar\.age\p24-infra-keys.txt"
$lines = sops --decrypt --input-type dotenv --output-type dotenv C:\code_2026\p24-infra\secrets\role-secret-manager.env.sops 2>$null
$token = ($lines | Where-Object { $_ -match "^ROLE_SECRET_MANAGER_SUPABASE_ACCESS_TOKEN=" } | Select-Object -First 1) -replace "^ROLE_SECRET_MANAGER_SUPABASE_ACCESS_TOKEN=", ""
 
$ref = "mwkqmgadqnkkihjdeqsi"
$url = "https://api.supabase.com/v1/projects/$ref/database/query"
 
function Invoke-SbQuery($sql, $label) {
  # Use manual escaping — both ConvertTo-Json (PS 5.1 corrupts SQL with single-quotes/dashes)
  # AND JavaScriptSerializer.Serialize(@{}) throw "cyclic reference" on PowerShell hashtables.
  # Manual escaping is the only reliable path (verified 2026-06-30).
  $escaped = $sql -replace '\\','\\' -replace '"','\"' -replace "`r`n",'\n' -replace "`n",'\n' -replace "`r",'\n' -replace "`t",'\t'
  $body = '{"query":"' + $escaped + '"}'
  $bytes = [System.Text.Encoding]::UTF8.GetBytes($body)
 
  $req = [System.Net.WebRequest]::Create($url)
  $req.Method = "POST"
  $req.ContentType = "application/json"
  $req.Headers.Add("Authorization", "Bearer $token")
  $req.ContentLength = $bytes.Length
  $s = $req.GetRequestStream(); $s.Write($bytes, 0, $bytes.Length); $s.Close()
 
  try {
    $result = (New-Object System.IO.StreamReader(($req.GetResponse()).GetResponseStream())).ReadToEnd()
    "OK: $label — $result"
  } catch [System.Net.WebException] {
    # Invoke-RestMethod swallows the response body on 4xx; System.Net.WebRequest exposes it
    $errBody = (New-Object System.IO.StreamReader($_.Exception.Response.GetResponseStream())).ReadToEnd()
    "ERR: $label — $errBody"
  }
}
 
# Usage examples
Invoke-SbQuery "SELECT COUNT(*) FROM infra_operations;" "count"
Invoke-SbQuery (Get-Content "monitoring\supabase\migrations\my_migration.sql" -Raw) "migration-name"

Why not ConvertTo-Json or JavaScriptSerializer?

Both serialization approaches fail on PowerShell 5.1 (Windows 10):

ConvertTo-Json — produces invalid JSON when the SQL contains:

  • Single-quoted string literals (common in SQL)
  • Double-dash comments (-- comment)
  • Adjacent newlines

Symptom: Supabase returns {"message":"Expected ',' or '}' after property value in JSON at position N"}.

[System.Web.Script.Serialization.JavaScriptSerializer]::new().Serialize(@{query=$sql}) — throws "cyclic reference detected" when serializing a PowerShell hashtable on PS 5.1.

Fix: Manual string escaping (verified 2026-06-30 — the only approach that works):

$escaped = $sql -replace '\\','\\' -replace '"','\"' -replace "`r`n",'\n' -replace "`n",'\n' -replace "`r",'\n' -replace "`t",'\t'
$body = '{"query":"' + $escaped + '"}'

Why not $env:VAR across tool calls?

Claude Code’s PowerShell tool initializes a fresh shell for each call. Environment variables set with $env:VARNAME = "..." are NOT available in subsequent tool calls.

Rule: Always extract the token (and any other needed env vars) at the top of the same PowerShell call that consumes them.

Capturing error details from 4xx responses

Invoke-RestMethod swallows the response body when the HTTP status is 4xx/5xx — the exception message only shows (400) Bad Request. Use [System.Net.WebRequest] directly to access the error stream:

} catch [System.Net.WebException] {
  $errBody = (New-Object System.IO.StreamReader($_.Exception.Response.GetResponseStream())).ReadToEnd()
  Write-Host "SQL error: $errBody"
}

Testing token validity

$hdr = @{ Authorization = "Bearer $token" }
(Invoke-RestMethod -Method Get -Uri "https://api.supabase.com/v1/projects" -Headers $hdr).Count
# Returns: number of projects (e.g. 5) → token is valid
# Returns 401 → token is stale; re-read from SOPS (not .env.local)

Escalation

If the Management API also returns 401 after reading fresh from SOPS:

  1. Token may have been rotated — check docs/secrets-rotation-log.md for recent rotations
  2. Re-generate in the Supabase dashboard: Settings → API → Access tokens
  3. Update secrets/role-secret-manager.env.sops following the standard SOPS edit pattern
  4. Update .env.local as a fallback copy

Graph View

Backlinks