Database Setup and Migrations

Overview

Documenso uses Prisma as its ORM (Object-Relational Mapping) tool with PostgreSQL as the database. This guide covers database setup, schema management, migrations, and common operations.

Database Stack

Database: PostgreSQL 15+
ORM: Prisma 6.19.0
Query Builder: Kysely (via prisma-kysely extension)
Schema Location: packages/prisma/schema.prisma
Migrations: packages/prisma/migrations/

Schema Overview

The Prisma schema defines the entire database structure including:

Users & Authentication: User accounts, sessions, passkeys, OAuth providers
Documents & Envelopes: Document storage, signing workflows, templates
Recipients & Fields: Signer information, form fields, signatures
Organizations & Teams: Multi-tenant structure, team management
Subscriptions & Billing: Stripe integration, subscription claims
Webhooks & API: Webhook configurations, API tokens
Storage: Document data, attachments, avatar images

View the complete schema at packages/prisma/schema.prisma.

Database Setup

Using Docker (Recommended)

The quickest way to set up a PostgreSQL database:

Start Docker services

npm run dx:up

This starts PostgreSQL on localhost:54320 with:

User: documenso
Password: password
Database: documenso

Run migrations

npm run prisma:migrate-dev

Seed the database (optional)

npm run prisma:seed

Manual PostgreSQL Setup

If you have PostgreSQL installed locally:

Create the database

createdb documenso

Or using psql:

CREATE DATABASE documenso;

Configure connection

Update your .env file with the connection string:

NEXT_PRIVATE_DATABASE_URL="postgres://username:password@localhost:5432/documenso"
NEXT_PRIVATE_DIRECT_DATABASE_URL="postgres://username:password@localhost:5432/documenso"

Run migrations

npm run prisma:migrate-dev

Prisma Commands

All Prisma commands are available as npm scripts:

# Create and apply a new migration (development)
npm run prisma:migrate-dev

# Apply migrations (production)
npm run prisma:migrate-deploy

# Reset the database (WARNING: deletes all data)
npm run prisma:migrate-reset

Running Commands with Environment Variables

If running Prisma commands directly, wrap them with with:env:

npm run with:env -- npx prisma studio
npm run with:env -- npx prisma db push

Working with Migrations

Creating a Migration

When you modify the Prisma schema:

Update the schema

Edit packages/prisma/schema.prisma to add, modify, or remove models and fields.Example:

model Document {
  id        String   @id @default(cuid())
  title     String
  status    DocumentStatus @default(DRAFT)
  createdAt DateTime @default(now())
  // Add new field
  tags      String[]
}

Create the migration

npm run prisma:migrate-dev

Prisma will:

Prompt you for a migration name
Generate SQL migration files
Apply the migration to your database
Regenerate the Prisma Client

Review the migration

Check the generated SQL file in packages/prisma/migrations/:

migrations/
└── 20260304123456_add_document_tags/
    └── migration.sql

Test the changes

Verify the migration works correctly with your application code.

Migration Files

Migrations are stored in packages/prisma/migrations/ with timestamps:

migrations/
├── migration_lock.toml
├── 20260224000000_add_email_domain_last_verified_at/
│   └── migration.sql
├── 20260219141233_add_rate_limit_table/
│   └── migration.sql
└── ...

Each migration folder contains:

migration.sql - SQL statements to apply the migration

Applying Migrations in Production

For production deployments, use migrate deploy instead of migrate dev:

npm run prisma:migrate-deploy

This command:

Applies pending migrations
Does NOT prompt for input (CI/CD friendly)
Does NOT automatically create migrations
Does NOT reset the database

Resetting the Database

To completely reset your database (useful for development):

npm run prisma:migrate-reset

This will:

Drop the database
Recreate it
Apply all migrations
Run the seed script (if configured)

This deletes all data. Only use in development!

Database Seeding

The seed script creates test data for development:

Running the Seed

npm run prisma:seed

Seed Script Location

The seed script is located at packages/prisma/seed-database.ts and configured in packages/prisma/package.json:

{
  "prisma": {
    "seed": "tsx ./seed-database.ts"
  }
}

What Gets Seeded

The seed script typically creates:

Test user accounts
Sample documents
Example templates
Test recipients and signatures

Check the seed files in packages/prisma/seed/ for details.

Prisma Studio

Prisma Studio is a visual database browser:

Start Prisma Studio

npm run prisma:studio

Access the interface

Open http://localhost:5555 in your browser

Browse and edit data

View all database tables
Filter and search records
Create, update, and delete records
Explore relationships between models

Prisma Client Usage

The Prisma Client is auto-generated and provides type-safe database queries.

Importing Prisma Client

import { prisma } from '@documenso/prisma';

Example Queries

// Find a user by email
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' },
  include: { accounts: true },
});

// Create a new document
const document = await prisma.envelope.create({
  data: {
    title: 'Contract Agreement',
    status: 'DRAFT',
    userId: user.id,
    teamId: team.id,
    documentMetaId: documentMeta.id,
  },
});

// Update a recipient
const recipient = await prisma.recipient.update({
  where: { id: recipientId },
  data: { signingStatus: 'SIGNED', signedAt: new Date() },
});

