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

Normal view Raw

fc1817aClaude	1	import {
	2	pgTable,
	3	text,
	4	timestamp,
	5	uuid,
	6	boolean,
	7	integer,
	8	uniqueIndex,
79136bbClaude	9	index,
	10	serial,
abfa9adClaude	11	bigint,
	12	jsonb,
5ca514aClaude	13	numeric,
abfa9adClaude	14	customType,
fc1817aClaude	15	} from "drizzle-orm/pg-core";
	16
abfa9adClaude	17	// Postgres `bytea` — drizzle-orm doesn't ship a first-class bytea column, so
	18	// we declare one via customType that maps to Buffer on both read and write.
	19	// Used by `workflow_run_cache.content` where we really do need raw bytes
	20	// (unlike `workflow_artifacts.content`, which stayed base64-text for v1).
	21	const bytea = customType<{ data: Buffer; default: false }>({
	22	dataType() {
	23	return "bytea";
	24	},
	25	});
	26
a686079Claude	27	// pgvector `vector(N)` — drizzle-orm has no first-class pgvector column.
	28	// We map it to `number[]` and serialise/deserialise via Postgres's
	29	// canonical text format `[0.1,0.2,...]`. The `default: false` flag tells
	30	// drizzle this column has no default value.
	31	//
	32	// Migration 0057 creates the column with `vector(1024)`. If the migration
	33	// degraded (pgvector unavailable on the host), the column is missing and
	34	// every read/write throws; src/lib/semantic-index.ts catches at the
	35	// boundary and falls back to empty-result behaviour.
	36	const vector = customType<{ data: number[]; driverData: string; default: false }>({
	37	dataType(config: unknown) {
	38	const cfg = config as { dimensions?: number } \| undefined;
	39	return cfg?.dimensions ? `vector(${cfg.dimensions})` : "vector";
	40	},
	41	toDriver(value: number[]): string {
	42	return "[" + value.join(",") + "]";
	43	},
	44	fromDriver(value: string \| number[]): number[] {
	45	if (Array.isArray(value)) return value as number[];
	46	// Canonical format: "[0.1,0.2,0.3]"
	47	const trimmed = String(value).trim();
	48	if (!trimmed) return [];
	49	const inner = trimmed.startsWith("[") && trimmed.endsWith("]")
	50	? trimmed.slice(1, -1)
	51	: trimmed;
	52	if (!inner) return [];
	53	return inner.split(",").map((s) => parseFloat(s));
	54	},
	55	});
	56
fc1817aClaude	57	export const users = pgTable("users", {
	58	id: uuid("id").primaryKey().defaultRandom(),
	59	username: text("username").notNull().unique(),
	60	email: text("email").notNull().unique(),
	61	displayName: text("display_name"),
	62	passwordHash: text("password_hash").notNull(),
	63	avatarUrl: text("avatar_url"),
	64	bio: text("bio"),
24cf2caClaude	65	// Email notification preferences (Block A8). Default on; opt-out via /settings.
	66	notifyEmailOnMention: boolean("notify_email_on_mention").default(true).notNull(),
	67	notifyEmailOnAssign: boolean("notify_email_on_assign").default(true).notNull(),
	68	notifyEmailOnGateFail: boolean("notify_email_on_gate_fail").default(true).notNull(),
08420cdClaude	69	// Block I7 — weekly digest opt-in.
	70	notifyEmailDigestWeekly: boolean("notify_email_digest_weekly").default(false).notNull(),
cb5a796Claude	71	// Drizzle/0066 — Pending comment moderation requests. Defaults ON so
	72	// a new repo owner is alerted as soon as the first comment lands in
	73	// the queue. The in-app notification is always written regardless;
	74	// this controls fan-out (currently the audit/notification surface,
	75	// future email).
	76	notifyEmailOnPendingComment: boolean("notify_email_on_pending_comment")
	77	.default(true)
	78	.notNull(),
08420cdClaude	79	lastDigestSentAt: timestamp("last_digest_sent_at"),
46d6165Claude	80	// Block L1 — Sleep Mode. When enabled, the autopilot sleep-mode-digest
	81	// task delivers a daily "what Claude shipped overnight" report at the
	82	// user-configured UTC hour (0-23, default 9). Reuses lastDigestSentAt
	83	// as the 23h cooldown anchor — the cooldown is shared with the weekly
	84	// digest, so a user cannot receive both on the same day.
	85	sleepModeEnabled: boolean("sleep_mode_enabled").default(false).notNull(),
	86	sleepModeDigestHourUtc: integer("sleep_mode_digest_hour_utc").default(9).notNull(),
534f04aClaude	87	// Block M2 — Web Push per-event preferences. Default on; opt-out via /settings.
	88	notifyPushOnMention: boolean("notify_push_on_mention").default(true).notNull(),
	89	notifyPushOnAssign: boolean("notify_push_on_assign").default(true).notNull(),
	90	notifyPushOnReviewRequest: boolean("notify_push_on_review_request")
	91	.default(true)
	92	.notNull(),
	93	notifyPushOnDeployFailed: boolean("notify_push_on_deploy_failed")
	94	.default(true)
	95	.notNull(),
a4f3e24Claude	96	isAdmin: boolean("is_admin").default(false).notNull(),
c63b860Claude	97	// Block P2 — set when the user clicks the verification link. Soft-gate
	98	// only: registration succeeds regardless; /dashboard surfaces a banner
	99	// until this is non-null.
	100	emailVerifiedAt: timestamp("email_verified_at"),
	101	// Block P5 — Account deletion with 30-day grace period.
	102	// See drizzle/0049_account_deletion.sql.
	103	deletedAt: timestamp("deleted_at"),
	104	deletionScheduledFor: timestamp("deletion_scheduled_for"),
	105	// Block P3 — Terms of Service / Privacy Policy acceptance audit trail.
	106	// Set on /register when the user ticks the accept_terms checkbox.
	107	// termsVersion bumps when Terms change; UI prompts re-acceptance later.
	108	termsAcceptedAt: timestamp("terms_accepted_at"),
	109	termsVersion: text("terms_version"),
cd4f63bTest User	110	// Block Q3 — Anonymous playground accounts. When `isPlayground=true`, the
	111	// account was minted by /play with a synthetic email + random password
	112	// and is hard-deleted by the autopilot `playground-purge` task once
	113	// `playgroundExpiresAt` passes. Real accounts always carry false/null.
	114	// See drizzle/0052_playground_accounts.sql.
	115	isPlayground: boolean("is_playground").default(false).notNull(),
	116	playgroundExpiresAt: timestamp("playground_expires_at"),
ee7e577Claude	117	// Migration 0071 — Personal cross-repo semantic index opt-in. When true,
	118	// /chat (and the /api/v2/me/chat/messages endpoint) may search across
	119	// every repo the user owns OR is an accepted collaborator on. Default
	120	// OFF — privacy-first; the user must explicitly enable it via
	121	// /settings/personal-semantic-toggle. The personal-semantic helpers
	122	// hard-refuse to return any rows while this flag is false.
	123	personalSemanticIndexEnabled: boolean("personal_semantic_index_enabled")
	124	.default(false)
	125	.notNull(),
fc1817aClaude	126	createdAt: timestamp("created_at").defaultNow().notNull(),
	127	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	128	});
	129
06d5ffeClaude	130	export const sessions = pgTable("sessions", {
	131	id: uuid("id").primaryKey().defaultRandom(),
	132	userId: uuid("user_id")
	133	.notNull()
	134	.references(() => users.id, { onDelete: "cascade" }),
	135	token: text("token").notNull().unique(),
	136	expiresAt: timestamp("expires_at").notNull(),
7298a17Claude	137	// B4: true when the user has entered their password but not yet their TOTP
	138	// code. softAuth/requireAuth treat such sessions as anonymous; only
	139	// /login/2fa can consume them. Flips to false on successful 2FA.
	140	requires2fa: boolean("requires_2fa").default(false).notNull(),
06d5ffeClaude	141	createdAt: timestamp("created_at").defaultNow().notNull(),
	142	});
	143
45e31d0Claude	144	// @ts-ignore — self-referential FK on forkedFromId causes circular inference
fc1817aClaude	145	export const repositories = pgTable(
	146	"repositories",
	147	{
	148	id: uuid("id").primaryKey().defaultRandom(),
	149	name: text("name").notNull(),
7437605Claude	150	// ownerId = creator / user-owner. Always set (for attribution + user
	151	// namespace uniqueness). For org-owned repos, also represents "created by".
fc1817aClaude	152	ownerId: uuid("owner_id")
	153	.notNull()
	154	.references(() => users.id),
7437605Claude	155	// Block B2: nullable org owner. When set, the repo lives in the org
	156	// namespace and URL resolution routes `/:orgSlug/:repo` to it.
	157	orgId: uuid("org_id"),
fc1817aClaude	158	description: text("description"),
	159	isPrivate: boolean("is_private").default(false).notNull(),
3ef4c9dClaude	160	isArchived: boolean("is_archived").default(false).notNull(),
71cd5ecClaude	161	isTemplate: boolean("is_template").default(false).notNull(),
fc1817aClaude	162	defaultBranch: text("default_branch").default("main").notNull(),
	163	diskPath: text("disk_path").notNull(),
45e31d0Claude	164	forkedFromId: uuid("forked_from_id"),
fc1817aClaude	165	createdAt: timestamp("created_at").defaultNow().notNull(),
	166	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	167	pushedAt: timestamp("pushed_at"),
	168	starCount: integer("star_count").default(0).notNull(),
	169	forkCount: integer("fork_count").default(0).notNull(),
79136bbClaude	170	issueCount: integer("issue_count").default(0).notNull(),
534f04aClaude	171	// Block M5: autopilot stale-sweep opt-out flags. Default true — the
	172	// 2-stage close (poke at 7d/30d, close at 14d/60d after poke) runs on
	173	// every repo unless an owner disables it via repo-settings.
	174	autoCloseStalePrs: boolean("auto_close_stale_prs").default(true).notNull(),
	175	autoCloseStaleIssues: boolean("auto_close_stale_issues")
	176	.default(true)
	177	.notNull(),
4bbacbeClaude	178	// Migration 0062 — opt-out flag for per-branch preview URLs. Default on;
	179	// owners can disable via repo-settings. When false, post-receive skips
	180	// the enqueuePreviewBuild call entirely.
	181	previewBuildsEnabled: boolean("preview_builds_enabled")
	182	.default(true)
	183	.notNull(),
0c3eee5Claude	184	// Migration 0065 — opt-in flag for the AI test generator autopilot task.
	185	// Default false (off) because writing tests against unreviewed code can
	186	// produce noise; owners must explicitly enable it via repo-settings.
	187	autoGenerateTests: boolean("auto_generate_tests")
	188	.default(false)
	189	.notNull(),
79ed944Claude	190	// Migration 0067 — opt-in flag for auto-provisioning a runnable PR
	191	// sandbox on every PR open. Default false (off) because each sandbox
	192	// burns compute; owners must explicitly enable it via repo-settings.
	193	autoPrSandbox: boolean("auto_pr_sandbox").default(false).notNull(),
9b3a183Claude	194	// Migration 0072 — opt-in flag for hosted-in-browser dev environments
	195	// (cloud Codespaces alternative). Default false (off) because each
	196	// env burns a container until the idle sweep tears it down; owners
	197	// must explicitly enable per-repo via repo-settings.
	198	devEnvsEnabled: boolean("dev_envs_enabled").default(false).notNull(),
fc1817aClaude	199	},
7437605Claude	200	(table) => [
	201	// Partial: uniqueness only in the user namespace (org-owned rows exempt).
	202	// Matches the partial index in migration 0004.
	203	uniqueIndex("repos_owner_name").on(table.ownerId, table.name),
	204	index("repos_org").on(table.orgId),
	205	]
fc1817aClaude	206	);
	207
3ef4c9dClaude	208	/**
	209	* Per-repository gate + auto-repair configuration.
	210	* Every new repo is created with all gates ENABLED by default —
	211	* the "full green ecosystem" default. Owners can manually opt-out per setting.
	212	*/
	213	export const repoSettings = pgTable("repo_settings", {
	214	id: uuid("id").primaryKey().defaultRandom(),
	215	repositoryId: uuid("repository_id")
	216	.notNull()
	217	.unique()
	218	.references(() => repositories.id, { onDelete: "cascade" }),
	219	// Gates
	220	gateTestEnabled: boolean("gate_test_enabled").default(true).notNull(),
	221	aiReviewEnabled: boolean("ai_review_enabled").default(true).notNull(),
	222	secretScanEnabled: boolean("secret_scan_enabled").default(true).notNull(),
	223	securityScanEnabled: boolean("security_scan_enabled").default(true).notNull(),
	224	dependencyScanEnabled: boolean("dependency_scan_enabled").default(true).notNull(),
	225	lintEnabled: boolean("lint_enabled").default(true).notNull(),
	226	typeCheckEnabled: boolean("type_check_enabled").default(true).notNull(),
	227	testEnabled: boolean("test_enabled").default(true).notNull(),
	228	// Auto-repair
	229	autoFixEnabled: boolean("auto_fix_enabled").default(true).notNull(),
	230	autoMergeResolveEnabled: boolean("auto_merge_resolve_enabled").default(true).notNull(),
	231	autoFormatEnabled: boolean("auto_format_enabled").default(true).notNull(),
	232	// AI features
	233	aiCommitMessagesEnabled: boolean("ai_commit_messages_enabled").default(true).notNull(),
	234	aiPrSummaryEnabled: boolean("ai_pr_summary_enabled").default(true).notNull(),
	235	aiChangelogEnabled: boolean("ai_changelog_enabled").default(true).notNull(),
	236	// Deploy
	237	autoDeployEnabled: boolean("auto_deploy_enabled").default(true).notNull(),
	238	deployRequireAllGreen: boolean("deploy_require_all_green").default(true).notNull(),
	239	createdAt: timestamp("created_at").defaultNow().notNull(),
	240	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	241	});
	242
	243	/**
	244	* Branch protection rules — enforced on push and merge.
	245	* Every repo's default branch gets a protection rule on creation.
	246	*/
	247	export const branchProtection = pgTable(
	248	"branch_protection",
	249	{
	250	id: uuid("id").primaryKey().defaultRandom(),
	251	repositoryId: uuid("repository_id")
	252	.notNull()
	253	.references(() => repositories.id, { onDelete: "cascade" }),
	254	pattern: text("pattern").notNull(), // branch name or glob (e.g. "main", "release/*")
	255	requirePullRequest: boolean("require_pull_request").default(true).notNull(),
	256	requireGreenGates: boolean("require_green_gates").default(true).notNull(),
	257	requireAiApproval: boolean("require_ai_approval").default(true).notNull(),
	258	requireHumanReview: boolean("require_human_review").default(false).notNull(),
	259	requiredApprovals: integer("required_approvals").default(0).notNull(),
	260	allowForcePush: boolean("allow_force_push").default(false).notNull(),
	261	allowDeletion: boolean("allow_deletion").default(false).notNull(),
	262	dismissStaleReviews: boolean("dismiss_stale_reviews").default(true).notNull(),
4626e61Claude	263	// K2 — Auto-merge opt-in. When true, the autopilot ticker may auto-merge
	264	// a PR whose base branch matches this rule, provided every gate the
	265	// manual-merge path enforces is green. Default-deny.
	266	enableAutoMerge: boolean("enable_auto_merge").default(false).notNull(),
3ef4c9dClaude	267	createdAt: timestamp("created_at").defaultNow().notNull(),
	268	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	269	},
	270	(table) => [
	271	uniqueIndex("branch_protection_repo_pattern").on(
	272	table.repositoryId,
	273	table.pattern
	274	),
	275	]
	276	);
	277
	278	/**
	279	* Gate run history. Every push + every PR creates gate_runs entries —
	280	* one per configured gate. Serves as the source of truth for "is this green?".
	281	*/
	282	export const gateRuns = pgTable(
	283	"gate_runs",
	284	{
	285	id: uuid("id").primaryKey().defaultRandom(),
	286	repositoryId: uuid("repository_id")
	287	.notNull()
	288	.references(() => repositories.id, { onDelete: "cascade" }),
	289	pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
	290	onDelete: "cascade",
	291	}),
	292	commitSha: text("commit_sha").notNull(),
	293	ref: text("ref").notNull(),
	294	gateName: text("gate_name").notNull(), // e.g. "GateTest", "AI Review", "Secret Scan", "Type Check"
	295	status: text("status").notNull(), // pending, running, passed, failed, skipped, repaired
	296	summary: text("summary"),
	297	details: text("details"), // JSON: per-check output, affected files, etc
	298	repairAttempted: boolean("repair_attempted").default(false).notNull(),
	299	repairSucceeded: boolean("repair_succeeded").default(false).notNull(),
	300	repairCommitSha: text("repair_commit_sha"),
	301	durationMs: integer("duration_ms"),
	302	createdAt: timestamp("created_at").defaultNow().notNull(),
	303	completedAt: timestamp("completed_at"),
	304	},
	305	(table) => [
	306	index("gate_runs_repo_sha").on(table.repositoryId, table.commitSha),
	307	index("gate_runs_pr").on(table.pullRequestId),
	308	index("gate_runs_created").on(table.createdAt),
	309	]
	310	);
	311
	312	/**
	313	* In-app notifications. Powered by the activity feed + explicit mentions.
	314	*/
	315	export const notifications = pgTable(
	316	"notifications",
	317	{
	318	id: uuid("id").primaryKey().defaultRandom(),
	319	userId: uuid("user_id")
	320	.notNull()
	321	.references(() => users.id, { onDelete: "cascade" }),
	322	repositoryId: uuid("repository_id").references(() => repositories.id, {
	323	onDelete: "cascade",
	324	}),
	325	kind: text("kind").notNull(), // mention, review_requested, pr_merged, pr_closed, gate_failed, gate_repaired, ai_review, assigned, security_alert, deploy_success, deploy_failed
	326	title: text("title").notNull(),
	327	body: text("body"),
	328	url: text("url"), // link to the relevant page
	329	readAt: timestamp("read_at"),
	330	createdAt: timestamp("created_at").defaultNow().notNull(),
	331	},
	332	(table) => [
	333	index("notifications_user_unread").on(table.userId, table.readAt),
	334	index("notifications_user_created").on(table.userId, table.createdAt),
	335	]
	336	);
	337
	338	/**
	339	* Releases — named snapshots of a repo at a tag/commit.
	340	* AI-generated changelogs bundled in notes field.
	341	*/
	342	export const releases = pgTable(
	343	"releases",
	344	{
	345	id: uuid("id").primaryKey().defaultRandom(),
	346	repositoryId: uuid("repository_id")
	347	.notNull()
	348	.references(() => repositories.id, { onDelete: "cascade" }),
	349	authorId: uuid("author_id")
	350	.notNull()
	351	.references(() => users.id),
	352	tag: text("tag").notNull(),
	353	name: text("name").notNull(),
	354	body: text("body"), // AI-generated release notes + changelog
	355	targetCommit: text("target_commit").notNull(),
	356	isDraft: boolean("is_draft").default(false).notNull(),
	357	isPrerelease: boolean("is_prerelease").default(false).notNull(),
	358	createdAt: timestamp("created_at").defaultNow().notNull(),
	359	publishedAt: timestamp("published_at"),
	360	},
	361	(table) => [
	362	uniqueIndex("releases_repo_tag").on(table.repositoryId, table.tag),
	363	]
	364	);
	365
	366	/**
	367	* Milestones — group issues + PRs toward a shared goal.
	368	*/
	369	export const milestones = pgTable(
	370	"milestones",
	371	{
	372	id: uuid("id").primaryKey().defaultRandom(),
	373	repositoryId: uuid("repository_id")
	374	.notNull()
	375	.references(() => repositories.id, { onDelete: "cascade" }),
	376	title: text("title").notNull(),
	377	description: text("description"),
	378	state: text("state").notNull().default("open"), // open, closed
	379	dueDate: timestamp("due_date"),
	380	createdAt: timestamp("created_at").defaultNow().notNull(),
	381	closedAt: timestamp("closed_at"),
	382	},
	383	(table) => [index("milestones_repo_state").on(table.repositoryId, table.state)]
	384	);
	385
	386	/**
	387	* Reactions on issues, PRs, and comments. Universal target pointer.
	388	*/
	389	export const reactions = pgTable(
	390	"reactions",
	391	{
	392	id: uuid("id").primaryKey().defaultRandom(),
	393	userId: uuid("user_id")
	394	.notNull()
	395	.references(() => users.id, { onDelete: "cascade" }),
	396	targetType: text("target_type").notNull(), // issue, pr, issue_comment, pr_comment
	397	targetId: uuid("target_id").notNull(),
	398	emoji: text("emoji").notNull(), // thumbs_up, thumbs_down, rocket, heart, eyes, laugh, hooray, confused
	399	createdAt: timestamp("created_at").defaultNow().notNull(),
	400	},
	401	(table) => [
	402	uniqueIndex("reactions_unique").on(
	403	table.userId,
	404	table.targetType,
	405	table.targetId,
	406	table.emoji
	407	),
	408	index("reactions_target").on(table.targetType, table.targetId),
	409	]
	410	);
	411
	412	/**
	413	* PR reviews (formal approve/request-changes).
	414	* Separate from inline comments in pr_comments.
	415	*/
	416	export const prReviews = pgTable(
	417	"pr_reviews",
	418	{
	419	id: uuid("id").primaryKey().defaultRandom(),
	420	pullRequestId: uuid("pull_request_id")
	421	.notNull()
	422	.references(() => pullRequests.id, { onDelete: "cascade" }),
	423	reviewerId: uuid("reviewer_id")
	424	.notNull()
	425	.references(() => users.id),
	426	state: text("state").notNull(), // approved, changes_requested, commented
	427	body: text("body"),
	428	isAi: boolean("is_ai").default(false).notNull(),
	429	createdAt: timestamp("created_at").defaultNow().notNull(),
	430	},
	431	(table) => [index("pr_reviews_pr").on(table.pullRequestId)]
	432	);
	433
	434	/**
	435	* Code owners — who owns which paths (auto-request review on PR).
	436	* Parsed from a CODEOWNERS file at the root of the default branch.
	437	*/
	438	export const codeOwners = pgTable(
	439	"code_owners",
	440	{
	441	id: uuid("id").primaryKey().defaultRandom(),
	442	repositoryId: uuid("repository_id")
	443	.notNull()
	444	.references(() => repositories.id, { onDelete: "cascade" }),
	445	pathPattern: text("path_pattern").notNull(),
	446	ownerUsernames: text("owner_usernames").notNull(), // comma-separated
	447	createdAt: timestamp("created_at").defaultNow().notNull(),
	448	},
	449	(table) => [index("code_owners_repo").on(table.repositoryId)]
	450	);
	451
	452	/**
	453	* Per-repo AI chat sessions — conversational repo assistant.
	454	*/
	455	export const aiChats = pgTable(
	456	"ai_chats",
	457	{
	458	id: uuid("id").primaryKey().defaultRandom(),
	459	userId: uuid("user_id")
	460	.notNull()
	461	.references(() => users.id, { onDelete: "cascade" }),
	462	repositoryId: uuid("repository_id").references(() => repositories.id, {
	463	onDelete: "cascade",
	464	}),
	465	title: text("title"),
	466	messages: text("messages").notNull().default("[]"), // JSON array of {role, content}
	467	createdAt: timestamp("created_at").defaultNow().notNull(),
	468	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	469	},
	470	(table) => [
	471	index("ai_chats_user").on(table.userId),
	472	index("ai_chats_repo").on(table.repositoryId),
	473	]
	474	);
	475
	476	/**
	477	* Audit log — every sensitive action. Who did what, when, from where.
	478	*/
	479	export const auditLog = pgTable(
	480	"audit_log",
	481	{
	482	id: uuid("id").primaryKey().defaultRandom(),
	483	userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
	484	repositoryId: uuid("repository_id").references(() => repositories.id, {
	485	onDelete: "set null",
	486	}),
	487	action: text("action").notNull(), // repo.create, repo.delete, repo.transfer, token.create, token.revoke, merge, force_push, branch_protection.update, deploy, ...
	488	targetType: text("target_type"),
	489	targetId: text("target_id"),
	490	ip: text("ip"),
	491	userAgent: text("user_agent"),
	492	metadata: text("metadata"), // JSON for extra context
	493	createdAt: timestamp("created_at").defaultNow().notNull(),
	494	},
	495	(table) => [
	496	index("audit_log_user").on(table.userId),
	497	index("audit_log_repo").on(table.repositoryId),
	498	index("audit_log_created").on(table.createdAt),
	499	]
	500	);
	501
	502	/**
	503	* Deployments — tracks every deploy to downstream systems (Crontech, etc).
	504	* Each deploy is gated on ALL green gates passing.
	505	*/
	506	export const deployments = pgTable(
	507	"deployments",
	508	{
	509	id: uuid("id").primaryKey().defaultRandom(),
	510	repositoryId: uuid("repository_id")
	511	.notNull()
	512	.references(() => repositories.id, { onDelete: "cascade" }),
	513	environment: text("environment").notNull().default("production"),
	514	commitSha: text("commit_sha").notNull(),
	515	ref: text("ref").notNull(),
a76d984Claude	516	status: text("status").notNull(), // pending, running, success, failed, blocked, waiting_timer
3ef4c9dClaude	517	blockedReason: text("blocked_reason"),
	518	target: text("target"), // e.g. "crontech", "fly.io"
	519	triggeredBy: uuid("triggered_by").references(() => users.id),
a76d984Claude	520	/**
	521	* Set when an approved deploy is held by `environments.wait_timer_minutes`.
	522	* NULL = no wait; non-null = autopilot flips status from "waiting_timer"
	523	* to "pending" once `now() >= ready_after`.
	524	*/
	525	readyAfter: timestamp("ready_after"),
3ef4c9dClaude	526	createdAt: timestamp("created_at").defaultNow().notNull(),
	527	completedAt: timestamp("completed_at"),
	528	},
	529	(table) => [
	530	index("deployments_repo").on(table.repositoryId),
	531	index("deployments_created").on(table.createdAt),
	532	]
	533	);
	534
	535	/**
	536	* Rate-limit buckets — in-memory or persisted counter per IP / token / route.
	537	*/
	538	export const rateLimitBuckets = pgTable(
	539	"rate_limit_buckets",
	540	{
	541	id: uuid("id").primaryKey().defaultRandom(),
	542	bucketKey: text("bucket_key").notNull().unique(), // "ip:1.2.3.4:api" or "token:abc:api"
	543	count: integer("count").default(0).notNull(),
	544	windowStart: timestamp("window_start").defaultNow().notNull(),
	545	expiresAt: timestamp("expires_at").notNull(),
	546	},
	547	(table) => [index("rate_limit_expires").on(table.expiresAt)]
fc1817aClaude	548	);
	549
