address concurrent-edit / asset-cleanup race condition in S3 orphan deletion

Summary

During review of PR #1258 (S3 key prefix support and auto-delete orphaned assets), a potential race condition was identified in the orphaned asset cleanup flow inside apps/api/src/storage/cleanup-assets.ts.

Problem

The current deleteOrphanedAssets implementation uses isAssetReferencedElsewhere() to check at a single point in time whether an asset is still referenced, then hard-deletes the S3 object and DB row. This is a specific manifestation of the broader last-write-wins problem for concurrent task edits:

1. Asset is found to be unreferenced at check time.
2. A concurrent edit by another user re-adds the same asset to the content.
3. The cleanup proceeds to delete the S3 object, leaving live content pointing at a missing object.

Context

This race condition is out of scope for PR #1258 because resolving it properly requires a larger architectural change (e.g., optimistic locking, versioning, mark-and-sweep/background GC, or a reservation + re-check pattern). The last-write-wins problem for concurrent edits already exists independently of this PR.

Suggested Approach

• Introduce a mark-and-sweep / background GC style flow for asset cleanup.
• Or add a pendingDeletion flag to assetTable, mark candidates, re-check references, and only hard-delete assets that are still unreferenced and still marked pending.
• Consider addressing the broader concurrent-edit / optimistic locking problem at the same time.

References

• PR: https://github.com/usekaneo/kaneo/pull/1258
• Review comment: https://github.com/usekaneo/kaneo/pull/1258#discussion_r3224196612

Reported by @tiran133

---

Originally requested by @app/coderabbitai on 2026-05-12. Original GitHub request #1261.