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