Decoding Workflow Database Tables: A Deep Dive for AR System Professionals

In the intricate world of IT Service Management (ITSM) and enterprise application development, understanding the underlying database structure is often the key to unlocking true mastery. For those working with platforms like BMC’s AR System (now Helix ITSM), the concept of “workflow” is central to automating business processes. But what exactly happens behind the scenes when an active link fires, a filter triggers, or an escalation kicks in? This article will pull back the curtain and explore the fundamental Workflow Database Tables, shedding light on how your application’s logic is stored and managed.

Whether you’re a seasoned AR System administrator, a developer crafting custom solutions, or an aspiring ITSM professional preparing for interviews, a solid grasp of these database elements is invaluable. It allows for deeper troubleshooting, more efficient development, and a more profound understanding of the system’s capabilities.

Understanding the Foundation: What is Workflow?

Before we dive into the tables, let’s clarify what “workflow” means in the context of AR System. At its core, workflow represents the set of processes and actions that drive your company’s operations. AR System automates these processes through a sophisticated engine that utilizes components like Active Links, Filters, and Escalations. These components are designed to trigger specific actions in response to predefined execution conditions.

Think of it this way:

• Workflow Object: This is the conceptual building block of automation.
• Function: How that object behaves and what it accomplishes.

These workflow objects are core application components of AR System, alongside Forms and Menus. Understanding their database representation is crucial for anyone looking to go beyond the GUI.

From Forms to Tables: The Schema Representation

The AR System defines its application structure using a schema, which directly translates into database tables. When you create a form in AR System, you are essentially defining a blueprint for a database table that will store the data for that form. The system then manages the creation and interaction with these tables behind the scenes.

Let’s break down the key tables associated with workflow components and form data:

Active Links: Reacting to User Interaction

Active Links are arguably the most visible form of workflow to an end-user. They are triggered by user actions within the application, such as clicking a button, changing a field’s value, or opening a form. These actions can range from displaying a simple message to complex data manipulations across different forms.

• Database Table: The core definitions for Active Links are stored in dbo.actlink. When you create an Active Link, the system may create corresponding entries in tables named dbo.actlink_, often appending the action name to further categorize or organize them.
• Client-Side Execution: A critical characteristic of Active Links is their execution on the client, within the current form window. This means they are processed by the AR System User Tool or Mid-Tier, not directly by the server.
• API Limitations: Because they are client-side, Active Links cannot be triggered directly through an API program. They are tied to the user interface’s event model.
• Naming Conventions: To maintain uniqueness, if you attempt to create an Active Link with the same name as an existing one, the system will automatically append _c to the new name.
• Minimum Requirement: Every Active Link definition must include at least one “add action.” This signifies the actual operation the Active Link will perform.
• Form Save Behavior: An important nuance is that Active Links are not copied when you save a form. However, if you rename a form, its associated Active Links will be automatically updated to reflect the new form name.

Filters: The Server-Side Powerhouse

Filters are the workhorses of server-side AR System workflow. They are triggered by interactions or operations occurring at the data/record/transaction level within a form or table. This typically happens when a user attempts to submit, modify, or delete a record.

• Database Table: Similar to Active Links, Filter definitions are managed in tables like dbo.filter and dbo.filter_. These tables store the conditions, actions, and execution order for each filter.
• Execution Context: Unlike Active Links, Filters execute on the server. This makes them ideal for enforcing business rules, data integrity, and complex data updates that should be consistent across all users and access methods.
• API Invocation: Filters can be triggered by API programs, making them a fundamental component for integrating AR System with other applications or automating bulk operations.

Escalations: Time-Based Automation

Escalations are a specialized type of workflow that operates based on a time interval rather than direct user interaction. They are essentially scheduled filters that check for specific conditions in the database at predefined times.

• Database Table: Escalation definitions are stored in the dbo.escalation table. This table holds the schedule, the query that identifies target records, and the actions to be performed.
• Use Cases: Escalations are perfect for tasks like sending overdue notifications for tickets, automatically closing inactive requests, or performing periodic data maintenance.

Tables Created After Saving a Form

