Schema for Jira family of products

We're updating our terminology in Jira

“Work” is the new collective term for all items tracked in Jira. As we roll out these changes, you may still see the term “issue” in some areas.

Tables for project data

There are several tables for Jira project data:

Project
Project component
Project fix version

Project

Projects in the trash may take up to a week to stop showing in the Data Lake.

This table also has a corresponding live table. Read more about live tables.

The jira_project table lists your Jira projects.

The table uses the following columns:

Assignee type*

The default work item assignee setting for the project.

SQL name	`assignee_type`
Data type	String
Example values	PROJECT_LEAD, UNASSIGNED

Created at

The date and time when the project was created.

SQL name	`created_at`
Data type	Datetime

Description*

The description of the project.

SQL name	`description`
Data type	String

Lead account ID

The unique identifier of the person defined as the project lead.

SQL name	`lead_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Name*

The name of the project.

SQL name	`name`
Data type	String

Project category*

The project category that this project is assigned to.

SQL name	`project_category`
Data type	String

Project ID

The unique identifier of the project. Use this as the primary key for project data.

SQL name	`project_id`
Data type	String

Project key*

The unique key for the project.

SQL name	`project_key`
Data type	String

Project reference*

The identifier of the project within a Jira site.

It can be used to associate this record with the project identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name	`project_ref`
Data type	Number

Project type*

The type of the project. Learn more about project types.

SQL name	`project_type`
Data type	String
Example values	business = Jira Work Management software = Jira service_desk = Jira Service Management

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`max_row_refreshed_at`
Data type	Datetime

Status

The status of the project.

SQL name	`status`
Data type	String
Example values	active - Default state for all projects that haven’t been archived or put in the trash archived - The project was archived and inactive. More about archiving projects. deleted - The project is in the trash, where it has 60 days before it’s permanently deleted. Jira admins can restore the dashboard anytime within those 60 days.

Updated at

The date and time (UTC time zone) when the project was last updated.

SQL name	`updated_at`
Data type	Datetime

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Project component

The jira_project_component table lists the components defined for each project.

The table uses the following columns:

Assignee type*

The default work item assignee setting for the project.

SQL name	`assignee_type`
Data type	String
Example values	PROJECT_DEFAULT, UNASSIGNED

Component ID

The unique identifier of the project component. Use this as the primary key for component data.

SQL name	`component_id`
Data type	String

Component reference*

The identifier of the component within a Jira site.

It can be used to associate this record with the component identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name	`component_ref`
Data type	String

Description*

The description of the project component.

SQL name	`description`
Data type	String

Lead account ID

The unique identifier of the person defined as the component lead.

SQL name	`lead_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Name*

The name of the project component.

SQL name	`name`
Data type	String

Project ID

The unique identifier of the project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `jira_project` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Project fix version

The jira_project_fix_version table lists the versions defined for each project.

The table uses the following columns:

Description*

The description of the project version.

SQL name	`description`
Data type	String

Name*

The name of the project version.

SQL name	`name`
Data type	String

Project ID

The unique identifier of the project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `jira_project` table.

Release date

The release date of the project version.

SQL name	`release_date`
Data type	Date

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Sort order*

The sequence order of versions, matching their order of appearance in the Jira product.

SQL name	`sort_order`
Data type	Number

Start date

The start date of the project version.

SQL name	`start_date`
Data type	Date

Status*

The current status of the project version.

SQL name	`status`
Data type	String

Version ID

The unique identifier of the project version. Use this as the primary key for version data.

SQL name	`version_id`
Data type	String

Version reference*

The identifier of the project version with a Jira site.

It can be used to associate this record with the version identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name	`version_ref`
Data type	String

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Tables for work item data

There are several tables for Jira work item data:

Work item
Work item affected version mapping
Work item cycle time
Work item custom fields
Work item group mapping
Work item field
Work item fix version mapping
Work item history
Work item link
Work item status history
Work item work log

Work item

This table also has a corresponding live table. Read more about live tables.

The jira_issue lists your Jira work items.

The table uses the following columns:

Assignee account ID*

The unique identifier of the person assigned to the work item.

SQL name	`assignee_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Created at

The date and time (UTC time zone) when the work item was created.

SQL name	`created_at`
Data type	Datetime

Creator account ID*

The unique identifier of the person who created the work item.

SQL name	`creator_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Description*

The description of the work item.

SQL name	`description`
Data type	String

Due date*

The due date (UTC time zone) of the work item.

SQL name	`due_date`
Data type	Date

Environment*

A short description of the environment in which the work item occurred.

SQL name	`environment`
Data type	String
Example value	linux

Is archived*

