When you install the Herzum Approval add-on into your JIRA instance, the following tables are created in your JIRA database.

The image below shows the HAP Entity Relationship Diagram. 


Following, you can find the description of Approval tables and fields.

Table: AO_D195E9_CONFIG

The Table holds values of parameters used on various administration setup

ColumnTypeDescription 
IDint(11) AI PK 
RESPONSIVE_AWTtinyint(1)

Admissible values for this field are:

  • '1' : Responsive Automatic Transition is enabled
  • '0' : Forced Automatic Transition is enabled
USER_AWTvarchar(255)The system user applied to perform workflow transitioning
CUSTOM_DEFINE_APPROVALtinyint(1)

Admissible values for this field are:

  • '1' : Define Approval Administration->Global Settings is set to Only Selected Projects
  • '0' : Define Approval Administration->Global Settings is set to All (Default)
NOTIFICATION_RECIPIENT_CUSTOMtinyint(1)

Admissible values for this field are:

  • '1' : Custom notification recipients
  • '0' : Default notification recipients (Assignee)
USER_COND_RULEvarchar(255)The system user applied to evaluate JQL query
LABEL_REQUEST_NOTIFYlongtextThe text to be replaced on the 'Request Notify' label
TAB_HISTORY_ENABLEDtinyint(1)

Admissible values for this field are:

  • '1' : Approval History Tab enabled
  • '0' : Approval History Tab disabled (Default)
LABEL_REQUEST_APPROVALSlongtextThe text to be replaced on the 'Request Approvals' label
LOCK_ACTION_ENABLEDtinyint(1)

Admissible values for this field are:

  • '1' : Lock enabled
  • '0' : Lock disabled (Default)
USER_APPROVER_BULKlongtextA list of users allowed to bulk approve instead of any user.
LABEL_APPROVAL_NAMElongtextThe text to be replaced on the 'Approval name' label
LABEL_ACTION_APPROVALlongtextThe text to be replaced on the 'Action' label
LABEL_REQUEST_APPROVALlongtextThe text to be replaced on the 'Request Approval' label
LABEL_DETAILlongtextThe text to be replaced on the 'Detail' label
TAB_COMM_DISABLEDtinyint(1)

Admissible values for this field are:

  • '1' : History logging on Comment Tab enabled (Default)
  • '0' : History logging on Comment Tab disabled
USER_NOTIFY_NACvarchar(255)The system user applied to add the comment notifying the approval request
HIDE_HACTION_AUNOTIFYtinyint(1)

Admissible values for this field are:

  • '1' : Automatic Notification event is hidden from the Approval History
  • '0' : Automatic Notification event is showed on the Approval History(Default)
HIDE_HACTION_SING_NOTIFYtinyint(1)

Admissible values for this field are:

  • '1' : Single Notification event is hidden from the Approval History
  • '0' : Single Notification event is showed on t the Approval History(Default)
HIDE_HACTION_APPROVEDtinyint(1)

Admissible values for this field are:

  • '1' : Approved event is hidden from the Approval History
  • '0' : Approved event is showed on the Approval History(Default)
HIDE_HACTION_PENDINGtinyint(1)

Admissible values for this field are:

  • '1' : Pending event is hidden from the Approval History
  • '0' : Pending event is showed on the Approval History(Default)
HIDE_HACTION_REJECTEDtinyint(1)

Admissible values for this field are:

  • '1' : Rejected event is hidden from the Approval History
  • '0' : Rejected event is showed on the Approval History(Default)
EXPANED_APP_PANELtinyint(1)

Admissible values for this field are:

  • '1' : Automatic Notification event is hidden from the Approval History
  • '0' : Automatic Notification event is showed on the Approval History(Default)
HIDE_HACTION_APP_NOTIFYtinyint(1)

Admissible values for this field are:

  • '1' : Multiple approval notification event is hidden from the Approval History
  • '0' : Multiple approval notification event is showed on the Approval History(Default)
HIDE_HACTION_AUTRANStinyint(1)

Admissible values for this field are:

  • '1' : Automatic transitioning event is hidden from the Approval History
  • '0' : Automatic transitioning notification event is showed on the Approval History(Default)
HIDE_PREQUEST_NOTIFtinyint(1)

