Blame · Line-by-line history

`schema.ts`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

schema.tsBlame1916 lines · 1 contributor

Normal view Raw

fc1817aClaude	1	import {
	2	pgTable,
	3	text,
	4	timestamp,
	5	uuid,
	6	boolean,
	7	integer,
	8	uniqueIndex,
79136bbClaude	9	index,
	10	serial,
fc1817aClaude	11	} from "drizzle-orm/pg-core";
	12
	13	export const users = pgTable("users", {
	14	id: uuid("id").primaryKey().defaultRandom(),
	15	username: text("username").notNull().unique(),
	16	email: text("email").notNull().unique(),
	17	displayName: text("display_name"),
	18	passwordHash: text("password_hash").notNull(),
	19	avatarUrl: text("avatar_url"),
	20	bio: text("bio"),
24cf2caClaude	21	// Email notification preferences (Block A8). Default on; opt-out via /settings.
	22	notifyEmailOnMention: boolean("notify_email_on_mention").default(true).notNull(),
	23	notifyEmailOnAssign: boolean("notify_email_on_assign").default(true).notNull(),
	24	notifyEmailOnGateFail: boolean("notify_email_on_gate_fail").default(true).notNull(),
fc1817aClaude	25	createdAt: timestamp("created_at").defaultNow().notNull(),
	26	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	27	});
	28
06d5ffeClaude	29	export const sessions = pgTable("sessions", {
	30	id: uuid("id").primaryKey().defaultRandom(),
	31	userId: uuid("user_id")
	32	.notNull()
	33	.references(() => users.id, { onDelete: "cascade" }),
	34	token: text("token").notNull().unique(),
	35	expiresAt: timestamp("expires_at").notNull(),
7298a17Claude	36	// B4: true when the user has entered their password but not yet their TOTP
	37	// code. softAuth/requireAuth treat such sessions as anonymous; only
	38	// /login/2fa can consume them. Flips to false on successful 2FA.
	39	requires2fa: boolean("requires_2fa").default(false).notNull(),
06d5ffeClaude	40	createdAt: timestamp("created_at").defaultNow().notNull(),
	41	});
	42
fc1817aClaude	43	export const repositories = pgTable(
	44	"repositories",
	45	{
	46	id: uuid("id").primaryKey().defaultRandom(),
	47	name: text("name").notNull(),
7437605Claude	48	// ownerId = creator / user-owner. Always set (for attribution + user
	49	// namespace uniqueness). For org-owned repos, also represents "created by".
fc1817aClaude	50	ownerId: uuid("owner_id")
	51	.notNull()
	52	.references(() => users.id),
7437605Claude	53	// Block B2: nullable org owner. When set, the repo lives in the org
	54	// namespace and URL resolution routes `/:orgSlug/:repo` to it.
	55	orgId: uuid("org_id"),
fc1817aClaude	56	description: text("description"),
	57	isPrivate: boolean("is_private").default(false).notNull(),
3ef4c9dClaude	58	isArchived: boolean("is_archived").default(false).notNull(),
71cd5ecClaude	59	isTemplate: boolean("is_template").default(false).notNull(),
fc1817aClaude	60	defaultBranch: text("default_branch").default("main").notNull(),
	61	diskPath: text("disk_path").notNull(),
c81ab7aClaude	62	forkedFromId: uuid("forked_from_id").references(() => repositories.id, {
	63	onDelete: "set null",
	64	}),
fc1817aClaude	65	createdAt: timestamp("created_at").defaultNow().notNull(),
	66	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	67	pushedAt: timestamp("pushed_at"),
	68	starCount: integer("star_count").default(0).notNull(),
	69	forkCount: integer("fork_count").default(0).notNull(),
79136bbClaude	70	issueCount: integer("issue_count").default(0).notNull(),
fc1817aClaude	71	},
7437605Claude	72	(table) => [
	73	// Partial: uniqueness only in the user namespace (org-owned rows exempt).
	74	// Matches the partial index in migration 0004.
	75	uniqueIndex("repos_owner_name").on(table.ownerId, table.name),
	76	index("repos_org").on(table.orgId),
	77	]
fc1817aClaude	78	);
	79
