# Database

The `ctx.db` API: insert, get, update, delete, and index queries with ranges, order, `take`, `first`, `collect`, `count`, and `paginate`.

Every call is async. Await or return each one. `ctx.db` has one property per table in your schema. Queries, actions, and `GET`/`HEAD` endpoints get read methods only. Mutations and writable endpoints also get `insert`, `update`, and `delete`.

The examples use this schema:

```ts
schema: {
  tasks: table({
    title: string(),
    done: boolean().default(false),
    priority: number().default(0),
    dueAt: number().optional(),
    ownerId: userId()
  })
    .index("by_owner", ["ownerId"])
    .index("by_owner_done", ["ownerId", "done"])
    .index("by_owner_due", ["ownerId", "dueAt"])
}
```

## Rows

Every row has the fields you declared plus three Fold sets:

| Field | Value |
|---|---|
| `id` | A unique string. Use it with `get`, `update`, `delete`, and `id()` fields. |
| `createdAt` | ISO timestamp string, set on insert. |
| `updatedAt` | ISO timestamp string, set on insert and every update. |

You cannot set or update these three yourself.

## insert(value)

```ts
const task = await ctx.db.tasks.insert({ title: "Ship it", ownerId: userId });
// task.id, task.createdAt, task.done === false, task.priority === 0
```

Returns the new row with defaults filled in. Required fields must be present. Fields with `.default()` or `.optional()` may be left out. Pass `null` for an optional field to leave it empty. An unknown field throws `Unknown field for tasks: <name>`.

## get(id)

```ts
const task = await ctx.db.tasks.get(taskId);
if (!task) {
  throw new Error("Task not found.");
}
```

Returns the row, or `null`.

## update(id, patch)

```ts
const updated = await ctx.db.tasks.update(taskId, { done: true });
await ctx.db.tasks.update(taskId, { dueAt: null }); // clear an optional field
```

Changes only the fields in `patch`. Returns the updated row, or `null` if no row has that ID. Passing `null` clears an optional field.

## delete(id)

```ts
const removed = await ctx.db.tasks.delete(taskId); // true if a row was deleted
```

## Check ownership before writing

A row ID is not a credential. Before you update or delete a row on behalf of a user, load it and compare its owner:

```ts
mutation(async (ctx, taskId: string) => {
  const { userId } = ctx.auth.requireIdentity();
  const task = await ctx.db.tasks.get(taskId);
  if (!task || task.ownerId !== userId) {
    throw new Error("Task not found.");
  }
  await ctx.db.tasks.delete(taskId);
})
```

## Queries: withIndex

Every list read goes through an index:

```txt
ctx.db.<table>.withIndex(<index name>, (q) => <range>).order(<"asc" | "desc">).<terminal>()
```

`by_creation` exists on every table and sorts by creation time. Use it for unfiltered reads:

```ts
const newest = await ctx.db.tasks.withIndex("by_creation").order("desc").take(20);
```

Filter by declaring an index on the fields you filter on, then pass a range:

```ts
const mine = await ctx.db.tasks
  .withIndex("by_owner", (q) => q.eq("ownerId", userId))
  .collect();
```

There is no full-table filter. `where()`, `orderBy()`, `limit()`, and `all()` throw. Filter small results in JavaScript after an index query if you need to.

### Range operators

| Operator | Meaning |
|---|---|
| `q.eq(field, value)` | Field equals value. `eq(field, null)` on an optional field matches rows where it is absent. |
| `q.gt(field, value)` | Greater than |
| `q.gte(field, value)` | Greater than or equal |
| `q.lt(field, value)` | Less than |
| `q.lte(field, value)` | Less than or equal |

Rules:

- Clauses follow the index's field order. Start with `eq` on the first field, then `eq` on the next, and so on.
- After the `eq` clauses, you may add at most one lower bound (`gt` or `gte`) and one upper bound (`lt` or `lte`) on the next field.
- Range bounds only match rows where the field has a value.
- The range callback must return the builder it was given.

```ts
// Open tasks for this user
ctx.db.tasks.withIndex("by_owner_done", (q) => q.eq("ownerId", userId).eq("done", false)).collect();

// Due in the next 7 days, soonest first
const now = Date.now();
ctx.db.tasks
  .withIndex("by_owner_due", (q) => q.eq("ownerId", userId).gte("dueAt", now).lt("dueAt", now + 7 * 86_400_000))
  .order("asc")
  .collect();
```

Wrong order throws, for example `Index tasks.by_owner_done expected field ownerId, received done.`

### order(direction)

