Data · API

Supabase API

PostgREST, direct Postgres for bulk loads and DDL, and the four key types — including which one fails silently. Written around the three things that make inserts look successful when nothing landed: the 1000-row cap, RLS returning empty arrays instead of errors, and DDL that REST cannot do at all.

Skill name: supabase-api
Triggers on: 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.
Read time: 5 min · 5 files · free to use and edit

Download full skill

This skill ships 5 files. The references are where the method lives — SKILL.md on its own will point at files you do not have, so take the archive rather than the markdown.

SKILL.md
references/auth-and-keys.md
references/postgres-direct.md
references/rest-api.md
scripts/supabase_bulk.py

Prefer just the instructions? Download SKILL.md alone.

Use it in your assistant

Claude Code — drop the file in your skills folder and it loads on the next session. Use ~/.claude/skills for every project, or .claude/skills inside a repo to keep it to that project.

mkdir -p ~/.claude/skills
curl -L https://growsteady.io/skills/supabase-api/archive | tar xz -C ~/.claude/skills

Claude apps (web and desktop) — Settings → Capabilities → Skills → add a skill. Extract the archive and upload the whole supabase-api folder, references included (zip it if an archive is asked for).

No install— paste the file into a Claude Project's custom instructions with “Copy as prompt”. Same behaviour, scoped to that project. Note that a paste carries the instructions only: this skill's references do not come with it, so use a real install if you want the full method.

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:

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:

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.

← All skills