This is an automated email from the ASF dual-hosted git repository.
klesh pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/devlake.git
The following commit(s) were added to refs/heads/main by this push:
new 21ea61c30 feat(jira): persist Sprint Report snapshot for accurate
committed/completed velocity (#8967) (#9010)
21ea61c30 is described below
commit 21ea61c300e029cb1684a3f70863ce6ebabaa555
Author: Bramhanand Lingala <[email protected]>
AuthorDate: Mon Jul 27 02:29:53 2026 -0400
feat(jira): persist Sprint Report snapshot for accurate committed/completed
velocity (#8967) (#9010)
---
backend/core/models/domainlayer/ticket/sprint.go | 19 +--
.../20260722_add_sprint_velocity_fields.go | 49 +++++++
backend/core/models/migrationscripts/register.go | 1 +
backend/plugins/jira/impl/impl.go | 5 +
.../20260722_add_sprint_report_table.go | 55 ++++++++
.../jira/models/migrationscripts/register.go | 1 +
backend/plugins/jira/models/sprint_report.go | 64 +++++++++
.../jira/tasks/apiv2models/sprint_report.go | 73 ++++++++++
.../plugins/jira/tasks/sprint_report_collector.go | 116 ++++++++++++++++
.../plugins/jira/tasks/sprint_report_convertor.go | 99 ++++++++++++++
.../plugins/jira/tasks/sprint_report_extractor.go | 92 +++++++++++++
.../plugins/linear/e2e/snapshot_tables/sprints.csv | 6 +-
.../e2e/snapshot_tables/execution_sprint.csv | 18 +--
grafana/dashboards/mysql/Jira.json | 150 +++++++++++++++++++-
grafana/dashboards/postgresql/Jira.json | 152 ++++++++++++++++++++-
15 files changed, 878 insertions(+), 22 deletions(-)
diff --git a/backend/core/models/domainlayer/ticket/sprint.go
b/backend/core/models/domainlayer/ticket/sprint.go
index 29c449aca..47d1c2b35 100644
--- a/backend/core/models/domainlayer/ticket/sprint.go
+++ b/backend/core/models/domainlayer/ticket/sprint.go
@@ -18,9 +18,10 @@ limitations under the License.
package ticket
import (
+ "time"
+
"github.com/apache/incubator-devlake/core/models/common"
"github.com/apache/incubator-devlake/core/models/domainlayer"
- "time"
)
var (
@@ -31,13 +32,15 @@ var (
type Sprint struct {
domainlayer.DomainEntity
- Name string `gorm:"type:varchar(255)"`
- Url string `gorm:"type:varchar(255)"`
- Status string `gorm:"type:varchar(100)"`
- StartedDate *time.Time
- EndedDate *time.Time
- CompletedDate *time.Time
- OriginalBoardID string `gorm:"type:varchar(255)"`
+ Name string `gorm:"type:varchar(255)"`
+ Url string `gorm:"type:varchar(255)"`
+ Status string `gorm:"type:varchar(100)"`
+ StartedDate *time.Time
+ EndedDate *time.Time
+ CompletedDate *time.Time
+ OriginalBoardID string `gorm:"type:varchar(255)"`
+ CommittedStoryPoint *float64
+ CompletedStoryPoint *float64
}
func (Sprint) TableName() string {
diff --git
a/backend/core/models/migrationscripts/20260722_add_sprint_velocity_fields.go
b/backend/core/models/migrationscripts/20260722_add_sprint_velocity_fields.go
new file mode 100644
index 000000000..369b70b03
--- /dev/null
+++
b/backend/core/models/migrationscripts/20260722_add_sprint_velocity_fields.go
@@ -0,0 +1,49 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package migrationscripts
+
+import (
+ "github.com/apache/incubator-devlake/core/context"
+ "github.com/apache/incubator-devlake/core/errors"
+ "github.com/apache/incubator-devlake/core/plugin"
+)
+
+var _ plugin.MigrationScript = (*addSprintVelocityFields)(nil)
+
+type sprint20260722 struct {
+ CommittedStoryPoint *float64
+ CompletedStoryPoint *float64
+}
+
+func (sprint20260722) TableName() string {
+ return "sprints"
+}
+
+type addSprintVelocityFields struct{}
+
+func (script *addSprintVelocityFields) Up(basicRes context.BasicRes)
errors.Error {
+ return basicRes.GetDal().AutoMigrate(new(sprint20260722))
+}
+
+func (*addSprintVelocityFields) Version() uint64 {
+ return 20260722100000
+}
+
+func (*addSprintVelocityFields) Name() string {
+ return "add committed_story_point/completed_story_point to sprints"
+}
diff --git a/backend/core/models/migrationscripts/register.go
b/backend/core/models/migrationscripts/register.go
index e25363a1b..9dd5e762b 100644
--- a/backend/core/models/migrationscripts/register.go
+++ b/backend/core/models/migrationscripts/register.go
@@ -148,5 +148,6 @@ func All() []plugin.MigrationScript {
new(changeIssueComponentToText),
new(changeCqIssueCodeBlocksComponentToText),
new(addCqProjectMetricsHistory),
+ new(addSprintVelocityFields),
}
}
diff --git a/backend/plugins/jira/impl/impl.go
b/backend/plugins/jira/impl/impl.go
index 6fb428a92..b27ea9daa 100644
--- a/backend/plugins/jira/impl/impl.go
+++ b/backend/plugins/jira/impl/impl.go
@@ -85,6 +85,7 @@ func (p Jira) GetTablesInfo() []dal.Tabler {
&models.JiraServerInfo{},
&models.JiraSprint{},
&models.JiraSprintIssue{},
+ &models.JiraSprintReport{},
&models.JiraStatus{},
&models.JiraWorklog{},
&models.JiraIssueComment{},
@@ -138,6 +139,9 @@ func (p Jira) SubTaskMetas() []plugin.SubTaskMeta {
tasks.CollectSprintsMeta,
tasks.ExtractSprintsMeta,
+ tasks.CollectSprintReportMeta,
+ tasks.ExtractSprintReportMeta,
+
tasks.CollectEpicsMeta,
tasks.ExtractEpicsMeta,
@@ -153,6 +157,7 @@ func (p Jira) SubTaskMetas() []plugin.SubTaskMeta {
tasks.ConvertSprintsMeta,
tasks.ConvertSprintIssuesMeta,
+ tasks.ConvertSprintReportMeta,
tasks.CollectDevelopmentPanelMeta,
tasks.ExtractDevelopmentPanelMeta,
diff --git
a/backend/plugins/jira/models/migrationscripts/20260722_add_sprint_report_table.go
b/backend/plugins/jira/models/migrationscripts/20260722_add_sprint_report_table.go
new file mode 100644
index 000000000..c31b709de
--- /dev/null
+++
b/backend/plugins/jira/models/migrationscripts/20260722_add_sprint_report_table.go
@@ -0,0 +1,55 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package migrationscripts
+
+import (
+ "github.com/apache/incubator-devlake/core/context"
+ "github.com/apache/incubator-devlake/core/errors"
+ "github.com/apache/incubator-devlake/helpers/migrationhelper"
+)
+
+type jiraSprintReport20260722 struct {
+ ConnectionId uint64 `gorm:"primaryKey"`
+ BoardId uint64 `gorm:"primaryKey"`
+ SprintId uint64 `gorm:"primaryKey"`
+ IssueId uint64 `gorm:"primaryKey"`
+
+ IssueKey string `gorm:"type:varchar(255)"`
+ Bucket string `gorm:"type:varchar(32);index"`
+ Done bool
+ StoryPointsAtSprintStart *float64
+ StoryPointsAtSprintEnd *float64
+}
+
+func (jiraSprintReport20260722) TableName() string {
+ return "_tool_jira_sprint_reports"
+}
+
+type addSprintReportTable struct{}
+
+func (script *addSprintReportTable) Up(basicRes context.BasicRes) errors.Error
{
+ return migrationhelper.AutoMigrateTables(basicRes,
&jiraSprintReport20260722{})
+}
+
+func (*addSprintReportTable) Version() uint64 {
+ return 20260722000000
+}
+
+func (*addSprintReportTable) Name() string {
+ return "add _tool_jira_sprint_reports table to persist Jira's frozen
Sprint Report snapshot"
+}
diff --git a/backend/plugins/jira/models/migrationscripts/register.go
b/backend/plugins/jira/models/migrationscripts/register.go
index 379d54f89..90a3317bd 100644
--- a/backend/plugins/jira/models/migrationscripts/register.go
+++ b/backend/plugins/jira/models/migrationscripts/register.go
@@ -58,5 +58,6 @@ func All() []plugin.MigrationScript {
new(addSubQueryToBoards),
new(changeFixVersionsToText20260707),
new(addExtraJQLToScopeConfig),
+ new(addSprintReportTable),
}
}
diff --git a/backend/plugins/jira/models/sprint_report.go
b/backend/plugins/jira/models/sprint_report.go
new file mode 100644
index 000000000..b9280e2e4
--- /dev/null
+++ b/backend/plugins/jira/models/sprint_report.go
@@ -0,0 +1,64 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package models
+
+import (
+ "github.com/apache/incubator-devlake/core/models/common"
+)
+
+// Sprint Report bucket values, mirroring the four buckets returned by
+// GET rest/greenhopper/1.0/rapid/charts/sprintreport. Jira computes these
+// once, at sprint close, so persisting them (rather than reconstructing
+// from resolution_date) is what makes committed/completed velocity exact.
+const (
+ SprintReportBucketCompleted = "completed"
+ SprintReportBucketNotCompleted = "notCompleted"
+ SprintReportBucketPunted = "punted"
+ SprintReportBucketCompletedInOtherSprint = "completedInOtherSprint"
+)
+
+// JiraSprintReport is a frozen, per-(board, sprint, issue) snapshot taken
+// from Jira's Sprint Report at sprint close. Unlike JiraSprintIssue (which
+// is derived from each issue's live resolution_date and therefore
+// mis-attributes carryover issues), this table stores Jira's own
+// point-in-time bucketing, so it doesn't drift.
+type JiraSprintReport struct {
+ common.NoPKModel
+ ConnectionId uint64 `gorm:"primaryKey"`
+ BoardId uint64 `gorm:"primaryKey"`
+ SprintId uint64 `gorm:"primaryKey"`
+ IssueId uint64 `gorm:"primaryKey"`
+
+ IssueKey string `gorm:"type:varchar(255)"`
+ // Bucket is one of the SprintReportBucket* constants above.
+ Bucket string `gorm:"type:varchar(32);index"`
+ Done bool
+
+ // StoryPointsAtSprintStart is estimateStatistic.statFieldValue.value in
+ // Jira's response ("BOS points") — the estimate as it stood when the
+ // sprint began, i.e. what should be summed for *committed* velocity.
+ StoryPointsAtSprintStart *float64
+ // StoryPointsAtSprintEnd is
currentEstimateStatistic.statFieldValue.value
+ // ("EOS points") — the estimate as of sprint close, i.e. what should be
+ // summed (for Bucket == completed) for *completed* velocity.
+ StoryPointsAtSprintEnd *float64
+}
+
+func (JiraSprintReport) TableName() string {
+ return "_tool_jira_sprint_reports"
+}
diff --git a/backend/plugins/jira/tasks/apiv2models/sprint_report.go
b/backend/plugins/jira/tasks/apiv2models/sprint_report.go
new file mode 100644
index 000000000..eec40ace9
--- /dev/null
+++ b/backend/plugins/jira/tasks/apiv2models/sprint_report.go
@@ -0,0 +1,73 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package apiv2models
+
+import "time"
+
+// SprintReportInput drives the per-(board, sprint) Sprint Report collector.
+// It's what gets iterated over via the DAL cursor, and re-attached to each
+// raw row so the extractor knows which board/sprint a response belongs to.
+type SprintReportInput struct {
+ BoardId uint64 `json:"board_id"`
+ SprintId uint64 `json:"sprint_id"`
+ // UpdateTime is the sprint's CompleteDate; used as the incremental-sync
+ // watermark since a sprint report only exists/changes once a sprint
closes.
+ UpdateTime *time.Time `json:"update_time"`
+}
+
+// SprintReportStatFieldValue mirrors Jira's
+// {statFieldValue: {value, text}} shape used for per-issue point estimates.
+type SprintReportStatFieldValue struct {
+ Value *float64 `json:"value"`
+ Text string `json:"text"`
+}
+
+type SprintReportStatistic struct {
+ StatFieldValue SprintReportStatFieldValue `json:"statFieldValue"`
+}
+
+// SprintReportIssue is one entry inside any of the four bucket lists
+// (completedIssues, issuesNotCompletedInCurrentSprint, puntedIssues,
+// issuesCompletedInAnotherSprint) in the Sprint Report response.
+type SprintReportIssue struct {
+ Id uint64 `json:"id"`
+ Key string `json:"key"`
+ TypeName string `json:"typeName"`
+ Done bool `json:"done"`
+ // EstimateStatistic is the issue's estimate as of sprint *start*
+ // ("BOS points" / committed).
+ EstimateStatistic SprintReportStatistic `json:"estimateStatistic"`
+ // CurrentEstimateStatistic is the issue's estimate as of sprint *close*
+ // ("EOS points" / completed).
+ CurrentEstimateStatistic SprintReportStatistic
`json:"currentEstimateStatistic"`
+}
+
+// SprintReportContents is the "contents" object of the Sprint Report
+// response — the frozen snapshot Jira takes at sprint close.
+type SprintReportContents struct {
+ CompletedIssues []SprintReportIssue
`json:"completedIssues"`
+ IssuesNotCompletedInCurrentSprint []SprintReportIssue
`json:"issuesNotCompletedInCurrentSprint"`
+ PuntedIssues []SprintReportIssue
`json:"puntedIssues"`
+ IssuesCompletedInAnotherSprint []SprintReportIssue
`json:"issuesCompletedInAnotherSprint"`
+}
+
+// SprintReport is the top-level response of
+// GET rest/greenhopper/1.0/rapid/charts/sprintreport?rapidViewId=&sprintId=
+type SprintReport struct {
+ Contents SprintReportContents `json:"contents"`
+}
diff --git a/backend/plugins/jira/tasks/sprint_report_collector.go
b/backend/plugins/jira/tasks/sprint_report_collector.go
new file mode 100644
index 000000000..1f8db1f5c
--- /dev/null
+++ b/backend/plugins/jira/tasks/sprint_report_collector.go
@@ -0,0 +1,116 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package tasks
+
+import (
+ "encoding/json"
+ "fmt"
+ "io"
+ "net/http"
+ "net/url"
+ "reflect"
+
+ "github.com/apache/incubator-devlake/core/dal"
+ "github.com/apache/incubator-devlake/core/errors"
+ "github.com/apache/incubator-devlake/core/plugin"
+ "github.com/apache/incubator-devlake/helpers/pluginhelper/api"
+ "github.com/apache/incubator-devlake/plugins/jira/tasks/apiv2models"
+)
+
+const RAW_SPRINT_REPORT_TABLE = "jira_api_sprint_reports"
+
+var _ plugin.SubTaskEntryPoint = CollectSprintReport
+
+var CollectSprintReportMeta = plugin.SubTaskMeta{
+ Name: "collectSprintReport",
+ EntryPoint: CollectSprintReport,
+ EnabledByDefault: true,
+ Description: "collect Jira Sprint Report, the frozen
committed/completed snapshot Jira takes at sprint close",
+ DomainTypes: []string{plugin.DOMAIN_TYPE_TICKET},
+}
+
+// CollectSprintReport fetches the Greenhopper Sprint Report for every closed
+// sprint on this board. It depends on CollectSprints/ExtractSprints having
+// already populated _tool_jira_board_sprints and _tool_jira_sprints, since
+// that's where the (boardId, sprintId) pairs to query come from. Only closed
+// sprints are queried: the report is only a meaningful, frozen snapshot once
+// a sprint has actually closed.
+func CollectSprintReport(taskCtx plugin.SubTaskContext) errors.Error {
+ data := taskCtx.GetData().(*JiraTaskData)
+ db := taskCtx.GetDal()
+ logger := taskCtx.GetLogger()
+
+ apiCollector, err := api.NewStatefulApiCollector(api.RawDataSubTaskArgs{
+ Ctx: taskCtx,
+ Params: JiraApiParams{
+ ConnectionId: data.Options.ConnectionId,
+ BoardId: data.Options.BoardId,
+ },
+ Table: RAW_SPRINT_REPORT_TABLE,
+ })
+ if err != nil {
+ return err
+ }
+
+ clauses := []dal.Clause{
+ dal.Select("bs.board_id AS board_id, bs.sprint_id AS sprint_id,
s.complete_date AS update_time"),
+ dal.From("_tool_jira_board_sprints bs"),
+ dal.Join("LEFT JOIN _tool_jira_sprints s ON (bs.connection_id =
s.connection_id AND bs.sprint_id = s.sprint_id)"),
+ dal.Where("bs.connection_id = ? AND bs.board_id = ? AND s.state
= ?", data.Options.ConnectionId, data.Options.BoardId, "closed"),
+ }
+ if apiCollector.IsIncremental() && apiCollector.GetSince() != nil {
+ clauses = append(clauses, dal.Where("s.complete_date > ?",
apiCollector.GetSince()))
+ }
+ cursor, err := db.Cursor(clauses...)
+ if err != nil {
+ logger.Error(err, "collect sprint report error")
+ return err
+ }
+
+ iterator, err := api.NewDalCursorIterator(db, cursor,
reflect.TypeOf(apiv2models.SprintReportInput{}))
+ if err != nil {
+ return err
+ }
+
+ err = apiCollector.InitCollector(api.ApiCollectorArgs{
+ ApiClient: data.ApiClient,
+ Input: iterator,
+ // e.g.
https://xxx.atlassian.net/rest/greenhopper/1.0/rapid/charts/sprintreport?rapidViewId=1&sprintId=2
+ UrlTemplate: "greenhopper/1.0/rapid/charts/sprintreport",
+ Query: func(reqData *api.RequestData) (url.Values,
errors.Error) {
+ input := reqData.Input.(*apiv2models.SprintReportInput)
+ query := url.Values{}
+ query.Set("rapidViewId", fmt.Sprintf("%d",
input.BoardId))
+ query.Set("sprintId", fmt.Sprintf("%d", input.SprintId))
+ return query, nil
+ },
+ ResponseParser: func(res *http.Response) ([]json.RawMessage,
errors.Error) {
+ blob, e := io.ReadAll(res.Body)
+ if e != nil {
+ return nil, errors.Convert(e)
+ }
+ return []json.RawMessage{blob}, nil
+ },
+ AfterResponse: ignoreHTTPStatus400,
+ })
+ if err != nil {
+ return err
+ }
+
+ return apiCollector.Execute()
+}
diff --git a/backend/plugins/jira/tasks/sprint_report_convertor.go
b/backend/plugins/jira/tasks/sprint_report_convertor.go
new file mode 100644
index 000000000..76046c273
--- /dev/null
+++ b/backend/plugins/jira/tasks/sprint_report_convertor.go
@@ -0,0 +1,99 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package tasks
+
+import (
+ "github.com/apache/incubator-devlake/core/dal"
+ "github.com/apache/incubator-devlake/core/errors"
+ "github.com/apache/incubator-devlake/core/models/domainlayer/didgen"
+ "github.com/apache/incubator-devlake/core/models/domainlayer/ticket"
+ "github.com/apache/incubator-devlake/core/plugin"
+ "github.com/apache/incubator-devlake/plugins/jira/models"
+)
+
+var ConvertSprintReportMeta = plugin.SubTaskMeta{
+ Name: "convertSprintReport",
+ EntryPoint: ConvertSprintReport,
+ EnabledByDefault: true,
+ Description: "aggregate Jira Sprint Report into
committed/completed story points on the domain sprints table",
+ DomainTypes: []string{plugin.DOMAIN_TYPE_TICKET},
+}
+
+type sprintVelocity struct {
+ SprintId uint64 `json:"sprint_id"`
+ Committed *float64 `json:"committed"`
+ Completed *float64 `json:"completed"`
+}
+
+// ConvertSprintReport aggregates _tool_jira_sprint_reports (one row per
+// issue per sprint per bucket) into two numbers per sprint:
+// - committed: sum of each issue's start-of-sprint estimate, for every
+// issue that was part of the sprint when it began (completed,
+// notCompleted, and punted issues; punted issues were still committed,
+// they were just removed before the sprint closed).
+// - completed: sum of each issue's end-of-sprint estimate, for issues in
+// the completed bucket only.
+//
+// It writes these directly onto the existing domain sprints row via
+// UpdateColumns rather than going through the usual convert-and-save
+// pipeline, specifically so it only touches these two columns and can't
+// clobber the Name/Url/Status/dates that ConvertSprints already wrote.
+func ConvertSprintReport(taskCtx plugin.SubTaskContext) errors.Error {
+ data := taskCtx.GetData().(*JiraTaskData)
+ db := taskCtx.GetDal()
+ connectionId := data.Options.ConnectionId
+ sprintIdGen := didgen.NewDomainIdGenerator(&models.JiraSprint{})
+
+ clauses := []dal.Clause{
+ dal.Select(`
+ sprint_id,
+ SUM(CASE WHEN bucket IN (?, ?, ?) THEN
story_points_at_sprint_start ELSE NULL END) AS committed,
+ SUM(CASE WHEN bucket = ? THEN
story_points_at_sprint_end ELSE NULL END) AS completed
+ `,
+ models.SprintReportBucketCompleted,
+ models.SprintReportBucketNotCompleted,
+ models.SprintReportBucketPunted,
+ models.SprintReportBucketCompleted,
+ ),
+ dal.From("_tool_jira_sprint_reports"),
+ dal.Where("connection_id = ? AND board_id = ?", connectionId,
data.Options.BoardId),
+ dal.Groupby("sprint_id"),
+ }
+ cursor, err := db.Cursor(clauses...)
+ if err != nil {
+ return err
+ }
+ defer cursor.Close()
+
+ for cursor.Next() {
+ var row sprintVelocity
+ if err = db.Fetch(cursor, &row); err != nil {
+ return err
+ }
+ domainSprintId := sprintIdGen.Generate(connectionId,
row.SprintId)
+ updateSet := []dal.DalSet{
+ {ColumnName: "committed_story_point", Value:
row.Committed},
+ {ColumnName: "completed_story_point", Value:
row.Completed},
+ }
+ if err = db.UpdateColumns(&ticket.Sprint{}, updateSet,
dal.Where("id = ?", domainSprintId)); err != nil {
+ return err
+ }
+ }
+
+ return nil
+}
diff --git a/backend/plugins/jira/tasks/sprint_report_extractor.go
b/backend/plugins/jira/tasks/sprint_report_extractor.go
new file mode 100644
index 000000000..c24c6be36
--- /dev/null
+++ b/backend/plugins/jira/tasks/sprint_report_extractor.go
@@ -0,0 +1,92 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package tasks
+
+import (
+ "encoding/json"
+
+ "github.com/apache/incubator-devlake/core/errors"
+ "github.com/apache/incubator-devlake/core/plugin"
+ "github.com/apache/incubator-devlake/helpers/pluginhelper/api"
+ "github.com/apache/incubator-devlake/plugins/jira/models"
+ "github.com/apache/incubator-devlake/plugins/jira/tasks/apiv2models"
+)
+
+var _ plugin.SubTaskEntryPoint = ExtractSprintReport
+
+var ExtractSprintReportMeta = plugin.SubTaskMeta{
+ Name: "extractSprintReport",
+ EntryPoint: ExtractSprintReport,
+ EnabledByDefault: true,
+ Description: "extract Jira Sprint Report",
+ DomainTypes: []string{plugin.DOMAIN_TYPE_TICKET},
+}
+
+func ExtractSprintReport(taskCtx plugin.SubTaskContext) errors.Error {
+ data := taskCtx.GetData().(*JiraTaskData)
+ connectionId := data.Options.ConnectionId
+
+ extractor, err := api.NewApiExtractor(api.ApiExtractorArgs{
+ RawDataSubTaskArgs: api.RawDataSubTaskArgs{
+ Ctx: taskCtx,
+ Params: JiraApiParams{
+ ConnectionId: data.Options.ConnectionId,
+ BoardId: data.Options.BoardId,
+ },
+ Table: RAW_SPRINT_REPORT_TABLE,
+ },
+ Extract: func(row *api.RawData) ([]interface{}, errors.Error) {
+ var report apiv2models.SprintReport
+ if err := errors.Convert(json.Unmarshal(row.Data,
&report)); err != nil {
+ return nil, err
+ }
+ var input apiv2models.SprintReportInput
+ if err := errors.Convert(json.Unmarshal(row.Input,
&input)); err != nil {
+ return nil, err
+ }
+
+ var result []interface{}
+ addBucket := func(issues
[]apiv2models.SprintReportIssue, bucket string) {
+ for _, issue := range issues {
+ result = append(result,
&models.JiraSprintReport{
+ ConnectionId:
connectionId,
+ BoardId:
input.BoardId,
+ SprintId:
input.SprintId,
+ IssueId:
issue.Id,
+ IssueKey:
issue.Key,
+ Bucket:
bucket,
+ Done:
issue.Done,
+ StoryPointsAtSprintStart:
issue.EstimateStatistic.StatFieldValue.Value,
+ StoryPointsAtSprintEnd:
issue.CurrentEstimateStatistic.StatFieldValue.Value,
+ })
+ }
+ }
+ addBucket(report.Contents.CompletedIssues,
models.SprintReportBucketCompleted)
+
addBucket(report.Contents.IssuesNotCompletedInCurrentSprint,
models.SprintReportBucketNotCompleted)
+ addBucket(report.Contents.PuntedIssues,
models.SprintReportBucketPunted)
+
addBucket(report.Contents.IssuesCompletedInAnotherSprint,
models.SprintReportBucketCompletedInOtherSprint)
+
+ return result, nil
+ },
+ })
+ if err != nil {
+ return err
+ }
+
+ return extractor.Execute()
+}
diff --git a/backend/plugins/linear/e2e/snapshot_tables/sprints.csv
b/backend/plugins/linear/e2e/snapshot_tables/sprints.csv
index a09ab2ffe..c6324bf02 100644
--- a/backend/plugins/linear/e2e/snapshot_tables/sprints.csv
+++ b/backend/plugins/linear/e2e/snapshot_tables/sprints.csv
@@ -1,3 +1,3 @@
-id,name,url,status,started_date,ended_date,completed_date,original_board_id
-linear:LinearCycle:1:cycle-1,Cycle
1,,CLOSED,2026-04-20T00:00:00.000+00:00,2026-05-04T00:00:00.000+00:00,2026-05-04T00:00:00.000+00:00,linear:LinearTeam:1:team-1
-linear:LinearCycle:1:cycle-2,Sprint
2,,ACTIVE,2026-05-04T00:00:00.000+00:00,2026-05-18T00:00:00.000+00:00,,linear:LinearTeam:1:team-1
+id,name,url,status,started_date,ended_date,completed_date,original_board_id,committed_story_point,completed_story_point
+linear:LinearCycle:1:cycle-1,Cycle
1,,CLOSED,2026-04-20T00:00:00.000+00:00,2026-05-04T00:00:00.000+00:00,2026-05-04T00:00:00.000+00:00,linear:LinearTeam:1:team-1,,
+linear:LinearCycle:1:cycle-2,Sprint
2,,ACTIVE,2026-05-04T00:00:00.000+00:00,2026-05-18T00:00:00.000+00:00,,linear:LinearTeam:1:team-1,,
diff --git a/backend/plugins/zentao/e2e/snapshot_tables/execution_sprint.csv
b/backend/plugins/zentao/e2e/snapshot_tables/execution_sprint.csv
index adcc379f3..9568cddec 100644
--- a/backend/plugins/zentao/e2e/snapshot_tables/execution_sprint.csv
+++ b/backend/plugins/zentao/e2e/snapshot_tables/execution_sprint.csv
@@ -1,9 +1,9 @@
-id,name,url,status,started_date,ended_date,completed_date,original_board_id
-zentao:ZentaoExecution:1:133,为长期项目创建一个迭代,https://zentaomax.demo.qucheng.cc/execution-view-133.html,,2023-08-28T00:00:00.000+00:00,2023-09-08T00:00:00.000+00:00,,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:193,为长期项目创建一个迭代,https://zentaomax.demo.qucheng.cc/execution-view-193.html,ACTIVE,2023-08-28T00:00:00.000+00:00,2023-09-08T00:00:00.000+00:00,,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:194,新建一个迭代，完成之后检查sprints表,https://zentaomax.demo.qucheng.cc/execution-view-194.html,CLOSED,2023-08-29T00:00:00.000+00:00,2023-09-05T00:00:00.000+00:00,2023-08-29T08:04:13.000+00:00,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:266,新建迭代然后关闭,https://zentaomax.demo.qucheng.cc/execution-view-266.html,CLOSED,2023-09-07T00:00:00.000+00:00,2023-09-13T00:00:00.000+00:00,2023-09-07T02:15:31.000+00:00,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:267,建一个计划未来开始迭代,https://zentaomax.demo.qucheng.cc/execution-view-267.html,FUTURE,2023-09-09T00:00:00.000+00:00,2023-09-22T00:00:00.000+00:00,,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:268,起始-截止都是结束时间,https://zentaomax.demo.qucheng.cc/execution-view-268.html,FUTURE,2023-09-04T00:00:00.000+00:00,2023-09-05T00:00:00.000+00:00,,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:269,把迭代挂起,https://zentaomax.demo.qucheng.cc/execution-view-269.html,SUSPENDED,2023-09-07T00:00:00.000+00:00,2023-09-20T00:00:00.000+00:00,,zentao:ZentaoProject:1:192
-zentao:ZentaoExecution:1:270,流程扭转--挂起迭代2,https://zentaomax.demo.qucheng.cc/execution-view-270.html,SUSPENDED,2023-09-07T00:00:00.000+00:00,2023-09-20T00:00:00.000+00:00,,zentao:ZentaoProject:1:192
+id,name,url,status,started_date,ended_date,completed_date,original_board_id,committed_story_point,completed_story_point
+zentao:ZentaoExecution:1:133,为长期项目创建一个迭代,https://zentaomax.demo.qucheng.cc/execution-view-133.html,,2023-08-28T00:00:00.000+00:00,2023-09-08T00:00:00.000+00:00,,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:193,为长期项目创建一个迭代,https://zentaomax.demo.qucheng.cc/execution-view-193.html,ACTIVE,2023-08-28T00:00:00.000+00:00,2023-09-08T00:00:00.000+00:00,,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:194,新建一个迭代，完成之后检查sprints表,https://zentaomax.demo.qucheng.cc/execution-view-194.html,CLOSED,2023-08-29T00:00:00.000+00:00,2023-09-05T00:00:00.000+00:00,2023-08-29T08:04:13.000+00:00,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:266,新建迭代然后关闭,https://zentaomax.demo.qucheng.cc/execution-view-266.html,CLOSED,2023-09-07T00:00:00.000+00:00,2023-09-13T00:00:00.000+00:00,2023-09-07T02:15:31.000+00:00,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:267,建一个计划未来开始迭代,https://zentaomax.demo.qucheng.cc/execution-view-267.html,FUTURE,2023-09-09T00:00:00.000+00:00,2023-09-22T00:00:00.000+00:00,,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:268,起始-截止都是结束时间,https://zentaomax.demo.qucheng.cc/execution-view-268.html,FUTURE,2023-09-04T00:00:00.000+00:00,2023-09-05T00:00:00.000+00:00,,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:269,把迭代挂起,https://zentaomax.demo.qucheng.cc/execution-view-269.html,SUSPENDED,2023-09-07T00:00:00.000+00:00,2023-09-20T00:00:00.000+00:00,,zentao:ZentaoProject:1:192,,
+zentao:ZentaoExecution:1:270,流程扭转--挂起迭代2,https://zentaomax.demo.qucheng.cc/execution-view-270.html,SUSPENDED,2023-09-07T00:00:00.000+00:00,2023-09-20T00:00:00.000+00:00,,zentao:ZentaoProject:1:192,,
\ No newline at end of file
diff --git a/grafana/dashboards/mysql/Jira.json
b/grafana/dashboards/mysql/Jira.json
index ad663e498..62efb8c7a 100644
--- a/grafana/dashboards/mysql/Jira.json
+++ b/grafana/dashboards/mysql/Jira.json
@@ -1145,16 +1145,164 @@
"type": "barchart"
},
{
+ "collapsed": false,
"datasource": {
"type": "datasource",
"uid": "grafana"
},
"gridPos": {
- "h": 2,
+ "h": 1,
"w": 24,
"x": 0,
"y": 29
},
+ "id": 200,
+ "panels": [],
+ "targets": [
+ {
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "refId": "A"
+ }
+ ],
+ "title": "3. Sprint Velocity (Committed vs Completed, Closed Sprints)",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "devlake-mysql-api"
+ },
+ "description": "Committed = sum of each issue's start-of-sprint estimate
for issues that were part of the sprint when it began. Completed = sum of each
issue's end-of-sprint estimate for issues Jira's Sprint Report marked done at
sprint close. Requires the Jira Sprint Report collector to have run; sprints
collected before that will show no bars here.",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "Story Points",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ },
+ "unit": "short"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 24,
+ "x": 0,
+ "y": 30
+ },
+ "id": 201,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "Jira Plugin Docs",
+ "url": "https://devlake.apache.org/docs/Plugins/jira";
+ }
+ ],
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.7,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "none",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "maxHeight": 600,
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "13.0.2",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "devlake-mysql-api"
+ },
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\n s.name AS sprint,\n s.committed_story_point AS
'Committed',\n s.completed_story_point AS 'Completed'\nFROM sprints s\n JOIN
board_sprints bs ON bs.sprint_id = s.id\nWHERE\n bs.board_id in
(${board_id})\n AND s.committed_story_point is not null\nORDER BY
s.completed_date asc",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "sprints",
+ "timeColumn": "completed_date",
+ "timeColumnType": "timestamp",
+ "where": []
+ }
+ ],
+ "title": "Sprint Velocity: Committed vs Completed Story Points [Closed
Sprints]",
+ "type": "barchart"
+ },
+ {
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "gridPos": {
+ "h": 2,
+ "w": 24,
+ "x": 0,
+ "y": 38
+ },
"id": 130,
"options": {
"code": {
diff --git a/grafana/dashboards/postgresql/Jira.json
b/grafana/dashboards/postgresql/Jira.json
index 1b200dbb2..759315954 100644
--- a/grafana/dashboards/postgresql/Jira.json
+++ b/grafana/dashboards/postgresql/Jira.json
@@ -1145,16 +1145,166 @@
"type": "barchart"
},
{
+ "collapsed": false,
"datasource": {
"type": "datasource",
"uid": "grafana"
},
"gridPos": {
- "h": 2,
+ "h": 1,
"w": 24,
"x": 0,
"y": 29
},
+ "id": 200,
+ "panels": [],
+ "targets": [
+ {
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "refId": "A"
+ }
+ ],
+ "title": "3. Sprint Velocity (Committed vs Completed, Closed Sprints)",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "grafana-postgresql-datasource",
+ "uid": "devlake-postgres-api"
+ },
+ "description": "Committed = sum of each issue's start-of-sprint estimate
for issues that were part of the sprint when it began. Completed = sum of each
issue's end-of-sprint estimate for issues Jira's Sprint Report marked done at
sprint close. Requires the Jira Sprint Report collector to have run; sprints
collected before that will show no bars here.",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "Story Points",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ },
+ "unit": "short"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 24,
+ "x": 0,
+ "y": 30
+ },
+ "id": 201,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "Jira Plugin Docs",
+ "url": "https://devlake.apache.org/docs/Plugins/jira";
+ }
+ ],
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.7,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "none",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "maxHeight": 600,
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "13.0.2",
+ "targets": [
+ {
+ "datasource": {
+ "type": "grafana-postgresql-datasource",
+ "uid": "devlake-postgres-api"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT s.name AS sprint, s.committed_story_point AS
\"Committed\", s.completed_story_point AS \"Completed\" FROM sprints AS s JOIN
board_sprints AS bs ON bs.sprint_id = s.id WHERE ('${board_id:csv}' = '' OR
bs.board_id::text = ANY(ARRAY[${board_id:singlequote}]::text[])) AND
s.committed_story_point IS NOT NULL ORDER BY s.completed_date ASC NULLS FIRST",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Sprint Velocity: Committed vs Completed Story Points [Closed
Sprints]",
+ "type": "barchart"
+ },
+ {
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "gridPos": {
+ "h": 2,
+ "w": 24,
+ "x": 0,
+ "y": 38
+ },
"id": 130,
"options": {
"code": {