3ef4c9dClaude	80	/**
	81	* Per-repository gate + auto-repair configuration.
	82	* Every new repo is created with all gates ENABLED by default —
	83	* the "full green ecosystem" default. Owners can manually opt-out per setting.
	84	*/
	85	export const repoSettings = pgTable("repo_settings", {
	86	id: uuid("id").primaryKey().defaultRandom(),
	87	repositoryId: uuid("repository_id")
	88	.notNull()
	89	.unique()
	90	.references(() => repositories.id, { onDelete: "cascade" }),
	91	// Gates
	92	gateTestEnabled: boolean("gate_test_enabled").default(true).notNull(),
	93	aiReviewEnabled: boolean("ai_review_enabled").default(true).notNull(),
	94	secretScanEnabled: boolean("secret_scan_enabled").default(true).notNull(),
	95	securityScanEnabled: boolean("security_scan_enabled").default(true).notNull(),
	96	dependencyScanEnabled: boolean("dependency_scan_enabled").default(true).notNull(),
	97	lintEnabled: boolean("lint_enabled").default(true).notNull(),
	98	typeCheckEnabled: boolean("type_check_enabled").default(true).notNull(),
	99	testEnabled: boolean("test_enabled").default(true).notNull(),
	100	// Auto-repair
	101	autoFixEnabled: boolean("auto_fix_enabled").default(true).notNull(),
	102	autoMergeResolveEnabled: boolean("auto_merge_resolve_enabled").default(true).notNull(),
	103	autoFormatEnabled: boolean("auto_format_enabled").default(true).notNull(),
	104	// AI features
	105	aiCommitMessagesEnabled: boolean("ai_commit_messages_enabled").default(true).notNull(),
	106	aiPrSummaryEnabled: boolean("ai_pr_summary_enabled").default(true).notNull(),
	107	aiChangelogEnabled: boolean("ai_changelog_enabled").default(true).notNull(),
	108	// Deploy
	109	autoDeployEnabled: boolean("auto_deploy_enabled").default(true).notNull(),
	110	deployRequireAllGreen: boolean("deploy_require_all_green").default(true).notNull(),
	111	createdAt: timestamp("created_at").defaultNow().notNull(),
	112	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	113	});
	114
	115	/**
	116	* Branch protection rules — enforced on push and merge.
	117	* Every repo's default branch gets a protection rule on creation.
	118	*/
	119	export const branchProtection = pgTable(
	120	"branch_protection",
	121	{
	122	id: uuid("id").primaryKey().defaultRandom(),
	123	repositoryId: uuid("repository_id")
	124	.notNull()
	125	.references(() => repositories.id, { onDelete: "cascade" }),
	126	pattern: text("pattern").notNull(), // branch name or glob (e.g. "main", "release/*")
	127	requirePullRequest: boolean("require_pull_request").default(true).notNull(),
	128	requireGreenGates: boolean("require_green_gates").default(true).notNull(),
	129	requireAiApproval: boolean("require_ai_approval").default(true).notNull(),
	130	requireHumanReview: boolean("require_human_review").default(false).notNull(),
	131	requiredApprovals: integer("required_approvals").default(0).notNull(),
	132	allowForcePush: boolean("allow_force_push").default(false).notNull(),
	133	allowDeletion: boolean("allow_deletion").default(false).notNull(),
	134	dismissStaleReviews: boolean("dismiss_stale_reviews").default(true).notNull(),
	135	createdAt: timestamp("created_at").defaultNow().notNull(),
	136	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	137	},
	138	(table) => [
	139	uniqueIndex("branch_protection_repo_pattern").on(
	140	table.repositoryId,
	141	table.pattern
	142	),
	143	]
	144	);
	145
	146	/**
	147	* Gate run history. Every push + every PR creates gate_runs entries —
	148	* one per configured gate. Serves as the source of truth for "is this green?".
	149	*/
	150	export const gateRuns = pgTable(
	151	"gate_runs",
	152	{
	153	id: uuid("id").primaryKey().defaultRandom(),
	154	repositoryId: uuid("repository_id")
	155	.notNull()
	156	.references(() => repositories.id, { onDelete: "cascade" }),
	157	pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
	158	onDelete: "cascade",
	159	}),
	160	commitSha: text("commit_sha").notNull(),
	161	ref: text("ref").notNull(),
	162	gateName: text("gate_name").notNull(), // e.g. "GateTest", "AI Review", "Secret Scan", "Type Check"
	163	status: text("status").notNull(), // pending, running, passed, failed, skipped, repaired
	164	summary: text("summary"),
	165	details: text("details"), // JSON: per-check output, affected files, etc
	166	repairAttempted: boolean("repair_attempted").default(false).notNull(),
	167	repairSucceeded: boolean("repair_succeeded").default(false).notNull(),
	168	repairCommitSha: text("repair_commit_sha"),
	169	durationMs: integer("duration_ms"),
	170	createdAt: timestamp("created_at").defaultNow().notNull(),
	171	completedAt: timestamp("completed_at"),
	172	},
	173	(table) => [
	174	index("gate_runs_repo_sha").on(table.repositoryId, table.commitSha),
	175	index("gate_runs_pr").on(table.pullRequestId),
	176	index("gate_runs_created").on(table.createdAt),
	177	]
	178	);
	179
	180	/**
	181	* In-app notifications. Powered by the activity feed + explicit mentions.
	182	*/
	183	export const notifications = pgTable(
	184	"notifications",
	185	{
	186	id: uuid("id").primaryKey().defaultRandom(),
	187	userId: uuid("user_id")
	188	.notNull()
	189	.references(() => users.id, { onDelete: "cascade" }),
	190	repositoryId: uuid("repository_id").references(() => repositories.id, {
	191	onDelete: "cascade",
	192	}),
	193	kind: text("kind").notNull(), // mention, review_requested, pr_merged, pr_closed, gate_failed, gate_repaired, ai_review, assigned, security_alert, deploy_success, deploy_failed
	194	title: text("title").notNull(),
	195	body: text("body"),
	196	url: text("url"), // link to the relevant page
	197	readAt: timestamp("read_at"),
	198	createdAt: timestamp("created_at").defaultNow().notNull(),
	199	},
	200	(table) => [
	201	index("notifications_user_unread").on(table.userId, table.readAt),
	202	index("notifications_user_created").on(table.userId, table.createdAt),
	203	]
	204	);
	205
	206	/**
	207	* Releases — named snapshots of a repo at a tag/commit.
	208	* AI-generated changelogs bundled in notes field.
	209	*/
	210	export const releases = pgTable(
	211	"releases",
	212	{
	213	id: uuid("id").primaryKey().defaultRandom(),
	214	repositoryId: uuid("repository_id")
	215	.notNull()
	216	.references(() => repositories.id, { onDelete: "cascade" }),
	217	authorId: uuid("author_id")
	218	.notNull()
	219	.references(() => users.id),
	220	tag: text("tag").notNull(),
	221	name: text("name").notNull(),
	222	body: text("body"), // AI-generated release notes + changelog
	223	targetCommit: text("target_commit").notNull(),
	224	isDraft: boolean("is_draft").default(false).notNull(),
	225	isPrerelease: boolean("is_prerelease").default(false).notNull(),
	226	createdAt: timestamp("created_at").defaultNow().notNull(),
	227	publishedAt: timestamp("published_at"),
	228	},
	229	(table) => [
	230	uniqueIndex("releases_repo_tag").on(table.repositoryId, table.tag),
	231	]
	232	);
	233
	234	/**
	235	* Milestones — group issues + PRs toward a shared goal.
	236	*/
	237	export const milestones = pgTable(
	238	"milestones",
	239	{
	240	id: uuid("id").primaryKey().defaultRandom(),
	241	repositoryId: uuid("repository_id")
	242	.notNull()
	243	.references(() => repositories.id, { onDelete: "cascade" }),
	244	title: text("title").notNull(),
	245	description: text("description"),
	246	state: text("state").notNull().default("open"), // open, closed
	247	dueDate: timestamp("due_date"),
	248	createdAt: timestamp("created_at").defaultNow().notNull(),
	249	closedAt: timestamp("closed_at"),
	250	},
	251	(table) => [index("milestones_repo_state").on(table.repositoryId, table.state)]
	252	);
	253
	254	/**
	255	* Reactions on issues, PRs, and comments. Universal target pointer.
	256	*/
	257	export const reactions = pgTable(
	258	"reactions",
	259	{
	260	id: uuid("id").primaryKey().defaultRandom(),
	261	userId: uuid("user_id")
	262	.notNull()
	263	.references(() => users.id, { onDelete: "cascade" }),
	264	targetType: text("target_type").notNull(), // issue, pr, issue_comment, pr_comment
	265	targetId: uuid("target_id").notNull(),
	266	emoji: text("emoji").notNull(), // thumbs_up, thumbs_down, rocket, heart, eyes, laugh, hooray, confused
	267	createdAt: timestamp("created_at").defaultNow().notNull(),
	268	},
	269	(table) => [
	270	uniqueIndex("reactions_unique").on(
	271	table.userId,
	272	table.targetType,
	273	table.targetId,
	274	table.emoji
	275	),
	276	index("reactions_target").on(table.targetType, table.targetId),
	277	]
	278	);
	279
	280	/**
	281	* PR reviews (formal approve/request-changes).
	282	* Separate from inline comments in pr_comments.
	283	*/
	284	export const prReviews = pgTable(
	285	"pr_reviews",
	286	{
	287	id: uuid("id").primaryKey().defaultRandom(),
	288	pullRequestId: uuid("pull_request_id")
	289	.notNull()
	290	.references(() => pullRequests.id, { onDelete: "cascade" }),
	291	reviewerId: uuid("reviewer_id")
	292	.notNull()
	293	.references(() => users.id),
	294	state: text("state").notNull(), // approved, changes_requested, commented
	295	body: text("body"),
	296	isAi: boolean("is_ai").default(false).notNull(),
	297	createdAt: timestamp("created_at").defaultNow().notNull(),
	298	},
	299	(table) => [index("pr_reviews_pr").on(table.pullRequestId)]
	300	);
	301
	302	/**
	303	* Code owners — who owns which paths (auto-request review on PR).
	304	* Parsed from a CODEOWNERS file at the root of the default branch.
	305	*/
	306	export const codeOwners = pgTable(
	307	"code_owners",
	308	{
	309	id: uuid("id").primaryKey().defaultRandom(),
	310	repositoryId: uuid("repository_id")
	311	.notNull()
	312	.references(() => repositories.id, { onDelete: "cascade" }),
	313	pathPattern: text("path_pattern").notNull(),
	314	ownerUsernames: text("owner_usernames").notNull(), // comma-separated
	315	createdAt: timestamp("created_at").defaultNow().notNull(),
	316	},
	317	(table) => [index("code_owners_repo").on(table.repositoryId)]
	318	);
	319
	320	/**
	321	* Per-repo AI chat sessions — conversational repo assistant.
	322	*/
	323	export const aiChats = pgTable(
	324	"ai_chats",
	325	{
	326	id: uuid("id").primaryKey().defaultRandom(),
	327	userId: uuid("user_id")
	328	.notNull()
	329	.references(() => users.id, { onDelete: "cascade" }),
	330	repositoryId: uuid("repository_id").references(() => repositories.id, {
	331	onDelete: "cascade",
	332	}),
	333	title: text("title"),
	334	messages: text("messages").notNull().default("[]"), // JSON array of {role, content}
	335	createdAt: timestamp("created_at").defaultNow().notNull(),
	336	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	337	},
	338	(table) => [
	339	index("ai_chats_user").on(table.userId),
	340	index("ai_chats_repo").on(table.repositoryId),
	341	]
	342	);
	343
	344	/**
	345	* Audit log — every sensitive action. Who did what, when, from where.
	346	*/
	347	export const auditLog = pgTable(
	348	"audit_log",
	349	{
	350	id: uuid("id").primaryKey().defaultRandom(),
	351	userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
	352	repositoryId: uuid("repository_id").references(() => repositories.id, {
	353	onDelete: "set null",
	354	}),
	355	action: text("action").notNull(), // repo.create, repo.delete, repo.transfer, token.create, token.revoke, merge, force_push, branch_protection.update, deploy, ...
	356	targetType: text("target_type"),
	357	targetId: text("target_id"),
	358	ip: text("ip"),
	359	userAgent: text("user_agent"),
	360	metadata: text("metadata"), // JSON for extra context
	361	createdAt: timestamp("created_at").defaultNow().notNull(),
	362	},
	363	(table) => [
	364	index("audit_log_user").on(table.userId),
	365	index("audit_log_repo").on(table.repositoryId),
	366	index("audit_log_created").on(table.createdAt),
	367	]
	368	);
	369
	370	/**
	371	* Deployments — tracks every deploy to downstream systems (Crontech, etc).
	372	* Each deploy is gated on ALL green gates passing.
	373	*/
	374	export const deployments = pgTable(
	375	"deployments",
	376	{
	377	id: uuid("id").primaryKey().defaultRandom(),
	378	repositoryId: uuid("repository_id")
	379	.notNull()
	380	.references(() => repositories.id, { onDelete: "cascade" }),
	381	environment: text("environment").notNull().default("production"),
	382	commitSha: text("commit_sha").notNull(),
	383	ref: text("ref").notNull(),
	384	status: text("status").notNull(), // pending, running, success, failed, blocked
	385	blockedReason: text("blocked_reason"),
	386	target: text("target"), // e.g. "crontech", "fly.io"
	387	triggeredBy: uuid("triggered_by").references(() => users.id),
	388	createdAt: timestamp("created_at").defaultNow().notNull(),
	389	completedAt: timestamp("completed_at"),
	390	},
	391	(table) => [
	392	index("deployments_repo").on(table.repositoryId),
	393	index("deployments_created").on(table.createdAt),
	394	]
	395	);
	396
	397	/**
	398	* Rate-limit buckets — in-memory or persisted counter per IP / token / route.
	399	*/
	400	export const rateLimitBuckets = pgTable(
	401	"rate_limit_buckets",
	402	{
	403	id: uuid("id").primaryKey().defaultRandom(),
	404	bucketKey: text("bucket_key").notNull().unique(), // "ip:1.2.3.4:api" or "token:abc:api"
	405	count: integer("count").default(0).notNull(),
	406	windowStart: timestamp("window_start").defaultNow().notNull(),
	407	expiresAt: timestamp("expires_at").notNull(),
	408	},
	409	(table) => [index("rate_limit_expires").on(table.expiresAt)]
	410	);
	411
06d5ffeClaude	412	export const stars = pgTable(
	413	"stars",
	414	{
	415	id: uuid("id").primaryKey().defaultRandom(),
	416	userId: uuid("user_id")
	417	.notNull()
	418	.references(() => users.id, { onDelete: "cascade" }),
	419	repositoryId: uuid("repository_id")
	420	.notNull()
	421	.references(() => repositories.id, { onDelete: "cascade" }),
	422	createdAt: timestamp("created_at").defaultNow().notNull(),
	423	},
79136bbClaude	424	(table) => [
	425	uniqueIndex("stars_user_repo").on(table.userId, table.repositoryId),
	426	]
	427	);
	428
	429	export const issues = pgTable(
	430	"issues",
	431	{
	432	id: uuid("id").primaryKey().defaultRandom(),
	433	number: serial("number"),
	434	repositoryId: uuid("repository_id")
	435	.notNull()
	436	.references(() => repositories.id, { onDelete: "cascade" }),
	437	authorId: uuid("author_id")
	438	.notNull()
	439	.references(() => users.id),
	440	title: text("title").notNull(),
	441	body: text("body"),
	442	state: text("state").notNull().default("open"), // open, closed
	443	createdAt: timestamp("created_at").defaultNow().notNull(),
	444	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	445	closedAt: timestamp("closed_at"),
	446	},
	447	(table) => [
	448	index("issues_repo_state").on(table.repositoryId, table.state),
	449	index("issues_repo_number").on(table.repositoryId, table.number),
	450	]
	451	);
	452
	453	export const issueComments = pgTable(
	454	"issue_comments",
	455	{
	456	id: uuid("id").primaryKey().defaultRandom(),
	457	issueId: uuid("issue_id")
	458	.notNull()
	459	.references(() => issues.id, { onDelete: "cascade" }),
	460	authorId: uuid("author_id")
	461	.notNull()
	462	.references(() => users.id),
	463	body: text("body").notNull(),
	464	createdAt: timestamp("created_at").defaultNow().notNull(),
	465	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	466	},
	467	(table) => [index("comments_issue").on(table.issueId)]
	468	);
	469
	470	export const labels = pgTable(
	471	"labels",
	472	{
	473	id: uuid("id").primaryKey().defaultRandom(),
	474	repositoryId: uuid("repository_id")
	475	.notNull()
	476	.references(() => repositories.id, { onDelete: "cascade" }),
	477	name: text("name").notNull(),
	478	color: text("color").notNull().default("#8b949e"),
	479	description: text("description"),
	480	},
	481	(table) => [
	482	uniqueIndex("labels_repo_name").on(table.repositoryId, table.name),
	483	]
	484	);
	485
	486	export const issueLabels = pgTable(
	487	"issue_labels",
	488	{
	489	id: uuid("id").primaryKey().defaultRandom(),
	490	issueId: uuid("issue_id")
	491	.notNull()
	492	.references(() => issues.id, { onDelete: "cascade" }),
	493	labelId: uuid("label_id")
	494	.notNull()
	495	.references(() => labels.id, { onDelete: "cascade" }),
	496	},
	497	(table) => [
	498	uniqueIndex("issue_labels_unique").on(table.issueId, table.labelId),
	499	]
06d5ffeClaude	500	);
	501
