zahorniak opened a new issue, #9304: URL: https://github.com/apache/devlake/issues/9304
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/devlake/issues?q=is%3Aissue) and found no similar issues. ### What happened The CircleCI plugin collects the same jobs and workflows again on every incremental run, without a limit. The raw tables `_raw_circleci_api_jobs` and `_raw_circleci_api_workflows` grow every day, and the `circleci` task gets slower every day. Both collectors have a "collect unfinished details" step: | Subtask | Input query | Statuses | | --- | --- | --- | | `collectJobs` | `UnfinishedJobsInputClauses` (`backend/plugins/circleci/tasks/job_collector.go`) | `running`, `not_running`, `queued`, `on_hold` | | `collectWorkflows` | `CollectUnfinishedDetails.BuildInputIterator` (`backend/plugins/circleci/tasks/workflow_collector.go`) | `running`, `on_hold`, `failing` | These queries have no age limit. A CircleCI approval job (`type: approval`) that nobody approves stays `on_hold` for ever, and its workflow stays `on_hold` too. So DevLake collects all jobs of such a workflow again on every run, for ever. The set of these workflows only grows. The step runs with `Incremental: true` (`helpers/pluginhelper/api/api_collector_stateful.go`), so the collector only adds rows to the raw table. The old rows for the same jobs stay. ## Environment | | | | --- | --- | | DevLake version | **v1.0.3-beta14** (the code is the same on `main` at `351d86738`) | | Plugin | `circleci` | | Database | MySQL 8.4 | | CircleCI | CircleCI Cloud (`circleci.com` API v2) | | Trigger | Nightly blueprint (incremental, `skipCollectors=false`), 2 CircleCI projects | ## Data from our instance - 14,356 workflows are `on_hold` and 810 are `failing`. They were created from February to November 2026. The jobs that stay `on_hold` are approval jobs that nobody approved (for example `hold-deploy`, `hold-tests`, `start-development`). - One nightly run added 166,536 rows to `_raw_circleci_api_jobs`: | Source of the raw rows | Rows | Workflows | | --- | --- | --- | | "unfinished details" step | 166,508 | 14,866 | | new workflows | 28 | 4 | - `_raw_circleci_api_jobs` has about 9.3M rows (10.3 GB) for about 248k jobs. It grows by about 160k rows per day, also on weekends. - `extractJobs` reads the full raw table on every run (see the related refactor issue), so the `circleci` task takes 3–8 hours per run. ### What do you expect to happen DevLake should collect an unfinished workflow again only for a limited time after it was created. The raw tables should grow only with new CI activity. ### How to reproduce 1. Use a CircleCI project with a workflow that has an approval job (`type: approval`). 2. Trigger the workflow and do not approve the job. 3. Add the project to a blueprint and run the blueprint (incremental) several times. 4. Each run adds all jobs of that workflow to the raw table again: ```sql SELECT url, COUNT(*) FROM _raw_circleci_api_jobs GROUP BY url ORDER BY 2 DESC LIMIT 10; -- the same /v2/workflow/<id>/job URL appears once per run ``` ### Anything else **Proposed fix (plan of attack)** In `UnfinishedJobsInputClauses` and in the workflow collector's unfinished-details iterator, select only workflows created in a lookback window: ```go dal.Where("... AND created_date >= ?", time.Now().AddDate(0, 0, -lookbackDays)) ``` - Compute the date in Go, so the query works on MySQL and PostgreSQL. - Use a default of 30 days. It can be an option if maintainers prefer. - For the job query, join the workflow table (or use the job's workflow `created_date`) to apply the window. Data that supports a 30-day window: in our instance, 99.6% of approved workflows got their approval within 14 days. All approvals except 1 of about 1,860 came within 30 days. **Rejected alternatives** - Do not treat `on_hold` as a final status. A workflow that gets its approval later would then never be collected again, so its deploy jobs after the approval would be missing from DORA deployments. - Delete the previous raw rows of the same input before collecting it again. This needs a change in the framework collector and is more complex. I can start work on a PR for this if the DevLake maintainers approve this approach. ### Version v1.0.3-beta14 ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
