Database plugin — pool, unit of work and outbox

The first plugin in the list. No routes, no UI. It gives every other plugin:

a connection pool (InjectDatabase),
a transaction boundary (UnitOfWork),
a transactional outbox for domain events (OutboxWriter, OutboxDispatcher).

Install

npm install @orthacms/database

Register

// apps/server/src/plugins.ts
import { DatabasePlugin } from '@orthacms/database';

export function buildPlugins(config: OrthaCmsConfig): ServerPlugin[] {
    return [
        // First: the only plugin that opens a resource in onPluginInit
        DatabasePlugin({
            connectionString: config.database.url,
            outboxRetentionDays: config.database.outboxRetentionDays
        })
        // …every other plugin
    ];
}

Configuration

// apps/server/orthacms.config.ts
import { readNonNegativeInt, requireEnv } from '@orthacms/utils-server';

const config: OrthaCmsConfig = {
    // …
    database: {
        url: requireEnv('DATABASE_URL', 'Set it in your .env before starting the app.'),
        outboxRetentionDays: readNonNegativeInt('OUTBOX_RETENTION_DAYS', 30)
    }
};

DATABASE_URL=postgres://ortha:ortha@localhost:5432/ortha
OUTBOX_RETENTION_DAYS=30

Variable	Default	What it does
`DATABASE_URL`	required	Postgres connection string. The app refuses to load without it
`OUTBOX_RETENTION_DAYS`	`30`	Days a delivered outbox row is kept. `0` keeps them forever

DatabasePluginConfig has three more fields with no environment variable — pass them in plugins.ts if you need them:

Field	Default	Notes
`poolMax`	`10`	Leave headroom: the outbox drain holds one client per batch
`connectionTimeoutMillis`	`10000`	Wait for a free client. `pg` alone waits forever
`statementTimeoutMillis`	off	Postgres `statement_timeout` per session

DatabasePlugin({
    connectionString: config.database.url,
    outboxRetentionDays: config.database.outboxRetentionDays,
    poolMax: 20,
    statementTimeoutMillis: 30_000
});

Apps generated before 0.6.0

They pass only connectionString and still prune at 30 days. Add the outboxRetentionDays line (and the database.outboxRetentionDays field in orthacms.config.ts) before OUTBOX_RETENTION_DAYS has any effect.

Using it from a plugin

The pool:

import { Injectable } from '@nestjs/common';
import { InjectDatabase, type Database } from '@orthacms/database';

@Injectable()
export class OrderRepository {
    constructor(@InjectDatabase() private readonly db: Database) {}
}

A write plus its event, atomically:

import { Injectable } from '@nestjs/common';
import { createDomainEvent, OutboxWriter, UnitOfWork } from '@orthacms/database';

@Injectable()
export class PlaceOrder {
    constructor(
        private readonly uow: UnitOfWork,
        private readonly outbox: OutboxWriter,
        private readonly orders: OrderRepository
    ) {}

    execute(input: NewOrder) {
        return this.uow.run(async () => {
            const order = await this.orders.insert(input); // joins the transaction via uow.current()
            await this.outbox.append([
                createDomainEvent({
                    kind: 'order.placed',
                    aggregateType: 'order',
                    aggregateId: order.id,
                    payload: { total: order.total }
                })
            ]);
            return order;
        });
    }
}

uow.run nests: an inner run joins the outer transaction.
Repositories read uow.current() to get the ambient transaction.
After the outermost commit the outbox is drained; a 5-second poll is the backstop.

A subscriber:

import { Injectable, type OnApplicationBootstrap } from '@nestjs/common';
import {
    OutboxDispatcher,
    type DomainEvent,
    type DomainEventSubscriber
} from '@orthacms/database';

@Injectable()
export class OrderPlacedMailer implements DomainEventSubscriber, OnApplicationBootstrap {
    readonly kinds = ['order.placed'] as const; // or '*'

    constructor(private readonly dispatcher: OutboxDispatcher) {}

    onApplicationBootstrap(): void {
        this.dispatcher.register(this);
    }

    async handle(event: DomainEvent): Promise<void> {
        // at-least-once delivery: dedupe on event.eventId
    }
}

A new kind is not audited automatically

The activity log only records kinds it has a mapper for. See the check on that page.

Delivery and retry

Stage	Behaviour
Pending	Delivered after commit; polled every 5 s as a backstop
Failing	Retried with backoff: 1 s, doubling, capped at 5 min
Parked	After 15 failed attempts (~30 min). No longer claimed
Delivered	Pruned after `OUTBOX_RETENTION_DAYS`, at most once an hour

Delivery is at least once — subscribers must be idempotent.
Pending and parked rows are never pruned by age.
Parked rows: GET /api/activity/dead-letters; put one back with POST /api/activity/dead-letters/:id/retry — see activity.
On shutdown the in-flight outbox batch finishes, then the pool closes.

See domain events for more.

Migrations

It owns one table, outbox_events, tracked in __drizzle_migrations_database (0003_outbox_retention_index adds the partial index the retention sweep uses).

npx orthacms migrate   # applies every plugin's migrations, in plugin-list order

See the database and migrations and the CLI.