Blame · Line-by-line history

`0000_initial.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.

0000_initial.sqlBlame248 lines · 1 contributor

Normal view Raw

e883329Claude	1	-- Gluecron initial migration
	2	-- Generated manually to match src/db/schema.ts
	3
	4	--> statement-breakpoint
	5	CREATE TABLE IF NOT EXISTS "users" (
	6	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	7	"username" text NOT NULL,
	8	"email" text NOT NULL,
	9	"display_name" text,
	10	"password_hash" text NOT NULL,
	11	"avatar_url" text,
	12	"bio" text,
	13	"created_at" timestamp DEFAULT now() NOT NULL,
	14	"updated_at" timestamp DEFAULT now() NOT NULL,
	15	CONSTRAINT "users_username_unique" UNIQUE ("username"),
	16	CONSTRAINT "users_email_unique" UNIQUE ("email")
	17	);
	18
	19	--> statement-breakpoint
	20	CREATE TABLE IF NOT EXISTS "sessions" (
	21	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	22	"user_id" uuid NOT NULL,
	23	"token" text NOT NULL,
	24	"expires_at" timestamp NOT NULL,
	25	"created_at" timestamp DEFAULT now() NOT NULL,
	26	CONSTRAINT "sessions_token_unique" UNIQUE ("token"),
	27	CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
	28	);
	29
	30	--> statement-breakpoint
	31	CREATE TABLE IF NOT EXISTS "repositories" (
	32	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	33	"name" text NOT NULL,
	34	"owner_id" uuid NOT NULL,
	35	"description" text,
	36	"is_private" boolean DEFAULT false NOT NULL,
	37	"default_branch" text DEFAULT 'main' NOT NULL,
	38	"disk_path" text NOT NULL,
	39	"forked_from_id" uuid,
	40	"created_at" timestamp DEFAULT now() NOT NULL,
	41	"updated_at" timestamp DEFAULT now() NOT NULL,
	42	"pushed_at" timestamp,
	43	"star_count" integer DEFAULT 0 NOT NULL,
	44	"fork_count" integer DEFAULT 0 NOT NULL,
	45	"issue_count" integer DEFAULT 0 NOT NULL,
	46	CONSTRAINT "repositories_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "users" ("id"),
	47	CONSTRAINT "repositories_forked_from_id_repositories_id_fk" FOREIGN KEY ("forked_from_id") REFERENCES "repositories" ("id") ON DELETE SET NULL
	48	);
	49
	50	--> statement-breakpoint
	51	CREATE UNIQUE INDEX IF NOT EXISTS "repos_owner_name" ON "repositories" ("owner_id", "name");
	52
	53	--> statement-breakpoint
	54	CREATE TABLE IF NOT EXISTS "stars" (
	55	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	56	"user_id" uuid NOT NULL,
	57	"repository_id" uuid NOT NULL,
	58	"created_at" timestamp DEFAULT now() NOT NULL,
	59	CONSTRAINT "stars_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE,
	60	CONSTRAINT "stars_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	61	);
	62
	63	--> statement-breakpoint
	64	CREATE UNIQUE INDEX IF NOT EXISTS "stars_user_repo" ON "stars" ("user_id", "repository_id");
	65
	66	--> statement-breakpoint
	67	CREATE TABLE IF NOT EXISTS "issues" (
	68	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	69	"number" serial NOT NULL,
	70	"repository_id" uuid NOT NULL,
	71	"author_id" uuid NOT NULL,
	72	"title" text NOT NULL,
	73	"body" text,
	74	"state" text DEFAULT 'open' NOT NULL,
	75	"created_at" timestamp DEFAULT now() NOT NULL,
	76	"updated_at" timestamp DEFAULT now() NOT NULL,
	77	"closed_at" timestamp,
	78	CONSTRAINT "issues_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE,
	79	CONSTRAINT "issues_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "users" ("id")
	80	);
	81
	82	--> statement-breakpoint
	83	CREATE INDEX IF NOT EXISTS "issues_repo_state" ON "issues" ("repository_id", "state");
	84
	85	--> statement-breakpoint
	86	CREATE INDEX IF NOT EXISTS "issues_repo_number" ON "issues" ("repository_id", "number");
	87
	88	--> statement-breakpoint
	89	CREATE TABLE IF NOT EXISTS "issue_comments" (
	90	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	91	"issue_id" uuid NOT NULL,
	92	"author_id" uuid NOT NULL,
	93	"body" text NOT NULL,
	94	"created_at" timestamp DEFAULT now() NOT NULL,
	95	"updated_at" timestamp DEFAULT now() NOT NULL,
	96	CONSTRAINT "issue_comments_issue_id_issues_id_fk" FOREIGN KEY ("issue_id") REFERENCES "issues" ("id") ON DELETE CASCADE,
	97	CONSTRAINT "issue_comments_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "users" ("id")
	98	);
	99
	100	--> statement-breakpoint
	101	CREATE INDEX IF NOT EXISTS "comments_issue" ON "issue_comments" ("issue_id");
	102
	103	--> statement-breakpoint
	104	CREATE TABLE IF NOT EXISTS "labels" (
	105	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	106	"repository_id" uuid NOT NULL,
	107	"name" text NOT NULL,
	108	"color" text DEFAULT '#8b949e' NOT NULL,
	109	"description" text,
	110	CONSTRAINT "labels_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	111	);
	112
	113	--> statement-breakpoint
	114	CREATE UNIQUE INDEX IF NOT EXISTS "labels_repo_name" ON "labels" ("repository_id", "name");
	115
	116	--> statement-breakpoint
	117	CREATE TABLE IF NOT EXISTS "issue_labels" (
	118	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	119	"issue_id" uuid NOT NULL,
	120	"label_id" uuid NOT NULL,
	121	CONSTRAINT "issue_labels_issue_id_issues_id_fk" FOREIGN KEY ("issue_id") REFERENCES "issues" ("id") ON DELETE CASCADE,
	122	CONSTRAINT "issue_labels_label_id_labels_id_fk" FOREIGN KEY ("label_id") REFERENCES "labels" ("id") ON DELETE CASCADE
	123	);
	124
	125	--> statement-breakpoint
	126	CREATE UNIQUE INDEX IF NOT EXISTS "issue_labels_unique" ON "issue_labels" ("issue_id", "label_id");
	127
	128	--> statement-breakpoint
	129	CREATE TABLE IF NOT EXISTS "pull_requests" (
	130	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	131	"number" serial NOT NULL,
	132	"repository_id" uuid NOT NULL,
	133	"author_id" uuid NOT NULL,
	134	"title" text NOT NULL,
	135	"body" text,
	136	"state" text DEFAULT 'open' NOT NULL,
	137	"base_branch" text NOT NULL,
	138	"head_branch" text NOT NULL,
	139	"merged_at" timestamp,
	140	"merged_by" uuid,
	141	"created_at" timestamp DEFAULT now() NOT NULL,
	142	"updated_at" timestamp DEFAULT now() NOT NULL,
	143	"closed_at" timestamp,
	144	CONSTRAINT "pull_requests_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE,
	145	CONSTRAINT "pull_requests_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "users" ("id"),
	146	CONSTRAINT "pull_requests_merged_by_users_id_fk" FOREIGN KEY ("merged_by") REFERENCES "users" ("id")
	147	);
	148
	149	--> statement-breakpoint
	150	CREATE INDEX IF NOT EXISTS "prs_repo_state" ON "pull_requests" ("repository_id", "state");
	151
	152	--> statement-breakpoint
	153	CREATE INDEX IF NOT EXISTS "prs_repo_number" ON "pull_requests" ("repository_id", "number");
	154
	155	--> statement-breakpoint
	156	CREATE TABLE IF NOT EXISTS "pr_comments" (
	157	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	158	"pull_request_id" uuid NOT NULL,
	159	"author_id" uuid NOT NULL,
	160	"body" text NOT NULL,
	161	"is_ai_review" boolean DEFAULT false NOT NULL,
	162	"file_path" text,
	163	"line_number" integer,
	164	"created_at" timestamp DEFAULT now() NOT NULL,
	165	"updated_at" timestamp DEFAULT now() NOT NULL,
	166	CONSTRAINT "pr_comments_pull_request_id_pull_requests_id_fk" FOREIGN KEY ("pull_request_id") REFERENCES "pull_requests" ("id") ON DELETE CASCADE,
	167	CONSTRAINT "pr_comments_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "users" ("id")
	168	);
	169
	170	--> statement-breakpoint
	171	CREATE INDEX IF NOT EXISTS "pr_comments_pr" ON "pr_comments" ("pull_request_id");
	172
	173	--> statement-breakpoint
	174	CREATE TABLE IF NOT EXISTS "activity_feed" (
	175	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	176	"repository_id" uuid NOT NULL,
	177	"user_id" uuid,
	178	"action" text NOT NULL,
	179	"target_type" text,
	180	"target_id" text,
	181	"metadata" text,
	182	"created_at" timestamp DEFAULT now() NOT NULL,
	183	CONSTRAINT "activity_feed_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE,
	184	CONSTRAINT "activity_feed_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id")
	185	);
	186
	187	--> statement-breakpoint
	188	CREATE INDEX IF NOT EXISTS "activity_repo" ON "activity_feed" ("repository_id");
	189
	190	--> statement-breakpoint
	191	CREATE INDEX IF NOT EXISTS "activity_user" ON "activity_feed" ("user_id");
	192
	193	--> statement-breakpoint
	194	CREATE TABLE IF NOT EXISTS "webhooks" (
	195	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	196	"repository_id" uuid NOT NULL,
	197	"url" text NOT NULL,
	198	"secret" text,
	199	"events" text DEFAULT 'push' NOT NULL,
	200	"is_active" boolean DEFAULT true NOT NULL,
	201	"last_delivered_at" timestamp,
	202	"last_status" integer,
	203	"created_at" timestamp DEFAULT now() NOT NULL,
	204	CONSTRAINT "webhooks_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	205	);
	206
	207	--> statement-breakpoint
	208	CREATE INDEX IF NOT EXISTS "webhooks_repo" ON "webhooks" ("repository_id");
	209
	210	--> statement-breakpoint
	211	CREATE TABLE IF NOT EXISTS "api_tokens" (
	212	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	213	"user_id" uuid NOT NULL,
	214	"name" text NOT NULL,
	215	"token_hash" text NOT NULL,
	216	"token_prefix" text NOT NULL,
	217	"scopes" text DEFAULT 'repo' NOT NULL,
	218	"last_used_at" timestamp,
	219	"expires_at" timestamp,
	220	"created_at" timestamp DEFAULT now() NOT NULL,
	221	CONSTRAINT "api_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
	222	);
	223
	224	--> statement-breakpoint
	225	CREATE TABLE IF NOT EXISTS "repo_topics" (
	226	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	227	"repository_id" uuid NOT NULL,
	228	"topic" text NOT NULL,
	229	CONSTRAINT "repo_topics_repository_id_repositories_id_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories" ("id") ON DELETE CASCADE
	230	);
	231
	232	--> statement-breakpoint
	233	CREATE UNIQUE INDEX IF NOT EXISTS "repo_topics_unique" ON "repo_topics" ("repository_id", "topic");
	234
	235	--> statement-breakpoint
	236	CREATE INDEX IF NOT EXISTS "topics_name" ON "repo_topics" ("topic");
	237
	238	--> statement-breakpoint
	239	CREATE TABLE IF NOT EXISTS "ssh_keys" (
	240	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	241	"user_id" uuid NOT NULL,
	242	"title" text NOT NULL,
	243	"fingerprint" text NOT NULL,
	244	"public_key" text NOT NULL,
	245	"last_used_at" timestamp,
	246	"created_at" timestamp DEFAULT now() NOT NULL,
	247	CONSTRAINT "ssh_keys_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
	248	);