Background jobs that wait for the transaction

A booking confirmation, a reminder text, a webhook to another system: most things a clinic system does for a patient happen after the request that caused them. Sovica never sends them inline. Every side effect becomes a background job, written in the same database transaction as the change behind it.

Jobs live in Postgres

We run jobs on pg-boss, which keeps its queue in tables in our own Postgres database, in a schema our migrations create and a separate role owns. There is no second system to keep in step with the database: a job is just another row.

Enqueue inside the transaction

Modules call queue.enqueue(tx, name, data) with the transaction they are already in, and the queue runs pg-boss’s insert on that same transaction:

async enqueue(tx, name, data, options = {}) {
  if (!this.definitions.has(name)) throw new Error(`Unknown job ${name}`);
  await this.boss.send(name, data, {
    ...options,
    db: { executeSql: (text, values) => runInTx(tx, text, values) },
  });
}

The job commits or rolls back with the change.

If a booking fails to save, its confirmation never existed. If it saves, the job is already there for a worker to pick up. There is no moment where one happened without the other.

Ids, never patient details

Job data is ids and encrypted tokens only. The worker loads what it needs inside its own clinic transaction, under the same row level security as any request, so the queue tables never hold a name, a phone number or a note. Queue errors are logged with their name, message and stack only, because a database error’s other fields can quote row values.

Retries and timetables

• A failed job retries up to 8 times, 15 seconds apart at first and backing off to at most an hour.
• Every failure is reported, so a stuck job is visible rather than silent.
• Jobs on a timetable are scheduled by workers only, with one schedule row per job, so several API instances do not multiply the runs.
• Every job is defined when its module starts. Defining one after the queue has started is an error.

The rule is short enough to remember: change the data, queue the effect, commit once.