Reconcile orphaned S3 artifact blobs and add eager existence check for artifact downloads

[coderabbitai]

Summary

Follow-up from PR #300 review discussion (#300 (comment)).

Two related gaps in the S3-backed artifact storage path (src/aiq_agent/agents/deep_researcher/sandbox/artifacts/blob_store.py, src/aiq_agent/agents/deep_researcher/sandbox/artifacts/store.py):

1. Lazy S3 read in S3ArtifactBlobStore.open_bytes: _client.get_object is called inside the generator, so a missing/stale S3 object only surfaces as an error after StreamingResponse has already sent headers, producing a truncated 200 instead of a clean 404.
2. Partial-failure reconciliation: If the S3 upload succeeds but the process crashes/fails before the SQL metadata insert commits (or before status flips to AVAILABLE), the artifact can be left in an inconsistent state (S3 object exists but SQL row missing or stuck in a non-terminal status), leading to an orphaned S3 blob with no cleanup path.

Proposed changes

• Introduce a PENDING artifact status that is set before the S3 upload begins, and only transition to AVAILABLE after both the S3 write and the SQL metadata commit succeed.
• Add a background reconciliation/retry job that periodically scans for artifacts stuck in PENDING beyond a threshold, and either:
  • Completes the SQL metadata write if the S3 object is confirmed present (retry path), or
  • Deletes the orphaned S3 object and marks/removes the metadata row if it cannot be completed (cleanup path).
• Make S3ArtifactBlobStore.open_bytes (or its caller) perform the get_object/existence check eagerly, before constructing the StreamingResponse, so missing objects surface as a proper 404 rather than a truncated stream.

Affected areas

• src/aiq_agent/agents/deep_researcher/sandbox/artifacts/blob_store.py (S3ArtifactBlobStore.open_bytes, put)
• src/aiq_agent/agents/deep_researcher/sandbox/artifacts/store.py (SqlArtifactStore.put, artifact status lifecycle, cleanup_old_artifacts)
• frontends/aiq_api/src/aiq_api/routes/jobs.py (get_job_artifact_content)

Acceptance criteria

• Artifacts have a PENDING status set prior to S3 upload and only reach AVAILABLE after both S3 and SQL steps succeed.
• A retry/reconciliation job detects artifacts stuck in PENDING past a configurable threshold and either completes or cleans them up (including deleting orphaned S3 objects).
• Requesting artifact content for a missing/stale S3 object returns a 404 (or equivalent) before any response body bytes are streamed, not a truncated 200.
• Tests cover: stuck-pending detection, retry completion, orphan cleanup, and eager missing-object error surfacing.

References

• PR: feat(artifacts): add S3-compatible blob storage #300
• Discussion: feat(artifacts): add S3-compatible blob storage #300 (comment)
• Requested by: @soumilinandi