Designing Idempotent Operations with Supabase: Retries, Webhooks, and Queues

An idempotent operation is one where running it twice has the same effect as running it once. That sounds academic until a user's card gets charged twice because their phone lost signal for two seconds after they tapped "Pay," or a Stripe webhook fires the same event twice and your app creates two orders for it. Idempotent operations in Supabase aren't an edge case reserved for payment systems. Any app using Edge Function webhooks, Supabase Queues, or a client that retries failed requests will eventually replay a request it already processed. This post covers why that happens and walks through the concrete Postgres and Supabase patterns that make replays harmless instead of duplicated: unique constraints, an idempotency-key table, webhook deduplication, idempotent queue consumers, and advisory locks.

Quick Answer

Make an operation idempotent by giving it something to check before it acts: a unique constraint on the thing that shouldn't repeat (an order, a webhook event, a payment attempt), combined with INSERT ... ON CONFLICT DO NOTHING. For requests that need to return the same response on a retry (not just avoid a duplicate row), store the result against a client-generated idempotency key the first time, and return the saved result on every later request with that same key, without redoing the work. Never implement this as "check if it exists, then insert" in application code; two concurrent requests can both pass the check before either one inserts. Let Postgres's unique constraint be the judge, and handle the conflict it raises.

What Is Idempotency?

Idempotency is a property of an operation, not of a system: calling it once or calling it five times leaves the data in the same state.

• PATCH /users/1 { name: "Alex" } is idempotent. Run it five times, the name is still "Alex."
• POST /orders { items: [...] } is not idempotent by default. Run it five times, you get five orders.
• UPDATE wallet SET balance = balance + 100 is not idempotent. Run it twice, the user is credited 200.
• UPDATE wallet SET balance = 500 is idempotent. Run it twice, the balance is still 500.

The goal of this post isn't to make every operation naturally idempotent like the last example. Most real operations ("charge this card," "create this order") aren't naturally idempotent, because they're supposed to have a one-time effect. The goal is to make retrying a request that already happened safe, by detecting the retry and skipping (or short-circuiting) the second execution.

The Problem

Four ordinary situations in a Supabase app cause the exact same request to arrive more than once:

1. Client network retries. A user taps "Checkout." The request reaches your server, the order is created, but the response never makes it back before the connection drops. The client sees a timeout and, per HTTP semantics, is right to retry. Your server has no way to know the first request actually succeeded.
2. Webhook redelivery. Stripe (and most webhook providers) resend an event if your endpoint doesn't respond in time, returns a non-2xx status, or errors mid-handler. Your Edge Function will receive the same checkout.session.completed event more than once. That's documented, expected provider behavior, not a bug on either side.
3. Supabase Queues (pgmq) redelivery. A consumer calls read(), which hides a message from other consumers for a visibility timeout window rather than deleting it immediately. If your consumer does the real work but crashes (or the Edge Function is killed) before it calls archive()/delete(), the message becomes visible again once the timeout expires and a consumer picks it up a second time. This makes Queues at-least-once in practice, not exactly-once: a message can be read and processed more than once whenever the handler doesn't finish its acknowledgment step.
4. Double-clicks and duplicate form submits. A slow page response tempts a user into clicking "Submit" twice. Two nearly-simultaneous requests, no network failure involved at all.

In every case, the fix is the same shape: something in your data model has to recognize "I've seen this exact request before" and refuse to repeat its effect.

The Solution

In order of how often you'll reach for each one:

• Give the thing that must not repeat (an order, a webhook event) a unique constraint, and insert with ON CONFLICT DO NOTHING instead of checking existence first.
• For requests where the client needs the same response back on a retry, use the idempotency-key pattern: store (key, response, status) once, return the stored response on every repeat.
• For webhooks specifically, dedupe on the provider's event ID, not on the payload content.
• For Supabase Queues consumers, treat every handler as guaranteed to run more than once and make its writes idempotent the same way.
• For a critical section that isn't a single insertable row (e.g. "only one worker should start this daily job"), use a Postgres advisory lock.

Prerequisites

