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.tsBlame975 lines · 1 contributor
Normal viewRaw
fc1817aClaude1import {
2 pgTable,
3 text,
4 timestamp,
5 uuid,
6 boolean,
7 integer,
8 uniqueIndex,
79136bbClaude9 index,
10 serial,
fc1817aClaude11} from "drizzle-orm/pg-core";
12
13export const users = pgTable("users", {
14 id: uuid("id").primaryKey().defaultRandom(),
15 username: text("username").notNull().unique(),
16 email: text("email").notNull().unique(),
17 displayName: text("display_name"),
18 passwordHash: text("password_hash").notNull(),
19 avatarUrl: text("avatar_url"),
20 bio: text("bio"),
24cf2caClaude21 // Email notification preferences (Block A8). Default on; opt-out via /settings.
22 notifyEmailOnMention: boolean("notify_email_on_mention").default(true).notNull(),
23 notifyEmailOnAssign: boolean("notify_email_on_assign").default(true).notNull(),
24 notifyEmailOnGateFail: boolean("notify_email_on_gate_fail").default(true).notNull(),
fc1817aClaude25 createdAt: timestamp("created_at").defaultNow().notNull(),
26 updatedAt: timestamp("updated_at").defaultNow().notNull(),
27});
28
06d5ffeClaude29export const sessions = pgTable("sessions", {
30 id: uuid("id").primaryKey().defaultRandom(),
31 userId: uuid("user_id")
32 .notNull()
33 .references(() => users.id, { onDelete: "cascade" }),
34 token: text("token").notNull().unique(),
35 expiresAt: timestamp("expires_at").notNull(),
7298a17Claude36 // B4: true when the user has entered their password but not yet their TOTP
37 // code. softAuth/requireAuth treat such sessions as anonymous; only
38 // /login/2fa can consume them. Flips to false on successful 2FA.
39 requires2fa: boolean("requires_2fa").default(false).notNull(),
06d5ffeClaude40 createdAt: timestamp("created_at").defaultNow().notNull(),
41});
42
fc1817aClaude43export const repositories = pgTable(
44 "repositories",
45 {
46 id: uuid("id").primaryKey().defaultRandom(),
47 name: text("name").notNull(),
7437605Claude48 // ownerId = creator / user-owner. Always set (for attribution + user
49 // namespace uniqueness). For org-owned repos, also represents "created by".
fc1817aClaude50 ownerId: uuid("owner_id")
51 .notNull()
52 .references(() => users.id),
7437605Claude53 // Block B2: nullable org owner. When set, the repo lives in the org
54 // namespace and URL resolution routes `/:orgSlug/:repo` to it.
55 orgId: uuid("org_id"),
fc1817aClaude56 description: text("description"),
57 isPrivate: boolean("is_private").default(false).notNull(),
3ef4c9dClaude58 isArchived: boolean("is_archived").default(false).notNull(),
fc1817aClaude59 defaultBranch: text("default_branch").default("main").notNull(),
60 diskPath: text("disk_path").notNull(),
c81ab7aClaude61 forkedFromId: uuid("forked_from_id").references(() => repositories.id, {
62 onDelete: "set null",
63 }),
fc1817aClaude64 createdAt: timestamp("created_at").defaultNow().notNull(),
65 updatedAt: timestamp("updated_at").defaultNow().notNull(),
66 pushedAt: timestamp("pushed_at"),
67 starCount: integer("star_count").default(0).notNull(),
68 forkCount: integer("fork_count").default(0).notNull(),
79136bbClaude69 issueCount: integer("issue_count").default(0).notNull(),
fc1817aClaude70 },
7437605Claude71 (table) => [
72 // Partial: uniqueness only in the user namespace (org-owned rows exempt).
73 // Matches the partial index in migration 0004.
74 uniqueIndex("repos_owner_name").on(table.ownerId, table.name),
75 index("repos_org").on(table.orgId),
76 ]
fc1817aClaude77);
78
3ef4c9dClaude79/**
80 * Per-repository gate + auto-repair configuration.
81 * Every new repo is created with all gates ENABLED by default —
82 * the "full green ecosystem" default. Owners can manually opt-out per setting.
83 */
84export const repoSettings = pgTable("repo_settings", {
85 id: uuid("id").primaryKey().defaultRandom(),
86 repositoryId: uuid("repository_id")
87 .notNull()
88 .unique()
89 .references(() => repositories.id, { onDelete: "cascade" }),
90 // Gates
91 gateTestEnabled: boolean("gate_test_enabled").default(true).notNull(),
92 aiReviewEnabled: boolean("ai_review_enabled").default(true).notNull(),
93 secretScanEnabled: boolean("secret_scan_enabled").default(true).notNull(),
94 securityScanEnabled: boolean("security_scan_enabled").default(true).notNull(),
95 dependencyScanEnabled: boolean("dependency_scan_enabled").default(true).notNull(),
96 lintEnabled: boolean("lint_enabled").default(true).notNull(),
97 typeCheckEnabled: boolean("type_check_enabled").default(true).notNull(),
98 testEnabled: boolean("test_enabled").default(true).notNull(),
99 // Auto-repair
100 autoFixEnabled: boolean("auto_fix_enabled").default(true).notNull(),
101 autoMergeResolveEnabled: boolean("auto_merge_resolve_enabled").default(true).notNull(),
102 autoFormatEnabled: boolean("auto_format_enabled").default(true).notNull(),
103 // AI features
104 aiCommitMessagesEnabled: boolean("ai_commit_messages_enabled").default(true).notNull(),
105 aiPrSummaryEnabled: boolean("ai_pr_summary_enabled").default(true).notNull(),
106 aiChangelogEnabled: boolean("ai_changelog_enabled").default(true).notNull(),
107 // Deploy
108 autoDeployEnabled: boolean("auto_deploy_enabled").default(true).notNull(),
109 deployRequireAllGreen: boolean("deploy_require_all_green").default(true).notNull(),
110 createdAt: timestamp("created_at").defaultNow().notNull(),
111 updatedAt: timestamp("updated_at").defaultNow().notNull(),
112});
113
114/**
115 * Branch protection rules — enforced on push and merge.
116 * Every repo's default branch gets a protection rule on creation.
117 */
118export const branchProtection = pgTable(
119 "branch_protection",
120 {
121 id: uuid("id").primaryKey().defaultRandom(),
122 repositoryId: uuid("repository_id")
123 .notNull()
124 .references(() => repositories.id, { onDelete: "cascade" }),
125 pattern: text("pattern").notNull(), // branch name or glob (e.g. "main", "release/*")
126 requirePullRequest: boolean("require_pull_request").default(true).notNull(),
127 requireGreenGates: boolean("require_green_gates").default(true).notNull(),
128 requireAiApproval: boolean("require_ai_approval").default(true).notNull(),
129 requireHumanReview: boolean("require_human_review").default(false).notNull(),
130 requiredApprovals: integer("required_approvals").default(0).notNull(),
131 allowForcePush: boolean("allow_force_push").default(false).notNull(),
132 allowDeletion: boolean("allow_deletion").default(false).notNull(),
133 dismissStaleReviews: boolean("dismiss_stale_reviews").default(true).notNull(),
134 createdAt: timestamp("created_at").defaultNow().notNull(),
135 updatedAt: timestamp("updated_at").defaultNow().notNull(),
136 },
137 (table) => [
138 uniqueIndex("branch_protection_repo_pattern").on(
139 table.repositoryId,
140 table.pattern
141 ),
142 ]
143);
144
145/**
146 * Gate run history. Every push + every PR creates gate_runs entries —
147 * one per configured gate. Serves as the source of truth for "is this green?".
148 */
149export const gateRuns = pgTable(
150 "gate_runs",
151 {
152 id: uuid("id").primaryKey().defaultRandom(),
153 repositoryId: uuid("repository_id")
154 .notNull()
155 .references(() => repositories.id, { onDelete: "cascade" }),
156 pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
157 onDelete: "cascade",
158 }),
159 commitSha: text("commit_sha").notNull(),
160 ref: text("ref").notNull(),
161 gateName: text("gate_name").notNull(), // e.g. "GateTest", "AI Review", "Secret Scan", "Type Check"
162 status: text("status").notNull(), // pending, running, passed, failed, skipped, repaired
163 summary: text("summary"),
164 details: text("details"), // JSON: per-check output, affected files, etc
165 repairAttempted: boolean("repair_attempted").default(false).notNull(),
166 repairSucceeded: boolean("repair_succeeded").default(false).notNull(),
167 repairCommitSha: text("repair_commit_sha"),
168 durationMs: integer("duration_ms"),
169 createdAt: timestamp("created_at").defaultNow().notNull(),
170 completedAt: timestamp("completed_at"),
171 },
172 (table) => [
173 index("gate_runs_repo_sha").on(table.repositoryId, table.commitSha),
174 index("gate_runs_pr").on(table.pullRequestId),
175 index("gate_runs_created").on(table.createdAt),
176 ]
177);
178
179/**
180 * In-app notifications. Powered by the activity feed + explicit mentions.
181 */
182export const notifications = pgTable(
183 "notifications",
184 {
185 id: uuid("id").primaryKey().defaultRandom(),
186 userId: uuid("user_id")
187 .notNull()
188 .references(() => users.id, { onDelete: "cascade" }),
189 repositoryId: uuid("repository_id").references(() => repositories.id, {
190 onDelete: "cascade",
191 }),
192 kind: text("kind").notNull(), // mention, review_requested, pr_merged, pr_closed, gate_failed, gate_repaired, ai_review, assigned, security_alert, deploy_success, deploy_failed
193 title: text("title").notNull(),
194 body: text("body"),
195 url: text("url"), // link to the relevant page
196 readAt: timestamp("read_at"),
197 createdAt: timestamp("created_at").defaultNow().notNull(),
198 },
199 (table) => [
200 index("notifications_user_unread").on(table.userId, table.readAt),
201 index("notifications_user_created").on(table.userId, table.createdAt),
202 ]
203);
204
205/**
206 * Releases — named snapshots of a repo at a tag/commit.
207 * AI-generated changelogs bundled in notes field.
208 */
209export const releases = pgTable(
210 "releases",
211 {
212 id: uuid("id").primaryKey().defaultRandom(),
213 repositoryId: uuid("repository_id")
214 .notNull()
215 .references(() => repositories.id, { onDelete: "cascade" }),
216 authorId: uuid("author_id")
217 .notNull()
218 .references(() => users.id),
219 tag: text("tag").notNull(),
220 name: text("name").notNull(),
221 body: text("body"), // AI-generated release notes + changelog
222 targetCommit: text("target_commit").notNull(),
223 isDraft: boolean("is_draft").default(false).notNull(),
224 isPrerelease: boolean("is_prerelease").default(false).notNull(),
225 createdAt: timestamp("created_at").defaultNow().notNull(),
226 publishedAt: timestamp("published_at"),
227 },
228 (table) => [
229 uniqueIndex("releases_repo_tag").on(table.repositoryId, table.tag),
230 ]
231);
232
233/**
234 * Milestones — group issues + PRs toward a shared goal.
235 */
236export const milestones = pgTable(
237 "milestones",
238 {
239 id: uuid("id").primaryKey().defaultRandom(),
240 repositoryId: uuid("repository_id")
241 .notNull()
242 .references(() => repositories.id, { onDelete: "cascade" }),
243 title: text("title").notNull(),
244 description: text("description"),
245 state: text("state").notNull().default("open"), // open, closed
246 dueDate: timestamp("due_date"),
247 createdAt: timestamp("created_at").defaultNow().notNull(),
248 closedAt: timestamp("closed_at"),
249 },
250 (table) => [index("milestones_repo_state").on(table.repositoryId, table.state)]
251);
252
253/**
254 * Reactions on issues, PRs, and comments. Universal target pointer.
255 */
256export const reactions = pgTable(
257 "reactions",
258 {
259 id: uuid("id").primaryKey().defaultRandom(),
260 userId: uuid("user_id")
261 .notNull()
262 .references(() => users.id, { onDelete: "cascade" }),
263 targetType: text("target_type").notNull(), // issue, pr, issue_comment, pr_comment
264 targetId: uuid("target_id").notNull(),
265 emoji: text("emoji").notNull(), // thumbs_up, thumbs_down, rocket, heart, eyes, laugh, hooray, confused
266 createdAt: timestamp("created_at").defaultNow().notNull(),
267 },
268 (table) => [
269 uniqueIndex("reactions_unique").on(
270 table.userId,
271 table.targetType,
272 table.targetId,
273 table.emoji
274 ),
275 index("reactions_target").on(table.targetType, table.targetId),
276 ]
277);
278
279/**
280 * PR reviews (formal approve/request-changes).
281 * Separate from inline comments in pr_comments.
282 */
283export const prReviews = pgTable(
284 "pr_reviews",
285 {
286 id: uuid("id").primaryKey().defaultRandom(),
287 pullRequestId: uuid("pull_request_id")
288 .notNull()
289 .references(() => pullRequests.id, { onDelete: "cascade" }),
290 reviewerId: uuid("reviewer_id")
291 .notNull()
292 .references(() => users.id),
293 state: text("state").notNull(), // approved, changes_requested, commented
294 body: text("body"),
295 isAi: boolean("is_ai").default(false).notNull(),
296 createdAt: timestamp("created_at").defaultNow().notNull(),
297 },
298 (table) => [index("pr_reviews_pr").on(table.pullRequestId)]
299);
300
301/**
302 * Code owners — who owns which paths (auto-request review on PR).
303 * Parsed from a CODEOWNERS file at the root of the default branch.
304 */
305export const codeOwners = pgTable(
306 "code_owners",
307 {
308 id: uuid("id").primaryKey().defaultRandom(),
309 repositoryId: uuid("repository_id")
310 .notNull()
311 .references(() => repositories.id, { onDelete: "cascade" }),
312 pathPattern: text("path_pattern").notNull(),
313 ownerUsernames: text("owner_usernames").notNull(), // comma-separated
314 createdAt: timestamp("created_at").defaultNow().notNull(),
315 },
316 (table) => [index("code_owners_repo").on(table.repositoryId)]
317);
318
319/**
320 * Per-repo AI chat sessions — conversational repo assistant.
321 */
322export const aiChats = pgTable(
323 "ai_chats",
324 {
325 id: uuid("id").primaryKey().defaultRandom(),
326 userId: uuid("user_id")
327 .notNull()
328 .references(() => users.id, { onDelete: "cascade" }),
329 repositoryId: uuid("repository_id").references(() => repositories.id, {
330 onDelete: "cascade",
331 }),
332 title: text("title"),
333 messages: text("messages").notNull().default("[]"), // JSON array of {role, content}
334 createdAt: timestamp("created_at").defaultNow().notNull(),
335 updatedAt: timestamp("updated_at").defaultNow().notNull(),
336 },
337 (table) => [
338 index("ai_chats_user").on(table.userId),
339 index("ai_chats_repo").on(table.repositoryId),
340 ]
341);
342
343/**
344 * Audit log — every sensitive action. Who did what, when, from where.
345 */
346export const auditLog = pgTable(
347 "audit_log",
348 {
349 id: uuid("id").primaryKey().defaultRandom(),
350 userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
351 repositoryId: uuid("repository_id").references(() => repositories.id, {
352 onDelete: "set null",
353 }),
354 action: text("action").notNull(), // repo.create, repo.delete, repo.transfer, token.create, token.revoke, merge, force_push, branch_protection.update, deploy, ...
355 targetType: text("target_type"),
356 targetId: text("target_id"),
357 ip: text("ip"),
358 userAgent: text("user_agent"),
359 metadata: text("metadata"), // JSON for extra context
360 createdAt: timestamp("created_at").defaultNow().notNull(),
361 },
362 (table) => [
363 index("audit_log_user").on(table.userId),
364 index("audit_log_repo").on(table.repositoryId),
365 index("audit_log_created").on(table.createdAt),
366 ]
367);
368
369/**
370 * Deployments — tracks every deploy to downstream systems (Crontech, etc).
371 * Each deploy is gated on ALL green gates passing.
372 */
373export const deployments = pgTable(
374 "deployments",
375 {
376 id: uuid("id").primaryKey().defaultRandom(),
377 repositoryId: uuid("repository_id")
378 .notNull()
379 .references(() => repositories.id, { onDelete: "cascade" }),
380 environment: text("environment").notNull().default("production"),
381 commitSha: text("commit_sha").notNull(),
382 ref: text("ref").notNull(),
383 status: text("status").notNull(), // pending, running, success, failed, blocked
384 blockedReason: text("blocked_reason"),
385 target: text("target"), // e.g. "crontech", "fly.io"
386 triggeredBy: uuid("triggered_by").references(() => users.id),
387 createdAt: timestamp("created_at").defaultNow().notNull(),
388 completedAt: timestamp("completed_at"),
389 },
390 (table) => [
391 index("deployments_repo").on(table.repositoryId),
392 index("deployments_created").on(table.createdAt),
393 ]
394);
395
396/**
397 * Rate-limit buckets — in-memory or persisted counter per IP / token / route.
398 */
399export const rateLimitBuckets = pgTable(
400 "rate_limit_buckets",
401 {
402 id: uuid("id").primaryKey().defaultRandom(),
403 bucketKey: text("bucket_key").notNull().unique(), // "ip:1.2.3.4:api" or "token:abc:api"
404 count: integer("count").default(0).notNull(),
405 windowStart: timestamp("window_start").defaultNow().notNull(),
406 expiresAt: timestamp("expires_at").notNull(),
407 },
408 (table) => [index("rate_limit_expires").on(table.expiresAt)]
409);
410
06d5ffeClaude411export const stars = pgTable(
412 "stars",
413 {
414 id: uuid("id").primaryKey().defaultRandom(),
415 userId: uuid("user_id")
416 .notNull()
417 .references(() => users.id, { onDelete: "cascade" }),
418 repositoryId: uuid("repository_id")
419 .notNull()
420 .references(() => repositories.id, { onDelete: "cascade" }),
421 createdAt: timestamp("created_at").defaultNow().notNull(),
422 },
79136bbClaude423 (table) => [
424 uniqueIndex("stars_user_repo").on(table.userId, table.repositoryId),
425 ]
426);
427
428export const issues = pgTable(
429 "issues",
430 {
431 id: uuid("id").primaryKey().defaultRandom(),
432 number: serial("number"),
433 repositoryId: uuid("repository_id")
434 .notNull()
435 .references(() => repositories.id, { onDelete: "cascade" }),
436 authorId: uuid("author_id")
437 .notNull()
438 .references(() => users.id),
439 title: text("title").notNull(),
440 body: text("body"),
441 state: text("state").notNull().default("open"), // open, closed
442 createdAt: timestamp("created_at").defaultNow().notNull(),
443 updatedAt: timestamp("updated_at").defaultNow().notNull(),
444 closedAt: timestamp("closed_at"),
445 },
446 (table) => [
447 index("issues_repo_state").on(table.repositoryId, table.state),
448 index("issues_repo_number").on(table.repositoryId, table.number),
449 ]
450);
451
452export const issueComments = pgTable(
453 "issue_comments",
454 {
455 id: uuid("id").primaryKey().defaultRandom(),
456 issueId: uuid("issue_id")
457 .notNull()
458 .references(() => issues.id, { onDelete: "cascade" }),
459 authorId: uuid("author_id")
460 .notNull()
461 .references(() => users.id),
462 body: text("body").notNull(),
463 createdAt: timestamp("created_at").defaultNow().notNull(),
464 updatedAt: timestamp("updated_at").defaultNow().notNull(),
465 },
466 (table) => [index("comments_issue").on(table.issueId)]
467);
468
469export const labels = pgTable(
470 "labels",
471 {
472 id: uuid("id").primaryKey().defaultRandom(),
473 repositoryId: uuid("repository_id")
474 .notNull()
475 .references(() => repositories.id, { onDelete: "cascade" }),
476 name: text("name").notNull(),
477 color: text("color").notNull().default("#8b949e"),
478 description: text("description"),
479 },
480 (table) => [
481 uniqueIndex("labels_repo_name").on(table.repositoryId, table.name),
482 ]
483);
484
485export const issueLabels = pgTable(
486 "issue_labels",
487 {
488 id: uuid("id").primaryKey().defaultRandom(),
489 issueId: uuid("issue_id")
490 .notNull()
491 .references(() => issues.id, { onDelete: "cascade" }),
492 labelId: uuid("label_id")
493 .notNull()
494 .references(() => labels.id, { onDelete: "cascade" }),
495 },
496 (table) => [
497 uniqueIndex("issue_labels_unique").on(table.issueId, table.labelId),
498 ]
06d5ffeClaude499);
500
0074234Claude501export const pullRequests = pgTable(
502 "pull_requests",
503 {
504 id: uuid("id").primaryKey().defaultRandom(),
505 number: serial("number"),
506 repositoryId: uuid("repository_id")
507 .notNull()
508 .references(() => repositories.id, { onDelete: "cascade" }),
509 authorId: uuid("author_id")
510 .notNull()
511 .references(() => users.id),
512 title: text("title").notNull(),
513 body: text("body"),
514 state: text("state").notNull().default("open"), // open, closed, merged
515 baseBranch: text("base_branch").notNull(),
516 headBranch: text("head_branch").notNull(),
3ef4c9dClaude517 isDraft: boolean("is_draft").default(false).notNull(),
518 mergeStrategy: text("merge_strategy").default("merge").notNull(), // merge, squash, rebase
519 milestoneId: uuid("milestone_id"),
0074234Claude520 mergedAt: timestamp("merged_at"),
521 mergedBy: uuid("merged_by").references(() => users.id),
522 createdAt: timestamp("created_at").defaultNow().notNull(),
523 updatedAt: timestamp("updated_at").defaultNow().notNull(),
524 closedAt: timestamp("closed_at"),
525 },
526 (table) => [
527 index("prs_repo_state").on(table.repositoryId, table.state),
528 index("prs_repo_number").on(table.repositoryId, table.number),
529 ]
530);
531
532export const prComments = pgTable(
533 "pr_comments",
534 {
535 id: uuid("id").primaryKey().defaultRandom(),
536 pullRequestId: uuid("pull_request_id")
537 .notNull()
538 .references(() => pullRequests.id, { onDelete: "cascade" }),
539 authorId: uuid("author_id")
540 .notNull()
541 .references(() => users.id),
542 body: text("body").notNull(),
543 isAiReview: boolean("is_ai_review").default(false).notNull(),
544 filePath: text("file_path"),
545 lineNumber: integer("line_number"),
546 createdAt: timestamp("created_at").defaultNow().notNull(),
547 updatedAt: timestamp("updated_at").defaultNow().notNull(),
548 },
549 (table) => [index("pr_comments_pr").on(table.pullRequestId)]
550);
551
552export const activityFeed = pgTable(
553 "activity_feed",
554 {
555 id: uuid("id").primaryKey().defaultRandom(),
556 repositoryId: uuid("repository_id")
557 .notNull()
558 .references(() => repositories.id, { onDelete: "cascade" }),
559 userId: uuid("user_id").references(() => users.id),
560 action: text("action").notNull(), // push, issue_open, issue_close, pr_open, pr_merge, star, comment
561 targetType: text("target_type"), // issue, pr, commit
562 targetId: text("target_id"),
563 metadata: text("metadata"), // JSON string for extra data
564 createdAt: timestamp("created_at").defaultNow().notNull(),
565 },
566 (table) => [
567 index("activity_repo").on(table.repositoryId),
568 index("activity_user").on(table.userId),
569 ]
570);
571
c81ab7aClaude572export const webhooks = pgTable(
573 "webhooks",
574 {
575 id: uuid("id").primaryKey().defaultRandom(),
576 repositoryId: uuid("repository_id")
577 .notNull()
578 .references(() => repositories.id, { onDelete: "cascade" }),
579 url: text("url").notNull(),
580 secret: text("secret"),
581 events: text("events").notNull().default("push"), // comma-separated: push,issue,pr
582 isActive: boolean("is_active").default(true).notNull(),
583 lastDeliveredAt: timestamp("last_delivered_at"),
584 lastStatus: integer("last_status"),
585 createdAt: timestamp("created_at").defaultNow().notNull(),
586 },
587 (table) => [index("webhooks_repo").on(table.repositoryId)]
588);
589
590export const apiTokens = pgTable("api_tokens", {
591 id: uuid("id").primaryKey().defaultRandom(),
592 userId: uuid("user_id")
593 .notNull()
594 .references(() => users.id, { onDelete: "cascade" }),
595 name: text("name").notNull(),
596 tokenHash: text("token_hash").notNull(),
597 tokenPrefix: text("token_prefix").notNull(), // first 8 chars for display
598 scopes: text("scopes").notNull().default("repo"), // comma-separated
599 lastUsedAt: timestamp("last_used_at"),
600 expiresAt: timestamp("expires_at"),
601 createdAt: timestamp("created_at").defaultNow().notNull(),
602});
603
604export const repoTopics = pgTable(
605 "repo_topics",
606 {
607 id: uuid("id").primaryKey().defaultRandom(),
608 repositoryId: uuid("repository_id")
609 .notNull()
610 .references(() => repositories.id, { onDelete: "cascade" }),
611 topic: text("topic").notNull(),
612 },
613 (table) => [
614 uniqueIndex("repo_topics_unique").on(table.repositoryId, table.topic),
615 index("topics_name").on(table.topic),
616 ]
617);
618
fc1817aClaude619export const sshKeys = pgTable("ssh_keys", {
620 id: uuid("id").primaryKey().defaultRandom(),
621 userId: uuid("user_id")
622 .notNull()
06d5ffeClaude623 .references(() => users.id, { onDelete: "cascade" }),
fc1817aClaude624 title: text("title").notNull(),
625 fingerprint: text("fingerprint").notNull(),
626 publicKey: text("public_key").notNull(),
627 lastUsedAt: timestamp("last_used_at"),
628 createdAt: timestamp("created_at").defaultNow().notNull(),
629});
630
631export type User = typeof users.$inferSelect;
632export type NewUser = typeof users.$inferInsert;
633export type Repository = typeof repositories.$inferSelect;
634export type NewRepository = typeof repositories.$inferInsert;
06d5ffeClaude635export type Session = typeof sessions.$inferSelect;
636export type Star = typeof stars.$inferSelect;
637export type SshKey = typeof sshKeys.$inferSelect;
79136bbClaude638export type Issue = typeof issues.$inferSelect;
639export type IssueComment = typeof issueComments.$inferSelect;
640export type Label = typeof labels.$inferSelect;
0074234Claude641export type PullRequest = typeof pullRequests.$inferSelect;
642export type PrComment = typeof prComments.$inferSelect;
643export type ActivityEntry = typeof activityFeed.$inferSelect;
c81ab7aClaude644export type Webhook = typeof webhooks.$inferSelect;
645export type ApiToken = typeof apiTokens.$inferSelect;
646export type RepoTopic = typeof repoTopics.$inferSelect;
3ef4c9dClaude647export type RepoSettings = typeof repoSettings.$inferSelect;
648export type BranchProtection = typeof branchProtection.$inferSelect;
649export type GateRun = typeof gateRuns.$inferSelect;
650export type Notification = typeof notifications.$inferSelect;
651export type Release = typeof releases.$inferSelect;
652export type Milestone = typeof milestones.$inferSelect;
653export type Reaction = typeof reactions.$inferSelect;
654export type PrReview = typeof prReviews.$inferSelect;
655export type CodeOwner = typeof codeOwners.$inferSelect;
656export type AiChat = typeof aiChats.$inferSelect;
657export type AuditLogEntry = typeof auditLog.$inferSelect;
658export type Deployment = typeof deployments.$inferSelect;
24cf2caClaude659
660/**
661 * Saved replies — per-user canned responses, insertable into any
662 * issue / PR comment textarea. Shortcut name must be unique per user.
663 */
664export const savedReplies = pgTable(
665 "saved_replies",
666 {
667 id: uuid("id").primaryKey().defaultRandom(),
668 userId: uuid("user_id")
669 .notNull()
670 .references(() => users.id, { onDelete: "cascade" }),
671 shortcut: text("shortcut").notNull(),
672 body: text("body").notNull(),
673 createdAt: timestamp("created_at").defaultNow().notNull(),
674 updatedAt: timestamp("updated_at").defaultNow().notNull(),
675 },
676 (table) => [
677 uniqueIndex("saved_replies_user_shortcut").on(table.userId, table.shortcut),
678 ]
679);
680
681export type SavedReply = typeof savedReplies.$inferSelect;
5cc5d95Claude682
683/**
684 * Organizations (Block B1) — multi-user namespaces. Distinct from `users`.
685 * An org has members (with org-level roles) and may contain teams.
686 * Repos can be owned by an org via `repositories.orgId` (added in Block B2).
687 *
688 * Slug is globally unique against itself; collision with a username is
689 * checked at create time in the route handler (no DB-level cross-table
690 * uniqueness in Postgres).
691 */
692export const organizations = pgTable("organizations", {
693 id: uuid("id").primaryKey().defaultRandom(),
694 slug: text("slug").notNull().unique(),
695 name: text("name").notNull(),
696 description: text("description"),
697 avatarUrl: text("avatar_url"),
698 billingEmail: text("billing_email"),
699 createdById: uuid("created_by_id")
700 .notNull()
701 .references(() => users.id, { onDelete: "restrict" }),
702 createdAt: timestamp("created_at").defaultNow().notNull(),
703 updatedAt: timestamp("updated_at").defaultNow().notNull(),
704});
705
706/**
707 * Org membership. Roles: owner (full control, billing), admin (manage
708 * members + teams + repos), member (default; can be added to teams).
709 */
710export const orgMembers = pgTable(
711 "org_members",
712 {
713 id: uuid("id").primaryKey().defaultRandom(),
714 orgId: uuid("org_id")
715 .notNull()
716 .references(() => organizations.id, { onDelete: "cascade" }),
717 userId: uuid("user_id")
718 .notNull()
719 .references(() => users.id, { onDelete: "cascade" }),
720 role: text("role").notNull().default("member"), // owner | admin | member
721 createdAt: timestamp("created_at").defaultNow().notNull(),
722 },
723 (table) => [
724 uniqueIndex("org_members_unique").on(table.orgId, table.userId),
725 index("org_members_user").on(table.userId),
726 ]
727);
728
729/**
730 * Teams within an org. Slug is unique within an org.
731 * `parentTeamId` allows nesting (GitHub-style child teams). Optional.
732 */
733export const teams = pgTable(
734 "teams",
735 {
736 id: uuid("id").primaryKey().defaultRandom(),
737 orgId: uuid("org_id")
738 .notNull()
739 .references(() => organizations.id, { onDelete: "cascade" }),
740 slug: text("slug").notNull(),
741 name: text("name").notNull(),
742 description: text("description"),
743 parentTeamId: uuid("parent_team_id"),
744 createdAt: timestamp("created_at").defaultNow().notNull(),
745 updatedAt: timestamp("updated_at").defaultNow().notNull(),
746 },
747 (table) => [uniqueIndex("teams_org_slug").on(table.orgId, table.slug)]
748);
749
750/**
751 * Team membership. Roles: maintainer (can edit team), member (default).
752 * A user can belong to many teams; team membership requires org membership
753 * but that invariant is enforced at the route layer, not the DB layer.
754 */
755export const teamMembers = pgTable(
756 "team_members",
757 {
758 id: uuid("id").primaryKey().defaultRandom(),
759 teamId: uuid("team_id")
760 .notNull()
761 .references(() => teams.id, { onDelete: "cascade" }),
762 userId: uuid("user_id")
763 .notNull()
764 .references(() => users.id, { onDelete: "cascade" }),
765 role: text("role").notNull().default("member"), // maintainer | member
766 createdAt: timestamp("created_at").defaultNow().notNull(),
767 },
768 (table) => [
769 uniqueIndex("team_members_unique").on(table.teamId, table.userId),
770 index("team_members_user").on(table.userId),
771 ]
772);
773
774export type Organization = typeof organizations.$inferSelect;
775export type OrgMember = typeof orgMembers.$inferSelect;
776export type Team = typeof teams.$inferSelect;
777export type TeamMember = typeof teamMembers.$inferSelect;
778export type OrgRole = "owner" | "admin" | "member";
779export type TeamRole = "maintainer" | "member";
7298a17Claude780
781/**
782 * 2FA / TOTP (Block B4).
783 *
784 * Secret is stored in plain Base32 for now — the DB has row-level-secure
785 * access and the app boundary is the only code that reads it. A follow-up
786 * (B4.1) will wrap it with AES-GCM at rest once we standardise the KEK.
787 *
788 * `enabledAt` is set only after the user has successfully entered their
789 * first code (confirming the authenticator was set up correctly). Rows with
790 * `enabledAt = NULL` represent pending enrolment.
791 */
792export const userTotp = pgTable("user_totp", {
793 userId: uuid("user_id")
794 .primaryKey()
795 .references(() => users.id, { onDelete: "cascade" }),
796 secret: text("secret").notNull(),
797 enabledAt: timestamp("enabled_at"),
798 lastUsedAt: timestamp("last_used_at"),
799 createdAt: timestamp("created_at").defaultNow().notNull(),
800});
801
802/**
803 * Recovery codes — single-use fallback when the authenticator is lost.
804 * Stored as SHA-256 hashes; used rows are marked with `usedAt` rather than
805 * deleted so the audit log keeps the full history.
806 */
807export const userRecoveryCodes = pgTable(
808 "user_recovery_codes",
809 {
810 id: uuid("id").primaryKey().defaultRandom(),
811 userId: uuid("user_id")
812 .notNull()
813 .references(() => users.id, { onDelete: "cascade" }),
814 codeHash: text("code_hash").notNull(),
815 usedAt: timestamp("used_at"),
816 createdAt: timestamp("created_at").defaultNow().notNull(),
817 },
818 (table) => [
819 index("recovery_codes_user").on(table.userId),
820 uniqueIndex("recovery_codes_user_hash").on(table.userId, table.codeHash),
821 ]
822);
823
824export type UserTotp = typeof userTotp.$inferSelect;
825export type UserRecoveryCode = typeof userRecoveryCodes.$inferSelect;
2df1f8cClaude826
827/**
828 * WebAuthn passkeys (Block B5).
829 *
830 * Each row is one registered authenticator. The `credentialId` is the
831 * globally-unique identifier the browser returns; `publicKey` is the
832 * COSE-encoded public key we use to verify signatures. `counter` tracks
833 * the authenticator's signature counter for replay-protection.
834 *
835 * `transports` is a JSON array (stored as text) of the
836 * AuthenticatorTransport values ("usb" | "nfc" | "ble" | "internal" | "hybrid").
837 */
838export const userPasskeys = pgTable(
839 "user_passkeys",
840 {
841 id: uuid("id").primaryKey().defaultRandom(),
842 userId: uuid("user_id")
843 .notNull()
844 .references(() => users.id, { onDelete: "cascade" }),
845 credentialId: text("credential_id").notNull().unique(),
846 publicKey: text("public_key").notNull(), // base64url of COSE key
847 counter: integer("counter").default(0).notNull(),
848 transports: text("transports"), // JSON array string
849 name: text("name").notNull().default("Passkey"),
850 lastUsedAt: timestamp("last_used_at"),
851 createdAt: timestamp("created_at").defaultNow().notNull(),
852 },
853 (table) => [index("passkeys_user").on(table.userId)]
854);
855
856/**
857 * Short-lived WebAuthn challenges. A row is written when we issue options
858 * (registration or authentication) and deleted after the verify step or when
859 * it expires (5 min). Keeping them in the DB lets us verify without sticky
860 * sessions or client-side state.
861 */
862export const webauthnChallenges = pgTable(
863 "webauthn_challenges",
864 {
865 id: uuid("id").primaryKey().defaultRandom(),
866 userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
867 // For passwordless login we don't know the user yet, so userId is nullable
868 // and we bind the challenge to a short-lived cookie token instead.
869 sessionKey: text("session_key").notNull().unique(),
870 challenge: text("challenge").notNull(),
871 kind: text("kind").notNull(), // "register" | "authenticate"
872 expiresAt: timestamp("expires_at").notNull(),
873 createdAt: timestamp("created_at").defaultNow().notNull(),
874 },
875 (table) => [index("webauthn_challenges_expires").on(table.expiresAt)]
876);
877
878export type UserPasskey = typeof userPasskeys.$inferSelect;
879export type WebauthnChallenge = typeof webauthnChallenges.$inferSelect;
bfdb5e7Claude880
881/**
882 * OAuth 2.0 provider (Block B6).
883 *
884 * `oauthApps` is a third-party app registered by a developer. Each app has
885 * a public `client_id`, a hashed `client_secret`, and one or more allowed
886 * `redirect_uris` (newline-separated). The plaintext secret is shown to the
887 * developer exactly once at creation and cannot be recovered; they can
888 * rotate it instead.
889 *
890 * `oauthAuthorizations` is a short-lived authorization code issued after
891 * the user consents at /oauth/authorize. Single-use: `usedAt` is set on
892 * redemption so a replay after-the-fact fails.
893 *
894 * `oauthAccessTokens` is a long-lived bearer token plus an optional
895 * refresh token. Both are stored as SHA-256 hashes; the plaintext values
896 * are only returned to the client once in the /oauth/token response.
897 */
898export const oauthApps = pgTable(
899 "oauth_apps",
900 {
901 id: uuid("id").primaryKey().defaultRandom(),
902 ownerId: uuid("owner_id")
903 .notNull()
904 .references(() => users.id, { onDelete: "cascade" }),
905 name: text("name").notNull(),
906 clientId: text("client_id").notNull().unique(),
907 clientSecretHash: text("client_secret_hash").notNull(),
908 clientSecretPrefix: text("client_secret_prefix").notNull(), // first 8 chars for display
909 /** Newline-separated list of allowed redirect URIs. */
910 redirectUris: text("redirect_uris").notNull(),
911 homepageUrl: text("homepage_url"),
912 description: text("description"),
913 /**
914 * If `true`, the app must present its client_secret at /oauth/token.
915 * Public SPA/mobile apps should set this to `false` and use PKCE.
916 */
917 confidential: boolean("confidential").default(true).notNull(),
918 revokedAt: timestamp("revoked_at"),
919 createdAt: timestamp("created_at").defaultNow().notNull(),
920 updatedAt: timestamp("updated_at").defaultNow().notNull(),
921 },
922 (table) => [index("oauth_apps_owner").on(table.ownerId)]
923);
924
925export const oauthAuthorizations = pgTable(
926 "oauth_authorizations",
927 {
928 id: uuid("id").primaryKey().defaultRandom(),
929 appId: uuid("app_id")
930 .notNull()
931 .references(() => oauthApps.id, { onDelete: "cascade" }),
932 userId: uuid("user_id")
933 .notNull()
934 .references(() => users.id, { onDelete: "cascade" }),
935 codeHash: text("code_hash").notNull().unique(),
936 redirectUri: text("redirect_uri").notNull(),
937 scopes: text("scopes").notNull().default(""),
938 codeChallenge: text("code_challenge"),
939 codeChallengeMethod: text("code_challenge_method"), // "S256" | "plain"
940 expiresAt: timestamp("expires_at").notNull(),
941 usedAt: timestamp("used_at"),
942 createdAt: timestamp("created_at").defaultNow().notNull(),
943 },
944 (table) => [index("oauth_authorizations_expires").on(table.expiresAt)]
945);
946
947export const oauthAccessTokens = pgTable(
948 "oauth_access_tokens",
949 {
950 id: uuid("id").primaryKey().defaultRandom(),
951 appId: uuid("app_id")
952 .notNull()
953 .references(() => oauthApps.id, { onDelete: "cascade" }),
954 userId: uuid("user_id")
955 .notNull()
956 .references(() => users.id, { onDelete: "cascade" }),
957 accessTokenHash: text("access_token_hash").notNull().unique(),
958 refreshTokenHash: text("refresh_token_hash").unique(),
959 scopes: text("scopes").notNull().default(""),
960 expiresAt: timestamp("expires_at").notNull(),
961 refreshExpiresAt: timestamp("refresh_expires_at"),
962 revokedAt: timestamp("revoked_at"),
963 lastUsedAt: timestamp("last_used_at"),
964 createdAt: timestamp("created_at").defaultNow().notNull(),
965 },
966 (table) => [
967 index("oauth_access_tokens_user").on(table.userId),
968 index("oauth_access_tokens_app").on(table.appId),
969 index("oauth_access_tokens_expires").on(table.expiresAt),
970 ]
971);
972
973export type OauthApp = typeof oauthApps.$inferSelect;
974export type OauthAuthorization = typeof oauthAuthorizations.$inferSelect;
975export type OauthAccessToken = typeof oauthAccessTokens.$inferSelect;