Admissible values for this field are:

  • '1' : Request Notification column is hidden from the Approvals section of the issue
  • '0' : Request Notification column is showed on the Approvals section of the issue(Default)
HIDE_PACTIONtinyint(1)

Admissible values for this field are:

  • '1' : Action column is hidden from the Approvals section of the issue
  • '0' : Action column is showed on the Approvals section of the issue(Default)
HIDE_PDETAILtinyint(1)

Admissible values for this field are:

  • '1' : Detail column is hidden from the Approvals section of the issue
  • '0' : Detail column is showed on the Approvals section of the issue(Default)
HIDE_PAPP_NAMEtinyint(1)

Admissible values for this field are:

  • '1' : Approval Name column is hidden from the Approvals section of the issue
  • '0' : Approval Name column is showed on the Approvals section of the issue(Default)
CUSTOM_REQUESTER_APPROVALtinyint(1)

Admissible values for this field are:

  • '1' : Request Approval Notification Administration->Global Settings is set to Only Selected Projects
  • '0' : Request Approval Notification Administration->Global Settings is set to All (Default)
HIDE_HACTION_ABSTAINED tinyint(1)

Admissible values for this field are:

  • '1' : Abstain event is hidden from the Approval History
  • '0' : Abstain event is showed on the Approval History(Default)
ABSTAIN_ACTION_ENABLED tinyint(1)

Admissible values for this field are:

  • '1' : Abstain action is available on the Approvals section of the issue
  • '0' : Abstain action is not available on the Approvals section of the issue (Default)
LARGE_BUTTONS varchar(255)

Admissible values for this field are:

  • None
  • All
  • Only selected users
USER_LARGE_BUTTONS varchar(255)A list of users enabled to display large action buttons.
AWT_CMRA tinyint(1)tinyint(1)

Admissible values for this field are:

  • '1' :  Enable automatic workflow transition based on AWT/MRA/CMRA option is set to ON
  • '0' : Enable automatic workflow transition based on AWT/MRA/CMRA option is set to OFF
    (Default)

Table: AO_D195E9_APPROVAL_PROJECTS

The Table holds the projects selected via the Define Approval Administration screen and via the Request Approval Notification Administration screen.

ColumnTypeDescription 
IDint(11) AI PK The Unique Approval Projects Identifier (Primary Key)
PROJECT_ID bigint(20) The Unique Project Identifier
REQUESTER_TYPEtinyint(1)

Admissible values for this field are:

  • '1' : the issues belonging to the selected project display the buttons allowing to send the emails notifying the approval requests
  • '0' : the issues belonging to the selected project display display the Define Approval button

Table: AO_D195E9_APPROVAL

The Table holds anagraphic data for Approvals

ColumnTypeDescription 
ASSIGNEE_ENABLED tinyint(1)

Admissible values for this field are:

  • '1' : assignee approval is enabled
  • '0' : assignee approval is disabled
CONFIG_ID int(11) 
DESCRIPTIONvarchar(255)The Approval Description
GROUPSvarchar(255)The selected Group for the approval
IDint(11) AI PKThe Unique Approval Identifier (Primary Key)
NAMEvarchar(255)Approval name
PROJECT_LEAD_ENABLEDtinyint(1)    

Admissible values for this field are:

  • '1' : project lead approval is enabled
  • '0' : project lead approval is disabled
REPORTER_ENABLEDtinyint(1)

Admissible values for this field are:

  • '1' : reporter approval is enabled
  • '0' : reporter approval is disabled
ROLESvarchar(255)The selected Project Role for the approval
USERSvarchar(255)The selected User for the approval
USER_PICKERvarchar(255)The selected user picker custom field for the approval
GROUP_PICKERvarchar(255) The selected group picker custom field for the approval
OPERATORvarchar(255)

Admissible values for this field are:
'AND' : the approval must validate all the approval properties
'OR' : the approval must validate at least one of the approval properties

NOT_ASSIGNEEtinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Assignee
  • '0' : Not is unchecked for Assignee
NOT_GROUP_PICKERtinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Group Picker custom field
  • '0' : Not is unchecked for Group Picker custom field
NOT_GROUPStinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Groups
  • '0' : Not is unchecked for Groups
NOT_USER_PICKERtinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Users
  • '0' : Not is unchecked for Users
