0019. Run background jobs on pg-boss, with its schema in GuestCraft's migrations

• Status: accepted
• Date: 2026-10-26
• Deciders: the project owner (the choice of pg-boss), the maintainers (how it is run)

Context

Chapter 7 of the analysis chose Graphile Worker for jobs, in both runtimes, and listed pg-boss as the alternative. It also listed what Graphile’s core lacks for GuestCraft. runOnce() does not recover the locks of a killed process, and it does not start the crontab, which only the resident worker runs. So GuestCraft would have built its own scheduler table, tick and recovery around it. Graphile Worker’s core is MIT and free. Recovering the jobs of crashed workers is a feature of Worker Pro, which is paid.

The owner decided that nothing in GuestCraft may cost money, and that jobs run on pg-boss instead of Graphile Worker. pg-boss is MIT, free, and has no paid tier. It runs on PostgreSQL alone. It has expiration built in: a job still active after its timeout is failed and retried. It also has retries with exponential backoff, dead letter queues, cron schedules evaluated in a time zone, delayed jobs, and the creation of jobs inside an existing transaction.

This ADR corrects the analysis. Chapters 7, 8 and 12 name Graphile Worker; this decision replaces it, and the chapters are not edited. It also replaces the parts of ADRs 0005, 0009, 0010 and 0011 that name Graphile.

What the jobs must do, from the analysis and ADRs 0008 to 0011:

• Transactional enqueue. A job is enqueued in the same transaction as the data that caused it (ADR 0009).
• Two runtimes. A resident worker in Docker, and a drain with a time budget on Vercel, called by a cron route.
• Scheduling. Recurring jobs at the local time of a property, and delayed jobs.
• Recovery, retries and dead letters. The jobs of crashed workers are recovered. Failures are retried with backoff, then kept in a dead letter destination.
• A typed catalog. Payloads carry identifiers, never personal data (chapters 4 and 18). The handler sets the tenant context through the unit of work.
• No DDL at runtime (ADR 0010, ADR 0011).
• Poolers. It works through PgBouncer in transaction mode, the pooled URLs of Neon and Supabase.

Decision

pg-boss, pinned and contained

• pg-boss 12.38.0 is pinned in the catalog of pnpm-workspace.yaml. It is MIT; its dependencies are MIT, and Apache-2.0 for @opentelemetry/api and temporal-spec.
• @guestcraft/jobs is the only package that imports it at runtime. db has it as a devDependency only, for the generator of its migrations and for a test.
• Nothing in jobs reads the environment: the composition root passes the connection, the catalog, the handlers and the unit of work.
• Nothing connects at import: a queue connects at its first job, a runtime when it starts or drains (ADR 0010).

Its schema is a GuestCraft migration

pg-boss runs with its own migrations off (migrate: false). It checks at startup that its schema has the version it expects, and refuses to start otherwise. A runtime then stops with “run the migration step”; it never migrates.

• Where. The schema guestcraft_jobs is installed by packages/db/migrations/0003_scheduler_install_v46.sql, in the single journal of ADR 0010. Graphile’s separate migration step disappears.

• How it is generated. pnpm --filter @guestcraft/db scheduler:migration runs packages/db/scripts/scheduler-migration.ts, which takes the SQL from pg-boss itself:

  • getConstructionPlans for the first install;
  • getMigrationPlans from the installed version, after an upgrade of pg-boss, written as the next numbered migration (NNNN_scheduler_upgrade_vXX.sql).

  pg-boss wraps its plan in BEGIN and COMMIT. The runner applies each file in its own transaction, so the script leaves the wrapper out. Then it appends a revoke of the new functions from PUBLIC (ADR 0011). The files are not edited by hand.

• How it is checked.

  • db/scheduler exports SCHEDULER_SCHEMA_VERSION, the version the migrations install.
  • A unit test fails when the pinned pg-boss expects another version, or when the last scheduler migration is not that version.
  • Integration tests start pg-boss on the migrated database with its migrations off, and ask pg-boss’s own detectSchemaDrift(), which must find no drift.
  • Another test changes the version and checks that every runtime refuses to start, and that nothing was migrated.

No DDL at runtime

pg-boss is configured so that it never changes the schema:

• its migrations and index rebuilds are off (migrate: false, reindex: false);
• queues never get a table of their own (no partition: true): create_queue then only inserts a row;
• queue statistics and their daily partitions are off (persistQueueStats: false);
• the background index builds of an upgrade (pg-boss’s BAM) run only with migrate, so never.

The database enforces it too: the runtime roles own nothing and have no CREATE on the schema. A test checks that both roles are refused CREATE, ALTER and DROP there.

Grants

