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 osgarden
  .table("notes")
  .select("title, body")
  .eq("archived", false)
  .orderBy("createdAt", "desc")
  .limit(20);

await osgarden.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 osgarden
  .resource(roomId)
  .table("messages")
  .select("id, body")
  .orderBy("createdAt", "asc");

await osgarden.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 osgarden
  .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 osgarden.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 osgarden
  .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 osgarden.table("notes").eq("id", id).single(); // → Row (404 / 409)
await osgarden.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 osgarden.resource(id).table("photos").link(photoId); // publish an existing owned row here
await osgarden.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 osgarden.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 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 osgarden.batch(
  osgarden.table("notes").insert({ title: "a" }),
  osgarden.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.