Blame · Line-by-line history

`0067_pr_sandboxes.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.

0067_pr_sandboxes.sqlBlame64 lines · 1 contributor

Normal view Raw

79ed944Claude	1	-- Gluecron migration 0067: per-PR runnable sandboxes.
	2	--
	3	-- Every PR gets an ephemeral, executable sandbox that reviewers can poke
	4	-- live before merging. Goes beyond per-branch preview URLs (migration 0062):
	5	-- those are read-only previews. This row owns:
	6	-- - a deterministic sandbox URL on a separate subdomain root
	7	-- (default `sandbox.gluecron.com`) so it never collides with previews
	8	-- - a lifecycle (provisioning → ready / failed / destroyed) so the PR
	9	-- detail page can render a status pill + retry button
	10	-- - an `expires_at` defaulting to now + 4h so we don't leak compute
	11	-- - the resolved `playground_yml` blob (either committed by the repo
	12	-- or AI-generated on first provision) for future reference / diff
	13	--
	14	-- Wrapped in DO blocks so re-runs are safe and partial-replay friendly.
	15	-- The whole feature is graceful — when the table is missing every helper
	16	-- in src/lib/pr-sandbox.ts returns null and the UI hides the button.
	17
	18	--> statement-breakpoint
	19	DO $$
	20	BEGIN
	21	CREATE TABLE IF NOT EXISTS "pr_sandboxes" (
	22	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	23	"pr_id" uuid NOT NULL REFERENCES "pull_requests"("id") ON DELETE CASCADE,
	24	"status" text NOT NULL DEFAULT 'provisioning',
	25	"sandbox_url" text NOT NULL,
	26	"container_id" text,
	27	"playground_yml" text,
	28	"provisioned_at" timestamptz NOT NULL DEFAULT now(),
	29	"expires_at" timestamptz NOT NULL DEFAULT (now() + interval '4 hours'),
	30	"destroyed_at" timestamptz,
	31	"error_message" text,
	32	"created_at" timestamptz NOT NULL DEFAULT now()
	33	);
	34	EXCEPTION WHEN OTHERS THEN
	35	RAISE NOTICE 'pr_sandboxes create failed (%); PR sandboxes will be unavailable', SQLERRM;
	36	END $$;
	37
	38	--> statement-breakpoint
	39	DO $$
	40	BEGIN
	41	CREATE UNIQUE INDEX IF NOT EXISTS "pr_sandboxes_pr_id"
	42	ON "pr_sandboxes" ("pr_id");
	43	EXCEPTION WHEN OTHERS THEN
	44	RAISE NOTICE 'pr_sandboxes_pr_id index failed (%)', SQLERRM;
	45	END $$;
	46
	47	--> statement-breakpoint
	48	DO $$
	49	BEGIN
	50	CREATE INDEX IF NOT EXISTS "pr_sandboxes_status_expires"
	51	ON "pr_sandboxes" ("status", "expires_at")
	52	WHERE "status" IN ('provisioning', 'ready');
	53	EXCEPTION WHEN OTHERS THEN
	54	RAISE NOTICE 'pr_sandboxes_status_expires index failed (%)', SQLERRM;
	55	END $$;
	56
	57	--> statement-breakpoint
	58	DO $$
	59	BEGIN
	60	ALTER TABLE "repositories"
	61	ADD COLUMN IF NOT EXISTS "auto_pr_sandbox" boolean NOT NULL DEFAULT false;
	62	EXCEPTION WHEN OTHERS THEN
	63	RAISE NOTICE 'repositories.auto_pr_sandbox add failed (%)', SQLERRM;
	64	END $$;