gem0-api

name

gem0-api

description

Interface directly with Gem0 (the CouchDB-backed headless CMS / database-as-a-service) over HTTP using API keys — create tables (schemes), modify table schemas, and add/update/query/delete rows. Use this skill whenever the user mentions Gem0, gem0.dev, GemAPI, GemBackend, pillow endpoints, Gem0 schemes/collections/tables, the @gemzero0 SDK, GemSync, or wants any script/integration/agent that reads or writes data in Gem0 — even if they don't say "API" or only say "store this in my CMS".

Gem0 HTTP API

Gem0 stores data as Company → Project → Scheme (table) → Row (document). There are two separate API planes with two different keys — using the wrong key on the wrong plane is the #1 failure mode:

Plane	Server	What it does	Auth
Data plane (GemAPI)	e.g. https://api.gem0.dev (env: GEM_URL)	Row CRUD + queries on existing tables	Authorization: Bearer <project API key>
Admin plane (GemBackend)	https://apist.gem0.dev	Create / modify / delete tables (schemes)	X-KANGA-KEY: <static backend key> header (+ Auth-Token / Auth-Email user session headers on the hosted backend)

The project API key (data plane) is created in the Gem0 admin UI (project settings → API tokens; stored in the api_tokens table with optional enabled_until expiry). It scopes every request to one project — no project ID ever appears in GemAPI URLs. The admin plane instead identifies the project by subdomain (override_subdomain param) or by project ID in the path on the hosted backend.

Ask the user for the values you need (GEM_URL, API key, backend key, project ID/subdomain) if not already provided via env vars. Never hardcode keys in committed code — read GEM_URL / GEM_KEY from the environment (this is the convention the official SDK and GemSync use).

---

1. Rows (data plane — GemAPI)

GemAPI is tRPC-over-HTTP. Procedure keys follow tables.{collection}.{method}:

Method	HTTP	Purpose	Input shape
find	GET	One row by id	{ id, without_links?, with_base64? }
all	GET	List rows (paginated)	{ cursor?, limit?, sort? }
where	GET	Filtered query	{ query, cursor?, limit?, sort? }
create	POST	Add a row	{ ...fieldValues }
update	POST	Update a row	{ _uid, _ver?, ...changedFields }
destroy	POST	Delete a row	{ _uid, _ver? }

Wire format: GET passes input as a URL-encoded JSON object under key "0" in the input query param; POST passes a JSON body with the same "0" wrapper.

Add a row

curl -X POST "$GEM_URL/trpc/tables.posts.create" \
  -H "Authorization: Bearer $GEM_KEY" \
  -H "Content-Type: application/json" \
  -d '{"0":{"title":"Hello world","published":true,"views":0}}'

Response: {"result":{"data":{"success":true,"data":{...fields,"_uid":"...","_ver":"...","_created":"...","_updated":"..."}}}}

Send only schema fields. Never send _uid/_ver/_created/_updated/_errors on create — the server generates them. A missing/invalid required field throws; invalid optional fields are stripped.

List / paginate

curl -G "$GEM_URL/trpc/tables.posts.all" \
  -H "Authorization: Bearer $GEM_KEY" \
  --data-urlencode 'input={"0":{"limit":50,"sort":[{"created_at":"desc"}]}}'

Response data: { cursor?, error_ids?, results: [...] }. Default limit is 10. Pass the returned cursor back to get the next page; no cursor in the response means you reached the end. error_ids lists rows that failed schema validation — don't treat their absence from results as deletion.

Query (where)

Mango-style operators per field: $eq $ne $lt $lte $gt $gte $exists $type $in $nin $size $regex, combinable with top-level $and / $or:

curl -G "$GEM_URL/trpc/tables.posts.where" \
  -H "Authorization: Bearer $GEM_KEY" \
  --data-urlencode 'input={"0":{"query":{"published":{"$eq":true},"views":{"$gt":100}},"limit":20}}'

Update / delete

# update — _uid required; _ver optional (optimistic concurrency check)
curl -X POST "$GEM_URL/trpc/tables.posts.update" \
  -H "Authorization: Bearer $GEM_KEY" -H "Content-Type: application/json" \
  -d '{"0":{"_uid":"<uid from a previous response>","title":"Updated title"}}'