0074234Claude	502	export const pullRequests = pgTable(
	503	"pull_requests",
	504	{
	505	id: uuid("id").primaryKey().defaultRandom(),
	506	number: serial("number"),
	507	repositoryId: uuid("repository_id")
	508	.notNull()
	509	.references(() => repositories.id, { onDelete: "cascade" }),
	510	authorId: uuid("author_id")
	511	.notNull()
	512	.references(() => users.id),
	513	title: text("title").notNull(),
	514	body: text("body"),
	515	state: text("state").notNull().default("open"), // open, closed, merged
	516	baseBranch: text("base_branch").notNull(),
	517	headBranch: text("head_branch").notNull(),
3ef4c9dClaude	518	isDraft: boolean("is_draft").default(false).notNull(),
	519	mergeStrategy: text("merge_strategy").default("merge").notNull(), // merge, squash, rebase
	520	milestoneId: uuid("milestone_id"),
0074234Claude	521	mergedAt: timestamp("merged_at"),
	522	mergedBy: uuid("merged_by").references(() => users.id),
	523	createdAt: timestamp("created_at").defaultNow().notNull(),
	524	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	525	closedAt: timestamp("closed_at"),
	526	},
	527	(table) => [
	528	index("prs_repo_state").on(table.repositoryId, table.state),
	529	index("prs_repo_number").on(table.repositoryId, table.number),
	530	]
	531	);
	532
	533	export const prComments = pgTable(
	534	"pr_comments",
	535	{
	536	id: uuid("id").primaryKey().defaultRandom(),
	537	pullRequestId: uuid("pull_request_id")
	538	.notNull()
	539	.references(() => pullRequests.id, { onDelete: "cascade" }),
	540	authorId: uuid("author_id")
	541	.notNull()
	542	.references(() => users.id),
	543	body: text("body").notNull(),
	544	isAiReview: boolean("is_ai_review").default(false).notNull(),
	545	filePath: text("file_path"),
	546	lineNumber: integer("line_number"),
	547	createdAt: timestamp("created_at").defaultNow().notNull(),
	548	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	549	},
	550	(table) => [index("pr_comments_pr").on(table.pullRequestId)]
	551	);
	552
	553	export const activityFeed = pgTable(
	554	"activity_feed",
	555	{
	556	id: uuid("id").primaryKey().defaultRandom(),
	557	repositoryId: uuid("repository_id")
	558	.notNull()
	559	.references(() => repositories.id, { onDelete: "cascade" }),
	560	userId: uuid("user_id").references(() => users.id),
	561	action: text("action").notNull(), // push, issue_open, issue_close, pr_open, pr_merge, star, comment
	562	targetType: text("target_type"), // issue, pr, commit
	563	targetId: text("target_id"),
	564	metadata: text("metadata"), // JSON string for extra data
	565	createdAt: timestamp("created_at").defaultNow().notNull(),
	566	},
	567	(table) => [
	568	index("activity_repo").on(table.repositoryId),
	569	index("activity_user").on(table.userId),
	570	]
	571	);
	572
c81ab7aClaude	573	export const webhooks = pgTable(
	574	"webhooks",
	575	{
	576	id: uuid("id").primaryKey().defaultRandom(),
	577	repositoryId: uuid("repository_id")
	578	.notNull()
	579	.references(() => repositories.id, { onDelete: "cascade" }),
	580	url: text("url").notNull(),
	581	secret: text("secret"),
	582	events: text("events").notNull().default("push"), // comma-separated: push,issue,pr
	583	isActive: boolean("is_active").default(true).notNull(),
	584	lastDeliveredAt: timestamp("last_delivered_at"),
	585	lastStatus: integer("last_status"),
	586	createdAt: timestamp("created_at").defaultNow().notNull(),
	587	},
	588	(table) => [index("webhooks_repo").on(table.repositoryId)]
	589	);
	590
	591	export const apiTokens = pgTable("api_tokens", {
	592	id: uuid("id").primaryKey().defaultRandom(),
	593	userId: uuid("user_id")
	594	.notNull()
	595	.references(() => users.id, { onDelete: "cascade" }),
	596	name: text("name").notNull(),
	597	tokenHash: text("token_hash").notNull(),
	598	tokenPrefix: text("token_prefix").notNull(), // first 8 chars for display
	599	scopes: text("scopes").notNull().default("repo"), // comma-separated
	600	lastUsedAt: timestamp("last_used_at"),
	601	expiresAt: timestamp("expires_at"),
	602	createdAt: timestamp("created_at").defaultNow().notNull(),
	603	});
	604
	605	export const repoTopics = pgTable(
	606	"repo_topics",
	607	{
	608	id: uuid("id").primaryKey().defaultRandom(),
	609	repositoryId: uuid("repository_id")
	610	.notNull()
	611	.references(() => repositories.id, { onDelete: "cascade" }),
	612	topic: text("topic").notNull(),
	613	},
	614	(table) => [
	615	uniqueIndex("repo_topics_unique").on(table.repositoryId, table.topic),
	616	index("topics_name").on(table.topic),
	617	]
	618	);
	619
fc1817aClaude	620	export const sshKeys = pgTable("ssh_keys", {
	621	id: uuid("id").primaryKey().defaultRandom(),
	622	userId: uuid("user_id")
	623	.notNull()
06d5ffeClaude	624	.references(() => users.id, { onDelete: "cascade" }),
fc1817aClaude	625	title: text("title").notNull(),
	626	fingerprint: text("fingerprint").notNull(),
	627	publicKey: text("public_key").notNull(),
	628	lastUsedAt: timestamp("last_used_at"),
	629	createdAt: timestamp("created_at").defaultNow().notNull(),
	630	});
	631
	632	export type User = typeof users.$inferSelect;
	633	export type NewUser = typeof users.$inferInsert;
	634	export type Repository = typeof repositories.$inferSelect;
	635	export type NewRepository = typeof repositories.$inferInsert;
06d5ffeClaude	636	export type Session = typeof sessions.$inferSelect;
	637	export type Star = typeof stars.$inferSelect;
	638	export type SshKey = typeof sshKeys.$inferSelect;
79136bbClaude	639	export type Issue = typeof issues.$inferSelect;
	640	export type IssueComment = typeof issueComments.$inferSelect;
	641	export type Label = typeof labels.$inferSelect;
0074234Claude	642	export type PullRequest = typeof pullRequests.$inferSelect;
	643	export type PrComment = typeof prComments.$inferSelect;
	644	export type ActivityEntry = typeof activityFeed.$inferSelect;
c81ab7aClaude	645	export type Webhook = typeof webhooks.$inferSelect;
	646	export type ApiToken = typeof apiTokens.$inferSelect;
	647	export type RepoTopic = typeof repoTopics.$inferSelect;
3ef4c9dClaude	648	export type RepoSettings = typeof repoSettings.$inferSelect;
	649	export type BranchProtection = typeof branchProtection.$inferSelect;
	650	export type GateRun = typeof gateRuns.$inferSelect;
	651	export type Notification = typeof notifications.$inferSelect;
	652	export type Release = typeof releases.$inferSelect;
	653	export type Milestone = typeof milestones.$inferSelect;
	654	export type Reaction = typeof reactions.$inferSelect;
	655	export type PrReview = typeof prReviews.$inferSelect;
	656	export type CodeOwner = typeof codeOwners.$inferSelect;
	657	export type AiChat = typeof aiChats.$inferSelect;
	658	export type AuditLogEntry = typeof auditLog.$inferSelect;
	659	export type Deployment = typeof deployments.$inferSelect;
24cf2caClaude	660
	661	/**
	662	* Saved replies — per-user canned responses, insertable into any
	663	* issue / PR comment textarea. Shortcut name must be unique per user.
	664	*/
	665	export const savedReplies = pgTable(
	666	"saved_replies",
	667	{
	668	id: uuid("id").primaryKey().defaultRandom(),
	669	userId: uuid("user_id")
	670	.notNull()
	671	.references(() => users.id, { onDelete: "cascade" }),
	672	shortcut: text("shortcut").notNull(),
	673	body: text("body").notNull(),
	674	createdAt: timestamp("created_at").defaultNow().notNull(),
	675	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	676	},
	677	(table) => [
	678	uniqueIndex("saved_replies_user_shortcut").on(table.userId, table.shortcut),
	679	]
	680	);
	681
	682	export type SavedReply = typeof savedReplies.$inferSelect;
