5. A dedup store across executions

Description

By the end of this lesson you'll be able to build a deduplication store in Postgres that decides, in a single atomic, indivisible step, whether an order is new or repeated —and does so safely even when two triggers for the same order arrive almost at the same time—. You're going to understand what a uniqueness constraint is, exactly what INSERT ... ON CONFLICT DO NOTHING does, and how the RETURNING clause lets you know whether you just inserted (first time) or collided (duplicate). You're going to see how the Postgres node passes parameters safely so as not to open the door to SQL injection, how to compute the idempotency_key with crypto in a Code node, and how to branch the workflow to act only when it should.

This matters because this is where the "check then act" trap module 2 flagged finally gets closed, the one none of the alternatives —not Static Data, not the time window, not the Remove Duplicates node— could fully close against the near-simultaneous double trigger. The solution isn't writing more code; it's taking advantage of a guarantee the database already gives you. It's the module's most technical lesson and the one that makes everything before it useful: without this atomic step, you'd have a ledger that records duplicates but a system that keeps creating them.

Connection to the module: lesson 3 built the ledger, which records; this one builds the store that decides. Lesson 4 gave you the criterion for knowing why you choose the seen-key in your own table; this one implements it. idempotency_key comes from module 2 and lesson 3's design. And the result —a clean branch between "first time" and "duplicate"— is what lesson 8's project connects to a double-firing webhook to prove, end to end, that the second trigger doesn't create a second charge.

The two-step trap, and why the database is what closes the door

Let's go back to the trap, because understanding it is understanding why the solution is so elegant.

Deduplicating looks like two actions: first you check whether you've already seen the key, and if not, you act (and record it). The problem is the gap between those two actions. Imagine two doormen working the same door with the same list, and two identical people arriving at the same instant. Doorman A looks at the list: "not there." Doorman B, the same second, looks at the list: "not there" too, because A hasn't written it down yet. Both conclude "it's new," both let them through, both write it down. Result: they got in twice, and the list doesn't even look wrong —it has a single entry—.

That's exactly what happens when ORD-2041's webhook fires twice almost together. Each trigger is an execution that "checks" and "acts." If both check before either one records, both see "not there" and both create the charge. The gap between checking and acting is a race condition, and you don't close it by checking "more carefully": no matter how fast you check, there's always an instant between the check and the record where the other trigger can slip in.

The only way to close the gap is to eliminate it: making "check if it exists" and "record that it now exists" a single indivisible operation, impossible to interrupt halfway. And that's precisely what a relational database knows how to do and a trick inside the workflow doesn't. Back to the doormen: instead of "look at the list and then write it down," the database offers a turnstile with a reader: you swipe the card and the turnstile, in a single atomic motion, either lets you through and marks your entry, or locks because your mark was already there. There's no instant between "checking" and "marking" where someone else can slip in, because they're the same gesture.

That turnstile has two pieces in Postgres: a uniqueness constraint on the key column, and the ON CONFLICT DO NOTHING clause. Let's build them.

The uniqueness constraint: the database guarantees there are no repeats

First, the table. The dedup store is deliberately minimal —remember from lesson 4 that its job is a fast yes/no, not storing the ledger's rich history—:

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 matters is in the first column: idempotency_key TEXT PRIMARY KEY. By declaring it PRIMARY KEY, you're telling Postgres two things at once: that this column identifies every row, and —what matters to us— that there can never be two rows with the same value. A primary key is unique by definition. (You could also use UNIQUE on a separate column; for this table, where the key is the identity, PRIMARY KEY is the natural choice.)

What is a uniqueness constraint? It's a rule the database enforces for you on every write. It isn't something you program and hope to remember to apply in every workflow: it's a property of the table. From the moment you declare it, Postgres physically rejects any attempt to insert a second row with an idempotency_key that already exists. It doesn't matter who tries it, from which workflow, or how many triggers arrive at once: the database is the referee, and its rule is unbreakable.