06d5ffeClaude	550	export const stars = pgTable(
	551	"stars",
	552	{
	553	id: uuid("id").primaryKey().defaultRandom(),
	554	userId: uuid("user_id")
	555	.notNull()
	556	.references(() => users.id, { onDelete: "cascade" }),
	557	repositoryId: uuid("repository_id")
	558	.notNull()
	559	.references(() => repositories.id, { onDelete: "cascade" }),
	560	createdAt: timestamp("created_at").defaultNow().notNull(),
	561	},
79136bbClaude	562	(table) => [
	563	uniqueIndex("stars_user_repo").on(table.userId, table.repositoryId),
	564	]
	565	);
	566
	567	export const issues = pgTable(
	568	"issues",
	569	{
	570	id: uuid("id").primaryKey().defaultRandom(),
	571	number: serial("number"),
	572	repositoryId: uuid("repository_id")
	573	.notNull()
	574	.references(() => repositories.id, { onDelete: "cascade" }),
	575	authorId: uuid("author_id")
	576	.notNull()
	577	.references(() => users.id),
	578	title: text("title").notNull(),
	579	body: text("body"),
	580	state: text("state").notNull().default("open"), // open, closed
	581	createdAt: timestamp("created_at").defaultNow().notNull(),
	582	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	583	closedAt: timestamp("closed_at"),
	584	},
	585	(table) => [
	586	index("issues_repo_state").on(table.repositoryId, table.state),
	587	index("issues_repo_number").on(table.repositoryId, table.number),
	588	]
	589	);
	590
	591	export const issueComments = pgTable(
	592	"issue_comments",
	593	{
	594	id: uuid("id").primaryKey().defaultRandom(),
	595	issueId: uuid("issue_id")
	596	.notNull()
	597	.references(() => issues.id, { onDelete: "cascade" }),
	598	authorId: uuid("author_id")
	599	.notNull()
	600	.references(() => users.id),
	601	body: text("body").notNull(),
cb5a796Claude	602	// Comment moderation (drizzle/0066). 'approved' \| 'pending' \| 'rejected'
	603	// \| 'spam'. Default 'approved' keeps every legacy + collaborator path
	604	// unchanged; the gate in `lib/comment-moderation.ts` only routes new
	605	// non-collaborator comments into 'pending'.
	606	moderationStatus: text("moderation_status").notNull().default("approved"),
	607	moderatedAt: timestamp("moderated_at", { withTimezone: true }),
	608	moderatedByUserId: uuid("moderated_by_user_id").references(() => users.id, {
	609	onDelete: "set null",
	610	}),
79136bbClaude	611	createdAt: timestamp("created_at").defaultNow().notNull(),
	612	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	613	},
	614	(table) => [index("comments_issue").on(table.issueId)]
	615	);
	616
	617	export const labels = pgTable(
	618	"labels",
	619	{
	620	id: uuid("id").primaryKey().defaultRandom(),
	621	repositoryId: uuid("repository_id")
	622	.notNull()
	623	.references(() => repositories.id, { onDelete: "cascade" }),
	624	name: text("name").notNull(),
	625	color: text("color").notNull().default("#8b949e"),
	626	description: text("description"),
	627	},
	628	(table) => [
	629	uniqueIndex("labels_repo_name").on(table.repositoryId, table.name),
	630	]
	631	);
	632
	633	export const issueLabels = pgTable(
	634	"issue_labels",
	635	{
	636	id: uuid("id").primaryKey().defaultRandom(),
	637	issueId: uuid("issue_id")
	638	.notNull()
	639	.references(() => issues.id, { onDelete: "cascade" }),
	640	labelId: uuid("label_id")
	641	.notNull()
	642	.references(() => labels.id, { onDelete: "cascade" }),
	643	},
	644	(table) => [
	645	uniqueIndex("issue_labels_unique").on(table.issueId, table.labelId),
	646	]
06d5ffeClaude	647	);
	648
0074234Claude	649	export const pullRequests = pgTable(
	650	"pull_requests",
	651	{
	652	id: uuid("id").primaryKey().defaultRandom(),
	653	number: serial("number"),
	654	repositoryId: uuid("repository_id")
	655	.notNull()
	656	.references(() => repositories.id, { onDelete: "cascade" }),
	657	authorId: uuid("author_id")
	658	.notNull()
	659	.references(() => users.id),
	660	title: text("title").notNull(),
	661	body: text("body"),
	662	state: text("state").notNull().default("open"), // open, closed, merged
	663	baseBranch: text("base_branch").notNull(),
	664	headBranch: text("head_branch").notNull(),
3ef4c9dClaude	665	isDraft: boolean("is_draft").default(false).notNull(),
	666	mergeStrategy: text("merge_strategy").default("merge").notNull(), // merge, squash, rebase
	667	milestoneId: uuid("milestone_id"),
0074234Claude	668	mergedAt: timestamp("merged_at"),
	669	mergedBy: uuid("merged_by").references(() => users.id),
	670	createdAt: timestamp("created_at").defaultNow().notNull(),
	671	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	672	closedAt: timestamp("closed_at"),
	673	},
	674	(table) => [
	675	index("prs_repo_state").on(table.repositoryId, table.state),
	676	index("prs_repo_number").on(table.repositoryId, table.number),
	677	]
	678	);
	679
	680	export const prComments = pgTable(
	681	"pr_comments",
	682	{
	683	id: uuid("id").primaryKey().defaultRandom(),
	684	pullRequestId: uuid("pull_request_id")
	685	.notNull()
	686	.references(() => pullRequests.id, { onDelete: "cascade" }),
	687	authorId: uuid("author_id")
	688	.notNull()
	689	.references(() => users.id),
	690	body: text("body").notNull(),
	691	isAiReview: boolean("is_ai_review").default(false).notNull(),
	692	filePath: text("file_path"),
	693	lineNumber: integer("line_number"),
cb5a796Claude	694	// Comment moderation (drizzle/0066). See `issueComments.moderationStatus`.
	695	moderationStatus: text("moderation_status").notNull().default("approved"),
	696	moderatedAt: timestamp("moderated_at", { withTimezone: true }),
	697	moderatedByUserId: uuid("moderated_by_user_id").references(() => users.id, {
	698	onDelete: "set null",
	699	}),
0074234Claude	700	createdAt: timestamp("created_at").defaultNow().notNull(),
	701	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	702	},
	703	(table) => [index("pr_comments_pr").on(table.pullRequestId)]
	704	);
	705
cb5a796Claude	706	/**
	707	* Comment moderation (drizzle/0066) — per-repo allow/deny list for
	708	* commenters. A 'trusted' row makes `shouldRequireApproval` short-circuit
	709	* to false; a 'banned' row makes it short-circuit to "auto-reject without
	710	* notifying the owner" so a single spam decision sticks.
	711	*
	712	* Indexed by (repository_id, commenter_user_id) for the per-comment gate
	713	* lookup and by (repository_id, status) for the owner's trust-list page.
	714	*/
	715	export const repoCommenterTrust = pgTable(
	716	"repo_commenter_trust",
	717	{
	718	id: uuid("id").primaryKey().defaultRandom(),
	719	repositoryId: uuid("repository_id")
	720	.notNull()
	721	.references(() => repositories.id, { onDelete: "cascade" }),
	722	commenterUserId: uuid("commenter_user_id")
	723	.notNull()
	724	.references(() => users.id, { onDelete: "cascade" }),
	725	status: text("status").notNull(), // 'trusted' \| 'banned'
	726	grantedByUserId: uuid("granted_by_user_id").references(() => users.id, {
	727	onDelete: "set null",
	728	}),
	729	grantedAt: timestamp("granted_at", { withTimezone: true })
	730	.defaultNow()
	731	.notNull(),
	732	},
	733	(table) => [
	734	uniqueIndex("repo_commenter_trust_unique").on(
	735	table.repositoryId,
	736	table.commenterUserId
	737	),
	738	index("repo_commenter_trust_repo_status").on(
	739	table.repositoryId,
	740	table.status
	741	),
	742	]
	743	);
	744
0074234Claude	745	export const activityFeed = pgTable(
	746	"activity_feed",
	747	{
	748	id: uuid("id").primaryKey().defaultRandom(),
	749	repositoryId: uuid("repository_id")
	750	.notNull()
	751	.references(() => repositories.id, { onDelete: "cascade" }),
	752	userId: uuid("user_id").references(() => users.id),
	753	action: text("action").notNull(), // push, issue_open, issue_close, pr_open, pr_merge, star, comment
	754	targetType: text("target_type"), // issue, pr, commit
	755	targetId: text("target_id"),
	756	metadata: text("metadata"), // JSON string for extra data
	757	createdAt: timestamp("created_at").defaultNow().notNull(),
	758	},
	759	(table) => [
	760	index("activity_repo").on(table.repositoryId),
	761	index("activity_user").on(table.userId),
	762	]
	763	);
	764
c81ab7aClaude	765	export const webhooks = pgTable(
	766	"webhooks",
	767	{
	768	id: uuid("id").primaryKey().defaultRandom(),
	769	repositoryId: uuid("repository_id")
	770	.notNull()
	771	.references(() => repositories.id, { onDelete: "cascade" }),
	772	url: text("url").notNull(),
	773	secret: text("secret"),
	774	events: text("events").notNull().default("push"), // comma-separated: push,issue,pr
	775	isActive: boolean("is_active").default(true).notNull(),
	776	lastDeliveredAt: timestamp("last_delivered_at"),
	777	lastStatus: integer("last_status"),
	778	createdAt: timestamp("created_at").defaultNow().notNull(),
	779	},
	780	(table) => [index("webhooks_repo").on(table.repositoryId)]
	781	);
	782
8405c43Claude	783	// Reliable webhook delivery (migration 0056). One row per (hook, event)
	784	// emission. Worker in src/lib/webhook-delivery.ts polls pending rows whose
	785	// next_attempt_at <= now() and retries with exponential backoff.
	786	export const webhookDeliveries = pgTable(
	787	"webhook_deliveries",
	788	{
	789	id: uuid("id").primaryKey().defaultRandom(),
	790	webhookId: uuid("webhook_id")
	791	.notNull()
	792	.references(() => webhooks.id, { onDelete: "cascade" }),
	793	event: text("event").notNull(),
	794	payload: text("payload").notNull(),
	795	signature: text("signature").notNull(),
	796	attemptCount: integer("attempt_count").default(0).notNull(),
	797	nextAttemptAt: timestamp("next_attempt_at", { withTimezone: true }),
	798	status: text("status").default("pending").notNull(), // pending \| succeeded \| failed \| dead
	799	lastStatusCode: integer("last_status_code"),
	800	lastError: text("last_error"),
	801	lastAttemptedAt: timestamp("last_attempted_at", { withTimezone: true }),
	802	succeededAt: timestamp("succeeded_at", { withTimezone: true }),
	803	createdAt: timestamp("created_at", { withTimezone: true })
	804	.defaultNow()
	805	.notNull(),
	806	},
	807	(table) => [
	808	index("idx_webhook_deliveries_next_attempt").on(table.nextAttemptAt),
	809	index("idx_webhook_deliveries_webhook_id").on(
	810	table.webhookId,
	811	table.createdAt
	812	),
	813	]
	814	);
	815
c81ab7aClaude	816	export const apiTokens = pgTable("api_tokens", {
	817	id: uuid("id").primaryKey().defaultRandom(),
	818	userId: uuid("user_id")
	819	.notNull()
	820	.references(() => users.id, { onDelete: "cascade" }),
	821	name: text("name").notNull(),
	822	tokenHash: text("token_hash").notNull(),
	823	tokenPrefix: text("token_prefix").notNull(), // first 8 chars for display
	824	scopes: text("scopes").notNull().default("repo"), // comma-separated
	825	lastUsedAt: timestamp("last_used_at"),
	826	expiresAt: timestamp("expires_at"),
	827	createdAt: timestamp("created_at").defaultNow().notNull(),
	828	});
	829
	830	export const repoTopics = pgTable(
	831	"repo_topics",
	832	{
	833	id: uuid("id").primaryKey().defaultRandom(),
	834	repositoryId: uuid("repository_id")
	835	.notNull()
	836	.references(() => repositories.id, { onDelete: "cascade" }),
	837	topic: text("topic").notNull(),
	838	},
	839	(table) => [
	840	uniqueIndex("repo_topics_unique").on(table.repositoryId, table.topic),
	841	index("topics_name").on(table.topic),
	842	]
	843	);
	844
fc1817aClaude	845	export const sshKeys = pgTable("ssh_keys", {
	846	id: uuid("id").primaryKey().defaultRandom(),
	847	userId: uuid("user_id")
	848	.notNull()
06d5ffeClaude	849	.references(() => users.id, { onDelete: "cascade" }),
fc1817aClaude	850	title: text("title").notNull(),
	851	fingerprint: text("fingerprint").notNull(),
	852	publicKey: text("public_key").notNull(),
	853	lastUsedAt: timestamp("last_used_at"),
	854	createdAt: timestamp("created_at").defaultNow().notNull(),
	855	});
	856
534f04aClaude	857	// Block M2 — Web Push subscriptions. One row per (user, endpoint).
	858	// Endpoint is the browser-issued push URL; p256dh + auth are the W3C
	859	// payload-encryption keys (base64url). Stale endpoints (HTTP 410) are
	860	// deleted lazily on next `sendPushToUser` pass.
	861	export const pushSubscriptions = pgTable(
	862	"push_subscriptions",
	863	{
	864	id: uuid("id").primaryKey().defaultRandom(),
	865	userId: uuid("user_id")
	866	.notNull()
	867	.references(() => users.id, { onDelete: "cascade" }),
	868	endpoint: text("endpoint").notNull(),
	869	p256dh: text("p256dh").notNull(),
	870	auth: text("auth").notNull(),
	871	userAgent: text("user_agent"),
	872	createdAt: timestamp("created_at").defaultNow().notNull(),
	873	lastUsedAt: timestamp("last_used_at"),
	874	},
	875	(table) => [
	876	uniqueIndex("push_subscriptions_user_endpoint").on(
	877	table.userId,
	878	table.endpoint
	879	),
	880	index("idx_push_subscriptions_user").on(table.userId),
	881	]
	882	);
	883
fc1817aClaude	884	export type User = typeof users.$inferSelect;
	885	export type NewUser = typeof users.$inferInsert;
	886	export type Repository = typeof repositories.$inferSelect;
	887	export type NewRepository = typeof repositories.$inferInsert;
06d5ffeClaude	888	export type Session = typeof sessions.$inferSelect;
	889	export type Star = typeof stars.$inferSelect;
	890	export type SshKey = typeof sshKeys.$inferSelect;
534f04aClaude	891	export type PushSubscription = typeof pushSubscriptions.$inferSelect;
	892	export type NewPushSubscription = typeof pushSubscriptions.$inferInsert;
79136bbClaude	893	export type Issue = typeof issues.$inferSelect;
	894	export type IssueComment = typeof issueComments.$inferSelect;
	895	export type Label = typeof labels.$inferSelect;
0074234Claude	896	export type PullRequest = typeof pullRequests.$inferSelect;
	897	export type PrComment = typeof prComments.$inferSelect;
cb5a796Claude	898	export type RepoCommenterTrust = typeof repoCommenterTrust.$inferSelect;
	899	export type NewRepoCommenterTrust = typeof repoCommenterTrust.$inferInsert;
0074234Claude	900	export type ActivityEntry = typeof activityFeed.$inferSelect;
c81ab7aClaude	901	export type Webhook = typeof webhooks.$inferSelect;
	902	export type ApiToken = typeof apiTokens.$inferSelect;
	903	export type RepoTopic = typeof repoTopics.$inferSelect;
3ef4c9dClaude	904	export type RepoSettings = typeof repoSettings.$inferSelect;
	905	export type BranchProtection = typeof branchProtection.$inferSelect;
	906	export type GateRun = typeof gateRuns.$inferSelect;
	907	export type Notification = typeof notifications.$inferSelect;
	908	export type Release = typeof releases.$inferSelect;
	909	export type Milestone = typeof milestones.$inferSelect;
	910	export type Reaction = typeof reactions.$inferSelect;
	911	export type PrReview = typeof prReviews.$inferSelect;
	912	export type CodeOwner = typeof codeOwners.$inferSelect;
	913	export type AiChat = typeof aiChats.$inferSelect;
	914	export type AuditLogEntry = typeof auditLog.$inferSelect;
	915	export type Deployment = typeof deployments.$inferSelect;
24cf2caClaude	916
	917	/**
	918	* Saved replies — per-user canned responses, insertable into any
	919	* issue / PR comment textarea. Shortcut name must be unique per user.
	920	*/
	921	export const savedReplies = pgTable(
	922	"saved_replies",
	923	{
	924	id: uuid("id").primaryKey().defaultRandom(),
	925	userId: uuid("user_id")
	926	.notNull()
	927	.references(() => users.id, { onDelete: "cascade" }),
	928	shortcut: text("shortcut").notNull(),
	929	body: text("body").notNull(),
	930	createdAt: timestamp("created_at").defaultNow().notNull(),
	931	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	932	},
	933	(table) => [
	934	uniqueIndex("saved_replies_user_shortcut").on(table.userId, table.shortcut),
	935	]
	936	);
	937
	938	export type SavedReply = typeof savedReplies.$inferSelect;