• A Supabase project (or self-hosted Postgres 13+)
• psql, the Supabase SQL Editor, or the Supabase CLI for migrations
• @supabase/supabase-js if you're calling this from an Edge Function or a Next.js API route
• Basic familiarity with CREATE TABLE, unique constraints, and INSERT ... ON CONFLICT

Step-by-Step Implementation

Step 1: The core primitive: unique constraint + ON CONFLICT

Never do this. It's a race condition, not a fix:

-- Wrong: two concurrent requests can both pass the SELECT before either INSERTs
select id from orders where client_order_id = 'abc-123';
-- if not found:
insert into orders (client_order_id, user_id, total) values ('abc-123', ..., ...);

Do this instead. Let the database enforce uniqueness and tell you what happened:

create table public.orders (
  id              uuid        default gen_random_uuid() primary key,
  user_id         uuid        references auth.users(id) on delete cascade not null,
  client_order_id text        not null,
  total           numeric(10,2) not null,
  created_at      timestamptz default now() not null,
  unique (user_id, client_order_id)
);

insert into public.orders (user_id, client_order_id, total)
values ($1, $2, $3)
on conflict (user_id, client_order_id) do nothing
returning *;

client_order_id is a value the client generates once (a UUID created when the checkout button is tapped) and sends with every retry of that same request. If the insert returns a row, this is the first time you've seen it: proceed. If it returns zero rows, a row with that key already exists: this is a retry, so read the existing order instead of creating another one.

The same pattern works through supabase-js:

const { data, error } = await supabase
  .from("orders")
  .upsert(
    { user_id: userId, client_order_id: clientOrderId, total },
    { onConflict: "user_id,client_order_id", ignoreDuplicates: true }
  )
  .select();

ignoreDuplicates: true maps directly to ON CONFLICT DO NOTHING. This is enough for operations where "don't create a duplicate row" is the entire requirement.

Step 2: Build an idempotency-key table for request/response caching

ON CONFLICT DO NOTHING stops a duplicate row from being created, but it doesn't give a retry the original response: the second request gets zero rows back and has to look the real result up itself. For APIs where the client needs the exact same response on a retry (this is the pattern Stripe's own API uses), store the result against the key:

create table public.idempotency_keys (
  key         text        primary key,
  user_id     uuid        references auth.users(id) on delete cascade not null,
  status      text        not null default 'processing' check (status in ('processing', 'completed')),
  response    jsonb,
  created_at  timestamptz default now() not null
);

create index idx_idempotency_keys_created_at on public.idempotency_keys (created_at desc);

The handler logic, as a single Postgres function so the insert-then-work-then-update sequence stays inside one transaction:

create or replace function public.charge_with_idempotency(
  p_key text,
  p_user_id uuid,
  p_amount numeric
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_inserted int;
  v_response jsonb;
begin
  -- Try to claim this key. If it already exists, we skip straight to reading it back.
  insert into public.idempotency_keys (key, user_id, status)
  values (p_key, p_user_id, 'processing')
  on conflict (key) do nothing;

  get diagnostics v_inserted = row_count;

  if v_inserted = 0 then
    -- Someone already claimed this key. Return whatever it produced (or is producing).
    select response into v_response from public.idempotency_keys where key = p_key;
    return coalesce(v_response, jsonb_build_object('status', 'processing'));
  end if;

  -- This request owns the key. Do the real work exactly once.
  -- (In a real handler this might call out to a payment provider before writing the result.)
  v_response := jsonb_build_object('charged', p_amount, 'user_id', p_user_id);

  update public.idempotency_keys
  set response = v_response, status = 'completed'
  where key = p_key;

  return v_response;
end;
$$;

row_count after the INSERT ... ON CONFLICT DO NOTHING is the signal: 1 means this call is the one doing the work, 0 means a previous call already claimed the key. The unique constraint on key is what makes this safe under concurrency: two simultaneous requests with the same key can't both get v_inserted = 1, because Postgres serializes the conflicting inserts.

Step 3: Dedupe Stripe (or any) webhook events in an Edge Function

Webhook providers dedupe by event ID, not by payload. The same logical event can be delivered with the same ID more than once. Store IDs you've already handled and check before doing any work:

create table public.processed_webhook_events (
  event_id    text        primary key,
  source      text        not null,
  created_at  timestamptz default now() not null
);

// supabase/functions/stripe-webhooks/index.ts
import Stripe from "npm:stripe@^18";
import { createClient } from "jsr:@supabase/supabase-js@2";

const stripe = new Stripe(Deno.env.get("STRIPE_SECRET_KEY")!);
const webhookSecret = Deno.env.get("STRIPE_WEBHOOK_SIGNING_SECRET")!;

Deno.serve(async (req) => {
  const signature = req.headers.get("Stripe-Signature")!;
  const body = await req.text();

  let event: Stripe.Event;
  try {
    event = await stripe.webhooks.constructEventAsync(body, signature, webhookSecret);
  } catch (err) {
    return new Response(`Signature verification failed: ${err.message}`, { status: 400 });
  }

  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  );

  // Claim this event ID. If it's already there, we've handled it: return 200 without redoing work.
  const { error: insertError } = await supabase
    .from("processed_webhook_events")
    .insert({ event_id: event.id, source: "stripe" });

  if (insertError) {
    // Unique violation (23505) = duplicate delivery. Any other error is a real failure.
    if (insertError.code === "23505") {
      return new Response(JSON.stringify({ received: true, duplicate: true }), { status: 200 });
    }
    return new Response(insertError.message, { status: 500 });
  }

  if (event.type === "checkout.session.completed") {
    const session = event.data.object as Stripe.Checkout.Session;
    // orders.stripe_session_id needs its own unique constraint for this upsert to dedupe correctly
    await supabase
      .from("orders")
      .upsert(
        { stripe_session_id: session.id, status: "paid" },
        { onConflict: "stripe_session_id" }
      );
  }

  return new Response(JSON.stringify({ received: true }), { status: 200 });
});

Two things matter here: signature verification happens before touching the database (never trust an unverified payload), and the dedup insert happens before any business logic, not after. If it happened after, a crash between "do the work" and "record that we did it" would leave the event unrecorded and vulnerable to reprocessing on the next delivery.

Step 4: Make a Supabase Queues (pgmq) consumer idempotent