# delete
curl -X POST "$GEM_URL/trpc/tables.posts.destroy" \
  -H "Authorization: Bearer $GEM_KEY" -H "Content-Type: application/json" \
  -d '{"0":{"_uid":"<uid>"}}'

REST-style alias

/api/tables/{collection}/{method} mirrors the tRPC routes without the "0" wrapper — often more convenient for generated code:

# create: plain JSON body
curl -X POST "$GEM_URL/api/tables/posts/create" \
  -H "Authorization: Bearer $GEM_KEY" -H "Content-Type: application/json" \
  -d '{"title":"Hello"}'

# find: flat query params (strings only)
curl "$GEM_URL/api/tables/posts/find?id=<uid>" -H "Authorization: Bearer $GEM_KEY"

Caveat: the GET alias passes query params as flat strings, so all/where inputs that need numbers or nested objects (limit, sort, query) fail validation there — use the /trpc/ form for queries.

Responses and errors

GemAPI returns HTTP 200 for almost everything. Always branch on the JSON envelope, not the status code:

• Success: { "result": { "data": ... } }
• Error: { "error": { "code": -32603, "message": "...", "data": {} } }

Common error messages: Invalid API key / API key is not valid (bad/expired Bearer token), No permission (table posix forbids the operation — see §3), Collection not found, Document not valid. Missing or invalid required field: X, Invalid parameters (field value fails the schema's Zod validation).

Rows can also carry a soft _errors array (per-field Zod issues) on reads when stored data no longer matches the schema.

Field value formats

• Translatable fields (modifier translatable) are locale-keyed objects: {"en":"Hello","nl":"Hallo"}. Project locales come from GET /trpc/project.settings?input={"0":{}}.
• Link fields read as { "collection": "products", "id": "<uid>" }.
• File fields read as { filename, content_type, size }; add "with_base64": true to find/all/where input to inline base64.
• Dates are ISO strings.

Typed clients (when writing app code instead of curl)

GET $GEM_URL/sync (Bearer auth) returns generated TypeScript types for all tables. The official SDK:

import { createGemClient } from "@gemzero0/sdk-core"; // GitHub Package Registry
const gem = createGemClient<GemRouter>({ url: process.env.GEM_URL, key: process.env.GEM_KEY });
await gem.tables.posts.create.mutate({ title: "Hello" });
await gem.tables.posts.where.query({ query: { published: { $eq: true } } });

Run GEM_URL=... GEM_KEY=... GemSync to (re)generate gemRouter.ts.

---

2. Tables / schemes (admin plane — GemBackend)

Table management lives on the GemBackend "pillow" endpoints, not on GemAPI — the Bearer key cannot create or alter tables. All pillow endpoints are POST with a JSON body and these headers:

X-KANGA-KEY: <static backend API key>     # required in production
Auth-Token: <user auth token>             # hosted admin backend also expects a
Auth-Email: <user email>                  # logged-in user session
Content-Type: application/json

URL shape: {BACKEND}/en/{projectId}/pillow/scheme/{action} on the hosted backend (https://apist.gem0.dev). A self-hosted GemBackend from the repo routes /{locale}/pillow/{scheme}/{action} and resolves the project from the request subdomain — pass "override_subdomain": "<project subdomain>" in the body to select the project explicitly.

Responses use the envelope { "success": true|false, "data": ..., "version": "...", "server_time": "..." } with HTTP 200 on success and 404 for every application error (including validation failures) — branch on success.

List tables

curl -X POST "$BACKEND/en/$PROJECT_ID/pillow/scheme/fetch_all" \
  -H "X-KANGA-KEY: $KANGA_KEY" -H "Auth-Token: $AUTH_TOKEN" -H "Auth-Email: $AUTH_EMAIL" \
  -H "Content-Type: application/json" -d '{}'

Returns scheme documents: { _id: "schema:posts", _rev: "...", type: "schema", collection: "posts", schema: [...fields], posix?: "rwx" }. Get one with {"id":"schema:posts"}; get row counts per table with {"counts":1}.

Create a table

curl -X POST "$BACKEND/en/$PROJECT_ID/pillow/scheme/save" \
  -H "X-KANGA-KEY: $KANGA_KEY" -H "Auth-Token: $AUTH_TOKEN" -H "Auth-Email: $AUTH_EMAIL" \
  -H "Content-Type: application/json" \
  -d '{
    "id": null,
    "rev": null,
    "scheme_name": "posts",
    "ob": [
      { "name": "title",     "primitive": "string", "primary": true, "modifiers": ["required"] },
      { "name": "body",      "primitive": "string", "modifiers": ["richText"] },
      { "name": "views",     "primitive": "number", "prefill": "increment" },
      { "name": "published", "primitive": "boolean", "prefill": "false" }
    ]
  }'

scheme_name is sanitized to lowercase alphanumeric (My Blog Posts! → myblogposts) and becomes both the collection name and the document id schema:{name} — pick the final slug yourself so URLs stay predictable. The table name cannot be changed afterwards without migrating data.

Modify a table (add / change / remove columns)

save with id + rev replaces the entire field array. Always fetch-modify-save:

1. fetch_all with {"id":"schema:posts"} → take _rev and the current schema array.
2. Edit the array (append a field, change modifiers, remove a field, …). Keep existing field names stable — row data is stored under the field name, so renaming a field orphans existing values.
3. save with {"id":"schema:posts","rev":"<_rev>","scheme_name":"posts","ob":[...full updated array]}.

A stale rev fails (CouchDB conflict) — re-fetch and retry.

Delete a table

curl -X POST "$BACKEND/en/$PROJECT_ID/pillow/scheme/remove" \
  -H "X-KANGA-KEY: $KANGA_KEY" -H "Auth-Token: $AUTH_TOKEN" -H "Auth-Email: $AUTH_EMAIL" \
  -H "Content-Type: application/json" -d '{"id":"schema:posts"}'

Destructive and bulk: this deletes the scheme and every row in the collection. Confirm with the user before calling it.

---

3. POSIX permissions — the critical gotcha

Each scheme document carries a 3-char posix string gating what the data-plane Bearer key may do with that table:

char	position 0	position 1	position 2
meaning	r = read (find/all/where)	w = create	x = update/destroy

Valid values: ---, --x, -w-, -wx, r--, r-x, rw-, rwx.

A freshly created table has no posix at all, and a missing posix fails every check — the table is completely inaccessible through GemAPI (every row call returns a permission error). After creating a table, permissions must be opened up before the API key can touch it: in the admin UI this is Table settings → POSIX (set rwx for full access). The repo's backend save endpoint only replaces the schema field array and does not reliably persist posix — if setting it via API doesn't take effect, tell the user to flip it in the admin UI (or set posix directly on the schema:{name} document in CouchDB) rather than silently retrying.

If row calls fail with permission errors, check posix via fetch_all before assuming the key is bad.

---

4. Field definition reference

Each entry in a scheme's schema array:

{
  name: string,                 // field key in row documents (keep stable!)
  primitive: "string" | "number" | "boolean" | "date" | "file" | "link",
  primary?: boolean,            // one per table; used in generated row _ids
  modifiers?: string[],
  validations?: { method: string, params: number }[],
  transformations?: string[],   // string only: "trim" | "toLowerCase" | "toUpperCase"
  prefill?: string,
  options?: string[],           // select-style choice list for string fields
  multiline?: boolean,
  preview?: boolean,            // show in admin row-list previews
  // link fields only:
  primitive_link?: string,          // "projectId:collectionName"
  primitive_project_id?: string,
}

Per-primitive options:

primitive	modifiers	validations (method)	prefills
string	richText, wysiwyg, required, arrayOf, translatable	min, max, length, email, url, uuid, ip	empty, randomUniq
number	required	gt, gte, lt, lte, positive, nonnegative, negative, nonpositive, multipleOf	empty, increment
boolean	—	—	true, false
date	required	—	empty, now
file	required	image/*, maxSizeMb	empty
link	required	—	empty

Numeric-param validations look like { "method": "max", "params": 200 }.

Example link field: { "name": "author", "primitive": "link", "primitive_link": "123:authors", "primitive_project_id": "123" }.

---

5. Workflow for "set up a table and fill it"

1. Create the scheme (admin plane, §2) — sanitize the name yourself first.
2. Open posix (rwx, §3) — without this step every later call fails.
3. Add rows (data plane, §1) — batch by looping create; there is no bulk-insert endpoint.
4. Verify with tables.{name}.all and report the _uids created.

When anything fails, print the full JSON error envelope to the user — Gem0 error messages (Document not valid…, No permission, Invalid scheme) state the exact cause.