Paperclip uses PostgreSQL via Drizzle. The active DB target is resolved in this order:
  1. DATABASE_URL
  2. .paperclip/.env
  3. config.database.connectionString
  4. embedded PostgreSQL under ~/.paperclip/instances/<instance>/db
The schema stays the same across modes. The checked-in migration journal in packages/db/src/migrations/meta/_journal.json is the canonical migration order.

​
Runtime Modes

ModeWhen it is selectedNotes
Embedded PostgreSQLNo external connection string is configuredDefault local runtime
External PostgreSQLDATABASE_URL, .paperclip/.env, or config points at PostgresUse for paperclip_prod, Docker Postgres, or hosted providers

​
Embedded PostgreSQL

Leave DATABASE_URL unset: 
pnpm dev
On first start the runtime:
  1. creates ~/.paperclip/instances/default/db/
  2. ensures the paperclip database exists
  3. inspects and applies pending migrations through the DB package bootstrap flow
  4. starts serving requests
Reset local embedded state with: 
rm -rf ~/.paperclip/instances/default/db

​
External PostgreSQL

Use external PostgreSQL when you want the app to target paperclip_prod, local Docker Postgres, or a hosted provider. Local Docker example: 
docker compose up -d
cp .env.example .env
# DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip
The important rule is simple: point Paperclip at the right connection string and keep the existing migration flow intact.

​
Migration Workflow

Use the repo migration flow, not drizzle-kit push, for real runtime upgrades: 
pnpm db:migrate
Inspect migration state without applying: 
pnpm --filter @paperclipai/db exec tsx src/migration-status.ts --json
The dev runner already uses that same migration-status contract before watch-mode startup.

​
Backup And Restore

Create a backup with: 
pnpm paperclipai db:backup
# or
pnpm db:backup
Defaults:
  • backup dir: ~/.paperclip/instances/<instance>/data/backups
  • retention: 30 days
  • backup target follows the same DB resolution order as runtime startup
Restore uses the shared helper in packages/db/src/backup-lib.ts. That path is exercised today by controlled operator flows and worktree seeding; it is not yet wrapped in a standalone public db:restore command.

​
ClickHouse Mirror

Paperclip can optionally mirror append-heavy telemetry into ClickHouse. PostgreSQL remains canonical; ClickHouse is an additive analytics/event warehouse. Today the server mirrors these tables on write:
  • heartbeat_run_events
  • cost_events
  • finance_events
  • performance_ledger
Mirror behavior:
  • disabled unless ClickHouse is configured
  • non-blocking for app startup
  • write-through for new events only
  • no automated historical backfill
Analytical read surfaces then consume those mirrored facts through ClickHouse, MindsDB, Redash, Telescope, and adjacent tooling.

​
Deeper References