max / makenotwork
| 1 | -- Enforce one version row per S3 object (ultra-fuzz Run #1, Storage HIGH). |
| 2 | -- |
| 3 | -- Version download keys were `{user}/{item}/download/{filename}` with no |
| 4 | -- version segment, so two versions of the same item that shared a filename |
| 5 | -- (a creator who ships every release as `plugin.zip`) resolved to the SAME S3 |
| 6 | -- key. The second upload's PUT overwrote the first's bytes (immutable cache |
| 7 | -- then served the wrong release), and both version rows carried |
| 8 | -- `file_size_bytes` for one physical object, so the weekly |
| 9 | -- `recalculate_all_storage_batch` baked the double-charge into reconciliation |
| 10 | -- (the same non-self-healing drift migration 147 closed for gallery images). |
| 11 | -- |
| 12 | -- The key generator now weaves the version id into the path |
| 13 | -- (`generate_version_key` -> `{user}/{item}/download/{version_id}/{filename}`), |
| 14 | -- so every newly-confirmed version is globally unique by construction. This |
| 15 | -- partial unique index is the backstop that makes a future regression a write |
| 16 | -- error instead of a silent overwrite. |
| 17 | -- |
| 18 | -- Unlike the gallery fix, we do NOT auto-dedupe: a version row is meaningful |
| 19 | -- (distinct version_number, download history, current flag), so silently |
| 20 | -- dropping one would lose data. Surface any pre-existing legacy collision |
| 21 | -- loudly instead — it needs a human decision, not an automated DELETE. |
| 22 | DO $$ |
| 23 | DECLARE |
| 24 | dupes int; |
| 25 | BEGIN |
| 26 | SELECT count(*) INTO dupes FROM ( |
| 27 | SELECT s3_key FROM versions |
| 28 | WHERE s3_key IS NOT NULL |
| 29 | GROUP BY s3_key HAVING count(*) > 1 |
| 30 | ) d; |
| 31 | IF dupes > 0 THEN |
| 32 | RAISE EXCEPTION 'Cannot enforce unique versions.s3_key: % colliding key(s) exist from the pre-fix format. Resolve manually (re-upload the affected versions) before applying this migration.', dupes; |
| 33 | END IF; |
| 34 | END $$; |
| 35 | |
| 36 | NOT EXISTS idx_versions_s3_key |
| 37 | ON versions (s3_key) WHERE s3_key IS NOT NULL; |
| 38 |