Making an automation safe to fail

2. Eleven messages from one event

You enabled a notification workflow three weeks ago. This morning an upstream API slowed down, the trigger retried, and eleven runs started with the same source item. Each run behaved as if it were first. In the Lab version, eleven identical draft notifications appeared for one paper author. In the Company version, one customer received the same reminder repeatedly before anyone noticed.

The run history contains green and red executions, but there is no durable source job ID, no record of whether the external step partly succeeded, and no queue owned by a person. Restarting failed runs could send more messages. Disabling the trigger stops new damage but does not identify which records are safe to replay.

You need failure to have designed states. Claim each source job once, reject empty input before expensive work, retry only a known-safe transient failure with bounded backoff, stop ambiguous side effects, and route terminal failures to one watched queue. An irreversible action must wait for explicit human approval and use the destination's idempotency control when one exists.

3. After this you can

Make repeated delivery of the same source job harmless through an atomic durable claim.
Retry a pre-effect transient failure with bounded backoff and stop loudly after the limit.
Route invalid input, unusable model output, revoked credentials, and ambiguous effects to named human states.
Gate an irreversible action with approval, an idempotency key, and a circuit breaker.

4. Prerequisites

T05-L03 · The pipeline that writes itself, including its bounded batch, per-item validation, and inspectable run states.
An approved n8n test project with Manual Trigger, Edit Fields, Code, If or Switch, Wait, Postgres, and Error Trigger nodes.
A disposable PostgreSQL database or equivalent transactional job store whose schema you may create and delete.
An approved model test connection if you retain the synthesis step; the guaranteed failure tests below can use injected synthetic outputs instead.
An internal failure queue with a named owner and tested notification route that does not contact an author or customer.
Two separately authorised roles: a workflow owner who prepares a candidate and a reviewer who approves an external effect.
About 45 minutes for the independent failure-injection exercise.

Use synthetic jobs, addresses under example.test, and a mock delivery table only. Do not connect an SMTP, messaging, CRM, manuscript, instrument, customer, payment, or production API during this exercise. Store credentials in the approved n8n credential store, never in node parameters, expressions, exported workflow JSON, pinned data, screenshots, or execution notes. If testing a live integration is later authorised, use a provider sandbox and recipients explicitly approved for testing.

5. The idea in one page

A safe automation gives every accepted job a durable identity and terminal state. Branches that stop before a claim still produce a persisted review record or an explicit no-write outcome:

trigger -> validate -> circuit gate -> atomic job claim
             |             |              |
             v             v              +-> already claimed -> duplicate_ignored
       input_review   circuit_open
                                          claimed
                                             |
                                  reversible preparation
                                             |
                              model output validate once
                                  |                 |
                                 valid        one stricter retry
                                  |                 |
                                  |             invalid -> dead_letter
                                  v
                            pending_approval
                                  |
                          named human approves
                                  |
                  idempotent mock effect -> completed
                                  |
                     ambiguous result -> reconcile, never blind retry

In this build, claimed, prepared, pending_approval, approved, completed, dead_letter, and reconcile are persisted job states. duplicate_ignored and circuit_open are terminal route outcomes that create no new job row. input_review is a terminal route backed by a dead-letter row because an empty source ID cannot validly become a job. Revoked credentials have exactly one persisted representation: job state dead_letter plus error class credential_revoked. Do not also invent a credential_revoked job state.

Idempotency means repeating the same intended job produces no additional effect. Use the stable source-system event or record ID plus the workflow purpose, not a timestamp or n8n execution ID. Claim that key with a unique database constraint before model calls or writes. A separate "look, then insert" sequence races when two runs look simultaneously; one atomic insert must decide the winner.

Retries belong only around operations known not to have succeeded. Retry selected transient responses, such as a connection failure before acceptance, 429, or a temporary 5xx, with increasing waits and a hard attempt limit. Do not retry validation errors, revoked credentials, or a timed-out send whose destination may already have accepted the request. The last case is ambiguous and needs reconciliation using the same effect key.

A dead-letter queue is one watched destination for terminal failures. Keep the safe input reference, workflow revision, job key, attempt count, stage, error class, time, and next owner. Do not dump complete messages or secrets into it. An alert must reach a named channel that somebody tests; a database row nobody opens is storage, not routing.

