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). That is what you will look at in this article.

Table of Contents

• Prerequisites
• What is Jira?
  • Key Features of Jira
• Jira Data Model Development
• Jira Data Model Design
• Conclusion

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.

Note: For a lot of people, the first introduction to Jira is through its cloud offering. You may be one of them if you have only used Jira on the web through an account on atlassian.com. In the cloud offering, the Atlassian team manages your Jira software, and you really don’t need to use their API or understand the Jira Data Model.

The Jira Data Model understanding and API usage become relevant only if you have Jira installed on your own server (your organization may have opted for this option if they are bound by strict data privacy and sharing rules). Jira will soon be discontinuing its Jira server offering. Indeed, Atlassian has announced the end of support for its server software offering in 2024. You can read in detail about it 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: Jira gathers and organizes all of your company’s historical data. Users can quickly access and obtain data from this website anytime they need it. This application allows users to do a variety of Statistical Studies, including Trend Analysis. It also simplifies the process of creating Constructive Reports on Operational Progress.
• Engaging Workflow: Jira also offers a 360-degree view of your processes, which is really useful. This guarantees that everyone is aware of the procedures on the team and that none are forgotten. Team members can chat, share ideas, attach critical documents, and receive frequent updates on tasks and topics that interest them.
• Agile Project Management: Scrum and Kanban approaches are available on the platform, which is primarily focused on Agile Project Management. It can also keep track of regulatory evidence at various phases of development. Estimation Methods such as Hours, Storey Points, and others are available in Jira Software. This way, you can always be sure you’re working with the latest recent data.

Jira Data Model Development

You can find the Jira Data Model in the Database Schema reference. For example, the Database Schema for Jira 7.9.2 can be found here. 

While this may seem overwhelming at first, it shows you the fields in each table, and how the tables are related to each other, i.e. how you can perform joins on the table and using which fields.  Let’s zoom in a bit.

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.

Alternatively, if you are on Jira 7 or below, you can generate the schema in a PDF or DOT format (with the help of Graphviz). You’ll need the corresponding schema generator plugin though. It can be found here. 

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. 

You can read more about the issue status and the workflows here.

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. You can read in detail about custom fields here.

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! 

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) such as Jira and allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

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