Data persistence

The Vercel AI Chatbot uses PostgreSQL for structured data storage and Vercel Blob for file uploads, providing a complete persistence layer for chats, messages, artifacts, and user data.

Database architecture

The application uses Drizzle ORM with PostgreSQL for type-safe database operations:

lib/db/schema.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres(process.env.POSTGRES_URL!);
const db = drizzle(client);

Core tables

The database schema includes tables for users, chats, messages, documents, and more:

Users
Chats
Messages
Documents
Votes

lib/db/schema.ts

import { pgTable, uuid, varchar } from "drizzle-orm/pg-core";

export const user = pgTable("User", {
  id: uuid("id").primaryKey().notNull().defaultRandom(),
  email: varchar("email", { length: 64 }).notNull(),
  password: varchar("password", { length: 64 }),
});

export type User = InferSelectModel<typeof user>;

lib/db/schema.ts

export const chat = pgTable("Chat", {
  id: uuid("id").primaryKey().notNull().defaultRandom(),
  createdAt: timestamp("createdAt").notNull(),
  title: text("title").notNull(),
  userId: uuid("userId")
    .notNull()
    .references(() => user.id),
  visibility: varchar("visibility", { enum: ["public", "private"] })
    .notNull()
    .default("private"),
});

export type Chat = InferSelectModel<typeof chat>;

lib/db/schema.ts

export const message = pgTable("Message_v2", {
  id: uuid("id").primaryKey().notNull().defaultRandom(),
  chatId: uuid("chatId")
    .notNull()
    .references(() => chat.id),
  role: varchar("role").notNull(),
  parts: json("parts").notNull(),
  attachments: json("attachments").notNull(),
  createdAt: timestamp("createdAt").notNull(),
});

export type DBMessage = InferSelectModel<typeof message>;

lib/db/schema.ts

export const document = pgTable(
  "Document",
  {
    id: uuid("id").notNull().defaultRandom(),
    createdAt: timestamp("createdAt").notNull(),
    title: text("title").notNull(),
    content: text("content"),
    kind: varchar("text", { enum: ["text", "code", "image", "sheet"] })
      .notNull()
      .default("text"),
    userId: uuid("userId")
      .notNull()
      .references(() => user.id),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.id, table.createdAt] }),
  })
);

export type Document = InferSelectModel<typeof document>;

lib/db/schema.ts

export const vote = pgTable(
  "Vote_v2",
  {
    chatId: uuid("chatId")
      .notNull()
      .references(() => chat.id),
    messageId: uuid("messageId")
      .notNull()
      .references(() => message.id),
    isUpvoted: boolean("isUpvoted").notNull(),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.chatId, table.messageId] }),
  })
);

export type Vote = InferSelectModel<typeof vote>;

Chat operations

Manage chat sessions with type-safe database queries:

Creating chats

lib/db/queries.ts

import { chat } from "./schema";

export async function saveChat({
  id,
  userId,
  title,
  visibility,
}: {
  id: string;
  userId: string;
  title: string;
  visibility: VisibilityType;
}) {
  try {
    return await db.insert(chat).values({
      id,
      createdAt: new Date(),
      userId,
      title,
      visibility,
    });
  } catch (_error) {
    throw new ChatbotError("bad_request:database", "Failed to save chat");
  }
}

Retrieving chats

lib/db/queries.ts

import { desc, eq, gt, lt } from "drizzle-orm";

export async function getChatsByUserId({
  id,
  limit,
  startingAfter,
  endingBefore,
}: {
  id: string;
  limit: number;
  startingAfter: string | null;
  endingBefore: string | null;
}) {
  const extendedLimit = limit + 1;

  const query = (whereCondition?: SQL<any>) =>
    db
      .select()
      .from(chat)
      .where(
        whereCondition
          ? and(whereCondition, eq(chat.userId, id))
          : eq(chat.userId, id)
      )
      .orderBy(desc(chat.createdAt))
      .limit(extendedLimit);

  let filteredChats: Chat[] = [];

  if (startingAfter) {
    const [selectedChat] = await db
      .select()
      .from(chat)
      .where(eq(chat.id, startingAfter))
      .limit(1);

    filteredChats = await query(gt(chat.createdAt, selectedChat.createdAt));
  } else if (endingBefore) {
    const [selectedChat] = await db
      .select()
      .from(chat)
      .where(eq(chat.id, endingBefore))
      .limit(1);

    filteredChats = await query(lt(chat.createdAt, selectedChat.createdAt));
  } else {
    filteredChats = await query();
  }

  const hasMore = filteredChats.length > limit;

  return {
    chats: hasMore ? filteredChats.slice(0, limit) : filteredChats,
    hasMore,
  };
}

