---
name: supabase-project-setup
description: Provision a new Supabase project + database entirely via Management API — create project, run SQL (schema + seed), get API keys, wire up Vercel env vars, and redeploy. Use when psql is unavailable (IPv6 on Oracle Cloud blocks direct DB host), pooler isn't ready, or the user wants fully automated setup without touching the Supabase GUI.
---

# Supabase Project Setup via Management API

Use when you need to create a brand new Supabase project and database for a deployed web app, entirely via API — no GUI, no psql.

## When to use

- User says "set up Supabase for me" or "I don't want to do the setup manually"
- Deploying an app that needs a fresh Supabase backend
- psql is unavailable (Oracle Cloud VMs can't reach IPv6-only Supabase direct DB hosts; pooler may not be provisioned yet for new projects)
- Need São Paulo region (sa-east-1) for low latency from Argentina

## Prerequisites

- Supabase access token (`SUPABASE_ACCESS_TOKEN` env var)
- Vercel token (`VERCEL_TOKEN` env var) if app is on Vercel
- SQL schema file ready to execute

## Step 1 — List existing projects to get org_id

```bash
curl -s 'https://api.supabase.com/v1/projects' \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" | python3 -c "
import json, sys
for p in json.load(sys.stdin):
    print(f\"{p['name']} | {p['ref']} | {p['status']} | org={p['organization_id']}\")
"
```

Note the `organization_id` — you need it to create new projects.

**Free plan limit:** max 2 active projects. If at limit, pause an inactive one first:
```bash
curl -s -X POST "https://api.supabase.com/v1/projects/OLD_REF/pause" \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN"
```

## Step 2 — Create the project

```bash
DB_PASS=$(openssl rand -base64 18 | tr -d "=+/" | cut -c1-20)

curl -s -X POST 'https://api.supabase.com/v1/projects' \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
  -H 'Content-Type: application/json' \
  -d "{
    \"name\": \"myproject\",
    \"region\": \"sa-east-1\",
    \"db_pass\": \"$DB_PASS\",
    \"organization_id\": \"YOUR_ORG_ID\"
  }"
```

Available regions: `us-east-1`, `us-west-1`, `eu-west-1`, `eu-central-1`, `ap-southeast-1`, `ap-northeast-1`, `sa-east-1`

Record `ref` and `DB_PASS` from the response.

**SAVE THE PASSWORD.** Write it to a secure location. It's only displayed at creation time.

Wait ~8-10 seconds for the project to provision before using it.

## Step 3 — Run SQL via Management API (no psql needed!)

**Why not psql?** Supabase direct DB hosts (`db.REF.supabase.co`) use IPv6 — Oracle Cloud VMs can't reach them. The pooler (`aws-X-region.pooler.supabase.com`) may return "Tenant or user not found" for brand new projects. The Management API SQL endpoint always works.

```bash
python3 -c "
import json
with open('/path/to/schema.sql') as f:
    sql = f.read()
print(json.dumps({'query': sql}))
" | curl -s -X POST "https://api.supabase.com/v1/projects/YOUR_REF/database/query" \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d @-
```

Success returns `[]`. Any other output is an error message.

**PITFALL — Date type casting:** When running SQL via the Management API, PostgreSQL is stricter about implicit type conversions. Date string literals in INSERTs need explicit casts:

❌ `'2021-08-01'` → **ERROR: column "date" is of type date but expression is of type text**
✅ `'2021-08-01'::date`

This affects all date/timestamp columns in INSERT statements. Always add `::date`, `::timestamp`, `::timestamptz` casts.

**PITFALL — SQL errors roll back the entire query.** If one statement fails, nothing is committed. Fix the SQL and re-run. Use `DROP TABLE IF EXISTS ... CASCADE` at the top of your schema to make re-runs safe.

## Step 4 — Verify data

```bash
python3 -c "
import json
for q in [
    'SELECT count(*) FROM politicians;',
    'SELECT count(*) FROM lobby_groups;',
    'SELECT count(*) FROM connections;'
]:
    print(json.dumps({'query': q}))
" | while read line; do
  echo "$line" | curl -s -X POST "https://api.supabase.com/v1/projects/YOUR_REF/database/query" \
    -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
    -H "Content-Type: application/json" \
    -d @-
  echo ""
done
```

## Step 5 — Get API keys

```bash
curl -s "https://api.supabase.com/v1/projects/YOUR_REF/api-keys" \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" | python3 -c "
import json, sys
keys = json.load(sys.stdin)
for k in keys:
    if k.get('type') == 'publishable':
        print(f\"ANON_KEY={k['api_key']}\")
        break
print('SUPABASE_URL=https://YOUR_REF.supabase.co')
"
```

Use the `publishable` key for `NEXT_PUBLIC_SUPABASE_ANON_KEY`. Use `service_role` key for `SUPABASE_SERVICE_ROLE_KEY` (server-side only, never expose to client).

## Step 6 — Find Vercel project and set env vars

```bash
# Find the project
curl -s "https://api.vercel.com/v9/projects?limit=20" \
  -H "Authorization: Bearer $VERCEL_TOKEN" | python3 -c "
import json, sys
data = json.load(sys.stdin)
projects = data if isinstance(data, list) else data.get('projects', [])
for p in projects:
    if 'myproject' in p.get('name', '').lower():
        print(p['id'])
"

# Set env vars
PROJECT_ID="prj_..."

for KV in "NEXT_PUBLIC_SUPABASE_URL=https://YOUR_REF.supabase.co" "NEXT_PUBLIC_SUPABASE_ANON_KEY=sb_pub_YOURKEY"; do
  KEY="${KV%%=*}"
  VAL="${KV#*=}"
  curl -s -X POST "https://api.vercel.com/v10/projects/${PROJECT_ID}/env" \
    -H "Authorization: Bearer $VERCEL_TOKEN" \
    -H "Content-Type: application/json" \
    -d "{\"key\":\"$KEY\",\"value\":\"$VAL\",\"type\":\"encrypted\",\"target\":[\"production\",\"preview\",\"development\"]}"
done
```

Vercel create response uses `{"created": {...}}` — both `created` and `id` responses indicate success.

## Step 7 — Redeploy

**Preferred method: Vercel CLI** (more reliable than API redeploy, especially after failed builds)

```bash
cd /path/to/project && vercel --token "$VERCEL_TOKEN" --prod --yes
```

This does a fresh build + deploy and automatically aliases to your production domain. Wait ~30s, verify: `curl -s -o /dev/null -w "%{http_code}" https://myproject.vercel.app`

**Fallback: API redeploy** (only works if the last deployment was READY)

```bash
# Get latest production deployment ID
DEPLOY_ID=$(curl -s "https://api.vercel.com/v6/deployments?projectId=${PROJECT_ID}&limit=1&target=production" \
  -H "Authorization: Bearer $VERCEL_TOKEN" | python3 -c "
import json, sys
data = json.load(sys.stdin)
deps = data.get('deployments', [])
print(deps[0]['uid'] if deps else '')
")

# Trigger redeploy
curl -s -X POST "https://api.vercel.com/v13/deployments" \
  -H "Authorization: Bearer $VERCEL_TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"name\":\"myproject\",\"deploymentId\":\"$DEPLOY_ID\",\"meta\":{\"action\":\"redeploy\"}}"
```

**PITFALL — API redeploy fails when last deployment errored:** If the most recent deployment is in ERROR state, the API redeploy (`v13/deployments` with `deploymentId`) may keep failing silently. `vercel --prod` does a clean build and bypasses the stale deployment chain. Always prefer `vercel` CLI over API redeploy when recovering from build errors.

## Step 8 — Verify public data access

```bash
curl -s "https://YOUR_REF.supabase.co/rest/v1/TABLENAME?select=col1,col2" \
  -H "apikey: sb_pub_YOUR_ANON_KEY" \
  -H "Authorization: Bearer sb_pub_YOUR_ANON_KEY"
```

## Pitfalls summary

| Issue | Fix |
|---|---|
| psql says "Network is unreachable" | Supabase direct DB uses IPv6; Oracle Cloud VMs can't reach it. Use Management API SQL endpoint. |
| Pooler says "Tenant or user not found" | Pooler may not be provisioned for brand new projects. Use Management API SQL endpoint. |
| SQL fails with date type error | Add `::date` casts to all date string literals |
| Free plan project limit | Pause an inactive project first |
| Vercel env returns error object | Check that `target` array includes `["production", "preview", "development"]` |
| Supabase `.then().catch()` fails TypeScript build | `PostgrestFilterBuilder.then()` returns `PromiseLike<void>`, not `Promise` — `.catch()` doesn't exist on it. Convert all Supabase queries to `async/await` with `try/catch`. See code fix below. |
| Vercel API redeploy fails after ERROR build | Use `vercel --token $VERCEL_TOKEN --prod --yes` for a clean build + deploy instead of the API redeploy chain. |
| Vercel deploy error `missing_pages_app` | Happens when deploying via API with empty `files:[]` on a Next.js project. Use `vercel` CLI which auto-detects framework and sends correct build config. |

### Fix: Supabase `.then().catch()` → async/await

When build logs show:
```
Type error: Property 'catch' does not exist on type 'PromiseLike<void>'
```

Change all Supabase queries from:
```ts
// ❌ Fails TypeScript build
supabase().from("table").select("*").then(({ data, error }) => {
  if (error) return;
  setData(data);
}).catch((err) => console.error(err));
```

To:
```ts
// ✅ Clean build
void (async () => {
  try {
    const { data, error } = await supabase().from("table").select("*");
    if (!error && data) setData(data);
  } catch (err) {
    console.error(err);
  }
})();
```

Always verify with `npm run build` locally before pushing to Vercel. This catches TypeScript errors faster than waiting for Vercel build logs.