max / makenotwork

1.9 KB · 57 lines History Blame Raw

1	-- Custom domains for creator accounts + item slugs for pretty URLs.
2
3	CREATE TABLE custom_domains (
4	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
5	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
6	domain TEXT NOT NULL,
7	verified BOOLEAN NOT NULL DEFAULT false,
8	verification_token TEXT NOT NULL,
9	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
10	verified_at TIMESTAMPTZ,
11	CONSTRAINT uq_custom_domains_domain UNIQUE (domain)
12	);
13	CREATE INDEX idx_custom_domains_user ON custom_domains(user_id);
14
15	-- Item slugs for pretty URLs on custom domains.
16	ALTER TABLE items ADD COLUMN slug TEXT;
17
18	-- Backfill existing items with slugs derived from titles.
19	-- Handles collisions within the same project by appending a counter.
20	DO $$
21	DECLARE
22	r RECORD;
23	base_slug TEXT;
24	candidate TEXT;
25	counter INT;
26	BEGIN
27	FOR r IN SELECT id, project_id, title FROM items WHERE slug IS NULL ORDER BY created_at
28	LOOP
29	-- Generate base slug: lowercase, non-alphanum to hyphen, collapse, trim
30	base_slug := lower(r.title);
31	base_slug := regexp_replace(base_slug, '[^a-z0-9]+', '-', 'g');
32	base_slug := regexp_replace(base_slug, '-+', '-', 'g');
33	base_slug := trim(BOTH '-' FROM base_slug);
34	IF length(base_slug) < 2 THEN
35	base_slug := 'item';
36	END IF;
37
38	candidate := base_slug;
39	counter := 2;
40
41	-- Resolve collisions within the same project
42	WHILE EXISTS (
43	SELECT 1 FROM items
44	WHERE project_id = r.project_id AND slug = candidate AND id != r.id
45	) LOOP
46	candidate := base_slug \|\| '-' \|\| counter;
47	counter := counter + 1;
48	END LOOP;
49
50	UPDATE items SET slug = candidate WHERE id = r.id;
51	END LOOP;
52	END $$;
53
54	-- After backfill, make slug NOT NULL and add unique constraint per project.
55	ALTER TABLE items ALTER COLUMN slug SET NOT NULL;
56	CREATE UNIQUE INDEX idx_items_slug_project ON items(project_id, slug);
57