Archetype 9 — Future Constraint + Claimable Run #

What this archetype is #

Work is defined now but must execute later. The core states are scheduled intent, runnable work, and execution.

Examples: reminders, delayed email, retry-after task.

We will use scheduled reminder jobs as the running example.

Layer 1: Entities and Postgres table design #

ScheduleState
RunnableJobState
ExecutionState

create table scheduled_jobs (
  job_id uuid primary key,
  owner_id bigint not null,
  run_at timestamptz not null,
  status text not null default 'SCHEDULED',
  payload jsonb not null,
  version bigint not null default 1
);

create index scheduled_jobs_due_idx
on scheduled_jobs (status, run_at);

create table job_executions (
  execution_id uuid primary key,
  job_id uuid not null references scheduled_jobs(job_id),
  status text not null,
  claimed_by text,
  attempt_number int not null default 1,
  claim_expires_at timestamptz,
  result jsonb,
  version bigint not null default 1
);

Layer 2: Write path mechanics #

Schedule job #

insert into scheduled_jobs (
  job_id, owner_id, run_at, status, payload
) values ($1, $2, $3, 'SCHEDULED', $4);

Materialize runnable jobs #

update scheduled_jobs
set status = 'RUNNABLE',
    version = version + 1
where status = 'SCHEDULED'
  and run_at <= now();

Claim execution #

update scheduled_jobs
set status = 'IN_PROGRESS',
    version = version + 1
where job_id = $1
  and status = 'RUNNABLE'
returning *;

Or claim from a runnable queue populated from the DB.

Layer 3: Fault tolerance #

• due item materialized twice
• due item never materialized
• job claimed twice
• run executed but result not recorded

Controls:

• status transitions guarded in SQL
• unique execution rows or idempotent downstream processing
• reconciliation sweep over overdue runnable jobs

Layer 4: Scale #

Default hotspots:

• due-time burstiness
• due bucket/index hotspot
• queue backlog
• worker burst pressure

Common mitigations:

• time bucketing / partitioning on run_at
• delayed queue in front of workers
• timing wheel or scheduler service when DB polling becomes hot