Separate reversible preparation from irreversible action. Drafting, classifying, or writing a candidate row can usually be replaced. Sending, publishing, approving, purchasing, or changing an authoritative record requires enforced approval. A circuit breaker stops claims when recent failure rate or volume exceeds a written threshold. It limits a bad morning; it does not repair jobs already affected.

6. The worked example: deliver at most one approved synthetic notice

Extend the inspectable workflow from T05-L03, but replace its live source with Manual Trigger and its destination with PostgreSQL tables. Build a preparation workflow and a small approved dispatcher. Keeping approval and delivery in a separate dispatcher prevents a failed preparation retry from repeating the effect.

Create the durable control tables

Run this reviewed migration in the disposable database. The primary key is the concurrency control; application checks alone are not enough.

create table automation_jobs (
  workflow_key text not null,
  source_job_id text not null,
  state text not null default 'claimed' check (state in (
    'claimed', 'prepared', 'dead_letter',
    'pending_approval', 'approved', 'completed', 'reconcile'
  )),
  last_error_class text check (last_error_class is null or last_error_class in (
    'invalid_input', 'unusable_output', 'credential_revoked',
    'transient_exhausted', 'unexpected_execution', 'ambiguous_effect'
  )),
  run_ref text not null,
  attempt_count integer not null default 0 check (attempt_count >= 0),
  claimed_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (workflow_key, source_job_id)
);

create table automation_dead_letters (
  id bigint generated always as identity primary key,
  workflow_key text not null,
  source_job_id text,
  run_ref text not null,
  stage text not null,
  error_class text not null,
  safe_input_ref text,
  attempt_count integer not null,
  occurred_at timestamptz not null default now(),
  owner_state text not null default 'new'
    check (owner_state in ('new', 'investigating', 'resolved'))
);

create table notification_outbox (
  effect_key text primary key,
  workflow_key text not null,
  source_job_id text not null,
  recipient_ref text not null,
  body text not null,
  approval_state text not null default 'pending'
    check (approval_state in ('pending', 'approved', 'rejected')),
  approved_by_role text,
  approved_at timestamptz,
  unique (workflow_key, source_job_id),
  foreign key (workflow_key, source_job_id)
    references automation_jobs (workflow_key, source_job_id)
);

create table synthetic_delivery_log (
  effect_key text primary key,
  delivered_at timestamptz not null default now()
);

create table automation_failure_events (
  id bigint generated always as identity primary key,
  workflow_key text not null,
  occurred_at timestamptz not null default now()
);

Create three no-login database roles and bind each credential login to exactly one role. Apply these grants as the migration owner; never use that owner in n8n:

create role safe_notice_prepare nologin;
create role safe_notice_reviewer nologin;
create role safe_notice_dispatch nologin;

revoke all on automation_jobs, automation_dead_letters,
  notification_outbox, synthetic_delivery_log,
  automation_failure_events from public;

grant select on automation_jobs, notification_outbox to
  safe_notice_prepare, safe_notice_reviewer, safe_notice_dispatch;
grant insert (workflow_key, source_job_id, run_ref)
  on automation_jobs to safe_notice_prepare;
grant insert on automation_dead_letters,
  automation_failure_events to safe_notice_prepare;
grant insert (effect_key, workflow_key, source_job_id, recipient_ref, body)
  on notification_outbox to safe_notice_prepare;
grant update (state, last_error_class, attempt_count, updated_at)
  on automation_jobs to safe_notice_prepare;
grant update (approval_state, approved_by_role, approved_at)
  on notification_outbox to safe_notice_reviewer;
grant update (state, updated_at) on automation_jobs to
  safe_notice_reviewer, safe_notice_dispatch;
grant insert on synthetic_delivery_log to safe_notice_dispatch;
grant usage, select on automation_dead_letters_id_seq,
  automation_failure_events_id_seq to safe_notice_prepare;

Have an administrator provision one login per role; keep login creation and passwords outside the migration and workflow JSON. Test as the actual logins, not the migration owner using SET ROLE: preparation cannot approve or dispatch, reviewer cannot claim or dispatch, and dispatcher cannot claim or approve. Keep the denials in the single test record.

Column grants alone do not constrain values in state. Add a reviewed transition trigger that checks the login's role and the prior state:

