Blame · Line-by-line history

`0000_init.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_init.sqlBlame195 lines · 1 contributor

Normal view Raw

8ade77bClaude	1	-- Gluecron database schema
	2	-- Run this against your Neon PostgreSQL database to initialize all tables.
	3	-- psql $DATABASE_URL -f drizzle/0000_init.sql
	4
	5	CREATE EXTENSION IF NOT EXISTS "pgcrypto";
	6
	7	-- Users
	8	CREATE TABLE IF NOT EXISTS users (
	9	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	10	username TEXT NOT NULL UNIQUE,
	11	email TEXT NOT NULL UNIQUE,
	12	display_name TEXT,
	13	password_hash TEXT NOT NULL,
	14	avatar_url TEXT,
	15	bio TEXT,
	16	created_at TIMESTAMP DEFAULT NOW() NOT NULL,
	17	updated_at TIMESTAMP DEFAULT NOW() NOT NULL
	18	);
	19
	20	-- Sessions
	21	CREATE TABLE IF NOT EXISTS sessions (
	22	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	23	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	24	token TEXT NOT NULL UNIQUE,
	25	expires_at TIMESTAMP NOT NULL,
	26	created_at TIMESTAMP DEFAULT NOW() NOT NULL
	27	);
	28
	29	-- Repositories
	30	CREATE TABLE IF NOT EXISTS repositories (
	31	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	32	name TEXT NOT NULL,
	33	owner_id UUID NOT NULL REFERENCES users(id),
	34	description TEXT,
	35	is_private BOOLEAN DEFAULT FALSE NOT NULL,
	36	default_branch TEXT DEFAULT 'main' NOT NULL,
	37	disk_path TEXT NOT NULL,
	38	forked_from_id UUID REFERENCES repositories(id) ON DELETE SET NULL,
	39	created_at TIMESTAMP DEFAULT NOW() NOT NULL,
	40	updated_at TIMESTAMP DEFAULT NOW() NOT NULL,
	41	pushed_at TIMESTAMP,
	42	star_count INTEGER DEFAULT 0 NOT NULL,
	43	fork_count INTEGER DEFAULT 0 NOT NULL,
	44	issue_count INTEGER DEFAULT 0 NOT NULL
	45	);
	46	CREATE UNIQUE INDEX IF NOT EXISTS repos_owner_name ON repositories(owner_id, name);
	47
	48	-- Stars
	49	CREATE TABLE IF NOT EXISTS stars (
	50	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	51	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	52	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	53	created_at TIMESTAMP DEFAULT NOW() NOT NULL
	54	);
	55	CREATE UNIQUE INDEX IF NOT EXISTS stars_user_repo ON stars(user_id, repository_id);
	56
	57	-- Issues
	58	CREATE TABLE IF NOT EXISTS issues (
	59	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	60	number SERIAL,
	61	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	62	author_id UUID NOT NULL REFERENCES users(id),
	63	title TEXT NOT NULL,
	64	body TEXT,
	65	state TEXT NOT NULL DEFAULT 'open',
	66	created_at TIMESTAMP DEFAULT NOW() NOT NULL,
	67	updated_at TIMESTAMP DEFAULT NOW() NOT NULL,
	68	closed_at TIMESTAMP
	69	);
	70	CREATE INDEX IF NOT EXISTS issues_repo_state ON issues(repository_id, state);
	71	CREATE INDEX IF NOT EXISTS issues_repo_number ON issues(repository_id, number);
	72
	73	-- Issue Comments
	74	CREATE TABLE IF NOT EXISTS issue_comments (
	75	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	76	issue_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
	77	author_id UUID NOT NULL REFERENCES users(id),
	78	body TEXT NOT NULL,
	79	created_at TIMESTAMP DEFAULT NOW() NOT NULL,
	80	updated_at TIMESTAMP DEFAULT NOW() NOT NULL
	81	);
	82	CREATE INDEX IF NOT EXISTS comments_issue ON issue_comments(issue_id);
	83
	84	-- Labels
	85	CREATE TABLE IF NOT EXISTS labels (
	86	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	87	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	88	name TEXT NOT NULL,
	89	color TEXT NOT NULL DEFAULT '#8b949e',
	90	description TEXT
	91	);
	92	CREATE UNIQUE INDEX IF NOT EXISTS labels_repo_name ON labels(repository_id, name);
	93
	94	-- Issue Labels
	95	CREATE TABLE IF NOT EXISTS issue_labels (
	96	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	97	issue_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
	98	label_id UUID NOT NULL REFERENCES labels(id) ON DELETE CASCADE
	99	);
	100	CREATE UNIQUE INDEX IF NOT EXISTS issue_labels_unique ON issue_labels(issue_id, label_id);
	101
	102	-- Pull Requests
	103	CREATE TABLE IF NOT EXISTS pull_requests (
	104	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	105	number SERIAL,
	106	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	107	author_id UUID NOT NULL REFERENCES users(id),
	108	title TEXT NOT NULL,
	109	body TEXT,
	110	state TEXT NOT NULL DEFAULT 'open',
	111	base_branch TEXT NOT NULL,
	112	head_branch TEXT NOT NULL,
	113	merged_at TIMESTAMP,
	114	merged_by UUID REFERENCES users(id),
	115	created_at TIMESTAMP DEFAULT NOW() NOT NULL,
	116	updated_at TIMESTAMP DEFAULT NOW() NOT NULL,
	117	closed_at TIMESTAMP
	118	);
	119	CREATE INDEX IF NOT EXISTS prs_repo_state ON pull_requests(repository_id, state);
	120	CREATE INDEX IF NOT EXISTS prs_repo_number ON pull_requests(repository_id, number);
	121
	122	-- PR Comments
	123	CREATE TABLE IF NOT EXISTS pr_comments (
	124	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	125	pull_request_id UUID NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
	126	author_id UUID NOT NULL REFERENCES users(id),
	127	body TEXT NOT NULL,
	128	is_ai_review BOOLEAN DEFAULT FALSE NOT NULL,
	129	file_path TEXT,
	130	line_number INTEGER,
	131	created_at TIMESTAMP DEFAULT NOW() NOT NULL,
	132	updated_at TIMESTAMP DEFAULT NOW() NOT NULL
	133	);
	134	CREATE INDEX IF NOT EXISTS pr_comments_pr ON pr_comments(pull_request_id);
	135
	136	-- Activity Feed
	137	CREATE TABLE IF NOT EXISTS activity_feed (
	138	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	139	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	140	user_id UUID REFERENCES users(id),
	141	action TEXT NOT NULL,
	142	target_type TEXT,
	143	target_id TEXT,
	144	metadata TEXT,
	145	created_at TIMESTAMP DEFAULT NOW() NOT NULL
	146	);
	147	CREATE INDEX IF NOT EXISTS activity_repo ON activity_feed(repository_id);
	148	CREATE INDEX IF NOT EXISTS activity_user ON activity_feed(user_id);
	149
	150	-- Webhooks
	151	CREATE TABLE IF NOT EXISTS webhooks (
	152	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	153	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	154	url TEXT NOT NULL,
	155	secret TEXT,
	156	events TEXT NOT NULL DEFAULT 'push',
	157	is_active BOOLEAN DEFAULT TRUE NOT NULL,
	158	last_delivered_at TIMESTAMP,
	159	last_status INTEGER,
	160	created_at TIMESTAMP DEFAULT NOW() NOT NULL
	161	);
	162	CREATE INDEX IF NOT EXISTS webhooks_repo ON webhooks(repository_id);
	163
	164	-- API Tokens
	165	CREATE TABLE IF NOT EXISTS api_tokens (
	166	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	167	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	168	name TEXT NOT NULL,
	169	token_hash TEXT NOT NULL,
	170	token_prefix TEXT NOT NULL,
	171	scopes TEXT NOT NULL DEFAULT 'repo',
	172	last_used_at TIMESTAMP,
	173	expires_at TIMESTAMP,
	174	created_at TIMESTAMP DEFAULT NOW() NOT NULL
	175	);
	176
	177	-- Repository Topics
	178	CREATE TABLE IF NOT EXISTS repo_topics (
	179	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	180	repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	181	topic TEXT NOT NULL
	182	);
	183	CREATE UNIQUE INDEX IF NOT EXISTS repo_topics_unique ON repo_topics(repository_id, topic);
	184	CREATE INDEX IF NOT EXISTS topics_name ON repo_topics(topic);
	185
	186	-- SSH Keys
	187	CREATE TABLE IF NOT EXISTS ssh_keys (
	188	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	189	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	190	title TEXT NOT NULL,
	191	fingerprint TEXT NOT NULL,
	192	public_key TEXT NOT NULL,
	193	last_used_at TIMESTAMP,
	194	created_at TIMESTAMP DEFAULT NOW() NOT NULL
	195	);