This is part of my build-in-public series on running LLM workflows in n8n in production. Everything here is a failure I actually watched happen, not a hypothetical.
Two workflows wrote to the same customer record on the same night. One updated the plan from a billing webhook. The other updated the contact details from a form submission. Both executions said success. The record that came out was neither update: the plan change was there, the contact change was gone, and nothing in either log suggested anything had been lost.
The mistake was assuming that two workflows touching the same row at different times is the same as two workflows touching it in sequence. It is not. Each workflow read the record, changed its own field, and wrote the whole record back. The second write overwrote the first, not because either workflow was wrong about its own field, but because neither workflow knew the other had been there in between. A read-modify-write cycle with no protection is a race, even when the two writers never run at the same second — they only have to overlap between the read and the write.
The fix is to stop writing whole records from workflows. A workflow should send a change, not a replacement: update this field, to this value, based on this version. If the version it read is no longer current, the update must fail instead of silently overwriting. That failure is not a bug in the workflow. It is the system catching a collision that would otherwise have passed as success.
Two details make this work, and both are easy to get wrong.
First, the version check has to live in the update itself, not in a node before it. "Read the version, check it is still the same, then write" is again two steps with a gap in between, and another workflow can slip into that gap. The check must be part of the same statement that writes: update where the version matches, and report how many rows changed. Zero rows changed means someone else moved first, and the workflow must re-read, re-apply its own change on top of the new state, and try again — or hand the conflict to a human if the two changes cannot both be true.
Second, retries must not replay a stale change. A workflow that failed on the version check and simply reruns from its first node will re-read, which is fine. A workflow that retries only the write step, with the old values still in memory, will fail the same way forever, or worse, succeed after a third workflow has moved the record again and land on top of work it never saw. The retry has to start from a fresh read, every time. Stale data in a retry is how a collision that was caught once gets lost a second time.
I also keep a rule for which fields a workflow is allowed to touch. Each workflow owns its fields, and it writes only those. The billing workflow never writes contact details, even if it read them. The form workflow never writes the plan, even if the form mentioned it. Narrow writes shrink the surface where two workflows can collide, and when a collision does happen, the version check tells me exactly which two changes fought over which field, instead of leaving me with a record that is quietly half right.
The rule I keep coming back to: a workflow that writes a whole record is claiming it knows the record's full state. It does not. It knows one field, at one moment, and the honest write says so.
Free workflows from this series (MIT): https://github.com/zhp910318/n8n-ai-automation-lab
The production templates in my store: https://payhip.com/haopengailab
Top comments (138)
We hit this exact shape of bug with retried payment webhooks overwriting a plan change: read-modify-write on the record, two writers, no version check, and both executions logging success. What finally killed it for us was idempotency keys per event plus the version check living inside the update itself, not a step before it. The field ownership rule you end on maps neatly onto how we split webhook handlers now.
That maps exactly — and your case shows why the two mechanisms cover different halves. The idempotency key per event stops the same webhook delivery from applying twice; the version check inside the update stops two different events from overwriting each other. Either one alone leaves a hole: keys without a version check still let a stale retry land on top of a newer plan change, and a version check without keys turns every duplicate delivery into a conflict to review instead of a no-op. Splitting handlers by field ownership is the third leg — it shrinks which pairs of events can even collide. Thanks for sharing the webhook version of this; it is the same shape as the article, just with money attached, which is why the silent version is worse.
Exactly the three-legged version we landed on. The one that surprised us was the third leg: splitting handlers by field ownership shrank the blast radius more than either of the other two. Two events can only collide when they write the same fields, so once each handler owned its fields outright, the review table went from several entries a week to almost none. Money attached is why we kept the review path instead of auto-merging anything touching billing state.
Exactly — and the n8n wrinkle is that a workflow's Postgres node runs one statement at a time, so “same transaction” does not happen by just placing two nodes in sequence. In practice that means folding the check and the write into a single statement (INSERT ... ON CONFLICT ... RETURNING) or a small database function that does both and returns inserted-versus-replay, so the atomicity lives in Postgres, not in the workflow's step order.
That n8n detail is the one that bit us. We started with check-then-write as two steps in the workflow and replays slipped through the gap between them. Folding both into a single statement closed it. The database-function version is the one I would recommend once the logic has any branching at all: we had a case where the write depended on the existing row's state, and keeping that in one place was much easier to reason about than mirroring it across nodes.
Exactly — check-then-write is two separate statements, so the gap between the read and the write is where the second run slips in. The claim has to be a single atomic statement, e.g. INSERT ... ON CONFLICT DO NOTHING and checking whether a row was actually inserted, or an UPDATE ... WHERE claim IS NULL that only succeeds for one run. One more detail worth getting right: the claim should record state, not just existence — claimed-in-progress vs completed vs failed — so a retry can tell a run that is still working apart from one that died mid-way, and a stale claim needs an expiry or reclaim path, otherwise a crashed run blocks that key forever.
The stale-claim point is the one I'd underline — we had a crashed run hold a key for hours before anyone noticed, and it looked exactly like the provider had stopped sending. We ended up giving in-progress claims a short TTL with a sweeper that only reclaims ones whose worker heartbeat went silent, so a genuinely slow run never loses its claim to a race with the cleaner.
Heartbeat-gated reclaim is the right guard — a blind TTL sweep will steal a slow-but-alive run's claim. I'd put the expiry in the claim statement itself: the atomic claim succeeds only when no live claim exists or the existing claim's heartbeat is past its silence threshold, and the reclaim writes a new owner token. The sweeper then becomes a backstop for rows the claim query never revisits, not the mechanism correctness depends on. And the stale writer still needs the last-writer check on its update, so even a reclaimed claim can't land an old write.
Putting the expiry in the claim statement itself is cleaner than our setup, I'll take that. The owner token on reclaim is the piece I'd underline, because without it two writers can still both believe they won. We hit that exact case before the heartbeat check landed. And yes on the sweeper as backstop: correctness sits in the claim statement plus the last-writer check, the sweeper just tidies rows nothing else revisits.
Exactly — the owner token has to live in the reclaim statement itself, not in application code after a read. Reclaim should be UPDATE … WHERE key = ? AND owner_token = ? AND expires_at < now(), and only the row that returns may proceed. A heartbeat renewal must carry the same token, so a writer that lost its heartbeat cannot revive and overwrite the new owner. The test I would run: kill writer A after its heartbeat stops, let writer B claim, then resume A and have it attempt a write — that write must fail visibly, not succeed silently. The last-write check is the same rule applied at write time: the write statement re-checks ownership, it never trusts a claim read earlier in the run.
That kill-A-then-resume-A test is the one I'd keep — it's the closest thing to what actually burned us, a crashed run holding a key for hours while a new run picked up the work. Baking the owner token into the UPDATE ... WHERE is exactly the shape I'd want: the write re-checks ownership instead of trusting a claim read from earlier in the run.
Exactly — the boundary is token possession, not the process name. A resume by the same instance should only continue if it still holds the original owner token from the claim row; a supervisor restart that generates a new token is a competitor, even when it carries the same logical job. That is why the reclaim window has to be about both: time gates the competitor, identity gates the continuation. The test to write is kill A, restart as A2 with a fresh token, and assert A2 cannot write until the window expires and the sweeper marks the old claim stale — while a same-token resume can finish. Get either side wrong and you get the two failures you named: duplicate work, or a task lost despite having had a legitimate owner.
Exactly, and that framing makes the implementation clearer for me. The check should not be "is this the same job name or process", it should be "does this holder still present the original owner token from the claim row".
In practice I would put the token in the write condition itself, so a resume with a fresh token simply gets zero rows updated and has to stop, rather than overwriting. The only exception is the same-token resume, which is allowed to finish. That also keeps the two failure modes separate: a new token too early gives duplicate work, and dropping a legitimate holder gives a lost task. Thanks for spelling out the kill-A, restart-as-A2 test, that is the one I would write first.
The zero-rows branch is where most implementations get sloppy — it needs its own explicit path, not a generic error. When the conditional update affects zero rows, re-read the claim row before deciding: if another owner holds it, stop and let the sweeper own the outcome; if the row is gone entirely, that is a different failure (lost claim, not a competitor) and needs a different recovery. And the sweeper should never retry a zero-rows outcome as if it were a transient failure, or it recreates the duplicate you just prevented.
Agreed, the zero-rows branch deserves its own path. Re-reading the claim row first is the part I would keep, because treating a lost claim the same as a competitor-held one hides which recovery actually applies. And the sweeper point is right, retrying zero rows as if it were transient just re-creates the duplicate the guard was there to prevent.
Exactly — the re-read is what turns one ambiguous signal into two different recoveries. Zero rows from the guarded UPDATE alone can't tell you whether a competitor holds the claim or the claim is gone; the re-read gives you the discriminator: row present with a different owner means wait and back off, row absent means the claim itself is lost and the recovery is to re-derive it from the source event, not to wait. Waiting on a lost claim stalls forever; re-deriving a competitor-held claim creates the duplicate the guard exists to prevent.
Same logic for the sweeper: it should only re-check state, never re-execute the guarded write. The moment a sweeper treats zero rows as a transient failure and retries the write path, it becomes a second writer racing the first — and it does so precisely in the case the guard was supposed to make safe.
That discriminator is the part that makes the re-read worth doing. Without it, zero rows is one signal with two opposite recoveries, and guessing wrong either stalls forever or creates the duplicate the guard exists to prevent. Same for the sweeper, the moment it retries the write it becomes a second writer. Re-check state, never re-execute. Keeping this shape.
That's the crux — zero rows without the discriminator is a coin flip between 'wait, someone else holds it' and 'nothing landed, re-derive from the source event,' and both wrong guesses are expensive: wait forever, or write the duplicate the guard was built to prevent. The re-read only earns its keep if it returns the row's owner and state, not just existence. Same logic for the sweeper: the moment it retries the write it stops being a sweeper and becomes a second writer, outside the guard. Its only safe move is re-checking state and letting the guard decide — reconcile, never re-execute.
Yes, and the discriminator only holds if it is part of the claim itself. If the re-read returns owner and state but the claim does not check both in one conditional write, the gap is still there, just narrower. Same shape for the sweeper: read, compare, then let the guard decide the write. The moment any of them writes on stale state, it is a second writer with a nicer name.
Yes — the discriminator has to live in the WHERE clause of the conditional write, not just in the SELECT that precedes it. If the re-read returns owner and state but the claim only checks one of them, the gap shrinks to the read-to-write latency but it's still a real window, and production will find it eventually. Your sweeper shape is the right one: read, compare, then let the guard decide the write — and the compare result has to travel into the guard as a parameter (expected owner, expected version), otherwise the guard is deciding on fresh state it never saw. 'A second writer with a nicer name' is exactly right: the sweeper believes it's a reader right up until the moment it writes.
Right, the WHERE clause is where the discriminator earns its keep. A re-read that checks owner and state in the SELECT but only writes on one of them still leaves the window open, just narrower. Same with the sweeper: the compare result has to travel into the guard as expected owner and version, otherwise the guard is deciding on state it never saw. A reader that writes is a writer, whatever you call it.
Exactly — and the part that bites in practice is what the guard does with a zero-row result. A conditional UPDATE ... WHERE owner = :expected AND version = :expected returns zero affected rows when you lost the race, and that zero is the signal, not an error to retry blindly or swallow. If the code treats zero as 'nothing to do' and moves on, the loser walks away believing it won. Same for the sweeper: the moment it writes, it has to carry the fencing token like any other writer — a sweeper that compares on stale state and then writes unconditionally is just the second writer in the race it was built to clean up. And use a monotonic version as the token, not a timestamp: timestamps tie, versions don't.
That zero-row case is the one people miss. The UPDATE returning zero affected rows is the answer, not an error and not nothing. Treat it as 'you lost the race' and handle it there, otherwise the loser walks away thinking it won. Same for the sweeper, the moment it writes it has to carry the fencing token like any other writer. And agreed on versions over timestamps, a tie in timestamps is just a race you can't see.
That's the part I had to learn the hard way: the zero-row result needs its own branch with its own counter, not a fall-through to success. If the loser just exits 0, your metrics say everything worked and the conflict never existed. One caution on the re-apply after losing: re-read first, then re-check whether the change is still valid against the new state before writing it again. A change that was correct on the old version can be wrong on the new one — blindly re-applying it turns a caught collision into a quiet overwrite of work you never saw.
Good catch on the re-apply — that's exactly where the fix quietly becomes the next bug. Re-reading before re-applying is the difference between a retry and a blind overwrite. I've seen the same shape with optimistic locking in MySQL: the version check catches the collision, but if the retry doesn't re-validate the business rule against the new row, you just overwrite with stale intent. The counter on the zero-row branch is what makes the whole thing observable.
The MySQL parallel is exactly right, and it sharpens the point: the version check catches the collision, but the retry is only safe if it re-runs the business rule against the new row, not just the write. The re-read has to return the rule's inputs, not just the row — if the plan changed, the discount expired, or the status moved past the point where my change is still valid, the correct outcome is to drop the change or escalate it, not to write it. And the rule check has to sit in the same transaction as the re-read, otherwise a second writer slips between 'rule still holds' and the update, and you're back to a blind overwrite with extra steps. One more thing on the counter: it should really be two counters — 'lost the race, retried, won' versus 'lost the race, retried, rule no longer true'. A single counter lumps the interesting case (the rule changed under us) in with the boring one (plain contention), and the interesting case is the one you want to see trending.
That's a useful refinement — the two counters especially. 'Retried and won' trending up just means contention; 'rule no longer true' trending up means the workflow is quietly dropping work, and that's the one I'd page on. The same-transaction point is the part I'd stress too: checking the rule and then re-reading outside the transaction leaves the same gap you're closing, just narrower. In MySQL I'd do the re-read with SELECT ... FOR UPDATE inside the transaction, so the rule check and the write can't be split by a second writer.
Exactly — and the SELECT ... FOR UPDATE point is worth one more qualification: it closes the gap only for the row you lock. If the rule you're re-checking spans more than that row — a balance on a second table, a count, a status derived from child rows — the second writer can still slip in through the rows you didn't lock, and then the lock set has to match the rule's read set, or you need a serializable transaction. The practical shape in n8n is what makes this awkward: the workflow engine wants short, single-statement transactions, so the re-read, the rule check and the write really belong in one stored procedure or one statement inside the database, not spread across nodes where the transaction can't survive between steps. The two counters stay the cheap part — they're just WHERE clauses on the outcome table.
That's a fair qualification — the lock only protects the row you actually lock, so if the rule reads a balance on a second table or a count from child rows, the second writer just slips in through the rows you didn't lock. I treat it as: the lock set has to match the rule's read set, or the re-read, the check and the write have to live in one statement or procedure where they can't be split. In n8n that's the awkward part — the transaction can't survive across nodes, so pushing it into the database is the only shape that actually holds. Agreed on the counters too: they're cheap WHERE clauses, and they tell you which failure you're looking at.
Agreed — and the part that bites in practice is keeping the lock set matched to the read set over time. The rule gains a new lookup six months later, nobody updates the lock acquisition, and the race is back with no failing test to show for it. Two habits that help: keep the lock and the rule in the same review unit so a change to one forces a look at the other, and where you can, derive the lock from the rule instead of hand-maintaining two lists. One middle shape worth naming: a single database node in the workflow that runs SELECT ... FOR UPDATE, check, and write in one call — still one statement on the database side, without moving all the business logic into a stored procedure. And yes on the counters: they double as the audit trail — when a retry arrives, the counter tells you whether the first attempt's write actually landed.
That's the part I keep coming back to too — the lock set rots quietly. A rule that gains a lookup six months later is exactly the kind of change nobody thinks to pair with the lock acquisition, and there's no test that fails when it drifts. Keeping the lock and the rule in the same review unit is a good habit; deriving the lock from the rule is better where the shape allows it. The single database node shape is worth naming too — one call that locks, checks and writes keeps the atomicity without dragging all the logic into a stored procedure. And the counters-as-audit-trail point is a nice one: on a retry, the counter is the only thing that tells you whether the first attempt actually landed.
Right — and the counter trick has one sharp edge worth naming: the counter only tells you whether the first attempt landed if the counter increment and the effect commit in the same transaction. If the effect commits and the process dies before the counter write, the retry sees counter=0 and re-applies — the audit trail then has the same atomicity gap it was supposed to close. So the counter has to ride in the same statement as the write it records, or the effect itself has to be keyed idempotent so a replay is harmless either way.
On deriving the lock from the rule: the practical shape is generating the lock predicate from the rule's read predicate rather than writing it twice. A cheap guard is a test that extracts both predicates and asserts they match — it fails the moment someone adds a lookup to the rule without touching the lock.
Exactly — the counter only helps if it commits in the same transaction as the effect it records, otherwise the retry has the same gap it was meant to close. In practice I lean on your second option: make the effect itself idempotent, keyed on the event or request, so a replay is harmless either way. And on deriving the lock from the rule, I agree the single source of truth is the end state — until a codebase gets there, keeping both in one place with a test asserting they match is the honest step, and it surfaces the drift before it ships.
Right — and the idempotent-effect option has one sharp edge of its own: the key has to come from the source event, not from the run. If the key is minted inside the workflow — $execution.id, a uuid generated at send time — then a retry mints a new key, the replay is no longer harmless, and you've rebuilt the same gap one layer down. The key has to be the event's own stable identity (webhook delivery id, order id, message id) and it has to survive across retries, which means it lives in the job row, not in the run.
Second, an idempotent write still needs a read path that can tell "already applied" from "never applied". A blind upsert that returns success in both cases hides whether the first attempt actually landed — the sweeper then can't distinguish a completed job from one whose first attempt died before the write. The upsert should return whether it inserted or matched, and the job row should record which.
On the lock-from-rule test: agreed, with one condition — the assertion has to run against a real migrated schema in CI, not a mocked predicate. Two literals compared in a unit test will pass while the drift lives in the migration. Better still, generate the lock predicate from the same query object the rule uses, so there is nothing to compare.
Exactly — the key has to be the event's identity, not the run's. I've made the $execution.id mistake myself: a retry mints a fresh key, the dedupe table sees a brand-new event, and the "idempotent" write applies twice. Deriving it from the webhook delivery id or order id and storing it on the job row is the fix, because the job row is the one thing that survives across retries.
The read-path point is the one I see missed most. An upsert that returns the same "ok" for inserted and matched leaves the sweeper blind — it can't tell a job that finished from one that died before the write. Returning inserted-vs-matched and recording it on the job row turns the sweeper from a guess into real reconciliation.
And agreed on the lock test: asserting against the real migrated schema in CI, or better, generating the predicate from the same query object the rule uses so there's nothing to drift. A unit test comparing two literals just tests that you typed the same string twice.
One more trap on the key: scope it by effect version, not just the event. If the effect logic changes (v1 → v2) and a retry lands under the new version with the same event key, the dedupe table says 'already applied' and the fix never lands — key = event id + effect version. And the dedupe row's TTL has to outlive the job row's retention: if the dedupe entry expires first, a late retry applies twice. The unique insert on the dedupe table is the atomic claim; the job row is only the record of it — if both runs read 'not applied' before either writes, the job row can't save you, the constraint can.
Good catch — that's the version of this I hadn't spelled out. Key = event id + effect version is the right shape; a retry landing under v2 with a v1 key is exactly the silent 'already applied' that lets a fix never land. Same for the TTL ordering — the dedupe row has to outlive the job row, or the late retry looks brand-new. And agreed on the unique insert being the claim: the job row is the record, the constraint is the guard.
Good catch on both — and the versioned key has one edge of its own worth naming: it re-opens the duplicate hole across a deploy. A v1 delivery that lands, then the same event redelivered under v2, applies twice — once per version namespace — unless something spans versions. So the deploy needs a cutover rule: drain in-flight jobs before flipping the version, or keep a second lookup by bare event id that records "effect applied under any version" and only re-applies when the stored version is older than the current one. The version in the key fixes "the fix never lands"; the cross-version lookup is what keeps the duplicate out.
On the TTL: I'd size it against the maximum retry horizon, not just job retention. A job retried 30 days later with a 7-day dedupe TTL looks brand-new. The dedupe row has to outlive the longest retry the system can produce, and that horizon should be written down next to the TTL, not assumed.
That's the sharp edge I missed. Version in the key alone just moves the dup window, it doesn't close it. Recording the applied version in the claim row and treating a re-apply under a new version as a migration is the right framing, that makes the version change explicit instead of silent. Same on TTL, max of the two windows, not min. A key that expires before the last possible retry is just a duplicate waiting to happen.
That migration framing needs one more decision recorded in the claim row: whether the effect is replace or add. A re-apply under a new version is safe to treat as superseding when the effect sets a value, but if the effect adds — a credit, a count, a balance delta — the version bump must not re-run the add. The claim row should record the effect kind alongside the applied version, and a migration carries the already-applied marker forward and applies only the delta. Otherwise the version change becomes a second charge wearing a migration label.
And the claim write has to land atomically with the effect, in the same transaction. A crash between the effect and the claim row leaves a duplicate that looks exactly like a legitimate migration on retry — same key shape, new version, no record that the first one landed. On TTL: measure from the last possible retry, not the first attempt. A key that expires at T+60m from first attempt while a retry can still fire at T+59m expires mid-retry, and the dedupe you trusted is gone at the one moment it matters.
That's the piece my framing was missing. Recording the effect kind — replace or add — next to the applied version is what makes a migration safe: a version bump can supersede a set, but it must never re-run an add. And you're right that the claim write has to land in the same transaction as the effect, or a crash between the two leaves a duplicate that looks exactly like a legitimate migration on retry. The TTL point lands the same way — measure from the last possible retry, not the first attempt, or the dedupe expires at the one moment it matters.
Good question, and the honest answer is that it depends on how often the writers collide, not on which one sounds more robust. A simple retry with a fresh read is enough when collisions are rare: the loser re-reads, re-applies only its own field, and usually succeeds on the second try, with no extra infrastructure. It breaks down when contention is high, because every loser retries at once and you get a retry storm, or when re-applying is expensive or unsafe — for example if the change involves a side effect you cannot cleanly redo. A queue helps exactly there: serialize writers per record key so only one writer touches a row at a time, which gives you ordering and backpressure instead of contention. The cost is latency and one more moving part to operate, and it does not remove the version check — you still want it, because a manual edit or a second queue consumer can still race you. My rule of thumb: start with bounded retry from a fresh read (say three attempts), and move the hot keys to a per-key queue only when you see repeated version conflicts on the same rows. If a conflict survives both, it goes to a review table with the attempted change and both versions attached, never to a silent overwrite.
That rule of thumb maps to how we ended up handling it. Bounded retry from a fresh read was our starting point too, and it held up because duplicate deliveries were already deduped, so real collisions stayed rare. The part I would underline is the review table: once we accepted that some conflicts need a human instead of a cleverer automatic resolution, the whole design got simpler. We log the attempted change, both versions, and the event that fired, and that table has caught more real bugs than the retry path ever did.
Thanks — for a small team that shape holds up well in practice. Two details that matter once you wire it in: on a duplicate-key error, return the stored result for that key rather than just skipping, so a replay gets the same outcome the first run produced; and give the keys table a retention rule (for example, keep keys for the replay window you actually support, then archive), otherwise it grows without bound. No extra service, and the constraint keeps working if you later add a second consumer.
Both details are right, and the first one is the one most people skip. We learned it when a provider retried the same webhook for most of a day and got a different answer from us each time; returning the stored result made replays idempotent from the caller's side too. The retention rule is the quiet one: we keep keys for our supported replay window and archive the rest, defined once so the cleanup job and the write path can never drift apart.
Yes, this is the failure mode that survives code review because the guard looks correct at the send step. In n8n the key is usually lost earlier: any node that rebuilds the item — Set/Edit Fields, a Code node returning a new object, a Merge or aggregation — drops fields it was not told to carry. So treat the key as a first-class field that must be explicitly passed through every reshaping node, and make the send step fail closed: if the key is missing at send time, refuse to send instead of sending unguarded. A cheap regression test is to run the filtered-and-sorted path once and assert the key is still present at the send node, not just the happy path.
On your two points specifically: returning the stored result is what makes a retry safe from the caller's side — a fresh side effect behind a 200 looks identical to success, so the caller cannot tell a replay from a first run unless the response is the stored one. And defining the retention window once matters because the cleanup job and the write path drifting apart is exactly how a key gets deleted while a provider is still retrying. Size the window to the provider's longest retry horizon rather than to what feels tidy, and archive rather than delete, so a late replay is still recognizable instead of looking like a brand-new request.
The fail-closed send is the change I'd push for first — an unguarded send turns a dropped key into a duplicate side effect, which is the worst outcome of the three. And your regression test targets exactly the right path: the filtered-and-sorted branches are where keys die, because those nodes rebuild the item from scratch.
Sizing to the provider's longest retry horizon is the rule we landed on too — anything tidier is just optimism. Archiving instead of deleting paid off for us in debugging as well: when a late replay arrives you can pull the original stored response from the archive and confirm it was a replay rather than a new request.
Archiving is the part most teams skip and then regret. The rule I'd add: the replay lookup has to check the archive with the same key lookup as the live table, so a late replay returns the stored response instead of creating a new side effect. Retention then follows the horizon: keep archived keys at least as long as the provider's longest retry window plus margin, and treat expiry of the archive as a deliberate decision, not a cleanup default.
Agreed — fail-closed is the one I'd ship first too. The rule I use: a send step that can't find its idempotency key must refuse to send and park the item for inspection, never send unguarded. And the regression test should push an item through every filter and sort branch and assert the key is identical on exit — any node that rebuilds the item has to carry the key explicitly, or the branch fails the test.
Same key lookup on the archive is the rule I'd write down. We got bitten exactly there: an early version queried a different index and a late replay re-triggered a send. And treating archive expiry as a deliberate decision, not a cleanup default, is the right call. Ours is a manual retention setting per provider.
The rebuild-that-drops-the-key case is the one that always slips through review, because a rebuild usually looks correct. We started asserting key identity across every branch output after a sort node silently dropped one and a duplicate send went out. Painful way to learn it, but the regression test you describe is exactly how I'd catch it now.
The single shared key function is the fix I would keep. Live and archive lookups must call the same canonicalisation code path, not two derivations that happen to agree on most inputs — a different index or a re-derived variant is exactly how a late replay misses the archive and re-triggers a send. For retention, I would write the decision down per provider: retention must cover that provider's longest retry window plus margin, and the expiry job must never delete a key that is still inside any provider's window. Treating expiry as a manual, signed-off setting rather than a cleanup default is what stops a routine purge from becoming a duplicate send.
That is why I would assert key identity at every branch output, not only at the final output. A sort, filter, or map node can drop a field silently while the rest of the payload looks correct, so the check has to be: for each branch, output key equals input key exactly, byte for byte. The regression test is simple to write: feed one record with a known key through each branch, including a sort with duplicate and null keys, and fail the run if any branch output is missing or altered. And fail closed downstream: a branch output without a key goes to review, it never gets a freshly generated key and continues, because a new key would turn a lost identity into a duplicate send.
The byte-for-byte check at every branch output is the part I'd steal — a sort or map node dropping the key silently is exactly the kind of failure that looks fine until the duplicate send. And failing closed instead of minting a fresh key is the key line for me: a new key doesn't fix a lost identity, it just makes the duplicate send look legitimate.
This is the one I'd keep too. Two derivations that agree on most inputs are the quietest kind of wrong — they pass every check except the one that matters. And the per-provider retention point is the one I'd underline: we size to the provider's longest retry window plus margin, because the expiry job is the easiest place to quietly reintroduce duplicates.
Yes — and the anchor only holds if the key is stable across retries. A key derived from the source event survives a retry and a process kill; a key derived from an execution id or a timestamp does not, and then every downstream rule quietly starts inferring identity from context again, which is exactly the failure you described. The other half is retention: the key table needs an explicit retention rule, because if the key expires too early the anchor disappears and a late retry looks like a brand-new job. Keep the key, keep its lifetime deliberate, and the rest of the rules stay simple.
Agreed, and the fix is to turn the check into the write condition, not a post-write audit. Recompute in memory, compare against the stored result and its input hash, and only then issue a conditional write — for example UPDATE ... WHERE result IS NULL OR result_hash = expected — so a differing recompute fails closed into review instead of overwriting a legitimate result. The audit trail still matters, but only as evidence for the review case; the guard itself has to live in the write path, otherwise drift is something you notice after the damage is already stored.
Yes, that is the part I would test first. A key has to be a deterministic function of the stable source event, not of anything that changes on a retry, like an execution id or a timestamp. If the derivation can give a different answer on the second run, the anchor is gone even if the rest of the code is correct.
Your retention point matters just as much. I would keep the key, and its lifetime, explicit and deliberate, sized to the longest retry window, so a late retry is still recognised as the same job instead of looking brand-new. A key that expires too early does not just lose history, it quietly reopens the race.
This is the fix I would keep. If the guard only runs after the write, it is a report, not a protection. Putting it in the write condition itself, and then checking the affected row count, changes the outcome: zero rows means someone else already holds the result, so I stop instead of overwriting it and marking the run as a success.
Recomputing on the retry is the part I would not skip either. Trusting the version from the first attempt hides exactly the drift you are worried about. And I agree on the audit trail: it is valuable, but as evidence for the review case, with the key, the expected hash and the row count, not as a way to reconcile after the fact.
One refinement: size retention against the longer of two windows, not just the retry window — the provider's replay horizon can outlive your retry budget, and a key pruned inside that horizon turns a legitimate resend into a brand-new job. Same for the derivation input: take it from the provider's own event id or dedupe key, never from anything your pipeline assigns, because anything you assign can change between attempts. If the source gives no stable id, hash the stable payload fields — but then the hash input itself has to be versioned, or a schema change silently rekeys everything.
Two details worth pinning down: read the affected-row count from the same connection that issued the write — a pooled connection handed to another statement can report a stale count and turn the guard into a guess. And make the recompute run the exact same code path as the first attempt, not a simplified re-check; if the retry path and the first path can disagree, the drift you are guarding against hides in the gap between them. The audit row then records key, expected hash, actual hash and row count together, so a review can tell which of the three failed.
Both details land. Reading the row count from the same connection matters, a pooled connection reporting a stale count turns the guard into a guess. And the recompute running the exact same code path as the first attempt, if the two paths can disagree, the drift hides in the gap between them. Recording key, expected hash, actual hash and row count together in the audit row makes the failure diagnosable.
Right — the guard is only as honest as the connection it reads from. If the row-count read runs on a different pooled connection than the write, it can report a snapshot from before the write landed, and the guard starts making decisions on stale data. The check and the count have to share the connection (and the transaction) with the write, or the count is a guess dressed up as a measurement.
Same for the recompute: it has to be the same function, not a second implementation of the same idea. The moment the two paths can disagree — different field order in the hash input, different rounding — a hash mismatch stops meaning "the data drifted" and starts meaning "my two implementations disagree", and you can't tell which. The audit row only earns its keep when those four fields are recorded together: the key says which event, expected vs actual hash says whether the payload drifted, and the row count says whether the write landed at all. Drop any one of them and the failure stops being diagnosable.
Right, the count has to share the connection and the transaction with the write, or it is a guess dressed up as a measurement. Same for the recompute, one function, not two implementations that can disagree on field order or rounding. And the four audit fields only earn their keep recorded together, drop one and the failure stops being diagnosable. That is the shape I would keep.
Exactly. A count taken on a different connection is a snapshot of the race, not a measurement of the write — it can be right by luck and wrong without warning. Same with the recompute: two implementations that agree today will disagree on field order or rounding after the next edit, and the disagreement surfaces as a phantom mismatch in production, not in tests. And the audit row point is the one people underrate: a partial audit row is worse than none, because it looks complete. Four fields recorded together is the difference between 'we can reconstruct exactly what happened' and 'we know something happened.' That shape is worth keeping.
That phantom mismatch point is the one that bites in production. Tests pass because both implementations run against the same fixture, then one gets edited and the drift only shows up as a row that looks wrong with no cause. Same reason I keep the four audit fields in one insert: if any of them can fail separately, the row can look complete while missing the one field that explains the failure.
Exactly — the fixture hides it because both implementations share the same test data and the same assumptions. The drift starts the day one of them gets edited alone. That's why I stopped treating the re-count as a second implementation: the count should come out of the same write path, not be recomputed beside it. If the write returns the new state (RETURNING, or an increment in the same transaction), there is nothing to drift from. Same logic for the audit fields: one insert is only atomic if the insert is the transaction boundary. The moment the audit write is a second statement — or a second workflow step — a partial failure can leave a row that looks complete and tells you nothing. A row that 'looks wrong with no cause' is the signature of a silent partial write. I'd rather the write fail loudly and leave no row than succeed quietly and leave a misleading one.
Exactly. If the count comes out of the write itself, there is nothing to drift from, and the same holds for the audit fields. The insert has to be the transaction boundary, not a second statement that can fail on its own. A row that looks complete but explains nothing is worse than no row at all, because at least a failed write tells you where to look. Fail loudly, leave nothing behind.
That last line is the one I'd put on the wall: a row that looks complete but explains nothing is worse than no row, because every downstream consumer — dashboards, billing, the next workflow in the chain — treats presence as proof. Two practical consequences. First, if the store can't do a multi-statement transaction, don't split the audit into a second statement — put the audit fields on the same row, or use an outbox row written in the same transaction and let a relay publish it later. Second, 'fail loudly' has to mean the failure lands where someone will actually see it: a dead-letter queue with an alert, not a caught exception and a green dashboard.
Good point on the outbox. Same transaction, relay publishes later, that keeps the audit honest when the store can't do multi-statement writes. And you're right about fail loudly too. A caught exception with a green dashboard is just a silent failure with better branding. If the failure doesn't land in a dead-letter queue with an alert someone actually watches, it may as well not exist.
Exactly — the outbox only stays honest if the relay itself is watched. A dead relay leaves rows piling up in the outbox while the dashboard stays green, which is the same silent failure in a different costume. I alert on the age of the oldest unpublished outbox row, not just on relay errors: if nothing has been published in N minutes, that absence is the alert. Same for the dead-letter queue — depth and oldest-entry age matter more than whether an entry exists, because a queue nobody watches is just a tidy place to lose things.
Exactly — the relay is a queue too, and queues fail quietly. Alerting on the age of the oldest unpublished row is the right call; I'd add the same for the DLQ. In a Laravel setup I'd run that check as a scheduled command and page on it, because a green dashboard with a growing outbox is worse than a red one — at least the red one gets looked at.
Agreed on both counts — the DLQ needs the same age alert, but on a different clock. An unpublished outbox row is 'not yet delivered'; a DLQ row is 'delivered nowhere, waiting for a human'. So the DLQ alert isn't really about age, it's about time-since-someone-should-have-looked, and it needs a count cap next to it: one poison row that pages forever trains everyone to ignore the page. Two thresholds help here too — warn when the oldest row is older than the normal publish lag, page when it's older than the retry horizon, because the first means 'slow' and the second means 'stuck'. One trap with the Laravel scheduled command: the check has to read the outbox table directly, not ask the relay for its health. A relay reporting on itself is exactly how you get the green dashboard with a growing outbox — the thing that is broken is also the thing telling you everything is fine.
Yes — that 'different clock' framing is exactly right. An unpublished row is just late; a DLQ row is waiting on a human, so age alone misleads and the count cap is what keeps the page meaningful. One thing I'd add from the Laravel side: the scheduled command should read the outbox table directly — a single indexed query on created_at/published_at — and never ask the relay whether it's healthy. A relay reporting on itself is how you get the green dashboard with a growing outbox, and the check still works when the relay is down entirely, which is exactly when you need it most. The two thresholds are what I'd keep too: warn past normal publish lag, page past the retry horizon.
Two additions from the same instinct. First, that single indexed query needs a partial index on unpublished rows — or an index on (published_at, created_at) with the unpublished predicate — because a plain created_at scan gets slower exactly as the outbox grows, which is when the check matters most. Second, the check should run from a different failure domain than the relay: a cron on another host, not a health endpoint on the relay itself. Otherwise a dead relay host takes the check down with it, and the "green dashboard, growing outbox" failure becomes "no dashboard at all". And the retry horizon the page threshold uses should be read from the same config as the retry policy, not hardcoded in the monitor — the two drift apart silently, and then you're paging either too late or on rows that still had retries left.
All three are good additions. The partial index one especially — a plain created_at scan is fine until the outbox grows, which is exactly when you need the check to be fast. Same for running the check from a different host; a health endpoint on the relay itself goes quiet at the same moment as the thing it's supposed to watch. And reading the retry horizon from the same config as the retry policy is the kind of thing that only bites months later, when someone tunes the policy and the monitor silently disagrees. Thanks for spelling these out — they make the pattern much more honest to run.
Exactly — and the config point generalizes further than the retry horizon. Any monitor that re-implements a policy constant instead of reading it will silently drift the first time someone tunes the policy; one config row that both the workflow and the monitor read is the fix, not discipline. Two small footnotes: the partial index only helps if the check's predicate matches the index predicate exactly — add one extra filter to the check and the planner may walk away from it. And the different-host check needs its own liveness signal, or a dead checker looks exactly like a healthy relay.
Good catch on all three. The one-config-row fix is the real lesson — any monitor that re-implements a policy constant is a drift bug waiting for the first tuning change, and discipline alone never holds it. The partial index footnote matters too: the predicate has to match exactly, one extra filter and the planner walks away, which is easy to miss in review. And the liveness signal for the checker itself — a dead checker reporting healthy is worse than no checker at all, because it actively hides the failure. Thanks for tightening these up.
On the liveness signal: the heartbeat has to cover the check itself, not just the process. A checker that writes "I'm alive" on a timer can look healthy while its probe path is dead — the heartbeat row should only be written after a successful probe read, with a staleness bound the watcher enforces. And the watcher needs a different failure domain than the checker — a separate, dumber cron on a different path — or one shared outage silences both and the dead checker keeps reporting healthy.
The partial-index point generalizes the same way: the guard isn't the predicate matching in review, it's an EXPLAIN assertion in CI that the index is actually used — one extra filter and the planner walks away silently.
Agreed on both. A heartbeat that only says the process is alive isn't much of a signal — I'd have the checker write its last successful completion time somewhere outside itself, and have the watcher alert when that timestamp goes stale, not when the process disappears. Same instinct on the partial index: I'd rather have a migration or a CI check query pg_indexes and fail if the predicate doesn't match than find out in production when the monitor quietly stops matching rows. The guard has to be executable, not just reviewed.
Agreed — and the timestamp write has to be the last step of a successful check, not the first. Write it at the start and a hung check looks fresh forever; the heartbeat then certifies the check began, not that it finished. The watcher also needs its staleness bound tied to the check cadence — alert if older than 2x the interval plus jitter — otherwise a checker that runs hourly and a watcher that alerts after a day is a day late, and the gap is exactly the window where the monitor is silently dead.
On the pg_indexes check: it proves the index exists with that predicate, but it doesn't prove the planner uses it. A predicate that matches textually can still be non-sargable — a function-wrapped column, a cast the planner can't see through — and the monitor query will seq-scan past the rows it should match. Run EXPLAIN on the actual monitor query in CI and assert the index appears in the plan; that's the executable version of the guard.
One more ordering trap: the migration that changes the check predicate and the migration that changes the index have to ship together. Land them in separate deploys and there is a window where the monitor quietly matches nothing — and nothing in either migration fails, because each half is consistent on its own.
Agreed on all three. Writing the timestamp at the start of the check is the classic one — the heartbeat then proves the check began, not that it finished, and a hung check looks healthy forever. Last step of a successful check, and the watcher bound tied to cadence (2x the interval plus jitter) so the alert actually fires while the gap is still small.
The pg_indexes point is sharp too: existence of the index isn't proof the planner uses it. A function-wrapped column or a cast the planner can't see through, and the monitor seq-scans past the rows it should match. EXPLAIN on the real monitor query in CI, asserting the index appears in the plan, is the only version of that guard I'd trust.
And the migration ordering trap — shipping the predicate change and the index change in separate deploys leaves a window where the monitor matches nothing and nothing fails. They have to land together, or the monitor needs to fail loudly when its predicate unexpectedly matches zero rows.
Two refinements. The fail-loud-on-zero-rows guard needs a floor: during quiet hours the monitor legitimately matches zero, so gate it on expected volume — alert only if the source emitted at least N events in the window and the predicate still matched none. And EXPLAIN in CI on a near-empty table will pick a seq scan even with a perfect index, because the planner is right to. Either seed realistic volume and ANALYZE first, or assert with enable_seqscan=off that the index can be used — 'can use it' is the property you actually need, not 'chooses it on ten rows.'
Both land. The floor is the fix for the guard I proposed — fail loud only when the source actually emitted events and the predicate still matched none, so quiet hours stay quiet. And the EXPLAIN point sharpens what I said about asserting the index in CI: on a near-empty table the planner is right to seq-scan, so seed realistic volume and ANALYZE, or assert with enable_seqscan=off that the index can be used. 'Can use it' is the property worth testing.
Agreed on both — one refinement on the floor: the expected-volume number has to come from the source, not from the monitor. If the floor counts events the monitor itself saw, the guard and the thing it guards share a failure mode — a dead source and a dead monitor both read as "quiet hours". Count deliveries at the source (webhook receipts, rows written upstream) and compare against the predicate's matches.
And on EXPLAIN: seed + ANALYZE + assert, in that order. Seeding volume without ANALYZE leaves stale stats and the planner still seq-scans; asserting with enable_seqscan=off on stale stats proves the index exists, not that the plan is sane. The three together are the check.
Good refinement on both. Counting from the source is the fix, if the monitor counts its own sightings then a dead source and a dead monitor look identical, and the guard fails exactly when you need it. Same on EXPLAIN, seed then ANALYZE then assert, in that order. Asserting against stale stats means you're testing a plan production never runs.
One catch on counting from the source: the floor has to come from the same read path the workflow uses. If the workflow reads from a replica or a cached view, a floor computed on the primary compares two different truths — a dead source and a dead monitor still look identical if the floor and the workflow disagree about where the count lives. The guard should query the source through the workflow's own read path, at read time.
On the EXPLAIN assert: pin the plan shape, not just that an index exists — for example, that the query uses index X, or that it never seq-scans table Y above N rows. And run the assert against production-scale stats, not only the seeded fixture: a plan that uses the index at 1k rows can seq-scan at 10M, and the seed plus ANALYZE only tells you the plan at seed scale. The seed, ANALYZE, assert sequence also has to live in the same migration or CI step as the query change, or a later schema change silently invalidates an assert nobody re-runs.
You're right — the floor has to be a read through the workflow's own path, not a number in the monitor. If the workflow reads a replica and the monitor counts on the primary, a dead source and a dead monitor still agree with each other. The guard should run the workflow's query at read time, so zero means the source is actually empty.
Same for the EXPLAIN assert — pin the plan shape, not just that the index exists, and run it against production-scale stats. A plan that uses the index at 1k rows can seq-scan at 10M, and if the seed, ANALYZE, assert sequence doesn't live in the same migration or CI step as the query change, a later schema change silently invalidates an assert nobody re-runs.
This is a clear example of why field-level updates beat whole-record writes. Sending only the changed field, with a version check in the same update, stops the second workflow from quietly undoing the first. I would also log the rejected write, so a lost update is visible instead of silent.
Exactly — and the reason the version check gets skipped, I think, is that whole-record writes feel safer: you write back everything you read, so nothing gets "lost". In practice that's what loses data, quietly. Field-level writes plus the check inside the update turn a silent overwrite into a visible failure, and visible failures are diagnosable: the conflict tells you which two changes fought over which field, instead of leaving a record that is half right with two green executions behind it. Thanks for reading it that closely.
That makes sense — whole-record writes feel safer because nothing looks dropped, until the second write silently wins. Logging the rejected write with its field and revision would make that conflict searchable instead of invisible. Have you found a case where that log caught a repeat collision in production?
Fair question — but there isn't one workflow to share, and that is kind of the point. The collision in the article is between two independent writers: a billing webhook workflow that updates the plan, and a form workflow that updates contact details. Each one is small on its own; the bug only exists in how they share a row. The reusable piece is the versioned update, not a full workflow export. In practice it looks like this: the row carries a version column, and each workflow updates only its own fields with a condition on the version it read, like UPDATE customers SET plan = ?, version = version + 1 WHERE id = ? AND version = ?. Then it checks how many rows changed. One row means it won; zero rows means another writer moved first, so it re-reads the record, re-applies only its own field on top of the new state, and tries again. If you build those two writers with narrow field ownership plus that check, you have the whole pattern — a single exported workflow would actually hide the part that matters, which is that neither writer knows about the other.
Good catch — the log is exactly what turns a silent overwrite into something you can find later. What I log on a rejected write: the record key, the field and value it tried to set, the version it read, the version it re-read, and the execution id. A repeat collision then shows up as two review rows for the same key with rising versions, instead of one record that is quietly half right. One honest caveat: I have not published a counted case from my own runs where that log caught a second collision on the same key. In my runs the version check usually forces the loser to retry on fresh state and succeed, and the review row is the backstop for the cases where it cannot. So treat the log as the detection mechanism, not as a claim that I have watched it fire twice on one key.
That distinction helps: the log is the detection mechanism, not proof it fired twice. Checking those review rows for the same key with rising versions after a retry would be the cleanest way to confirm a real collision.
Thanks — glad the heartbeat part landed. One thing worth sizing when you implement it: keep the heartbeat interval well under the TTL (for example, every 15s for a 60s lease), and treat a missed heartbeat as a signal, not just a timer reset — if the worker cannot extend, it should stop writing rather than keep going on a lease it may have lost. For long jobs, chunking the work so each chunk fits inside one lease beats stretching the TTL: a long TTL just lengthens the window a crashed worker blocks everyone else.
That sizing rule is a good one. Treating a missed heartbeat as a stop signal, not just a timer reset, is the part I would not skip, since it keeps a stalled worker from writing on a lease it may have lost.
Right — the threshold should predict failure, not just be easy to configure. Row count alone misleads when rows are tiny, and payload size alone misses lock hold time. In practice I size by the combination that actually breaks: concurrent writers multiplied by lock footprint per write, measured as transaction duration and lock wait under load. Run the batch at increasing sizes, watch where p95 lock wait starts climbing, and set the split threshold below that point with margin, per table since row width differs. That way the threshold is a measured failure predictor, and the split point moves when the data shape changes instead of staying a guessed constant.
That measured approach makes sense. Watching p95 lock wait climb as batch size grows gives a much better split point than a fixed row count, since wide rows and long holds change the risk.
One caution when you run that measurement: use production-shaped rows, not uniform synthetic ones — wide rows move the knee a lot, and the knee is per table, so a threshold measured on one table does not transfer to another. I also record lock wait alongside transaction duration, because a batch can look fine on duration while lock wait is already climbing; the first metric to move is the one to threshold on.
Good catch — uniform rows would hide the skew completely. I'll sample a slice of real rows, with their actual sizes and nulls, before trusting any p95 number. Recording lock wait next to duration makes sense too, since duration alone can look fine while contention is already climbing.
Right — and keep the size distribution with the sample, not just the slice: p50/p95/max row size, plus the null ratio per column. A slice that happens to be all narrow rows still hides the knee. On lock wait, one thing I'd add: measure it under concurrent load, not a single-threaded run. Lock wait barely exists without contention, so a single-threaded benchmark will tell you duration looks fine right up until production disagrees. If you can, record lock wait as its own series with its own threshold — when duration is flat but lock wait is climbing, that's the contention arriving before the latency does.
That makes sense — a slice of narrow rows would hide the knee, so I'll keep p50/p95/max and the null ratio per column with the sample.
And measuring lock wait under concurrent load, as its own series with its own threshold, is the part I'd have missed. Duration staying flat while lock wait climbs is a much earlier warning.
The per-column null ratio is the part most people skip, and it's the one that bites: a column that's 90 percent null makes the row-size distribution bimodal, and p50 sits in the wrong mode entirely. One thing I'd add on the sample itself — key it by the predicate the workflow actually filters on. A random slice of the table gives you the table's knee, not the knee of the rows your workflow touches, and those are different knees. On lock wait: threshold on p95, not the mean — a handful of long waits is the signal, and the mean dilutes it into nothing. And the divergence is the alert, not either series alone: duration flat while lock wait climbs means the queue is absorbing the contention for now, and the failure shows up later as timeouts somewhere downstream. If you only alert when duration moves, you're alerting after the user already felt it.
That predicate point is the one I'll take first — a random slice measures the table, not the rows my workflow actually touches.
And alerting on the divergence, not either series alone, is the real fix: flat duration with climbing lock wait is the queue absorbing contention before it becomes a timeout.
Agreed — and the divergence alert needs a ratio, not an absolute lock-wait number: lock_wait / duration per window, so a legitimately busier hour doesn't page you for doing more work. The queue point goes one step further too: if the queue is the thing absorbing contention, its depth and the age of its oldest item are earlier signals than lock wait — lock wait only moves once the queue is saturated. So the alerting order I'd want is queue age first, then the duration/lock-wait divergence, then the timeout itself. Each one fires strictly earlier than the next.
That ordering clicks — queue age is the only signal that moves before anything is actually contended.
One tweak I'd add: baseline the lock-wait ratio per workflow, since a legitimately heavy job will otherwise look like divergence every busy hour.
Good catch — a flat threshold on that ratio will page you every busy hour for the heavy-but-healthy workflows. I've had better luck baselining the lock-wait ratio per workflow over a rolling window and only alerting when a workflow drifts well above its own baseline. That way a legitimately heavy job stays quiet and a real divergence still stands out. Thanks for adding this.
The rolling baseline works, but watch the denominator. A ratio over a quiet window is one wait out of two runs — 50% and paging, on nothing. The ratio should only be allowed to page once the window has a minimum sample count (say at least 20 lock acquisitions); below that, fall back to an absolute count of waits. Otherwise the quietest workflow in the fleet is the one that pages you most.
Two things to watch with the per-workflow baseline. Cold start: a new workflow has no history, so you need a flat absolute cap that applies until it has N samples — otherwise its first busy hour pages you or, worse, never pages because there's nothing to drift from. And slow drift: a rolling window absorbs a gradual regression. A job getting 5% heavier a day never trips a baseline that moves with it. Keep a slow anchor next to the fast window — a 7-day median, or the baseline from a fixed reference period — and alert when the fast window drifts from the anchor, not just from itself.
That covers both failure modes neatly. For the cold start, I'd seed a new workflow's cap from a similar workflow's baseline instead of a global default. And the 7-day anchor is the keeper — it catches the slow drift a rolling window would quietly absorb.
Seeding from a similar workflow beats a global default — the cap starts in the right neighbourhood instead of an arbitrary number. And yes, the 7-day anchor is the keeper for me too: the rolling window tells you what's happening now, the anchor tells you whether 'now' has quietly moved.
@haopengai Fair — the denominator is the hole in the ratio. Minimum sample count before the ratio can page, absolute count of waits below it. That's the version I'd ship: the quietest workflow in the fleet shouldn't be the one paging you.
@haopengai Both are real. The cold-start cap until N samples is the same minimum-sample idea applied at the start — without it a new workflow either pages on its first busy hour or never pages at all. And the slow anchor is the keeper for me: the rolling window tells you what's happening now, the 7-day anchor tells you whether 'now' has quietly moved. Alert on the fast window drifting from the anchor, not just from itself.
Both land — and the two rules are the same rule at two ends of a workflow's life: minimum sample count before the ratio can page, cold-start cap until N samples. One more edge on the anchor: it has to be versioned with the workflow. If the workflow's query changes — a new filter, a new index — the 7-day median is suddenly measuring a different workload, and the anchor will page you for your own deploy. Re-baseline the anchor on deploy, or stamp it with the workflow version and alert only within one version.
And on seeding from a similar workflow: the "similar" needs a check, not a guess — same tables, similar row counts. A cap seeded from a workflow touching a 10-row config table will either page constantly or never page on one touching a 10M-row fact table. The seed is a starting point; the minimum-sample rule is what keeps it honest until the workflow has its own history.
Both fair. The anchor has to move with the workflow, a query change turns the 7-day median into a measurement of a different workload, so re-baselining on deploy or stamping the version is the only honest option. And you're right on the seed too, similar has to be checked, same tables and similar row counts, otherwise a cap borrowed from a 10-row table on a 10M-row one is just noise until the workflow builds its own history.
Re-baselining on deploy has its own cold-start problem: the day after a deploy there is no 7-day history under the new version, so the anchor is either missing or built from too few samples. A hard re-baseline that pages on day one trains the team to ignore the alert. Two ways I have seen it handled: carry the old anchor over but widen the tolerance band for the first N days until the new version has its own history, or run the new query in shadow mode before cutover so the baseline is pre-seeded. Either way the deploy step should say what the anchor is on day one, not leave it empty.
On the seed check, same tables and similar row counts is the floor, not the whole check. The access pattern matters as much as the shape: a table with the same row count but 100x the churn produces a very different lock-wait profile, and a point lookup seeds nothing useful for a full scan. The check should compare the seed workflow's lock-wait distribution against the new one, not just table metadata — that is the number the cap is actually guarding.
Good point — a hard re-baseline on deploy just moves the cold-start problem to day one, and an alert that pages the day after a deploy trains the team to ignore it. Carrying the old anchor over with a widened tolerance for the first few days is the safer default for me; shadow mode is nicer, but only if the new query can actually run before cutover. Either way, the deploy step should state what the anchor is on day one — an empty anchor is a decision too, just an unrecorded one.
And you're right on the seed check — same tables and similar row counts is only the floor. A table with the same shape but 100x the churn has a completely different lock-wait profile, so the check should compare the seed workflow's lock-wait distribution against the new one. That's the number the cap is actually guarding.
Carrying the old anchor with a wider tolerance feels right to me too — a page on day one just trains the team to ignore it. I'd also write the anchor and tolerance into the deploy notes, so the next person can see what 'normal' was meant to be that week.
Exactly, the deploy notes part is the bit that makes it actually work in practice. A tolerance nobody wrote down just becomes a magic number the next person is scared to touch. Writing down what 'normal' was that week, and when the anchor was set, means the next deploy starts from something documented instead of someone's memory.
That 'magic number nobody wants to touch' point is exactly it. I'd also note the date the anchor was set next to the tolerance, so a value from three months ago doesn't get treated as current.
Good addition — I'd do exactly that. The date is what turns the anchor from a magic number into something you can judge: if it's three months old, you know to re-check it against the first day of real traffic instead of trusting it. I'd write the anchor, the tolerance, the date it was set, and the deploy it came from together in the deploy notes, and treat anything much older than a month as stale until that re-check confirms it still holds.
The one-month stale rule makes sense to me. I'd add one small step: when that re-check confirms the anchor still holds, update its date in the notes too, so the next person doesn't keep re-checking a value that's already been verified.
Good call — that's exactly the bit I was missing. If the re-check confirms the anchor still holds, updating the date in the notes means the next person doesn't have to redo a check that's already been done. I'll start doing that.
Glad that landed. I'd keep the re-check date right next to the anchor value too, not only in the notes, so a quick scan shows how fresh it is without opening anything else.
Good point — keeping the date right next to the anchor value makes the freshness obvious at a glance, without opening the notes at all. I'll do it that way going forward. Thanks for the suggestion!
Happy it helped! That little date-next-to-value habit has saved me more than once when scanning old runs. Let me know how it holds up once you try it.
Will do — I'm putting it in place this week on the payment-webhook runs, since that's where a stale anchor would cost us the most. The date next to the value means the next person reading a run can tell at a glance whether the anchor is from last week or last month, without digging through notes. I'll report back once it's been through a few retries.
The line that hit me: "A workflow that writes a whole record is claiming it knows the record's full state. It does not."
This is the classic "lost update" problem from database theory, but it's so much more dangerous in workflow automation tools. In a traditional app, you have an ORM or a database transaction to protect you. In n8n, it's incredibly easy to just pull a record, change one field, and push the whole object back without realizing you just overwrote a completely different process.
Your point about the version check needing to live inside the update statement is spot on. But I'd love to hear how you handle this practically in n8n.
Standard nodes often abstract away the raw SQL, making it hard to do an atomic UPDATE ... WHERE version = X. Do you usually drop down to a raw SQL node to handle the optimistic locking, or do you try to structure the workflow to avoid fetching the whole record in the first place?
Also, the "field ownership" rule is a brilliant practical takeaway. It shrinks the blast radius of these collisions massively. Great breakdown.
The detail that both executions reported success while one update disappeared is the whole problem. Silence looked like health, and the logs had nothing to argue with.
Putting the version check inside the write, not in a node before it, is the part most teams skip. A separate read-then-write still leaves a gap another workflow can slip into. Narrow field ownership helps too: billing never writes contact details, and the form never writes the plan, so a collision points at a field instead of a half-right record.
At Ascendra Ventures we treat this as part of handover on automation builds. The client gets a list of which jobs may touch which fields, and acceptance includes a collision case where two updates overlap and only one is allowed to land.
Have you found a good way to keep that field ownership list current as people add new workflows?
Honestly, no — the list is the part that rots fastest, and I don't have a clean answer yet. What I do now: the registry is a single table (workflow → fields it may write), and a check on deploy parses the workflow export, extracts the fields each write node touches, and fails the deploy if a workflow writes a field it doesn't own. Deriving it from the exported definition instead of trusting a hand-maintained list is what keeps it from drifting when someone edits in the UI and forgets the table. The acceptance case you describe is the real test — I run the two overlapping updates in staging and assert exactly one lands and the loser is recorded, not just that nothing crashed. Still, if a team adds workflows outside that deploy path, the registry goes stale. That's the gap I haven't closed.
Your fix carries a requirement you don't state: the version has to be a value the competing writer cannot author. A server-assigned revision that the update statement advances atomically works. A client-supplied timestamp or revision number does not, and the check still runs, still reports rows changed, still admits the write.
Worth being precise about why, because it isn't the equality check that breaks. Comparing against a stale timestamp is fine, since an earlier time doesn't equal the stored one. What breaks is any scheme that treats author-declared ordering as evidence about concurrency, including the "keep the newest write" resolution people reach for once version checks start failing often.
I measured the edges of that on an append-only ledger I run. Its skew check is real and fires in both directions against the author's clock, so this isn't a missing-validation story. It still stores 23 rows whose authored timestamp sits after the moment the server received them, by up to 12 seconds, because they landed inside the permitted 300-second future window. The past bound allows seven days. The server does assign its own arrival time, non-null across all 123,456 rows, and the public read API returns seven fields per event without it. Third parties are left comparing the value the other writer picked. 65,415 rows share their created_at second with another row, so it isn't unique either.
Your second detail is where I'd push harder. "Zero rows changed" is a return value. One party sees it, once. Escalating a conflict for review needs something durable, and a failed precondition leaves an absence instead. My ledger has the same hole from the other side: the only write precondition is primary-key uniqueness, a duplicate raises an integrity error, the handler returns false, and nothing about the collision is written down.
Two things worth checking in your setup. Does every accepted update advance a revision the workflow itself cannot set? And if you force a stale update right now, is there a row afterwards that your escalation path can actually read?
You're right, and this is the sharper version of the rule: the version has to be assigned and advanced by the store, in the same statement that writes. The workflow supplies the expected revision, never the new one — the update sets the field and increments the revision only where the id and the expected revision both match. A client-supplied timestamp or revision fails for a different reason than a bad comparison: the writer is authoring its own ordering claim. A timestamp inside a permitted future window is still a claim, not an arrival order, and "keep the newest write" just resolves the conflict in favour of whoever claimed the latest time.
Your second point is the one I had missed stating: zero rows changed is a return value seen once, by one execution. If the conflict path only logs it, there is nothing for an escalation to read later. In my setup the stale-update branch has to write a conflict row first — the change it tried to make, the version it read, and the version it found on re-read — into a review table the escalation path queries. Execution logs don't count: they expire, and they aren't a queue a human or a sweeper can work through.
So the two checks, answered honestly: every accepted update must advance a revision the workflow cannot set, and the test for the second one is to force a stale update and then query the review table — not to watch the update return zero, but to confirm a row exists that the escalation path can actually pick up. Thanks for pushing on both — the first is a correction to the article, the second is a gap in it.
The “both said success” part is what makes this one stick. In a small business that's exactly how a customer ends up on the wrong plan or with an old phone number on file, and nobody finds out until the customer calls. Sending only the change, instead of rewriting the whole record, is a simple rule to live by — and the field-ownership idea is the part I'd actually write down first, because it forces you to decide who is allowed to touch what before the next workflow gets added.
Exactly — and in a small business the customer-facing version of this is the status transition, not the row. A stale read that moves an order from shipped back to processing is the same bug with a customer on the phone. The guard that holds is UPDATE ... WHERE status = , so a writer that read a stale state gets zero rows instead of a success — and an allowed-transition table (processing → shipped, never backwards) catches the move even when the guard doesn't.
On the field-ownership list: the cheap version that actually survives is a one-page doc the team updates before a new workflow is added, not after. The list rots the moment adding a workflow is easier than updating the list — so make the list part of adding the workflow, or derive it from the workflow export the way I described in the replies above. The mechanism matters more than the tooling; a shared sheet the team actually checks beats a registry nobody opens.
Oof, silent overwrites are terrifying. We run into similar race condition nightmares with order updates at The Printing World when multiple systems try to sync shipping statuses at once. Good catch on this.
Order status is the classic case — it's where a silent overwrite stops being a data bug and becomes a customer-facing one. A stale read followed by a write can move an order backwards (shipped back to processing), and nothing in the row tells you it happened. The fix that actually holds is guarding the transition, not just the row: UPDATE ... WHERE status =
<expected previous status>, so a writer that read a stale state gets zero rows instead of a success.The second half is that two writers usually aren't updating the same field — one sets status, the other sets tracking info — and a whole-row write-back lets one silently drop the other's field. Field-level updates plus the same version check per write turn that into a visible conflict instead of a lost update. And if your statuses only ever move forward, an explicit allowed-transition table (processing → shipped, never shipped → processing) catches the backward move even when the guard doesn't.