Think of it as the lock on a numbered locker. Locker 42 fits one padlock and only one. If you already put yours on, the next person trying to hang a padlock on 42 finds it doesn't fit: the lock is already occupied. There's no need to watch or ask; the locker itself enforces that there's only one padlock. The uniqueness constraint is that lock, applied to a column's values.

This guarantee is half the solution. The other half is what to do when the lock is already occupied.

ON CONFLICT DO NOTHING: insert, or do nothing, with no failure

If you try to insert a repeated key into a unique column, by default Postgres throws an error —"duplicate key"— and stops the operation. That, in a workflow, means the Postgres node turns red and the execution cuts off. You could catch that error and decide what to do, but there's a much cleaner way, made exactly for this.

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

Let's take it apart, because every piece does a precise job:

• INSERT INTO processed_orders (idempotency_key, order_id) VALUES ($1, $2) — tries to insert a row with the key and the order. The $1 and $2 are parameter placeholders, not the values themselves; we'll see right away why they matter.
• ON CONFLICT (idempotency_key) — "if this insertion collides with the uniqueness constraint on the idempotency_key column…". It's the branch that activates when the lock was already occupied.
• DO NOTHING — "…then do nothing, and above all, do not throw an error." The node doesn't turn red. The operation ends cleanly with no insertion.
• RETURNING idempotency_key — "and give me back the key of the row you inserted." This line is what lets you know what happened.

And here's the trick that makes it all work. That statement has two possible outcomes, and RETURNING tells you which one occurred:

• If the key was new: Postgres inserts the row and RETURNING returns you a row with the idempotency_key. Translation: "it was the first time; you just recorded it; act."
• If the key already existed: ON CONFLICT DO NOTHING inserts nothing, so there's no row to return and RETURNING returns nothing (zero rows). Translation: "it was a duplicate; it was already recorded; don't act."

Pause on how elegant this is. In a single statement, indivisible, you did three things: you checked whether the key existed, you recorded it if it didn't, and you found out which of the two cases it was. There's no gap between "checking" and "recording" for the other trigger to slip into, because they aren't two steps: they're one. The race condition disappears, not because you handle it carefully, but because the operation is atomic by construction. It's the turnstile with the reader: you swipe the key, and in a single motion it either goes through and gets marked, or it locks.

Against the double trigger, here's what happens:

Trigger #1  →  INSERT ... ON CONFLICT DO NOTHING RETURNING  →  returns a row  →  ACTS (creates the charge)
Trigger #2  →  INSERT ... ON CONFLICT DO NOTHING RETURNING  →  returns EMPTY  →  DISCARDS

Even if both triggers arrive the same second, the database serializes them: one wins the INSERT and gets the row back; the other collides with the constraint and gets nothing. There's no way for both to win, because the lock only fits one padlock. It's impossible to charge twice.

The parameters: $1, $2, and why you never concatenate values into the SQL

Look again at VALUES ($1, $2). Those placeholders deserve an explanation, because they involve one of the most important security rules in any system that talks to a database.

The beginner's temptation is to build the query by pasting the values directly into the SQL text, something like VALUES (' + orderId + '). Never do that. If a value contains quotes or SQL fragments —by accident or because someone put them there on purpose— that text mixes with your query and can change what the query does. That's SQL injection, one of the oldest and most costly vulnerabilities there is. Paste a malicious value into a WHERE and you could be deleting a table without knowing it.

The solution is to separate the query's text from the values. Instead of pasting the value, you leave a placeholder —$1, $2, $3…— and deliver the values through a separate channel. The database then treats those values as pure data, never as part of the SQL: no matter what they contain, they can't alter the query's structure. Per the Postgres node's documentation, "n8n sanitizes the data in the query parameters, which prevents SQL injection."

In the Postgres node, with the Execute Query operation, it looks like this:

# 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 (the values substituting $1, $2, in order):
  {{ [ $json.idempotency_key, $json.order_id ] }}

The SQL text goes in the Query field, with the $1 and $2 placeholders. The values go separately, in the Query Parameters field, as a list in the placeholders' order: the first element substitutes $1, the second $2. The database receives the structure on one side and the data on the other, and joins them safely.

