Schema for Atlassian Projects

Atlassian projects help bring work into one place and provide just the right amount of context so that anyone at your company can understand what is being worked on, why it is happening, who is responsible for it, and how it is going. Read more about Atlassian projects.

If you add Atlassian Projects data to an Atlassian Data Lake connection, you can query data from the following tables:

Project

The atlassian_project table holds the latest information about Atlassian projects. Read more about Projects.

The table uses the following columns:

Created at

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

SQL name	`created_at`
Data type	Datetime

Is archived

Indicates whether or not the project is archived.

SQL name	`is_archived`
Data type	Boolean

Key

The key of the project.

SQL name	`key`
Data type	String
Example value	GLOBE-1234

Name

The name of the project.

SQL name	`name`
Data type	String

Owner ID

The unique identifier of the owner of the project.

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

Phase

The state of the project.

SQL name	`phase`
Data type	String
Example values	pending in progress paused done

Project ID

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

SQL name	`project_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 project started.

SQL name	`started_at`
Data type	Datetime

Status

The status of the project.

SQL name	`status`
Data type	String
Example values	On track At risk Off track

Target date type

The confidence of the target date.

SQL name	`target_date_type`
Data type	String
Example values	day month quarter

Targeted at

The date and time (UTC time zone) of the project’s target date.

SQL name	`targeted_at`
Data type	Datetime

The measurement

Describes how the success of the project will be measured. It corresponds to the “What will success look like?” field in the “About” tab of the project.

SQL name	`the_measurement`
Data type	String

The what

Describes what’s being done in the project. It corresponds to the “What are we doing” field in the “About” tab of the project.

SQL name	`the_what`
Data type	String

The why

Describes why the project exists. It corresponds to the “Why are we doing it?” field in the “About” tab of the project.

SQL name	`the_why`
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.

Project comment

The atlassian_project_comment table contains information about the comments on Atlassian projects.

The table uses the following columns:

Body

The text of the comment.

SQL name	`body`
Data type	String

Comment ID

The unique identifier of the comment on the project.

SQL name	`project_comment_id`
Data type	String

Created at

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

SQL name	`created_at`
Data type	Datetime

Created by

The unique identifier of the person who created the comment.

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

Project ID

The unique identifier of the project that the comment was made to.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Updated at

The date and time (UTC time zone) when the comment 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 member mapping

The atlassian_project_member_mapping table holds associations between projects and members who are contributing to the project.

The table uses the following columns:

Account ID

The unique identifier of the project member.

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

Project ID

The unique identifier of the project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `atlassian_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 milestone

The atlassian_project_milestone table contains the latest information on the specific milestones of an Atlassian project.

The table uses the following columns:

Completed at

The date and time (UTC time zone) when the milestone was completed.

SQL name	`completed_at`
Data type	Datetime

Completed in project update ID

The unique identifier of the project update in which the milestone was completed.

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

Created at

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

SQL name	`created_at`
Data type	Datetime

Milestone ID

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

SQL name	`milestone_id`
Data type	String

Project ID

The unique identifier of the project associated with the milestone.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Status

The status of the milestone.

SQL name	`status`
Data type	String
Example values	not_started in_progress completed

Target date type

The confidence of the target date of the milestone.

SQL name	`target_date_type`
Data type	String
Example values	day month quarter

Targeted at

The date and time (UTC time zone) that the milestone is targeted to be completed.

SQL name	`targeted_at`
Data type	Datetime

Title

The title of the milestone.

SQL name	`title`
Data type	String

Updated at

The date and time (UTC time zone) when the milestone 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.

The atlassian_project_related_project table contains information about the Atlassian projects that are related to a particular Atlassian project.

The table uses the following columns:

Created at

The date and time (UTC time zone) when the relationship between the projects was created.

SQL name	`created_at`
Data type	Datetime

Project ID

The unique identifier of the project.

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

Related project ID

The unique identifier of the related project.

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

Relationship type

The type of relationships between the two projects.

SQL name	`relationship_type`
Data type	String
Example values	depended_on_by depends_on

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 team mapping

The atlassian_project_team_mapping table holds associations between projects and teams that are contributing to the project.

The table uses the following columns:

Project ID

The unique identifier of the project.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Team ID

The unique identifier of the team contributing to the project.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `atlassian_team` 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.

Project update

The atlassian_project_update table contains information about updates to Atlassian projects.

The table uses the following columns:

Created at

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

SQL name	`created_at`
Data type	Datetime

Created by

The unique identifier of the person who created the update.

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

Is missed update

Indicates whether or not the update was missed.

SQL name	`is_missed_update`
Data type	Boolean

New phase

The state of the project after the update.

SQL name	`new_phase`
Data type	String
Example values	pending in progress paused

New status

The status of the project after the update.

SQL name	`new_status`
Data type	String
Example values	off-track on-track completed

New targeted at

The date and time (UTC time zone) of the target date of the project after the update.

SQL name	`new_targeted_at`
Data type	Datetime

New targeted confidence

The confidence of the target date after the update.

SQL name	`new_targeted_confidence`
Data type	Number

Old phase

