Implementing State Machines in PostgreSQL (2017)

While this is a good approach to implements state machines, the transition function is hardwired to one transition graph. Another approach is to generalize the state and transition graph into a table, and group those transitions by a machine id, as shown here:

https://github.com/michelp/pgfsm

Now many machines (sub-graphs of state transitions) can be defined in general, and the transition checking function checks the validity of the next state based on the table, instead of static rules in a function.

TFA says he's using this to

> implement a realtime analytics dashboard for an application with over a billion event rows.

I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE.

There would also need to be some python[2] to provide the PL/SQL logic shown.

[1] https://sqlite.org/windowfunctions.html

[2] https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...