max / makenotwork

1.8 KB · 34 lines History Blame Raw

1	-- Cover liveness via a bare S3 key, replacing fragile URL-suffix matching.
2	--
3	-- The deletion worker's liveness check (`db::pending_s3_deletions::is_s3_key_live`)
4	-- exact-matches a stored bare key for every S3 key class EXCEPT covers, which it
5	-- had to LIKE-suffix-match against a full `cover_image_url`, with hand-rolled
6	-- wildcard escaping and `/`-anchoring. `items.cover_s3_key` already exists
7	-- (migration 053) and is written by the item-image confirm path; `projects`
8	-- stored only the URL. Give projects a bare key column too, backfill both tables'
9	-- legacy rows from the canonical `{cdn_base}/{s3_key}` URL form, and the registry
10	-- switches both cover classes to exact matching (the UrlSuffix matcher and its
11	-- escaping are deleted from the code in the same change).
12
13	ALTER TABLE projects ADD COLUMN IF NOT EXISTS cover_s3_key TEXT;
14
15	-- Backfill from the canonical URL form: strip scheme + host (and any query
16	-- string) to recover the object key. Stored cover URLs are always
17	-- `{cdn_base}/{s3_key}`, so the path after the host IS the key. Only touch rows
18	-- that have a URL but no key yet; a URL that isn't an http(s) path with a
19	-- non-empty path is left NULL (nothing for the worker to protect).
20	UPDATE projects
21	SET cover_s3_key = substring(cover_image_url from '^https?://[^/]+/([^?]*)')
22	WHERE cover_image_url IS NOT NULL
23	AND cover_s3_key IS NULL
24	AND cover_image_url ~ '^https?://[^/]+/[^?]+';
25
26	-- Legacy items predating migration 053 may carry a cover_image_url with a NULL
27	-- cover_s3_key; backfill them the same way so dropping the items URL-suffix
28	-- registry entry can't strand a still-live cover.
29	UPDATE items
30	SET cover_s3_key = substring(cover_image_url from '^https?://[^/]+/([^?]*)')
31	WHERE cover_image_url IS NOT NULL
32	AND cover_s3_key IS NULL
33	AND cover_image_url ~ '^https?://[^/]+/[^?]+';
34