3. Designing a run ledger

Description

By the end of this lesson you'll be able to design a run ledger —an execution log— in Postgres: what columns it has, why each one is there, which states represent an execution's lifecycle (pending, done, failed), and how it's read from and written to from n8n with the Postgres node. You're going to understand why the ledger is the single source of truth for what already happened in your system, and you're going to see the two-write pattern —recording before acting and updating after— that makes the ledger more than a log: what turns it into the foundation for failure recovery.

This matters because the ledger is the piece that turns "my workflow does things" into "my system knows what it's done." Without it, when someone asks "was order ORD-2041 processed?", the only answer is opening n8n's execution history and eyeballing it. With it, it's a query: SELECT * FROM run_ledger WHERE order_id = 'ORD-2041'. The difference between those two ways of answering is the difference between operating by intuition and operating on data, and it's exactly what gets rewarded in a system-owner role.

Connection to the module: lesson 2 closed the door on storing the truth inside the workflow; this one opens the first table that stores it outside. The ledger is the more complete of the two structures you build in this module: it records the full history of every execution. Lesson 5 is going to build its sharper sibling, the deduplication store, which stores less but decides faster; and there you'll see the ledger and the store don't compete but complement each other. The idempotency_key column you design here is the same natural or synthetic key you worked with in module 2; now you give it a persistent home. And the pending/done/failed state cycle is the foundation module 6 is going to build retries and recovery on.

The accounting ledger

Let's start with the analogy, because "ledger" might be a word you don't use every day, and its origin explains everything the table does.

A ledger is, literally, an accounting book: that notebook where a business writes down, line by line, every money movement. Every entry has a date, a description, an amount, and a balance. And it has a defining property: entries don't get erased. If you made a mistake, you don't cross out the old entry; you write a new one that corrects it. The book is an append-only record —you only add— because its value lies precisely in preserving the complete history. At any moment you can go through it and answer: has this invoice been paid? when? for how much? The book isn't where the money lives; it's where the story of what happened to the money lives.

A run ledger is exactly that, but for your system's executions instead of for money. Every time order-triage processes an order, it writes an entry: which order, with what key, at what time, what state it ended in. The entries accumulate. And at any moment you can go through the book and ask: has order ORD-2041 already been processed? when? did it go well or fail?

Think of it also as a reception's visitor log. Every person who comes in signs with their name and the time. At the end of the day, reception doesn't have to remember who came by: it reads it in the log. And if someone asks "did Laura come in today?", the answer doesn't depend on anyone's memory —it's written down—. The run ledger is your executions' visitor log: each one signs on the way in, and the truth of who came by stops depending on the execution's fragile memory and starts living on a page that doesn't get erased.

This is the underlying difference with the deduplication store you'll see in lesson 5. The store answers a quick, binary question —"have I seen this key already? yes/no"— and stores the minimum. The ledger answers rich questions —"what happened with this execution, when, with what result?"— and stores the history. Both are useful; they serve different purposes.

The run ledger's anatomy: which column, and why

Designing the table means deciding which questions you want to be able to answer it with. Let's go column by column, because each one is there to answer something specific. Remember every identifier —table and column names— goes in English, as on any real technical team; the prose explaining them goes in English here.

Let's break down the ones with meat on them.

idempotency_key is the central column, and it's unique. This deserves a pause. In module 2 you learned an idempotency key identifies the work, not the attempt: two triggers for the same order share the same key, even though they're two different executions. Here that key becomes a column, and you give it a uniqueness constraint: you tell Postgres "this table can never have two rows with the same idempotency_key, ever." That constraint is what turns the ledger into a guardian: if you try to record an entry with a key that already exists, the database rejects it. For order-triage, the key can be order_id itself (a natural key) or a hash combining several fields (a synthetic key), based on what you decided in module 2. The table doesn't change; only what you put in that column.

status counts the lifecycle. An execution isn't an instant, it's a process with a beginning and an end, and between those two points anything can happen —exactly the moment the network drops or the CRM is slow—. That's why the ledger doesn't just store "happened" or "didn't happen," but where along the way it is:

• pending — the work started but hasn't finished yet. The entry gets written with this state before creating the charge.
• done — the work finished well. It gets updated to this state after the charge was created successfully.
• failed — the work started but failed. It gets updated to this state if something broke along the way.

Those three states are a minimal model, and they're enough for the entire module. The reason there are three and not two —why pending deserves its own state— is the thread connecting this lesson to module 6, and we develop it in the deep-dive section.