// Query with relations
const envelopes = await prisma.envelope.findMany({
  where: { userId: user.id, status: 'COMPLETED' },
  include: {
    recipients: true,
    fields: true,
    documentMeta: true,
  },
  orderBy: { createdAt: 'desc' },
});

Database Connection

Connection Strings

Documenso uses two connection strings:

Standard Connection (NEXT_PRIVATE_DATABASE_URL):
- Used for normal application queries
- Can use connection pooling (e.g., PgBouncer)
- Format: postgres://user:password@host:port/database
Direct Connection (NEXT_PRIVATE_DIRECT_DATABASE_URL):
- Used for migrations and schema operations
- Must be a direct connection (no pooling)
- Format: Same as above for most setups

Connection Pooling

For production, consider using connection pooling:

# With PgBouncer
NEXT_PRIVATE_DATABASE_URL="postgres://user:password@pgbouncer:6432/database?pgbouncer=true"
NEXT_PRIVATE_DIRECT_DATABASE_URL="postgres://user:password@postgres:5432/database"

# With Supabase Pooler
NEXT_PRIVATE_DATABASE_URL="postgres://user:[email protected]:6543/postgres"
NEXT_PRIVATE_DIRECT_DATABASE_URL="postgres://user:[email protected]:5432/postgres"

Code Generators

Prisma schema uses multiple generators:

Prisma Client

generator client {
  provider = "prisma-client-js"
}

Generates the standard Prisma Client for database queries.

Kysely

generator kysely {
  provider = "prisma-kysely"
}

Generates Kysely type definitions for type-safe SQL queries.

Zod Schemas

generator zod {
  provider = "zod-prisma-types"
  createInputTypes = false
  writeBarrelFiles = false
  useMultipleFiles = true
}

Generates Zod validation schemas from Prisma models.

JSON Types

generator json {
  provider = "prisma-json-types-generator"
}

Provides type safety for JSON fields.

Database Schema Conventions

When modifying the schema, follow these conventions:

Naming

Model names: PascalCase (e.g., User, Envelope, DocumentMeta)
Field names: camelCase (e.g., userId, createdAt, emailVerified)
Enum values: SCREAMING_SNAKE_CASE (e.g., DRAFT, PENDING, COMPLETED)

Timestamps

Include timestamps on most models:

model Example {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

IDs

Use appropriate ID types:

// CUID for public-facing IDs
id String @id @default(cuid())

// Auto-increment for internal IDs
id Int @id @default(autoincrement())

Relations

Always define both sides of relations:

model User {
  id        Int         @id @default(autoincrement())
  envelopes Envelope[]
}

model Envelope {
  id     String @id
  userId Int
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Troubleshooting

Migration Failed

If a migration fails:

Check the error message for SQL issues
Review the migration SQL file
Fix the schema and try again
If needed, reset the database: npm run prisma:migrate-reset

Schema Out of Sync

If your database schema doesn’t match the Prisma schema:

# Force synchronize (development only)
npm run with:env -- npx prisma db push

# Or create a new migration
npm run prisma:migrate-dev

Prisma Client Not Found

Regenerate the client:

npm run prisma:generate

Type Errors After Schema Changes

After modifying the schema:

Run npm run prisma:migrate-dev (generates new types)
Restart your TypeScript server
Update code to match new types

Getting Started

API Reference

Webhooks

Embedding

Local Development

Contributing

​Overview

​Database Stack

​Schema Overview

​Database Setup

​Using Docker (Recommended)

​Manual PostgreSQL Setup

​Prisma Commands

​Running Commands with Environment Variables

​Working with Migrations

​Creating a Migration

​Migration Files

​Applying Migrations in Production

​Resetting the Database

​Database Seeding

​Running the Seed

​Seed Script Location

​What Gets Seeded

​Prisma Studio

​Prisma Client Usage

​Importing Prisma Client

​Example Queries

​Database Connection

​Connection Strings

​Connection Pooling

​Code Generators

​Prisma Client

​Kysely

​Zod Schemas

​JSON Types

​Database Schema Conventions

​Naming

​Timestamps

​IDs

​Relations

​Troubleshooting

​Migration Failed

​Schema Out of Sync

​Prisma Client Not Found

​Type Errors After Schema Changes

​Next Steps

Build docs developers (and LLMs) love

Overview

Database Stack

Schema Overview

Database Setup

Using Docker (Recommended)

Manual PostgreSQL Setup

Prisma Commands

Running Commands with Environment Variables

Working with Migrations

Creating a Migration

Migration Files

Applying Migrations in Production

Resetting the Database

Database Seeding

Running the Seed

Seed Script Location

What Gets Seeded

Prisma Studio

Prisma Client Usage

Importing Prisma Client

Example Queries

Database Connection

Connection Strings

Connection Pooling

Code Generators

Prisma Client

Kysely

Zod Schemas

JSON Types

Database Schema Conventions

Naming

Timestamps

IDs

Relations

Troubleshooting

Migration Failed

Schema Out of Sync

Prisma Client Not Found

Type Errors After Schema Changes

Next Steps