NOT_LEADtinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Project Lead
  • '0' : Not is unchecked for Project Lead
NOT_REPORTERtinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Reporter
  • '0' : Not is unchecked for Reporter
NOT_USERStinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Users
  • '0' : Not is unchecked for Users
NOT_ROLEStinyint(1)

Admissible values for this field are:

  • '1' : NOT is checked for Project Roles
  • '0' : Not is unchecked for Project Roles

  

Table: AO_D195E9_ISSUE_APPROVAL

The Table holds approvals issue status

ColumnTypeDescription 
APPROVAL_ID int(11) 

The Approval identifier

APPROVAL_STATUSvarchar(255)

Admissible values for this field are:

  • 'APPROVED'
  • 'PENDING'
  • 'REJECTED'
  • Null
APPROVER_USERvarchar(255) The Last user changing approval status
DATEvarchar(255) Date of the last approval status update
HAS_CONFIG_FOR_SINGLE_ISSUEvarchar(255) 

Admissible values for this field are:

  • 'TRUE' : The approval has been configured for the specific issue
  • null
ID int(11) AI PK The Unique Identifier of the Issue Approval(Primary Key).
ISSUE_ID bigint(20) The issue identifier
ISSUE_STATUSvarchar(255)The issue status identifier
NOTIFICATION_DATEvarchar(255)

Admissible values for this field are:

  •  data-time the approval request has been sent
  • Not Sent, if the approval request has not been sent yet
  • Null, if the approval request was created before of this field was managed (versions earlier than 2.8).
SOURCE_STATUS_IDvarchar(255)The configured source issue status identifier
TRANSITION_IDvarchar(255)The transition identifier configured with the Approval
TRANSITION_NAMEvarchar(255)The transition name configured with the Approval
TYPEvarchar(255)

Admissible values for this field are:

  • 'SINGLE ISSUE'
  • 'WORKFLOW
AWTtinyint(1)

Admissible values for this field are:

  • 1 (to allow the automatic workflow transitioning)
  • 0
ARNtinyint(1)

Admissible values for this field are:

  • 1 (to allow the automatic email notification)
  • 0
CUSTOM_APPROVERvarchar(255)User or group selected on a custom field set on the Approval
DEFINE_CUSTOM_APPROVERtinyint(1)

Admissible values for this field are:

  • 1 (the issue has been configured to have as approval a custom field)
  • 0
ERROR_TRANSITtinyint(1)

