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.
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.
deleted by creator
I guess separation of concerns? One is for the system, one is for display?
A unique Boolean is easier to query than sorting and limiting?
Maybe you don’t ever need the ability to display a history, but you still want the history (instead of just making the transaction_id unique constrained)… so you just drop the sort_order column?
I understand what you are saying.
Maybe that’s just how they went about it. Originally planning on using created_at to do the ordering, then later realising that a customisable sort_order is required so they can make things make sense without having to fudge timestamps.