5cc5d95Claude	683
	684	/**
	685	* Organizations (Block B1) — multi-user namespaces. Distinct from `users`.
	686	* An org has members (with org-level roles) and may contain teams.
	687	* Repos can be owned by an org via `repositories.orgId` (added in Block B2).
	688	*
	689	* Slug is globally unique against itself; collision with a username is
	690	* checked at create time in the route handler (no DB-level cross-table
	691	* uniqueness in Postgres).
	692	*/
	693	export const organizations = pgTable("organizations", {
	694	id: uuid("id").primaryKey().defaultRandom(),
	695	slug: text("slug").notNull().unique(),
	696	name: text("name").notNull(),
	697	description: text("description"),
	698	avatarUrl: text("avatar_url"),
	699	billingEmail: text("billing_email"),
	700	createdById: uuid("created_by_id")
	701	.notNull()
	702	.references(() => users.id, { onDelete: "restrict" }),
	703	createdAt: timestamp("created_at").defaultNow().notNull(),
	704	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	705	});
	706
	707	/**
	708	* Org membership. Roles: owner (full control, billing), admin (manage
	709	* members + teams + repos), member (default; can be added to teams).
	710	*/
	711	export const orgMembers = pgTable(
	712	"org_members",
	713	{
	714	id: uuid("id").primaryKey().defaultRandom(),
	715	orgId: uuid("org_id")
	716	.notNull()
	717	.references(() => organizations.id, { onDelete: "cascade" }),
	718	userId: uuid("user_id")
	719	.notNull()
	720	.references(() => users.id, { onDelete: "cascade" }),
	721	role: text("role").notNull().default("member"), // owner \| admin \| member
	722	createdAt: timestamp("created_at").defaultNow().notNull(),
	723	},
	724	(table) => [
	725	uniqueIndex("org_members_unique").on(table.orgId, table.userId),
	726	index("org_members_user").on(table.userId),
	727	]
	728	);
	729
	730	/**
	731	* Teams within an org. Slug is unique within an org.
	732	* `parentTeamId` allows nesting (GitHub-style child teams). Optional.
	733	*/
	734	export const teams = pgTable(
	735	"teams",
	736	{
	737	id: uuid("id").primaryKey().defaultRandom(),
	738	orgId: uuid("org_id")
	739	.notNull()
	740	.references(() => organizations.id, { onDelete: "cascade" }),
	741	slug: text("slug").notNull(),
	742	name: text("name").notNull(),
	743	description: text("description"),
	744	parentTeamId: uuid("parent_team_id"),
	745	createdAt: timestamp("created_at").defaultNow().notNull(),
	746	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	747	},
	748	(table) => [uniqueIndex("teams_org_slug").on(table.orgId, table.slug)]
	749	);
	750
	751	/**
	752	* Team membership. Roles: maintainer (can edit team), member (default).
	753	* A user can belong to many teams; team membership requires org membership
	754	* but that invariant is enforced at the route layer, not the DB layer.
	755	*/
	756	export const teamMembers = pgTable(
	757	"team_members",
	758	{
	759	id: uuid("id").primaryKey().defaultRandom(),
	760	teamId: uuid("team_id")
	761	.notNull()
	762	.references(() => teams.id, { onDelete: "cascade" }),
	763	userId: uuid("user_id")
	764	.notNull()
	765	.references(() => users.id, { onDelete: "cascade" }),
	766	role: text("role").notNull().default("member"), // maintainer \| member
	767	createdAt: timestamp("created_at").defaultNow().notNull(),
	768	},
	769	(table) => [
	770	uniqueIndex("team_members_unique").on(table.teamId, table.userId),
	771	index("team_members_user").on(table.userId),
	772	]
	773	);
	774
	775	export type Organization = typeof organizations.$inferSelect;
	776	export type OrgMember = typeof orgMembers.$inferSelect;
	777	export type Team = typeof teams.$inferSelect;
	778	export type TeamMember = typeof teamMembers.$inferSelect;
	779	export type OrgRole = "owner" \| "admin" \| "member";
	780	export type TeamRole = "maintainer" \| "member";
7298a17Claude	781
	782	/**
	783	* 2FA / TOTP (Block B4).
	784	*
	785	* Secret is stored in plain Base32 for now — the DB has row-level-secure
	786	* access and the app boundary is the only code that reads it. A follow-up
	787	* (B4.1) will wrap it with AES-GCM at rest once we standardise the KEK.
	788	*
	789	* `enabledAt` is set only after the user has successfully entered their
	790	* first code (confirming the authenticator was set up correctly). Rows with
	791	* `enabledAt = NULL` represent pending enrolment.
	792	*/
	793	export const userTotp = pgTable("user_totp", {
	794	userId: uuid("user_id")
	795	.primaryKey()
	796	.references(() => users.id, { onDelete: "cascade" }),
	797	secret: text("secret").notNull(),
	798	enabledAt: timestamp("enabled_at"),
	799	lastUsedAt: timestamp("last_used_at"),
	800	createdAt: timestamp("created_at").defaultNow().notNull(),
	801	});
	802
	803	/**
	804	* Recovery codes — single-use fallback when the authenticator is lost.
	805	* Stored as SHA-256 hashes; used rows are marked with `usedAt` rather than
	806	* deleted so the audit log keeps the full history.
	807	*/
	808	export const userRecoveryCodes = pgTable(
	809	"user_recovery_codes",
	810	{
	811	id: uuid("id").primaryKey().defaultRandom(),
	812	userId: uuid("user_id")
	813	.notNull()
	814	.references(() => users.id, { onDelete: "cascade" }),
	815	codeHash: text("code_hash").notNull(),
	816	usedAt: timestamp("used_at"),
	817	createdAt: timestamp("created_at").defaultNow().notNull(),
	818	},
	819	(table) => [
	820	index("recovery_codes_user").on(table.userId),
	821	uniqueIndex("recovery_codes_user_hash").on(table.userId, table.codeHash),
	822	]
	823	);
	824
	825	export type UserTotp = typeof userTotp.$inferSelect;
	826	export type UserRecoveryCode = typeof userRecoveryCodes.$inferSelect;
2df1f8cClaude	827
	828	/**
	829	* WebAuthn passkeys (Block B5).
	830	*
	831	* Each row is one registered authenticator. The `credentialId` is the
	832	* globally-unique identifier the browser returns; `publicKey` is the
	833	* COSE-encoded public key we use to verify signatures. `counter` tracks
	834	* the authenticator's signature counter for replay-protection.
	835	*
	836	* `transports` is a JSON array (stored as text) of the
	837	* AuthenticatorTransport values ("usb" \| "nfc" \| "ble" \| "internal" \| "hybrid").
	838	*/
	839	export const userPasskeys = pgTable(
	840	"user_passkeys",
	841	{
	842	id: uuid("id").primaryKey().defaultRandom(),
	843	userId: uuid("user_id")
	844	.notNull()
	845	.references(() => users.id, { onDelete: "cascade" }),
	846	credentialId: text("credential_id").notNull().unique(),
	847	publicKey: text("public_key").notNull(), // base64url of COSE key
	848	counter: integer("counter").default(0).notNull(),
	849	transports: text("transports"), // JSON array string
	850	name: text("name").notNull().default("Passkey"),
	851	lastUsedAt: timestamp("last_used_at"),
	852	createdAt: timestamp("created_at").defaultNow().notNull(),
	853	},
	854	(table) => [index("passkeys_user").on(table.userId)]
	855	);
	856
	857	/**
	858	* Short-lived WebAuthn challenges. A row is written when we issue options
	859	* (registration or authentication) and deleted after the verify step or when
	860	* it expires (5 min). Keeping them in the DB lets us verify without sticky
	861	* sessions or client-side state.
	862	*/
	863	export const webauthnChallenges = pgTable(
	864	"webauthn_challenges",
	865	{
	866	id: uuid("id").primaryKey().defaultRandom(),
	867	userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
	868	// For passwordless login we don't know the user yet, so userId is nullable
	869	// and we bind the challenge to a short-lived cookie token instead.
	870	sessionKey: text("session_key").notNull().unique(),
	871	challenge: text("challenge").notNull(),
	872	kind: text("kind").notNull(), // "register" \| "authenticate"
	873	expiresAt: timestamp("expires_at").notNull(),
	874	createdAt: timestamp("created_at").defaultNow().notNull(),
	875	},
	876	(table) => [index("webauthn_challenges_expires").on(table.expiresAt)]
	877	);
	878
	879	export type UserPasskey = typeof userPasskeys.$inferSelect;
	880	export type WebauthnChallenge = typeof webauthnChallenges.$inferSelect;
bfdb5e7Claude	881
	882	/**
	883	* OAuth 2.0 provider (Block B6).
	884	*
	885	* `oauthApps` is a third-party app registered by a developer. Each app has
	886	* a public `client_id`, a hashed `client_secret`, and one or more allowed
	887	* `redirect_uris` (newline-separated). The plaintext secret is shown to the
	888	* developer exactly once at creation and cannot be recovered; they can
	889	* rotate it instead.
	890	*
	891	* `oauthAuthorizations` is a short-lived authorization code issued after
	892	* the user consents at /oauth/authorize. Single-use: `usedAt` is set on
	893	* redemption so a replay after-the-fact fails.
	894	*
	895	* `oauthAccessTokens` is a long-lived bearer token plus an optional
	896	* refresh token. Both are stored as SHA-256 hashes; the plaintext values
	897	* are only returned to the client once in the /oauth/token response.
	898	*/
	899	export const oauthApps = pgTable(
	900	"oauth_apps",
	901	{
	902	id: uuid("id").primaryKey().defaultRandom(),
	903	ownerId: uuid("owner_id")
	904	.notNull()
	905	.references(() => users.id, { onDelete: "cascade" }),
	906	name: text("name").notNull(),
	907	clientId: text("client_id").notNull().unique(),
	908	clientSecretHash: text("client_secret_hash").notNull(),
	909	clientSecretPrefix: text("client_secret_prefix").notNull(), // first 8 chars for display
	910	/** Newline-separated list of allowed redirect URIs. */
	911	redirectUris: text("redirect_uris").notNull(),
	912	homepageUrl: text("homepage_url"),
	913	description: text("description"),
	914	/**
	915	* If `true`, the app must present its client_secret at /oauth/token.
	916	* Public SPA/mobile apps should set this to `false` and use PKCE.
	917	*/
	918	confidential: boolean("confidential").default(true).notNull(),
	919	revokedAt: timestamp("revoked_at"),
	920	createdAt: timestamp("created_at").defaultNow().notNull(),
	921	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	922	},
	923	(table) => [index("oauth_apps_owner").on(table.ownerId)]
	924	);
	925
	926	export const oauthAuthorizations = pgTable(
	927	"oauth_authorizations",
	928	{
	929	id: uuid("id").primaryKey().defaultRandom(),
	930	appId: uuid("app_id")
	931	.notNull()
	932	.references(() => oauthApps.id, { onDelete: "cascade" }),
	933	userId: uuid("user_id")
	934	.notNull()
	935	.references(() => users.id, { onDelete: "cascade" }),
	936	codeHash: text("code_hash").notNull().unique(),
	937	redirectUri: text("redirect_uri").notNull(),
	938	scopes: text("scopes").notNull().default(""),
	939	codeChallenge: text("code_challenge"),
	940	codeChallengeMethod: text("code_challenge_method"), // "S256" \| "plain"
	941	expiresAt: timestamp("expires_at").notNull(),
	942	usedAt: timestamp("used_at"),
	943	createdAt: timestamp("created_at").defaultNow().notNull(),
	944	},
	945	(table) => [index("oauth_authorizations_expires").on(table.expiresAt)]
	946	);
	947
	948	export const oauthAccessTokens = pgTable(
	949	"oauth_access_tokens",
	950	{
	951	id: uuid("id").primaryKey().defaultRandom(),
	952	appId: uuid("app_id")
	953	.notNull()
	954	.references(() => oauthApps.id, { onDelete: "cascade" }),
	955	userId: uuid("user_id")
	956	.notNull()
	957	.references(() => users.id, { onDelete: "cascade" }),
	958	accessTokenHash: text("access_token_hash").notNull().unique(),
	959	refreshTokenHash: text("refresh_token_hash").unique(),
	960	scopes: text("scopes").notNull().default(""),
	961	expiresAt: timestamp("expires_at").notNull(),
	962	refreshExpiresAt: timestamp("refresh_expires_at"),
	963	revokedAt: timestamp("revoked_at"),
	964	lastUsedAt: timestamp("last_used_at"),
	965	createdAt: timestamp("created_at").defaultNow().notNull(),
	966	},
	967	(table) => [
	968	index("oauth_access_tokens_user").on(table.userId),
	969	index("oauth_access_tokens_app").on(table.appId),
	970	index("oauth_access_tokens_expires").on(table.expiresAt),
	971	]
	972	);
	973
	974	export type OauthApp = typeof oauthApps.$inferSelect;
	975	export type OauthAuthorization = typeof oauthAuthorizations.$inferSelect;
	976	export type OauthAccessToken = typeof oauthAccessTokens.$inferSelect;
