[Bug][jenkins] Incremental collection skips the stages of builds that finish after the next sync #9177

Search before asking

• I had searched in the issues and found no similar issues.

What happened

With incremental collection, collectApiStages never collects the stages of a Jenkins build that started before the previous collection and finished after it. Those builds end up in _tool_jenkins_builds with a result, but with no rows in _raw_jenkins_api_stages / _tool_jenkins_stages, so their deployment stages never become cicd_tasks and the deployments are missing from DORA.

Cause (backend/plugins/jenkins/tasks/stage_collector.go, both the single-job and the multi-branch collectors):

if apiCollector.IsIncremental() && apiCollector.GetSince() != nil {
    clauses = append(clauses, dal.Where(`tjb.start_time >= ?`, apiCollector.GetSince()))
}

The build collectors skip builds that are still running (buildObj["result"] != nil), so a build is stored the first time it is collected finished. For a build that started before since and finished after it, that happens in a later run, and the stage collector filters it out by start_time in that run and in every run after it. Nothing reports it.

This is common in pipelines that wait on an input step, e.g. a "rollback?" or "deploy to production?" approval left open for hours or days after the deploy.

Evidence from our instance (v1.0.3-beta15, 190 multi-branch scopes, daily collection):

For example, front-channel/master#111 (SUCCESS, 148 h) is in _tool_jenkins_builds and has no row in _raw_jenkins_api_stages. The collector is unchanged on main.

What do you expect to happen

An incremental run collects the stages of every build that became available since the previous run, i.e. every build that finished since then, whenever it started. A full and an incremental collection should produce the same stages.

How to reproduce

1. Jenkins scope with incremental collection (any sync after the first).
2. Start a pipeline build that waits on an input step, and let a collection run while it waits.
3. Answer the input after that collection so the build finishes, then run another collection.
4. The build is in _tool_jenkins_builds, but _tool_jenkins_stages has no rows for it.

Anything else

Proposed fix, PR to follow: filter on the finish time, tjb.timestamp + tjb.duration >= since. Both columns are milliseconds, so it is plain arithmetic on MySQL and PostgreSQL, no dialect-specific date functions. It is a superset of the current condition (duration ≥ 0).

Version

v1.0.3-beta15 (collector unchanged on main)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct