HTTP Response Status Code for bad data found on the DB

Question

I expect the JSON response returned by one of my Web APIs to contain a minimum set of fields annotated as mandatory by the business.

Which HTTP status code fit better in case some bad data that does not respect the contract is found on the db?

At the moment we're using 500 but we probably need to improve it (also because Varnish put in front of our service translates that 500 into a 503 Service Unavailable).

Example:

{
  "id": "123",
  "message": "500 - Exception during request processing. Cause: subtitles is a required field of class Movie and cannot be empty",
  "_links": {
    "self": {
      "href": "/products/movies/123"
    }
  }
}

Thanks

Answer 1

An 500 Internal Server Error seems to be enough here to be honest as the issue is essentially from the server side and it doesn't reflect any wrong doing from the client side.

Answer 2

400 means BAD request but there is nothing bad with the request so don't use it.

500 means server error as in something unexpected happened - the code imploded, the galaxy crashed. Bad data is not a cause of any of that.

You have a case of bad data so you have a number of approaches at your disposal.

The first question is what do you intend to do with the bad data?

1. You could delete it in which case you would return a 204 which means the request is fine but there is no data to send back. I would not return a 404 for no data because 404 means endpoint doesn't exist, not data does not exist.

2. You could move it out of the main db into a temp data while someone fixes it.

Either way you would not return the bad data and the client doesn't care why that data is bad. All the client is concerned with is there any data or not. Why should the client care that your data is missing a field you deem important?

You could take another approach and return the data you have.

Bottom line : decide how you're dealing with the bad data and don't put any kind of responsibility on the client.