dotConnect for PostgreSQL provides PgSqlTrackingParticipant class that stores TrackingRecord objects in the PostgreSQL database. Tracking participants are the objects that allow tracking the execution of a workflow instances by subscribing for tracking records that are emitted by the workflow runtime. The tracking participants contain the logic to process the tracking records.

To store tracking records to an PostgreSQL database, first you need to create the schema for storing tracking records. dotConnect for PostgreSQL provides the scripts for creating this database. They are placed to the WF Services subfolder of the dotConnect for PostgreSQL installation folder. By default it is %ProgramFiles%\Devart\dotConnect\PostgreSQL\WF Services\. Execute the PgSqlTrackingSchema.sql script first and then execute PgSqlTrackingLogic.sql. You may use these scripts to clean up the persistence database to have a fresh database in the same way, just execute them in the same order.

Configuring InstanceStore with PgSqlTrackingParticipant class

To use PgSqlTrackingParticipant class, perform the following steps:

1. Add the Devart.Data.PostgreSql.WorkflowFoundation assembly to References of your solution.
2. Add the following line to your code: "using Devart.Data.PostgreSql.ActivitiesTracking".
3. Create a TrackingProfile instance.
4. Add tracking queries to the profile.
5. Create an PgSqlTrackingParticipant instance, passing a connection string as the parameter to its constructor
6. Assign the tracking profile to the TrackingProfile property of your PgSqlTrackingParticipant object.
7. Add the tracking participant to Extensions of your WorkflowApplication.

TrackingProfile profile = new TrackingProfile {
  Name = "SampleTrackingProfile",
  ActivityDefinitionId = "*",
};
profile.Queries.Add(new WorkflowInstanceQuery { States = { "*" } });
profile.Queries.Add(new ActivityStateQuery { States = { "*" } });
profile.Queries.Add(new ActivityScheduledQuery());
profile.Queries.Add(new BookmarkResumptionQuery() { Name = "*" });

PgSqlTrackingParticipant trackingParticipant = new PgSqlTrackingParticipant("host=server;database=test;user id=postgres;");
trackingParticipant.TrackingProfile = profile;

WorkflowApplication wfApp = new WorkflowApplication(new Workflow1());
wfApp.Extensions.Add(trackingParticipant);

wfApp.Run();

Dim profile As New TrackingProfile() With { _
	Key .Name = "SampleTrackingProfile", _
	Key .ActivityDefinitionId = "*" _
}
profile.Queries.Add(New WorkflowInstanceQuery() With { _
	Key .States = {"*"} _
})
profile.Queries.Add(New ActivityStateQuery() With { _
	Key .States = {"*"} _
})
profile.Queries.Add(New ActivityScheduledQuery())
profile.Queries.Add(New BookmarkResumptionQuery() With { _
	Key .Name = "*" _
})

Dim trackingParticipant As New PgSqlTrackingParticipant("host=server;database=test;user id=postgres;")
trackingParticipant.TrackingProfile = profile

Dim wfApp As New WorkflowApplication(New Workflow1())
wfApp.Extensions.Add(trackingParticipant)

wfApp.Run()

Configuring Workflow Tracking with Config File

To configure PostgreSQL Tracking by using the postgresqlTracking behavior element in a configuration file, perform the following steps:

1. Add the following behavior extension element to the Machine.config file so that you can use the <postgresqlTracking> service behavior element in the configuration files of your applications to configure persistence for your services:

   <configuration>
       <system.serviceModel>
           <extensions>
               <behaviorExtensions>
                   <add name="postgresqlTracking" type="Devart.Data.PostgreSql.Activities.Configuration.PgSqlTrackingElement, Devart.Data.PostgreSql.WorkflowFoundation, Version=7.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
               </behaviorExtensions>
           </extensions>
       </system.serviceModel>
   </configuration>

   Note: Replace 7.1.26.0 in the code with your actual version.

2. Configure PostgreSQL Tracking by using the postgresqlTracking behavior element in a configuration file of your application:

   <configuration>
       <system.serviceModel>
   	 <tracking>
   	    <profiles> 
         		<trackingProfile name="Sample Tracking Profile">
           	    <workflow activityDefinitionId="*">
             		<workflowInstanceQueries>
               		    <workflowInstanceQuery>
                 			<states>
                   		    <state name="Started"/>
                   		    <state name="Completed"/>
                 			</states>
               		    </workflowInstanceQuery>
             		</workflowInstanceQueries>
           	    </workflow>
         		</trackingProfile>        
       	    </profiles>
     	</tracking>
       <behaviors>  
           <serviceBehaviors>
       	    	<behavior name="">
             	<postgresqlTracking
       	                connectionString="host=server;database=test;user id=postgres;"
                   	    profileName="Sample Tracking Profile" />
       	    	</behavior>
           </serviceBehaviors>
       </behaviors>  
     </system.serviceModel>
   </configuration>

Configuring Workflow Tracking with PgSqlTrackingBehavior Class

To enable tracking using the PgSqlTrackingBehavior class, perform the following steps:

1. Add a reference to the Devart.Data.PostgreSql.WorkflowFoundation.dll.

2. Add the following statement at the top of the source file after the existing "using" statements.

   • C#

   using Devart.Data.PostgreSql.Activities.Description;

   • Visual Basic

   Imports Devart.Data.PostgreSql.Activities.Description
   

3. Create an instance of the WorkflowServiceHost and add endpoints for the workflow service.

   • C#

   WorkflowServiceHost host = new WorkflowServiceHost(new CountingWorkflow(), new Uri(hostBaseAddress));
   host.AddServiceEndpoint("ICountingWorkflow", new BasicHttpBinding(), "");

   • Visual Basic

   Dim host As New WorkflowServiceHost(New CountingWorkflow(), New Uri(hostBaseAddress))
   host.AddServiceEndpoint("ICountingWorkflow", New BasicHttpBinding(), "")
   

4. Construct an PgSqlTrackingBehavior object and to set properties of the behavior object.

   • C#

   ServiceHost svcHost = new ServiceHost(typeof(WorkflowService), new 
                                  Uri("http://localhost:8001/Sample"));
   PgSqlTrackingBehavior trackingBehavior = new PgSqlTrackingBehavior(connectionString){
   	 ProfileName = "Sample Tracking Profile"
   };
   svcHost.Description.Behaviors.Add(trackingBehavior);
   

   • Visual Basic

   Dim svcHost As New ServiceHost(GetType(WorkflowService), New Uri("http://localhost:8001/Sample"))
   Dim trackingBehavior As New PgSqlTrackingBehavior(connectionString) With { _
   	Key .ProfileName = "Sample Tracking Profile" _
   }
   svcHost.Description.Behaviors.Add(trackingBehavior)
   

5. Open the workflow service host.

   • C#

   svcHost.Open();

   • Visual Basic

   svcHost.Open()
   

Workflow Tracking Information

The PgSqlTrackingParticipant class stores the workflow tracking information in the created schema. Its tables are described below:

• wf_tracking_record - contains all tracking records.
• wf_record_type - maps numeric record type values in wf_tracking_record table to the corresponding type names.
• wf_annotation - contains annotations for tracking records.
• wf_instance_record - contains additional information for records of WorkflowInstanceRecord and derived types. This table should be joined with wf_tracking_record table to get all the WorkflowInstanceRecord data.
• wf_activity_info - contains values from ActivityInfo objects that are used in many record types.
• wf_unhandled_exception_record - contains additional information for records of WorkflowInstanceUnhandledExceptionRecord type. This table should be joined with wf_tracking_record and wf_instance_record tables.
• wf_value_collection - contains key/value collections used in tracking record (arguments and variables).
• wf_value - contains keys and values for each collection in wf_value_collection table.
• wf_activity_state_record - contains additional information for records of ActivityStateRecord type. This table should be joined with wf_tracking_record table.
• wf_activity_scheduled_record - contains additional information for records of ActivityScheduledRecord type. This table should be joined with wf_tracking_record table.
• wf_fault_propagation_record - contains additional information for records of FaultPropagationRecord type. This table should be joined with wf_tracking_record table.
• wf_cancel_requested_record - contains additional information for records of CancelRequestedRecord type. This table should be joined with wf_tracking_record table.
• wf_bookmark_resumption_record - contains additional information for records of BookmarkResumptionRecord type. This table should be joined with wf_tracking_record table.
• wf_custom_tracking_record - contains additional information for records of CustomTrackingRecord type. This table should be joined with wf_tracking_record table

Here is the diagram of this schema:

To retrieve the workflow tracking information, execute queries to these tables. For example, to retrieve all the ActivityStateRecord data for today, use the following query.

SELECT * FROM wf_tracking_record TR
 INNER JOIN wf_activity_state_record SR ON TR.RECORD_ID = SR.RECORD_ID
WHERE TR.event_time::date >=  current_date

See Also

PostgreSQL Workflow Instance Store  | Development Tools and Features