AI SDK chat persistence

The Vercel AI SDK leaves chat persistence to you: load previous messages when a chat opens, save new ones in the stream’s onFinish callback. PerSQL makes the storage side one table in an isolated SQLite database.

npm install ai @persql/sdk

One table, one JSON column — UIMessage objects round-trip as text:

import { PerSQL } from "@persql/sdk";

const db = new PerSQL({ token: process.env.PERSQL_TOKEN }).database("acme/chat");

await db.query(
  `CREATE TABLE IF NOT EXISTS chat_messages (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     chat_id TEXT NOT NULL,
     message TEXT NOT NULL,
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   )`
);

Save in the route handler’s onFinish, load before rendering:

import { convertToModelMessages, streamText, type UIMessage } from "ai";

export async function POST(req: Request) {
  const { id, messages }: { id: string; messages: UIMessage[] } = await req.json();

  const result = streamText({
    model: "openai/gpt-5",
    messages: await convertToModelMessages(messages),
  });

  return result.toUIMessageStreamResponse({
    originalMessages: messages,
    onFinish: async ({ messages }) => {
      await db.batch([
        { sql: "DELETE FROM chat_messages WHERE chat_id = ?", params: [id] },
        ...messages.map((m) => ({
          sql: "INSERT INTO chat_messages (chat_id, message) VALUES (?, ?)",
          params: [id, JSON.stringify(m)],
        })),
      ], { transaction: true });
    },
  });
}

const rows = await db.query(
  "SELECT message FROM chat_messages WHERE chat_id = ? ORDER BY id",
  [chatId]
);
const messages = rows.data.map((r) => JSON.parse(r.message as string)) as UIMessage[];

Pass the loaded messages to useChat as the initial state and the conversation survives reloads, deploys, and device switches.

Why a database per app (or per user)

Chat history is user data with real blast radius. A PerSQL database per app — or per user, provisioned on first message — keeps each scope isolated, meters its own usage, and stays queryable: “messages per day”, “longest threads”, “users active this week” are SQL, not a product analytics request.