Content Management Design >

6: Workflow

The first question to ask when designing a workflow is: How restrictive do we want to be? If a news story typically goes through 4 steps -- writing, editing, layout, and approval -- does every story need to go through these four step? Do these steps need to be completed in a specified order?

The least-restrictive workflow is the one described earlier, which is practically no workflow at all: the revision's state is used to send information to others working on the item, and users are allowed to move between any two states (except for the 'approved' state, which is reserved for editors). When a writer finishes a story, he puts it in the 'written' state and the editor knows it is his turn to look at it. If the designer has completed marking up a story (leaving it in the 'formatted' state) and the writer wants to add a few more lines, he can check it out, putting it in the 'writing' state, make his changes, then check it back in, putting it in the 'written' state so the editor knows to review the changes.

There are a few ways we can add more structure:

1. We could restrict which users are allowed to use which states. Maybe writers should only be able to put content into the 'created', 'writing', and 'written' states, while editors can choose any states.

2. We could allow a person to check out content only when it's at certain points in the workflow. Perhaps a writer should be able to check out a story if the last revision was in the 'created', 'writing', or 'written' states, but not otherwise. Once the editor moved it into 'editing' or beyond, they would no longer be able to check it out. If the writer needed to make a change for some reason, he would ask the editor to change the state back to one of the three states.

3. We can specify a rigid workflow that ensures an item goes through the states in a specific order. A staff member could move an item forward one step or back one step, but not jump steps. If an editor received a story and thought it was perfect and ready to go live, he would quickly push it through the 5 or 6 steps before it reached the 'approved' state, then make it go live.

Flexible workflows

A "flexible" workflow is one where the steps can be completed in any order, and it's possible to move between any two states.

Here are a few flexible workflow configurations that might make sense for a publishing organization, from least restrictive to most restrictive:

1. Any user can choose any state, and move a revision from any state into any other states, except for the 'approved' state, which is available only to editors.

2. Any staff member can check out any story, but the list of states they're allowed to use is determined by their position (writer, editor, designer, etc).

3. #2, plus the rule that staff members can only check out a story if the last revision is in one of their allowed states.

4. A combination of the above: Editors and designers can choose any state, but a writer can check out an item only if the last revision is in 'created', 'writing', or 'written'.

5. Instead of users being attached to departments with a role, and roles being attached to roles, permissions are set per-person. For example, Bob the writer could use 'created', 'writing', 'written', while Tom the designer could use 'created', 'written', 'edited', etc.

The first thing to notice is that the user interface is the same regardless of which design we choose. If the system determines you're allowed to check out a story, you'll get the "check out" button at the top of the item's page. Before the story is checked out to you, you're forced to choose a state from a pull-down menu.

The data model will be different for each of these designs. The design challenge for workflow is the same as permissions -- creating a consistent interface that will look the same regardless of the data model sitting beneath it. And the solution is the same: create a collection of views and query only against those views, never against the underlying tables. To change from one workflow design to another, create the new tables and redefine the views.

For permissions, we needed the system to answer two questions: "Is user X allowed to do Y on item Z", and "Is user X allowed to do Y in department Z". For workflow, we have two questions:

1. Is user X allowed to check out item Y?
2. Is user X allowed to put revision Y into state Z?

The first question will be answered by the view cm_v_items_users_can_checkout, which contains two columns: user_id and item_id. A user will be able to check out the item if a row exists with that user_id and item_id:

select count(*)
from cm_v_items_users_can_checkout
where user_id = :user_id
and item_id = :item_id

The second question will be answered by the view cm_v_allowed_rev_state_usr_map, which contains every allowed combination of revision_id, user_id, and state_id. If the user is checking in a story and we need to generate the selectlist with the states available to this user, the query is just:

select state_id ...
from cm_v_allowed_rev_state_usr_map
where user_id = :user_id
and revision_id = :revision_id

If the system needs to verify this user is allowed to choose a state, we do a count:

select count(*)
from cm_v_allowed_rev_state_usr_map
where user_id = :user_id
and revision_id = :revision_id
and state_key = :state_key

