Pre-launch — Gluecron is in final validation. Public signups and git hosting for non-owner users open after launch review.
ccantynz/Gluecron.com
CodeIssuesPull RequestsActionsSecurityInsightsSettings
✨ AI
ExplainAsk AIWorkspaceSpec to PRTestsNL SearchDebt MapArchaeology
More
CommitsDiscussionsWikiProjectsReleasesContributorsPulseGatesDeploymentsPipelineAgents
Blame · Line-by-line history

schema.ts

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

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