When you create and save a form in AR System, the system doesn’t just create one table for your data. Depending on the form’s complexity and the data types used, it dynamically generates several related tables to manage different aspects of the form’s content and history.

• T – Transaction Table: This is the primary table where the actual data entered by users into the form’s fields is stored. Each row in this table typically corresponds to a record (e.g., a ticket, an incident, a request) in your AR System application. The column names usually follow a pattern like C1, C2, etc., corresponding to the field IDs.
• B – Binary Table: This table is used to store binary or large object (BLOB) data. The most common use is for attachments. It also plays a role in storing elements of Active Links, as we’ll see later with the attachment pool.
• H – History Table: This table is crucial for tracking changes to records over time. It specifically stores the status history of a record, allowing you to see how a ticket, for instance, has progressed through different stages (e.g., New, Assigned, In Progress, Resolved, Closed).

Menu Storage: Organizing Navigation

Menus in AR System, which users interact with for navigation and selecting options, are also stored in dedicated database tables. These tables define the structure and content of both static and dynamic menus.

• Database Table: Menu definitions are typically found in the dbo.char_menu table. This table stores information about menu names, types, and the items they contain.

The AR System Installation Sequence: Building the Foundation

When AR System is installed, a specific sequence of database tables is created to establish the core framework for the application. Understanding this sequence can be helpful for troubleshooting installation issues or understanding system dependencies.

1. control: This table likely contains essential system configuration and control parameters for the AR System server.
2. controlRecordIds: This table is probably used to manage unique identifiers for records across different AR System objects and tables, ensuring data integrity and preventing conflicts.
3. arschema: This is a fundamental table. It defines the schemas, which are the blueprints for your forms. It contains metadata about each form, such as its name, ID, and other properties.
4. schema_index: This table stores information about indexes created for various schemas (forms). Indexes are crucial for database performance, allowing the system to quickly locate records based on specific field values.
5. schema_group_ids: This table likely relates to how schemas are grouped or organized, possibly for permissions, licensing, or functional grouping.

Database Essentials

For context, it’s worth noting the typical database and user involved in AR System operations:

• Database: By default, AR System utilizes a database named Arsystem.
• Database User: The default database user for AR System operations is ARAdmin. This user has the necessary privileges to manage the AR System database objects.

Practical Examples and Technical Insights

Let’s explore some practical scenarios and technical details that illuminate how these database tables are used.

Status History in Detail

Tracking the lifecycle of a record is vital in many ITSM processes. The Status History functionality provides this audit trail.

• Viewing Status History: In the AR System User tool, you can typically access the Status History by modifying a ticket, going to the ‘View’ menu, and selecting ‘Status History’. A handy shortcut is to open a form in modify mode and press Ctrl+H.
• Database Reflection: When you update a ticket’s status, this change is recorded not as a new row in a separate history table (as one might initially assume), but rather as an update to a specific row in the transaction table (T table). The system intelligently manages a history of statuses within the primary record’s context, often using specific fields or internal mechanisms within that row. The reference `[ T0 U0, T1 U1,….T4 U4]` likely points to internal AR System references that link to the status changes and their associated data within the primary transaction tables. This is an efficient way to keep history tied directly to the record it describes.

Diary Field Values

Diary fields are often used for free-form notes and comments. Their storage in the database has a specific format.

• Database Format: Diary field entries are stored as a string with a specific delimiter. The format typically looks like: [modified timestamp in seconds -| User -| Data].
• Append Operation: Each time a diary field is modified, the new entry is appended to the existing content in this format. This allows for a chronological log of notes within a single field.

Attachment Pool: Managing Files

Handling file attachments is a common requirement. AR System uses a dedicated mechanism for this, involving specific data types and tables.

• Data Types: The data type for handling attachments is often referred to as attachement pool, and an individual attachment field is of type attachement.
• Database Table Creation: When you use attachment fields, AR System creates two primary tables to manage this data efficiently:
  1. BSchema_id: This is the binary table associated with a specific schema (form). For each attachment added to a record in this form, the system adds three columns to this binary table:
     • C (Content/Path): This column stores the actual path or identifier for where the file’s binary data is located.
     • O (Original Size): Stores the size of the file in its original, uncompressed format (in bytes).
     • C (Compressed Size): Stores the size of the file after compression (in bytes). Note the duplication of ‘C’ here is likely a system internal convention, not necessarily two distinct columns with the same name in typical RDBMS terms, but rather representing different metadata aspects related to the attachment.
  2. Battachpoolid: This table acts as a central repository for the actual binary data of attachments. It typically contains the Request ID (linking it back to the primary record) and the binary data itself. This separation helps manage large binary data efficiently and potentially allows for shared storage or caching.

