Database and migrations

Ortha needs PostgreSQL 16 or newer and one connection string. No second datastore.

# docker-compose.yml in a generated app — for development
services:
    postgres:
        image: postgres:17-alpine
        restart: unless-stopped
        environment:
            POSTGRES_USER: orthacms
            POSTGRES_PASSWORD: orthacms
            POSTGRES_DB: my_cms
        ports:
            - '5432:5432'
        volumes:
            - postgres-data:/var/lib/postgresql/data
        healthcheck:
            test: ['CMD-SHELL', 'pg_isready -U orthacms']
            interval: 5s
            timeout: 5s
            retries: 10

volumes:
    postgres-data:

DATABASE_URL=postgresql://orthacms:orthacms@localhost:5432/my_cms

The monorepo’s own compose file runs postgres:16-alpine with database orthacms. A production database is yours to provision.

One connection, one pool

• One Drizzle client over pg, shared by every plugin. No read replicas, no sharding, no database-per-workspace.
• Pool: at most 10 connections per process; a request waits at most 10 s for one, then fails (instead of pg’s wait-forever default).
• Size Postgres max_connections for 10 per replica plus headroom.

Tune it in plugins.ts, not the environment:

DatabasePlugin({
    connectionString: config.database.url,
    outboxRetentionDays: config.database.outboxRetentionDays,
    poolMax: 20,                       // default 10
    connectionTimeoutMillis: 5_000,    // default 10000
    statementTimeoutMillis: 30_000     // default: none
})

Migrations

Each plugin owns its migrations and tracks them in its own table (__drizzle_migrations_identity, __drizzle_migrations_workspaces, …).

npx orthacms migrate            # generated app
npx nx run server:db:migrate    # inside the monorepo — same implementation

• Compiles the server, reads the compiled config, applies pending migrations in plugin-list order.
• Refuses to run without DATABASE_URL, and prints the host:port/database it will change.
• A failure names the plugin and how many plugins committed before it. Fix, re-run — it resumes.

Your own content tables

npx orthacms generate --name=add-author   # drizzle-kit diff → migrations/*.sql
npx orthacms migrate

Read and commit the generated SQL. See migrations.

In a release pipeline

Run migrations as one release step, before the new version starts:

• not safe to run concurrently from several replicas;
• a server booted against an un-migrated database fails on its first query, not at startup.

npx orthacms build
npx orthacms migrate        # once, from one job
# then roll out the new version

Migrations are forward-only. There is no down.

Inspecting

npx orthacms studio                         # Drizzle Studio on the live database
npx orthacms studio --host 0.0.0.0 --port 4983

What grows

Pruned automatically, at most hourly:

• outbox_events — delivered rows older than OUTBOX_RETENTION_DAYS (30). 0 keeps them.
• Webhook deliveries — finished rows older than WEBHOOKS_RETENTION_DAYS (30). For “forever”, set retentionDays: 0 in config/webhooks.ts.
• mail_deliveries — a row is deleted once the relay accepts it; expired rows (dead letters included) are swept.

Never pruned:

-- Largest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Parked outbox events: failed 15 times, no longer retried
SELECT * FROM outbox_events
WHERE dispatched_at IS NULL AND attempts >= 15;

Retry a parked event from Activity → Review and retry, or:

curl -X POST -b jar.txt https://cms.example.com/api/activity/dead-letters/<id>/retry

(needs an administrator session; activity:manage). The dialog’s Retry all retries the rows it shows, one request at a time through this same route, and stops at the first failure other than an event that is already gone; there is no bulk route.

Backups

Ordinary PostgreSQL backups — plus the media blobs, which are not in the database.

pg_dump --format=custom --file=orthacms.dump "$DATABASE_URL"
pg_restore --clean --dbname="$DATABASE_URL" orthacms.dump

A database backup without the matching blobs restores a library of broken references. See media storage.

Running several stacks locally

Development slots share one Postgres container and separate by database name:

npm run worktree -- provision <slot> --path <worktree>
npm run worktree -- release <slot> --yes