The table

spool keeps its jobs in one table, spool_jobs (Spool.table), made by its own migrations. They are plain SQL, installed with the package in share/spool/migrations, and applied beside your own as a history of their own, before your application starts:

rowtype-migrate up --source spool    # spool's migrations, then yours

--source needs rowtype 0.5.0 or later; rowtype’s migrations say the rest of the command. An upgrade of spool that changes the table ships a new migration, and the same command applies it. With another migration tool, copy the files from $(opam var spool:share)/migrations into its folder: they are plain Postgres.

Checked before a runner starts

A runner checks the table before it starts, and refuses a database where it is missing or behind, naming the command to run: Runner.create answers Unmigrated, with each problem it found.

Spool.statements is every statement the package runs, for Rowtype_postgres.verify beside your own. A test that runs it finds a migration that broke one before a job does:

(* A test that every statement the application runs is what its database
   says, spool's among them, so a migration that renamed a column fails a
   test and not a job.

     dune exec examples/verify.exe *)

module Pg = Rowtype_postgres

let database = "postgres://postgres:postgres@localhost:5432/postgres"

let orders =
  Rowtype.list ~params:Rowtype.unit
    ~row:Rowtype.(t2 int text)
    "select id, email from orders order by id"

let check conn =
  match Pg.verify conn (Spool.statements @ [ Rowtype.Any orders ]) with
  | Ok () -> print_endline "Every statement agrees with the database."
  | Error (`Disagreements problems) -> List.iter prerr_endline problems
  | Error (#Rowtype.error as e) -> prerr_endline (Rowtype.error_to_string e)

let () =
  Eio_main.run @@ fun env ->
  Eio.Switch.run @@ fun sw ->
  match Pg.Conninfo.of_string database with
  | Error e -> prerr_endline e
  | Ok conninfo -> (
      match
        Pg.connect ~sw ~net:(Eio.Stdenv.net env)
          ~mono_clock:(Eio.Stdenv.mono_clock env)
          conninfo
      with
      | Error e -> prerr_endline (Rowtype.error_to_string e)
      | Ok conn ->
          check conn;
          Pg.close conn)

States

Five states, and the rest derived: scheduled is available with a later scheduled_at, retrying is available with an attempt made.

State	What it means
`available`	waiting, or scheduled, or to be retried
`running`	claimed by a runner, under its lease
`completed`	its handler answered `Ok`
`discarded`	failed on its last attempt, expired, or given up
`cancelled`	cancelled by an operator

Columns

Column	What it holds
`queue`	The queue a runner claims it from, with that queue’s concurrency.
`kind`	What runs it: a kind’s name, its identity, so a changed payload shape is a new kind.
`payload`	What the job is given: an object, as its kind’s description wrote it.
`priority`	0 to 9, 0 first: before the instant and the order of enqueueing.
`state`	One of the five above.
`attempt`	Attempts begun: raised by the claim, so a job that kills its process uses one.
`max_attempts`	Its kind’s attempts, when it was written.
`scheduled_at`	When it may run next.
`expires_at`	After when it is not worth running, and is discarded unrun.
`unique_key`	Repeated asks of one kind with one key, folded into the job waiting.
`runner`	The runner holding its lease, while it runs.
`lease_ends_at`	Until when its runner holds it, renewed while it runs; a lapsed one is rescued.
`inserted_at`, `attempted_at`, `finished_at`	When it was written, last claimed, and finished.
`errors`	Each failed attempt’s attempt, instant and reason, oldest first.

Every instant in it is the now a caller gave: no statement reads the database’s clock.

The migration that makes it

-- create_spool_jobs: the table of jobs, spool's own, made by its first migration.

create table spool_jobs (
  id            bigint generated always as identity primary key,
  queue         text not null,
  kind          text not null,
  payload       jsonb not null,
  priority      smallint not null,
  state         text not null,
  attempt       integer not null,
  max_attempts  integer not null,
  scheduled_at  timestamptz not null,
  expires_at    timestamptz,
  unique_key    text,
  runner        text,
  lease_ends_at   timestamptz,
  inserted_at   timestamptz not null,
  attempted_at  timestamptz,
  finished_at   timestamptz,
  errors        jsonb not null,
  constraint spool_jobs_state check (state in
    ('available', 'running', 'completed', 'discarded', 'cancelled')),
  constraint spool_jobs_priority check (priority between 0 and 9),
  constraint spool_jobs_attempts check (max_attempts >= 1 and attempt >= 0),
  constraint spool_jobs_running_leased check
    ((state = 'running') = (runner is not null and lease_ends_at is not null)),
  constraint spool_jobs_finished check
    ((state in ('completed', 'discarded', 'cancelled')) = (finished_at is not null))
);

create index spool_jobs_due on spool_jobs (queue, priority, scheduled_at, id)
  where state = 'available';
create index spool_jobs_leased on spool_jobs (lease_ends_at) where state = 'running';
create unique index spool_jobs_waiting_key on spool_jobs (kind, unique_key)
  where state = 'available' and unique_key is not null;
create index spool_jobs_finished_at on spool_jobs (finished_at)
  where state in ('completed', 'discarded', 'cancelled');

comment on table spool_jobs is 'Jobs: work owed, written in the transaction that made it owed, claimed by a runner under a lease, and finished once.';
comment on column spool_jobs.queue is 'The queue a runner claims it from, with that queue''s concurrency.';
comment on column spool_jobs.kind is 'What runs it: a kind''s name, its identity, so a changed payload shape is a new kind.';
comment on column spool_jobs.payload is 'What the job is given: an object, as its kind''s description wrote it.';
comment on column spool_jobs.priority is '0 to 9, 0 first: before the instant and the order of enqueueing.';
comment on column spool_jobs.state is 'available, running, completed, discarded or cancelled; scheduled and retrying are available with a later instant or an attempt made.';
comment on column spool_jobs.attempt is 'Attempts begun: raised by the claim, so a job that kills its process uses one.';
comment on column spool_jobs.scheduled_at is 'When it may run next.';
comment on column spool_jobs.expires_at is 'After when it is not worth running, and is discarded unrun.';
comment on column spool_jobs.unique_key is 'Repeated asks of one kind with one key, folded into the job waiting.';
comment on column spool_jobs.runner is 'The runner holding its lease, while it runs.';
comment on column spool_jobs.lease_ends_at is 'Until when its runner holds it, renewed while it runs; a lapsed one is rescued.';
comment on column spool_jobs.errors is 'Each failed attempt''s attempt, instant and reason, oldest first.';