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