A Conditional UPDATE Needs error, Not Just data

The short version

A supabase-js conditional UPDATE does not throw on a write error. It just returns data:null and error. If the idempotency check “zero rows means already processed” branches on the presence of data alone, a transient write failure gets counted as “already processed” too.

In Stripe webhook handling this surfaced as “we detected a real card payment, and the receipt is never issued”. The fix is to look at error and throw. That is all.

What it looks like

• The card payment webhook arrives (Stripe’s logs show it was delivered)
• The contract’s payment status is not updated
• No receipt email is sent
• The Stripe dashboard shows the delivery as successful (no redelivery)

No error is recorded. The processing counts as having succeeded, yet only the result never happened — that was the discrepancy.

Why

Settling a payment is implemented idempotently with a conditional UPDATE.

const { data: claimed, error } = await admin
  .from("contracts")
  .update(patch)
  .eq("id", contractId)
  .in("payment_status", ["未請求", "請求済"])
  .select("id")
  .maybeSingle();

if (!claimed) return { claimed: false }; // ← judged 0 rows = already processed, without reading error

If payment_status is not one of the target states, the UPDATE finishes having updated zero rows. That is the correct behaviour when the same event arrives twice: the second time claimed is null and it exits doing nothing. An intended idempotent no-op.

The problem is that data is also null when error is populated. supabase-js does not throw on a DB error; it returns a value of the same shape, {data:null, error}. When the caller decides “already processed or not” from the presence of data alone,

zero rows (intended idempotent no-op)         → data:null, error:null
transient write failure (pooler timeout etc.) → data:null, error:{...}

these two look identical as long as you only read data. Mistake the second for “already processed” and a real card payment is swallowed while the handler returns normally. Because the handler throws nothing, the calling webhook route also treats it as a success and returns 200. Stripe does not resend a successful delivery, so this payment is finalised without appearing anywhere in the records.

Fixing it

Read error explicitly and throw if it is there.

if (error) {
  throw new Error(`markContractPaid update failed: ${error.message}`);
}
if (!claimed) return { claimed: false }; // already paid = idempotent no-op

The key is placing the error check before the data check. That separates “write failure” from “intended idempotent no-op” cleanly.

The thrown exception is caught by the calling webhook route.

try {
  const { contractId } = await handleStripeEvent(admin, stripe, event);
  await admin.from("stripe_events")
    .update({ processed_at: new Date().toISOString(), contract_id: contractId })
    .eq("id", event.id);
  return Response.json({ received: true });
} catch (e) {
  // Delete the ledger row and 500 → let Stripe's redelivery reprocess it (at-least-once).
  await admin.from("stripe_events").delete().eq("id", event.id);
  return new Response(`error: ${e instanceof Error ? e.message : "error"}`, { status: 500 });
}

The INSERT into the idempotency ledger (stripe_events) is protected by a unique constraint on event.id, so a Stripe redelivery cannot double-process. Throwing and returning 500 is both the signal “this failed” and the switch that starts the recovery path of Stripe redelivery. Quietly returning 200 here means that recovery path is never used.

The generalisable shape

This shape is not limited to settling payments. It applies to every conditional operation where “zero rows” can mean more than one thing.

• Intended zero (duplicate, already processed, out of scope)
• Unintended zero (transient write failure, dropped connection, timeout)

Beyond supabase-js, in any SDK designed to return errors as a tuple like {data, error} rather than throwing, error has to be read before branching on the contents of data. This implementation makes the same split on the ledger INSERT side.

if (insErr) {
  if (String(insErr.code ?? "").includes("23505")) {
    return Response.json({ received: true, duplicate: true }); // intended duplicate
  }
  return new Response("ledger insert failed", { status: 500 }); // unintended failure
}

The point is that the error code explicitly separates “intended duplicate” from “unintended failure”. The branch condition is written specifically so that code meant to swallow one does not swallow the other along with it.