max / makenotwork

2.2 KB · 52 lines History Blame Raw

1	-- Ordered image galleries for items and projects (launchplan S.1).
2	--
3	-- The single `cover_image_url` on items/projects stays as-is (it remains the
4	-- OG/Twitter card image). These tables are additive: zero or more extra images
5	-- per entity, each with an alt string and an explicit display position, render
6	-- through the shared carousel widget.
7	--
8	-- s3_key is stored directly (not just the URL) so the cleanup/garbage paths can
9	-- match it exactly via the S3_KEY_REFS registry, and so a delete can decrement
10	-- storage from the recorded file_size_bytes without an S3 HEAD probe (the same
11	-- drift-avoidance reason items.cover_file_size_bytes exists, migration 126).
12	--
13	-- Scanning follows the project-image precedent: gallery uploads enqueue a scan
14	-- job (target_kind 'gallery_image'); the worker scans + quarantines a malicious
15	-- object but writes no per-row status column (no display gating), so no
16	-- scan_status column here.
17
18	CREATE TABLE item_images (
19	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
20	item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
21	s3_key TEXT NOT NULL,
22	image_url TEXT NOT NULL,
23	alt TEXT NOT NULL DEFAULT '',
24	position INT NOT NULL DEFAULT 0,
25	file_size_bytes BIGINT NOT NULL DEFAULT 0,
26	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
27	);
28
29	CREATE INDEX idx_item_images_item ON item_images (item_id, position);
30
31	CREATE TABLE project_images (
32	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
33	project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
34	s3_key TEXT NOT NULL,
35	image_url TEXT NOT NULL,
36	alt TEXT NOT NULL DEFAULT '',
37	position INT NOT NULL DEFAULT 0,
38	file_size_bytes BIGINT NOT NULL DEFAULT 0,
39	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
40	);
41
42	CREATE INDEX idx_project_images_project ON project_images (project_id, position);
43
44	-- Allow the new scan target kind. Drop-and-re-add is order-independent of any
45	-- prior alteration to this constraint.
46	ALTER TABLE scan_jobs DROP CONSTRAINT IF EXISTS scan_jobs_target_kind_check;
47	ALTER TABLE scan_jobs ADD CONSTRAINT scan_jobs_target_kind_check
48	CHECK (target_kind IN (
49	'version', 'item', 'project_image', 'item_image', 'media',
50	'content_insertion', 'gallery_image'
51	));
52