5cc5d95Claude	939
	940	/**
	941	* Organizations (Block B1) — multi-user namespaces. Distinct from `users`.
	942	* An org has members (with org-level roles) and may contain teams.
	943	* Repos can be owned by an org via `repositories.orgId` (added in Block B2).
	944	*
	945	* Slug is globally unique against itself; collision with a username is
	946	* checked at create time in the route handler (no DB-level cross-table
	947	* uniqueness in Postgres).
	948	*/
	949	export const organizations = pgTable("organizations", {
	950	id: uuid("id").primaryKey().defaultRandom(),
	951	slug: text("slug").notNull().unique(),
	952	name: text("name").notNull(),
	953	description: text("description"),
	954	avatarUrl: text("avatar_url"),
	955	billingEmail: text("billing_email"),
	956	createdById: uuid("created_by_id")
	957	.notNull()
	958	.references(() => users.id, { onDelete: "restrict" }),
	959	createdAt: timestamp("created_at").defaultNow().notNull(),
	960	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	961	});
	962
	963	/**
	964	* Org membership. Roles: owner (full control, billing), admin (manage
	965	* members + teams + repos), member (default; can be added to teams).
	966	*/
	967	export const orgMembers = pgTable(
	968	"org_members",
	969	{
	970	id: uuid("id").primaryKey().defaultRandom(),
	971	orgId: uuid("org_id")
	972	.notNull()
	973	.references(() => organizations.id, { onDelete: "cascade" }),
	974	userId: uuid("user_id")
	975	.notNull()
	976	.references(() => users.id, { onDelete: "cascade" }),
	977	role: text("role").notNull().default("member"), // owner \| admin \| member
	978	createdAt: timestamp("created_at").defaultNow().notNull(),
	979	},
	980	(table) => [
	981	uniqueIndex("org_members_unique").on(table.orgId, table.userId),
	982	index("org_members_user").on(table.userId),
	983	]
	984	);
	985
	986	/**
	987	* Teams within an org. Slug is unique within an org.
	988	* `parentTeamId` allows nesting (GitHub-style child teams). Optional.
	989	*/
	990	export const teams = pgTable(
	991	"teams",
	992	{
	993	id: uuid("id").primaryKey().defaultRandom(),
	994	orgId: uuid("org_id")
	995	.notNull()
	996	.references(() => organizations.id, { onDelete: "cascade" }),
	997	slug: text("slug").notNull(),
	998	name: text("name").notNull(),
	999	description: text("description"),
	1000	parentTeamId: uuid("parent_team_id"),
	1001	createdAt: timestamp("created_at").defaultNow().notNull(),
	1002	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1003	},
	1004	(table) => [uniqueIndex("teams_org_slug").on(table.orgId, table.slug)]
	1005	);
	1006
	1007	/**
	1008	* Team membership. Roles: maintainer (can edit team), member (default).
	1009	* A user can belong to many teams; team membership requires org membership
	1010	* but that invariant is enforced at the route layer, not the DB layer.
	1011	*/
	1012	export const teamMembers = pgTable(
	1013	"team_members",
	1014	{
	1015	id: uuid("id").primaryKey().defaultRandom(),
	1016	teamId: uuid("team_id")
	1017	.notNull()
	1018	.references(() => teams.id, { onDelete: "cascade" }),
	1019	userId: uuid("user_id")
	1020	.notNull()
	1021	.references(() => users.id, { onDelete: "cascade" }),
	1022	role: text("role").notNull().default("member"), // maintainer \| member
	1023	createdAt: timestamp("created_at").defaultNow().notNull(),
	1024	},
	1025	(table) => [
	1026	uniqueIndex("team_members_unique").on(table.teamId, table.userId),
	1027	index("team_members_user").on(table.userId),
	1028	]
	1029	);
	1030
	1031	export type Organization = typeof organizations.$inferSelect;
	1032	export type OrgMember = typeof orgMembers.$inferSelect;
	1033	export type Team = typeof teams.$inferSelect;
	1034	export type TeamMember = typeof teamMembers.$inferSelect;
	1035	export type OrgRole = "owner" \| "admin" \| "member";
	1036	export type TeamRole = "maintainer" \| "member";
7298a17Claude	1037
	1038	/**
	1039	* 2FA / TOTP (Block B4).
	1040	*
	1041	* Secret is stored in plain Base32 for now — the DB has row-level-secure
	1042	* access and the app boundary is the only code that reads it. A follow-up
	1043	* (B4.1) will wrap it with AES-GCM at rest once we standardise the KEK.
	1044	*
	1045	* `enabledAt` is set only after the user has successfully entered their
	1046	* first code (confirming the authenticator was set up correctly). Rows with
	1047	* `enabledAt = NULL` represent pending enrolment.
	1048	*/
	1049	export const userTotp = pgTable("user_totp", {
	1050	userId: uuid("user_id")
	1051	.primaryKey()
	1052	.references(() => users.id, { onDelete: "cascade" }),
	1053	secret: text("secret").notNull(),
	1054	enabledAt: timestamp("enabled_at"),
	1055	lastUsedAt: timestamp("last_used_at"),
	1056	createdAt: timestamp("created_at").defaultNow().notNull(),
	1057	});
	1058
	1059	/**
	1060	* Recovery codes — single-use fallback when the authenticator is lost.
	1061	* Stored as SHA-256 hashes; used rows are marked with `usedAt` rather than
	1062	* deleted so the audit log keeps the full history.
	1063	*/
	1064	export const userRecoveryCodes = pgTable(
	1065	"user_recovery_codes",
	1066	{
	1067	id: uuid("id").primaryKey().defaultRandom(),
	1068	userId: uuid("user_id")
	1069	.notNull()
	1070	.references(() => users.id, { onDelete: "cascade" }),
	1071	codeHash: text("code_hash").notNull(),
	1072	usedAt: timestamp("used_at"),
	1073	createdAt: timestamp("created_at").defaultNow().notNull(),
	1074	},
	1075	(table) => [
	1076	index("recovery_codes_user").on(table.userId),
	1077	uniqueIndex("recovery_codes_user_hash").on(table.userId, table.codeHash),
	1078	]
	1079	);
	1080
	1081	export type UserTotp = typeof userTotp.$inferSelect;
	1082	export type UserRecoveryCode = typeof userRecoveryCodes.$inferSelect;
2df1f8cClaude	1083
	1084	/**
	1085	* WebAuthn passkeys (Block B5).
	1086	*
	1087	* Each row is one registered authenticator. The `credentialId` is the
	1088	* globally-unique identifier the browser returns; `publicKey` is the
	1089	* COSE-encoded public key we use to verify signatures. `counter` tracks
	1090	* the authenticator's signature counter for replay-protection.
	1091	*
	1092	* `transports` is a JSON array (stored as text) of the
	1093	* AuthenticatorTransport values ("usb" \| "nfc" \| "ble" \| "internal" \| "hybrid").
	1094	*/
	1095	export const userPasskeys = pgTable(
	1096	"user_passkeys",
	1097	{
	1098	id: uuid("id").primaryKey().defaultRandom(),
	1099	userId: uuid("user_id")
	1100	.notNull()
	1101	.references(() => users.id, { onDelete: "cascade" }),
	1102	credentialId: text("credential_id").notNull().unique(),
	1103	publicKey: text("public_key").notNull(), // base64url of COSE key
	1104	counter: integer("counter").default(0).notNull(),
	1105	transports: text("transports"), // JSON array string
	1106	name: text("name").notNull().default("Passkey"),
	1107	lastUsedAt: timestamp("last_used_at"),
	1108	createdAt: timestamp("created_at").defaultNow().notNull(),
	1109	},
	1110	(table) => [index("passkeys_user").on(table.userId)]
	1111	);
	1112
	1113	/**
	1114	* Short-lived WebAuthn challenges. A row is written when we issue options
	1115	* (registration or authentication) and deleted after the verify step or when
	1116	* it expires (5 min). Keeping them in the DB lets us verify without sticky
	1117	* sessions or client-side state.
	1118	*/
	1119	export const webauthnChallenges = pgTable(
	1120	"webauthn_challenges",
	1121	{
	1122	id: uuid("id").primaryKey().defaultRandom(),
	1123	userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
	1124	// For passwordless login we don't know the user yet, so userId is nullable
	1125	// and we bind the challenge to a short-lived cookie token instead.
	1126	sessionKey: text("session_key").notNull().unique(),
	1127	challenge: text("challenge").notNull(),
	1128	kind: text("kind").notNull(), // "register" \| "authenticate"
	1129	expiresAt: timestamp("expires_at").notNull(),
	1130	createdAt: timestamp("created_at").defaultNow().notNull(),
	1131	},
	1132	(table) => [index("webauthn_challenges_expires").on(table.expiresAt)]
	1133	);
	1134
	1135	export type UserPasskey = typeof userPasskeys.$inferSelect;
	1136	export type WebauthnChallenge = typeof webauthnChallenges.$inferSelect;
bfdb5e7Claude	1137
	1138	/**
	1139	* OAuth 2.0 provider (Block B6).
	1140	*
	1141	* `oauthApps` is a third-party app registered by a developer. Each app has
	1142	* a public `client_id`, a hashed `client_secret`, and one or more allowed
	1143	* `redirect_uris` (newline-separated). The plaintext secret is shown to the
	1144	* developer exactly once at creation and cannot be recovered; they can
	1145	* rotate it instead.
	1146	*
	1147	* `oauthAuthorizations` is a short-lived authorization code issued after
	1148	* the user consents at /oauth/authorize. Single-use: `usedAt` is set on
	1149	* redemption so a replay after-the-fact fails.
	1150	*
	1151	* `oauthAccessTokens` is a long-lived bearer token plus an optional
	1152	* refresh token. Both are stored as SHA-256 hashes; the plaintext values
	1153	* are only returned to the client once in the /oauth/token response.
	1154	*/
	1155	export const oauthApps = pgTable(
	1156	"oauth_apps",
	1157	{
	1158	id: uuid("id").primaryKey().defaultRandom(),
	1159	ownerId: uuid("owner_id")
	1160	.notNull()
	1161	.references(() => users.id, { onDelete: "cascade" }),
	1162	name: text("name").notNull(),
	1163	clientId: text("client_id").notNull().unique(),
	1164	clientSecretHash: text("client_secret_hash").notNull(),
	1165	clientSecretPrefix: text("client_secret_prefix").notNull(), // first 8 chars for display
	1166	/** Newline-separated list of allowed redirect URIs. */
	1167	redirectUris: text("redirect_uris").notNull(),
	1168	homepageUrl: text("homepage_url"),
	1169	description: text("description"),
	1170	/**
	1171	* If `true`, the app must present its client_secret at /oauth/token.
	1172	* Public SPA/mobile apps should set this to `false` and use PKCE.
	1173	*/
	1174	confidential: boolean("confidential").default(true).notNull(),
	1175	revokedAt: timestamp("revoked_at"),
	1176	createdAt: timestamp("created_at").defaultNow().notNull(),
	1177	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1178	},
	1179	(table) => [index("oauth_apps_owner").on(table.ownerId)]
	1180	);
	1181
	1182	export const oauthAuthorizations = pgTable(
	1183	"oauth_authorizations",
	1184	{
	1185	id: uuid("id").primaryKey().defaultRandom(),
	1186	appId: uuid("app_id")
	1187	.notNull()
	1188	.references(() => oauthApps.id, { onDelete: "cascade" }),
	1189	userId: uuid("user_id")
	1190	.notNull()
	1191	.references(() => users.id, { onDelete: "cascade" }),
	1192	codeHash: text("code_hash").notNull().unique(),
	1193	redirectUri: text("redirect_uri").notNull(),
	1194	scopes: text("scopes").notNull().default(""),
	1195	codeChallenge: text("code_challenge"),
	1196	codeChallengeMethod: text("code_challenge_method"), // "S256" \| "plain"
	1197	expiresAt: timestamp("expires_at").notNull(),
	1198	usedAt: timestamp("used_at"),
	1199	createdAt: timestamp("created_at").defaultNow().notNull(),
	1200	},
	1201	(table) => [index("oauth_authorizations_expires").on(table.expiresAt)]
	1202	);
	1203
	1204	export const oauthAccessTokens = pgTable(
	1205	"oauth_access_tokens",
	1206	{
	1207	id: uuid("id").primaryKey().defaultRandom(),
	1208	appId: uuid("app_id")
	1209	.notNull()
	1210	.references(() => oauthApps.id, { onDelete: "cascade" }),
	1211	userId: uuid("user_id")
	1212	.notNull()
	1213	.references(() => users.id, { onDelete: "cascade" }),
	1214	accessTokenHash: text("access_token_hash").notNull().unique(),
	1215	refreshTokenHash: text("refresh_token_hash").unique(),
	1216	scopes: text("scopes").notNull().default(""),
	1217	expiresAt: timestamp("expires_at").notNull(),
	1218	refreshExpiresAt: timestamp("refresh_expires_at"),
	1219	revokedAt: timestamp("revoked_at"),
	1220	lastUsedAt: timestamp("last_used_at"),
	1221	createdAt: timestamp("created_at").defaultNow().notNull(),
	1222	},
	1223	(table) => [
	1224	index("oauth_access_tokens_user").on(table.userId),
	1225	index("oauth_access_tokens_app").on(table.appId),
	1226	index("oauth_access_tokens_expires").on(table.expiresAt),
	1227	]
	1228	);
	1229
	1230	export type OauthApp = typeof oauthApps.$inferSelect;
	1231	export type OauthAuthorization = typeof oauthAuthorizations.$inferSelect;
	1232	export type OauthAccessToken = typeof oauthAccessTokens.$inferSelect;
eafe8c6Claude	1233
	1234	/**
	1235	* Actions-equivalent workflow runner (Block C1).
	1236	*
	1237	* `workflows` rows are the YAML files discovered at `.gluecron/workflows/*.yml`
	1238	* on the repo's default branch. `parsed` is the normalised JSON form used by
	1239	* the runner so we don't re-parse on every trigger.
	1240	*
	1241	* `workflow_runs` is one execution: one row per trigger event. Status
	1242	* progression: queued → running → success\|failure\|cancelled. `conclusion`
	1243	* stays null until `status` is terminal.
	1244	*
	1245	* `workflow_jobs` is a single job within a run — each has its own steps
	1246	* array and concatenated logs. We keep logs inline for v1 (no streaming)
	1247	* to avoid a fifth table; they're truncated at the runner.
	1248	*
	1249	* `workflow_artifacts` persist files a job uploaded. `content` is a bytea;
	1250	* we'll move this to object storage once we hit size limits.
	1251	*/
	1252	export const workflows = pgTable(
	1253	"workflows",
	1254	{
	1255	id: uuid("id").primaryKey().defaultRandom(),
	1256	repositoryId: uuid("repository_id")
	1257	.notNull()
	1258	.references(() => repositories.id, { onDelete: "cascade" }),
	1259	name: text("name").notNull(),
	1260	path: text("path").notNull(), // e.g. ".gluecron/workflows/ci.yml"
	1261	yaml: text("yaml").notNull(),
	1262	parsed: text("parsed").notNull(), // JSON string
	1263	onEvents: text("on_events").notNull().default("[]"), // JSON array of event names
	1264	disabled: boolean("disabled").default(false).notNull(),
	1265	createdAt: timestamp("created_at").defaultNow().notNull(),
	1266	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1267	},
	1268	(table) => [
	1269	index("workflows_repo").on(table.repositoryId),
	1270	uniqueIndex("workflows_repo_path").on(table.repositoryId, table.path),
	1271	]
	1272	);
	1273
	1274	export const workflowRuns = pgTable(
	1275	"workflow_runs",
	1276	{
	1277	id: uuid("id").primaryKey().defaultRandom(),
	1278	workflowId: uuid("workflow_id")
	1279	.notNull()
	1280	.references(() => workflows.id, { onDelete: "cascade" }),
	1281	repositoryId: uuid("repository_id")
	1282	.notNull()
	1283	.references(() => repositories.id, { onDelete: "cascade" }),
	1284	runNumber: integer("run_number").notNull(),
	1285	event: text("event").notNull(), // "push" \| "pull_request" \| "manual" \| ...
	1286	ref: text("ref"),
	1287	commitSha: text("commit_sha"),
	1288	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1289	onDelete: "set null",
	1290	}),
	1291	status: text("status").notNull().default("queued"), // queued\|running\|success\|failure\|cancelled
	1292	conclusion: text("conclusion"),
	1293	queuedAt: timestamp("queued_at").defaultNow().notNull(),
	1294	startedAt: timestamp("started_at"),
	1295	finishedAt: timestamp("finished_at"),
	1296	createdAt: timestamp("created_at").defaultNow().notNull(),
	1297	},
	1298	(table) => [
	1299	index("workflow_runs_repo").on(table.repositoryId),
	1300	index("workflow_runs_status").on(table.status),
	1301	index("workflow_runs_workflow").on(table.workflowId),
	1302	]
	1303	);
	1304
	1305	export const workflowJobs = pgTable(
	1306	"workflow_jobs",
	1307	{
	1308	id: uuid("id").primaryKey().defaultRandom(),
	1309	runId: uuid("run_id")
	1310	.notNull()
	1311	.references(() => workflowRuns.id, { onDelete: "cascade" }),
	1312	name: text("name").notNull(),
	1313	jobOrder: integer("job_order").default(0).notNull(),
	1314	runsOn: text("runs_on").notNull().default("default"),
	1315	status: text("status").notNull().default("queued"),
	1316	conclusion: text("conclusion"),
	1317	exitCode: integer("exit_code"),
	1318	steps: text("steps").notNull().default("[]"), // JSON array of step results
	1319	logs: text("logs").notNull().default(""),
	1320	startedAt: timestamp("started_at"),
	1321	finishedAt: timestamp("finished_at"),
	1322	createdAt: timestamp("created_at").defaultNow().notNull(),
	1323	},
	1324	(table) => [index("workflow_jobs_run").on(table.runId)]
	1325	);
	1326
	1327	export const workflowArtifacts = pgTable(
	1328	"workflow_artifacts",
	1329	{
	1330	id: uuid("id").primaryKey().defaultRandom(),
	1331	runId: uuid("run_id")
	1332	.notNull()
	1333	.references(() => workflowRuns.id, { onDelete: "cascade" }),
	1334	jobId: uuid("job_id").references(() => workflowJobs.id, {
	1335	onDelete: "set null",
	1336	}),
	1337	name: text("name").notNull(),
	1338	sizeBytes: integer("size_bytes").default(0).notNull(),
	1339	contentType: text("content_type")
	1340	.default("application/octet-stream")
	1341	.notNull(),
	1342	// bytea — drizzle doesn't have a built-in bytea type at the level we use
	1343	// elsewhere; store as text (base64) for v1. Migration uses real bytea so
	1344	// we can swap the column type later.
	1345	content: text("content"),
	1346	createdAt: timestamp("created_at").defaultNow().notNull(),
	1347	},
	1348	(table) => [index("workflow_artifacts_run").on(table.runId)]
	1349	);
	1350
	1351	export type Workflow = typeof workflows.$inferSelect;
	1352	export type WorkflowRun = typeof workflowRuns.$inferSelect;
	1353	export type WorkflowJob = typeof workflowJobs.$inferSelect;
	1354	export type WorkflowArtifact = typeof workflowArtifacts.$inferSelect;