The state of the project before the update.

SQL name	`old_phase`
Data type	String
Example values	pending in progress paused

Old status

The status of the project before the update.

SQL name	`old_status`
Data type	String
Example values	off-track on-track completed

Old targeted at

The date and time (UTC time zone) of the target date of the project before the update.

SQL name	`old_targeted_at`
Data type	Datetime

Old targeted confidence

The confidence of the target date before the update.

SQL name	`old_targeted_confidence`
Data type	String

Project ID

The unique identifier of the project associated with the update.

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

Project update ID

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

SQL name	`project_update_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

Summary

The summary of the update.

SQL name	`summary`
Data type	String

Updated at

The date and time (UTC time zone) when the update was last edited.

SQL name	`updated_at`
Data type	Datetime

Updated by

The unique identifier of the person who last edited the update.

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.

Project update comment

The atlassian_project_update_comment table contains information about comments made to project updates.

The table uses the following columns:

Body

The text of the comment.

SQL name	`body`
Data type	String

Comment ID

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

SQL name	`project_comment_id`
Data type	String

Created at

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

SQL name	`created_at`
Data type	Datetime

Created by

The unique identifier of the person who created the comment.

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

Project ID

The unique identifier of the project that the comment is part of.

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

Project update ID

The unique identifier of the project update that the comment was made to.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Updated at

The date and time (UTC time zone) when the comment was last edited.

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 update note

The atlassian_project_update_note table contains information about notes attached to project updates.

The table uses the following columns:

Created at

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

SQL name	`created_at`
Data type	Datetime

Created by

The unique identifier of the person who created the note.

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

Is archived

Indicates whether or not the project associated with the update note is archived.

SQL name	`is_archived`
Data type	Boolean

Project update ID

The unique identifier of the project update associated with the note.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Summary

The text of the update note.

SQL name	`summary`
Data type	String

Title

The name of the update note.

SQL name	`title`
Data type	String

Update note ID

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

SQL name	`update_note_id`
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.

Project watcher mapping

The atlassian_project_watcher_mapping table holds associations between projects and people watching those projects.

The table uses the following columns:

Account ID

The unique identifier of the person watching the project.

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

Project ID

The unique identifier of the project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `atlassian_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.

Tables for relationships across apps

These tables help to connect data from this schema to data from other app schemas in the Atlassian Data Lake. You can query these tables if at least one of the apps is included in the Data Lake connection. However, if both apps aren’t included, the relevant tables won’t have data.

Atlassian project Jira work item mapping

The atlassian_project_jira_issue_mapping table holds associations between Atlassian projects and Jira work items.

The table uses the following columns:

Jira workspace ID

An Atlassian identifier that maps to the Jira instance that the work item belongs to.

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

Project ID

The unique identifier of an Atlassian project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `atlassian_project` table in the schema for Atlassian Projects.

Project workspace ID

An Atlassian identifier that maps to the Atlassian Projects instance that the project belongs to.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Updated at

The date and time (UTC time zone) when the association between the Atlassian project and the Jira work item was last updated.

SQL name	`updated_at`
Data type	Datetime

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 in the schema for Jira family of products.

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.

Focus area Atlassian project mapping

The focus_area_atlassian_project_mapping table holds associations between focus areas from the Focus app and Atlassian projects.

The table uses the following columns:

Focus area ID

The unique identifier of a focus area.

SQL name	`focus_area_id`
Data type	String
Foreign key	Links to a record in the `focus_area` table in the schema for Focus.

Focus workspace ID

An Atlassian identifier that maps to the Focus instance that the focus area belongs to.

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

Project ID

The unique identifier of an Atlassian project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `atlassian_project` table in the schema for Atlassian Projects.

Project workspace ID

An Atlassian identifier that maps to the Atlassian Projects instance that the project belongs to.

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

Row refreshed at

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

SQL name	`row_refreshed_at`
Data type	Datetime

Updated at

The date and time (UTC time zone) when the association between the focus area and the Atlassian 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.

Goal Atlassian project mapping

The goal_atlassian_project_mapping table holds associations between goals from the Goals app and Atlassian projects.

The table uses the following columns:

Goal ID

The unique identifier of a goal.

SQL name	`goal_id`
Data type	String
Foreign key	Links to a record in the `goal` table in the schema for Goals.

Project ID

The unique identifier of an Atlassian project.

SQL name	`project_id`
Data type	String
Foreign key	Links to a record in the `atlassian_project` table in the schema for Atlassian Projects.

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.

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

Schema for Atlassian Projects

Project

Project comment

Project member mapping

Project milestone

Project related project

Project team mapping

Project update

Project update comment

Project update note

Project watcher mapping

Tables for relationships across apps

Atlassian project Jira work item mapping

Jira workspace ID

Project ID

Project workspace ID

Row refreshed at

Updated at

Work item ID

Workspace ID

Focus area Atlassian project mapping

Focus area ID

Focus workspace ID

Project ID

Project workspace ID

Row refreshed at

Updated at

Workspace ID

Goal Atlassian project mapping

Goal ID

Project ID

Row refreshed at

Workspace ID

Still need help?