Idempotency everywhere

MERGE not CREATE (L4). Dedup an alert by msg-id (L15). A graphwrite IdemKey (L9). An anti-join before a bridge edge (L27). Create-then-mark a Linear ticket (L33). A replay-safe AT_RISK supersede (L19). Six lessons, one defense — against a single fact about the system. This is the third synthesis: after what to combine (L46) and how to combine it reproducibly (L47), here's how to write the result safely when the same write may happen many times.

1 · The root cause: at-least-once delivery

The pipeline runs on Redis Streams with consumer groups (L7). The delivery guarantee there — and in essentially every durable queue — is at-least-once: an entry isn't considered done until the consumer XACKs it, so a crash, a timeout, or backpressure between processing and ack means the entry is redelivered. You cannot buy exactly-once delivery in a distributed stream — it's a well-known impossibility.

2 · The four techniques

Idempotency always reduces to the same goal — make the write a pure function of a stable key and current state — but it's reached four ways depending on what you're writing.

A · Stable-key upsert — "address the effect by a key, hit the same slot"

The shape is always "name the effect by a key so the second occurrence lands on the first." CREATE, by contrast, duplicates on replay — which is why you almost never see it on the hot path (L42).

B · Monotonic guard — "a stale replay loses to the newer truth"

SET r.foo = $v WHERE coalesce(r.updated_block, 0) <= $block   // newer-block-wins

Re-applying an old write (a replay, or a reconcile heal racing an event) matches zero rows because the stored updated_block is already newer. You saw this as the write-path's monotonic guard (L4) and as the reconcile transport's temporal fence (L31, behind a WITH). It buys idempotency and ordering at once: the same write twice is a no-op, and an out-of-order write can't clobber fresher state.

C · Existence guard — "only act if not already done"

The oracle bridger's anti-join (L27): WHERE NOT EXISTS { MATCH (market)-[:ORACLE_DEP]->(oracle) } MERGE …. Once the edge exists, the pass creates nothing — so running it every 30 min converges and then idles. The interval-bucketed IdemKey (L27) is the same instinct at the request level.

D · Whole-set recompute — "the output is a pure function of current inputs"

The AT_RISK edge supersede (L19): MERGE the current cells' edges and unconditionally delete any inbound AT_RISK edge not in this run, both keyed on the same $runAt. The final edge set is a pure function of the current cell set, regardless of apply order or how many times it runs. Genesis's balance rebuild (L37) is the same idea at startup — re-derive the whole cache from the graph; safe to run every boot.

3 · The hard case — idempotency for an external side effect

MERGE works because you control the database. But you can't MERGE a Linear ticket or a sent notification — the effect lives in someone else's system. L33's promoter is the template for that case:

4 · The trilogy, and the deeper tie

Idempotency is the write-side twin of determinism (L47). Both are "same inputs → same result": determinism is same computation → same value; idempotency is same write applied N times → same final state. And block-determinism (L8) — "state at cursor N is a pure function of blocks 0..N" — needs both: a replayed block must re-compute the same values (L47) and re-apply them without doubling (here). The three syntheses line up:

What you can now do

• Explain why at-least-once delivery makes idempotency mandatory, and what "effectively-once" means.
• Classify any write into one of the four techniques: stable-key upsert, monotonic guard, existence guard, whole-set recompute.
• Explain external-effect idempotency (recorded key + chosen failure window) and why MERGE doesn't apply.
• Relate idempotency to determinism (L47) and block-determinism (L8) as the write face of reproducibility.
• Spot a non-idempotent write — a CREATE where MERGE belongs, or an unstable dedup key — in review.