REST data API

Every table in your project is a REST endpoint. Row-Level Security is enforced on every request.

All requests need Authorization: Bearer <key-or-token>. PATCH and DELETE refuse to run without at least one filter — there is no accidental full-table write.

Reading rows

GET /rest/v1/todos?select=id,task&done=is.false&order=id.desc&limit=20

Filter operators

Quote items in in.(…) to include commas (?tag=in.("a,b","c")). A full-text operator may name a text-search config: ?body=fts(english).hello.

Negation and logical trees

Prefix any operator with not. to negate it, and combine conditions with or=(…) / and=(…) — nestable, using the dotted column.op.value form inside:

# NOT: rows where id is not 3
GET /rest/v1/todos?id=not.eq.3

# OR: done, or high priority
GET /rest/v1/todos?or=(done.is.true,priority.gte.5)

# nested: (done = false) AND (priority ≥ 5 OR pinned = true)
GET /rest/v1/todos?done=is.false&and=(priority.gte.5,or(pinned.is.true))

Counting rows

Ask for the total number of matching rows with the Prefer: count=exact header. The total comes back in the Content-Range response header (and populates the count field that client libraries read):

curl -sI "https://<ref>.kethosbase.com/rest/v1/todos?done=is.false" \
  -H "Authorization: Bearer <key>" \
  -H "Prefer: count=exact"

→ Content-Range: 0-19/42

The count applies the same filters as the read, ignoring limit and offset.

A single object instead of an array

Send Accept: application/vnd.pgrst.object+json to get one JSON object back instead of an array. It answers 406 (code PGRST116) when the result is not exactly one row — this is the contract behind a client's .single() (errors on zero rows) and .maybeSingle() (maps the 406 to null):

curl "https://<ref>.kethosbase.com/rest/v1/todos?id=eq.1" \
  -H "Authorization: Bearer <key>" \
  -H "Accept: application/vnd.pgrst.object+json"

→ { "id": 1, "task": "ship it", "done": false }

Embedding related tables

Name a related table inside select to pull its rows in the same request. The relationship is resolved through a foreign key: a to-one FK embeds a JSON object (or null), a to-many FK embeds an array.

GET /rest/v1/posts?select=id,title,author(*),comments(id,body)

→ [ { "id": 1, "title": "Hello",
      "author": { "id": 7, "name": "Alice" },
      "comments": [ { "id": 3, "body": "nice" } ] } ]

Add !inner to drop parent rows with no embedded match (author!inner(*)); !left is the default. Embeds nest, and mix freely with * and plain columns. Every embedded subquery runs under the same Row-Level Security as the base read — an embed can never surface a row a direct read couldn't, so it adds reach, never privilege.

Inserting rows

POST a JSON object, or an array of objects for a batch insert:

curl -X POST "https://<ref>.kethosbase.com/rest/v1/todos" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '[{"task": "one"}, {"task": "two"}]'

With Prefer: return=representation the response carries the inserted rows (including generated ids); without it you get 201 Created and no body.

Upsert

Insert, but merge on a conflict instead of failing. POST with Prefer: resolution=merge-duplicates (update the existing row) or resolution=ignore-duplicates (leave it). The conflict target defaults to the table's primary key; name a different unique column set with ?on_conflict=:

curl -X POST "https://<ref>.kethosbase.com/rest/v1/todos?on_conflict=id" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates,return=representation" \
  -d '{"id": 1, "task": "revised"}'

A table with neither a primary key nor an explicit on_conflict answers 400 with a clear message rather than a raw database error.

Updating and deleting

curl -X PATCH "https://<ref>.kethosbase.com/rest/v1/todos?id=eq.1" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -d '{"done": true}'

curl -X DELETE "https://<ref>.kethosbase.com/rest/v1/todos?done=is.true" \
  -H "Authorization: Bearer <key>"

Both answer 204 No Content, or the affected rows with Prefer: return=representation.

Calling functions (RPC)

Invoke a PostgreSQL function in the public schema over /rest/v1/rpc/{function}. POST supplies named arguments in the JSON body; a read-only function can be called with GET and query-string arguments:

curl -X POST "https://<ref>.kethosbase.com/rest/v1/rpc/search_todos" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -d '{"q": "ship"}'

A set-returning function yields a JSON array; a scalar or single-composite function yields the bare value; a void function returns 204. The call runs under the caller's role with RLS applied, so a function honours its own PostgreSQL EXECUTE grants — to keep a function off the publishable key, revoke execute … from public (and the anon role).

Responses and errors

Successful reads return a JSON array (or a single object with the object Accept header). On the data routes errors come back as a flat object so a client's error type populates directly:

{ "message": "…", "code": "…", "details": null, "hint": null }

An anonymous caller gets generic text with no details/ hint; only the secret key sees database detail. The auth and storage routes keep their own { "error": { "code", "message" } } envelope. See Limits & errors for every status code, the 10-second statement timeout, and the daily request quotas.