DB-backed thread

In-memory conversations die when the browser tab closes. Real agents remember. The pattern is simple: on every request, look up the thread by id, append the new messages, hand the full transcript to the model, then persist whatever the model and tools produced. Drizzle and Postgres keep the schema honest.

db/schema.ts
typescript
import { pgTable, text, timestamp, jsonb, uuid } from 'drizzle-orm/pg-core';

export const threads = pgTable('threads', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: text('user_id').notNull(),
  title: text('title'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const messages = pgTable('messages', {
  id: uuid('id').defaultRandom().primaryKey(),
  threadId: uuid('thread_id').references(() => threads.id).notNull(),
  role: text('role').notNull(),
  parts: jsonb('parts').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Threads own messages, each in its own table. The parts column is jsonb so text and tool invocations stay together exactly as the UI renders them.

app/api/chat/route.ts
typescript
import { db } from '@/db/client';
import { messages as messagesTable, threads } from '@/db/schema';
import { eq } from 'drizzle-orm';

export async function POST(req: Request) {
  const { threadId, messages: incoming } = await req.json();

  const history = threadId
    ? await db.select().from(messagesTable).where(eq(messagesTable.threadId, threadId))
    : [];

  const result = streamText({
    model: getModel(),
    system: SYSTEM_PROMPT,
    messages: convertToModelMessages([...history, ...incoming]),
    tools: agentTools,
    maxSteps: 5,
    onFinish: async ({ response }) => {
      const id = threadId ?? (await db.insert(threads).values({ userId }).returning())[0].id;
      for (const m of [...incoming, ...response.messages]) {
        await db.insert(messagesTable).values({
          threadId: id,
          role: m.role,
          parts: m.parts,
        });
      }
    },
  });

  return result.toUIMessageStreamResponse();
}

History is loaded before the stream, new messages are written in onFinish. onFinish fires after the model and tools are done, which is the safe moment to persist.

Quiz: Quiz

Loading practice…