Blame · Line-by-line history

`0001_green_ecosystem.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.

0001_green_ecosystem.sqlBlame274 lines · 1 contributor

Normal view Raw

3ef4c9dClaude	1	-- Gluecron migration 0001: green ecosystem — advanced platform features
	2	-- Adds: repo_settings, branch_protection, gate_runs, notifications, releases,
	3	-- milestones, reactions, pr_reviews, code_owners, ai_chats, audit_log,
	4	-- deployments, rate_limit_buckets, plus new columns on existing tables.
	5
	6	--> statement-breakpoint
	7	ALTER TABLE "repositories" ADD COLUMN IF NOT EXISTS "is_archived" boolean DEFAULT false NOT NULL;
	8
	9	--> statement-breakpoint
	10	ALTER TABLE "pull_requests" ADD COLUMN IF NOT EXISTS "is_draft" boolean DEFAULT false NOT NULL;
	11
	12	--> statement-breakpoint
	13	ALTER TABLE "pull_requests" ADD COLUMN IF NOT EXISTS "merge_strategy" text DEFAULT 'merge' NOT NULL;
	14
	15	--> statement-breakpoint
	16	ALTER TABLE "pull_requests" ADD COLUMN IF NOT EXISTS "milestone_id" uuid;
	17
	18	--> statement-breakpoint
	19	CREATE TABLE IF NOT EXISTS "repo_settings" (
	20	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	21	"repository_id" uuid NOT NULL UNIQUE,
	22	"gate_test_enabled" boolean DEFAULT true NOT NULL,
	23	"ai_review_enabled" boolean DEFAULT true NOT NULL,
	24	"secret_scan_enabled" boolean DEFAULT true NOT NULL,
	25	"security_scan_enabled" boolean DEFAULT true NOT NULL,
	26	"dependency_scan_enabled" boolean DEFAULT true NOT NULL,
	27	"lint_enabled" boolean DEFAULT true NOT NULL,
	28	"type_check_enabled" boolean DEFAULT true NOT NULL,
	29	"test_enabled" boolean DEFAULT true NOT NULL,
	30	"auto_fix_enabled" boolean DEFAULT true NOT NULL,
	31	"auto_merge_resolve_enabled" boolean DEFAULT true NOT NULL,
	32	"auto_format_enabled" boolean DEFAULT true NOT NULL,
	33	"ai_commit_messages_enabled" boolean DEFAULT true NOT NULL,
	34	"ai_pr_summary_enabled" boolean DEFAULT true NOT NULL,
	35	"ai_changelog_enabled" boolean DEFAULT true NOT NULL,
	36	"auto_deploy_enabled" boolean DEFAULT true NOT NULL,
	37	"deploy_require_all_green" boolean DEFAULT true NOT NULL,
	38	"created_at" timestamp DEFAULT now() NOT NULL,
	39	"updated_at" timestamp DEFAULT now() NOT NULL,
	40	CONSTRAINT "repo_settings_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	41	);
	42
	43	--> statement-breakpoint
	44	CREATE TABLE IF NOT EXISTS "branch_protection" (
	45	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	46	"repository_id" uuid NOT NULL,
	47	"pattern" text NOT NULL,
	48	"require_pull_request" boolean DEFAULT true NOT NULL,
	49	"require_green_gates" boolean DEFAULT true NOT NULL,
	50	"require_ai_approval" boolean DEFAULT true NOT NULL,
	51	"require_human_review" boolean DEFAULT false NOT NULL,
	52	"required_approvals" integer DEFAULT 0 NOT NULL,
	53	"allow_force_push" boolean DEFAULT false NOT NULL,
	54	"allow_deletion" boolean DEFAULT false NOT NULL,
	55	"dismiss_stale_reviews" boolean DEFAULT true NOT NULL,
	56	"created_at" timestamp DEFAULT now() NOT NULL,
	57	"updated_at" timestamp DEFAULT now() NOT NULL,
	58	CONSTRAINT "branch_protection_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	59	);
	60
	61	--> statement-breakpoint
	62	CREATE UNIQUE INDEX IF NOT EXISTS "branch_protection_repo_pattern" ON "branch_protection" ("repository_id", "pattern");
	63
	64	--> statement-breakpoint
	65	CREATE TABLE IF NOT EXISTS "gate_runs" (
	66	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	67	"repository_id" uuid NOT NULL,
	68	"pull_request_id" uuid,
	69	"commit_sha" text NOT NULL,
	70	"ref" text NOT NULL,
	71	"gate_name" text NOT NULL,
	72	"status" text NOT NULL,
	73	"summary" text,
	74	"details" text,
	75	"repair_attempted" boolean DEFAULT false NOT NULL,
	76	"repair_succeeded" boolean DEFAULT false NOT NULL,
	77	"repair_commit_sha" text,
	78	"duration_ms" integer,
	79	"created_at" timestamp DEFAULT now() NOT NULL,
	80	"completed_at" timestamp,
	81	CONSTRAINT "gate_runs_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE,
	82	CONSTRAINT "gate_runs_pr_fk" FOREIGN KEY ("pull_request_id") REFERENCES "pull_requests" ("id") ON DELETE CASCADE
	83	);
	84
	85	--> statement-breakpoint
	86	CREATE INDEX IF NOT EXISTS "gate_runs_repo_sha" ON "gate_runs" ("repository_id", "commit_sha");
	87
	88	--> statement-breakpoint
	89	CREATE INDEX IF NOT EXISTS "gate_runs_pr" ON "gate_runs" ("pull_request_id");
	90
	91	--> statement-breakpoint
	92	CREATE INDEX IF NOT EXISTS "gate_runs_created" ON "gate_runs" ("created_at");
	93
	94	--> statement-breakpoint
	95	CREATE TABLE IF NOT EXISTS "notifications" (
	96	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	97	"user_id" uuid NOT NULL,
	98	"repository_id" uuid,
	99	"kind" text NOT NULL,
	100	"title" text NOT NULL,
	101	"body" text,
	102	"url" text,
	103	"read_at" timestamp,
	104	"created_at" timestamp DEFAULT now() NOT NULL,
	105	CONSTRAINT "notifications_user_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE,
	106	CONSTRAINT "notifications_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	107	);
	108
	109	--> statement-breakpoint
	110	CREATE INDEX IF NOT EXISTS "notifications_user_unread" ON "notifications" ("user_id", "read_at");
	111
	112	--> statement-breakpoint
	113	CREATE INDEX IF NOT EXISTS "notifications_user_created" ON "notifications" ("user_id", "created_at");
	114
	115	--> statement-breakpoint
	116	CREATE TABLE IF NOT EXISTS "releases" (
	117	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	118	"repository_id" uuid NOT NULL,
	119	"author_id" uuid NOT NULL,
	120	"tag" text NOT NULL,
	121	"name" text NOT NULL,
	122	"body" text,
	123	"target_commit" text NOT NULL,
	124	"is_draft" boolean DEFAULT false NOT NULL,
	125	"is_prerelease" boolean DEFAULT false NOT NULL,
	126	"created_at" timestamp DEFAULT now() NOT NULL,
	127	"published_at" timestamp,
	128	CONSTRAINT "releases_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE,
	129	CONSTRAINT "releases_author_fk" FOREIGN KEY ("author_id") REFERENCES "users" ("id")
	130	);
	131
	132	--> statement-breakpoint
	133	CREATE UNIQUE INDEX IF NOT EXISTS "releases_repo_tag" ON "releases" ("repository_id", "tag");
	134
	135	--> statement-breakpoint
	136	CREATE TABLE IF NOT EXISTS "milestones" (
	137	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	138	"repository_id" uuid NOT NULL,
	139	"title" text NOT NULL,
	140	"description" text,
	141	"state" text DEFAULT 'open' NOT NULL,
	142	"due_date" timestamp,
	143	"created_at" timestamp DEFAULT now() NOT NULL,
	144	"closed_at" timestamp,
	145	CONSTRAINT "milestones_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	146	);
	147
	148	--> statement-breakpoint
	149	CREATE INDEX IF NOT EXISTS "milestones_repo_state" ON "milestones" ("repository_id", "state");
	150
	151	--> statement-breakpoint
	152	CREATE TABLE IF NOT EXISTS "reactions" (
	153	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	154	"user_id" uuid NOT NULL,
	155	"target_type" text NOT NULL,
	156	"target_id" uuid NOT NULL,
	157	"emoji" text NOT NULL,
	158	"created_at" timestamp DEFAULT now() NOT NULL,
	159	CONSTRAINT "reactions_user_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
	160	);
	161
	162	--> statement-breakpoint
	163	CREATE UNIQUE INDEX IF NOT EXISTS "reactions_unique" ON "reactions" ("user_id", "target_type", "target_id", "emoji");
	164
	165	--> statement-breakpoint
	166	CREATE INDEX IF NOT EXISTS "reactions_target" ON "reactions" ("target_type", "target_id");
	167
	168	--> statement-breakpoint
	169	CREATE TABLE IF NOT EXISTS "pr_reviews" (
	170	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	171	"pull_request_id" uuid NOT NULL,
	172	"reviewer_id" uuid NOT NULL,
	173	"state" text NOT NULL,
	174	"body" text,
	175	"is_ai" boolean DEFAULT false NOT NULL,
	176	"created_at" timestamp DEFAULT now() NOT NULL,
	177	CONSTRAINT "pr_reviews_pr_fk" FOREIGN KEY ("pull_request_id") REFERENCES "pull_requests" ("id") ON DELETE CASCADE,
	178	CONSTRAINT "pr_reviews_reviewer_fk" FOREIGN KEY ("reviewer_id") REFERENCES "users" ("id")
	179	);
	180
	181	--> statement-breakpoint
	182	CREATE INDEX IF NOT EXISTS "pr_reviews_pr" ON "pr_reviews" ("pull_request_id");
	183
	184	--> statement-breakpoint
	185	CREATE TABLE IF NOT EXISTS "code_owners" (
	186	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	187	"repository_id" uuid NOT NULL,
	188	"path_pattern" text NOT NULL,
	189	"owner_usernames" text NOT NULL,
	190	"created_at" timestamp DEFAULT now() NOT NULL,
	191	CONSTRAINT "code_owners_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	192	);
	193
	194	--> statement-breakpoint
	195	CREATE INDEX IF NOT EXISTS "code_owners_repo" ON "code_owners" ("repository_id");
	196
	197	--> statement-breakpoint
	198	CREATE TABLE IF NOT EXISTS "ai_chats" (
	199	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	200	"user_id" uuid NOT NULL,
	201	"repository_id" uuid,
	202	"title" text,
	203	"messages" text DEFAULT '[]' NOT NULL,
	204	"created_at" timestamp DEFAULT now() NOT NULL,
	205	"updated_at" timestamp DEFAULT now() NOT NULL,
	206	CONSTRAINT "ai_chats_user_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE,
	207	CONSTRAINT "ai_chats_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	208	);
	209
	210	--> statement-breakpoint
	211	CREATE INDEX IF NOT EXISTS "ai_chats_user" ON "ai_chats" ("user_id");
	212
	213	--> statement-breakpoint
	214	CREATE INDEX IF NOT EXISTS "ai_chats_repo" ON "ai_chats" ("repository_id");
	215
	216	--> statement-breakpoint
	217	CREATE TABLE IF NOT EXISTS "audit_log" (
	218	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	219	"user_id" uuid,
	220	"repository_id" uuid,
	221	"action" text NOT NULL,
	222	"target_type" text,
	223	"target_id" text,
	224	"ip" text,
	225	"user_agent" text,
	226	"metadata" text,
	227	"created_at" timestamp DEFAULT now() NOT NULL,
	228	CONSTRAINT "audit_log_user_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE SET NULL,
	229	CONSTRAINT "audit_log_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE SET NULL
	230	);
	231
	232	--> statement-breakpoint
	233	CREATE INDEX IF NOT EXISTS "audit_log_user" ON "audit_log" ("user_id");
	234
	235	--> statement-breakpoint
	236	CREATE INDEX IF NOT EXISTS "audit_log_repo" ON "audit_log" ("repository_id");
	237
	238	--> statement-breakpoint
	239	CREATE INDEX IF NOT EXISTS "audit_log_created" ON "audit_log" ("created_at");
	240
	241	--> statement-breakpoint
	242	CREATE TABLE IF NOT EXISTS "deployments" (
	243	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	244	"repository_id" uuid NOT NULL,
	245	"environment" text DEFAULT 'production' NOT NULL,
	246	"commit_sha" text NOT NULL,
	247	"ref" text NOT NULL,
	248	"status" text NOT NULL,
	249	"blocked_reason" text,
	250	"target" text,
	251	"triggered_by" uuid,
	252	"created_at" timestamp DEFAULT now() NOT NULL,
	253	"completed_at" timestamp,
	254	CONSTRAINT "deployments_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE,
	255	CONSTRAINT "deployments_user_fk" FOREIGN KEY ("triggered_by") REFERENCES "users" ("id")
	256	);
	257
	258	--> statement-breakpoint
	259	CREATE INDEX IF NOT EXISTS "deployments_repo" ON "deployments" ("repository_id");
	260
	261	--> statement-breakpoint
	262	CREATE INDEX IF NOT EXISTS "deployments_created" ON "deployments" ("created_at");
	263
	264	--> statement-breakpoint
	265	CREATE TABLE IF NOT EXISTS "rate_limit_buckets" (
	266	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	267	"bucket_key" text NOT NULL UNIQUE,
	268	"count" integer DEFAULT 0 NOT NULL,
	269	"window_start" timestamp DEFAULT now() NOT NULL,
	270	"expires_at" timestamp NOT NULL
	271	);
	272
	273	--> statement-breakpoint
	274	CREATE INDEX IF NOT EXISTS "rate_limit_expires" ON "rate_limit_buckets" ("expires_at");