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.tsBlame2643 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,
abfa9adClaude	11	bigint,
	12	jsonb,
	13	customType,
fc1817aClaude	14	} from "drizzle-orm/pg-core";
	15
abfa9adClaude	16	// Postgres `bytea` — drizzle-orm doesn't ship a first-class bytea column, so
	17	// we declare one via customType that maps to Buffer on both read and write.
	18	// Used by `workflow_run_cache.content` where we really do need raw bytes
	19	// (unlike `workflow_artifacts.content`, which stayed base64-text for v1).
	20	const bytea = customType<{ data: Buffer; default: false }>({
	21	dataType() {
	22	return "bytea";
	23	},
	24	});
	25
fc1817aClaude	26	export const users = pgTable("users", {
	27	id: uuid("id").primaryKey().defaultRandom(),
	28	username: text("username").notNull().unique(),
	29	email: text("email").notNull().unique(),
	30	displayName: text("display_name"),
	31	passwordHash: text("password_hash").notNull(),
	32	avatarUrl: text("avatar_url"),
	33	bio: text("bio"),
24cf2caClaude	34	// Email notification preferences (Block A8). Default on; opt-out via /settings.
	35	notifyEmailOnMention: boolean("notify_email_on_mention").default(true).notNull(),
	36	notifyEmailOnAssign: boolean("notify_email_on_assign").default(true).notNull(),
	37	notifyEmailOnGateFail: boolean("notify_email_on_gate_fail").default(true).notNull(),
08420cdClaude	38	// Block I7 — weekly digest opt-in.
	39	notifyEmailDigestWeekly: boolean("notify_email_digest_weekly").default(false).notNull(),
	40	lastDigestSentAt: timestamp("last_digest_sent_at"),
a4f3e24Claude	41	isAdmin: boolean("is_admin").default(false).notNull(),
fc1817aClaude	42	createdAt: timestamp("created_at").defaultNow().notNull(),
	43	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	44	});
	45
06d5ffeClaude	46	export const sessions = pgTable("sessions", {
	47	id: uuid("id").primaryKey().defaultRandom(),
	48	userId: uuid("user_id")
	49	.notNull()
	50	.references(() => users.id, { onDelete: "cascade" }),
	51	token: text("token").notNull().unique(),
	52	expiresAt: timestamp("expires_at").notNull(),
7298a17Claude	53	// B4: true when the user has entered their password but not yet their TOTP
	54	// code. softAuth/requireAuth treat such sessions as anonymous; only
	55	// /login/2fa can consume them. Flips to false on successful 2FA.
	56	requires2fa: boolean("requires_2fa").default(false).notNull(),
06d5ffeClaude	57	createdAt: timestamp("created_at").defaultNow().notNull(),
	58	});
	59
45e31d0Claude	60	// @ts-ignore — self-referential FK on forkedFromId causes circular inference
fc1817aClaude	61	export const repositories = pgTable(
	62	"repositories",
	63	{
	64	id: uuid("id").primaryKey().defaultRandom(),
	65	name: text("name").notNull(),
7437605Claude	66	// ownerId = creator / user-owner. Always set (for attribution + user
	67	// namespace uniqueness). For org-owned repos, also represents "created by".
fc1817aClaude	68	ownerId: uuid("owner_id")
	69	.notNull()
	70	.references(() => users.id),
7437605Claude	71	// Block B2: nullable org owner. When set, the repo lives in the org
	72	// namespace and URL resolution routes `/:orgSlug/:repo` to it.
	73	orgId: uuid("org_id"),
fc1817aClaude	74	description: text("description"),
	75	isPrivate: boolean("is_private").default(false).notNull(),
3ef4c9dClaude	76	isArchived: boolean("is_archived").default(false).notNull(),
71cd5ecClaude	77	isTemplate: boolean("is_template").default(false).notNull(),
fc1817aClaude	78	defaultBranch: text("default_branch").default("main").notNull(),
	79	diskPath: text("disk_path").notNull(),
45e31d0Claude	80	forkedFromId: uuid("forked_from_id"),
fc1817aClaude	81	createdAt: timestamp("created_at").defaultNow().notNull(),
	82	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	83	pushedAt: timestamp("pushed_at"),
	84	starCount: integer("star_count").default(0).notNull(),
	85	forkCount: integer("fork_count").default(0).notNull(),
79136bbClaude	86	issueCount: integer("issue_count").default(0).notNull(),
fc1817aClaude	87	},
7437605Claude	88	(table) => [
	89	// Partial: uniqueness only in the user namespace (org-owned rows exempt).
	90	// Matches the partial index in migration 0004.
	91	uniqueIndex("repos_owner_name").on(table.ownerId, table.name),
	92	index("repos_org").on(table.orgId),
	93	]
fc1817aClaude	94	);
	95
3ef4c9dClaude	96	/**
	97	* Per-repository gate + auto-repair configuration.
	98	* Every new repo is created with all gates ENABLED by default —
	99	* the "full green ecosystem" default. Owners can manually opt-out per setting.
	100	*/
	101	export const repoSettings = pgTable("repo_settings", {
	102	id: uuid("id").primaryKey().defaultRandom(),
	103	repositoryId: uuid("repository_id")
	104	.notNull()
	105	.unique()
	106	.references(() => repositories.id, { onDelete: "cascade" }),
	107	// Gates
	108	gateTestEnabled: boolean("gate_test_enabled").default(true).notNull(),
	109	aiReviewEnabled: boolean("ai_review_enabled").default(true).notNull(),
	110	secretScanEnabled: boolean("secret_scan_enabled").default(true).notNull(),
	111	securityScanEnabled: boolean("security_scan_enabled").default(true).notNull(),
	112	dependencyScanEnabled: boolean("dependency_scan_enabled").default(true).notNull(),
	113	lintEnabled: boolean("lint_enabled").default(true).notNull(),
	114	typeCheckEnabled: boolean("type_check_enabled").default(true).notNull(),
	115	testEnabled: boolean("test_enabled").default(true).notNull(),
	116	// Auto-repair
	117	autoFixEnabled: boolean("auto_fix_enabled").default(true).notNull(),
	118	autoMergeResolveEnabled: boolean("auto_merge_resolve_enabled").default(true).notNull(),
	119	autoFormatEnabled: boolean("auto_format_enabled").default(true).notNull(),
	120	// AI features
	121	aiCommitMessagesEnabled: boolean("ai_commit_messages_enabled").default(true).notNull(),
	122	aiPrSummaryEnabled: boolean("ai_pr_summary_enabled").default(true).notNull(),
	123	aiChangelogEnabled: boolean("ai_changelog_enabled").default(true).notNull(),
	124	// Deploy
	125	autoDeployEnabled: boolean("auto_deploy_enabled").default(true).notNull(),
	126	deployRequireAllGreen: boolean("deploy_require_all_green").default(true).notNull(),
	127	createdAt: timestamp("created_at").defaultNow().notNull(),
	128	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	129	});
	130
	131	/**
	132	* Branch protection rules — enforced on push and merge.
	133	* Every repo's default branch gets a protection rule on creation.
	134	*/
	135	export const branchProtection = pgTable(
	136	"branch_protection",
	137	{
	138	id: uuid("id").primaryKey().defaultRandom(),
	139	repositoryId: uuid("repository_id")
	140	.notNull()
	141	.references(() => repositories.id, { onDelete: "cascade" }),
	142	pattern: text("pattern").notNull(), // branch name or glob (e.g. "main", "release/*")
	143	requirePullRequest: boolean("require_pull_request").default(true).notNull(),
	144	requireGreenGates: boolean("require_green_gates").default(true).notNull(),
	145	requireAiApproval: boolean("require_ai_approval").default(true).notNull(),
	146	requireHumanReview: boolean("require_human_review").default(false).notNull(),
	147	requiredApprovals: integer("required_approvals").default(0).notNull(),
	148	allowForcePush: boolean("allow_force_push").default(false).notNull(),
	149	allowDeletion: boolean("allow_deletion").default(false).notNull(),
	150	dismissStaleReviews: boolean("dismiss_stale_reviews").default(true).notNull(),
4626e61Claude	151	// K2 — Auto-merge opt-in. When true, the autopilot ticker may auto-merge
	152	// a PR whose base branch matches this rule, provided every gate the
	153	// manual-merge path enforces is green. Default-deny.
	154	enableAutoMerge: boolean("enable_auto_merge").default(false).notNull(),
3ef4c9dClaude	155	createdAt: timestamp("created_at").defaultNow().notNull(),
	156	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	157	},
	158	(table) => [
	159	uniqueIndex("branch_protection_repo_pattern").on(
	160	table.repositoryId,
	161	table.pattern
	162	),
	163	]
	164	);
	165
	166	/**
	167	* Gate run history. Every push + every PR creates gate_runs entries —
	168	* one per configured gate. Serves as the source of truth for "is this green?".
	169	*/
	170	export const gateRuns = pgTable(
	171	"gate_runs",
	172	{
	173	id: uuid("id").primaryKey().defaultRandom(),
	174	repositoryId: uuid("repository_id")
	175	.notNull()
	176	.references(() => repositories.id, { onDelete: "cascade" }),
	177	pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
	178	onDelete: "cascade",
	179	}),
	180	commitSha: text("commit_sha").notNull(),
	181	ref: text("ref").notNull(),
	182	gateName: text("gate_name").notNull(), // e.g. "GateTest", "AI Review", "Secret Scan", "Type Check"
	183	status: text("status").notNull(), // pending, running, passed, failed, skipped, repaired
	184	summary: text("summary"),
	185	details: text("details"), // JSON: per-check output, affected files, etc
	186	repairAttempted: boolean("repair_attempted").default(false).notNull(),
	187	repairSucceeded: boolean("repair_succeeded").default(false).notNull(),
	188	repairCommitSha: text("repair_commit_sha"),
	189	durationMs: integer("duration_ms"),
	190	createdAt: timestamp("created_at").defaultNow().notNull(),
	191	completedAt: timestamp("completed_at"),
	192	},
	193	(table) => [
	194	index("gate_runs_repo_sha").on(table.repositoryId, table.commitSha),
	195	index("gate_runs_pr").on(table.pullRequestId),
	196	index("gate_runs_created").on(table.createdAt),
	197	]
	198	);
	199
	200	/**
	201	* In-app notifications. Powered by the activity feed + explicit mentions.
	202	*/
	203	export const notifications = pgTable(
	204	"notifications",
	205	{
	206	id: uuid("id").primaryKey().defaultRandom(),
	207	userId: uuid("user_id")
	208	.notNull()
	209	.references(() => users.id, { onDelete: "cascade" }),
	210	repositoryId: uuid("repository_id").references(() => repositories.id, {
	211	onDelete: "cascade",
	212	}),
	213	kind: text("kind").notNull(), // mention, review_requested, pr_merged, pr_closed, gate_failed, gate_repaired, ai_review, assigned, security_alert, deploy_success, deploy_failed
	214	title: text("title").notNull(),
	215	body: text("body"),
	216	url: text("url"), // link to the relevant page
	217	readAt: timestamp("read_at"),
	218	createdAt: timestamp("created_at").defaultNow().notNull(),
	219	},
	220	(table) => [
	221	index("notifications_user_unread").on(table.userId, table.readAt),
	222	index("notifications_user_created").on(table.userId, table.createdAt),
	223	]
	224	);
	225
	226	/**
	227	* Releases — named snapshots of a repo at a tag/commit.
	228	* AI-generated changelogs bundled in notes field.
	229	*/
	230	export const releases = pgTable(
	231	"releases",
	232	{
	233	id: uuid("id").primaryKey().defaultRandom(),
	234	repositoryId: uuid("repository_id")
	235	.notNull()
	236	.references(() => repositories.id, { onDelete: "cascade" }),
	237	authorId: uuid("author_id")
	238	.notNull()
	239	.references(() => users.id),
	240	tag: text("tag").notNull(),
	241	name: text("name").notNull(),
	242	body: text("body"), // AI-generated release notes + changelog
	243	targetCommit: text("target_commit").notNull(),
	244	isDraft: boolean("is_draft").default(false).notNull(),
	245	isPrerelease: boolean("is_prerelease").default(false).notNull(),
	246	createdAt: timestamp("created_at").defaultNow().notNull(),
	247	publishedAt: timestamp("published_at"),
	248	},
	249	(table) => [
	250	uniqueIndex("releases_repo_tag").on(table.repositoryId, table.tag),
	251	]
	252	);
	253
	254	/**
	255	* Milestones — group issues + PRs toward a shared goal.
	256	*/
	257	export const milestones = pgTable(
	258	"milestones",
	259	{
	260	id: uuid("id").primaryKey().defaultRandom(),
	261	repositoryId: uuid("repository_id")
	262	.notNull()
	263	.references(() => repositories.id, { onDelete: "cascade" }),
	264	title: text("title").notNull(),
	265	description: text("description"),
	266	state: text("state").notNull().default("open"), // open, closed
	267	dueDate: timestamp("due_date"),
	268	createdAt: timestamp("created_at").defaultNow().notNull(),
	269	closedAt: timestamp("closed_at"),
	270	},
	271	(table) => [index("milestones_repo_state").on(table.repositoryId, table.state)]
	272	);
	273
	274	/**
	275	* Reactions on issues, PRs, and comments. Universal target pointer.
	276	*/
	277	export const reactions = pgTable(
	278	"reactions",
	279	{
	280	id: uuid("id").primaryKey().defaultRandom(),
	281	userId: uuid("user_id")
	282	.notNull()
	283	.references(() => users.id, { onDelete: "cascade" }),
	284	targetType: text("target_type").notNull(), // issue, pr, issue_comment, pr_comment
	285	targetId: uuid("target_id").notNull(),
	286	emoji: text("emoji").notNull(), // thumbs_up, thumbs_down, rocket, heart, eyes, laugh, hooray, confused
	287	createdAt: timestamp("created_at").defaultNow().notNull(),
	288	},
	289	(table) => [
	290	uniqueIndex("reactions_unique").on(
	291	table.userId,
	292	table.targetType,
	293	table.targetId,
	294	table.emoji
	295	),
	296	index("reactions_target").on(table.targetType, table.targetId),
	297	]
	298	);
	299
	300	/**
	301	* PR reviews (formal approve/request-changes).
	302	* Separate from inline comments in pr_comments.
	303	*/
	304	export const prReviews = pgTable(
	305	"pr_reviews",
	306	{
	307	id: uuid("id").primaryKey().defaultRandom(),
	308	pullRequestId: uuid("pull_request_id")
	309	.notNull()
	310	.references(() => pullRequests.id, { onDelete: "cascade" }),
	311	reviewerId: uuid("reviewer_id")
	312	.notNull()
	313	.references(() => users.id),
	314	state: text("state").notNull(), // approved, changes_requested, commented
	315	body: text("body"),
	316	isAi: boolean("is_ai").default(false).notNull(),
	317	createdAt: timestamp("created_at").defaultNow().notNull(),
	318	},
	319	(table) => [index("pr_reviews_pr").on(table.pullRequestId)]
	320	);
	321
	322	/**
	323	* Code owners — who owns which paths (auto-request review on PR).
	324	* Parsed from a CODEOWNERS file at the root of the default branch.
	325	*/
	326	export const codeOwners = pgTable(
	327	"code_owners",
	328	{
	329	id: uuid("id").primaryKey().defaultRandom(),
	330	repositoryId: uuid("repository_id")
	331	.notNull()
	332	.references(() => repositories.id, { onDelete: "cascade" }),
	333	pathPattern: text("path_pattern").notNull(),
	334	ownerUsernames: text("owner_usernames").notNull(), // comma-separated
	335	createdAt: timestamp("created_at").defaultNow().notNull(),
	336	},
	337	(table) => [index("code_owners_repo").on(table.repositoryId)]
	338	);
	339
	340	/**
	341	* Per-repo AI chat sessions — conversational repo assistant.
	342	*/
	343	export const aiChats = pgTable(
	344	"ai_chats",
	345	{
	346	id: uuid("id").primaryKey().defaultRandom(),
	347	userId: uuid("user_id")
	348	.notNull()
	349	.references(() => users.id, { onDelete: "cascade" }),
	350	repositoryId: uuid("repository_id").references(() => repositories.id, {
	351	onDelete: "cascade",
	352	}),
	353	title: text("title"),
	354	messages: text("messages").notNull().default("[]"), // JSON array of {role, content}
	355	createdAt: timestamp("created_at").defaultNow().notNull(),
	356	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	357	},
	358	(table) => [
	359	index("ai_chats_user").on(table.userId),
	360	index("ai_chats_repo").on(table.repositoryId),
	361	]
	362	);
	363
	364	/**
	365	* Audit log — every sensitive action. Who did what, when, from where.
	366	*/
	367	export const auditLog = pgTable(
	368	"audit_log",
	369	{
	370	id: uuid("id").primaryKey().defaultRandom(),
	371	userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
	372	repositoryId: uuid("repository_id").references(() => repositories.id, {
	373	onDelete: "set null",
	374	}),
	375	action: text("action").notNull(), // repo.create, repo.delete, repo.transfer, token.create, token.revoke, merge, force_push, branch_protection.update, deploy, ...
	376	targetType: text("target_type"),
	377	targetId: text("target_id"),
	378	ip: text("ip"),
	379	userAgent: text("user_agent"),
	380	metadata: text("metadata"), // JSON for extra context
	381	createdAt: timestamp("created_at").defaultNow().notNull(),
	382	},
	383	(table) => [
	384	index("audit_log_user").on(table.userId),
	385	index("audit_log_repo").on(table.repositoryId),
	386	index("audit_log_created").on(table.createdAt),
	387	]
	388	);
	389
	390	/**
	391	* Deployments — tracks every deploy to downstream systems (Crontech, etc).
	392	* Each deploy is gated on ALL green gates passing.
	393	*/
	394	export const deployments = pgTable(
	395	"deployments",
	396	{
	397	id: uuid("id").primaryKey().defaultRandom(),
	398	repositoryId: uuid("repository_id")
	399	.notNull()
	400	.references(() => repositories.id, { onDelete: "cascade" }),
	401	environment: text("environment").notNull().default("production"),
	402	commitSha: text("commit_sha").notNull(),
	403	ref: text("ref").notNull(),
a76d984Claude	404	status: text("status").notNull(), // pending, running, success, failed, blocked, waiting_timer
3ef4c9dClaude	405	blockedReason: text("blocked_reason"),
	406	target: text("target"), // e.g. "crontech", "fly.io"
	407	triggeredBy: uuid("triggered_by").references(() => users.id),
a76d984Claude	408	/**
	409	* Set when an approved deploy is held by `environments.wait_timer_minutes`.
	410	* NULL = no wait; non-null = autopilot flips status from "waiting_timer"
	411	* to "pending" once `now() >= ready_after`.
	412	*/
	413	readyAfter: timestamp("ready_after"),
3ef4c9dClaude	414	createdAt: timestamp("created_at").defaultNow().notNull(),
	415	completedAt: timestamp("completed_at"),
	416	},
	417	(table) => [
	418	index("deployments_repo").on(table.repositoryId),
	419	index("deployments_created").on(table.createdAt),
	420	]
	421	);
	422
	423	/**
	424	* Rate-limit buckets — in-memory or persisted counter per IP / token / route.
	425	*/
	426	export const rateLimitBuckets = pgTable(
	427	"rate_limit_buckets",
	428	{
	429	id: uuid("id").primaryKey().defaultRandom(),
	430	bucketKey: text("bucket_key").notNull().unique(), // "ip:1.2.3.4:api" or "token:abc:api"
	431	count: integer("count").default(0).notNull(),
	432	windowStart: timestamp("window_start").defaultNow().notNull(),
	433	expiresAt: timestamp("expires_at").notNull(),
	434	},
	435	(table) => [index("rate_limit_expires").on(table.expiresAt)]
fc1817aClaude	436	);
	437
