Blame · Line-by-line history

`0062_branch_previews.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0062_branch_previews.sqlBlame73 lines · 1 contributor

Normal view Raw

4bbacbeClaude	1	-- Gluecron migration 0062: per-branch preview URLs.
	2	--
	3	-- Every push to a non-default branch enqueues a build row that, once
	4	-- finished, holds the canonical "preview URL" that links to a fresh
	5	-- copy of the branch. The row is the unit of dedupe (one row per
	6	-- repo/branch pair) — pushing the branch again replaces commit_sha,
	7	-- bumps build_started_at, and resets status to 'building'.
	8	--
	9	-- TTL: previews automatically transition to status='expired' 24h after
	10	-- the last push to that branch. The autopilot expireOldPreviews() task
	11	-- sweeps the table hourly.
	12	--
	13	-- Wrapped in DO blocks so the migration is safe to re-run and gracefully
	14	-- ignores duplicates / missing parents on partial replays. The whole
	15	-- feature degrades to "no rows" when the table is missing, which is
	16	-- exactly what we want for environments without preview hosting.
	17
	18	--> statement-breakpoint
	19	DO $$
	20	BEGIN
	21	CREATE TABLE IF NOT EXISTS "branch_previews" (
	22	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	23	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	24	"branch_name" text NOT NULL,
	25	"commit_sha" text NOT NULL,
	26	"preview_url" text NOT NULL,
	27	"status" text NOT NULL DEFAULT 'building',
	28	"build_started_at" timestamptz NOT NULL DEFAULT now(),
	29	"build_completed_at" timestamptz,
	30	"expires_at" timestamptz NOT NULL DEFAULT (now() + interval '24 hours'),
	31	"error_message" text,
	32	"created_at" timestamptz NOT NULL DEFAULT now()
	33	);
	34	EXCEPTION WHEN OTHERS THEN
	35	RAISE NOTICE 'branch_previews create failed (%); preview URLs will be unavailable', SQLERRM;
	36	END $$;
	37
	38	--> statement-breakpoint
	39	DO $$
	40	BEGIN
	41	CREATE UNIQUE INDEX IF NOT EXISTS "branch_previews_repo_branch"
	42	ON "branch_previews" ("repository_id", "branch_name");
	43	EXCEPTION WHEN OTHERS THEN
	44	RAISE NOTICE 'branch_previews_repo_branch index failed (%)', SQLERRM;
	45	END $$;
	46
	47	--> statement-breakpoint
	48	DO $$
	49	BEGIN
	50	CREATE INDEX IF NOT EXISTS "branch_previews_repo_status"
	51	ON "branch_previews" ("repository_id", "status");
	52	EXCEPTION WHEN OTHERS THEN
	53	RAISE NOTICE 'branch_previews_repo_status index failed (%)', SQLERRM;
	54	END $$;
	55
	56	--> statement-breakpoint
	57	DO $$
	58	BEGIN
	59	CREATE INDEX IF NOT EXISTS "branch_previews_expires"
	60	ON "branch_previews" ("expires_at")
	61	WHERE "status" IN ('building', 'ready');
	62	EXCEPTION WHEN OTHERS THEN
	63	RAISE NOTICE 'branch_previews_expires index failed (%)', SQLERRM;
	64	END $$;
	65
	66	--> statement-breakpoint
	67	DO $$
	68	BEGIN
	69	ALTER TABLE "repositories"
	70	ADD COLUMN IF NOT EXISTS "preview_builds_enabled" boolean NOT NULL DEFAULT true;
	71	EXCEPTION WHEN OTHERS THEN
	72	RAISE NOTICE 'repositories.preview_builds_enabled add failed (%)', SQLERRM;
	73	END $$;