max / makenotwork
| 1 | -- Enforce one gallery row per S3 object (STOR-S1, ultra-fuzz Run #23). |
| 2 | -- |
| 3 | -- `gallery_confirm` had no idempotency guard: a replayed confirm (the classic |
| 4 | -- lost-response retry — first confirm committed, client never saw the 200) would |
| 5 | -- insert a second `item_images`/`project_images` row for the SAME `s3_key` and |
| 6 | -- charge storage a second time. The drift is not self-healing: the weekly |
| 7 | -- `recalculate_all_storage_batch` SUMs `file_size_bytes` over the rows, so two |
| 8 | -- rows for one object bake the overcharge into reconciliation. |
| 9 | -- |
| 10 | -- `s3_key` already carries a per-image UUID under the entity's gallery prefix, so |
| 11 | -- it is globally unique by construction — one object, one row. De-duplicate any |
| 12 | -- rows that already share a key (keep the physically-earliest), then enforce it. |
| 13 | -- The surviving row keeps the S3 object live (the `S3_KEY_REFS` registry still |
| 14 | -- sees it), so dropping the duplicate orphans nothing and the next storage |
| 15 | -- recalc self-corrects the prior double-charge. |
| 16 | |
| 17 | DELETE FROM item_images a USING item_images b |
| 18 | WHERE a.s3_key = b.s3_key AND a.ctid > b.ctid; |
| 19 | DELETE FROM project_images a USING project_images b |
| 20 | WHERE a.s3_key = b.s3_key AND a.ctid > b.ctid; |
| 21 | |
| 22 | NOT EXISTS idx_item_images_s3_key ON item_images (s3_key); |
| 23 | NOT EXISTS idx_project_images_s3_key ON project_images (s3_key); |
| 24 |