result is JSONB type, and that choice is deliberate. JSONB is Postgres's type for storing a complete JSON object inside a single column. Think of it as a flexible pocket: instead of creating one column for the charge's id, another for the error message, another for every other thing you might think of storing, you put in an object with whatever fits each case. For a done entry, result might be {"charge_id": "CHG-9981", "amount": 1734}. For a failed one, {"error": "CRM returned 503", "retryable": true}. One pocket, different contents depending on the outcome. It's the pragmatic way for the ledger to remember what happened without having to redesign the table every time you want to store a new piece of data.

created_at and updated_at are timestamps with time zone. The TIMESTAMPTZ type stores the instant and its time zone, avoiding the classic tangle of "is this time Mexico's or the server's?" created_at marks when the entry was born; updated_at, when it was last touched. With these two you can answer real operational questions: how long did this execution take between starting and finishing? are there entries stuck at pending for an hour, suggesting they hung?

The SQL that creates the table

With the design clear, here's the statement that creates the ledger. You're going to run it once, when you set up the system. Don't worry if the SQL is new to you; we're going to read it line by line right after.

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()
);

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

• CREATE TABLE IF NOT EXISTS run_ledger — creates a table called run_ledger. IF NOT EXISTS is an important courtesy: if the table already exists, it doesn't fail or delete it; it simply does nothing. That lets you run this statement fearlessly, even if you don't remember whether you already created it.
• id BIGSERIAL PRIMARY KEY — BIGSERIAL tells Postgres "this column is a number you assign yourself, incrementing it with every new row." PRIMARY KEY means "this column uniquely identifies every row and is the table's main index." It's the accounting book's entry number, which the database keeps for you.
• idempotency_key TEXT NOT NULL UNIQUE — text (TEXT), required (NOT NULL, no empty values), and unique (UNIQUE, can't repeat). This is the line turning the ledger into a guardian: two entries with the same key are impossible.
• order_id TEXT NOT NULL — the order, required, so you can search in a human-readable way.
• status TEXT NOT NULL DEFAULT 'pending' — the state, required, with a default value: if you insert a row with no status specified, Postgres sets 'pending' automatically. It's a convenient detail, because every entry's initial state is precisely pending.
• result JSONB — the flexible pocket for the result. Notice it does not say NOT NULL: a freshly born entry, still pending, doesn't have a result yet, so empty (NULL) is allowed.
• created_at ... DEFAULT now() and updated_at ... DEFAULT now() — the two timestamps, and now() is a Postgres function returning the current instant. With DEFAULT, you don't have to compute the time yourself: the database sets it on insert.

An honesty note: there's more than one good way to write this. Some teams add a workflow_name column, others use a UUID instead of BIGSERIAL, others separate created_at and finished_at. There isn't a single correct table; there's a table that answers well the questions that matter to you. This one answers the module's. When you adapt it to your system, the guiding question is always the same: what am I going to need to ask this book three months from now?

The two-write pattern

Here's the idea that makes the ledger something alive and not a simple log you fill in at the end. The entry doesn't get written once; it gets written twice. And the order matters.

Write 1 — on starting, in pending state, before the effect. As soon as order-triage receives an order and decides to process it, before calling the CRM, it writes the entry: "I'm starting to process ORD-2041, this key, pending state." The execution declares its intention before acting.

Write 2 — on finishing, updating to done or failed, after the effect. When the charge was created successfully, it updates the same entry: done state, and stores the charge's id in result. If something failed, it updates it to failed with the error.

Receives ORD-2041
   │
   ▼
[Write 1]  INSERT into run_ledger  →  status = 'pending'      (before the effect)
   │
   ▼
HTTP Request: "Create charge in CRM"  →  creates the charge   (the effect)
   │
   ▼
[Write 2]  UPDATE in run_ledger  →  status = 'done',           (after the effect)
                                     result = { charge_id }

Why in that order, instead of just noting everything at the end? Because the order is what protects you from the worst moment: when something breaks right between the effect and the record. Imagine you wrote the entry after creating the charge, and the execution crashed after creating the charge but before writing it down. The charge would exist in the CRM, but your ledger would have no trace of it at all: the truth and reality would end up out of sync, and you on the blind side. By writing pending before the effect, you guarantee there's never an effect with no entry mentioning it. Worst case, you'll have an entry stuck at pending that never got closed —and that's information: you know that order started being processed and you don't know whether it finished, which is exactly what you want to investigate—. A stuck pending entry is a problem you can see; a charge with no entry is an invisible problem.

That's the underlying reason pending deserves to be its own state and not just "the row doesn't exist yet." The pending state is the statement "this started and I don't know how it ended," and being able to tell that apart from "this never started" is the foundation of all the failure recovery you'll see in module 6. A system with only "done" and "not done" can't recover from a crash mid-execution, because it doesn't know what got left halfway.

Worked example: order-triage writes its entry

Let's see the full pattern in the workflow, with the concrete nodes. Remember a key n8n 2.0 restriction: writes to Postgres are done by the Postgres node, not a Code node. The Code node can compute the idempotency_key, but the one talking to the database is the dedicated node.

The workflow ends up like this:

Webhook
  └─► Code: "Compute idempotency key"        ← computes the key with crypto
        └─► Postgres: "Ledger — insert pending"   ← Write 1 (Insert)
              └─► AI Agent: "Classify order"
                    └─► HTTP Request: "Create charge in CRM"   ← the effect
                          └─► Postgres: "Ledger — mark done"   ← Write 2 (Update)

The Code node computing the key can use crypto, which is indeed allowed in n8n 2.0's Code node (unlike fetch or axios, which aren't):