SCHEDULER_TABLES and SCHEDULER_FUNCTIONS in packages/db/src/inventory.ts list what each runtime role needs. applyGrants applies them after every migration, as for the other schemas, after revoking everything from PUBLIC and from the runtime roles.

Table	App role (enqueues)	Worker role (runs jobs)
version	select	select, update (the claims of the periodic passes)
queue	select	select, insert, update (registration)
job (partitioned)	none	select, insert, update, delete
job_common (its partition)	insert, and select of id only (RETURNING id)	select, insert, update, delete
schedule	none	select, insert, update, delete
instance, job_dependency	none	select, update, delete: the cleanup reads and trims them, though GuestCraft fills neither
bam, queue_stats, subscription, warning	none	none

Both roles may execute job_now(); only the worker may execute create_queue. No function is SECURITY DEFINER. The catalog test compares these privileges with the real catalog, table by table and column by column. A behavioural test checks that the app role cannot read a payload or a result, nor change, delete or release a job, a queue or a schedule.

Enqueueing joins the unit of work

• The unit of work gives every use case a scheduler capability: an opaque handle on its transaction, with no methods.
• Only db/scheduler, which only jobs and the composition root may import, turns it into a SQL executor bound to the connection of the transaction.
• enqueue(capabilities, job) checks the payload against the catalog. Then it calls pg-boss’s send(name, payload, { db }) with that executor, so the INSERT runs inside the transaction. The commit makes the job visible to workers; a rollback removes it with the data.
• The handle refuses statements once its transaction has ended.
• pg-boss reads the queue settings through its own pool, and caches them. It never writes outside the transaction.

The catalog

• Jobs. defineJob declares a name (lower case and dotted, like channex.push_ari), a scope, payload fields, a timeout and a retry policy. defineCatalog refuses two jobs with one name.
• Payloads carry identifiers. Fields are built with field only: TypeIDs with their prefix, values of a closed set, integers and calendar dates. There is no text field. A field built otherwise is refused, and unknown keys are refused when the payload is checked.
• Tenant context. A workspace job carries workspaceId. Its handler reaches data only through context.run(work): a unit of work scoped to that workspace, which sets app.workspace_ids for every query. A system job’s unit of work has no tenant, so row-level security shows it nothing.
• Checked twice. Payloads are checked at enqueue, and again before the handler runs. A stored payload that no longer matches its job goes to the dead letter queue without running.

Outcomes, retries and the dead letter queue

Delivery is at least once, so handlers must be idempotent. The outcome of an attempt follows ADR 0008:

• Retried: an InfrastructureError that is retryable, or whose outcome is unknown, since repeating an idempotent handler is safe. The delay grows exponentially, with jitter: retryDelay × 2^n / 2 × (1 + random) seconds for the n-th retry, up to maxDelaySeconds. By default there are 5 retries, from 10 seconds up to 10 minutes. After the last retry the job goes to the dead letter queue.
• Sent to the dead letter queue at once: anything else, a bug or a failure that will not heal, because a retry would fail the same way.

The dead letter queue is one queue, dead_letter, where jobs stay for 30 days. pg-boss records for each dead job its source queue and job, its retries and its last output. The output uses stable safe failure codes for unexpected and infrastructure failures. Arbitrary error names, messages and causes never enter job output or callback logs (ADR 0008). deadLetters() lists the dead jobs, and redrive() sends some or all of them back for a new series of attempts. pg-boss’s retry delay belongs to the queue, so the retryAfterMs of an error is not used; a handler that must wait for a rate limit enqueues a delayed job instead.

Recovery

• Timeouts. Every job has a timeout, which becomes its expiration in pg-boss. The handler’s signal is aborted when it passes.
• Recovery pass. A pass fails the jobs still active after their timeout, as when their worker crashed. Each such failure counts as an attempt and is retried with backoff.
• When it runs. The resident worker runs the pass every recoveryIntervalSeconds, 60 by default. A claim in the database lets one instance per interval do it. A drain runs it once, at its start.
• The bound. A crashed worker’s job is retried at most its timeout, plus the interval, plus the backoff, after it started. pg-boss does this itself; GuestCraft needs no heartbeat table of its own, unlike the plan for Graphile in the analysis.
• Stopping. stop() takes no new job, and lets the running ones finish within its timeout. A job still running then is failed, to be retried.

A test kills a worker process with SIGKILL in the middle of a job, and checks that another worker retries the job once its timeout has passed.

Two runtimes, one code path per job

Both runtimes use the worker role, the same handlers and the same attempt function.

• The resident worker. start() and stop(). It polls every queue (2 seconds by default), and runs the recovery, schedule and cleanup passes on timers.

