Add built-in retries for failed automation jobs

[open-inspect]

Context

Follow-up to #561, specifically the latest comment:

the challenge i've found with a retry mechanism (at least if applied at the actual call itself) is that the entire D1 is locked for 20-70 seconds.

i'm open to having a job level retry, which in-theory should at least be able to mitigate some of the issues

Transient infrastructure failures such as the D1 stalls described in #561 can cause automation jobs to fail even though the underlying service recovers shortly afterward. Retrying individual D1 calls is unlikely to help while the entire database remains unavailable, but retrying the automation job after a delay could allow it to recover automatically.

Proposal

Allow automation jobs to have a built-in retry mechanism when a run fails.

• Support configuring the maximum number of retry attempts for an automation job.
• Delay retries long enough to accommodate transient outages such as the observed 20–70 second D1 stalls.
• Track each retry attempt and preserve the final failure state when all attempts are exhausted.
• Avoid retrying jobs that were explicitly cancelled.
• Ensure retries do not produce duplicate concurrent runs for the same scheduled execution.
• Surface retry status and attempt count so users can distinguish a retrying job from a permanently failed job.

Outcome

Automation jobs can recover from transient failures without manual intervention while remaining bounded and observable.

[harshvardhan-khachane]

Hey @ColeMurray I'd like to pick this up, could you please assign it to me?

[harshvardhan-khachane]

Hi @ColeMurray, I've been reading through the scheduler code for this one. Before I write the
migration I wanted to run the approach past you, because it needs changing two indexes on
automation_runs.

Here's what I'm thinking:

When a run fails for a reason that looks transient, I set a retry_at timestamp on it. The tick
that already runs every minute gets one more sweep, which picks up runs whose retry is due and
inserts a new run row for the same target with attempt + 1. The old row gets marked superseded so
the aggregates stop counting it.

I went with a new row per attempt instead of reusing the same row, so each attempt keeps its own
failure_reason. And because the aggregates only look at the live attempt, a retry that succeeds
shows up as completed instead of partial_failed.

For the delay I was going to do 2 min, then 4, then 8, so the first retry lands after the 20-70s
stall you mentioned in #561 instead of in the middle of it.

New columns would be max_retries on automations (default 0, so nothing changes for existing ones
until someone turns it on), and attempt, retry_at, superseded_at on automation_runs. Plus
one partial index on retry_at for the sweep to use.

The part I want your opinion on:

idx_runs_invocation_repo and idx_runs_invocation_environment are unique on (invocation, target),
so right now a second run for the same target can't exist at all. I'd have to drop them and recreate
them with attempt added. Every existing row gets attempt = 1, so I don't think anything would
conflict, but it's still an index rebuild on a live table and after reading #561 I'd rather ask
first.