Jira Data Model: A Comprehensive Guide 101

Jira is a Project Management and Issue-Tracking software developed by Atlassian. It can be used by cross-functional teams for Bug-Tracking, Task Delegation, Milestone-Tracking, Customer-Ticket Tracking, and general Agile Project Management.

It is very popular and used by thousands of teams worldwide.Just like nearly every popular software, Jira allows programmatic access to developers through APIs.

If you are a developer wishing to create automation using Jira or integrate Jira into your application, then you might need to understand the Jira Data Model (the database schema essentially).

Prerequisites

• You need to have familiarity with Jira or any other project management software to understand some of the terms used in this article. In order to follow this article step by step, you need to have Jira server software installed. You can download it from here.

What is Jira?

• Atlassian created Jira as a software application for Bug Tracking and Agile Project Management. Since it supports English, French, German, Japanese, and Spanish, Jira is a popular multilingual tool.
• For many types of testing, it is frequently used as an Issue-Tracking tool. It’s Project Management, Bug Tracking, Issue Tracking, and Workflow Application with incident management capabilities.
• It’s extremely versatile and can be tailored to any workflow. Some of the databases supported by Jira include MySQL, Oracle, PostgreSQL, and SQL.

Key Features of Jira

• Data Easily accessible
• Engaging Workflow
• Agile Project Management

Jira Data Model Development

• As you can see, the qrtz_job_details table in jiradb has an ID field, which corresponds to the JOB field in the qrtz_triggers and the qrtz_job_listeners table. The ID field of qrtz_triggers further corresponds to the trigger_id of qrtz_cron_triggers, qrtz_fired_triggers, qrtz_simple_triggers, and qrtz_trigger_listeners. Now, along with the relations between the tables, you can also see the names and types of all the columns in each table. 

• While the above is a well-rendered depiction of the Jira Data Model, you can also find the Database Schema in the raw format in the WEB-INF/classes/entitydefs/entitymodel.xml file in your Jira application directory. On Windows, the application directory can be generally found in C:Program FilesAtlassianJiraatlassian-jira.
• As you can see from the image above, in this XML as well, the table name, the fields within it, and their types are listed. If a field is a primary key, that is mentioned. Indexes in an entity are mentioned. 
• If you don’t like the text view of the XML file, you can try the grid view on xmlgrid.net and get a well-formed view of all the tables, their fields, primary keys, and indexes. Now, you just need to upload the XML file.

Jira Data Model Design

Now, coming to the tables, there are five major categories of tables (there are tables that don’t fall in either of these 5, but they are generally lesser-used):

1. Users and Groups
2. Issue status and Workflows
3. Issue fields
4. Change history
5. Custom fields

Within the Users and Groups category, the following tables are likely to be frequently dealt with:

1. app_user table: Created at the time of user sign up. Stores the user key and name for each user
2. cwd_user: Contains the details of the users in the current working directory, including name, email, created_at, etc.
3. cwd_group: Details of the group in the current working directory
4. cwd_membership: User group mapping or parent-child mapping for nested groups
5. cwd_directory: Details (like description, type, etc.) of the multiple user directories

Within the Issue status and Workflows category, the following tables are likely to be frequently accessed:

1. jiraissue: All details of an issue (created by, type, summary, assignee, due date, status, etc.). The jiraissue table also has a workflow_id column. This can help identify the workflow step of that issue. 
2. OS_CURRENTSTEP: Shows the current step of an issue in the workflow, based on the workflow_id
3. OS_WFENTRY: This shows the applicable workflow for the issue, based on the workflow_id. 

Under the Issue Fields category, the jiraissue table discussed earlier becomes important as it contains all the information pertaining to an issue. When two issues are linked to each other, the association is defined in the issuelink table, and the link type can be obtained from the issuelinktype table.

The changes to each issue are recorded in the changegroup table. This table records the issue, time of change, and the user who made that change (null for not logged in users). The actual changes (which field was changed, what was the old value, what’s the new value, and so on) are recorded in the changeitem table. 

If you record any custom field for an issue, an entry is made in the customfield table, and an instance of the custom field is stored in the customfieldvalue table. Thus, while the customfield table will store the details of the field, like its type and name, the customfieldvalue table will contain the actual value for that field used in the issue.

In case the custom field has multiple values (i.e., if multi-select is possible with the custom field), there will be multiple rows in the customfieldvalue table for that field. The possible options for a custom field are stored in the customfieldoption table and the default value is stored in the genericconfiguration table. In the genericconfiguration table, because the value needs to be stored for any type of custom field, the value is stored as XML.

Conclusion

You saw understanding the Jira database schema is helpful (i.e. when you are a developer trying to interface with Jira Server, not Jira Cloud).

You also saw how to access the Database Schema using various methods (XML, Atlassian website, and so on). You also saw the important frequently used tables on the Jira database and their broad functions. In case you want to analyze data from Jira into your desired Database/destination, then Hevo Data is the right choice for you! 

Share your experience of learning about the Jira Data Model! Let us know in the comments section below!