Database Setup - Paperclip

Paperclip uses PostgreSQL via Drizzle ORM. Choose from embedded PostgreSQL, local Docker, or hosted providers.

Database Modes

Three deployment options:

Embedded PostgreSQL - Zero config, auto-managed (default)
Local PostgreSQL - Docker Compose for development
Hosted PostgreSQL - Supabase, AWS RDS, etc. for production

Embedded PostgreSQL

The simplest option - no setup required.

How It Works

Leave DATABASE_URL unset
Server auto-starts embedded PostgreSQL
Data persists in ~/.paperclip/instances/default/db/

# Just run - embedded DB starts automatically
pnpm dev

Configuration

Data directory:

PAPERCLIP_HOME=~/.paperclip
PAPERCLIP_INSTANCE_ID=default

Data location: $PAPERCLIP_HOME/instances/$PAPERCLIP_INSTANCE_ID/db/ Custom port (if default 54329 conflicts):

// ~/.paperclip/instances/default/config.json
{
  "database": {
    "embeddedPostgresPort": 54330
  }
}

Reset Embedded Database

rm -rf ~/.paperclip/instances/default/db
pnpm dev

Database recreates automatically on startup.

When to Use

✅ Local development
✅ Testing and demos
✅ Single-machine deployments
❌ High-traffic production
❌ Multi-instance deployments

Local PostgreSQL (Docker)

For development with a full PostgreSQL server.

Setup

Start PostgreSQL:

docker compose up -d

This starts PostgreSQL 17 on localhost:5432. Set connection string:

cp .env.example .env

.env:

DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip

Push schema:

DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip \
  npx drizzle-kit push

Start server:

pnpm dev

docker-compose.yml

Included in the repo:

services:
  db:
    image: postgres:17-alpine
    environment:
      POSTGRES_USER: paperclip
      POSTGRES_PASSWORD: paperclip
      POSTGRES_DB: paperclip
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

When to Use

✅ Team development
✅ Testing migrations
✅ Matching production setup locally
❌ Quick local experiments (use embedded instead)

Hosted PostgreSQL

For production deployments.

Supabase

1. Create project:

Visit database.new
Create a new project
Wait for provisioning (~2 minutes)

2. Get connection string:

Go to Project Settings > Database > Connection string
Copy Connection pooling URL (port 6543)

3. Configure Drizzle for pooling: Edit packages/db/src/client.ts:

export function createDb(url: string) {
  const sql = postgres(url, { prepare: false }); // Disable prepared statements
  return drizzlePg(sql, { schema });
}

4. Set environment variable:

# Application connection (pooled)
DATABASE_URL=postgres://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres

5. Push schema (use direct connection):

# Direct connection for migrations (port 5432)
DATABASE_URL=postgres://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:5432/postgres \
  npx drizzle-kit push

Supabase free tier:

500 MB database storage
200 concurrent connections
Projects pause after 1 week of inactivity

AWS RDS

1. Create RDS instance:

# Via AWS Console or CLI
aws rds create-db-instance \
  --db-instance-identifier paperclip-prod \
  --db-instance-class db.t3.micro \
  --engine postgres \
  --engine-version 17.2 \
  --master-username paperclip \
  --master-user-password <PASSWORD> \
  --allocated-storage 20

2. Configure security group:

Allow inbound PostgreSQL (port 5432) from your deployment

3. Set connection string:

DATABASE_URL=postgres://paperclip:<PASSWORD>@paperclip-prod.abc123.us-east-1.rds.amazonaws.com:5432/postgres

Other Providers

Any PostgreSQL 17+ provider works:

Provider	Best For	Notes
Neon	Serverless	Auto-scaling, generous free tier
Railway	Simplicity	One-click Postgres plugin
Google Cloud SQL	GCP deployments	Managed backups
Azure Database	Azure deployments	Integrated security
DigitalOcean	Cost-effective	Simple pricing

General setup:

Create PostgreSQL 17+ instance
Get connection string
Set DATABASE_URL
Push schema with drizzle-kit push

Migrations

Automatic Migrations

On startup, Paperclip automatically runs migrations if:

Database is empty (fresh install)
PAPERCLIP_MIGRATION_PROMPT=never is set

Manual Migrations

Generate migration from schema changes:

pnpm db:generate