create function enforce_safe_notice_transition()
returns trigger language plpgsql as $$
begin
  if new.state = old.state then return new; end if;

  if old.state = 'claimed' and new.state in ('prepared', 'dead_letter', 'reconcile')
     and pg_has_role(session_user, 'safe_notice_prepare', 'member') then
    return new;
  elsif old.state = 'prepared' and new.state = 'pending_approval'
     and pg_has_role(session_user, 'safe_notice_prepare', 'member') then
    return new;
  elsif old.state = 'pending_approval' and new.state = 'approved'
     and pg_has_role(session_user, 'safe_notice_reviewer', 'member') then
    return new;
  elsif old.state = 'approved' and new.state in ('completed', 'reconcile')
     and pg_has_role(session_user, 'safe_notice_dispatch', 'member') then
    return new;
  end if;

  raise exception 'forbidden job-state transition: % -> %', old.state, new.state;
end;
$$;

create trigger guard_safe_notice_transition
before update of state on automation_jobs
for each row execute function enforce_safe_notice_transition();

create function queue_safe_notice_candidate(
  p_workflow_key text,
  p_source_job_id text,
  p_effect_key text,
  p_recipient_ref text,
  p_body text
) returns boolean
language plpgsql
as $$
begin
  update automation_jobs
  set state = 'prepared', last_error_class = null, updated_at = now()
  where workflow_key = p_workflow_key
    and source_job_id = p_source_job_id
    and state = 'claimed';

  if not found then
    return false;
  end if;

  insert into notification_outbox (
    effect_key, workflow_key, source_job_id, recipient_ref, body
  ) values (
    p_effect_key, p_workflow_key, p_source_job_id, p_recipient_ref, p_body
  );

  update automation_jobs
  set state = 'pending_approval', updated_at = now()
  where workflow_key = p_workflow_key
    and source_job_id = p_source_job_id
    and state = 'prepared';

  if not found then
    raise exception 'could not complete prepared -> pending_approval transition';
  end if;

  return true;
end;
$$;

revoke all on function queue_safe_notice_candidate(
  text, text, text, text, text
) from public;
grant execute on function queue_safe_notice_candidate(
  text, text, text, text, text
) to safe_notice_prepare;

The queue function deliberately uses three separate PL/pgSQL statements. PostgreSQL data-modifying CTE sub-statements share one snapshot and cannot reliably update the same job row first to prepared and then to pending_approval. A function call is one atomic database statement, while its procedural statements run in order; an insert or second-transition exception rolls back the first transition and the outbox insert. false means the expected claimed prior state was absent.

Use three non-personal n8n credentials: one login that is a member only of safe_notice_prepare, one only of safe_notice_reviewer, and one only of safe_notice_dispatch. Share each credential only into its named n8n project. The preparation owner must not be able to view, use, or share the reviewer credential. Retain the migration and function outside n8n in the reviewed repository; keep login secrets only in the credential store.

Define one input contract for both framings

Create a disabled workflow named Safe notice preparation - synthetic. Add Manual Trigger and Edit Fields named Synthetic event with:

{
  "workflow_key": "safe-notice-v1",
  "source_job_id": "LAB-PAPER-SYN-041",
  "run_ref": "RUN-SYN-001",
  "profile": "lab",
  "recipient_ref": "AUTHOR-SYN-07",
  "subject_ref": "PAPER-SYN-041",
  "event_type": "review_candidate_ready",
  "service_mode": "ok"
}

Add a Code node named Validate source event. Set Language to JavaScript and Mode to Run Once for All Items. This makes the required n8n return shape explicit: an array of items, each with an object-valued json property.

const allowedProfiles = new Set(['lab', 'company']);
const allowedEvents = new Set(['review_candidate_ready', 'reminder_due']);
const requiredStrings = [
  'workflow_key', 'source_job_id', 'run_ref', 'profile',
  'recipient_ref', 'subject_ref', 'event_type', 'service_mode',
];

return $input.all().map((item) => {
  const data = item.json;
  const errors = [];
  for (const field of requiredStrings) {
    if (typeof data[field] !== 'string' || data[field].trim() === '') errors.push(field);
  }
  if (!allowedProfiles.has(data.profile)) errors.push('profile');
  if (!allowedEvents.has(data.event_type)) errors.push('event_type');
  if (!/^[A-Z0-9-]{6,80}$/.test(data.source_job_id || '')) errors.push('source_job_id');

  return {
    json: {
      ...data,
      input_valid: errors.length === 0,
      validation_reason: errors.length ? `invalid:${[...new Set(errors)].join(',')}` : 'OK',
    },
  };
});

