Hi, thanks for taking the time to explain your ideas.
There are certainly many ways to model this problem in the database. But to me the main idea of the article is implementing a FSM as a user defined aggregate. This is technique is actually not mutually exclusive with an "accumulating snapshot" table, and in fact the trigger could be modified to maintain such a table instead or in addition to the order_events table.
I don't quite understand why you insist on not having a transactions (order_events) table. IMO it's very helpful for auditing purposes, and can be used to capture additional data for each state transition. E.g. you could easily put an IP column on it. From what I can tell you'd have to add one column per event for doing this with your accumulated snapshot table. Our application has ~10 events, so I'm not sure I'd like to have an explosion of columns on it.
Also, I think your solution falls apart completely when it's possible to re-enter a previous state. (or would you overwrite the first timestamp?)
Last but not least, there are many advantages to putting logic into the db. I know you object to the advanced analytics because they can be done differently when you control the DB schema from day one. But imagine you inherit a database with an events table that can't easily be modified to use an accumulating snapshot table. How would you write analytical queries against it? Another advantage of keeping logic in the database is reducing the number of queries (and network overhead) that are needed.
Anyway, you've made many good suggestions, but I think you've taken the toy example a bit too serious. It's very hard to come up with good real world examples without disclosing details of your actual application (which I can't in this case).
Anyway, using FSMs backed by user defined aggregates is definitely not an academic solution. This powers a mission-critical application of one of the worlds big companies for a database with over a billion records. It's also not the first iteration, and previous implementations at the application layer had caused problems that FSMs inside the DB have solved. Last but not least, this modeling the problem domain as FSMs has allowed the entire team (including non-technical people) to deeply and correctly understand how our analytics work.
Given the problem in the article, an accumulating snapshot table is simply the best solution for the reasons I laid out. If your actual system has different requirements in terms of storage and analysis (which apparently it does), I would be happy to hear about those and come up with an updated recommendation.
Along those same lines I'm not insisting you can't have a transaction table. If you need to record more information, like an IP address or user_id with each transition, then it would require a transaction table (although the article mentions none of this).
Honestly, the storage concerns I bring up (that is the schema) are really far less of a concern than the basic idea of storing your transitions in the database. Put simply, you are storing the wrong information. Instead of storing the actual state, you are storing a series of inputs that allows you to derive the state. This is a HUGE mistake. What if you add a new state? Say, [in_transit]. Every one of those 1 billion rows that are already in there will no longer be able to be processed correctly because they won't have the correct order of events.
This entire thing could be one table that stores the [order_id] [state] [timestamp] [is_current], with a stored procedure to update it that takes an "order_id" and "event_name". Instead, you have created a house of cards.
Our actual system has the requirements of an existing DB schema that can't be changed, that's already using an events table, and that needs to be analyzed. I wrote some more about it here [1]. The FSMs are also cyclic, which can't be handled by your approach as I pointed out earlier.
Anyway, coming back to the example from the post, if I change the FSM (e.g. add a state/event) I need to make sure all existing data conforms to it one way or another (by making the change backwards compatible or by migrating the data). But the same problem exists for any other invariant you're trying to enforce/add to your DB (FKs, Checks, etc.). It's just the cost of enforcing an invariant.
I'm also confused at this point how you jump between saying it's okay to have a transactions table and then later backtrace and say it's a huge mistake because it's not storing the actual state. I already pointed out that the two are not mutually exclusive, and that the trigger could maintain an accumulated snapshot in addition to the order_events table. In fact, the trigger could event derive the new state based on the latest value from the accumulating snapshot table which may also address some of your concerns about adding new states.
Also, I haven't created a house of cards. I had the novel idea of for leveraging user defined aggregates as finite state machines and decided to share it with the PostgreSQL community. I couldn't use my companies business domain as an example, so I had to come up with a bogus one that people would understand easily. Now don't get me wrong, I'm totally enjoying the discussion around this bogus example, but I'm not enjoying your unwavering faith in the absolute correctness of your ideas and the HUGE mistakes you claim I'm making. So is is probably my last reply unless you're cool with toning it down a bit.
If you are bound to an existing database schema then there isn't much you can do (depending on how bound you are of course). That's a totally valid reason to choose your current path. I'm not naive to business requirements. Often, systems end up being far from theoretically perfect due to legacy {insert component of stack here}. I get it.
So ruling out a snapshot table (I think you have made clear this is not an option), your best choice would then be a transaction table where your [state] is a Type 2 Slowly Changing Dimension (SCD2) with an indicator field for the current state. I want to be clear here: you should be storing your state, NOT the events (arguments) that can be used to derive your state. I cannot express this enough. Your problem is not unique. You simply cannot be naive enough to think you are the first person to want store the state history of an FSM in a database. This is literally data warehousing 101. The novelty of your solution stems from the fact that you are (dangerously) misunderstanding the problem. If, for some reason, you need to store what [event_name] caused the transition to the [state], just add a field. Simple.
As for invariants. Let's use an example: say you add a "package" event that transitions to an [in_transit] state between [awaiting_shipment] and [shipped] (note here we may also then want to change "ship" to "deliver"). This simple and totally reasonable change would make every order with an event queue: "create", "pay", "ship" result in [error]. If you just stored the [state], then a [shipped] order will remain [shipped] regardless of what series of events brought it there - which would be correct. There would be no need to go back and change historical data - nor would you want to. How would that even be done? Manufacture shipping information and timestamps? There are no invariants in this process (again, I want to reiterate that this is a solved problem). True FSMs (if this is actually modeled as one) only care about the current state anyway and maybe the previous state (see SCD3). What I mean is, future actions are determined using the current state. If the current state becomes [error] for 1 billion orders, that's a problem. A huge problem.
"Absolute correctness" is an interesting term... and I agree that I am probably coming off a bit aggressive in my comments. Look, I'm not trying to be a bully here, and I DO think that the FSM you have created is interesting, unique, and a cool example of how user-defined aggregates can be used. The problem with it is, unfortunately, an extremely common one. If I had a nickle for every time a programmer jumped into the world of databases and came up with a "new" solution to a "never-before-seen" problem... I'd be a wealthy man. Relational databases have been around for a long time and are one of THE MOST studied and written about pieces of infrastructure/software in existence (they're interesting for so many reasons!). What this means is that it's EXTREMELY unlikely you have found a problem that hasn't already been "solved" (of course there can be wiggle room) in a theoretically correct manner. It's almost always just an individual not recognizing (or breaking down) the problem efficiently.
And that's what I am seeing here: a problem that is present and handled in LOTS of databases (I've personally done this dozens of times), but is being approached from a less-than-perfect (albeit unique) angle. I can't say that I'm "absolutely correct"... I don't have the full grasp of your domain and business requirements. I don't know precisely the degree of constraint your schema is imposing. I don't know how well communication between teams and management of your stack is handled (yes, these can be important - you did mention you had problems in these areas). Maybe yours really is the best solution - though I remain unconvinced. But I can say, that given what I know about the problem, the entity relational model, database design, and data warehousing that your solution is simply not sound. It's difficult to put that nicely. I don't mean that your wrong (apparently this is working for you). It's not black and white. Just that if one of my clients came to me with similar requirements, I would follow "the book", because this has already been written.
And if you want a transitions table for your FSM to enforce explicit constraints (I don't recommend this, but it's MUCH better than hiding this data in a function/trigger):
There are certainly many ways to model this problem in the database. But to me the main idea of the article is implementing a FSM as a user defined aggregate. This is technique is actually not mutually exclusive with an "accumulating snapshot" table, and in fact the trigger could be modified to maintain such a table instead or in addition to the order_events table.
I don't quite understand why you insist on not having a transactions (order_events) table. IMO it's very helpful for auditing purposes, and can be used to capture additional data for each state transition. E.g. you could easily put an IP column on it. From what I can tell you'd have to add one column per event for doing this with your accumulated snapshot table. Our application has ~10 events, so I'm not sure I'd like to have an explosion of columns on it.
Also, I think your solution falls apart completely when it's possible to re-enter a previous state. (or would you overwrite the first timestamp?)
Last but not least, there are many advantages to putting logic into the db. I know you object to the advanced analytics because they can be done differently when you control the DB schema from day one. But imagine you inherit a database with an events table that can't easily be modified to use an accumulating snapshot table. How would you write analytical queries against it? Another advantage of keeping logic in the database is reducing the number of queries (and network overhead) that are needed.
Anyway, you've made many good suggestions, but I think you've taken the toy example a bit too serious. It's very hard to come up with good real world examples without disclosing details of your actual application (which I can't in this case).
Anyway, using FSMs backed by user defined aggregates is definitely not an academic solution. This powers a mission-critical application of one of the worlds big companies for a database with over a billion records. It's also not the first iteration, and previous implementations at the application layer had caused problems that FSMs inside the DB have solved. Last but not least, this modeling the problem domain as FSMs has allowed the entire team (including non-technical people) to deeply and correctly understand how our analytics work.