Reliability: Make photo-submission promotion blob upload and DB write consistent

[richardorchard]

Parent

Part of epic #580 (Architecture review 2026-08).

Priority

P2

Problem

PhotoSubmissionPromotionService uploads gallery blobs before the DB transaction that inserts PIC_FILES_T and updates the submission. If the DB commit fails after upload, orphan blobs remain in storage with no compensating delete.

Proposed solutions

Option A — Compensating delete (pragmatic)

• On DB failure after upload, delete the uploaded blob(s) (best-effort + log).
• Still a crash window between upload success and compensate, but much smaller operational residue.

Option B — DB-first with pending blob path

• Insert DB row pointing at a pending path / mark, upload, then finalize — or upload to a staging prefix and rename/copy after commit.
• Cleaner; more code.

Option C — Periodic orphan GC

• Independent sweeper for unreferenced blobs (good complement to A/B; not sufficient alone).

Needs decision?

Mild — recommend A + track C unless promotion volume justifies B.

Acceptance criteria

• Failed promotion does not leave durable orphan gallery blobs in the common failure path
• Successful promotion unchanged for public gallery
• Tests cover upload-then-DB-failure compensation (fakes)
• Opt-in content submission probe still passes when run