As a datapoint, I've been a backend guy for coming up on 25 years now, and not only have I never needed to roll back a database migration, I'm not aware of that ever having needed to be done anywhere, even anecdotally. It just never happens in the reality we live in.
Yet all migration tools support it, require reverse migration scripts, etc. And you listed it in your "must have" list.
About rollbacks: yes, they are not always useful and you can think of ways to avoid them. But these days when you can rollback a docker application upgrade with a few commands/clicks, this way you can also roll back the DB changes. Not all the changes require this, but there are many ways a schema upgrade can go. Datapoint: changing stored procedures/functions that should match the calling application's version. Yes, not everybody uses logic in the DB, but sometimes you need that performance.
Automatic rollbacks strike me as somewhat risky. My thinking is that if the migration (or upgrade) failed it was likely due to something that hadn't been thought about when the migration script was written. In that case it seems unlikely to me that another automated process (the rollback) will be any more successful.