# Capsule Database

New Lakebed artifacts use database API v1. Database calls are async, queries require declared indexes, and every handler
runs inside one parent-owned transaction.

## Indexes

```ts
import { boolean, capsule, id, query, string, table } from "lakebed/server";

export default capsule({
  schema: {
    rooms: table({ name: string() }),
    users: table({ displayName: string() }),
    messages: table({
      roomId: id("rooms"),
      authorId: id("users"),
      body: string(),
      pinned: boolean().default(false)
    })
      .index("by_room", ["roomId"])
      .index("by_room_pinned", ["roomId", "pinned"])
      .index("by_author", ["authorId"])
  },
  queries: {
    recent: query(async (ctx, args: { roomId: string }) =>
      ctx.db.messages
        .withIndex("by_room", (q) => q.eq("roomId", args.roomId))
        .order("desc")
        .take(20)
    )
  }
});
```

Every index has an implicit `createdAt, id` suffix. This makes traversal deterministic even when declared fields and
timestamps are equal. Ranges support an equality prefix followed by `gt`, `gte`, `lt`, or `lte` on the next index field.
`by_creation` is available on every table.

Each declared index field has one schema-defined scalar type: `string`, `boolean`, or `id` (encoded as a string).
Lakebed uses the same canonical byte encoding for those values in memory, source-runtime, and Postgres execution.

## Pagination

`paginate({ cursor, numItems })` uses an authenticated keyset cursor. A cursor is tied to the deploy, schema, query
arguments, index, range, and order. Each page has a repeatable snapshot; the cursor does not preserve one historical
snapshot across separate requests.

```ts
const result = await ctx.db.messages
  .withIndex("by_room", (q) => q.eq("roomId", args.roomId))
  .order("desc")
  .paginate(args.pagination);
```

On the client, `usePaginatedQuery(name, args)` supplies a trailing `pagination` argument, keeps every loaded page
subscribed, accumulates results, and resets to the first page after reactive
invalidation.

## Relationships

Use `id("table")` fields and bounded application-level traversal. Lakebed does not expose SQL joins.

```ts
const page = await ctx.db.messages
  .withIndex("by_room", (q) => q.eq("roomId", args.roomId))
  .take(20);

const enriched = [];
for (const message of page) {
  enriched.push({ ...message, author: await ctx.db.users.get(message.authorId as string) });
}
```

Many-to-many relationships use an explicit join table with indexes for both directions.

## Consistency

- Query handlers receive a read-only database and one repeatable snapshot.
- Mutations and endpoints commit row and index writes atomically.
- Mutations read their own writes.
- Writes are serialized per deploy in v1.
- Worker failure, timeout, quota failure, or store errors roll back the session.

## Migrating Legacy Code

Add `async` to handlers, add `await` to every database terminal, declare indexes, and replace:

- `where(...).orderBy(...).limit(...).all()` with `withIndex(...).order(...).take(...)`
- `all()` with `collect()` or `paginate()`
- synchronous `get`, `insert`, `update`, and `delete` with awaited calls

Existing immutable artifacts keep the legacy snapshot runtime. Redeploying compiles database API v1 and backfills durable
index entries from the existing JSON rows without rewriting application data.

See the [Database API v1 migration guide](./database-migration.md) for complete before-and-after examples and a
copy-paste prompt for migrating a capsule with an agent.