8. Project: coordinating three dependent workflows

Description

By the end of this lesson you will have built, with your own hands, the complete system this module has been preparing you for: a director (order-triage), an outbox, and an idempotent relay that coordinate Cumbre's three dependent sub-workflows — check-credit, issue-refund, and inventory-sync — and, most importantly, you will prove that a mid-chain crash neither duplicates nor loses effects on retry. The deliverable is two concrete, defensible things: the system's dependency graph (what you learned to draw in lesson 3) and the running system, with evidence — ledger and outbox queries — that it survives all three disasters from lesson 1.

This matters because it's where everything stops being theory. You can understand the outbox pattern by reading about it; you're going to believe it when you kill an execution halfway through, retry it, and query the database to see the refund was issued exactly once. That moment — seeing with your own eyes that the system recovered without duplicating — is what turns knowledge into confidence. And it's also a portfolio deliverable: a multi-workflow system you can show in an interview saying "this survives duplicate triggers and partial crashes, and here's the proof."

Connection to the module: this project is the integration of all eight lessons. You draw the graph (lesson 3), decide what gets orchestrated and what goes through the outbox (lessons 2 and 6), protect inventory's fan-out with per-line keys (lesson 4), preserve order with ticket age (lesson 5), and it all rests on idempotency (Module 2), contracts (Module 3), and the ledger (Module 4). There's nothing new to learn here; there's everything to put together. If anything below isn't clear, the lesson that teaches it is flagged so you can go back.

The assignment

You're going to build Cumbre's refund and inventory coordination system. The business rule is simple:

When an order comes in, the customer's credit gets checked. If credit isn't enough, a refund of the deposit gets issued. If credit is enough, inventory gets discounted for each order line. In every case, the system must process each order exactly once in its effects, even if the webhook fires twice and even if an execution crashes halfway through.

Four pieces:

1. order-triage — the director. Receives the order, deduplicates, classifies, checks credit synchronously, and decides the effects by writing them to the outbox atomically. It doesn't execute effects itself.
2. check-credit — a read sub-workflow. Checks credit and returns the result. It's called synchronously because the director needs its response to decide.
3. outbox-relay — the relay. Reads pending tickets from the outbox and executes each effect idempotently, calling issue-refund or inventory-sync with the idempotency key.
4. issue-refund and inventory-sync — idempotent effect sub-workflows: they check the ledger before acting and register what got done.

A reminder of the n8n 2.0 rules you'll respect throughout the project: external effects (the gateway, the warehouse system) are done by HTTP Request nodes or — in the local lab — a Postgres node that simulates the external system; never a Code node, which can't make HTTP calls or touch the database. The Code node, if you use it, is only for building keys or classifying, with crypto or moment as the only available modules. Writes to the ledger and the outbox are done by the Postgres node. The agent/classifier decides; the dedicated nodes execute.

Part 1 — The dependency graph (first deliverable)

Before touching a single node, draw the graph. It's half the deliverable and it's what's going to guide the build. Using lesson 3's notation:

                         order-triage
                    (receives, deduplicates, decides)
              │                                   │
              │ (sync)                            │ (writes to outbox — does not wait)
              ▼                                   ▼
        check-credit [L]                    ┌─── outbox (table) ───┐
        (reads credit)                      │  pending tickets      │
                                            └───────────┬───────────┘
                                                        │ (Schedule Trigger)
                                                        ▼
                                                  outbox-relay
                                          (reads tickets, executes idempotently)
                                            │                      │
                                            │ (sync, with          │ (sync, with
                                            │  Idempotency-Key)    │  Idempotency-Key)
                                            ▼                      ▼
                                      issue-refund [E]      inventory-sync [E]
                                      (moves money)         (changes stock)

Shared resources (hidden dependencies):
  ⚠ ledger/outbox Postgres  → all five workflows depend on it
  ⚠ payment gateway          → issue-refund (and the original charge)

Read your own graph before moving on, because it already tells you how the system behaves:

• order-triage → check-credit is a synchronous arrow to a read: the director waits for the result to decide. Safe to repeat; cascade risk if check-credit lags.
• order-triage → outbox is not a call: it's an atomic write. The director writes the ticket and moves on, without waiting for any effect. This is where the cascade gets cut: the director never blocks waiting on the gateway.
• The outbox-relay is the only one that touches the effects, synchronously and with an idempotency key. It's the only one that calls issue-refund and inventory-sync.

