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