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
    }
    
}

River's blog: Building an idempotent email API with River unique jobs