e2da5c6Claude	1355
	1356	// ---------------------------------------------------------------------------
	1357	// Block C2 — Package registry (npm-compatible)
	1358	// ---------------------------------------------------------------------------
	1359
	1360	export const packages = pgTable(
	1361	"packages",
	1362	{
	1363	id: uuid("id").primaryKey().defaultRandom(),
	1364	repositoryId: uuid("repository_id")
	1365	.notNull()
	1366	.references(() => repositories.id, { onDelete: "cascade" }),
	1367	ecosystem: text("ecosystem").notNull().default("npm"), // "npm" \| "container"
	1368	scope: text("scope"), // "@acme" for npm; null for unscoped
	1369	name: text("name").notNull(), // "my-lib" (without scope)
	1370	description: text("description"),
	1371	readme: text("readme"),
	1372	homepage: text("homepage"),
	1373	license: text("license"),
	1374	visibility: text("visibility").notNull().default("public"), // "public" \| "private"
	1375	createdAt: timestamp("created_at").defaultNow().notNull(),
	1376	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1377	},
	1378	(table) => [
	1379	index("packages_repo").on(table.repositoryId),
	1380	uniqueIndex("packages_eco_scope_name").on(
	1381	table.ecosystem,
	1382	table.scope,
	1383	table.name
	1384	),
	1385	]
	1386	);
	1387
	1388	export const packageVersions = pgTable(
	1389	"package_versions",
	1390	{
	1391	id: uuid("id").primaryKey().defaultRandom(),
	1392	packageId: uuid("package_id")
	1393	.notNull()
	1394	.references(() => packages.id, { onDelete: "cascade" }),
	1395	version: text("version").notNull(), // "1.2.3"
	1396	shasum: text("shasum").notNull(), // sha1 (for npm compat) hex
	1397	integrity: text("integrity"), // "sha512-..." base64
	1398	sizeBytes: integer("size_bytes").default(0).notNull(),
	1399	metadata: text("metadata").notNull().default("{}"), // package.json JSON
	1400	tarball: text("tarball"), // base64-encoded; bytea in migration
	1401	publishedBy: uuid("published_by").references(() => users.id, {
	1402	onDelete: "set null",
	1403	}),
	1404	yanked: boolean("yanked").default(false).notNull(),
	1405	yankedReason: text("yanked_reason"),
	1406	publishedAt: timestamp("published_at").defaultNow().notNull(),
	1407	},
	1408	(table) => [
	1409	index("package_versions_pkg").on(table.packageId),
	1410	uniqueIndex("package_versions_pkg_version").on(table.packageId, table.version),
	1411	]
	1412	);
	1413
	1414	export const packageTags = pgTable(
	1415	"package_tags",
	1416	{
	1417	id: uuid("id").primaryKey().defaultRandom(),
	1418	packageId: uuid("package_id")
	1419	.notNull()
	1420	.references(() => packages.id, { onDelete: "cascade" }),
	1421	tag: text("tag").notNull(), // "latest" \| "beta" \| ...
	1422	versionId: uuid("version_id")
	1423	.notNull()
	1424	.references(() => packageVersions.id, { onDelete: "cascade" }),
	1425	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1426	},
	1427	(table) => [
	1428	uniqueIndex("package_tags_pkg_tag").on(table.packageId, table.tag),
	1429	]
	1430	);
	1431
	1432	export type Package = typeof packages.$inferSelect;
	1433	export type PackageVersion = typeof packageVersions.$inferSelect;
	1434	export type PackageTag = typeof packageTags.$inferSelect;
	1435
	1436	// ---------------------------------------------------------------------------
	1437	// Block C3 — Pages / static hosting
	1438	// ---------------------------------------------------------------------------
	1439
	1440	export const pagesDeployments = pgTable(
	1441	"pages_deployments",
	1442	{
	1443	id: uuid("id").primaryKey().defaultRandom(),
	1444	repositoryId: uuid("repository_id")
	1445	.notNull()
	1446	.references(() => repositories.id, { onDelete: "cascade" }),
	1447	ref: text("ref").notNull().default("refs/heads/gh-pages"),
	1448	commitSha: text("commit_sha").notNull(),
	1449	status: text("status").notNull().default("success"), // "success" \| "failed"
	1450	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1451	onDelete: "set null",
	1452	}),
	1453	createdAt: timestamp("created_at").defaultNow().notNull(),
	1454	},
	1455	(table) => [
	1456	index("pages_deployments_repo").on(table.repositoryId),
	1457	index("pages_deployments_created").on(table.createdAt),
	1458	]
	1459	);
	1460
	1461	export const pagesSettings = pgTable("pages_settings", {
	1462	repositoryId: uuid("repository_id")
	1463	.primaryKey()
	1464	.references(() => repositories.id, { onDelete: "cascade" }),
	1465	enabled: boolean("enabled").default(true).notNull(),
	1466	sourceBranch: text("source_branch").notNull().default("gh-pages"),
	1467	sourceDir: text("source_dir").notNull().default("/"), // e.g. "/" or "/docs"
	1468	customDomain: text("custom_domain"),
	1469	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1470	});
	1471
	1472	export type PagesDeployment = typeof pagesDeployments.$inferSelect;
	1473	export type PagesSettings = typeof pagesSettings.$inferSelect;
	1474
	1475	// ---------------------------------------------------------------------------
	1476	// Block C4 — Environments with protected approvals
	1477	// ---------------------------------------------------------------------------
	1478
	1479	export const environments = pgTable(
	1480	"environments",
	1481	{
	1482	id: uuid("id").primaryKey().defaultRandom(),
	1483	repositoryId: uuid("repository_id")
	1484	.notNull()
	1485	.references(() => repositories.id, { onDelete: "cascade" }),
	1486	name: text("name").notNull(), // "production" \| "staging" \| "preview"
	1487	requireApproval: boolean("require_approval").default(false).notNull(),
	1488	// JSON array of user IDs that can approve deploys.
	1489	reviewers: text("reviewers").notNull().default("[]"),
	1490	waitTimerMinutes: integer("wait_timer_minutes").default(0).notNull(),
	1491	allowedBranches: text("allowed_branches").notNull().default("[]"), // JSON glob patterns
	1492	createdAt: timestamp("created_at").defaultNow().notNull(),
	1493	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1494	},
	1495	(table) => [
	1496	uniqueIndex("environments_repo_name").on(table.repositoryId, table.name),
	1497	]
	1498	);
	1499
	1500	export const deploymentApprovals = pgTable(
	1501	"deployment_approvals",
	1502	{
	1503	id: uuid("id").primaryKey().defaultRandom(),
	1504	deploymentId: uuid("deployment_id")
	1505	.notNull()
	1506	.references(() => deployments.id, { onDelete: "cascade" }),
	1507	userId: uuid("user_id")
	1508	.notNull()
	1509	.references(() => users.id, { onDelete: "cascade" }),
	1510	decision: text("decision").notNull(), // "approved" \| "rejected"
	1511	comment: text("comment"),
	1512	createdAt: timestamp("created_at").defaultNow().notNull(),
	1513	},
	1514	(table) => [
	1515	index("deployment_approvals_deployment").on(table.deploymentId),
	1516	]
	1517	);
	1518
	1519	export type Environment = typeof environments.$inferSelect;
	1520	export type DeploymentApproval = typeof deploymentApprovals.$inferSelect;
3cbe3d6Claude	1521
	1522	// ---------------------------------------------------------------------------
	1523	// Block D — AI-native differentiation (migration 0012)
	1524	// ---------------------------------------------------------------------------
	1525
	1526	// D6 — cached "explain this codebase" markdown keyed on commit sha.
	1527	export const codebaseExplanations = pgTable(
	1528	"codebase_explanations",
	1529	{
	1530	id: uuid("id").primaryKey().defaultRandom(),
	1531	repositoryId: uuid("repository_id")
	1532	.notNull()
	1533	.references(() => repositories.id, { onDelete: "cascade" }),
	1534	commitSha: text("commit_sha").notNull(),
	1535	summary: text("summary").notNull(),
	1536	markdown: text("markdown").notNull(),
	1537	model: text("model").notNull(),
	1538	generatedAt: timestamp("generated_at").defaultNow().notNull(),
	1539	},
	1540	(table) => [
	1541	uniqueIndex("codebase_explanations_repo_sha").on(
	1542	table.repositoryId,
	1543	table.commitSha
	1544	),
	1545	]
	1546	);
	1547
	1548	// D2 — AI dependency bumper run history.
	1549	export const depUpdateRuns = pgTable(
	1550	"dep_update_runs",
	1551	{
	1552	id: uuid("id").primaryKey().defaultRandom(),
	1553	repositoryId: uuid("repository_id")
	1554	.notNull()
	1555	.references(() => repositories.id, { onDelete: "cascade" }),
	1556	status: text("status").notNull().default("pending"), // pending\|running\|success\|failed\|no_updates
	1557	ecosystem: text("ecosystem").notNull(), // npm\|bun
	1558	manifestPath: text("manifest_path").notNull(),
	1559	attemptedBumps: text("attempted_bumps").notNull().default("[]"), // JSON
	1560	appliedBumps: text("applied_bumps").notNull().default("[]"), // JSON
	1561	branchName: text("branch_name"),
	1562	prNumber: integer("pr_number"),
	1563	errorMessage: text("error_message"),
	1564	triggeredBy: uuid("triggered_by").references(() => users.id, {
	1565	onDelete: "set null",
	1566	}),
	1567	createdAt: timestamp("created_at").defaultNow().notNull(),
	1568	completedAt: timestamp("completed_at"),
	1569	},
	1570	(table) => [
	1571	index("dep_update_runs_repo").on(table.repositoryId),
	1572	index("dep_update_runs_created").on(table.createdAt),
	1573	]
	1574	);
	1575
	1576	// D1 — code chunks for semantic search. Embedding stored as JSON-encoded
	1577	// number array in text to avoid requiring pgvector; cosine similarity is
	1578	// computed in JS. Upgrade path: ALTER COLUMN embedding TYPE vector(1024).
	1579	export const codeChunks = pgTable(
	1580	"code_chunks",
	1581	{
	1582	id: uuid("id").primaryKey().defaultRandom(),
	1583	repositoryId: uuid("repository_id")
	1584	.notNull()
	1585	.references(() => repositories.id, { onDelete: "cascade" }),
	1586	commitSha: text("commit_sha").notNull(),
	1587	path: text("path").notNull(),
	1588	startLine: integer("start_line").notNull(),
	1589	endLine: integer("end_line").notNull(),
	1590	content: text("content").notNull(),
	1591	embedding: text("embedding"), // JSON number[]
	1592	embeddingModel: text("embedding_model"),
	1593	createdAt: timestamp("created_at").defaultNow().notNull(),
	1594	},
	1595	(table) => [
	1596	index("code_chunks_repo").on(table.repositoryId),
	1597	index("code_chunks_repo_path").on(table.repositoryId, table.path),
	1598	]
	1599	);
	1600
	1601	export type CodebaseExplanation = typeof codebaseExplanations.$inferSelect;
	1602	export type DepUpdateRun = typeof depUpdateRuns.$inferSelect;
1e162a8Claude	1603
	1604	// ---------------------------------------------------------------------------
	1605	// Block E2 — Discussions (migration 0013)
	1606	// ---------------------------------------------------------------------------
	1607
	1608	/**
	1609	* Discussions — forum-style threaded conversations attached to a repo.
	1610	* Similar to GitHub Discussions: categorised + pinnable + answerable.
	1611	*/
	1612	export const discussions = pgTable(
	1613	"discussions",
	1614	{
	1615	id: uuid("id").primaryKey().defaultRandom(),
	1616	number: serial("number"),
	1617	repositoryId: uuid("repository_id")
	1618	.notNull()
	1619	.references(() => repositories.id, { onDelete: "cascade" }),
	1620	authorId: uuid("author_id")
	1621	.notNull()
	1622	.references(() => users.id),
	1623	// one of: "general" \| "q-and-a" \| "ideas" \| "announcements" \| "show-and-tell"
	1624	category: text("category").notNull().default("general"),
	1625	title: text("title").notNull(),
	1626	body: text("body"),
	1627	state: text("state").notNull().default("open"), // open, closed
	1628	locked: boolean("locked").notNull().default(false),
	1629	answerCommentId: uuid("answer_comment_id"),
	1630	pinned: boolean("pinned").notNull().default(false),
	1631	createdAt: timestamp("created_at").defaultNow().notNull(),
	1632	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1633	},
	1634	(table) => [
	1635	index("discussions_repo").on(table.repositoryId),
	1636	uniqueIndex("discussions_repo_number").on(
	1637	table.repositoryId,
	1638	table.number
	1639	),
	1640	]
	1641	);
	1642
	1643	export const discussionComments = pgTable(
	1644	"discussion_comments",
	1645	{
	1646	id: uuid("id").primaryKey().defaultRandom(),
	1647	discussionId: uuid("discussion_id")
	1648	.notNull()
	1649	.references(() => discussions.id, { onDelete: "cascade" }),
	1650	parentCommentId: uuid("parent_comment_id"),
	1651	authorId: uuid("author_id")
	1652	.notNull()
	1653	.references(() => users.id),
	1654	body: text("body").notNull(),
	1655	isAnswer: boolean("is_answer").notNull().default(false),
	1656	createdAt: timestamp("created_at").defaultNow().notNull(),
	1657	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1658	},
	1659	(table) => [
	1660	index("discussion_comments_discussion").on(table.discussionId),
	1661	]
	1662	);
	1663
	1664	export type Discussion = typeof discussions.$inferSelect;
	1665	export type DiscussionComment = typeof discussionComments.$inferSelect;
3cbe3d6Claude	1666	export type CodeChunk = typeof codeChunks.$inferSelect;
1e162a8Claude	1667
	1668	// ---------------------------------------------------------------------------
	1669	// Block E4 — Gists (migration 0014)
	1670	// ---------------------------------------------------------------------------
	1671	//
	1672	// User-owned small snippets/files that behave like tiny repos. DB-backed
	1673	// for v1 (no bare git repo): each gist owns a collection of gist_files and
	1674	// every edit appends a gist_revisions row with a JSON snapshot.
	1675
	1676	export const gists = pgTable(
	1677	"gists",
	1678	{
	1679	id: uuid("id").primaryKey().defaultRandom(),
	1680	ownerId: uuid("owner_id")
	1681	.notNull()
	1682	.references(() => users.id, { onDelete: "cascade" }),
	1683	// 8-char hex slug used in pretty URLs (e.g. /gists/a1b2c3d4).
	1684	slug: text("slug").notNull().unique(),
	1685	title: text("title").notNull().default(""),
	1686	description: text("description").notNull().default(""),
	1687	isPublic: boolean("is_public").default(true).notNull(),
	1688	createdAt: timestamp("created_at").defaultNow().notNull(),
	1689	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1690	},
	1691	(table) => [index("gists_owner").on(table.ownerId)]
	1692	);
	1693
	1694	export const gistFiles = pgTable(
	1695	"gist_files",
	1696	{
	1697	id: uuid("id").primaryKey().defaultRandom(),
	1698	gistId: uuid("gist_id")
	1699	.notNull()
	1700	.references(() => gists.id, { onDelete: "cascade" }),
	1701	filename: text("filename").notNull(),
	1702	// Optional explicit language override; falls back to filename detection.
	1703	language: text("language"),
	1704	content: text("content").notNull().default(""),
	1705	sizeBytes: integer("size_bytes").default(0).notNull(),
	1706	},
	1707	(table) => [
	1708	index("gist_files_gist").on(table.gistId),
	1709	uniqueIndex("gist_files_gist_filename").on(table.gistId, table.filename),
	1710	]
	1711	);
	1712
	1713	export const gistRevisions = pgTable(
	1714	"gist_revisions",
	1715	{
	1716	id: uuid("id").primaryKey().defaultRandom(),
	1717	gistId: uuid("gist_id")
	1718	.notNull()
	1719	.references(() => gists.id, { onDelete: "cascade" }),
	1720	revision: integer("revision").notNull(),
	1721	// JSON-encoded {filename: content} map capturing the full snapshot at
	1722	// this revision. Stored as text to avoid requiring jsonb.
	1723	snapshot: text("snapshot").notNull().default("{}"),
	1724	authorId: uuid("author_id")
	1725	.notNull()
	1726	.references(() => users.id, { onDelete: "cascade" }),
	1727	message: text("message"),
	1728	createdAt: timestamp("created_at").defaultNow().notNull(),
	1729	},
	1730	(table) => [
	1731	index("gist_revisions_gist_rev").on(table.gistId, table.revision),
	1732	]
	1733	);
	1734
	1735	export const gistStars = pgTable(
	1736	"gist_stars",
	1737	{
	1738	id: uuid("id").primaryKey().defaultRandom(),
	1739	gistId: uuid("gist_id")
	1740	.notNull()
	1741	.references(() => gists.id, { onDelete: "cascade" }),
	1742	userId: uuid("user_id")
	1743	.notNull()
	1744	.references(() => users.id, { onDelete: "cascade" }),
	1745	createdAt: timestamp("created_at").defaultNow().notNull(),
	1746	},
	1747	(table) => [uniqueIndex("gist_stars_gist_user").on(table.gistId, table.userId)]
	1748	);
	1749
	1750	export type Gist = typeof gists.$inferSelect;
	1751	export type GistFile = typeof gistFiles.$inferSelect;
	1752	export type GistRevision = typeof gistRevisions.$inferSelect;
	1753	export type GistStar = typeof gistStars.$inferSelect;
	1754
	1755	// ---------------------------------------------------------------------------
	1756	// Block E1 — Projects / kanban (migration 0015)
	1757	// ---------------------------------------------------------------------------
	1758
	1759	export const projects = pgTable(
	1760	"projects",
	1761	{
	1762	id: uuid("id").primaryKey().defaultRandom(),
	1763	number: serial("number"),
	1764	repositoryId: uuid("repository_id")
	1765	.notNull()
	1766	.references(() => repositories.id, { onDelete: "cascade" }),
	1767	ownerId: uuid("owner_id")
	1768	.notNull()
	1769	.references(() => users.id),
	1770	title: text("title").notNull(),
	1771	description: text("description").notNull().default(""),
	1772	state: text("state").notNull().default("open"), // open \| closed
	1773	createdAt: timestamp("created_at").defaultNow().notNull(),
	1774	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1775	},
	1776	(table) => [
	1777	index("projects_repo").on(table.repositoryId),
	1778	uniqueIndex("projects_repo_number").on(table.repositoryId, table.number),
	1779	]
	1780	);
	1781
	1782	export const projectColumns = pgTable(
	1783	"project_columns",
	1784	{
	1785	id: uuid("id").primaryKey().defaultRandom(),
	1786	projectId: uuid("project_id")
	1787	.notNull()
	1788	.references(() => projects.id, { onDelete: "cascade" }),
	1789	name: text("name").notNull(),
	1790	position: integer("position").notNull().default(0),
	1791	createdAt: timestamp("created_at").defaultNow().notNull(),
	1792	},
	1793	(table) => [index("project_columns_project").on(table.projectId)]
	1794	);
	1795
	1796	export const projectItems = pgTable(
	1797	"project_items",
	1798	{
	1799	id: uuid("id").primaryKey().defaultRandom(),
	1800	projectId: uuid("project_id")
	1801	.notNull()
	1802	.references(() => projects.id, { onDelete: "cascade" }),
	1803	columnId: uuid("column_id")
	1804	.notNull()
	1805	.references(() => projectColumns.id, { onDelete: "cascade" }),
	1806	position: integer("position").notNull().default(0),
	1807	// "note" \| "issue" \| "pr" — application-level FK on itemId by type
	1808	itemType: text("item_type").notNull().default("note"),
	1809	itemId: uuid("item_id"),
	1810	title: text("title").notNull().default(""),
	1811	note: text("note").notNull().default(""),
	1812	createdAt: timestamp("created_at").defaultNow().notNull(),
	1813	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1814	},
	1815	(table) => [
	1816	index("project_items_project").on(table.projectId),
	1817	index("project_items_column").on(table.columnId, table.position),
	1818	]
	1819	);
	1820
	1821	export type Project = typeof projects.$inferSelect;
	1822	export type ProjectColumn = typeof projectColumns.$inferSelect;
	1823	export type ProjectItem = typeof projectItems.$inferSelect;
	1824
	1825	// ---------------------------------------------------------------------------
	1826	// Block E3 — Wikis (migration 0016)
	1827	// ---------------------------------------------------------------------------
	1828	// DB-backed for v1; git-backed mirror is a future upgrade.
	1829
	1830	export const wikiPages = pgTable(
	1831	"wiki_pages",
	1832	{
	1833	id: uuid("id").primaryKey().defaultRandom(),
	1834	repositoryId: uuid("repository_id")
	1835	.notNull()
	1836	.references(() => repositories.id, { onDelete: "cascade" }),
	1837	slug: text("slug").notNull(),
	1838	title: text("title").notNull(),
	1839	body: text("body").notNull().default(""),
	1840	revision: integer("revision").notNull().default(1),
	1841	createdAt: timestamp("created_at").defaultNow().notNull(),
	1842	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1843	updatedBy: uuid("updated_by").references(() => users.id),
	1844	},
	1845	(table) => [
	1846	index("wiki_pages_repo").on(table.repositoryId),
	1847	uniqueIndex("wiki_pages_repo_slug").on(table.repositoryId, table.slug),
	1848	]
	1849	);
	1850
	1851	export const wikiRevisions = pgTable(
	1852	"wiki_revisions",
	1853	{
	1854	id: uuid("id").primaryKey().defaultRandom(),
	1855	pageId: uuid("page_id")
	1856	.notNull()
	1857	.references(() => wikiPages.id, { onDelete: "cascade" }),
	1858	revision: integer("revision").notNull(),
	1859	title: text("title").notNull(),
	1860	body: text("body").notNull().default(""),
	1861	message: text("message"),
	1862	authorId: uuid("author_id")
	1863	.notNull()
	1864	.references(() => users.id),
	1865	createdAt: timestamp("created_at").defaultNow().notNull(),
	1866	},
	1867	(table) => [index("wiki_revisions_page").on(table.pageId, table.revision)]
	1868	);
	1869
	1870	export type WikiPage = typeof wikiPages.$inferSelect;
	1871	export type WikiRevision = typeof wikiRevisions.$inferSelect;
a79a9edClaude	1872
	1873	// ---------------------------------------------------------------------------
	1874	// Block E5 — Merge queues (migration 0017)
	1875	// ---------------------------------------------------------------------------
	1876
	1877	export const mergeQueueEntries = pgTable(
	1878	"merge_queue_entries",
	1879	{
	1880	id: uuid("id").primaryKey().defaultRandom(),
	1881	repositoryId: uuid("repository_id")
	1882	.notNull()
	1883	.references(() => repositories.id, { onDelete: "cascade" }),
	1884	pullRequestId: uuid("pull_request_id")
	1885	.notNull()
	1886	.references(() => pullRequests.id, { onDelete: "cascade" }),
	1887	baseBranch: text("base_branch").notNull(),
	1888	// queued \| running \| merged \| failed \| dequeued
	1889	state: text("state").notNull().default("queued"),
	1890	position: integer("position").notNull().default(0),
	1891	enqueuedBy: uuid("enqueued_by").references(() => users.id),
	1892	enqueuedAt: timestamp("enqueued_at").defaultNow().notNull(),
	1893	startedAt: timestamp("started_at"),
	1894	finishedAt: timestamp("finished_at"),
	1895	errorMessage: text("error_message"),
	1896	},
	1897	(table) => [
	1898	index("merge_queue_repo_branch").on(
	1899	table.repositoryId,
	1900	table.baseBranch,
	1901	table.state
	1902	),
	1903	]
	1904	);
	1905
	1906	export type MergeQueueEntry = typeof mergeQueueEntries.$inferSelect;
	1907
	1908	// ---------------------------------------------------------------------------
	1909	// Block E6 — Required status checks matrix (migration 0018)
	1910	// ---------------------------------------------------------------------------
	1911
	1912	export const branchRequiredChecks = pgTable(
	1913	"branch_required_checks",
	1914	{
	1915	id: uuid("id").primaryKey().defaultRandom(),
	1916	branchProtectionId: uuid("branch_protection_id")
	1917	.notNull()
	1918	.references(() => branchProtection.id, { onDelete: "cascade" }),
	1919	checkName: text("check_name").notNull(),
	1920	createdAt: timestamp("created_at").defaultNow().notNull(),
	1921	},
	1922	(table) => [
	1923	index("branch_required_checks_rule").on(table.branchProtectionId),
	1924	uniqueIndex("branch_required_checks_unique").on(
	1925	table.branchProtectionId,
	1926	table.checkName
	1927	),
	1928	]
	1929	);
	1930
	1931	export type BranchRequiredCheck = typeof branchRequiredChecks.$inferSelect;
	1932
	1933	// ---------------------------------------------------------------------------
	1934	// Block E7 — Protected tags (migration 0019)
	1935	// ---------------------------------------------------------------------------
	1936
	1937	export const protectedTags = pgTable(
	1938	"protected_tags",
	1939	{
	1940	id: uuid("id").primaryKey().defaultRandom(),
	1941	repositoryId: uuid("repository_id")
	1942	.notNull()
	1943	.references(() => repositories.id, { onDelete: "cascade" }),
	1944	pattern: text("pattern").notNull(),
	1945	createdAt: timestamp("created_at").defaultNow().notNull(),
	1946	createdBy: uuid("created_by").references(() => users.id),
	1947	},
	1948	(table) => [
	1949	index("protected_tags_repo").on(table.repositoryId),
	1950	uniqueIndex("protected_tags_repo_pattern").on(
	1951	table.repositoryId,
	1952	table.pattern
	1953	),
	1954	]
	1955	);
	1956
	1957	export type ProtectedTag = typeof protectedTags.$inferSelect;
