# Database API v1 Migration Guide

Use this guide when upgrading a Lakebed capsule from the synchronous full-scan database API to database API v1.

Database API v1 makes database operations asynchronous, requires declared indexes for queries, and runs each handler
inside one parent-owned transaction. Existing deployed artifacts keep their legacy behavior. The migration takes effect
when the capsule is rebuilt and redeployed.

## Syntax Changes

Legacy query:

```ts
schema: {
  todos: table({
    text: string(),
    done: boolean().default(false),
    ownerId: string()
  })
},
queries: {
  todos: query((ctx) =>
    ctx.db.todos
      .where("ownerId", ctx.auth.userId)
      .orderBy("createdAt", "desc")
      .all()
  )
}
```

Database API v1 query:

```ts
schema: {
  todos: table({
    text: string(),
    done: boolean().default(false),
    ownerId: string()
  }).index("by_owner", ["ownerId"])
},
queries: {
  todos: query(async (ctx) => {
    const todos = await ctx.db.todos
      .withIndex("by_owner", (q) => q.eq("ownerId", ctx.auth.userId))
      .order("desc")
      .collect();

    return todos;
  })
}
```

Legacy mutation:

```ts
setTodoDone: mutation((ctx, id: string, done: boolean) => {
  const todo = ctx.db.todos.get(id);
  if (!todo || todo.ownerId !== ctx.auth.userId) {
    return;
  }

  ctx.db.todos.update(id, { done });
})
```

Database API v1 mutation:

```ts
setTodoDone: mutation(async (ctx, id: string, done: boolean) => {
  const todo = await ctx.db.todos.get(id);
  if (!todo || todo.ownerId !== ctx.auth.userId) {
    return;
  }

  await ctx.db.todos.update(id, { done });
})
```

Apply these replacements:

- Add `async` to every query, mutation, or endpoint handler that uses `ctx.db`.
- Await `get`, `insert`, `update`, `delete`, `collect`, `take`, `first`, and `paginate`. Returning the database promise
  directly from an expression-bodied async handler is also valid; never start a database operation without awaiting or
  returning it.
- Replace `where(...)` with a declared `.index(...)` and `withIndex(...)`.
- Replace `orderBy(field, direction)` with index traversal and `order(direction)`.
- Replace `all()` with `collect()`.
- Replace `limit(count).all()` with `take(count)`.
- Use `first()` when only one matching row is needed.
- Use `paginate({ cursor, numItems })` for unbounded lists.

## Designing Indexes

Declare indexes on the table:

```ts
todos: table({
  text: string(),
  done: boolean().default(false),
  ownerId: string()
})
  .index("by_owner", ["ownerId"])
  .index("by_owner_done", ["ownerId", "done"])
```

Then query the matching index:

```ts
const doneTodos = await ctx.db.todos
  .withIndex("by_owner_done", (q) =>
    q.eq("ownerId", ctx.auth.userId).eq("done", true)
  )
  .order("desc")
  .collect();
```

Index rules:

- The fields passed to `withIndex` must follow the index field order.
- Use zero or more `eq` clauses, then optionally one `gt`, `gte`, `lt`, or `lte` clause on the next index field.
- Every index has an implicit `createdAt, id` suffix for deterministic ordering.
- Every table has a built-in `by_creation` index. Use it for an unfiltered newest-first or oldest-first feed.
- Use `id("tableName")` for references to another Lakebed table. Keep external identifiers such as `ctx.auth.userId`
  as `string()`.
- Queries cannot perform full table scans. If a query filters or sorts by a field, declare an index that supports it.

## Pagination

The server query accepts an argument with a `pagination` property:

```ts
messages: query(async (
  ctx,
  args: {
    roomId: string;
    pagination: { cursor: string | null; numItems: number };
  }
) =>
  ctx.db.messages
    .withIndex("by_room", (q) => q.eq("roomId", args.roomId))
    .order("desc")
    .paginate(args.pagination)
)
```

The client supplies the non-pagination arguments:

```tsx
const messages = usePaginatedQuery<Message>(
  "messages",
  { roomId },
  { initialNumItems: 25 }
);
```

`usePaginatedQuery` adds the `pagination` property, accumulates loaded pages, and resets after reactive invalidation.

## Data And Deployment

- Rebuilding creates a database API v1 artifact and a schema manifest.
- Existing rows are retained. Durable index entries are backfilled from the stored JSON rows during schema activation.
- Adding an index does not require rewriting application rows.
- Query handlers are read-only. Mutations and endpoints can write.
- A failed handler, timeout, quota failure, or store error rolls back the handler transaction.
- Preserve authorization checks while migrating. Indexes improve lookup; they do not replace ownership validation.

## Agent Migration Prompt

Give the following prompt to an agent from the root of an existing Lakebed capsule:

```text
Migrate this Lakebed capsule to database API v1. Work directly in the existing
capsule and preserve its user-visible behavior, authorization rules, routes,
query names, mutation names, and stored data shape.

Requirements:

1. Read server/index.ts, client/index.tsx, shared files, and any local AGENTS.md
   before editing.
2. Keep the capsule structure: server/index.ts, client/index.tsx, and shared/.
   Do not install packages, add a database service, or use Node built-ins.
3. Import schema helpers from lakebed/server. Keep fields declared with
   table(), string(), boolean(), id("table"), and default() as appropriate.
4. Inspect every legacy query before choosing indexes. Add table indexes that
   preserve its filters and ordering:
   - equality filters become a composite index equality prefix;
   - an optional range filter uses gt/gte/lt/lte on the next index field;
   - createdAt and id are implicit index suffixes;
   - use the built-in by_creation index for unfiltered creation-order queries.
5. Replace legacy database chains:
   - where(...) -> withIndex("index_name", q => q.eq(...))
   - orderBy(..., "asc" | "desc") -> order("asc" | "desc")
   - all() -> collect()
   - limit(n).all() -> take(n)
   - single-result scans -> first()
6. Make every query, mutation, and endpoint that uses ctx.db asynchronous.
   Await or directly return every get, insert, update, delete, collect, take,
   first, and paginate operation. Do not leave fire-and-forget database calls.
7. Keep queries read-only. Keep writes in mutations or endpoints. Preserve all
   input validation and re-check row ownership before updates or deletes.
8. Use id("tableName") only for references to another Lakebed table. Keep
   external IDs, including ctx.auth.userId, as string().
9. For unbounded client lists, migrate the server query to paginate() and the
   client to usePaginatedQuery(). The server argument must include:
   pagination: { cursor: string | null; numItems: number }.
10. Update README snippets and shared row types when the schema or client API
    changed. Stored rows still include id, createdAt, and updatedAt.
11. Search the capsule for remaining .where(, .orderBy(, .limit(, and .all(
    calls. None should remain in migrated application code.
12. Run:
    npx lakebed build . --target anonymous --json
    Fix every reported diagnostic. Then run any existing project tests.

Do not weaken authorization to make the build pass. Do not replace indexed
queries with client-side filtering. Do not delete or rewrite existing data.

When finished, summarize:
- files changed;
- indexes added and which queries use them;
- async database calls migrated;
- pagination changes, if any;
- validation commands and results.
```