An honesty and verification note: the details of what the field is called and what exact format it expects can vary between node versions. The documentation as of this guide's writing points to numbered placeholders $1, $2, $3 for the Execute Query operation, and a parameters field where you deliver the values. If your version's node looks different, the idea that doesn't change is this: the SQL text and the values travel through separate channels; you never paste a value inside the text. Confirm the exact shape on your node's panel.

The idempotency_key: computing it with crypto in a Code node

The key you put into $1 comes from a Code node, same as in lesson 3. Remember n8n 2.0's rules: the Code node can't make HTTP requests or touch the database —the Postgres node handles that—, but it can use crypto to compute a hash.

// ============================================================
// Node: Code — "Compute idempotency key"
// Mode: Run Once for Each Item
//
// INPUT:   a Cumbre order with order_id
// OUTPUT:  the same item, with a computed idempotency_key
// NOTE:    crypto IS allowed in n8n 2.0's Code node.
//          We do no HTTP and touch no Postgres here; the Postgres node handles that.
// ============================================================

const crypto = require('crypto');

const order = $input.item.json;

// The key identifies THE WORK, not the attempt. Two triggers of the same
// order with the same content must produce the same key, so the second
// one collides with the uniqueness constraint and gets discarded.
const raw = `${order.order_id}:${order.order_total}`;
const idempotencyKey = crypto.createHash('sha256').update(raw).digest('hex');

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

Two design decisions worth flagging. First, what you put into the hash defines what you consider "the same work" —exactly lesson 4's false-duplicate topic—. Here we use order_id plus the total: two identical triggers for order ORD-2041 produce the same key and the second gets discarded; but if Luna Coffee places a genuinely new order, its different order_id generates a different key and it gets processed. Second, if your order_id is already a unique, stable identifier on its own, you can use it directly as a natural key, with no hash; the hash is useful when you want the key to depend on several fields or when the natural identifier is long or sensitive.

The complete workflow: branching between "first time" and "duplicate"

Let's put the pieces together. The dedup store sits as a gate before the effect: nothing reaches the charge creation without going through it first.

Webhook
  └─► Code: "Compute idempotency key"
        └─► Postgres: "Dedup gate"  (Execute Query, INSERT ... ON CONFLICT ... RETURNING)
              └─► IF: "Did it insert?"
                    ├─ true  (returned a row → first time)
                    │    └─► AI Agent → HTTP Request: "Create charge in CRM"
                    │
                    └─ false (returned empty → duplicate)
                         └─► (do nothing / log the duplicate / respond "already processed")

The gate is the Postgres node with ON CONFLICT ... RETURNING. Next comes an IF node checking whether the gate returned a row or not. The "first time" branch continues to the effect; the "duplicate" branch creates nothing.

There's a practical detail worth being honest about here, because it depends on your version's node behavior. When RETURNING returns no rows (the duplicate case), the Postgres node can behave in two ways: producing zero output items —in which case the following nodes simply don't run, and the effect is avoided on its own— or producing an empty item. To not depend on that detail, the robust pattern is to add the IF that explicitly checks whether an idempotency_key came back:

# Node: IF — "Did it insert?"
Condition:  {{ $json.idempotency_key }}  ->  exists / is not empty
  - true  ->  first time, continues to the effect
  - false ->  duplicate, discard branch

If the gate returned the key, it's the first time and the IF sends it to true. If it returned empty, it's a duplicate and the IF sends it to false. Confirm on your version's panel whether the node produces zero items or an empty item in the duplicate case, and adjust the IF's condition to what you see; the logic —"act only if the gate returned the key"— is the same either way.

Worked example: ORD-2041's two triggers

Let's follow order ORD-2041 through its two triggers, step by step.

First trigger, 09:12. The webhook receives ORD-2041. The Code node computes the key —say a1b2c3…—. The gate runs INSERT ... ON CONFLICT DO NOTHING RETURNING. Since key a1b2c3… wasn't in processed_orders, the row gets inserted and RETURNING returns { idempotency_key: 'a1b2c3…' }. The IF sees the key, goes to true, the AI Agent classifies, and the HTTP Request creates the charge. All correct.