• The drain. drain({ budgetMs }) is for serverless runtimes. It returns within its budget:

  • Only jobs whose whole timeout fits in the budget, plus one second, may run. A drain shorter than every timeout is refused.
  • It registers the catalog, runs the recovery and cleanup pass once, and runs the schedule pass.
  • It takes jobs until the deadline minus the longest timeout and one second, lets the running ones finish, and stops.
  • It returns early when every worker found nothing to do.

  The composition root will call it from a cron route and, as the analysis plans, after writes.

pg-boss waits out its polling interval after each job, so a worker with a backlog would run one job per interval. The runtime wakes the queue’s workers when a job ends; they poll again only when the queue is empty.

Scheduling

• Recurring jobs are cron expressions read in an IANA time zone, such as “every day at 03:00 in Europe/Rome”, so they follow the clocks of a property across daylight saving time.
• Registration. The code declares them (schedules), keyed by job and key, such as one per workspace. Registration writes only what changed, and removes the stored schedules of the catalog’s jobs that the code no longer declares.
• Missed occurrences. An occurrence missed while nothing ran is sent once (missed: "once"), as on a sandbox whose drain runs once a day.
• When they fire. The resident worker evaluates the schedules every scheduleIntervalSeconds, 30 by default, with a claim like the recovery pass; a drain evaluates them once.
• Delayed jobs use startAfterSeconds at enqueue.

pg-boss’s schedules replace the scheduler table and tick that the analysis planned beside Graphile.

Queues exist before the first job

pg-boss refuses a job for a queue that does not exist. registerJobs creates the queues of the catalog and the dead letter queue, aligns their settings with the catalog, and stores the schedules. The migration step will call it with the owner’s connection, after the migrations and the grants, so that the API can enqueue before any worker has started. Workers and drains also register when they start; it is idempotent. A queue whose job left the catalog is kept, with its jobs.

Poolers

• No session state. LISTEN/NOTIFY is off and workers poll, so nothing depends on a connection staying with one client. pg-boss sends each of its transactions as one query, and its advisory locks are transaction-scoped.
• Poolers work. The app and the worker may use a transaction pooler. The integration tests enqueue, run the resident worker and drain through PgBouncer in transaction mode.
• One direct connection. Only the migration step needs one, because the runner holds a session advisory lock (ADR 0011).
• Correction to chapter 12. The analysis gave the worker a session connection for LISTEN; that is no longer needed.

Consequences

• The other ADRs. In ADR 0009, the enqueue is pg-boss’s send with the transaction’s executor, instead of graphile_worker.add_job. In ADR 0010, the scheduler’s schema is a GuestCraft migration, and no second migration step runs. In ADR 0005, the scheduler’s tables are inventoried with their grants, instead of “the Graphile tables listed explicitly”.
• Upgrading pg-boss is a reviewed change. The steps:
  1. Change the catalog.
  2. Run scheduler:migration, and set SCHEDULER_SCHEMA_VERSION.
  3. Review the generated SQL.
  4. Check that the inventory still lists what the roles need: the integration tests fail on a missing privilege, and the catalog test on an extra one.
• A single maintainer. pg-boss has one main maintainer, as the analysis notes. The pinned version, the schema in our migrations, the drift test and a single importing package keep a later replacement to one package and one migration.
• At least once. Handlers must be idempotent, and the guarantee is documented on JobHandler.
• Latency. Workers poll: an idle queue answers within its polling interval, 2 seconds on the resident worker and half a second in a drain.
• Throughput. A job runs one attempt at a time per worker, and concurrency adds workers per job in a process.
• Cost. Nothing to pay: pg-boss is MIT, and runs in the deployer’s own PostgreSQL.

Alternatives considered

• Graphile Worker, as in the analysis. Its core is MIT and free. But the owner chose pg-boss. And recovering the jobs of crashed workers would have meant either Worker Pro, which is paid, or the recovery and scheduler that the analysis planned to write around runOnce().
• pg-boss with its own migrations at startup. The default of pg-boss. It runs DDL with runtime credentials and from several instances at once, which ADRs 0010 and 0011 forbid.
• pg-boss’s schema installed by its CLI in the migration step. A second journal, and SQL that reviewers do not see in the repository.
• DBOS. MIT, with durable workflows and rate limits per partition, but it ties the code to its model of workflows (chapter 7).
• Vercel Workflow. Its included quota runs out within a week on Hobby, and its runs belong to the deployment that started them (chapter 7).
• A queue of our own. pg-boss already has the hard parts, tested by its users: SKIP LOCKED claims, expiration, backoff, schedules, dead letters.

Amendment: 2026-10-30 — normalize queue-generated failure output

