prebuilds: backoff never applies to presets whose instance count comes only from a schedule #30057

Summary

GetPresetsBackoff limits its build window with rn <= desired_instances, and desired_instances holds the preset's base instances. A preset that sets instances = 0 and gets its count only from scheduling therefore never produces a backoff row, and failing prebuilds are retried on every reconcile cycle regardless of reconciliation_backoff_interval.

Where

• coderd/database/queries/prebuilds.sql#L189: WHERE tsb.rn <= tsb.desired_instances. With desired_instances = 0, no row passes.
• coderd/provisionerdserver/provisionerdserver.go#L2760 stores the base instances as desired_instances. Scheduled counts go to template_version_preset_prebuild_schedules (L2795), which the backoff query does not read.
• coderd/prebuilds/preset_snapshot.go#L423 (needsBackoffPeriod) returns early when there is no backoff row. CalculateDesiredInstances applies the schedule, but only to the create and delete counts.

Steps to reproduce

1. Push a template whose preset count comes only from a schedule, and whose builds fail:

   data "coder_workspace_preset" "p" {
     name = "p"
     prebuilds {
       instances = 0
       scheduling {
         timezone = "UTC"
         schedule {
           cron      = "* * * * *"
           instances = 2
         }
       }
     }
   }
   
   resource "terraform_data" "fail" {
     count = data.coder_workspace.me.start_count
     provisioner "local-exec" {
       command = "exit 1"
     }
   }

2. Watch the reconciler.

The query alone shows it: a preset with desired_instances = 0, one template_version_preset_prebuild_schedules row with desired_instances = 2, and one failed prebuild build make GetPresetsBackoff return no rows. The same build on a preset with desired_instances = 1 returns one row.

Expected

After a failure, the preset backs off by reconciliation_backoff_interval per failure, the same as a preset with instances = 2.

Actual

No backoff. On v2.35.7, one such preset produced 156 failed prebuild builds in about 2 hours. The failure hard limit did not stop it either; see #30058.

Version

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