By Lars Pind
This requirements and design document is primarily motivated by:
Use cases:
create table workflow_action_children(
child_id integer
constraint ...
primary key,
action_id integer
constraint ...
not null
constraint ...
references workflow_actions(action_id)
on delete cascade,
child_workflow integer
constraint wf_action_child_wf_fk
references workflows(workflow_id)
);
create table workflow_action_child_role_map(
parent_action_id integer
constraint wf_act_chid_rl_map_prnt_act_fk
references workflow_actions(action_id),
parent_role integer
constraint wf_act_chid_rl_map_prnt_rl_fk
references workflow_roles(role_id),
child_role integer
constraint wf_act_chid_rl_map_chld_rl_fk
references workflow_roles(role_id),
mapping_type char(40)
constraint wf_act_chid_rl_map_type_ck
check (mapping_type in
('per_role','per_member','per_user'))
);
create table workflow_case_enabled_actions(
enabled_action_id integer
constraint wf_case_enbl_act_case_id_pk
primary key,
case_id integer
constraint wf_case_enbl_act_case_id_nn
not null
constraint wf_case_enbl_act_case_id_fk
references workflow_cases(case_id)
on delete cascade,
action_id integer
constraint wf_case_enbl_act_action_id_nn
not null
constraint wf_case_enbl_act_action_id_fk
references workflow_actions(action_id)
on delete cascade,
enabled_state char(40)
constraint wf_case_enbl_act_state_ck
check (enabled_state in ('enabled','running','completed','canceled','refused')),
-- the timestamp when this action automatically fires
fire_timestamp timestamp
constraint wf_case_enbl_act_timeout_nn
not null,
constraint wf_case_ena_act_case_act_un
primary key (case_id, action_id)
);
create table workflow_case_child_cases(
case_id integer
constraint wf_case_child_cases_case_fk
references workflow_cases
constraint wf_case_child_cases_case_pk
primary key,
enabled_action_id integer
constraint wf_case_child_cases_en_act_fk
references workflow_case_enabled_actions
constraint wf_case_child_cases_en_act_nn
not null
);
The enabled_state of rows in workflow_case_enabled_actions can be in one of the following:
When an action with child workflows is enabled, we start the child cases defined by the parent workflow, executing the initial action on each of them.
We create one case per role in workflow_action_children times one case per member/user for roles with a mapping_type of 'per_member'/'per_user'. If more than one role has a mapping_type other than 'per_role', we will create cases for the cartesian product of members/users of those roles in the parent workflow.
The action can be triggered by a timeout, by the user, by child cases reaching a certain state, or by all child cases being completed.
An example of "child cases reaching a certain state" would be the TIP voting process, where 2/3rd Approved votes is enough to determine the outcome, and we don't need the rest to vote anymore.
When triggered, all child cases with a case_state of 'active' are put into the 'canceled' state. All child cases have their 'locked_p' flag set to true, so they cannot be reopened.
----------------------------------------------------------------------
-- Knowledge level
----------------------------------------------------------------------
create table workflows(
);
create table workflow_roles(
);
create table workflow_actions(
action_id integer primary key
...
parent_action_id integer references workflow_actions,
assigned_role integer references workflow_roles
trigger_type char(40)
constraint wf_case_enbl_act_trig_type_ck
check (trigger_type in ('user','auto','message','workflow','parallel','dynamic')),
...
);
create table workflow_fsm_states(
state_id integer primary key
...
parent_action_id integer references workflow_actions
);
----------------------------------------------------------------------
-- Operational level
----------------------------------------------------------------------
create table workflow_case_fsm(
case_id
action_id
state_id
);
create table workflow_case_actions(
case_id integer references workflow_cases
action_id integer references workflow_actions
assigned_p boolean
execution_time timestamptz
);
roles role_id
---------
lawyer
client
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
--------------+------------------+---------------+--------------+---------------
init | | | init | open
ask client | | | workflow | done
ac-init | ask client | | init | ac-asking
ac-ask | ask client | lawyer | user | ac-responding
ac-respond | ask client | client | user | ac-done
abort | | judge | user | done
states state_id | parent_action_id
---------------+------------------
open |
done |
ac-asking | ask client
ac-responding | ask client
ac-done | ask client
state_action_map state_id | action_id
---------------+------------
open | ask client
open | abort
ac-asking | ac-ask
ac-responding | ac-respond
case_state action_id | state_id
------------+---------
| open
ask client | ac-asking
case_enabled_actions ID | action_id | parent_enabled_action_id | assigned_p
----+------------+--------------------------+------------
#1 | ask client | | no
#2 | ac-ask | #1 | yes
#3 | abort | | no
roles role_id
---------
lawyer
client
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
----------------+------------------+---------------+--------------+---------------
init | | | init | open
rev & op | | | parallel | done
review-wr | rev & op | | workflow |
rev-init | review-wr | | init | rev-open
review | review-wr | lawyer | user | rev-done
opinion-wr | rev & op | | workflow |
opi-init | opinion-wr | | init | opi-open
opinion | opinion-wr | lawyer | user | opi-done
states state_id | parent_action_id
---------------+------------------
open |
done |
rev-open | review-wr
rev-done | review-wr
opi-open | opinion-wr
opi-done | opinion-wr
state_action_map state_id | action_id
---------------+------------
open | rev & op
rev-open | review
opi-open | opinion
case_state action_id | state_id
------------+---------
| open
review-wr | rev-open
opinion-wr | opi-open
case_enabled_actions ID | action_id | parent_enabled_action_id | assigned_p
----+--------------+--------------------------+-----------
#1 | rev & op | | no
#2 | review-wr | #1 | no
#3 | review | #2 | yes
#4 | opinion-wr | #1 | no
#5 | opinion | #4 | yes
Difference between parallel sub-actions and non-parallel sub-actions: If they are parallel, we enable all of them and don't maintain state there; if they're not, we look for an init-action, and do maintain state.
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
(workflow) ----------------+------------------+---------------+--------------+---------------
init | | | init | open
ask client | | | workflow | done
ac-init | ask client | | init | ac-asking
ac-ask | ask client | lawyer | user | ac-responding
ac-respond | ask client | client | user | ac-done
abort | | judge | user | done
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
(parallel) ----------------+------------------+---------------+--------------+---------------
init | | | init | open
rev & op | | | parallel | done
review-wr | rev & op | | workflow |
rev-init | review-wr | lawyer | init | rev-open
review | review-wr | lawyer | user | rev-done
opinion-wr | rev & op | | workflow |
opi-init | opinion-wr | lawyer | init | opi-open
opinion | opinion-wr | lawyer | user | opi-done
An action with type 'workflow' will maintain state inside itself.
Can we do away with the extra layer of 'workflow' inside the 'parallel' track? How do we know that the child workflow has been completed -- i guess we do, because we keep the state until its parent is gone...
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
(parallel-simple) ----------------+------------------+---------------+--------------+---------------
init | | | init | open
rev & op | | | parallel | done
review | rev & op | lawyer | user |
opinion | rev & op | lawyer | user |
states state_id | parent_action_id
---------------+------------------
open |
done |
state_action_map state_id | action_id
---------------+------------
open | rev & op
case_enabled_actions ID | action_id | parent_enabled_action_id | assigned_p | completed_p
----+--------------+--------------------------+------------+-------------
#1 | rev & op | | no | no
#2 | review | #1 | yes | no
#3 | opinion | #1 | yes | no
Simple: We'd have to keep the row in case_enabled_actions around with completed_p = yes until the parent action is also complete. When an action is completed, it deletes the rows for all its children. If the action does not have a parent action, we delete the row (thus we don't keep completed_p rows around for top-level actions).
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
(dynamic-simple) ----------------+------------------+---------------+--------------+---------------
init | | | init | open
all-votes | | | dynamic | done
vote | all-votes | voters | user |
states state_id | parent_action_id
---------------+------------------
open |
done |
state_action_map state_id | action_id
---------------+------------
open | all-votes
case_enabled_actions enabled_action_id | action_id | parent_enabled_action_id | assigned_p | completed_p
-------------------+--------------+--------------------------+------------+-------------
#A | all-vote | | no | no
#B | vote | #A | yes | no
#C | vote | #A | yes | no
case_action_assignees enabled_action_id | party_id
-------------------+----------
#B | malte
#C | peter
actions action_id | parent_action_id | assigned_role | trigger_type | new_state
(dynamic-workflow) ----------------+------------------+---------------+--------------+---------------
init | | | init | open
all-votes | | | dynamic | voted
vote | all-vote | | workflow |
init | vote | | init | vote-voting
approve | vote | voters | user | vote-approved
reject | vote | voters | user | vote-rejected
withdraw | | submitter | user | withdrawn
states state_id | parent_action_id
---------------+------------------
open |
vote-voting | vote
vote-approved | vote
vote-rejected | vote
state_action_map state_id | action_id
---------------+------------
open | all-votes
vote-voting | approve
vote-voting | reject
case_state enabled_action_id | state_id
--------------------+---------
| open
#B | vote-voting
#E | vote-voting
case_enabled_actions ID | action_id | parent_enabled_action_id | assigned_p | completed_p | assigned_role
----+--------------+--------------------------+------------+-------------+---------------
#A | all-votes | | no | no |
#B | vote | #A | yes | no |
#C | approve | #B | yes | no |
#D | reject | #B | yes | no |
#E | vote | #A | yes | no |
#F | approve | #E | yes | no |
#G | reject | #E | yes | no |
#H | withdraw | | yes | no | submitter
case_action_assignees enabled_action_id | party_id
-------------------+------------
#B | malte
#D | malte
#F | peter
#G | peter
When a dynamic action is enabled, we create new enabled_action rows for each of the child actions/workflows needed
state_changed_handler
--------------------
- find all actually enabled actions
- chase the action-state-map for all current_states
- add children of enabled actions, where the child has always_enabled_p = true,
or the parent has trigger_type = parallel or dynamic.
- enable all new actions
enable
------
- if trigger_type = workflow, find and execute child action with trigger_type = init.
- if trigger_type = parallel, find subactions and ::enable them
- if trigger_type = dynamic, we'll need code to determine which children to ::enable, and how to create them
execute_state_change
--------------------
- if action.new_state not null
- if there's a state with the same parent_action_id as the action being executed, update that state with new_state
- otherwise insert row with new_state
complete
--------
- if action.parent_action_id not null
- set workflow_case_enabled_actions.completed_p = true
- call child_state_changed_handler -action_id $parent_action_id
- otherwise, delete row in workflow_case_enabled_actions
- delete all child rows
child_state_changed_handler
---------------------------
- trigger_type = workflow/parallel/dynamic:
- if there are no actions with parent_action_id = :action_id, completed_p = false,
then call ::action::execute on the parent action
calculating the actual state-action map (which we can do later, if we want to)
------------------------------------------------------------------------------
- apply the state-action mapping table
- add always-enabled actions with parent_id to the states in which their parent is enabled (or all, if no parent)
- add actions with parent.trigger_type dynamic/parallel to states in which their parent is enabled
always_enabled_p ---------------- - if parent_action_id is null, it means it's always enabled - if parent action is trigger_type workflow, it means it's enabled when its parent workflow is enabled. -> ::enable it after starting the workflow, i.e. executing the initial action -> will it stay enabled? -> it will get disabled automatically by cascading delete when its parent is deleted - if parent action is trigger_type parallel, it has no meaning, all the parent's children will get enabled, anyway - if parent action is trigger_type dynamic, same shit, no semantics How about the state_changed_handler only deals with state change at a particular parent_enabled_action_id level?
If any change to any child workflow of a case attempts to trigger the parent action, the trigger condition would tell us whether to allow the trigger to go through.
The trigger condition could check to see if all child cases are completed, or it could check if there's enough to determine the outcome, e.g. a 2/3 approval.
XXXXXXXXXXXXXXXWe provide a default implementation, which simply checks if the child cases are in the 'complete' state, and if so, fires.
NOTE: What do we do if any of the child cases are canceled? Consider the complete and move on with the parent workflow? Cancel the parent workflow?
NOTE: Should we provide this as internal workflow logic or as a default callback implementation? If we leave this as a service contract with a default implementation, then applications can customize. But would that ever be relevant? Maybe this callback is never needed.
create table workflow_cases(
...
state char(40)
constraint workflow_cases_state_ck
check (state in ('active', 'completed',
'canceled', 'suspended'))
default 'active',
locked_p boolean default 'f',
suspended_until timestamptz,
...
);
Cases can be active, complete, suspended, or canceled.
They start out as active. For FSMs, when they hit a state with
complete_p = t, the case is moved to
'complete'.
Users can choose to cancel or suspend a case. When suspending, they can type in a date, on which the case will spring back to 'active' life.
When a parent worfklow completes an action with a sub-workflow, the child cases that are 'completed' are marked 'closed', and the child cases that are 'active' are marked 'canceled'.
The difference between 'completed' and 'closed' is that completed does not prevent the workflow from continuing (e.g. bug-tracker 'closed' state doesn't mean that it cannot be reopened), whereas a closed case cannot be reactivarted (terminology confusion alert!).
create table workflow_action_fsm_output_map(
action_id integer
not null
references workflow_actions(action_id)
on delete cascade,
output_short_name varchar(100),
new_state integer
references workflow_fsm_states,
constraint ...
primary key (action_id, output_value)
);
Callback: Action.OnFire -> (output): Executed when the action fires. Output can be used to determine the new state of the case (see below).
The callback must enumerate all the values it can possible output (similar construct to GetObjectType operation on other current workflow service contracts), and the callback itself must return one of those possible values.
The workflow engine will then allow the workflow designer to map these possible output values of the callback to new states, in the case of an FSM, or similar relevant state changes for other models.
workflow.Action_OnFire: OnFire -> string GetObjectType -> string GetOutputs -> [string]
GetOutputs returns a list of short_names and pretty_names (possibly localizable, with #...# notation) of possible outputs.
The above table could be merged with the current workflow_fsm_actions table, which only contains one possible new state, with a null output_short_name.
create table workflow_outcomes(
outcome_id integer
constraint ...
primary key,
workflow_id integer
constraint wf_outcomes_wf_fk
references workflows(workflow_id),
short_name varchar(100)
constraint wf_outcomes_short_name_nn
not null,
pretty_name varchar(200)
constraint wf_outcomes_pretty_name_nn
not null
);
create table workflow_fsm_states(
...
-- If this is non-null, it implies that the case has completed with
-- the given output, for use in determining the parent workflow's
-- new state
outcome integer
constraint
references workflow_outcomes(outcome_id),
...
);
An action does not become available until a given list of other actions have completed. The advanced version is that you can also specify for each of these other tasks how many times they must've been executed.
Also, an action can at most be executed a certain number of times.
create table workflow_action_dependencies(
action_id integer
constraint wf_action_dep_action_fk
references workflow_actions(action_id),
dependent_on_action integer
constraint wf_action_dep_dep_action_fk
references workflow_actions(action_id),
min_n integer default 1,
max_n integer,
constraint wf_action_dep_act_dep_pk
primary key (action_id, dependent_on_action)
);
When an action is about to be enabled, and before calling the CanEnableP callback, we check the workflow_case_enabled_actions table to see that the required actions have the required number of rows in the workflow_case_enabled_actions table with enabled_state 'completed'.
The second part, about maximum number of times an action can be executed, this could be solved with a row in the above table with the action being dependent upon it self with the given max_n value.
Action.CanEnableP -> (CanEnabledP): Gets called when an action is about to be enabled, and can be used to prevent the action from actually being enabled.
Is called after all database-driven enable preconditions have been met, i.e. FSM enabled-in-state, and "gated on"-conditions.
This will only get called once per case state change, so if the callback refuses to let the action become enabled, it will not be asked again until the next time an action is executed.
If the callback returns false, the enabled_state of
the row in workflow_case_enabled_actions will be set
to 'refused' (NOTE: Or the row will be deleted?).
Some actions, for example those will child workflows, may not want to allow users to trigger them.
create table workflow_actions( ... user_trigger_p boolean default 't', ... );
If user_trigger_p is false, we do not show the action on any user's task list.
The bug-tracker has resolution codes under the "Resolve" action. It would be useful if these could be customized.
In addition, I saw one other dynamic-workflow product (TrackStudio) on the web, and they have the concept of resolution codes included. That made me realize that this is generally useful.
In general, a resolution code is a way of distinguishing different states, even though those states are identical in terms of the workflow process.
Currently, the code to make these happen is fairly clumsy, what with the "FormatLogTitle" callback which we invented.
create sequence ...
create table workflow_action_resolutions(
resolution_id integer
constraint wf_act_res_pk
primary key,
action_id integer
constraint wf_act_res_action_fk
references workflow_actions(action_id)
on delete cascade,
sort_order integer
constraint wf_act_res_sort_order_nn
not null,
short_name varchar(100)
constraint wf_act_res_short_name_nn
not null,
pretty_name varchar(200)
constraint wf_act_res_pretty_name_nn
not null
);
create index workflow_act_res_act_idx on workflow_action_resolutions(action_id);
create table workflow_action_res_output_map(
action_id integer
not null
references workflow_actions(action_id)
on delete cascade,
acs_sc_impl_id integer
not null
references acs_sc_impls(impl_id)
on delete cascade,
output_value varchar(4000),
resolution_id integer
not null
references workflow_action_resolutions(resolution_id)
on delete cascade,
);
-- FK index on action_id
-- FK index on acs_sc_impl_id
-- FK index on resolution
When someone is assigned to an action, we want the notification email to say "You are now assigned to these tasks".
We'd need to postpone the notifications until we have fully updated the workflow state to reflect the changed state, to determine who should get the normal notifications, and who should get personalized ones.
Notifications doesn't support personalized notifications, but we could use acs-mail/acs-mail-lite to send them out instead, and exclude them from the normal notifications if they have instant notifications set up.
We want to periodically send out email reminders with a list of actions the user is assigned to, asking them to come do something about it. There should be a link to a web page showing all these actions.
For each action we will list the action pretty-name, the name of the case object, the date it was enabled, the deadline, and a link to the action page, where they can do something about it.
Executed when any action in the workflow has been executed, to determine which actions are now enabled.
If the action is enabled:
If the action is not enabled.
Executed when an action which was previously not enabled becomes enabled.
Executed when an action which was previously enabled is no longer enabled, because the workflow's state was changed by some other action.
Executed when an enabled action is triggered.
We execute the OnChildCaseStateChange callback, if any. This gets to determine whether the parent action is now complete and should fire.
We provide a default implementation, which simply checks if the child cases are in the 'complete' state, and if so, fires.
NOTE: What do we do if any of the child cases are canceled? Consider the complete and move on with the parent workflow? Cancel the parent workflow?
NOTE: Should we provide this as internal workflow logic or as a default callback implementation? If we leave this as a service contract with a default implementation, then applications can customize. But would that ever be relevant? Maybe this callback is never needed.
When the action finally fires.
If there's any OnFire callback defined, we execute this.
If the callback has output values defined, we use the mappings
in workflow_action_fsm_output_map to determine which
state to move to.
After firing, we execute the SideEffect callbacks and send off notifications.
DESIGN QUESTION: How do we handle notifications for child cases? We should consider the child case part of the parent in terms of notifications, so when a child action executes, we notify those who have requested notifications on the parent. And when the last child case completes, which will also complete the parent action, we should avoid sending out duplicate notifications. How?
Use cases:
The timer will always be of the form "This action will automatically execute x amount of time after it becomes enabled". If it is later un-enabled (disabled) because another action (e.g. a vote action in the second use casae above) was executed, then the timer will be reset. If the action later becomes enabled, the timer will start anew.
We currently do not have any information on which actions are enabled, and when they're enabled. We will probably need a table, perhaps one just for timed actions, in which a row is created when a timed action is enabled, and the row is deleted again when the state changes.
create table workflow_actions(
...
-- The number of seconds after having become enabled the action
-- will automatically execute
timeout interval
...
);
DESIGN NOTE: The 'interval' datatype is not supported in Oracle.
create table workflow_case_enabled_actions(
case_id integer
constraint wf_case_enbl_act_case_id_nn
not null
constraint wf_case_enbl_act_case_id_fk
references workflow_cases(case_id)
on delete cascade,
action_id integer
constraint wf_case_enbl_act_action_id_nn
not null
constraint wf_case_enbl_act_action_id_fk
references workflow_actions(action_id)
on delete cascade,
-- the timestamp when this action will fires
execution_time timestamptz
constraint wf_case_enbl_act_timeout_nn
not null,
constraint workflow_case_enabled_actions_pk
primary key (case_id, action_id)
);
After executing an action,
workflow::case::action::execute will:
worklfow_case_enabled_actions which are no longer
enabled.workflow_case_enabled_actions, with
fire_timestamp = current_timestamp +
workflow_actions.timeout_seconds .NOTE: We need to keep running, so if another automatic action becomes enabled after this action fires, they'll fire as well.
The sweeper will find rows in
workflow_case_enabled_actions with
fire_timetsamp < current_timestamp, ordered by
fire_timstamp, and execute them.
It should do a query to find the action to fire first, then release the db-handle and execute it. Then do a fresh query to find the next, etc. That way we will handle the situation correctly where the first action firing causes the second action to no longer be enabled.
Every time the sweeper runs, at least one DB query will be made, even if there are no timed actions to be executed.
Possible optimizations: