Building a Custom Workflow Task Audit & Assignment History Trail in Joget

While Joget DX maintains internal engine tables for workflow assignments, enterprise applications often require a clean, user-facing audit history table. This allows business users to view a timeline of every approval step: who was assigned, when the task arrived, when it was completed, and any comments submitted.

In this guide, we'll write a BeanShell Tool script that logs a new audit entry into a custom database table upon task creation and updates the entry when the task completes.

---

Architecture Overview

1. Task Start (Assignment Event): A BeanShell Tool tool step at the beginning of an activity inserts a record into your custom audit table (app_fd_process_audit_trail), recording the process ID, activity name, target assigned users, and creation timestamp.
2. Context Persistence: The generated audit record UUID is passed into a workflow variable (history_uuid) using WorkflowManager.activityVariable().
3. Task Completion: When the user submits the form, a second script uses the history_uuid workflow variable to record the completion timestamp, actual actor, and action outcome.

---

1. Database Table Schema

Create a custom form or table in Joget named process_audit_trail with these fields:

• c_record_id (Text): Process or record ID
• c_process_name (Text): Name of the workflow process
• c_activity_name (Text): Current activity name
• c_assigned_to (Text): Comma-separated usernames or group names
• c_completed_by (Text): User who actually completed the task
• c_status (Text): Status (e.g., Pending, Approved, Rejected)

---

2. BeanShell Script: Logging Task Creation

Place this BeanShell Tool script at the start of your workflow activity:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
import java.util.UUID;
import java.util.Date;
import java.text.SimpleDateFormat;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;
import org.joget.workflow.model.service.WorkflowManager;

// Retrieve assigned user from Hash Variable or App Variable
String assignedUsers = "#beanshell.p_mapping_reviewer#"; 
String processName = "Purchase Approval Process";
String activityName = "Review Request";
String processId = "#process.recordId#";

Connection con = null;
PreparedStatement stmt = null;

try {
    DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
    con = ds.getConnection();

    if (con != null && !con.isClosed()) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String now = sdf.format(new Date());
        String auditId = UUID.randomUUID().toString();

        String sql = "INSERT INTO app_fd_process_audit_trail " +
                     "(id, c_record_id, c_process_name, c_activity_name, dateCreated, c_assigned_to, c_status) " +
                     "VALUES (?, ?, ?, ?, ?, ?, ?)";

        stmt = con.prepareStatement(sql);
        stmt.setString(1, auditId);
        stmt.setString(2, processId);
        stmt.setString(3, processName);
        stmt.setString(4, activityName);
        stmt.setString(5, now);
        stmt.setString(6, assignedUsers);
        stmt.setString(7, "Pending");

        stmt.executeUpdate();
        LogUtil.info("Process Audit", "Logged task audit entry: " + auditId + " for process: " + processId);

        // Save the audit UUID into a workflow variable so completion scripts can reference it
        WorkflowManager wm = (WorkflowManager) pluginManager.getBean("workflowManager");
        wm.activityVariable(workflowAssignment.getActivityId(), "history_uuid", auditId);   
    }

} catch (Exception e) {
    LogUtil.error("Process Audit", e, "Error inserting task audit entry");
} finally {
    if (stmt != null) try { stmt.close(); } catch (Exception e) {}
    if (con != null) try { con.close(); } catch (Exception e) {}
}

---

3. BeanShell Script: Logging Task Completion

Place this BeanShell Tool script at the end of your workflow activity or in the post-processing tool:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

String auditId = "#variable.history_uuid#";
String completedBy = "#currentUser.username#";
String actionStatus = "#form.app_fd_purchase_req.c_decision#"; // e.g., Approved or Rejected

if (auditId != null && !auditId.trim().isEmpty()) {
    Connection con = null;
    PreparedStatement stmt = null;

    try {
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        con = ds.getConnection();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String now = sdf.format(new Date());

        String sql = "UPDATE app_fd_process_audit_trail " +
                     "SET dateModified = ?, c_completed_by = ?, c_status = ? " +
                     "WHERE id = ?";

        stmt = con.prepareStatement(sql);
        stmt.setString(1, now);
        stmt.setString(2, completedBy);
        stmt.setString(3, actionStatus != null ? actionStatus : "Completed");
        stmt.setString(4, auditId);

        int updated = stmt.executeUpdate();
        LogUtil.info("Process Audit", "Updated task completion for audit ID: " + auditId);

    } catch (Exception e) {
        LogUtil.error("Process Audit", e, "Error updating task completion audit");
    } finally {
        if (stmt != null) try { stmt.close(); } catch (Exception e) {}
        if (con != null) try { con.close(); } catch (Exception e) {}
    }
}

---

Key Benefits

• User-Facing Timelines: Display custom approval audit history directly in Userviews using standard DataLists.
• SLA & Bottleneck Reporting: Measure exact duration per activity by comparing dateCreated and dateModified.
• Compliance Ready: Keeps a clean SQL history table independent of internal workflow engine purges.