Understanding Your Jira Schema

This tutorial is about getting familiar with the default Jira schema found in Stitch and understanding the context of these fields to effectively use Jira for your data analytics. A schema is a collection of objects within a database, typically consisting of tables (defined below) and the relationships between those tables.

Note: – The primary key in every table is denoted by 🔑

jira_issues

This table contains information on issues in Jira.

AttributeDescription
🔑Issue ID (id)Unique ID of the issue
selfA RESTful API URL to identify the particular issue. This URL has the key attribute as well
keyUnique JIRA-identified key for the issue.

Structure of the key is <project key>-<issue number>

expandA comma-separated list of the parameters to expand
changelogLog of activities that occur for the issue
descriptionDescription of the issue
attachment*Files attached to this issue
subtasks*Other issues that are linked to this as a parent issue
labels*Group of labels that this issue belongs to
fixversions*Version of the release is this issue completed for
comments*Comments attached to this issue
Creator InfoUser key or name of the original creator of the issue
Status InfoStatus of the issue with name, id and description
SummaryTitle of the issue
VotesNumber of votes on this issue
ReporterUser key of who raised this issue
PriorityHow high of a priority is this issue
Issue TypeWhether this issue is a bug, story, etc.

 

jira_projects

This table contains information about individual projects in Jira.

AttributeDescription
🔑Project ID (id)Unique ID of the project
avatarurlsURL of the avatar image of the project
descriptionDescription of the project
expandA comma-separated list of the parameters to expand.
keyUnique Project acronym titlecard
lead__activeA boolean whether the project lead is active of not
lead__avatarurlsURL of the avatar image of the lead
lead__displaynameDisplay name of the lead
lead__keyUnique username of the lead profile
lead__nameName of the lead
lead__selfRESTful API URL using the key of lead profile
nameName of the project
projecttypekeyKey of the project type
projectkeys*Key of the project
selfRESTful API URL using the key

 

jira_project_categories

This table contains information about the categories assigned to your projects.

AttributeDescription
🔑Project Category ID (id)Unique ID of the project category
selfRESTful API URL using the key
nameName of the project category
descriptionDescription of the project category

 

jira_project_roles

This table contains information about the roles that can be assigned to your projects.

AttributeDescription
🔑Project Role ID (id)Unique ID of the project roles
selfRESTful API URL using the key
nameName of the roles in the project
descriptionDescription of the roles in the project
roles__actors*List of users that members in the roles

 

jira_project_types

The table contains information about the project types.

AttributeDescription
🔑Project Type Key (key)Unique Key of project types
formattedkeyName and key of project type
descriptioni18nkeyProject type for software projects
iconIcon of the project type
colorColor to display in Jira for this project type

 

jira_resolutions

The table contains information about the resolutions in Jira.

AttributeDescription
🔑Resolution ID (id)Unique ID of resolutions
selfRESTful API URL using the key
nameName of the resolution for issues
descriptionDescription of the resolution

 

jira_users

The table contains information about the users in Jira.

AttributeDescription
🔑User Key (key)Unique key of users
activeBoolean if the user is active
avatarurlsURL of the avatar image of user
displaynameDisplay name of the user profile
emailaddressEmail address of the user profile
keyUnique username of the profile
localeLocation of the user profile
nameName of the user profile
selfRESTful API URL using the key
timezoneTimezone of the user profile

 

Foreign Key Joins

A foreign key is a column in a table that establishes an association with another table via shared values. To accomplish this, a foreign key is populated with values of the primary key from the other table. Foreign keys are also typically titled IDs, but prepended with the name of the referenced table. Below are some of the relations you could have when joining the different Jira tables.

Note: – All foreign key relationships are color coordinated based on each primary key in the tables above for easy identification.

Joining jira_issues with jira_users

When you want to link your users to specific issues. Here’s how you do it: jira_issues.Creator Info <-> jira_users.key

Similarly, when you want to link your users that have raised an issue in Jira. Here’s how you do it: jira_issues.Reporter <-> jira_users.key

Joining jira_projects with jira_users

Projects also have list of users that can be selected as leads, and so you join these two tables together. jira_projects.lead_key <-> jira_users.key

Joining jira_projects with jira_project_types

Project Type table has descriptions of the project, which you could link to get the full information for the project. jira_projects.projecttypekey <-> jira_project_types.key

 

Resources:

https://www.stitchdata.com/integrations/jira/

https://www.stitchdata.com/docs/integrations/saas/jira#schema

https://developer.atlassian.com/cloud/jira/platform/rest/#api-api-2-project-type-get

 

Jonathan Kurniawan

About Jonathan Kurniawan

Hi! I'm Jonathan Kurniawan. I have 4 years of experience working as a software engineer at Dolby on various different products. I'm currently pursuing my MBA from Hult International Business School and received my Bachelor in Computer Science from University of New South Wales, Australia. I'm excited to share my knowledge at the Data School by Chartio.