max / makenotwork
| 1 | -- Backstop: one OTA artifact row per S3 object (ultra-fuzz Run #1 --deep, Storage). |
| 2 | -- |
| 3 | -- OTA artifact keys are `ota/{app_id}/{version}/{target}/{arch}/artifact`. They |
| 4 | -- are already unique by construction — `ota_releases` enforces |
| 5 | -- UNIQUE(app_id, version) and `ota_artifacts` enforces UNIQUE(release_id, target, |
| 6 | -- arch), so two artifact rows cannot derive the same key today. But the s3_key |
| 7 | -- column itself carried no constraint, so a future change to the key layout (or a |
| 8 | -- hand-built key bypassing `generate_ota_artifact_key`) could silently point two |
| 9 | -- rows at one object and overwrite a published artifact's bytes — the same class |
| 10 | -- the versions/gallery unique indexes (147, 148) closed. Centralizing key |
| 11 | -- construction in `S3Client::generate_ota_artifact_key` plus this index makes a |
| 12 | -- regression a write error, not a silent overwrite. |
| 13 | -- |
| 14 | -- Surface any pre-existing legacy collision loudly: an OTA artifact row is a |
| 15 | -- published release binary; dropping one automatically would lose a release, so |
| 16 | -- it needs a human decision, not a DELETE. |
| 17 | DO $$ |
| 18 | DECLARE |
| 19 | dupes int; |
| 20 | BEGIN |
| 21 | SELECT count(*) INTO dupes FROM ( |
| 22 | SELECT s3_key FROM ota_artifacts |
| 23 | GROUP BY s3_key HAVING count(*) > 1 |
| 24 | ) d; |
| 25 | IF dupes > 0 THEN |
| 26 | RAISE EXCEPTION 'Cannot enforce unique ota_artifacts.s3_key: % colliding key(s) exist. Resolve manually before applying this migration.', dupes; |
| 27 | END IF; |
| 28 | END $$; |
| 29 | |
| 30 | NOT EXISTS idx_ota_artifacts_s3_key |
| 31 | ON ota_artifacts (s3_key); |
| 32 |