eafe8c6Claude	977
	978	/**
	979	* Actions-equivalent workflow runner (Block C1).
	980	*
	981	* `workflows` rows are the YAML files discovered at `.gluecron/workflows/*.yml`
	982	* on the repo's default branch. `parsed` is the normalised JSON form used by
	983	* the runner so we don't re-parse on every trigger.
	984	*
	985	* `workflow_runs` is one execution: one row per trigger event. Status
	986	* progression: queued → running → success\|failure\|cancelled. `conclusion`
	987	* stays null until `status` is terminal.
	988	*
	989	* `workflow_jobs` is a single job within a run — each has its own steps
	990	* array and concatenated logs. We keep logs inline for v1 (no streaming)
	991	* to avoid a fifth table; they're truncated at the runner.
	992	*
	993	* `workflow_artifacts` persist files a job uploaded. `content` is a bytea;
	994	* we'll move this to object storage once we hit size limits.
	995	*/
	996	export const workflows = pgTable(
	997	"workflows",
	998	{
	999	id: uuid("id").primaryKey().defaultRandom(),
	1000	repositoryId: uuid("repository_id")
	1001	.notNull()
	1002	.references(() => repositories.id, { onDelete: "cascade" }),
	1003	name: text("name").notNull(),
	1004	path: text("path").notNull(), // e.g. ".gluecron/workflows/ci.yml"
	1005	yaml: text("yaml").notNull(),
	1006	parsed: text("parsed").notNull(), // JSON string
	1007	onEvents: text("on_events").notNull().default("[]"), // JSON array of event names
	1008	disabled: boolean("disabled").default(false).notNull(),
	1009	createdAt: timestamp("created_at").defaultNow().notNull(),
	1010	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1011	},
	1012	(table) => [
	1013	index("workflows_repo").on(table.repositoryId),
	1014	uniqueIndex("workflows_repo_path").on(table.repositoryId, table.path),
	1015	]
	1016	);
	1017
	1018	export const workflowRuns = pgTable(
	1019	"workflow_runs",
	1020	{
	1021	id: uuid("id").primaryKey().defaultRandom(),
	1022	workflowId: uuid("workflow_id")
	1023	.notNull()
	1024	.references(() => workflows.id, { onDelete: "cascade" }),
	1025	repositoryId: uuid("repository_id")
	1026	.notNull()
	1027	.references(() => repositories.id, { onDelete: "cascade" }),
	1028	runNumber: integer("run_number").notNull(),
	1029	event: text("event").notNull(), // "push" \| "pull_request" \| "manual" \| ...
	1030	ref: text("ref"),
	1031	commitSha: text("commit_sha"),
	1032	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1033	onDelete: "set null",
	1034	}),
	1035	status: text("status").notNull().default("queued"), // queued\|running\|success\|failure\|cancelled
	1036	conclusion: text("conclusion"),
	1037	queuedAt: timestamp("queued_at").defaultNow().notNull(),
	1038	startedAt: timestamp("started_at"),
	1039	finishedAt: timestamp("finished_at"),
	1040	createdAt: timestamp("created_at").defaultNow().notNull(),
	1041	},
	1042	(table) => [
	1043	index("workflow_runs_repo").on(table.repositoryId),
	1044	index("workflow_runs_status").on(table.status),
	1045	index("workflow_runs_workflow").on(table.workflowId),
	1046	]
	1047	);
	1048
	1049	export const workflowJobs = pgTable(
	1050	"workflow_jobs",
	1051	{
	1052	id: uuid("id").primaryKey().defaultRandom(),
	1053	runId: uuid("run_id")
	1054	.notNull()
	1055	.references(() => workflowRuns.id, { onDelete: "cascade" }),
	1056	name: text("name").notNull(),
	1057	jobOrder: integer("job_order").default(0).notNull(),
	1058	runsOn: text("runs_on").notNull().default("default"),
	1059	status: text("status").notNull().default("queued"),
	1060	conclusion: text("conclusion"),
	1061	exitCode: integer("exit_code"),
	1062	steps: text("steps").notNull().default("[]"), // JSON array of step results
	1063	logs: text("logs").notNull().default(""),
	1064	startedAt: timestamp("started_at"),
	1065	finishedAt: timestamp("finished_at"),
	1066	createdAt: timestamp("created_at").defaultNow().notNull(),
	1067	},
	1068	(table) => [index("workflow_jobs_run").on(table.runId)]
	1069	);
	1070
	1071	export const workflowArtifacts = pgTable(
	1072	"workflow_artifacts",
	1073	{
	1074	id: uuid("id").primaryKey().defaultRandom(),
	1075	runId: uuid("run_id")
	1076	.notNull()
	1077	.references(() => workflowRuns.id, { onDelete: "cascade" }),
	1078	jobId: uuid("job_id").references(() => workflowJobs.id, {
	1079	onDelete: "set null",
	1080	}),
	1081	name: text("name").notNull(),
	1082	sizeBytes: integer("size_bytes").default(0).notNull(),
	1083	contentType: text("content_type")
	1084	.default("application/octet-stream")
	1085	.notNull(),
	1086	// bytea — drizzle doesn't have a built-in bytea type at the level we use
	1087	// elsewhere; store as text (base64) for v1. Migration uses real bytea so
	1088	// we can swap the column type later.
	1089	content: text("content"),
	1090	createdAt: timestamp("created_at").defaultNow().notNull(),
	1091	},
	1092	(table) => [index("workflow_artifacts_run").on(table.runId)]
	1093	);
	1094
	1095	export type Workflow = typeof workflows.$inferSelect;
	1096	export type WorkflowRun = typeof workflowRuns.$inferSelect;
	1097	export type WorkflowJob = typeof workflowJobs.$inferSelect;
	1098	export type WorkflowArtifact = typeof workflowArtifacts.$inferSelect;
e2da5c6Claude	1099
	1100	// ---------------------------------------------------------------------------
	1101	// Block C2 — Package registry (npm-compatible)
	1102	// ---------------------------------------------------------------------------
	1103
	1104	export const packages = pgTable(
	1105	"packages",
	1106	{
	1107	id: uuid("id").primaryKey().defaultRandom(),
	1108	repositoryId: uuid("repository_id")
	1109	.notNull()
	1110	.references(() => repositories.id, { onDelete: "cascade" }),
	1111	ecosystem: text("ecosystem").notNull().default("npm"), // "npm" \| "container"
	1112	scope: text("scope"), // "@acme" for npm; null for unscoped
	1113	name: text("name").notNull(), // "my-lib" (without scope)
	1114	description: text("description"),
	1115	readme: text("readme"),
	1116	homepage: text("homepage"),
	1117	license: text("license"),
	1118	visibility: text("visibility").notNull().default("public"), // "public" \| "private"
	1119	createdAt: timestamp("created_at").defaultNow().notNull(),
	1120	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1121	},
	1122	(table) => [
	1123	index("packages_repo").on(table.repositoryId),
	1124	uniqueIndex("packages_eco_scope_name").on(
	1125	table.ecosystem,
	1126	table.scope,
	1127	table.name
	1128	),
	1129	]
	1130	);
	1131
	1132	export const packageVersions = pgTable(
	1133	"package_versions",
	1134	{
	1135	id: uuid("id").primaryKey().defaultRandom(),
	1136	packageId: uuid("package_id")
	1137	.notNull()
	1138	.references(() => packages.id, { onDelete: "cascade" }),
	1139	version: text("version").notNull(), // "1.2.3"
	1140	shasum: text("shasum").notNull(), // sha1 (for npm compat) hex
	1141	integrity: text("integrity"), // "sha512-..." base64
	1142	sizeBytes: integer("size_bytes").default(0).notNull(),
	1143	metadata: text("metadata").notNull().default("{}"), // package.json JSON
	1144	tarball: text("tarball"), // base64-encoded; bytea in migration
	1145	publishedBy: uuid("published_by").references(() => users.id, {
	1146	onDelete: "set null",
	1147	}),
	1148	yanked: boolean("yanked").default(false).notNull(),
	1149	yankedReason: text("yanked_reason"),
	1150	publishedAt: timestamp("published_at").defaultNow().notNull(),
	1151	},
	1152	(table) => [
	1153	index("package_versions_pkg").on(table.packageId),
	1154	uniqueIndex("package_versions_pkg_version").on(table.packageId, table.version),
	1155	]
	1156	);
	1157
	1158	export const packageTags = pgTable(
	1159	"package_tags",
	1160	{
	1161	id: uuid("id").primaryKey().defaultRandom(),
	1162	packageId: uuid("package_id")
	1163	.notNull()
	1164	.references(() => packages.id, { onDelete: "cascade" }),
	1165	tag: text("tag").notNull(), // "latest" \| "beta" \| ...
	1166	versionId: uuid("version_id")
	1167	.notNull()
	1168	.references(() => packageVersions.id, { onDelete: "cascade" }),
	1169	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1170	},
	1171	(table) => [
	1172	uniqueIndex("package_tags_pkg_tag").on(table.packageId, table.tag),
	1173	]
	1174	);
	1175
	1176	export type Package = typeof packages.$inferSelect;
	1177	export type PackageVersion = typeof packageVersions.$inferSelect;
	1178	export type PackageTag = typeof packageTags.$inferSelect;
	1179
	1180	// ---------------------------------------------------------------------------
	1181	// Block C3 — Pages / static hosting
	1182	// ---------------------------------------------------------------------------
	1183
	1184	export const pagesDeployments = pgTable(
	1185	"pages_deployments",
	1186	{
	1187	id: uuid("id").primaryKey().defaultRandom(),
	1188	repositoryId: uuid("repository_id")
	1189	.notNull()
	1190	.references(() => repositories.id, { onDelete: "cascade" }),
	1191	ref: text("ref").notNull().default("refs/heads/gh-pages"),
	1192	commitSha: text("commit_sha").notNull(),
	1193	status: text("status").notNull().default("success"), // "success" \| "failed"
	1194	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1195	onDelete: "set null",
	1196	}),
	1197	createdAt: timestamp("created_at").defaultNow().notNull(),
	1198	},
	1199	(table) => [
	1200	index("pages_deployments_repo").on(table.repositoryId),
	1201	index("pages_deployments_created").on(table.createdAt),
	1202	]
	1203	);
	1204
	1205	export const pagesSettings = pgTable("pages_settings", {
	1206	repositoryId: uuid("repository_id")
	1207	.primaryKey()
	1208	.references(() => repositories.id, { onDelete: "cascade" }),
	1209	enabled: boolean("enabled").default(true).notNull(),
	1210	sourceBranch: text("source_branch").notNull().default("gh-pages"),
	1211	sourceDir: text("source_dir").notNull().default("/"), // e.g. "/" or "/docs"
	1212	customDomain: text("custom_domain"),
	1213	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1214	});
	1215
	1216	export type PagesDeployment = typeof pagesDeployments.$inferSelect;
	1217	export type PagesSettings = typeof pagesSettings.$inferSelect;
	1218
	1219	// ---------------------------------------------------------------------------
	1220	// Block C4 — Environments with protected approvals
	1221	// ---------------------------------------------------------------------------
	1222
	1223	export const environments = pgTable(
	1224	"environments",
	1225	{
	1226	id: uuid("id").primaryKey().defaultRandom(),
	1227	repositoryId: uuid("repository_id")
	1228	.notNull()
	1229	.references(() => repositories.id, { onDelete: "cascade" }),
	1230	name: text("name").notNull(), // "production" \| "staging" \| "preview"
	1231	requireApproval: boolean("require_approval").default(false).notNull(),
	1232	// JSON array of user IDs that can approve deploys.
	1233	reviewers: text("reviewers").notNull().default("[]"),
	1234	waitTimerMinutes: integer("wait_timer_minutes").default(0).notNull(),
	1235	allowedBranches: text("allowed_branches").notNull().default("[]"), // JSON glob patterns
	1236	createdAt: timestamp("created_at").defaultNow().notNull(),
	1237	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1238	},
	1239	(table) => [
	1240	uniqueIndex("environments_repo_name").on(table.repositoryId, table.name),
	1241	]
	1242	);
	1243
	1244	export const deploymentApprovals = pgTable(
	1245	"deployment_approvals",
	1246	{
	1247	id: uuid("id").primaryKey().defaultRandom(),
	1248	deploymentId: uuid("deployment_id")
	1249	.notNull()
	1250	.references(() => deployments.id, { onDelete: "cascade" }),
	1251	userId: uuid("user_id")
	1252	.notNull()
	1253	.references(() => users.id, { onDelete: "cascade" }),
	1254	decision: text("decision").notNull(), // "approved" \| "rejected"
	1255	comment: text("comment"),
	1256	createdAt: timestamp("created_at").defaultNow().notNull(),
	1257	},
	1258	(table) => [
	1259	index("deployment_approvals_deployment").on(table.deploymentId),
	1260	]
	1261	);
	1262
	1263	export type Environment = typeof environments.$inferSelect;
	1264	export type DeploymentApproval = typeof deploymentApprovals.$inferSelect;