Because read() gives at-least-once delivery (a message reappears if the consumer doesn't archive()/delete() it before the visibility timeout expires), every consumer has to assume it might see the same message twice:

Deno.serve(async () => {
  const { data: rows, error } = await supabase.schema("pgmq_public").rpc("read", {
    queue_name: "send_receipts",
    sleep_seconds: 60,
    n: 10,
  });
  if (error) return new Response(error.message, { status: 500 });

  for (const msg of rows ?? []) {
    const { order_id } = msg.message;

    // Idempotent write: a receipt for this order_id can only exist once.
    const { error: insertError } = await supabase
      .from("sent_receipts")
      .insert({ order_id });

    if (!insertError || insertError.code === "23505") {
      // Either we just sent it, or a prior (crashed) attempt already did. Both are fine.
      if (!insertError) await sendReceiptEmail(order_id);

      await supabase.schema("pgmq_public").rpc("archive", {
        queue_name: "send_receipts",
        message_id: msg.msg_id,
      });
    }
    // Any other error: don't archive. Let the visibility timeout expire and retry.
  }

  return new Response("ok");
});

async function sendReceiptEmail(_orderId: string) {
  /* call your email provider */
}

The sent_receipts table with a unique constraint on order_id is what prevents a duplicate email. The archive() call only tells pgmq "don't redeliver this," which is a separate concern from "don't repeat the side effect."

Step 5: Use an advisory lock for work that isn't a single insertable row

Not every "only do this once" requirement maps to one row you can insert. "Only one worker should start generating today's report" is a good example: there's no natural unique key to conflict on until the report already exists. pg_try_advisory_xact_lock claims a lock scoped to the current transaction and returns immediately instead of blocking:

create or replace function public.try_start_daily_report(p_report_date date)
returns boolean
language plpgsql
as $$
begin
  -- hashtext() turns the date into a stable lock key; any two calls with the
  -- same date compete for the same lock.
  if not pg_try_advisory_xact_lock(hashtext('daily_report:' || p_report_date::text)) then
    return false; -- someone else already holds it
  end if;

  -- Safe to proceed: no other transaction can be inside this block for the same date.
  insert into public.report_runs (report_date, started_at) values (p_report_date, now());
  return true;
end;
$$;

The lock releases automatically when the transaction commits or rolls back, with no manual unlock step and no risk of a leaked lock from a crashed connection.

Step 6: Prove it, don't assume it

Send the same request twice and check the result count, not just that the second call "didn't error":

# Fire the same idempotency key twice, back to back
curl -s -X POST https://your-project.functions.supabase.co/charge \
  -H "Content-Type: application/json" \
  -d '{"key":"test-key-1","amount":100}'

curl -s -X POST https://your-project.functions.supabase.co/charge \
  -H "Content-Type: application/json" \
  -d '{"key":"test-key-1","amount":100}'

-- Only one row should exist for the key, regardless of how many times you sent it
select count(*) from idempotency_keys where key = 'test-key-1'; -- expect 1

A response body that looks identical on both calls is not proof. Check the database for row counts instead: that's the only way to know whether the side effect ran once.

Common Problems / Errors

The unique constraint exists, but I still get two rows sometimes

Almost always means the code checked existence with a SELECT before inserting instead of relying on ON CONFLICT. Under concurrency, two requests can both pass the SELECT before either commits its INSERT. Replace the check-then-insert with a single INSERT ... ON CONFLICT.

"duplicate key value violates unique constraint" is bubbling up as a 500 error

This is expected behavior for a concurrent duplicate, not a bug. Catch Postgres error code 23505 specifically and treat it as "already handled," the same way the webhook example above does, instead of letting it propagate as a server error.

A Stripe webhook was processed twice even though I check event.type

Checking event.type filters which events you act on, it doesn't detect redelivery of the same event. You need the dedup table keyed on event.id, independent of what the event type is.

A queue consumer occasionally double-sends even with idempotent-looking code

Check whether the "idempotent" write happens after an external side effect (like calling an email API) instead of before or atomically with it. If the email send happens, then the process crashes before the dedup row commits, the retry has nothing to detect and sends again. Order operations so the side effect you can't undo happens after (or is itself deduped by) the write that would block a repeat.

pg_try_advisory_xact_lock never seems to block a second caller

Advisory locks are keyed by a numeric ID. If two different logical resources hash to the same key (or you're passing an inconsistent key derivation, like an unstable timestamp instead of a stable date), they either falsely collide or never collide. Derive the key from stable, meaningful data (hashtext('daily_report:' || date)), and keep the derivation identical everywhere it's called.

Best Practices

• Never implement idempotency as check-then-insert in application code. Let a unique constraint and ON CONFLICT do the check atomically.
• Use a client-generated key (UUID) for anything the client might retry. The client is the only party that reliably knows "this is the same logical request as before."
• Dedupe webhooks by provider event ID, verify the signature before touching the database, and insert the dedup row before running business logic.
• Treat every Supabase Queues consumer as guaranteed to run more than once per message, not as a nice-to-have safety margin.
• Store the response, not just a "handled" flag, when the client needs the same answer back on retry. A flag alone forces you to reconstruct the response some other way.
• Catch Postgres error 23505 explicitly where you expect conflicts, instead of letting a generic error handler mask what actually happened.
• Reach for pg_try_advisory_xact_lock only when the work isn't reducible to one insertable row. It's a coordination tool, not a replacement for a unique constraint.

Performance and Security Considerations

An idempotency-key table grows forever if nothing prunes it. Add a scheduled cleanup (a Supabase Cron job calling delete from idempotency_keys where created_at < now() - interval '30 days') once keys are old enough that no client could plausibly still be retrying with them.

security definer functions like charge_with_idempotency above run with the function owner's privileges, so always pin set search_path = '' and use fully-qualified table names (public.idempotency_keys). An unset search path on a security-definer function is a real privilege-escalation path, not a style preference.

Idempotency keys are meant to be unguessable, single-use tokens tied to one logical request. Never derive one from predictable data (an incrementing counter, a timestamp alone) that another user could forge to read back someone else's cached response. Scope the lookup by user_id as well as the key, as the charge_with_idempotency example does, so one user can never retrieve another user's stored response even if they somehow guessed the key.

Alternatives / Comparison

Idempotency Key vs. Plain Unique Constraint vs. Advisory Lock

These aren't competing choices. A payment endpoint typically uses an idempotency-key table for the client-facing contract, while the order it creates underneath still gets its own unique constraint.

When Should You Use It?

Build explicit idempotency handling when:

• The operation has a side effect a user would notice being repeated (a charge, an email, an order)
• The request goes through a webhook provider, a message queue, or any path with documented at-least-once delivery
• Clients on unreliable networks (mobile especially) might legitimately retry a request

You can skip it when:

• The operation is naturally idempotent already (a PATCH that sets an absolute value, a DELETE by ID)
• It's a pure read with no side effects
• All access to the resource is fully serialized elsewhere and duplicate submission is structurally impossible (rare in practice)

If you're designing the checkout or webhook path of a new multi-tenant SaaS architecture, decide the idempotency-key contract before writing the first handler. Retrofitting it after a duplicate-charge incident is a much harder conversation.

FAQ

What is an idempotency key in Supabase or Postgres?

It's a unique value, usually a client-generated UUID, attached to a request and stored in a table with a unique constraint on that value. The first request with a given key does the real work and saves its result; any later request with the same key returns the saved result instead of repeating the work.

Does ON CONFLICT DO NOTHING make an operation idempotent?

It makes the insert idempotent: retrying it never creates a second row. It doesn't automatically give the retry the original response; for that you need the idempotency-key pattern (Step 2), which stores and replays the result.

Is Supabase Queues exactly-once or at-least-once delivery?

In practice, at-least-once. A message stays hidden for a visibility timeout after read(), but if the consumer doesn't archive() or delete() it before that timeout expires (a crash mid-processing, for example), it becomes visible again and gets redelivered. Consumers need to be written as if every message can arrive more than once.

Why shouldn't I just check if a row exists before inserting it?

Because two requests running at nearly the same time can both run the SELECT and both see "not found" before either one's INSERT commits, producing two rows anyway. A unique constraint combined with ON CONFLICT moves that check into Postgres itself, where it's evaluated atomically.

How do I make a Stripe webhook handler idempotent in a Supabase Edge Function?

Insert the incoming event's id into a table with a unique constraint before running any business logic. If the insert raises a unique-violation error (Postgres code 23505), you've already processed that event. Return a 200 without redoing the work.

Do I need idempotency keys for GET requests?

No. A GET shouldn't have side effects, so it's idempotent by definition. Sending an idempotency key with a GET has no effect, which is also how Stripe's own API treats it.

Conclusion

Duplicate charges, duplicate orders, and double-sent emails almost never come from a logic bug. They come from a request arriving more than once and the system having no way to recognize the repeat. A unique constraint with ON CONFLICT DO NOTHING stops duplicate rows outright; an idempotency-key table lets a retried request get back the exact response the first one produced; deduping webhooks by event ID and treating queue consumers as at-least-once closes the two gaps most Supabase apps hit in production. None of this requires exotic infrastructure: it's a handful of unique constraints and one extra table, applied at the places where a request can legitimately arrive twice.

Need Help Hardening a Supabase Backend?

If your app is handling payments, webhooks, or background jobs and you're not confident a network retry or a redelivered webhook can't create a duplicate, our backend development team can review your schema and request paths and close the gaps before they show up as a support ticket. Contact us to discuss your project.

Further Reading

• Supabase Row Level Security at Scale: Fixing Performance Problems
• Supabase Queues: Building Reliable Background Jobs
• Supabase Edge Functions: When to Use Serverless Functions vs Your Backend