The request didn't fail, it simply followed application logic and returned a successful response indicating the nature of the application logic to the client. That is not an error condition, it's just application logic.
If you want to use HTTP as nothing more than a transport layer and return 200 OK's all day then fine, but why are you even using HTTP at that point?
If HTTP delivers my application protocol's message successfully, it's an HTTP 2xx, but my application's protocol may have a range of statuses that are not "transport-like" in the way HTTP status codes are, which are the main things that API consumers care about, assuming the basic transport is working.
If you think about it pretty much 99% of requests the server receives could be correct if the "application logic" was implemented differently. You can't really decouple the two in any meaningful way.
If you want to return 200 OK status codes all day when why are you using HTTP in the first place?
(Also, HTTP is an application protocol)
I can't disagree more. It just seems like clients would have a nightmare of a time debugging client-side code against such a system.
The API consumer doesn't need to think there is a problem, since you might have ways of handling it in a more subtle way. Maybe when your DB connection times out you send an application specific message that includes the number of milliseconds to wait for a retry. You don't have to let the shit hit the fan and return a 5xx any time something slightly exceptional occurs, 5xx is for things that are truly exceptional and for which you have no helpful information for the consumer about what to do.