Admissible values for this field are:

  • 1 (the issue has not been automatically transitioned due to conditions and validators haven't been validated)
  • 0
MRAdouble

Admissible values for this field are:

  • -1 -> MRA is set to All
  • 0 -> MRA -> is set to None
  • decimal -> MRA is set to percentual
  • integer -> MRA is set to fixed number
CONDITIONAL_RULE_IDint(11)The Unique Identifier of the Conditional Rule
COMMENT_NOTIFICATION_DATEvarchar(255)

Admissible values for this field are:

  •  data-time the approval request has been notified via comment
  • Null, if the approval request was created before of this field was managed (versions earlier than 2.11).
RAC tinyint(1)

Admissible values for this field are:

  • 1, the Approval Comment is required
  • 0 , the Approval Comment is not required(Default)

Table: AO_D195E9_APPROVAL_MAPPING_REL

The Table holds correlation between AO_D195E9_APPROVAL

and AO_D195E9_APPROVAL_MAPPING  

ColumnTypeDescription 
APPROVAL_IDint(11) 

The Approval identifier

APPROVAL_MAPPING_IDint(11)The Approval Mapping identifier
IDint(11) AI PKThe unique Approval_mapping_rel identifier

Table: AO_D195E9_APPROVAL_MAPPING

The Table holds Approval Mappings main configuration

ColumnTypeDescription 
CONFIG_ID  int(11) 

 

DESTINATION_STATUS_IDvarchar(255)The destination status identifier
ID int(11) AI PKThe unique Approval Mapping identifier
ISSUE_TYPE_IDvarchar(255)The issue type identifier
PROJECT_ID bigint(20)The project identifier
SOURCE_STATUS_IDvarchar(255)The source status identifier
TRANSITION_IDvarchar(255)The transition identifier
AWTtinyint(1)

Admissible values for this field are:

  • 1 (to allow the automatic workflow transitioning)
  • 0
ARNtinyint(1)

Admissible values for this field are:

  • 1 (to allow the automatic email notification)
  • 0
MRAdouble

Admissible values for this field are:

  • -1 -> MRA is set to All
  • 0 -> MRA -> is set to None
  • decimal -> MRA is set to percentual
  • integer -> MRA is set to fixed number
CONDITIONAL_RULE_IDint(11)The Unique Identifier of the Conditional Rule
IS_LOCKEDtinyint(1)

Admissible values for this field are:

  • 1 (the add-on is still restoring consistence of approvals)
  • 0
IS_DRAFTtinyint(1)

Admissible values for this field are:

  • 1 (the mapping has not be saved jet)
  • 0
IS_DELETEtinyint(1)

Admissible values for this field are:

  • 1 (the required approvals have been removed and the mapping has not be saved jet)
  • 0
LOCKED_DATEvarchar(255)Data/Hours the mapping has been locked
IS_NOT_SINGLEtinyint(1)

Admissible values for this field are:

  • 1 (the mapping has not be saved jet and the create screen is still open)
  • 0
NACtinyint(1)

Admissible values for this field are:

  • -1 -> NAC is set to Enabled
  • 0 -> MRA -> is set to Disable
RACtinyint(1)

Admissible values for this field are:

  • 1, the Approval Comment is required
  • 0 , the Approval Comment is not required(Default)

Table: AO_D195E9_APPROVAL_DEFINERS

The Table holds permissions to manage the Define Approval

ColumnTypeDescription 
DEFINERvarchar(255) 

Approval Define value

IDint(11) AI PK The unique approval definers identifier
ISSUE_TYPE_IDvarchar(255) The issue type identifier
PROJECT_IDbigint(20) The project identifier
TYPEvarchar(255)

Admissible values for this field are:

  • Users
  • Groups
  • Roles
  • Reporter
  • Assignee
  • Project Lead

Table: AO_D195E9_APPROVAL_RECIPIENTS

The Table holds the recipients of approval reply notifications

ColumnTypeDescription 
IDint(11) AI PK The unique approval recipients identifier
RECIPIENTvarchar(255)

Approval recipient value.

When APPROVER TYPE is set to APPROVER, the field can assume the following values:

  • On reached Approval
  • On Rejected
TYPEvarchar(255)

Admissible values for this field are:

  • Users
  • Groups
  • Roles
  • Reporter
  • Assignee
  • Project Lead
  • Approver

Table: AO_D195E9_CUSTOM_MAIL_TEMPLATE

The Table holds the list of custom templates and their main setting

ColumnTypeDescription 

CUSTOM_TEMPLATE_NAME

varchar(255) 

The name of the template

IDint(11) AI PK The unique template identifier
PROJECT_ID bigint(20)The project identifier. It is Null for the customized Default Template, when configured.
CONDITIONAL_RULE_IDint(11)The Unique Identifier of the Conditional Rule
HIDE_CREATEDtinyint(1)

Admissible values for this field are:

  • 1 (the issue creation date is hide)
  • 0 (the issue creation date is shown)
HIDE_ISSUE_TYPEtinyint(1)

Admissible values for this field are:

  • 1 (the issue type is hide)
  • 0 (the issue type is shown)
HIDE_PRIORITYtinyint(1)

Admissible values for this field are:

  • 1 (the issue priority is hide)
  • 0 (the issue priority is shown)
CUSTOM_APPROVAL_INSTRUCTlongtext

Admissible values for this field are:

  • Null (Default Instructions)
  • Text (Customized Instructions)
HIDE_APPROVAL_INSTRUCTtinyint(1)

Admissible values for this field are:

  • 1 (the Approval instructions are hide)
  • 0 (the Approval instructions are shown)
HIDE_ASSIGNEEtinyint(1)

Admissible values for this field are:

  • 1 (the issue assignee is hide)
  • 0 (the issue assignee date is shown)
HIDE_WORKFLOW_TRANStinyint(1)

Admissible values for this field are:

  • 1 (the issue workflow transition is hide)
  • 0 (the issue workflow transition is shown)
HIDE_PROJECTtinyint(1)

Admissible values for this field are:

  • 1 (the issue project is hide)
  • 0 (the issue project is shown)
HIDE_REPORTERtinyint(1)

Admissible values for this field are:

  • 1 (the issue reporter is hide)
  • 0 (the issue reporter is shown)
HIDE_DESCRIPTIONtinyint(1)

Admissible values for this field are:

  • 1 (the issue description is hide)
  • 0 (the issue description is shown)
IS_EMAIL_TEMPLATE_DEFAULTtinyint(1)

Admissible values for this field are:

  • 1 (the template is the customized default template )
  • 0 (the template is a Project's template)

Table: AO_D195E9_FIELDS_TEMPLATE

The Table holds the custom templates configuration

ColumnTypeDescription 

CUSTOM_MAIL_TEMPLATE_ID

int(11)

The Template identifier

FIELD_ID varchar(255) The field identifier
FIELD_NAMEvarchar(255) The field name
IDint(11) AI PK The unique fields_template identifier
SEQUENCEint(11)The order that will be followed to list the fields on the email

Table: AO_D195E9_CONDITIONAL_RULE

The Table holds the conditional rules

ColumnTypeDescription 

CONDITIONAL_NAME

varchar(255) 

The Conditional Rule name

CONDITIONAL_RULElongtext

The Conditional Rule expression

IDint(11) AI PK The Unique Identifier of the Conditional Rule

Table: AO_D195E9_APPROVAL_HISTORY

The Table holds approvals history

ColumnTypeDescription 
ACTIONvarchar(255)

Admissible values for this field are:

  • Approved
  • Pending
  • Rejected
  • Automatic transitioning
  • Automatic notification
  • Single notification
  • Approvals Notification
  • Abstained
APPROVAL_NAMEvarchar(255)Approval denomination
COMMENT

longtext (Mysql)

Clob (Oracle)

The Approver comment on approving/rejecting an approval request
DATEvarchar(255)Action's Data/Hours
DESTINATION_STATUSvarchar(255)The workflow destination status
EXECUTORvarchar(255)The user having performed the action (if applicable)
IDint(11) AI PK The Unique Identifier of the Approval History
ISSUE_IDbigint(20)The issue Identifier
SOURCE_STATUSvarchar(255)The workflow source status
TRANSITION_NAMEvarchar(255)The workflow transition
APPROVAL_DETAILlongtextA text detailing the Approval configuration
APPROVAL COMMENT

longtext (Mysql)

Clob (Oracle)

The Approver comment on approving/rejecting an approval request (starting from v.3.9)

Table: AO_D195E9_APPROVAL_REQUESTERS 

The Table holds permissions to notify Approval Requests

ColumnTypeDescription 
REQUESTERvarchar(255) 

Approval Requester value

IDint(11) AI PK The unique approval definers identifier
ISSUE_TYPE_IDvarchar(255) The issue type identifier
PROJECT_IDbigint(20) The project identifier
TYPEvarchar(255)

Admissible values for this field are:

  • Users
  • Groups
  • Roles
  • Reporter
  • Assignee
  • Project Lead

Table: AO_D195E9_APPROVAL_MRA

The Table holds the Conditional Minimum Required Approval(CMRA)

ColumnTypeDescription 
APPROVAL_MAPPING_ID int(11)

The Approval Mapping identifier

CONDITIONAL_RULE_IDint(11)The Conditional Rule identifier
IDint(11) AI PK The unique approval definers identifier
ISSUE_IDbigint(20)The issueidentifier
MRAdoubleThe MRA value
ORDERint(11)

The order of evaluation

SOURCE_STATUS_ID varchar(255)The workflow source status identifier
TRANSITION_IDvarchar(255)The workflow transition identifier

Table: AO_D195E9_AUTO_TRANSITION 

The Table holds temporary data allowing 1) forcing issue refreshing when the add-on automatically transition an issue, 2) avoiding loops due to recusive automatic transitions

ColumnTypeDescription 

DESTINATION_STATUS_ID bigint(20)

bigint(20)

The workflow destination status identifier

EXECUTION_TIME bigint(20)Execution time expressed in milliseconds
ID int(11) AI PK int(11) AI PK The unique record identifier
ISSUE_ID bigint(20)The issue identifier
FORCE_REFRESH tinyint(1)The boolean discriminating refresh tasks
TRANSITION_EVENT_DISPATCHEDtinyint(1)The boolean discriminating automatic transition tasks