Supabase client retry behavior and PostgreSQL upsert interaction for idempotent writes

Answer the exact question first

1. Disabling or customizing retry count
The Supabase JavaScript SDK exposes a maxRetries option on the PostgrestClient constructor. Setting maxRetries: 0 turns off automatic retrying of failed requests. If the option is omitted, the client attempts up to three retries with exponential back‑off.

2. Client‑generated UUID with ON CONFLICT DO NOTHING
When you use ON CONFLICT DO NOTHING on a UUID primary key, the UUID must be supplied by the client *before* the first request. If the UUID is generated server‑side or omitted, each retry will produce a new UUID and insert a new row, defeating idempotency.

3. Edge Function idempotency‑key handling
An Edge Function that relies on an Idempotency-Key header should respond with a 4xx status (e.g., 400 or 422) when the header is missing. This prevents accidental duplicate writes that could occur if the function silently generates a key on each retry.

Likely explanation

The SDK’s retry logic is independent of the SQL statement; it simply re‑issues the HTTP request. Therefore, any idempotency guarantees must come from the database (e.g., ON CONFLICT clauses) or from application‑level logic (e.g., checking an Idempotency-Key table). If the client fails to supply a stable identifier before the first attempt, retries will treat each attempt as a distinct write.

Confirmed facts

• In SDK versions that expose maxRetries, setting it to 0 disables retries.
• Default retry count is three attempts with exponential back‑off.
• Client‑generated UUIDs must be stable across retries to avoid duplicate rows.
• Edge Functions should reject missing Idempotency-Key headers to avoid unintended writes.

Steps for this case

1. Instantiate the client with new PostgrestClient(url, { maxRetries: 0 }) if you want no automatic retries.

2. Generate the UUID once on the client (e.g., const id = crypto.randomUUID()) and include it in the INSERT payload.

3. Use INSERT INTO table (id, …) VALUES (…) ON CONFLICT DO NOTHING to guarantee that retries do not create new rows.

4. In your Edge Function, add a guard:

   const key = req.headers.get('Idempotency-Key');
   if (!key) return new Response('Missing Idempotency-Key', { status: 400 });
   // look up key in a unique table; if exists, return the existing result, else perform write
   

Missing diagnostic detail

Could you confirm which Supabase JavaScript SDK version you are using? Earlier releases may not expose the maxRetries option, and the default retry behavior might differ.