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