8. Project: a deduplication ledger for a webhook that fires double

Description

By the end of this lesson you'll have built, running on your machine, the complete system this module designed piece by piece: a run ledger and a deduplication store in local Postgres, connected to a webhook that fires twice, with the proof in plain view that the second trigger gets discarded before touching the effect. You're going to bring together everything before it —the table design, the ON CONFLICT gate, the idempotency key with crypto, branching with IF, the ledger's two-write pattern— into a single flow you can run, break on purpose, and defend.

This matters because it's the difference between understanding the pattern and owning it. A running project, with its tables and its proof it doesn't duplicate, is what you bring to an interview, to a portfolio, or to your team when someone asks "and how does this avoid charging twice?" You don't answer with theory: you open the flow, fire the webhook twice, and show a single row in the table and a single effect. This lesson's deliverable is exactly that: the table schema plus the flow that uses them, tested end to end.

Connection to the module: this lesson introduces no new concepts; it integrates the previous seven. Lesson 2 gave the argument (why a database), 3 the ledger, 4 the strategy criterion, 5 the atomic gate, 6 the local stack, 7 the pattern's generality. Here all of that becomes a working system. And it sets up module 5: once a single workflow is idempotent and leaves a trace in a ledger, the next step is coordinating several workflows without them duplicating each other's work, which is what dependency coordination is about.

The project brief

Let's be concrete about what you're building and how you know it turned out right.

What you build. A Cumbre workflow —a version of order-triage focused on what this module teaches— that:

1. Receives an order through a webhook.
2. Computes the order's idempotency_key.
3. Goes through a dedup gate that atomically decides whether it's the first time or a duplicate.
4. On the "first time" branch: writes a pending entry to the ledger, runs the effect (the charge, which we safely simulate in testing), and updates the entry to done.
5. On the "duplicate" branch: doesn't run the effect; optionally leaves a record.

The deliverable. Two things: (a) the schema for the run_ledger and processed_orders tables —the SQL that creates them—, and (b) the flow using them, capable of demonstrating that a webhook fired twice produces a single row in processed_orders, a single done entry in run_ledger, and a single effect.

How you know it turned out right. The success criterion is a test, not an opinion: you fire the webhook twice with the same order and verify, with table queries, that the system acted only once. If the second execution shows up green in n8n's history but didn't create a second effect, you won. That "green with no duplicate" is idempotency's goal: repeating without causing harm.

Before starting, confirm you have lesson 6's setup: the Starter Kit running (you get into http://localhost:5678) and a Postgres credential that connects (green test, Host postgres). If that's in place, let's continue.

A note on pace: this project gets built in layers, and it's worth testing each layer before setting up the next. First the tables. Then the webhook that receives. Then the key. Then the gate. Then the branching, and finally the ledger around the effect. If you build everything at once and something fails, you won't know which layer; if you build it in parts, each checkpoint tells you exactly how far you got correctly. Let's go this way, step by step.

Step 1: create the tables

We start with the state, because the flow rests on it. A Postgres node in Execute Query operation, which you run once:

-- The run ledger: the rich history of every execution
CREATE TABLE IF NOT EXISTS run_ledger (
  id               BIGSERIAL     PRIMARY KEY,
  idempotency_key  TEXT          NOT NULL UNIQUE,
  order_id         TEXT          NOT NULL,
  status           TEXT          NOT NULL DEFAULT 'pending',
  result           JSONB,
  created_at       TIMESTAMPTZ   NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- The dedup store: the fast, atomic yes/no
CREATE TABLE IF NOT EXISTS processed_orders (
  idempotency_key  TEXT          PRIMARY KEY,
  order_id         TEXT          NOT NULL,
  processed_at     TIMESTAMPTZ   NOT NULL DEFAULT now()
);

What to expect. The node runs with no error. To confirm it, a SELECT count(*) FROM run_ledger; and a SELECT count(*) FROM processed_orders; should each return 0: the tables exist and are empty. That's the clean starting point. Remember IF NOT EXISTS makes this step repeatable: if you run it again, it breaks nothing.

Step 2: the webhook and how it fires twice

The flow's trigger is a Webhook node. When you configure it, n8n gives you a URL. A Cumbre order arrives as a POST to that URL with a JSON body, for example:

{
  "order_id": "ORD-2041",
  "customer_name": "Luna Coffee",
  "order_total": 1734,
  "currency": "MXN"
}

To simulate the double trigger —the test's heart—, you send that same POST to the webhook's URL twice. You can do it with whichever tool you prefer: n8n's own "test" function run twice, an API client, or any means that sends the same request twice. What matters is that both carry the same order_id and the same content, because they represent the same order accidentally fired twice, which is the module's scenario.

A detail from lesson 2 that's now practical: remember there's a difference between testing from the editor and running the workflow active through its webhook. For the test to be faithful to production —and for any internal state to behave as it would in reality— it's worth activating the workflow and firing it through its production URL, not just testing it from the editor. Your real state, either way, lives in Postgres, which persists in both modes; but the honest test of the double trigger is with the workflow active.

Step 3: compute the idempotency_key

The first node after the webhook is a Code node computing the key with crypto. Remember: the Code node does no HTTP and touches no database, but it can hash.

// ============================================================
// Node: Code — "Compute idempotency key"
// Mode: Run Once for Each Item
//
// INPUT:   the order that arrived through the webhook
// OUTPUT:  the same order, with idempotency_key
// NOTE:    the key identifies THE WORK (this order with this content),
//          not the attempt. Two triggers of the same order → same key.
// ============================================================

const crypto = require('crypto');

const order = $input.item.json;

// Synthetic key: order_id + total. Two identical triggers share a key
// and the second one will collide with the uniqueness constraint. A different
// order (a different order_id) generates another key and gets processed normally.
const raw = `${order.order_id}:${order.order_total}`;
const idempotencyKey = crypto.createHash('sha256').update(raw).digest('hex');

return {
  json: {
    ...order,
    idempotency_key: idempotencyKey,
  },
};

What to expect. In the node's output, every item keeps the order and adds an idempotency_key field with a long string of characters —the hash—. What's crucial: if you fire the same ORD-2041 twice with the same total, both executions produce exactly the same idempotency_key. Confirm it by looking at both executions' output in the history: the key matches. If it didn't match, your deduplication would have nothing to collide against.

Step 4: the deduplication gate

The next node is the system's heart: a Postgres node in Execute Query operation running INSERT ... ON CONFLICT DO NOTHING RETURNING.

# Node: Postgres — "Dedup gate" (operation: Execute Query)

Query:
  INSERT INTO processed_orders (idempotency_key, order_id)
  VALUES ($1, $2)
  ON CONFLICT (idempotency_key) DO NOTHING
  RETURNING idempotency_key;

Query Parameters (values for $1, $2, in order):
  {{ [ $json.idempotency_key, $json.order_id ] }}

Remember why it looks this way: the SQL text carries the $1 and $2 placeholders, and the values travel through the parameters field, separate from the text, so n8n sanitizes them and there's no SQL injection. The statement tries to insert the key; if it already exists, DO NOTHING prevents the error and RETURNING returns nothing.

What to expect. On the first trigger for ORD-2041, the key didn't exist, so it gets inserted and the node returns a row with idempotency_key. On the second trigger, the key is already there, ON CONFLICT DO NOTHING doesn't insert, and the node returns empty (or zero items, depending on your version). That difference —row vs. empty— is what the following branch uses to decide.

Step 5: branch between first time and duplicate

An IF node checks whether the gate returned the key:

# Node: IF — "Is it the first time?"
Condition:  {{ $json.idempotency_key }}  ->  exists / is not empty
  - true  ->  first time  (the gate returned the key → continue to the effect)
  - false ->  duplicate    (the gate returned empty → do not run the effect)

As warned in lesson 5, confirm on your version's panel what the Postgres node produces when RETURNING brings back no rows —zero items or an empty item— and adjust the IF's condition accordingly. The logic doesn't change: act only if the gate returned the key.

What to expect. The first trigger takes the true branch; the second, the false branch. You can see it in the history: in the first execution the flow continues toward the effect; in the second, it goes down the duplicate branch and stops with no charge.

Step 6: the "first time" branch — ledger and effect

On the true branch goes the real work, with the ledger's two-write pattern wrapped around the effect.

IF (true) 
  └─► Postgres: "Ledger — insert pending"    ← Write 1 (before the effect)
        └─► [Effect]  Create charge in CRM     ← the charge (simulated in tests)
              └─► Postgres: "Ledger — mark done"  ← Write 2 (after the effect)

Write 1 is a Postgres node, Execute Query operation:

Query:
  INSERT INTO run_ledger (idempotency_key, order_id, status)
  VALUES ($1, $2, 'pending');

Query Parameters:
  {{ [ $json.idempotency_key, $json.order_id ] }}

The effect. In the real order-triage, the HTTP Request that creates the charge in the CRM goes here. To test without charging anyone or spending anything, safely simulate the effect: you can point the HTTP Request at a harmless test endpoint that just echoes what it receives, or temporarily replace it with an Edit Fields (Set) node writing something like { "charge_created": true, "charge_id": "CHG-TEST-001" }. What matters for the project is that the effect happens only once; how real the charge is is secondary while you're learning. Leave a clear comment that in production that node is the real call to the CRM.

Write 2 updates the entry to done with the result:

Query:
  UPDATE run_ledger
  SET status = 'done',
      result = $2::jsonb,
      updated_at = now()
  WHERE idempotency_key = $1;

Query Parameters:
  {{ [ $json.idempotency_key, JSON.stringify({ charge_id: $json.charge_id }) ] }}

Notice the $2::jsonb: we tell Postgres that parameter is JSON, so it fits into the JSONB-typed result column. We build the content with JSON.stringify from what the effect returned. If your version's exact parameter format differs, check it on the panel; the idea —updating the same entry to done with the result— is what governs.

What to expect on the first-time branch. After running this branch for ORD-2041, the run_ledger table has one entry that went from pending to done, with result containing the charge_id and updated_at later than created_at. The effect ran once. And in processed_orders there's one row with the key. All the system state, consistent.

Step 7: the "duplicate" branch and the final test

On the false branch there's almost nothing to do —that's the point—: the effect doesn't run. Optionally, you can leave a record that a duplicate arrived, for example by responding to the webhook with "already processed" or logging the event; but the essential part is that the effect doesn't run.

Now, the test crowning the project. With the workflow active, fire the webhook twice with the same ORD-2041. Then run these verification queries (Postgres nodes in Select or Execute Query):

-- How many times was this order recorded in the dedup store?
SELECT count(*) AS times FROM processed_orders WHERE order_id = 'ORD-2041';

-- How many 'done' entries are there for this order in the ledger?
SELECT count(*) AS charges FROM run_ledger
WHERE order_id = 'ORD-2041' AND status = 'done';

What to expect —and this is this entire module's goal—. The first query returns 1: a single key in the dedup store, even though you fired twice. The second returns 1: a single charge. And if you look at n8n's history, you see two executions in green: both ran with no error, but only one reached the effect. The second took the duplicate branch and stopped before charging.

That's idempotency demonstrated: not that the system "didn't fire twice" —it did fire twice—, but that firing twice, it acted only once. The duplicate wasn't prevented; it was absorbed. It's exactly the promise the module opened with, now turned into something you can run and show.

What to answer whoever fired it: the response is idempotent too

There's a detail separating a learning project from one ready for the real world, and it's worth including because it closes the loop. The question is: when the second trigger arrives and you discard it, what do you answer whoever sent it?

Think of it from the other side. The system calling your webhook fired twice precisely because, often, it didn't get a clear response the first time —that's why it retried—. If you answer its second attempt with an error, or don't answer at all, it's going to think something's still wrong and might retry a third time. The right response to a duplicate isn't an error: it's the same success response it would have gotten the first time, so the caller understands "done, this is already processed" and stops retrying.

That's why, on the false branch (duplicate), the ideal isn't just stopping silently, but responding to the webhook with success, ideally with the same result the first time produced. And you have that result: it's stored in run_ledger, in the original done entry's result column. The duplicate branch can query it and return it:

IF (false, duplicate)
  └─► Postgres: look up the original result
        SELECT result FROM run_ledger
        WHERE idempotency_key = $1 AND status = 'done';
  └─► Respond to Webhook:  200 OK, with the retrieved result
        (the caller receives "already processed, here is your result" and stops retrying)

Notice how elegant this turns out: the ledger you built for auditing and recovery also serves you for responding idempotently. The first time you stored the result; on the duplicate, you return it instead of recalculating it. The caller gets the same response both times, which is the purest definition of idempotency from the outside: making the same request twice produces the same response and a single effect. For the learning project this is optional, but knowing it exists is what turns "I didn't duplicate the charge" into "I built a genuinely idempotent API."

The complete flow, at a glance

Webhook: "Cumbre order in"
  └─► Code: "Compute idempotency key"                (crypto → idempotency_key)
        └─► Postgres: "Dedup gate"                   (INSERT ... ON CONFLICT ... RETURNING)
              └─► IF: "Is it the first time?"
                    │
                    ├── true (returned the key) ─────────────────────────────┐
                    │     └─► Postgres: "Ledger — insert pending"  (status='pending')
                    │           └─► HTTP Request: "Create charge"  (the effect, only once)
                    │                 └─► Postgres: "Ledger — mark done"  (status='done', result)
                    │
                    └── false (returned empty) ───────────────────────────────┐
                          └─► (optional) respond "already processed" / log duplicate
                                (NO effect)

That diagram is half the deliverable; the CREATE TABLE for both tables is the other half. Together they're a complete, defensible idempotent system.

Definition of done

Before considering the project closed, review this list. It isn't bureaucracy: every point is one of the decisions the module defended, and if any fails, the system duplicates in some scenario.

• The run_ledger and processed_orders tables exist in local Postgres and start empty (SELECT count(*) gives 0).
• The idempotency_key is the same on both triggers of the same order (includes no time or attempt number).
• The gate uses INSERT ... ON CONFLICT DO NOTHING RETURNING and passes values through parameters ($1, $2), not concatenated.
• The effect is after the gate and only on the first-time branch.
• The ledger writes pending before the effect and done after.
• The test passes: two triggers of the same order → one row in processed_orders, one done entry, a single effect, two executions in green.
• (Optional, production level) The duplicate branch responds to the webhook with success and the original result retrieved from the ledger.

If the first six are checked, you have a correct idempotent system. The seventh is the extra mile that takes it to production.

Common mistakes

Testing the double trigger from the editor and getting confused by the result (practical). What happens: the flow gets tested from the editor instead of through the active webhook, and something behaves differently than expected. Why it happens: the editor and the active webhook aren't identical; for a faithful test of the double trigger, the active workflow is worth using. How to spot it: if your two "triggers" were two clicks of "Test workflow," it isn't the real test. How to fix it: activate the workflow and fire it twice through its production URL. Your Postgres state persists the same way, but the honest test of the scenario is with the workflow active.

Putting the effect before the gate (conceptual, the one that ruins everything). What happens: by oversight, the charge's HTTP Request ends up before the dedup node, so it always charges and the gate only decides afterward. Why it happens: the flow gets built out of order. How to spot it: if the effect runs in both executions, look at it: it's probably before the gate or on the wrong branch. How to fix it: the gate goes before the effect, and the effect goes only on the true branch. Nothing should be able to charge without first passing through the gate and landing on "first time."

Forgetting to enable RETURNING or misbuilding the IF (practical). What happens: the gate returns nothing useful, or the IF evaluates wrong, and both executions take the same branch. Why it happens: RETURNING is missing, or the IF's condition doesn't match what the Postgres node produces in the duplicate case. How to spot it: if both executions charge, or neither does, review the gate and the IF's condition. How to fix it: make sure the query has RETURNING idempotency_key, and test on the panel what the node returns in the duplicate case (zero items or an empty item) to adjust the condition.

Putting something that changes between triggers into the key (conceptual). What happens: idempotency_key includes a timestamp or an execution id, so the two triggers generate different keys, neither collides, and it charges twice. Why it happens: identifying the work gets confused with identifying the attempt. How to spot it: if both executions produce different idempotency_keys for the same order, the key is wrong. How to fix it: the key should depend only on what defines the order (its order_id and content), never on the moment or the attempt number.

Writing the ledger only at the end (conceptual). What happens: a single write happens to the ledger, as done, after the effect, skipping pending. Why it happens: it seems simpler. How to spot it: if your run_ledger never has rows at pending, this is it. How to fix it: write pending before the effect and update to done after. If the system crashes between the effect and the final write, the prior pending is the only clue something got left halfway; without it, you'd have a charge with no trace.

Exercises

Exercise 1 — Break it on purpose. With your project working, make these three changes one at a time, predict what will happen, run it, and compare. (a) Move the effect so it comes before the gate. (b) Change idempotency_key to include Date.now(). (c) Remove RETURNING from the gate.

Exercise 2 — Isolate the stuck state. Simulate a crash: on the true branch, after Write 1 (pending) and the effect, temporarily disable Write 2 (done), and fire an order. Then, write the query that finds entries stuck at pending for more than, say, five minutes, and explain what it would be used for in a real system.

SELECT order_id, idempotency_key, created_at
FROM run_ledger
WHERE status = 'pending'
  AND created_at < now() - INTERVAL '5 minutes';

Exercise 3 — Defend it. Write the two-minute script you'd use to present this project in an interview, answering "how do you guarantee a webhook that fires twice doesn't charge twice?" It should cover: the problem, where the truth lives, the atomic mechanism, and the proof.

Summary and next step

You closed the module by building the complete system. You have, running on your machine and at zero cost, a run ledger and a deduplication store in local Postgres, connected to a webhook that fires twice, with the proof in plain view: a single key in processed_orders, a single done entry in run_ledger, a single effect, and both executions green. The deliverable —the table schema plus the flow that uses them— is defensible in an interview or a portfolio.

And most important: every piece is where it is for a reason you now understand. The gate goes before the effect and on the first-time branch. The key identifies the work, not the attempt. The ledger writes pending before and done after, so crashes leave a trace. The truth lives in a database, not in the workflow's memory. You broke the system on purpose to see what holds up each part, and you isolated a stuck state to see pending's value.

Before closing you should be able to: build the complete flow from memory in its seven steps; explain every design decision; and demonstrate idempotency with two triggers and two queries.

With this, the system's data model comes to an end. What follows, in module 5, is coordinating several workflows that depend on each other without one duplicating another's work: orchestration and choreography, the dependency graph, fan-out and fan-in with no lost items, and the outbox pattern —deciding and executing separately—, which rests directly on the ledger you just built. A single idempotent workflow is the foundation; a system of coordinated, reliable workflows is the goal. You already have the foundation.

Resources

• Webhook node — n8n Docs — the project's trigger: how it's configured, the production URL vs. the test one, and how to receive the order's POST.
• Postgres node — n8n Docs — the Execute Query operation for the gate, the ledger, and the verification queries, with the $1, $2 parameter placeholders.
• IF node — n8n Docs — the branching between "first time" and "duplicate" based on what the gate returned.
• PostgreSQL — INSERT ... ON CONFLICT — the reference for the atomic mechanism making the gate proof against the double-trigger race.
• Deploy with the AI starter kit — n8n Docs — the local stack sustaining the whole project: n8n and the Postgres where the tables live, at zero cost.