Two Webhook Deliveries Need One Database Claim

Article · Interrupted execution

A duplicate webhook test passes when deliveries run one after another. The first delivery changes the account and records the event. The second finds the record and exits.

Now let both deliveries check at the same time. Each can observe that the event is absent. Each can then apply the change. The existence check was an observation. It never reserved the right to process the event.

Use a database constraint to choose the first local application of the event. Keep that claim and its business change in the same transaction. Define what the other caller receives, including the case where an event identity arrives with changed content.

Give the claim the right scope

The lab uses a primary key over tenant, provider, and event identity. A provider event ID is not automatically unique across all customers and integrations. The scope should match the provider's actual contract and your account mapping.

The fixture stores a payload marker beside the identity. Its strings are synthetic matching values, not cryptographic digests. A production implementation should compute an appropriate digest from authenticated event data or retain protected bytes. First verify the provider signature and map the tenant from trusted configuration.

This is the complete application function used by the concurrent test:

async fn apply_event(
    pool: PgPool,
    payload: &str,
) -> std::result::Result<&'static str, sqlx::Error> {
    let mut tx = pool.begin().await?;
    let claimed: Option<String> = sqlx::query_scalar(
        "INSERT INTO received_events (tenant_id, provider, event_id, payload_hash)
         VALUES ('tenant-a', 'fixture', 'event-42', $1)
         ON CONFLICT (tenant_id, provider, event_id) DO NOTHING RETURNING event_id",
    )
    .bind(payload)
    .fetch_optional(&mut *tx)
    .await?;
    let outcome = if claimed.is_some() {
        sqlx::query("UPDATE business_effect SET value = value + 1")
            .execute(&mut *tx)
            .await?;
        "applied"
    } else {
        let original: String = sqlx::query_scalar("SELECT payload_hash FROM received_events WHERE tenant_id = 'tenant-a' AND provider = 'fixture' AND event_id = 'event-42'")
            .fetch_one(&mut *tx)
            .await?;
        if original == payload {
            "duplicate"
        } else {
            "conflict"
        }
    };
    tx.commit().await?;
    Ok(outcome)
}

The table also has a small counter representing the local business effect. The complete schema and test are in the release fixture. The function is deliberately small so that the transaction boundary is visible. The fixture uses fixed tenant, provider, and event identities throughout. A real handler should bind its authenticated request identity consistently in both the claim and the lookup.

Read the outcome of the claim

When RETURNING yields an event identity, this transaction inserted the claim. It increments the counter and commits both facts together. When no identity is returned, it reads the previously stored payload marker and selects either duplicate or conflict.

PostgreSQL's INSERT documentation describes ON CONFLICT DO NOTHING. For this lab, the normal Read Committed isolation level lets the later lookup observe the competing committed row. Do not copy that assumption into another isolation level without checking its visibility and retry rules.

The function returns an outcome only after commit. A connection error while committing can leave the caller uncertain. The lost commit reply investigation shows why that error needs a lookup or same-key recovery path rather than a new event identity.

A conflict is different from a duplicate. Reusing the identity with changed content can indicate a provider mistake, a mapping error, or hostile input. Preserve enough safe evidence to investigate it. Do not overwrite the original payload identity just to make the request succeed.

Release two deliveries together

The Rust test creates two tasks and a three-party barrier. Both tasks wait before calling the application function. The observer releases them together, then awaits both results. The expected outcomes are one applied and one duplicate.

A separate query then checks one claim and a counter value of one. The test submits the changed payload marker and expects conflict. The counter must remain one. These are stored-state assertions, not just checks that neither task panicked.

This exercises simultaneous calls to PostgreSQL 18 through SQLx 0.8.6. The barrier coordinates attempts at the application boundary. It does not prove every possible scheduler order, network failure, or database isolation setting. The unique constraint is the protection that survives another process or replica; the barrier makes one important overlap easy to reproduce.

The transaction guide explains which local writes should share a commit. Keep the receipt and the effect together even when the effect later grows beyond one counter update.

Decide whether the claim means received or applied

A receipt committed before the business change needs a pending state and a worker. Otherwise a stopped process can leave an event marked seen even though its effect never happened. The function here uses an applied-event claim: the claim becomes durable with its effect.

A larger design might accept the webhook quickly and process it later. That can be useful, but reception and application are different facts. Persist recoverable input and expose processing failures. The request's success response should describe the fact it actually committed.

Choose a duplicate response that matches the provider's retry contract. The idempotency guide covers returning an earlier result and rejecting changed requests. Avoid treating every database error as a successful duplicate.

External effects need another boundary. Sending email or requesting a refund inside this transaction does not give PostgreSQL control over that provider. Store durable intent and use a documented remote recovery contract. The useful guarantee here is one committed local application for this scoped identity and the tested path, with a visible conflict when the input changes.