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