An invalid item routes to input_review before the circuit query, job claim, model, or outbox. Insert one automation_dead_letters row with stage: input_validation, error_class: invalid_input, source_job_id: null when the ID is empty, and the supplied run_ref and safe reason. This is its persisted review record; do not fabricate a job ID. Empty input is not retryable.

Use Postgres Execute Query with Query Parameters {{ ['safe-notice-v1', $json.run_ref, $json.validation_reason] }} on that branch:

insert into automation_dead_letters (
  workflow_key, source_job_id, run_ref, stage, error_class,
  safe_input_ref, attempt_count
)
values ($1, null, $2, 'input_validation', 'invalid_input', $3, 0)
returning id, owner_state;

Open the circuit before claiming more work

Add a Postgres Execute Query node named Check recent failures and volume. Postgres nodes emit query rows rather than automatically carrying their input fields forward, so preserve the durable identity explicitly. Set Operation to Execute Query, Query Batching to Independently, and Query Parameters to {{ [$('Validate source event').item.json.workflow_key, $('Validate source event').item.json.source_job_id, $('Validate source event').item.json.run_ref] }}. The expressions read the validated item, not a mutable field from an intervening node. The query returns one row even when counts are zero, including the three identity fields:

select
  $1::text as workflow_key,
  $2::text as source_job_id,
  $3::text as run_ref,
   (select count(*) from automation_failure_events
    where workflow_key = $1 and occurred_at >= now() - interval '10 minutes') as failures_10m,
   (select count(*) from automation_jobs
    where workflow_key = $1 and claimed_at >= now() - interval '1 minute') as jobs_1m;

For this synthetic workflow, open the circuit at three failures in ten minutes or twenty claimed jobs in one minute. Count claimed_at, not updated_at: approval and completion update old jobs and must not look like new volume. Store thresholds in the workflow description and have an owner approve changes. Route an open result to circuit_open, alert the failure-queue owner once, and create no job claim. Inspect that route's execution data and confirm workflow_key, source_job_id, and run_ref still equal the validated event. A production threshold must come from measured normal traffic and risk, not these training numbers.

Claim the source job atomically

On the closed branch, add Postgres Execute Query named Claim source job. Set Query Batching to Independently and Query Parameters to {{ [$json.workflow_key, $json.source_job_id, $json.run_ref] }}:

with claim as (
  insert into automation_jobs (workflow_key, source_job_id, run_ref)
  values ($1, $2, $3)
  on conflict (workflow_key, source_job_id) do nothing
  returning 1
)
select
  $1::text as workflow_key,
  $2::text as source_job_id,
  $3::text as run_ref,
  exists(select 1 from claim) as claimed;

claimed: true continues. claimed: false ends as duplicate_ignored with zero model, approval, and delivery calls. Before preparation, add a Code node named Restore claimed event context, set it to JavaScript and Run Once for Each Item, and use the linked validated item rather than combining unrelated items by position:

const original = $('Validate source event').item.json;
const claim = $json;
const identity = ['workflow_key', 'source_job_id', 'run_ref'];

for (const field of identity) {
  if (typeof original[field] !== 'string' || claim[field] !== original[field]) {
    throw new Error(`claim context mismatch: ${field}`);
  }
}

return {
  json: {
    ...original,
    ...claim,
  },
};

Inspect the Code output and require the original profile, recipient_ref, subject_ref, event_type, and service_mode, plus matching workflow_key, source_job_id, and run_ref. The mismatch exception stops processing rather than attaching a claim to the wrong source event. This makes context restoration visible rather than assuming a Postgres node passes input through. Never make the key from Date.now(), current time, or execution ID; those values differ on every duplicate.

Prepare, validate, retry once, then stop

The reversible preparation creates only a candidate notice. Reuse the structured-output validation from T05-L02 and per-item evidence from T05-L03. Require exactly source_job_id, recipient_ref, subject_ref, body, and review_required. The IDs must match input, body must be 20-400 characters, and review_required must be true. After validation, derive effect_key as workflow_key + ':' + source_job_id. Never accept it from the model or trigger. Stop if either component differs from the claimed identity; inspect the result before queueing.

If model output is invalid, make exactly one stricter model retry with the validation reason. If that output is also invalid, atomically insert one dead-letter row with stage: model_validation, error_class: unusable_output, and attempt_count: 2, then transition the job from claimed to dead_letter with the same error class. Alert the owner. The alert links to the safe run reference. It does not contain the raw source or model response.

