Skip to main content

Database Management

OpenCLM uses PostgreSQL 15+ as its database. Prisma ORM manages the schema and migrations.

Migrations​

Run migrations whenever you upgrade OpenCLM:

docker compose exec api npx prisma migrate deploy

This applies any pending migrations. It is safe to run multiple times — applied migrations are skipped.

Creating a New Migration (Development)​

When you change the Prisma schema (server/prisma/schema.prisma):

cd server
npx prisma migrate dev --name describe_the_change

This creates a new migration file in server/prisma/migrations/.

Seeding​

The seed script creates the default 12 RBAC roles and a Super Admin user:

docker compose exec api npx prisma db seed

Re-running seed is safe — it uses upsert operations and will not duplicate data.

Backup​

Automated Backup Script​

Use pg_dump to create a backup:

#!/bin/bash
# backup.sh
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="openclm_backup_${TIMESTAMP}.sql.gz"

docker compose exec -T db pg_dump \
  -U ${POSTGRES_USER} \
  -d ${POSTGRES_DB} \
  --no-owner \
  --no-acl \
  | gzip > "/backups/${BACKUP_FILE}"

echo "Backup saved: ${BACKUP_FILE}"

Schedule with cron:

0 2 * * * /opt/openclm/backup.sh >> /var/log/openclm-backup.log 2>&1

Backup to S3​

# After creating the dump, upload to S3
aws s3 cp "/backups/${BACKUP_FILE}" "s3://your-bucket/openclm-backups/"

Restore​

# Restore from a backup file
gunzip < /backups/openclm_backup_20250901_020000.sql.gz \
  | docker compose exec -T db psql \
    -U ${POSTGRES_USER} \
    -d ${POSTGRES_DB}

:::warning Stop the API before restoring Stop the API service before restoring to prevent write conflicts:

docker compose stop api
# ... restore ...
docker compose start api

:::

Database Connection Pooling​

For high-traffic deployments, add PgBouncer in front of PostgreSQL:

# Add to docker-compose.yml
pgbouncer:
  image: bitnami/pgbouncer:latest
  environment:
    POSTGRESQL_HOST: db
    POSTGRESQL_PORT: 5432
    POSTGRESQL_DATABASE: ${POSTGRES_DB}
    POSTGRESQL_USERNAME: ${POSTGRES_USER}
    POSTGRESQL_PASSWORD: ${POSTGRES_PASSWORD}
    PGBOUNCER_POOL_MODE: transaction
    PGBOUNCER_MAX_CLIENT_CONN: 200
    PGBOUNCER_DEFAULT_POOL_SIZE: 25

Update DATABASE_URL in the API service to point to PgBouncer instead of directly to db.

Monitoring​

Monitor PostgreSQL health with:

docker compose exec db psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\l"
docker compose exec db pg_isready -U ${POSTGRES_USER}

For production, consider integrating with pganalyze, Datadog, or Prometheus + postgres_exporter.