Idempotency: Retried Work Writes Once

The Reconciler has been running fine for a week.

Then one night the network blips between the Worker and the database. The Worker thinks the write failed. It retries. The write actually succeeded the first time. Now there are two expenses rows for the same bank transaction. Multiply by a hundred users. By morning the boss is yelling, finance is yelling, and the Worker is still cheerfully running.

Nothing in the journal you built in Lesson 2 prevents this. The journal recorded both attempts honestly. The problem is not observability. The problem is that the database accepted the second write.

This lesson teaches the smallest fix: the Worker derives a deterministic key for every tool call, the database holds a unique constraint on that key, and the second write becomes a no-op.

Key Terms for This Lesson

• Idempotency: A write is idempotent if running it twice has the same durable effect as running it once.
• Idempotency key: A short string the Worker derives from the call's inputs. Same inputs always produce the same key.
• Deterministic key: A key whose value depends only on its inputs. No timestamps. No random ids. No request counters.
• Unique constraint: A database rule that refuses a second row with the same value in a chosen column.
• ON CONFLICT: A clause on INSERT that tells Postgres what to do when the new row collides with an existing one. DO NOTHING skips. DO UPDATE merges using values from EXCLUDED.

What "Idempotent" Means

A write is idempotent if running it twice has the same durable effect as running it once.

That is the whole definition. Two common misunderstandings get in the way.

The first: idempotency is not about whether the second call returns the same response. It is about whether the database holds the same rows after the second call as it did after the first. The Worker can hear "OK, already done" or "OK, inserted" on the second call. Either is fine. What is not fine is a second row.

The second: idempotency is not retry logic. Retry logic is the Worker's job. Idempotency is the database's job. The Worker does its best to avoid duplicate sends. The database catches the ones that slip through. Both layers are needed.

The Key Contract

Every Worker tool call ships with an idempotency key. Three rules govern it.

Rule	What it means
Deterministic	Same inputs always produce the same key. No clocks, no UUIDs.
Tied to the call	The key is built from (worker_id, run_id, turn_seq, tool_name, canonical_input_hash) so two different calls cannot collide.
Unique-constrained at the database	Postgres holds the key in a column with a unique constraint, so a second insert with the same key is refused.

The Reconciler computes the key in the application layer (the agent's code, which you read in Lesson 2's tool_calls journal). The database does not care how the key was derived; it only cares that the (worker_id, idempotency_key) pair is unique.

A reasonable shape:

idempotency_key = sha256(
  worker_id || ':' || run_id || ':' || turn_seq || ':' || tool_name || ':' || canonical_input_hash
)

Plain English: take the five fields that identify "this exact tool call", concatenate them in a fixed order, hash the result. Run it twice with identical inputs and you get the same hash. Run it with any input changed (a different amount, a different user, a different turn) and you get a different hash.

ON CONFLICT, Recognition Level

Once the key exists in the database, the INSERT carries an ON CONFLICT clause. You will read these. You will not write them.

Idempotent insert (the dominant pattern for Worker writes):

INSERT INTO expenses (worker_id, user_id, category, amount, spent_at, idempotency_key)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (worker_id, idempotency_key) DO NOTHING;

Translation: insert a new expense. If this Worker already wrote a row with this idempotency key, skip silently. The row count returned is 1 the first time and 0 the second time.

Upsert (use when a retry might carry corrected data):

INSERT INTO expenses (worker_id, user_id, category, amount, spent_at, idempotency_key)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (worker_id, idempotency_key) DO UPDATE
SET amount = EXCLUDED.amount,
    updated_at = now();

Translation: insert a new expense. If a row with this key already exists, update its amount to the value from the new attempt and bump the timestamp. EXCLUDED is Postgres's name for the row that the INSERT was trying to add.

Which one fits the Reconciler's expense-write?

