Database Operations

Managing the PostgreSQL database with Prisma.

Overview

The database schema is defined in packages/database/ using Prisma. We use:

• Neon for PostgreSQL hosting
• Prisma for schema management and migrations
• Three environments: local, staging, production

Credentials Location

Database credentials are stored in:

packages/database/.env.local
packages/database/.env.staging
packages/database/.env.production

Each contains:

DATABASE_URL="postgresql://..."     # Pooled connection
DIRECT_URL="postgresql://..."       # Direct connection (migrations)
REDIS_URL="redis://..."

Warning

Never commit these files. They're git-ignored and managed separately.

Migrations

Development Workflow

# 1. Edit schema in packages/database/prisma/schema.prisma

# 2. Create migration
pnpm db:migrate:local

# 3. Apply to staging
pnpm db:migrate:staging

# 4. Apply to production (careful!)
pnpm db:migrate:prod

Deploy Migrations

Deploy applies migrations without interactive prompts (for CI/CD):

pnpm db:deploy:local
pnpm db:deploy:staging
pnpm db:deploy:prod

Generate Client

After schema changes:

pnpm db:generate

This regenerates the Prisma client for all packages.

Prisma Studio

Visual database browser:

# Local database
pnpm db:studio:local

# Staging database
pnpm db:studio:staging

# Production database (read-only recommended)
pnpm db:studio:prod

Opens at http://localhost:5555

Backup & Recovery

Backup

# Create backup
pnpm db:backup

Backups are stored in backups/ with timestamps.

Restore

# Restore from backup
pnpm db:restore

Clone Database

# Clone database (useful for testing)
pnpm db:clone

Database Protection

Prevent accidental writes to production:

# Enable protection
pnpm db:protect

# Disable protection (for migrations)
pnpm db:unprotect

Status Check

# View database status
pnpm db:status

Shows migration status, connection info, and table stats.

Schema Structure

Key tables:

• workflow - Workflow definitions
• collection - NFT collections
• miniapp_generation - User generations
• miniapp_payment - Payment records
• user - Universal user table

Best Practices

1. Always backup before production migrations
2. Test migrations on staging first
3. Use db:protect when not migrating
4. Review migration SQL before applying to prod
5. Keep migrations small and reversible