An Hourly n8n Workflow That Does Not Overlap Itself: Locks, Merge Barriers and Quiet Runs

This is part 4 of the follow-up machine series. Part 3 showed how deals get registered. This part covers “Followup 2: Evaluate”, the workflow that decides, every hour, what each watched deal needs.

It has 45 nodes. Most of the interesting failures in it happened on runs where there was nothing to do.

What one run does

The schedule is 0 7-19 * * 1-5 in Europe/Vienna: on the hour, 07:00 to 19:00, weekdays. A run goes through six stages:

1. Claim the run, so two runs never overlap.
2. Reconcile with the CRM: register any in-scope deal that has no watch.
3. Select the follow-ups that are due today.
4. Route each one: close the watch, reset its clock, or fire a task.
5. Create or adopt the ClickUp task, exactly once per due step.
6. Finish: record the result, and alert if the CRM has been unreachable three runs in a row.

Stage 1: claim the run

Part 2 showed the claim query: an insert into run_log that only succeeds when no other run is live. What part 2 did not show is what happens when it does not succeed.

A Postgres node whose query returns zero rows does not output zero items. It outputs one item, { success: true }. So the next node receives an item either way, and without a check, a blocked run carries on as if it owned the work.

The build notes said “if no row is returned, end the run”. The node that does that was never built. A blocked run ran the whole pipeline without a run_log_id and crashed at the last node with there is no parameter $6, which then fired a false alert.

The fix is an IF node right after the claim:

Run claimed?   {{ $json.run_log_id ? 'claimed' : 'busy' }}  equals  claimed
  true  → continue
  false → "Skipped (run already live)" (No Operation)

Why did nobody notice earlier?

Because the schedule hid it. A claim blocks for 55 minutes, and hourly runs are 60 minutes apart. They never met.

It only showed while testing that two runs in a row create no duplicates, when the second run started while the first claim was still live.

Stage 2: reconcile, and the merge that starved

Reconciliation fetches the in-scope deals from the CRM and the open watches from Postgres in parallel, then a Code node computes the difference:

for (const d of espoDeals) {
  if (watched.has(d.id)) continue;
  out.push({ op: 'create', subject_id: d.id, /* ... */ });
}
for (const w of openWatches) {
  if (!inScope.has(w.subject_id)) out.push({ op: 'close_out_of_scope', watch_id: w.watch_id });
}
if (!out.some((o) => o.op === 'create')) {
  out.push({ op: 'none' });
}
return out;

The create items go to a Postgres node that inserts watches. After that, a Merge node called “Wait for apply”, in “choose branch” mode, waits for the inserts to finish before the run reads the due steps. The run should only read state after its own writes have landed.

Look at the last three lines of the Code node. They were not in the first version.

Most of the time, there is nothing to create. Every deal already has a watch. That is the steady state, not the exception.

In that state, no item reached the insert node, so no item reached that input of the Merge node. A Merge node waiting for both inputs does not continue when one of them never receives data. The run simply ended there, silently, and the claimed run_log row stayed running, which blocked every run for the next 55 minutes.

It showed up on the first real steady-state run, at 17:00 on 3 August.

The fix is the { op: 'none' } sentinel. When there is nothing to create, the diff emits one marker item, and a filter called “No changes signal” routes it straight to the Merge node. Exactly one of the two paths delivers every run.

One more detail about the Merge node came up three days later: it waits for each input to receive data, not for every connection into that input. When I wired a new query as another feeder into the same input, the sentinel satisfied that input, and a live test run read the due steps before the new update had landed. The new query went in series in front of the read instead.

Closing without losing the reason

The diff also knows which watches belong to deals that have left the in-scope stages. It does not know why they left: won, lost, deleted or moved backwards. That reason matters, because the Friday digest reports it.