Indicates whether or not the work item is currently archived.

SQL name	`is_archived`
Data type	Boolean

Work item ID

The unique identifier of the work item. Use this as the primary key for work item data.

SQL name	`issue_id`
Data type	String

Work item key*

The number of the work item within the project.

SQL name	`issue_key`
Data type	String
Example value	TEST-12

Work item reference*

The identifier of the work item within a Jira site.

It can be used to associate this record with the work item identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name	`issue_ref`
Data type	String

Work type*

The type of work item.

SQL name	`issue_type`
Data type	String
Example values	Task, Story

Work type hierarchy level*

The hierarchy level of the work type. More about work type hierarchy.

SQL name	`issue_type_hierarchy_level`
Data type	Number
Example values	1 (Epic), 0 (Story), -1 (Sub-task)

Work type hierarchy name*

The hierarchy name of the work type.

SQL name	`issue_type_hierarchy_name`
Data type	String
Example values	Epic, Story, Subtask

Labels*

An array of label names added to the work item.

SQL name	`labels`
Data type	Array of strings

Original time estimate (seconds)*

This column requires time-tracking enabled.

The original time estimate (in seconds) that was set.

SQL name	`original_time_estimate_secs`
Data type	Number

Parent work item ID

The unique identifier of the immediate parent of the work item, if the work item uses the “Parent” field.

SQL name	`parent_issue_id`
Data type	String

Priority*

The current work item priority.

SQL name	`priority`
Data type	String

Priority sort order

The sequence order of the priority, matching its order of appearance in the Jira product.

SQL name	`priority_sort_order`
Data type	Number

Project ID

The unique identifier of the project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `jira_project` table.

Reporter account ID*

The unique identifier of the person who reported the work item.

