Pre-launch — Gluecron is in final validation. Public signups and git hosting for non-owner users open after launch review.
ccantynz/Gluecron.com
CodeIssuesDiscussionsWikiPull RequestsProjectsCommitsActionsReleasesContributorsPulse● GatesSecuritySettingsDeploymentsPipelineInsightsAgents✨ Explain✨ Ask AI✨ Workspace✨ Spec✨ Tests▓ Debt Map✨ NL Search🏛 Archaeology
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.

Gluecron.com/src/db/schema.ts
schema.tsBlame4602 lines · 3 contributors
Normal viewRaw
fc1817aClaude1import {
2 pgTable,
3 text,
4 timestamp,
5 uuid,
6 boolean,
7 integer,
8 uniqueIndex,
79136bbClaude9 index,
10 serial,
abfa9adClaude11 bigint,
12 jsonb,
5ca514aClaude13 numeric,
abfa9adClaude14 customType,
cc34156Claude15 primaryKey,
fc1817aClaude16} from "drizzle-orm/pg-core";
17
abfa9adClaude18// Postgres `bytea` — drizzle-orm doesn't ship a first-class bytea column, so
19// we declare one via customType that maps to Buffer on both read and write.
20// Used by `workflow_run_cache.content` where we really do need raw bytes
21// (unlike `workflow_artifacts.content`, which stayed base64-text for v1).
22const bytea = customType<{ data: Buffer; default: false }>({
23 dataType() {
24 return "bytea";
25 },
26});
27
a686079Claude28// pgvector `vector(N)` — drizzle-orm has no first-class pgvector column.
29// We map it to `number[]` and serialise/deserialise via Postgres's
30// canonical text format `[0.1,0.2,...]`. The `default: false` flag tells
31// drizzle this column has no default value.
32//
33// Migration 0057 creates the column with `vector(1024)`. If the migration
34// degraded (pgvector unavailable on the host), the column is missing and
35// every read/write throws; src/lib/semantic-index.ts catches at the
36// boundary and falls back to empty-result behaviour.
37const vector = customType<{ data: number[]; driverData: string; default: false }>({
38 dataType(config: unknown) {
39 const cfg = config as { dimensions?: number } | undefined;
40 return cfg?.dimensions ? `vector(${cfg.dimensions})` : "vector";
41 },
42 toDriver(value: number[]): string {
43 return "[" + value.join(",") + "]";
44 },
45 fromDriver(value: string | number[]): number[] {
46 if (Array.isArray(value)) return value as number[];
47 // Canonical format: "[0.1,0.2,0.3]"
48 const trimmed = String(value).trim();
49 if (!trimmed) return [];
50 const inner = trimmed.startsWith("[") && trimmed.endsWith("]")
51 ? trimmed.slice(1, -1)
52 : trimmed;
53 if (!inner) return [];
54 return inner.split(",").map((s) => parseFloat(s));
55 },
56});
57
fc1817aClaude58export const users = pgTable("users", {
59 id: uuid("id").primaryKey().defaultRandom(),
60 username: text("username").notNull().unique(),
61 email: text("email").notNull().unique(),
62 displayName: text("display_name"),
63 passwordHash: text("password_hash").notNull(),
64 avatarUrl: text("avatar_url"),
65 bio: text("bio"),
24cf2caClaude66 // Email notification preferences (Block A8). Default on; opt-out via /settings.
67 notifyEmailOnMention: boolean("notify_email_on_mention").default(true).notNull(),
68 notifyEmailOnAssign: boolean("notify_email_on_assign").default(true).notNull(),
69 notifyEmailOnGateFail: boolean("notify_email_on_gate_fail").default(true).notNull(),
08420cdClaude70 // Block I7 — weekly digest opt-in.
71 notifyEmailDigestWeekly: boolean("notify_email_digest_weekly").default(false).notNull(),
cb5a796Claude72 // Drizzle/0066 — Pending comment moderation requests. Defaults ON so
73 // a new repo owner is alerted as soon as the first comment lands in
74 // the queue. The in-app notification is always written regardless;
75 // this controls fan-out (currently the audit/notification surface,
76 // future email).
77 notifyEmailOnPendingComment: boolean("notify_email_on_pending_comment")
78 .default(true)
79 .notNull(),
08420cdClaude80 lastDigestSentAt: timestamp("last_digest_sent_at"),
46d6165Claude81 // Block L1 — Sleep Mode. When enabled, the autopilot sleep-mode-digest
82 // task delivers a daily "what Claude shipped overnight" report at the
e1fc7dbClaude83 // user-configured UTC hour (0-23, default 9). Uses its own independent
84 // cooldown anchor (lastSleepDigestSentAt) so the weekly digest timer is
85 // not reset when a sleep-mode digest fires, and vice versa.
86 // Migration 0077 adds the column.
87 lastSleepDigestSentAt: timestamp("last_sleep_digest_sent_at"),
46d6165Claude88 sleepModeEnabled: boolean("sleep_mode_enabled").default(false).notNull(),
89 sleepModeDigestHourUtc: integer("sleep_mode_digest_hour_utc").default(9).notNull(),
534f04aClaude90 // Block M2 — Web Push per-event preferences. Default on; opt-out via /settings.
91 notifyPushOnMention: boolean("notify_push_on_mention").default(true).notNull(),
92 notifyPushOnAssign: boolean("notify_push_on_assign").default(true).notNull(),
93 notifyPushOnReviewRequest: boolean("notify_push_on_review_request")
94 .default(true)
95 .notNull(),
96 notifyPushOnDeployFailed: boolean("notify_push_on_deploy_failed")
97 .default(true)
98 .notNull(),
a4f3e24Claude99 isAdmin: boolean("is_admin").default(false).notNull(),
c63b860Claude100 // Block P2 — set when the user clicks the verification link. Soft-gate
101 // only: registration succeeds regardless; /dashboard surfaces a banner
102 // until this is non-null.
103 emailVerifiedAt: timestamp("email_verified_at"),
104 // Block P5 — Account deletion with 30-day grace period.
105 // See drizzle/0049_account_deletion.sql.
106 deletedAt: timestamp("deleted_at"),
107 deletionScheduledFor: timestamp("deletion_scheduled_for"),
108 // Block P3 — Terms of Service / Privacy Policy acceptance audit trail.
109 // Set on /register when the user ticks the accept_terms checkbox.
110 // termsVersion bumps when Terms change; UI prompts re-acceptance later.
111 termsAcceptedAt: timestamp("terms_accepted_at"),
112 termsVersion: text("terms_version"),
cd4f63bTest User113 // Block Q3 — Anonymous playground accounts. When `isPlayground=true`, the
114 // account was minted by /play with a synthetic email + random password
115 // and is hard-deleted by the autopilot `playground-purge` task once
116 // `playgroundExpiresAt` passes. Real accounts always carry false/null.
117 // See drizzle/0052_playground_accounts.sql.
118 isPlayground: boolean("is_playground").default(false).notNull(),
119 playgroundExpiresAt: timestamp("playground_expires_at"),
ee7e577Claude120 // Migration 0071 — Personal cross-repo semantic index opt-in. When true,
121 // /chat (and the /api/v2/me/chat/messages endpoint) may search across
122 // every repo the user owns OR is an accepted collaborator on. Default
123 // OFF — privacy-first; the user must explicitly enable it via
124 // /settings/personal-semantic-toggle. The personal-semantic helpers
125 // hard-refuse to return any rows while this flag is false.
126 personalSemanticIndexEnabled: boolean("personal_semantic_index_enabled")
127 .default(false)
128 .notNull(),
b12f20dClaude129 // Onboarding drip sequence (migration 0081). Stores a JSON array of string
f65f600Claude130 // keys for emails already delivered, e.g. ["welcome","day1","day3"].
131 // The autopilot `onboarding-drip` task compares this against the canonical
132 // drip schedule and sends any outstanding emails. Never null — defaults to
133 // an empty array at insert time.
134 onboardingEmailsSent: jsonb("onboarding_emails_sent").$type<string[]>().default([]).notNull(),
cc34156Claude135 // Migration 0089 — Smart morning digest (AI-curated daily notification queue).
136 // When true, the autopilot `smart-digest` task delivers one AI-curated
137 // digest notification per day at 07:00 UTC. Independent cooldown via
138 // `lastSmartDigestSentAt` so it doesn't interact with weekly email digest.
139 notifySmartDigest: boolean("notify_smart_digest").default(true).notNull(),
140 lastSmartDigestSentAt: timestamp("last_smart_digest_sent_at", { withTimezone: true }),
fc1817aClaude141 createdAt: timestamp("created_at").defaultNow().notNull(),
142 updatedAt: timestamp("updated_at").defaultNow().notNull(),
143});
144
06d5ffeClaude145export const sessions = pgTable("sessions", {
146 id: uuid("id").primaryKey().defaultRandom(),
147 userId: uuid("user_id")
148 .notNull()
149 .references(() => users.id, { onDelete: "cascade" }),
150 token: text("token").notNull().unique(),
151 expiresAt: timestamp("expires_at").notNull(),
7298a17Claude152 // B4: true when the user has entered their password but not yet their TOTP
153 // code. softAuth/requireAuth treat such sessions as anonymous; only
154 // /login/2fa can consume them. Flips to false on successful 2FA.
155 requires2fa: boolean("requires_2fa").default(false).notNull(),
ba9e143Claude156 // Migration 0077 — SOC 2 session visibility. Populated at login and
157 // refreshed on each authenticated request (best-effort, non-blocking).
158 ip: text("ip"),
159 userAgent: text("user_agent"),
160 lastSeenAt: timestamp("last_seen_at"),
06d5ffeClaude161 createdAt: timestamp("created_at").defaultNow().notNull(),
162});
163
ba9e143Claude164/**
165 * Login attempt log — SOC 2 CC6.1 account-lockout evidence.
166 * Records every login attempt (success or failure) per email + IP.
167 * After 10 failures within 1 hour the login handler blocks the email
168 * for 15 minutes and emits `auth.login.locked` to the audit log.
169 * Migration 0078.
170 */
171export const loginAttempts = pgTable(
172 "login_attempts",
173 {
174 id: uuid("id").primaryKey().defaultRandom(),
175 email: text("email").notNull(),
176 ip: text("ip").notNull(),
177 success: boolean("success").notNull().default(false),
178 createdAt: timestamp("created_at").defaultNow().notNull(),
179 },
180 (table) => [
181 index("login_attempts_email_created").on(table.email, table.createdAt),
182 index("login_attempts_ip_created").on(table.ip, table.createdAt),
183 ]
184);
185
186export type LoginAttempt = typeof loginAttempts.$inferSelect;
187
45e31d0Claude188// @ts-ignore — self-referential FK on forkedFromId causes circular inference
fc1817aClaude189export const repositories = pgTable(
190 "repositories",
191 {
192 id: uuid("id").primaryKey().defaultRandom(),
193 name: text("name").notNull(),
7437605Claude194 // ownerId = creator / user-owner. Always set (for attribution + user
195 // namespace uniqueness). For org-owned repos, also represents "created by".
fc1817aClaude196 ownerId: uuid("owner_id")
197 .notNull()
198 .references(() => users.id),
7437605Claude199 // Block B2: nullable org owner. When set, the repo lives in the org
200 // namespace and URL resolution routes `/:orgSlug/:repo` to it.
201 orgId: uuid("org_id"),
fc1817aClaude202 description: text("description"),
203 isPrivate: boolean("is_private").default(false).notNull(),
3ef4c9dClaude204 isArchived: boolean("is_archived").default(false).notNull(),
71cd5ecClaude205 isTemplate: boolean("is_template").default(false).notNull(),
fc1817aClaude206 defaultBranch: text("default_branch").default("main").notNull(),
207 diskPath: text("disk_path").notNull(),
45e31d0Claude208 forkedFromId: uuid("forked_from_id"),
fc1817aClaude209 createdAt: timestamp("created_at").defaultNow().notNull(),
210 updatedAt: timestamp("updated_at").defaultNow().notNull(),
211 pushedAt: timestamp("pushed_at"),
212 starCount: integer("star_count").default(0).notNull(),
213 forkCount: integer("fork_count").default(0).notNull(),
79136bbClaude214 issueCount: integer("issue_count").default(0).notNull(),
534f04aClaude215 // Block M5: autopilot stale-sweep opt-out flags. Default true — the
216 // 2-stage close (poke at 7d/30d, close at 14d/60d after poke) runs on
217 // every repo unless an owner disables it via repo-settings.
218 autoCloseStalePrs: boolean("auto_close_stale_prs").default(true).notNull(),
219 autoCloseStaleIssues: boolean("auto_close_stale_issues")
220 .default(true)
221 .notNull(),
4bbacbeClaude222 // Migration 0062 — opt-out flag for per-branch preview URLs. Default on;
223 // owners can disable via repo-settings. When false, post-receive skips
224 // the enqueuePreviewBuild call entirely.
225 previewBuildsEnabled: boolean("preview_builds_enabled")
226 .default(true)
227 .notNull(),
0c3eee5Claude228 // Migration 0065 — opt-in flag for the AI test generator autopilot task.
229 // Default false (off) because writing tests against unreviewed code can
230 // produce noise; owners must explicitly enable it via repo-settings.
231 autoGenerateTests: boolean("auto_generate_tests")
232 .default(false)
233 .notNull(),
79ed944Claude234 // Migration 0067 — opt-in flag for auto-provisioning a runnable PR
235 // sandbox on every PR open. Default false (off) because each sandbox
236 // burns compute; owners must explicitly enable it via repo-settings.
237 autoPrSandbox: boolean("auto_pr_sandbox").default(false).notNull(),
9b3a183Claude238 // Migration 0072 — opt-in flag for hosted-in-browser dev environments
239 // (cloud Codespaces alternative). Default false (off) because each
240 // env burns a container until the idle sweep tears it down; owners
241 // must explicitly enable per-repo via repo-settings.
242 devEnvsEnabled: boolean("dev_envs_enabled").default(false).notNull(),
44f1a02Claude243 dataRegion: text("data_region").default("us").notNull(),
1df50d5Claude244 previewBuildCommand: text("preview_build_command"),
245 previewOutputDir: text("preview_output_dir").default("dist"),
f5ad215Claude246 // Migration 0077 — opt-in flag for the AI dependency auto-updater.
247 // When true, the autopilot dep-update-sweep task reads package.json,
248 // queries npm for patch/minor updates, applies them, runs GateTest,
249 // and auto-merges (pass) or opens a PR with an AI migration guide
250 // (fail). Default false — off by default because it touches branches.
251 depUpdaterEnabled: boolean("dep_updater_enabled").default(false).notNull(),
641aa42Claude252 // Migration 0088 — smart empty states. Set to true once the onboarding
253 // card has been dismissed by the repo owner. Generated on first push.
254 onboardingShown: boolean("onboarding_shown").default(false).notNull(),
fc1817aClaude255 },
7437605Claude256 (table) => [
257 // Partial: uniqueness only in the user namespace (org-owned rows exempt).
258 // Matches the partial index in migration 0004.
259 uniqueIndex("repos_owner_name").on(table.ownerId, table.name),
260 index("repos_org").on(table.orgId),
261 ]
fc1817aClaude262);
263
3ef4c9dClaude264/**
265 * Per-repository gate + auto-repair configuration.
266 * Every new repo is created with all gates ENABLED by default —
267 * the "full green ecosystem" default. Owners can manually opt-out per setting.
268 */
269export const repoSettings = pgTable("repo_settings", {
270 id: uuid("id").primaryKey().defaultRandom(),
271 repositoryId: uuid("repository_id")
272 .notNull()
273 .unique()
274 .references(() => repositories.id, { onDelete: "cascade" }),
275 // Gates
276 gateTestEnabled: boolean("gate_test_enabled").default(true).notNull(),
277 aiReviewEnabled: boolean("ai_review_enabled").default(true).notNull(),
278 secretScanEnabled: boolean("secret_scan_enabled").default(true).notNull(),
279 securityScanEnabled: boolean("security_scan_enabled").default(true).notNull(),
280 dependencyScanEnabled: boolean("dependency_scan_enabled").default(true).notNull(),
281 lintEnabled: boolean("lint_enabled").default(true).notNull(),
282 typeCheckEnabled: boolean("type_check_enabled").default(true).notNull(),
283 testEnabled: boolean("test_enabled").default(true).notNull(),
284 // Auto-repair
285 autoFixEnabled: boolean("auto_fix_enabled").default(true).notNull(),
286 autoMergeResolveEnabled: boolean("auto_merge_resolve_enabled").default(true).notNull(),
287 autoFormatEnabled: boolean("auto_format_enabled").default(true).notNull(),
288 // AI features
289 aiCommitMessagesEnabled: boolean("ai_commit_messages_enabled").default(true).notNull(),
290 aiPrSummaryEnabled: boolean("ai_pr_summary_enabled").default(true).notNull(),
291 aiChangelogEnabled: boolean("ai_changelog_enabled").default(true).notNull(),
292 // Deploy
293 autoDeployEnabled: boolean("auto_deploy_enabled").default(true).notNull(),
294 deployRequireAllGreen: boolean("deploy_require_all_green").default(true).notNull(),
295 createdAt: timestamp("created_at").defaultNow().notNull(),
296 updatedAt: timestamp("updated_at").defaultNow().notNull(),
297});
298
299/**
300 * Branch protection rules — enforced on push and merge.
301 * Every repo's default branch gets a protection rule on creation.
302 */
303export const branchProtection = pgTable(
304 "branch_protection",
305 {
306 id: uuid("id").primaryKey().defaultRandom(),
307 repositoryId: uuid("repository_id")
308 .notNull()
309 .references(() => repositories.id, { onDelete: "cascade" }),
310 pattern: text("pattern").notNull(), // branch name or glob (e.g. "main", "release/*")
311 requirePullRequest: boolean("require_pull_request").default(true).notNull(),
312 requireGreenGates: boolean("require_green_gates").default(true).notNull(),
313 requireAiApproval: boolean("require_ai_approval").default(true).notNull(),
314 requireHumanReview: boolean("require_human_review").default(false).notNull(),
315 requiredApprovals: integer("required_approvals").default(0).notNull(),
316 allowForcePush: boolean("allow_force_push").default(false).notNull(),
317 allowDeletion: boolean("allow_deletion").default(false).notNull(),
318 dismissStaleReviews: boolean("dismiss_stale_reviews").default(true).notNull(),
4626e61Claude319 // K2 — Auto-merge opt-in. When true, the autopilot ticker may auto-merge
320 // a PR whose base branch matches this rule, provided every gate the
321 // manual-merge path enforces is green. Default-deny.
322 enableAutoMerge: boolean("enable_auto_merge").default(false).notNull(),
3ef4c9dClaude323 createdAt: timestamp("created_at").defaultNow().notNull(),
324 updatedAt: timestamp("updated_at").defaultNow().notNull(),
325 },
326 (table) => [
327 uniqueIndex("branch_protection_repo_pattern").on(
328 table.repositoryId,
329 table.pattern
330 ),
331 ]
332);
333
334/**
335 * Gate run history. Every push + every PR creates gate_runs entries —
336 * one per configured gate. Serves as the source of truth for "is this green?".
337 */
338export const gateRuns = pgTable(
339 "gate_runs",
340 {
341 id: uuid("id").primaryKey().defaultRandom(),
342 repositoryId: uuid("repository_id")
343 .notNull()
344 .references(() => repositories.id, { onDelete: "cascade" }),
345 pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
346 onDelete: "cascade",
347 }),
348 commitSha: text("commit_sha").notNull(),
349 ref: text("ref").notNull(),
350 gateName: text("gate_name").notNull(), // e.g. "GateTest", "AI Review", "Secret Scan", "Type Check"
351 status: text("status").notNull(), // pending, running, passed, failed, skipped, repaired
352 summary: text("summary"),
353 details: text("details"), // JSON: per-check output, affected files, etc
354 repairAttempted: boolean("repair_attempted").default(false).notNull(),
355 repairSucceeded: boolean("repair_succeeded").default(false).notNull(),
356 repairCommitSha: text("repair_commit_sha"),
357 durationMs: integer("duration_ms"),
358 createdAt: timestamp("created_at").defaultNow().notNull(),
359 completedAt: timestamp("completed_at"),
360 },
361 (table) => [
362 index("gate_runs_repo_sha").on(table.repositoryId, table.commitSha),
363 index("gate_runs_pr").on(table.pullRequestId),
364 index("gate_runs_created").on(table.createdAt),
365 ]
366);
367
368/**
369 * In-app notifications. Powered by the activity feed + explicit mentions.
370 */
371export const notifications = pgTable(
372 "notifications",
373 {
374 id: uuid("id").primaryKey().defaultRandom(),
375 userId: uuid("user_id")
376 .notNull()
377 .references(() => users.id, { onDelete: "cascade" }),
378 repositoryId: uuid("repository_id").references(() => repositories.id, {
379 onDelete: "cascade",
380 }),
381 kind: text("kind").notNull(), // mention, review_requested, pr_merged, pr_closed, gate_failed, gate_repaired, ai_review, assigned, security_alert, deploy_success, deploy_failed
382 title: text("title").notNull(),
383 body: text("body"),
384 url: text("url"), // link to the relevant page
385 readAt: timestamp("read_at"),
386 createdAt: timestamp("created_at").defaultNow().notNull(),
387 },
388 (table) => [
389 index("notifications_user_unread").on(table.userId, table.readAt),
390 index("notifications_user_created").on(table.userId, table.createdAt),
391 ]
392);
393
394/**
395 * Releases — named snapshots of a repo at a tag/commit.
396 * AI-generated changelogs bundled in notes field.
397 */
398export const releases = pgTable(
399 "releases",
400 {
401 id: uuid("id").primaryKey().defaultRandom(),
402 repositoryId: uuid("repository_id")
403 .notNull()
404 .references(() => repositories.id, { onDelete: "cascade" }),
405 authorId: uuid("author_id")
406 .notNull()
407 .references(() => users.id),
408 tag: text("tag").notNull(),
409 name: text("name").notNull(),
410 body: text("body"), // AI-generated release notes + changelog
411 targetCommit: text("target_commit").notNull(),
412 isDraft: boolean("is_draft").default(false).notNull(),
413 isPrerelease: boolean("is_prerelease").default(false).notNull(),
414 createdAt: timestamp("created_at").defaultNow().notNull(),
415 publishedAt: timestamp("published_at"),
416 },
417 (table) => [
418 uniqueIndex("releases_repo_tag").on(table.repositoryId, table.tag),
419 ]
420);
421
422/**
423 * Milestones — group issues + PRs toward a shared goal.
424 */
425export const milestones = pgTable(
426 "milestones",
427 {
428 id: uuid("id").primaryKey().defaultRandom(),
429 repositoryId: uuid("repository_id")
430 .notNull()
431 .references(() => repositories.id, { onDelete: "cascade" }),
432 title: text("title").notNull(),
433 description: text("description"),
434 state: text("state").notNull().default("open"), // open, closed
435 dueDate: timestamp("due_date"),
436 createdAt: timestamp("created_at").defaultNow().notNull(),
437 closedAt: timestamp("closed_at"),
438 },
439 (table) => [index("milestones_repo_state").on(table.repositoryId, table.state)]
440);
441
442/**
443 * Reactions on issues, PRs, and comments. Universal target pointer.
444 */
445export const reactions = pgTable(
446 "reactions",
447 {
448 id: uuid("id").primaryKey().defaultRandom(),
449 userId: uuid("user_id")
450 .notNull()
451 .references(() => users.id, { onDelete: "cascade" }),
452 targetType: text("target_type").notNull(), // issue, pr, issue_comment, pr_comment
453 targetId: uuid("target_id").notNull(),
454 emoji: text("emoji").notNull(), // thumbs_up, thumbs_down, rocket, heart, eyes, laugh, hooray, confused
455 createdAt: timestamp("created_at").defaultNow().notNull(),
456 },
457 (table) => [
458 uniqueIndex("reactions_unique").on(
459 table.userId,
460 table.targetType,
461 table.targetId,
462 table.emoji
463 ),
464 index("reactions_target").on(table.targetType, table.targetId),
465 ]
466);
467
468/**
469 * PR reviews (formal approve/request-changes).
470 * Separate from inline comments in pr_comments.
471 */
472export const prReviews = pgTable(
473 "pr_reviews",
474 {
475 id: uuid("id").primaryKey().defaultRandom(),
476 pullRequestId: uuid("pull_request_id")
477 .notNull()
478 .references(() => pullRequests.id, { onDelete: "cascade" }),
479 reviewerId: uuid("reviewer_id")
480 .notNull()
481 .references(() => users.id),
482 state: text("state").notNull(), // approved, changes_requested, commented
483 body: text("body"),
484 isAi: boolean("is_ai").default(false).notNull(),
485 createdAt: timestamp("created_at").defaultNow().notNull(),
486 },
487 (table) => [index("pr_reviews_pr").on(table.pullRequestId)]
488);
489
490/**
491 * Code owners — who owns which paths (auto-request review on PR).
492 * Parsed from a CODEOWNERS file at the root of the default branch.
493 */
494export const codeOwners = pgTable(
495 "code_owners",
496 {
497 id: uuid("id").primaryKey().defaultRandom(),
498 repositoryId: uuid("repository_id")
499 .notNull()
500 .references(() => repositories.id, { onDelete: "cascade" }),
501 pathPattern: text("path_pattern").notNull(),
502 ownerUsernames: text("owner_usernames").notNull(), // comma-separated
503 createdAt: timestamp("created_at").defaultNow().notNull(),
504 },
505 (table) => [index("code_owners_repo").on(table.repositoryId)]
506);
507
ec9e3e3Claude508/**
509 * PR review requests — tracks reviewers auto-assigned via CODEOWNERS
510 * or manually requested. The UNIQUE constraint prevents duplicate requests.
511 * Migration 0077.
512 */
513export const prReviewRequests = pgTable(
514 "pr_review_requests",
515 {
516 id: uuid("id").primaryKey().defaultRandom(),
517 prId: uuid("pr_id")
518 .notNull()
519 .references(() => pullRequests.id, { onDelete: "cascade" }),
520 reviewerId: uuid("reviewer_id")
521 .notNull()
522 .references(() => users.id, { onDelete: "cascade" }),
523 requestedBy: uuid("requested_by").references(() => users.id),
524 createdAt: timestamp("created_at").defaultNow().notNull(),
525 },
526 (table) => [
527 index("pr_review_requests_pr").on(table.prId),
528 index("pr_review_requests_reviewer").on(table.reviewerId),
529 ]
530);
531
3ef4c9dClaude532/**
533 * Per-repo AI chat sessions — conversational repo assistant.
534 */
535export const aiChats = pgTable(
536 "ai_chats",
537 {
538 id: uuid("id").primaryKey().defaultRandom(),
539 userId: uuid("user_id")
540 .notNull()
541 .references(() => users.id, { onDelete: "cascade" }),
542 repositoryId: uuid("repository_id").references(() => repositories.id, {
543 onDelete: "cascade",
544 }),
545 title: text("title"),
546 messages: text("messages").notNull().default("[]"), // JSON array of {role, content}
547 createdAt: timestamp("created_at").defaultNow().notNull(),
548 updatedAt: timestamp("updated_at").defaultNow().notNull(),
549 },
550 (table) => [
551 index("ai_chats_user").on(table.userId),
552 index("ai_chats_repo").on(table.repositoryId),
553 ]
554);
555
556/**
557 * Audit log — every sensitive action. Who did what, when, from where.
558 */
559export const auditLog = pgTable(
560 "audit_log",
561 {
562 id: uuid("id").primaryKey().defaultRandom(),
563 userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
564 repositoryId: uuid("repository_id").references(() => repositories.id, {
565 onDelete: "set null",
566 }),
567 action: text("action").notNull(), // repo.create, repo.delete, repo.transfer, token.create, token.revoke, merge, force_push, branch_protection.update, deploy, ...
568 targetType: text("target_type"),
569 targetId: text("target_id"),
570 ip: text("ip"),
571 userAgent: text("user_agent"),
572 metadata: text("metadata"), // JSON for extra context
573 createdAt: timestamp("created_at").defaultNow().notNull(),
574 },
575 (table) => [
576 index("audit_log_user").on(table.userId),
577 index("audit_log_repo").on(table.repositoryId),
578 index("audit_log_created").on(table.createdAt),
579 ]
580);
581
582/**
583 * Deployments — tracks every deploy to downstream systems (Crontech, etc).
584 * Each deploy is gated on ALL green gates passing.
585 */
586export const deployments = pgTable(
587 "deployments",
588 {
589 id: uuid("id").primaryKey().defaultRandom(),
590 repositoryId: uuid("repository_id")
591 .notNull()
592 .references(() => repositories.id, { onDelete: "cascade" }),
593 environment: text("environment").notNull().default("production"),
594 commitSha: text("commit_sha").notNull(),
595 ref: text("ref").notNull(),
a76d984Claude596 status: text("status").notNull(), // pending, running, success, failed, blocked, waiting_timer
3ef4c9dClaude597 blockedReason: text("blocked_reason"),
598 target: text("target"), // e.g. "crontech", "fly.io"
599 triggeredBy: uuid("triggered_by").references(() => users.id),
a76d984Claude600 /**
601 * Set when an approved deploy is held by `environments.wait_timer_minutes`.
602 * NULL = no wait; non-null = autopilot flips status from "waiting_timer"
603 * to "pending" once `now() >= ready_after`.
604 */
605 readyAfter: timestamp("ready_after"),
3ef4c9dClaude606 createdAt: timestamp("created_at").defaultNow().notNull(),
607 completedAt: timestamp("completed_at"),
608 },
609 (table) => [
610 index("deployments_repo").on(table.repositoryId),
611 index("deployments_created").on(table.createdAt),
612 ]
613);
614
615/**
616 * Rate-limit buckets — in-memory or persisted counter per IP / token / route.
617 */
618export const rateLimitBuckets = pgTable(
619 "rate_limit_buckets",
620 {
621 id: uuid("id").primaryKey().defaultRandom(),
622 bucketKey: text("bucket_key").notNull().unique(), // "ip:1.2.3.4:api" or "token:abc:api"
623 count: integer("count").default(0).notNull(),
624 windowStart: timestamp("window_start").defaultNow().notNull(),
625 expiresAt: timestamp("expires_at").notNull(),
626 },
627 (table) => [index("rate_limit_expires").on(table.expiresAt)]
fc1817aClaude628);
629
06d5ffeClaude630export const stars = pgTable(
631 "stars",
632 {
633 id: uuid("id").primaryKey().defaultRandom(),
634 userId: uuid("user_id")
635 .notNull()
636 .references(() => users.id, { onDelete: "cascade" }),
637 repositoryId: uuid("repository_id")
638 .notNull()
639 .references(() => repositories.id, { onDelete: "cascade" }),
640 createdAt: timestamp("created_at").defaultNow().notNull(),
641 },
79136bbClaude642 (table) => [
643 uniqueIndex("stars_user_repo").on(table.userId, table.repositoryId),
644 ]
645);
646
647export const issues = pgTable(
648 "issues",
649 {
650 id: uuid("id").primaryKey().defaultRandom(),
651 number: serial("number"),
652 repositoryId: uuid("repository_id")
653 .notNull()
654 .references(() => repositories.id, { onDelete: "cascade" }),
655 authorId: uuid("author_id")
656 .notNull()
657 .references(() => users.id),
658 title: text("title").notNull(),
659 body: text("body"),
660 state: text("state").notNull().default("open"), // open, closed
85c4e13Claude661 // Migration 0077 — milestone grouping. Optional; ON DELETE SET NULL so
662 // deleting a milestone never removes the issue.
663 milestoneId: uuid("milestone_id").references(() => milestones.id, {
664 onDelete: "set null",
665 }),
79136bbClaude666 createdAt: timestamp("created_at").defaultNow().notNull(),
667 updatedAt: timestamp("updated_at").defaultNow().notNull(),
668 closedAt: timestamp("closed_at"),
669 },
670 (table) => [
671 index("issues_repo_state").on(table.repositoryId, table.state),
672 index("issues_repo_number").on(table.repositoryId, table.number),
85c4e13Claude673 index("issues_milestone").on(table.milestoneId),
79136bbClaude674 ]
675);
676
677export const issueComments = pgTable(
678 "issue_comments",
679 {
680 id: uuid("id").primaryKey().defaultRandom(),
681 issueId: uuid("issue_id")
682 .notNull()
683 .references(() => issues.id, { onDelete: "cascade" }),
684 authorId: uuid("author_id")
685 .notNull()
686 .references(() => users.id),
687 body: text("body").notNull(),
cb5a796Claude688 // Comment moderation (drizzle/0066). 'approved' | 'pending' | 'rejected'
689 // | 'spam'. Default 'approved' keeps every legacy + collaborator path
690 // unchanged; the gate in `lib/comment-moderation.ts` only routes new
691 // non-collaborator comments into 'pending'.
692 moderationStatus: text("moderation_status").notNull().default("approved"),
693 moderatedAt: timestamp("moderated_at", { withTimezone: true }),
694 moderatedByUserId: uuid("moderated_by_user_id").references(() => users.id, {
695 onDelete: "set null",
696 }),
79136bbClaude697 createdAt: timestamp("created_at").defaultNow().notNull(),
698 updatedAt: timestamp("updated_at").defaultNow().notNull(),
699 },
700 (table) => [index("comments_issue").on(table.issueId)]
701);
702
703export const labels = pgTable(
704 "labels",
705 {
706 id: uuid("id").primaryKey().defaultRandom(),
707 repositoryId: uuid("repository_id")
708 .notNull()
709 .references(() => repositories.id, { onDelete: "cascade" }),
710 name: text("name").notNull(),
711 color: text("color").notNull().default("#8b949e"),
712 description: text("description"),
713 },
714 (table) => [
715 uniqueIndex("labels_repo_name").on(table.repositoryId, table.name),
716 ]
717);
718
719export const issueLabels = pgTable(
720 "issue_labels",
721 {
722 id: uuid("id").primaryKey().defaultRandom(),
723 issueId: uuid("issue_id")
724 .notNull()
725 .references(() => issues.id, { onDelete: "cascade" }),
726 labelId: uuid("label_id")
727 .notNull()
728 .references(() => labels.id, { onDelete: "cascade" }),
729 },
730 (table) => [
731 uniqueIndex("issue_labels_unique").on(table.issueId, table.labelId),
732 ]
06d5ffeClaude733);
734
0074234Claude735export const pullRequests = pgTable(
736 "pull_requests",
737 {
738 id: uuid("id").primaryKey().defaultRandom(),
739 number: serial("number"),
740 repositoryId: uuid("repository_id")
741 .notNull()
742 .references(() => repositories.id, { onDelete: "cascade" }),
743 authorId: uuid("author_id")
744 .notNull()
745 .references(() => users.id),
746 title: text("title").notNull(),
747 body: text("body"),
748 state: text("state").notNull().default("open"), // open, closed, merged
749 baseBranch: text("base_branch").notNull(),
750 headBranch: text("head_branch").notNull(),
3ef4c9dClaude751 isDraft: boolean("is_draft").default(false).notNull(),
752 mergeStrategy: text("merge_strategy").default("merge").notNull(), // merge, squash, rebase
753 milestoneId: uuid("milestone_id"),
0074234Claude754 mergedAt: timestamp("merged_at"),
755 mergedBy: uuid("merged_by").references(() => users.id),
756 createdAt: timestamp("created_at").defaultNow().notNull(),
757 updatedAt: timestamp("updated_at").defaultNow().notNull(),
758 closedAt: timestamp("closed_at"),
6ecda8fClaude759 // AI loop columns (migration 0101). Track the autonomous issue→PR→merge loop.
760 // aiLoopAttempts: how many fix-and-retry cycles have run so far (0 = not started).
761 // aiLoopStatus: null = not started, 'running' = loop active, 'merged' = success,
762 // 'failed' = exhausted attempts or unrecoverable error.
763 aiLoopAttempts: integer("ai_loop_attempts").notNull().default(0),
764 aiLoopStatus: text("ai_loop_status"), // null | 'running' | 'merged' | 'failed'
0074234Claude765 },
766 (table) => [
767 index("prs_repo_state").on(table.repositoryId, table.state),
768 index("prs_repo_number").on(table.repositoryId, table.number),
769 ]
770);
771
772export const prComments = pgTable(
773 "pr_comments",
774 {
775 id: uuid("id").primaryKey().defaultRandom(),
776 pullRequestId: uuid("pull_request_id")
777 .notNull()
778 .references(() => pullRequests.id, { onDelete: "cascade" }),
779 authorId: uuid("author_id")
780 .notNull()
781 .references(() => users.id),
782 body: text("body").notNull(),
783 isAiReview: boolean("is_ai_review").default(false).notNull(),
784 filePath: text("file_path"),
785 lineNumber: integer("line_number"),
cb5a796Claude786 // Comment moderation (drizzle/0066). See `issueComments.moderationStatus`.
787 moderationStatus: text("moderation_status").notNull().default("approved"),
788 moderatedAt: timestamp("moderated_at", { withTimezone: true }),
789 moderatedByUserId: uuid("moderated_by_user_id").references(() => users.id, {
790 onDelete: "set null",
791 }),
0074234Claude792 createdAt: timestamp("created_at").defaultNow().notNull(),
793 updatedAt: timestamp("updated_at").defaultNow().notNull(),
794 },
795 (table) => [index("pr_comments_pr").on(table.pullRequestId)]
796);
797
cb5a796Claude798/**
799 * Comment moderation (drizzle/0066) — per-repo allow/deny list for
800 * commenters. A 'trusted' row makes `shouldRequireApproval` short-circuit
801 * to false; a 'banned' row makes it short-circuit to "auto-reject without
802 * notifying the owner" so a single spam decision sticks.
803 *
804 * Indexed by (repository_id, commenter_user_id) for the per-comment gate
805 * lookup and by (repository_id, status) for the owner's trust-list page.
806 */
807export const repoCommenterTrust = pgTable(
808 "repo_commenter_trust",
809 {
810 id: uuid("id").primaryKey().defaultRandom(),
811 repositoryId: uuid("repository_id")
812 .notNull()
813 .references(() => repositories.id, { onDelete: "cascade" }),
814 commenterUserId: uuid("commenter_user_id")
815 .notNull()
816 .references(() => users.id, { onDelete: "cascade" }),
817 status: text("status").notNull(), // 'trusted' | 'banned'
818 grantedByUserId: uuid("granted_by_user_id").references(() => users.id, {
819 onDelete: "set null",
820 }),
821 grantedAt: timestamp("granted_at", { withTimezone: true })
822 .defaultNow()
823 .notNull(),
824 },
825 (table) => [
826 uniqueIndex("repo_commenter_trust_unique").on(
827 table.repositoryId,
828 table.commenterUserId
829 ),
830 index("repo_commenter_trust_repo_status").on(
831 table.repositoryId,
832 table.status
833 ),
834 ]
835);
836
0074234Claude837export const activityFeed = pgTable(
838 "activity_feed",
839 {
840 id: uuid("id").primaryKey().defaultRandom(),
841 repositoryId: uuid("repository_id")
842 .notNull()
843 .references(() => repositories.id, { onDelete: "cascade" }),
844 userId: uuid("user_id").references(() => users.id),
845 action: text("action").notNull(), // push, issue_open, issue_close, pr_open, pr_merge, star, comment
846 targetType: text("target_type"), // issue, pr, commit
847 targetId: text("target_id"),
848 metadata: text("metadata"), // JSON string for extra data
849 createdAt: timestamp("created_at").defaultNow().notNull(),
850 },
851 (table) => [
852 index("activity_repo").on(table.repositoryId),
853 index("activity_user").on(table.userId),
854 ]
855);
856
c81ab7aClaude857export const webhooks = pgTable(
858 "webhooks",
859 {
860 id: uuid("id").primaryKey().defaultRandom(),
861 repositoryId: uuid("repository_id")
862 .notNull()
863 .references(() => repositories.id, { onDelete: "cascade" }),
864 url: text("url").notNull(),
865 secret: text("secret"),
866 events: text("events").notNull().default("push"), // comma-separated: push,issue,pr
867 isActive: boolean("is_active").default(true).notNull(),
868 lastDeliveredAt: timestamp("last_delivered_at"),
869 lastStatus: integer("last_status"),
870 createdAt: timestamp("created_at").defaultNow().notNull(),
871 },
872 (table) => [index("webhooks_repo").on(table.repositoryId)]
873);
874
8405c43Claude875// Reliable webhook delivery (migration 0056). One row per (hook, event)
876// emission. Worker in src/lib/webhook-delivery.ts polls pending rows whose
877// next_attempt_at <= now() and retries with exponential backoff.
878export const webhookDeliveries = pgTable(
879 "webhook_deliveries",
880 {
881 id: uuid("id").primaryKey().defaultRandom(),
882 webhookId: uuid("webhook_id")
883 .notNull()
884 .references(() => webhooks.id, { onDelete: "cascade" }),
885 event: text("event").notNull(),
886 payload: text("payload").notNull(),
887 signature: text("signature").notNull(),
888 attemptCount: integer("attempt_count").default(0).notNull(),
889 nextAttemptAt: timestamp("next_attempt_at", { withTimezone: true }),
890 status: text("status").default("pending").notNull(), // pending | succeeded | failed | dead
891 lastStatusCode: integer("last_status_code"),
892 lastError: text("last_error"),
893 lastAttemptedAt: timestamp("last_attempted_at", { withTimezone: true }),
894 succeededAt: timestamp("succeeded_at", { withTimezone: true }),
895 createdAt: timestamp("created_at", { withTimezone: true })
896 .defaultNow()
897 .notNull(),
898 },
899 (table) => [
900 index("idx_webhook_deliveries_next_attempt").on(table.nextAttemptAt),
901 index("idx_webhook_deliveries_webhook_id").on(
902 table.webhookId,
903 table.createdAt
904 ),
905 ]
906);
907
c81ab7aClaude908export const apiTokens = pgTable("api_tokens", {
909 id: uuid("id").primaryKey().defaultRandom(),
910 userId: uuid("user_id")
911 .notNull()
912 .references(() => users.id, { onDelete: "cascade" }),
913 name: text("name").notNull(),
914 tokenHash: text("token_hash").notNull(),
915 tokenPrefix: text("token_prefix").notNull(), // first 8 chars for display
916 scopes: text("scopes").notNull().default("repo"), // comma-separated
917 lastUsedAt: timestamp("last_used_at"),
918 expiresAt: timestamp("expires_at"),
919 createdAt: timestamp("created_at").defaultNow().notNull(),
920});
921
922export const repoTopics = pgTable(
923 "repo_topics",
924 {
925 id: uuid("id").primaryKey().defaultRandom(),
926 repositoryId: uuid("repository_id")
927 .notNull()
928 .references(() => repositories.id, { onDelete: "cascade" }),
929 topic: text("topic").notNull(),
930 },
931 (table) => [
932 uniqueIndex("repo_topics_unique").on(table.repositoryId, table.topic),
933 index("topics_name").on(table.topic),
934 ]
935);
936
fc1817aClaude937export const sshKeys = pgTable("ssh_keys", {
938 id: uuid("id").primaryKey().defaultRandom(),
939 userId: uuid("user_id")
940 .notNull()
06d5ffeClaude941 .references(() => users.id, { onDelete: "cascade" }),
fc1817aClaude942 title: text("title").notNull(),
943 fingerprint: text("fingerprint").notNull(),
944 publicKey: text("public_key").notNull(),
945 lastUsedAt: timestamp("last_used_at"),
946 createdAt: timestamp("created_at").defaultNow().notNull(),
947});
948
845fd8aClaude949// OCI Container Registry — migration 0083_oci_registry.sql
950// oci_repositories tracks image namespaces (e.g. "alice/myapp").
951// oci_tags maps mutable tag names to a manifest digest for a repository.
952
953export const ociRepositories = pgTable(
954 "oci_repositories",
955 {
956 id: uuid("id").primaryKey().defaultRandom(),
957 ownerId: uuid("owner_id")
958 .notNull()
959 .references(() => users.id, { onDelete: "cascade" }),
960 /** Full image name in "<owner>/<image>" format. */
961 name: text("name").notNull(),
962 visibility: text("visibility").notNull().default("private"), // "public" | "private"
963 createdAt: timestamp("created_at").defaultNow().notNull(),
964 },
965 (table) => [
966 uniqueIndex("oci_repositories_owner_name").on(table.ownerId, table.name),
967 index("idx_oci_repositories_owner").on(table.ownerId),
968 ]
969);
970
971export const ociTags = pgTable(
972 "oci_tags",
973 {
974 id: uuid("id").primaryKey().defaultRandom(),
975 repositoryId: uuid("repository_id")
976 .notNull()
977 .references(() => ociRepositories.id, { onDelete: "cascade" }),
978 tag: text("tag").notNull(),
979 manifestDigest: text("manifest_digest").notNull(), // "sha256:<hex64>"
980 createdAt: timestamp("created_at").defaultNow().notNull(),
981 updatedAt: timestamp("updated_at").defaultNow().notNull(),
982 },
983 (table) => [
984 uniqueIndex("oci_tags_repo_tag").on(table.repositoryId, table.tag),
985 index("idx_oci_tags_repo").on(table.repositoryId),
986 ]
987);
988
989export type OciRepository = typeof ociRepositories.$inferSelect;
990export type NewOciRepository = typeof ociRepositories.$inferInsert;
991export type OciTag = typeof ociTags.$inferSelect;
992export type NewOciTag = typeof ociTags.$inferInsert;
993
534f04aClaude994// Block M2 — Web Push subscriptions. One row per (user, endpoint).
995// Endpoint is the browser-issued push URL; p256dh + auth are the W3C
996// payload-encryption keys (base64url). Stale endpoints (HTTP 410) are
997// deleted lazily on next `sendPushToUser` pass.
998export const pushSubscriptions = pgTable(
999 "push_subscriptions",
1000 {
1001 id: uuid("id").primaryKey().defaultRandom(),
1002 userId: uuid("user_id")
1003 .notNull()
1004 .references(() => users.id, { onDelete: "cascade" }),
1005 endpoint: text("endpoint").notNull(),
1006 p256dh: text("p256dh").notNull(),
1007 auth: text("auth").notNull(),
1008 userAgent: text("user_agent"),
1009 createdAt: timestamp("created_at").defaultNow().notNull(),
1010 lastUsedAt: timestamp("last_used_at"),
1011 },
1012 (table) => [
1013 uniqueIndex("push_subscriptions_user_endpoint").on(
1014 table.userId,
1015 table.endpoint
1016 ),
1017 index("idx_push_subscriptions_user").on(table.userId),
1018 ]
1019);
1020
fc1817aClaude1021export type User = typeof users.$inferSelect;
1022export type NewUser = typeof users.$inferInsert;
1023export type Repository = typeof repositories.$inferSelect;
1024export type NewRepository = typeof repositories.$inferInsert;
06d5ffeClaude1025export type Session = typeof sessions.$inferSelect;
1026export type Star = typeof stars.$inferSelect;
1027export type SshKey = typeof sshKeys.$inferSelect;
534f04aClaude1028export type PushSubscription = typeof pushSubscriptions.$inferSelect;
1029export type NewPushSubscription = typeof pushSubscriptions.$inferInsert;
79136bbClaude1030export type Issue = typeof issues.$inferSelect;
1031export type IssueComment = typeof issueComments.$inferSelect;
1032export type Label = typeof labels.$inferSelect;
0074234Claude1033export type PullRequest = typeof pullRequests.$inferSelect;
1034export type PrComment = typeof prComments.$inferSelect;
cb5a796Claude1035export type RepoCommenterTrust = typeof repoCommenterTrust.$inferSelect;
1036export type NewRepoCommenterTrust = typeof repoCommenterTrust.$inferInsert;
0074234Claude1037export type ActivityEntry = typeof activityFeed.$inferSelect;
c81ab7aClaude1038export type Webhook = typeof webhooks.$inferSelect;
1039export type ApiToken = typeof apiTokens.$inferSelect;
1040export type RepoTopic = typeof repoTopics.$inferSelect;
3ef4c9dClaude1041export type RepoSettings = typeof repoSettings.$inferSelect;
1042export type BranchProtection = typeof branchProtection.$inferSelect;
1043export type GateRun = typeof gateRuns.$inferSelect;
1044export type Notification = typeof notifications.$inferSelect;
1045export type Release = typeof releases.$inferSelect;
1046export type Milestone = typeof milestones.$inferSelect;
1047export type Reaction = typeof reactions.$inferSelect;
1048export type PrReview = typeof prReviews.$inferSelect;
1049export type CodeOwner = typeof codeOwners.$inferSelect;
ec9e3e3Claude1050export type PrReviewRequest = typeof prReviewRequests.$inferSelect;
3ef4c9dClaude1051export type AiChat = typeof aiChats.$inferSelect;
1052export type AuditLogEntry = typeof auditLog.$inferSelect;
1053export type Deployment = typeof deployments.$inferSelect;
24cf2caClaude1054
1055/**
1056 * Saved replies — per-user canned responses, insertable into any
1057 * issue / PR comment textarea. Shortcut name must be unique per user.
1058 */
1059export const savedReplies = pgTable(
1060 "saved_replies",
1061 {
1062 id: uuid("id").primaryKey().defaultRandom(),
1063 userId: uuid("user_id")
1064 .notNull()
1065 .references(() => users.id, { onDelete: "cascade" }),
1066 shortcut: text("shortcut").notNull(),
1067 body: text("body").notNull(),
1068 createdAt: timestamp("created_at").defaultNow().notNull(),
1069 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1070 },
1071 (table) => [
1072 uniqueIndex("saved_replies_user_shortcut").on(table.userId, table.shortcut),
1073 ]
1074);
1075
1076export type SavedReply = typeof savedReplies.$inferSelect;
5cc5d95Claude1077
1078/**
1079 * Organizations (Block B1) — multi-user namespaces. Distinct from `users`.
1080 * An org has members (with org-level roles) and may contain teams.
1081 * Repos can be owned by an org via `repositories.orgId` (added in Block B2).
1082 *
1083 * Slug is globally unique against itself; collision with a username is
1084 * checked at create time in the route handler (no DB-level cross-table
1085 * uniqueness in Postgres).
1086 */
1087export const organizations = pgTable("organizations", {
1088 id: uuid("id").primaryKey().defaultRandom(),
1089 slug: text("slug").notNull().unique(),
1090 name: text("name").notNull(),
1091 description: text("description"),
1092 avatarUrl: text("avatar_url"),
1093 billingEmail: text("billing_email"),
1094 createdById: uuid("created_by_id")
1095 .notNull()
1096 .references(() => users.id, { onDelete: "restrict" }),
1097 createdAt: timestamp("created_at").defaultNow().notNull(),
1098 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1099});
1100
1101/**
1102 * Org membership. Roles: owner (full control, billing), admin (manage
1103 * members + teams + repos), member (default; can be added to teams).
1104 */
1105export const orgMembers = pgTable(
1106 "org_members",
1107 {
1108 id: uuid("id").primaryKey().defaultRandom(),
1109 orgId: uuid("org_id")
1110 .notNull()
1111 .references(() => organizations.id, { onDelete: "cascade" }),
1112 userId: uuid("user_id")
1113 .notNull()
1114 .references(() => users.id, { onDelete: "cascade" }),
1115 role: text("role").notNull().default("member"), // owner | admin | member
1116 createdAt: timestamp("created_at").defaultNow().notNull(),
1117 },
1118 (table) => [
1119 uniqueIndex("org_members_unique").on(table.orgId, table.userId),
1120 index("org_members_user").on(table.userId),
1121 ]
1122);
1123
1124/**
1125 * Teams within an org. Slug is unique within an org.
1126 * `parentTeamId` allows nesting (GitHub-style child teams). Optional.
1127 */
1128export const teams = pgTable(
1129 "teams",
1130 {
1131 id: uuid("id").primaryKey().defaultRandom(),
1132 orgId: uuid("org_id")
1133 .notNull()
1134 .references(() => organizations.id, { onDelete: "cascade" }),
1135 slug: text("slug").notNull(),
1136 name: text("name").notNull(),
1137 description: text("description"),
1138 parentTeamId: uuid("parent_team_id"),
1139 createdAt: timestamp("created_at").defaultNow().notNull(),
1140 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1141 },
1142 (table) => [uniqueIndex("teams_org_slug").on(table.orgId, table.slug)]
1143);
1144
1145/**
1146 * Team membership. Roles: maintainer (can edit team), member (default).
1147 * A user can belong to many teams; team membership requires org membership
1148 * but that invariant is enforced at the route layer, not the DB layer.
1149 */
1150export const teamMembers = pgTable(
1151 "team_members",
1152 {
1153 id: uuid("id").primaryKey().defaultRandom(),
1154 teamId: uuid("team_id")
1155 .notNull()
1156 .references(() => teams.id, { onDelete: "cascade" }),
1157 userId: uuid("user_id")
1158 .notNull()
1159 .references(() => users.id, { onDelete: "cascade" }),
1160 role: text("role").notNull().default("member"), // maintainer | member
1161 createdAt: timestamp("created_at").defaultNow().notNull(),
1162 },
1163 (table) => [
1164 uniqueIndex("team_members_unique").on(table.teamId, table.userId),
1165 index("team_members_user").on(table.userId),
1166 ]
1167);
1168
1169export type Organization = typeof organizations.$inferSelect;
1170export type OrgMember = typeof orgMembers.$inferSelect;
1171export type Team = typeof teams.$inferSelect;
1172export type TeamMember = typeof teamMembers.$inferSelect;
1173export type OrgRole = "owner" | "admin" | "member";
1174export type TeamRole = "maintainer" | "member";
7298a17Claude1175
1176/**
1177 * 2FA / TOTP (Block B4).
1178 *
1179 * Secret is stored in plain Base32 for now — the DB has row-level-secure
1180 * access and the app boundary is the only code that reads it. A follow-up
1181 * (B4.1) will wrap it with AES-GCM at rest once we standardise the KEK.
1182 *
1183 * `enabledAt` is set only after the user has successfully entered their
1184 * first code (confirming the authenticator was set up correctly). Rows with
1185 * `enabledAt = NULL` represent pending enrolment.
1186 */
1187export const userTotp = pgTable("user_totp", {
1188 userId: uuid("user_id")
1189 .primaryKey()
1190 .references(() => users.id, { onDelete: "cascade" }),
1191 secret: text("secret").notNull(),
1192 enabledAt: timestamp("enabled_at"),
1193 lastUsedAt: timestamp("last_used_at"),
1194 createdAt: timestamp("created_at").defaultNow().notNull(),
1195});
1196
1197/**
1198 * Recovery codes — single-use fallback when the authenticator is lost.
1199 * Stored as SHA-256 hashes; used rows are marked with `usedAt` rather than
1200 * deleted so the audit log keeps the full history.
1201 */
1202export const userRecoveryCodes = pgTable(
1203 "user_recovery_codes",
1204 {
1205 id: uuid("id").primaryKey().defaultRandom(),
1206 userId: uuid("user_id")
1207 .notNull()
1208 .references(() => users.id, { onDelete: "cascade" }),
1209 codeHash: text("code_hash").notNull(),
1210 usedAt: timestamp("used_at"),
1211 createdAt: timestamp("created_at").defaultNow().notNull(),
1212 },
1213 (table) => [
1214 index("recovery_codes_user").on(table.userId),
1215 uniqueIndex("recovery_codes_user_hash").on(table.userId, table.codeHash),
1216 ]
1217);
1218
1219export type UserTotp = typeof userTotp.$inferSelect;
1220export type UserRecoveryCode = typeof userRecoveryCodes.$inferSelect;
2df1f8cClaude1221
1222/**
1223 * WebAuthn passkeys (Block B5).
1224 *
1225 * Each row is one registered authenticator. The `credentialId` is the
1226 * globally-unique identifier the browser returns; `publicKey` is the
1227 * COSE-encoded public key we use to verify signatures. `counter` tracks
1228 * the authenticator's signature counter for replay-protection.
1229 *
1230 * `transports` is a JSON array (stored as text) of the
1231 * AuthenticatorTransport values ("usb" | "nfc" | "ble" | "internal" | "hybrid").
1232 */
1233export const userPasskeys = pgTable(
1234 "user_passkeys",
1235 {
1236 id: uuid("id").primaryKey().defaultRandom(),
1237 userId: uuid("user_id")
1238 .notNull()
1239 .references(() => users.id, { onDelete: "cascade" }),
1240 credentialId: text("credential_id").notNull().unique(),
1241 publicKey: text("public_key").notNull(), // base64url of COSE key
1242 counter: integer("counter").default(0).notNull(),
1243 transports: text("transports"), // JSON array string
1244 name: text("name").notNull().default("Passkey"),
1245 lastUsedAt: timestamp("last_used_at"),
1246 createdAt: timestamp("created_at").defaultNow().notNull(),
1247 },
1248 (table) => [index("passkeys_user").on(table.userId)]
1249);
1250
1251/**
1252 * Short-lived WebAuthn challenges. A row is written when we issue options
1253 * (registration or authentication) and deleted after the verify step or when
1254 * it expires (5 min). Keeping them in the DB lets us verify without sticky
1255 * sessions or client-side state.
1256 */
1257export const webauthnChallenges = pgTable(
1258 "webauthn_challenges",
1259 {
1260 id: uuid("id").primaryKey().defaultRandom(),
1261 userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
1262 // For passwordless login we don't know the user yet, so userId is nullable
1263 // and we bind the challenge to a short-lived cookie token instead.
1264 sessionKey: text("session_key").notNull().unique(),
1265 challenge: text("challenge").notNull(),
1266 kind: text("kind").notNull(), // "register" | "authenticate"
1267 expiresAt: timestamp("expires_at").notNull(),
1268 createdAt: timestamp("created_at").defaultNow().notNull(),
1269 },
1270 (table) => [index("webauthn_challenges_expires").on(table.expiresAt)]
1271);
1272
1273export type UserPasskey = typeof userPasskeys.$inferSelect;
1274export type WebauthnChallenge = typeof webauthnChallenges.$inferSelect;
bfdb5e7Claude1275
1276/**
1277 * OAuth 2.0 provider (Block B6).
1278 *
1279 * `oauthApps` is a third-party app registered by a developer. Each app has
1280 * a public `client_id`, a hashed `client_secret`, and one or more allowed
1281 * `redirect_uris` (newline-separated). The plaintext secret is shown to the
1282 * developer exactly once at creation and cannot be recovered; they can
1283 * rotate it instead.
1284 *
1285 * `oauthAuthorizations` is a short-lived authorization code issued after
1286 * the user consents at /oauth/authorize. Single-use: `usedAt` is set on
1287 * redemption so a replay after-the-fact fails.
1288 *
1289 * `oauthAccessTokens` is a long-lived bearer token plus an optional
1290 * refresh token. Both are stored as SHA-256 hashes; the plaintext values
1291 * are only returned to the client once in the /oauth/token response.
1292 */
1293export const oauthApps = pgTable(
1294 "oauth_apps",
1295 {
1296 id: uuid("id").primaryKey().defaultRandom(),
1297 ownerId: uuid("owner_id")
1298 .notNull()
1299 .references(() => users.id, { onDelete: "cascade" }),
1300 name: text("name").notNull(),
1301 clientId: text("client_id").notNull().unique(),
1302 clientSecretHash: text("client_secret_hash").notNull(),
1303 clientSecretPrefix: text("client_secret_prefix").notNull(), // first 8 chars for display
1304 /** Newline-separated list of allowed redirect URIs. */
1305 redirectUris: text("redirect_uris").notNull(),
1306 homepageUrl: text("homepage_url"),
1307 description: text("description"),
1308 /**
1309 * If `true`, the app must present its client_secret at /oauth/token.
1310 * Public SPA/mobile apps should set this to `false` and use PKCE.
1311 */
1312 confidential: boolean("confidential").default(true).notNull(),
1313 revokedAt: timestamp("revoked_at"),
1314 createdAt: timestamp("created_at").defaultNow().notNull(),
1315 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1316 },
1317 (table) => [index("oauth_apps_owner").on(table.ownerId)]
1318);
1319
1320export const oauthAuthorizations = pgTable(
1321 "oauth_authorizations",
1322 {
1323 id: uuid("id").primaryKey().defaultRandom(),
1324 appId: uuid("app_id")
1325 .notNull()
1326 .references(() => oauthApps.id, { onDelete: "cascade" }),
1327 userId: uuid("user_id")
1328 .notNull()
1329 .references(() => users.id, { onDelete: "cascade" }),
1330 codeHash: text("code_hash").notNull().unique(),
1331 redirectUri: text("redirect_uri").notNull(),
1332 scopes: text("scopes").notNull().default(""),
1333 codeChallenge: text("code_challenge"),
1334 codeChallengeMethod: text("code_challenge_method"), // "S256" | "plain"
1335 expiresAt: timestamp("expires_at").notNull(),
1336 usedAt: timestamp("used_at"),
1337 createdAt: timestamp("created_at").defaultNow().notNull(),
1338 },
1339 (table) => [index("oauth_authorizations_expires").on(table.expiresAt)]
1340);
1341
1342export const oauthAccessTokens = pgTable(
1343 "oauth_access_tokens",
1344 {
1345 id: uuid("id").primaryKey().defaultRandom(),
1346 appId: uuid("app_id")
1347 .notNull()
1348 .references(() => oauthApps.id, { onDelete: "cascade" }),
1349 userId: uuid("user_id")
1350 .notNull()
1351 .references(() => users.id, { onDelete: "cascade" }),
1352 accessTokenHash: text("access_token_hash").notNull().unique(),
1353 refreshTokenHash: text("refresh_token_hash").unique(),
1354 scopes: text("scopes").notNull().default(""),
1355 expiresAt: timestamp("expires_at").notNull(),
1356 refreshExpiresAt: timestamp("refresh_expires_at"),
1357 revokedAt: timestamp("revoked_at"),
1358 lastUsedAt: timestamp("last_used_at"),
1359 createdAt: timestamp("created_at").defaultNow().notNull(),
1360 },
1361 (table) => [
1362 index("oauth_access_tokens_user").on(table.userId),
1363 index("oauth_access_tokens_app").on(table.appId),
1364 index("oauth_access_tokens_expires").on(table.expiresAt),
1365 ]
1366);
1367
1368export type OauthApp = typeof oauthApps.$inferSelect;
1369export type OauthAuthorization = typeof oauthAuthorizations.$inferSelect;
1370export type OauthAccessToken = typeof oauthAccessTokens.$inferSelect;
eafe8c6Claude1371
1372/**
1373 * Actions-equivalent workflow runner (Block C1).
1374 *
1375 * `workflows` rows are the YAML files discovered at `.gluecron/workflows/*.yml`
1376 * on the repo's default branch. `parsed` is the normalised JSON form used by
1377 * the runner so we don't re-parse on every trigger.
1378 *
1379 * `workflow_runs` is one execution: one row per trigger event. Status
1380 * progression: queued → running → success|failure|cancelled. `conclusion`
1381 * stays null until `status` is terminal.
1382 *
1383 * `workflow_jobs` is a single job within a run — each has its own steps
1384 * array and concatenated logs. We keep logs inline for v1 (no streaming)
1385 * to avoid a fifth table; they're truncated at the runner.
1386 *
1387 * `workflow_artifacts` persist files a job uploaded. `content` is a bytea;
1388 * we'll move this to object storage once we hit size limits.
1389 */
1390export const workflows = pgTable(
1391 "workflows",
1392 {
1393 id: uuid("id").primaryKey().defaultRandom(),
1394 repositoryId: uuid("repository_id")
1395 .notNull()
1396 .references(() => repositories.id, { onDelete: "cascade" }),
1397 name: text("name").notNull(),
1398 path: text("path").notNull(), // e.g. ".gluecron/workflows/ci.yml"
1399 yaml: text("yaml").notNull(),
1400 parsed: text("parsed").notNull(), // JSON string
1401 onEvents: text("on_events").notNull().default("[]"), // JSON array of event names
1402 disabled: boolean("disabled").default(false).notNull(),
1403 createdAt: timestamp("created_at").defaultNow().notNull(),
1404 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1405 },
1406 (table) => [
1407 index("workflows_repo").on(table.repositoryId),
1408 uniqueIndex("workflows_repo_path").on(table.repositoryId, table.path),
1409 ]
1410);
1411
1412export const workflowRuns = pgTable(
1413 "workflow_runs",
1414 {
1415 id: uuid("id").primaryKey().defaultRandom(),
1416 workflowId: uuid("workflow_id")
1417 .notNull()
1418 .references(() => workflows.id, { onDelete: "cascade" }),
1419 repositoryId: uuid("repository_id")
1420 .notNull()
1421 .references(() => repositories.id, { onDelete: "cascade" }),
1422 runNumber: integer("run_number").notNull(),
1423 event: text("event").notNull(), // "push" | "pull_request" | "manual" | ...
1424 ref: text("ref"),
1425 commitSha: text("commit_sha"),
1426 triggeredBy: uuid("triggered_by").references(() => users.id, {
1427 onDelete: "set null",
1428 }),
1429 status: text("status").notNull().default("queued"), // queued|running|success|failure|cancelled
1430 conclusion: text("conclusion"),
1431 queuedAt: timestamp("queued_at").defaultNow().notNull(),
1432 startedAt: timestamp("started_at"),
1433 finishedAt: timestamp("finished_at"),
1434 createdAt: timestamp("created_at").defaultNow().notNull(),
1435 },
1436 (table) => [
1437 index("workflow_runs_repo").on(table.repositoryId),
1438 index("workflow_runs_status").on(table.status),
1439 index("workflow_runs_workflow").on(table.workflowId),
1440 ]
1441);
1442
1443export const workflowJobs = pgTable(
1444 "workflow_jobs",
1445 {
1446 id: uuid("id").primaryKey().defaultRandom(),
1447 runId: uuid("run_id")
1448 .notNull()
1449 .references(() => workflowRuns.id, { onDelete: "cascade" }),
1450 name: text("name").notNull(),
1451 jobOrder: integer("job_order").default(0).notNull(),
1452 runsOn: text("runs_on").notNull().default("default"),
1453 status: text("status").notNull().default("queued"),
1454 conclusion: text("conclusion"),
1455 exitCode: integer("exit_code"),
1456 steps: text("steps").notNull().default("[]"), // JSON array of step results
1457 logs: text("logs").notNull().default(""),
1458 startedAt: timestamp("started_at"),
1459 finishedAt: timestamp("finished_at"),
1460 createdAt: timestamp("created_at").defaultNow().notNull(),
1461 },
1462 (table) => [index("workflow_jobs_run").on(table.runId)]
1463);
1464
1465export const workflowArtifacts = pgTable(
1466 "workflow_artifacts",
1467 {
1468 id: uuid("id").primaryKey().defaultRandom(),
1469 runId: uuid("run_id")
1470 .notNull()
1471 .references(() => workflowRuns.id, { onDelete: "cascade" }),
1472 jobId: uuid("job_id").references(() => workflowJobs.id, {
1473 onDelete: "set null",
1474 }),
1475 name: text("name").notNull(),
1476 sizeBytes: integer("size_bytes").default(0).notNull(),
1477 contentType: text("content_type")
1478 .default("application/octet-stream")
1479 .notNull(),
1480 // bytea — drizzle doesn't have a built-in bytea type at the level we use
1481 // elsewhere; store as text (base64) for v1. Migration uses real bytea so
1482 // we can swap the column type later.
1483 content: text("content"),
1484 createdAt: timestamp("created_at").defaultNow().notNull(),
1485 },
1486 (table) => [index("workflow_artifacts_run").on(table.runId)]
1487);
1488
1489export type Workflow = typeof workflows.$inferSelect;
1490export type WorkflowRun = typeof workflowRuns.$inferSelect;
1491export type WorkflowJob = typeof workflowJobs.$inferSelect;
1492export type WorkflowArtifact = typeof workflowArtifacts.$inferSelect;
e2da5c6Claude1493
1494// ---------------------------------------------------------------------------
1495// Block C2 — Package registry (npm-compatible)
1496// ---------------------------------------------------------------------------
1497
1498export const packages = pgTable(
1499 "packages",
1500 {
1501 id: uuid("id").primaryKey().defaultRandom(),
1502 repositoryId: uuid("repository_id")
1503 .notNull()
1504 .references(() => repositories.id, { onDelete: "cascade" }),
1505 ecosystem: text("ecosystem").notNull().default("npm"), // "npm" | "container"
1506 scope: text("scope"), // "@acme" for npm; null for unscoped
1507 name: text("name").notNull(), // "my-lib" (without scope)
1508 description: text("description"),
1509 readme: text("readme"),
1510 homepage: text("homepage"),
1511 license: text("license"),
1512 visibility: text("visibility").notNull().default("public"), // "public" | "private"
1513 createdAt: timestamp("created_at").defaultNow().notNull(),
1514 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1515 },
1516 (table) => [
1517 index("packages_repo").on(table.repositoryId),
1518 uniqueIndex("packages_eco_scope_name").on(
1519 table.ecosystem,
1520 table.scope,
1521 table.name
1522 ),
1523 ]
1524);
1525
1526export const packageVersions = pgTable(
1527 "package_versions",
1528 {
1529 id: uuid("id").primaryKey().defaultRandom(),
1530 packageId: uuid("package_id")
1531 .notNull()
1532 .references(() => packages.id, { onDelete: "cascade" }),
1533 version: text("version").notNull(), // "1.2.3"
1534 shasum: text("shasum").notNull(), // sha1 (for npm compat) hex
1535 integrity: text("integrity"), // "sha512-..." base64
1536 sizeBytes: integer("size_bytes").default(0).notNull(),
1537 metadata: text("metadata").notNull().default("{}"), // package.json JSON
1538 tarball: text("tarball"), // base64-encoded; bytea in migration
1539 publishedBy: uuid("published_by").references(() => users.id, {
1540 onDelete: "set null",
1541 }),
1542 yanked: boolean("yanked").default(false).notNull(),
1543 yankedReason: text("yanked_reason"),
1544 publishedAt: timestamp("published_at").defaultNow().notNull(),
1545 },
1546 (table) => [
1547 index("package_versions_pkg").on(table.packageId),
1548 uniqueIndex("package_versions_pkg_version").on(table.packageId, table.version),
1549 ]
1550);
1551
1552export const packageTags = pgTable(
1553 "package_tags",
1554 {
1555 id: uuid("id").primaryKey().defaultRandom(),
1556 packageId: uuid("package_id")
1557 .notNull()
1558 .references(() => packages.id, { onDelete: "cascade" }),
1559 tag: text("tag").notNull(), // "latest" | "beta" | ...
1560 versionId: uuid("version_id")
1561 .notNull()
1562 .references(() => packageVersions.id, { onDelete: "cascade" }),
1563 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1564 },
1565 (table) => [
1566 uniqueIndex("package_tags_pkg_tag").on(table.packageId, table.tag),
1567 ]
1568);
1569
1570export type Package = typeof packages.$inferSelect;
1571export type PackageVersion = typeof packageVersions.$inferSelect;
1572export type PackageTag = typeof packageTags.$inferSelect;
1573
1574// ---------------------------------------------------------------------------
1575// Block C3 — Pages / static hosting
1576// ---------------------------------------------------------------------------
1577
1578export const pagesDeployments = pgTable(
1579 "pages_deployments",
1580 {
1581 id: uuid("id").primaryKey().defaultRandom(),
1582 repositoryId: uuid("repository_id")
1583 .notNull()
1584 .references(() => repositories.id, { onDelete: "cascade" }),
1585 ref: text("ref").notNull().default("refs/heads/gh-pages"),
1586 commitSha: text("commit_sha").notNull(),
1587 status: text("status").notNull().default("success"), // "success" | "failed"
1588 triggeredBy: uuid("triggered_by").references(() => users.id, {
1589 onDelete: "set null",
1590 }),
1591 createdAt: timestamp("created_at").defaultNow().notNull(),
1592 },
1593 (table) => [
1594 index("pages_deployments_repo").on(table.repositoryId),
1595 index("pages_deployments_created").on(table.createdAt),
1596 ]
1597);
1598
1599export const pagesSettings = pgTable("pages_settings", {
1600 repositoryId: uuid("repository_id")
1601 .primaryKey()
1602 .references(() => repositories.id, { onDelete: "cascade" }),
1603 enabled: boolean("enabled").default(true).notNull(),
1604 sourceBranch: text("source_branch").notNull().default("gh-pages"),
1605 sourceDir: text("source_dir").notNull().default("/"), // e.g. "/" or "/docs"
1606 customDomain: text("custom_domain"),
1607 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1608});
1609
1610export type PagesDeployment = typeof pagesDeployments.$inferSelect;
1611export type PagesSettings = typeof pagesSettings.$inferSelect;
1612
1613// ---------------------------------------------------------------------------
1614// Block C4 — Environments with protected approvals
1615// ---------------------------------------------------------------------------
1616
1617export const environments = pgTable(
1618 "environments",
1619 {
1620 id: uuid("id").primaryKey().defaultRandom(),
1621 repositoryId: uuid("repository_id")
1622 .notNull()
1623 .references(() => repositories.id, { onDelete: "cascade" }),
1624 name: text("name").notNull(), // "production" | "staging" | "preview"
1625 requireApproval: boolean("require_approval").default(false).notNull(),
1626 // JSON array of user IDs that can approve deploys.
1627 reviewers: text("reviewers").notNull().default("[]"),
1628 waitTimerMinutes: integer("wait_timer_minutes").default(0).notNull(),
1629 allowedBranches: text("allowed_branches").notNull().default("[]"), // JSON glob patterns
1630 createdAt: timestamp("created_at").defaultNow().notNull(),
1631 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1632 },
1633 (table) => [
1634 uniqueIndex("environments_repo_name").on(table.repositoryId, table.name),
1635 ]
1636);
1637
1638export const deploymentApprovals = pgTable(
1639 "deployment_approvals",
1640 {
1641 id: uuid("id").primaryKey().defaultRandom(),
1642 deploymentId: uuid("deployment_id")
1643 .notNull()
1644 .references(() => deployments.id, { onDelete: "cascade" }),
1645 userId: uuid("user_id")
1646 .notNull()
1647 .references(() => users.id, { onDelete: "cascade" }),
1648 decision: text("decision").notNull(), // "approved" | "rejected"
1649 comment: text("comment"),
1650 createdAt: timestamp("created_at").defaultNow().notNull(),
1651 },
1652 (table) => [
1653 index("deployment_approvals_deployment").on(table.deploymentId),
1654 ]
1655);
1656
1657export type Environment = typeof environments.$inferSelect;
1658export type DeploymentApproval = typeof deploymentApprovals.$inferSelect;
3cbe3d6Claude1659
1660// ---------------------------------------------------------------------------
1661// Block D — AI-native differentiation (migration 0012)
1662// ---------------------------------------------------------------------------
1663
1664// D6 — cached "explain this codebase" markdown keyed on commit sha.
1665export const codebaseExplanations = pgTable(
1666 "codebase_explanations",
1667 {
1668 id: uuid("id").primaryKey().defaultRandom(),
1669 repositoryId: uuid("repository_id")
1670 .notNull()
1671 .references(() => repositories.id, { onDelete: "cascade" }),
1672 commitSha: text("commit_sha").notNull(),
1673 summary: text("summary").notNull(),
1674 markdown: text("markdown").notNull(),
1675 model: text("model").notNull(),
1676 generatedAt: timestamp("generated_at").defaultNow().notNull(),
1677 },
1678 (table) => [
1679 uniqueIndex("codebase_explanations_repo_sha").on(
1680 table.repositoryId,
1681 table.commitSha
1682 ),
1683 ]
1684);
1685
1686// D2 — AI dependency bumper run history.
1687export const depUpdateRuns = pgTable(
1688 "dep_update_runs",
1689 {
1690 id: uuid("id").primaryKey().defaultRandom(),
1691 repositoryId: uuid("repository_id")
1692 .notNull()
1693 .references(() => repositories.id, { onDelete: "cascade" }),
1694 status: text("status").notNull().default("pending"), // pending|running|success|failed|no_updates
1695 ecosystem: text("ecosystem").notNull(), // npm|bun
1696 manifestPath: text("manifest_path").notNull(),
1697 attemptedBumps: text("attempted_bumps").notNull().default("[]"), // JSON
1698 appliedBumps: text("applied_bumps").notNull().default("[]"), // JSON
1699 branchName: text("branch_name"),
1700 prNumber: integer("pr_number"),
1701 errorMessage: text("error_message"),
1702 triggeredBy: uuid("triggered_by").references(() => users.id, {
1703 onDelete: "set null",
1704 }),
1705 createdAt: timestamp("created_at").defaultNow().notNull(),
1706 completedAt: timestamp("completed_at"),
1707 },
1708 (table) => [
1709 index("dep_update_runs_repo").on(table.repositoryId),
1710 index("dep_update_runs_created").on(table.createdAt),
1711 ]
1712);
1713
1714// D1 — code chunks for semantic search. Embedding stored as JSON-encoded
1715// number array in text to avoid requiring pgvector; cosine similarity is
1716// computed in JS. Upgrade path: ALTER COLUMN embedding TYPE vector(1024).
1717export const codeChunks = pgTable(
1718 "code_chunks",
1719 {
1720 id: uuid("id").primaryKey().defaultRandom(),
1721 repositoryId: uuid("repository_id")
1722 .notNull()
1723 .references(() => repositories.id, { onDelete: "cascade" }),
1724 commitSha: text("commit_sha").notNull(),
1725 path: text("path").notNull(),
1726 startLine: integer("start_line").notNull(),
1727 endLine: integer("end_line").notNull(),
1728 content: text("content").notNull(),
1729 embedding: text("embedding"), // JSON number[]
1730 embeddingModel: text("embedding_model"),
1731 createdAt: timestamp("created_at").defaultNow().notNull(),
1732 },
1733 (table) => [
1734 index("code_chunks_repo").on(table.repositoryId),
1735 index("code_chunks_repo_path").on(table.repositoryId, table.path),
1736 ]
1737);
1738
1739export type CodebaseExplanation = typeof codebaseExplanations.$inferSelect;
1740export type DepUpdateRun = typeof depUpdateRuns.$inferSelect;
1e162a8Claude1741
0a69faaClaude1742// ---------------------------------------------------------------------------
1743// Migration 0077 — repo_explain_cache: structured AI analysis per repo
1744// ---------------------------------------------------------------------------
1745
1746export const repoExplainCache = pgTable("repo_explain_cache", {
1747 id: serial("id").primaryKey(),
1748 repoId: uuid("repo_id")
1749 .notNull()
1750 .unique()
1751 .references(() => repositories.id, { onDelete: "cascade" }),
1752 result: jsonb("result").notNull(),
1753 createdAt: timestamp("created_at").defaultNow(),
1754});
1755
1756export type RepoExplainCache = typeof repoExplainCache.$inferSelect;
1757
1e162a8Claude1758// ---------------------------------------------------------------------------
c645a86Claude1759// Block E2 — Discussions (migration 0013 + 0077)
1e162a8Claude1760// ---------------------------------------------------------------------------
1761
c645a86Claude1762/**
1763 * Per-repo discussion categories (migration 0077).
1764 * Seeded lazily on first discussion creation: General, Q&A, Announcements, Ideas.
1765 * is_answerable = true surfaces "Mark as answer" on threads in that category.
1766 */
1767export const discussionCategories = pgTable(
1768 "discussion_categories",
1769 {
1770 id: serial("id").primaryKey(),
1771 repositoryId: uuid("repository_id")
1772 .notNull()
1773 .references(() => repositories.id, { onDelete: "cascade" }),
1774 name: text("name").notNull(),
1775 emoji: text("emoji").notNull().default("💬"),
1776 description: text("description"),
1777 isAnswerable: boolean("is_answerable").notNull().default(false),
1778 },
1779 (table) => [index("discussion_categories_repo").on(table.repositoryId)]
1780);
1781
1782export type DiscussionCategory = typeof discussionCategories.$inferSelect;
1783
1e162a8Claude1784/**
1785 * Discussions — forum-style threaded conversations attached to a repo.
1786 * Similar to GitHub Discussions: categorised + pinnable + answerable.
1787 */
1788export const discussions = pgTable(
1789 "discussions",
1790 {
1791 id: uuid("id").primaryKey().defaultRandom(),
1792 number: serial("number"),
1793 repositoryId: uuid("repository_id")
1794 .notNull()
1795 .references(() => repositories.id, { onDelete: "cascade" }),
1796 authorId: uuid("author_id")
1797 .notNull()
1798 .references(() => users.id),
1799 // one of: "general" | "q-and-a" | "ideas" | "announcements" | "show-and-tell"
1800 category: text("category").notNull().default("general"),
1801 title: text("title").notNull(),
1802 body: text("body"),
1803 state: text("state").notNull().default("open"), // open, closed
1804 locked: boolean("locked").notNull().default(false),
1805 answerCommentId: uuid("answer_comment_id"),
1806 pinned: boolean("pinned").notNull().default(false),
1807 createdAt: timestamp("created_at").defaultNow().notNull(),
1808 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1809 },
1810 (table) => [
1811 index("discussions_repo").on(table.repositoryId),
1812 uniqueIndex("discussions_repo_number").on(
1813 table.repositoryId,
1814 table.number
1815 ),
1816 ]
1817);
1818
1819export const discussionComments = pgTable(
1820 "discussion_comments",
1821 {
1822 id: uuid("id").primaryKey().defaultRandom(),
1823 discussionId: uuid("discussion_id")
1824 .notNull()
1825 .references(() => discussions.id, { onDelete: "cascade" }),
1826 parentCommentId: uuid("parent_comment_id"),
1827 authorId: uuid("author_id")
1828 .notNull()
1829 .references(() => users.id),
1830 body: text("body").notNull(),
1831 isAnswer: boolean("is_answer").notNull().default(false),
1832 createdAt: timestamp("created_at").defaultNow().notNull(),
1833 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1834 },
1835 (table) => [
1836 index("discussion_comments_discussion").on(table.discussionId),
1837 ]
1838);
1839
1840export type Discussion = typeof discussions.$inferSelect;
1841export type DiscussionComment = typeof discussionComments.$inferSelect;
3cbe3d6Claude1842export type CodeChunk = typeof codeChunks.$inferSelect;
1e162a8Claude1843
1844// ---------------------------------------------------------------------------
1845// Block E4 — Gists (migration 0014)
1846// ---------------------------------------------------------------------------
1847//
1848// User-owned small snippets/files that behave like tiny repos. DB-backed
1849// for v1 (no bare git repo): each gist owns a collection of gist_files and
1850// every edit appends a gist_revisions row with a JSON snapshot.
1851
1852export const gists = pgTable(
1853 "gists",
1854 {
1855 id: uuid("id").primaryKey().defaultRandom(),
1856 ownerId: uuid("owner_id")
1857 .notNull()
1858 .references(() => users.id, { onDelete: "cascade" }),
1859 // 8-char hex slug used in pretty URLs (e.g. /gists/a1b2c3d4).
1860 slug: text("slug").notNull().unique(),
1861 title: text("title").notNull().default(""),
1862 description: text("description").notNull().default(""),
1863 isPublic: boolean("is_public").default(true).notNull(),
1864 createdAt: timestamp("created_at").defaultNow().notNull(),
1865 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1866 },
1867 (table) => [index("gists_owner").on(table.ownerId)]
1868);
1869
1870export const gistFiles = pgTable(
1871 "gist_files",
1872 {
1873 id: uuid("id").primaryKey().defaultRandom(),
1874 gistId: uuid("gist_id")
1875 .notNull()
1876 .references(() => gists.id, { onDelete: "cascade" }),
1877 filename: text("filename").notNull(),
1878 // Optional explicit language override; falls back to filename detection.
1879 language: text("language"),
1880 content: text("content").notNull().default(""),
1881 sizeBytes: integer("size_bytes").default(0).notNull(),
1882 },
1883 (table) => [
1884 index("gist_files_gist").on(table.gistId),
1885 uniqueIndex("gist_files_gist_filename").on(table.gistId, table.filename),
1886 ]
1887);
1888
1889export const gistRevisions = pgTable(
1890 "gist_revisions",
1891 {
1892 id: uuid("id").primaryKey().defaultRandom(),
1893 gistId: uuid("gist_id")
1894 .notNull()
1895 .references(() => gists.id, { onDelete: "cascade" }),
1896 revision: integer("revision").notNull(),
1897 // JSON-encoded {filename: content} map capturing the full snapshot at
1898 // this revision. Stored as text to avoid requiring jsonb.
1899 snapshot: text("snapshot").notNull().default("{}"),
1900 authorId: uuid("author_id")
1901 .notNull()
1902 .references(() => users.id, { onDelete: "cascade" }),
1903 message: text("message"),
1904 createdAt: timestamp("created_at").defaultNow().notNull(),
1905 },
1906 (table) => [
1907 index("gist_revisions_gist_rev").on(table.gistId, table.revision),
1908 ]
1909);
1910
1911export const gistStars = pgTable(
1912 "gist_stars",
1913 {
1914 id: uuid("id").primaryKey().defaultRandom(),
1915 gistId: uuid("gist_id")
1916 .notNull()
1917 .references(() => gists.id, { onDelete: "cascade" }),
1918 userId: uuid("user_id")
1919 .notNull()
1920 .references(() => users.id, { onDelete: "cascade" }),
1921 createdAt: timestamp("created_at").defaultNow().notNull(),
1922 },
1923 (table) => [uniqueIndex("gist_stars_gist_user").on(table.gistId, table.userId)]
1924);
1925
1926export type Gist = typeof gists.$inferSelect;
1927export type GistFile = typeof gistFiles.$inferSelect;
1928export type GistRevision = typeof gistRevisions.$inferSelect;
1929export type GistStar = typeof gistStars.$inferSelect;
1930
1931// ---------------------------------------------------------------------------
1932// Block E1 — Projects / kanban (migration 0015)
1933// ---------------------------------------------------------------------------
1934
1935export const projects = pgTable(
1936 "projects",
1937 {
1938 id: uuid("id").primaryKey().defaultRandom(),
1939 number: serial("number"),
1940 repositoryId: uuid("repository_id")
1941 .notNull()
1942 .references(() => repositories.id, { onDelete: "cascade" }),
1943 ownerId: uuid("owner_id")
1944 .notNull()
1945 .references(() => users.id),
1946 title: text("title").notNull(),
1947 description: text("description").notNull().default(""),
1948 state: text("state").notNull().default("open"), // open | closed
1949 createdAt: timestamp("created_at").defaultNow().notNull(),
1950 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1951 },
1952 (table) => [
1953 index("projects_repo").on(table.repositoryId),
1954 uniqueIndex("projects_repo_number").on(table.repositoryId, table.number),
1955 ]
1956);
1957
1958export const projectColumns = pgTable(
1959 "project_columns",
1960 {
1961 id: uuid("id").primaryKey().defaultRandom(),
1962 projectId: uuid("project_id")
1963 .notNull()
1964 .references(() => projects.id, { onDelete: "cascade" }),
1965 name: text("name").notNull(),
1966 position: integer("position").notNull().default(0),
1967 createdAt: timestamp("created_at").defaultNow().notNull(),
1968 },
1969 (table) => [index("project_columns_project").on(table.projectId)]
1970);
1971
1972export const projectItems = pgTable(
1973 "project_items",
1974 {
1975 id: uuid("id").primaryKey().defaultRandom(),
1976 projectId: uuid("project_id")
1977 .notNull()
1978 .references(() => projects.id, { onDelete: "cascade" }),
1979 columnId: uuid("column_id")
1980 .notNull()
1981 .references(() => projectColumns.id, { onDelete: "cascade" }),
1982 position: integer("position").notNull().default(0),
1983 // "note" | "issue" | "pr" — application-level FK on itemId by type
1984 itemType: text("item_type").notNull().default("note"),
1985 itemId: uuid("item_id"),
1986 title: text("title").notNull().default(""),
1987 note: text("note").notNull().default(""),
1988 createdAt: timestamp("created_at").defaultNow().notNull(),
1989 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1990 },
1991 (table) => [
1992 index("project_items_project").on(table.projectId),
1993 index("project_items_column").on(table.columnId, table.position),
1994 ]
1995);
1996
1997export type Project = typeof projects.$inferSelect;
1998export type ProjectColumn = typeof projectColumns.$inferSelect;
1999export type ProjectItem = typeof projectItems.$inferSelect;
2000
2001// ---------------------------------------------------------------------------
2002// Block E3 — Wikis (migration 0016)
2003// ---------------------------------------------------------------------------
2004// DB-backed for v1; git-backed mirror is a future upgrade.
2005
2006export const wikiPages = pgTable(
2007 "wiki_pages",
2008 {
2009 id: uuid("id").primaryKey().defaultRandom(),
2010 repositoryId: uuid("repository_id")
2011 .notNull()
2012 .references(() => repositories.id, { onDelete: "cascade" }),
2013 slug: text("slug").notNull(),
2014 title: text("title").notNull(),
2015 body: text("body").notNull().default(""),
2016 revision: integer("revision").notNull().default(1),
2017 createdAt: timestamp("created_at").defaultNow().notNull(),
2018 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2019 updatedBy: uuid("updated_by").references(() => users.id),
2020 },
2021 (table) => [
2022 index("wiki_pages_repo").on(table.repositoryId),
2023 uniqueIndex("wiki_pages_repo_slug").on(table.repositoryId, table.slug),
2024 ]
2025);
2026
2027export const wikiRevisions = pgTable(
2028 "wiki_revisions",
2029 {
2030 id: uuid("id").primaryKey().defaultRandom(),
2031 pageId: uuid("page_id")
2032 .notNull()
2033 .references(() => wikiPages.id, { onDelete: "cascade" }),
2034 revision: integer("revision").notNull(),
2035 title: text("title").notNull(),
2036 body: text("body").notNull().default(""),
2037 message: text("message"),
2038 authorId: uuid("author_id")
2039 .notNull()
2040 .references(() => users.id),
2041 createdAt: timestamp("created_at").defaultNow().notNull(),
2042 },
2043 (table) => [index("wiki_revisions_page").on(table.pageId, table.revision)]
2044);
2045
2046export type WikiPage = typeof wikiPages.$inferSelect;
2047export type WikiRevision = typeof wikiRevisions.$inferSelect;
a79a9edClaude2048
2049// ---------------------------------------------------------------------------
2050// Block E5 — Merge queues (migration 0017)
2051// ---------------------------------------------------------------------------
2052
2053export const mergeQueueEntries = pgTable(
2054 "merge_queue_entries",
2055 {
2056 id: uuid("id").primaryKey().defaultRandom(),
2057 repositoryId: uuid("repository_id")
2058 .notNull()
2059 .references(() => repositories.id, { onDelete: "cascade" }),
2060 pullRequestId: uuid("pull_request_id")
2061 .notNull()
2062 .references(() => pullRequests.id, { onDelete: "cascade" }),
2063 baseBranch: text("base_branch").notNull(),
2064 // queued | running | merged | failed | dequeued
2065 state: text("state").notNull().default("queued"),
2066 position: integer("position").notNull().default(0),
2067 enqueuedBy: uuid("enqueued_by").references(() => users.id),
2068 enqueuedAt: timestamp("enqueued_at").defaultNow().notNull(),
2069 startedAt: timestamp("started_at"),
2070 finishedAt: timestamp("finished_at"),
2071 errorMessage: text("error_message"),
2072 },
2073 (table) => [
2074 index("merge_queue_repo_branch").on(
2075 table.repositoryId,
2076 table.baseBranch,
2077 table.state
2078 ),
2079 ]
2080);
2081
2082export type MergeQueueEntry = typeof mergeQueueEntries.$inferSelect;
2083
2084// ---------------------------------------------------------------------------
2085// Block E6 — Required status checks matrix (migration 0018)
2086// ---------------------------------------------------------------------------
2087
2088export const branchRequiredChecks = pgTable(
2089 "branch_required_checks",
2090 {
2091 id: uuid("id").primaryKey().defaultRandom(),
2092 branchProtectionId: uuid("branch_protection_id")
2093 .notNull()
2094 .references(() => branchProtection.id, { onDelete: "cascade" }),
2095 checkName: text("check_name").notNull(),
2096 createdAt: timestamp("created_at").defaultNow().notNull(),
2097 },
2098 (table) => [
2099 index("branch_required_checks_rule").on(table.branchProtectionId),
2100 uniqueIndex("branch_required_checks_unique").on(
2101 table.branchProtectionId,
2102 table.checkName
2103 ),
2104 ]
2105);
2106
2107export type BranchRequiredCheck = typeof branchRequiredChecks.$inferSelect;
2108
2109// ---------------------------------------------------------------------------
2110// Block E7 — Protected tags (migration 0019)
2111// ---------------------------------------------------------------------------
2112
2113export const protectedTags = pgTable(
2114 "protected_tags",
2115 {
2116 id: uuid("id").primaryKey().defaultRandom(),
2117 repositoryId: uuid("repository_id")
2118 .notNull()
2119 .references(() => repositories.id, { onDelete: "cascade" }),
2120 pattern: text("pattern").notNull(),
2121 createdAt: timestamp("created_at").defaultNow().notNull(),
2122 createdBy: uuid("created_by").references(() => users.id),
2123 },
2124 (table) => [
2125 index("protected_tags_repo").on(table.repositoryId),
2126 uniqueIndex("protected_tags_repo_pattern").on(
2127 table.repositoryId,
2128 table.pattern
2129 ),
2130 ]
2131);
2132
2133export type ProtectedTag = typeof protectedTags.$inferSelect;
8f50ed0Claude2134
2135// ---------------------------------------------------------------------------
2136// Block F — Observability + admin (migration 0020)
2137// ---------------------------------------------------------------------------
2138
2139// F1 — Traffic analytics per repo
2140export const repoTrafficEvents = pgTable(
2141 "repo_traffic_events",
2142 {
2143 id: uuid("id").primaryKey().defaultRandom(),
2144 repositoryId: uuid("repository_id")
2145 .notNull()
2146 .references(() => repositories.id, { onDelete: "cascade" }),
2147 kind: text("kind").notNull(), // view | clone | api | ui
2148 path: text("path"),
2149 userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
2150 ipHash: text("ip_hash"),
2151 userAgent: text("user_agent"),
2152 referer: text("referer"),
2153 createdAt: timestamp("created_at").defaultNow().notNull(),
2154 },
2155 (table) => [
2156 index("repo_traffic_events_repo_time").on(
2157 table.repositoryId,
2158 table.createdAt
2159 ),
2160 index("repo_traffic_events_kind").on(
2161 table.repositoryId,
2162 table.kind,
2163 table.createdAt
2164 ),
2165 ]
2166);
2167
2168export type RepoTrafficEvent = typeof repoTrafficEvents.$inferSelect;
2169
2170// F3 — Admin panel (site admins + toggleable flags)
2171export const systemFlags = pgTable("system_flags", {
2172 key: text("key").primaryKey(),
2173 value: text("value").notNull().default(""),
2174 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2175 updatedBy: uuid("updated_by").references(() => users.id),
2176});
2177
2178export type SystemFlag = typeof systemFlags.$inferSelect;
2179
2180export const siteAdmins = pgTable("site_admins", {
2181 userId: uuid("user_id")
2182 .primaryKey()
2183 .references(() => users.id, { onDelete: "cascade" }),
2184 grantedAt: timestamp("granted_at").defaultNow().notNull(),
2185 grantedBy: uuid("granted_by").references(() => users.id),
2186});
2187
2188export type SiteAdmin = typeof siteAdmins.$inferSelect;
2189
509c376Claude2190// /admin/integrations — DB-stored platform integration secrets (migration 0055).
2191// Loaded into process.env at boot so the existing synchronous config getters
2192// keep working transparently. Never exposes DATABASE_URL / SELF_HOST_REPO /
2193// PORT / GIT_REPOS_PATH — those stay env-only.
2194export const systemConfig = pgTable("system_config", {
2195 key: text("key").primaryKey(),
2196 value: text("value").notNull().default(""),
2197 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2198 updatedByUserId: uuid("updated_by_user_id").references(() => users.id, {
2199 onDelete: "set null",
2200 }),
2201});
2202
2203export type SystemConfig = typeof systemConfig.$inferSelect;
2204
8f50ed0Claude2205// F4 — Billing + quotas
2206export const billingPlans = pgTable("billing_plans", {
2207 id: uuid("id").primaryKey().defaultRandom(),
2208 slug: text("slug").notNull().unique(),
2209 name: text("name").notNull(),
2210 priceCents: integer("price_cents").notNull().default(0),
2211 repoLimit: integer("repo_limit").notNull().default(10),
2212 storageMbLimit: integer("storage_mb_limit").notNull().default(1024),
2213 aiTokensMonthly: integer("ai_tokens_monthly").notNull().default(100000),
2214 bandwidthGbMonthly: integer("bandwidth_gb_monthly").notNull().default(10),
2215 privateRepos: boolean("private_repos").notNull().default(false),
2216 createdAt: timestamp("created_at").defaultNow().notNull(),
2217});
2218
2219export type BillingPlan = typeof billingPlans.$inferSelect;
2220
2221export const userQuotas = pgTable("user_quotas", {
2222 userId: uuid("user_id")
2223 .primaryKey()
2224 .references(() => users.id, { onDelete: "cascade" }),
2225 planSlug: text("plan_slug").notNull().default("free"),
2226 storageMbUsed: integer("storage_mb_used").notNull().default(0),
2227 aiTokensUsedThisMonth: integer("ai_tokens_used_this_month")
2228 .notNull()
2229 .default(0),
2230 bandwidthGbUsedThisMonth: integer("bandwidth_gb_used_this_month")
2231 .notNull()
2232 .default(0),
2233 cycleStart: timestamp("cycle_start").defaultNow().notNull(),
2234 updatedAt: timestamp("updated_at").defaultNow().notNull(),
619109aClaude2235 // Stripe linkage (migration 0038). Null until the user first upgrades.
2236 stripeCustomerId: text("stripe_customer_id"),
2237 stripeSubscriptionId: text("stripe_subscription_id"),
2238 stripeSubscriptionStatus: text("stripe_subscription_status"),
2239 currentPeriodEnd: timestamp("current_period_end"),
8f50ed0Claude2240});
2241
2242export type UserQuota = typeof userQuotas.$inferSelect;
06139e6Claude2243
2244// Block H — App marketplace + bot identities (GitHub Apps equivalent)
2245
2246export const apps = pgTable(
2247 "apps",
2248 {
2249 id: uuid("id").primaryKey().defaultRandom(),
2250 slug: text("slug").notNull().unique(),
2251 name: text("name").notNull(),
2252 description: text("description").notNull().default(""),
2253 iconUrl: text("icon_url"),
2254 homepageUrl: text("homepage_url"),
2255 webhookUrl: text("webhook_url"),
2256 webhookSecret: text("webhook_secret"),
2257 creatorId: uuid("creator_id")
2258 .notNull()
2259 .references(() => users.id, { onDelete: "cascade" }),
2260 permissions: text("permissions").notNull().default("[]"), // JSON array
2261 defaultEvents: text("default_events").notNull().default("[]"), // JSON array
2262 isPublic: boolean("is_public").notNull().default(true),
2263 createdAt: timestamp("created_at").defaultNow().notNull(),
2264 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2265 },
2266 (table) => [index("apps_public_slug").on(table.isPublic, table.slug)]
2267);
2268
2269export type App = typeof apps.$inferSelect;
2270
2271export const appInstallations = pgTable(
2272 "app_installations",
2273 {
2274 id: uuid("id").primaryKey().defaultRandom(),
2275 appId: uuid("app_id")
2276 .notNull()
2277 .references(() => apps.id, { onDelete: "cascade" }),
2278 installedBy: uuid("installed_by")
2279 .notNull()
2280 .references(() => users.id, { onDelete: "cascade" }),
2281 targetType: text("target_type").notNull(), // user | org | repository
2282 targetId: uuid("target_id").notNull(),
2283 grantedPermissions: text("granted_permissions").notNull().default("[]"),
2284 suspendedAt: timestamp("suspended_at"),
2285 createdAt: timestamp("created_at").defaultNow().notNull(),
2286 uninstalledAt: timestamp("uninstalled_at"),
2287 },
2288 (table) => [
2289 index("app_installations_app").on(table.appId),
2290 index("app_installations_target").on(table.targetType, table.targetId),
2291 ]
2292);
2293
2294export type AppInstallation = typeof appInstallations.$inferSelect;
2295
2296export const appBots = pgTable("app_bots", {
2297 id: uuid("id").primaryKey().defaultRandom(),
2298 appId: uuid("app_id")
2299 .notNull()
2300 .unique()
2301 .references(() => apps.id, { onDelete: "cascade" }),
2302 username: text("username").notNull().unique(),
2303 displayName: text("display_name").notNull(),
2304 avatarUrl: text("avatar_url"),
2305 createdAt: timestamp("created_at").defaultNow().notNull(),
2306});
2307
2308export type AppBot = typeof appBots.$inferSelect;
2309
2310export const appInstallTokens = pgTable(
2311 "app_install_tokens",
2312 {
2313 id: uuid("id").primaryKey().defaultRandom(),
2314 installationId: uuid("installation_id")
2315 .notNull()
2316 .references(() => appInstallations.id, { onDelete: "cascade" }),
2317 tokenHash: text("token_hash").notNull().unique(),
2318 expiresAt: timestamp("expires_at").notNull(),
2319 createdAt: timestamp("created_at").defaultNow().notNull(),
2320 revokedAt: timestamp("revoked_at"),
2321 },
2322 (table) => [index("app_install_tokens_hash").on(table.tokenHash)]
2323);
2324
2325export type AppInstallToken = typeof appInstallTokens.$inferSelect;
2326
2327export const appEvents = pgTable(
2328 "app_events",
2329 {
2330 id: uuid("id").primaryKey().defaultRandom(),
2331 appId: uuid("app_id")
2332 .notNull()
2333 .references(() => apps.id, { onDelete: "cascade" }),
2334 installationId: uuid("installation_id"),
2335 kind: text("kind").notNull(), // installed | uninstalled | delivery_ok | delivery_fail
2336 payload: text("payload"),
2337 responseStatus: integer("response_status"),
2338 createdAt: timestamp("created_at").defaultNow().notNull(),
2339 },
2340 (table) => [index("app_events_app_time").on(table.appId, table.createdAt)]
2341);
2342
2343export type AppEvent = typeof appEvents.$inferSelect;
71cd5ecClaude2344
2345// ---------- Block I3 — Repository transfer history ----------
2346
2347export const repoTransfers = pgTable(
2348 "repo_transfers",
2349 {
2350 id: uuid("id").primaryKey().defaultRandom(),
2351 repositoryId: uuid("repository_id")
2352 .notNull()
2353 .references(() => repositories.id, { onDelete: "cascade" }),
2354 fromOwnerId: uuid("from_owner_id").notNull(),
2355 fromOrgId: uuid("from_org_id"),
2356 toOwnerId: uuid("to_owner_id").notNull(),
2357 toOrgId: uuid("to_org_id"),
2358 initiatedBy: uuid("initiated_by")
2359 .notNull()
2360 .references(() => users.id, { onDelete: "cascade" }),
2361 createdAt: timestamp("created_at").defaultNow().notNull(),
2362 },
2363 (table) => [
2364 index("repo_transfers_repo").on(table.repositoryId, table.createdAt),
2365 ]
2366);
2367
2368export type RepoTransfer = typeof repoTransfers.$inferSelect;
08420cdClaude2369
2370// ---------- Block I6 — Sponsors ----------
2371
2372export const sponsorshipTiers = pgTable(
2373 "sponsorship_tiers",
2374 {
2375 id: uuid("id").primaryKey().defaultRandom(),
2376 maintainerId: uuid("maintainer_id")
2377 .notNull()
2378 .references(() => users.id, { onDelete: "cascade" }),
2379 name: text("name").notNull(),
2380 description: text("description").default("").notNull(),
2381 monthlyCents: integer("monthly_cents").notNull(),
2382 oneTimeAllowed: boolean("one_time_allowed").default(true).notNull(),
2383 isActive: boolean("is_active").default(true).notNull(),
2384 createdAt: timestamp("created_at").defaultNow().notNull(),
2385 },
2386 (table) => [
2387 index("sponsor_tiers_maintainer").on(table.maintainerId, table.isActive),
2388 ]
2389);
2390
2391export type SponsorshipTier = typeof sponsorshipTiers.$inferSelect;
2392
2393export const sponsorships = pgTable(
2394 "sponsorships",
2395 {
2396 id: uuid("id").primaryKey().defaultRandom(),
2397 sponsorId: uuid("sponsor_id")
2398 .notNull()
2399 .references(() => users.id, { onDelete: "cascade" }),
2400 maintainerId: uuid("maintainer_id")
2401 .notNull()
2402 .references(() => users.id, { onDelete: "cascade" }),
2403 tierId: uuid("tier_id"),
2404 amountCents: integer("amount_cents").notNull(),
2405 kind: text("kind").notNull(), // one_time | monthly
2406 note: text("note"),
2407 isPublic: boolean("is_public").default(true).notNull(),
2408 externalRef: text("external_ref"),
2409 cancelledAt: timestamp("cancelled_at"),
2410 createdAt: timestamp("created_at").defaultNow().notNull(),
2411 },
2412 (table) => [
2413 index("sponsorships_maintainer").on(
2414 table.maintainerId,
2415 table.createdAt
2416 ),
2417 index("sponsorships_sponsor").on(table.sponsorId, table.createdAt),
2418 ]
2419);
2420
2421export type Sponsorship = typeof sponsorships.$inferSelect;
4c8f666Claude2422
2423// Block I8 — Code symbol index for xref navigation.
2424export const codeSymbols = pgTable(
2425 "code_symbols",
2426 {
2427 id: uuid("id").primaryKey().defaultRandom(),
2428 repositoryId: uuid("repository_id")
2429 .notNull()
2430 .references(() => repositories.id, { onDelete: "cascade" }),
2431 commitSha: text("commit_sha").notNull(),
2432 name: text("name").notNull(),
2433 kind: text("kind").notNull(), // function | class | interface | type | const | variable
2434 path: text("path").notNull(),
2435 line: integer("line").notNull(),
2436 signature: text("signature"),
2437 createdAt: timestamp("created_at").defaultNow().notNull(),
2438 },
2439 (table) => [
2440 index("code_symbols_repo_name_idx").on(table.repositoryId, table.name),
2441 index("code_symbols_repo_path_idx").on(table.repositoryId, table.path),
2442 ]
2443);
2444
2445export type CodeSymbol = typeof codeSymbols.$inferSelect;
4a0dea1Claude2446
2447// Block I9 — Repository mirroring. One row per mirrored repo + an
2448// append-only log of sync attempts.
2449export const repoMirrors = pgTable("repo_mirrors", {
2450 id: uuid("id").primaryKey().defaultRandom(),
2451 repositoryId: uuid("repository_id")
2452 .notNull()
2453 .unique()
2454 .references(() => repositories.id, { onDelete: "cascade" }),
2455 upstreamUrl: text("upstream_url").notNull(),
2456 intervalMinutes: integer("interval_minutes").default(1440).notNull(),
2457 lastSyncedAt: timestamp("last_synced_at"),
2458 lastStatus: text("last_status"), // "ok" | "error"
2459 lastError: text("last_error"),
2460 isEnabled: boolean("is_enabled").default(true).notNull(),
2461 createdAt: timestamp("created_at").defaultNow().notNull(),
2462 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2463});
2464
2465export type RepoMirror = typeof repoMirrors.$inferSelect;
2466
2467export const repoMirrorRuns = pgTable(
2468 "repo_mirror_runs",
2469 {
2470 id: uuid("id").primaryKey().defaultRandom(),
2471 mirrorId: uuid("mirror_id")
2472 .notNull()
2473 .references(() => repoMirrors.id, { onDelete: "cascade" }),
2474 startedAt: timestamp("started_at").defaultNow().notNull(),
2475 finishedAt: timestamp("finished_at"),
2476 status: text("status").default("running").notNull(),
2477 message: text("message"),
2478 exitCode: integer("exit_code"),
2479 },
2480 (table) => [
2481 index("repo_mirror_runs_mirror_id_idx").on(table.mirrorId, table.startedAt),
2482 ]
2483);
2484
2485export type RepoMirrorRun = typeof repoMirrorRuns.$inferSelect;
edf7c36Claude2486
2487// ----------------------------------------------------------------------------
2488// Block I10 — Enterprise SSO (OIDC)
2489// ----------------------------------------------------------------------------
2490
2491/** Site-wide SSO provider. Singleton row with id = 'default'. */
2492export const ssoConfig = pgTable("sso_config", {
2493 id: text("id").primaryKey(),
2494 enabled: boolean("enabled").default(false).notNull(),
2495 providerName: text("provider_name").default("SSO").notNull(),
2496 issuer: text("issuer"),
2497 authorizationEndpoint: text("authorization_endpoint"),
2498 tokenEndpoint: text("token_endpoint"),
2499 userinfoEndpoint: text("userinfo_endpoint"),
2500 clientId: text("client_id"),
2501 clientSecret: text("client_secret"),
2502 scopes: text("scopes").default("openid profile email").notNull(),
2503 allowedEmailDomains: text("allowed_email_domains"),
2504 autoCreateUsers: boolean("auto_create_users").default(true).notNull(),
2505 createdAt: timestamp("created_at").defaultNow().notNull(),
2506 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2507});
2508
2509export type SsoConfig = typeof ssoConfig.$inferSelect;
2510
2511/** Maps a local user to an IdP `sub` claim. */
2512export const ssoUserLinks = pgTable(
2513 "sso_user_links",
2514 {
2515 id: uuid("id").primaryKey().defaultRandom(),
2516 userId: uuid("user_id")
2517 .notNull()
2518 .references(() => users.id, { onDelete: "cascade" }),
2519 subject: text("subject").notNull().unique(),
2520 emailAtLink: text("email_at_link").notNull(),
2521 linkedAt: timestamp("linked_at").defaultNow().notNull(),
2522 },
2523 (table) => [index("sso_user_links_user_id_idx").on(table.userId)]
2524);
2525
2526export type SsoUserLink = typeof ssoUserLinks.$inferSelect;
8098672Claude2527
2528// ----------------------------------------------------------------------------
2529// Block J1 — Dependency graph
2530// ----------------------------------------------------------------------------
2531
2532/**
2533 * Last known set of dependencies parsed from manifest files. Each reindex
2534 * replaces the prior rows for that repo. One row per (ecosystem, name,
2535 * manifest_path) — same name in multiple manifests is kept.
2536 */
2537export const repoDependencies = pgTable(
2538 "repo_dependencies",
2539 {
2540 id: uuid("id").primaryKey().defaultRandom(),
2541 repositoryId: uuid("repository_id")
2542 .notNull()
2543 .references(() => repositories.id, { onDelete: "cascade" }),
2544 ecosystem: text("ecosystem").notNull(),
2545 name: text("name").notNull(),
2546 versionSpec: text("version_spec"),
2547 manifestPath: text("manifest_path").notNull(),
2548 isDev: boolean("is_dev").default(false).notNull(),
2549 commitSha: text("commit_sha").notNull(),
2550 indexedAt: timestamp("indexed_at").defaultNow().notNull(),
2551 },
2552 (table) => [
2553 index("repo_dependencies_repo_id_idx").on(
2554 table.repositoryId,
2555 table.ecosystem
2556 ),
2557 index("repo_dependencies_name_idx").on(table.name),
2558 ]
2559);
2560
2561export type RepoDependency = typeof repoDependencies.$inferSelect;
f60ccdeClaude2562
2563// ----------------------------------------------------------------------------
2564// Block J2 — Security advisories + alerts
2565// ----------------------------------------------------------------------------
2566
2567/** CVE-style package advisories. Populated via seed + admin import. */
2568export const securityAdvisories = pgTable(
2569 "security_advisories",
2570 {
2571 id: uuid("id").primaryKey().defaultRandom(),
2572 ghsaId: text("ghsa_id").unique(),
2573 cveId: text("cve_id"),
2574 summary: text("summary").notNull(),
2575 severity: text("severity").default("moderate").notNull(),
2576 ecosystem: text("ecosystem").notNull(),
2577 packageName: text("package_name").notNull(),
2578 affectedRange: text("affected_range").notNull(),
2579 fixedVersion: text("fixed_version"),
2580 referenceUrl: text("reference_url"),
2581 publishedAt: timestamp("published_at").defaultNow().notNull(),
2582 },
2583 (table) => [
2584 index("security_advisories_pkg_idx").on(
2585 table.ecosystem,
2586 table.packageName
2587 ),
2588 ]
2589);
2590
2591export type SecurityAdvisory = typeof securityAdvisories.$inferSelect;
2592
2593/** Per-repo match state. One row per (repo, advisory, manifest_path). */
2594export const repoAdvisoryAlerts = pgTable(
2595 "repo_advisory_alerts",
2596 {
2597 id: uuid("id").primaryKey().defaultRandom(),
2598 repositoryId: uuid("repository_id")
2599 .notNull()
2600 .references(() => repositories.id, { onDelete: "cascade" }),
2601 advisoryId: uuid("advisory_id")
2602 .notNull()
2603 .references(() => securityAdvisories.id, { onDelete: "cascade" }),
2604 dependencyName: text("dependency_name").notNull(),
2605 dependencyVersion: text("dependency_version"),
2606 manifestPath: text("manifest_path").notNull(),
2607 status: text("status").default("open").notNull(),
2608 dismissedReason: text("dismissed_reason"),
2609 createdAt: timestamp("created_at").defaultNow().notNull(),
2610 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2611 },
2612 (table) => [
2613 index("repo_advisory_alerts_status_idx").on(
2614 table.repositoryId,
2615 table.status
2616 ),
2617 ]
2618);
2619
2620export type RepoAdvisoryAlert = typeof repoAdvisoryAlerts.$inferSelect;
3951454Claude2621
2622// ----------------------------------------------------------------------------
2623// Block J3 — Commit signature verification (GPG + SSH)
2624// ----------------------------------------------------------------------------
2625
2626/** Per-user GPG/SSH public keys for commit signing. */
2627export const signingKeys = pgTable(
2628 "signing_keys",
2629 {
2630 id: uuid("id").primaryKey().defaultRandom(),
2631 userId: uuid("user_id")
2632 .notNull()
2633 .references(() => users.id, { onDelete: "cascade" }),
2634 keyType: text("key_type").notNull(), // 'gpg' | 'ssh'
2635 title: text("title").notNull(),
2636 fingerprint: text("fingerprint").notNull(),
2637 publicKey: text("public_key").notNull(),
2638 email: text("email"),
2639 expiresAt: timestamp("expires_at"),
2640 lastUsedAt: timestamp("last_used_at"),
2641 createdAt: timestamp("created_at").defaultNow().notNull(),
2642 },
2643 (table) => [
2644 uniqueIndex("signing_keys_fp_unique").on(table.keyType, table.fingerprint),
2645 index("signing_keys_user_idx").on(table.userId),
2646 ]
2647);
2648
2649export type SigningKey = typeof signingKeys.$inferSelect;
2650
2651/**
2652 * Cached verification result for a (repo, commit) pair. Repopulated on demand;
2653 * rows are invalidated implicitly by CASCADE when either side is removed.
2654 */
2655export const commitVerifications = pgTable(
2656 "commit_verifications",
2657 {
2658 id: uuid("id").primaryKey().defaultRandom(),
2659 repositoryId: uuid("repository_id")
2660 .notNull()
2661 .references(() => repositories.id, { onDelete: "cascade" }),
2662 commitSha: text("commit_sha").notNull(),
2663 verified: boolean("verified").default(false).notNull(),
2664 reason: text("reason").notNull(),
2665 signatureType: text("signature_type"),
2666 signerKeyId: uuid("signer_key_id").references(() => signingKeys.id, {
2667 onDelete: "set null",
2668 }),
2669 signerUserId: uuid("signer_user_id").references(() => users.id, {
2670 onDelete: "set null",
2671 }),
2672 signerFingerprint: text("signer_fingerprint"),
2673 verifiedAt: timestamp("verified_at").defaultNow().notNull(),
2674 },
2675 (table) => [
2676 uniqueIndex("commit_verifications_sha_unique").on(
2677 table.repositoryId,
2678 table.commitSha
2679 ),
2680 ]
2681);
2682
2683export type CommitVerification = typeof commitVerifications.$inferSelect;
7aa8b99Claude2684
2685// ----------------------------------------------------------------------------
2686// Block J4 — User following
2687// ----------------------------------------------------------------------------
2688
2689/**
2690 * Directed user→user follow edges. Primary key is the composite
2691 * (follower_id, following_id) at the SQL level; drizzle sees it as a
2692 * regular table with a unique index plus a secondary index on the
2693 * reverse-lookup column.
2694 */
2695export const userFollows = pgTable(
2696 "user_follows",
2697 {
2698 followerId: uuid("follower_id")
2699 .notNull()
2700 .references(() => users.id, { onDelete: "cascade" }),
2701 followingId: uuid("following_id")
2702 .notNull()
2703 .references(() => users.id, { onDelete: "cascade" }),
2704 createdAt: timestamp("created_at").defaultNow().notNull(),
2705 },
2706 (table) => [
2707 uniqueIndex("user_follows_pair_unique").on(
2708 table.followerId,
2709 table.followingId
2710 ),
2711 index("user_follows_following_idx").on(table.followingId),
2712 ]
2713);
2714
2715export type UserFollow = typeof userFollows.$inferSelect;
9ff7128Claude2716
2717// ----------------------------------------------------------------------------
2718// Block J6 — Repository rulesets
2719// ----------------------------------------------------------------------------
2720
2721/**
2722 * A ruleset groups N rules under a named policy at enforcement level active /
2723 * evaluate / disabled. Unique per (repo, name) so a repo can carry multiple
2724 * overlapping rulesets (e.g. "release branches" vs "everywhere").
2725 */
2726export const repoRulesets = pgTable(
2727 "repo_rulesets",
2728 {
2729 id: uuid("id").primaryKey().defaultRandom(),
2730 repositoryId: uuid("repository_id")
2731 .notNull()
2732 .references(() => repositories.id, { onDelete: "cascade" }),
2733 name: text("name").notNull(),
2734 enforcement: text("enforcement").default("active").notNull(),
2735 createdBy: uuid("created_by").references(() => users.id, {
2736 onDelete: "set null",
2737 }),
2738 createdAt: timestamp("created_at").defaultNow().notNull(),
2739 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2740 },
2741 (table) => [
2742 index("repo_rulesets_repo_idx").on(table.repositoryId),
2743 uniqueIndex("repo_rulesets_repo_name_unique").on(
2744 table.repositoryId,
2745 table.name
2746 ),
2747 ]
2748);
2749
2750export type RepoRuleset = typeof repoRulesets.$inferSelect;
2751
2752/** Individual rule — type tag plus JSON params. */
2753export const rulesetRules = pgTable(
2754 "ruleset_rules",
2755 {
2756 id: uuid("id").primaryKey().defaultRandom(),
2757 rulesetId: uuid("ruleset_id")
2758 .notNull()
2759 .references(() => repoRulesets.id, { onDelete: "cascade" }),
2760 ruleType: text("rule_type").notNull(),
2761 params: text("params").default("{}").notNull(),
2762 createdAt: timestamp("created_at").defaultNow().notNull(),
2763 },
2764 (table) => [index("ruleset_rules_set_idx").on(table.rulesetId)]
2765);
2766
2767export type RulesetRule = typeof rulesetRules.$inferSelect;
0cdfd89Claude2768
2769// ---------------------------------------------------------------------------
2770// Block J8 — Commit statuses.
2771// ---------------------------------------------------------------------------
2772
2773/**
2774 * External CI / automation posts per-commit (sha, context) statuses. Upsert
2775 * semantics keyed on (repository, commit_sha, context). State vocabulary:
2776 * pending | success | failure | error. Combined rollup logic lives in
2777 * `src/lib/commit-statuses.ts`.
2778 */
2779export const commitStatuses = pgTable(
2780 "commit_statuses",
2781 {
2782 id: uuid("id").primaryKey().defaultRandom(),
2783 repositoryId: uuid("repository_id")
2784 .notNull()
2785 .references(() => repositories.id, { onDelete: "cascade" }),
2786 commitSha: text("commit_sha").notNull(),
2787 state: text("state").notNull(),
2788 context: text("context").default("default").notNull(),
2789 description: text("description"),
2790 targetUrl: text("target_url"),
2791 creatorId: uuid("creator_id").references(() => users.id, {
2792 onDelete: "set null",
2793 }),
2794 createdAt: timestamp("created_at").defaultNow().notNull(),
2795 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2796 },
2797 (table) => [
2798 uniqueIndex("commit_statuses_repo_sha_context_unique").on(
2799 table.repositoryId,
2800 table.commitSha,
2801 table.context
2802 ),
2803 index("commit_statuses_repo_sha_idx").on(
2804 table.repositoryId,
2805 table.commitSha
2806 ),
2807 ]
2808);
2809
2810export type CommitStatus = typeof commitStatuses.$inferSelect;
23d1a81Claude2811
2812// ---------------------------------------------------------------------------
2813// Collaborators — per-repo role grants (read / write / admin).
2814// ---------------------------------------------------------------------------
2815
2816/**
2817 * A user granted access to a repository beyond ownership. Roles are
2818 * hierarchical — 'admin' implies write, write implies read. `invitedBy` tracks
2819 * who added them (nullable once that user is deleted); `acceptedAt` is null
2820 * until the invitee explicitly accepts. Unique per (repo, user) so a given
2821 * user has at most one role per repo.
2822 */
2823export const repoCollaborators = pgTable(
2824 "repo_collaborators",
2825 {
2826 id: uuid("id").primaryKey().defaultRandom(),
2827 repositoryId: uuid("repository_id")
2828 .notNull()
2829 .references(() => repositories.id, { onDelete: "cascade" }),
2830 userId: uuid("user_id")
2831 .notNull()
2832 .references(() => users.id, { onDelete: "cascade" }),
2833 role: text("role", { enum: ["read", "write", "admin"] })
2834 .notNull()
2835 .default("read"),
2836 invitedBy: uuid("invited_by").references(() => users.id, {
2837 onDelete: "set null",
2838 }),
2839 invitedAt: timestamp("invited_at").defaultNow().notNull(),
2840 acceptedAt: timestamp("accepted_at"),
febd4f0Claude2841 // sha256(plaintext) of the outstanding invite token. Set when the owner
2842 // sends an invite, cleared when the invitee accepts. NULL on older rows
2843 // that were auto-accepted before the email flow existed.
2844 inviteTokenHash: text("invite_token_hash"),
23d1a81Claude2845 },
2846 (table) => [
2847 uniqueIndex("repo_collaborators_repo_user_uq").on(
2848 table.repositoryId,
2849 table.userId
2850 ),
2851 index("repo_collaborators_repo_idx").on(table.repositoryId),
2852 index("repo_collaborators_user_idx").on(table.userId),
2853 ]
2854);
2855
2856export type RepoCollaborator = typeof repoCollaborators.$inferSelect;
abfa9adClaude2857
2858// ---------------------------------------------------------------------------
2859// Workflow engine v2 — Sprint 1 additions (drizzle/0037_workflow_engine_v2.sql)
2860//
2861// Strictly additive to Block C1. The original workflow tables defined above
2862// (workflows / workflow_runs / workflow_jobs / workflow_artifacts) are locked
2863// and must not be altered in-place; the four tables below extend the runner
2864// with secrets, workflow_dispatch inputs, a content-addressable cache, and a
2865// warm-runner worker pool.
2866// ---------------------------------------------------------------------------
2867
2868/**
2869 * Per-repo encrypted secrets exposed to workflow jobs as env vars.
2870 *
2871 * `encrypted_value` is base64 of `iv || authTag || ciphertext` produced by
2872 * AES-256-GCM — the crypto lives in `src/lib/workflow-crypto.ts` (Agent 2);
2873 * the DB only stores opaque ciphertext. `name` is validated against
2874 * `[A-Z_][A-Z0-9_]*` at the write-site, not the DB. Unique per (repo, name).
2875 */
2876export const workflowSecrets = pgTable(
2877 "workflow_secrets",
2878 {
2879 id: uuid("id").primaryKey().defaultRandom(),
2880 repositoryId: uuid("repository_id")
2881 .notNull()
2882 .references(() => repositories.id, { onDelete: "cascade" }),
2883 name: text("name").notNull(),
2884 encryptedValue: text("encrypted_value").notNull(),
2885 createdBy: uuid("created_by").references(() => users.id, {
2886 onDelete: "set null",
2887 }),
2888 createdAt: timestamp("created_at").defaultNow().notNull(),
2889 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2890 },
2891 (table) => [
2892 uniqueIndex("workflow_secrets_repo_name_uq").on(
2893 table.repositoryId,
2894 table.name
2895 ),
2896 index("workflow_secrets_repo_idx").on(table.repositoryId),
2897 ]
2898);
2899
2900export type WorkflowSecret = typeof workflowSecrets.$inferSelect;
2901export type NewWorkflowSecret = typeof workflowSecrets.$inferInsert;
2902
2903/**
2904 * Parameter schema for the `workflow_dispatch` trigger. One row per input
2905 * declared in the workflow YAML. `type` is constrained to the four values
2906 * GitHub Actions supports; `options` is only meaningful for type='choice'
2907 * (a JSON array of allowed strings). `default_value` and `description` are
2908 * optional. Unique per (workflow, name) so two inputs on the same workflow
2909 * can't share an identifier.
2910 */
2911export const workflowDispatchInputs = pgTable(
2912 "workflow_dispatch_inputs",
2913 {
2914 id: uuid("id").primaryKey().defaultRandom(),
2915 workflowId: uuid("workflow_id")
2916 .notNull()
2917 .references(() => workflows.id, { onDelete: "cascade" }),
2918 name: text("name").notNull(),
2919 type: text("type", {
2920 enum: ["string", "boolean", "choice", "number"],
2921 }).notNull(),
2922 required: boolean("required").default(false).notNull(),
2923 defaultValue: text("default_value"),
2924 // JSON array of strings; only populated when type = 'choice'.
2925 options: jsonb("options"),
2926 description: text("description"),
2927 },
2928 (table) => [
2929 uniqueIndex("workflow_dispatch_inputs_wf_name_uq").on(
2930 table.workflowId,
2931 table.name
2932 ),
2933 ]
2934);
2935
2936export type WorkflowDispatchInput =
2937 typeof workflowDispatchInputs.$inferSelect;
2938export type NewWorkflowDispatchInput =
2939 typeof workflowDispatchInputs.$inferInsert;
2940
2941/**
2942 * Content-addressable cache backing the `gluecron/cache@v1` action. Rows are
2943 * keyed by user-chosen `cache_key` within a `scope` — 'repo' for repo-wide
2944 * entries, 'branch' or 'tag' when isolation is desired (with `scope_ref`
2945 * holding the branch/tag name). `content_hash` is the sha256 of `content`
2946 * so jobs can short-circuit redundant writes. `content` is real bytea; the
2947 * 100MB payload cap is enforced at the write-site, not by the DB. LRU
2948 * eviction reads (`repository_id`, `last_accessed_at`).
2949 */
2950export const workflowRunCache = pgTable(
2951 "workflow_run_cache",
2952 {
2953 id: uuid("id").primaryKey().defaultRandom(),
2954 repositoryId: uuid("repository_id")
2955 .notNull()
2956 .references(() => repositories.id, { onDelete: "cascade" }),
2957 cacheKey: text("cache_key").notNull(),
2958 // 'repo' | 'branch' | 'tag' — constraint enforced at the write-site.
2959 scope: text("scope").default("repo").notNull(),
2960 scopeRef: text("scope_ref"),
2961 contentHash: text("content_hash").notNull(),
2962 content: bytea("content").notNull(),
2963 sizeBytes: bigint("size_bytes", { mode: "number" }).notNull(),
2964 createdAt: timestamp("created_at").defaultNow().notNull(),
2965 lastAccessedAt: timestamp("last_accessed_at").defaultNow().notNull(),
2966 },
2967 (table) => [
2968 uniqueIndex("workflow_run_cache_repo_key_scope_uq").on(
2969 table.repositoryId,
2970 table.cacheKey,
2971 table.scope,
2972 table.scopeRef
2973 ),
2974 index("workflow_run_cache_repo_lru_idx").on(
2975 table.repositoryId,
2976 table.lastAccessedAt
2977 ),
2978 ]
2979);
2980
2981export type WorkflowRunCache = typeof workflowRunCache.$inferSelect;
2982export type NewWorkflowRunCache = typeof workflowRunCache.$inferInsert;
2983
2984/**
2985 * Warm-runner worker registry. The scheduler reads idle rows to dispatch
2986 * queued jobs without paying cold-start cost; workers heartbeat here so
2987 * stale entries (>N seconds since `last_heartbeat_at`) can be reaped.
2988 * `worker_id` is a stable string (hostname:pid or similar) and is globally
2989 * unique so a restarted worker naturally replaces its predecessor.
2990 * `current_run_id` is set when status='busy' and cleared on completion.
2991 */
2992export const workflowRunnerPool = pgTable(
2993 "workflow_runner_pool",
2994 {
2995 id: uuid("id").primaryKey().defaultRandom(),
2996 workerId: text("worker_id").notNull().unique(),
2997 status: text("status", {
2998 enum: ["idle", "busy", "draining", "dead"],
2999 }).notNull(),
3000 currentRunId: uuid("current_run_id").references(() => workflowRuns.id, {
3001 onDelete: "set null",
3002 }),
3003 warmedAt: timestamp("warmed_at").defaultNow().notNull(),
3004 lastHeartbeatAt: timestamp("last_heartbeat_at").defaultNow().notNull(),
3005 capacity: integer("capacity").default(1).notNull(),
3006 },
3007 (table) => [index("workflow_runner_pool_status_idx").on(table.status)]
3008);
3009
3010export type WorkflowRunnerPoolEntry =
3011 typeof workflowRunnerPool.$inferSelect;
3012export type NewWorkflowRunnerPoolEntry =
3013 typeof workflowRunnerPool.$inferInsert;
e6bad81Claude3014
3015
3016/**
3017 * Repair Flywheel — every auto-repair attempt is recorded here so future
3018 * failures with the same signature can short-circuit straight to the cached
3019 * patch (Tier 0). After ~5000 entries the cache dominates and AI calls drop.
3020 * Migration: 0039_repair_flywheel.sql
3021 */
3022export const repairFlywheel = pgTable(
3023 "repair_flywheel",
3024 {
3025 id: uuid("id").primaryKey().defaultRandom(),
3026 repositoryId: uuid("repository_id").references(() => repositories.id, {
3027 onDelete: "cascade",
3028 }),
3029 // Fingerprint of the normalised failure text (variables/paths stripped).
3030 failureSignature: text("failure_signature").notNull(),
3031 // Original failure text (capped at write-site, ~4KB).
3032 failureText: text("failure_text").notNull(),
3033 // Mechanical classification or NULL if AI/human-driven.
3034 failureClassification: text("failure_classification"),
3035 // 'cached' | 'mechanical' | 'ai-sonnet' | 'human'
3036 repairTier: text("repair_tier").notNull(),
3037 patchSummary: text("patch_summary").notNull(),
3038 filesChanged: jsonb("files_changed").notNull().default([]),
3039 commitSha: text("commit_sha"),
3040 // 'pending' | 'success' | 'failed' | 'reverted'
3041 outcome: text("outcome").notNull().default("pending"),
3042 appliedAt: timestamp("applied_at").defaultNow().notNull(),
3043 outcomeAt: timestamp("outcome_at"),
3044 parentPatternId: uuid("parent_pattern_id"),
3045 cacheHitCount: integer("cache_hit_count").default(0).notNull(),
3046 isPublicPattern: boolean("is_public_pattern").default(false).notNull(),
3047 createdAt: timestamp("created_at").defaultNow().notNull(),
3048 },
3049 (table) => [
3050 index("repair_flywheel_signature_idx").on(table.failureSignature),
3051 index("repair_flywheel_repo_idx").on(table.repositoryId),
3052 index("repair_flywheel_outcome_idx").on(table.outcome),
3053 index("repair_flywheel_classification_idx").on(table.failureClassification),
3054 index("repair_flywheel_lookup_idx").on(
3055 table.repositoryId,
3056 table.failureSignature,
3057 table.outcome
3058 ),
3059 ]
3060);
3061
3062export type RepairFlywheelEntry = typeof repairFlywheel.$inferSelect;
3063export type NewRepairFlywheelEntry = typeof repairFlywheel.$inferInsert;
3064
534f04aClaude3065/**
3066 * Block M3 — AI pre-merge risk score cache.
3067 *
3068 * One row per (pull_request_id, commit_sha). The score is computed by a
3069 * transparent formula over `signals`; the LLM only writes `ai_summary`.
3070 * Pinning by head SHA means a new push invalidates the cache implicitly
3071 * (the new SHA won't have a row yet → cache miss → recompute).
3072 *
3073 * Migration: 0044_pr_risk_scores.sql
3074 */
3075export const prRiskScores = pgTable(
3076 "pr_risk_scores",
3077 {
3078 id: uuid("id").primaryKey().defaultRandom(),
3079 pullRequestId: uuid("pull_request_id")
3080 .notNull()
3081 .references(() => pullRequests.id, { onDelete: "cascade" }),
3082 commitSha: text("commit_sha").notNull(),
3083 // 0..10 integer score.
3084 score: integer("score").notNull(),
3085 // 'low' | 'medium' | 'high' | 'critical'
3086 band: text("band").notNull(),
3087 // Raw signal map — see PrRiskSignals in src/lib/pr-risk.ts.
3088 signals: jsonb("signals").notNull(),
3089 aiSummary: text("ai_summary"),
3090 generatedAt: timestamp("generated_at", { withTimezone: true })
3091 .defaultNow()
3092 .notNull(),
3093 },
3094 (table) => [
3095 uniqueIndex("pr_risk_scores_pr_sha_uq").on(
3096 table.pullRequestId,
3097 table.commitSha
3098 ),
3099 index("pr_risk_scores_pr_idx").on(table.pullRequestId),
3100 ]
3101);
3102
3103export type PrRiskScoreRow = typeof prRiskScores.$inferSelect;
3104export type NewPrRiskScoreRow = typeof prRiskScores.$inferInsert;
3105
c63b860Claude3106/**
3107 * Block P1 — Password reset tokens.
3108 *
3109 * One row per outstanding reset request. `tokenHash` is a SHA-256 of the
3110 * plaintext token mailed to the user; the plaintext never persists.
3111 * `usedAt` is flipped on consume so a replayed link cannot rotate the
3112 * password twice. `expiresAt` is enforced at consume time (1-hour TTL).
3113 *
3114 * Migration: 0047_password_reset_tokens.sql
3115 */
3116export const passwordResetTokens = pgTable(
3117 "password_reset_tokens",
3118 {
3119 id: uuid("id").primaryKey().defaultRandom(),
3120 userId: uuid("user_id")
3121 .notNull()
3122 .references(() => users.id, { onDelete: "cascade" }),
3123 tokenHash: text("token_hash").notNull().unique(),
3124 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3125 usedAt: timestamp("used_at", { withTimezone: true }),
3126 requestIp: text("request_ip"),
3127 createdAt: timestamp("created_at", { withTimezone: true })
3128 .defaultNow()
3129 .notNull(),
3130 },
3131 (table) => [
3132 index("idx_password_reset_tokens_user").on(table.userId),
3133 index("idx_password_reset_tokens_expires").on(table.expiresAt),
3134 ]
3135);
3136
3137export type PasswordResetToken = typeof passwordResetTokens.$inferSelect;
3138export type NewPasswordResetToken = typeof passwordResetTokens.$inferInsert;
3139
3140/**
3141 * Block P2 — email verification tokens.
3142 *
3143 * SHA-256 hashed at rest. `email` captured at issuance so a verification
3144 * link still resolves the right address even after a profile-email change.
3145 * Migration: drizzle/0048_email_verification.sql
3146 */
3147export const emailVerificationTokens = pgTable(
3148 "email_verification_tokens",
3149 {
3150 id: uuid("id").primaryKey().defaultRandom(),
3151 userId: uuid("user_id")
3152 .notNull()
3153 .references(() => users.id, { onDelete: "cascade" }),
3154 email: text("email").notNull(),
3155 tokenHash: text("token_hash").notNull().unique(),
3156 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3157 usedAt: timestamp("used_at", { withTimezone: true }),
3158 createdAt: timestamp("created_at", { withTimezone: true })
3159 .defaultNow()
3160 .notNull(),
3161 },
3162 (table) => [index("idx_email_verify_tokens_user").on(table.userId)]
3163);
3164
3165export type EmailVerificationToken = typeof emailVerificationTokens.$inferSelect;
3166export type NewEmailVerificationToken = typeof emailVerificationTokens.$inferInsert;
3167
cd4f63bTest User3168/**
3169 * Block Q2 — Magic-link sign-in tokens.
3170 *
3171 * Structurally identical to passwordResetTokens / emailVerificationTokens:
3172 * a short plaintext token is mailed to the user, only its sha256 hash is
3173 * persisted, the token is single-use (usedAt) and time-limited (expiresAt).
3174 *
3175 * Difference: `userId` is NULLABLE. When a user enters an email that does
3176 * NOT yet have an account, we still mint a row — consume will create the
3177 * account on click (autoCreate=true), using the click itself as proof the
3178 * recipient owns the address. See `src/lib/magic-link.ts`.
3179 *
3180 * Migration: drizzle/0051_magic_link_tokens.sql
3181 */
3182export const magicLinkTokens = pgTable(
3183 "magic_link_tokens",
3184 {
3185 id: uuid("id").primaryKey().defaultRandom(),
3186 email: text("email").notNull(),
3187 userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
3188 tokenHash: text("token_hash").notNull().unique(),
3189 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3190 usedAt: timestamp("used_at", { withTimezone: true }),
3191 requestIp: text("request_ip"),
3192 createdAt: timestamp("created_at", { withTimezone: true })
3193 .defaultNow()
3194 .notNull(),
3195 },
3196 (table) => [
3197 index("idx_magic_link_tokens_email").on(table.email),
3198 index("idx_magic_link_tokens_user").on(table.userId),
3199 index("idx_magic_link_tokens_expires").on(table.expiresAt),
3200 ]
3201);
3202
3203export type MagicLinkToken = typeof magicLinkTokens.$inferSelect;
3204export type NewMagicLinkToken = typeof magicLinkTokens.$inferInsert;
3205
b1be050CC LABS App3206// ---------------------------------------------------------------------------
3207// BLOCK S4 — Synthetic monitor history.
3208//
3209// Every autopilot tick runs `runSyntheticChecks()` (see
3210// `src/lib/synthetic-monitor.ts`) and inserts one row per check here.
3211// The /admin/status page reads the most-recent row per check_name and
3212// renders the red/green dashboard. On a green->red transition we fire
3213// MONITOR_ALERT_WEBHOOK_URL so the owner sees the site is on fire.
3214// ---------------------------------------------------------------------------
3215export const syntheticChecks = pgTable(
3216 "synthetic_checks",
3217 {
3218 id: uuid("id").primaryKey().defaultRandom(),
3219 checkName: text("check_name").notNull(),
3220 status: text("status").notNull(), // "green" | "red" | "yellow"
3221 statusCode: integer("status_code"),
3222 durationMs: integer("duration_ms").notNull(),
3223 error: text("error"),
3224 checkedAt: timestamp("checked_at", { withTimezone: true })
3225 .defaultNow()
3226 .notNull(),
3227 },
3228 (table) => [
3229 index("idx_synthetic_checks_checked_at").on(table.checkedAt),
3230 index("idx_synthetic_checks_name_checked_at").on(
3231 table.checkName,
3232 table.checkedAt
3233 ),
3234 ]
3235);
3236
3237export type SyntheticCheckRow = typeof syntheticChecks.$inferSelect;
3238export type NewSyntheticCheckRow = typeof syntheticChecks.$inferInsert;
3239
a686079Claude3240// ---------------------------------------------------------------------------
3241// 0057 — Continuous semantic index (per-push embeddings).
e75eddcClaude3242// One row per (repository_id, file_path). `indexChangedFiles()` upserts on
3243// every push, `searchSemantic()` ranks by cosine distance via pgvector.
a686079Claude3244// ---------------------------------------------------------------------------
3245export const codeEmbeddings = pgTable(
3246 "code_embeddings",
3247 {
3248 id: uuid("id").primaryKey().defaultRandom(),
3249 repositoryId: uuid("repository_id")
3250 .notNull()
3251 .references(() => repositories.id, { onDelete: "cascade" }),
3252 filePath: text("file_path").notNull(),
3253 blobSha: text("blob_sha").notNull(),
3254 commitSha: text("commit_sha").notNull(),
3255 contentSnippet: text("content_snippet").notNull().default(""),
3256 embedding: vector("embedding", { dimensions: 1024 }),
3257 embeddingModel: text("embedding_model"),
3258 updatedAt: timestamp("updated_at", { withTimezone: true })
3259 .defaultNow()
3260 .notNull(),
3261 },
3262 (table) => [
3263 uniqueIndex("code_embeddings_repo_path_uniq").on(
3264 table.repositoryId,
3265 table.filePath
3266 ),
3267 index("code_embeddings_repo_idx").on(table.repositoryId),
3268 ]
3269);
3270
3271export type CodeEmbedding = typeof codeEmbeddings.$inferSelect;
3272export type NewCodeEmbedding = typeof codeEmbeddings.$inferInsert;
3273
e75eddcClaude3274// ---------------------------------------------------------------------------
3275// 0058 — Agent multiplayer v1: per-agent namespacing + leases + budget caps.
3276// See src/lib/agent-multiplayer.ts for helpers and 0058_agent_multiplayer.sql
3277// for the canonical column docs. UNIQUE partial index on (target_type,
3278// target_id) WHERE status='active' is enforced in SQL (drizzle exposes it
3279// as a regular index here).
3280// ---------------------------------------------------------------------------
3281export const agentSessions = pgTable(
3282 "agent_sessions",
3283 {
3284 id: uuid("id").primaryKey().defaultRandom(),
3285 name: text("name").notNull(),
3286 ownerUserId: uuid("owner_user_id")
3287 .notNull()
3288 .references(() => users.id, { onDelete: "cascade" }),
3289 repositoryId: uuid("repository_id").references(() => repositories.id, {
3290 onDelete: "cascade",
3291 }),
3292 tokenHash: text("token_hash").notNull().unique(),
3293 branchNamespace: text("branch_namespace").notNull(),
3294 budgetCentsPerDay: integer("budget_cents_per_day").default(500).notNull(),
3295 spentCentsToday: integer("spent_cents_today").default(0).notNull(),
3296 lastActiveAt: timestamp("last_active_at", { withTimezone: true }),
3297 createdAt: timestamp("created_at", { withTimezone: true })
3298 .defaultNow()
3299 .notNull(),
3300 },
3301 (table) => [
3302 uniqueIndex("agent_sessions_owner_name").on(table.ownerUserId, table.name),
3303 index("agent_sessions_owner").on(table.ownerUserId),
3304 index("agent_sessions_repo").on(table.repositoryId),
3305 ]
3306);
3307
3308export const agentLeases = pgTable(
3309 "agent_leases",
3310 {
3311 id: uuid("id").primaryKey().defaultRandom(),
3312 agentSessionId: uuid("agent_session_id")
3313 .notNull()
3314 .references(() => agentSessions.id, { onDelete: "cascade" }),
3315 targetType: text("target_type").notNull(),
3316 targetId: text("target_id").notNull(),
3317 acquiredAt: timestamp("acquired_at", { withTimezone: true })
3318 .defaultNow()
3319 .notNull(),
3320 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3321 status: text("status").default("active").notNull(),
3322 createdAt: timestamp("created_at", { withTimezone: true })
3323 .defaultNow()
3324 .notNull(),
3325 },
3326 (table) => [
3327 index("agent_leases_active_target").on(table.targetType, table.targetId),
3328 index("agent_leases_agent").on(table.agentSessionId, table.status),
3329 index("agent_leases_expires").on(table.expiresAt),
3330 ]
3331);
3332
3333export type AgentSession = typeof agentSessions.$inferSelect;
3334export type NewAgentSession = typeof agentSessions.$inferInsert;
3335export type AgentLease = typeof agentLeases.$inferSelect;
3336export type NewAgentLease = typeof agentLeases.$inferInsert;
3337
38d31d3Claude3338// ---------------------------------------------------------------------------
3c03977Claude3339// 0060 — AI repo rubber-duck chat. See src/lib/repo-chat.ts.
38d31d3Claude3340// ---------------------------------------------------------------------------
3341export const repoChats = pgTable(
3342 "repo_chats",
3343 {
3344 id: uuid("id").primaryKey().defaultRandom(),
3345 repositoryId: uuid("repository_id")
3346 .notNull()
3347 .references(() => repositories.id, { onDelete: "cascade" }),
3348 ownerUserId: uuid("owner_user_id")
3349 .notNull()
3350 .references(() => users.id, { onDelete: "cascade" }),
3351 title: text("title"),
3352 createdAt: timestamp("created_at", { withTimezone: true })
3353 .defaultNow()
3354 .notNull(),
3355 updatedAt: timestamp("updated_at", { withTimezone: true })
3356 .defaultNow()
3357 .notNull(),
3358 },
3359 (table) => [
3360 index("repo_chats_owner_updated").on(table.ownerUserId, table.updatedAt),
3361 index("repo_chats_repo").on(table.repositoryId),
3362 ]
3363);
3364
3365export const repoChatMessages = pgTable(
3366 "repo_chat_messages",
3367 {
3368 id: uuid("id").primaryKey().defaultRandom(),
3369 chatId: uuid("chat_id")
3370 .notNull()
3371 .references(() => repoChats.id, { onDelete: "cascade" }),
3372 role: text("role").notNull(),
3373 content: text("content").notNull(),
3374 citations: jsonb("citations").$type<Array<{ file_path: string; blob_sha: string }>>().notNull().default([]),
3375 tokenCost: integer("token_cost").notNull().default(0),
3376 createdAt: timestamp("created_at", { withTimezone: true })
3377 .defaultNow()
3378 .notNull(),
3379 },
3380 (table) => [
3381 index("repo_chat_messages_chat_created").on(table.chatId, table.createdAt),
3382 ]
3383);
3384
3385export type RepoChat = typeof repoChats.$inferSelect;
3386export type NewRepoChat = typeof repoChats.$inferInsert;
3387export type RepoChatMessage = typeof repoChatMessages.$inferSelect;
3388export type NewRepoChatMessage = typeof repoChatMessages.$inferInsert;
3389
ee7e577Claude3390// ---------------------------------------------------------------------------
3391// 0071 — Personal cross-repo chat. Same shape as repoChats/repoChatMessages
3392// but user-scoped, not repo-scoped. The retrieval layer
3393// (src/lib/personal-semantic.ts) runs searchSemantic across the union of
3394// repos the owner has access to, then attaches a repo_name annotation to
3395// each citation. Gated on users.personalSemanticIndexEnabled.
3396// ---------------------------------------------------------------------------
3397export const personalChats = pgTable(
3398 "personal_chats",
3399 {
3400 id: uuid("id").primaryKey().defaultRandom(),
3401 ownerUserId: uuid("owner_user_id")
3402 .notNull()
3403 .references(() => users.id, { onDelete: "cascade" }),
3404 title: text("title"),
3405 createdAt: timestamp("created_at", { withTimezone: true })
3406 .defaultNow()
3407 .notNull(),
3408 updatedAt: timestamp("updated_at", { withTimezone: true })
3409 .defaultNow()
3410 .notNull(),
3411 },
3412 (table) => [
3413 index("personal_chats_owner_updated").on(
3414 table.ownerUserId,
3415 table.updatedAt
3416 ),
3417 ]
3418);
3419
3420export const personalChatMessages = pgTable(
3421 "personal_chat_messages",
3422 {
3423 id: uuid("id").primaryKey().defaultRandom(),
3424 chatId: uuid("chat_id")
3425 .notNull()
3426 .references(() => personalChats.id, { onDelete: "cascade" }),
3427 role: text("role").notNull(),
3428 content: text("content").notNull(),
3429 citations: jsonb("citations")
3430 .$type<
3431 Array<{ file_path: string; blob_sha: string; repo_name: string }>
3432 >()
3433 .notNull()
3434 .default([]),
3435 tokenCost: integer("token_cost").notNull().default(0),
3436 createdAt: timestamp("created_at", { withTimezone: true })
3437 .defaultNow()
3438 .notNull(),
3439 },
3440 (table) => [
3441 index("personal_chat_messages_chat_created").on(
3442 table.chatId,
3443 table.createdAt
3444 ),
3445 ]
3446);
3447
3448export type PersonalChat = typeof personalChats.$inferSelect;
3449export type NewPersonalChat = typeof personalChats.$inferInsert;
3450export type PersonalChatMessage = typeof personalChatMessages.$inferSelect;
3451export type NewPersonalChatMessage = typeof personalChatMessages.$inferInsert;
3452
3c03977Claude3453// ---------------------------------------------------------------------------
3454// 0061 — Live co-editing on PRs. See src/lib/pr-live.ts.
3455// ---------------------------------------------------------------------------
3456export const prLiveSessions = pgTable(
3457 "pr_live_sessions",
3458 {
3459 id: uuid("id").primaryKey().defaultRandom(),
3460 prId: uuid("pr_id")
3461 .notNull()
3462 .references(() => pullRequests.id, { onDelete: "cascade" }),
3463 userId: uuid("user_id").references(() => users.id, {
3464 onDelete: "cascade",
3465 }),
3466 agentSessionId: uuid("agent_session_id").references(
3467 () => agentSessions.id,
3468 { onDelete: "cascade" }
3469 ),
3470 cursorPosition: jsonb("cursor_position"),
3471 color: text("color").notNull(),
3472 joinedAt: timestamp("joined_at", { withTimezone: true })
3473 .defaultNow()
3474 .notNull(),
3475 lastSeenAt: timestamp("last_seen_at", { withTimezone: true })
3476 .defaultNow()
3477 .notNull(),
3478 status: text("status").default("active").notNull(),
3479 },
3480 (table) => [
3481 index("pr_live_sessions_active_pr").on(table.prId, table.lastSeenAt),
3482 index("pr_live_sessions_status_seen").on(table.status, table.lastSeenAt),
3483 ]
3484);
3485
3486export type PrLiveSession = typeof prLiveSessions.$inferSelect;
3487export type NewPrLiveSession = typeof prLiveSessions.$inferInsert;
3488
4bbacbeClaude3489// ---------------------------------------------------------------------------
3490// ---------------------------------------------------------------------------
23d0abfClaude3491// Migration 0062 — per-branch preview URLs. See src/lib/branch-previews.ts.
3492// ---------------------------------------------------------------------------
4bbacbeClaude3493export const branchPreviews = pgTable(
3494 "branch_previews",
3495 {
3496 id: uuid("id").primaryKey().defaultRandom(),
3497 repositoryId: uuid("repository_id")
3498 .notNull()
3499 .references(() => repositories.id, { onDelete: "cascade" }),
3500 branchName: text("branch_name").notNull(),
3501 commitSha: text("commit_sha").notNull(),
3502 previewUrl: text("preview_url").notNull(),
3503 status: text("status").default("building").notNull(),
3504 buildStartedAt: timestamp("build_started_at", { withTimezone: true })
3505 .defaultNow()
3506 .notNull(),
3507 buildCompletedAt: timestamp("build_completed_at", { withTimezone: true }),
3508 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3509 errorMessage: text("error_message"),
3510 createdAt: timestamp("created_at", { withTimezone: true })
3511 .defaultNow()
3512 .notNull(),
3513 },
3514 (table) => [
3515 uniqueIndex("branch_previews_repo_branch").on(
3516 table.repositoryId,
3517 table.branchName
3518 ),
3519 index("branch_previews_repo_status").on(table.repositoryId, table.status),
3520 ]
3521);
3522
3523export type BranchPreview = typeof branchPreviews.$inferSelect;
3524export type NewBranchPreview = typeof branchPreviews.$inferInsert;
3525
23d0abfClaude3526// ---------------------------------------------------------------------------
3527// 0063 — Multi-repo refactor agent. See src/lib/multi-repo-refactor.ts.
3528// ---------------------------------------------------------------------------
3529export const multiRepoRefactors = pgTable(
3530 "multi_repo_refactors",
3531 {
3532 id: uuid("id").primaryKey().defaultRandom(),
3533 ownerUserId: uuid("owner_user_id")
3534 .notNull()
3535 .references(() => users.id, { onDelete: "cascade" }),
3536 title: text("title").notNull(),
3537 description: text("description").notNull(),
3538 status: text("status").default("planning").notNull(),
3539 createdAt: timestamp("created_at").defaultNow().notNull(),
3540 updatedAt: timestamp("updated_at").defaultNow().notNull(),
3541 },
3542 (table) => [
3543 index("multi_repo_refactors_owner").on(table.ownerUserId, table.createdAt),
3544 index("multi_repo_refactors_status").on(table.status, table.updatedAt),
3545 ]
3546);
3547
3548export const multiRepoRefactorPrs = pgTable(
3549 "multi_repo_refactor_prs",
3550 {
3551 id: uuid("id").primaryKey().defaultRandom(),
3552 refactorId: uuid("refactor_id")
3553 .notNull()
3554 .references(() => multiRepoRefactors.id, { onDelete: "cascade" }),
3555 repositoryId: uuid("repository_id")
3556 .notNull()
3557 .references(() => repositories.id, { onDelete: "cascade" }),
3558 pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
3559 onDelete: "set null",
3560 }),
3561 status: text("status").default("pending").notNull(),
3562 errorMessage: text("error_message"),
3563 createdAt: timestamp("created_at").defaultNow().notNull(),
3564 updatedAt: timestamp("updated_at").defaultNow().notNull(),
3565 },
3566 (table) => [
3567 uniqueIndex("multi_repo_refactor_prs_unique").on(
3568 table.refactorId,
3569 table.repositoryId
3570 ),
3571 index("multi_repo_refactor_prs_refactor").on(
3572 table.refactorId,
3573 table.status
3574 ),
3575 index("multi_repo_refactor_prs_repo").on(table.repositoryId),
3576 ]
3577);
3578
3579export type MultiRepoRefactor = typeof multiRepoRefactors.$inferSelect;
3580export type NewMultiRepoRefactor = typeof multiRepoRefactors.$inferInsert;
3581export type MultiRepoRefactorPr = typeof multiRepoRefactorPrs.$inferSelect;
3582export type NewMultiRepoRefactorPr = typeof multiRepoRefactorPrs.$inferInsert;
3583
e3c90cdClaude3584// ─── Chat integrations (migration 0064) ────────────────────────────────────
3585// Slack / Discord / Teams bindings. One row per (owner, kind, team, channel).
3586// `webhook_url` is the outbound Incoming-Webhook URL for posting back into
3587// the channel; `signing_secret` is the per-install verification key (HMAC for
3588// Slack, Ed25519 public key for Discord). See drizzle/0064_chat_integrations.sql
3589// for the full schema rationale.
3590export const chatIntegrations = pgTable(
3591 "chat_integrations",
3592 {
3593 id: uuid("id").primaryKey().defaultRandom(),
3594 ownerUserId: uuid("owner_user_id")
3595 .notNull()
3596 .references(() => users.id, { onDelete: "cascade" }),
3597 kind: text("kind").notNull(), // 'slack' | 'discord' | 'teams'
3598 teamId: text("team_id"),
3599 channelId: text("channel_id"),
3600 webhookUrl: text("webhook_url"),
3601 signingSecret: text("signing_secret"),
3602 enabled: boolean("enabled").default(true).notNull(),
3603 createdAt: timestamp("created_at").defaultNow().notNull(),
3604 lastUsedAt: timestamp("last_used_at"),
3605 },
3606 (table) => [
3607 index("chat_integrations_owner").on(table.ownerUserId, table.kind),
3608 ]
3609);
3610
3611export type ChatIntegration = typeof chatIntegrations.$inferSelect;
3612export type NewChatIntegration = typeof chatIntegrations.$inferInsert;
3613
0c3eee5Claude3614// ---------------------------------------------------------------------------
3615// 0065 — Per-call AI cost tracking. See src/lib/ai-cost-tracker.ts and
3616// src/routes/billing-usage.tsx. Best-effort: insert failures must NEVER
3617// throw out of the Claude caller, so every call site wraps recordAiCost in
3618// try/catch. Cents are computed at insert time from a hardcoded pricing
3619// table so historical aggregates stay stable across price changes.
3620// ---------------------------------------------------------------------------
3621export const aiCostEvents = pgTable(
3622 "ai_cost_events",
3623 {
3624 id: uuid("id").primaryKey().defaultRandom(),
3625 occurredAt: timestamp("occurred_at", { withTimezone: true })
3626 .defaultNow()
3627 .notNull(),
3628 ownerUserId: uuid("owner_user_id").references(() => users.id, {
3629 onDelete: "set null",
3630 }),
3631 repositoryId: uuid("repository_id").references(() => repositories.id, {
3632 onDelete: "set null",
3633 }),
3634 agentSessionId: uuid("agent_session_id").references(
3635 () => agentSessions.id,
3636 { onDelete: "set null" }
3637 ),
3638 model: text("model").notNull(),
3639 inputTokens: integer("input_tokens").default(0).notNull(),
3640 outputTokens: integer("output_tokens").default(0).notNull(),
3641 centsEstimate: integer("cents_estimate").default(0).notNull(),
3642 category: text("category").default("other").notNull(),
3643 sourceId: text("source_id"),
3644 sourceKind: text("source_kind"),
3645 createdAt: timestamp("created_at", { withTimezone: true })
3646 .defaultNow()
3647 .notNull(),
3648 },
3649 (table) => [
3650 index("ai_cost_events_owner_time").on(table.ownerUserId, table.occurredAt),
3651 index("ai_cost_events_repo_time").on(table.repositoryId, table.occurredAt),
3652 index("ai_cost_events_agent_time").on(
3653 table.agentSessionId,
3654 table.occurredAt
3655 ),
3656 index("ai_cost_events_category_time").on(table.category, table.occurredAt),
3657 ]
3658);
3659
3660export type AiCostEvent = typeof aiCostEvents.$inferSelect;
3661export type NewAiCostEvent = typeof aiCostEvents.$inferInsert;
3662
3663export const aiBudgets = pgTable("ai_budgets", {
3664 userId: uuid("user_id")
3665 .primaryKey()
3666 .references(() => users.id, { onDelete: "cascade" }),
3667 monthlyCents: integer("monthly_cents").default(0).notNull(),
3668 updatedAt: timestamp("updated_at", { withTimezone: true })
3669 .defaultNow()
3670 .notNull(),
3671});
3672
3673export type AiBudget = typeof aiBudgets.$inferSelect;
3674export type NewAiBudget = typeof aiBudgets.$inferInsert;
79ed944Claude3675
3676// ---------------------------------------------------------------------------
3677// Migration 0067 — per-PR runnable sandboxes. See src/lib/pr-sandbox.ts.
3678//
3679// Each open PR can hold at most one sandbox row (UNIQUE on pr_id). The row
3680// owns the lifecycle (provisioning → ready / failed / destroyed) plus the
3681// resolved playground.yml the sandbox was provisioned from. Re-provisioning
3682// the same PR upserts onto the existing row so a force-push always points
3683// at the newest head.
3684// ---------------------------------------------------------------------------
3685export const prSandboxes = pgTable(
3686 "pr_sandboxes",
3687 {
3688 id: uuid("id").primaryKey().defaultRandom(),
3689 prId: uuid("pr_id")
3690 .notNull()
3691 .references(() => pullRequests.id, { onDelete: "cascade" }),
3692 status: text("status").default("provisioning").notNull(),
3693 sandboxUrl: text("sandbox_url").notNull(),
3694 containerId: text("container_id"),
3695 playgroundYml: text("playground_yml"),
3696 provisionedAt: timestamp("provisioned_at", { withTimezone: true })
3697 .defaultNow()
3698 .notNull(),
3699 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3700 destroyedAt: timestamp("destroyed_at", { withTimezone: true }),
3701 errorMessage: text("error_message"),
3702 createdAt: timestamp("created_at", { withTimezone: true })
3703 .defaultNow()
3704 .notNull(),
3705 },
3706 (table) => [
3707 uniqueIndex("pr_sandboxes_pr_id").on(table.prId),
3708 index("pr_sandboxes_status_expires").on(table.status, table.expiresAt),
3709 ]
3710);
3711
3712export type PrSandbox = typeof prSandboxes.$inferSelect;
3713export type NewPrSandbox = typeof prSandboxes.$inferInsert;
d199847Claude3714
3715// ---------------------------------------------------------------------------
3716// 0068 — AI-tracked documentation sections. See src/lib/ai-doc-updater.ts.
3717//
3718// Stores the currently claimed source-file hash for each
3719// `<!-- gluecron:doc-track src=... -->` region in a tracked markdown file.
3720// The post-receive hook re-hashes the referenced source after every push
3721// and proposes a PR (tagged `ai:doc-update`) when the prose drifts from
3722// the truth.
3723// ---------------------------------------------------------------------------
3724export const docTracking = pgTable(
3725 "doc_tracking",
3726 {
3727 id: uuid("id").primaryKey().defaultRandom(),
3728 repositoryId: uuid("repository_id")
3729 .notNull()
3730 .references(() => repositories.id, { onDelete: "cascade" }),
3731 docPath: text("doc_path").notNull(),
3732 sectionMarker: text("section_marker").notNull(),
3733 srcPath: text("src_path").notNull(),
3734 claimedHash: text("claimed_hash").notNull(),
3735 lastCheckedAt: timestamp("last_checked_at", { withTimezone: true })
3736 .defaultNow()
3737 .notNull(),
3738 lastPrId: uuid("last_pr_id").references(() => pullRequests.id, {
3739 onDelete: "set null",
3740 }),
3741 createdAt: timestamp("created_at", { withTimezone: true })
3742 .defaultNow()
3743 .notNull(),
3744 },
3745 (table) => [
3746 uniqueIndex("doc_tracking_repo_doc_marker").on(
3747 table.repositoryId,
3748 table.docPath,
3749 table.sectionMarker
3750 ),
3751 index("doc_tracking_repo").on(table.repositoryId),
3752 ]
3753);
3754
3755export type DocTracking = typeof docTracking.$inferSelect;
3756export type NewDocTracking = typeof docTracking.$inferInsert;
c6db5eeClaude3757
3758// ---------------------------------------------------------------------------
3759// 0069 — Hosted Claude tool-use loops. See src/lib/hosted-claude-loop.ts and
3760// src/routes/claude-deploy.tsx.
3761//
3762// Users paste a Claude tool-use loop, get a hosted endpoint + budget meter.
3763// Each loop is paired to an agent_sessions row so it inherits multiplayer
3764// namespacing and the daily budget mutex.
3765// ---------------------------------------------------------------------------
3766export const hostedClaudeLoops = pgTable(
3767 "hosted_claude_loops",
3768 {
3769 id: uuid("id").primaryKey().defaultRandom(),
3770 ownerUserId: uuid("owner_user_id")
3771 .notNull()
3772 .references(() => users.id, { onDelete: "cascade" }),
3773 name: text("name").notNull(),
3774 sourceCode: text("source_code").notNull(),
3775 endpointPath: text("endpoint_path").notNull().unique(),
3776 agentSessionId: uuid("agent_session_id").references(
3777 () => agentSessions.id,
3778 { onDelete: "set null" }
3779 ),
3780 status: text("status").default("paused").notNull(),
3781 isPublic: boolean("is_public").default(false).notNull(),
3782 monthlyBudgetCents: integer("monthly_budget_cents").default(500).notNull(),
3783 lastRunAt: timestamp("last_run_at", { withTimezone: true }),
3784 totalInvocations: integer("total_invocations").default(0).notNull(),
3785 totalCentsSpent: integer("total_cents_spent").default(0).notNull(),
3786 createdAt: timestamp("created_at", { withTimezone: true })
3787 .defaultNow()
3788 .notNull(),
3789 updatedAt: timestamp("updated_at", { withTimezone: true })
3790 .defaultNow()
3791 .notNull(),
3792 },
3793 (table) => [
3794 index("hosted_claude_loops_owner").on(table.ownerUserId, table.updatedAt),
3795 index("hosted_claude_loops_status").on(table.status),
3796 ]
3797);
3798
3799export const hostedClaudeLoopRuns = pgTable(
3800 "hosted_claude_loop_runs",
3801 {
3802 id: uuid("id").primaryKey().defaultRandom(),
3803 loopId: uuid("loop_id")
3804 .notNull()
3805 .references(() => hostedClaudeLoops.id, { onDelete: "cascade" }),
3806 inputPayload: jsonb("input_payload").default({}).notNull(),
3807 outputPayload: jsonb("output_payload"),
3808 stdout: text("stdout"),
3809 stderr: text("stderr"),
3810 startedAt: timestamp("started_at", { withTimezone: true })
3811 .defaultNow()
3812 .notNull(),
3813 finishedAt: timestamp("finished_at", { withTimezone: true }),
3814 status: text("status").default("running").notNull(),
3815 centsEstimate: integer("cents_estimate").default(0).notNull(),
3816 claudeInputTokens: integer("claude_input_tokens").default(0).notNull(),
3817 claudeOutputTokens: integer("claude_output_tokens").default(0).notNull(),
3818 exitCode: integer("exit_code"),
3819 errorMessage: text("error_message"),
3820 createdAt: timestamp("created_at", { withTimezone: true })
3821 .defaultNow()
3822 .notNull(),
3823 },
3824 (table) => [
3825 index("hosted_claude_loop_runs_loop_time").on(
3826 table.loopId,
3827 table.startedAt
3828 ),
3829 index("hosted_claude_loop_runs_status").on(table.status, table.startedAt),
3830 ]
3831);
3832
3833export type HostedClaudeLoop = typeof hostedClaudeLoops.$inferSelect;
3834export type NewHostedClaudeLoop = typeof hostedClaudeLoops.$inferInsert;
3835export type HostedClaudeLoopRun = typeof hostedClaudeLoopRuns.$inferSelect;
3836export type NewHostedClaudeLoopRun = typeof hostedClaudeLoopRuns.$inferInsert;
5ca514aClaude3837
3838// ---------------------------------------------------------------------------
9b3a183Claude3839// 0070 — Agent Marketplace. See src/lib/agent-marketplace.ts.
5ca514aClaude3840// ---------------------------------------------------------------------------
3841export const agentMarketplaceListings = pgTable(
3842 "agent_marketplace_listings",
3843 {
3844 id: uuid("id").primaryKey().defaultRandom(),
3845 publisherUserId: uuid("publisher_user_id")
3846 .notNull()
3847 .references(() => users.id, { onDelete: "cascade" }),
3848 slug: text("slug").notNull().unique(),
3849 name: text("name").notNull(),
3850 tagline: text("tagline").default("").notNull(),
3851 description: text("description").default("").notNull(),
3852 category: text("category").default("custom").notNull(),
3853 pricingModel: text("pricing_model").default("free").notNull(),
3854 priceCents: integer("price_cents").default(0).notNull(),
3855 agentTemplate: jsonb("agent_template")
3856 .$type<{
3857 branchNamespace?: string;
3858 budgetCentsPerDay?: number;
3859 capabilities?: string[];
3860 [k: string]: unknown;
3861 }>()
3862 .default({})
3863 .notNull(),
3864 sourceUrl: text("source_url"),
3865 status: text("status").default("draft").notNull(),
3866 installCount: integer("install_count").default(0).notNull(),
3867 ratingAvg: numeric("rating_avg", { precision: 3, scale: 2 })
3868 .default("0")
3869 .notNull(),
3870 ratingCount: integer("rating_count").default(0).notNull(),
3871 createdAt: timestamp("created_at", { withTimezone: true })
3872 .defaultNow()
3873 .notNull(),
3874 updatedAt: timestamp("updated_at", { withTimezone: true })
3875 .defaultNow()
3876 .notNull(),
3877 },
3878 (table) => [
9b3a183Claude3879 index("agent_marketplace_listings_category").on(table.category, table.status),
3880 index("agent_marketplace_listings_rating").on(table.ratingAvg, table.ratingCount),
5ca514aClaude3881 index("agent_marketplace_listings_installs").on(table.installCount),
3882 index("agent_marketplace_listings_publisher").on(table.publisherUserId),
9b3a183Claude3883 index("agent_marketplace_listings_status_created").on(table.status, table.createdAt),
5ca514aClaude3884 ]
3885);
3886
3887export const agentMarketplaceInstalls = pgTable(
3888 "agent_marketplace_installs",
3889 {
3890 id: uuid("id").primaryKey().defaultRandom(),
3891 listingId: uuid("listing_id")
3892 .notNull()
3893 .references(() => agentMarketplaceListings.id, { onDelete: "cascade" }),
3894 repositoryId: uuid("repository_id")
3895 .notNull()
3896 .references(() => repositories.id, { onDelete: "cascade" }),
3897 installedByUserId: uuid("installed_by_user_id")
3898 .notNull()
3899 .references(() => users.id, { onDelete: "cascade" }),
3900 agentSessionId: uuid("agent_session_id").references(
3901 () => agentSessions.id,
3902 { onDelete: "set null" }
3903 ),
3904 status: text("status").default("active").notNull(),
3905 installedAt: timestamp("installed_at", { withTimezone: true })
3906 .defaultNow()
3907 .notNull(),
3908 lastInvokedAt: timestamp("last_invoked_at", { withTimezone: true }),
3909 createdAt: timestamp("created_at", { withTimezone: true })
3910 .defaultNow()
3911 .notNull(),
3912 },
3913 (table) => [
9b3a183Claude3914 uniqueIndex("agent_marketplace_installs_listing_repo").on(table.listingId, table.repositoryId),
5ca514aClaude3915 index("agent_marketplace_installs_repo").on(table.repositoryId, table.status),
3916 index("agent_marketplace_installs_installer").on(table.installedByUserId),
3917 ]
3918);
3919
3920export const agentMarketplaceReviews = pgTable(
3921 "agent_marketplace_reviews",
3922 {
3923 id: uuid("id").primaryKey().defaultRandom(),
3924 listingId: uuid("listing_id")
3925 .notNull()
3926 .references(() => agentMarketplaceListings.id, { onDelete: "cascade" }),
3927 reviewerUserId: uuid("reviewer_user_id")
3928 .notNull()
3929 .references(() => users.id, { onDelete: "cascade" }),
3930 rating: integer("rating").notNull(),
3931 body: text("body").default("").notNull(),
3932 createdAt: timestamp("created_at", { withTimezone: true })
3933 .defaultNow()
3934 .notNull(),
3935 },
3936 (table) => [
9b3a183Claude3937 index("agent_marketplace_reviews_listing_created").on(table.listingId, table.createdAt),
5ca514aClaude3938 index("agent_marketplace_reviews_reviewer").on(table.reviewerUserId),
3939 ]
3940);
3941
9b3a183Claude3942export type AgentMarketplaceListing = typeof agentMarketplaceListings.$inferSelect;
3943export type NewAgentMarketplaceListing = typeof agentMarketplaceListings.$inferInsert;
3944export type AgentMarketplaceInstall = typeof agentMarketplaceInstalls.$inferSelect;
3945export type NewAgentMarketplaceInstall = typeof agentMarketplaceInstalls.$inferInsert;
3946export type AgentMarketplaceReview = typeof agentMarketplaceReviews.$inferSelect;
3947export type NewAgentMarketplaceReview = typeof agentMarketplaceReviews.$inferInsert;
3948
3949// ---------------------------------------------------------------------------
3950// 0072 — per-(repo, user) cloud dev environments. See src/lib/dev-env.ts.
3951// ---------------------------------------------------------------------------
3952export const devEnvs = pgTable(
3953 "dev_envs",
3954 {
3955 id: uuid("id").primaryKey().defaultRandom(),
3956 repositoryId: uuid("repository_id")
3957 .notNull()
3958 .references(() => repositories.id, { onDelete: "cascade" }),
3959 ownerUserId: uuid("owner_user_id")
3960 .notNull()
3961 .references(() => users.id, { onDelete: "cascade" }),
3962 status: text("status").default("cold").notNull(),
3963 previewUrl: text("preview_url"),
3964 containerId: text("container_id"),
3965 machineSize: text("machine_size").default("small").notNull(),
3966 idleMinutes: integer("idle_minutes").default(30).notNull(),
3967 devYml: text("dev_yml"),
3968 errorMessage: text("error_message"),
3969 lastActiveAt: timestamp("last_active_at", { withTimezone: true })
3970 .defaultNow()
3971 .notNull(),
3972 expiresAt: timestamp("expires_at", { withTimezone: true }),
3973 createdAt: timestamp("created_at", { withTimezone: true })
3974 .defaultNow()
3975 .notNull(),
3976 },
3977 (table) => [
3978 uniqueIndex("dev_envs_repo_owner").on(table.repositoryId, table.ownerUserId),
3979 index("dev_envs_status_active").on(table.status, table.lastActiveAt),
3980 ]
3981);
3982
3983export type DevEnv = typeof devEnvs.$inferSelect;
3984export type NewDevEnv = typeof devEnvs.$inferInsert;
783dd46Claude3985
3986// ---------------------------------------------------------------------------
3987// Block ST — Server targets (drizzle/0073_server_targets.sql)
3988//
3989// Admin-managed remote boxes Gluecron can SSH into and run a deploy script
3990// on. Each target carries an encrypted private SSH key, a pinned host
3991// fingerprint, an optional repo+branch to watch, and a per-target list of
3992// env vars materialised on the box at deploy time. Customer-facing rollout
3993// is gated to a follow-up block (Block 2) that scopes by owner_user_id.
3994// ---------------------------------------------------------------------------
3995
3996export const serverTargets = pgTable(
3997 "server_targets",
3998 {
3999 id: uuid("id").primaryKey().defaultRandom(),
4000 name: text("name").notNull(),
4001 host: text("host").notNull(),
4002 port: integer("port").default(22).notNull(),
4003 sshUser: text("ssh_user").notNull(),
4004 encryptedPrivateKey: text("encrypted_private_key").notNull(),
4005 hostFingerprint: text("host_fingerprint"),
4006 deployPath: text("deploy_path").default("/var/www/app").notNull(),
4007 deployScript: text("deploy_script").default("bash deploy.sh").notNull(),
4008 watchedRepositoryId: uuid("watched_repository_id").references(
4009 () => repositories.id,
4010 { onDelete: "set null" }
4011 ),
4012 watchedBranch: text("watched_branch"),
4013 status: text("status").default("unverified").notNull(),
4014 lastSeenAt: timestamp("last_seen_at", { withTimezone: true }),
4015 createdBy: uuid("created_by").references(() => users.id, {
4016 onDelete: "set null",
4017 }),
4018 createdAt: timestamp("created_at", { withTimezone: true })
4019 .defaultNow()
4020 .notNull(),
4021 updatedAt: timestamp("updated_at", { withTimezone: true })
4022 .defaultNow()
4023 .notNull(),
4024 },
4025 (table) => [
4026 uniqueIndex("server_targets_name_uq").on(table.name),
4027 index("server_targets_watch_idx").on(
4028 table.watchedRepositoryId,
4029 table.watchedBranch
4030 ),
4031 ]
4032);
4033
4034export type ServerTarget = typeof serverTargets.$inferSelect;
4035export type NewServerTarget = typeof serverTargets.$inferInsert;
4036
4037export const serverTargetEnv = pgTable(
4038 "server_target_env",
4039 {
4040 id: uuid("id").primaryKey().defaultRandom(),
4041 targetId: uuid("target_id")
4042 .notNull()
4043 .references(() => serverTargets.id, { onDelete: "cascade" }),
4044 name: text("name").notNull(),
4045 encryptedValue: text("encrypted_value").notNull(),
4046 isSecret: boolean("is_secret").default(true).notNull(),
4047 updatedBy: uuid("updated_by").references(() => users.id, {
4048 onDelete: "set null",
4049 }),
4050 createdAt: timestamp("created_at", { withTimezone: true })
4051 .defaultNow()
4052 .notNull(),
4053 updatedAt: timestamp("updated_at", { withTimezone: true })
4054 .defaultNow()
4055 .notNull(),
4056 },
4057 (table) => [uniqueIndex("server_target_env_uq").on(table.targetId, table.name)]
4058);
4059
4060export type ServerTargetEnv = typeof serverTargetEnv.$inferSelect;
4061export type NewServerTargetEnv = typeof serverTargetEnv.$inferInsert;
4062
4063export const serverTargetDeployments = pgTable(
4064 "server_target_deployments",
4065 {
4066 id: uuid("id").primaryKey().defaultRandom(),
4067 targetId: uuid("target_id")
4068 .notNull()
4069 .references(() => serverTargets.id, { onDelete: "cascade" }),
4070 commitSha: text("commit_sha"),
4071 ref: text("ref"),
4072 status: text("status").default("pending").notNull(),
4073 exitCode: integer("exit_code"),
4074 stdout: text("stdout"),
4075 stderr: text("stderr"),
4076 triggeredBy: uuid("triggered_by").references(() => users.id, {
4077 onDelete: "set null",
4078 }),
4079 triggerSource: text("trigger_source").default("push").notNull(),
4080 startedAt: timestamp("started_at", { withTimezone: true })
4081 .defaultNow()
4082 .notNull(),
4083 finishedAt: timestamp("finished_at", { withTimezone: true }),
4084 },
4085 (table) => [
4086 index("server_target_deployments_target_idx").on(
4087 table.targetId,
4088 table.startedAt
4089 ),
4090 ]
4091);
4092
4093export type ServerTargetDeployment =
4094 typeof serverTargetDeployments.$inferSelect;
4095export type NewServerTargetDeployment =
4096 typeof serverTargetDeployments.$inferInsert;
4097
4098export const serverTargetAudit = pgTable(
4099 "server_target_audit",
4100 {
4101 id: uuid("id").primaryKey().defaultRandom(),
4102 targetId: uuid("target_id").references(() => serverTargets.id, {
4103 onDelete: "set null",
4104 }),
4105 actorId: uuid("actor_id").references(() => users.id, {
4106 onDelete: "set null",
4107 }),
4108 action: text("action").notNull(),
4109 detail: text("detail"),
4110 ip: text("ip"),
4111 createdAt: timestamp("created_at", { withTimezone: true })
4112 .defaultNow()
4113 .notNull(),
4114 },
4115 (table) => [
4116 index("server_target_audit_target_idx").on(table.targetId, table.createdAt),
4117 ]
4118);
4119
4120export type ServerTargetAudit = typeof serverTargetAudit.$inferSelect;
4121export type NewServerTargetAudit = typeof serverTargetAudit.$inferInsert;
90c7531Claude4122
4123// ---------------------------------------------------------------------------
4124// Block CW — Claude on the web (drizzle/0074_claude_web_sessions.sql)
4125//
4126// Per-repo interactive Claude Code sessions runnable from any browser.
4127// Each session owns a working dir on the web server (a fresh git clone of
4128// the repo's bare store) and persists turn-by-turn transcripts so an iPad
4129// user can resume the same conversation later from a laptop.
4130//
4131// v1 admin-only; v2 will scope by owner_user_id and isolate per-session
4132// containers. Schema is forward-compatible with both.
4133// ---------------------------------------------------------------------------
4134
4135export const claudeWebSessions = pgTable(
4136 "claude_web_sessions",
4137 {
4138 id: uuid("id").primaryKey().defaultRandom(),
4139 repositoryId: uuid("repository_id")
4140 .notNull()
4141 .references(() => repositories.id, { onDelete: "cascade" }),
4142 ownerUserId: uuid("owner_user_id")
4143 .notNull()
4144 .references(() => users.id, { onDelete: "cascade" }),
4145 title: text("title").default("New session").notNull(),
4146 branch: text("branch").default("main").notNull(),
4147 workdirPath: text("workdir_path").notNull(),
4148 claudeSessionId: text("claude_session_id"),
4149 status: text("status").default("cold").notNull(),
4150 lastActiveAt: timestamp("last_active_at", { withTimezone: true })
4151 .defaultNow()
4152 .notNull(),
4153 createdAt: timestamp("created_at", { withTimezone: true })
4154 .defaultNow()
4155 .notNull(),
4156 },
4157 (table) => [
4158 index("claude_web_sessions_repo_idx").on(
4159 table.repositoryId,
4160 table.lastActiveAt
4161 ),
4162 index("claude_web_sessions_owner_idx").on(
4163 table.ownerUserId,
4164 table.lastActiveAt
4165 ),
4166 ]
4167);
4168
4169export type ClaudeWebSession = typeof claudeWebSessions.$inferSelect;
4170export type NewClaudeWebSession = typeof claudeWebSessions.$inferInsert;
4171
4172export const claudeWebMessages = pgTable(
4173 "claude_web_messages",
4174 {
4175 id: uuid("id").primaryKey().defaultRandom(),
4176 sessionId: uuid("session_id")
4177 .notNull()
4178 .references(() => claudeWebSessions.id, { onDelete: "cascade" }),
4179 role: text("role").notNull(),
4180 body: text("body").notNull(),
4181 exitCode: integer("exit_code"),
4182 durationMs: integer("duration_ms"),
4183 createdAt: timestamp("created_at", { withTimezone: true })
4184 .defaultNow()
4185 .notNull(),
4186 },
4187 (table) => [
4188 index("claude_web_messages_session_idx").on(
4189 table.sessionId,
4190 table.createdAt
4191 ),
4192 ]
4193);
4194
4195export type ClaudeWebMessage = typeof claudeWebMessages.$inferSelect;
873f13cClaude4196
4197// ---------------------------------------------------------------------------
4198// 0077 — Status page: incident history + subscriber list.
4199//
4200// incidents: manually-filed or autopilot-detected outage records shown on
4201// the public /status page. severity: 'minor' | 'major' | 'critical'.
4202// status: 'investigating' | 'identified' | 'monitoring' | 'resolved'.
4203//
4204// status_subscribers: email addresses that have opted-in to receive alerts
4205// when a new incident is filed.
4206// ---------------------------------------------------------------------------
4207export const incidents = pgTable(
4208 "incidents",
4209 {
4210 id: uuid("id").primaryKey().defaultRandom(),
4211 title: text("title").notNull(),
4212 severity: text("severity").notNull().default("minor"),
4213 status: text("status").notNull().default("resolved"),
4214 startedAt: timestamp("started_at", { withTimezone: true })
4215 .defaultNow()
4216 .notNull(),
4217 resolvedAt: timestamp("resolved_at", { withTimezone: true }),
4218 body: text("body"),
4219 createdAt: timestamp("created_at", { withTimezone: true })
4220 .defaultNow()
4221 .notNull(),
4222 updatedAt: timestamp("updated_at", { withTimezone: true })
4223 .defaultNow()
4224 .notNull(),
4225 },
4226 (table) => [
4227 index("idx_incidents_started_at").on(table.startedAt),
4228 index("idx_incidents_status").on(table.status),
4229 ]
4230);
4231
4232export type Incident = typeof incidents.$inferSelect;
4233export type NewIncident = typeof incidents.$inferInsert;
4234
4235export const statusSubscribers = pgTable(
4236 "status_subscribers",
4237 {
4238 id: uuid("id").primaryKey().defaultRandom(),
4239 email: text("email").notNull().unique(),
4240 confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
4241 confirmToken: text("confirm_token").unique(),
4242 unsubscribeToken: text("unsubscribe_token").unique(),
4243 createdAt: timestamp("created_at", { withTimezone: true })
4244 .defaultNow()
4245 .notNull(),
4246 },
4247 (table) => [
4248 index("idx_status_subscribers_email").on(table.email),
4249 ]
4250);
4251
4252export type StatusSubscriber = typeof statusSubscribers.$inferSelect;
4253export type NewStatusSubscriber = typeof statusSubscribers.$inferInsert;
90c7531Claude4254export type NewClaudeWebMessage = typeof claudeWebMessages.$inferInsert;
9f29b65Claude4255
4256// ---------------------------------------------------------------------------
1df50d5Claude4257// Enterprise leads (contact form submissions from /enterprise)
9f29b65Claude4258// ---------------------------------------------------------------------------
4259
4260export const enterpriseLeads = pgTable(
4261 "enterprise_leads",
4262 {
4263 id: uuid("id").primaryKey().defaultRandom(),
4264 name: text("name").notNull(),
4265 company: text("company").notNull(),
4266 email: text("email").notNull(),
4267 teamSize: text("team_size").notNull(),
4268 message: text("message"),
4269 ip: text("ip"),
4270 createdAt: timestamp("created_at").defaultNow().notNull(),
4271 },
4272 (table) => [index("enterprise_leads_created").on(table.createdAt)]
4273);
4274
4275export type EnterpriseLead = typeof enterpriseLeads.$inferSelect;
4276export type NewEnterpriseLead = typeof enterpriseLeads.$inferInsert;
1df50d5Claude4277
4278// ---------------------------------------------------------------------------
4279// PR preview builder — one row per (pr_id, head_sha)
4280// ---------------------------------------------------------------------------
4281export const prPreviews = pgTable(
4282 "pr_previews",
4283 {
4284 id: serial("id").primaryKey(),
4285 repoId: uuid("repo_id")
4286 .notNull()
4287 .references(() => repositories.id, { onDelete: "cascade" }),
4288 prId: uuid("pr_id")
4289 .notNull()
4290 .references(() => pullRequests.id, { onDelete: "cascade" }),
4291 branchName: text("branch_name").notNull(),
4292 headSha: text("head_sha").notNull(),
4293 status: text("status").notNull().default("building"),
4294 buildLog: text("build_log"),
4295 previewUrl: text("preview_url"),
4296 buildCommand: text("build_command"),
4297 outputDir: text("output_dir").default("dist"),
4298 buildDurationMs: integer("build_duration_ms"),
4299 createdAt: timestamp("created_at").defaultNow(),
4300 updatedAt: timestamp("updated_at").defaultNow(),
4301 },
4302 (table) => [
4303 index("pr_previews_pr_id_idx").on(table.prId),
4304 index("pr_previews_repo_id_idx").on(table.repoId),
4305 ]
4306);
4307
4308export type PrPreview = typeof prPreviews.$inferSelect;
4309export type NewPrPreview = typeof prPreviews.$inferInsert;
3a845e4Claude4310
4311// ----------------------------------------------------------------------------
4312// Migration 0092 — Enterprise SSO (SAML 2.0 + OIDC per-org) + SCIM
4313// ----------------------------------------------------------------------------
4314
4315/**
4316 * Per-org SSO configuration. One row per org. Supports both SAML 2.0 and OIDC.
4317 * Distinct from the site-wide `sso_config` table (Block I10 / OIDC-only).
4318 */
4319export const orgSsoConfigs = pgTable("org_sso_configs", {
4320 id: uuid("id").primaryKey().defaultRandom(),
4321 orgId: uuid("org_id")
4322 .notNull()
4323 .unique()
4324 .references(() => organizations.id, { onDelete: "cascade" }),
4325 provider: text("provider").notNull().default("saml"), // 'saml' | 'oidc'
4326 // SAML fields
4327 idpEntityId: text("idp_entity_id"),
4328 idpSsoUrl: text("idp_sso_url"),
4329 idpCertificate: text("idp_certificate"), // PEM cert from IdP
4330 spEntityId: text("sp_entity_id"), // our SP entity ID
4331 // OIDC fields
4332 oidcClientId: text("oidc_client_id"),
4333 oidcClientSecret: text("oidc_client_secret"),
4334 oidcDiscoveryUrl: text("oidc_discovery_url"),
4335 // Common
4336 domainHint: text("domain_hint"), // e.g. "acme.com" — auto-routes users from this domain
4337 attributeMapping: jsonb("attribute_mapping")
4338 .$type<Record<string, string>>()
4339 .default({ email: "email", name: "name", username: "preferred_username" }),
4340 enabled: boolean("enabled").notNull().default(false),
4341 createdAt: timestamp("created_at").defaultNow(),
4342 updatedAt: timestamp("updated_at").defaultNow(),
4343});
4344
4345/**
4346 * SCIM provisioning tokens. Hashed SHA-256 bearer tokens issued per-org.
4347 * Identity providers (Okta, Azure AD) use these to provision/deprovision users.
4348 */
4349export const scimTokens = pgTable("scim_tokens", {
4350 id: uuid("id").primaryKey().defaultRandom(),
4351 orgId: uuid("org_id")
4352 .notNull()
4353 .references(() => organizations.id, { onDelete: "cascade" }),
4354 tokenHash: text("token_hash").notNull().unique(), // SHA-256 of the token
4355 createdBy: uuid("created_by")
4356 .notNull()
4357 .references(() => users.id),
4358 createdAt: timestamp("created_at").defaultNow(),
4359 lastUsedAt: timestamp("last_used_at"),
4360});
4361
4362/**
4363 * Tracks active IdP-initiated SSO sessions per org. Allows per-org
4364 * SLO (Single Logout) and session audit.
4365 */
4366export const orgSsoSessions = pgTable(
4367 "org_sso_sessions",
4368 {
4369 id: uuid("id").primaryKey().defaultRandom(),
4370 userId: uuid("user_id")
4371 .notNull()
4372 .references(() => users.id, { onDelete: "cascade" }),
4373 orgId: uuid("org_id")
4374 .notNull()
4375 .references(() => organizations.id, { onDelete: "cascade" }),
4376 idpSessionId: text("idp_session_id"),
4377 createdAt: timestamp("created_at").defaultNow(),
4378 expiresAt: timestamp("expires_at").notNull(),
4379 },
4380 (table) => [
4381 index("org_sso_sessions_user").on(table.userId),
4382 index("org_sso_sessions_expires").on(table.expiresAt),
4383 ]
4384);
4385
4386export type OrgSsoConfig = typeof orgSsoConfigs.$inferSelect;
4387export type NewOrgSsoConfig = typeof orgSsoConfigs.$inferInsert;
4388export type ScimToken = typeof scimTokens.$inferSelect;
4389export type OrgSsoSession = typeof orgSsoSessions.$inferSelect;
70e3293Claude4390
b271465Claude4391// Migration 0077 — Incident hook configs
4392// Maps a monitoring provider (PagerDuty / Datadog / Opsgenie / generic) to a
4393// specific repo. Inbound webhooks are validated against secret_hash (SHA-256
4394// of the user-chosen webhook secret passed in the ?secret= query param).
4395// ---------------------------------------------------------------------------
4396export const incidentHookConfigs = pgTable(
4397 "incident_hook_configs",
4398 {
4399 id: uuid("id").primaryKey().defaultRandom(),
4400 userId: uuid("user_id")
4401 .notNull()
4402 .references(() => users.id, { onDelete: "cascade" }),
70e3293Claude4403 repoId: uuid("repo_id")
4404 .notNull()
4405 .references(() => repositories.id, { onDelete: "cascade" }),
4406 /** 'pagerduty' | 'datadog' | 'opsgenie' | 'generic' */
4407 provider: text("provider").notNull(),
4408 /** SHA-256 hex of the user's plaintext webhook secret. */
4409 secretHash: text("secret_hash").notNull(),
4410 createdAt: timestamp("created_at").defaultNow(),
4411 },
4412 (table) => [
4413 uniqueIndex("incident_hook_configs_repo_provider").on(
4414 table.repoId,
4415 table.provider
4416 ),
4417 ]
4418);
4419
4420export type IncidentHookConfig = typeof incidentHookConfigs.$inferSelect;
4421export type NewIncidentHookConfig = typeof incidentHookConfigs.$inferInsert;
9c888c3Claude4422
4423
c9ed210Claude4424
4425/**
4426 * Cloud deploy configurations — per-repo settings for push-triggered deploys
4427 * to Fly.io, Railway, Render, Vercel, Netlify, or a generic webhook URL.
4428 * Migration 0077.
4429 */
4430export const cloudDeployConfigs = pgTable(
4431 "cloud_deploy_configs",
4432 {
4433 id: uuid("id").primaryKey().defaultRandom(),
4434 repoId: uuid("repo_id")
4435 .notNull()
4436 .references(() => repositories.id, { onDelete: "cascade" }),
4437 // 'fly' | 'railway' | 'render' | 'vercel' | 'netlify' | 'webhook'
4438 provider: text("provider").notNull(),
4439 // Fly app name, Railway service ID, Render service ID, Vercel project ID, webhook URL
4440 providerAppId: text("provider_app_id").notNull(),
4441 // AES-256-GCM encrypted via SERVER_TARGETS_KEY
4442 apiTokenEncrypted: text("api_token_encrypted").notNull(),
4443 triggerBranch: text("trigger_branch").notNull().default("main"),
4444 enabled: boolean("enabled").notNull().default(true),
4445 createdAt: timestamp("created_at").defaultNow(),
4446 },
4447 (table) => [index("cloud_deploy_configs_repo").on(table.repoId)]
4448);
4449
4450/**
4451 * Cloud deployment runs — one row per triggered deployment attempt.
4452 * Status transitions: pending -> running -> success | failed | cancelled.
4453 * Migration 0077.
4454 */
4455export const cloudDeployments = pgTable(
4456 "cloud_deployments",
4457 {
4458 id: uuid("id").primaryKey().defaultRandom(),
4459 configId: uuid("config_id")
4460 .notNull()
4461 .references(() => cloudDeployConfigs.id, { onDelete: "cascade" }),
4462 repoId: uuid("repo_id")
4463 .notNull()
4464 .references(() => repositories.id, { onDelete: "cascade" }),
4465 commitSha: text("commit_sha").notNull(),
4466 // pending | running | success | failed | cancelled
4467 status: text("status").notNull().default("pending"),
4468 providerDeployId: text("provider_deploy_id"),
4469 logUrl: text("log_url"),
4470 deployUrl: text("deploy_url"),
4471 errorMessage: text("error_message"),
4472 startedAt: timestamp("started_at").defaultNow(),
4473 completedAt: timestamp("completed_at"),
4474 durationMs: integer("duration_ms"),
4475 },
4476 (table) => [
4477 index("cloud_deployments_repo").on(table.repoId, table.startedAt),
4478 index("cloud_deployments_config").on(table.configId, table.startedAt),
4479 ]
4480);
4481
4482export type CloudDeployConfig = typeof cloudDeployConfigs.$inferSelect;
4483export type NewCloudDeployConfig = typeof cloudDeployConfigs.$inferInsert;
4484export type CloudDeployment = typeof cloudDeployments.$inferSelect;
4485export type NewCloudDeployment = typeof cloudDeployments.$inferInsert;
34e63b9Claude4486// ---------------------------------------------------------------------------
4487// Recurring pattern detection (migration 0088)
4488// ---------------------------------------------------------------------------
4489
4490export const recurringPatterns = pgTable(
4491 "recurring_patterns",
4492 {
4493 id: uuid("id").primaryKey().defaultRandom(),
4494 repositoryId: uuid("repository_id")
4495 .notNull()
4496 .references(() => repositories.id, { onDelete: "cascade" }),
4497 title: text("title").notNull(),
4498 occurrences: integer("occurrences").notNull().default(1),
4499 commitShas: jsonb("commit_shas").$type<string[]>().notNull().default([]),
4500 rootCauseHypothesis: text("root_cause_hypothesis"),
4501 suggestedFile: text("suggested_file"),
4502 severity: text("severity").notNull().default("medium"),
4503 detectedAt: timestamp("detected_at", { withTimezone: true }).notNull().defaultNow(),
4504 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
4505 },
4506 (table) => [
4507 index("idx_recurring_patterns_repo").on(table.repositoryId),
4508 index("idx_recurring_patterns_expires").on(table.expiresAt),
4509 ]
4510);
4511
4512export type RecurringPattern = typeof recurringPatterns.$inferSelect;
4513export type NewRecurringPattern = typeof recurringPatterns.$inferInsert;
1d6db4dClaude4514// ---------------------------------------------------------------------------
4515// Bus Factor Cache — migration 0088
4516// Stores per-repo knowledge concentration analysis (at-risk files where one
4517// author owns >75% of commits). Refreshed on demand; 7-day soft TTL.
4518// ---------------------------------------------------------------------------
4519export const busFactorCache = pgTable("bus_factor_cache", {
4520 id: uuid("id").primaryKey().defaultRandom(),
4521 repositoryId: uuid("repository_id")
4522 .notNull()
4523 .references(() => repositories.id, { onDelete: "cascade" })
4524 .unique(),
4525 analyzedAt: timestamp("analyzed_at", { withTimezone: true })
4526 .notNull()
4527 .defaultNow(),
4528 atRiskFiles: jsonb("at_risk_files").notNull().default([]),
4529 totalFilesAnalyzed: integer("total_files_analyzed").notNull().default(0),
4530});
cc34156Claude4531// ---------------------------------------------------------------------------
4532// Migration 0088 — PR visit tracking for context-restore feature
4533// ---------------------------------------------------------------------------
4534
4535/**
4536 * pr_visits — lightweight upsert table tracking each user's last visit to a
4537 * PR. Used by `src/lib/review-context.ts` to compute what changed since the
4538 * reviewer was last here and generate a "Welcome back" context banner.
4539 */
4540export const prVisits = pgTable(
4541 "pr_visits",
4542 {
4543 prId: uuid("pr_id")
4544 .notNull()
4545 .references(() => pullRequests.id, { onDelete: "cascade" }),
4546 userId: uuid("user_id")
4547 .notNull()
4548 .references(() => users.id, { onDelete: "cascade" }),
4549 visitedAt: timestamp("visited_at", { withTimezone: true }).notNull().defaultNow(),
4550 },
4551 (t) => [primaryKey({ columns: [t.prId, t.userId] })]
4552);
4553
4554export type PrVisit = typeof prVisits.$inferSelect;
641aa42Claude4555// ---------------------------------------------------------------------------
4556// Migration 0088 — Smart empty states: repo onboarding data
4557// Generated by generateRepoOnboarding() on first push to a repo.
4558// ---------------------------------------------------------------------------
4559export const repoOnboardingData = pgTable("repo_onboarding_data", {
4560 repositoryId: uuid("repository_id")
4561 .primaryKey()
4562 .references(() => repositories.id, { onDelete: "cascade" }),
4563 detectedLanguage: text("detected_language"),
4564 detectedFramework: text("detected_framework"),
4565 suggestedReadme: text("suggested_readme"),
4566 suggestedLabels: jsonb("suggested_labels")
4567 .notNull()
4568 .default([])
4569 .$type<Array<{ name: string; color: string; description: string }>>(),
4570 suggestedGatesConfig: text("suggested_gates_config"),
4571 firstCommitSuggestions: jsonb("first_commit_suggestions")
4572 .notNull()
4573 .default([])
4574 .$type<string[]>(),
4575 generatedAt: timestamp("generated_at", { withTimezone: true })
4576 .notNull()
4577 .defaultNow(),
4578});
4579
4580export type RepoOnboardingData = typeof repoOnboardingData.$inferSelect;
4581export type NewRepoOnboardingData = typeof repoOnboardingData.$inferInsert;
6ecda8fClaude4582
4583// ---------------------------------------------------------------------------
4584// Migration 0102 — Cross-repo impact cache (15-min TTL, keyed on PR id)
4585// ---------------------------------------------------------------------------
4586export const crossRepoImpactCache = pgTable(
4587 "cross_repo_impact_cache",
4588 {
4589 id: uuid("id").primaryKey().defaultRandom(),
4590 prId: uuid("pr_id")
4591 .notNull()
4592 .references(() => pullRequests.id, { onDelete: "cascade" }),
4593 report: jsonb("report").notNull(),
4594 analyzedAt: timestamp("analyzed_at").defaultNow(),
4595 cachedUntil: timestamp("cached_until").notNull(),
4596 },
4597 (table) => [
4598 index("idx_cross_repo_impact_pr").on(table.prId),
4599 ]
4600);
4601
4602export type CrossRepoImpactCache = typeof crossRepoImpactCache.$inferSelect;