Handle duplicate project webhooks with one SQLite transaction

By Scopefolio Support. Prepared with AI assistance.

A project integration receives a completion event, saves the new status, and loses its HTTP response on the way back to the sender. The sender retries. Your endpoint now has to recognize work it already committed.

Recording an event ID helps, but the receipt and project update need to succeed together. Saving the receipt first can lose work after a crash. Saving it afterward can allow the same work twice.

Define the guarantees before the handler

This illustrative integration receives immutable events containing a project snapshot and an increasing integer revision. A revision identifies one project state. The trusted connection key identifies the provider and source account; project IDs and event IDs are unique within that connection.

Those are requirements of this example, not universal webhook guarantees. Stripe's webhook documentation, for example, warns about both duplicate deliveries and unordered events. An event timestamp is not a reliable substitute for a resource revision.

Save this as store.mjs. It uses Node's built-in SQLite module, without installing packages:

import {DatabaseSync} from "node:sqlite";

export function store() {
  const db = new DatabaseSync(":memory:");
  db.exec(`
    CREATE TABLE receipts (
      connection TEXT NOT NULL, id TEXT NOT NULL,
      PRIMARY KEY(connection, id)
    );
    CREATE TABLE projects (
      connection TEXT NOT NULL, id TEXT NOT NULL,
      version INTEGER NOT NULL,
      status TEXT NOT NULL CHECK(status IN ('active', 'done')),
      PRIMARY KEY(connection, id)
    );
  `);
  const receipt = db.prepare(`INSERT INTO receipts VALUES (?, ?)
    ON CONFLICT(connection, id) DO NOTHING`);
  const update = db.prepare(`INSERT INTO projects VALUES (?, ?, ?, ?)
    ON CONFLICT(connection, id) DO UPDATE SET
      version = excluded.version, status = excluded.status
    WHERE excluded.version > projects.version`);

  function apply(e) {
    if (!e || ![e.connection, e.id, e.project, e.status].every(
      value => typeof value === "string" && value.length > 0) ||
      !Number.isSafeInteger(e.version) || e.version < 1) {
      throw new Error("Invalid event");
    }
    db.exec("BEGIN IMMEDIATE");
    try {
      let result = "duplicate";
      if (receipt.run(e.connection, e.id).changes) {
        result = update.run(e.connection, e.project, e.version, e.status)
          .changes ? "applied" : "stale";
      }
      db.exec("COMMIT");
      return result;
    } catch (error) {
      db.exec("ROLLBACK");
      throw error;
    }
  }
  return {db, apply};
}

Keep two decisions separate

The receipt's unique key handles repeated deliveries of one event. The conditional update handles a different event that describes an older or equal revision. Both are necessary. A previously unseen event can still contain stale state.

The receipt remains committed for a stale event because processing it is complete. A failed update rolls back its new receipt, allowing a retry after the underlying problem is fixed. SQLite documents these transaction boundaries and the conditional behavior of UPSERT.

Exercise the failure path

Save this as store.test.mjs:

import assert from "node:assert/strict";
import test from "node:test";
import {store} from "./store.mjs";

test("duplicate, stale, isolated, and failed deliveries", () => {
  const {db, apply} = store();
  const e = {connection: "tracker:team-a", id: "e2",
    project: "p1", version: 2, status: "done"};
  try {
    assert.equal(apply(e), "applied");
    assert.equal(apply(e), "duplicate");
    assert.equal(apply({...e, id: "e1", version: 1, status: "active"}), "stale");
    assert.equal(db.prepare("SELECT status FROM projects").get().status, "done");
    assert.equal(apply({...e, connection: "tracker:team-b"}), "applied");
    assert.throws(() => apply({...e, id: "e3", version: 3, status: "invalid"}));
    assert.equal(db.prepare("SELECT count(*) AS n FROM receipts WHERE id='e3'")
      .get().n, 0);
    assert.equal(apply({...e, id: "e3", version: 3, status: "active"}), "applied");
  } finally {
    db.close();
  }
});

Run node --test store.test.mjs. This exact example passed its test and eight assertions on Node v24.19.0. The test uses memory storage and does not demonstrate durability across process crashes or concurrent workers.

Connect this core carefully

Verify the provider's signature using its documented method before creating trusted input. For Stripe, verification needs the unmodified request body. Validate the event type and schema, and resolve the connection from authenticated configuration. An event ID cannot authenticate a sender. Stripe's verification guidance explains that boundary.

A deployed handler needs persistent storage, migrations, lock-contention handling, and a receipt-retention policy covering supported replays. Acknowledge success only after this short transaction, or after durably queuing the event for later processing.

Keep the connection namespace in both tables. Two independent customer accounts may each have a project named p1 and an event named e2. A globally unique receipt key alone would let the first account suppress the second account's update. The isolation assertion exercises this exact collision.

Log the event ID, connection, and result without storing unnecessary payload data. Repeated duplicate results can be normal; database errors need investigation. If one revision ever arrives with conflicting state, the source has violated this model's assumptions. Flag that inconsistency rather than silently choosing whichever payload arrived first.

If a provider supplies no reliable revision, use a provider-specific reconciliation strategy instead of inventing ordering from arrival time. For notifications or other external effects, write an outbox job in the same transaction and make its consumer idempotent too. A database rollback cannot unsend an email.