So the diff does not close them. The first version simply dropped those items and assumed the watch would close when its next follow-up came due and the per-deal route fetched the stage. With follow-ups 5, 12 and 21 business days apart, that took days. On 6 August, two lost deals were still counted as open follow-ups.

The fix moves the watch’s next due date to today and lets the normal route close it with the precise reason, in the same run:

update watch_steps ws
set due_on = (now() at time zone 'Europe/Vienna')::date
from watches w, p
where ws.watch_id = w.id
  and w.status = 'open'
  and ws.fired_at is null and ws.skipped_at is null
  and ws.due_on > (now() at time zone 'Europe/Vienna')::date
  and jsonb_array_length(p.j -> 'inScope') > 0          -- never on an empty list
  and not exists (select 1 from jsonb_array_elements_text(p.j -> 'inScope') e
                  where e = w.subject_id)
  and ws.step_no = (select min(x.step_no) from watch_steps x
                    where x.watch_id = w.id and x.fired_at is null and x.skipped_at is null);

Two guards matter. It does nothing when the in-scope list is empty, because a bug that returns an empty list would otherwise pull every open watch forward at once. And it never runs when the CRM request failed: an IF node called “Espo partial?” routes around reconciliation entirely in that case.

Stage 3: select due steps, and Split In Batches with nothing to split

select distinct on (w.id)
  s.id as step_id, s.step_no, s.due_on::text as due_on, w.id as watch_id, w.subject_id
  -- trimmed
from watch_steps s
join watches w on w.id = s.watch_id
where w.status = 'open'
  and s.fired_at is null
  and s.skipped_at is null
  and s.due_on <= (now() at time zone 'Europe/Vienna')::date
order by w.id, s.due_on, s.step_no;

distinct on (w.id) caps it at one step per watch per run. If the machine was off for a month, a deal gets one task per run, marked as overdue, instead of all its missed follow-ups in the same minute.

The rows go into a Loop (Split In Batches, batch size 10). And here is the second quiet-run stall. When the query returns nothing, Split In Batches emits on neither of its outputs. Not the “loop” output, and not the “done” output either. So “Finish run”, which hangs off “done”, never ran, and the claim stayed running again.

The fix takes two parts:

1. Always Output Data ON for “Select due steps”, so an empty result still emits one empty item.
2. An IF node, “Any due steps?”, checks {{ $json.step_id ? 'due' : 'none' }}. due goes into the loop, none goes straight to “Finish run”.

The empty item has to be filtered out of the count that run_log records, or every quiet run reports one item seen.

Test the run where nothing happens. It is the most common run.

The same pair of bugs, the missing claim check and the empty loop, turned out to exist in the Enrich workflow too. It was masked there only because at least one watch happened to be open.

Three n8n behaviours on empty runs

Node	With no input	Fix
Postgres, zero rows	Outputs one item { success: true }	Check for the field you need
Merge, choose branch	Waits forever for the empty input	Send a sentinel item
Split In Batches	Emits on neither output	Always Output Data, then IF

Stage 4: route each deal

For each due step, the workflow fetches the deal’s stage and its latest activity from the CRM, then a Code node decides. The decision is a pure function with tests, not logic spread across IF nodes:

// Shortened. The full function also computes `touched`, `newDueDates` and `overdueNote`.
export function decide(input: RouteInput): Outcome {
  if (!input.subjectFound) return { kind: 'close', reason: 'subject_gone' };

  const terminal = input.stage === null ? undefined : TERMINAL_STAGES[input.stage];
  if (terminal) return { kind: 'close', reason: terminal };

  if (input.stage !== null && !STAGES_IN_SCOPE.includes(input.stage)) {
    return { kind: 'close', reason: 'out_of_scope' };
  }

  const clockStart = input.lastResetAt ?? input.registeredAt;
  if (input.lastActivityAt && input.lastActivityAt.getTime() > clockStart.getTime()) {
    // recompute the remaining due dates from the activity date
    return { kind: 'reset', newDueDates /* ... */ };
  }

  // untouched for 90 days: stop, but show it in the digest
  if (input.now.getTime() - touched.getTime() > 90 * DAY_MS) {
    return { kind: 'close', reason: 'exhausted' };
  }

  return { kind: 'fire', overdueNote };
}

