Database and migrations in an Apograph deployment

Apograph needs PostgreSQL 16 and one connection string. There is no second datastore.

# docker-compose.yml, as shipped
services:
    postgres:
        image: postgres:16-alpine
        environment:
            POSTGRES_USER: apograph
            POSTGRES_PASSWORD: apograph
            POSTGRES_DB: apograph_cms

The supplied compose file is for development. A production database is yours to provision.

One connection, one pool

The database plugin opens a single Drizzle client over pg and shares it with every other plugin. There is no read-replica routing, no sharding and no database-per-workspace — workspace isolation is a column and an application-layer rule, not a schema boundary.

Migrations

Every plugin owns its own migrations, tracked in its own table (__drizzle_migrations_identity, __drizzle_migrations_activity, and so on), so their histories are independent.

One command applies all of them:

npx apograph migrate

That is the command in an app installed from npm. Inside the Apograph monorepo, where the private @apograph/nx plugin infers the target, the same operation is npx nx run server:db:migrate — two adapters over one implementation.

Plugin order is load-bearing, and undeclared

Migrations are applied in exactly the order the plugin list returns, and there are foreign keys between plugins’ schemas — workspaces’ memberships reference identity’s users. Reordering the list can break a fresh install while leaving an existing database working.

A failure names the plugin, says how many committed before it, and points at ordering as the usual cause.

The command refuses to run without a database URL, and names the host:port/database it is about to change. That refusal exists because an empty connection string makes pg fall through to PGHOST/PGUSER/PGDATABASE, or to localhost and the OS user — which used to mean a cheerful “Migrations complete” after building an entire schema in a database nobody had named.

In a release pipeline

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

Migrations are not safe to run concurrently from several instances.
A server that boots against an un-migrated database fails on its first query, not at startup.

The committed SQL is the artefact. apograph migrate compiles the server and reads the compiled config to find each plugin’s migrations; a pipeline can run the same SQL however it prefers, as long as each plugin’s tracking table is respected.

Inspecting

npx apograph studio

Drizzle Studio against the live database. Takes --host and --port to change where it binds.

What grows

Three tables grow without bound, and nothing prunes any of them. A fourth, webhook_deliveries, is the exception that proves it: the webhooks plugin prunes completed deliveries older than WEBHOOKS_RETENTION_DAYS (default 30) at most once an hour, and setting it to 0 opts into growth deliberately.

Table	Grows with	Notes
`content_entry_revisions`	Every save of every entry	A full snapshot each time, including rich-text bodies. Usually the largest table by far.
`activity_events`	Every audited change	Append-only by design.
`outbox_events`	Every domain event	Dispatched rows stay.

Plan for revisions before they surprise you

There is no retention policy, no cap, and no archival job. On a busy editorial type with large bodies, revisions will dominate your database size.

If you need a policy, it is yours to write — and to write carefully, since deleting revisions removes the ability to restore or diff them.

Parked outbox rows are worth watching for a different reason: a row that has failed delivery 15 times is no longer retried, and nothing tells you.

SELECT * FROM outbox_events
WHERE dispatched_at IS NULL AND attempts >= 15;

Backups

Ordinary PostgreSQL backups. There is nothing to quiesce and no second store to keep consistent with it — except media blobs, which live on disk or in S3 rather than in the database. A database backup without the corresponding 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 — CREATE DATABASE already isolates the data, and several containers do not fit in memory.

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