Support for standard Idempotency-Key header

[steve-chavez]

Problem

Currently idempotent requests (GET, HEAD) are retryable but not non-idempotent requests like POST or PATCH are not. This makes those requests fault intolerant in case of network errors.

Solution

Add support for specifying the standard Idempotency-Key header. The RFC is currently in draft but some services already support it:

• https://stripe.com/docs/api/idempotent_requests
• https://developers.squarespace.com/commerce-apis/idempotency-key

The main idea:

# 1st request
POST /projects
Authorization: Bearer $JWT
Idempotency-Key: 8e03978e-40d5-43e8-bc93-6894a57f9324

{"name": "project-1"}

# generated query
INSERT INTO "test"."projects"("id", "name")
SELECT "pgrst_body"."id", "pgrst_body"."name" FROM (SELECT '{"id": 346, "name": "project-1"}'::json AS json_data) pgrst_payload,
LATERAL (SELECT CASE WHEN json_typeof(pgrst_payload.json_data) = 'array' THEN pgrst_payload.json_data ELSE json_build_array(pgrst_payload.json_data) END AS val) pgrst_uniform_json,
LATERAL (SELECT "id", "name" FROM json_to_recordset(pgrst_uniform_json.val) AS _("id" integer, "name" text) ) pgrst_body
RETURNING "test"."projects".*;

HTTP/1.1 201 Created
{"id": 345, "name": "project-1"}

# 2nd request

POST /projects
Authorization: Bearer $JWT
Idempotency-Key: 8e03978e-40d5-43e8-bc93-6894a57f9324

HTTP/1.1 201 Created
{"id": 345, "name": "project-1"}

# generated query, no INSERT this time
SELECT "pgrst_body"."id", "pgrst_body"."name" FROM (SELECT '{"id": 346, "name": "project-1"}'::json AS json_data) pgrst_payload,
LATERAL (SELECT CASE WHEN json_typeof(pgrst_payload.json_data) = 'array' THEN pgrst_payload.json_data ELSE json_build_array(pgrst_payload.json_data) END AS val) pgrst_uniform_json,
LATERAL (SELECT "id", "name" FROM json_to_recordset(pgrst_uniform_json.val) AS _("id" integer, "name" text) ) pgrst_body

Under error it should cache the error:

# 1st request
POST /projects
Authorization: Bearer $JWT
Prefer: return=representation
Idempotency-Key: fe1fd58f-28fa-4312-8787-9f431798d859

{"code":"23502","details":"Failing row contains (null, project-1, null).","hint":null,"message":"null value in column \"id\" of relation \"projects\" violates not-null constraint"}

# 2nd request
POST /projects
Authorization: Bearer $JWT
Prefer: return=representation
Idempotency-Key: fe1fd58f-28fa-4312-8787-9f431798d859

{"code":"23502","details":"Failing row contains (null, project-1, null).","hint":null,"message":"null value in column \"id\" of relation \"projects\" violates not-null constraint"}

In this case there's no need to change the query.

Implementation Notes

• The idempotency key must be an UUID. The rfc recommends UUIDs but doesn't mandate it. Some services support long strings additionally to uuid. e.g. Stripe supports 255 chars string (ref) and squarespace supports 64 char string (ref). Let's avoid the inconsistency and only support UUID. This should reduce memory usage too.
• The idempotency fingerprint will be the JWT for a better uniqueness guarantee. This makes sense since we already have JWT caching. This means the same uuid cannot be used for any other operation until the JWT expires.
• The idempotency expiry will be tied to the JWT lifetime. This should be valid for the exp (expiration) claim in the JWT or the jwt-cache-max-lifetime (introduced on feat: implement JWT caching #2928).
  • Coincidentally, we thought of a 24 hour lifetime for JWTs without exp, and Stripe also has a 24 hour limit for the idempotency key caching (ref).

Open Questions

• Would there be a problem if the JWT lifetime is short? If it expires too soon then a UUID could be treated as a new request and not a retry.

[steve-chavez]

The idempotency fingerprint will be the JWT for a better uniqueness guarantee.

The above looks wrong, the RFC recommends:

An idempotency fingerprint generation algorithm may use one of the following or similar approaches to create a fingerprint.

Checksum of the entire request payload.
Checksum of selected element(s) in the request payload.
Field value match for each field in the request payload.
Field value match for selected element(s) in the request payload.
Request digest/signature.

I think we need the checksum for this to work. Also considering the request path.

[wolfgangwalther]

The two generated queries are not equivalent. This part is missing:

RETURNING "test"."projects".*;

I.e. you need to select the right columns for the SELECT-only query. + you need a WHERE condition to filter only the same row that was inserted earlier.

This will only work reliably, if:

• the table has a primary key
• you cache the idempotency-key + primary key combo

[steve-chavez]

This will only work reliably, if:

the table has a primary key
you cache the idempotency-key + primary key combo

That sounds good, that way we don't have to parse the request body. We could just get the id with RETURNING.

Seems this should work for batch inserts too? We'd do a checksum of all the pks in the payload + idempotency-key plus cache it.

This should guarantee enough uniqueness I believe, so we don't need to use the JWT.

[wolfgangwalther]

Seems this should work for batch inserts too? We'd do a checksum of all the pks in the payload + idempotency-key plus cache it.

I don't think a checksum of PKs would help at all. You need the actual PKs to run the SELECT query, not a checksum.

This should guarantee enough uniqueness I believe, so we don't need to use the JWT.

I think you are confusing what the "idempotency fingerprint" is about. You can't use any of the PKs - because you don't know them before, when sending the request. Whatever you use as a fingerprint needs to come from the request itself, there is no way around that.

Using something from the JWT (either the JWT itself or just the role claim) would at least make sure that different users can't collide with the same idempotency key. When using UUID this is not a problem, but UUID is just a recommendation, not a requirement.

Using a checksum of the full payload makes the most sense, imho.