Scenario	Choice	Reason
Same input, same amount, retry due to network blip	DO NOTHING	The first attempt got it right. The second attempt should be silent.
Same logical transaction, but the agent re-categorised it on retry	DO UPDATE	The newer attempt carries a correction. The row should reflect it.
Different transaction altogether	Different key	Different inputs produce a different key, so there is no conflict.

The Reconciler's bank-statement reconciliation is DO NOTHING territory. The amount and category come from the same source row both times. A retry should never touch the durable state again.

What the Migration Looks Like

The agent writes the migration. You read it.

This sample uses Postgres digest(...) from pgcrypto for the backfill. If your database does not have pgcrypto enabled, the agent must either enable the extension in the migration or backfill with an equivalent deterministic hash from application code before adding SET NOT NULL.

ALTER TABLE expenses
  ADD COLUMN worker_id text,
  ADD COLUMN idempotency_key text;

UPDATE expenses
SET worker_id = 'historical-backfill',
    idempotency_key = encode(
      digest('historical-backfill:' || id::text, 'sha256'),
      'hex'
    )
WHERE idempotency_key IS NULL OR worker_id IS NULL;

ALTER TABLE expenses
  ALTER COLUMN worker_id SET NOT NULL,
  ALTER COLUMN idempotency_key SET NOT NULL;

ALTER TABLE expenses
  ADD CONSTRAINT expenses_worker_idem_key
  UNIQUE (worker_id, idempotency_key);

Three things to read:

1. The worker_id and idempotency_key columns start nullable so the migration can run on an existing table without lying.
2. Existing rows are backfilled with a historical worker label and deterministic keys before SET NOT NULL.
3. The unique constraint is added only after every row has a real key. A default empty string plus a unique constraint would collide on existing rows.
4. The unique constraint is on (worker_id, idempotency_key). Two different Workers may produce the same key by coincidence. Scoping the constraint to the Worker makes the collision domain right.
5. There is no DROP. The migration adds, backfills, tightens, and constrains. Reversible on a Neon branch (callback to Lesson 1).

If the agent's migration is missing any of these (especially the unique constraint), the column is decoration, not protection. Reject and ask for the constraint.

PRIMM-AI+ Practice: Run the Same Reconciler Write Twice

Predict [AI-FREE]

The Reconciler is about to write one expense row for Alice. Before you let the agent run anything, write down:

• After the first insert, how many rows in expenses carry this idempotency key?
• After a retry of the exact same insert, how many rows carry this idempotency key?
• Will the second insert raise a runtime error, or will it return 0 rows affected silently?
• Your confidence score from 1 to 5.

You should be able to answer all three before reading another word.

Run

Ask the agent to perform two inserts in sequence.

Ask Claude Code: "Insert one expense row for Alice with worker_id 'reconciler-v1', category 'Food', amount 12.50, and an idempotency_key of 'demo-key-001'. Use ON CONFLICT (worker_id, idempotency_key) DO NOTHING. Then run the exact same insert a second time. After both inserts, run a SELECT count(*) WHERE worker_id = 'reconciler-v1' AND idempotency_key = 'demo-key-001'. Show me each statement and its returned row count."

What you should see:

Statement	Rows affected	Why
First INSERT	1	New key. Postgres accepts the row.
Second INSERT	0	Key collision. DO NOTHING makes the conflict silent.
SELECT count	1	Only one row exists for this idempotency key.

Investigate

Write your own one-paragraph explanation:

1. The second insert returned 0 rows affected because the unique constraint on idempotency_key matched.
2. ON CONFLICT DO NOTHING turned a constraint violation into a no-op instead of an error.
3. The durable state in expenses is identical after the first insert and after the second.

Then ask the agent:

1. "What would have happened if the migration only added the column but not the unique constraint? Show me the same two inserts in that world."
2. "What is the difference between ON CONFLICT DO NOTHING and a try/except around a duplicate-key error in the application code? Which one is closer to a system-of-record discipline?"
3. "If the network had blipped between the database commit and the Worker reading the response, would DO NOTHING still have produced one row? Walk through the sequence."

