Database setup

The Vercel AI Chatbot uses PostgreSQL for data persistence and Drizzle ORM for database management.

Database schema

The application uses the following database tables:

User

Stores user accounts and authentication information.

CREATE TABLE "User" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "email" varchar(64) NOT NULL,
  "password" varchar(64)
);

Fields:

id - UUID primary key, auto-generated
email - User email address (max 64 characters)
password - Hashed password (nullable for guest users)

Chat

Stores chat sessions and metadata.

CREATE TABLE "Chat" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "createdAt" timestamp NOT NULL,
  "title" text NOT NULL,
  "userId" uuid NOT NULL REFERENCES "User"("id"),
  "visibility" varchar DEFAULT 'private' NOT NULL
);

Fields:

id - UUID primary key, auto-generated
createdAt - Timestamp when chat was created
title - Chat title/name
userId - Reference to User table
visibility - Either public or private (default: private)

Message_v2

Stores chat messages with support for multi-part content and attachments.

CREATE TABLE "Message_v2" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "chatId" uuid NOT NULL REFERENCES "Chat"("id"),
  "role" varchar NOT NULL,
  "parts" json NOT NULL,
  "attachments" json NOT NULL,
  "createdAt" timestamp NOT NULL
);

Fields:

id - UUID primary key, auto-generated
chatId - Reference to Chat table
role - Message role (user, assistant, system)
parts - JSON array of message parts (text, code, images, etc.)
attachments - JSON array of file attachments
createdAt - Timestamp when message was created

The Message_v2 table replaces the deprecated Message table. See the migration guide for details.

Vote_v2

Stores user feedback (upvotes/downvotes) on messages.

CREATE TABLE "Vote_v2" (
  "chatId" uuid NOT NULL REFERENCES "Chat"("id"),
  "messageId" uuid NOT NULL REFERENCES "Message_v2"("id"),
  "isUpvoted" boolean NOT NULL,
  PRIMARY KEY ("chatId", "messageId")
);

Fields:

chatId - Reference to Chat table
messageId - Reference to Message_v2 table
isUpvoted - True for upvote, false for downvote
Composite primary key on (chatId, messageId)

Document

Stores user documents created in the chat (text, code, images, spreadsheets).

CREATE TABLE "Document" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "createdAt" timestamp NOT NULL,
  "title" text NOT NULL,
  "content" text,
  "kind" varchar DEFAULT 'text' NOT NULL,
  "userId" uuid NOT NULL REFERENCES "User"("id"),
  PRIMARY KEY ("id", "createdAt")
);

Fields:

id - UUID, auto-generated
createdAt - Timestamp when document was created
title - Document title
content - Document content (text, code, etc.)
kind - Document type: text, code, image, or sheet
userId - Reference to User table
Composite primary key on (id, createdAt)

Suggestion

Stores AI-generated suggestions for document improvements.

CREATE TABLE "Suggestion" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "documentId" uuid NOT NULL,
  "documentCreatedAt" timestamp NOT NULL,
  "originalText" text NOT NULL,
  "suggestedText" text NOT NULL,
  "description" text,
  "isResolved" boolean DEFAULT false NOT NULL,
  "userId" uuid NOT NULL REFERENCES "User"("id"),
  "createdAt" timestamp NOT NULL,
  FOREIGN KEY ("documentId", "documentCreatedAt") 
    REFERENCES "Document"("id", "createdAt")
);

Fields:

id - UUID primary key, auto-generated
documentId + documentCreatedAt - Composite foreign key to Document
originalText - Original text being improved
suggestedText - AI-suggested improvement
description - Description of the suggestion
isResolved - Whether suggestion was accepted/rejected
userId - User who requested the suggestion
createdAt - Timestamp when suggestion was created

Stream

Stores streaming session metadata.

CREATE TABLE "Stream" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "chatId" uuid NOT NULL REFERENCES "Chat"("id"),
  "createdAt" timestamp NOT NULL
);

Database configuration

The database is configured using Drizzle Kit in drizzle.config.ts:

drizzle.config.ts

import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({
  path: ".env.local",
});

export default defineConfig({
  schema: "./lib/db/schema.ts",
  out: "./lib/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.POSTGRES_URL!,
  },
});

Key configuration:

schema - TypeScript schema definition (lib/db/schema.ts)
out - Migration files output directory (lib/db/migrations)
dialect - Database type (postgresql)
dbCredentials.url - Connection string from POSTGRES_URL environment variable

Setting up PostgreSQL

Option 1: Vercel Postgres (recommended)

The easiest way to set up PostgreSQL is using Vercel Postgres:

Create database

Go to your Vercel project dashboard
Click on the “Storage” tab
Click “Create Database”
Select “Postgres”
Choose a region close to your deployment

Connect to project

Vercel automatically adds the following environment variables to your project:

POSTGRES_URL
POSTGRES_PRISMA_URL
POSTGRES_URL_NON_POOLING
Additional connection details

Pull environment variables

vercel env pull

This downloads the database credentials to your .env.local file.

Documentation: https://vercel.com/docs/postgres

Option 2: Other PostgreSQL providers

You can use any PostgreSQL provider:

Neon - Serverless Postgres (https://neon.tech)
Supabase - Open-source Firebase alternative (https://supabase.com)
Railway - Simple cloud database (https://railway.app)
Amazon RDS - AWS managed database
Google Cloud SQL - GCP managed database
Self-hosted - Your own PostgreSQL instance

Set the POSTGRES_URL environment variable with your connection string:

POSTGRES_URL=postgres://username:password@host:port/database

Running migrations

Migrations are managed using Drizzle ORM and stored in lib/db/migrations/.

Automatic migrations (during build)

Migrations run automatically during the build process:

pnpm build

This executes:

tsx lib/db/migrate && next build

The migration script (lib/db/migrate.ts):

lib/db/migrate.ts

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

config({
  path: ".env.local",
});

const runMigrate = async () => {
  if (!process.env.POSTGRES_URL) {
    console.log("⏭️  POSTGRES_URL not defined, skipping migrations");
    process.exit(0);
  }

  const connection = postgres(process.env.POSTGRES_URL, { max: 1 });
  const db = drizzle(connection);

  console.log("⏳ Running migrations...");

  const start = Date.now();
  await migrate(db, { migrationsFolder: "./lib/db/migrations" });
  const end = Date.now();

  console.log("✅ Migrations completed in", end - start, "ms");
  process.exit(0);
};

runMigrate().catch((err) => {
  console.error("❌ Migration failed");
  console.error(err);
  process.exit(1);
});

If POSTGRES_URL is not defined, migrations are skipped gracefully. This is useful for preview deployments without a database.

Manual migrations

You can also run migrations manually:

pnpm db:migrate

This executes:

npx tsx lib/db/migrate.ts

Generating new migrations

When you modify the schema in lib/db/schema.ts, generate a migration:

pnpm db:generate

This creates a new migration file in lib/db/migrations/ based on schema changes.

Database management commands

The package.json includes several database management scripts:

package.json

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "npx tsx lib/db/migrate.ts",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push",
    "db:pull": "drizzle-kit pull",
    "db:check": "drizzle-kit check",
    "db:up": "drizzle-kit up"
  }
}

Available commands

db:generate

Generate migration files from schema changes:

pnpm db:generate

Use this after modifying lib/db/schema.ts to create a new migration.

db:migrate

Run pending migrations:

pnpm db:migrate

Applies all migrations from lib/db/migrations/ that haven’t been run yet.

db:studio

Open Drizzle Studio (database GUI):

pnpm db:studio

Launches a web interface to browse and edit your database.

db:push

Push schema changes directly to database:

pnpm db:push

⚠️ Warning: This bypasses migrations and directly modifies your database. Use for development only.

db:pull

Pull schema from existing database:

pnpm db:pull

Introspects your database and generates a schema file.

db:check

Validate migration files:

pnpm db:check

Checks for issues in your migrations.

Troubleshooting

Migration failed - connection refused

Cause: Cannot connect to PostgreSQL database.Solutions:

Verify POSTGRES_URL is correctly set
Check database is running and accessible
Verify network/firewall allows connections
Test connection: psql $POSTGRES_URL

Migration failed - permission denied

Cause: Database user lacks necessary permissions.Solutions:

Ensure database user has CREATE and ALTER permissions
Check user can create tables and modify schema
On managed services, verify user role/permissions

Duplicate migration error

Cause: Migration has already been applied.Solutions:

Check migration history in database
Drizzle tracks applied migrations automatically
If needed, manually remove migration from tracking table

Schema mismatch

Cause: Database schema doesn’t match TypeScript schema.Solutions:

Generate a new migration: pnpm db:generate
Review the generated migration file
Apply migration: pnpm db:migrate
For development, you can use: pnpm db:push (destructive)

Best practices

Follow these best practices for database management:

Always use migrations - Don’t manually modify production database schema
Review migrations - Check generated migration files before applying
Test migrations - Test on development/staging before production
Backup before migrating - Always backup production data before running migrations
Version control migrations - Commit migration files to Git
Never edit applied migrations - Create new migrations for changes
Use transactions - Migrations run in transactions by default for safety

Get Started

Core Features

Deployment

Customization

Database schema

User

Chat

Message_v2

Vote_v2

Document

Suggestion

Stream

Database configuration

Setting up PostgreSQL

Option 1: Vercel Postgres (recommended)

Option 2: Other PostgreSQL providers

Running migrations

Automatic migrations (during build)

Manual migrations

Generating new migrations

Database management commands

Available commands

Troubleshooting

Best practices

Next steps

Deploy to Vercel

Environment variables

Get Started

Core Features

Deployment

Customization

​Database schema

​User

​Chat

​Message_v2

​Vote_v2

​Document

​Suggestion

​Stream

​Database configuration

​Setting up PostgreSQL

​Option 1: Vercel Postgres (recommended)

​Option 2: Other PostgreSQL providers

​Running migrations

​Automatic migrations (during build)

​Manual migrations

​Generating new migrations

​Database management commands

​Available commands

​Troubleshooting

​Best practices

​Next steps

Deploy to Vercel

Environment variables

Database schema

User

Chat

Message_v2

Vote_v2

Document

Suggestion

Stream

Database configuration

Setting up PostgreSQL

Option 1: Vercel Postgres (recommended)

Option 2: Other PostgreSQL providers

Running migrations

Automatic migrations (during build)

Manual migrations

Generating new migrations

Database management commands

Available commands

Troubleshooting

Best practices

Next steps