Designing fault-tolerant and idempotent APIs with HTTP requests mapped to database's transactions at 1:1 model

CreateNewUserController.java

/**
 * This Spring Boot controller was implemented as an example of a simple but robust idempotent REST API that
 * leverages the ACID properties of a relational database.
 */
@RestController
public class CreateNewUserController {

    @Autowired
    private UserRepository repository;
    @Autowired
    private EventsRepository eventsRepository; // a table that works as a queue for background jobs

    @Transactional(
        // we can use a strictier isolation level to prevent race conditions
        isolation = Isolation.SERIALIZABLE 
    )
    @PostMapping("/api/users")
    public ResponseEntity<?> create(@RequestBody @Valid NewUserRequest request) {
        
        // if user already exists, then we simplify the client-side returning status 200-OK
        if (repository.existsByEmail(request.getEmail())) {
            return ResponseEntity.ok("User exists"); // http 200
        });

        // create the new user
        User user = request.toModel();
        repository.save(user); // don't worry, we have an UNIQUE constraint here
      
        // enqueue a job to tell an external service that a new user's been created
        eventsRepository.save(
            new Event("user_created:send_welcome_email", Map.of("email", user.getEmail()))
        );

        // pass back a successful response
        URI location = ...;
        return ResponseEntity
                .created(location).build(); // http 201
    }
    
    
    /**
     * Controller Advice for the UNIQUE constraint error
     */
    @ExceptionHandler(ConstraintViolationException.class) // org.hibernate.exception.ConstraintViolationException
    public ResponseEntity<?> handleUniqueConstraintErrors(ConstraintViolationException e, WebRequest request) {

        Map<String, Object> body = Map.of(
                "timestamp", LocalDateTime.now(),
                "message", "User exists"
        );

        return ResponseEntity.ok(body); // http 200
    }
    
    /**
     * Controller Advice for Serializable isolation level errors
     */
    @ExceptionHandler({
        CannotAcquireLockException.class, // org.springframework.dao.CannotAcquireLockException
        LockAcquisitionException.class, // org.hibernate.exception.LockAcquisitionException (nested exception)
    })
    public ResponseEntity<?> handleSerializableErrors(RuntimeException e, WebRequest request) {

        Map<String, Object> body = Map.of(
                "timestamp", LocalDateTime.now(),
                "message", "Sorry, another request is being processed at this moment"
        );

        return ResponseEntity
                .status(HttpStatus.CONFLICT).body(body); // http 409
    }
    
}

Some fantastic articles on how to design and implement fault-tolerant idempotent APIs leveraging a relational database:

• Building Robust Systems with ACID and Constraints
• Using Atomic Transactions to Power an Idempotent API
• Implementing Stripe-like Idempotency Keys in Postgres
  • api.rb: The Ruby code of a fault-tolerant and idempotent API implemented in the article
  • Procfile: Background jobs needed to help the API with fault-tolerance implemented in the article
• Transactionally Staged Job Drains in Postgres

• My tweet on designing and implementing idempotent APIs (pt_BR)

• Making retries safe with idempotent APIs

An important consideration is that the process that combines recording the idempotent token and all mutating operations related to servicing the request must meet the properties for an atomic, consistent, isolated, and durable (ACID) operation. An ACID server-side operation needs to be an “all or nothing” or atomic process. This ensures that we avoid situations where we could potentially record the idempotent token and fail to create some resources or, alternatively, create the resources and fail to record the idempotent token.