Deleting chats

lib/db/queries.ts

export async function deleteChatById({ id }: { id: string }) {
  try {
    // Delete related data first
    await db.delete(vote).where(eq(vote.chatId, id));
    await db.delete(message).where(eq(message.chatId, id));
    await db.delete(stream).where(eq(stream.chatId, id));

    // Delete the chat
    const [chatsDeleted] = await db
      .delete(chat)
      .where(eq(chat.id, id))
      .returning();
    
    return chatsDeleted;
  } catch (_error) {
    throw new ChatbotError(
      "bad_request:database",
      "Failed to delete chat by id"
    );
  }
}

Message storage

Messages are stored with structured parts for multimodal content:

Saving messages

lib/db/queries.ts

export async function saveMessages({ messages }: { messages: DBMessage[] }) {
  try {
    return await db.insert(message).values(messages);
  } catch (_error) {
    throw new ChatbotError("bad_request:database", "Failed to save messages");
  }
}

Updating messages

lib/db/queries.ts

export async function updateMessage({
  id,
  parts,
}: {
  id: string;
  parts: DBMessage["parts"];
}) {
  try {
    return await db.update(message).set({ parts }).where(eq(message.id, id));
  } catch (_error) {
    throw new ChatbotError("bad_request:database", "Failed to update message");
  }
}

Retrieving messages

lib/db/queries.ts

import { asc } from "drizzle-orm";

export async function getMessagesByChatId({ id }: { id: string }) {
  try {
    return await db
      .select()
      .from(message)
      .where(eq(message.chatId, id))
      .orderBy(asc(message.createdAt));
  } catch (_error) {
    throw new ChatbotError(
      "bad_request:database",
      "Failed to get messages by chat id"
    );
  }
}

Document management

Documents (artifacts) support version history through composite primary keys:

Saving documents

lib/db/queries.ts

export async function saveDocument({
  id,
  title,
  kind,
  content,
  userId,
}: {
  id: string;
  title: string;
  kind: ArtifactKind;
  content: string;
  userId: string;
}) {
  try {
    return await db
      .insert(document)
      .values({
        id,
        title,
        kind,
        content,
        userId,
        createdAt: new Date(),
      })
      .returning();
  } catch (_error) {
    throw new ChatbotError("bad_request:database", "Failed to save document");
  }
}

Document versioning

lib/db/queries.ts

export async function getDocumentsById({ id }: { id: string }) {
  try {
    const documents = await db
      .select()
      .from(document)
      .where(eq(document.id, id))
      .orderBy(asc(document.createdAt));

    return documents;
  } catch (_error) {
    throw new ChatbotError(
      "bad_request:database",
      "Failed to get documents by id"
    );
  }
}

Documents use a composite primary key (id, createdAt) to maintain version history. Each edit creates a new row with the same ID but a different timestamp.

Deleting document versions

lib/db/queries.ts

export async function deleteDocumentsByIdAfterTimestamp({
  id,
  timestamp,
}: {
  id: string;
  timestamp: Date;
}) {
  try {
    // Delete suggestions first (foreign key constraint)
    await db
      .delete(suggestion)
      .where(
        and(
          eq(suggestion.documentId, id),
          gt(suggestion.documentCreatedAt, timestamp)
        )
      );

    // Delete document versions
    return await db
      .delete(document)
      .where(and(eq(document.id, id), gt(document.createdAt, timestamp)))
      .returning();
  } catch (_error) {
    throw new ChatbotError(
      "bad_request:database",
      "Failed to delete documents by id after timestamp"
    );
  }
}

Vote tracking

Track user feedback on assistant responses:

lib/db/queries.ts

export async function voteMessage({
  chatId,
  messageId,
  type,
}: {
  chatId: string;
  messageId: string;
  type: "up" | "down";
}) {
  try {
    const [existingVote] = await db
      .select()
      .from(vote)
      .where(eq(vote.messageId, messageId));

    if (existingVote) {
      return await db
        .update(vote)
        .set({ isUpvoted: type === "up" })
        .where(and(eq(vote.messageId, messageId), eq(vote.chatId, chatId)));
    }
    
    return await db.insert(vote).values({
      chatId,
      messageId,
      isUpvoted: type === "up",
    });
  } catch (_error) {
    throw new ChatbotError("bad_request:database", "Failed to vote message");
  }
}

