feat: Implement durable retry logic for failed S3/MinIO deletions

[coderabbitai]

Summary

When a post-commit S3/MinIO deletion fails in AttachmentService.deleteAttachment(), the failure is currently only logged. This means the blob remains orphaned indefinitely with no automated recovery path.

This issue tracks the work required to implement a durable retry mechanism so that no files are left orphaned in MinIO if a post-commit deletion fails.

Background

Discussed in PR #112 (comment: #112 (comment)).

In AttachmentService, the afterCommit() hook attempts to delete the S3 object after the database row has been removed. If that deletion fails, there is currently no mechanism to retry it — the S3 key is lost from the database and the blob becomes a permanent orphan.

Tasks

1. Create OrphanedS3Object Entity

• Create a JPA entity OrphanedS3Object with fields:
  • id (UUID, primary key)
  • s3Key (String, unique, not null)
  • s3Bucket (String, not null)
  • createdAt (Instant, auto-set on persist)
  • retryCount (int, default 0)
  • lastAttemptAt (Instant, nullable)
  • lastError (String, nullable — truncated error message for debugging)

2. Create OrphanedS3ObjectRepository

• Extend JpaRepository<OrphanedS3Object, UUID>
• Add a query method to fetch records eligible for retry (e.g., ordered by lastAttemptAt ascending, with a max retry cap)
• Ensure upsert/save is idempotent (unique constraint on s3Key)

3. Update AttachmentService

• In the afterCommit() catch block (inside TransactionSynchronizationManager.registerSynchronization), call OrphanedS3ObjectRepository.save(new OrphanedS3Object(s3Key, bucketName)) instead of only logging
• The enqueue/save must happen inside the same afterCommit registration so it only fires after a successful DB commit

4. Create a Scheduled Background Worker

• Create a @Component (e.g., OrphanedS3CleanupWorker) annotated with @Scheduled
• Periodically query OrphanedS3ObjectRepository for pending orphaned objects
• For each, attempt fileStorageService.delete(s3Key)
• On success: delete the OrphanedS3Object row
• On failure: increment retryCount, update lastAttemptAt and lastError, save
• Implement a maximum retry cap (e.g., 10 attempts) after which the entry is flagged/alerted rather than retried endlessly

Acceptance Criteria

• OrphanedS3Object entity and migration script created
• OrphanedS3ObjectRepository created with idempotent save
• AttachmentService.afterCommit() enqueues failed deletions to OrphanedS3ObjectRepository instead of only logging
• Background worker retries deletions on a schedule and removes entries on success
• Retry count is bounded; entries exceeding the cap are logged/alerted
• Solution is covered by unit and/or integration tests

References

• Raised by: @johanbriger
• Related PR: 42 feat attachmentservicejava #112