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