If the count is greater than zero, the user is allowed.

Implementing design #1

Three tables are needed:

-- The list of all states

create table cm_workflow_states (
  state_key             varchar(20) primary key,
  state_name            varchar(100) not null,
  editor_state_p        number(1) default 0 not null 
                        check(editor_state_p in (1, 0)),
  action_state_p        number(1) default 0 not null 
                        check(action_state_p in (1, 0))
);

-- which states are possible for each content type

create table cm_workflow_state_type_map (
  state_key             varchar(20) references cm_workflow_states,
  type_key              varchar(20) references cm_types, 
  primary key(state_key, type_key),
  sort_key              integer not null,
  -- be sure the sort_key is unique amoung states for that 
  -- content type 
  unique(type_key, sort_key)
);

-- Which users are staff members, and which of those are editors

create table cm_department_members (
  user_id               integer references users,
  department_id         integer references cm_departments, 
  primary key(user_id, department_id),
  editor_p              number(1) default 0 not null 
);

To simplify cm_v_allowed_rev_state_usr_map, we create a helper view that returns all possible revision/state_key combinations:

create or replace view cm_v_possible_revision_states as
select 
    r.revision_id,
    i.department_id,
    t.state_key,
    t.sort_key
from cm_revisions r, cm_items i, cm_workflow_state_type_map t
where r.item_id = i.item_id
and i.type_key = t.type_key;

Then cm_v_allowed_rev_state_usr_map is:

create or replace view cm_v_allowed_rev_state_usr_map as
select
    unique r.revision_id,
    s.state_key,
    m.user_id,
    r.sort_key
from cm_v_possible_revision_states r, cm_workflow_states s,
     cm_v_department_members m
where r.state_key = s.state_key
and r.department_id = m.department_id
and (m.editor_p = 1 or s.editor_state_p = 0);

An item can be checked out by anyone in the item's department, so cm_v_items_users_can_checkout is:

create or replace view cm_v_items_users_can_checkout as
select
    unique i.item_id,
    m.user_id
from cm_v_department_members m, cm_items i
where m.department_id = i.department_id;

Implementing design #2

For #2, the list of possible states is determined by the person's position on staff.

First we need a place to store the list of roles and which users fill which roles:

create table cm_staff_roles (
  role_key              varchar(20) primary key,
  role_name             varchar(100) not null
);

create table cm_department_role_user_map (
  department_id         varchar(20) references cm_departments,
  role_key              varchar(20) references cm_staff_roles,
  user_id               integer references users,
  primary key(department_id, role_key, user_id)
);

cm_department_role_user_map replaces cm_department_members in design #1.

We still use cm_workflow_states but drop editor_state_p in favor of a many-to-many mapping table:

    
create table cm_workflow_states (
  state_key             varchar(20) primary key,
  state_name            varchar(100) not null,
  action_state_p        number(1) default 0 not null 
                        check(action_state_p in (1, 0))
);

create table cm_workflow_state_role_map (
  role_key              varchar(20) references cm_staff_roles,
  state_key             varchar(20) references cm_states,
  primary key(role_key, state_key)
);

After these changes, cm_v_allowed_rev_state_usr_map becomes:

create or replace view cm_v_allowed_rev_state_usr_map as
select
    unique r.revision_id,
    r.state_id,
    t.user_id,
    r.sort_key
from cm_v_possible_revision_states r, 
     cm_workflow_state_role_map t,
     cm_department_role_user_map f
where r.state_id = t.state_id
and t.role_key = f.role_key; 
and r.department_id = t.department_id

And cm_v_items_users_can_checkout becomes:

create or replace view cm_v_items_users_can_checkout
select
    unique i.item_id,
    d.user_id
from cm_department_role_user_map d, cm_items r
where d.department_id = r.department_id;

Implementing design #3

#3 is the same as #2 except staff members can only check out an item if the story's last revision is in one of their allowed states. Relative to #2, the only change is in cm_v_items_users_can_checkout:

create or replace view cm_v_items_users_can_checkout
select
    c.item_id,
    m.user_id
from cm_vlast_content c, cm_v_allowed_rev_state_usr_map m
where c.revision_id = m.revision_id
and c.state_id = m.state_id;

Implementing design #4

#4 is similar to #3 except editors and designers are allowed to use all states, while other positions need to have states granted to them. The simplest solution is to create a view that looks exactly like cm_department_role_user_map, but grants all states to editors and designers:

create or replace view cm_v_department_role_user_map as
select 
    role_key,
    state_key
from cm_department_role_user_map
UNION
select 
    r.role_key,
    s.state_key
from cm_workflow_states s, cm_staff_roles r
where r.role_key in ('editor, 'designer');

Then cm_v_department_role_user_map can be dropped in as a replacement for cm_department_role_user_map:

create or replace view cm_v_allowed_rev_state_usr_map as
select
    unique r.revision_id,
    r.state_id,
    t.user_id,
    r.sort_key
from cm_v_possible_revision_states r, 
     cm_workflow_state_role_map t,
     cm_v_department_role_user_map f
where r.state_id = t.state_id
and t.role_key = f.role_key; 
and r.department_id = t.department_id

Implementing design #5

For #5, states are granted directly to the user instead of the role. For example, Bob can use 'created', 'writing', and 'written' in one department, and all states in another, and no states in a third.

We drop cm_department_roles, cm_department_role_user_map, and cm_workflow_state_role_map and replace them all with one map between users, states, and departments:

create table cm_department_state_user_map (
  user_id               integer references users,
  department_id         varchar(20) references cm_departments,
  state_id              varchar(20) references cm_workflow_states,
  primary key(user_id, department_id, state_id)
);

Then cm_v_allowed_rev_state_usr_map becomes:

create or replace view cm_v_allowed_rev_state_usr_map as
select
    unique r.revision_id,
    r.state_key,
    f.user_id,
    r.sort_key
from cm_v_possible_revision_states r, 
     cm_department_state_user_map m
where r.state_key = m.state_key
and r.department_id = m.department_id

Making use of our views

To simplify workflow, two procedures are built on top of these views: cm_verify_state_allowed, which throws an error if a user tries to set a state they're not allowed to use, and cm_verify_checkout_allowed which throws an exception if the user isn't allowed to check the item they're trying to check out:

create or replace procedure cm_verify_state_allowed (
    --
    -- Throw an exception if this user isn't allowed to use this state
    -- 
    p_revision_id           in integer,
    p_user_id               in integer,
    p_state_key             in cm_workflow_states.state_key%type
)
as
    v_allowed_p             integer;
begin

    select count(*) into v_allowed_p
    from cm_v_allowed_rev_state_usr_map
    where revision_id = p_revision_id
    and user_id= p_user_id
    and state_key = p_state_key;

    if v_allowed_p = 0 then
        cm_throw_exception('You''re not allowed to use this state');
    end if;

end cm_verify_state_allowed ;

create or replace procedure cm_verify_checkout_allowed (
    --
    -- Throw an exception if this user isn't allowed to check out 
    -- this item
    -- 
    p_item_id               in integer,
    p_user_id               in integer
)
as
    v_allowed_p             integer;
begin

    select count(*) into v_allowed_p
    from cm_v_items_users_can_checkout
    where item_id = p_item_id
    and user_id = p_user_id;

    if v_allowed_p = 0 then 
        cm_throw_exception('You''re not allowed to checkout this item');
    end if;

end;

cm_item_checkout shows both in action:

create or replace function cm_item_checkout (
    --
    -- Checks out this item to this user and create a new revision
    -- with the requested state. The new revision_id is returned.
    --
    -- If the content is checked out by someone else, or this user 
    -- isn't allowed to check out the content, throw an exception.
    --
    p_item_id               in integer, 
    p_user_id               in integer,
    p_state_key             in cm_workflow_states.state_key%type
) return integer
as
    v_type_key              cm_types.type_key%TYPE;
    -- The new revision created
    v_revision_id           integer;
begin

    cm_verify_checkout_allowed (
        p_item_id => p_item_id,
        p_user_id => p_user_id 
    );

    insert into cm_items_checked_out (
        item_id, user_id, checked_out_date) 
    select p_item_id, p_user_id, sysdate from dual
    where not exists (select 1 
                      from cm_items_checked_out
                      where item_id = p_item_id);

    if sql%rowcount = 0 then
        -- There's already a row in cm_items_checked_out, which means
        -- it's already checked out by someone else

        cm_throw_exception('That content is already checked out by someone else');

    else 

        v_revision_id := cm_revision_create (
            p_item_id   => p_item_id,
            p_user_id   => p_user_id,
            p_state_key => p_state_key
        );

        -- Once the new revision is created, verify that the state
        -- was allowed. If not, this will throw an exception and
        -- roll back the new revision.

        cm_verify_state_allowed (
            p_revision_id => v_revision_id,
            p_user_id     => p_user_id,
            p_state_key   => p_state_key
        );

    end if;

    return v_revision_id;

end cm_item_checkout;

Implementing rigid workflows

A "rigid workflow" is one that requires an item to go through a set of steps in a defined order. You can move to the next or back a step, but you can't jump steps.

In practice, the difference is greater than this. In a flexible workflow, states are used to communicate to others what's happening to the item right now -- that's why we have both 'writing' and 'written'. In a rigid workflow, states are more like checkoff points -- the writer checks off the story as being written, the editor checks off the story as being edited, and the designer says the story has been marked up. If the editor receives the story, either after it's been written or marked up, and isn't happy with the result, he backs it up a step, essentially saying "No, this isn't done being written", or "No, this html doesn't look good enough." The writer or editor makes changes and pushes it forward again.

This change in behavior means the user interface is different. We don't ask for state information when a user checks out or checks in a revision. Since you only want to promote an item to the next step once you're done making changes, the option to move a revision to the next step should be available only when the item isn't checked out. If a writer creates a story, then finishes the writing and checks it in, his interface will show:

Current state is assigned

Change: [ forward to written ]

When the writer moves the story forward to the 'written', the editor will see:

Current state is written

Change: [ forward to edited | back to assigned ]

Since states are now functioning like check-off points, we drop the "action states" -- 'writing', 'editing', etc. Every step must be reached, but since there won't always be the need for an editor to make changes, and we don't, for example, want to force a revision through the 'editing' state unless editing is actually taking place. The writer leaves the story in the 'written' state, and the editor moves it to 'edited' once all his changes have been made.

Even though a rigid workflow functions very differently, the abstractions used above still work here. The only difference is that the list of possible states is based on the current state; the staff member has the option of moving it back a step, forward a step, or leaving it where it is. To make any of data models above work with a rigid workflow, only cm_v_possible_revision_states needs to be modified:

-- A helper view to make the next view more understandable
-- For a given state and give type, return (1) the current
-- state, (2) the next state, and (3) the previous state. sort_key
-- is offset so that the previous state has a sort_key of -1,
-- the current state has a sort_key of 0, and the next state
-- has a sort_key of 1.

create or replace view cm_v_possible_state_trans as
select
    t1.type_id,
    t1.state_id,
    t2.state_id as next_state_id,
    t2.sort_key - t1.sort_key as next_sort_key
from cm_workflow_state_type_map t1, cm_workflow_state_type_map t2
where t1.type_id = t2.type_id
and t2.sort_key in (t1.sort_key, t1.sort_key - 1, t1.sort_key + 1);

create or replace view cm_v_possible_revision_states as
select 
    r.revision_id,
    i.department_id,
    s.next_state_id as state_id,
    s.next_sort_key as sort_key
from cm_revisions r, cm_items i, cm_v_possible_state_trans s
where r.item_id = i.item_id
and i.type_id = s.type_id
and r.state_id = s.state_id;

Next: Writing plug-ins to handle custom content types

---

Send questions/comments/errors to dvr@dvrodriguez.com