MetaMuseum
Menu
← Documentation home

Canonical Markdown source · Oct 20, 2018

Transactional Outbox Projector (Postgres -> Solr/GraphDB)

ops/outbox-projector.md · 135 lines · SHA-256 e6c332598258

This runbook covers the transactional outbox gate for record write paths.

What is implemented

  • Record writes in Postgres mode (`/api/records` and all import routes using `createRecordsService.add`) now:
  • upsert `storage_documents.records` and
  • enqueue `outbox_events`
  • in the same database transaction.
  • Outbox events use replay-safe idempotency keys:
  • `sha256("record.upsert|recordId|sourceHash")`
  • Projector worker claims pending events with `FOR UPDATE SKIP LOCKED`, retries with backoff, and dead-letters after max attempts.

Commands


pnpm projection:readiness
pnpm projection:readiness:check
pnpm outbox:projector:once
pnpm outbox:projector
pnpm outbox:status
pnpm outbox:dlq:list
pnpm outbox:replay:dlq
pnpm outbox:requeue:stale
pnpm outbox:alert:check

`pnpm outbox:projector:once` runs a single batch.

`pnpm outbox:projector` drains multiple batches until empty (or batch cap reached).

Scale-readiness decision

Keep Solr/GraphDB projection off while the in-app discovery path is sufficient.

Before enabling either target, run:


pnpm projection:readiness -- --active-discovery-workflows=<count> --active-graph-workflows=<count> --search-p95-ms=<ms> --graph-p95-ms=<ms>

The command reads the current `records.json` count by default, writes

`artifacts/launch/projection-readiness-latest.json`, and classifies the path as:

target flags off.

`METAMUSEUM_OUTBOX_CRON_ENABLED=1` are deployed, then verify `/workers`.

ready; continue monitoring `/workers` and `pnpm outbox:alert:check`.

  • `portable`: keep `OUTBOX_PROJECT_TO_SOLR=0` and `OUTBOX_PROJECT_TO_GRAPHDB=0`.
  • `watch`: prepare Solr/GraphDB credentials and a backfill window, but keep
  • `enable`: enable the recommended target only after `CRON_SECRET` and
  • `enabled`: at least one target flag is already on and the scheduled drain is

Default enable thresholds:

discovery workflows over `10,000` or more records.

workflows over `10,000` or more records.

  • Solr: `50,000` records, search p95 >= `1000ms`, or at least `3` active
  • GraphDB: graph traversal p95 >= `1500ms`, or at least `2` active graph

Use `pnpm projection:readiness:check` in closeout or CI; it exits non-zero when

the scale path should be enabled but the target/scheduler surface is not ready.

Production scheduler:

`METAMUSEUM_OUTBOX_CRON_ENABLED=1`.

`METAMUSEUM_OUTBOX_CRON_LIMIT`.

explicitly enabled with `OUTBOX_PROJECT_TO_SOLR=1` or

`OUTBOX_PROJECT_TO_GRAPHDB=1`.

drain time, and disabled projection target mode.

  • Vercel Cron calls `GET /api/cron/outbox` every 5 minutes.
  • The route requires `Authorization: Bearer $CRON_SECRET`.
  • It is safe to deploy while disabled; it drains only when
  • Each invocation runs one bounded projector cycle. Tune batch size with
  • It will not claim outbox events until at least one projection target is
  • `/workers` shows projection lag, backlog, next cron wakeup, effective next

Operator commands:

  • `pnpm outbox:status` queue health counters + aging
  • `pnpm outbox:dlq:list` inspect dead-letter queue
  • `pnpm outbox:replay:dlq` replay dead-letter events back to pending
  • `pnpm outbox:requeue:stale` requeue stuck `processing` events
  • `pnpm outbox:alert:check` evaluate alert policy and optionally dispatch webhook

Required env

  • `DATABASE_URL` (Postgres)
  • `METAMUSEUM_STORAGE_MODE=postgres` for fully transactional write+enqueue
  • Optional projector targets:
  • `OUTBOX_PROJECT_TO_SOLR=1` (default off; skipped when unset)
  • `OUTBOX_PROJECT_TO_GRAPHDB=1` (default off; skipped when unset)
  • `SOLR_BASE_URL`, `SOLR_CORE`, `SOLR_USERNAME`, `SOLR_PASSWORD`
  • `GRAPHDB_BASE_URL`, `GRAPHDB_REPOSITORY_ID`, `GRAPHDB_USERNAME`, `GRAPHDB_PASSWORD`

Failure policy + alert env:

  • `OUTBOX_MAX_ATTEMPTS` (default `8`)
  • `OUTBOX_REPLAY_BATCH_LIMIT` (default `100`)
  • `OUTBOX_DLQ_ALERT_THRESHOLD` (default `1`)
  • `OUTBOX_RETRY_ALERT_THRESHOLD` (default `50`)
  • `OUTBOX_PENDING_ALERT_THRESHOLD` (default `200`)
  • `OUTBOX_PENDING_MAX_AGE_MINUTES` (default `15`)
  • `OUTBOX_STALLED_PROCESSING_MAX_AGE_MINUTES` (default `10`)
  • `OUTBOX_ALERT_WEBHOOK_URL` (optional webhook target)
  • `OUTBOX_ALERT_FAIL_ON_ALERT=1` to fail CI/cron checks on threshold breach

Table schema

`ops/postgres/init/02-outbox.sql` creates:

  • `outbox_events`
  • status/availability index
  • aggregate index

Statuses:

  • `pending`
  • `processing`
  • `retry`
  • `processed`
  • `dead_letter`

Failure handling policy

  • Retry budget:
  • attempts increment on claim (`pending/retry -> processing`)
  • failures backoff exponentially (`2^attempt` seconds, capped at 300s)
  • dead-letter transition at `attempt_count >= OUTBOX_MAX_ATTEMPTS`
  • Dead-letter queue:
  • inspect with `pnpm outbox:dlq:list`
  • replay with `pnpm outbox:replay:dlq`
  • Stalled worker recovery:
  • `pnpm outbox:requeue:stale` unlocks long-running `processing` rows back to `retry`
  • Alerting:
  • `pnpm outbox:alert:check` emits threshold alerts and can POST a webhook payload