Second trigger, 09:19. The webhook receives ORD-2041 again, in a new execution that knows nothing about the previous one. The Code node computes the same key a1b2c3… —because the key identifies the work, not the attempt—. The gate runs the same INSERT, but this time the key a1b2c3… already exists in the table. ON CONFLICT DO NOTHING inserts nothing, RETURNING returns nothing, and the IF goes to false. No second charge gets created.

What to expect. In processed_orders there's a single row for a1b2c3…, written at 09:12. In Cumbre's CRM there's a single charge. And —this is what matters— both executions show up green in n8n's history: both ran fine, neither failed. The second one simply took the duplicate branch and didn't perform the effect. The system did the right thing with no errors, which is idempotency's goal: repeating without duplicating.

And now you did solve the case neither Static Data nor the time window could: the second trigger arrived seven minutes later, in another execution, and still got discarded. Not because a window caught it —seven minutes could fall outside many windows—, but because the key stayed persistently recorded and the database atomically guaranteed only one insertion could win.

Combining with the ledger: deciding and recording

It's worth seeing how this store coexists with lesson 3's ledger, because in the real order-triage you use both. The dedup store is the fast gate deciding yes/no; the ledger is the book recording the history. A reasonable order:

Webhook → Code (key)
   → Postgres: Dedup gate (ON CONFLICT ... RETURNING)   ← decides
        ├─ duplicate → log in the ledger "seen again" (optional) and stop
        └─ first time
             → Postgres: Ledger insert 'pending'         ← records intention
                  → AI Agent → HTTP Request: create charge  ← effect
                       → Postgres: Ledger update 'done'   ← records outcome

The gate cuts off duplicates before they touch the effect or fill the ledger with noise. The ledger, on the first-time side, stores the rich history you're going to want when someone asks what happened. Each table does what it does well: the store decides in an instant, the ledger remembers in detail.

Common mistakes

