@@ -125,6 +125,20 @@ WHERE tsb.rn <= tsb.desired_instances -- Fetch the last N builds, where N is the
125125AND created_at>= @lookback::timestamptz
126126GROUP BY tsb .template_version_id ,tsb .preset_id ,fc .num_failed ;
127127
128+ -- GetPresetsAtFailureLimit groups workspace builds by preset ID.
129+ -- Each preset is associated with exactly one template version ID.
130+ -- For each preset, the query checks last hard_limit builds.
131+ -- If all of them failed, it's considered a permanent error - stop retrying.
132+ -- Query returns a list of preset IDs for which hard limit is hit.
133+ -- Only active template versions with configured presets are considered.
134+ -- TODO: remove?
135+ -- We also return the number of failed workspace builds that occurred during the lookback period.
136+ --
137+ -- NOTE:
138+ -- - To **decide whether to back off**, we look at up to the N most recent builds (within the defined lookback period).
139+ -- - To **calculate the number of failed builds**, we consider all builds within the defined lookback period.
140+ --
141+ -- The number of failed builds is used downstream to determine the backoff duration.
128142-- name: GetPresetsAtFailureLimit :many
129143WITH filtered_buildsAS (
130144-- Only select builds which are for prebuild creations
@@ -149,7 +163,7 @@ SELECT
149163tsb .preset_id
150164FROM time_sorted_builds tsb
151165-- Fetch the last hard_limit builds for each preset.
152- -- If all of them failed, it's considered a permanent error— stop retrying.
166+ -- If all of them failed, it's considered a permanent error- stop retrying.
153167WHERE tsb .rn <= @hard_limit::bigint
154168AND tsb .job_status = ' failed'
155169GROUP BY tsb .template_version_id ,tsb .preset_id