Approach (brief): accept Idempotency-Key header; persist an Idempotency record (key, status, request_hash, response_payload, created_at, expires_at). Use a DB unique constraint on idempotency_key to serialize creators; wrap create-or-acquire and profile creation in a single DB transaction when possible. On concurrent requests, the first wins and others return stored response.Database schema (Postgres snippet):sql
CREATE TABLE idempotency (
id BIGSERIAL PRIMARY KEY,
idempotency_key TEXT NOT NULL UNIQUE,
user_id UUID, -- populated after creation
request_hash TEXT, -- optional: verify same payload
response_payload JSONB, -- stored response (or location header)
status VARCHAR(20) NOT NULL, -- PENDING, COMPLETED, FAILED
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
expires_at TIMESTAMP WITH TIME ZONE
);
Implementation outline (Spring Boot service):- Controller reads Idempotency-Key header, computes optional requestHash (hash of body).- Call service.createUserProfile(idemKey, requestDto).- Service logic (transactional): 1. Try insert idempotency row with status='PENDING' (INSERT ...). If insert succeeds, proceed to create user profile. 2. If insert fails due to unique constraint, SELECT row FOR UPDATE (or simple SELECT) to read status: - If status='PENDING': wait/retry with short backoff or return 409/202 depending on policy. - If status='COMPLETED': return stored response_payload. - If status='FAILED': optionally allow retry if request_hash matches. 3. After creating user (in same transaction or another depending on DB ops), update idempotency row: set user_id, response_payload, status='COMPLETED', updated_at. 4. Commit transaction and return response.Sample service pseudo-code (key parts):java
@Transactional
public Response createUserProfile(String idemKey, UserDto dto) {
try {
idempotencyRepo.insert(new Idempotency(idemKey, "PENDING", hash(dto)));
User user = userRepo.save(mapToEntity(dto)); // creates profile
Idempotency rec = idempotencyRepo.findByKeyForUpdate(idemKey); // optional
rec.setUserId(user.getId());
rec.setResponsePayload(serializeResponse(user));
rec.setStatus("COMPLETED");
idempotencyRepo.save(rec);
return buildResponse(user);
} catch (DataIntegrityViolationException e) {
// unique constraint hit — another request in-flight or completed
Idempotency existing = idempotencyRepo.findByKey(idemKey);
if ("COMPLETED".equals(existing.getStatus())) {
return deserializeResponse(existing.getResponsePayload());
} else if ("PENDING".equals(existing.getStatus())) {
// either poll/wait, or return 202 Accepted with retry-after
throw new ResponseStatusException(HttpStatus.ACCEPTED, "Request in progress");
} else {
// FAILED or mismatch: allow retry if payload hash matches
}
}
}
Transaction boundaries & isolation:- Use a transaction that covers inserting the PENDING idempotency row and the creation of the user when possible. This prevents other requests from proceeding beyond unique constraint. If user creation calls external systems, consider two-phase approach: insert PENDING (commit), perform external call, then update to COMPLETED in a second transaction. That requires handling PENDING reconciliation (background job to pick up stale PENDING rows).- Use UNIQUE(idempotency_key) to enforce single creator. Optionally SELECT ... FOR UPDATE to read/lock the row for readers.- Set reasonable expires_at to garbage-collect old idempotency records.Edge cases & best practices:- Store enough response data (location, resource id) to return identical responses.- Compute and compare request_hash to detect different payloads using same key — if mismatch, return 409.- Protect against long-running PENDING by background sweeper to mark timeouts or retries.- Consider optimistic approaches for high scale: use idempotency table in fast KV store (Redis) with atomic SETNX+value and a durable write to DB asynchronously, ensuring durability trade-offs are acceptable.