3cbe3d6Claude	1265
	1266	// ---------------------------------------------------------------------------
	1267	// Block D — AI-native differentiation (migration 0012)
	1268	// ---------------------------------------------------------------------------
	1269
	1270	// D6 — cached "explain this codebase" markdown keyed on commit sha.
	1271	export const codebaseExplanations = pgTable(
	1272	"codebase_explanations",
	1273	{
	1274	id: uuid("id").primaryKey().defaultRandom(),
	1275	repositoryId: uuid("repository_id")
	1276	.notNull()
	1277	.references(() => repositories.id, { onDelete: "cascade" }),
	1278	commitSha: text("commit_sha").notNull(),
	1279	summary: text("summary").notNull(),
	1280	markdown: text("markdown").notNull(),
	1281	model: text("model").notNull(),
	1282	generatedAt: timestamp("generated_at").defaultNow().notNull(),
	1283	},
	1284	(table) => [
	1285	uniqueIndex("codebase_explanations_repo_sha").on(
	1286	table.repositoryId,
	1287	table.commitSha
	1288	),
	1289	]
	1290	);
	1291
	1292	// D2 — AI dependency bumper run history.
	1293	export const depUpdateRuns = pgTable(
	1294	"dep_update_runs",
	1295	{
	1296	id: uuid("id").primaryKey().defaultRandom(),
	1297	repositoryId: uuid("repository_id")
	1298	.notNull()
	1299	.references(() => repositories.id, { onDelete: "cascade" }),
	1300	status: text("status").notNull().default("pending"), // pending\|running\|success\|failed\|no_updates
	1301	ecosystem: text("ecosystem").notNull(), // npm\|bun
	1302	manifestPath: text("manifest_path").notNull(),
	1303	attemptedBumps: text("attempted_bumps").notNull().default("[]"), // JSON
	1304	appliedBumps: text("applied_bumps").notNull().default("[]"), // JSON
	1305	branchName: text("branch_name"),
	1306	prNumber: integer("pr_number"),
	1307	errorMessage: text("error_message"),
	1308	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1309	onDelete: "set null",
	1310	}),
	1311	createdAt: timestamp("created_at").defaultNow().notNull(),
	1312	completedAt: timestamp("completed_at"),
	1313	},
	1314	(table) => [
	1315	index("dep_update_runs_repo").on(table.repositoryId),
	1316	index("dep_update_runs_created").on(table.createdAt),
	1317	]
	1318	);
	1319
	1320	// D1 — code chunks for semantic search. Embedding stored as JSON-encoded
	1321	// number array in text to avoid requiring pgvector; cosine similarity is
	1322	// computed in JS. Upgrade path: ALTER COLUMN embedding TYPE vector(1024).
	1323	export const codeChunks = pgTable(
	1324	"code_chunks",
	1325	{
	1326	id: uuid("id").primaryKey().defaultRandom(),
	1327	repositoryId: uuid("repository_id")
	1328	.notNull()
	1329	.references(() => repositories.id, { onDelete: "cascade" }),
	1330	commitSha: text("commit_sha").notNull(),
	1331	path: text("path").notNull(),
	1332	startLine: integer("start_line").notNull(),
	1333	endLine: integer("end_line").notNull(),
	1334	content: text("content").notNull(),
	1335	embedding: text("embedding"), // JSON number[]
	1336	embeddingModel: text("embedding_model"),
	1337	createdAt: timestamp("created_at").defaultNow().notNull(),
	1338	},
	1339	(table) => [
	1340	index("code_chunks_repo").on(table.repositoryId),
	1341	index("code_chunks_repo_path").on(table.repositoryId, table.path),
	1342	]
	1343	);
	1344
	1345	export type CodebaseExplanation = typeof codebaseExplanations.$inferSelect;
	1346	export type DepUpdateRun = typeof depUpdateRuns.$inferSelect;
1e162a8Claude	1347
	1348	// ---------------------------------------------------------------------------
	1349	// Block E2 — Discussions (migration 0013)
	1350	// ---------------------------------------------------------------------------
	1351
	1352	/**
	1353	* Discussions — forum-style threaded conversations attached to a repo.
	1354	* Similar to GitHub Discussions: categorised + pinnable + answerable.
	1355	*/
	1356	export const discussions = pgTable(
	1357	"discussions",
	1358	{
	1359	id: uuid("id").primaryKey().defaultRandom(),
	1360	number: serial("number"),
	1361	repositoryId: uuid("repository_id")
	1362	.notNull()
	1363	.references(() => repositories.id, { onDelete: "cascade" }),
	1364	authorId: uuid("author_id")
	1365	.notNull()
	1366	.references(() => users.id),
	1367	// one of: "general" \| "q-and-a" \| "ideas" \| "announcements" \| "show-and-tell"
	1368	category: text("category").notNull().default("general"),
	1369	title: text("title").notNull(),
	1370	body: text("body"),
	1371	state: text("state").notNull().default("open"), // open, closed
	1372	locked: boolean("locked").notNull().default(false),
	1373	answerCommentId: uuid("answer_comment_id"),
	1374	pinned: boolean("pinned").notNull().default(false),
	1375	createdAt: timestamp("created_at").defaultNow().notNull(),
	1376	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1377	},
	1378	(table) => [
	1379	index("discussions_repo").on(table.repositoryId),
	1380	uniqueIndex("discussions_repo_number").on(
	1381	table.repositoryId,
	1382	table.number
	1383	),
	1384	]
	1385	);
	1386
	1387	export const discussionComments = pgTable(
	1388	"discussion_comments",
	1389	{
	1390	id: uuid("id").primaryKey().defaultRandom(),
	1391	discussionId: uuid("discussion_id")
	1392	.notNull()
	1393	.references(() => discussions.id, { onDelete: "cascade" }),
	1394	parentCommentId: uuid("parent_comment_id"),
	1395	authorId: uuid("author_id")
	1396	.notNull()
	1397	.references(() => users.id),
	1398	body: text("body").notNull(),
	1399	isAnswer: boolean("is_answer").notNull().default(false),
	1400	createdAt: timestamp("created_at").defaultNow().notNull(),
	1401	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1402	},
	1403	(table) => [
	1404	index("discussion_comments_discussion").on(table.discussionId),
	1405	]
	1406	);
	1407
	1408	export type Discussion = typeof discussions.$inferSelect;
	1409	export type DiscussionComment = typeof discussionComments.$inferSelect;
