prebuilds: failure hard limit does not engage while the newest build is still in flight #30058

Summary

GetPresetsAtFailureLimit requires the newest failure_hard_limit start builds of a preset to all be failed, but it ranks builds of every status. A pending or running build takes one of those slots without being counted, so the count cannot reach the limit while a build is in flight. The reconciler reads this once per cycle and then creates prebuilds one at a time, so when a create takes about as long as a failing build, every snapshot contains an in-flight build and the preset is never hard-limited.

Where

• enterprise/coderd/prebuilds/reconcile.go#L556 (SnapshotState) reads all inputs once per cycle in one transaction, including GetPresetsAtFailureLimit (L604).
• coderd/database/queries/prebuilds.sql#L225-L228: WHERE tsb.rn <= @hard_limit AND tsb.job_status = 'failed' ... HAVING COUNT(*) = @hard_limit. An in-flight build among the newest N is ranked, then filtered out, so COUNT stays below the limit.
• enterprise/coderd/prebuilds/reconcile.go#L819-L834 checks IsHardLimited once from the snapshot, then creates prebuilds in a sequential loop without re-reading state.

Steps to reproduce

The query alone shows it. For one preset on the active template version, create prebuild workspaces whose latest start builds are, newest first:

1. pending (or running)
2. failed
3. failed
4. failed

Run GetPresetsAtFailureLimit with hard_limit = 3. It returns no row for the preset, although the 3 builds before the in-flight one all failed. Without the in-flight build, it returns the preset.

In a live deployment, a template whose failing build takes longer than the 60s reconcile interval (for example command = "sleep 90; exit 1") keeps a build in flight at every snapshot.

What we observed

On v2.35.7 with failure_hard_limit = 3 and reconciliation_interval = 60s, each prebuild create took about 28s and each failing build about 37s. Over 83 consecutive reconcile cycles the newest 3 builds were never all failed at snapshot time, and the preset produced 156 failed builds in about 2 hours instead of stopping after 3. It stopped only when one build happened to fail in 18s and finished before the next snapshot. Backoff did not apply either; see #30057.

Expected

An in-flight build does not stop the hard limit from engaging. For example, the query could rank only finished builds.

Related

• Limit prebuilt workspace failure cost #17432: failed prebuilds are kept on purpose as evidence, so the backoff and the hard limit are the only safeguards against a failure loop.
• fix(enterprise/coderd/prebuilds): hard-limit preset if all create attempts failed #18551, WIP feat: support retries/hard failure limit for prebuilds in reconcile prior to provisioner #21326: related hard-limit work, but neither covers in-flight builds.

Version

Seen on v2.35.7. The query is unchanged on main (d40e24cf7d).