8f50ed0Claude	1958
	1959	// ---------------------------------------------------------------------------
	1960	// Block F — Observability + admin (migration 0020)
	1961	// ---------------------------------------------------------------------------
	1962
	1963	// F1 — Traffic analytics per repo
	1964	export const repoTrafficEvents = pgTable(
	1965	"repo_traffic_events",
	1966	{
	1967	id: uuid("id").primaryKey().defaultRandom(),
	1968	repositoryId: uuid("repository_id")
	1969	.notNull()
	1970	.references(() => repositories.id, { onDelete: "cascade" }),
	1971	kind: text("kind").notNull(), // view \| clone \| api \| ui
	1972	path: text("path"),
	1973	userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
	1974	ipHash: text("ip_hash"),
	1975	userAgent: text("user_agent"),
	1976	referer: text("referer"),
	1977	createdAt: timestamp("created_at").defaultNow().notNull(),
	1978	},
	1979	(table) => [
	1980	index("repo_traffic_events_repo_time").on(
	1981	table.repositoryId,
	1982	table.createdAt
	1983	),
	1984	index("repo_traffic_events_kind").on(
	1985	table.repositoryId,
	1986	table.kind,
	1987	table.createdAt
	1988	),
	1989	]
	1990	);
	1991
	1992	export type RepoTrafficEvent = typeof repoTrafficEvents.$inferSelect;
	1993
	1994	// F3 — Admin panel (site admins + toggleable flags)
	1995	export const systemFlags = pgTable("system_flags", {
	1996	key: text("key").primaryKey(),
	1997	value: text("value").notNull().default(""),
	1998	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	1999	updatedBy: uuid("updated_by").references(() => users.id),
	2000	});
	2001
	2002	export type SystemFlag = typeof systemFlags.$inferSelect;
	2003
	2004	export const siteAdmins = pgTable("site_admins", {
	2005	userId: uuid("user_id")
	2006	.primaryKey()
	2007	.references(() => users.id, { onDelete: "cascade" }),
	2008	grantedAt: timestamp("granted_at").defaultNow().notNull(),
	2009	grantedBy: uuid("granted_by").references(() => users.id),
	2010	});
	2011
	2012	export type SiteAdmin = typeof siteAdmins.$inferSelect;
	2013
509c376Claude	2014	// /admin/integrations — DB-stored platform integration secrets (migration 0055).
	2015	// Loaded into process.env at boot so the existing synchronous config getters
	2016	// keep working transparently. Never exposes DATABASE_URL / SELF_HOST_REPO /
	2017	// PORT / GIT_REPOS_PATH — those stay env-only.
	2018	export const systemConfig = pgTable("system_config", {
	2019	key: text("key").primaryKey(),
	2020	value: text("value").notNull().default(""),
	2021	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2022	updatedByUserId: uuid("updated_by_user_id").references(() => users.id, {
	2023	onDelete: "set null",
	2024	}),
	2025	});
	2026
	2027	export type SystemConfig = typeof systemConfig.$inferSelect;
	2028
8f50ed0Claude	2029	// F4 — Billing + quotas
	2030	export const billingPlans = pgTable("billing_plans", {
	2031	id: uuid("id").primaryKey().defaultRandom(),
	2032	slug: text("slug").notNull().unique(),
	2033	name: text("name").notNull(),
	2034	priceCents: integer("price_cents").notNull().default(0),
	2035	repoLimit: integer("repo_limit").notNull().default(10),
	2036	storageMbLimit: integer("storage_mb_limit").notNull().default(1024),
	2037	aiTokensMonthly: integer("ai_tokens_monthly").notNull().default(100000),
	2038	bandwidthGbMonthly: integer("bandwidth_gb_monthly").notNull().default(10),
	2039	privateRepos: boolean("private_repos").notNull().default(false),
	2040	createdAt: timestamp("created_at").defaultNow().notNull(),
	2041	});
	2042
	2043	export type BillingPlan = typeof billingPlans.$inferSelect;
	2044
	2045	export const userQuotas = pgTable("user_quotas", {
	2046	userId: uuid("user_id")
	2047	.primaryKey()
	2048	.references(() => users.id, { onDelete: "cascade" }),
	2049	planSlug: text("plan_slug").notNull().default("free"),
	2050	storageMbUsed: integer("storage_mb_used").notNull().default(0),
	2051	aiTokensUsedThisMonth: integer("ai_tokens_used_this_month")
	2052	.notNull()
	2053	.default(0),
	2054	bandwidthGbUsedThisMonth: integer("bandwidth_gb_used_this_month")
	2055	.notNull()
	2056	.default(0),
	2057	cycleStart: timestamp("cycle_start").defaultNow().notNull(),
	2058	updatedAt: timestamp("updated_at").defaultNow().notNull(),
619109aClaude	2059	// Stripe linkage (migration 0038). Null until the user first upgrades.
	2060	stripeCustomerId: text("stripe_customer_id"),
	2061	stripeSubscriptionId: text("stripe_subscription_id"),
	2062	stripeSubscriptionStatus: text("stripe_subscription_status"),
	2063	currentPeriodEnd: timestamp("current_period_end"),
8f50ed0Claude	2064	});
	2065
	2066	export type UserQuota = typeof userQuotas.$inferSelect;
06139e6Claude	2067
	2068	// Block H — App marketplace + bot identities (GitHub Apps equivalent)
	2069
	2070	export const apps = pgTable(
	2071	"apps",
	2072	{
	2073	id: uuid("id").primaryKey().defaultRandom(),
	2074	slug: text("slug").notNull().unique(),
	2075	name: text("name").notNull(),
	2076	description: text("description").notNull().default(""),
	2077	iconUrl: text("icon_url"),
	2078	homepageUrl: text("homepage_url"),
	2079	webhookUrl: text("webhook_url"),
	2080	webhookSecret: text("webhook_secret"),
	2081	creatorId: uuid("creator_id")
	2082	.notNull()
	2083	.references(() => users.id, { onDelete: "cascade" }),
	2084	permissions: text("permissions").notNull().default("[]"), // JSON array
	2085	defaultEvents: text("default_events").notNull().default("[]"), // JSON array
	2086	isPublic: boolean("is_public").notNull().default(true),
	2087	createdAt: timestamp("created_at").defaultNow().notNull(),
	2088	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2089	},
	2090	(table) => [index("apps_public_slug").on(table.isPublic, table.slug)]
	2091	);
	2092
	2093	export type App = typeof apps.$inferSelect;
	2094
	2095	export const appInstallations = pgTable(
	2096	"app_installations",
	2097	{
	2098	id: uuid("id").primaryKey().defaultRandom(),
	2099	appId: uuid("app_id")
	2100	.notNull()
	2101	.references(() => apps.id, { onDelete: "cascade" }),
	2102	installedBy: uuid("installed_by")
	2103	.notNull()
	2104	.references(() => users.id, { onDelete: "cascade" }),
	2105	targetType: text("target_type").notNull(), // user \| org \| repository
	2106	targetId: uuid("target_id").notNull(),
	2107	grantedPermissions: text("granted_permissions").notNull().default("[]"),
	2108	suspendedAt: timestamp("suspended_at"),
	2109	createdAt: timestamp("created_at").defaultNow().notNull(),
	2110	uninstalledAt: timestamp("uninstalled_at"),
	2111	},
	2112	(table) => [
	2113	index("app_installations_app").on(table.appId),
	2114	index("app_installations_target").on(table.targetType, table.targetId),
	2115	]
	2116	);
	2117
	2118	export type AppInstallation = typeof appInstallations.$inferSelect;
	2119
	2120	export const appBots = pgTable("app_bots", {
	2121	id: uuid("id").primaryKey().defaultRandom(),
	2122	appId: uuid("app_id")
	2123	.notNull()
	2124	.unique()
	2125	.references(() => apps.id, { onDelete: "cascade" }),
	2126	username: text("username").notNull().unique(),
	2127	displayName: text("display_name").notNull(),
	2128	avatarUrl: text("avatar_url"),
	2129	createdAt: timestamp("created_at").defaultNow().notNull(),
	2130	});
	2131
	2132	export type AppBot = typeof appBots.$inferSelect;
	2133
	2134	export const appInstallTokens = pgTable(
	2135	"app_install_tokens",
	2136	{
	2137	id: uuid("id").primaryKey().defaultRandom(),
	2138	installationId: uuid("installation_id")
	2139	.notNull()
	2140	.references(() => appInstallations.id, { onDelete: "cascade" }),
	2141	tokenHash: text("token_hash").notNull().unique(),
	2142	expiresAt: timestamp("expires_at").notNull(),
	2143	createdAt: timestamp("created_at").defaultNow().notNull(),
	2144	revokedAt: timestamp("revoked_at"),
	2145	},
	2146	(table) => [index("app_install_tokens_hash").on(table.tokenHash)]
	2147	);
	2148
	2149	export type AppInstallToken = typeof appInstallTokens.$inferSelect;
	2150
	2151	export const appEvents = pgTable(
	2152	"app_events",
	2153	{
	2154	id: uuid("id").primaryKey().defaultRandom(),
	2155	appId: uuid("app_id")
	2156	.notNull()
	2157	.references(() => apps.id, { onDelete: "cascade" }),
	2158	installationId: uuid("installation_id"),
	2159	kind: text("kind").notNull(), // installed \| uninstalled \| delivery_ok \| delivery_fail
	2160	payload: text("payload"),
	2161	responseStatus: integer("response_status"),
	2162	createdAt: timestamp("created_at").defaultNow().notNull(),
	2163	},
	2164	(table) => [index("app_events_app_time").on(table.appId, table.createdAt)]
	2165	);
	2166
	2167	export type AppEvent = typeof appEvents.$inferSelect;
71cd5ecClaude	2168
	2169	// ---------- Block I3 — Repository transfer history ----------
	2170
	2171	export const repoTransfers = pgTable(
	2172	"repo_transfers",
	2173	{
	2174	id: uuid("id").primaryKey().defaultRandom(),
	2175	repositoryId: uuid("repository_id")
	2176	.notNull()
	2177	.references(() => repositories.id, { onDelete: "cascade" }),
	2178	fromOwnerId: uuid("from_owner_id").notNull(),
	2179	fromOrgId: uuid("from_org_id"),
	2180	toOwnerId: uuid("to_owner_id").notNull(),
	2181	toOrgId: uuid("to_org_id"),
	2182	initiatedBy: uuid("initiated_by")
	2183	.notNull()
	2184	.references(() => users.id, { onDelete: "cascade" }),
	2185	createdAt: timestamp("created_at").defaultNow().notNull(),
	2186	},
	2187	(table) => [
	2188	index("repo_transfers_repo").on(table.repositoryId, table.createdAt),
	2189	]
	2190	);
	2191
	2192	export type RepoTransfer = typeof repoTransfers.$inferSelect;
08420cdClaude	2193
	2194	// ---------- Block I6 — Sponsors ----------
	2195
	2196	export const sponsorshipTiers = pgTable(
	2197	"sponsorship_tiers",
	2198	{
	2199	id: uuid("id").primaryKey().defaultRandom(),
	2200	maintainerId: uuid("maintainer_id")
	2201	.notNull()
	2202	.references(() => users.id, { onDelete: "cascade" }),
	2203	name: text("name").notNull(),
	2204	description: text("description").default("").notNull(),
	2205	monthlyCents: integer("monthly_cents").notNull(),
	2206	oneTimeAllowed: boolean("one_time_allowed").default(true).notNull(),
	2207	isActive: boolean("is_active").default(true).notNull(),
	2208	createdAt: timestamp("created_at").defaultNow().notNull(),
	2209	},
	2210	(table) => [
	2211	index("sponsor_tiers_maintainer").on(table.maintainerId, table.isActive),
	2212	]
	2213	);
	2214
	2215	export type SponsorshipTier = typeof sponsorshipTiers.$inferSelect;
	2216
	2217	export const sponsorships = pgTable(
	2218	"sponsorships",
	2219	{
	2220	id: uuid("id").primaryKey().defaultRandom(),
	2221	sponsorId: uuid("sponsor_id")
	2222	.notNull()
	2223	.references(() => users.id, { onDelete: "cascade" }),
	2224	maintainerId: uuid("maintainer_id")
	2225	.notNull()
	2226	.references(() => users.id, { onDelete: "cascade" }),
	2227	tierId: uuid("tier_id"),
	2228	amountCents: integer("amount_cents").notNull(),
	2229	kind: text("kind").notNull(), // one_time \| monthly
	2230	note: text("note"),
	2231	isPublic: boolean("is_public").default(true).notNull(),
	2232	externalRef: text("external_ref"),
	2233	cancelledAt: timestamp("cancelled_at"),
	2234	createdAt: timestamp("created_at").defaultNow().notNull(),
	2235	},
	2236	(table) => [
	2237	index("sponsorships_maintainer").on(
	2238	table.maintainerId,
	2239	table.createdAt
	2240	),
	2241	index("sponsorships_sponsor").on(table.sponsorId, table.createdAt),
	2242	]
	2243	);
	2244
	2245	export type Sponsorship = typeof sponsorships.$inferSelect;
4c8f666Claude	2246
	2247	// Block I8 — Code symbol index for xref navigation.
	2248	export const codeSymbols = pgTable(
	2249	"code_symbols",
	2250	{
	2251	id: uuid("id").primaryKey().defaultRandom(),
	2252	repositoryId: uuid("repository_id")
	2253	.notNull()
	2254	.references(() => repositories.id, { onDelete: "cascade" }),
	2255	commitSha: text("commit_sha").notNull(),
	2256	name: text("name").notNull(),
	2257	kind: text("kind").notNull(), // function \| class \| interface \| type \| const \| variable
	2258	path: text("path").notNull(),
	2259	line: integer("line").notNull(),
	2260	signature: text("signature"),
	2261	createdAt: timestamp("created_at").defaultNow().notNull(),
	2262	},
	2263	(table) => [
	2264	index("code_symbols_repo_name_idx").on(table.repositoryId, table.name),
	2265	index("code_symbols_repo_path_idx").on(table.repositoryId, table.path),
	2266	]
	2267	);
	2268
	2269	export type CodeSymbol = typeof codeSymbols.$inferSelect;
4a0dea1Claude	2270
	2271	// Block I9 — Repository mirroring. One row per mirrored repo + an
	2272	// append-only log of sync attempts.
	2273	export const repoMirrors = pgTable("repo_mirrors", {
	2274	id: uuid("id").primaryKey().defaultRandom(),
	2275	repositoryId: uuid("repository_id")
	2276	.notNull()
	2277	.unique()
	2278	.references(() => repositories.id, { onDelete: "cascade" }),
	2279	upstreamUrl: text("upstream_url").notNull(),
	2280	intervalMinutes: integer("interval_minutes").default(1440).notNull(),
	2281	lastSyncedAt: timestamp("last_synced_at"),
	2282	lastStatus: text("last_status"), // "ok" \| "error"
	2283	lastError: text("last_error"),
	2284	isEnabled: boolean("is_enabled").default(true).notNull(),
	2285	createdAt: timestamp("created_at").defaultNow().notNull(),
	2286	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2287	});
	2288
	2289	export type RepoMirror = typeof repoMirrors.$inferSelect;
	2290
	2291	export const repoMirrorRuns = pgTable(
	2292	"repo_mirror_runs",
	2293	{
	2294	id: uuid("id").primaryKey().defaultRandom(),
	2295	mirrorId: uuid("mirror_id")
	2296	.notNull()
	2297	.references(() => repoMirrors.id, { onDelete: "cascade" }),
	2298	startedAt: timestamp("started_at").defaultNow().notNull(),
	2299	finishedAt: timestamp("finished_at"),
	2300	status: text("status").default("running").notNull(),
	2301	message: text("message"),
	2302	exitCode: integer("exit_code"),
	2303	},
	2304	(table) => [
	2305	index("repo_mirror_runs_mirror_id_idx").on(table.mirrorId, table.startedAt),
	2306	]
	2307	);
	2308
	2309	export type RepoMirrorRun = typeof repoMirrorRuns.$inferSelect;
edf7c36Claude	2310
	2311	// ----------------------------------------------------------------------------
	2312	// Block I10 — Enterprise SSO (OIDC)
	2313	// ----------------------------------------------------------------------------
	2314
	2315	/** Site-wide SSO provider. Singleton row with id = 'default'. */
	2316	export const ssoConfig = pgTable("sso_config", {
	2317	id: text("id").primaryKey(),
	2318	enabled: boolean("enabled").default(false).notNull(),
	2319	providerName: text("provider_name").default("SSO").notNull(),
	2320	issuer: text("issuer"),
	2321	authorizationEndpoint: text("authorization_endpoint"),
	2322	tokenEndpoint: text("token_endpoint"),
	2323	userinfoEndpoint: text("userinfo_endpoint"),
	2324	clientId: text("client_id"),
	2325	clientSecret: text("client_secret"),
	2326	scopes: text("scopes").default("openid profile email").notNull(),
	2327	allowedEmailDomains: text("allowed_email_domains"),
	2328	autoCreateUsers: boolean("auto_create_users").default(true).notNull(),
	2329	createdAt: timestamp("created_at").defaultNow().notNull(),
	2330	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2331	});
	2332
	2333	export type SsoConfig = typeof ssoConfig.$inferSelect;
	2334
	2335	/** Maps a local user to an IdP `sub` claim. */
	2336	export const ssoUserLinks = pgTable(
	2337	"sso_user_links",
	2338	{
	2339	id: uuid("id").primaryKey().defaultRandom(),
	2340	userId: uuid("user_id")
	2341	.notNull()
	2342	.references(() => users.id, { onDelete: "cascade" }),
	2343	subject: text("subject").notNull().unique(),
	2344	emailAtLink: text("email_at_link").notNull(),
	2345	linkedAt: timestamp("linked_at").defaultNow().notNull(),
	2346	},
	2347	(table) => [index("sso_user_links_user_id_idx").on(table.userId)]
	2348	);
	2349
	2350	export type SsoUserLink = typeof ssoUserLinks.$inferSelect;
8098672Claude	2351
	2352	// ----------------------------------------------------------------------------
	2353	// Block J1 — Dependency graph
	2354	// ----------------------------------------------------------------------------
	2355
	2356	/**
	2357	* Last known set of dependencies parsed from manifest files. Each reindex
	2358	* replaces the prior rows for that repo. One row per (ecosystem, name,
	2359	* manifest_path) — same name in multiple manifests is kept.
	2360	*/
	2361	export const repoDependencies = pgTable(
	2362	"repo_dependencies",
	2363	{
	2364	id: uuid("id").primaryKey().defaultRandom(),
	2365	repositoryId: uuid("repository_id")
	2366	.notNull()
	2367	.references(() => repositories.id, { onDelete: "cascade" }),
	2368	ecosystem: text("ecosystem").notNull(),
	2369	name: text("name").notNull(),
	2370	versionSpec: text("version_spec"),
	2371	manifestPath: text("manifest_path").notNull(),
	2372	isDev: boolean("is_dev").default(false).notNull(),
	2373	commitSha: text("commit_sha").notNull(),
	2374	indexedAt: timestamp("indexed_at").defaultNow().notNull(),
	2375	},
	2376	(table) => [
	2377	index("repo_dependencies_repo_id_idx").on(
	2378	table.repositoryId,
	2379	table.ecosystem
	2380	),
	2381	index("repo_dependencies_name_idx").on(table.name),
	2382	]
	2383	);
	2384
	2385	export type RepoDependency = typeof repoDependencies.$inferSelect;
