A useful test for a workflow that creates records or sends messages:
- Send the same event twice.
- Send it twice simultaneously.
- Interrupt the run after the external action succeeds, but before completion is recorded.
My proposed design would use a stable event key, an atomic claim, and a recorded completion state.
For a PostgreSQL-backed claim, a unique key with ON CONFLICT DO NOTHING RETURNING can identify whether the claim was actually inserted.
But that database claim does not, by itself, prove whether an external email or payment completed. That needs provider-side deduplication or reconciliation.
The outcome to test: legitimate new events still proceed, duplicates don’t repeat the action, and uncertain results stay visible.
Agreed on the ordering, and the send-twice-simultaneously case is where the claim has to be one statement rather than read-then-write: INSERT ... ON CONFLICT DO NOTHING RETURNING before the external call, where an empty result means another execution already holds the claim and this one should skip instead of proceeding. The third test is the one a claim cannot answer. A row saying “claimed” is not evidence the email or payment happened, so write the intent before the call and give the row three states — claimed, confirmed, unknown — with the provider’s receipt (message id, payment id, or the echoed idempotency key) as the only thing allowed to move it to confirmed. Treat unknown as its own reconciliation queue, never as a retryable failure: retrying something you could not observe is exactly how the duplicate happens. And where the provider offers no idempotency key, at-most-once is the honest target for that step — decide which side of that tradeoff you can live with before you build the claim.
1 Like
Great test list, and the “interrupt after the external action succeeds but before completion is recorded” case is the one that bites hardest in production. Building on the atomic-claim idea, here’s how I implement this pattern concretely in n8n:
1. Claim node first. Right after computing the stable event key (I hash source + externalEventId, normalized: lowercase, trimmed, canonical timezone on any timestamps), run a Postgres node with INSERT INTO event_claims(event_key, status, claimed_at) VALUES (...) ON CONFLICT DO NOTHING RETURNING event_key. If the result set is empty, another execution holds or already finished the claim — route those items to a “duplicate, skip” branch (an IF node checking whether the returned row is empty). Because Postgres serializes the INSERT, this covers both the sequential double-send and the simultaneous-race case.
2. The interrupt case needs two-phase state, not just a claim. A claim alone can’t tell you whether the email/payment actually completed. I use a status column: set it to claimed before the external call, then update to completed (or failed, storing the error payload) right after. Then I add a separate lightweight “sweeper” workflow on a Schedule Trigger that selects status = 'claimed' rows older than X minutes and reconciles: it queries the external system’s API (send log / charge lookup / CRM record) to see whether the action actually happened, then marks the claim completed or releases it.
3. n8n-specific gotchas I’ve hit:
- Don’t use workflow static data (
$getWorkflowStaticData) for the claim store. It’s per-workflow, not safe across concurrent executions, and invisible to retries in queue mode. The database must be the source of truth.
- For APIs that support it, also send an
Idempotency-Key header on the HTTP Request node (Stripe-style providers dedupe server-side), so even if a race slips through, the provider absorbs it.
- Wrap the external call so that network timeouts don’t silently leave
claimed rows: set the HTTP Request node’s timeout and “retry on fail” settings deliberately low there (a handful of attempts, short waits), because the atomic claim + sweeper now owns retry correctness — the node’s built-in retry should only cover transient blips.
- Test the interrupt case for real: run the workflow manually, stop the execution mid-run, then re-send the same event. Your branch logic should route it as in-flight/duplicate rather than replaying the action, and the sweeper should later reconcile the orphaned
claimed row.
With claim + two-phase status + sweeper, all three of your test cases pass: legitimate new events proceed, duplicates skip without re-firing, and interrupted runs get reconciled instead of double-executed. This is also much cheaper than it looks — the sweeper only touches rows that never completed, so in steady state it’s nearly idle.