supabase-js upsert and PostgreSQL ON CONFLICT idempotency for retried inserts

Idempotency and Response Contract

When using supabase-js upsert with a defined onConflict column matching a unique constraint, retried inserts are idempotent if the conflict resolution is set to DO NOTHING. The promise resolves without error; it does not throw a unique violation error.

Differentiating Skip from Failure

A caller must differentiate a benign duplicate skip from a genuine failure by examining the promise resolution state, not just the returned row count.

• Genuine Failure: The promise rejects (e.g., RLS violation, permission error, malformed request).
• Benign Skip (DO NOTHING): The promise resolves successfully, but the returned data array is empty, and the HTTP response is typically 204 No Content.
• Update Success (DO UPDATE): The promise resolves, and the returned data array contains the existing or newly updated row.

RLS Evaluation

Confirmed fact: PostgreSQL Row Level Security (RLS) policies are evaluated against the INSERT command itself. The RLS check occurs before the conflict resolution logic (DO NOTHING or DO UPDATE) is applied; therefore, security behavior does not differ between the normal insert path and the conflict path.

Practical Steps

1. Ensure the target table has a unique or exclusion constraint on the column specified in onConflict.
2. Configure the upsert operation to use onConflict without specifying update columns if DO NOTHING semantics are desired for idempotency.
3. Upon resolution, check for promise rejection first. If resolved, check if the returned data is empty to confirm a benign skip.

The only missing diagnostic detail that changes the recommendation is confirmation of whether the upsert configuration enforces DO NOTHING or DO UPDATE semantics for the conflict target.