f60ccdeClaude	2386
	2387	// ----------------------------------------------------------------------------
	2388	// Block J2 — Security advisories + alerts
	2389	// ----------------------------------------------------------------------------
	2390
	2391	/** CVE-style package advisories. Populated via seed + admin import. */
	2392	export const securityAdvisories = pgTable(
	2393	"security_advisories",
	2394	{
	2395	id: uuid("id").primaryKey().defaultRandom(),
	2396	ghsaId: text("ghsa_id").unique(),
	2397	cveId: text("cve_id"),
	2398	summary: text("summary").notNull(),
	2399	severity: text("severity").default("moderate").notNull(),
	2400	ecosystem: text("ecosystem").notNull(),
	2401	packageName: text("package_name").notNull(),
	2402	affectedRange: text("affected_range").notNull(),
	2403	fixedVersion: text("fixed_version"),
	2404	referenceUrl: text("reference_url"),
	2405	publishedAt: timestamp("published_at").defaultNow().notNull(),
	2406	},
	2407	(table) => [
	2408	index("security_advisories_pkg_idx").on(
	2409	table.ecosystem,
	2410	table.packageName
	2411	),
	2412	]
	2413	);
	2414
	2415	export type SecurityAdvisory = typeof securityAdvisories.$inferSelect;
	2416
	2417	/** Per-repo match state. One row per (repo, advisory, manifest_path). */
	2418	export const repoAdvisoryAlerts = pgTable(
	2419	"repo_advisory_alerts",
	2420	{
	2421	id: uuid("id").primaryKey().defaultRandom(),
	2422	repositoryId: uuid("repository_id")
	2423	.notNull()
	2424	.references(() => repositories.id, { onDelete: "cascade" }),
	2425	advisoryId: uuid("advisory_id")
	2426	.notNull()
	2427	.references(() => securityAdvisories.id, { onDelete: "cascade" }),
	2428	dependencyName: text("dependency_name").notNull(),
	2429	dependencyVersion: text("dependency_version"),
	2430	manifestPath: text("manifest_path").notNull(),
	2431	status: text("status").default("open").notNull(),
	2432	dismissedReason: text("dismissed_reason"),
	2433	createdAt: timestamp("created_at").defaultNow().notNull(),
	2434	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2435	},
	2436	(table) => [
	2437	index("repo_advisory_alerts_status_idx").on(
	2438	table.repositoryId,
	2439	table.status
	2440	),
	2441	]
	2442	);
	2443
	2444	export type RepoAdvisoryAlert = typeof repoAdvisoryAlerts.$inferSelect;
3951454Claude	2445
	2446	// ----------------------------------------------------------------------------
	2447	// Block J3 — Commit signature verification (GPG + SSH)
	2448	// ----------------------------------------------------------------------------
	2449
	2450	/** Per-user GPG/SSH public keys for commit signing. */
	2451	export const signingKeys = pgTable(
	2452	"signing_keys",
	2453	{
	2454	id: uuid("id").primaryKey().defaultRandom(),
	2455	userId: uuid("user_id")
	2456	.notNull()
	2457	.references(() => users.id, { onDelete: "cascade" }),
	2458	keyType: text("key_type").notNull(), // 'gpg' \| 'ssh'
	2459	title: text("title").notNull(),
	2460	fingerprint: text("fingerprint").notNull(),
	2461	publicKey: text("public_key").notNull(),
	2462	email: text("email"),
	2463	expiresAt: timestamp("expires_at"),
	2464	lastUsedAt: timestamp("last_used_at"),
	2465	createdAt: timestamp("created_at").defaultNow().notNull(),
	2466	},
	2467	(table) => [
	2468	uniqueIndex("signing_keys_fp_unique").on(table.keyType, table.fingerprint),
	2469	index("signing_keys_user_idx").on(table.userId),
	2470	]
	2471	);
	2472
	2473	export type SigningKey = typeof signingKeys.$inferSelect;
	2474
	2475	/**
	2476	* Cached verification result for a (repo, commit) pair. Repopulated on demand;
	2477	* rows are invalidated implicitly by CASCADE when either side is removed.
	2478	*/
	2479	export const commitVerifications = pgTable(
	2480	"commit_verifications",
	2481	{
	2482	id: uuid("id").primaryKey().defaultRandom(),
	2483	repositoryId: uuid("repository_id")
	2484	.notNull()
	2485	.references(() => repositories.id, { onDelete: "cascade" }),
	2486	commitSha: text("commit_sha").notNull(),
	2487	verified: boolean("verified").default(false).notNull(),
	2488	reason: text("reason").notNull(),
	2489	signatureType: text("signature_type"),
	2490	signerKeyId: uuid("signer_key_id").references(() => signingKeys.id, {
	2491	onDelete: "set null",
	2492	}),
	2493	signerUserId: uuid("signer_user_id").references(() => users.id, {
	2494	onDelete: "set null",
	2495	}),
	2496	signerFingerprint: text("signer_fingerprint"),
	2497	verifiedAt: timestamp("verified_at").defaultNow().notNull(),
	2498	},
	2499	(table) => [
	2500	uniqueIndex("commit_verifications_sha_unique").on(
	2501	table.repositoryId,
	2502	table.commitSha
	2503	),
	2504	]
	2505	);
	2506
	2507	export type CommitVerification = typeof commitVerifications.$inferSelect;
7aa8b99Claude	2508
	2509	// ----------------------------------------------------------------------------
	2510	// Block J4 — User following
	2511	// ----------------------------------------------------------------------------
	2512
	2513	/**
	2514	* Directed user→user follow edges. Primary key is the composite
	2515	* (follower_id, following_id) at the SQL level; drizzle sees it as a
	2516	* regular table with a unique index plus a secondary index on the
	2517	* reverse-lookup column.
	2518	*/
	2519	export const userFollows = pgTable(
	2520	"user_follows",
	2521	{
	2522	followerId: uuid("follower_id")
	2523	.notNull()
	2524	.references(() => users.id, { onDelete: "cascade" }),
	2525	followingId: uuid("following_id")
	2526	.notNull()
	2527	.references(() => users.id, { onDelete: "cascade" }),
	2528	createdAt: timestamp("created_at").defaultNow().notNull(),
	2529	},
	2530	(table) => [
	2531	uniqueIndex("user_follows_pair_unique").on(
	2532	table.followerId,
	2533	table.followingId
	2534	),
	2535	index("user_follows_following_idx").on(table.followingId),
	2536	]
	2537	);
	2538
	2539	export type UserFollow = typeof userFollows.$inferSelect;
9ff7128Claude	2540
	2541	// ----------------------------------------------------------------------------
	2542	// Block J6 — Repository rulesets
	2543	// ----------------------------------------------------------------------------
	2544
	2545	/**
	2546	* A ruleset groups N rules under a named policy at enforcement level active /
	2547	* evaluate / disabled. Unique per (repo, name) so a repo can carry multiple
	2548	* overlapping rulesets (e.g. "release branches" vs "everywhere").
	2549	*/
	2550	export const repoRulesets = pgTable(
	2551	"repo_rulesets",
	2552	{
	2553	id: uuid("id").primaryKey().defaultRandom(),
	2554	repositoryId: uuid("repository_id")
	2555	.notNull()
	2556	.references(() => repositories.id, { onDelete: "cascade" }),
	2557	name: text("name").notNull(),
	2558	enforcement: text("enforcement").default("active").notNull(),
	2559	createdBy: uuid("created_by").references(() => users.id, {
	2560	onDelete: "set null",
	2561	}),
	2562	createdAt: timestamp("created_at").defaultNow().notNull(),
	2563	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2564	},
	2565	(table) => [
	2566	index("repo_rulesets_repo_idx").on(table.repositoryId),
	2567	uniqueIndex("repo_rulesets_repo_name_unique").on(
	2568	table.repositoryId,
	2569	table.name
	2570	),
	2571	]
	2572	);
	2573
	2574	export type RepoRuleset = typeof repoRulesets.$inferSelect;
	2575
	2576	/** Individual rule — type tag plus JSON params. */
	2577	export const rulesetRules = pgTable(
	2578	"ruleset_rules",
	2579	{
	2580	id: uuid("id").primaryKey().defaultRandom(),
	2581	rulesetId: uuid("ruleset_id")
	2582	.notNull()
	2583	.references(() => repoRulesets.id, { onDelete: "cascade" }),
	2584	ruleType: text("rule_type").notNull(),
	2585	params: text("params").default("{}").notNull(),
	2586	createdAt: timestamp("created_at").defaultNow().notNull(),
	2587	},
	2588	(table) => [index("ruleset_rules_set_idx").on(table.rulesetId)]
	2589	);
	2590
	2591	export type RulesetRule = typeof rulesetRules.$inferSelect;
0cdfd89Claude	2592
	2593	// ---------------------------------------------------------------------------
	2594	// Block J8 — Commit statuses.
	2595	// ---------------------------------------------------------------------------
	2596
	2597	/**
	2598	* External CI / automation posts per-commit (sha, context) statuses. Upsert
	2599	* semantics keyed on (repository, commit_sha, context). State vocabulary:
	2600	* pending \| success \| failure \| error. Combined rollup logic lives in
	2601	* `src/lib/commit-statuses.ts`.
	2602	*/
	2603	export const commitStatuses = pgTable(
	2604	"commit_statuses",
	2605	{
	2606	id: uuid("id").primaryKey().defaultRandom(),
	2607	repositoryId: uuid("repository_id")
	2608	.notNull()
	2609	.references(() => repositories.id, { onDelete: "cascade" }),
	2610	commitSha: text("commit_sha").notNull(),
	2611	state: text("state").notNull(),
	2612	context: text("context").default("default").notNull(),
	2613	description: text("description"),
	2614	targetUrl: text("target_url"),
	2615	creatorId: uuid("creator_id").references(() => users.id, {
	2616	onDelete: "set null",
	2617	}),
	2618	createdAt: timestamp("created_at").defaultNow().notNull(),
	2619	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2620	},
	2621	(table) => [
	2622	uniqueIndex("commit_statuses_repo_sha_context_unique").on(
	2623	table.repositoryId,
	2624	table.commitSha,
	2625	table.context
	2626	),
	2627	index("commit_statuses_repo_sha_idx").on(
	2628	table.repositoryId,
	2629	table.commitSha
	2630	),
	2631	]
	2632	);
	2633
	2634	export type CommitStatus = typeof commitStatuses.$inferSelect;
23d1a81Claude	2635
	2636	// ---------------------------------------------------------------------------
	2637	// Collaborators — per-repo role grants (read / write / admin).
	2638	// ---------------------------------------------------------------------------
	2639
	2640	/**
	2641	* A user granted access to a repository beyond ownership. Roles are
	2642	* hierarchical — 'admin' implies write, write implies read. `invitedBy` tracks
	2643	* who added them (nullable once that user is deleted); `acceptedAt` is null
	2644	* until the invitee explicitly accepts. Unique per (repo, user) so a given
	2645	* user has at most one role per repo.
	2646	*/
	2647	export const repoCollaborators = pgTable(
	2648	"repo_collaborators",
	2649	{
	2650	id: uuid("id").primaryKey().defaultRandom(),
	2651	repositoryId: uuid("repository_id")
	2652	.notNull()
	2653	.references(() => repositories.id, { onDelete: "cascade" }),
	2654	userId: uuid("user_id")
	2655	.notNull()
	2656	.references(() => users.id, { onDelete: "cascade" }),
	2657	role: text("role", { enum: ["read", "write", "admin"] })
	2658	.notNull()
	2659	.default("read"),
	2660	invitedBy: uuid("invited_by").references(() => users.id, {
	2661	onDelete: "set null",
	2662	}),
	2663	invitedAt: timestamp("invited_at").defaultNow().notNull(),
	2664	acceptedAt: timestamp("accepted_at"),
febd4f0Claude	2665	// sha256(plaintext) of the outstanding invite token. Set when the owner
	2666	// sends an invite, cleared when the invitee accepts. NULL on older rows
	2667	// that were auto-accepted before the email flow existed.
	2668	inviteTokenHash: text("invite_token_hash"),
23d1a81Claude	2669	},
	2670	(table) => [
	2671	uniqueIndex("repo_collaborators_repo_user_uq").on(
	2672	table.repositoryId,
	2673	table.userId
	2674	),
	2675	index("repo_collaborators_repo_idx").on(table.repositoryId),
	2676	index("repo_collaborators_user_idx").on(table.userId),
	2677	]
	2678	);
	2679
	2680	export type RepoCollaborator = typeof repoCollaborators.$inferSelect;
abfa9adClaude	2681
	2682	// ---------------------------------------------------------------------------
	2683	// Workflow engine v2 — Sprint 1 additions (drizzle/0037_workflow_engine_v2.sql)
	2684	//
	2685	// Strictly additive to Block C1. The original workflow tables defined above
	2686	// (workflows / workflow_runs / workflow_jobs / workflow_artifacts) are locked
	2687	// and must not be altered in-place; the four tables below extend the runner
	2688	// with secrets, workflow_dispatch inputs, a content-addressable cache, and a
	2689	// warm-runner worker pool.
	2690	// ---------------------------------------------------------------------------
	2691
	2692	/**
	2693	* Per-repo encrypted secrets exposed to workflow jobs as env vars.
	2694	*
	2695	* `encrypted_value` is base64 of `iv \|\| authTag \|\| ciphertext` produced by
	2696	* AES-256-GCM — the crypto lives in `src/lib/workflow-crypto.ts` (Agent 2);
	2697	* the DB only stores opaque ciphertext. `name` is validated against
	2698	* `[A-Z_][A-Z0-9_]*` at the write-site, not the DB. Unique per (repo, name).
	2699	*/
	2700	export const workflowSecrets = pgTable(
	2701	"workflow_secrets",
	2702	{
	2703	id: uuid("id").primaryKey().defaultRandom(),
	2704	repositoryId: uuid("repository_id")
	2705	.notNull()
	2706	.references(() => repositories.id, { onDelete: "cascade" }),
	2707	name: text("name").notNull(),
	2708	encryptedValue: text("encrypted_value").notNull(),
	2709	createdBy: uuid("created_by").references(() => users.id, {
	2710	onDelete: "set null",
	2711	}),
	2712	createdAt: timestamp("created_at").defaultNow().notNull(),
	2713	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	2714	},
	2715	(table) => [
	2716	uniqueIndex("workflow_secrets_repo_name_uq").on(
	2717	table.repositoryId,
	2718	table.name
	2719	),
	2720	index("workflow_secrets_repo_idx").on(table.repositoryId),
	2721	]
	2722	);
	2723
	2724	export type WorkflowSecret = typeof workflowSecrets.$inferSelect;
	2725	export type NewWorkflowSecret = typeof workflowSecrets.$inferInsert;
	2726
	2727	/**
	2728	* Parameter schema for the `workflow_dispatch` trigger. One row per input
	2729	* declared in the workflow YAML. `type` is constrained to the four values
	2730	* GitHub Actions supports; `options` is only meaningful for type='choice'
	2731	* (a JSON array of allowed strings). `default_value` and `description` are
	2732	* optional. Unique per (workflow, name) so two inputs on the same workflow
	2733	* can't share an identifier.
	2734	*/
	2735	export const workflowDispatchInputs = pgTable(
	2736	"workflow_dispatch_inputs",
	2737	{
	2738	id: uuid("id").primaryKey().defaultRandom(),
	2739	workflowId: uuid("workflow_id")
	2740	.notNull()
	2741	.references(() => workflows.id, { onDelete: "cascade" }),
	2742	name: text("name").notNull(),
	2743	type: text("type", {
	2744	enum: ["string", "boolean", "choice", "number"],
	2745	}).notNull(),
	2746	required: boolean("required").default(false).notNull(),
	2747	defaultValue: text("default_value"),
	2748	// JSON array of strings; only populated when type = 'choice'.
	2749	options: jsonb("options"),
	2750	description: text("description"),
	2751	},
	2752	(table) => [
	2753	uniqueIndex("workflow_dispatch_inputs_wf_name_uq").on(
	2754	table.workflowId,
	2755	table.name
	2756	),
	2757	]
	2758	);
	2759
	2760	export type WorkflowDispatchInput =
	2761	typeof workflowDispatchInputs.$inferSelect;
	2762	export type NewWorkflowDispatchInput =
	2763	typeof workflowDispatchInputs.$inferInsert;
	2764
	2765	/**
	2766	* Content-addressable cache backing the `gluecron/cache@v1` action. Rows are
	2767	* keyed by user-chosen `cache_key` within a `scope` — 'repo' for repo-wide
	2768	* entries, 'branch' or 'tag' when isolation is desired (with `scope_ref`
	2769	* holding the branch/tag name). `content_hash` is the sha256 of `content`
	2770	* so jobs can short-circuit redundant writes. `content` is real bytea; the
	2771	* 100MB payload cap is enforced at the write-site, not by the DB. LRU
	2772	* eviction reads (`repository_id`, `last_accessed_at`).
	2773	*/
	2774	export const workflowRunCache = pgTable(
	2775	"workflow_run_cache",
	2776	{
	2777	id: uuid("id").primaryKey().defaultRandom(),
	2778	repositoryId: uuid("repository_id")
	2779	.notNull()
	2780	.references(() => repositories.id, { onDelete: "cascade" }),
	2781	cacheKey: text("cache_key").notNull(),
	2782	// 'repo' \| 'branch' \| 'tag' — constraint enforced at the write-site.
	2783	scope: text("scope").default("repo").notNull(),
	2784	scopeRef: text("scope_ref"),
	2785	contentHash: text("content_hash").notNull(),
	2786	content: bytea("content").notNull(),
	2787	sizeBytes: bigint("size_bytes", { mode: "number" }).notNull(),
	2788	createdAt: timestamp("created_at").defaultNow().notNull(),
	2789	lastAccessedAt: timestamp("last_accessed_at").defaultNow().notNull(),
	2790	},
	2791	(table) => [
	2792	uniqueIndex("workflow_run_cache_repo_key_scope_uq").on(
	2793	table.repositoryId,
	2794	table.cacheKey,
	2795	table.scope,
	2796	table.scopeRef
	2797	),
	2798	index("workflow_run_cache_repo_lru_idx").on(
	2799	table.repositoryId,
	2800	table.lastAccessedAt
	2801	),
	2802	]
	2803	);
	2804
	2805	export type WorkflowRunCache = typeof workflowRunCache.$inferSelect;
	2806	export type NewWorkflowRunCache = typeof workflowRunCache.$inferInsert;
	2807
	2808	/**
	2809	* Warm-runner worker registry. The scheduler reads idle rows to dispatch
	2810	* queued jobs without paying cold-start cost; workers heartbeat here so
	2811	* stale entries (>N seconds since `last_heartbeat_at`) can be reaped.
	2812	* `worker_id` is a stable string (hostname:pid or similar) and is globally
	2813	* unique so a restarted worker naturally replaces its predecessor.
	2814	* `current_run_id` is set when status='busy' and cleared on completion.
	2815	*/
	2816	export const workflowRunnerPool = pgTable(
	2817	"workflow_runner_pool",
	2818	{
	2819	id: uuid("id").primaryKey().defaultRandom(),
	2820	workerId: text("worker_id").notNull().unique(),
	2821	status: text("status", {
	2822	enum: ["idle", "busy", "draining", "dead"],
	2823	}).notNull(),
	2824	currentRunId: uuid("current_run_id").references(() => workflowRuns.id, {
	2825	onDelete: "set null",
	2826	}),
	2827	warmedAt: timestamp("warmed_at").defaultNow().notNull(),
	2828	lastHeartbeatAt: timestamp("last_heartbeat_at").defaultNow().notNull(),
	2829	capacity: integer("capacity").default(1).notNull(),
	2830	},
	2831	(table) => [index("workflow_runner_pool_status_idx").on(table.status)]
	2832	);
	2833
	2834	export type WorkflowRunnerPoolEntry =
	2835	typeof workflowRunnerPool.$inferSelect;
	2836	export type NewWorkflowRunnerPoolEntry =
	2837	typeof workflowRunnerPool.$inferInsert;
e6bad81Claude	2838
	2839
	2840	/**
	2841	* Repair Flywheel — every auto-repair attempt is recorded here so future
	2842	* failures with the same signature can short-circuit straight to the cached
	2843	* patch (Tier 0). After ~5000 entries the cache dominates and AI calls drop.
	2844	* Migration: 0039_repair_flywheel.sql
	2845	*/
	2846	export const repairFlywheel = pgTable(
	2847	"repair_flywheel",
	2848	{
	2849	id: uuid("id").primaryKey().defaultRandom(),
	2850	repositoryId: uuid("repository_id").references(() => repositories.id, {
	2851	onDelete: "cascade",
	2852	}),
	2853	// Fingerprint of the normalised failure text (variables/paths stripped).
	2854	failureSignature: text("failure_signature").notNull(),
	2855	// Original failure text (capped at write-site, ~4KB).
	2856	failureText: text("failure_text").notNull(),
	2857	// Mechanical classification or NULL if AI/human-driven.
	2858	failureClassification: text("failure_classification"),
	2859	// 'cached' \| 'mechanical' \| 'ai-sonnet' \| 'human'
	2860	repairTier: text("repair_tier").notNull(),
	2861	patchSummary: text("patch_summary").notNull(),
	2862	filesChanged: jsonb("files_changed").notNull().default([]),
	2863	commitSha: text("commit_sha"),
	2864	// 'pending' \| 'success' \| 'failed' \| 'reverted'
	2865	outcome: text("outcome").notNull().default("pending"),
	2866	appliedAt: timestamp("applied_at").defaultNow().notNull(),
	2867	outcomeAt: timestamp("outcome_at"),
	2868	parentPatternId: uuid("parent_pattern_id"),
	2869	cacheHitCount: integer("cache_hit_count").default(0).notNull(),
	2870	isPublicPattern: boolean("is_public_pattern").default(false).notNull(),
	2871	createdAt: timestamp("created_at").defaultNow().notNull(),
	2872	},
	2873	(table) => [
	2874	index("repair_flywheel_signature_idx").on(table.failureSignature),
	2875	index("repair_flywheel_repo_idx").on(table.repositoryId),
	2876	index("repair_flywheel_outcome_idx").on(table.outcome),
	2877	index("repair_flywheel_classification_idx").on(table.failureClassification),
	2878	index("repair_flywheel_lookup_idx").on(
	2879	table.repositoryId,
	2880	table.failureSignature,
	2881	table.outcome
	2882	),
	2883	]
	2884	);
	2885
	2886	export type RepairFlywheelEntry = typeof repairFlywheel.$inferSelect;
	2887	export type NewRepairFlywheelEntry = typeof repairFlywheel.$inferInsert;
	2888