Checking with a SELECT and then inserting in two steps (conceptual, and it's the big one). What happens: someone builds a Postgres node that queries SELECT ... WHERE idempotency_key = ..., an IF checking whether something came back, and only if not, another node that inserts and acts. It works in tests and fails with the near-simultaneous double trigger. Why it happens: they're two steps with a gap in between; two executions can both do the SELECT before either inserts, both see "not there," and both act. It's the intact race condition. How to spot it: if your deduplication has a SELECT followed by an INSERT in separate nodes, you have it. How to fix it: collapse both steps into one with INSERT ... ON CONFLICT DO NOTHING RETURNING. The atomicity doesn't come from how careful you are, it comes from it being a single statement.

Concatenating values into the SQL text (practical, and dangerous). What happens: the query gets built by pasting order_id directly into the string, and everything "works" until a value with odd characters breaks the query or —worse— someone injects SQL. Why it happens: concatenating is the first thing that comes to mind. How to spot it: if your Query field has quotes wrapped around a {{ }} expression instead of a $1, you're concatenating. How to fix it: use $1, $2 placeholders in the text and deliver the values through the parameters field. Never paste a value inside the SQL.

Forgetting RETURNING and not knowing what happened (practical). What happens: INSERT ... ON CONFLICT DO NOTHING gets used with no RETURNING, the insertion never fails (good), but the workflow has no way to tell "I inserted" from "I collided," so it always acts. Why it happens: ON CONFLICT DO NOTHING alone prevents the error but doesn't inform you of the outcome. How to spot it: if your gate has no RETURNING and you still branch, what are you branching on? How to fix it: add RETURNING idempotency_key so the "first time" case returns a row and the "duplicate" case returns empty, and branch on that.

Putting a field into the key that changes between attempts (conceptual). What happens: idempotency_key includes a timestamp of the trigger's moment, so the two triggers of the same order generate different keys and neither collides: it gets charged twice. Why it happens: "identifying the attempt" gets confused with "identifying the work." How to spot it: if your key changes when you retry the same work, it's built wrong. How to fix it: the key should depend only on what defines the work (the order and its content), not on the moment or the attempt number. It's module 2's key lesson applied here.

Assuming the node's behavior in the "zero rows" case (practical). What happens: the effect gets connected directly after the gate expecting "zero rows" to skip it, and on a certain version the node emits an empty item that does trigger the effect. Why it happens: the node's behavior on an empty RETURNING can vary between versions. How to spot it: test the duplicate case and see whether the effect runs when it shouldn't. How to fix it: don't depend on implicit behavior; put an explicit IF checking whether the idempotency_key came back, and let the effect through only on the true branch.

Exercises

Exercise 1 — Trace the two outcomes. For the statement INSERT INTO processed_orders (idempotency_key, order_id) VALUES ($1, $2) ON CONFLICT (idempotency_key) DO NOTHING RETURNING idempotency_key;, answer: (a) what it returns when the key is new, (b) what it returns when the key already existed, and (c) why in the second case the node does not turn red.

Exercise 2 — Find the race. A coworker implemented this deduplication: (1) a Postgres node doing SELECT idempotency_key FROM processed_orders WHERE idempotency_key = $1; (2) an IF checking whether anything came back; (3) on the "nothing came back" branch, a node that creates the charge and another that does INSERT INTO processed_orders .... Explain why, with the near-simultaneous double trigger, this can charge twice, and how you'd fix it with a single statement.

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

Exercise 3 — Design the key. For each Cumbre case, propose what the idempotency_key should include and explain why, being careful not to let duplicates through or discard legitimate orders.

(a) A web order with an order_id the online store guarantees is unique and stable. (b) A rep_csv order where the same file can be uploaded twice, and order_id sometimes arrives empty but customer_id, created_at, and the lines are always there. (c) A flow where a customer can, legitimately, place two orders with identical content the same day (same product list), and both should be charged.

Summary and next step

In this lesson you closed the "check then act" trap. The problem was the race condition: between checking whether a key exists and recording that it now exists there's a gap the double trigger slips through, and you don't close it by checking more carefully. You close it by eliminating the gap, making checking and recording a single atomic operation.

That operation has two pieces in Postgres. A uniqueness constraint on the idempotency_key column —a PRIMARY KEY or a UNIQUE— that makes the database reject any second row with the same key: the locker lock that only fits one padlock. And the INSERT ... ON CONFLICT DO NOTHING RETURNING statement, which tries to insert and, in a single indivisible gesture, either inserts and returns you the key (first time, act) or silently collides and returns empty (duplicate, discard). With an IF checking whether the key came back, you branch between acting and not acting.

You saw how the Postgres node passes the values with $1, $2 placeholders through a channel separate from the SQL text —so as not to open the door to injection—, how to compute idempotency_key with crypto in a Code node, and how the key must identify the work and not the attempt so as not to let duplicates through or discard legitimate orders. And you tested it with ORD-2041: two triggers seven minutes apart, a single charge, both executions green.

Before moving on you should be able to: explain why a SELECT followed by an INSERT doesn't close the race; describe what RETURNING returns in each outcome; and say why you never concatenate values into the SQL text.

Lesson 6 brings all of this down to earth: how to connect these Postgres nodes to the real Postgres the Self-Hosted AI Starter Kit v2 already ships with, with the credential set up step by step, so the run_ledger and processed_orders tables live in a real database, running on your machine, at zero cost. Up to here you designed the mechanism; now you're going to plug it in.

Resources

• PostgreSQL — INSERT ... ON CONFLICT — the official reference for ON CONFLICT DO NOTHING, the RETURNING clause, and how insertion behaves on a conflict with a uniqueness constraint. The exact source for this lesson's central pattern.
• PostgreSQL — Constraints (Unique) — what a uniqueness constraint and a primary key are, and the guarantee that the database rejects duplicate rows.
• Postgres node — n8n Docs — the Execute Query operation, the $1, $2, $3 parameter placeholders, and the note that n8n sanitizes parameter data to prevent SQL injection. Confirm your version's exact format there.
• Code node — n8n Docs — the node where you compute idempotency_key with crypto, and its limits in n8n 2.0.
• IF node — n8n Docs — the node you use to branch between "first time" and "duplicate" based on what the gate returned.