max / makenotwork

660 B · 12 lines History Blame Raw

1	-- Track which removed images have had their backing S3 object deleted.
2	-- `remove_image` sets removed_at but the object delete is best-effort; this
3	-- column lets a background sweep find removed images whose object still needs
4	-- purging (the pre-existing backlog and any inline-delete failures) and retry
5	-- them convergently — once purged, an image is never revisited.
6	ALTER TABLE images ADD COLUMN IF NOT EXISTS s3_purged_at TIMESTAMPTZ;
7
8	-- The sweep scans for removed-but-not-purged images; keep that lookup cheap.
9	CREATE INDEX IF NOT EXISTS idx_images_pending_purge
10	ON images(removed_at)
11	WHERE removed_at IS NOT NULL AND s3_purged_at IS NULL;
12