534f04aClaude	2889	/**
	2890	* Block M3 — AI pre-merge risk score cache.
	2891	*
	2892	* One row per (pull_request_id, commit_sha). The score is computed by a
	2893	* transparent formula over `signals`; the LLM only writes `ai_summary`.
	2894	* Pinning by head SHA means a new push invalidates the cache implicitly
	2895	* (the new SHA won't have a row yet → cache miss → recompute).
	2896	*
	2897	* Migration: 0044_pr_risk_scores.sql
	2898	*/
	2899	export const prRiskScores = pgTable(
	2900	"pr_risk_scores",
	2901	{
	2902	id: uuid("id").primaryKey().defaultRandom(),
	2903	pullRequestId: uuid("pull_request_id")
	2904	.notNull()
	2905	.references(() => pullRequests.id, { onDelete: "cascade" }),
	2906	commitSha: text("commit_sha").notNull(),
	2907	// 0..10 integer score.
	2908	score: integer("score").notNull(),
	2909	// 'low' \| 'medium' \| 'high' \| 'critical'
	2910	band: text("band").notNull(),
	2911	// Raw signal map — see PrRiskSignals in src/lib/pr-risk.ts.
	2912	signals: jsonb("signals").notNull(),
	2913	aiSummary: text("ai_summary"),
	2914	generatedAt: timestamp("generated_at", { withTimezone: true })
	2915	.defaultNow()
	2916	.notNull(),
	2917	},
	2918	(table) => [
	2919	uniqueIndex("pr_risk_scores_pr_sha_uq").on(
	2920	table.pullRequestId,
	2921	table.commitSha
	2922	),
	2923	index("pr_risk_scores_pr_idx").on(table.pullRequestId),
	2924	]
	2925	);
	2926
	2927	export type PrRiskScoreRow = typeof prRiskScores.$inferSelect;
	2928	export type NewPrRiskScoreRow = typeof prRiskScores.$inferInsert;
	2929
c63b860Claude	2930	/**
	2931	* Block P1 — Password reset tokens.
	2932	*
	2933	* One row per outstanding reset request. `tokenHash` is a SHA-256 of the
	2934	* plaintext token mailed to the user; the plaintext never persists.
	2935	* `usedAt` is flipped on consume so a replayed link cannot rotate the
	2936	* password twice. `expiresAt` is enforced at consume time (1-hour TTL).
	2937	*
	2938	* Migration: 0047_password_reset_tokens.sql
	2939	*/
	2940	export const passwordResetTokens = pgTable(
	2941	"password_reset_tokens",
	2942	{
	2943	id: uuid("id").primaryKey().defaultRandom(),
	2944	userId: uuid("user_id")
	2945	.notNull()
	2946	.references(() => users.id, { onDelete: "cascade" }),
	2947	tokenHash: text("token_hash").notNull().unique(),
	2948	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	2949	usedAt: timestamp("used_at", { withTimezone: true }),
	2950	requestIp: text("request_ip"),
	2951	createdAt: timestamp("created_at", { withTimezone: true })
	2952	.defaultNow()
	2953	.notNull(),
	2954	},
	2955	(table) => [
	2956	index("idx_password_reset_tokens_user").on(table.userId),
	2957	index("idx_password_reset_tokens_expires").on(table.expiresAt),
	2958	]
	2959	);
	2960
	2961	export type PasswordResetToken = typeof passwordResetTokens.$inferSelect;
	2962	export type NewPasswordResetToken = typeof passwordResetTokens.$inferInsert;
	2963
	2964	/**
	2965	* Block P2 — email verification tokens.
	2966	*
	2967	* SHA-256 hashed at rest. `email` captured at issuance so a verification
	2968	* link still resolves the right address even after a profile-email change.
	2969	* Migration: drizzle/0048_email_verification.sql
	2970	*/
	2971	export const emailVerificationTokens = pgTable(
	2972	"email_verification_tokens",
	2973	{
	2974	id: uuid("id").primaryKey().defaultRandom(),
	2975	userId: uuid("user_id")
	2976	.notNull()
	2977	.references(() => users.id, { onDelete: "cascade" }),
	2978	email: text("email").notNull(),
	2979	tokenHash: text("token_hash").notNull().unique(),
	2980	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	2981	usedAt: timestamp("used_at", { withTimezone: true }),
	2982	createdAt: timestamp("created_at", { withTimezone: true })
	2983	.defaultNow()
	2984	.notNull(),
	2985	},
	2986	(table) => [index("idx_email_verify_tokens_user").on(table.userId)]
	2987	);
	2988
	2989	export type EmailVerificationToken = typeof emailVerificationTokens.$inferSelect;
	2990	export type NewEmailVerificationToken = typeof emailVerificationTokens.$inferInsert;
	2991
cd4f63bTest User	2992	/**
	2993	* Block Q2 — Magic-link sign-in tokens.
	2994	*
	2995	* Structurally identical to passwordResetTokens / emailVerificationTokens:
	2996	* a short plaintext token is mailed to the user, only its sha256 hash is
	2997	* persisted, the token is single-use (usedAt) and time-limited (expiresAt).
	2998	*
	2999	* Difference: `userId` is NULLABLE. When a user enters an email that does
	3000	* NOT yet have an account, we still mint a row — consume will create the
	3001	* account on click (autoCreate=true), using the click itself as proof the
	3002	* recipient owns the address. See `src/lib/magic-link.ts`.
	3003	*
	3004	* Migration: drizzle/0051_magic_link_tokens.sql
	3005	*/
	3006	export const magicLinkTokens = pgTable(
	3007	"magic_link_tokens",
	3008	{
	3009	id: uuid("id").primaryKey().defaultRandom(),
	3010	email: text("email").notNull(),
	3011	userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
	3012	tokenHash: text("token_hash").notNull().unique(),
	3013	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	3014	usedAt: timestamp("used_at", { withTimezone: true }),
	3015	requestIp: text("request_ip"),
	3016	createdAt: timestamp("created_at", { withTimezone: true })
	3017	.defaultNow()
	3018	.notNull(),
	3019	},
	3020	(table) => [
	3021	index("idx_magic_link_tokens_email").on(table.email),
	3022	index("idx_magic_link_tokens_user").on(table.userId),
	3023	index("idx_magic_link_tokens_expires").on(table.expiresAt),
	3024	]
	3025	);
	3026
	3027	export type MagicLinkToken = typeof magicLinkTokens.$inferSelect;
	3028	export type NewMagicLinkToken = typeof magicLinkTokens.$inferInsert;
	3029
b1be050CC LABS App	3030	// ---------------------------------------------------------------------------
	3031	// BLOCK S4 — Synthetic monitor history.
	3032	//
	3033	// Every autopilot tick runs `runSyntheticChecks()` (see
	3034	// `src/lib/synthetic-monitor.ts`) and inserts one row per check here.
	3035	// The /admin/status page reads the most-recent row per check_name and
	3036	// renders the red/green dashboard. On a green->red transition we fire
	3037	// MONITOR_ALERT_WEBHOOK_URL so the owner sees the site is on fire.
	3038	// ---------------------------------------------------------------------------
	3039	export const syntheticChecks = pgTable(
	3040	"synthetic_checks",
	3041	{
	3042	id: uuid("id").primaryKey().defaultRandom(),
	3043	checkName: text("check_name").notNull(),
	3044	status: text("status").notNull(), // "green" \| "red" \| "yellow"
	3045	statusCode: integer("status_code"),
	3046	durationMs: integer("duration_ms").notNull(),
	3047	error: text("error"),
	3048	checkedAt: timestamp("checked_at", { withTimezone: true })
	3049	.defaultNow()
	3050	.notNull(),
	3051	},
	3052	(table) => [
	3053	index("idx_synthetic_checks_checked_at").on(table.checkedAt),
	3054	index("idx_synthetic_checks_name_checked_at").on(
	3055	table.checkName,
	3056	table.checkedAt
	3057	),
	3058	]
	3059	);
	3060
	3061	export type SyntheticCheckRow = typeof syntheticChecks.$inferSelect;
	3062	export type NewSyntheticCheckRow = typeof syntheticChecks.$inferInsert;
	3063
a686079Claude	3064	// ---------------------------------------------------------------------------
	3065	// 0057 — Continuous semantic index (per-push embeddings).
e75eddcClaude	3066	// One row per (repository_id, file_path). `indexChangedFiles()` upserts on
	3067	// every push, `searchSemantic()` ranks by cosine distance via pgvector.
a686079Claude	3068	// ---------------------------------------------------------------------------
	3069	export const codeEmbeddings = pgTable(
	3070	"code_embeddings",
	3071	{
	3072	id: uuid("id").primaryKey().defaultRandom(),
	3073	repositoryId: uuid("repository_id")
	3074	.notNull()
	3075	.references(() => repositories.id, { onDelete: "cascade" }),
	3076	filePath: text("file_path").notNull(),
	3077	blobSha: text("blob_sha").notNull(),
	3078	commitSha: text("commit_sha").notNull(),
	3079	contentSnippet: text("content_snippet").notNull().default(""),
	3080	embedding: vector("embedding", { dimensions: 1024 }),
	3081	embeddingModel: text("embedding_model"),
	3082	updatedAt: timestamp("updated_at", { withTimezone: true })
	3083	.defaultNow()
	3084	.notNull(),
	3085	},
	3086	(table) => [
	3087	uniqueIndex("code_embeddings_repo_path_uniq").on(
	3088	table.repositoryId,
	3089	table.filePath
	3090	),
	3091	index("code_embeddings_repo_idx").on(table.repositoryId),
	3092	]
	3093	);
	3094
	3095	export type CodeEmbedding = typeof codeEmbeddings.$inferSelect;
	3096	export type NewCodeEmbedding = typeof codeEmbeddings.$inferInsert;
	3097
e75eddcClaude	3098	// ---------------------------------------------------------------------------
	3099	// 0058 — Agent multiplayer v1: per-agent namespacing + leases + budget caps.
	3100	// See src/lib/agent-multiplayer.ts for helpers and 0058_agent_multiplayer.sql
	3101	// for the canonical column docs. UNIQUE partial index on (target_type,
	3102	// target_id) WHERE status='active' is enforced in SQL (drizzle exposes it
	3103	// as a regular index here).
	3104	// ---------------------------------------------------------------------------
	3105	export const agentSessions = pgTable(
	3106	"agent_sessions",
	3107	{
	3108	id: uuid("id").primaryKey().defaultRandom(),
	3109	name: text("name").notNull(),
	3110	ownerUserId: uuid("owner_user_id")
	3111	.notNull()
	3112	.references(() => users.id, { onDelete: "cascade" }),
	3113	repositoryId: uuid("repository_id").references(() => repositories.id, {
	3114	onDelete: "cascade",
	3115	}),
	3116	tokenHash: text("token_hash").notNull().unique(),
	3117	branchNamespace: text("branch_namespace").notNull(),
	3118	budgetCentsPerDay: integer("budget_cents_per_day").default(500).notNull(),
	3119	spentCentsToday: integer("spent_cents_today").default(0).notNull(),
	3120	lastActiveAt: timestamp("last_active_at", { withTimezone: true }),
	3121	createdAt: timestamp("created_at", { withTimezone: true })
	3122	.defaultNow()
	3123	.notNull(),
	3124	},
	3125	(table) => [
	3126	uniqueIndex("agent_sessions_owner_name").on(table.ownerUserId, table.name),
	3127	index("agent_sessions_owner").on(table.ownerUserId),
	3128	index("agent_sessions_repo").on(table.repositoryId),
	3129	]
	3130	);
	3131
	3132	export const agentLeases = pgTable(
	3133	"agent_leases",
	3134	{
	3135	id: uuid("id").primaryKey().defaultRandom(),
	3136	agentSessionId: uuid("agent_session_id")
	3137	.notNull()
	3138	.references(() => agentSessions.id, { onDelete: "cascade" }),
	3139	targetType: text("target_type").notNull(),
	3140	targetId: text("target_id").notNull(),
	3141	acquiredAt: timestamp("acquired_at", { withTimezone: true })
	3142	.defaultNow()
	3143	.notNull(),
	3144	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	3145	status: text("status").default("active").notNull(),
	3146	createdAt: timestamp("created_at", { withTimezone: true })
	3147	.defaultNow()
	3148	.notNull(),
	3149	},
	3150	(table) => [
	3151	index("agent_leases_active_target").on(table.targetType, table.targetId),
	3152	index("agent_leases_agent").on(table.agentSessionId, table.status),
	3153	index("agent_leases_expires").on(table.expiresAt),
	3154	]
	3155	);
	3156
	3157	export type AgentSession = typeof agentSessions.$inferSelect;
	3158	export type NewAgentSession = typeof agentSessions.$inferInsert;
	3159	export type AgentLease = typeof agentLeases.$inferSelect;
	3160	export type NewAgentLease = typeof agentLeases.$inferInsert;
	3161
38d31d3Claude	3162	// ---------------------------------------------------------------------------
3c03977Claude	3163	// 0060 — AI repo rubber-duck chat. See src/lib/repo-chat.ts.
38d31d3Claude	3164	// ---------------------------------------------------------------------------
	3165	export const repoChats = pgTable(
	3166	"repo_chats",
	3167	{
	3168	id: uuid("id").primaryKey().defaultRandom(),
	3169	repositoryId: uuid("repository_id")
	3170	.notNull()
	3171	.references(() => repositories.id, { onDelete: "cascade" }),
	3172	ownerUserId: uuid("owner_user_id")
	3173	.notNull()
	3174	.references(() => users.id, { onDelete: "cascade" }),
	3175	title: text("title"),
	3176	createdAt: timestamp("created_at", { withTimezone: true })
	3177	.defaultNow()
	3178	.notNull(),
	3179	updatedAt: timestamp("updated_at", { withTimezone: true })
	3180	.defaultNow()
	3181	.notNull(),
	3182	},
	3183	(table) => [
	3184	index("repo_chats_owner_updated").on(table.ownerUserId, table.updatedAt),
	3185	index("repo_chats_repo").on(table.repositoryId),
	3186	]
	3187	);
	3188
	3189	export const repoChatMessages = pgTable(
	3190	"repo_chat_messages",
	3191	{
	3192	id: uuid("id").primaryKey().defaultRandom(),
	3193	chatId: uuid("chat_id")
	3194	.notNull()
	3195	.references(() => repoChats.id, { onDelete: "cascade" }),
	3196	role: text("role").notNull(),
	3197	content: text("content").notNull(),
	3198	citations: jsonb("citations").$type<Array<{ file_path: string; blob_sha: string }>>().notNull().default([]),
	3199	tokenCost: integer("token_cost").notNull().default(0),
	3200	createdAt: timestamp("created_at", { withTimezone: true })
	3201	.defaultNow()
	3202	.notNull(),
	3203	},
	3204	(table) => [
	3205	index("repo_chat_messages_chat_created").on(table.chatId, table.createdAt),
	3206	]
	3207	);
	3208
	3209	export type RepoChat = typeof repoChats.$inferSelect;
	3210	export type NewRepoChat = typeof repoChats.$inferInsert;
	3211	export type RepoChatMessage = typeof repoChatMessages.$inferSelect;
	3212	export type NewRepoChatMessage = typeof repoChatMessages.$inferInsert;
	3213
ee7e577Claude	3214	// ---------------------------------------------------------------------------
	3215	// 0071 — Personal cross-repo chat. Same shape as repoChats/repoChatMessages
	3216	// but user-scoped, not repo-scoped. The retrieval layer
	3217	// (src/lib/personal-semantic.ts) runs searchSemantic across the union of
	3218	// repos the owner has access to, then attaches a repo_name annotation to
	3219	// each citation. Gated on users.personalSemanticIndexEnabled.
	3220	// ---------------------------------------------------------------------------
	3221	export const personalChats = pgTable(
	3222	"personal_chats",
	3223	{
	3224	id: uuid("id").primaryKey().defaultRandom(),
	3225	ownerUserId: uuid("owner_user_id")
	3226	.notNull()
	3227	.references(() => users.id, { onDelete: "cascade" }),
	3228	title: text("title"),
	3229	createdAt: timestamp("created_at", { withTimezone: true })
	3230	.defaultNow()
	3231	.notNull(),
	3232	updatedAt: timestamp("updated_at", { withTimezone: true })
	3233	.defaultNow()
	3234	.notNull(),
	3235	},
	3236	(table) => [
	3237	index("personal_chats_owner_updated").on(
	3238	table.ownerUserId,
	3239	table.updatedAt
	3240	),
	3241	]
	3242	);
	3243
	3244	export const personalChatMessages = pgTable(
	3245	"personal_chat_messages",
	3246	{
	3247	id: uuid("id").primaryKey().defaultRandom(),
	3248	chatId: uuid("chat_id")
	3249	.notNull()
	3250	.references(() => personalChats.id, { onDelete: "cascade" }),
	3251	role: text("role").notNull(),
	3252	content: text("content").notNull(),
	3253	citations: jsonb("citations")
	3254	.$type<
	3255	Array<{ file_path: string; blob_sha: string; repo_name: string }>
	3256	>()
	3257	.notNull()
	3258	.default([]),
	3259	tokenCost: integer("token_cost").notNull().default(0),
	3260	createdAt: timestamp("created_at", { withTimezone: true })
	3261	.defaultNow()
	3262	.notNull(),
	3263	},
	3264	(table) => [
	3265	index("personal_chat_messages_chat_created").on(
	3266	table.chatId,
	3267	table.createdAt
	3268	),
	3269	]
	3270	);
	3271
	3272	export type PersonalChat = typeof personalChats.$inferSelect;
	3273	export type NewPersonalChat = typeof personalChats.$inferInsert;
	3274	export type PersonalChatMessage = typeof personalChatMessages.$inferSelect;
	3275	export type NewPersonalChatMessage = typeof personalChatMessages.$inferInsert;
	3276
3c03977Claude	3277	// ---------------------------------------------------------------------------
	3278	// 0061 — Live co-editing on PRs. See src/lib/pr-live.ts.
	3279	// ---------------------------------------------------------------------------
	3280	export const prLiveSessions = pgTable(
	3281	"pr_live_sessions",
	3282	{
	3283	id: uuid("id").primaryKey().defaultRandom(),
	3284	prId: uuid("pr_id")
	3285	.notNull()
	3286	.references(() => pullRequests.id, { onDelete: "cascade" }),
	3287	userId: uuid("user_id").references(() => users.id, {
	3288	onDelete: "cascade",
	3289	}),
	3290	agentSessionId: uuid("agent_session_id").references(
	3291	() => agentSessions.id,
	3292	{ onDelete: "cascade" }
	3293	),
	3294	cursorPosition: jsonb("cursor_position"),
	3295	color: text("color").notNull(),
	3296	joinedAt: timestamp("joined_at", { withTimezone: true })
	3297	.defaultNow()
	3298	.notNull(),
	3299	lastSeenAt: timestamp("last_seen_at", { withTimezone: true })
	3300	.defaultNow()
	3301	.notNull(),
	3302	status: text("status").default("active").notNull(),
	3303	},
	3304	(table) => [
	3305	index("pr_live_sessions_active_pr").on(table.prId, table.lastSeenAt),
	3306	index("pr_live_sessions_status_seen").on(table.status, table.lastSeenAt),
	3307	]
	3308	);
	3309
	3310	export type PrLiveSession = typeof prLiveSessions.$inferSelect;
	3311	export type NewPrLiveSession = typeof prLiveSessions.$inferInsert;
	3312
4bbacbeClaude	3313	// ---------------------------------------------------------------------------
	3314	// ---------------------------------------------------------------------------
23d0abfClaude	3315	// Migration 0062 — per-branch preview URLs. See src/lib/branch-previews.ts.
	3316	// ---------------------------------------------------------------------------
4bbacbeClaude	3317	export const branchPreviews = pgTable(
	3318	"branch_previews",
	3319	{
	3320	id: uuid("id").primaryKey().defaultRandom(),
	3321	repositoryId: uuid("repository_id")
	3322	.notNull()
	3323	.references(() => repositories.id, { onDelete: "cascade" }),
	3324	branchName: text("branch_name").notNull(),
	3325	commitSha: text("commit_sha").notNull(),
	3326	previewUrl: text("preview_url").notNull(),
	3327	status: text("status").default("building").notNull(),
	3328	buildStartedAt: timestamp("build_started_at", { withTimezone: true })
	3329	.defaultNow()
	3330	.notNull(),
	3331	buildCompletedAt: timestamp("build_completed_at", { withTimezone: true }),
	3332	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	3333	errorMessage: text("error_message"),
	3334	createdAt: timestamp("created_at", { withTimezone: true })
	3335	.defaultNow()
	3336	.notNull(),
	3337	},
	3338	(table) => [
	3339	uniqueIndex("branch_previews_repo_branch").on(
	3340	table.repositoryId,
	3341	table.branchName
	3342	),
	3343	index("branch_previews_repo_status").on(table.repositoryId, table.status),
	3344	]
	3345	);
	3346
	3347	export type BranchPreview = typeof branchPreviews.$inferSelect;
	3348	export type NewBranchPreview = typeof branchPreviews.$inferInsert;
	3349
