# Stop n8n retries from repeating a create, send or charge

**The problem.** When you click Retry on a failed execution, n8n re-runs the failed node with all of its input items, and it cannot tell whether the external service already did the work before the timeout. So items 1 and 2 of a batch that failed on item 3 can be created twice, and a call that timed out after the remote accepted it can be sent again. A dedupe keyed on the execution id does not help, because a retry is a new execution (all of this is reported in the n8n forum and Reddit threads in `thread.txt`).

## How the fix works

A Postgres table, `side_effect_ledger`, holds one row per business item and action. The key is `<action>:<business id>`, for example `create_crm_contact:ORD-1003`. The key never contains the execution id.

1. **Claim before the write.** One atomic statement, `INSERT ... ON CONFLICT (idem_key) DO NOTHING`. Only the run that creates the row may do the write.
2. **Write.** The HTTP Request node sends the same key in an `Idempotency-Key` header, so a remote that supports the header can also dedupe on its side.
3. **Mark the result after the write.**
   * 2xx: status `done`.
   * 4xx (except 408 and 409): the remote definitely rejected it, status `failed`, safe to send again later.
   * Anything else (5xx, 408, 409, timeout, network error): the row stays `started`, because nobody knows whether the remote acted. An alert goes out.
4. **On a retry** the claim finds the row already there, so the workflow looks it up:
   * `done`: skip (no-op).
   * `failed`: atomically reclaim the row and send again.
   * `started`: send to the "Needs review" alert. It is never resent automatically.

## Files

| File | What it is |
|---|---|
| `schema.sql` | Table, claim, mark done, mark failed, lookup, reclaim, and the SQL a human uses to resolve a stuck row |
| `guard.n8n.json` | Importable n8n workflow template |
| `simulate.py` | Offline simulation of the same logic with sqlite3 |
| `test_fix.py` | Runs the simulation and checks the files |
| `sample/` | Fake records (example.com, "Sample Co") and failure scenarios |

## Setup

1. Create a Postgres database (or pick one you already use for n8n helper tables).
2. Run the `CREATE TABLE` statement at the top of `schema.sql` in that database. The other statements in the file are the queries the workflow already contains, kept there for reference.
3. In n8n, create a Postgres credential with your host, database, user and password. Name it `Ledger Postgres`.
4. In n8n, choose Import from File and select `guard.n8n.json`.
5. Open each of the five Postgres nodes (Claim, Mark done, Mark failed, Lookup status, Reclaim failed row) and select the `Ledger Postgres` credential. The file only contains a placeholder credential id.
6. Replace the `Sample batch` Code node with your real source (a Webhook trigger, a Split Out node, an HTTP Request, and so on). You need one item per business record, and each must have a stable id.
7. In `Build idem_key`, set `action` to a name for this external write, and change `$json.id` if your id field has another name. Use a different action name for each different external write, otherwise two different writes for the same record would block each other.
8. In `Write to external system`, set the real URL, method, authentication and body. Keep the `Idempotency-Key` header. Leave Retry On Fail switched off on this node (see Limits).
9. In `Alert: needs review`, point the URL at your own alert hook (Slack incoming webhook, a ticket tool, email service, and so on). Use the credential type that service needs.
10. Test with a throwaway endpoint first. Run the workflow twice: the second run should end at `Skip (already done)` for every item.

## Resolving an item that is in review

The row stays `started` until a person decides. Look at the remote system. Then run one of the two `UPDATE` statements in section 6 of `schema.sql`: mark it `done` if the write happened, or `failed` if it did not (the next run will send it).

## Test it offline

```
python3 test_fix.py
python3 simulate.py --mock
```

Python 3 standard library only, no network, no credentials. The simulation uses sqlite3 with the same claim and mark logic. Scenario A: a 5-item batch where item 3 is rejected, then the whole batch is retried. Scenario B: the remote accepts items 3 and 5 but the client sees a timeout or a 500, then the whole batch is retried. It also prints a no-guard baseline so you can see the duplicates the guard prevents, and asserts that no item had its effect more than once and that unknown items go to review. In mock mode only the HTTP function is replaced. A non-mock run needs `--url` and `--apply`.

## Limits, read before you rely on it

* **Not tested against a real n8n or a real Postgres.** The simulation covers the decision logic in sqlite. The Postgres statements and the n8n JSON were checked for structure only (valid JSON, every connection points to an existing node). Import it into a test n8n first. Node parameter formats can differ between n8n versions, so if a node shows an error after import, check the current docs for that node and fix the parameter by hand.
* **Why the claim query in the workflow is wrapped.** The plain `INSERT ... RETURNING` gives no output row on conflict, and how a Postgres node reports "no rows" can differ by version (the forum thread says so). The workflow therefore uses the same insert inside a `WITH` and always returns one row with `claimed` true or false. The plain form is in `schema.sql` too.
* **It cannot make an unknown outcome known.** If the remote accepted the call and the answer was lost, the item goes to a human. Only the remote system can tell you, so for that case prefer an API that supports idempotency keys or lookup by your business id.
* **A crash between the remote write and "Mark done" leaves the row `started`.** That is deliberate: it goes to review, not to a resend.
* **There is no automatic lease takeover.** A `started` row is always sent to review, with its age in the message, because an old `started` row means unknown, not safe. The alert includes the age so you can tell a run that is still going from a dead one.
* **Retry On Fail on the write node is not covered by the ledger.** It repeats the call inside the same node run, after the claim. Keep it off, or only turn it on if the remote enforces the `Idempotency-Key` header.
* **The 4xx rule is a default, not a promise.** Some APIs return 4xx for things that partly happened. Adjust the `Classify response` node for your API.
* **If the Mark done query itself fails** (database down), the row stays `started` and the item goes to review on the next retry.
* **Keys must come from business data.** Records without a stable id are rejected by `Build idem_key` on purpose.
* **Size:** one ledger row per item, forever. Decide your own cleanup for old `done` rows, but keep them at least as long as your longest retry window.