pg-boss also writes outputs itself: handler timeouts (an Error with a stack), expired active claims after a crash, heartbeat expiry, forced shutdown, unexpected batch/settlement errors, and redrive conflicts. Retention cleanup deletes rows; cancelling a job changes its state without adding output. Heartbeats and dependency/transaction-handler modes are not enabled by GuestCraft, but the storage rule covers their writes too. A killed process writes nothing; the later recovery pass writes its timeout result.

Migration scheduler_safe_output normalizes both output columns in a BEFORE INSERT/UPDATE trigger on guestcraft_jobs.job, inherited by job_common. This is the shared atomic boundary for worker, drain, supervisor and dead-letter copies; post-processing a handler’s result cannot protect a timeout that pg-boss already settled. Existing retained outputs are scrubbed too. Safe GuestCraft reports retain their fixed code, class and retry flag, with all other fields removed. Native timeout/recovery, shutdown, heartbeat and redrive results become jobs.timed_out, jobs.interrupted, jobs.heartbeat_lost and jobs.redrive_conflict. Unknown output becomes jobs.queue_failure. SQL NULL output stays NULL. Job states, retry limits/counts, payloads and redrive routing remain pg-boss’s responsibility.

The new functions are invokers with a fixed pg_catalog search path, revoked from PUBLIC; the inventory grants the data-free normalizer to the two runtime roles, and grants neither role direct execution of the trigger function. The migration is declared contract because the expand linter does not permit existing-table triggers/backfills. Recovery is roll-forward, and deployment must allow for scrubbing the retained queue rows in that transaction. Creating the trigger and scanning retained rows pause job writes, including app enqueues; the measured pause was 2.9 seconds for 1.05 million rows, with a five-second lock-acquisition bound. Because this is a contract migration, a runtime built before 0029 is refused at startup once 0029 is applied; recovery is roll-forward.

If a pg-boss upgrade recreates or re-partitions guestcraft_jobs.job, the migration must retain the normalize_job_output trigger; the failure-output integration tests prove that protection.

Only deadLetters() consumes these stored reports today; there is no dead-letter HTTP API, CLI or UI in the repository. Handler callbacks use closed codes already and the composed worker logs a generic failure. No public HTTP contract changes.

Amendment: 2026-11-01 — queue-policy migrations, and the forms the linter enforces

A queue’s policy is fixed when pg-boss creates it. pg-boss 12.38 refuses to change it (updateQueue throws “queue policy cannot be changed after creation”), and registerJobs refuses a stored policy that differs from the catalog’s, so that a job made a singleton later never runs under the wrong policy. A generated migration cannot express the change either: pg-boss’s getMigrationPlans brings the schema from one version to the next, and the schema version does not change. Each job row also copies its queue’s policy when it is sent, and the policy’s unique indexes read the row’s copy. So when a job of the catalog changes policy, as webhooks.deliver did when it became a singleton per workspace, the stored queue must change together with the jobs it holds, in the migration step.

That is a third form of migration on guestcraft_jobs, beside the generated scheduler_install_vN/scheduler_upgrade_vN and this ADR’s amendments:

• Named NNNN_scheduler_queue_policy_<name>.sql, written by hand and declared contract.
• Only two statements, in this exact lower-case text (comment lines and runs of whitespace aside): update guestcraft_jobs.queue set policy = '<policy>' where name = '<queue>', once per queue, with a policy registration gives (standard or stately); and delete from guestcraft_jobs.job where name = '<queue>' and state in ('created', 'retry'), only for a queue whose policy it sets. The waiting jobs make way because they carry the old policy and the new policy’s indexes do not see them; the code that sent them sends them again (the delivery dispatcher, every minute). A running job finishes under the policy it was sent with.

We chose this form over a change made by packages/jobs at registration because pg-boss has no supported way to change a policy: its only route would be deleting and recreating the queue, which drops every job, including the active and retained ones, at runtime and from every instance at once.

packages/db/src/migrations/scheduler-forms.ts states the forms, and migrations:lint and the scheduler’s unit test check every migration against them. A migration whose SQL names guestcraft_jobs, or whose name starts with scheduler_, must be one of:

• a generated migration whose file is exactly what scheduler-migration.ts writes for its name (header, pg-boss version, plan, and the closing revoke); the latest one must also equal the plan the pinned pg-boss emits, when it was generated by that version;
• a queue-policy migration with only the statements above;
• a migration decided by an amendment of this ADR, by name and the checksum it was reviewed at: today only scheduler_safe_output.

Anything else fails, so a hand-written change of the job queue’s schema needs a reviewed amendment of this ADR. Migration 0067, scheduler_queue_policy_webhook_delivery, is the first queue-policy migration.