A dedicated IDLE Processing State for Idle Retries, handled like RE_REQUESTED

GStraccini-bot updates a pull request’s branch only when it processes that pull request, and it moves on to another open pull request in the same repository only when one closes (checkForOtherPullRequests). Pull requests can therefore sit outdated, waiting for a branch update that never starts. Each hour, the idle-pull-requests Worker gives the first due Idle Pull Request an Idle Retry (github_pull_requests_idle_view, in its round-robin order across repositories). It marks that pull request with a new ProcessingState, IDLE, and doesn’t reuse RE_REQUESTED. IDLE exists so that a time-based Idle Retry can be told apart from an Event-driven re-request in the database, the processing counts, and the dashboards. The bot deliberately handles it exactly like RE_REQUESTED: IDLE sits in the same claim list, and the pull request handler doesn’t read the state. Don’t “simplify” it away as redundant, and don’t give it its own bot logic without revisiting this decision.

Consequences

• IDLE is a value of github_pull_requests.ProcessingState only. Other github_* tables keep their enums, even though the bot’s claim list containing IDLE is shared across all of them.
• The webhooks schema owns the enum (Sql/0038). Every hardcoded list of states has to include IDLE as a pending state, or idle pull requests silently vanish from counts:
  • webhooks: processing-state.php, openapi.json
  • projects-monitor: badge, state lists, presets
• Linear backoff with a cap: github_pull_requests.IdleRetryCount (default 0) increases by 1 on every Idle Retry. A PROCESSED open pull request is due once UpdatedAt <= NOW() - INTERVAL (IdleRetryCount + 1) DAY, and only while IdleRetryCount < 10. That gives 10 retries over about 55 idle days. The step, base and cap are literals in the view.
• The view shows every Idle Pull Request, not just the due ones: every open PROCESSED pull request idle for at least 1 day, each with an IdleStatus:
  • DUE: the worker takes the first of these
  • WAITING: inside its backoff window
  • GAVE_UP: at the cap

  Dashboards can therefore show retries that are pending and pull requests the automation gave up on. Since Sql/0039 the view also joins github_repositories (most recent row per owner/name) to expose RepositoryPrivate. It is a left join (Sql/0040): a repository with no row there yields NULL, and the pull request stays an Idle Pull Request. The inner join Sql/0039 started with dropped most of them, since most repositories predate the rows this service writes.

• Round-robin across repositories (Sql/0042): the view is ordered by RepositoryIdleRank (the pull request’s position among its repository’s idle ones: oldest DUE first, then WAITING, then GAVE_UP), then RepositoryLastPullRequestUpdate (the most recent UpdatedAt among all of the repository’s pull requests), then UpdatedAt. Ordered by UpdatedAt alone, the repository with the oldest pull requests got every Idle Retry in a row; now each repository’s first pull request is listed before any repository’s second, and the repository that has been quiet the longest goes first. Once the bot processes an Idle Retry, that repository’s last update jumps to that moment and it moves to the back of the line. The activity key is deliberately any pull request update, not only Idle Retries: a repository with daily activity is already being moved along by the normal workflow (a closing pull request re-requests the next one), so it waits; a pull request stuck in the middle of that chain is a manual check. The value is derived from github_pull_requests rather than stored on github_repositories, which has no unique key per repository and no row at all for most of them.
• Least retried first (Sql/0043): IdleStatus (DUE, WAITING, GAVE_UP) and then IdleRetryCount come before the round-robin keys, and IdleRetryCount comes before age inside RepositoryIdleRank. Ranked by age alone, a retried pull request won: its longer backoff made it older than the never-retried ones next to it by the time it was DUE again, so it got its third Idle Retry while they waited for their first. Now every DUE pull request with 0 retries comes before any with 1, and so on, round-robin inside each count. A much-retried pull request can wait while newly idle ones keep arriving; that is the intent — a first nudge for everyone before a second for anyone.
• Reset on real activity: every Event-driven write sets IdleRetryCount = 0. That includes edited, and the repository-wide writes for branch protection rules and rulesets. The bot’s own RE_REQUESTED in upsertPullRequest, the next-pull-request chain, resets it too. The reset is an explicit column in each writer’s UPDATE. A trigger would cover future writers automatically, but it can’t be SQL SECURITY INVOKER (see ADR-0001) and it’s invisible when reading the PHP.
• The worker doesn’t bump UpdatedAt (it writes UpdatedAt = UpdatedAt), so the pull request stays near the front of the bot’s ORDER BY UpdatedAt ASC claim queue. Leaving PROCESSED is what takes it out of the due set. When the bot finishes it, UpdatedAt updates automatically, which starts the next, longer wait.
• Rollout order: webhooks first, then gstraccini-bot-service.
  • The bot’s reset writes IdleRetryCount, so it can’t deploy before migration 0038 adds that column. upsertPullRequest would hit die() on the unknown column.
  • The reverse window is harmless: IDLE rows, at most one per hour, wait unclaimed until the bot learns the state.
  • Inside webhooks’ own deploy, the worker can run before 0038. MariaDB’s strict sql_mode makes that write fail, and the worker retries on restart. Under a non-strict mode, a stranded row would show up as ProcessingState = '', so check for that after the deploy.