Notice the overall shape: the decision part (above) is fast and touches no effects; the execution part (below, via the relay) touches the effects and is idempotent. That separation is the outbox pattern, drawn out. Keep this diagram: it's the first thing you'd show when explaining the system.

Part 2 — The data model

Two tables in your local Postgres (from Module 4's Starter Kit). You can use two tables or unify them; here I keep them separate so they read clearly.

The ledger, the shared memory of which effects already happened:

CREATE TABLE ledger (
  effect_key   TEXT PRIMARY KEY,           -- the effect's idempotency key
  order_id     TEXT NOT NULL,
  effect_type  TEXT NOT NULL,              -- 'refund' | 'inventory'
  status       TEXT NOT NULL,              -- 'done'
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

The outbox, the ticket rail:

CREATE TABLE outbox (
  id               BIGSERIAL PRIMARY KEY,
  aggregate_id     TEXT NOT NULL,          -- order_id
  effect_type      TEXT NOT NULL,          -- 'issue_refund' | 'inventory_sync'
  payload          JSONB NOT NULL,
  idempotency_key  TEXT NOT NULL UNIQUE,   -- the same one that will go to the ledger
  status           TEXT NOT NULL DEFAULT 'pending',   -- pending | processing | done
  attempts         INT NOT NULL DEFAULT 0,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at     TIMESTAMPTZ
);

And a table that simulates the external systems — the gateway and the warehouse — so that in the local lab, at zero cost, you can verify "the effect happened N times" by counting rows. In production, this would be the real API; here it's your way of seeing the truth:

-- Simulates the outside world: every row is an effect that "went out into the world."
-- Counting rows here = counting how many times an effect really happened.
CREATE TABLE external_effects_log (
  id           BIGSERIAL PRIMARY KEY,
  effect_key   TEXT NOT NULL,             -- NO unique: we want to be able to see duplicates if any occur
  effect_type  TEXT NOT NULL,
  order_id     TEXT NOT NULL,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

That external_effects_log is your measuring instrument. The whole project's proof is going to be: count external_effects_log's rows for an order; they must be exactly the ones that should be there, not one more, no matter what happens.

Part 3 — The idempotent effect sub-workflows

Every effect checks the ledger before acting. This is Module 4's piece, and they're structurally identical; I show issue-refund and the pattern repeats for inventory-sync.

# Sub-workflow: issue-refund
#   Trigger: Execute Sub-workflow Trigger (Define using fields below)
#   Input contract: order_id, amount, currency, effect_key

1. Postgres — is it already done?
     SELECT 1 FROM ledger WHERE effect_key = {{ $json.effect_key }};

2. IF (row exists) → return { status: 'already_done' } and finish.

3. (doesn't exist) Real effect: register in external_effects_log
     INSERT INTO external_effects_log (effect_key, effect_type, order_id)
     VALUES ({{ $json.effect_key }}, 'refund', {{ $json.order_id }});
   -- In production, an HTTP Request to the gateway would go here with
   -- Idempotency-Key = effect_key. The Code node CANNOT do this.

4. Postgres — record in the ledger that it's done
     INSERT INTO ledger (effect_key, order_id, effect_type, status)
     VALUES ({{ $json.effect_key }}, {{ $json.order_id }}, 'refund', 'done')
     ON CONFLICT (effect_key) DO NOTHING;

5. Return { status: 'done' }.

inventory-sync is the same, with effect_type = 'inventory' and its own per-line effect_key. Notice the double protection: the check in steps 1-2 (check and act) avoids the work, and the ON CONFLICT in step 4 is the safety net in case two executions pass the check at the same time — Module 2's "check then act" trap, covered by the database's UNIQUE constraint. For the lab this is enough; in production, the real API's Idempotency-Key is the final safety net on the external effect's side.

Part 4 — The director (order-triage)

The director receives, deduplicates, classifies, checks credit, and decides the effects by writing them to the outbox atomically.

# Workflow: order-triage
#   Trigger: Webhook (receives Cumbre's order)

1. Postgres — deduplicate the event (Module 4)
     Did we already process this event_id? If so, respond 200 and finish.
     (this covers a webhook that fires twice for the same event)

2. AI Agent / Code — classify the order
     (this is where you decide priority/category; for the project, what
      matters is that after this you have order_id, customer_id, amount, line_items)

3. Execute Sub-workflow — check-credit   (SYNCHRONOUS: Wait for Completion = ON)
     Inputs: order_id, customer_id, amount
     Returns: credit_ok (true/false)

4. IF — {{ $json.credit_ok }}

   ── false branch (no credit): decide a refund ──
   5a. Postgres (ATOMIC) — write the refund ticket to the outbox
        INSERT INTO outbox (aggregate_id, effect_type, payload, idempotency_key, status)
        VALUES ('ORD-2041', 'issue_refund',
                '{"amount":2154.00,"currency":"MXN"}'::jsonb,
                'refund:ORD-2041', 'pending')
        ON CONFLICT (idempotency_key) DO NOTHING;

   ── true branch (has credit): decide the inventory discount ──
   5b. Split Out — split line_items into one item per line
   5c. Postgres (ATOMIC) — write ONE ticket per line to the outbox
        INSERT INTO outbox (aggregate_id, effect_type, payload, idempotency_key, status)
        VALUES ('ORD-2041', 'inventory_sync',
                '{"sku":"CF-ARA-500","quantity":12}'::jsonb,
                'inventory:ORD-2041:CF-ARA-500', 'pending')
        ON CONFLICT (idempotency_key) DO NOTHING;
        -- (repeats for each line; the Split Out makes this node
        --  run once per line)

6. Respond 200 to the vendor and finish.

The points that make this correct, each one from a lesson:

• Step 1, deduplication: covers a webhook fired twice for the same event_id (Module 4). If the same event arrives twice, the second one stops here.
• Step 3, synchronous: the director waits for check-credit because it needs its result for the IF. It's lesson 2's orchestration, correct here because there's a decision based on a result.
• Steps 5a/5c, ON CONFLICT DO NOTHING: make the decision idempotent. If the director ran twice for the same order (and somehow skipped deduplication), it wouldn't write duplicate tickets: the outbox's UNIQUE key rejects them. It's the second line of defense behind deduplication.
• The director doesn't execute effects. It writes tickets and responds. It doesn't wait on the gateway or the warehouse. If it crashes after step 5, the tickets are already on the rail, safe. This is where the cascade gets cut.

Part 5 — The relay (outbox-relay)

The relay runs on its own, with a Schedule Trigger, and drains the outbox.

# Workflow: outbox-relay
#   Trigger: Schedule Trigger (every 10 seconds, for example)

1. Postgres — pick up pending tickets (oldest first)
     SELECT * FROM outbox
     WHERE status = 'pending'
        OR (status = 'processing' AND processed_at IS NULL
            AND created_at < now() - interval '2 minutes')  -- recover stuck ones
     ORDER BY created_at
     LIMIT 10
     FOR UPDATE SKIP LOCKED;

2. Postgres — mark 'processing'
     UPDATE outbox SET status = 'processing', attempts = attempts + 1
     WHERE id = {{ $json.id }};

3. Switch — based on effect_type:
     'issue_refund'   → Execute Sub-workflow: issue-refund   (SYNCHRONOUS)
     'inventory_sync' → Execute Sub-workflow: inventory-sync (SYNCHRONOUS)
   Inputs passed: order_id (= aggregate_id), the unpacked payload,
   and effect_key = idempotency_key.

4. Postgres — mark 'done'
     UPDATE outbox SET status = 'done', processed_at = now()
     WHERE id = {{ $json.id }};

The relay inherits all of lesson 6's robustness:

• FOR UPDATE SKIP LOCKED: if you run two relays for more capacity, they don't take the same ticket.
• Recovering stuck processing tickets (step 1): a ticket left in processing because of a relay crash gets picked back up after two minutes. Safe, because the effect is idempotent.
• The order of 3 and 4 (effect, then mark done): this is the "Order B" that would be dangerous, but here it's correct because the effect is idempotent. If the relay crashes between 3 and 4, the ticket comes back as stuck, the effect gets re-executed, issue-refund sees the ledger already written and doesn't insert into external_effects_log again, and marks done. Exactly once.
• ORDER BY created_at: processes by age, preserving the order in which effects were decided.

Part 6 — The proof (the second deliverable, and the convincing one)

Here's the project's heart. Building the system is half of it; proving it survives is what makes it a system-owner deliverable. Three tests, each against one of the disasters. You measure the truth by counting rows in external_effects_log.

Test A — The happy path

Send an order with insufficient credit (so a refund gets decided). Let the relay run.

What to expect. The outbox has a refund:ORD-2041 ticket that goes from pending to processing to done. The ledger has one refund:ORD-2041 row. And the truth:

SELECT count(*) FROM external_effects_log WHERE order_id = 'ORD-2041' AND effect_type = 'refund';
-- Expected: 1

One refund. The system works in the normal case. Repeat with an order that has enough credit and verify there's exactly one inventory row per order line.

Test B — The duplicate webhook

Fire the same event twice (same event_id, same order_id), simulating a vendor retry or a double click. Let the relay run.

What to expect. The second trigger stops at the director's step 1 (deduplication by event_id) and doesn't write a second ticket. Even if it skipped deduplication, the outbox's ON CONFLICT would reject the duplicate ticket (same idempotency_key). The result:

SELECT count(*) FROM outbox WHERE idempotency_key = 'refund:ORD-2041';
-- Expected: 1  (a single ticket, even though the event arrived twice)

SELECT count(*) FROM external_effects_log WHERE order_id = 'ORD-2041' AND effect_type = 'refund';
-- Expected: 1  (a single refund)

Disaster 3 — the duplicated effect from a double trigger — did not happen. Two layers prevented it: event deduplication and ticket uniqueness.

Test C — The mid-chain crash (the star test)

This is the one that proves the outbox's value. You're going to kill the relay after the effect executed but before it marks the ticket as done, and then let it retry.

How to trigger it. In issue-refund, between step 3 (inserting into external_effects_log) and step 4 (recording in the ledger), forcibly stop the execution — disable the relay, or kill the execution from the panel, or temporarily put a node that fails right there. The goal is to deliberately leave the world in this inconsistent state:

• external_effects_log: has the refund row (the effect already went out).
• ledger: doesn't have the row (it never got to record it).
• outbox: the ticket is left in processing.

Verify that intermediate state:

SELECT count(*) FROM external_effects_log WHERE effect_key = 'refund:ORD-2041';  -- 1
SELECT count(*) FROM ledger WHERE effect_key = 'refund:ORD-2041';                -- 0
SELECT status FROM outbox WHERE idempotency_key = 'refund:ORD-2041';             -- processing

That's exactly lesson 6's "broken Order B" from the start: the effect happened, but the system didn't record it. Without idempotency, retrying here would duplicate the refund. Now re-enable the relay and let it run.

What to expect on retry. The relay picks up the ticket stuck in processing (step 1's logic). It calls issue-refund again. issue-refund runs its step 1 — checking the ledger — and... the ledger still says it isn't done (step 4 never ran the first time). This is where the double protection matters: the sub-workflow is going to attempt the effect again. But notice the design: in the lab, the safety net that avoids the duplicate is the order of operations inside issue-refund. For Test C to demonstrate exactly-once, issue-refund must record in the ledger in the same atomic operation as executing the simulated effect, or use the Idempotency-Key against an external_effects_log with a unique key. Adjust it like this:

-- issue-refund, version proofed against the mid-effect crash:
-- effect and ledger record, atomic, with the key as the lock.
WITH done AS (
  INSERT INTO ledger (effect_key, order_id, effect_type, status)
  VALUES ({{ $json.effect_key }}, {{ $json.order_id }}, 'refund', 'done')
  ON CONFLICT (effect_key) DO NOTHING       -- if already there, does nothing
  RETURNING effect_key
)
INSERT INTO external_effects_log (effect_key, effect_type, order_id)
SELECT {{ $json.effect_key }}, 'refund', {{ $json.order_id }}
FROM done;                                   -- only records the effect if the ledger entry was new

With issue-refund set up this way, the mid-effect crash leaves the world consistent: either both writes happened (ledger + effect) or neither did, because they're in a single transaction. Retrying is safe:

-- After retrying:
SELECT count(*) FROM external_effects_log WHERE effect_key = 'refund:ORD-2041';  -- 1
SELECT count(*) FROM ledger WHERE effect_key = 'refund:ORD-2041';                -- 1
SELECT status FROM outbox WHERE idempotency_key = 'refund:ORD-2041';             -- done

Exactly one refund, despite the crash. The system recovered: the ticket was safe on the rail, the retry completed it, and the atomicity of effect-plus-ledger guaranteed it wasn't duplicated. That query returning 1 is the proof of the entire module. It's what you show when someone asks you "and how do I know this doesn't charge twice?"

An honest note about the simulation. In the lab, effect-plus-ledger atomicity is easy because the "effect" is a row in your own database. With a real gateway, the effect lives in another house and doesn't fit inside your transaction — it's the dual-write problem again. There, the safety net is the Idempotency-Key you send to the gateway: retrying calls the gateway with the same key, and it deduplicates. The lab demonstrates the coordination mechanism (outbox + relay + ledger); the real API's Idempotency-Key is what carries the guarantee over to the external effect. Both pieces work together, as you saw in lesson 6.

Common mistakes

Executing the effect in the director "to avoid setting up the relay" (conceptual). What happens: order-triage gets built so that, after deciding, it calls issue-refund directly and synchronously, skipping the outbox and the relay. It works in tests A and B, and fails C: a crash between deciding and executing loses or duplicates the effect. Why it happens: the relay feels like an extra piece, and calling the sub-workflow directly is faster to build. How to detect it: if your director has an Execute Sub-workflow toward an effect, instead of an INSERT into the outbox, you didn't implement the pattern. How to fix it: the director decides and writes; the relay executes. Test C is the one that reveals whether you really separated the two things — if you fail it, the effect and the decision are still stuck together.

Putting the ticket and the decision in separate Postgres nodes (practical). What happens: the director changes a state in one node and writes the ticket in another; a crash between the two leaves the system inconsistent, and the outbox doesn't keep its promise because its premise — the atomic write — got broken. Why it happens: one node per operation is the natural reflex in n8n. How to detect it: if the decision and the ticket are in different nodes, they aren't atomic. How to fix it: a single statement or transaction — or, as in this project, let the outbox ticket be the decision's record, and then it's a single insert, trivially atomic.

Using the same idempotency key for every line (practical). What happens: when writing the inventory tickets, the key gets built with only the order_id, so all three lines share inventory:ORD-2041 and the ON CONFLICT only lets the first one through; one line gets discounted and two get lost. Why it happens: it's lesson 4's granularity mistake, here at the moment of writing tickets. How to detect it: if a three-line order generates a single inventory ticket, the key is too coarse. How to fix it: the fan-out's key carries the sku (inventory:ORD-2041:CF-ARA-500); one ticket per unit of work that must happen once.

Testing only the happy path and declaring the project done (conceptual). What happens: Test A gets run, everything works, and the system gets declared complete without running B or C. In production, the first duplicate webhook or the first crash reveals the coordination wasn't shielded. Why it happens: Test A is the satisfying one — everything green — and the other two take work. How to detect it: if you haven't killed an execution halfway through and retried it, you haven't tested what this module teaches. How to fix it: tests B and C are the deliverable; A only confirms the system does something. A system owner delivers evidence it survives what goes wrong, not that it works when everything goes right.

Exercises

Exercise 1 — Add the fan-out-with-partial-crash test. Design a fourth test (Test D) that combines the inventory fan-out with a partial crash: a three-line order where the relay processes two inventory tickets and crashes before the third. Say what state you expect in each table right after the crash, and what you expect after retrying, with the SQL queries that verify it.

-- external_effects_log: two inventory effects for the order
SELECT count(*) FROM external_effects_log
WHERE order_id = 'ORD-2041' AND effect_type = 'inventory';   -- 2

-- outbox: two 'done' tickets, one 'pending' or 'processing'
SELECT idempotency_key, status FROM outbox
WHERE aggregate_id = 'ORD-2041' AND effect_type = 'inventory_sync';
-- inventory:ORD-2041:CF-ARA-500  → done
-- inventory:ORD-2041:TE-CHM-100  → done
-- inventory:ORD-2041:CF-DEC-250  → pending/processing

SELECT count(*) FROM external_effects_log
WHERE order_id = 'ORD-2041' AND effect_type = 'inventory';   -- 3, not one more

SELECT count(*) FROM outbox
WHERE aggregate_id = 'ORD-2041' AND effect_type = 'inventory_sync' AND status = 'done';  -- 3

Exercise 2 — Harden it against two relays. You want to run two instances of the outbox-relay at once for more capacity. Explain which part of the design already allows that without duplicating effects, what could go wrong if that part weren't there, and how you'd verify it by running both relays against a burst of tickets.

-- No ticket was left unprocessed
SELECT count(*) FROM outbox WHERE status <> 'done';        -- 0

-- Each effect happened exactly once (no duplicates from overlap)
SELECT effect_key, count(*) FROM external_effects_log
GROUP BY effect_key HAVING count(*) > 1;                    -- 0 rows (no duplicates)

Exercise 3 — Defend the system in an interview. Imagine you're presenting this project and someone asks you: "How do I know that if the vendor fires the webhook three times and on top of that your server crashes halfway through, the customer doesn't get refunded more than once?" Answer in one paragraph, naming the concrete pieces that give the guarantee and the order in which they act.

Summary and next step

In this lesson you built and proved the complete system the module was preparing you for: a director (order-triage) that receives, deduplicates, checks credit synchronously, and decides the effects by writing them to an outbox atomically; a relay (outbox-relay) that drains the outbox and executes each effect idempotently via issue-refund and inventory-sync; and the ledger as shared memory of what's already happened. The first deliverable is the dependency graph, which shows the separation between the decision part (fast, no effects) and the execution part (idempotent, via the relay). The second, the convincing one, is the tests: the happy path (Test A), the duplicate webhook that doesn't duplicate effects (Test B), the mid-chain crash that recovers without duplicating or losing anything (Test C), and the fan-out with a partial crash (Test D from the exercise). In all of them, the truth gets measured the same way — counting rows in external_effects_log — and in all of them the result is the same: every effect, exactly once.

With this you close Module 5. You know how to coordinate several dependent workflows without falling into the three disasters: you map the system with the graph, you choose between orchestration and choreography, you split and join work without losing items, you control pace and order, and — the piece that ties it all together — you separate deciding from executing with the outbox pattern, even when the decider is an agent. The module's exit capability is met: you can map the dependency graph of a multi-workflow system and coordinate its pieces with the outbox and queue mode with no cascades, no lost items, and no duplicated effects.

What's left for Module 6 is the last layer of robustness: what to do when, despite everything, something really fails. You're going to learn to retry without duplicating by leaning on the idempotency you already have, to design compensating actions to undo what can't be avoided repeating, to decide which failure deserves an alert and which recovers on its own, to route real failures to a dead-letter queue with an Error Trigger, and to reproduce a duplicate bug with n8n 2.0's replay engine. It's the difference between a system that's correct when everything goes well and one that also recovers gracefully when something goes wrong — and knows how to ask for help when it truly needs it.

Resources

• Execute Sub-workflow Trigger — n8n Docs — the trigger you use to declare check-credit, issue-refund, and inventory-sync's input contract (with Define using fields below), and where the last node defines the response to the caller.
• Execute Sub-workflow node — n8n Docs — the node the director uses to call check-credit synchronously and the relay uses to execute the effects, passing the idempotency key in the assignment.
• Postgres node — n8n Docs — the node behind every atomic write in the project: the outbox ticket, effect-plus-ledger, and picking up tickets with FOR UPDATE SKIP LOCKED.
• Schedule Trigger — n8n Docs — the relay's trigger, which checks the outbox every few seconds.
• Split Out node — n8n Docs — the node that splits line_items into one item per line, so you can write one inventory ticket per sku.
• Self-Hosted AI Starter Kit — n8n Docs — the package with local Postgres (and an AI model) where you run this whole project at zero cost; check the docs for the current version when you set it up.