06d5ffeClaude	438	export const stars = pgTable(
	439	"stars",
	440	{
	441	id: uuid("id").primaryKey().defaultRandom(),
	442	userId: uuid("user_id")
	443	.notNull()
	444	.references(() => users.id, { onDelete: "cascade" }),
	445	repositoryId: uuid("repository_id")
	446	.notNull()
	447	.references(() => repositories.id, { onDelete: "cascade" }),
	448	createdAt: timestamp("created_at").defaultNow().notNull(),
	449	},
79136bbClaude	450	(table) => [
	451	uniqueIndex("stars_user_repo").on(table.userId, table.repositoryId),
	452	]
	453	);
	454
	455	export const issues = pgTable(
	456	"issues",
	457	{
	458	id: uuid("id").primaryKey().defaultRandom(),
	459	number: serial("number"),
	460	repositoryId: uuid("repository_id")
	461	.notNull()
	462	.references(() => repositories.id, { onDelete: "cascade" }),
	463	authorId: uuid("author_id")
	464	.notNull()
	465	.references(() => users.id),
	466	title: text("title").notNull(),
	467	body: text("body"),
	468	state: text("state").notNull().default("open"), // open, closed
	469	createdAt: timestamp("created_at").defaultNow().notNull(),
	470	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	471	closedAt: timestamp("closed_at"),
	472	},
	473	(table) => [
	474	index("issues_repo_state").on(table.repositoryId, table.state),
	475	index("issues_repo_number").on(table.repositoryId, table.number),
	476	]
	477	);
	478
	479	export const issueComments = pgTable(
	480	"issue_comments",
	481	{
	482	id: uuid("id").primaryKey().defaultRandom(),
	483	issueId: uuid("issue_id")
	484	.notNull()
	485	.references(() => issues.id, { onDelete: "cascade" }),
	486	authorId: uuid("author_id")
	487	.notNull()
	488	.references(() => users.id),
	489	body: text("body").notNull(),
	490	createdAt: timestamp("created_at").defaultNow().notNull(),
	491	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	492	},
	493	(table) => [index("comments_issue").on(table.issueId)]
	494	);
	495
	496	export const labels = pgTable(
	497	"labels",
	498	{
	499	id: uuid("id").primaryKey().defaultRandom(),
	500	repositoryId: uuid("repository_id")
	501	.notNull()
	502	.references(() => repositories.id, { onDelete: "cascade" }),
	503	name: text("name").notNull(),
	504	color: text("color").notNull().default("#8b949e"),
	505	description: text("description"),
	506	},
	507	(table) => [
	508	uniqueIndex("labels_repo_name").on(table.repositoryId, table.name),
	509	]
	510	);
	511
	512	export const issueLabels = pgTable(
	513	"issue_labels",
	514	{
	515	id: uuid("id").primaryKey().defaultRandom(),
	516	issueId: uuid("issue_id")
	517	.notNull()
	518	.references(() => issues.id, { onDelete: "cascade" }),
	519	labelId: uuid("label_id")
	520	.notNull()
	521	.references(() => labels.id, { onDelete: "cascade" }),
	522	},
	523	(table) => [
	524	uniqueIndex("issue_labels_unique").on(table.issueId, table.labelId),
	525	]
06d5ffeClaude	526	);
	527
0074234Claude	528	export const pullRequests = pgTable(
	529	"pull_requests",
	530	{
	531	id: uuid("id").primaryKey().defaultRandom(),
	532	number: serial("number"),
	533	repositoryId: uuid("repository_id")
	534	.notNull()
	535	.references(() => repositories.id, { onDelete: "cascade" }),
	536	authorId: uuid("author_id")
	537	.notNull()
	538	.references(() => users.id),
	539	title: text("title").notNull(),
	540	body: text("body"),
	541	state: text("state").notNull().default("open"), // open, closed, merged
	542	baseBranch: text("base_branch").notNull(),
	543	headBranch: text("head_branch").notNull(),
3ef4c9dClaude	544	isDraft: boolean("is_draft").default(false).notNull(),
	545	mergeStrategy: text("merge_strategy").default("merge").notNull(), // merge, squash, rebase
	546	milestoneId: uuid("milestone_id"),
0074234Claude	547	mergedAt: timestamp("merged_at"),
	548	mergedBy: uuid("merged_by").references(() => users.id),
	549	createdAt: timestamp("created_at").defaultNow().notNull(),
	550	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	551	closedAt: timestamp("closed_at"),
	552	},
	553	(table) => [
	554	index("prs_repo_state").on(table.repositoryId, table.state),
	555	index("prs_repo_number").on(table.repositoryId, table.number),
	556	]
	557	);
	558
	559	export const prComments = pgTable(
	560	"pr_comments",
	561	{
	562	id: uuid("id").primaryKey().defaultRandom(),
	563	pullRequestId: uuid("pull_request_id")
	564	.notNull()
	565	.references(() => pullRequests.id, { onDelete: "cascade" }),
	566	authorId: uuid("author_id")
	567	.notNull()
	568	.references(() => users.id),
	569	body: text("body").notNull(),
	570	isAiReview: boolean("is_ai_review").default(false).notNull(),
	571	filePath: text("file_path"),
	572	lineNumber: integer("line_number"),
	573	createdAt: timestamp("created_at").defaultNow().notNull(),
	574	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	575	},
	576	(table) => [index("pr_comments_pr").on(table.pullRequestId)]
	577	);
	578
	579	export const activityFeed = pgTable(
	580	"activity_feed",
	581	{
	582	id: uuid("id").primaryKey().defaultRandom(),
	583	repositoryId: uuid("repository_id")
	584	.notNull()
	585	.references(() => repositories.id, { onDelete: "cascade" }),
	586	userId: uuid("user_id").references(() => users.id),
	587	action: text("action").notNull(), // push, issue_open, issue_close, pr_open, pr_merge, star, comment
	588	targetType: text("target_type"), // issue, pr, commit
	589	targetId: text("target_id"),
	590	metadata: text("metadata"), // JSON string for extra data
	591	createdAt: timestamp("created_at").defaultNow().notNull(),
	592	},
	593	(table) => [
	594	index("activity_repo").on(table.repositoryId),
	595	index("activity_user").on(table.userId),
	596	]
	597	);
	598
c81ab7aClaude	599	export const webhooks = pgTable(
	600	"webhooks",
	601	{
	602	id: uuid("id").primaryKey().defaultRandom(),
	603	repositoryId: uuid("repository_id")
	604	.notNull()
	605	.references(() => repositories.id, { onDelete: "cascade" }),
	606	url: text("url").notNull(),
	607	secret: text("secret"),
	608	events: text("events").notNull().default("push"), // comma-separated: push,issue,pr
	609	isActive: boolean("is_active").default(true).notNull(),
	610	lastDeliveredAt: timestamp("last_delivered_at"),
	611	lastStatus: integer("last_status"),
	612	createdAt: timestamp("created_at").defaultNow().notNull(),
	613	},
	614	(table) => [index("webhooks_repo").on(table.repositoryId)]
	615	);
	616
	617	export const apiTokens = pgTable("api_tokens", {
	618	id: uuid("id").primaryKey().defaultRandom(),
	619	userId: uuid("user_id")
	620	.notNull()
	621	.references(() => users.id, { onDelete: "cascade" }),
	622	name: text("name").notNull(),
	623	tokenHash: text("token_hash").notNull(),
	624	tokenPrefix: text("token_prefix").notNull(), // first 8 chars for display
	625	scopes: text("scopes").notNull().default("repo"), // comma-separated
	626	lastUsedAt: timestamp("last_used_at"),
	627	expiresAt: timestamp("expires_at"),
	628	createdAt: timestamp("created_at").defaultNow().notNull(),
	629	});
	630
	631	export const repoTopics = pgTable(
	632	"repo_topics",
	633	{
	634	id: uuid("id").primaryKey().defaultRandom(),
	635	repositoryId: uuid("repository_id")
	636	.notNull()
	637	.references(() => repositories.id, { onDelete: "cascade" }),
	638	topic: text("topic").notNull(),
	639	},
	640	(table) => [
	641	uniqueIndex("repo_topics_unique").on(table.repositoryId, table.topic),
	642	index("topics_name").on(table.topic),
	643	]
	644	);
	645
fc1817aClaude	646	export const sshKeys = pgTable("ssh_keys", {
	647	id: uuid("id").primaryKey().defaultRandom(),
	648	userId: uuid("user_id")
	649	.notNull()
06d5ffeClaude	650	.references(() => users.id, { onDelete: "cascade" }),
fc1817aClaude	651	title: text("title").notNull(),
	652	fingerprint: text("fingerprint").notNull(),
	653	publicKey: text("public_key").notNull(),
	654	lastUsedAt: timestamp("last_used_at"),
	655	createdAt: timestamp("created_at").defaultNow().notNull(),
	656	});
	657
	658	export type User = typeof users.$inferSelect;
	659	export type NewUser = typeof users.$inferInsert;
	660	export type Repository = typeof repositories.$inferSelect;
	661	export type NewRepository = typeof repositories.$inferInsert;
06d5ffeClaude	662	export type Session = typeof sessions.$inferSelect;
	663	export type Star = typeof stars.$inferSelect;
	664	export type SshKey = typeof sshKeys.$inferSelect;
79136bbClaude	665	export type Issue = typeof issues.$inferSelect;
	666	export type IssueComment = typeof issueComments.$inferSelect;
	667	export type Label = typeof labels.$inferSelect;
0074234Claude	668	export type PullRequest = typeof pullRequests.$inferSelect;
	669	export type PrComment = typeof prComments.$inferSelect;
	670	export type ActivityEntry = typeof activityFeed.$inferSelect;
c81ab7aClaude	671	export type Webhook = typeof webhooks.$inferSelect;
	672	export type ApiToken = typeof apiTokens.$inferSelect;
	673	export type RepoTopic = typeof repoTopics.$inferSelect;
3ef4c9dClaude	674	export type RepoSettings = typeof repoSettings.$inferSelect;
	675	export type BranchProtection = typeof branchProtection.$inferSelect;
	676	export type GateRun = typeof gateRuns.$inferSelect;
	677	export type Notification = typeof notifications.$inferSelect;
	678	export type Release = typeof releases.$inferSelect;
	679	export type Milestone = typeof milestones.$inferSelect;
	680	export type Reaction = typeof reactions.$inferSelect;
	681	export type PrReview = typeof prReviews.$inferSelect;
	682	export type CodeOwner = typeof codeOwners.$inferSelect;
	683	export type AiChat = typeof aiChats.$inferSelect;
	684	export type AuditLogEntry = typeof auditLog.$inferSelect;
	685	export type Deployment = typeof deployments.$inferSelect;
24cf2caClaude	686
	687	/**
	688	* Saved replies — per-user canned responses, insertable into any
	689	* issue / PR comment textarea. Shortcut name must be unique per user.
	690	*/
	691	export const savedReplies = pgTable(
	692	"saved_replies",
	693	{
	694	id: uuid("id").primaryKey().defaultRandom(),
	695	userId: uuid("user_id")
	696	.notNull()
	697	.references(() => users.id, { onDelete: "cascade" }),
	698	shortcut: text("shortcut").notNull(),
	699	body: text("body").notNull(),
	700	createdAt: timestamp("created_at").defaultNow().notNull(),
	701	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	702	},
	703	(table) => [
	704	uniqueIndex("saved_replies_user_shortcut").on(table.userId, table.shortcut),
	705	]
	706	);
	707
	708	export type SavedReply = typeof savedReplies.$inferSelect;
5cc5d95Claude	709
	710	/**
	711	* Organizations (Block B1) — multi-user namespaces. Distinct from `users`.
	712	* An org has members (with org-level roles) and may contain teams.
	713	* Repos can be owned by an org via `repositories.orgId` (added in Block B2).
	714	*
	715	* Slug is globally unique against itself; collision with a username is
	716	* checked at create time in the route handler (no DB-level cross-table
	717	* uniqueness in Postgres).
	718	*/
	719	export const organizations = pgTable("organizations", {
	720	id: uuid("id").primaryKey().defaultRandom(),
	721	slug: text("slug").notNull().unique(),
	722	name: text("name").notNull(),
	723	description: text("description"),
	724	avatarUrl: text("avatar_url"),
	725	billingEmail: text("billing_email"),
	726	createdById: uuid("created_by_id")
	727	.notNull()
	728	.references(() => users.id, { onDelete: "restrict" }),
	729	createdAt: timestamp("created_at").defaultNow().notNull(),
	730	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	731	});
	732
	733	/**
	734	* Org membership. Roles: owner (full control, billing), admin (manage
	735	* members + teams + repos), member (default; can be added to teams).
	736	*/
	737	export const orgMembers = pgTable(
	738	"org_members",
	739	{
	740	id: uuid("id").primaryKey().defaultRandom(),
	741	orgId: uuid("org_id")
	742	.notNull()
	743	.references(() => organizations.id, { onDelete: "cascade" }),
	744	userId: uuid("user_id")
	745	.notNull()
	746	.references(() => users.id, { onDelete: "cascade" }),
	747	role: text("role").notNull().default("member"), // owner \| admin \| member
	748	createdAt: timestamp("created_at").defaultNow().notNull(),
	749	},
	750	(table) => [
	751	uniqueIndex("org_members_unique").on(table.orgId, table.userId),
	752	index("org_members_user").on(table.userId),
	753	]
	754	);
	755
	756	/**
	757	* Teams within an org. Slug is unique within an org.
	758	* `parentTeamId` allows nesting (GitHub-style child teams). Optional.
	759	*/
	760	export const teams = pgTable(
	761	"teams",
	762	{
	763	id: uuid("id").primaryKey().defaultRandom(),
	764	orgId: uuid("org_id")
	765	.notNull()
	766	.references(() => organizations.id, { onDelete: "cascade" }),
	767	slug: text("slug").notNull(),
	768	name: text("name").notNull(),
	769	description: text("description"),
	770	parentTeamId: uuid("parent_team_id"),
	771	createdAt: timestamp("created_at").defaultNow().notNull(),
	772	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	773	},
	774	(table) => [uniqueIndex("teams_org_slug").on(table.orgId, table.slug)]
	775	);
	776
	777	/**
	778	* Team membership. Roles: maintainer (can edit team), member (default).
	779	* A user can belong to many teams; team membership requires org membership
	780	* but that invariant is enforced at the route layer, not the DB layer.
	781	*/
	782	export const teamMembers = pgTable(
	783	"team_members",
	784	{
	785	id: uuid("id").primaryKey().defaultRandom(),
	786	teamId: uuid("team_id")
	787	.notNull()
	788	.references(() => teams.id, { onDelete: "cascade" }),
	789	userId: uuid("user_id")
	790	.notNull()
	791	.references(() => users.id, { onDelete: "cascade" }),
	792	role: text("role").notNull().default("member"), // maintainer \| member
	793	createdAt: timestamp("created_at").defaultNow().notNull(),
	794	},
	795	(table) => [
	796	uniqueIndex("team_members_unique").on(table.teamId, table.userId),
	797	index("team_members_user").on(table.userId),
	798	]
	799	);
	800
	801	export type Organization = typeof organizations.$inferSelect;
	802	export type OrgMember = typeof orgMembers.$inferSelect;
	803	export type Team = typeof teams.$inferSelect;
	804	export type TeamMember = typeof teamMembers.$inferSelect;
	805	export type OrgRole = "owner" \| "admin" \| "member";
	806	export type TeamRole = "maintainer" \| "member";
7298a17Claude	807
	808	/**
	809	* 2FA / TOTP (Block B4).
	810	*
	811	* Secret is stored in plain Base32 for now — the DB has row-level-secure
	812	* access and the app boundary is the only code that reads it. A follow-up
	813	* (B4.1) will wrap it with AES-GCM at rest once we standardise the KEK.
	814	*
	815	* `enabledAt` is set only after the user has successfully entered their
	816	* first code (confirming the authenticator was set up correctly). Rows with
	817	* `enabledAt = NULL` represent pending enrolment.
	818	*/
	819	export const userTotp = pgTable("user_totp", {
	820	userId: uuid("user_id")
	821	.primaryKey()
	822	.references(() => users.id, { onDelete: "cascade" }),
	823	secret: text("secret").notNull(),
	824	enabledAt: timestamp("enabled_at"),
	825	lastUsedAt: timestamp("last_used_at"),
	826	createdAt: timestamp("created_at").defaultNow().notNull(),
	827	});
	828
	829	/**
	830	* Recovery codes — single-use fallback when the authenticator is lost.
	831	* Stored as SHA-256 hashes; used rows are marked with `usedAt` rather than
	832	* deleted so the audit log keeps the full history.
	833	*/
	834	export const userRecoveryCodes = pgTable(
	835	"user_recovery_codes",
	836	{
	837	id: uuid("id").primaryKey().defaultRandom(),
	838	userId: uuid("user_id")
	839	.notNull()
	840	.references(() => users.id, { onDelete: "cascade" }),
	841	codeHash: text("code_hash").notNull(),
	842	usedAt: timestamp("used_at"),
	843	createdAt: timestamp("created_at").defaultNow().notNull(),
	844	},
	845	(table) => [
	846	index("recovery_codes_user").on(table.userId),
	847	uniqueIndex("recovery_codes_user_hash").on(table.userId, table.codeHash),
	848	]
	849	);
	850
	851	export type UserTotp = typeof userTotp.$inferSelect;
	852	export type UserRecoveryCode = typeof userRecoveryCodes.$inferSelect;
2df1f8cClaude	853
	854	/**
	855	* WebAuthn passkeys (Block B5).
	856	*
	857	* Each row is one registered authenticator. The `credentialId` is the
	858	* globally-unique identifier the browser returns; `publicKey` is the
	859	* COSE-encoded public key we use to verify signatures. `counter` tracks
	860	* the authenticator's signature counter for replay-protection.
	861	*
	862	* `transports` is a JSON array (stored as text) of the
	863	* AuthenticatorTransport values ("usb" \| "nfc" \| "ble" \| "internal" \| "hybrid").
	864	*/
	865	export const userPasskeys = pgTable(
	866	"user_passkeys",
	867	{
	868	id: uuid("id").primaryKey().defaultRandom(),
	869	userId: uuid("user_id")
	870	.notNull()
	871	.references(() => users.id, { onDelete: "cascade" }),
	872	credentialId: text("credential_id").notNull().unique(),
	873	publicKey: text("public_key").notNull(), // base64url of COSE key
	874	counter: integer("counter").default(0).notNull(),
	875	transports: text("transports"), // JSON array string
	876	name: text("name").notNull().default("Passkey"),
	877	lastUsedAt: timestamp("last_used_at"),
	878	createdAt: timestamp("created_at").defaultNow().notNull(),
	879	},
	880	(table) => [index("passkeys_user").on(table.userId)]
	881	);
	882
	883	/**
	884	* Short-lived WebAuthn challenges. A row is written when we issue options
	885	* (registration or authentication) and deleted after the verify step or when
	886	* it expires (5 min). Keeping them in the DB lets us verify without sticky
	887	* sessions or client-side state.
	888	*/
	889	export const webauthnChallenges = pgTable(
	890	"webauthn_challenges",
	891	{
	892	id: uuid("id").primaryKey().defaultRandom(),
	893	userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
	894	// For passwordless login we don't know the user yet, so userId is nullable
	895	// and we bind the challenge to a short-lived cookie token instead.
	896	sessionKey: text("session_key").notNull().unique(),
	897	challenge: text("challenge").notNull(),
	898	kind: text("kind").notNull(), // "register" \| "authenticate"
	899	expiresAt: timestamp("expires_at").notNull(),
	900	createdAt: timestamp("created_at").defaultNow().notNull(),
	901	},
	902	(table) => [index("webauthn_challenges_expires").on(table.expiresAt)]
	903	);
	904
	905	export type UserPasskey = typeof userPasskeys.$inferSelect;
	906	export type WebauthnChallenge = typeof webauthnChallenges.$inferSelect;
bfdb5e7Claude	907
	908	/**
	909	* OAuth 2.0 provider (Block B6).
	910	*
	911	* `oauthApps` is a third-party app registered by a developer. Each app has
	912	* a public `client_id`, a hashed `client_secret`, and one or more allowed
	913	* `redirect_uris` (newline-separated). The plaintext secret is shown to the
	914	* developer exactly once at creation and cannot be recovered; they can
	915	* rotate it instead.
	916	*
	917	* `oauthAuthorizations` is a short-lived authorization code issued after
	918	* the user consents at /oauth/authorize. Single-use: `usedAt` is set on
	919	* redemption so a replay after-the-fact fails.
	920	*
	921	* `oauthAccessTokens` is a long-lived bearer token plus an optional
	922	* refresh token. Both are stored as SHA-256 hashes; the plaintext values
	923	* are only returned to the client once in the /oauth/token response.
	924	*/
	925	export const oauthApps = pgTable(
	926	"oauth_apps",
	927	{
	928	id: uuid("id").primaryKey().defaultRandom(),
	929	ownerId: uuid("owner_id")
	930	.notNull()
	931	.references(() => users.id, { onDelete: "cascade" }),
	932	name: text("name").notNull(),
	933	clientId: text("client_id").notNull().unique(),
	934	clientSecretHash: text("client_secret_hash").notNull(),
	935	clientSecretPrefix: text("client_secret_prefix").notNull(), // first 8 chars for display
	936	/** Newline-separated list of allowed redirect URIs. */
	937	redirectUris: text("redirect_uris").notNull(),
	938	homepageUrl: text("homepage_url"),
	939	description: text("description"),
	940	/**
	941	* If `true`, the app must present its client_secret at /oauth/token.
	942	* Public SPA/mobile apps should set this to `false` and use PKCE.
	943	*/
	944	confidential: boolean("confidential").default(true).notNull(),
	945	revokedAt: timestamp("revoked_at"),
	946	createdAt: timestamp("created_at").defaultNow().notNull(),
	947	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	948	},
	949	(table) => [index("oauth_apps_owner").on(table.ownerId)]
	950	);
	951
	952	export const oauthAuthorizations = pgTable(
	953	"oauth_authorizations",
	954	{
	955	id: uuid("id").primaryKey().defaultRandom(),
	956	appId: uuid("app_id")
	957	.notNull()
	958	.references(() => oauthApps.id, { onDelete: "cascade" }),
	959	userId: uuid("user_id")
	960	.notNull()
	961	.references(() => users.id, { onDelete: "cascade" }),
	962	codeHash: text("code_hash").notNull().unique(),
	963	redirectUri: text("redirect_uri").notNull(),
	964	scopes: text("scopes").notNull().default(""),
	965	codeChallenge: text("code_challenge"),
	966	codeChallengeMethod: text("code_challenge_method"), // "S256" \| "plain"
	967	expiresAt: timestamp("expires_at").notNull(),
	968	usedAt: timestamp("used_at"),
	969	createdAt: timestamp("created_at").defaultNow().notNull(),
	970	},
	971	(table) => [index("oauth_authorizations_expires").on(table.expiresAt)]
	972	);
	973
	974	export const oauthAccessTokens = pgTable(
	975	"oauth_access_tokens",
	976	{
	977	id: uuid("id").primaryKey().defaultRandom(),
	978	appId: uuid("app_id")
	979	.notNull()
	980	.references(() => oauthApps.id, { onDelete: "cascade" }),
	981	userId: uuid("user_id")
	982	.notNull()
	983	.references(() => users.id, { onDelete: "cascade" }),
	984	accessTokenHash: text("access_token_hash").notNull().unique(),
	985	refreshTokenHash: text("refresh_token_hash").unique(),
	986	scopes: text("scopes").notNull().default(""),
	987	expiresAt: timestamp("expires_at").notNull(),
	988	refreshExpiresAt: timestamp("refresh_expires_at"),
	989	revokedAt: timestamp("revoked_at"),
	990	lastUsedAt: timestamp("last_used_at"),
	991	createdAt: timestamp("created_at").defaultNow().notNull(),
	992	},
	993	(table) => [
	994	index("oauth_access_tokens_user").on(table.userId),
	995	index("oauth_access_tokens_app").on(table.appId),
	996	index("oauth_access_tokens_expires").on(table.expiresAt),
	997	]
	998	);
	999
	1000	export type OauthApp = typeof oauthApps.$inferSelect;
	1001	export type OauthAuthorization = typeof oauthAuthorizations.$inferSelect;
	1002	export type OauthAccessToken = typeof oauthAccessTokens.$inferSelect;
eafe8c6Claude	1003
	1004	/**
	1005	* Actions-equivalent workflow runner (Block C1).
	1006	*
	1007	* `workflows` rows are the YAML files discovered at `.gluecron/workflows/*.yml`
	1008	* on the repo's default branch. `parsed` is the normalised JSON form used by
	1009	* the runner so we don't re-parse on every trigger.
	1010	*
	1011	* `workflow_runs` is one execution: one row per trigger event. Status
	1012	* progression: queued → running → success\|failure\|cancelled. `conclusion`
	1013	* stays null until `status` is terminal.
	1014	*
	1015	* `workflow_jobs` is a single job within a run — each has its own steps
	1016	* array and concatenated logs. We keep logs inline for v1 (no streaming)
	1017	* to avoid a fifth table; they're truncated at the runner.
	1018	*
	1019	* `workflow_artifacts` persist files a job uploaded. `content` is a bytea;
	1020	* we'll move this to object storage once we hit size limits.
	1021	*/
	1022	export const workflows = pgTable(
	1023	"workflows",
	1024	{
	1025	id: uuid("id").primaryKey().defaultRandom(),
	1026	repositoryId: uuid("repository_id")
	1027	.notNull()
	1028	.references(() => repositories.id, { onDelete: "cascade" }),
	1029	name: text("name").notNull(),
	1030	path: text("path").notNull(), // e.g. ".gluecron/workflows/ci.yml"
	1031	yaml: text("yaml").notNull(),
	1032	parsed: text("parsed").notNull(), // JSON string
	1033	onEvents: text("on_events").notNull().default("[]"), // JSON array of event names
	1034	disabled: boolean("disabled").default(false).notNull(),
	1035	createdAt: timestamp("created_at").defaultNow().notNull(),
	1036	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1037	},
	1038	(table) => [
	1039	index("workflows_repo").on(table.repositoryId),
	1040	uniqueIndex("workflows_repo_path").on(table.repositoryId, table.path),
	1041	]
	1042	);
	1043
	1044	export const workflowRuns = pgTable(
	1045	"workflow_runs",
	1046	{
	1047	id: uuid("id").primaryKey().defaultRandom(),
	1048	workflowId: uuid("workflow_id")
	1049	.notNull()
	1050	.references(() => workflows.id, { onDelete: "cascade" }),
	1051	repositoryId: uuid("repository_id")
	1052	.notNull()
	1053	.references(() => repositories.id, { onDelete: "cascade" }),
	1054	runNumber: integer("run_number").notNull(),
	1055	event: text("event").notNull(), // "push" \| "pull_request" \| "manual" \| ...
	1056	ref: text("ref"),
	1057	commitSha: text("commit_sha"),
	1058	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1059	onDelete: "set null",
	1060	}),
	1061	status: text("status").notNull().default("queued"), // queued\|running\|success\|failure\|cancelled
	1062	conclusion: text("conclusion"),
	1063	queuedAt: timestamp("queued_at").defaultNow().notNull(),
	1064	startedAt: timestamp("started_at"),
	1065	finishedAt: timestamp("finished_at"),
	1066	createdAt: timestamp("created_at").defaultNow().notNull(),
	1067	},
	1068	(table) => [
	1069	index("workflow_runs_repo").on(table.repositoryId),
	1070	index("workflow_runs_status").on(table.status),
	1071	index("workflow_runs_workflow").on(table.workflowId),
	1072	]
	1073	);
	1074
	1075	export const workflowJobs = pgTable(
	1076	"workflow_jobs",
	1077	{
	1078	id: uuid("id").primaryKey().defaultRandom(),
	1079	runId: uuid("run_id")
	1080	.notNull()
	1081	.references(() => workflowRuns.id, { onDelete: "cascade" }),
	1082	name: text("name").notNull(),
	1083	jobOrder: integer("job_order").default(0).notNull(),
	1084	runsOn: text("runs_on").notNull().default("default"),
	1085	status: text("status").notNull().default("queued"),
	1086	conclusion: text("conclusion"),
	1087	exitCode: integer("exit_code"),
	1088	steps: text("steps").notNull().default("[]"), // JSON array of step results
	1089	logs: text("logs").notNull().default(""),
	1090	startedAt: timestamp("started_at"),
	1091	finishedAt: timestamp("finished_at"),
	1092	createdAt: timestamp("created_at").defaultNow().notNull(),
	1093	},
	1094	(table) => [index("workflow_jobs_run").on(table.runId)]
	1095	);
	1096
	1097	export const workflowArtifacts = pgTable(
	1098	"workflow_artifacts",
	1099	{
	1100	id: uuid("id").primaryKey().defaultRandom(),
	1101	runId: uuid("run_id")
	1102	.notNull()
	1103	.references(() => workflowRuns.id, { onDelete: "cascade" }),
	1104	jobId: uuid("job_id").references(() => workflowJobs.id, {
	1105	onDelete: "set null",
	1106	}),
	1107	name: text("name").notNull(),
	1108	sizeBytes: integer("size_bytes").default(0).notNull(),
	1109	contentType: text("content_type")
	1110	.default("application/octet-stream")
	1111	.notNull(),
	1112	// bytea — drizzle doesn't have a built-in bytea type at the level we use
	1113	// elsewhere; store as text (base64) for v1. Migration uses real bytea so
	1114	// we can swap the column type later.
	1115	content: text("content"),
	1116	createdAt: timestamp("created_at").defaultNow().notNull(),
	1117	},
	1118	(table) => [index("workflow_artifacts_run").on(table.runId)]
	1119	);
	1120
	1121	export type Workflow = typeof workflows.$inferSelect;
	1122	export type WorkflowRun = typeof workflowRuns.$inferSelect;
	1123	export type WorkflowJob = typeof workflowJobs.$inferSelect;
	1124	export type WorkflowArtifact = typeof workflowArtifacts.$inferSelect;
e2da5c6Claude	1125
	1126	// ---------------------------------------------------------------------------
	1127	// Block C2 — Package registry (npm-compatible)
	1128	// ---------------------------------------------------------------------------
	1129
	1130	export const packages = pgTable(
	1131	"packages",
	1132	{
	1133	id: uuid("id").primaryKey().defaultRandom(),
	1134	repositoryId: uuid("repository_id")
	1135	.notNull()
	1136	.references(() => repositories.id, { onDelete: "cascade" }),
	1137	ecosystem: text("ecosystem").notNull().default("npm"), // "npm" \| "container"
	1138	scope: text("scope"), // "@acme" for npm; null for unscoped
	1139	name: text("name").notNull(), // "my-lib" (without scope)
	1140	description: text("description"),
	1141	readme: text("readme"),
	1142	homepage: text("homepage"),
	1143	license: text("license"),
	1144	visibility: text("visibility").notNull().default("public"), // "public" \| "private"
	1145	createdAt: timestamp("created_at").defaultNow().notNull(),
	1146	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1147	},
	1148	(table) => [
	1149	index("packages_repo").on(table.repositoryId),
	1150	uniqueIndex("packages_eco_scope_name").on(
	1151	table.ecosystem,
	1152	table.scope,
	1153	table.name
	1154	),
	1155	]
	1156	);
	1157
	1158	export const packageVersions = pgTable(
	1159	"package_versions",
	1160	{
	1161	id: uuid("id").primaryKey().defaultRandom(),
	1162	packageId: uuid("package_id")
	1163	.notNull()
	1164	.references(() => packages.id, { onDelete: "cascade" }),
	1165	version: text("version").notNull(), // "1.2.3"
	1166	shasum: text("shasum").notNull(), // sha1 (for npm compat) hex
	1167	integrity: text("integrity"), // "sha512-..." base64
	1168	sizeBytes: integer("size_bytes").default(0).notNull(),
	1169	metadata: text("metadata").notNull().default("{}"), // package.json JSON
	1170	tarball: text("tarball"), // base64-encoded; bytea in migration
	1171	publishedBy: uuid("published_by").references(() => users.id, {
	1172	onDelete: "set null",
	1173	}),
	1174	yanked: boolean("yanked").default(false).notNull(),
	1175	yankedReason: text("yanked_reason"),
	1176	publishedAt: timestamp("published_at").defaultNow().notNull(),
	1177	},
	1178	(table) => [
	1179	index("package_versions_pkg").on(table.packageId),
	1180	uniqueIndex("package_versions_pkg_version").on(table.packageId, table.version),
	1181	]
	1182	);
	1183
	1184	export const packageTags = pgTable(
	1185	"package_tags",
	1186	{
	1187	id: uuid("id").primaryKey().defaultRandom(),
	1188	packageId: uuid("package_id")
	1189	.notNull()
	1190	.references(() => packages.id, { onDelete: "cascade" }),
	1191	tag: text("tag").notNull(), // "latest" \| "beta" \| ...
	1192	versionId: uuid("version_id")
	1193	.notNull()
	1194	.references(() => packageVersions.id, { onDelete: "cascade" }),
	1195	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1196	},
	1197	(table) => [
	1198	uniqueIndex("package_tags_pkg_tag").on(table.packageId, table.tag),
	1199	]
	1200	);
	1201
	1202	export type Package = typeof packages.$inferSelect;
	1203	export type PackageVersion = typeof packageVersions.$inferSelect;
	1204	export type PackageTag = typeof packageTags.$inferSelect;
	1205
	1206	// ---------------------------------------------------------------------------
	1207	// Block C3 — Pages / static hosting
	1208	// ---------------------------------------------------------------------------
	1209
	1210	export const pagesDeployments = pgTable(
	1211	"pages_deployments",
	1212	{
	1213	id: uuid("id").primaryKey().defaultRandom(),
	1214	repositoryId: uuid("repository_id")
	1215	.notNull()
	1216	.references(() => repositories.id, { onDelete: "cascade" }),
	1217	ref: text("ref").notNull().default("refs/heads/gh-pages"),
	1218	commitSha: text("commit_sha").notNull(),
	1219	status: text("status").notNull().default("success"), // "success" \| "failed"
	1220	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1221	onDelete: "set null",
	1222	}),
	1223	createdAt: timestamp("created_at").defaultNow().notNull(),
	1224	},
	1225	(table) => [
	1226	index("pages_deployments_repo").on(table.repositoryId),
	1227	index("pages_deployments_created").on(table.createdAt),
	1228	]
	1229	);
	1230
	1231	export const pagesSettings = pgTable("pages_settings", {
	1232	repositoryId: uuid("repository_id")
	1233	.primaryKey()
	1234	.references(() => repositories.id, { onDelete: "cascade" }),
	1235	enabled: boolean("enabled").default(true).notNull(),
	1236	sourceBranch: text("source_branch").notNull().default("gh-pages"),
	1237	sourceDir: text("source_dir").notNull().default("/"), // e.g. "/" or "/docs"
	1238	customDomain: text("custom_domain"),
	1239	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1240	});
	1241
	1242	export type PagesDeployment = typeof pagesDeployments.$inferSelect;
	1243	export type PagesSettings = typeof pagesSettings.$inferSelect;
	1244
	1245	// ---------------------------------------------------------------------------
	1246	// Block C4 — Environments with protected approvals
	1247	// ---------------------------------------------------------------------------
	1248
	1249	export const environments = pgTable(
	1250	"environments",
	1251	{
	1252	id: uuid("id").primaryKey().defaultRandom(),
	1253	repositoryId: uuid("repository_id")
	1254	.notNull()
	1255	.references(() => repositories.id, { onDelete: "cascade" }),
	1256	name: text("name").notNull(), // "production" \| "staging" \| "preview"
	1257	requireApproval: boolean("require_approval").default(false).notNull(),
	1258	// JSON array of user IDs that can approve deploys.
	1259	reviewers: text("reviewers").notNull().default("[]"),
	1260	waitTimerMinutes: integer("wait_timer_minutes").default(0).notNull(),
	1261	allowedBranches: text("allowed_branches").notNull().default("[]"), // JSON glob patterns
	1262	createdAt: timestamp("created_at").defaultNow().notNull(),
	1263	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1264	},
	1265	(table) => [
	1266	uniqueIndex("environments_repo_name").on(table.repositoryId, table.name),
	1267	]
	1268	);
	1269
	1270	export const deploymentApprovals = pgTable(
	1271	"deployment_approvals",
	1272	{
	1273	id: uuid("id").primaryKey().defaultRandom(),
	1274	deploymentId: uuid("deployment_id")
	1275	.notNull()
	1276	.references(() => deployments.id, { onDelete: "cascade" }),
	1277	userId: uuid("user_id")
	1278	.notNull()
	1279	.references(() => users.id, { onDelete: "cascade" }),
	1280	decision: text("decision").notNull(), // "approved" \| "rejected"
	1281	comment: text("comment"),
	1282	createdAt: timestamp("created_at").defaultNow().notNull(),
	1283	},
	1284	(table) => [
	1285	index("deployment_approvals_deployment").on(table.deploymentId),
	1286	]
	1287	);
	1288
	1289	export type Environment = typeof environments.$inferSelect;
	1290	export type DeploymentApproval = typeof deploymentApprovals.$inferSelect;
3cbe3d6Claude	1291
	1292	// ---------------------------------------------------------------------------
	1293	// Block D — AI-native differentiation (migration 0012)
	1294	// ---------------------------------------------------------------------------
	1295
	1296	// D6 — cached "explain this codebase" markdown keyed on commit sha.
	1297	export const codebaseExplanations = pgTable(
	1298	"codebase_explanations",
	1299	{
	1300	id: uuid("id").primaryKey().defaultRandom(),
	1301	repositoryId: uuid("repository_id")
	1302	.notNull()
	1303	.references(() => repositories.id, { onDelete: "cascade" }),
	1304	commitSha: text("commit_sha").notNull(),
	1305	summary: text("summary").notNull(),
	1306	markdown: text("markdown").notNull(),
	1307	model: text("model").notNull(),
	1308	generatedAt: timestamp("generated_at").defaultNow().notNull(),
	1309	},
	1310	(table) => [
	1311	uniqueIndex("codebase_explanations_repo_sha").on(
	1312	table.repositoryId,
	1313	table.commitSha
	1314	),
	1315	]
	1316	);
	1317
	1318	// D2 — AI dependency bumper run history.
	1319	export const depUpdateRuns = pgTable(
	1320	"dep_update_runs",
	1321	{
	1322	id: uuid("id").primaryKey().defaultRandom(),
	1323	repositoryId: uuid("repository_id")
	1324	.notNull()
	1325	.references(() => repositories.id, { onDelete: "cascade" }),
	1326	status: text("status").notNull().default("pending"), // pending\|running\|success\|failed\|no_updates
	1327	ecosystem: text("ecosystem").notNull(), // npm\|bun
	1328	manifestPath: text("manifest_path").notNull(),
	1329	attemptedBumps: text("attempted_bumps").notNull().default("[]"), // JSON
	1330	appliedBumps: text("applied_bumps").notNull().default("[]"), // JSON
	1331	branchName: text("branch_name"),
	1332	prNumber: integer("pr_number"),
	1333	errorMessage: text("error_message"),
	1334	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1335	onDelete: "set null",
	1336	}),
	1337	createdAt: timestamp("created_at").defaultNow().notNull(),
	1338	completedAt: timestamp("completed_at"),
	1339	},
	1340	(table) => [
	1341	index("dep_update_runs_repo").on(table.repositoryId),
	1342	index("dep_update_runs_created").on(table.createdAt),
	1343	]
	1344	);
	1345
	1346	// D1 — code chunks for semantic search. Embedding stored as JSON-encoded
	1347	// number array in text to avoid requiring pgvector; cosine similarity is
	1348	// computed in JS. Upgrade path: ALTER COLUMN embedding TYPE vector(1024).
	1349	export const codeChunks = pgTable(
	1350	"code_chunks",
	1351	{
	1352	id: uuid("id").primaryKey().defaultRandom(),
	1353	repositoryId: uuid("repository_id")
	1354	.notNull()
	1355	.references(() => repositories.id, { onDelete: "cascade" }),
	1356	commitSha: text("commit_sha").notNull(),
	1357	path: text("path").notNull(),
	1358	startLine: integer("start_line").notNull(),
	1359	endLine: integer("end_line").notNull(),
	1360	content: text("content").notNull(),
	1361	embedding: text("embedding"), // JSON number[]
	1362	embeddingModel: text("embedding_model"),
	1363	createdAt: timestamp("created_at").defaultNow().notNull(),
	1364	},
	1365	(table) => [
	1366	index("code_chunks_repo").on(table.repositoryId),
	1367	index("code_chunks_repo_path").on(table.repositoryId, table.path),
	1368	]
	1369	);
	1370
	1371	export type CodebaseExplanation = typeof codebaseExplanations.$inferSelect;
	1372	export type DepUpdateRun = typeof depUpdateRuns.$inferSelect;
1e162a8Claude	1373
	1374	// ---------------------------------------------------------------------------
	1375	// Block E2 — Discussions (migration 0013)
	1376	// ---------------------------------------------------------------------------
	1377
	1378	/**
	1379	* Discussions — forum-style threaded conversations attached to a repo.
	1380	* Similar to GitHub Discussions: categorised + pinnable + answerable.
	1381	*/
	1382	export const discussions = pgTable(
	1383	"discussions",
	1384	{
	1385	id: uuid("id").primaryKey().defaultRandom(),
	1386	number: serial("number"),
	1387	repositoryId: uuid("repository_id")
	1388	.notNull()
	1389	.references(() => repositories.id, { onDelete: "cascade" }),
	1390	authorId: uuid("author_id")
	1391	.notNull()
	1392	.references(() => users.id),
	1393	// one of: "general" \| "q-and-a" \| "ideas" \| "announcements" \| "show-and-tell"
	1394	category: text("category").notNull().default("general"),
	1395	title: text("title").notNull(),
	1396	body: text("body"),
	1397	state: text("state").notNull().default("open"), // open, closed
	1398	locked: boolean("locked").notNull().default(false),
	1399	answerCommentId: uuid("answer_comment_id"),
	1400	pinned: boolean("pinned").notNull().default(false),
	1401	createdAt: timestamp("created_at").defaultNow().notNull(),
	1402	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1403	},
	1404	(table) => [
	1405	index("discussions_repo").on(table.repositoryId),
	1406	uniqueIndex("discussions_repo_number").on(
	1407	table.repositoryId,
	1408	table.number
	1409	),
	1410	]
	1411	);
	1412
	1413	export const discussionComments = pgTable(
	1414	"discussion_comments",
	1415	{
	1416	id: uuid("id").primaryKey().defaultRandom(),
	1417	discussionId: uuid("discussion_id")
	1418	.notNull()
	1419	.references(() => discussions.id, { onDelete: "cascade" }),
	1420	parentCommentId: uuid("parent_comment_id"),
	1421	authorId: uuid("author_id")
	1422	.notNull()
	1423	.references(() => users.id),
	1424	body: text("body").notNull(),
	1425	isAnswer: boolean("is_answer").notNull().default(false),
	1426	createdAt: timestamp("created_at").defaultNow().notNull(),
	1427	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1428	},
	1429	(table) => [
	1430	index("discussion_comments_discussion").on(table.discussionId),
	1431	]
	1432	);
	1433
	1434	export type Discussion = typeof discussions.$inferSelect;
	1435	export type DiscussionComment = typeof discussionComments.$inferSelect;
3cbe3d6Claude	1436	export type CodeChunk = typeof codeChunks.$inferSelect;
1e162a8Claude	1437
	1438	// ---------------------------------------------------------------------------
	1439	// Block E4 — Gists (migration 0014)
	1440	// ---------------------------------------------------------------------------
	1441	//
	1442	// User-owned small snippets/files that behave like tiny repos. DB-backed
	1443	// for v1 (no bare git repo): each gist owns a collection of gist_files and
	1444	// every edit appends a gist_revisions row with a JSON snapshot.
	1445
	1446	export const gists = pgTable(
	1447	"gists",
	1448	{
	1449	id: uuid("id").primaryKey().defaultRandom(),
	1450	ownerId: uuid("owner_id")
	1451	.notNull()
	1452	.references(() => users.id, { onDelete: "cascade" }),
	1453	// 8-char hex slug used in pretty URLs (e.g. /gists/a1b2c3d4).
	1454	slug: text("slug").notNull().unique(),
	1455	title: text("title").notNull().default(""),
	1456	description: text("description").notNull().default(""),
	1457	isPublic: boolean("is_public").default(true).notNull(),
	1458	createdAt: timestamp("created_at").defaultNow().notNull(),
	1459	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1460	},
	1461	(table) => [index("gists_owner").on(table.ownerId)]
	1462	);
	1463
	1464	export const gistFiles = pgTable(
	1465	"gist_files",
	1466	{
	1467	id: uuid("id").primaryKey().defaultRandom(),
	1468	gistId: uuid("gist_id")
	1469	.notNull()
	1470	.references(() => gists.id, { onDelete: "cascade" }),
	1471	filename: text("filename").notNull(),
	1472	// Optional explicit language override; falls back to filename detection.
	1473	language: text("language"),
	1474	content: text("content").notNull().default(""),
	1475	sizeBytes: integer("size_bytes").default(0).notNull(),
	1476	},
	1477	(table) => [
	1478	index("gist_files_gist").on(table.gistId),
	1479	uniqueIndex("gist_files_gist_filename").on(table.gistId, table.filename),
	1480	]
	1481	);
	1482
	1483	export const gistRevisions = pgTable(
	1484	"gist_revisions",
	1485	{
	1486	id: uuid("id").primaryKey().defaultRandom(),
	1487	gistId: uuid("gist_id")
	1488	.notNull()
	1489	.references(() => gists.id, { onDelete: "cascade" }),
	1490	revision: integer("revision").notNull(),
	1491	// JSON-encoded {filename: content} map capturing the full snapshot at
	1492	// this revision. Stored as text to avoid requiring jsonb.
	1493	snapshot: text("snapshot").notNull().default("{}"),
	1494	authorId: uuid("author_id")
	1495	.notNull()
	1496	.references(() => users.id, { onDelete: "cascade" }),
	1497	message: text("message"),
	1498	createdAt: timestamp("created_at").defaultNow().notNull(),
	1499	},
	1500	(table) => [
	1501	index("gist_revisions_gist_rev").on(table.gistId, table.revision),
	1502	]
	1503	);
	1504
	1505	export const gistStars = pgTable(
	1506	"gist_stars",
	1507	{
	1508	id: uuid("id").primaryKey().defaultRandom(),
	1509	gistId: uuid("gist_id")
	1510	.notNull()
	1511	.references(() => gists.id, { onDelete: "cascade" }),
	1512	userId: uuid("user_id")
	1513	.notNull()
	1514	.references(() => users.id, { onDelete: "cascade" }),
	1515	createdAt: timestamp("created_at").defaultNow().notNull(),
	1516	},
	1517	(table) => [uniqueIndex("gist_stars_gist_user").on(table.gistId, table.userId)]
	1518	);
	1519
	1520	export type Gist = typeof gists.$inferSelect;
	1521	export type GistFile = typeof gistFiles.$inferSelect;
	1522	export type GistRevision = typeof gistRevisions.$inferSelect;
	1523	export type GistStar = typeof gistStars.$inferSelect;
	1524
	1525	// ---------------------------------------------------------------------------
	1526	// Block E1 — Projects / kanban (migration 0015)
	1527	// ---------------------------------------------------------------------------
	1528
	1529	export const projects = pgTable(
	1530	"projects",
	1531	{
	1532	id: uuid("id").primaryKey().defaultRandom(),
	1533	number: serial("number"),
	1534	repositoryId: uuid("repository_id")
	1535	.notNull()
	1536	.references(() => repositories.id, { onDelete: "cascade" }),
	1537	ownerId: uuid("owner_id")
	1538	.notNull()
	1539	.references(() => users.id),
	1540	title: text("title").notNull(),
	1541	description: text("description").notNull().default(""),
	1542	state: text("state").notNull().default("open"), // open \| closed
	1543	createdAt: timestamp("created_at").defaultNow().notNull(),
	1544	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1545	},
	1546	(table) => [
	1547	index("projects_repo").on(table.repositoryId),
	1548	uniqueIndex("projects_repo_number").on(table.repositoryId, table.number),
	1549	]
	1550	);
	1551
	1552	export const projectColumns = pgTable(
	1553	"project_columns",
	1554	{
	1555	id: uuid("id").primaryKey().defaultRandom(),
	1556	projectId: uuid("project_id")
	1557	.notNull()
	1558	.references(() => projects.id, { onDelete: "cascade" }),
	1559	name: text("name").notNull(),
	1560	position: integer("position").notNull().default(0),
	1561	createdAt: timestamp("created_at").defaultNow().notNull(),
	1562	},
	1563	(table) => [index("project_columns_project").on(table.projectId)]
	1564	);
	1565
	1566	export const projectItems = pgTable(
	1567	"project_items",
	1568	{
	1569	id: uuid("id").primaryKey().defaultRandom(),
	1570	projectId: uuid("project_id")
	1571	.notNull()
	1572	.references(() => projects.id, { onDelete: "cascade" }),
	1573	columnId: uuid("column_id")
	1574	.notNull()
	1575	.references(() => projectColumns.id, { onDelete: "cascade" }),
	1576	position: integer("position").notNull().default(0),
	1577	// "note" \| "issue" \| "pr" — application-level FK on itemId by type
	1578	itemType: text("item_type").notNull().default("note"),
	1579	itemId: uuid("item_id"),
	1580	title: text("title").notNull().default(""),
	1581	note: text("note").notNull().default(""),
	1582	createdAt: timestamp("created_at").defaultNow().notNull(),
	1583	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1584	},
	1585	(table) => [
	1586	index("project_items_project").on(table.projectId),
	1587	index("project_items_column").on(table.columnId, table.position),
	1588	]
	1589	);
	1590
	1591	export type Project = typeof projects.$inferSelect;
	1592	export type ProjectColumn = typeof projectColumns.$inferSelect;
	1593	export type ProjectItem = typeof projectItems.$inferSelect;
	1594
	1595	// ---------------------------------------------------------------------------
	1596	// Block E3 — Wikis (migration 0016)
	1597	// ---------------------------------------------------------------------------
	1598	// DB-backed for v1; git-backed mirror is a future upgrade.
	1599
	1600	export const wikiPages = pgTable(
	1601	"wiki_pages",
	1602	{
	1603	id: uuid("id").primaryKey().defaultRandom(),
	1604	repositoryId: uuid("repository_id")
	1605	.notNull()
	1606	.references(() => repositories.id, { onDelete: "cascade" }),
	1607	slug: text("slug").notNull(),
	1608	title: text("title").notNull(),
	1609	body: text("body").notNull().default(""),
	1610	revision: integer("revision").notNull().default(1),
	1611	createdAt: timestamp("created_at").defaultNow().notNull(),
	1612	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1613	updatedBy: uuid("updated_by").references(() => users.id),
	1614	},
	1615	(table) => [
	1616	index("wiki_pages_repo").on(table.repositoryId),
	1617	uniqueIndex("wiki_pages_repo_slug").on(table.repositoryId, table.slug),
	1618	]
	1619	);
	1620
	1621	export const wikiRevisions = pgTable(
	1622	"wiki_revisions",
	1623	{
	1624	id: uuid("id").primaryKey().defaultRandom(),
	1625	pageId: uuid("page_id")
	1626	.notNull()
	1627	.references(() => wikiPages.id, { onDelete: "cascade" }),
	1628	revision: integer("revision").notNull(),
	1629	title: text("title").notNull(),
	1630	body: text("body").notNull().default(""),
	1631	message: text("message"),
	1632	authorId: uuid("author_id")
	1633	.notNull()
	1634	.references(() => users.id),
	1635	createdAt: timestamp("created_at").defaultNow().notNull(),
	1636	},
	1637	(table) => [index("wiki_revisions_page").on(table.pageId, table.revision)]
	1638	);
	1639
	1640	export type WikiPage = typeof wikiPages.$inferSelect;
	1641	export type WikiRevision = typeof wikiRevisions.$inferSelect;
a79a9edClaude	1642
	1643	// ---------------------------------------------------------------------------
	1644	// Block E5 — Merge queues (migration 0017)
	1645	// ---------------------------------------------------------------------------
	1646
	1647	export const mergeQueueEntries = pgTable(
	1648	"merge_queue_entries",
	1649	{
	1650	id: uuid("id").primaryKey().defaultRandom(),
	1651	repositoryId: uuid("repository_id")
	1652	.notNull()
	1653	.references(() => repositories.id, { onDelete: "cascade" }),
	1654	pullRequestId: uuid("pull_request_id")
	1655	.notNull()
	1656	.references(() => pullRequests.id, { onDelete: "cascade" }),
	1657	baseBranch: text("base_branch").notNull(),
	1658	// queued \| running \| merged \| failed \| dequeued
	1659	state: text("state").notNull().default("queued"),
	1660	position: integer("position").notNull().default(0),
	1661	enqueuedBy: uuid("enqueued_by").references(() => users.id),
	1662	enqueuedAt: timestamp("enqueued_at").defaultNow().notNull(),
	1663	startedAt: timestamp("started_at"),
	1664	finishedAt: timestamp("finished_at"),
	1665	errorMessage: text("error_message"),
	1666	},
	1667	(table) => [
	1668	index("merge_queue_repo_branch").on(
	1669	table.repositoryId,
	1670	table.baseBranch,
	1671	table.state
	1672	),
	1673	]
	1674	);
	1675
	1676	export type MergeQueueEntry = typeof mergeQueueEntries.$inferSelect;
	1677
	1678	// ---------------------------------------------------------------------------
	1679	// Block E6 — Required status checks matrix (migration 0018)
	1680	// ---------------------------------------------------------------------------
	1681
	1682	export const branchRequiredChecks = pgTable(
	1683	"branch_required_checks",
	1684	{
	1685	id: uuid("id").primaryKey().defaultRandom(),
	1686	branchProtectionId: uuid("branch_protection_id")
	1687	.notNull()
	1688	.references(() => branchProtection.id, { onDelete: "cascade" }),
	1689	checkName: text("check_name").notNull(),
	1690	createdAt: timestamp("created_at").defaultNow().notNull(),
	1691	},
	1692	(table) => [
	1693	index("branch_required_checks_rule").on(table.branchProtectionId),
	1694	uniqueIndex("branch_required_checks_unique").on(
	1695	table.branchProtectionId,
	1696	table.checkName
	1697	),
	1698	]
	1699	);
	1700
	1701	export type BranchRequiredCheck = typeof branchRequiredChecks.$inferSelect;
	1702
	1703	// ---------------------------------------------------------------------------
	1704	// Block E7 — Protected tags (migration 0019)
	1705	// ---------------------------------------------------------------------------
	1706
	1707	export const protectedTags = pgTable(
	1708	"protected_tags",
	1709	{
	1710	id: uuid("id").primaryKey().defaultRandom(),
	1711	repositoryId: uuid("repository_id")
	1712	.notNull()
	1713	.references(() => repositories.id, { onDelete: "cascade" }),
	1714	pattern: text("pattern").notNull(),
	1715	createdAt: timestamp("created_at").defaultNow().notNull(),
	1716	createdBy: uuid("created_by").references(() => users.id),
	1717	},
	1718	(table) => [
	1719	index("protected_tags_repo").on(table.repositoryId),
	1720	uniqueIndex("protected_tags_repo_pattern").on(
	1721	table.repositoryId,
	1722	table.pattern
	1723	),
	1724	]
	1725	);
	1726
	1727	export type ProtectedTag = typeof protectedTags.$inferSelect;
8f50ed0Claude	1728
	1729	// ---------------------------------------------------------------------------
	1730	// Block F — Observability + admin (migration 0020)
	1731	// ---------------------------------------------------------------------------
	1732
	1733	// F1 — Traffic analytics per repo
	1734	export const repoTrafficEvents = pgTable(
	1735	"repo_traffic_events",
	1736	{
	1737	id: uuid("id").primaryKey().defaultRandom(),
	1738	repositoryId: uuid("repository_id")
	1739	.notNull()
	1740	.references(() => repositories.id, { onDelete: "cascade" }),
	1741	kind: text("kind").notNull(), // view \| clone \| api \| ui
	1742	path: text("path"),
	1743	userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
	1744	ipHash: text("ip_hash"),
	1745	userAgent: text("user_agent"),
	1746	referer: text("referer"),
	1747	createdAt: timestamp("created_at").defaultNow().notNull(),
	1748	},
	1749	(table) => [
	1750	index("repo_traffic_events_repo_time").on(
	1751	table.repositoryId,
	1752	table.createdAt
	1753	),
	1754	index("repo_traffic_events_kind").on(
	1755	table.repositoryId,
	1756	table.kind,
	1757	table.createdAt
	1758	),
	1759	]
	1760	);
	1761
	1762	export type RepoTrafficEvent = typeof repoTrafficEvents.$inferSelect;
	1763
	1764	// F3 — Admin panel (site admins + toggleable flags)
	1765	export const systemFlags = pgTable("system_flags", {
	1766	key: text("key").primaryKey(),
	1767	value: text("value").notNull().default(""),
	1768	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1769	updatedBy: uuid("updated_by").references(() => users.id),
	1770	});
	1771
	1772	export type SystemFlag = typeof systemFlags.$inferSelect;
	1773
	1774	export const siteAdmins = pgTable("site_admins", {
	1775	userId: uuid("user_id")
	1776	.primaryKey()
	1777	.references(() => users.id, { onDelete: "cascade" }),
	1778	grantedAt: timestamp("granted_at").defaultNow().notNull(),
	1779	grantedBy: uuid("granted_by").references(() => users.id),
	1780	});
	1781
	1782	export type SiteAdmin = typeof siteAdmins.$inferSelect;
	1783
	1784	// F4 — Billing + quotas
	1785	export const billingPlans = pgTable("billing_plans", {
	1786	id: uuid("id").primaryKey().defaultRandom(),
	1787	slug: text("slug").notNull().unique(),
	1788	name: text("name").notNull(),
	1789	priceCents: integer("price_cents").notNull().default(0),
	1790	repoLimit: integer("repo_limit").notNull().default(10),
	1791	storageMbLimit: integer("storage_mb_limit").notNull().default(1024),
	1792	aiTokensMonthly: integer("ai_tokens_monthly").notNull().default(100000),
	1793	bandwidthGbMonthly: integer("bandwidth_gb_monthly").notNull().default(10),
	1794	privateRepos: boolean("private_repos").notNull().default(false),
	1795	createdAt: timestamp("created_at").defaultNow().notNull(),
	1796	});
	1797
	1798	export type BillingPlan = typeof billingPlans.$inferSelect;
	1799
	1800	export const userQuotas = pgTable("user_quotas", {
	1801	userId: uuid("user_id")
	1802	.primaryKey()
	1803	.references(() => users.id, { onDelete: "cascade" }),
	1804	planSlug: text("plan_slug").notNull().default("free"),
	1805	storageMbUsed: integer("storage_mb_used").notNull().default(0),
	1806	aiTokensUsedThisMonth: integer("ai_tokens_used_this_month")
	1807	.notNull()
	1808	.default(0),
	1809	bandwidthGbUsedThisMonth: integer("bandwidth_gb_used_this_month")
	1810	.notNull()
	1811	.default(0),
	1812	cycleStart: timestamp("cycle_start").defaultNow().notNull(),
	1813	updatedAt: timestamp("updated_at").defaultNow().notNull(),
619109aClaude	1814	// Stripe linkage (migration 0038). Null until the user first upgrades.
	1815	stripeCustomerId: text("stripe_customer_id"),
	1816	stripeSubscriptionId: text("stripe_subscription_id"),
	1817	stripeSubscriptionStatus: text("stripe_subscription_status"),
	1818	currentPeriodEnd: timestamp("current_period_end"),
8f50ed0Claude	1819	});
	1820
	1821	export type UserQuota = typeof userQuotas.$inferSelect;
06139e6Claude	1822
	1823	// Block H — App marketplace + bot identities (GitHub Apps equivalent)
	1824
	1825	export const apps = pgTable(
	1826	"apps",
	1827	{
	1828	id: uuid("id").primaryKey().defaultRandom(),
	1829	slug: text("slug").notNull().unique(),
	1830	name: text("name").notNull(),
	1831	description: text("description").notNull().default(""),
	1832	iconUrl: text("icon_url"),
	1833	homepageUrl: text("homepage_url"),
	1834	webhookUrl: text("webhook_url"),
	1835	webhookSecret: text("webhook_secret"),
	1836	creatorId: uuid("creator_id")
	1837	.notNull()
	1838	.references(() => users.id, { onDelete: "cascade" }),
	1839	permissions: text("permissions").notNull().default("[]"), // JSON array
	1840	defaultEvents: text("default_events").notNull().default("[]"), // JSON array
	1841	isPublic: boolean("is_public").notNull().default(true),
	1842	createdAt: timestamp("created_at").defaultNow().notNull(),
	1843	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1844	},
	1845	(table) => [index("apps_public_slug").on(table.isPublic, table.slug)]
	1846	);
	1847
	1848	export type App = typeof apps.$inferSelect;
	1849
	1850	export const appInstallations = pgTable(
	1851	"app_installations",
	1852	{
	1853	id: uuid("id").primaryKey().defaultRandom(),
	1854	appId: uuid("app_id")
	1855	.notNull()
	1856	.references(() => apps.id, { onDelete: "cascade" }),
	1857	installedBy: uuid("installed_by")
	1858	.notNull()
	1859	.references(() => users.id, { onDelete: "cascade" }),
	1860	targetType: text("target_type").notNull(), // user \| org \| repository
	1861	targetId: uuid("target_id").notNull(),
	1862	grantedPermissions: text("granted_permissions").notNull().default("[]"),
	1863	suspendedAt: timestamp("suspended_at"),
	1864	createdAt: timestamp("created_at").defaultNow().notNull(),
	1865	uninstalledAt: timestamp("uninstalled_at"),
	1866	},
	1867	(table) => [
	1868	index("app_installations_app").on(table.appId),
	1869	index("app_installations_target").on(table.targetType, table.targetId),
	1870	]
	1871	);
	1872
	1873	export type AppInstallation = typeof appInstallations.$inferSelect;
	1874
	1875	export const appBots = pgTable("app_bots", {
	1876	id: uuid("id").primaryKey().defaultRandom(),
	1877	appId: uuid("app_id")
	1878	.notNull()
	1879	.unique()
	1880	.references(() => apps.id, { onDelete: "cascade" }),
	1881	username: text("username").notNull().unique(),
	1882	displayName: text("display_name").notNull(),
	1883	avatarUrl: text("avatar_url"),
	1884	createdAt: timestamp("created_at").defaultNow().notNull(),
	1885	});
	1886
	1887	export type AppBot = typeof appBots.$inferSelect;
	1888
	1889	export const appInstallTokens = pgTable(
	1890	"app_install_tokens",
	1891	{
	1892	id: uuid("id").primaryKey().defaultRandom(),
	1893	installationId: uuid("installation_id")
	1894	.notNull()
	1895	.references(() => appInstallations.id, { onDelete: "cascade" }),
	1896	tokenHash: text("token_hash").notNull().unique(),
	1897	expiresAt: timestamp("expires_at").notNull(),
	1898	createdAt: timestamp("created_at").defaultNow().notNull(),
	1899	revokedAt: timestamp("revoked_at"),
	1900	},
	1901	(table) => [index("app_install_tokens_hash").on(table.tokenHash)]
	1902	);
	1903
	1904	export type AppInstallToken = typeof appInstallTokens.$inferSelect;
	1905
	1906	export const appEvents = pgTable(
	1907	"app_events",
	1908	{
	1909	id: uuid("id").primaryKey().defaultRandom(),
	1910	appId: uuid("app_id")
	1911	.notNull()
	1912	.references(() => apps.id, { onDelete: "cascade" }),
	1913	installationId: uuid("installation_id"),
	1914	kind: text("kind").notNull(), // installed \| uninstalled \| delivery_ok \| delivery_fail
	1915	payload: text("payload"),
	1916	responseStatus: integer("response_status"),
	1917	createdAt: timestamp("created_at").defaultNow().notNull(),
	1918	},
	1919	(table) => [index("app_events_app_time").on(table.appId, table.createdAt)]
	1920	);
	1921
	1922	export type AppEvent = typeof appEvents.$inferSelect;
71cd5ecClaude	1923
	1924	// ---------- Block I3 — Repository transfer history ----------
	1925
	1926	export const repoTransfers = pgTable(
	1927	"repo_transfers",
	1928	{
	1929	id: uuid("id").primaryKey().defaultRandom(),
	1930	repositoryId: uuid("repository_id")
	1931	.notNull()
	1932	.references(() => repositories.id, { onDelete: "cascade" }),
	1933	fromOwnerId: uuid("from_owner_id").notNull(),
	1934	fromOrgId: uuid("from_org_id"),
	1935	toOwnerId: uuid("to_owner_id").notNull(),
	1936	toOrgId: uuid("to_org_id"),
	1937	initiatedBy: uuid("initiated_by")
	1938	.notNull()
	1939	.references(() => users.id, { onDelete: "cascade" }),
	1940	createdAt: timestamp("created_at").defaultNow().notNull(),
	1941	},
	1942	(table) => [
	1943	index("repo_transfers_repo").on(table.repositoryId, table.createdAt),
	1944	]
	1945	);
	1946
	1947	export type RepoTransfer = typeof repoTransfers.$inferSelect;
08420cdClaude	1948
	1949	// ---------- Block I6 — Sponsors ----------
	1950
	1951	export const sponsorshipTiers = pgTable(
	1952	"sponsorship_tiers",
	1953	{
	1954	id: uuid("id").primaryKey().defaultRandom(),
	1955	maintainerId: uuid("maintainer_id")
	1956	.notNull()
	1957	.references(() => users.id, { onDelete: "cascade" }),
	1958	name: text("name").notNull(),
	1959	description: text("description").default("").notNull(),
	1960	monthlyCents: integer("monthly_cents").notNull(),
	1961	oneTimeAllowed: boolean("one_time_allowed").default(true).notNull(),
	1962	isActive: boolean("is_active").default(true).notNull(),
	1963	createdAt: timestamp("created_at").defaultNow().notNull(),
	1964	},
	1965	(table) => [
	1966	index("sponsor_tiers_maintainer").on(table.maintainerId, table.isActive),
	1967	]
	1968	);
	1969
	1970	export type SponsorshipTier = typeof sponsorshipTiers.$inferSelect;
	1971
	1972	export const sponsorships = pgTable(
	1973	"sponsorships",
	1974	{
	1975	id: uuid("id").primaryKey().defaultRandom(),
	1976	sponsorId: uuid("sponsor_id")
	1977	.notNull()
	1978	.references(() => users.id, { onDelete: "cascade" }),
	1979	maintainerId: uuid("maintainer_id")
	1980	.notNull()
	1981	.references(() => users.id, { onDelete: "cascade" }),
	1982	tierId: uuid("tier_id"),
	1983	amountCents: integer("amount_cents").notNull(),
	1984	kind: text("kind").notNull(), // one_time \| monthly
	1985	note: text("note"),
	1986	isPublic: boolean("is_public").default(true).notNull(),
	1987	externalRef: text("external_ref"),
	1988	cancelledAt: timestamp("cancelled_at"),
	1989	createdAt: timestamp("created_at").defaultNow().notNull(),
	1990	},
	1991	(table) => [
	1992	index("sponsorships_maintainer").on(
	1993	table.maintainerId,
	1994	table.createdAt
	1995	),
	1996	index("sponsorships_sponsor").on(table.sponsorId, table.createdAt),
	1997	]
	1998	);
	1999
	2000	export type Sponsorship = typeof sponsorships.$inferSelect;
4c8f666Claude	2001
	2002	// Block I8 — Code symbol index for xref navigation.
	2003	export const codeSymbols = pgTable(
	2004	"code_symbols",
	2005	{
	2006	id: uuid("id").primaryKey().defaultRandom(),
	2007	repositoryId: uuid("repository_id")
	2008	.notNull()
	2009	.references(() => repositories.id, { onDelete: "cascade" }),
	2010	commitSha: text("commit_sha").notNull(),
	2011	name: text("name").notNull(),
	2012	kind: text("kind").notNull(), // function \| class \| interface \| type \| const \| variable
	2013	path: text("path").notNull(),
	2014	line: integer("line").notNull(),
	2015	signature: text("signature"),
	2016	createdAt: timestamp("created_at").defaultNow().notNull(),
	2017	},
	2018	(table) => [
	2019	index("code_symbols_repo_name_idx").on(table.repositoryId, table.name),
	2020	index("code_symbols_repo_path_idx").on(table.repositoryId, table.path),
	2021	]
	2022	);
	2023
	2024	export type CodeSymbol = typeof codeSymbols.$inferSelect;
4a0dea1Claude	2025
	2026	// Block I9 — Repository mirroring. One row per mirrored repo + an
	2027	// append-only log of sync attempts.
	2028	export const repoMirrors = pgTable("repo_mirrors", {
	2029	id: uuid("id").primaryKey().defaultRandom(),
	2030	repositoryId: uuid("repository_id")
	2031	.notNull()
	2032	.unique()
	2033	.references(() => repositories.id, { onDelete: "cascade" }),
	2034	upstreamUrl: text("upstream_url").notNull(),
	2035	intervalMinutes: integer("interval_minutes").default(1440).notNull(),
	2036	lastSyncedAt: timestamp("last_synced_at"),
	2037	lastStatus: text("last_status"), // "ok" \| "error"
	2038	lastError: text("last_error"),
	2039	isEnabled: boolean("is_enabled").default(true).notNull(),
	2040	createdAt: timestamp("created_at").defaultNow().notNull(),
	2041	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2042	});
	2043
	2044	export type RepoMirror = typeof repoMirrors.$inferSelect;
	2045
	2046	export const repoMirrorRuns = pgTable(
	2047	"repo_mirror_runs",
	2048	{
	2049	id: uuid("id").primaryKey().defaultRandom(),
	2050	mirrorId: uuid("mirror_id")
	2051	.notNull()
	2052	.references(() => repoMirrors.id, { onDelete: "cascade" }),
	2053	startedAt: timestamp("started_at").defaultNow().notNull(),
	2054	finishedAt: timestamp("finished_at"),
	2055	status: text("status").default("running").notNull(),
	2056	message: text("message"),
	2057	exitCode: integer("exit_code"),
	2058	},
	2059	(table) => [
	2060	index("repo_mirror_runs_mirror_id_idx").on(table.mirrorId, table.startedAt),
	2061	]
	2062	);
	2063
	2064	export type RepoMirrorRun = typeof repoMirrorRuns.$inferSelect;
edf7c36Claude	2065
	2066	// ----------------------------------------------------------------------------
	2067	// Block I10 — Enterprise SSO (OIDC)
	2068	// ----------------------------------------------------------------------------
	2069
	2070	/** Site-wide SSO provider. Singleton row with id = 'default'. */
	2071	export const ssoConfig = pgTable("sso_config", {
	2072	id: text("id").primaryKey(),
	2073	enabled: boolean("enabled").default(false).notNull(),
	2074	providerName: text("provider_name").default("SSO").notNull(),
	2075	issuer: text("issuer"),
	2076	authorizationEndpoint: text("authorization_endpoint"),
	2077	tokenEndpoint: text("token_endpoint"),
	2078	userinfoEndpoint: text("userinfo_endpoint"),
	2079	clientId: text("client_id"),
	2080	clientSecret: text("client_secret"),
	2081	scopes: text("scopes").default("openid profile email").notNull(),
	2082	allowedEmailDomains: text("allowed_email_domains"),
	2083	autoCreateUsers: boolean("auto_create_users").default(true).notNull(),
	2084	createdAt: timestamp("created_at").defaultNow().notNull(),
	2085	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2086	});
	2087
	2088	export type SsoConfig = typeof ssoConfig.$inferSelect;
	2089
	2090	/** Maps a local user to an IdP `sub` claim. */
	2091	export const ssoUserLinks = pgTable(
	2092	"sso_user_links",
	2093	{
	2094	id: uuid("id").primaryKey().defaultRandom(),
	2095	userId: uuid("user_id")
	2096	.notNull()
	2097	.references(() => users.id, { onDelete: "cascade" }),
	2098	subject: text("subject").notNull().unique(),
	2099	emailAtLink: text("email_at_link").notNull(),
	2100	linkedAt: timestamp("linked_at").defaultNow().notNull(),
	2101	},
	2102	(table) => [index("sso_user_links_user_id_idx").on(table.userId)]
	2103	);
	2104
	2105	export type SsoUserLink = typeof ssoUserLinks.$inferSelect;
8098672Claude	2106
	2107	// ----------------------------------------------------------------------------
	2108	// Block J1 — Dependency graph
	2109	// ----------------------------------------------------------------------------
	2110
	2111	/**
	2112	* Last known set of dependencies parsed from manifest files. Each reindex
	2113	* replaces the prior rows for that repo. One row per (ecosystem, name,
	2114	* manifest_path) — same name in multiple manifests is kept.
	2115	*/
	2116	export const repoDependencies = pgTable(
	2117	"repo_dependencies",
	2118	{
	2119	id: uuid("id").primaryKey().defaultRandom(),
	2120	repositoryId: uuid("repository_id")
	2121	.notNull()
	2122	.references(() => repositories.id, { onDelete: "cascade" }),
	2123	ecosystem: text("ecosystem").notNull(),
	2124	name: text("name").notNull(),
	2125	versionSpec: text("version_spec"),
	2126	manifestPath: text("manifest_path").notNull(),
	2127	isDev: boolean("is_dev").default(false).notNull(),
	2128	commitSha: text("commit_sha").notNull(),
	2129	indexedAt: timestamp("indexed_at").defaultNow().notNull(),
	2130	},
	2131	(table) => [
	2132	index("repo_dependencies_repo_id_idx").on(
	2133	table.repositoryId,
	2134	table.ecosystem
	2135	),
	2136	index("repo_dependencies_name_idx").on(table.name),
	2137	]
	2138	);
	2139
	2140	export type RepoDependency = typeof repoDependencies.$inferSelect;
f60ccdeClaude	2141
	2142	// ----------------------------------------------------------------------------
	2143	// Block J2 — Security advisories + alerts
	2144	// ----------------------------------------------------------------------------
	2145
	2146	/** CVE-style package advisories. Populated via seed + admin import. */
	2147	export const securityAdvisories = pgTable(
	2148	"security_advisories",
	2149	{
	2150	id: uuid("id").primaryKey().defaultRandom(),
	2151	ghsaId: text("ghsa_id").unique(),
	2152	cveId: text("cve_id"),
	2153	summary: text("summary").notNull(),
	2154	severity: text("severity").default("moderate").notNull(),
	2155	ecosystem: text("ecosystem").notNull(),
	2156	packageName: text("package_name").notNull(),
	2157	affectedRange: text("affected_range").notNull(),
	2158	fixedVersion: text("fixed_version"),
	2159	referenceUrl: text("reference_url"),
	2160	publishedAt: timestamp("published_at").defaultNow().notNull(),
	2161	},
	2162	(table) => [
	2163	index("security_advisories_pkg_idx").on(
	2164	table.ecosystem,
	2165	table.packageName
	2166	),
	2167	]
	2168	);
	2169
	2170	export type SecurityAdvisory = typeof securityAdvisories.$inferSelect;
	2171
	2172	/** Per-repo match state. One row per (repo, advisory, manifest_path). */
	2173	export const repoAdvisoryAlerts = pgTable(
	2174	"repo_advisory_alerts",
	2175	{
	2176	id: uuid("id").primaryKey().defaultRandom(),
	2177	repositoryId: uuid("repository_id")
	2178	.notNull()
	2179	.references(() => repositories.id, { onDelete: "cascade" }),
	2180	advisoryId: uuid("advisory_id")
	2181	.notNull()
	2182	.references(() => securityAdvisories.id, { onDelete: "cascade" }),
	2183	dependencyName: text("dependency_name").notNull(),
	2184	dependencyVersion: text("dependency_version"),
	2185	manifestPath: text("manifest_path").notNull(),
	2186	status: text("status").default("open").notNull(),
	2187	dismissedReason: text("dismissed_reason"),
	2188	createdAt: timestamp("created_at").defaultNow().notNull(),
	2189	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2190	},
	2191	(table) => [
	2192	index("repo_advisory_alerts_status_idx").on(
	2193	table.repositoryId,
	2194	table.status
	2195	),
	2196	]
	2197	);
	2198
	2199	export type RepoAdvisoryAlert = typeof repoAdvisoryAlerts.$inferSelect;
3951454Claude	2200
	2201	// ----------------------------------------------------------------------------
	2202	// Block J3 — Commit signature verification (GPG + SSH)
	2203	// ----------------------------------------------------------------------------
	2204
	2205	/** Per-user GPG/SSH public keys for commit signing. */
	2206	export const signingKeys = pgTable(
	2207	"signing_keys",
	2208	{
	2209	id: uuid("id").primaryKey().defaultRandom(),
	2210	userId: uuid("user_id")
	2211	.notNull()
	2212	.references(() => users.id, { onDelete: "cascade" }),
	2213	keyType: text("key_type").notNull(), // 'gpg' \| 'ssh'
	2214	title: text("title").notNull(),
	2215	fingerprint: text("fingerprint").notNull(),
	2216	publicKey: text("public_key").notNull(),
	2217	email: text("email"),
	2218	expiresAt: timestamp("expires_at"),
	2219	lastUsedAt: timestamp("last_used_at"),
	2220	createdAt: timestamp("created_at").defaultNow().notNull(),
	2221	},
	2222	(table) => [
	2223	uniqueIndex("signing_keys_fp_unique").on(table.keyType, table.fingerprint),
	2224	index("signing_keys_user_idx").on(table.userId),
	2225	]
	2226	);
	2227
	2228	export type SigningKey = typeof signingKeys.$inferSelect;
	2229
	2230	/**
	2231	* Cached verification result for a (repo, commit) pair. Repopulated on demand;
	2232	* rows are invalidated implicitly by CASCADE when either side is removed.
	2233	*/
	2234	export const commitVerifications = pgTable(
	2235	"commit_verifications",
	2236	{
	2237	id: uuid("id").primaryKey().defaultRandom(),
	2238	repositoryId: uuid("repository_id")
	2239	.notNull()
	2240	.references(() => repositories.id, { onDelete: "cascade" }),
	2241	commitSha: text("commit_sha").notNull(),
	2242	verified: boolean("verified").default(false).notNull(),
	2243	reason: text("reason").notNull(),
	2244	signatureType: text("signature_type"),
	2245	signerKeyId: uuid("signer_key_id").references(() => signingKeys.id, {
	2246	onDelete: "set null",
	2247	}),
	2248	signerUserId: uuid("signer_user_id").references(() => users.id, {
	2249	onDelete: "set null",
	2250	}),
	2251	signerFingerprint: text("signer_fingerprint"),
	2252	verifiedAt: timestamp("verified_at").defaultNow().notNull(),
	2253	},
	2254	(table) => [
	2255	uniqueIndex("commit_verifications_sha_unique").on(
	2256	table.repositoryId,
	2257	table.commitSha
	2258	),
	2259	]
	2260	);
	2261
	2262	export type CommitVerification = typeof commitVerifications.$inferSelect;
7aa8b99Claude	2263
	2264	// ----------------------------------------------------------------------------
	2265	// Block J4 — User following
	2266	// ----------------------------------------------------------------------------
	2267
	2268	/**
	2269	* Directed user→user follow edges. Primary key is the composite
	2270	* (follower_id, following_id) at the SQL level; drizzle sees it as a
	2271	* regular table with a unique index plus a secondary index on the
	2272	* reverse-lookup column.
	2273	*/
	2274	export const userFollows = pgTable(
	2275	"user_follows",
	2276	{
	2277	followerId: uuid("follower_id")
	2278	.notNull()
	2279	.references(() => users.id, { onDelete: "cascade" }),
	2280	followingId: uuid("following_id")
	2281	.notNull()
	2282	.references(() => users.id, { onDelete: "cascade" }),
	2283	createdAt: timestamp("created_at").defaultNow().notNull(),
	2284	},
	2285	(table) => [
	2286	uniqueIndex("user_follows_pair_unique").on(
	2287	table.followerId,
	2288	table.followingId
	2289	),
	2290	index("user_follows_following_idx").on(table.followingId),
	2291	]
	2292	);
	2293
	2294	export type UserFollow = typeof userFollows.$inferSelect;
9ff7128Claude	2295
	2296	// ----------------------------------------------------------------------------
	2297	// Block J6 — Repository rulesets
	2298	// ----------------------------------------------------------------------------
	2299
	2300	/**
	2301	* A ruleset groups N rules under a named policy at enforcement level active /
	2302	* evaluate / disabled. Unique per (repo, name) so a repo can carry multiple
	2303	* overlapping rulesets (e.g. "release branches" vs "everywhere").
	2304	*/
	2305	export const repoRulesets = pgTable(
	2306	"repo_rulesets",
	2307	{
	2308	id: uuid("id").primaryKey().defaultRandom(),
	2309	repositoryId: uuid("repository_id")
	2310	.notNull()
	2311	.references(() => repositories.id, { onDelete: "cascade" }),
	2312	name: text("name").notNull(),
	2313	enforcement: text("enforcement").default("active").notNull(),
	2314	createdBy: uuid("created_by").references(() => users.id, {
	2315	onDelete: "set null",
	2316	}),
	2317	createdAt: timestamp("created_at").defaultNow().notNull(),
	2318	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2319	},
	2320	(table) => [
	2321	index("repo_rulesets_repo_idx").on(table.repositoryId),
	2322	uniqueIndex("repo_rulesets_repo_name_unique").on(
	2323	table.repositoryId,
	2324	table.name
	2325	),
	2326	]
	2327	);
	2328
	2329	export type RepoRuleset = typeof repoRulesets.$inferSelect;
	2330
	2331	/** Individual rule — type tag plus JSON params. */
	2332	export const rulesetRules = pgTable(
	2333	"ruleset_rules",
	2334	{
	2335	id: uuid("id").primaryKey().defaultRandom(),
	2336	rulesetId: uuid("ruleset_id")
	2337	.notNull()
	2338	.references(() => repoRulesets.id, { onDelete: "cascade" }),
	2339	ruleType: text("rule_type").notNull(),
	2340	params: text("params").default("{}").notNull(),
	2341	createdAt: timestamp("created_at").defaultNow().notNull(),
	2342	},
	2343	(table) => [index("ruleset_rules_set_idx").on(table.rulesetId)]
	2344	);
	2345
	2346	export type RulesetRule = typeof rulesetRules.$inferSelect;
0cdfd89Claude	2347
	2348	// ---------------------------------------------------------------------------
	2349	// Block J8 — Commit statuses.
	2350	// ---------------------------------------------------------------------------
	2351
	2352	/**
	2353	* External CI / automation posts per-commit (sha, context) statuses. Upsert
	2354	* semantics keyed on (repository, commit_sha, context). State vocabulary:
	2355	* pending \| success \| failure \| error. Combined rollup logic lives in
	2356	* `src/lib/commit-statuses.ts`.
	2357	*/
	2358	export const commitStatuses = pgTable(
	2359	"commit_statuses",
	2360	{
	2361	id: uuid("id").primaryKey().defaultRandom(),
	2362	repositoryId: uuid("repository_id")
	2363	.notNull()
	2364	.references(() => repositories.id, { onDelete: "cascade" }),
	2365	commitSha: text("commit_sha").notNull(),
	2366	state: text("state").notNull(),
	2367	context: text("context").default("default").notNull(),
	2368	description: text("description"),
	2369	targetUrl: text("target_url"),
	2370	creatorId: uuid("creator_id").references(() => users.id, {
	2371	onDelete: "set null",
	2372	}),
	2373	createdAt: timestamp("created_at").defaultNow().notNull(),
	2374	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2375	},
	2376	(table) => [
	2377	uniqueIndex("commit_statuses_repo_sha_context_unique").on(
	2378	table.repositoryId,
	2379	table.commitSha,
	2380	table.context
	2381	),
	2382	index("commit_statuses_repo_sha_idx").on(
	2383	table.repositoryId,
	2384	table.commitSha
	2385	),
	2386	]
	2387	);
	2388
	2389	export type CommitStatus = typeof commitStatuses.$inferSelect;
23d1a81Claude	2390
	2391	// ---------------------------------------------------------------------------
	2392	// Collaborators — per-repo role grants (read / write / admin).
	2393	// ---------------------------------------------------------------------------
	2394
	2395	/**
	2396	* A user granted access to a repository beyond ownership. Roles are
	2397	* hierarchical — 'admin' implies write, write implies read. `invitedBy` tracks
	2398	* who added them (nullable once that user is deleted); `acceptedAt` is null
	2399	* until the invitee explicitly accepts. Unique per (repo, user) so a given
	2400	* user has at most one role per repo.
	2401	*/
	2402	export const repoCollaborators = pgTable(
	2403	"repo_collaborators",
	2404	{
	2405	id: uuid("id").primaryKey().defaultRandom(),
	2406	repositoryId: uuid("repository_id")
	2407	.notNull()
	2408	.references(() => repositories.id, { onDelete: "cascade" }),
	2409	userId: uuid("user_id")
	2410	.notNull()
	2411	.references(() => users.id, { onDelete: "cascade" }),
	2412	role: text("role", { enum: ["read", "write", "admin"] })
	2413	.notNull()
	2414	.default("read"),
	2415	invitedBy: uuid("invited_by").references(() => users.id, {
	2416	onDelete: "set null",
	2417	}),
	2418	invitedAt: timestamp("invited_at").defaultNow().notNull(),
	2419	acceptedAt: timestamp("accepted_at"),
febd4f0Claude	2420	// sha256(plaintext) of the outstanding invite token. Set when the owner
	2421	// sends an invite, cleared when the invitee accepts. NULL on older rows
	2422	// that were auto-accepted before the email flow existed.
	2423	inviteTokenHash: text("invite_token_hash"),
23d1a81Claude	2424	},
	2425	(table) => [
	2426	uniqueIndex("repo_collaborators_repo_user_uq").on(
	2427	table.repositoryId,
	2428	table.userId
	2429	),
	2430	index("repo_collaborators_repo_idx").on(table.repositoryId),
	2431	index("repo_collaborators_user_idx").on(table.userId),
	2432	]
	2433	);
	2434
	2435	export type RepoCollaborator = typeof repoCollaborators.$inferSelect;
abfa9adClaude	2436
	2437	// ---------------------------------------------------------------------------
	2438	// Workflow engine v2 — Sprint 1 additions (drizzle/0037_workflow_engine_v2.sql)
	2439	//
	2440	// Strictly additive to Block C1. The original workflow tables defined above
	2441	// (workflows / workflow_runs / workflow_jobs / workflow_artifacts) are locked
	2442	// and must not be altered in-place; the four tables below extend the runner
	2443	// with secrets, workflow_dispatch inputs, a content-addressable cache, and a
	2444	// warm-runner worker pool.
	2445	// ---------------------------------------------------------------------------
	2446
	2447	/**
	2448	* Per-repo encrypted secrets exposed to workflow jobs as env vars.
	2449	*
	2450	* `encrypted_value` is base64 of `iv \|\| authTag \|\| ciphertext` produced by
	2451	* AES-256-GCM — the crypto lives in `src/lib/workflow-crypto.ts` (Agent 2);
	2452	* the DB only stores opaque ciphertext. `name` is validated against
	2453	* `[A-Z_][A-Z0-9_]*` at the write-site, not the DB. Unique per (repo, name).
	2454	*/
	2455	export const workflowSecrets = pgTable(
	2456	"workflow_secrets",
	2457	{
	2458	id: uuid("id").primaryKey().defaultRandom(),
	2459	repositoryId: uuid("repository_id")
	2460	.notNull()
	2461	.references(() => repositories.id, { onDelete: "cascade" }),
	2462	name: text("name").notNull(),
	2463	encryptedValue: text("encrypted_value").notNull(),
	2464	createdBy: uuid("created_by").references(() => users.id, {
	2465	onDelete: "set null",
	2466	}),
	2467	createdAt: timestamp("created_at").defaultNow().notNull(),
	2468	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2469	},
	2470	(table) => [
	2471	uniqueIndex("workflow_secrets_repo_name_uq").on(
	2472	table.repositoryId,
	2473	table.name
	2474	),
	2475	index("workflow_secrets_repo_idx").on(table.repositoryId),
	2476	]
	2477	);
	2478
	2479	export type WorkflowSecret = typeof workflowSecrets.$inferSelect;
	2480	export type NewWorkflowSecret = typeof workflowSecrets.$inferInsert;
	2481
	2482	/**
	2483	* Parameter schema for the `workflow_dispatch` trigger. One row per input
	2484	* declared in the workflow YAML. `type` is constrained to the four values
	2485	* GitHub Actions supports; `options` is only meaningful for type='choice'
	2486	* (a JSON array of allowed strings). `default_value` and `description` are
	2487	* optional. Unique per (workflow, name) so two inputs on the same workflow
	2488	* can't share an identifier.
	2489	*/
	2490	export const workflowDispatchInputs = pgTable(
	2491	"workflow_dispatch_inputs",
	2492	{
	2493	id: uuid("id").primaryKey().defaultRandom(),
	2494	workflowId: uuid("workflow_id")
	2495	.notNull()
	2496	.references(() => workflows.id, { onDelete: "cascade" }),
	2497	name: text("name").notNull(),
	2498	type: text("type", {
	2499	enum: ["string", "boolean", "choice", "number"],
	2500	}).notNull(),
	2501	required: boolean("required").default(false).notNull(),
	2502	defaultValue: text("default_value"),
	2503	// JSON array of strings; only populated when type = 'choice'.
	2504	options: jsonb("options"),
	2505	description: text("description"),
	2506	},
	2507	(table) => [
	2508	uniqueIndex("workflow_dispatch_inputs_wf_name_uq").on(
	2509	table.workflowId,
	2510	table.name
	2511	),
	2512	]
	2513	);
	2514
	2515	export type WorkflowDispatchInput =
	2516	typeof workflowDispatchInputs.$inferSelect;
	2517	export type NewWorkflowDispatchInput =
	2518	typeof workflowDispatchInputs.$inferInsert;
	2519
	2520	/**
	2521	* Content-addressable cache backing the `gluecron/cache@v1` action. Rows are
	2522	* keyed by user-chosen `cache_key` within a `scope` — 'repo' for repo-wide
	2523	* entries, 'branch' or 'tag' when isolation is desired (with `scope_ref`
	2524	* holding the branch/tag name). `content_hash` is the sha256 of `content`
	2525	* so jobs can short-circuit redundant writes. `content` is real bytea; the
	2526	* 100MB payload cap is enforced at the write-site, not by the DB. LRU
	2527	* eviction reads (`repository_id`, `last_accessed_at`).
	2528	*/
	2529	export const workflowRunCache = pgTable(
	2530	"workflow_run_cache",
	2531	{
	2532	id: uuid("id").primaryKey().defaultRandom(),
	2533	repositoryId: uuid("repository_id")
	2534	.notNull()
	2535	.references(() => repositories.id, { onDelete: "cascade" }),
	2536	cacheKey: text("cache_key").notNull(),
	2537	// 'repo' \| 'branch' \| 'tag' — constraint enforced at the write-site.
	2538	scope: text("scope").default("repo").notNull(),
	2539	scopeRef: text("scope_ref"),
	2540	contentHash: text("content_hash").notNull(),
	2541	content: bytea("content").notNull(),
	2542	sizeBytes: bigint("size_bytes", { mode: "number" }).notNull(),
	2543	createdAt: timestamp("created_at").defaultNow().notNull(),
	2544	lastAccessedAt: timestamp("last_accessed_at").defaultNow().notNull(),
	2545	},
	2546	(table) => [
	2547	uniqueIndex("workflow_run_cache_repo_key_scope_uq").on(
	2548	table.repositoryId,
	2549	table.cacheKey,
	2550	table.scope,
	2551	table.scopeRef
	2552	),
	2553	index("workflow_run_cache_repo_lru_idx").on(
	2554	table.repositoryId,
	2555	table.lastAccessedAt
	2556	),
	2557	]
	2558	);
	2559
	2560	export type WorkflowRunCache = typeof workflowRunCache.$inferSelect;
	2561	export type NewWorkflowRunCache = typeof workflowRunCache.$inferInsert;
	2562
	2563	/**
	2564	* Warm-runner worker registry. The scheduler reads idle rows to dispatch
	2565	* queued jobs without paying cold-start cost; workers heartbeat here so
	2566	* stale entries (>N seconds since `last_heartbeat_at`) can be reaped.
	2567	* `worker_id` is a stable string (hostname:pid or similar) and is globally
	2568	* unique so a restarted worker naturally replaces its predecessor.
	2569	* `current_run_id` is set when status='busy' and cleared on completion.
	2570	*/
	2571	export const workflowRunnerPool = pgTable(
	2572	"workflow_runner_pool",
	2573	{
	2574	id: uuid("id").primaryKey().defaultRandom(),
	2575	workerId: text("worker_id").notNull().unique(),
	2576	status: text("status", {
	2577	enum: ["idle", "busy", "draining", "dead"],
	2578	}).notNull(),
	2579	currentRunId: uuid("current_run_id").references(() => workflowRuns.id, {
	2580	onDelete: "set null",
	2581	}),
	2582	warmedAt: timestamp("warmed_at").defaultNow().notNull(),
	2583	lastHeartbeatAt: timestamp("last_heartbeat_at").defaultNow().notNull(),
	2584	capacity: integer("capacity").default(1).notNull(),
	2585	},
	2586	(table) => [index("workflow_runner_pool_status_idx").on(table.status)]
	2587	);
	2588
	2589	export type WorkflowRunnerPoolEntry =
	2590	typeof workflowRunnerPool.$inferSelect;
	2591	export type NewWorkflowRunnerPoolEntry =
	2592	typeof workflowRunnerPool.$inferInsert;
e6bad81Claude	2593
	2594
	2595	/**
	2596	* Repair Flywheel — every auto-repair attempt is recorded here so future
	2597	* failures with the same signature can short-circuit straight to the cached
	2598	* patch (Tier 0). After ~5000 entries the cache dominates and AI calls drop.
	2599	* Migration: 0039_repair_flywheel.sql
	2600	*/
	2601	export const repairFlywheel = pgTable(
	2602	"repair_flywheel",
	2603	{
	2604	id: uuid("id").primaryKey().defaultRandom(),
	2605	repositoryId: uuid("repository_id").references(() => repositories.id, {
	2606	onDelete: "cascade",
	2607	}),
	2608	// Fingerprint of the normalised failure text (variables/paths stripped).
	2609	failureSignature: text("failure_signature").notNull(),
	2610	// Original failure text (capped at write-site, ~4KB).
	2611	failureText: text("failure_text").notNull(),
	2612	// Mechanical classification or NULL if AI/human-driven.
	2613	failureClassification: text("failure_classification"),
	2614	// 'cached' \| 'mechanical' \| 'ai-sonnet' \| 'human'
	2615	repairTier: text("repair_tier").notNull(),
	2616	patchSummary: text("patch_summary").notNull(),
	2617	filesChanged: jsonb("files_changed").notNull().default([]),
	2618	commitSha: text("commit_sha"),
	2619	// 'pending' \| 'success' \| 'failed' \| 'reverted'
	2620	outcome: text("outcome").notNull().default("pending"),
	2621	appliedAt: timestamp("applied_at").defaultNow().notNull(),
	2622	outcomeAt: timestamp("outcome_at"),
	2623	parentPatternId: uuid("parent_pattern_id"),
	2624	cacheHitCount: integer("cache_hit_count").default(0).notNull(),
	2625	isPublicPattern: boolean("is_public_pattern").default(false).notNull(),
	2626	createdAt: timestamp("created_at").defaultNow().notNull(),
	2627	},
	2628	(table) => [
	2629	index("repair_flywheel_signature_idx").on(table.failureSignature),
	2630	index("repair_flywheel_repo_idx").on(table.repositoryId),
	2631	index("repair_flywheel_outcome_idx").on(table.outcome),
	2632	index("repair_flywheel_classification_idx").on(table.failureClassification),
	2633	index("repair_flywheel_lookup_idx").on(
	2634	table.repositoryId,
	2635	table.failureSignature,
	2636	table.outcome
	2637	),
	2638	]
	2639	);
	2640
	2641	export type RepairFlywheelEntry = typeof repairFlywheel.$inferSelect;
	2642	export type NewRepairFlywheelEntry = typeof repairFlywheel.$inferInsert;
	2643