SQL name	`reporter_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Resolution*

The current work item resolution.

SQL name	`resolution`
Data type	String
Example values	Done, Won’t do, Duplicate

Resolution at

The date and time (UTC time zone) when the work item was resolved.

SQL name	`resolution_at`
Data type	Datetime

Resolution sort order

The sequence order of the resolution, matching its order of appearance in the Jira product.

SQL name	`resolution_sort_order`
Data type	Number

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`max_row_refreshed_at`
Data type	Datetime

Status*

The current work item status.

SQL name	`status`
Data type	String
Example value	Code review

Status category*

The status category of the current work item status.

SQL name	`status_category`
Data type	String
Example values	To do, In progress, Done

Status sort order

The sequence order of the status, matching its order of appearance in the Jira product.

SQL name	`status_sort_order`
Data type	Number

Story points*

Only holds data for work items in team-managed projects. To get story points for work items in company-managed projects, use the “Work item field” table.

The number of story points assigned to the work item.

This field is populated with either the value in the “Story point estimate” field or the “Story Points” field (as long as the default configuration is unchanged), but it will prioritize the value in the “Story point estimate” field if both fields are used on the same work item.

SQL name	`story_points`
Data type	Number

Summary*

The summary of the work item.

SQL name	`summary`
Data type	String

Team ID

The unique identifier of the team associated with the work item.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `atlassian_team` table in the schema for organization data.

Time estimate (seconds)*

This column requires time-tracking enabled.

The estimated time (in seconds) remaining from the original estimate.

SQL name	`time_estimate_secs`
Data type	Number

Time spent (seconds)*

This column requires time-tracking enabled.

The amount of logged work (in seconds). The field is NULL if there is no time spent.

SQL name	`time_spent_secs`
Data type	Number

Updated at

The date and time (UTC time zone) when the work item was last updated.

SQL name	`updated_at`
Data type	Datetime

URL*

The URL of the work item.

SQL name	`url`
Data type	String

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item affected version mapping

The jira_issue_affected_version_mapping table holds associations between work items and project versions in which a bug or problem was found.

The table uses the following columns:

Work item ID

The unique identifier of a work item.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Version ID

The unique identifier of a project version, which is the fix version of the work item.

SQL name	`version_id`
Data type	String
Foreign key	Links to a record in the `jira_project_fix_version` table.

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item cycle time

The jira_issue_cycle_time table contains data derived from the “Work item status history” table.

You can use it to calculate flow metrics based on work item status transitions. The start of work is defined as the first transition to a status assigned the In progress status category, and the end of work is defined as the latest transition to a status assigned the Done status category.

If the work item is currently in a status of the Done status category, the elapsed time will be available in the cycle_time_secs column. That value will be NULL if the end of work precedes the start of work, to avoid capturing negative cycle times).

The table uses the following columns:

Cycle time (seconds)

The overall time elapsed (in seconds) from the start of work to the end of work, indicating the overall work item cycle time.

If the recorded work_ended_at is before work_started_at, the value will be NULL.

SQL name	`cycle_time_secs`
Data type	Number

Work item ID

The unique identifier of the work item.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Work ended at

The latest date and time (UTC time zone) in which the work item transitioned into a status in the Done status category, denoting the time work on the work item completed.

SQL name	`work_ended_at`
Data type	Datetime

Work started at

The earliest date and time (UTC time zone) in which the work item transitioned into a status in the In progress status category, denoting the time work on the work item started.

SQL name	`work_started_at`
Data type	Datetime

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Issue custom fields

You can only use this table in visual mode queries, and it’s not available for controls created without the control editor.

This table is derived from “Work item field” table and displays all your custom fields as regular columns that you can add to your query.

If you need other attributes for your custom fields, you’ll need to use the “Work item field” table.

Work item group mapping

The jira_issue_group_mapping table holds associations between work item and groups.

The table uses the following columns:

Field ID

The unique identifier of the work item field where the group was chosen.

SQL name	`field_id`
Data type	String
Foreign key	Links to a record in the `jira_issue_field` table.

Group ID

The unique identifier of the group associated with the work item.

SQL name	`group_id`
Data type	String
Foreign key	Links to a record in the `atlassian_group` table in the schema for organization data.

Work item ID

The unique identifier of the work item.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item field

This table also has a corresponding live table. Read more about live tables.

The jira_issue_field lists the contents of Jira fields.

The table uses the following columns:

Field ID

The unique identifier of the Jira field. Use it together with the issue_id column in this table as the primary key for work item field data.

SQL name	`field_id`
Data type	String

Field key*

The key of a Jira field.

The key can hold a different value to the ID when it is defined in apps.

SQL name	`field_key`
Data type	String

Field reference

The identifier of the field within a Jira site.

It can be used to associate this record with the field identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.

SQL name	`field_ref`
Data type	String

Field type*

The data type stored in the field.

SQL name	`field_type`
Data type	String

Work item ID

The unique identifier of the work item. Use it together with the field_id column in this table as the primary key for work item field data.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Name*

The name of the field.

If a field contains multiple values, there will be a record for each value in the field.

If a field contains cascading values, there will be a record for the parent value and a record for each child value. To get the parent value, append (parent) to the field name. To get the child values, append (child) to the field name.

For example, let’s say you have a Jira field named “Location”. For the field’s value, you must first select a country then select a region. The country is stored in the parent value, and the region is stored in the child value. To query both from the Data Lake, you’d filter for the field names Location (parent) and Location (child) to get the country and region values, respectively.

SQL name	`name`
Data type	String

Value*

The contents of the Jira field for this work item. The type of data represented by this string will depend on the field.

SQL name	`value`
Data type	String

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item fix version mapping

The jira_issue_fix_version_mapping table holds associations between work items and project versions that are fix versions for a work item.

The table uses the following columns:

Work item ID

The unique identifier of a work item.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Version ID

The unique identifier of a project version, which is the fix version of the work item.

SQL name	`version_id`
Data type	String
Foreign key	Links to a record in the `jira_project_fix_version` table.

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item history

The jira_issue_history table tracks changes in work item data over time. Unlike the History tab in work item details, this table includes the initial value allocated to a field on issue creation.

Sometimes, several updates are applied to the work item at the same time. These updates will share the same changelog_id value. For each of these groups of changes, there will be a row for each field that was updated.

The table uses the following columns:

Author account ID*

The unique identifier of the person who applied the changes to the work item.

SQL name	`author_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Changelog ID

The unique identifier for a group of updates to a work item.

SQL name	`changelog_id`
Data type	String

Ended at

The date and time (UTC time zone) when the new field value was replaced by another.

This is the time where the change captured in this record was displaced by a subsequent change. It is NULL if there was no subsequent change.

SQL name	`ended_at`
Data type	Datetime

Field*

The name of the field that was updated.

SQL name	`field`
Data type	String

Field ID

The unique identifier of the field that was updated.

SQL name	`field_id`
Data type	String
Foreign key	Links to a record in the `jira_issue_field` table.

Field key*

The key of the field.

SQL name	`field_key`
Data type	String

Field name*

The current name of the field that was updated.

It can be NULL if the field was removed since the history record was created.

SQL name	`field_name`
Data type	String

Field type*

The type of the field that was updated.

It can be NULL if the field was removed since the history record was created.

SQL name	`field_type`
Data type	String

Work item ID

The unique identifier of the work item that was updated.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Previous value*

The value of the field before the change.

SQL name	`prev_value`
Data type	String

Previous value (string)*

A string representing the value of the field before the change.

