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.tsBlame2938 lines · 3 contributors

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