Custom Workflow Architecture: The Routing Table

!!ROUGH DRAFT!!

note to self...maybe instead of calling this the "Routing Table" it should be something like "Workflow Activity Logging". I'm actually describing how to log user actions that route an item from one step to another in the workflow instance. Hmm...

Introduction
Looking back on just about every web application I’ve ever built, they all have one thing in common - they are all workflow based applications. Every system I’ve designed has the concept of an item to be acted on - be it a web form, a file attachment, or some other set of business data - that requires set of actions to happen in order to "finalize" the item.

Just think of any workflow as a simple flowchart - a start point with one or more steps drawn as boxes, each box connected to another by one or more lines with arrows showing direction (transitions) until you finally reach an end point. In any workflow, there is always a start point, an end point and at least 1 or more steps all connected by transitions.

A Simple Workflow Example
Lets take a look at an example workflow and examine a simple method for tracking workflow activity.

Consider a single approval step workflow. There are only two steps in this workflow and a handful of transitions that can take place. Imagine we have a web form that controls the workflow - the web form allows employees at our company generate vacation requests, and allows managers to view and approve them. A flowchart diagram of our simple workflow might look like this:

Now lets think about how workflow activity will be recorded in a persistent database (in this example, we'll assume an Oracle 10g database). Lets not worry about persisting the business data the moment (data about the vacation request and the user input via web forms), we'll just focus on the workflow and how we will store the routing history of an item - the transitions that took place, when they were performed and by whom.

If we want to be able to look back and see what happened to a specific item processed by our workflow, at a minimum we need to record every action/transition that takes place in the workflow, staring with when the item was created. We'll start with a single table to record all the transitions that take place in our workflow. Keep in mind that the table is oversimplified on purpose, so we dont have to waste time on reference tables and the like.

CREATE TABLE WorkflowRouting (
ID INT NOT NULL,
ItemName VARCHAR2(128) NULL,
NTUsername VARCHAR2(128) NULL,
StepName VARCHAR(128) NULL,
ActionTaken NOT NULL,
CreateDate TIMESTAMP DEFAULT SYSDATE
);

Now lets talk about a simple user case. Bob Smith, an employee at our company, decides to create a vacation request using our workflow application. His manger is Jane Doe, so she will need to approve his request, as per the specifications we've already collected.

Lets assume Bob will use a web form to create his request in our system. We give Bob a link to the web form in order to start the request. We wont get into the details of the code, but lets imagine the first thing we do in the web form is ask bob what kind of request he wants to make - we present him a drop-down list of possible request types to pick from and a Submit button to create the request. Bob picks "Vacation Request" and submits the web form. When Bob clicks "Submit" prepossessing will take place before bob actually enters in any business data about his request. Later, we will use more pre and post processing for each “step” in the workflow in order to track workflow activity. Below is an example of the kind of insert we might expect to see when Bob creates his request (again, oversimplified for ease of reading).

--- Assume that the ID column is populated by a trigger/sequence
INSERT INTO WorkflowRouting (
ItemName,
NTUsername,
StepName,
ActionTaken,
CreateDate
)VALUES(
‘Bob Smith vacation request’,
‘bsmith’,
‘Initial Data Entry and Submission’,
‘Created’,
SYSDATE
);
COMMIT;

So, the first routing record for Bob's request now exists in our database, so the system knows about it even though Bob still hasnt completed entering in his business data or sent the item for approval. Having that initial route record about when the item was created will become very important later when we want to build reports for the system.

Next, Bob finalizes his vacation request and submits it for approval. He submits the request by clicking a button in the web form that he’s been using to enter his vacation request data. The web form commits the business data to the database (which we wont worry about) and then does post processing for that step, committing another record to our WorkflowRouting table as shown in the insert statement below.

--- Assume that the ID column is populated by a trigger/sequence
INSERT INTO WorkflowRouting (
ItemName,
NTUsername,
StepName,
ActionTaken,
CreateDate
)VALUES(
‘Bob Smith vacation request’,
‘bsmith’,
‘Initial Data Entry and Submission’,
‘Submitted for Approval’,
SYSDATE
);
COMMIT;

Note that the StepName is the same as our first route record - This is because we are still on the same step where bob is working on his request, but now we are recording that the action taken was “Submitted for Approval” rather than “Created”, and this record gets a new timestamp. With these two database records for the item “Bob Smith vacation request”, we can now see when the item was created and when it was approved, which allows us to determine how long it took Bob to submit the action for approval.

Now now that bob has submitted the item for approval, our workflow engine (more code we wont get into yet) can use the existing routing records to see that the vacation requester, Bob, completed his step and that the item should now be pending Jane’s approval, since she is his manager. Just assume that somehow the workflow engine knows the item is pending Jane’s approval based on what we've recorded in the routing table so far.

Note: Our routing table only records information about things that have already taken place, so you wont see who the action is pending approval by in this table. That information should be controlled by the workflow engine, which determines how one step flows to the next in the workflow.

Now Jane can start working on the item. She sees that an item is waiting for her approval, so she logs in and clicks on a link to access a web form where she can work on the item. As soon as Jane opens the web form to work on the item, we -could- record that action to the route table. Lets not worry about that yet, just know we could do that if we wanted to, just as we did for when Bob created the item.

Jane completes her review of Bob’s vacation request and she decides to approve it. She clicks a button in the web form, “Approve”, and the web form then writes another record to the database as shown in the insert statement below.

--- Assume that the ID column is populated by a trigger/sequence
INSERT INTO WorkflowRouting (
ItemName,
NTUsername,
StepName,
ActionTaken,
CreateDate
)VALUES(
‘Bob Smith vacation request’,
‘jdoe’,
‘Manager Review’,
‘Approved’,
SYSDATE
);
COMMIT;

We are still working on Bob’s vacation request, as you can see by the ItemName value which is the same as our previous two records. Jane is now performing the action though, so we use her Active Directory username “jdoe” to identify her as the actor in this record. The StepName value also changed because the action is now pending the manager’s approval. Finally, we record that the action that she took was “Approved” - this way we can tell the difference between a manager’s approval and the requester’s item submission for approval.

At this point, we’re actually done with this example. We’ve recorded all the actions that took place in the system that we care about, and now we have enough information to report back what happened to Bob’s vacation request. We could then write some simple SQL queries to give us basic reports about whats happening in our system.

The next post in this series, Custom Workflow Architecture: Workflow Steps and Transitions, looks at a way to define the workflow completely in the database, including all of its steps and transitions, rather than using hardcoded strings.