Database API — /v1/db

Scoped, metered CRUD over the tables an app declares in its schema. The client sends a query AST (never raw SQL); the gateway validates every identifier against the compiled schema, injects the ownership/membership predicate, compiles it to parameterized SQL, executes it against the app's isolated Postgres schema, and meters the op. This compilation step is the entire security boundary — there is no raw SQL path.

Apps build queries with the SDK's chainable builder; the wire format (QueryRequest/QueryResult) is documented at the end for reference and non-JS clients.

Scopes#

Every query targets one of three scopes. The same table can be reachable in more than one — the scope decides which rows are visible and who pays, not the table itself.

private — the current user's own data#

await tallpond
  .table("notes")
  .select("title, body")
  .eq("archived", false)
  .orderBy("createdAt", "desc")
  .limit(20);

await tallpond.table("notes").insert({ title: "Hi" });

Every row is scoped to owner_principal = <userId>. This is the canonical view of everything the user owns, including rows they've published into resource contexts. Payer is always the user.

resource — data within one resource context#

await tallpond
  .resource(roomId)
  .table("messages")
  .select("id, body")
  .orderBy("createdAt", "asc");

await tallpond.resource(roomId).table("messages").insert({ body: "hi" });

Resolves the resource, checks the caller's membership and the table's access rule, then serves one of two table kinds, declared at schema time with r.owns(...) or r.shares(...):

• Resource-owned (r.owns): rows belong to the resource; owner_principal = <resourceId>. Payer follows the resource payer policy.
• Shared (r.shares): a top-level, creator-owned table published into this resource type. Rows are creator-owned and read through the resource's _contexts link table. See Ownership models. Payer is always the creator.

A members-visibility resource returns 404 to non-members (its existence is hidden); a visible-but-denied op returns 403. Writing to an unlisted/discoverable resource you aren't yet a member of joins you at the type's default role as part of the write — see Auto-join.

federated — read across every resource of a type#

await tallpond
  .resources({ type: "room" })
  .table("messages")
  .select("body, resourceId")
  .limit(50);

Fans a read across every resource of type the caller is an active member of, and where the table's read rule passes for their role there. Results include a virtual resourceId column identifying the source. Read-only (select / count); a write op returns invalid_request.

This is the inbox/home-feed pattern — "latest messages across all my rooms" as one paginated query, instead of fetching the membership list client-side and fanning out N queries per page (which makes cross-resource pagination all but impossible to get right).

Ownership models: owns vs shares#

The distinction is about lifecycle, not access — and it's chosen once, at schema declaration time, by the app developer. A query never specifies it; the gateway resolves resource scope + table name → kind automatically.

A row shared into 5 resources is one row plus 5 lightweight entries in the resource's _contexts link table — not 5 copies. Use r.owns for data that's intrinsically the resource's (an audit log, room settings); use r.shares for content a user authors that should be exportable, editable across contexts, and survive a resource's deletion (chat messages, photos).

Operations by scope#

Unsupported combinations return invalid_request (400).

Filters#

await tallpond.table("notes").eq("archived", false).gt("rank", 3);

filters is an AND-ed list of { column, op, value }:

update and delete require at least one filter — an unbounded destructive op is rejected with invalid_request.

Pagination#

Pagination is cursor-based (keyset), not offset-based. orderBy(column, dir).limit(n) returns a page whose nextCursor encodes the last row's sort value plus its id as a tiebreak; passing that back as .after(cursor) continues from exactly that point.

await tallpond
  .table("notes")
  .orderBy("createdAt", "desc")
  .limit(20)
  .after(cursor)
  .page(); // → { rows, nextCursor }

Cursor pagination requires a single orderBy column, and is stable under concurrent writes: a row inserted or deleted elsewhere in the table can't shift another row across a page boundary, because each page's position is defined by a value, not an offset.

Offset paging (OFFSET n LIMIT m) is deliberately not supported. It has two problems a cursor doesn't: it re-scans and discards n rows on every page (cost grows with page number), and it silently skips or duplicates rows when a concurrent insert/delete shifts everything after it — there's no way for the client to detect either failure mode.

Single-row reads#

single: "required" returns 404 if no row matches and 409 if more than one does. single: "maybe" returns an empty rows array instead of 404. In the SDK these are .single() and .maybeSingle().

await tallpond.table("notes").eq("id", id).single(); // → Row (404 / 409)
await tallpond.table("notes").eq("id", id).maybeSingle(); // → Row | null

Shared tables (r.shares)#

A shared table is a top-level, creator-owned table published into a resource type. The row lives in its creator's namespace; the resource is a context it appears in. This is what lets the same row appear in several resources without duplication, and what lets an admin remove content from a resource without touching the author's copy.

The creator access value scopes an op to the caller's own rows (e.g. update: 'creator' means you can only edit rows you authored). A verb can also take a union of values — update: ['creator', 'admin'] lets both the row's creator (row-scoped) and any admin (any row) through; see schema-and-deploy.md.

await tallpond.resource(id).table("photos").link(photoId); // publish an existing owned row here
await tallpond.resource(id).table("photos").unlink(photoId); // withdraw from this context (moderation)

contexts (private scope)#

Answers "where is this row of mine published?" — returns { resourceId } rows for a rowId the caller owns.

await tallpond.table("photos").contexts(photoId); // → string[] of resource ids

Access model#

Access is default-deny: an undefined rule denies. Each op maps to a verb (select/count → read, insert/upsert → create, update → update, delete → delete; shared tables add link → create + creator, unlink → unlink), checked against the table's declared rule:

A role name is checked against your role in the row's room. For a row in the default room that is your resource role, which is why an app with no second room behaves exactly as it always has. See rooms.

public and resourceOwner were removed when rooms shipped. Both were assignment wearing policy's clothes: resourceOwner is owner (resource creation already inserts the owner as an unremovable owner-role member), and visibility belongs to a grant rather than a rule. Declaring either is now a deploy-time error naming the replacement.

A rule may be a single value or an array (union) of values — see schema-and-deploy.md.

Managed columns#

Every physical row carries id (uuid), owner_principal, createdAt, updatedAt. owner_principal is internal — never selectable or writable. createdAt/updatedAt are platform-managed. id may be supplied on insert. Writing a managed column is invalid_request.

Billing#

Each op resolves a payer and meters via accumulate-and-flush:

• private → the acting user.
• resource-owned → the resource payer policy: actor (caller) or owner (resource owner). split currently bills the actor (see limitations).
• shared → always the creator (creator-owned rows are the creator's cost).

Reads bill the fetching session (egress); writes bill the owner (at-rest). In a batch, cost is accumulated per payer and metered separately.

Batch#

await tallpond.batch(
  tallpond.table("notes").insert({ title: "a" }),
  tallpond.table("notes").insert({ title: "b" })
);

POST /v1/db/batch takes { "operations": QueryRequest[] } and returns { "results": QueryResult[] }. Runs up to 50 operations in one transaction — if any operation fails, the whole batch rolls back.

Wire format#

The SDK builder above compiles to this request shape; direct use is only needed for non-JS clients or the escape hatch.

interface QueryRequest {
  scope:
    | { kind: "private" }
    | { kind: "resource"; resourceId: string }
    | { kind: "federated"; type: string };
  table: string;
  op:
    | "select"
    | "insert"
    | "update"
    | "upsert"
    | "delete"
    | "count"
    | "link"
    | "unlink"
    | "contexts";
  columns?: string; // projection, e.g. "id, body" or "*" (default)
  values?: object | object[]; // insert / update / upsert
  filters?: Array<{ column: string; op: FilterOp; value?: unknown }>;
  order?: Array<{ column: string; direction: "asc" | "desc" }>;
  limit?: number; // default 100, max 1000
  cursor?: string; // opaque, from a prior page's nextCursor
  single?: "required" | "maybe";
  onConflict?: string[]; // upsert conflict target (default ["id"])
  rowId?: string; // link / unlink / contexts target
}

interface QueryResult {
  rows: object[];
  count?: number; // for op: "count"
  nextCursor?: string | null; // for paginated selects
}

POST /v1/db/query runs one operation; POST /v1/db/batch takes { operations: QueryRequest[] } and returns { results: QueryResult[] }. Both require authentication.