Recover interrupted checkpoint operations and allow failed checkpoint cleanup

[EItanya]

Follow-up from the review of #2742, separate from revision GC scheduling in #2744.

A checkpoint that reaches FAILED still retains prepared_revision, but BeginDeleteAgentInstanceCheckpoint only accepts READY and DELETING. The public delete path therefore reports NotFound for the failed record instead of cleaning it up. Retiring the template and deleting its source instance cannot release that revision.

An interrupted CREATING reservation is more disruptive: it retains the revision and blocks task admission and source-instance lifecycle changes. Retrying the original create request can resume it, but there is no automatic recovery when the caller disappears. A failed compensation can also leave the reservation in CREATING.

Implement recovery in the checkpoint lifecycle owner:

• Allow failed checkpoints to be deleted through the public API, including the case where tag creation never succeeded or compensation already removed the tag.
• Resume or compensate abandoned creation and deletion after restart. Coordinate recovery with in-flight create requests so a slow, live tag copy cannot be reaped solely because it is old.
• Keep revision/context references until the lifecycle owner has established that cleanup is complete. Revision GC must continue to count every checkpoint row; filtering out FAILED or CREATING would bypass the safety invariant.
• Preserve request idempotency, tag identity checks, and fork-reference protection. Do not hold database locks across Substrate calls.

Acceptance coverage: PostgreSQL and real Substrate tests for failed tag creation, failed compensation, lost responses, and restart between reservation/tag creation/finalization; deleting a failed checkpoint must release its references, abandoned reservations must stop blocking their source instance, and a concurrent live create or fork must remain safe.