As far as my experience goes, I would let rollbacks deal with app code and leave the DB rollback to devs. 90% of the times we won’t to rollback it, the others it’ll be about reverting the last migration, not to mention there are practices on db schema migrations to mitigate these.
I’m fine with the plan you suggest. If we separate db rollbacks from app rollbacks then, instead of deploying the previous commit we can simply execute the rollback. That should be way faster as it’s just changing the current symlink to point to the previous deploy’s folder. Then, we can focus on what needs to happen at db-level.
I like the idea of knowing the status of each instance but I think it’ll be easier to maintain up-to-date if we implement Display software version once and for all. It’s been already a few times I wished we had it. It’s seriously a 15min thing. Have an endpoint return the metadata we need beyond the release version and aggregate them in an HTML table and there you have it. No human intervention.
BTW I used to use https://capistranorb.com/documentation/getting-started/rollbacks/ back in time. It might be worth checking for reference.