Archetype 4 — Workflow / Lifecycle State #

What this archetype is #

A workflow row moves through a state machine. Correctness comes from valid transitions and reliable side effects.

Examples: onboarding, approval, order lifecycle.

We will use loan approval workflow as the running example.

Layer 1: Entities and Postgres table design #

WorkflowState
  -> source truth for one workflow instance

create table loan_workflows (
  workflow_id uuid primary key,
  applicant_id bigint not null,
  state text not null,
  payload jsonb not null,
  version bigint not null default 1,
  updated_at timestamptz not null default now()
);

create table workflow_outbox (
  outbox_id bigserial primary key,
  workflow_id uuid not null,
  effect_type text not null,
  effect_payload jsonb not null,
  created_at timestamptz not null default now(),
  delivered_at timestamptz
);

create index workflow_state_updated_idx
on loan_workflows (state, updated_at desc);

Layer 2: Write path mechanics #

Transition with outbox #

begin;

update loan_workflows
set state = 'APPROVED',
    version = version + 1,
    updated_at = now()
where workflow_id = $1
  and version = $2
  and state = 'PENDING_REVIEW';

insert into workflow_outbox (
  workflow_id, effect_type, effect_payload
) values (
  $1, 'SEND_APPROVAL_EMAIL', $3
);

commit;

This is the generic Postgres equivalent of state transition + transactional outbox.

Read workflow #

select *
from loan_workflows
where workflow_id = $1;

Layer 3: Fault tolerance #

• retry of same transition after commit
• invalid transition from stale state
• competing transitions
• crash after state update but before external side effect -> solved with outbox relay
• stale status read

Layer 4: Scale #

Default hotspots:

• hot workflow transition rows
• expensive status polling
• outbox relay backlog

Common mitigations:

• state-indexed queries
• polling reduction via push notifications
• separate outbox workers and batching