Modify

Change one rule. Replace DO NOTHING with DO UPDATE SET amount = EXCLUDED.amount, updated_at = now().

Predict the difference. Then run a retry where the agent ships a corrected amount of 13.75 instead of 12.50.

What should you see in expenses?

• Still one row.
• The amount column is now 13.75.
• The updated_at column shows the time of the second attempt.

This is the upsert shape. Same retry-safe contract, different durable behaviour.

Make [Mastery Gate]

The Reconciler should also record an idempotent confirmation_email row, so a retried Worker run does not double-send the email. The brief in business English:

"Every time the Reconciler writes an expense row, it should also write a confirmation_email row for the user. If the same Reconciler run reattempts the same expense, it must not produce a second confirmation_email. Same key contract, same DO NOTHING."

Hand this brief to the agent. Ask for:

1. The proposed confirmation_emails table.
2. The unique constraint that scopes the idempotency key (run_id plus expense_id is a good shape).
3. The migration file.
4. The shape of the INSERT ... ON CONFLICT statement.

You read each piece. The gate passes when you can point at the unique constraint and explain in business terms which retry it refuses. If the agent forgets the unique constraint, you reject. If the agent uses DO UPDATE when the brief said no double-send, you reject.

This is the same discipline as Lesson 1: the agent writes, you read, the database refuses. Idempotency keys are how the database refuses.

Try With AI

Prompt 1: Deterministic Key Drill

Walk me through how you would compute an idempotency key for a Worker
tool call that creates an expense row. List the exact fields you would
hash, in order, and explain in one sentence why each field belongs in
the key. Do not include a timestamp. Do not include a random UUID.
After your list, I will tell you which field I think you got wrong.

What you're learning: Determinism is the whole game. If the key changes between the first attempt and the retry, the database cannot recognise the duplicate. Building the right key is what makes ON CONFLICT useful.

Prompt 2: ON CONFLICT Recognition

I will paste three INSERT statements. For each one, tell me whether
it is idempotent on retry. If it is, name the constraint and the clause.
If it is not, explain what failure mode would occur on a retry.

A) INSERT INTO expenses (worker_id, user_id, amount, idempotency_key)
   VALUES ('reconciler-v1', 1, 12.50, 'k-001')
   ON CONFLICT (worker_id, idempotency_key) DO NOTHING;

B) INSERT INTO expenses (worker_id, user_id, amount, idempotency_key)
   VALUES ('reconciler-v1', 1, 12.50, 'k-001');

C) INSERT INTO expenses (worker_id, user_id, amount, idempotency_key)
   VALUES ('reconciler-v1', 1, 12.50, 'k-001')
   ON CONFLICT (worker_id, idempotency_key) DO UPDATE SET amount = EXCLUDED.amount;

What you're learning: Recognition. A is the safe DO NOTHING. B is a duplicate-row factory. C is the upsert; safe on retry, but it overwrites the existing amount, which may or may not be what you want.

Prompt 3: Retry Simulation

Simulate the Reconciler retrying the same bank transaction three times
in a row. Insert the same row with the same idempotency key three times.
After each attempt, show me the row count for that key and the affected
row count of the INSERT. Then explain in plain English why the second
and third attempts are no-ops.

What you're learning: Watching the row count stay at 1 across three identical attempts is the visible confirmation that the contract works. This is the demo you can show the boss when she asks "what stops the Worker from double-writing?"

Checkpoint

• I can explain in one sentence what makes a write idempotent.
• I can read an idempotency key derivation and explain why each input field belongs in it.
• I can recognise ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE and predict which one fits a given Worker scenario.
• I can read a migration that adds an idempotency column and confirm the unique constraint is present.
• I have run the same insert twice and watched the second attempt return 0 rows affected.
• I rejected at least one agent-proposed migration during practice for missing the unique constraint.

Flashcards Study Aid