Set one n8n error workflow beginning with Error Trigger for unexpected node or platform failures. It normalises the workflow name, safe execution reference, last stage, error class, and time into the same dead-letter table, then sends the same tested alert. Remove stack traces and raw node input from the notification. Every handled service or validation branch still writes its own precise state; the error workflow catches failures that escaped those branches. Insert an automation_failure_events row for each terminal service, model, or unexpected execution failure so the circuit query uses observed failures rather than a manually maintained count.

For an upstream HTTP read before preparation, use a separate retry loop. Initialise attempt_count: 1. Retry only a no-response-before-acceptance, 429, 500, 502, 503, or 504; wait 5 seconds before attempt 2 and 20 seconds before attempt 3. After attempt 3, transition to job state dead_letter with error class transient_exhausted, insert the matching dead-letter row, and alert. A 401 or 403 transitions immediately to job state dead_letter with error class credential_revoked; waiting will not repair authorization. Any result that may have partly applied an effect becomes reconcile with error class ambiguous_effect and no retry.

Use a Code node named Classify inspected HTTP result. Configure JavaScript and Run Once for All Items; have the preceding Edit Fields node always supply attempt_count, integer http_status (use 0 when there was no response), Boolean no_response, and Boolean destination_accepted:

const retryableStatus = new Set([429, 500, 502, 503, 504]);
const waits = { 1: 5, 2: 20 };

return $input.all().map((item) => {
  const data = item.json;
  const attempt = Number(data.attempt_count);
  const status = Number(data.http_status);
  const accepted = data.destination_accepted === true;
  const noResponse = data.no_response === true;
  if (!Number.isInteger(attempt) || attempt < 1 || attempt > 3) {
    throw new Error('attempt_count must be 1, 2, or 3');
  }
  if (!Number.isInteger(status) || status < 0 || status > 599) {
    throw new Error('http_status must be an integer from 0 to 599');
  }

  let decision;
  let error_class = null;
  if (accepted && (noResponse || status < 200 || status >= 300)) {
    decision = 'reconcile';
    error_class = 'ambiguous_effect';
  } else if (status === 401 || status === 403) {
    decision = 'dead_letter';
    error_class = 'credential_revoked';
  } else if ((noResponse || retryableStatus.has(status)) && attempt < 3) {
    decision = 'retry';
  } else if (noResponse || retryableStatus.has(status)) {
    decision = 'dead_letter';
    error_class = 'transient_exhausted';
  } else if (status >= 200 && status < 300) {
    decision = 'success';
  } else {
    decision = 'dead_letter';
    error_class = 'unexpected_execution';
  }

  return {
    json: {
      ...data,
      decision,
      error_class,
      retry_after_seconds: decision === 'retry' ? waits[attempt] : 0,
    },
  };
});

Connect retry to a Wait node using retry_after_seconds, then an Edit Fields node that sets attempt_count to {{ Number($json.attempt_count) + 1 }}, and return only to the pre-effect HTTP call. Do not wrap the complete workflow in retry-on-fail. Confirm from execution data that validation, job claim, approval, and delivery are not repeated by this loop.

Make every persisted transition reproducible

Do not use a generic UPDATE state = {{ $json.decision }} node. Give each arrow its own reviewed, parameterised statement with an expected prior state. A statement that returns transitioned: false is itself a stopped error and must alert the owner; it must not continue.

For a valid candidate, add Postgres Execute Query named Queue candidate. Set Query Batching to Independently and Query Parameters to {{ [$json.workflow_key, $json.source_job_id, $json.effect_key, $json.recipient_ref, $json.body] }}. Call the reviewed function so the two updates of the same job row occur as ordered procedural statements, not as sibling data-modifying CTEs. The function permits only claimed -> prepared -> pending_approval and creates the outbox row atomically:

select queue_safe_notice_candidate($1, $2, $3, $4, $5)
  as transitioned;

Require transitioned: true before continuing. Test the false path by calling it after the job has left claimed; it must create no second outbox row and make no state change. Also force an outbox constraint failure in the disposable database and verify the function call leaves the job in claimed, proving that the ordered path rolls back as one unit.

For a 401 or 403, add Postgres Execute Query named Persist revoked credential. Set Query Parameters to {{ [$json.workflow_key, $json.source_job_id, $json.run_ref, $json.safe_input_ref, $json.attempt_count] }}. The constants in this statement ensure that credential_revoked is an error class, never a state:

with failed as (
  update automation_jobs
  set state = 'dead_letter',
      last_error_class = 'credential_revoked',
      attempt_count = $5,
      updated_at = now()
  where workflow_key = $1 and source_job_id = $2 and state = 'claimed'
  returning workflow_key, source_job_id
), letter as (
  insert into automation_dead_letters (
    workflow_key, source_job_id, run_ref, stage, error_class,
    safe_input_ref, attempt_count
  )
  select workflow_key, source_job_id, $3, 'upstream_read',
         'credential_revoked', $4, $5
  from failed
  returning 1
), failure_event as (
  insert into automation_failure_events (workflow_key)
  select $1 from letter
  returning 1
)
select
  exists(select 1 from letter) as transitioned,
  exists(select 1 from failure_event) as failure_recorded;

Require both returned Booleans to be true; otherwise stop and alert. This keeps the circuit's failure count in the same transaction as the terminal failure. Use the same shape for the other fixed arrows: claimed -> dead_letter with unusable_output or transient_exhausted, and claimed -> reconcile with ambiguous_effect. Keep stage and error-class constants in the reviewed SQL, not in incoming event data. Together with the claim, approval, and dispatch statements, these are the complete allowed arrows for this exercise; there is no free-form state editor.

Lab framing: notify an author once

For Lab, the prepared body may say:

Synthetic notice: PAPER-SYN-041 is ready for internal review. No response is required.

The recipient_ref is an invented internal identifier, not an email address. Insert the valid candidate into notification_outbox with effect_key: safe-notice-v1:LAB-PAPER-SYN-041, approval_state: pending, and job state pending_approval. A literature owner opens the candidate, source reference, and evidence in the approved internal queue. Approval must be an explicit state change by the reviewer role; opening the row is not approval. No author is actually contacted.

Company framing: prepare one customer reminder

Use the same workflow with:

{
  "workflow_key": "safe-notice-v1",
  "source_job_id": "CO-REMINDER-SYN-073",
  "run_ref": "RUN-SYN-101",
  "profile": "company",
  "recipient_ref": "CUSTOMER-SYN-12",
  "subject_ref": "ORDER-SYN-073",
  "event_type": "reminder_due",
  "service_mode": "ok"
}

The candidate states only that the fictional record is ready for internal reminder review. It does not promise delivery, request payment, or infer customer consent. A service owner checks policy and source state before approving. The exercise still writes only to the mock delivery table.

Dispatch only an approved effect

Approval is a separate authorization boundary, not a branch the preparation owner can invoke. Put the approval action in an access-controlled internal reviewer surface backed by the safe_notice_reviewer credential. Require an authenticated member of the reviewer group; derive reviewer_principal from that authenticated session, never from a form field, source event, or preparation-workflow expression. The reviewer sees the synthetic source reference, candidate body, evidence, effect key, and prior attempts. A different person than the workflow owner makes the decision.

For approval, run one Postgres Execute Query using the reviewer credential. Set Query Parameters to the trusted server-side values workflow_key, source_job_id, effect_key, and authenticated reviewer_principal, in that order. This is the only statement permitted to perform pending_approval -> approved:

with outbox_approved as (
  update notification_outbox
  set approval_state = 'approved',
      approved_by_role = $4,
      approved_at = now()
  where workflow_key = $1 and source_job_id = $2 and effect_key = $3
    and approval_state = 'pending'
    and exists (
      select 1 from automation_jobs j
      where j.workflow_key = $1 and j.source_job_id = $2
        and j.state = 'pending_approval'
    )
  returning workflow_key, source_job_id
), job_approved as (
  update automation_jobs j
  set state = 'approved', updated_at = now()
  from outbox_approved o
  where j.workflow_key = o.workflow_key
    and j.source_job_id = o.source_job_id
    and j.state = 'pending_approval'
  returning 1
)
select exists(select 1 from job_approved) as transitioned;

The database role behind safe_notice_prepare has no permission to run this approval statement or change approval columns. The reviewer role cannot claim, prepare, or dispatch. Test the boundary by attempting approval with the preparation credential and retaining the permission-denied result.

Build a second disabled workflow named Approved notice dispatcher - synthetic using only safe_notice_dispatch. It selects one outbox row with approval_state: approved, confirms approved_by_role and approved_at are present, and verifies the related job state is approved. An unapproved row ends with route outcome approval_required and no state change.

For the mock effect, set Query Batching to Independently and Query Parameters to {{ [$json.effect_key, $json.workflow_key, $json.source_job_id] }}. This one statement simulates a destination that deduplicates on the effect key:

with delivered as (
  insert into synthetic_delivery_log (effect_key)
  select o.effect_key
  from automation_jobs j
  join notification_outbox o
    on o.workflow_key = j.workflow_key
   and o.source_job_id = j.source_job_id
  where j.workflow_key = $2 and j.source_job_id = $3
    and j.state = 'approved'
    and o.effect_key = $1
    and o.approval_state = 'approved'
    and o.approved_by_role is not null
    and o.approved_at is not null
  on conflict (effect_key) do nothing
  returning 1
), finished as (
  update automation_jobs
  set state = 'completed', updated_at = now()
  where workflow_key = $2 and source_job_id = $3
    and state = 'approved'
    and exists(select 1 from delivered)
  returning 1
)
select
  exists(select 1 from delivered) as new_effect,
  exists(select 1 from finished) as job_completed;

The insert now depends on the trusted job state inside the same statement; a prior interface check alone would not enforce approval. The first approved dispatch should return new_effect: true and job_completed: true. Repeating it with the same effect key should return new_effect: false; there remains exactly one delivery-log row. In a real API, pass the same stable key through the provider's documented idempotency mechanism. If the destination has no such mechanism and the response is ambiguous, stop in reconcile; do not claim exactly-once delivery.

Inject the failures before activation

Run this fixed matrix with the same workflow revision:

Test	Injection	Required route	Observable result
`DOUBLE`	Trigger the same valid `source_job_id` twice	first `pending_approval`, second `duplicate_ignored`	one job and one outbox row
`REVOKED`	Set inspected upstream result to `401`	job state `dead_letter`, error class `credential_revoked`, and owner alert	one attempt; matching job and dead-letter rows; no approval or delivery
`EMPTY`	Set `source_job_id` to an empty string	`input_review` with error class `invalid_input`	one review row with null job ID; no circuit query, claim, model, or delivery
`TRANSIENT`	Inject `503`, `503`, then `200` before preparation	continue after bounded retry	attempts at 1, 2, 3 with waits 5 and 20 seconds
`AMBIGUOUS`	Set `destination_accepted: true` with a timeout result	`reconcile`	no automatic retry
`CIRCUIT`	Insert three synthetic recent failure events	`circuit_open`	no new claim

For the required three tests, expected summary output is:

DOUBLE   PASS jobs=1 outbox=1 deliveries=0 second=duplicate_ignored
REVOKED  PASS state=dead_letter error_class=credential_revoked attempts=1 dead_letters=1 alert=test-received
EMPTY    PASS route=input_review error_class=invalid_input claims=0 model_calls=0 deliveries=0

Test the ten-second kill switch: the workflow description must name the exact n8n project, both workflow names, and who may disable them. Have another authorised person locate and disable both workflows in under ten seconds from the workflow list. Leave both disabled after the exercise. Do not enable on a Friday afternoon or before the failure-queue owner confirms the alert arrived.

7. What goes wrong

Only the happy path is tested

Symptom: a normal item reaches approval, but nobody knows what duplicate, empty, revoked, timeout, or malformed runs do.

Fix: write expected terminal states first and inject each failure with synthetic data on the same workflow revision. Inspect database post-state as well as the green canvas.

A timestamp is used as the duplicate key

Symptom: every retry receives a new key, so eleven runs all look unique.

Fix: combine a stable source-system job ID with the workflow purpose and enforce uniqueness atomically in durable storage.

The workflow checks before it inserts

Symptom: two concurrent runs both see no prior row and both proceed.

Fix: claim with one INSERT ... ON CONFLICT DO NOTHING or equivalent compare-and-set operation, then branch on whether this run won.

A complete workflow is retried after sending

Symptom: a timeout after the destination accepted a message restarts preparation and sends again.

Fix: retry only the pre-effect operation. Use a destination idempotency key; route uncertain acceptance to reconciliation instead of blind replay.

Every approval is approved unread

Symptom: the queue becomes a rubber stamp, and the same person or workflow prepares and approves the effect.

Fix: show source evidence, candidate content, effect, and prior attempts; require a named authorised reviewer and record an explicit decision before dispatch.

