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.tsBlame2811 lines · 1 contributor

Normal view Raw

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