3cbe3d6Claude	1410	export type CodeChunk = typeof codeChunks.$inferSelect;
1e162a8Claude	1411
	1412	// ---------------------------------------------------------------------------
	1413	// Block E4 — Gists (migration 0014)
	1414	// ---------------------------------------------------------------------------
	1415	//
	1416	// User-owned small snippets/files that behave like tiny repos. DB-backed
	1417	// for v1 (no bare git repo): each gist owns a collection of gist_files and
	1418	// every edit appends a gist_revisions row with a JSON snapshot.
	1419
	1420	export const gists = pgTable(
	1421	"gists",
	1422	{
	1423	id: uuid("id").primaryKey().defaultRandom(),
	1424	ownerId: uuid("owner_id")
	1425	.notNull()
	1426	.references(() => users.id, { onDelete: "cascade" }),
	1427	// 8-char hex slug used in pretty URLs (e.g. /gists/a1b2c3d4).
	1428	slug: text("slug").notNull().unique(),
	1429	title: text("title").notNull().default(""),
	1430	description: text("description").notNull().default(""),
	1431	isPublic: boolean("is_public").default(true).notNull(),
	1432	createdAt: timestamp("created_at").defaultNow().notNull(),
	1433	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1434	},
	1435	(table) => [index("gists_owner").on(table.ownerId)]
	1436	);
	1437
	1438	export const gistFiles = pgTable(
	1439	"gist_files",
	1440	{
	1441	id: uuid("id").primaryKey().defaultRandom(),
	1442	gistId: uuid("gist_id")
	1443	.notNull()
	1444	.references(() => gists.id, { onDelete: "cascade" }),
	1445	filename: text("filename").notNull(),
	1446	// Optional explicit language override; falls back to filename detection.
	1447	language: text("language"),
	1448	content: text("content").notNull().default(""),
	1449	sizeBytes: integer("size_bytes").default(0).notNull(),
	1450	},
	1451	(table) => [
	1452	index("gist_files_gist").on(table.gistId),
	1453	uniqueIndex("gist_files_gist_filename").on(table.gistId, table.filename),
	1454	]
	1455	);
	1456
	1457	export const gistRevisions = pgTable(
	1458	"gist_revisions",
	1459	{
	1460	id: uuid("id").primaryKey().defaultRandom(),
	1461	gistId: uuid("gist_id")
	1462	.notNull()
	1463	.references(() => gists.id, { onDelete: "cascade" }),
	1464	revision: integer("revision").notNull(),
	1465	// JSON-encoded {filename: content} map capturing the full snapshot at
	1466	// this revision. Stored as text to avoid requiring jsonb.
	1467	snapshot: text("snapshot").notNull().default("{}"),
	1468	authorId: uuid("author_id")
	1469	.notNull()
	1470	.references(() => users.id, { onDelete: "cascade" }),
	1471	message: text("message"),
	1472	createdAt: timestamp("created_at").defaultNow().notNull(),
	1473	},
	1474	(table) => [
	1475	index("gist_revisions_gist_rev").on(table.gistId, table.revision),
	1476	]
	1477	);
	1478
	1479	export const gistStars = pgTable(
	1480	"gist_stars",
	1481	{
	1482	id: uuid("id").primaryKey().defaultRandom(),
	1483	gistId: uuid("gist_id")
	1484	.notNull()
	1485	.references(() => gists.id, { onDelete: "cascade" }),
	1486	userId: uuid("user_id")
	1487	.notNull()
	1488	.references(() => users.id, { onDelete: "cascade" }),
	1489	createdAt: timestamp("created_at").defaultNow().notNull(),
	1490	},
	1491	(table) => [uniqueIndex("gist_stars_gist_user").on(table.gistId, table.userId)]
	1492	);
	1493
	1494	export type Gist = typeof gists.$inferSelect;
	1495	export type GistFile = typeof gistFiles.$inferSelect;
	1496	export type GistRevision = typeof gistRevisions.$inferSelect;
	1497	export type GistStar = typeof gistStars.$inferSelect;
	1498
	1499	// ---------------------------------------------------------------------------
	1500	// Block E1 — Projects / kanban (migration 0015)
	1501	// ---------------------------------------------------------------------------
	1502
	1503	export const projects = pgTable(
	1504	"projects",
	1505	{
	1506	id: uuid("id").primaryKey().defaultRandom(),
	1507	number: serial("number"),
	1508	repositoryId: uuid("repository_id")
	1509	.notNull()
	1510	.references(() => repositories.id, { onDelete: "cascade" }),
	1511	ownerId: uuid("owner_id")
	1512	.notNull()
	1513	.references(() => users.id),
	1514	title: text("title").notNull(),
	1515	description: text("description").notNull().default(""),
	1516	state: text("state").notNull().default("open"), // open \| closed
	1517	createdAt: timestamp("created_at").defaultNow().notNull(),
	1518	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1519	},
	1520	(table) => [
	1521	index("projects_repo").on(table.repositoryId),
	1522	uniqueIndex("projects_repo_number").on(table.repositoryId, table.number),
	1523	]
	1524	);
	1525
	1526	export const projectColumns = pgTable(
	1527	"project_columns",
	1528	{
	1529	id: uuid("id").primaryKey().defaultRandom(),
	1530	projectId: uuid("project_id")
	1531	.notNull()
	1532	.references(() => projects.id, { onDelete: "cascade" }),
	1533	name: text("name").notNull(),
	1534	position: integer("position").notNull().default(0),
	1535	createdAt: timestamp("created_at").defaultNow().notNull(),
	1536	},
	1537	(table) => [index("project_columns_project").on(table.projectId)]
	1538	);
	1539
	1540	export const projectItems = pgTable(
	1541	"project_items",
	1542	{
	1543	id: uuid("id").primaryKey().defaultRandom(),
	1544	projectId: uuid("project_id")
	1545	.notNull()
	1546	.references(() => projects.id, { onDelete: "cascade" }),
	1547	columnId: uuid("column_id")
	1548	.notNull()
	1549	.references(() => projectColumns.id, { onDelete: "cascade" }),
	1550	position: integer("position").notNull().default(0),
	1551	// "note" \| "issue" \| "pr" — application-level FK on itemId by type
	1552	itemType: text("item_type").notNull().default("note"),
	1553	itemId: uuid("item_id"),
	1554	title: text("title").notNull().default(""),
	1555	note: text("note").notNull().default(""),
	1556	createdAt: timestamp("created_at").defaultNow().notNull(),
	1557	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1558	},
	1559	(table) => [
	1560	index("project_items_project").on(table.projectId),
	1561	index("project_items_column").on(table.columnId, table.position),
	1562	]
	1563	);
	1564
	1565	export type Project = typeof projects.$inferSelect;
	1566	export type ProjectColumn = typeof projectColumns.$inferSelect;
	1567	export type ProjectItem = typeof projectItems.$inferSelect;
	1568
	1569	// ---------------------------------------------------------------------------
	1570	// Block E3 — Wikis (migration 0016)
	1571	// ---------------------------------------------------------------------------
	1572	// DB-backed for v1; git-backed mirror is a future upgrade.
	1573
	1574	export const wikiPages = pgTable(
	1575	"wiki_pages",
	1576	{
	1577	id: uuid("id").primaryKey().defaultRandom(),
	1578	repositoryId: uuid("repository_id")
	1579	.notNull()
	1580	.references(() => repositories.id, { onDelete: "cascade" }),
	1581	slug: text("slug").notNull(),
	1582	title: text("title").notNull(),
	1583	body: text("body").notNull().default(""),
	1584	revision: integer("revision").notNull().default(1),
	1585	createdAt: timestamp("created_at").defaultNow().notNull(),
	1586	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1587	updatedBy: uuid("updated_by").references(() => users.id),
	1588	},
	1589	(table) => [
	1590	index("wiki_pages_repo").on(table.repositoryId),
	1591	uniqueIndex("wiki_pages_repo_slug").on(table.repositoryId, table.slug),
	1592	]
	1593	);
	1594
	1595	export const wikiRevisions = pgTable(
	1596	"wiki_revisions",
	1597	{
	1598	id: uuid("id").primaryKey().defaultRandom(),
	1599	pageId: uuid("page_id")
	1600	.notNull()
	1601	.references(() => wikiPages.id, { onDelete: "cascade" }),
	1602	revision: integer("revision").notNull(),
	1603	title: text("title").notNull(),
	1604	body: text("body").notNull().default(""),
	1605	message: text("message"),
	1606	authorId: uuid("author_id")
	1607	.notNull()
	1608	.references(() => users.id),
	1609	createdAt: timestamp("created_at").defaultNow().notNull(),
	1610	},
	1611	(table) => [index("wiki_revisions_page").on(table.pageId, table.revision)]
	1612	);
	1613
	1614	export type WikiPage = typeof wikiPages.$inferSelect;
	1615	export type WikiRevision = typeof wikiRevisions.$inferSelect;
a79a9edClaude	1616
	1617	// ---------------------------------------------------------------------------
	1618	// Block E5 — Merge queues (migration 0017)
	1619	// ---------------------------------------------------------------------------
	1620
	1621	export const mergeQueueEntries = pgTable(
	1622	"merge_queue_entries",
	1623	{
	1624	id: uuid("id").primaryKey().defaultRandom(),
	1625	repositoryId: uuid("repository_id")
	1626	.notNull()
	1627	.references(() => repositories.id, { onDelete: "cascade" }),
	1628	pullRequestId: uuid("pull_request_id")
	1629	.notNull()
	1630	.references(() => pullRequests.id, { onDelete: "cascade" }),
	1631	baseBranch: text("base_branch").notNull(),
	1632	// queued \| running \| merged \| failed \| dequeued
	1633	state: text("state").notNull().default("queued"),
	1634	position: integer("position").notNull().default(0),
	1635	enqueuedBy: uuid("enqueued_by").references(() => users.id),
	1636	enqueuedAt: timestamp("enqueued_at").defaultNow().notNull(),
	1637	startedAt: timestamp("started_at"),
	1638	finishedAt: timestamp("finished_at"),
	1639	errorMessage: text("error_message"),
	1640	},
	1641	(table) => [
	1642	index("merge_queue_repo_branch").on(
	1643	table.repositoryId,
	1644	table.baseBranch,
	1645	table.state
	1646	),
	1647	]
	1648	);
	1649
	1650	export type MergeQueueEntry = typeof mergeQueueEntries.$inferSelect;
	1651
	1652	// ---------------------------------------------------------------------------
	1653	// Block E6 — Required status checks matrix (migration 0018)
	1654	// ---------------------------------------------------------------------------
	1655
	1656	export const branchRequiredChecks = pgTable(
	1657	"branch_required_checks",
	1658	{
	1659	id: uuid("id").primaryKey().defaultRandom(),
	1660	branchProtectionId: uuid("branch_protection_id")
	1661	.notNull()
	1662	.references(() => branchProtection.id, { onDelete: "cascade" }),
	1663	checkName: text("check_name").notNull(),
	1664	createdAt: timestamp("created_at").defaultNow().notNull(),
	1665	},
	1666	(table) => [
	1667	index("branch_required_checks_rule").on(table.branchProtectionId),
	1668	uniqueIndex("branch_required_checks_unique").on(
	1669	table.branchProtectionId,
	1670	table.checkName
	1671	),
	1672	]
	1673	);
	1674
	1675	export type BranchRequiredCheck = typeof branchRequiredChecks.$inferSelect;
	1676
	1677	// ---------------------------------------------------------------------------
	1678	// Block E7 — Protected tags (migration 0019)
	1679	// ---------------------------------------------------------------------------
	1680
	1681	export const protectedTags = pgTable(
	1682	"protected_tags",
	1683	{
	1684	id: uuid("id").primaryKey().defaultRandom(),
	1685	repositoryId: uuid("repository_id")
	1686	.notNull()
	1687	.references(() => repositories.id, { onDelete: "cascade" }),
	1688	pattern: text("pattern").notNull(),
	1689	createdAt: timestamp("created_at").defaultNow().notNull(),
	1690	createdBy: uuid("created_by").references(() => users.id),
	1691	},
	1692	(table) => [
	1693	index("protected_tags_repo").on(table.repositoryId),
	1694	uniqueIndex("protected_tags_repo_pattern").on(
	1695	table.repositoryId,
	1696	table.pattern
	1697	),
	1698	]
	1699	);
	1700
	1701	export type ProtectedTag = typeof protectedTags.$inferSelect;