SQL name	`prev_value_string`
Data type	String

Started at*

The date and time (UTC time zone) when the new field value became effective.

This is the time of the change captured by this record.

SQL name	`started_at`
Data type	Datetime

Time in value (seconds)

The time interval (in seconds) between the start and end time of this field value.

It is NULL if there was no subsequent change in the field (in other words, the new field value is the current one).

SQL name	`time_in_value_secs`
Data type	Number

Value*

The value of the field after the change.

SQL name	`value`
Data type	String

Value (string)*

A string representing the value of the field after the change.

SQL name	`value_string`
Data type	String

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item link

The jira_issue_link table represents a link between two Jira work items. All work items come in pairs of rows, one for an inward relationship and another for an outward relationship.

Examples:

Outward relationship: ABC-1 blocks ABC-2
Inward relationship: ABC-2 is blocked by ABC-1

The table uses the following columns:

Work item ID

The unique identifier of the work item that the link relates from.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Work item link type ID

The unique identifier of the type of work item link the relationship is.

SQL name	`issue_link_type_id`
Data type	String

Link direction*

The direction the relationship flows.

SQL name	`link_direction`
Data type	String
Example values	inward outward

Link ID

The unique identifier of the work item link. Use this as the primary key for work item link data.

SQL name	`link_id`
Data type	String

Link name*

The name of the type of work item link the relationship is.

SQL name	`link_name`
Data type	String
Example value	Blocks

Link relationship*

The description of the relationship.

SQL name	`link_relationship`
Data type	String
Example values	blocks is blocked by

Linked work item ID

The unique identifier of the work item that the link relates to.

SQL name	`linked_issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item status history

This table also has a corresponding live table. Read more about live tables.

The jira_issue_status_history table tracks changes in work item status over time. Unlike the History tab in work item details, this tables also includes the initial status on work item creation. There is a row for each work item transition.

The table uses the following columns:

Author account ID*

The unique identifier of the person who set or changed the work item status.

SQL name	`author_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Ended at

The date and time (UTC time zone) when the new work item status was replaced by another.

It is NULL if there was no subsequent work item transition.

SQL name	`ended_at`
Data type	Datetime

Work item ID

The unique identifier of the work item that was updated.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Previous state*

The previous work item status.

SQL name	`prev_status`
Data type	String

Previous status category*

The status category of the previous work item status.

SQL name	`prev_status_category`
Data type	String

Previous status ID

The unique identifier of the previous work item status.

SQL name	`prev_status_id`
Data type	String

Started at*

The date and time (UTC time zone) when the new work item status was set.

SQL name	`started_at`
Data type	Datetime

Status*

The new work item status.

SQL name	`status`
Data type	String

Status category*

The status category of the new work item status.

SQL name	`status_category`
Data type	String

Status ID

The unique identifier of the new work item status.

SQL name	`status_id`
Data type	String

Time in status (seconds)

The time interval (in seconds) between the start and end time of the status.

It will be NULL if there was no subsequent work item transition (in other words, the new work item status is the current one).

SQL name	`time_in_status_secs`
Data type	Number

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Work item work log

The jira_issue_worklog table indicates the amount of time that’s been spent on an work item and records each work log entry (in other words, each time that’s logged on a work item).

Authored by

The unique identifier of the person who created the work log entry and to whom the time will be attributed.

SQL name	`authored_by`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Created at

The date and time (UTC time zone) when the work log entry was created.

SQL name	`created_at`
Data type	Datetime

Document content*

The work log document details in ADF format.

SQL name	`document_content`
Data type	String

Work item ID

The unique identifier of the work item that the work log belongs to.

SQL name	`issue_id`
Data type	String
Foreign key	Links to a record in the `jira_issue` table.

Work item work log ID

The unique identifier of the work log entry. Use this as the primary key for work log data.

SQL name	`issue_worklog_id`
Data type	String

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Started at

The date and time (UTC time zone) when the work on the work item was started.

SQL name	`started_at`
Data type	Datetime

Time spent (seconds)*

The time (in seconds) logged on the work item.

SQL name	`time_spent_seconds`
Data type	Number

Updated at

The date and time (UTC time zone) when the work log entry was last updated.

SQL name	`updated_at`
Data type	Datetime

Updated by

The unique identifier of the person who last updated the work log entry.

SQL name	`updated_by`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Workspace ID

An Atlassian identifier that maps data to a site in the workspace table

SQL name	`workspace_id`
Data type	String
Foreign key	Links to a record in the `workspace` table in the schema for organization data.

Was this helpful?

It wasn't accurateIt wasn't clearIt wasn't relevant

Still need help?

The Atlassian Community is here for you.

Ask the Community