// ============================================================
// 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 available in n8n 2.0's Code node.
//          We do no HTTP and touch no database here:
//          the dedicated nodes handle that.
// ============================================================

const crypto = require('crypto');

const order = $input.item.json;

// Synthetic key: hash of order_id + total, so the same order
// with the same content always produces the same key.
// (If your natural key, order_id alone, is already unique and stable,
//  you can use it directly. See module 2.)
const raw = `${order.order_id}:${order.order_total}`;
const idempotencyKey = crypto.createHash('sha256').update(raw).digest('hex');

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

Write 1 is done by a Postgres node in Insert operation, pointing at the run_ledger table, with the fields idempotency_key, order_id, and status = 'pending'. Write 2 is done by another Postgres node in Update operation, which looks up the entry by its idempotency_key and sets status = 'done' and the result.

What to expect. With an order ORD-2041 arriving for the first time, right after the first Postgres node you'll see, in the run_ledger table, a row with status = 'pending', its created_at set to the current time, and result empty. After the charge gets created and the second Postgres node runs, that same row changes to status = 'done', with result containing the charge's id and updated_at refreshed. A single entry, two writes, that execution's full history readable at a glance.

And it's worth being honest about what this example still doesn't solve: on its own, the ledger records but doesn't deduplicate. If ORD-2041 arrives twice, the UNIQUE constraint on idempotency_key will make the second INSERT fail —which is good, it's the signal—, but a failed INSERT stops the node with an error, and you haven't decided yet what to do with that error to prevent the effect. Turning "the insert failed" into "discard the order and don't charge" is precisely the deduplication store's job and lesson 5's ON CONFLICT pattern. The ledger is the half that records; lesson 5 is the half that decides. That's why the two tables complement each other.

How the ledger gets read: the single source of truth

The ledger's value isn't just in writing it, but in being able to ask it questions. Once it exists, answering "what happened with this order?" stops being an archaeology dig through n8n's history and becomes a query:

-- Was this order processed, and how did it end?
SELECT status, result, created_at, updated_at
FROM run_ledger
WHERE order_id = 'ORD-2041';

With that single query —which in n8n runs through a Postgres node in Select or Execute Query operation— you answer what used to require opening executions by hand. And questions show up that only a ledger allows:

-- Which executions got stuck in 'pending' more than 15 minutes ago?
-- (Candidates for having crashed mid-execution: module 6 material.)
SELECT order_id, idempotency_key, created_at
FROM run_ledger
WHERE status = 'pending'
  AND created_at < now() - INTERVAL '15 minutes';

That second query is what separates a log from a real ledger. A log tells you what happened when you read it. A ledger lets you interrogate the system's state: finding what got left halfway, what failed, what took too long. It's the raw material for module 6's alerts and recovery, and you only have it because you decided, from the design, to store the state and the timing.

When we say the ledger is the single source of truth, this is what it means: if two systems disagree about whether ORD-2041 was processed —the CRM says one thing, n8n's history suggests another—, the ledger is the referee. Not because it's magic, but because you designed it so every execution left its trace there, in order, before and after acting. The truth doesn't live in anyone's memory; it lives in the book.

Common mistakes

Writing the entry only at the end (conceptual). What happens: for simplicity, someone records into the ledger a single time, after creating the charge, in done state. Why it happens: writing twice seems redundant, and "note it down when it's finished" sounds natural. How to spot it: if your ledger never has rows at pending, this is it. How to fix it: write pending before the effect. If the execution crashes between the effect and the record, without the prior entry you'd have a ghost charge with no trace; with it, you have at least a pending shouting "check this." An effect with no entry is invisible; an entry with no outcome is investigable.

Storing the system's truth in the column that changes (conceptual). What happens: someone reuses idempotency_key to put in variable information, or changes an existing entry's order_id. Why it happens: it looks like "updating the record." How to spot it: if your UPDATEs touch columns that identify the entry (the key, the order), this is it. How to fix it: the key and the order get written once and don't get touched; what changes over the lifecycle is status, result, and updated_at. A ledger whose row identity mutates stops being trustworthy as a source of truth.

