ai-persistence/build-drizzle-adapter

# Drizzle Chat Persistence

The deliverable is **one file in the app** — `src/lib/chat-persistence.ts` —
exporting a `ChatPersistence` built from the app's existing Drizzle `db`. Plus
four tables added to the app's existing schema file and a migration generated
through the app's existing `drizzle-kit` setup.

Do not create a package, a second `db` instance, a migration runner, or a
`drizzle.config.ts`. The app has those.

Read the **Store Reference**
(`docs/persistence/store-reference.md`) for the store contracts and
invariants, and **ai-persistence/stores** for the shape rules. Every
store below mirrors the reference in-memory backend in
`@tanstack/ai-persistence` (`memory.ts`); the shared conformance testkit is the
proof.

## 1. Read the app before writing anything

| Find               | Where to look                                                       | What it decides                                             |
| ------------------ | ------------------------------------------------------------------- | ----------------------------------------------------------- |
| Dialect            | `drizzle.config.ts` `dialect:`, or the `drizzle-orm/*-core` import  | `sqlite-core` vs `pg-core` vs `mysql-core` column builders  |
| Schema file(s)     | `drizzle.config.ts` `schema:` glob                                  | Where the four tables go — append, never start a new file   |
| The `db` handle    | `src/db/index.ts`, `src/db.ts`, `src/server/db.ts`                  | Module singleton (`export const db`) vs factory (`getDb()`) |
| Migration flow     | `drizzle.config.ts` `out:`, the `migrations/` or `drizzle/` journal | Which generate/apply commands to tell the user to run       |
| Naming conventions | Existing tables in the schema file                                  | Table prefix, var casing, `snake_case` column names         |
| Import alias       | `tsconfig.json` `paths`                                             | `@/db`, `~/db`, `#/db/index`, or a relative path            |

Match what is already there. If their tables are `chat_*`-prefixed and their
vars are camelCase, so are yours. If they already have a `messages` table for
something else, prefix — the store code reads database names off the table
objects, so any name works.

**Never invent a migration path.** Add the tables to their schema file, then
have them run their own commands (`npx drizzle-kit generate` then
`migrate`/`push`, or `wrangler d1 migrations apply` for D1). A parallel
migration table behind their back is how schemas drift.

## 2. Add the tables to their schema file

SQLite. JSON payloads use `text({ mode: 'json' })` so Drizzle round-trips
objects for you; timestamps are `integer` epoch ms.

```ts ignore
import {
  index,
  integer,
  primaryKey,
  sqliteTable,
  text,
} from 'drizzle-orm/sqlite-core'
import type { ModelMessage, TokenUsage } from '@tanstack/ai'
import type { InterruptRecord, RunStatus } from '@tanstack/ai-persistence'

export const chatThreads = sqliteTable('chat_threads', {
  threadId: text('thread_id').primaryKey(),
  messagesJson: text('messages_json', { mode: 'json' })
    .$type<Array<ModelMessage>>()
    .notNull(),
  updatedAt: integer('updated_at').notNull(),
})

export const chatRuns = sqliteTable(
  'chat_runs',
  {
    runId: text('run_id').primaryKey(),
    threadId: text('thread_id').notNull(),
    status: text('status').$type<RunStatus>().notNull(),
    startedAt: integer('started_at').notNull(),
    finishedAt: integer('finished_at'),
    error: text('error'),
    errorCode: text('error_code'),
    usageJson: text('usage_json', { mode: 'json' }).$type<TokenUsage>(),
    sandboxKey: text('sandbox_key'),
    detachedSince: integer('detached_since'),
    cancelRequested: integer('cancel_requested', { mode: 'boolean' }),
    driverEpoch: integer('driver_epoch'),
  },
  (table) => [
    // Powers listReclaimable: status = 'running' AND detachedSince <= cutoff.
    index('chat_runs_status_detached').on(table.status, table.detachedSince),
    // Powers listByThread and findActiveRun.
    index('chat_runs_thread_started').on(table.threadId, table.startedAt),
  ],
)

export const chatInterrupts = sqliteTable('chat_interrupts', {
  interruptId: text('interrupt_id').primaryKey(),
  runId: text('run_id').notNull(),
  threadId: text('thread_id').notNull(),
  status: text('status').$type<InterruptRecord['status']>().notNull(),
  requestedAt: integer('requested_at').notNull(),
  resolvedAt: integer('resolved_at'),
  payloadJson: text('payload_json', { mode: 'json' })
    .$type<Record<string, unknown>>()
    .notNull(),
  responseJson: text('response_json', { mode: 'json' }).$type<unknown>(),
})

export const chatMetadata = sqliteTable(
  'chat_metadata',
  {
    namespace: text('namespace').notNull(),
    key: text('key').notNull(),
    valueJson: text('value_json', { mode: 'json' }).$type<unknown>().notNull(),
  },
  (table) => [primaryKey({ columns: [table.namespace, table.key] })],
)
```

`updatedAt` on threads is an app-owned extra, not part of any contract — the
stores never read columns they do not know about, so add `userId`, tenant ids,
or audit columns the same way (nullable or defaulted so inserts still succeed).
The `namespace` column is the `MetadataStore` first argument; the stock SQL in
the guide calls the same column `scope`.

`RunRecord.error` is a structured `RunError` (`{ message: string, code?: string }`),
so it gets two columns rather than one JSON blob: `error` for the provider's
prose and `errorCode` for the stable classification an operator filters and
groups by. `error` and `errorCode` always move together in `update`, so a
later code-less failure can never leave a stale `code` from an earlier one
behind.

**Postgres** (`drizzle-orm/pg-core`): `jsonb()` for the JSON payloads,
`bigint({ mode: 'number' })` for epoch-ms timestamps (including
`detachedSince`), `integer()` for `driverEpoch`, `boolean()` for
`cancelRequested`, `text()` elsewhere, composite `primaryKey` o