Menus: Navigation and Data Retrieval

Menus provide the user interface for navigation and selecting options. They can be static or dynamic, with different refresh behaviors impacting performance.

• Menu Types:
  • Static: These menus have fixed content, defined directly in AR System. Examples include character menus (predefined lists), form data dictionaries, and field data dictionaries.
  • Dynamic: These menus retrieve their content based on a query. This can be a Search menu (based on form records) or an SQL menu (executing direct SQL queries).
• File Menus: Menus defined using file-based configurations can be of either static or dynamic types.
• Dependency on Forms/Fields: A key point is that deleting a field or form that is referenced by a menu does NOT automatically delete the menu itself. You’ll need to manually manage and clean up orphaned menus.
• Refresh Rate in Menus: This setting dictates how often the menu’s content is refreshed from its source.
  • On Connect: The menu is retrieved the first time a user opens it after selecting the form. To see updates, the user must re-open the form. This is a good balance between performance and keeping data somewhat fresh.
  • On Open: The menu is retrieved every single time the user opens it. This ensures the most up-to-date data but can significantly slow down performance, especially for dynamic menus with complex queries.
  • On 15 Minute Interval: The menu is retrieved upon the first open, and then again every 15 minutes. For browser-based clients, this behavior often mimics the ‘On Open’ setting, retrieving data more frequently.
  • Character Menus: As static menus, they are not subject to refresh intervals; their content is fixed unless manually updated.

Help Text: User Guidance

Help text provides users with context-sensitive assistance for fields. Its visibility can vary based on the user’s login.

• Login by Demo: When logged in as ‘Demo’ (often an administrative or development user), you’ll see more detailed information: the Field Name (label), Field ID, and the Help Text value.
• Login by Other User: For regular users, only the Help Text value is displayed. If no help text is defined, the system will fall back to showing the field’s description, which might include its symbol, keyword, and length.

Granular Overlays: Customization and Upgrades

Overlays are a powerful AR System feature that allows for customization without directly modifying the original system objects. This is critical for managing upgrades and maintaining a clean codebase. Different overlay types offer varying degrees of control.

• Additive Overlay OR Extensions Overlay: This type of overlay adds customized information to the original object. If the origin object is modified during an upgrade, your additions are generally appended, preserving your customizations.
• Overwrite Overlay: This is the most aggressive type. The entire overlaid object replaces the origin object. The original object’s definition is effectively ignored in favor of your overlay. This can be useful for completely re-engineering a component but carries a higher risk during upgrades if the original object’s structure changes significantly.
• No Overlay OR Inheritance Overlay: This is the default behavior. The overlay simply inherits all properties from the origin object without making any changes. It acts as a placeholder or a way to explicitly state that no customization is being applied to this specific component.

Troubleshooting Common Workflow Database Issues

Understanding the database tables can be a lifesaver when troubleshooting workflow problems. Here are a few common scenarios:

Interview Relevance: What Recruiters Look For

For those aiming to land or advance in AR System roles, demonstrating knowledge of workflow database tables is a significant advantage. Recruiters and hiring managers often probe this area to gauge a candidate’s depth of understanding beyond just using the GUI.

Conclusion

The workflow database tables in AR System are more than just abstract storage mechanisms; they are the very foundation upon which your automated business processes are built. By understanding the roles of tables like dbo.actlink, dbo.filter, dbo.escalation, and the dynamically generated T, B, and H tables, you gain a powerful advantage in managing, troubleshooting, and optimizing your AR System environment.

Whether you’re debugging a stubborn workflow, designing a new automation, or preparing for a critical interview, this knowledge empowers you to look beyond the surface and truly master the intricacies of AR System workflow.