A Switch node then sends close, reset and fire to their own branches.

Three details about the CRM fetches:

• A 404 is a normal answer. “Fetch subject” has “Include Response Status” on and routes errors to its error output. An IF node checks for 404, which means the deal was deleted, and passes that on so the route closes the watch. Any other error skips the item and marks the run partial. A blanket “never error” setting would turn a 500 into a success with an empty stage, and the machine would act on nothing.
• Only activity that happened counts. The latest activity comes from EspoCRM’s history endpoint, which holds completed emails, calls and meetings. A call planned for next week sits in a different list and correctly does not reset the clock.
• Emails and calls put their date in different fields. Calls and meetings have dateStart. Emails have dateSent. The first version read only dateStart, so an email, the most common kind of contact, never reset anything. It reads dateStart ?? dateSent now.

Business days

Due dates are business days in Europe/Vienna, skipping a holidays table:

export function addBusinessDays(start: IsoDate, n: number, holidays: ReadonlySet<IsoDate>): IsoDate {
  let t = new Date(`${start}T12:00:00Z`).getTime();
  let remaining = n;
  let iso = start;
  while (remaining > 0) {
    t += DAY_MS;
    iso = new Date(t).toISOString().slice(0, 10);
    if (isBusinessDay(iso, holidays)) remaining--;
  }
  return iso;
}

Every date is handled at noon UTC, so a daylight saving change can never move a calendar day.

Two separate mechanisms are at work here. The trigger schedule, 0 7-19 * * 1-5, limits the runs to Monday through Friday. The due dates come from the business-day arithmetic and the holidays table instead. A public holiday does not stop the trigger, it only moves the dates.

Stage 5: fire a task exactly once

The fire branch creates a ClickUp task. ClickUp has no uniqueness constraint the machine can rely on, so this is the one place where “run twice equals run once” needs explicit work.

The failure it prevents: the task is created, then the Postgres write that records it fails. The next run sees the step as unfired and creates the task again. And again, every hour.

So before every create, “ClickUp idempotency check” queries the list for an open task whose custom field Deal holds this deal’s ID. If a task with the expected name exists, “Adopt existing task” takes its ID and skips the create. Either way, “Mark fired” writes the task ID:

update watch_steps
set fired_at = now(), clickup_task_id = $1
where id = $2::bigint and fired_at is null
returning id;

One configuration trap here: the Deal custom field has to be of type short text. A field of type URL rejects the CRM ID with FIELD_010, “Value is not a valid URL”, and every task create fails with a 400. If the field is missing entirely, the run fails loudly on purpose. That is a configuration error, and continuing without it would create tasks nobody can match to a deal.

Stage 6: finish, and notice a dead CRM

“Finish run” updates the run_log row: ok if every item succeeded, partial if any item was skipped. A second query checks the last three runs:

select count(*) = 3 as streak
from (
  select status from run_log
  where workflow = 'wf2' and status in ('ok', 'partial', 'failed')
  order by started_at desc
  limit 3
) t
where status = 'partial';

Three partial runs in a row almost always mean the CRM is down, and that sends an alert. A single partial run has more than one possible cause: one deal could not be read, or a ClickUp call failed. Either way, the item it skipped is simply retried next hour.

What to take from this part

• A Postgres node with zero rows still outputs an item. Check for the value you need.
• A Merge node waiting for two inputs stalls when one path delivers nothing. Give the empty case its own item.
• Split In Batches with no input emits on neither output. Always output data, then branch.
• Test the run where nothing happens. It is the most common run.
• Before creating something in a system without constraints, look for it first.

Next: Part 5, Enrich.