If you build a state machine on top of a relational database you can abstract concurrency problems away from your business logic and allow developers to write safe-by-default code without dealing with concurrency concerns. This post explains how to build a library that offers those protections, and how they work under-the-hood.
Yeah, I’m wondering that too. Also, why would a transition ever be updated? Should a successful transition not be a write-once operation? I guess it boils down to the finer details of the requirements of the application.
I think the most_recent is to power the unique index constraint. This “powers” the whole thing.
Whereas the sort_order is to allow easy sorting, which is just for human readability. You could argue that you can rely on the “created_at” for this.
Considering the examples increment it by 10, I assume this is to allow admins to manually override a sequence or force a data consistency thing or whatever.