8. Project: making a step that creates records idempotent

Description

By the end of this lesson you'll have a concrete deliverable: a Cumbre flow that used to duplicate —inserting a record and calling an API on every retry— turned idempotent, and proven by re-running it twice to demonstrate it leaves a single record and a single effect. This isn't a lesson of new concepts: it's where you pull the previous six together and produce something you can show. You're going to assign the idempotency key, turn the insertion into an upsert, shield the API call with its header, avoid the check-then-act trap, and —most importantly— you're going to build the proof that separates "I think it's idempotent" from "I demonstrated it's idempotent."

This matters because the difference between someone who "knows about idempotency" and someone a team hires is exactly this: the second person doesn't promise their workflow doesn't duplicate, they demonstrate it. A deliverable that shows the flow, the chosen key, and a two-execution proof with a single effect is defensible in an interview and in a portfolio. It's the "workflow builder vs. automation system owner" the whole guide talks about, turned into something you can see and touch.

Connection to the module: this lesson closes module 2 by putting everything together. The key comes from lesson 3, the upsert from 4, the header from 5, the caution about check-then-act from 6, and agent tool idempotency from 7. There's no new concept; there's integration and proof. When you finish, you'll have the "safe operation" Phase 1 of the guide promised, ready for module 3 to add contracts to and module 4 to add persistent state to.

The done criterion: the two-execution proof

Before touching anything, let's define what "done" means, because a project with no acceptance criterion is a project you never know actually works.

Think of it as an elevator's safety inspection. It's not enough for the installer to say "it's fine." An inspector presses the button five times and checks the elevator doesn't go haywire; opens the doors with the elevator moving and checks it stops; overloads the cabin and checks it doesn't start. Certification isn't the installer's word; it's the result of concrete tests. Your project needs its own certification.

This project's done criterion is a single sentence, and it's the idempotency proof we've been anticipating since lesson 1:

I run the flow twice with the same order ORD-2041, and at the end there's exactly one record in the database and exactly one effect in the API. Both executions finish with no error.

Notice the three parts, because each one rules out a different failure:

"Exactly one record" rules out the INSERT that duplicates (lesson 4). If there are two rows, the upsert failed or the uniqueness constraint is missing.

"Exactly one effect" rules out the POST that duplicates (lesson 5). If there are two charges, the idempotency header is failing or the key isn't stable.

"No error" rules out you having "solved" the duplicate by breaking the flow. A workflow that fails on the second execution isn't idempotent; it's a broken workflow that happens not to duplicate. The second execution has to run in full and clean, silently absorbing the repetition.

Burn this criterion into memory, because everything that follows exists to satisfy it, and at the end you're going to verify it step by step.

Phase 0: the fragile flow you're going to fix

This is the starting point —the fragile order-triage that opened the module, in its simplest version, no agent to start with; we add the agent as an extension at the end—:

Webhook          Postgres (INSERT)          HTTP Request (POST)
receives    ──►  INSERT INTO orders    ──►  POST /charges
ORD-2041         (blind)                    (creates a new charge)

Two effects that accumulate, both already diagnosed in lesson 2: the blind INSERT puts in a new row on every retry, and the POST /charges creates a new charge on every retry. If you trigger this flow twice with ORD-2041, you end up with two rows and two charges. It fails the done criterion on all three parts.

Before fixing it, run the two-execution test in its fragile state, to have a baseline to compare against. What to expect: two rows in orders with order_id = 'ORD-2041', and two charges at the gateway. That's the problem, measured. Now let's make it disappear, phase by phase.

Phase 1: assign the idempotency key

The first thing any repair needs is a stable name for the event. You insert a Code node right after the Webhook, before any effect.

Webhook  ──►  Code               ──►  Postgres (INSERT)  ──►  HTTP Request (POST)
              (computes                (still blind)          (still duplicates)
               idempotency_key)

Since Cumbre's web orders carry a stable order_id the store keeps across retries, we use the natural key: order_id itself. Even so, we store the value in an explicit idempotency_key field, so the following nodes read it from a single place, regardless of channel:

// Node: Code — "Assign idempotency_key"
// Mode: Run Once for Each Item
// Goal: get the stable key ready in a field, early, for every following node.

const order = $input.item.json;

// Web orders carry a stable order_id: it is the natural key, the best option.
// (For channels with no order_id, here you would compute a hash with crypto — lesson 3.)
const idempotencyKey = order.order_id;   // 'ORD-2041'

return {
  json: {
    ...order,                        // keep the whole order
    idempotency_key: idempotencyKey, // and add the key, ready for the effects
  },
};

What to expect: the OUTPUT panel shows the order with a new field, idempotency_key, with the value "ORD-2041". If you run the node twice with the same order, the key is identical both times —that's the property everything else is going to build on—.

A reminder from lesson 3, because this is where everything gets ruined if you're careless: don't put anything time-related or random into this key. No new Date(), no randomUUID(). The key has to be the same when the webhook arrives a second time. With order_id it is, for free; if you'd had to compute a hash, it would be from the order's stable fields, without the arrival time.

Phase 2: turn the INSERT into an upsert

Now let's fix the first effect: the write to the CRM. Remember lesson 4 —the upsert needs, first, a uniqueness constraint on the key column—.

Step 2a — The uniqueness constraint (once). On your Postgres database, make sure orders doesn't allow two rows with the same idempotency_key:

-- Once, when preparing the table.
ALTER TABLE orders ADD CONSTRAINT orders_idem_unique UNIQUE (idempotency_key);

What to expect: if the table didn't have the constraint, it's created with no fuss. If running it warns you there are already duplicate values, it's because your Phase 0 baseline test left two ORD-2041s; delete one and try again. (That warning is, in itself, a confirmation the problem was real.)

Step 2b — The upsert. You change the Postgres node from a blind INSERT to an upsert with ON CONFLICT. You can use the node's upsert operation or, for full control, the execute-query operation:

-- Postgres node, "Execute Query" operation.
-- Values come from the item, passed as parameters.
INSERT INTO orders (idempotency_key, order_id, customer_id, amount, status)
VALUES ($1, $2, $3, $4, 'pending')
ON CONFLICT (idempotency_key) DO NOTHING;

Parameters $1..$4 get filled with idempotency_key, order_id, customer_id, and amount from the item.

What to expect when you test it now: trigger the flow once → one row in orders. Trigger the same ORD-2041 again → the node runs with no error and there's still a single row. The second insertion collided with the uniqueness constraint and DO NOTHING absorbed it. The first effect is already idempotent. (The POST /charges, on the other hand, still duplicates; that's Phase 3.)

Phase 3: making the API call idempotent

Now the second effect: the charge. We assume, as in lesson 5, that Cumbre's gateway supports the Idempotency-Key header —and that you confirmed it in their documentation, along with the exact name and the retention window—.

In the HTTP Request node that makes POST /charges, you turn on sending headers and add:

• Name: Idempotency-Key
• Value: {{ $json.idempotency_key }}

That expression takes the key Phase 1 left in the item —ORD-2041— and sends it in the header. The gateway, the first time, creates the charge and stores "key ORD-2041 → this charge"; the second time, with the same key, returns the same charge with no new one created.

What to expect when you test it now: trigger the flow once → one charge at the gateway. Trigger the same ORD-2041 again → the call returns success (no error) and there's still a single charge, the same one from the first time. The second effect is already idempotent.