This reads packages/db/src/schema/*.ts and generates SQL in packages/db/src/migrations/. Apply migrations:

pnpm db:migrate

Or use drizzle-kit push to sync schema directly (for development):

DATABASE_URL=postgres://... npx drizzle-kit push

Migration Workflow

1. Edit schema:

// packages/db/src/schema/my-table.ts
export const myTable = pgTable('my_table', {
  id: text('id').primaryKey(),
  newColumn: text('new_column'), // Add this
});

2. Export from schema index:

// packages/db/src/schema/index.ts
export * from './my-table.js';

3. Generate migration:

pnpm db:generate

4. Review generated SQL:

cat packages/db/src/migrations/0024_*.sql

5. Apply migration:

pnpm db:migrate

6. Verify TypeScript compiles:

pnpm -r typecheck

Migration Files

Migrations are stored in packages/db/src/migrations/:

packages/db/src/migrations/
├── 0000_mature_masked_marvel.sql
├── 0001_fast_northstar.sql
├── 0002_big_zaladane.sql
└── ...

Each migration is a timestamped SQL file that modifies the schema.

Database Schema

Core tables:

companies - Company entities
agents - AI agents
tasks - Work items
issues - Issue tracking
goals - Company goals
projects - Project organization
agent_api_keys - Agent authentication
company_secrets - Secret storage
company_secret_versions - Secret versioning
activity_log - Audit trail
authUsers - User authentication
authSessions - Session management
instance_user_roles - Instance permissions
company_memberships - Company access

Full schema: packages/db/src/schema/

Connection Management

Connection Pooling

For hosted databases, use connection pooling: Supabase:

# Use port 6543 (pooled)
DATABASE_URL=postgres://...pooler.supabase.com:6543/postgres

Disable prepared statements:

// packages/db/src/client.ts
const sql = postgres(url, { prepare: false });

Connection Limits

PostgreSQL default: 100 connections Paperclip uses 1-2 connections per server instance. For high-traffic deployments:

Use connection pooling (PgBouncer, Supabase Pooler)
Scale PostgreSQL instance
Use read replicas for read-heavy workloads

Backup and Restore

Backup Embedded Database

./scripts/backup-db.sh

Or manually:

tar -czf paperclip-backup-$(date +%Y%m%d).tar.gz \
  ~/.paperclip/instances/default/db

Backup External Database

pg_dump $DATABASE_URL > paperclip-backup-$(date +%Y%m%d).sql

Restore

Embedded:

rm -rf ~/.paperclip/instances/default/db
tar -xzf paperclip-backup-20260304.tar.gz -C ~/.paperclip/instances/default/

External:

psql $DATABASE_URL < paperclip-backup-20260304.sql

Troubleshooting

Connection Refused

Check PostgreSQL is running:

docker compose ps

Check connection string:

echo $DATABASE_URL

Test connection:

psql $DATABASE_URL -c "SELECT version();"

Migration Failures

Reset and retry:

# Embedded
rm -rf ~/.paperclip/instances/default/db
pnpm dev

# External
DATABASE_URL=postgres://... npx drizzle-kit push --force

Too Many Connections

Use connection pooling:

Supabase: Use port 6543
Deploy PgBouncer
Upgrade database instance

Check active connections:

SELECT count(*) FROM pg_stat_activity;

Slow Queries

Enable query logging:

// packages/db/src/client.ts
const sql = postgres(url, { 
  debug: (connection, query, params) => {
    console.log({ query, params });
  }
});

Analyze query performance:

EXPLAIN ANALYZE SELECT * FROM tasks WHERE company_id = '...';

Production Checklist

Use hosted PostgreSQL

Don’t use embedded PostgreSQL in production.

Enable connection pooling

Use pooled connections (port 6543 for Supabase).

Set up automated backups

Use provider backups or pg_dump cron jobs.

Monitor connections

Alert on connection count approaching limits.

Test disaster recovery

Verify backup restoration works.

Use migrations in CI/CD

Run pnpm db:migrate in deployment pipeline.

Next Steps

Configuration

Configure database connection and runtime options

Security

Secure database credentials and access

Get Started

Core Concepts

Guides

Agent Integration

Deployment

​Database Modes

​Embedded PostgreSQL

​How It Works

​Configuration

​Reset Embedded Database

​When to Use

​Local PostgreSQL (Docker)

​Setup

​docker-compose.yml

​When to Use

​Hosted PostgreSQL

​Supabase

​AWS RDS

​Other Providers

​Migrations

​Automatic Migrations

​Manual Migrations

​Migration Workflow

​Migration Files

​Database Schema

​Connection Management

​Connection Pooling

​Connection Limits

​Backup and Restore

​Backup Embedded Database

​Backup External Database

​Restore

​Troubleshooting

​Connection Refused

​Migration Failures

​Too Many Connections

​Slow Queries

​Production Checklist

​Next Steps

Configuration

Security

Database Modes

Embedded PostgreSQL

How It Works

Configuration

Reset Embedded Database

When to Use

Local PostgreSQL (Docker)

Setup

docker-compose.yml

When to Use

Hosted PostgreSQL

Supabase

AWS RDS

Other Providers

Migrations

Automatic Migrations

Manual Migrations

Migration Workflow

Migration Files

Database Schema

Connection Management

Connection Pooling

Connection Limits

Backup and Restore

Backup Embedded Database

Backup External Database

Restore

Troubleshooting

Connection Refused

Migration Failures

Too Many Connections

Slow Queries

Production Checklist

Next Steps