8f50ed0Claude	1702
	1703	// ---------------------------------------------------------------------------
	1704	// Block F — Observability + admin (migration 0020)
	1705	// ---------------------------------------------------------------------------
	1706
	1707	// F1 — Traffic analytics per repo
	1708	export const repoTrafficEvents = pgTable(
	1709	"repo_traffic_events",
	1710	{
	1711	id: uuid("id").primaryKey().defaultRandom(),
	1712	repositoryId: uuid("repository_id")
	1713	.notNull()
	1714	.references(() => repositories.id, { onDelete: "cascade" }),
	1715	kind: text("kind").notNull(), // view \| clone \| api \| ui
	1716	path: text("path"),
	1717	userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
	1718	ipHash: text("ip_hash"),
	1719	userAgent: text("user_agent"),
	1720	referer: text("referer"),
	1721	createdAt: timestamp("created_at").defaultNow().notNull(),
	1722	},
	1723	(table) => [
	1724	index("repo_traffic_events_repo_time").on(
	1725	table.repositoryId,
	1726	table.createdAt
	1727	),
	1728	index("repo_traffic_events_kind").on(
	1729	table.repositoryId,
	1730	table.kind,
	1731	table.createdAt
	1732	),
	1733	]
	1734	);
	1735
	1736	export type RepoTrafficEvent = typeof repoTrafficEvents.$inferSelect;
	1737
	1738	// F3 — Admin panel (site admins + toggleable flags)
	1739	export const systemFlags = pgTable("system_flags", {
	1740	key: text("key").primaryKey(),
	1741	value: text("value").notNull().default(""),
	1742	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1743	updatedBy: uuid("updated_by").references(() => users.id),
	1744	});
	1745
	1746	export type SystemFlag = typeof systemFlags.$inferSelect;
	1747
	1748	export const siteAdmins = pgTable("site_admins", {
	1749	userId: uuid("user_id")
	1750	.primaryKey()
	1751	.references(() => users.id, { onDelete: "cascade" }),
	1752	grantedAt: timestamp("granted_at").defaultNow().notNull(),
	1753	grantedBy: uuid("granted_by").references(() => users.id),
	1754	});
	1755
	1756	export type SiteAdmin = typeof siteAdmins.$inferSelect;
	1757
	1758	// F4 — Billing + quotas
	1759	export const billingPlans = pgTable("billing_plans", {
	1760	id: uuid("id").primaryKey().defaultRandom(),
	1761	slug: text("slug").notNull().unique(),
	1762	name: text("name").notNull(),
	1763	priceCents: integer("price_cents").notNull().default(0),
	1764	repoLimit: integer("repo_limit").notNull().default(10),
	1765	storageMbLimit: integer("storage_mb_limit").notNull().default(1024),
	1766	aiTokensMonthly: integer("ai_tokens_monthly").notNull().default(100000),
	1767	bandwidthGbMonthly: integer("bandwidth_gb_monthly").notNull().default(10),
	1768	privateRepos: boolean("private_repos").notNull().default(false),
	1769	createdAt: timestamp("created_at").defaultNow().notNull(),
	1770	});
	1771
	1772	export type BillingPlan = typeof billingPlans.$inferSelect;
	1773
	1774	export const userQuotas = pgTable("user_quotas", {
	1775	userId: uuid("user_id")
	1776	.primaryKey()
	1777	.references(() => users.id, { onDelete: "cascade" }),
	1778	planSlug: text("plan_slug").notNull().default("free"),
	1779	storageMbUsed: integer("storage_mb_used").notNull().default(0),
	1780	aiTokensUsedThisMonth: integer("ai_tokens_used_this_month")
	1781	.notNull()
	1782	.default(0),
	1783	bandwidthGbUsedThisMonth: integer("bandwidth_gb_used_this_month")
	1784	.notNull()
	1785	.default(0),
	1786	cycleStart: timestamp("cycle_start").defaultNow().notNull(),
	1787	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1788	});
	1789
	1790	export type UserQuota = typeof userQuotas.$inferSelect;
06139e6Claude	1791
	1792	// Block H — App marketplace + bot identities (GitHub Apps equivalent)
	1793
	1794	export const apps = pgTable(
	1795	"apps",
	1796	{
	1797	id: uuid("id").primaryKey().defaultRandom(),
	1798	slug: text("slug").notNull().unique(),
	1799	name: text("name").notNull(),
	1800	description: text("description").notNull().default(""),
	1801	iconUrl: text("icon_url"),
	1802	homepageUrl: text("homepage_url"),
	1803	webhookUrl: text("webhook_url"),
	1804	webhookSecret: text("webhook_secret"),
	1805	creatorId: uuid("creator_id")
	1806	.notNull()
	1807	.references(() => users.id, { onDelete: "cascade" }),
	1808	permissions: text("permissions").notNull().default("[]"), // JSON array
	1809	defaultEvents: text("default_events").notNull().default("[]"), // JSON array
	1810	isPublic: boolean("is_public").notNull().default(true),
	1811	createdAt: timestamp("created_at").defaultNow().notNull(),
	1812	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1813	},
	1814	(table) => [index("apps_public_slug").on(table.isPublic, table.slug)]
	1815	);
	1816
	1817	export type App = typeof apps.$inferSelect;
	1818
	1819	export const appInstallations = pgTable(
	1820	"app_installations",
	1821	{
	1822	id: uuid("id").primaryKey().defaultRandom(),
	1823	appId: uuid("app_id")
	1824	.notNull()
	1825	.references(() => apps.id, { onDelete: "cascade" }),
	1826	installedBy: uuid("installed_by")
	1827	.notNull()
	1828	.references(() => users.id, { onDelete: "cascade" }),
	1829	targetType: text("target_type").notNull(), // user \| org \| repository
	1830	targetId: uuid("target_id").notNull(),
	1831	grantedPermissions: text("granted_permissions").notNull().default("[]"),
	1832	suspendedAt: timestamp("suspended_at"),
	1833	createdAt: timestamp("created_at").defaultNow().notNull(),
	1834	uninstalledAt: timestamp("uninstalled_at"),
	1835	},
	1836	(table) => [
	1837	index("app_installations_app").on(table.appId),
	1838	index("app_installations_target").on(table.targetType, table.targetId),
	1839	]
	1840	);
	1841
	1842	export type AppInstallation = typeof appInstallations.$inferSelect;
	1843
	1844	export const appBots = pgTable("app_bots", {
	1845	id: uuid("id").primaryKey().defaultRandom(),
	1846	appId: uuid("app_id")
	1847	.notNull()
	1848	.unique()
	1849	.references(() => apps.id, { onDelete: "cascade" }),
	1850	username: text("username").notNull().unique(),
	1851	displayName: text("display_name").notNull(),
	1852	avatarUrl: text("avatar_url"),
	1853	createdAt: timestamp("created_at").defaultNow().notNull(),
	1854	});
	1855
	1856	export type AppBot = typeof appBots.$inferSelect;
	1857
	1858	export const appInstallTokens = pgTable(
	1859	"app_install_tokens",
	1860	{
	1861	id: uuid("id").primaryKey().defaultRandom(),
	1862	installationId: uuid("installation_id")
	1863	.notNull()
	1864	.references(() => appInstallations.id, { onDelete: "cascade" }),
	1865	tokenHash: text("token_hash").notNull().unique(),
	1866	expiresAt: timestamp("expires_at").notNull(),
	1867	createdAt: timestamp("created_at").defaultNow().notNull(),
	1868	revokedAt: timestamp("revoked_at"),
	1869	},
	1870	(table) => [index("app_install_tokens_hash").on(table.tokenHash)]
	1871	);
	1872
	1873	export type AppInstallToken = typeof appInstallTokens.$inferSelect;
	1874
	1875	export const appEvents = pgTable(
	1876	"app_events",
	1877	{
	1878	id: uuid("id").primaryKey().defaultRandom(),
	1879	appId: uuid("app_id")
	1880	.notNull()
	1881	.references(() => apps.id, { onDelete: "cascade" }),
	1882	installationId: uuid("installation_id"),
	1883	kind: text("kind").notNull(), // installed \| uninstalled \| delivery_ok \| delivery_fail
	1884	payload: text("payload"),
	1885	responseStatus: integer("response_status"),
	1886	createdAt: timestamp("created_at").defaultNow().notNull(),
	1887	},
	1888	(table) => [index("app_events_app_time").on(table.appId, table.createdAt)]
	1889	);
	1890
	1891	export type AppEvent = typeof appEvents.$inferSelect;
71cd5ecClaude	1892
	1893	// ---------- Block I3 — Repository transfer history ----------
	1894
	1895	export const repoTransfers = pgTable(
	1896	"repo_transfers",
	1897	{
	1898	id: uuid("id").primaryKey().defaultRandom(),
	1899	repositoryId: uuid("repository_id")
	1900	.notNull()
	1901	.references(() => repositories.id, { onDelete: "cascade" }),
	1902	fromOwnerId: uuid("from_owner_id").notNull(),
	1903	fromOrgId: uuid("from_org_id"),
	1904	toOwnerId: uuid("to_owner_id").notNull(),
	1905	toOrgId: uuid("to_org_id"),
	1906	initiatedBy: uuid("initiated_by")
	1907	.notNull()
	1908	.references(() => users.id, { onDelete: "cascade" }),
	1909	createdAt: timestamp("created_at").defaultNow().notNull(),
	1910	},
	1911	(table) => [
	1912	index("repo_transfers_repo").on(table.repositoryId, table.createdAt),
	1913	]
	1914	);
	1915
	1916	export type RepoTransfer = typeof repoTransfers.$inferSelect;