Dead letters have no reader

Symptom: failures accumulate in a table while authors, customers, or colleagues report the problem first.

Fix: assign an owner, send a safe alert, test receipt, define response timing, and track new, investigating, and resolved states.

The circuit opens too late or never closes safely

Symptom: abnormal volume continues through the expensive or irreversible path, or somebody resets the breaker without understanding the failures.

Fix: evaluate failure and volume before job claim, stop new work at a measured threshold, and require owner review plus a synthetic canary before closing it.

8. Do it yourself: inject three failures in 45 minutes

Minutes 0-5: choose Lab or Company. Name the job key, reversible preparation, irreversible effect, approval owner, dead-letter owner, alert route, and exact two-workflow kill switch. Keep both workflows disabled.

Minutes 5-12: create the durable job, dead-letter, outbox, and mock-delivery tables. Configure the three separately scoped database credentials and verify exported workflow JSON contains no password or token.

Minutes 12-19: add input validation and the atomic claim. Run the same valid source event twice concurrently or in immediate succession. Confirm one winner, one duplicate terminal state, and one outbox row.

Minutes 19-25: inject an empty source_job_id. Confirm input_review occurs before database claim or model call, and query the tables to prove no job or delivery row was created.

Minutes 25-31: inject a 401 from the pre-effect service. Confirm job state dead_letter with last_error_class: credential_revoked, one matching watched dead-letter row, one received safe alert, exactly one attempt, and no approval or delivery call.

Minutes 31-36: run a 503, 503, 200 sequence to verify attempts and waits. Then inject ambiguous acceptance and confirm it stops at reconcile without retry.

Minutes 36-40: first attempt approval with the preparation credential and confirm permission is denied. Then have the separate reviewer role approve one valid synthetic candidate through the access-controlled reviewer surface. Run the mock dispatcher twice and confirm exactly one delivery-log row for the stable effect key.

Minutes 40-43: inject the circuit threshold and confirm no new job claim. Remove the synthetic failure events, run one approved canary, and keep activation off.

Minutes 43-45: have another authorised person use the written kill switch. Export one test record containing the three required runs and their database post-state, remove credentials and unnecessary raw text, and record pass only if the alert was actually received.

9. Exit check

Deliver exactly one artifact: one passing failure-injection test record for the double trigger, revoked key, and empty input on one workflow revision.

It passes when the record identifies the workflow revision, stable source job keys, injected condition, expected and observed route, attempt count, waits where applicable, model and effect call counts, job/outbox/delivery/dead-letter post-state, alert receipt, approval state, circuit state, test time, reviewer, reviewer-boundary denial, and kill-switch result. The double trigger must create one durable claim and at most one pending or completed effect. The revoked-key case must persist job state dead_letter and error class credential_revoked in both the job and watched dead-letter row after exactly one attempt. The empty-input case must persist an input_review record with error class invalid_input and no job claim before expensive work. Logs and table extracts are embedded evidence inside this single record. It fails if revoked credentials appear as a second invented job state, the preparation credential can approve, a timestamp supplies idempotency, a complete workflow retries after ambiguous delivery, an alert is merely configured rather than received, a real person is contacted, or credentials or real records appear in evidence.

10. Rule to remember

It is finished when you know what it does when it fails.

11. Further reading & tools

Taught: T05-L03 · The pipeline that writes itself - supplies bounded input, per-item validation, and inspectable branches before live failure controls are added.
Taught: T05-L02 · Automations with AI in the middle - supplies structured validation, one stricter model retry, and human fallback.
Taught: n8n - introduces visible node inputs, branches, execution data, and credential boundaries.
Taught: n8n error handling (opens in a new tab) - primary guidance for error outputs, error workflows, and stopping or continuing deliberately.
Taught: n8n Postgres node (opens in a new tab) - primary node reference for parameterised queries used by the durable claim.
Catalogued: n8n Wait node (opens in a new tab) - current reference for bounded retry waits; approval remains a separate enforced state in this design.
Catalogued: n8n workflow settings (opens in a new tab) - current timeout, error-workflow, and execution settings.
Catalogued: n8n human fallback guidance (opens in a new tab) - additional queue pattern for uncertain model results.
Catalogued: T05-L05 · Operating automations - continues with monitoring, tracing, cost, replay, and runbooks.
Catalogued: Tools index - compare workflow systems only after defining durable job identity, retry, approval, and stop semantics.