max / makenotwork

1.3 KB · 28 lines History Blame Raw

1	-- Reusable insertion clips (creator uploads once, places on many items)
2	CREATE TABLE content_insertions (
3	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
5	title VARCHAR(200) NOT NULL,
6	media_type VARCHAR(20) NOT NULL DEFAULT 'audio',
7	storage_key TEXT NOT NULL,
8	duration_ms INTEGER NOT NULL,
9	file_size BIGINT NOT NULL,
10	mime_type VARCHAR(100) NOT NULL,
11	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
12	);
13	CREATE INDEX idx_content_insertions_user ON content_insertions(user_id);
14
15	-- Per-item placement of insertions
16	CREATE TABLE content_insertion_placements (
17	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
18	item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
19	insertion_id UUID NOT NULL REFERENCES content_insertions(id) ON DELETE CASCADE,
20	position VARCHAR(20) NOT NULL, -- 'pre_roll', 'mid_roll', 'post_roll'
21	offset_ms INTEGER, -- for mid_roll: ms into main content
22	sort_order INTEGER NOT NULL DEFAULT 0,
23	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
24	UNIQUE(item_id, insertion_id, position, offset_ms)
25	);
26	CREATE INDEX idx_insertion_placements_item ON content_insertion_placements(item_id);
27	CREATE INDEX idx_insertion_placements_insertion ON content_insertion_placements(insertion_id);
28