`"asc"` (default) or `"desc"`. Rows are sorted by the index fields, then `createdAt`, then `id`. So `withIndex("by_owner", ...).order("desc")` returns a user's newest rows first.

### Terminals

| Call | Returns | Notes |
|---|---|---|
| `.collect()` | `Promise<Row[]>` | All matches. Throws `collect() exceeded the 1000 row return limit. Use paginate().` past 1,000 rows. |
| `.take(n)` | `Promise<Row[]>` | At most `n` rows. `n` is an integer from 0 to 1,000. |
| `.first()` | `Promise<Row \| null>` | The first match. |
| `.count()` | `Promise<number>` | Number of matches, without reading row bodies. Each counted entry still counts toward the 5,000 rows-read budget. |
| `.paginate({ cursor, numItems })` | `Promise<{ page, continueCursor, isDone }>` | One page. `numItems` is 1 to 1,000. Pass `cursor: null` for the first page. |

### Pagination

```ts
import { fold, query, type PaginationOptions } from "@aozumi/fold/server";

queries: {
  feed: query(async (ctx, args: { pagination: PaginationOptions }) => {
    return ctx.db.tasks.withIndex("by_creation").order("desc").paginate(args.pagination);
  })
}
```

Pass `continueCursor` back as the next `cursor` until `isDone` is `true`. A cursor only works for the same query shape, and stops working after a schema change (`Pagination cursor belongs to a stale schema.`). On the client, `usePaginatedQuery` handles cursors for you. See [Client](/fold/client/#paginated-queries).

## Read and write budgets

Each handler call has a budget. Hitting one throws `Database <name> limit exceeded (<limit>).` These apply in local dev and hosted.

| Budget | Limit per call |
|---|---|
| Rows read (including counted entries) | 5,000 |
| Rows returned | 1,000 |
| Bytes read | 4 MiB |
| `get()` calls | 1,000 |
| Index queries | 100 |
| Writes | 1,000 |
| One field value | 64 KiB |
| One index key | 2,048 bytes |

A loop over `paginate()` inside one handler shares the same budget. It does not get around these limits.

## Dashboard counts

For counts and totals, keep a summary row up to date when you write, and read the summary instead of scanning.

```ts
import { fold, mutation, number, query, string, table } from "@aozumi/fold/server";

export default fold({
  name: "clicks",
  schema: {
    events: table({ kind: string(), at: number() }).index("by_kind_at", ["kind", "at"]),
    counters: table({ key: string(), total: number().default(0) }).index("by_key", ["key"])
  },
  mutations: {
    record: mutation(async (ctx, kind: string) => {
      const at = Date.now();
      await ctx.db.events.insert({ kind, at });
      const counter = await ctx.db.counters.withIndex("by_key", (q) => q.eq("key", kind)).first();
      if (counter) {
        await ctx.db.counters.update(counter.id, { total: Number(counter.total) + 1 });
      } else {
        await ctx.db.counters.insert({ key: kind, total: 1 });
      }
    })
  },
  queries: {
    totals: query(async (ctx) => ctx.db.counters.withIndex("by_key").collect()),
    lastHour: query(async (ctx, args: { kind: string }) => {
      const since = Date.now() - 3_600_000;
      return ctx.db.events.withIndex("by_kind_at", (q) => q.eq("kind", args.kind).gte("at", since)).count();
    })
  }
});
```

Guidance:

- Store times as `number()` epoch milliseconds and index them after the fields you filter on, as in `by_kind_at`.
- `count()` is fine for a few thousand matching rows. Past 5,000 it hits the rows-read budget. Use summary rows for anything that can grow past that.
- The mutation updates the event and the counter in one transaction, so totals stay exact.
- A live `useQuery("totals")` on the client updates as soon as `record` runs.
- Stored data in an anonymous deploy is capped at 1 MB. Delete or roll up old rows if events pile up.
- To feed a dashboard from another system, have that system `POST` to a writable endpoint that calls the same insert-and-count logic, one small batch per request. Anonymous deploys cannot `fetch` out, and `ctx.env` is empty, so they cannot pull data or check a secret from server env. Protect an ingest endpoint with `ctx.auth.requireSignedIn()` or keep it to data that is safe for anyone to send.

## Types

Inside `fold({ schema, ... })`, rows are typed from the schema: `task.title` is a `string`, `task.dueAt` is `number | undefined`. On the client, `createClient<typeof app>()` gives each query result the same JSON shape. The `Id<"tasks">` type from `@aozumi/fold/server` marks an ID string by table.