Remember lesson 5's failure diagnosis, in case you see two charges: it's almost always that the header isn't being sent (check the toggle and the name) or that the key isn't stable (impossible here, since it's order_id, but check it if you used a hash). And verify the exact header name in the gateway's documentation; not every one calls it the same.

Phase 4: verify no "check then act" is left

Before the final test, a lesson-6 review. Walk through your flow and confirm there's no pattern of "first a node that checks if it exists, then an If, then a node that creates." If at some point, to "make sure," you added a SELECT that checks before the upsert, remove it: the upsert already does the check atomically, and putting a SELECT in front adds no safety, it adds a race window and noise.

The finished flow has no manual existence checks. It has an atomic upsert and an idempotency header, which delegate the uniqueness to something that knows how to be atomic —the database and the gateway—. It looks like this:

Webhook  ──►  Code               ──►  Postgres (UPSERT)        ──►  HTTP Request (POST)
              (idempotency_key)        INSERT ... ON CONFLICT        with
                                       DO NOTHING                    Idempotency-Key header

Compare it with Phase 0's fragile flow. One node was added (Code) and two configurations were changed (the INSERT to upsert, the header on the HTTP Request). No check node, no existence If. Less complexity than the naive solution would have, and correct under concurrency.

Phase 5: the proof that demonstrates it (the deliverable)

This is the phase that turns your work into a defensible deliverable. It's not enough for it to work; you have to demonstrate it works, and keep the evidence.

The test procedure, step by step:

Step 1 — Clean initial state. Make sure there's no trace of ORD-2041 from previous tests. Query, and clean up if needed:

SELECT * FROM orders WHERE order_id = 'ORD-2041';   -- should return 0 rows before you start

And check there's no prior charge for ORD-2041 at the gateway.

Step 2 — First execution. Trigger the flow with ORD-2041. What to expect: one row in orders, one charge at the gateway, the execution finishes with no error.

Step 3 — Second execution (the one that matters). Trigger the flow again with the exact same ORD-2041, simulating the webhook's retry. What to expect: the execution finishes with no error, and —the moment of truth— when you query again:

SELECT COUNT(*) FROM orders WHERE order_id = 'ORD-2041';   -- should return 1

a single row, and at the gateway a single charge. The second execution ran in full, absorbed the repetition, and left no new effect.

Step 4 — Save the evidence. This step is what distinguishes a project from a deliverable. Capture:

• The flow diagram before (fragile) and after (idempotent).
• The Code node with the chosen key and the justification for why it's stable.
• The SELECT COUNT(*) returning 1 after two executions.
• The gateway screenshot showing a single charge.

With that you have the demonstration, not the promise. If your instance is n8n 2.0 on top of that, you can use the replay engine to re-run one of the two runs and show it still doesn't duplicate —but that's module 6 material; for now, the two-execution proof is more than enough—.

Definition of done, verified: two executions, one row, one charge, zero errors. If your test shows that, the project is complete. If it shows two rows or two charges, go back to the corresponding phase —two rows is Phase 2 (upsert or constraint), two charges is Phase 3 (header or key)—.

What this project does NOT cover (and why that's fine)

Part of delivering honestly is knowing the limits of what you built. Your flow is idempotent for the case this module promises —repeating the same event doesn't duplicate the effect— but there are things it deliberately does not solve, and confusing them would be overselling your work.

It doesn't deduplicate across executions separated by a long time if you depend only on the header. Remember from lesson 5 that the gateway forgets the key after a few hours. Your two-back-to-back-executions test is more than covered; but if the "same" event got reprocessed days later —a manual replay of an old execution— the header would no longer remember it. The durable defense against that is a record of yours that doesn't expire, and that record —the deduplication ledger— is module 4. The upsert on your database is durable (the row doesn't expire), so the record is protected forever; the charge, only within the API's window. Knowing that asymmetry is part of mastering the topic.

It doesn't coordinate several effects that depend on each other. Your flow does two independent effects (record and charge). If you had three effects where the second depends on the first's success, and you wanted a failure halfway through not to leave the system half-done, that's coordination —module 5's outbox pattern—. Here each effect is idempotent on its own, which is the necessary foundation, but it isn't coordination.

It doesn't retry or alert when something truly fails. Your criterion includes "no error," but you didn't build what happens when the gateway is down, or when the error isn't an expected duplicate but a real failure. Safe retries, compensating actions, and alerts are module 6.

None of these absences is a defect in your project; they're the following modules. Your deliverable satisfies exactly what it promises —an operation safe to repeat— and that's the fundamental piece everything else is built on. A good engineer delivers the complete piece and precisely names where it ends; that's more valuable than pretending they solved everything.

How to present it in an interview or portfolio

An undefended deliverable is half its value. Here's how to tell this story in two minutes, which is the time you have in an interview:

Start with the problem, not the solution. "This distributor's workflow processes orders through a webhook. The webhook sometimes fires twice —provider retry, double click, engine retry— and the original version created a second charge and a second record every time. A customer would get charged twice." The problem, told this way, anyone understands why it matters.

Name the property, not just the fix. "I made it idempotent: repeating the operation leaves the same result as doing it once." The word "idempotent" in a technical interview immediately places you on the side of someone who understands systems.

Show the two key decisions. First, the key: "I used order_id as the idempotency key because the provider keeps it across retries; if it didn't, I would have hashed the stable content." Second, where the uniqueness lives: "the database enforces it with a uniqueness constraint and an atomic upsert, and the gateway with its Idempotency-Key header —not a node that checks and then creates, which would have a race condition under concurrency—." That last sentence, about not falling into check-then-act, is what demonstrates depth.

Close with the proof. "And I don't just claim it, I demonstrate it: here's the flow run twice with the same order, and the COUNT gives one." The evidence is what sets you apart from someone who only read about idempotency.

That's the conversation of an automation system owner, not a workflow builder. It's, literally, the guide's goal turned into two minutes you can rehearse.

A piece of advice on how to save the deliverable so it stays useful six months from now: pair the screenshots with a short note answering three questions —which key you chose and why it's stable, where the uniqueness guarantee lives (the database constraint and the API header), and how it's tested (the two executions)—. That note is your own "system manual," and it's what's going to let you pick the project back up, explain it to a coworker, or defend it in an interview without having to rebuild the reasoning from scratch. A deliverable with its design note is worth much more than one without it, and writing it takes five minutes that pay for themselves.

Agent extension: the full order-triage flow

If you want the full challenge, add the AI Agent the case study really has, applying lesson 7:

Webhook  ──►  Code               ──►  AI Agent  ──►  (idempotent tools)
              (idempotency_key)        classifies      charge_customer (header)
                                       and decides      upsert_order (ON CONFLICT)

Lesson 7's rule governs: the idempotency_key is computed by the Code node before the agent, derived from the order, and the tools (charge_customer, upsert_order) use it without recomputing it or deriving it from the agent's classification. That way, even if the agent invokes charge_customer twice or classifies differently between runs, the charge is one. The done proof is the same —two executions, one row, one charge— plus one extra check: that a double agent invocation within a single execution also doesn't duplicate.

Common mistakes

Declaring "done" without the two-execution proof (conceptual). What happens: the upsert and the header get configured, the flow is triggered once, it works, and it's considered solved. It was never triggered twice, so the idempotency was never tested —only that it runs was tested—. Why it happens: idempotency is invisible in a single execution; it only shows up in the second. How to spot it: if your evidence is "I ran it and it worked," you didn't test idempotency. The proof is the second execution. How to fix it: always run the two-execution test and check the COUNT. "It ran with no error" isn't the criterion; "I ran it twice and there's exactly one effect" is. Without that proof, you don't have a deliverable, you have a hope.

Forgetting the uniqueness constraint and believing the upsert failed (practical). What happens: the upsert gets configured, tested, and two rows show up; it's concluded "the upsert doesn't work" and another solution gets sought. Why it happens: the uniqueness constraint on idempotency_key is missing, without which the upsert has nothing to collide against and degrades into an insert. It's lesson 4's number-one cause of "my upsert doesn't protect." How to spot it: query the table's constraints; if there's no UNIQUE on the key column, that's the problem, not the upsert. How to fix it: create the constraint (ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (idempotency_key)); if it fails because of existing duplicates, clean them up first. The upsert's step zero doesn't get skipped.

Testing only sequentially and assuming it covers concurrency (conceptual). What happens: the two-execution test is done by triggering one, waiting for it to finish, and triggering the other. It passes. But if the flow had a hidden check-then-act, that sequential test wouldn't catch it —the failure only shows up with overlapping executions—. Why it happens: the sequential test is the easy one to do by hand, and it covers the typical retry, but not concurrency. How to spot it: review the design (Phase 4) in addition to the test; if there's no SELECT+If+INSERT anywhere, the sequential test is sufficient because the upsert and the header are atomic by design. If there is one, no sequential test saves you. How to fix it: eliminate any check-then-act (Phase 4) so correctness doesn't depend on reproducing concurrency in a test —the atomic upsert gives you the guarantee by construction, not by lucky timing—.

Exercises

Exercise 1 — Write your acceptance criterion. For the flow you built, write the done criterion in one sentence, and then list the three concrete SELECTs or checks you'd run to verify it. Don't look at this lesson's criterion until you're done; then compare.

Exercise 2 — Diagnose by symptom. After your two-execution test, you get each of these results. For each one, say which phase you'd check and what the most likely cause is:

(a) Two rows in orders, a single charge. (b) One row in orders, two charges. (c) One row, one charge, but the second execution finished with an error. (d) Zero rows, zero charges, both executions "successful."

Exercise 3 — Adapt it to a channel with no order_id. Your flow uses order_id as the natural key, perfect for web orders. Now you get an order via WhatsApp with no order_id. Describe what you'd change —and what you'd not change— in the five phases so the flow stays idempotent.

const crypto = require('crypto');
const order = $input.item.json;
const fingerprint = [
  order.session_id,          // if it exists, stable per submission: adds specificity
  order.customer_id,
  order.amount,
  order.line_items.map((l) => `${l.sku}x${l.quantity}`).join(','),
].join('|');
const idempotencyKey = crypto.createHash('sha256').update(fingerprint).digest('hex');

Summary and next step

In this lesson you pulled the whole module together into a deliverable. You took the fragile order-triage that opened with a blind INSERT and a duplicating POST, and turned it idempotent in phases: you assigned the stable key in a Code node (Phase 1, lesson 3), turned the insertion into an ON CONFLICT upsert with its uniqueness constraint (Phase 2, lesson 4), shielded the charge with the Idempotency-Key header (Phase 3, lesson 5), and verified there was no check-then-act left (Phase 4, lesson 6). And most importantly: you defined and ran the done criterion —two executions, one row, one charge, zero errors— which turns "I think it's idempotent" into "I demonstrated it's idempotent" (Phase 5). You learned to present it the way an automation system owner would: starting with the problem, naming the property, showing the chosen key and where the uniqueness lives, and closing with the proof, not the promise. And you saw how the agent extension (lesson 7) and the channel change fit in without redoing the flow, because the event's identity always lives in a single field.

With this you close Phase 1 of the guide —the system owner mindset (module 1) and a safe operation (module 2)—. You now know how to make an effect, repeated as many times as it wants, happen only once.

Module 3 raises the level. Up to now you protected one workflow. But real systems are several workflows calling each other, and when one passes data to another, it needs a promise about what shape that data has —a contract—. You're going to see what a workflow contract is, how to design the input and output schema, how to validate at the Execute Workflow node's boundary, and how to version a contract without breaking whoever calls it. And the idempotency key you learned here is going to reappear: when one workflow passes an effect to another, the contract is what guarantees the key arrives in the correct shape. You already have the safe operation; now you make it reliable between workflows.

Resources

• INSERT ... ON CONFLICT — PostgreSQL documentation — Phase 2's upsert, with DO NOTHING, DO UPDATE, and the uniqueness constraint requirement.
• HTTP Request node — n8n Docs — how to send Phase 3's Idempotency-Key header; verify the headers-sending label in your version.
• Idempotent requests — Stripe API reference — confirm the exact header name and retention window for the API you're calling; don't assume every one uses Idempotency-Key.
• Postgres node — n8n Docs — the Postgres node's upsert and execute-query operations, and how to pass parameters from the item.
• Executions and testing — n8n Docs — how to review the execution history to verify your test's two runs finished with no error, part of the done criterion.