Finite State Machine (FSM) library for PostgreSQL (PL/pgSQL)

Features

[x] User-defined state support: let your user specify their own state machine while still ensuring consistency

[x] Multi-tenant: each row/column from your table can reference a state in another state machine

[x] Historical support: successful state changes are recorded

[x] Complete API

[x] Fully tested

[x] Visual graph generation

Convention

A finite state machine has states, events and transitions.

A state machine can go from one state to another state through a transition. State change is triggered with an event.

A transition is a tuple of a start (previous) state, the event that will trigger the transition and a next state.

An abstract state machine describes a state machine, its (abstract) state and (abstract) transition.

A state machine is an instance of an abstract state machine and points to an abstract state, thus an abstract state machine. A consistent naming convention is essential to build a future-proof project. Use shared SQL convention as well as an SQL linter.

state name

• must be a verb

• verb tense must be either at the simple past (+ed) or at present progressive (+ing)

• lower-case

• snake_case if multiple words (e.g. locked_out)

Examples: opened, loading, loaded, recorded, closed, locked, dumped, shipped, finished, running, failed, entered, enabled, disabled, approved, published, archived, activated, pending, pending_renewal, expired, ordered, canceled, returned, refunded, checked_out

event name

• should be a single word (use snake_case otherwise)

• must be a verb

• verb tense must be infinitive

• lower-case

Examples: start, open, close, lock, unlock, load, unload, dump, ship, fail, enter, enable, disable, run, return, order, cancel, refund, confirm

Usage

Example

-- An abstract state machine will allow us to create state machines instances.
-- Typically, you'll want to store a state machine id in your table column as a foreign key pointing to `lib_fsm.state_machine (state_machine__id)`.
-- In this code block, we'll create an abstract state machine (by declaring its states and possible transitions between these states).
do $$
declare
    abstract_machine_id uuid;
    state_a_id uuid;
    state_b_id uuid;
    state_c_id uuid;
begin
  --                                                    vvv name  vvv description (optional)                                      vvv uuid (optional)                    vvv created_at (optional)
  abstract_machine_id = lib_fsm.abstract_machine_create('status', 'this abstract state machine will allow us to manage a status', 'cee36b4f-f46b-40cb-be6e-a415829eada6', now());

  -- states
  --                                                                                                vvv the initial state (there must be one)
  state_a_id = lib_fsm.abstract_state_create(abstract_machine_id, 'state_a', 'state_a description', true);
  state_b_id = lib_fsm.abstract_state_create(abstract_machine_id, 'state_b', 'state_b description');
  state_c_id = lib_fsm.abstract_state_create(abstract_machine_id, 'state_c', 'state_c description');

  -- transitions
  perform lib_fsm.abstract_transition_create(state_a_id, 'transition_state_a_state_b', state_b_id);
  perform lib_fsm.abstract_transition_create(state_b_id, 'transition_state_b_state_c', state_c_id);
end;
$$;

-- At this point, we have a readily usable abstract state machine and are able to instantiate state machine,
-- we'll do this through a table:

create table public.fsm_example (
  -- will have the instanciated state_machine id    vvv abstract_state_machine id
  status uuid default lib_fsm.state_machine_create ('cee36b4f-f46b-40cb-be6e-a415829eada6') references lib_fsm.state_machine (state_machine__id) on delete cascade
);

-- create a row
insert into public.fsm_example default values;

-- check what we have
select status from public.fsm_example; -- 7c25a444-d16c-469b-9d9f-ad38234b17a8 -> this is our state machine id

-- with it, we can fetch the its current state
select name
  from public.fsm_example
  join lib_fsm.state_machine ON state_machine.state_machine__id = fsm_example.status
  join lib_fsm.abstract_state ON abstract_state.abstract_state__id = state_machine.abstract_state__id; -- -> state_a

-- transition from state_a to state_b
select lib_fsm.state_machine_transition('7c25a444-d16c-469b-9d9f-ad38234b17a8', 'transition_state_a_state_b');

-- transition from state_b to state_c
select lib_fsm.state_machine_transition('7c25a444-d16c-469b-9d9f-ad38234b17a8', 'transition_state_b_state_c');

-- fetch the new current state:
select name
  from public.fsm_example
  join lib_fsm.state_machine ON state_machine.state_machine__id = fsm_example.status
  join lib_fsm.abstract_state ON abstract_state.abstract_state__id = state_machine.abstract_state__id; -- -> state_c

-- get an history of our state machine (an event triggers a transition):
select *
  from lib_fsm.state_machine_event
  where state_machine__id = '7c25a444-d16c-469b-9d9f-ad38234b17a8';
