Blame · Line-by-line history

`0072_dev_envs.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.

0072_dev_envs.sqlBlame66 lines · 1 contributor

Normal view Raw

9b3a183Claude	1	-- Gluecron migration 0072: per-(repo, user) cloud dev environments.
	2	--
	3	-- Hosted VS Code in the browser. Push a repo, hit /:owner/:repo/dev, get a
	4	-- full-screen IDE backed by a cold-start sandbox container. Microsoft
	5	-- Codespaces costs them a fortune in Azure VM time — we go cheaper by
	6	-- spinning environments down after `idle_minutes` of no traffic and
	7	-- re-warming on demand.
	8	--
	9	-- One environment per (repository_id, owner_user_id) — re-starting the
	10	-- same repo as the same user upserts onto the existing row so the URL
	11	-- stays stable.
	12	--
	13	-- Wrapped in DO blocks so re-runs are safe and partial-replay friendly.
	14	-- Per repo opt-in lives on `repositories.dev_envs_enabled` (added below);
	15	-- the whole feature is graceful — when either the table or the column
	16	-- is missing every helper in src/lib/dev-env.ts returns null and the
	17	-- route renders a "disabled" notice instead of throwing.
	18
	19	--> statement-breakpoint
	20	DO $$
	21	BEGIN
	22	CREATE TABLE IF NOT EXISTS "dev_envs" (
	23	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	24	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	25	"owner_user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
	26	"status" text NOT NULL DEFAULT 'cold',
	27	"preview_url" text,
	28	"container_id" text,
	29	"machine_size" text NOT NULL DEFAULT 'small',
	30	"idle_minutes" integer NOT NULL DEFAULT 30,
	31	"dev_yml" text,
	32	"error_message" text,
	33	"last_active_at" timestamptz NOT NULL DEFAULT now(),
	34	"expires_at" timestamptz,
	35	"created_at" timestamptz NOT NULL DEFAULT now()
	36	);
	37	EXCEPTION WHEN OTHERS THEN
	38	RAISE NOTICE 'dev_envs create failed (%); dev environments will be unavailable', SQLERRM;
	39	END $$;
	40
	41	--> statement-breakpoint
	42	DO $$
	43	BEGIN
	44	CREATE UNIQUE INDEX IF NOT EXISTS "dev_envs_repo_owner"
	45	ON "dev_envs" ("repository_id", "owner_user_id");
	46	EXCEPTION WHEN OTHERS THEN
	47	RAISE NOTICE 'dev_envs_repo_owner index failed (%)', SQLERRM;
	48	END $$;
	49
	50	--> statement-breakpoint
	51	DO $$
	52	BEGIN
	53	CREATE INDEX IF NOT EXISTS "dev_envs_status_active"
	54	ON "dev_envs" ("status", "last_active_at");
	55	EXCEPTION WHEN OTHERS THEN
	56	RAISE NOTICE 'dev_envs_status_active index failed (%)', SQLERRM;
	57	END $$;
	58
	59	--> statement-breakpoint
	60	DO $$
	61	BEGIN
	62	ALTER TABLE "repositories"
	63	ADD COLUMN IF NOT EXISTS "dev_envs_enabled" boolean NOT NULL DEFAULT false;
	64	EXCEPTION WHEN OTHERS THEN
	65	RAISE NOTICE 'repositories.dev_envs_enabled add failed (%)', SQLERRM;
	66	END $$;