---
name: supabase-api
description: Endpoint-level reference for talking to Supabase from code — the auto-generated PostgREST REST API, direct Postgres connections for bulk loads and DDL, the four API key types and which one silently fails, and Python patterns for ingest pipelines. Use this skill whenever the user mentions Supabase, PostgREST, a `supabase.co` URL, `SUPABASE_ANON_KEY` / `SERVICE_ROLE_KEY` / `sb_publishable_` / `sb_secret_`, supabase-py, or asks to create a table, push scraped or enriched rows into Supabase, upsert a batch, query a Supabase table from a script, wire a data pipeline to Supabase, or debug why inserts return success but no rows appear. Use it BEFORE writing any Supabase call, because the REST API caps every response at 1000 rows by default, RLS turns denied reads into empty arrays rather than errors, and DDL cannot go through the REST API at all.
---

# Supabase API

Supabase is Postgres with three access surfaces stacked on it. Picking the wrong one is the
single most common failure, and the failures are quiet — an insert that returns `201` while
writing nothing, a select that returns `[]` instead of "permission denied", a 50,000-row
table that reads back as exactly 1,000 rows.

| Surface | Reach | Use it for |
|---|---|---|
| **REST (PostgREST)** | Tables, views, functions. **No DDL** | Reads, small writes, anything from an app |
| **Direct Postgres** | Everything Postgres does | DDL, bulk loads, transactions, `COPY` |
| **Management API / MCP** | Project administration | Creating projects, running migrations |

## Onboarding — start here

**What this does.** Gives you the exact request shapes for reading and writing Supabase data
from a script: REST endpoints and their query syntax, connection strings for direct Postgres,
which key to use where, and a bulk-upsert helper that handles batching and conflict targets.

**What it does not do, and where to go instead.** It does not cover Supabase Auth flows
(sign-up, JWT verification, OAuth), Storage buckets, Realtime subscriptions, or Edge
Functions — those are separate products with their own docs. It does not design your schema.
For project-level administration (creating projects, applying migrations interactively) use
the Supabase MCP tools, not this skill. For anything ambiguous, `mcp__*__search_docs` reads
the live documentation and is free.

**Setup.** Four environment variables, all read from `local.env`:

```bash
set -a && . ./local.env && set +a
```

| Variable | What it is | Where |
|---|---|---|
| `SUPABASE_URL` | `https://<project-ref>.supabase.co` | Dashboard → Project Settings → API |
| `SUPABASE_SECRET_KEY` | `sb_secret_...` — server-side, bypasses RLS | Dashboard → API Keys |
| `SUPABASE_DB_URL` | Postgres connection string | Dashboard → Connect |
| `SUPABASE_ACCESS_TOKEN` | `sbp_...` — MCP/CLI only, not data access | Account → Access Tokens |

`sb_publishable_...` is the browser-safe key and it is **not** what a server-side ingest
script wants. It is bound by RLS, so with RLS on and no policies it reads empty and writes
nothing — without erroring. If a pipeline "runs fine but the table is empty", this is why.

**Verify in one call.** Substitute a real table name:

```bash
curl -s "$SUPABASE_URL/rest/v1/your_table?select=*&limit=1" \
  -H "apikey: $SUPABASE_SECRET_KEY" \
  -H "Authorization: Bearer $SUPABASE_SECRET_KEY"
```

A JSON array (even `[]`) means auth and networking are fine. `{"message":"Invalid API key"}`
means the key is wrong. A hang usually means IPv6 — see `references/postgres-direct.md`.

**Cost.** No per-call charge. Compute is billed hourly per project, storage and egress by
quota — a research dataset of tens of thousands of rows sits far inside the free tier. The
real constraints are the 1000-row response cap and connection limits (60 on Nano/Micro), not
money.

**How to invoke.** Say "push these rows to Supabase", "query the X table", "create a table
for Y", or name any Supabase credential.

**Where the detail lives.**

| File | Read it when |
|---|---|
| `references/rest-api.md` | Writing any REST call — filters, upserts, pagination, RPC |
| `references/postgres-direct.md` | Bulk loading, DDL, migrations, connection strings |
| `references/auth-and-keys.md` | Choosing a key, RLS behaviour, debugging silent failures |
| `scripts/supabase_bulk.py` | Loading more than a few hundred rows |

## Choosing a surface

Decide by operation, not by preference:

- **Creating or altering tables** → direct Postgres or the MCP's `apply_migration`. The REST
  API has no DDL. There is no header or flag that changes this.
- **Loading more than ~1,000 rows** → direct Postgres with `execute_values` or `COPY`. Use
  `scripts/supabase_bulk.py`; it batches, retries, and takes a conflict target.
- **Reads and writes of tens to hundreds of rows** → REST. Simpler, no connection to manage,
  works from anywhere.
- **Anything transactional** — several writes that must all land or none — → direct Postgres.
  REST calls are independent requests and cannot be rolled back together.

## The four failures that cost the most time

**1. The 1000-row cap.** PostgREST caps responses; Supabase's default is 1,000 rows. A query
over a larger table returns exactly 1,000 with no warning, and code that treats that as the
full set computes wrong aggregates. Paginate explicitly with `limit`/`offset` or a `Range`
header, and check `Content-Range` for the true total. Any read that could exceed 1,000 rows
should either paginate or run over direct Postgres.

**2. RLS returning empty instead of denied.** RLS is on by default for tables created in the
dashboard, and a table with RLS enabled and no policies denies everything to publishable
keys — as `[]` on reads and a silent no-op on writes, not as an error. When rows go missing,
check the key type before you debug the query.

**3. Upsert without a unique constraint.** `Prefer: resolution=merge-duplicates` needs a
unique or primary-key constraint on the conflict column, and `on_conflict=<col>` naming it.
Without the constraint you get duplicate rows instead of an update — which corrupts a dataset
quietly across re-runs. Re-runnable pipelines need this right on the first load.

**4. Transaction-mode pooler with prepared statements.** Port `6543` does not support
prepared statements. `psycopg` uses them automatically past a threshold, so a script that
works locally fails intermittently against the pooler. Either connect direct on `5432` or set
`prepare_threshold=None`.

## Writing a re-runnable ingest

Pipelines get re-run — after a crash, a schema change, a bad batch. Design for it from the
first load rather than retrofitting after a duplicate mess:

- **A stable natural key per row**, unique-constrained, so re-running upserts instead of
  duplicating. For scraped people, a hash of the profile URL works and doubles as the
  deletion key a GDPR erasure request needs.
- **A `fetched_at` timestamp on every row.** Engagement counts and profile fields move;
  a row without a timestamp can't be interpreted later.
- **Idempotent batches.** Load in chunks with the conflict target set, so a mid-run failure
  is fixed by re-running rather than by working out where it stopped.
- **Raw payload in `jsonb` only if you need it.** It's convenient and it bloats the table;
  store the parsed columns you query on, and keep raw responses on disk.