Pre-launch — Gluecron is in final validation. Public signups and git hosting for non-owner users open after launch review.
ccantynz/Gluecron.com
CodeIssuesDiscussionsWikiPull RequestsProjectsCommitsActionsReleasesContributorsPulse● GatesSecuritySettingsDeploymentsPipelineInsightsAgents✨ Explain✨ Ask AI✨ Workspace✨ Spec✨ Tests▓ Debt Map✨ NL Search🏛 Archaeology
Blame · Line-by-line history

schema.ts

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

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