Events, idempotency and database writes

A practical refresher. Read the contract, trace the examples, and attempt the exercise before opening its answer. Code blocks may share context; only ts check blocks are standalone checks.

Duplicates and stale updates

Duplicate delivery asks whether an event was already handled. Stale update asks whether a newer entity version is already stored. Their keys differ.

Versions from unrelated sources are not automatically comparable; use a composite state key unless the domain defines a cross-source ordering. Identity resolution between providers is another business problem.

Why find-then-insert races

Two callers can both observe a missing record before either inserts it. A JavaScript check does not replace a database uniqueness constraint. Enforce the composite key in storage and combine the condition with the write.

A transaction alone is not a magic substitute for choosing the correct constraint, isolation and atomic statement.

const existing = await db.find(id);
if (!existing) await db.insert(record);
else await db.update(id, record);

Conditional upsert

An INSERT ON CONFLICT with a version condition can keep the highest version despite concurrent delivery. RETURNING distinguishes an applied update from a skipped one. The unique constraint must match the conflict target.

Equal versions are skipped in this contract, so their payloads must be immutable or a conflict rule is required. This example demonstrates state selection; it does not magically deduplicate external side effects.

CREATE TABLE products (
  source_id text NOT NULL,
  product_id text NOT NULL,
  version bigint NOT NULL CHECK (version >= 0),
  price_cents bigint NOT NULL CHECK (price_cents >= 0),
  PRIMARY KEY (source_id, product_id)
);

INSERT INTO products (source_id, product_id, version, price_cents)
VALUES ($1, $2, $3, $4)
ON CONFLICT (source_id, product_id)
DO UPDATE SET
  version = EXCLUDED.version,
  price_cents = EXCLUDED.price_cents
WHERE EXCLUDED.version > products.version
RETURNING source_id, product_id, version;

Idempotent side effects

Email, payment or another external effect cannot safely be repeated merely because a record update is conditional. Combine an inbox marker and database changes in the same transaction; an outbox can atomically record work to publish later.

A downstream consumer still needs an idempotency key or deduplication policy.

Explain the failure window between commit and delivery rather than promising exactly-once behavior without assumptions.

Deletion and old events

Physically removing version 5 can let late version 3 recreate the entity. A tombstone retains deletion state and version so old updates are rejected. Retention depends on replay and recovery windows. If a source can reset versions, the ordering contract must also include an epoch or another generation identifier.

Write SQL by hand

Use ROW_NUMBER partitioned by the entity key to select a latest row, with a deterministic tie-breaker. A left join must preserve missing right-side rows; count or aggregate according to null semantics. Identify useful indexes from predicates and joins, and inspect the actual query plan. SQL examples need an appropriate schema and database to execute.

SELECT source_id, product_id, price_cents, updated_at
FROM (
  SELECT e.*,
    ROW_NUMBER() OVER (
      PARTITION BY source_id, product_id
      ORDER BY updated_at DESC, event_id DESC
    ) AS rn
  FROM product_events e
) ranked
WHERE rn = 1;

SELECT source_id, COUNT(*) AS total, AVG(price_cents) AS avg_price
FROM products
GROUP BY source_id;

Practice

Two events v5 and v3 for the same source/entity complete in reverse order. What protects the latest state? What changes if each event sends an email?

Official references

• Node.js: sql-insert