Who this is for: Developers building production‑grade n8n workflows that write to databases or external APIs and need reliable, duplicate‑free results. We cover this in detail in the Production‑Grade n8n Architecture.
Quick diagnosis: Your workflow sometimes creates duplicate rows or loses updates when several executions run in parallel. In production this often appears when a webhook fires before the prior run finishes.
Solution snapshot: Enable node‑level transactions, use n8n’s retry and continue‑on‑fail flags, and design idempotent payloads (upserts with stable keys). That gives strong eventual consistency across supported databases and APIs.
Execution Model & Atomicity
How n8n isolates work and where you must add your own safeguards?
Before diving into the table, remember each node runs in its own sandbox; there’s no hidden shared state you can rely on.
| Aspect | n8n behaviour |
|---|---|
| Node granularity | Each node runs in its own JavaScript sandbox. The workflow engine does not wrap the whole workflow in a single transaction. |
| Workflow run isolation | Before any node runs, n8n stores the run record (execution_data) in its internal SQLite/Postgres DB. This makes the run state recoverable after a crash. |
| Parallel branches | Branches are executed concurrently when maxConcurrency > 1. Nodes inside a branch do not share memory. |
Implementation tip – Start a DB transaction in the first node and commit or roll back in the last. Either use a single “Execute Command” node with a multi‑statement transaction, or chain the native Start Transaction → … → Commit nodes. Most teams hit this after a few weeks, not immediately.
Database Connectors: Transaction Support
If you encounter any single vs multi instance n8n resolve them before continuing with the setup.
| Connector | Transaction capability |
|---|---|
| Postgres | ✅ Full support (BEGIN, COMMIT, ROLLBACK) |
| MySQL | ✅ Full support (START TRANSACTION) |
| MongoDB | ✅ Multi‑document ACID via session.startTransaction() |
| SQLite (internal) | ✅ Implicit per‑run transaction |
| REST API | ❌ No native DB transaction |
| Connector | Default isolation level | Typical use case |
|---|---|---|
| Postgres | READ COMMITTED | Batch insert with ON CONFLICT DO UPDATE |
| MySQL | REPEATABLE READ | Upsert rows while preserving foreign keys |
| MongoDB | READ COMMITTED | Atomic updates across collections |
| SQLite | SERIALIZABLE | Quick local caching of intermediate data |
| REST API | N/A | Use idempotency keys or server‑side retries |
Sample Transaction – Postgres Node Chain
Below is a step‑by‑step illustration. Each snippet is limited to a few lines for readability. If you encounter any n8n high availability patterns resolve them before continuing with the setup.
1️⃣ Start the transaction
{
"name": "Start Transaction",
"type": "n8n-nodes-base.postgres",
"parameters": {
"operation": "executeQuery",
"query": "BEGIN;"
}
}
2️⃣ Insert (or upsert) the order record
{
"name": "Insert Orders",
"type": "n8n-nodes-base.postgres",
"parameters": {
"operation": "executeQuery",
"query": "INSERT INTO orders (id, status) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET status = EXCLUDED.status;",
"values": ["{{$json[\"orderId\"]}}", "{{$json[\"status\"]}}"]
}
}
3️⃣ Insert the related order items
{
"name": "Insert Items",
"type": "n8n-nodes-base.postgres",
"parameters": {
"operation": "executeQuery",
"query": "INSERT INTO order_items (order_id, sku, qty) VALUES ($1, $2, $3);",
"values": ["{{$json[\"orderId\"]}}", "{{$json[\"sku\"]}}", "{{$json[\"qty\"]}}"]
}
}
4️⃣ Commit the transaction
{
"name": "Commit Transaction",
"type": "n8n-nodes-base.postgres",
"parameters": {
"operation": "executeQuery",
"query": "COMMIT;"
}
}
5️⃣ Wire the nodes together – The connection map tells n8n the order of execution.
{
"connections": {
"Start Transaction": { "main": [ [ { "node": "Insert Orders", "type": "main" } ] ] },
"Insert Orders": { "main": [ [ { "node": "Insert Items", "type": "main" } ] ] },
"Insert Items": { "main": [ [ { "node": "Commit Transaction", "type": "main" } ] ] }
}
}
EEFA note – Add an *Error Trigger* that runs ROLLBACK; if any node fails. That stops half‑committed data. Rolling back is usually faster than chasing edge‑case leftovers.
Built‑in Retry & Error Handling
If you encounter any n8n zero downtime upgrades resolve them before continuing with the setup.
| Feature | When to enable |
|---|---|
| Retry on Failure | Transient network glitches, API rate‑limit spikes |
| Continue On Fail | Non‑critical side‑effects (e.g., logging) |
| Error Trigger workflow | Centralized alerting and rollback logic |
| Run Once mode | Prevent duplicate processing when the same webhook fires multiple times |
Idempotent Retry – REST API Example
1️⃣ Create a deterministic idempotency key
{
"name": "Prepare Payload",
"type": "n8n-nodes-base.set",
"parameters": {
"values": [
{ "name": "idempotencyKey", "value": "{{$nowUnixMs}}-{{$json[\"orderId\"]}}" }
]
}
}
2️⃣ Send the request with retry settings
{
"name": "Create Order",
"type": "n8n-nodes-base.httpRequest",
"parameters": {
"url": "https://api.example.com/orders",
"method": "POST",
"jsonParameters": true,
"bodyParametersJson": "{{$json}}",
"headerParametersJson": "{\"Idempotency-Key\": \"{{$json.idempotencyKey}}\"}",
"retryOnFail": true,
"maxAttempts": 5,
"retryDelay": 2000
}
}
EEFA tip – Verify the target API respects Idempotency-Key. If not, fall back to a lookup‑before‑write pattern. Skipping this check often leads to hidden duplicates.
Idempotent Design Patterns for n8n Nodes
- Upsert instead of Insert – Use
ON CONFLICT … DO UPDATE(Postgres) or equivalent. - Deterministic primary keys – Derive UUIDs from business data (
orderId + source) rather than random generators. - Checksum / version column – Store a hash of the payload; skip the update when the hash matches the previous run.
Checklist: Making a Node Idempotent
- Use a stable unique key derived from the payload.
- Choose an upsert or replace operation in the query.
- Add a version/timestamp column and guard with
WHERE version = :prevVersion. - Enable Retry on Fail with exponential back‑off.
- Log the idempotency key to the internal execution data for audit purposes.
Consistency Checks & Validation Nodes
| Validation node | Typical use | Example expression |
|---|---|---|
| If | Branch based on data integrity | {{ $json[“status”] === “COMPLETED” }} |
| Function | Compute checksums, compare with previous run | return { checksum: crypto.createHash(‘sha256’).update(JSON.stringify($json)).digest(‘hex’) }; |
| Set | Enforce required fields before DB write | {{ $json[“email”] ? $json : (throw new Error(“Missing email”)) }} |
| Data Validation (community) | JSON‑Schema enforcement | { “type”:”object”,”required”:[“orderId”,”total”] } |
EEFA advice – Place validation immediately after any external API call. If it fails, trigger the *Error Trigger* workflow to roll back any open DB transaction.
Monitoring, Logging, and Auditing
- Execution Data Export – Turn on “Save Execution Data” in workflow settings to keep the full input/output of each node.
- Webhook for Audits – After a successful commit, send a concise summary to a logging service.
Audit‑log request (compact snippet)
{
"name": "Audit Log",
"type": "n8n-nodes-base.httpRequest",
"parameters": {
"url": "https://logs.example.com/ingest",
"method": "POST",
"jsonParameters": true,
"bodyParametersJson": {
"workflowId": "{{$workflow.id}}",
"runId": "{{$execution.id}}",
"status": "COMPLETED",
"recordsAffected": "{{$node[\"Commit Transaction\"].json.rowCount}}"
}
}
}
EEFA reminder – Never store raw PII in execution data unless you have encryption at rest. Use the community **Encrypt** node to mask sensitive fields before sending them to external log stores.
Bottom Line
Explicitly managing transactions, using idempotent upserts, leveraging n8n’s retry/error flags, and validating payloads before each write gives strong, production‑grade data consistency across any mix of databases and APIs n8n integrates with. The approach removes duplicate rows, stops lost updates, and provides a clear audit trail for troubleshooting in real‑world deployments.
