{"id":"ops/outbox-projector","relativePath":"ops/outbox-projector.md","title":"Transactional Outbox Projector (Postgres -> Solr/GraphDB)","markdown":"# Transactional Outbox Projector (Postgres -> Solr/GraphDB)\n\nThis runbook covers the transactional outbox gate for record write paths.\n\n## What is implemented\n\n- Record writes in Postgres mode (`/api/records` and all import routes using `createRecordsService.add`) now:\n - upsert `storage_documents.records` and\n - enqueue `outbox_events`\n - in the same database transaction.\n- Outbox events use replay-safe idempotency keys:\n - `sha256(\"record.upsert|recordId|sourceHash\")`\n- Projector worker claims pending events with `FOR UPDATE SKIP LOCKED`, retries with backoff, and dead-letters after max attempts.\n\n## Commands\n\n```bash\npnpm projection:readiness\npnpm projection:readiness:check\npnpm outbox:projector:once\npnpm outbox:projector\npnpm outbox:status\npnpm outbox:dlq:list\npnpm outbox:replay:dlq\npnpm outbox:requeue:stale\npnpm outbox:alert:check\n```\n\n`pnpm outbox:projector:once` runs a single batch. \n`pnpm outbox:projector` drains multiple batches until empty (or batch cap reached).\n\n## Scale-readiness decision\n\nKeep Solr/GraphDB projection off while the in-app discovery path is sufficient.\nBefore enabling either target, run:\n\n```bash\npnpm projection:readiness -- --active-discovery-workflows= --active-graph-workflows= --search-p95-ms= --graph-p95-ms=\n```\n\nThe command reads the current `records.json` count by default, writes\n`artifacts/launch/projection-readiness-latest.json`, and classifies the path as:\n\n- `portable`: keep `OUTBOX_PROJECT_TO_SOLR=0` and `OUTBOX_PROJECT_TO_GRAPHDB=0`.\n- `watch`: prepare Solr/GraphDB credentials and a backfill window, but keep\n target flags off.\n- `enable`: enable the recommended target only after `CRON_SECRET` and\n `METAMUSEUM_OUTBOX_CRON_ENABLED=1` are deployed, then verify `/workers`.\n- `enabled`: at least one target flag is already on and the scheduled drain is\n ready; continue monitoring `/workers` and `pnpm outbox:alert:check`.\n\nDefault enable thresholds:\n\n- Solr: `50,000` records, search p95 >= `1000ms`, or at least `3` active\n discovery workflows over `10,000` or more records.\n- GraphDB: graph traversal p95 >= `1500ms`, or at least `2` active graph\n workflows over `10,000` or more records.\n\nUse `pnpm projection:readiness:check` in closeout or CI; it exits non-zero when\nthe scale path should be enabled but the target/scheduler surface is not ready.\n\nProduction scheduler:\n\n- Vercel Cron calls `GET /api/cron/outbox` every 5 minutes.\n- The route requires `Authorization: Bearer $CRON_SECRET`.\n- It is safe to deploy while disabled; it drains only when\n `METAMUSEUM_OUTBOX_CRON_ENABLED=1`.\n- Each invocation runs one bounded projector cycle. Tune batch size with\n `METAMUSEUM_OUTBOX_CRON_LIMIT`.\n- It will not claim outbox events until at least one projection target is\n explicitly enabled with `OUTBOX_PROJECT_TO_SOLR=1` or\n `OUTBOX_PROJECT_TO_GRAPHDB=1`.\n- `/workers` shows projection lag, backlog, next cron wakeup, effective next\n drain time, and disabled projection target mode.\n\nOperator commands:\n\n- `pnpm outbox:status` queue health counters + aging\n- `pnpm outbox:dlq:list` inspect dead-letter queue\n- `pnpm outbox:replay:dlq` replay dead-letter events back to pending\n- `pnpm outbox:requeue:stale` requeue stuck `processing` events\n- `pnpm outbox:alert:check` evaluate alert policy and optionally dispatch webhook\n\n## Required env\n\n- `DATABASE_URL` (Postgres)\n- `METAMUSEUM_STORAGE_MODE=postgres` for fully transactional write+enqueue\n- Optional projector targets:\n - `OUTBOX_PROJECT_TO_SOLR=1` (default off; skipped when unset)\n - `OUTBOX_PROJECT_TO_GRAPHDB=1` (default off; skipped when unset)\n - `SOLR_BASE_URL`, `SOLR_CORE`, `SOLR_USERNAME`, `SOLR_PASSWORD`\n - `GRAPHDB_BASE_URL`, `GRAPHDB_REPOSITORY_ID`, `GRAPHDB_USERNAME`, `GRAPHDB_PASSWORD`\n\nFailure policy + alert env:\n\n- `OUTBOX_MAX_ATTEMPTS` (default `8`)\n- `OUTBOX_REPLAY_BATCH_LIMIT` (default `100`)\n- `OUTBOX_DLQ_ALERT_THRESHOLD` (default `1`)\n- `OUTBOX_RETRY_ALERT_THRESHOLD` (default `50`)\n- `OUTBOX_PENDING_ALERT_THRESHOLD` (default `200`)\n- `OUTBOX_PENDING_MAX_AGE_MINUTES` (default `15`)\n- `OUTBOX_STALLED_PROCESSING_MAX_AGE_MINUTES` (default `10`)\n- `OUTBOX_ALERT_WEBHOOK_URL` (optional webhook target)\n- `OUTBOX_ALERT_FAIL_ON_ALERT=1` to fail CI/cron checks on threshold breach\n\n## Table schema\n\n`ops/postgres/init/02-outbox.sql` creates:\n\n- `outbox_events`\n- status/availability index\n- aggregate index\n\nStatuses:\n\n- `pending`\n- `processing`\n- `retry`\n- `processed`\n- `dead_letter`\n\n## Failure handling policy\n\n- Retry budget:\n - attempts increment on claim (`pending/retry -> processing`)\n - failures backoff exponentially (`2^attempt` seconds, capped at 300s)\n - dead-letter transition at `attempt_count >= OUTBOX_MAX_ATTEMPTS`\n- Dead-letter queue:\n - inspect with `pnpm outbox:dlq:list`\n - replay with `pnpm outbox:replay:dlq`\n- Stalled worker recovery:\n - `pnpm outbox:requeue:stale` unlocks long-running `processing` rows back to `retry`\n- Alerting:\n - `pnpm outbox:alert:check` emits threshold alerts and can POST a webhook payload\n","sections":[{"level":2,"heading":"What is implemented","anchor":"what-is-implemented"},{"level":2,"heading":"Commands","anchor":"commands"},{"level":2,"heading":"Scale-readiness decision","anchor":"scale-readiness-decision"},{"level":2,"heading":"Required env","anchor":"required-env"},{"level":2,"heading":"Table schema","anchor":"table-schema"},{"level":2,"heading":"Failure handling policy","anchor":"failure-handling-policy"}],"html":"

Transactional Outbox Projector (Postgres -> Solr/GraphDB)

\n

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

\n

What is implemented

\n
  • 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.
\n

Commands

\n
\npnpm projection:readiness\npnpm projection:readiness:check\npnpm outbox:projector:once\npnpm outbox:projector\npnpm outbox:status\npnpm outbox:dlq:list\npnpm outbox:replay:dlq\npnpm outbox:requeue:stale\npnpm outbox:alert:check\n
\n

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

\n

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

\n

Scale-readiness decision

\n

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

\n

Before enabling either target, run:

\n
\npnpm projection:readiness -- --active-discovery-workflows=<count> --active-graph-workflows=<count> --search-p95-ms=<ms> --graph-p95-ms=<ms>\n
\n

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

\n

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

\n

target flags off.

\n

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

\n

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

\n
  • `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
\n

Default enable thresholds:

\n

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

\n

workflows over `10,000` or more records.

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

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

\n

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

\n

Production scheduler:

\n

`METAMUSEUM_OUTBOX_CRON_ENABLED=1`.

\n

`METAMUSEUM_OUTBOX_CRON_LIMIT`.

\n

explicitly enabled with `OUTBOX_PROJECT_TO_SOLR=1` or

\n

`OUTBOX_PROJECT_TO_GRAPHDB=1`.

\n

drain time, and disabled projection target mode.

\n
  • 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
\n

Operator commands:

\n
  • `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
\n

Required env

\n
  • `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`
\n

Failure policy + alert env:

\n
  • `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
\n

Table schema

\n

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

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

Statuses:

\n
  • `pending`
  • `processing`
  • `retry`
  • `processed`
  • `dead_letter`
\n

Failure handling policy

\n
  • 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
","updatedAt":"2018-10-20T01:46:40.000Z","checksum":"e6c3325982584ba9bc0245435c56b4a5050a1bbb6fb80390af99dd2e82df624c","checksumPrefix":"e6c332598258","anchorCount":6,"lineCount":135,"rawUrl":"/api/docs/content?path=ops%2Foutbox-projector.md","htmlUrl":"/docs?doc=ops%2Foutbox-projector.md","apiUrl":"/api/docs/content?path=ops%2Foutbox-projector.md"}