Blame · Line-by-line history

`0073_server_targets.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.

0073_server_targets.sqlBlame104 lines · 1 contributor

Normal view Raw

783dd46Claude	1	-- Gluecron migration 0073: server targets (Block ST).
	2	--
	3	-- Admin-only first-class concept for "boxes Gluecron can push deploys to".
	4	-- A target owns:
	5	-- - SSH connection info (host, user, port, encrypted private key)
	6	-- - A host-key fingerprint pinned on first successful connection (TOFU)
	7	-- - A deploy_script that runs on the box when a watched branch is pushed
	8	-- - A set of env vars (server_target_env) materialised as a .env file
	9	-- uploaded before each deploy and sourced by the script
	10	--
	11	-- Customer-facing rollout (Block 2) reuses these tables with the addition
	12	-- of owner_user_id scoping + an `auth_method` enum. v1 is admin-only:
	13	-- created_by tracks the operator who registered the target.
	14	--
	15	-- Wrapped in DO blocks so partial replays are safe.
	16
	17	--> statement-breakpoint
	18	DO $$
	19	BEGIN
	20	CREATE TABLE IF NOT EXISTS "server_targets" (
	21	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	22	"name" text NOT NULL,
	23	"host" text NOT NULL,
	24	"port" integer NOT NULL DEFAULT 22,
	25	"ssh_user" text NOT NULL,
	26	"encrypted_private_key" text NOT NULL,
	27	"host_fingerprint" text,
	28	"deploy_path" text NOT NULL DEFAULT '/var/www/app',
	29	"deploy_script" text NOT NULL DEFAULT 'bash deploy.sh',
	30	"watched_repository_id" uuid REFERENCES "repositories"("id") ON DELETE SET NULL,
	31	"watched_branch" text,
	32	"status" text NOT NULL DEFAULT 'unverified',
	33	"last_seen_at" timestamptz,
	34	"created_by" uuid REFERENCES "users"("id") ON DELETE SET NULL,
	35	"created_at" timestamptz NOT NULL DEFAULT now(),
	36	"updated_at" timestamptz NOT NULL DEFAULT now()
	37	);
	38	CREATE UNIQUE INDEX IF NOT EXISTS "server_targets_name_uq" ON "server_targets"("name");
	39	CREATE INDEX IF NOT EXISTS "server_targets_watch_idx"
	40	ON "server_targets"("watched_repository_id", "watched_branch")
	41	WHERE "watched_repository_id" IS NOT NULL;
	42	EXCEPTION WHEN OTHERS THEN
	43	RAISE NOTICE 'server_targets create failed (%); feature will be unavailable', SQLERRM;
	44	END $$;
	45
	46	--> statement-breakpoint
	47	DO $$
	48	BEGIN
	49	CREATE TABLE IF NOT EXISTS "server_target_env" (
	50	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	51	"target_id" uuid NOT NULL REFERENCES "server_targets"("id") ON DELETE CASCADE,
	52	"name" text NOT NULL,
	53	"encrypted_value" text NOT NULL,
	54	"is_secret" boolean NOT NULL DEFAULT true,
	55	"updated_by" uuid REFERENCES "users"("id") ON DELETE SET NULL,
	56	"created_at" timestamptz NOT NULL DEFAULT now(),
	57	"updated_at" timestamptz NOT NULL DEFAULT now()
	58	);
	59	CREATE UNIQUE INDEX IF NOT EXISTS "server_target_env_uq"
	60	ON "server_target_env"("target_id", "name");
	61	EXCEPTION WHEN OTHERS THEN
	62	RAISE NOTICE 'server_target_env create failed (%);', SQLERRM;
	63	END $$;
	64
	65	--> statement-breakpoint
	66	DO $$
	67	BEGIN
	68	CREATE TABLE IF NOT EXISTS "server_target_deployments" (
	69	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	70	"target_id" uuid NOT NULL REFERENCES "server_targets"("id") ON DELETE CASCADE,
	71	"commit_sha" text,
	72	"ref" text,
	73	"status" text NOT NULL DEFAULT 'pending',
	74	"exit_code" integer,
	75	"stdout" text,
	76	"stderr" text,
	77	"triggered_by" uuid REFERENCES "users"("id") ON DELETE SET NULL,
	78	"trigger_source" text NOT NULL DEFAULT 'push',
	79	"started_at" timestamptz NOT NULL DEFAULT now(),
	80	"finished_at" timestamptz
	81	);
	82	CREATE INDEX IF NOT EXISTS "server_target_deployments_target_idx"
	83	ON "server_target_deployments"("target_id", "started_at" DESC);
	84	EXCEPTION WHEN OTHERS THEN
	85	RAISE NOTICE 'server_target_deployments create failed (%);', SQLERRM;
	86	END $$;
	87
	88	--> statement-breakpoint
	89	DO $$
	90	BEGIN
	91	CREATE TABLE IF NOT EXISTS "server_target_audit" (
	92	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	93	"target_id" uuid REFERENCES "server_targets"("id") ON DELETE SET NULL,
	94	"actor_id" uuid REFERENCES "users"("id") ON DELETE SET NULL,
	95	"action" text NOT NULL,
	96	"detail" text,
	97	"ip" text,
	98	"created_at" timestamptz NOT NULL DEFAULT now()
	99	);
	100	CREATE INDEX IF NOT EXISTS "server_target_audit_target_idx"
	101	ON "server_target_audit"("target_id", "created_at" DESC);
	102	EXCEPTION WHEN OTHERS THEN
	103	RAISE NOTICE 'server_target_audit create failed (%);', SQLERRM;
	104	END $$;