Putting everything that could go in result into separate columns (practical). What happens: a column gets created for charge_id, another for error_message, another for retry_count, and the table grows with every new piece of data you want to remember. Why it happens: the "one column per piece of data" instinct kicks in. How to spot it: if every time you want to store something new you have to do ALTER TABLE, this is it. How to fix it: for result data varying by outcome, result JSONB is the flexible pocket; reserve dedicated columns only for what you're going to query and filter on often (like status). It's a balance, not an absolute rule, but starting with JSONB saves you from redesigning the table constantly.

Confusing the ledger with the dedup store (conceptual). What happens: someone tries to use the ledger for everything, including the fast "act or discard?" decision, and ends up with complicated queries in the critical path. Why it happens: both tables store keys, so they look the same. How to spot it: if at the moment of deciding whether to charge you're reading status, result, and timestamps, you're using the full book for a yes/no question. How to fix it: the ledger is for the rich history (auditing, recovering); lesson 5's dedup store is for the fast, atomic yes/no. You can have both; each does its own job well.

Using date types with no time zone (practical). What happens: created_at gets defined as a plain TIMESTAMP, and months later nobody knows whether the times are the server's, UTC's, or Mexico's. Why it happens: TIMESTAMP is shorter to write and "looks" enough. How to spot it: if you have to ask yourself "what zone is this time in?", it's already happened to you. How to fix it: use TIMESTAMPTZ, which stores the instant with its zone. In a system where timing matters for detecting what hung, time-zone ambiguity is debt paid for in confusion.

Exercises

Exercise 1 — Justify every column. For each run_ledger column, write in one sentence what question it lets you answer that you'd lose if you removed it.

Exercise 2 — Order the writes. You're given these four order-triage steps out of order. Put them in the correct order and explain why that order protects the system.

(A) UPDATE run_ledger SET status = 'done', result = {...} WHERE idempotency_key = ... (B) HTTP Request: create the charge in the CRM (C) INSERT INTO run_ledger (..., status) VALUES (..., 'pending') (D) Compute the order's idempotency_key

Exercise 3 — Design an operations query. Write (or describe in words if you're not comfortable with SQL yet) the query that answers: "how many orders ended up failed in the last hour, and which ones?" Then explain what that query would be used for in practice.

SELECT order_id, idempotency_key, result, updated_at
FROM run_ledger
WHERE status = 'failed'
  AND updated_at > now() - INTERVAL '1 hour'
ORDER BY updated_at DESC;

Summary and next step

In this lesson you designed the run ledger, your executions' accounting book. It's a table in Postgres recording, entry by entry, what work ran (idempotency_key, order_id), what state it ended in (status: pending, done, failed), what it produced (result in JSONB), and when (created_at, updated_at). idempotency_key carries a uniqueness constraint turning the ledger into a guardian, and the TIMESTAMPTZ type stores times with no zone ambiguity.

The idea making it alive is the two-write pattern: recording the entry as pending before the effect, and updating it to done or failed after. That order guarantees there's never an effect with no entry mentioning it, and turns an invisible failure (a charge with no trace) into an investigable one (a stuck pending). That's why pending is its own state and not just "the row doesn't exist yet": it's the foundation for module 6's recovery.

And you saw this piece's honest limit: the ledger records, but on its own doesn't deduplicate. The UNIQUE constraint makes the same order's second INSERT fail, but turning that failure into "discard and don't charge" is the deduplication store's job.

Before moving on you should be able to: name the ledger's columns and what question each answers; explain why pending gets written before the effect; and write the idea of a query that interrogates the system's state.

Lesson 4 steps aside before building the dedup store: it shows you the three deduplication strategies that exist —time window, seen-key, and the Remove Duplicates node— and, above all, where each falls short. It's the lesson giving you the criterion for knowing why lesson 5's dedup store is built the way it is, instead of with the node n8n ships out of the box. Without that criterion, you'd pick the wrong strategy for the wrong case.

Resources

• Postgres node — n8n Docs — the operations you're going to use to write and read the ledger: Insert (Write 1), Update (Write 2), and Select / Execute Query (the operations queries).
• Code node — n8n Docs — the node where you compute idempotency_key with crypto, and its limits in n8n 2.0 (no HTTP, no file system access).
• PostgreSQL — CREATE TABLE — the official reference for the statement that creates the table, including PRIMARY KEY, UNIQUE, NOT NULL, and DEFAULT. Useful for confirming the exact syntax for your Postgres version.
• PostgreSQL — JSON Types — what JSONB is and why it's the right type for the result column storing a result flexibly.
• PostgreSQL — Date/Time Types — the difference between TIMESTAMP and TIMESTAMPTZ, and why the second avoids time-zone ambiguity in created_at and updated_at.