[Refactor][Change Lead Time Calculator] Use batch strategy for change lead time calculation queries

[JWShuff]

What and why to refactor

The current CalculateChangeLeadTime implementation in backend/plugins/dora/tasks/change_lead_time_calculator.go suffers from an N+1 query problem. For every pull request in a project's scope, three sequential database queries are executed:

1. getFirstCommit(pr.Id, db) fetches the earliest commit for the PR
2. getFirstReview(pr.Id, pr.AuthorId, db) fetches the first non-author review comment
3. getDeploymentCommit(pr.MergeCommitSha, projectName, db) fetches the deployment associated with the merge commit

For a project with N pull requests, this results in 3N + 1 database round-trips. In our environment, this drives change lead time calculation into the 15+ hour range across our applications, making the DORA metrics pipeline a significant bottleneck.

Describe the solution you'd like

Replace the per-PR queries with three upfront batch queries that return maps keyed by PR ID (or merge commit SHA for deployments), enabling O(1) lookups during the per-PR iteration loop. Specifically:

1. batchFetchFirstCommits(projectName, db) Uses a subquery with MIN(commit_authored_date) grouped by pull_request_id, joined back to pull_request_commits to retrieve full commit records. Scoped to the project via project_mapping. Returns map[string]*code.PullRequestCommit.
2. batchFetchFirstReviews(projectName, db) Uses a subquery with MIN(created_date) grouped by pull_request_id (excluding the PR author's own comments), joined back to pull_request_comments. Returns map[string]*code.PullRequestComment.
3. batchFetchDeployments(projectName, db) Queries cicd_deployment_commits joined with commits_diffs to map merge commit SHAs to their first successful production deployment. Returns map[string]*devops.CicdDeploymentCommit.

This looks like:

// Before (3 queries per PR):
firstCommit, err := getFirstCommit(pr.Id, db)
firstReview, err := getFirstReview(pr.Id, pr.AuthorId, db)
deployment, err := getDeploymentCommit(pr.MergeCommitSha, projectName, db)

// After (map lookups):
firstCommit := firstCommitsMap[pr.Id]
firstReview := firstReviewsMap[pr.Id]
deployment := deploymentsMap[pr.MergeCommitSha]

This reduces the query pattern from 3N + 1 to a constant 4 queries regardless of PR count. The existing calculation logic (coding time, pickup time, review time, deploy time) is unchanged; only the data fetching strategy changes. I'm not as familiar with the testing coverage in this part of the application, but that the tests still pass with this implementation gives me hope.

The implementation also adds timing logs for observability, we can remove if it is preferred.

Related issues

Issue #8361 from previous discoveries as we've extended the usage of this tool.

Additional context

Our implementation is available at PR #8714. The change is isolated to a single file (change_lead_time_calculator.go) with no changes to the data model, API surface, or calculation logic. The batch query SQL mirrors the existing per-PR query logic exactly; it's the same filtering and join conditions, just grouped and executed once rather than per-row.

[klesh]

Well done

[JWShuff]

This is all Ankesh, but I find the issue -> PR connection helps to sketch out the case. I tagged myself in after talking with @AnkeshThakur and appreciate the support!