-- +----------+--------------------------------------+--------------------------------------+----------------------------+---------------------+-------------------------------+
-- | event_id | state_machine__id                    | abstract_state__id                   | event                      | abstract_state_name | created_at                    |
-- |----------+--------------------------------------+--------------------------------------+----------------------------+---------------------+-------------------------------|
-- | 5        | 7c25a444-d16c-469b-9d9f-ad38234b17a8 | 63451367-2715-4708-a12a-9510e9d4d5f2 | <null>                     | state_a             | 2024-01-25 17:38:32.769021+01 |
-- | 6        | 7c25a444-d16c-469b-9d9f-ad38234b17a8 | 5eb3cbef-13eb-4011-9f1b-9d03498b1221 | transition_state_a_state_b | state_b             | 2024-01-25 17:38:45.521215+01 |
-- | 7        | 7c25a444-d16c-469b-9d9f-ad38234b17a8 | 6b71bce8-388f-4ef0-bf16-fb22bd50efd9 | transition_state_b_state_c | state_c             | 2024-01-25 17:38:49.15761+01  |
-- +----------+--------------------------------------+--------------------------------------+----------------------------+---------------------+-------------------------------+

Reference

abstract_state_machine:

• abstract_machine_create(name$ varchar(30), description$ text default null, abstract_machine__id$ uuid default public.gen_random_uuid(), created_at$ timestamptz default now()) returns uuid

• abstract_machine_update(abstract_machine__id$ uuid, name$ varchar(30), description$ text default null) returns void

• abstract_machine_delete(abstract_machine__id$ uuid) returns void

state_machine:

• state_machine_create(abstract_state_machineid_or_abstract_stateid$ uuid, state_machine__id$ uuid default public.gen_random_uuid()) returns uuid

• state_machine_belongs_to_abstract_machine(state_machineid$ uuid, abstract_machineid$ uuid) returns boolean

• state_machine_get(state_machine__id$ uuid) returns lib_fsm.state_machine_state

• state_machine_get_mermaid(abstract_state_machine__id$ uuid) returns text

• state_machine_delete(state_machine__id$ uuid) returns void

• state_machine_transition(state_machine__id$ uuid, event$ lib_fsm.event_identifier, dry_run$ boolean default false) returns lib_fsm.state_machine_state

• state_machine_get_next_transitions(state_machine__id$ uuid) returns setof lib_fsm.abstract_state_machine_transitions

abstract_state:

• abstract_state_create(abstract_machineid$ uuid, name$ lib_fsm.abstract_state_identifier, description$ text default null, is_initial$ boolean default false, abstract_stateid$ uuid default public.gen_random_uuid ) returns uuid

abstract_transition:

• abstract_transition_create(from_abstract_stateid$ uuid, event$ lib_fsm.event_identifier, to_abstract_stateid$ uuid, description$ text default null, created_at$ timestamptz default now()) returns void

Design

What was tried before current implementation

Why did they do that?

I would not have done this way

Try #1 Listen to every table column changes

A trigger on every tables that listen to the table state column and that have a custom type like lib_fsm.state_machine to know it must be monitored.

• Cons:

  • Custom types in PostgreSQL requires a C extension

  • C extensions are not supported in PostgreSQL managed environments

Rejected.

Try #2 Composite type

The previous idea but instead of a custom type, we rely on a composite type (last_state, abstract_machine__id).

• Pros:

  • Easier to maintain

  • Does not need column names convention

• Cons:

  • No foreign key on abstract_machine__id (ensure referential integrity with a trigger)

  • No foreign key on abstract_machine__id (ensuring referential integrity with a trigger would require a schema introspection to retrieve all columns of type lib_fsm.state_machine.abstract_machine\__id === old.abstract_machine__id)

Rejected.

Try #3 External table to store every states

Externalize each machine current states to an independent table. Each state is linked to a finite state machine (see abstract state machine).

• Pros:

  • The table schema explicitly states that one of more columns are each linked to their state machine

  • Supports multiple state (e.g. a contract might two columns, a signed_status and a writing_status)

• Cons:

  • Looking at a table, you don’t know the value of the current state (e.g. a contract status attribute). It requires an extra join.

Visual documentation generator

PGPASSWORD=$USER_PASSWORD psql -qtAX  -U $USER --password -c "select lib_fsm.state_machine_get_mermaid('081d831f-8f88-4650-aebe-4360599d4bdc') as mermaid;"

Next steps

• ❏ add support for versioning

• ❏ add support for transition properties

• ❏ add support for transition triggers: 0-N triggers, what events should automatically trigger the transition

• ❏ add support for transition conditions: 0-N (cf: ui-predicate), requires implementing lib_rule_engine first

• ❏ add support for transition pre_conditions: 0-N, these pre-conditions are run before displaying available events from 'from_state' post_actions (0-N, what to do once we switched to to_state) ⇐ WONT_IMPLEMENT

Credits - Previous Work

• 2017 - Implementing State Machines in PostgreSQL

• 2019 - Versioned FSM (Finite-State Machine) with Postgresql

SQL Conventions

Code is written following standard SQL-convention.