Upload lifecycle: coordinate PUT, completion, and R2 cleanup with per-key leases

[tiankaima]

Problem

UploadPending currently coordinates quota in PostgreSQL, while object PUT/delete happens in R2 outside the database transaction. The request paths have no immutable per-key claim or lease spanning those two systems.

Two concrete interleavings were found while reviewing #570:

1. An upload completion can enter its serializable transaction before expiry while a stale-session sweep observes the row after expiry, deletes the R2 object, and then loses its deleteMany race. The completion can commit Upload metadata for an object that no longer exists.
2. A PUT can validate an active pending row, remain in flight while expiry/completion/cleanup removes the row/object, and then finish late, recreating an R2 object with no owning UploadPending or Upload row. Concurrent/repeated PUTs can also overwrite an object after completion measured its metadata.

PostgreSQL serializable isolation alone cannot roll back or order an R2 side effect.

Current safety decision

#570 should not add opportunistic R2 cleanup to ordinary create/complete requests. Until a proven protocol exists, preserve data integrity and accept that expired objects may require later garbage collection; do not risk deleting an object referenced by committed metadata.

Proposed direction

Use a per-key state/claim protocol, for example reserved -> uploading -> uploaded -> completing -> cleaning, with an immutable attempt ID and bounded lease expiry, or an equivalent protocol with the same guarantees. The exact schema is open to design, but it must coordinate:

• authenticated raw PUT;
• upload completion and idempotent retries;
• expiry/quota cleanup;
• explicit upload deletion;
• worker/process failure and lease recovery.

Cleanup should run as its own bounded lifecycle job, not piggyback on unrelated auth cleanup or static importer workflows.

Acceptance criteria

• Only one active PUT attempt can own a pending key, or concurrent attempts are otherwise proven safe.
• Completion accepts only a fully published object owned by the same pending generation.
• A cleanup worker atomically claims a key before touching R2; a failed claim never deletes the object.
• A late PUT cannot recreate an untracked object after cleanup, and cannot overwrite a completed upload.
• A successful completion can never commit metadata for an object concurrently removed by cleanup.
• R2 deletion failure remains retryable without losing ownership state.
• Expired/stuck leases recover with bounded batches and deterministic ordering.
• Deterministic PostgreSQL + controllable-R2 tests cover the PUT/complete/cleanup interleavings above, including process failure between DB and R2 steps.
• Metrics/audit distinguish claimed, retried, skipped, failed, and completed cleanup work.
• The workflow is separate from auth-record cleanup and static import/sync.

Related

• feat(graphql): complete ordinary mutation parity #570 — GraphQL/MCP mutation parity; should keep upload byte transfer outside GraphQL/MCP.
• feat(graphql): close mutation parity and expose registered operations through MCP #549 — GraphQL/MCP parity scope; this lifecycle protocol is a follow-up rather than a prerequisite for exposing metadata mutations.