23d0abfClaude	3350	// ---------------------------------------------------------------------------
	3351	// 0063 — Multi-repo refactor agent. See src/lib/multi-repo-refactor.ts.
	3352	// ---------------------------------------------------------------------------
	3353	export const multiRepoRefactors = pgTable(
	3354	"multi_repo_refactors",
	3355	{
	3356	id: uuid("id").primaryKey().defaultRandom(),
	3357	ownerUserId: uuid("owner_user_id")
	3358	.notNull()
	3359	.references(() => users.id, { onDelete: "cascade" }),
	3360	title: text("title").notNull(),
	3361	description: text("description").notNull(),
	3362	status: text("status").default("planning").notNull(),
	3363	createdAt: timestamp("created_at").defaultNow().notNull(),
	3364	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	3365	},
	3366	(table) => [
	3367	index("multi_repo_refactors_owner").on(table.ownerUserId, table.createdAt),
	3368	index("multi_repo_refactors_status").on(table.status, table.updatedAt),
	3369	]
	3370	);
	3371
	3372	export const multiRepoRefactorPrs = pgTable(
	3373	"multi_repo_refactor_prs",
	3374	{
	3375	id: uuid("id").primaryKey().defaultRandom(),
	3376	refactorId: uuid("refactor_id")
	3377	.notNull()
	3378	.references(() => multiRepoRefactors.id, { onDelete: "cascade" }),
	3379	repositoryId: uuid("repository_id")
	3380	.notNull()
	3381	.references(() => repositories.id, { onDelete: "cascade" }),
	3382	pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
	3383	onDelete: "set null",
	3384	}),
	3385	status: text("status").default("pending").notNull(),
	3386	errorMessage: text("error_message"),
	3387	createdAt: timestamp("created_at").defaultNow().notNull(),
	3388	updatedAt: timestamp("updated_at").defaultNow().notNull(),
	3389	},
	3390	(table) => [
	3391	uniqueIndex("multi_repo_refactor_prs_unique").on(
	3392	table.refactorId,
	3393	table.repositoryId
	3394	),
	3395	index("multi_repo_refactor_prs_refactor").on(
	3396	table.refactorId,
	3397	table.status
	3398	),
	3399	index("multi_repo_refactor_prs_repo").on(table.repositoryId),
	3400	]
	3401	);
	3402
	3403	export type MultiRepoRefactor = typeof multiRepoRefactors.$inferSelect;
	3404	export type NewMultiRepoRefactor = typeof multiRepoRefactors.$inferInsert;
	3405	export type MultiRepoRefactorPr = typeof multiRepoRefactorPrs.$inferSelect;
	3406	export type NewMultiRepoRefactorPr = typeof multiRepoRefactorPrs.$inferInsert;
	3407
e3c90cdClaude	3408	// ─── Chat integrations (migration 0064) ────────────────────────────────────
	3409	// Slack / Discord / Teams bindings. One row per (owner, kind, team, channel).
	3410	// `webhook_url` is the outbound Incoming-Webhook URL for posting back into
	3411	// the channel; `signing_secret` is the per-install verification key (HMAC for
	3412	// Slack, Ed25519 public key for Discord). See drizzle/0064_chat_integrations.sql
	3413	// for the full schema rationale.
	3414	export const chatIntegrations = pgTable(
	3415	"chat_integrations",
	3416	{
	3417	id: uuid("id").primaryKey().defaultRandom(),
	3418	ownerUserId: uuid("owner_user_id")
	3419	.notNull()
	3420	.references(() => users.id, { onDelete: "cascade" }),
	3421	kind: text("kind").notNull(), // 'slack' \| 'discord' \| 'teams'
	3422	teamId: text("team_id"),
	3423	channelId: text("channel_id"),
	3424	webhookUrl: text("webhook_url"),
	3425	signingSecret: text("signing_secret"),
	3426	enabled: boolean("enabled").default(true).notNull(),
	3427	createdAt: timestamp("created_at").defaultNow().notNull(),
	3428	lastUsedAt: timestamp("last_used_at"),
	3429	},
	3430	(table) => [
	3431	index("chat_integrations_owner").on(table.ownerUserId, table.kind),
	3432	]
	3433	);
	3434
	3435	export type ChatIntegration = typeof chatIntegrations.$inferSelect;
	3436	export type NewChatIntegration = typeof chatIntegrations.$inferInsert;
	3437
0c3eee5Claude	3438	// ---------------------------------------------------------------------------
	3439	// 0065 — Per-call AI cost tracking. See src/lib/ai-cost-tracker.ts and
	3440	// src/routes/billing-usage.tsx. Best-effort: insert failures must NEVER
	3441	// throw out of the Claude caller, so every call site wraps recordAiCost in
	3442	// try/catch. Cents are computed at insert time from a hardcoded pricing
	3443	// table so historical aggregates stay stable across price changes.
	3444	// ---------------------------------------------------------------------------
	3445	export const aiCostEvents = pgTable(
	3446	"ai_cost_events",
	3447	{
	3448	id: uuid("id").primaryKey().defaultRandom(),
	3449	occurredAt: timestamp("occurred_at", { withTimezone: true })
	3450	.defaultNow()
	3451	.notNull(),
	3452	ownerUserId: uuid("owner_user_id").references(() => users.id, {
	3453	onDelete: "set null",
	3454	}),
	3455	repositoryId: uuid("repository_id").references(() => repositories.id, {
	3456	onDelete: "set null",
	3457	}),
	3458	agentSessionId: uuid("agent_session_id").references(
	3459	() => agentSessions.id,
	3460	{ onDelete: "set null" }
	3461	),
	3462	model: text("model").notNull(),
	3463	inputTokens: integer("input_tokens").default(0).notNull(),
	3464	outputTokens: integer("output_tokens").default(0).notNull(),
	3465	centsEstimate: integer("cents_estimate").default(0).notNull(),
	3466	category: text("category").default("other").notNull(),
	3467	sourceId: text("source_id"),
	3468	sourceKind: text("source_kind"),
	3469	createdAt: timestamp("created_at", { withTimezone: true })
	3470	.defaultNow()
	3471	.notNull(),
	3472	},
	3473	(table) => [
	3474	index("ai_cost_events_owner_time").on(table.ownerUserId, table.occurredAt),
	3475	index("ai_cost_events_repo_time").on(table.repositoryId, table.occurredAt),
	3476	index("ai_cost_events_agent_time").on(
	3477	table.agentSessionId,
	3478	table.occurredAt
	3479	),
	3480	index("ai_cost_events_category_time").on(table.category, table.occurredAt),
	3481	]
	3482	);
	3483
	3484	export type AiCostEvent = typeof aiCostEvents.$inferSelect;
	3485	export type NewAiCostEvent = typeof aiCostEvents.$inferInsert;
	3486
	3487	export const aiBudgets = pgTable("ai_budgets", {
	3488	userId: uuid("user_id")
	3489	.primaryKey()
	3490	.references(() => users.id, { onDelete: "cascade" }),
	3491	monthlyCents: integer("monthly_cents").default(0).notNull(),
	3492	updatedAt: timestamp("updated_at", { withTimezone: true })
	3493	.defaultNow()
	3494	.notNull(),
	3495	});
	3496
	3497	export type AiBudget = typeof aiBudgets.$inferSelect;
	3498	export type NewAiBudget = typeof aiBudgets.$inferInsert;
79ed944Claude	3499
	3500	// ---------------------------------------------------------------------------
	3501	// Migration 0067 — per-PR runnable sandboxes. See src/lib/pr-sandbox.ts.
	3502	//
	3503	// Each open PR can hold at most one sandbox row (UNIQUE on pr_id). The row
	3504	// owns the lifecycle (provisioning → ready / failed / destroyed) plus the
	3505	// resolved playground.yml the sandbox was provisioned from. Re-provisioning
	3506	// the same PR upserts onto the existing row so a force-push always points
	3507	// at the newest head.
	3508	// ---------------------------------------------------------------------------
	3509	export const prSandboxes = pgTable(
	3510	"pr_sandboxes",
	3511	{
	3512	id: uuid("id").primaryKey().defaultRandom(),
	3513	prId: uuid("pr_id")
	3514	.notNull()
	3515	.references(() => pullRequests.id, { onDelete: "cascade" }),
	3516	status: text("status").default("provisioning").notNull(),
	3517	sandboxUrl: text("sandbox_url").notNull(),
	3518	containerId: text("container_id"),
	3519	playgroundYml: text("playground_yml"),
	3520	provisionedAt: timestamp("provisioned_at", { withTimezone: true })
	3521	.defaultNow()
	3522	.notNull(),
	3523	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	3524	destroyedAt: timestamp("destroyed_at", { withTimezone: true }),
	3525	errorMessage: text("error_message"),
	3526	createdAt: timestamp("created_at", { withTimezone: true })
	3527	.defaultNow()
	3528	.notNull(),
	3529	},
	3530	(table) => [
	3531	uniqueIndex("pr_sandboxes_pr_id").on(table.prId),
	3532	index("pr_sandboxes_status_expires").on(table.status, table.expiresAt),
	3533	]
	3534	);
	3535
	3536	export type PrSandbox = typeof prSandboxes.$inferSelect;
	3537	export type NewPrSandbox = typeof prSandboxes.$inferInsert;
d199847Claude	3538
	3539	// ---------------------------------------------------------------------------
	3540	// 0068 — AI-tracked documentation sections. See src/lib/ai-doc-updater.ts.
	3541	//
	3542	// Stores the currently claimed source-file hash for each
	3543	// `<!-- gluecron:doc-track src=... -->` region in a tracked markdown file.
	3544	// The post-receive hook re-hashes the referenced source after every push
	3545	// and proposes a PR (tagged `ai:doc-update`) when the prose drifts from
	3546	// the truth.
	3547	// ---------------------------------------------------------------------------
	3548	export const docTracking = pgTable(
	3549	"doc_tracking",
	3550	{
	3551	id: uuid("id").primaryKey().defaultRandom(),
	3552	repositoryId: uuid("repository_id")
	3553	.notNull()
	3554	.references(() => repositories.id, { onDelete: "cascade" }),
	3555	docPath: text("doc_path").notNull(),
	3556	sectionMarker: text("section_marker").notNull(),
	3557	srcPath: text("src_path").notNull(),
	3558	claimedHash: text("claimed_hash").notNull(),
	3559	lastCheckedAt: timestamp("last_checked_at", { withTimezone: true })
	3560	.defaultNow()
	3561	.notNull(),
	3562	lastPrId: uuid("last_pr_id").references(() => pullRequests.id, {
	3563	onDelete: "set null",
	3564	}),
	3565	createdAt: timestamp("created_at", { withTimezone: true })
	3566	.defaultNow()
	3567	.notNull(),
	3568	},
	3569	(table) => [
	3570	uniqueIndex("doc_tracking_repo_doc_marker").on(
	3571	table.repositoryId,
	3572	table.docPath,
	3573	table.sectionMarker
	3574	),
	3575	index("doc_tracking_repo").on(table.repositoryId),
	3576	]
	3577	);
	3578
	3579	export type DocTracking = typeof docTracking.$inferSelect;
	3580	export type NewDocTracking = typeof docTracking.$inferInsert;
c6db5eeClaude	3581
	3582	// ---------------------------------------------------------------------------
	3583	// 0069 — Hosted Claude tool-use loops. See src/lib/hosted-claude-loop.ts and
	3584	// src/routes/claude-deploy.tsx.
	3585	//
	3586	// Users paste a Claude tool-use loop, get a hosted endpoint + budget meter.
	3587	// Each loop is paired to an agent_sessions row so it inherits multiplayer
	3588	// namespacing and the daily budget mutex.
	3589	// ---------------------------------------------------------------------------
	3590	export const hostedClaudeLoops = pgTable(
	3591	"hosted_claude_loops",
	3592	{
	3593	id: uuid("id").primaryKey().defaultRandom(),
	3594	ownerUserId: uuid("owner_user_id")
	3595	.notNull()
	3596	.references(() => users.id, { onDelete: "cascade" }),
	3597	name: text("name").notNull(),
	3598	sourceCode: text("source_code").notNull(),
	3599	endpointPath: text("endpoint_path").notNull().unique(),
	3600	agentSessionId: uuid("agent_session_id").references(
	3601	() => agentSessions.id,
	3602	{ onDelete: "set null" }
	3603	),
	3604	status: text("status").default("paused").notNull(),
	3605	isPublic: boolean("is_public").default(false).notNull(),
	3606	monthlyBudgetCents: integer("monthly_budget_cents").default(500).notNull(),
	3607	lastRunAt: timestamp("last_run_at", { withTimezone: true }),
	3608	totalInvocations: integer("total_invocations").default(0).notNull(),
	3609	totalCentsSpent: integer("total_cents_spent").default(0).notNull(),
	3610	createdAt: timestamp("created_at", { withTimezone: true })
	3611	.defaultNow()
	3612	.notNull(),
	3613	updatedAt: timestamp("updated_at", { withTimezone: true })
	3614	.defaultNow()
	3615	.notNull(),
	3616	},
	3617	(table) => [
	3618	index("hosted_claude_loops_owner").on(table.ownerUserId, table.updatedAt),
	3619	index("hosted_claude_loops_status").on(table.status),
	3620	]
	3621	);
	3622
	3623	export const hostedClaudeLoopRuns = pgTable(
	3624	"hosted_claude_loop_runs",
	3625	{
	3626	id: uuid("id").primaryKey().defaultRandom(),
	3627	loopId: uuid("loop_id")
	3628	.notNull()
	3629	.references(() => hostedClaudeLoops.id, { onDelete: "cascade" }),
	3630	inputPayload: jsonb("input_payload").default({}).notNull(),
	3631	outputPayload: jsonb("output_payload"),
	3632	stdout: text("stdout"),
	3633	stderr: text("stderr"),
	3634	startedAt: timestamp("started_at", { withTimezone: true })
	3635	.defaultNow()
	3636	.notNull(),
	3637	finishedAt: timestamp("finished_at", { withTimezone: true }),
	3638	status: text("status").default("running").notNull(),
	3639	centsEstimate: integer("cents_estimate").default(0).notNull(),
	3640	claudeInputTokens: integer("claude_input_tokens").default(0).notNull(),
	3641	claudeOutputTokens: integer("claude_output_tokens").default(0).notNull(),
	3642	exitCode: integer("exit_code"),
	3643	errorMessage: text("error_message"),
	3644	createdAt: timestamp("created_at", { withTimezone: true })
	3645	.defaultNow()
	3646	.notNull(),
	3647	},
	3648	(table) => [
	3649	index("hosted_claude_loop_runs_loop_time").on(
	3650	table.loopId,
	3651	table.startedAt
	3652	),
	3653	index("hosted_claude_loop_runs_status").on(table.status, table.startedAt),
	3654	]
	3655	);
	3656
	3657	export type HostedClaudeLoop = typeof hostedClaudeLoops.$inferSelect;
	3658	export type NewHostedClaudeLoop = typeof hostedClaudeLoops.$inferInsert;
	3659	export type HostedClaudeLoopRun = typeof hostedClaudeLoopRuns.$inferSelect;
	3660	export type NewHostedClaudeLoopRun = typeof hostedClaudeLoopRuns.$inferInsert;
5ca514aClaude	3661
	3662	// ---------------------------------------------------------------------------
9b3a183Claude	3663	// 0070 — Agent Marketplace. See src/lib/agent-marketplace.ts.
5ca514aClaude	3664	// ---------------------------------------------------------------------------
	3665	export const agentMarketplaceListings = pgTable(
	3666	"agent_marketplace_listings",
	3667	{
	3668	id: uuid("id").primaryKey().defaultRandom(),
	3669	publisherUserId: uuid("publisher_user_id")
	3670	.notNull()
	3671	.references(() => users.id, { onDelete: "cascade" }),
	3672	slug: text("slug").notNull().unique(),
	3673	name: text("name").notNull(),
	3674	tagline: text("tagline").default("").notNull(),
	3675	description: text("description").default("").notNull(),
	3676	category: text("category").default("custom").notNull(),
	3677	pricingModel: text("pricing_model").default("free").notNull(),
	3678	priceCents: integer("price_cents").default(0).notNull(),
	3679	agentTemplate: jsonb("agent_template")
	3680	.$type<{
	3681	branchNamespace?: string;
	3682	budgetCentsPerDay?: number;
	3683	capabilities?: string[];
	3684	[k: string]: unknown;
	3685	}>()
	3686	.default({})
	3687	.notNull(),
	3688	sourceUrl: text("source_url"),
	3689	status: text("status").default("draft").notNull(),
	3690	installCount: integer("install_count").default(0).notNull(),
	3691	ratingAvg: numeric("rating_avg", { precision: 3, scale: 2 })
	3692	.default("0")
	3693	.notNull(),
	3694	ratingCount: integer("rating_count").default(0).notNull(),
	3695	createdAt: timestamp("created_at", { withTimezone: true })
	3696	.defaultNow()
	3697	.notNull(),
	3698	updatedAt: timestamp("updated_at", { withTimezone: true })
	3699	.defaultNow()
	3700	.notNull(),
	3701	},
	3702	(table) => [
9b3a183Claude	3703	index("agent_marketplace_listings_category").on(table.category, table.status),
	3704	index("agent_marketplace_listings_rating").on(table.ratingAvg, table.ratingCount),
5ca514aClaude	3705	index("agent_marketplace_listings_installs").on(table.installCount),
	3706	index("agent_marketplace_listings_publisher").on(table.publisherUserId),
9b3a183Claude	3707	index("agent_marketplace_listings_status_created").on(table.status, table.createdAt),
5ca514aClaude	3708	]
	3709	);
	3710
	3711	export const agentMarketplaceInstalls = pgTable(
	3712	"agent_marketplace_installs",
	3713	{
	3714	id: uuid("id").primaryKey().defaultRandom(),
	3715	listingId: uuid("listing_id")
	3716	.notNull()
	3717	.references(() => agentMarketplaceListings.id, { onDelete: "cascade" }),
	3718	repositoryId: uuid("repository_id")
	3719	.notNull()
	3720	.references(() => repositories.id, { onDelete: "cascade" }),
	3721	installedByUserId: uuid("installed_by_user_id")
	3722	.notNull()
	3723	.references(() => users.id, { onDelete: "cascade" }),
	3724	agentSessionId: uuid("agent_session_id").references(
	3725	() => agentSessions.id,
	3726	{ onDelete: "set null" }
	3727	),
	3728	status: text("status").default("active").notNull(),
	3729	installedAt: timestamp("installed_at", { withTimezone: true })
	3730	.defaultNow()
	3731	.notNull(),
	3732	lastInvokedAt: timestamp("last_invoked_at", { withTimezone: true }),
	3733	createdAt: timestamp("created_at", { withTimezone: true })
	3734	.defaultNow()
	3735	.notNull(),
	3736	},
	3737	(table) => [
9b3a183Claude	3738	uniqueIndex("agent_marketplace_installs_listing_repo").on(table.listingId, table.repositoryId),
5ca514aClaude	3739	index("agent_marketplace_installs_repo").on(table.repositoryId, table.status),
	3740	index("agent_marketplace_installs_installer").on(table.installedByUserId),
	3741	]
	3742	);
	3743
	3744	export const agentMarketplaceReviews = pgTable(
	3745	"agent_marketplace_reviews",
	3746	{
	3747	id: uuid("id").primaryKey().defaultRandom(),
	3748	listingId: uuid("listing_id")
	3749	.notNull()
	3750	.references(() => agentMarketplaceListings.id, { onDelete: "cascade" }),
	3751	reviewerUserId: uuid("reviewer_user_id")
	3752	.notNull()
	3753	.references(() => users.id, { onDelete: "cascade" }),
	3754	rating: integer("rating").notNull(),
	3755	body: text("body").default("").notNull(),
	3756	createdAt: timestamp("created_at", { withTimezone: true })
	3757	.defaultNow()
	3758	.notNull(),
	3759	},
	3760	(table) => [
9b3a183Claude	3761	index("agent_marketplace_reviews_listing_created").on(table.listingId, table.createdAt),
5ca514aClaude	3762	index("agent_marketplace_reviews_reviewer").on(table.reviewerUserId),
	3763	]
	3764	);
	3765
9b3a183Claude	3766	export type AgentMarketplaceListing = typeof agentMarketplaceListings.$inferSelect;
	3767	export type NewAgentMarketplaceListing = typeof agentMarketplaceListings.$inferInsert;
	3768	export type AgentMarketplaceInstall = typeof agentMarketplaceInstalls.$inferSelect;
	3769	export type NewAgentMarketplaceInstall = typeof agentMarketplaceInstalls.$inferInsert;
	3770	export type AgentMarketplaceReview = typeof agentMarketplaceReviews.$inferSelect;
	3771	export type NewAgentMarketplaceReview = typeof agentMarketplaceReviews.$inferInsert;
	3772
	3773	// ---------------------------------------------------------------------------
	3774	// 0072 — per-(repo, user) cloud dev environments. See src/lib/dev-env.ts.
	3775	// ---------------------------------------------------------------------------
	3776	export const devEnvs = pgTable(
	3777	"dev_envs",
	3778	{
	3779	id: uuid("id").primaryKey().defaultRandom(),
	3780	repositoryId: uuid("repository_id")
	3781	.notNull()
	3782	.references(() => repositories.id, { onDelete: "cascade" }),
	3783	ownerUserId: uuid("owner_user_id")
	3784	.notNull()
	3785	.references(() => users.id, { onDelete: "cascade" }),
	3786	status: text("status").default("cold").notNull(),
	3787	previewUrl: text("preview_url"),
	3788	containerId: text("container_id"),
	3789	machineSize: text("machine_size").default("small").notNull(),
	3790	idleMinutes: integer("idle_minutes").default(30).notNull(),
	3791	devYml: text("dev_yml"),
	3792	errorMessage: text("error_message"),
	3793	lastActiveAt: timestamp("last_active_at", { withTimezone: true })
	3794	.defaultNow()
	3795	.notNull(),
	3796	expiresAt: timestamp("expires_at", { withTimezone: true }),
	3797	createdAt: timestamp("created_at", { withTimezone: true })
	3798	.defaultNow()
	3799	.notNull(),
	3800	},
	3801	(table) => [
	3802	uniqueIndex("dev_envs_repo_owner").on(table.repositoryId, table.ownerUserId),
	3803	index("dev_envs_status_active").on(table.status, table.lastActiveAt),
	3804	]
	3805	);
	3806
	3807	export type DevEnv = typeof devEnvs.$inferSelect;
	3808	export type NewDevEnv = typeof devEnvs.$inferInsert;