File uploads

File attachments are stored in Vercel Blob:

app/(chat)/api/files/upload/route.ts

import { put } from "@vercel/blob";
import { z } from "zod";

const FileSchema = z.object({
  file: z
    .instanceof(Blob)
    .refine((file) => file.size <= 5 * 1024 * 1024, {
      message: "File size should be less than 5MB",
    })
    .refine((file) => ["image/jpeg", "image/png"].includes(file.type), {
      message: "File type should be JPEG or PNG",
    }),
});

export async function POST(request: Request) {
  const session = await auth();

  if (!session) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const formData = await request.formData();
  const file = formData.get("file") as Blob;

  const validatedFile = FileSchema.safeParse({ file });

  if (!validatedFile.success) {
    const errorMessage = validatedFile.error.errors
      .map((error) => error.message)
      .join(", ");
    return NextResponse.json({ error: errorMessage }, { status: 400 });
  }

  const filename = (formData.get("file") as File).name;
  const fileBuffer = await file.arrayBuffer();

  const data = await put(`${filename}`, fileBuffer, {
    access: "public",
  });

  return NextResponse.json(data);
}

File size limit

Maximum file size is 5MB per upload

Supported formats

Only JPEG and PNG images are accepted

Database migrations

Manage schema changes with Drizzle migrations:

lib/db/migrate.ts

import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";

const runMigrations = async () => {
  const connection = postgres(process.env.POSTGRES_URL!, { max: 1 });
  const db = drizzle(connection);
  
  await migrate(db, { migrationsFolder: "./drizzle" });
  await connection.end();
};

runMigrations();

Drizzle configuration

drizzle.config.ts

import type { Config } from "drizzle-kit";

export default {
  schema: "./lib/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.POSTGRES_URL!,
  },
} satisfies Config;

Rate limiting

Track message counts for rate limiting:

lib/db/queries.ts

import { count, gte } from "drizzle-orm";

export async function getMessageCountByUserId({
  id,
  differenceInHours,
}: {
  id: string;
  differenceInHours: number;
}) {
  const timeAgo = new Date(
    Date.now() - differenceInHours * 60 * 60 * 1000
  );

  const [stats] = await db
    .select({ count: count(message.id) })
    .from(message)
    .innerJoin(chat, eq(message.chatId, chat.id))
    .where(
      and(
        eq(chat.userId, id),
        gte(message.createdAt, timeAgo),
        eq(message.role, "user")
      )
    )
    .execute();

  return stats?.count ?? 0;
}

Error handling

All database operations use custom error handling:

lib/errors.ts

export class ChatbotError extends Error {
  constructor(
    public code: string,
    message: string
  ) {
    super(message);
    this.name = "ChatbotError";
  }

  toResponse() {
    return Response.json(
      { error: this.message },
      { status: this.getStatusCode() }
    );
  }

  private getStatusCode() {
    if (this.code.startsWith("unauthorized")) return 401;
    if (this.code.startsWith("forbidden")) return 403;
    if (this.code.startsWith("not_found")) return 404;
    if (this.code.startsWith("rate_limit")) return 429;
    return 500;
  }
}

Authentication

Learn about user authentication

Artifacts

Understand document storage

Get Started

Core Features

Deployment

Customization

Database architecture

Core tables

Chat operations

Creating chats

Retrieving chats

Deleting chats

Message storage

Saving messages

Updating messages

Retrieving messages

Document management

Saving documents

Document versioning

Deleting document versions

Vote tracking

File uploads

File size limit

Supported formats

Database migrations

Drizzle configuration

Rate limiting

Error handling

Authentication

Artifacts

Get Started

Core Features

Deployment

Customization

​Database architecture

​Core tables

​Chat operations

​Creating chats

​Retrieving chats

​Deleting chats

​Message storage

​Saving messages

​Updating messages

​Retrieving messages

​Document management

​Saving documents

​Document versioning

​Deleting document versions

​Vote tracking

​File uploads

File size limit

Supported formats

​Database migrations

​Drizzle configuration

​Rate limiting

​Error handling

​Related documentation

Authentication

Artifacts

Database architecture

Core tables

Chat operations

Creating chats

Retrieving chats

Deleting chats

Message storage

Saving messages

Updating messages

Retrieving messages

Document management

Saving documents

Document versioning

Deleting document versions

Vote tracking

File uploads

Database migrations

Drizzle configuration

Rate limiting

Error handling

Related documentation