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.tsBlame4184 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,
5ca514aClaude13 numeric,
abfa9adClaude14 customType,
fc1817aClaude15} from "drizzle-orm/pg-core";
16
abfa9adClaude17// Postgres `bytea` — drizzle-orm doesn't ship a first-class bytea column, so
18// we declare one via customType that maps to Buffer on both read and write.
19// Used by `workflow_run_cache.content` where we really do need raw bytes
20// (unlike `workflow_artifacts.content`, which stayed base64-text for v1).
21const bytea = customType<{ data: Buffer; default: false }>({
22 dataType() {
23 return "bytea";
24 },
25});
26
a686079Claude27// pgvector `vector(N)` — drizzle-orm has no first-class pgvector column.
28// We map it to `number[]` and serialise/deserialise via Postgres's
29// canonical text format `[0.1,0.2,...]`. The `default: false` flag tells
30// drizzle this column has no default value.
31//
32// Migration 0057 creates the column with `vector(1024)`. If the migration
33// degraded (pgvector unavailable on the host), the column is missing and
34// every read/write throws; src/lib/semantic-index.ts catches at the
35// boundary and falls back to empty-result behaviour.
36const vector = customType<{ data: number[]; driverData: string; default: false }>({
37 dataType(config: unknown) {
38 const cfg = config as { dimensions?: number } | undefined;
39 return cfg?.dimensions ? `vector(${cfg.dimensions})` : "vector";
40 },
41 toDriver(value: number[]): string {
42 return "[" + value.join(",") + "]";
43 },
44 fromDriver(value: string | number[]): number[] {
45 if (Array.isArray(value)) return value as number[];
46 // Canonical format: "[0.1,0.2,0.3]"
47 const trimmed = String(value).trim();
48 if (!trimmed) return [];
49 const inner = trimmed.startsWith("[") && trimmed.endsWith("]")
50 ? trimmed.slice(1, -1)
51 : trimmed;
52 if (!inner) return [];
53 return inner.split(",").map((s) => parseFloat(s));
54 },
55});
56
fc1817aClaude57export const users = pgTable("users", {
58 id: uuid("id").primaryKey().defaultRandom(),
59 username: text("username").notNull().unique(),
60 email: text("email").notNull().unique(),
61 displayName: text("display_name"),
62 passwordHash: text("password_hash").notNull(),
63 avatarUrl: text("avatar_url"),
64 bio: text("bio"),
24cf2caClaude65 // Email notification preferences (Block A8). Default on; opt-out via /settings.
66 notifyEmailOnMention: boolean("notify_email_on_mention").default(true).notNull(),
67 notifyEmailOnAssign: boolean("notify_email_on_assign").default(true).notNull(),
68 notifyEmailOnGateFail: boolean("notify_email_on_gate_fail").default(true).notNull(),
08420cdClaude69 // Block I7 — weekly digest opt-in.
70 notifyEmailDigestWeekly: boolean("notify_email_digest_weekly").default(false).notNull(),
cb5a796Claude71 // Drizzle/0066 — Pending comment moderation requests. Defaults ON so
72 // a new repo owner is alerted as soon as the first comment lands in
73 // the queue. The in-app notification is always written regardless;
74 // this controls fan-out (currently the audit/notification surface,
75 // future email).
76 notifyEmailOnPendingComment: boolean("notify_email_on_pending_comment")
77 .default(true)
78 .notNull(),
08420cdClaude79 lastDigestSentAt: timestamp("last_digest_sent_at"),
46d6165Claude80 // Block L1 — Sleep Mode. When enabled, the autopilot sleep-mode-digest
81 // task delivers a daily "what Claude shipped overnight" report at the
e1fc7dbClaude82 // user-configured UTC hour (0-23, default 9). Uses its own independent
83 // cooldown anchor (lastSleepDigestSentAt) so the weekly digest timer is
84 // not reset when a sleep-mode digest fires, and vice versa.
85 // Migration 0077 adds the column.
86 lastSleepDigestSentAt: timestamp("last_sleep_digest_sent_at"),
46d6165Claude87 sleepModeEnabled: boolean("sleep_mode_enabled").default(false).notNull(),
88 sleepModeDigestHourUtc: integer("sleep_mode_digest_hour_utc").default(9).notNull(),
534f04aClaude89 // Block M2 — Web Push per-event preferences. Default on; opt-out via /settings.
90 notifyPushOnMention: boolean("notify_push_on_mention").default(true).notNull(),
91 notifyPushOnAssign: boolean("notify_push_on_assign").default(true).notNull(),
92 notifyPushOnReviewRequest: boolean("notify_push_on_review_request")
93 .default(true)
94 .notNull(),
95 notifyPushOnDeployFailed: boolean("notify_push_on_deploy_failed")
96 .default(true)
97 .notNull(),
a4f3e24Claude98 isAdmin: boolean("is_admin").default(false).notNull(),
c63b860Claude99 // Block P2 — set when the user clicks the verification link. Soft-gate
100 // only: registration succeeds regardless; /dashboard surfaces a banner
101 // until this is non-null.
102 emailVerifiedAt: timestamp("email_verified_at"),
103 // Block P5 — Account deletion with 30-day grace period.
104 // See drizzle/0049_account_deletion.sql.
105 deletedAt: timestamp("deleted_at"),
106 deletionScheduledFor: timestamp("deletion_scheduled_for"),
107 // Block P3 — Terms of Service / Privacy Policy acceptance audit trail.
108 // Set on /register when the user ticks the accept_terms checkbox.
109 // termsVersion bumps when Terms change; UI prompts re-acceptance later.
110 termsAcceptedAt: timestamp("terms_accepted_at"),
111 termsVersion: text("terms_version"),
cd4f63bTest User112 // Block Q3 — Anonymous playground accounts. When `isPlayground=true`, the
113 // account was minted by /play with a synthetic email + random password
114 // and is hard-deleted by the autopilot `playground-purge` task once
115 // `playgroundExpiresAt` passes. Real accounts always carry false/null.
116 // See drizzle/0052_playground_accounts.sql.
117 isPlayground: boolean("is_playground").default(false).notNull(),
118 playgroundExpiresAt: timestamp("playground_expires_at"),
ee7e577Claude119 // Migration 0071 — Personal cross-repo semantic index opt-in. When true,
120 // /chat (and the /api/v2/me/chat/messages endpoint) may search across
121 // every repo the user owns OR is an accepted collaborator on. Default
122 // OFF — privacy-first; the user must explicitly enable it via
123 // /settings/personal-semantic-toggle. The personal-semantic helpers
124 // hard-refuse to return any rows while this flag is false.
125 personalSemanticIndexEnabled: boolean("personal_semantic_index_enabled")
126 .default(false)
127 .notNull(),
b12f20dClaude128 // Onboarding drip sequence (migration 0081). Stores a JSON array of string
f65f600Claude129 // keys for emails already delivered, e.g. ["welcome","day1","day3"].
130 // The autopilot `onboarding-drip` task compares this against the canonical
131 // drip schedule and sends any outstanding emails. Never null — defaults to
132 // an empty array at insert time.
133 onboardingEmailsSent: jsonb("onboarding_emails_sent").$type<string[]>().default([]).notNull(),
fc1817aClaude134 createdAt: timestamp("created_at").defaultNow().notNull(),
135 updatedAt: timestamp("updated_at").defaultNow().notNull(),
136});
137
06d5ffeClaude138export const sessions = pgTable("sessions", {
139 id: uuid("id").primaryKey().defaultRandom(),
140 userId: uuid("user_id")
141 .notNull()
142 .references(() => users.id, { onDelete: "cascade" }),
143 token: text("token").notNull().unique(),
144 expiresAt: timestamp("expires_at").notNull(),
7298a17Claude145 // B4: true when the user has entered their password but not yet their TOTP
146 // code. softAuth/requireAuth treat such sessions as anonymous; only
147 // /login/2fa can consume them. Flips to false on successful 2FA.
148 requires2fa: boolean("requires_2fa").default(false).notNull(),
06d5ffeClaude149 createdAt: timestamp("created_at").defaultNow().notNull(),
150});
151
45e31d0Claude152// @ts-ignore — self-referential FK on forkedFromId causes circular inference
fc1817aClaude153export const repositories = pgTable(
154 "repositories",
155 {
156 id: uuid("id").primaryKey().defaultRandom(),
157 name: text("name").notNull(),
7437605Claude158 // ownerId = creator / user-owner. Always set (for attribution + user
159 // namespace uniqueness). For org-owned repos, also represents "created by".
fc1817aClaude160 ownerId: uuid("owner_id")
161 .notNull()
162 .references(() => users.id),
7437605Claude163 // Block B2: nullable org owner. When set, the repo lives in the org
164 // namespace and URL resolution routes `/:orgSlug/:repo` to it.
165 orgId: uuid("org_id"),
fc1817aClaude166 description: text("description"),
167 isPrivate: boolean("is_private").default(false).notNull(),
3ef4c9dClaude168 isArchived: boolean("is_archived").default(false).notNull(),
71cd5ecClaude169 isTemplate: boolean("is_template").default(false).notNull(),
fc1817aClaude170 defaultBranch: text("default_branch").default("main").notNull(),
171 diskPath: text("disk_path").notNull(),
45e31d0Claude172 forkedFromId: uuid("forked_from_id"),
fc1817aClaude173 createdAt: timestamp("created_at").defaultNow().notNull(),
174 updatedAt: timestamp("updated_at").defaultNow().notNull(),
175 pushedAt: timestamp("pushed_at"),
176 starCount: integer("star_count").default(0).notNull(),
177 forkCount: integer("fork_count").default(0).notNull(),
79136bbClaude178 issueCount: integer("issue_count").default(0).notNull(),
534f04aClaude179 // Block M5: autopilot stale-sweep opt-out flags. Default true — the
180 // 2-stage close (poke at 7d/30d, close at 14d/60d after poke) runs on
181 // every repo unless an owner disables it via repo-settings.
182 autoCloseStalePrs: boolean("auto_close_stale_prs").default(true).notNull(),
183 autoCloseStaleIssues: boolean("auto_close_stale_issues")
184 .default(true)
185 .notNull(),
4bbacbeClaude186 // Migration 0062 — opt-out flag for per-branch preview URLs. Default on;
187 // owners can disable via repo-settings. When false, post-receive skips
188 // the enqueuePreviewBuild call entirely.
189 previewBuildsEnabled: boolean("preview_builds_enabled")
190 .default(true)
191 .notNull(),
0c3eee5Claude192 // Migration 0065 — opt-in flag for the AI test generator autopilot task.
193 // Default false (off) because writing tests against unreviewed code can
194 // produce noise; owners must explicitly enable it via repo-settings.
195 autoGenerateTests: boolean("auto_generate_tests")
196 .default(false)
197 .notNull(),
79ed944Claude198 // Migration 0067 — opt-in flag for auto-provisioning a runnable PR
199 // sandbox on every PR open. Default false (off) because each sandbox
200 // burns compute; owners must explicitly enable it via repo-settings.
201 autoPrSandbox: boolean("auto_pr_sandbox").default(false).notNull(),
9b3a183Claude202 // Migration 0072 — opt-in flag for hosted-in-browser dev environments
203 // (cloud Codespaces alternative). Default false (off) because each
204 // env burns a container until the idle sweep tears it down; owners
205 // must explicitly enable per-repo via repo-settings.
206 devEnvsEnabled: boolean("dev_envs_enabled").default(false).notNull(),
44f1a02Claude207 // Migration 0077 — data residency region. 'us' = US East (default),
208 // 'eu' = Frankfurt (EU). EU data residency requires a Pro plan or
209 // higher. Future values (e.g. 'apac') are additive — no constraint
210 // at the DB level so we can extend without a new migration.
211 dataRegion: text("data_region").default("us").notNull(),
fc1817aClaude212 },
7437605Claude213 (table) => [
214 // Partial: uniqueness only in the user namespace (org-owned rows exempt).
215 // Matches the partial index in migration 0004.
216 uniqueIndex("repos_owner_name").on(table.ownerId, table.name),
217 index("repos_org").on(table.orgId),
218 ]
fc1817aClaude219);
220
3ef4c9dClaude221/**
222 * Per-repository gate + auto-repair configuration.
223 * Every new repo is created with all gates ENABLED by default —
224 * the "full green ecosystem" default. Owners can manually opt-out per setting.
225 */
226export const repoSettings = pgTable("repo_settings", {
227 id: uuid("id").primaryKey().defaultRandom(),
228 repositoryId: uuid("repository_id")
229 .notNull()
230 .unique()
231 .references(() => repositories.id, { onDelete: "cascade" }),
232 // Gates
233 gateTestEnabled: boolean("gate_test_enabled").default(true).notNull(),
234 aiReviewEnabled: boolean("ai_review_enabled").default(true).notNull(),
235 secretScanEnabled: boolean("secret_scan_enabled").default(true).notNull(),
236 securityScanEnabled: boolean("security_scan_enabled").default(true).notNull(),
237 dependencyScanEnabled: boolean("dependency_scan_enabled").default(true).notNull(),
238 lintEnabled: boolean("lint_enabled").default(true).notNull(),
239 typeCheckEnabled: boolean("type_check_enabled").default(true).notNull(),
240 testEnabled: boolean("test_enabled").default(true).notNull(),
241 // Auto-repair
242 autoFixEnabled: boolean("auto_fix_enabled").default(true).notNull(),
243 autoMergeResolveEnabled: boolean("auto_merge_resolve_enabled").default(true).notNull(),
244 autoFormatEnabled: boolean("auto_format_enabled").default(true).notNull(),
245 // AI features
246 aiCommitMessagesEnabled: boolean("ai_commit_messages_enabled").default(true).notNull(),
247 aiPrSummaryEnabled: boolean("ai_pr_summary_enabled").default(true).notNull(),
248 aiChangelogEnabled: boolean("ai_changelog_enabled").default(true).notNull(),
249 // Deploy
250 autoDeployEnabled: boolean("auto_deploy_enabled").default(true).notNull(),
251 deployRequireAllGreen: boolean("deploy_require_all_green").default(true).notNull(),
252 createdAt: timestamp("created_at").defaultNow().notNull(),
253 updatedAt: timestamp("updated_at").defaultNow().notNull(),
254});
255
256/**
257 * Branch protection rules — enforced on push and merge.
258 * Every repo's default branch gets a protection rule on creation.
259 */
260export const branchProtection = pgTable(
261 "branch_protection",
262 {
263 id: uuid("id").primaryKey().defaultRandom(),
264 repositoryId: uuid("repository_id")
265 .notNull()
266 .references(() => repositories.id, { onDelete: "cascade" }),
267 pattern: text("pattern").notNull(), // branch name or glob (e.g. "main", "release/*")
268 requirePullRequest: boolean("require_pull_request").default(true).notNull(),
269 requireGreenGates: boolean("require_green_gates").default(true).notNull(),
270 requireAiApproval: boolean("require_ai_approval").default(true).notNull(),
271 requireHumanReview: boolean("require_human_review").default(false).notNull(),
272 requiredApprovals: integer("required_approvals").default(0).notNull(),
273 allowForcePush: boolean("allow_force_push").default(false).notNull(),
274 allowDeletion: boolean("allow_deletion").default(false).notNull(),
275 dismissStaleReviews: boolean("dismiss_stale_reviews").default(true).notNull(),
4626e61Claude276 // K2 — Auto-merge opt-in. When true, the autopilot ticker may auto-merge
277 // a PR whose base branch matches this rule, provided every gate the
278 // manual-merge path enforces is green. Default-deny.
279 enableAutoMerge: boolean("enable_auto_merge").default(false).notNull(),
3ef4c9dClaude280 createdAt: timestamp("created_at").defaultNow().notNull(),
281 updatedAt: timestamp("updated_at").defaultNow().notNull(),
282 },
283 (table) => [
284 uniqueIndex("branch_protection_repo_pattern").on(
285 table.repositoryId,
286 table.pattern
287 ),
288 ]
289);
290
291/**
292 * Gate run history. Every push + every PR creates gate_runs entries —
293 * one per configured gate. Serves as the source of truth for "is this green?".
294 */
295export const gateRuns = pgTable(
296 "gate_runs",
297 {
298 id: uuid("id").primaryKey().defaultRandom(),
299 repositoryId: uuid("repository_id")
300 .notNull()
301 .references(() => repositories.id, { onDelete: "cascade" }),
302 pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
303 onDelete: "cascade",
304 }),
305 commitSha: text("commit_sha").notNull(),
306 ref: text("ref").notNull(),
307 gateName: text("gate_name").notNull(), // e.g. "GateTest", "AI Review", "Secret Scan", "Type Check"
308 status: text("status").notNull(), // pending, running, passed, failed, skipped, repaired
309 summary: text("summary"),
310 details: text("details"), // JSON: per-check output, affected files, etc
311 repairAttempted: boolean("repair_attempted").default(false).notNull(),
312 repairSucceeded: boolean("repair_succeeded").default(false).notNull(),
313 repairCommitSha: text("repair_commit_sha"),
314 durationMs: integer("duration_ms"),
315 createdAt: timestamp("created_at").defaultNow().notNull(),
316 completedAt: timestamp("completed_at"),
317 },
318 (table) => [
319 index("gate_runs_repo_sha").on(table.repositoryId, table.commitSha),
320 index("gate_runs_pr").on(table.pullRequestId),
321 index("gate_runs_created").on(table.createdAt),
322 ]
323);
324
325/**
326 * In-app notifications. Powered by the activity feed + explicit mentions.
327 */
328export const notifications = pgTable(
329 "notifications",
330 {
331 id: uuid("id").primaryKey().defaultRandom(),
332 userId: uuid("user_id")
333 .notNull()
334 .references(() => users.id, { onDelete: "cascade" }),
335 repositoryId: uuid("repository_id").references(() => repositories.id, {
336 onDelete: "cascade",
337 }),
338 kind: text("kind").notNull(), // mention, review_requested, pr_merged, pr_closed, gate_failed, gate_repaired, ai_review, assigned, security_alert, deploy_success, deploy_failed
339 title: text("title").notNull(),
340 body: text("body"),
341 url: text("url"), // link to the relevant page
342 readAt: timestamp("read_at"),
343 createdAt: timestamp("created_at").defaultNow().notNull(),
344 },
345 (table) => [
346 index("notifications_user_unread").on(table.userId, table.readAt),
347 index("notifications_user_created").on(table.userId, table.createdAt),
348 ]
349);
350
351/**
352 * Releases — named snapshots of a repo at a tag/commit.
353 * AI-generated changelogs bundled in notes field.
354 */
355export const releases = pgTable(
356 "releases",
357 {
358 id: uuid("id").primaryKey().defaultRandom(),
359 repositoryId: uuid("repository_id")
360 .notNull()
361 .references(() => repositories.id, { onDelete: "cascade" }),
362 authorId: uuid("author_id")
363 .notNull()
364 .references(() => users.id),
365 tag: text("tag").notNull(),
366 name: text("name").notNull(),
367 body: text("body"), // AI-generated release notes + changelog
368 targetCommit: text("target_commit").notNull(),
369 isDraft: boolean("is_draft").default(false).notNull(),
370 isPrerelease: boolean("is_prerelease").default(false).notNull(),
371 createdAt: timestamp("created_at").defaultNow().notNull(),
372 publishedAt: timestamp("published_at"),
373 },
374 (table) => [
375 uniqueIndex("releases_repo_tag").on(table.repositoryId, table.tag),
376 ]
377);
378
379/**
380 * Milestones — group issues + PRs toward a shared goal.
381 */
382export const milestones = pgTable(
383 "milestones",
384 {
385 id: uuid("id").primaryKey().defaultRandom(),
386 repositoryId: uuid("repository_id")
387 .notNull()
388 .references(() => repositories.id, { onDelete: "cascade" }),
389 title: text("title").notNull(),
390 description: text("description"),
391 state: text("state").notNull().default("open"), // open, closed
392 dueDate: timestamp("due_date"),
393 createdAt: timestamp("created_at").defaultNow().notNull(),
394 closedAt: timestamp("closed_at"),
395 },
396 (table) => [index("milestones_repo_state").on(table.repositoryId, table.state)]
397);
398
399/**
400 * Reactions on issues, PRs, and comments. Universal target pointer.
401 */
402export const reactions = pgTable(
403 "reactions",
404 {
405 id: uuid("id").primaryKey().defaultRandom(),
406 userId: uuid("user_id")
407 .notNull()
408 .references(() => users.id, { onDelete: "cascade" }),
409 targetType: text("target_type").notNull(), // issue, pr, issue_comment, pr_comment
410 targetId: uuid("target_id").notNull(),
411 emoji: text("emoji").notNull(), // thumbs_up, thumbs_down, rocket, heart, eyes, laugh, hooray, confused
412 createdAt: timestamp("created_at").defaultNow().notNull(),
413 },
414 (table) => [
415 uniqueIndex("reactions_unique").on(
416 table.userId,
417 table.targetType,
418 table.targetId,
419 table.emoji
420 ),
421 index("reactions_target").on(table.targetType, table.targetId),
422 ]
423);
424
425/**
426 * PR reviews (formal approve/request-changes).
427 * Separate from inline comments in pr_comments.
428 */
429export const prReviews = pgTable(
430 "pr_reviews",
431 {
432 id: uuid("id").primaryKey().defaultRandom(),
433 pullRequestId: uuid("pull_request_id")
434 .notNull()
435 .references(() => pullRequests.id, { onDelete: "cascade" }),
436 reviewerId: uuid("reviewer_id")
437 .notNull()
438 .references(() => users.id),
439 state: text("state").notNull(), // approved, changes_requested, commented
440 body: text("body"),
441 isAi: boolean("is_ai").default(false).notNull(),
442 createdAt: timestamp("created_at").defaultNow().notNull(),
443 },
444 (table) => [index("pr_reviews_pr").on(table.pullRequestId)]
445);
446
447/**
448 * Code owners — who owns which paths (auto-request review on PR).
449 * Parsed from a CODEOWNERS file at the root of the default branch.
450 */
451export const codeOwners = pgTable(
452 "code_owners",
453 {
454 id: uuid("id").primaryKey().defaultRandom(),
455 repositoryId: uuid("repository_id")
456 .notNull()
457 .references(() => repositories.id, { onDelete: "cascade" }),
458 pathPattern: text("path_pattern").notNull(),
459 ownerUsernames: text("owner_usernames").notNull(), // comma-separated
460 createdAt: timestamp("created_at").defaultNow().notNull(),
461 },
462 (table) => [index("code_owners_repo").on(table.repositoryId)]
463);
464
465/**
466 * Per-repo AI chat sessions — conversational repo assistant.
467 */
468export const aiChats = pgTable(
469 "ai_chats",
470 {
471 id: uuid("id").primaryKey().defaultRandom(),
472 userId: uuid("user_id")
473 .notNull()
474 .references(() => users.id, { onDelete: "cascade" }),
475 repositoryId: uuid("repository_id").references(() => repositories.id, {
476 onDelete: "cascade",
477 }),
478 title: text("title"),
479 messages: text("messages").notNull().default("[]"), // JSON array of {role, content}
480 createdAt: timestamp("created_at").defaultNow().notNull(),
481 updatedAt: timestamp("updated_at").defaultNow().notNull(),
482 },
483 (table) => [
484 index("ai_chats_user").on(table.userId),
485 index("ai_chats_repo").on(table.repositoryId),
486 ]
487);
488
489/**
490 * Audit log — every sensitive action. Who did what, when, from where.
491 */
492export const auditLog = pgTable(
493 "audit_log",
494 {
495 id: uuid("id").primaryKey().defaultRandom(),
496 userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
497 repositoryId: uuid("repository_id").references(() => repositories.id, {
498 onDelete: "set null",
499 }),
500 action: text("action").notNull(), // repo.create, repo.delete, repo.transfer, token.create, token.revoke, merge, force_push, branch_protection.update, deploy, ...
501 targetType: text("target_type"),
502 targetId: text("target_id"),
503 ip: text("ip"),
504 userAgent: text("user_agent"),
505 metadata: text("metadata"), // JSON for extra context
506 createdAt: timestamp("created_at").defaultNow().notNull(),
507 },
508 (table) => [
509 index("audit_log_user").on(table.userId),
510 index("audit_log_repo").on(table.repositoryId),
511 index("audit_log_created").on(table.createdAt),
512 ]
513);
514
515/**
516 * Deployments — tracks every deploy to downstream systems (Crontech, etc).
517 * Each deploy is gated on ALL green gates passing.
518 */
519export const deployments = pgTable(
520 "deployments",
521 {
522 id: uuid("id").primaryKey().defaultRandom(),
523 repositoryId: uuid("repository_id")
524 .notNull()
525 .references(() => repositories.id, { onDelete: "cascade" }),
526 environment: text("environment").notNull().default("production"),
527 commitSha: text("commit_sha").notNull(),
528 ref: text("ref").notNull(),
a76d984Claude529 status: text("status").notNull(), // pending, running, success, failed, blocked, waiting_timer
3ef4c9dClaude530 blockedReason: text("blocked_reason"),
531 target: text("target"), // e.g. "crontech", "fly.io"
532 triggeredBy: uuid("triggered_by").references(() => users.id),
a76d984Claude533 /**
534 * Set when an approved deploy is held by `environments.wait_timer_minutes`.
535 * NULL = no wait; non-null = autopilot flips status from "waiting_timer"
536 * to "pending" once `now() >= ready_after`.
537 */
538 readyAfter: timestamp("ready_after"),
3ef4c9dClaude539 createdAt: timestamp("created_at").defaultNow().notNull(),
540 completedAt: timestamp("completed_at"),
541 },
542 (table) => [
543 index("deployments_repo").on(table.repositoryId),
544 index("deployments_created").on(table.createdAt),
545 ]
546);
547
548/**
549 * Rate-limit buckets — in-memory or persisted counter per IP / token / route.
550 */
551export const rateLimitBuckets = pgTable(
552 "rate_limit_buckets",
553 {
554 id: uuid("id").primaryKey().defaultRandom(),
555 bucketKey: text("bucket_key").notNull().unique(), // "ip:1.2.3.4:api" or "token:abc:api"
556 count: integer("count").default(0).notNull(),
557 windowStart: timestamp("window_start").defaultNow().notNull(),
558 expiresAt: timestamp("expires_at").notNull(),
559 },
560 (table) => [index("rate_limit_expires").on(table.expiresAt)]
fc1817aClaude561);
562
06d5ffeClaude563export const stars = pgTable(
564 "stars",
565 {
566 id: uuid("id").primaryKey().defaultRandom(),
567 userId: uuid("user_id")
568 .notNull()
569 .references(() => users.id, { onDelete: "cascade" }),
570 repositoryId: uuid("repository_id")
571 .notNull()
572 .references(() => repositories.id, { onDelete: "cascade" }),
573 createdAt: timestamp("created_at").defaultNow().notNull(),
574 },
79136bbClaude575 (table) => [
576 uniqueIndex("stars_user_repo").on(table.userId, table.repositoryId),
577 ]
578);
579
580export const issues = pgTable(
581 "issues",
582 {
583 id: uuid("id").primaryKey().defaultRandom(),
584 number: serial("number"),
585 repositoryId: uuid("repository_id")
586 .notNull()
587 .references(() => repositories.id, { onDelete: "cascade" }),
588 authorId: uuid("author_id")
589 .notNull()
590 .references(() => users.id),
591 title: text("title").notNull(),
592 body: text("body"),
593 state: text("state").notNull().default("open"), // open, closed
594 createdAt: timestamp("created_at").defaultNow().notNull(),
595 updatedAt: timestamp("updated_at").defaultNow().notNull(),
596 closedAt: timestamp("closed_at"),
597 },
598 (table) => [
599 index("issues_repo_state").on(table.repositoryId, table.state),
600 index("issues_repo_number").on(table.repositoryId, table.number),
601 ]
602);
603
604export const issueComments = pgTable(
605 "issue_comments",
606 {
607 id: uuid("id").primaryKey().defaultRandom(),
608 issueId: uuid("issue_id")
609 .notNull()
610 .references(() => issues.id, { onDelete: "cascade" }),
611 authorId: uuid("author_id")
612 .notNull()
613 .references(() => users.id),
614 body: text("body").notNull(),
cb5a796Claude615 // Comment moderation (drizzle/0066). 'approved' | 'pending' | 'rejected'
616 // | 'spam'. Default 'approved' keeps every legacy + collaborator path
617 // unchanged; the gate in `lib/comment-moderation.ts` only routes new
618 // non-collaborator comments into 'pending'.
619 moderationStatus: text("moderation_status").notNull().default("approved"),
620 moderatedAt: timestamp("moderated_at", { withTimezone: true }),
621 moderatedByUserId: uuid("moderated_by_user_id").references(() => users.id, {
622 onDelete: "set null",
623 }),
79136bbClaude624 createdAt: timestamp("created_at").defaultNow().notNull(),
625 updatedAt: timestamp("updated_at").defaultNow().notNull(),
626 },
627 (table) => [index("comments_issue").on(table.issueId)]
628);
629
630export const labels = pgTable(
631 "labels",
632 {
633 id: uuid("id").primaryKey().defaultRandom(),
634 repositoryId: uuid("repository_id")
635 .notNull()
636 .references(() => repositories.id, { onDelete: "cascade" }),
637 name: text("name").notNull(),
638 color: text("color").notNull().default("#8b949e"),
639 description: text("description"),
640 },
641 (table) => [
642 uniqueIndex("labels_repo_name").on(table.repositoryId, table.name),
643 ]
644);
645
646export const issueLabels = pgTable(
647 "issue_labels",
648 {
649 id: uuid("id").primaryKey().defaultRandom(),
650 issueId: uuid("issue_id")
651 .notNull()
652 .references(() => issues.id, { onDelete: "cascade" }),
653 labelId: uuid("label_id")
654 .notNull()
655 .references(() => labels.id, { onDelete: "cascade" }),
656 },
657 (table) => [
658 uniqueIndex("issue_labels_unique").on(table.issueId, table.labelId),
659 ]
06d5ffeClaude660);
661
0074234Claude662export const pullRequests = pgTable(
663 "pull_requests",
664 {
665 id: uuid("id").primaryKey().defaultRandom(),
666 number: serial("number"),
667 repositoryId: uuid("repository_id")
668 .notNull()
669 .references(() => repositories.id, { onDelete: "cascade" }),
670 authorId: uuid("author_id")
671 .notNull()
672 .references(() => users.id),
673 title: text("title").notNull(),
674 body: text("body"),
675 state: text("state").notNull().default("open"), // open, closed, merged
676 baseBranch: text("base_branch").notNull(),
677 headBranch: text("head_branch").notNull(),
3ef4c9dClaude678 isDraft: boolean("is_draft").default(false).notNull(),
679 mergeStrategy: text("merge_strategy").default("merge").notNull(), // merge, squash, rebase
680 milestoneId: uuid("milestone_id"),
0074234Claude681 mergedAt: timestamp("merged_at"),
682 mergedBy: uuid("merged_by").references(() => users.id),
683 createdAt: timestamp("created_at").defaultNow().notNull(),
684 updatedAt: timestamp("updated_at").defaultNow().notNull(),
685 closedAt: timestamp("closed_at"),
686 },
687 (table) => [
688 index("prs_repo_state").on(table.repositoryId, table.state),
689 index("prs_repo_number").on(table.repositoryId, table.number),
690 ]
691);
692
693export const prComments = pgTable(
694 "pr_comments",
695 {
696 id: uuid("id").primaryKey().defaultRandom(),
697 pullRequestId: uuid("pull_request_id")
698 .notNull()
699 .references(() => pullRequests.id, { onDelete: "cascade" }),
700 authorId: uuid("author_id")
701 .notNull()
702 .references(() => users.id),
703 body: text("body").notNull(),
704 isAiReview: boolean("is_ai_review").default(false).notNull(),
705 filePath: text("file_path"),
706 lineNumber: integer("line_number"),
cb5a796Claude707 // Comment moderation (drizzle/0066). See `issueComments.moderationStatus`.
708 moderationStatus: text("moderation_status").notNull().default("approved"),
709 moderatedAt: timestamp("moderated_at", { withTimezone: true }),
710 moderatedByUserId: uuid("moderated_by_user_id").references(() => users.id, {
711 onDelete: "set null",
712 }),
0074234Claude713 createdAt: timestamp("created_at").defaultNow().notNull(),
714 updatedAt: timestamp("updated_at").defaultNow().notNull(),
715 },
716 (table) => [index("pr_comments_pr").on(table.pullRequestId)]
717);
718
cb5a796Claude719/**
720 * Comment moderation (drizzle/0066) — per-repo allow/deny list for
721 * commenters. A 'trusted' row makes `shouldRequireApproval` short-circuit
722 * to false; a 'banned' row makes it short-circuit to "auto-reject without
723 * notifying the owner" so a single spam decision sticks.
724 *
725 * Indexed by (repository_id, commenter_user_id) for the per-comment gate
726 * lookup and by (repository_id, status) for the owner's trust-list page.
727 */
728export const repoCommenterTrust = pgTable(
729 "repo_commenter_trust",
730 {
731 id: uuid("id").primaryKey().defaultRandom(),
732 repositoryId: uuid("repository_id")
733 .notNull()
734 .references(() => repositories.id, { onDelete: "cascade" }),
735 commenterUserId: uuid("commenter_user_id")
736 .notNull()
737 .references(() => users.id, { onDelete: "cascade" }),
738 status: text("status").notNull(), // 'trusted' | 'banned'
739 grantedByUserId: uuid("granted_by_user_id").references(() => users.id, {
740 onDelete: "set null",
741 }),
742 grantedAt: timestamp("granted_at", { withTimezone: true })
743 .defaultNow()
744 .notNull(),
745 },
746 (table) => [
747 uniqueIndex("repo_commenter_trust_unique").on(
748 table.repositoryId,
749 table.commenterUserId
750 ),
751 index("repo_commenter_trust_repo_status").on(
752 table.repositoryId,
753 table.status
754 ),
755 ]
756);
757
0074234Claude758export const activityFeed = pgTable(
759 "activity_feed",
760 {
761 id: uuid("id").primaryKey().defaultRandom(),
762 repositoryId: uuid("repository_id")
763 .notNull()
764 .references(() => repositories.id, { onDelete: "cascade" }),
765 userId: uuid("user_id").references(() => users.id),
766 action: text("action").notNull(), // push, issue_open, issue_close, pr_open, pr_merge, star, comment
767 targetType: text("target_type"), // issue, pr, commit
768 targetId: text("target_id"),
769 metadata: text("metadata"), // JSON string for extra data
770 createdAt: timestamp("created_at").defaultNow().notNull(),
771 },
772 (table) => [
773 index("activity_repo").on(table.repositoryId),
774 index("activity_user").on(table.userId),
775 ]
776);
777
c81ab7aClaude778export const webhooks = pgTable(
779 "webhooks",
780 {
781 id: uuid("id").primaryKey().defaultRandom(),
782 repositoryId: uuid("repository_id")
783 .notNull()
784 .references(() => repositories.id, { onDelete: "cascade" }),
785 url: text("url").notNull(),
786 secret: text("secret"),
787 events: text("events").notNull().default("push"), // comma-separated: push,issue,pr
788 isActive: boolean("is_active").default(true).notNull(),
789 lastDeliveredAt: timestamp("last_delivered_at"),
790 lastStatus: integer("last_status"),
791 createdAt: timestamp("created_at").defaultNow().notNull(),
792 },
793 (table) => [index("webhooks_repo").on(table.repositoryId)]
794);
795
8405c43Claude796// Reliable webhook delivery (migration 0056). One row per (hook, event)
797// emission. Worker in src/lib/webhook-delivery.ts polls pending rows whose
798// next_attempt_at <= now() and retries with exponential backoff.
799export const webhookDeliveries = pgTable(
800 "webhook_deliveries",
801 {
802 id: uuid("id").primaryKey().defaultRandom(),
803 webhookId: uuid("webhook_id")
804 .notNull()
805 .references(() => webhooks.id, { onDelete: "cascade" }),
806 event: text("event").notNull(),
807 payload: text("payload").notNull(),
808 signature: text("signature").notNull(),
809 attemptCount: integer("attempt_count").default(0).notNull(),
810 nextAttemptAt: timestamp("next_attempt_at", { withTimezone: true }),
811 status: text("status").default("pending").notNull(), // pending | succeeded | failed | dead
812 lastStatusCode: integer("last_status_code"),
813 lastError: text("last_error"),
814 lastAttemptedAt: timestamp("last_attempted_at", { withTimezone: true }),
815 succeededAt: timestamp("succeeded_at", { withTimezone: true }),
816 createdAt: timestamp("created_at", { withTimezone: true })
817 .defaultNow()
818 .notNull(),
819 },
820 (table) => [
821 index("idx_webhook_deliveries_next_attempt").on(table.nextAttemptAt),
822 index("idx_webhook_deliveries_webhook_id").on(
823 table.webhookId,
824 table.createdAt
825 ),
826 ]
827);
828
c81ab7aClaude829export const apiTokens = pgTable("api_tokens", {
830 id: uuid("id").primaryKey().defaultRandom(),
831 userId: uuid("user_id")
832 .notNull()
833 .references(() => users.id, { onDelete: "cascade" }),
834 name: text("name").notNull(),
835 tokenHash: text("token_hash").notNull(),
836 tokenPrefix: text("token_prefix").notNull(), // first 8 chars for display
837 scopes: text("scopes").notNull().default("repo"), // comma-separated
838 lastUsedAt: timestamp("last_used_at"),
839 expiresAt: timestamp("expires_at"),
840 createdAt: timestamp("created_at").defaultNow().notNull(),
841});
842
843export const repoTopics = pgTable(
844 "repo_topics",
845 {
846 id: uuid("id").primaryKey().defaultRandom(),
847 repositoryId: uuid("repository_id")
848 .notNull()
849 .references(() => repositories.id, { onDelete: "cascade" }),
850 topic: text("topic").notNull(),
851 },
852 (table) => [
853 uniqueIndex("repo_topics_unique").on(table.repositoryId, table.topic),
854 index("topics_name").on(table.topic),
855 ]
856);
857
fc1817aClaude858export const sshKeys = pgTable("ssh_keys", {
859 id: uuid("id").primaryKey().defaultRandom(),
860 userId: uuid("user_id")
861 .notNull()
06d5ffeClaude862 .references(() => users.id, { onDelete: "cascade" }),
fc1817aClaude863 title: text("title").notNull(),
864 fingerprint: text("fingerprint").notNull(),
865 publicKey: text("public_key").notNull(),
866 lastUsedAt: timestamp("last_used_at"),
867 createdAt: timestamp("created_at").defaultNow().notNull(),
868});
869
845fd8aClaude870// OCI Container Registry — migration 0083_oci_registry.sql
871// oci_repositories tracks image namespaces (e.g. "alice/myapp").
872// oci_tags maps mutable tag names to a manifest digest for a repository.
873
874export const ociRepositories = pgTable(
875 "oci_repositories",
876 {
877 id: uuid("id").primaryKey().defaultRandom(),
878 ownerId: uuid("owner_id")
879 .notNull()
880 .references(() => users.id, { onDelete: "cascade" }),
881 /** Full image name in "<owner>/<image>" format. */
882 name: text("name").notNull(),
883 visibility: text("visibility").notNull().default("private"), // "public" | "private"
884 createdAt: timestamp("created_at").defaultNow().notNull(),
885 },
886 (table) => [
887 uniqueIndex("oci_repositories_owner_name").on(table.ownerId, table.name),
888 index("idx_oci_repositories_owner").on(table.ownerId),
889 ]
890);
891
892export const ociTags = pgTable(
893 "oci_tags",
894 {
895 id: uuid("id").primaryKey().defaultRandom(),
896 repositoryId: uuid("repository_id")
897 .notNull()
898 .references(() => ociRepositories.id, { onDelete: "cascade" }),
899 tag: text("tag").notNull(),
900 manifestDigest: text("manifest_digest").notNull(), // "sha256:<hex64>"
901 createdAt: timestamp("created_at").defaultNow().notNull(),
902 updatedAt: timestamp("updated_at").defaultNow().notNull(),
903 },
904 (table) => [
905 uniqueIndex("oci_tags_repo_tag").on(table.repositoryId, table.tag),
906 index("idx_oci_tags_repo").on(table.repositoryId),
907 ]
908);
909
910export type OciRepository = typeof ociRepositories.$inferSelect;
911export type NewOciRepository = typeof ociRepositories.$inferInsert;
912export type OciTag = typeof ociTags.$inferSelect;
913export type NewOciTag = typeof ociTags.$inferInsert;
914
534f04aClaude915// Block M2 — Web Push subscriptions. One row per (user, endpoint).
916// Endpoint is the browser-issued push URL; p256dh + auth are the W3C
917// payload-encryption keys (base64url). Stale endpoints (HTTP 410) are
918// deleted lazily on next `sendPushToUser` pass.
919export const pushSubscriptions = pgTable(
920 "push_subscriptions",
921 {
922 id: uuid("id").primaryKey().defaultRandom(),
923 userId: uuid("user_id")
924 .notNull()
925 .references(() => users.id, { onDelete: "cascade" }),
926 endpoint: text("endpoint").notNull(),
927 p256dh: text("p256dh").notNull(),
928 auth: text("auth").notNull(),
929 userAgent: text("user_agent"),
930 createdAt: timestamp("created_at").defaultNow().notNull(),
931 lastUsedAt: timestamp("last_used_at"),
932 },
933 (table) => [
934 uniqueIndex("push_subscriptions_user_endpoint").on(
935 table.userId,
936 table.endpoint
937 ),
938 index("idx_push_subscriptions_user").on(table.userId),
939 ]
940);
941
fc1817aClaude942export type User = typeof users.$inferSelect;
943export type NewUser = typeof users.$inferInsert;
944export type Repository = typeof repositories.$inferSelect;
945export type NewRepository = typeof repositories.$inferInsert;
06d5ffeClaude946export type Session = typeof sessions.$inferSelect;
947export type Star = typeof stars.$inferSelect;
948export type SshKey = typeof sshKeys.$inferSelect;
534f04aClaude949export type PushSubscription = typeof pushSubscriptions.$inferSelect;
950export type NewPushSubscription = typeof pushSubscriptions.$inferInsert;
79136bbClaude951export type Issue = typeof issues.$inferSelect;
952export type IssueComment = typeof issueComments.$inferSelect;
953export type Label = typeof labels.$inferSelect;
0074234Claude954export type PullRequest = typeof pullRequests.$inferSelect;
955export type PrComment = typeof prComments.$inferSelect;
cb5a796Claude956export type RepoCommenterTrust = typeof repoCommenterTrust.$inferSelect;
957export type NewRepoCommenterTrust = typeof repoCommenterTrust.$inferInsert;
0074234Claude958export type ActivityEntry = typeof activityFeed.$inferSelect;
c81ab7aClaude959export type Webhook = typeof webhooks.$inferSelect;
960export type ApiToken = typeof apiTokens.$inferSelect;
961export type RepoTopic = typeof repoTopics.$inferSelect;
3ef4c9dClaude962export type RepoSettings = typeof repoSettings.$inferSelect;
963export type BranchProtection = typeof branchProtection.$inferSelect;
964export type GateRun = typeof gateRuns.$inferSelect;
965export type Notification = typeof notifications.$inferSelect;
966export type Release = typeof releases.$inferSelect;
967export type Milestone = typeof milestones.$inferSelect;
968export type Reaction = typeof reactions.$inferSelect;
969export type PrReview = typeof prReviews.$inferSelect;
970export type CodeOwner = typeof codeOwners.$inferSelect;
971export type AiChat = typeof aiChats.$inferSelect;
972export type AuditLogEntry = typeof auditLog.$inferSelect;
973export type Deployment = typeof deployments.$inferSelect;
24cf2caClaude974
975/**
976 * Saved replies — per-user canned responses, insertable into any
977 * issue / PR comment textarea. Shortcut name must be unique per user.
978 */
979export const savedReplies = pgTable(
980 "saved_replies",
981 {
982 id: uuid("id").primaryKey().defaultRandom(),
983 userId: uuid("user_id")
984 .notNull()
985 .references(() => users.id, { onDelete: "cascade" }),
986 shortcut: text("shortcut").notNull(),
987 body: text("body").notNull(),
988 createdAt: timestamp("created_at").defaultNow().notNull(),
989 updatedAt: timestamp("updated_at").defaultNow().notNull(),
990 },
991 (table) => [
992 uniqueIndex("saved_replies_user_shortcut").on(table.userId, table.shortcut),
993 ]
994);
995
996export type SavedReply = typeof savedReplies.$inferSelect;
5cc5d95Claude997
998/**
999 * Organizations (Block B1) — multi-user namespaces. Distinct from `users`.
1000 * An org has members (with org-level roles) and may contain teams.
1001 * Repos can be owned by an org via `repositories.orgId` (added in Block B2).
1002 *
1003 * Slug is globally unique against itself; collision with a username is
1004 * checked at create time in the route handler (no DB-level cross-table
1005 * uniqueness in Postgres).
1006 */
1007export const organizations = pgTable("organizations", {
1008 id: uuid("id").primaryKey().defaultRandom(),
1009 slug: text("slug").notNull().unique(),
1010 name: text("name").notNull(),
1011 description: text("description"),
1012 avatarUrl: text("avatar_url"),
1013 billingEmail: text("billing_email"),
1014 createdById: uuid("created_by_id")
1015 .notNull()
1016 .references(() => users.id, { onDelete: "restrict" }),
1017 createdAt: timestamp("created_at").defaultNow().notNull(),
1018 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1019});
1020
1021/**
1022 * Org membership. Roles: owner (full control, billing), admin (manage
1023 * members + teams + repos), member (default; can be added to teams).
1024 */
1025export const orgMembers = pgTable(
1026 "org_members",
1027 {
1028 id: uuid("id").primaryKey().defaultRandom(),
1029 orgId: uuid("org_id")
1030 .notNull()
1031 .references(() => organizations.id, { onDelete: "cascade" }),
1032 userId: uuid("user_id")
1033 .notNull()
1034 .references(() => users.id, { onDelete: "cascade" }),
1035 role: text("role").notNull().default("member"), // owner | admin | member
1036 createdAt: timestamp("created_at").defaultNow().notNull(),
1037 },
1038 (table) => [
1039 uniqueIndex("org_members_unique").on(table.orgId, table.userId),
1040 index("org_members_user").on(table.userId),
1041 ]
1042);
1043
1044/**
1045 * Teams within an org. Slug is unique within an org.
1046 * `parentTeamId` allows nesting (GitHub-style child teams). Optional.
1047 */
1048export const teams = pgTable(
1049 "teams",
1050 {
1051 id: uuid("id").primaryKey().defaultRandom(),
1052 orgId: uuid("org_id")
1053 .notNull()
1054 .references(() => organizations.id, { onDelete: "cascade" }),
1055 slug: text("slug").notNull(),
1056 name: text("name").notNull(),
1057 description: text("description"),
1058 parentTeamId: uuid("parent_team_id"),
1059 createdAt: timestamp("created_at").defaultNow().notNull(),
1060 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1061 },
1062 (table) => [uniqueIndex("teams_org_slug").on(table.orgId, table.slug)]
1063);
1064
1065/**
1066 * Team membership. Roles: maintainer (can edit team), member (default).
1067 * A user can belong to many teams; team membership requires org membership
1068 * but that invariant is enforced at the route layer, not the DB layer.
1069 */
1070export const teamMembers = pgTable(
1071 "team_members",
1072 {
1073 id: uuid("id").primaryKey().defaultRandom(),
1074 teamId: uuid("team_id")
1075 .notNull()
1076 .references(() => teams.id, { onDelete: "cascade" }),
1077 userId: uuid("user_id")
1078 .notNull()
1079 .references(() => users.id, { onDelete: "cascade" }),
1080 role: text("role").notNull().default("member"), // maintainer | member
1081 createdAt: timestamp("created_at").defaultNow().notNull(),
1082 },
1083 (table) => [
1084 uniqueIndex("team_members_unique").on(table.teamId, table.userId),
1085 index("team_members_user").on(table.userId),
1086 ]
1087);
1088
1089export type Organization = typeof organizations.$inferSelect;
1090export type OrgMember = typeof orgMembers.$inferSelect;
1091export type Team = typeof teams.$inferSelect;
1092export type TeamMember = typeof teamMembers.$inferSelect;
1093export type OrgRole = "owner" | "admin" | "member";
1094export type TeamRole = "maintainer" | "member";
7298a17Claude1095
1096/**
1097 * 2FA / TOTP (Block B4).
1098 *
1099 * Secret is stored in plain Base32 for now — the DB has row-level-secure
1100 * access and the app boundary is the only code that reads it. A follow-up
1101 * (B4.1) will wrap it with AES-GCM at rest once we standardise the KEK.
1102 *
1103 * `enabledAt` is set only after the user has successfully entered their
1104 * first code (confirming the authenticator was set up correctly). Rows with
1105 * `enabledAt = NULL` represent pending enrolment.
1106 */
1107export const userTotp = pgTable("user_totp", {
1108 userId: uuid("user_id")
1109 .primaryKey()
1110 .references(() => users.id, { onDelete: "cascade" }),
1111 secret: text("secret").notNull(),
1112 enabledAt: timestamp("enabled_at"),
1113 lastUsedAt: timestamp("last_used_at"),
1114 createdAt: timestamp("created_at").defaultNow().notNull(),
1115});
1116
1117/**
1118 * Recovery codes — single-use fallback when the authenticator is lost.
1119 * Stored as SHA-256 hashes; used rows are marked with `usedAt` rather than
1120 * deleted so the audit log keeps the full history.
1121 */
1122export const userRecoveryCodes = pgTable(
1123 "user_recovery_codes",
1124 {
1125 id: uuid("id").primaryKey().defaultRandom(),
1126 userId: uuid("user_id")
1127 .notNull()
1128 .references(() => users.id, { onDelete: "cascade" }),
1129 codeHash: text("code_hash").notNull(),
1130 usedAt: timestamp("used_at"),
1131 createdAt: timestamp("created_at").defaultNow().notNull(),
1132 },
1133 (table) => [
1134 index("recovery_codes_user").on(table.userId),
1135 uniqueIndex("recovery_codes_user_hash").on(table.userId, table.codeHash),
1136 ]
1137);
1138
1139export type UserTotp = typeof userTotp.$inferSelect;
1140export type UserRecoveryCode = typeof userRecoveryCodes.$inferSelect;
2df1f8cClaude1141
1142/**
1143 * WebAuthn passkeys (Block B5).
1144 *
1145 * Each row is one registered authenticator. The `credentialId` is the
1146 * globally-unique identifier the browser returns; `publicKey` is the
1147 * COSE-encoded public key we use to verify signatures. `counter` tracks
1148 * the authenticator's signature counter for replay-protection.
1149 *
1150 * `transports` is a JSON array (stored as text) of the
1151 * AuthenticatorTransport values ("usb" | "nfc" | "ble" | "internal" | "hybrid").
1152 */
1153export const userPasskeys = pgTable(
1154 "user_passkeys",
1155 {
1156 id: uuid("id").primaryKey().defaultRandom(),
1157 userId: uuid("user_id")
1158 .notNull()
1159 .references(() => users.id, { onDelete: "cascade" }),
1160 credentialId: text("credential_id").notNull().unique(),
1161 publicKey: text("public_key").notNull(), // base64url of COSE key
1162 counter: integer("counter").default(0).notNull(),
1163 transports: text("transports"), // JSON array string
1164 name: text("name").notNull().default("Passkey"),
1165 lastUsedAt: timestamp("last_used_at"),
1166 createdAt: timestamp("created_at").defaultNow().notNull(),
1167 },
1168 (table) => [index("passkeys_user").on(table.userId)]
1169);
1170
1171/**
1172 * Short-lived WebAuthn challenges. A row is written when we issue options
1173 * (registration or authentication) and deleted after the verify step or when
1174 * it expires (5 min). Keeping them in the DB lets us verify without sticky
1175 * sessions or client-side state.
1176 */
1177export const webauthnChallenges = pgTable(
1178 "webauthn_challenges",
1179 {
1180 id: uuid("id").primaryKey().defaultRandom(),
1181 userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
1182 // For passwordless login we don't know the user yet, so userId is nullable
1183 // and we bind the challenge to a short-lived cookie token instead.
1184 sessionKey: text("session_key").notNull().unique(),
1185 challenge: text("challenge").notNull(),
1186 kind: text("kind").notNull(), // "register" | "authenticate"
1187 expiresAt: timestamp("expires_at").notNull(),
1188 createdAt: timestamp("created_at").defaultNow().notNull(),
1189 },
1190 (table) => [index("webauthn_challenges_expires").on(table.expiresAt)]
1191);
1192
1193export type UserPasskey = typeof userPasskeys.$inferSelect;
1194export type WebauthnChallenge = typeof webauthnChallenges.$inferSelect;
bfdb5e7Claude1195
1196/**
1197 * OAuth 2.0 provider (Block B6).
1198 *
1199 * `oauthApps` is a third-party app registered by a developer. Each app has
1200 * a public `client_id`, a hashed `client_secret`, and one or more allowed
1201 * `redirect_uris` (newline-separated). The plaintext secret is shown to the
1202 * developer exactly once at creation and cannot be recovered; they can
1203 * rotate it instead.
1204 *
1205 * `oauthAuthorizations` is a short-lived authorization code issued after
1206 * the user consents at /oauth/authorize. Single-use: `usedAt` is set on
1207 * redemption so a replay after-the-fact fails.
1208 *
1209 * `oauthAccessTokens` is a long-lived bearer token plus an optional
1210 * refresh token. Both are stored as SHA-256 hashes; the plaintext values
1211 * are only returned to the client once in the /oauth/token response.
1212 */
1213export const oauthApps = pgTable(
1214 "oauth_apps",
1215 {
1216 id: uuid("id").primaryKey().defaultRandom(),
1217 ownerId: uuid("owner_id")
1218 .notNull()
1219 .references(() => users.id, { onDelete: "cascade" }),
1220 name: text("name").notNull(),
1221 clientId: text("client_id").notNull().unique(),
1222 clientSecretHash: text("client_secret_hash").notNull(),
1223 clientSecretPrefix: text("client_secret_prefix").notNull(), // first 8 chars for display
1224 /** Newline-separated list of allowed redirect URIs. */
1225 redirectUris: text("redirect_uris").notNull(),
1226 homepageUrl: text("homepage_url"),
1227 description: text("description"),
1228 /**
1229 * If `true`, the app must present its client_secret at /oauth/token.
1230 * Public SPA/mobile apps should set this to `false` and use PKCE.
1231 */
1232 confidential: boolean("confidential").default(true).notNull(),
1233 revokedAt: timestamp("revoked_at"),
1234 createdAt: timestamp("created_at").defaultNow().notNull(),
1235 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1236 },
1237 (table) => [index("oauth_apps_owner").on(table.ownerId)]
1238);
1239
1240export const oauthAuthorizations = pgTable(
1241 "oauth_authorizations",
1242 {
1243 id: uuid("id").primaryKey().defaultRandom(),
1244 appId: uuid("app_id")
1245 .notNull()
1246 .references(() => oauthApps.id, { onDelete: "cascade" }),
1247 userId: uuid("user_id")
1248 .notNull()
1249 .references(() => users.id, { onDelete: "cascade" }),
1250 codeHash: text("code_hash").notNull().unique(),
1251 redirectUri: text("redirect_uri").notNull(),
1252 scopes: text("scopes").notNull().default(""),
1253 codeChallenge: text("code_challenge"),
1254 codeChallengeMethod: text("code_challenge_method"), // "S256" | "plain"
1255 expiresAt: timestamp("expires_at").notNull(),
1256 usedAt: timestamp("used_at"),
1257 createdAt: timestamp("created_at").defaultNow().notNull(),
1258 },
1259 (table) => [index("oauth_authorizations_expires").on(table.expiresAt)]
1260);
1261
1262export const oauthAccessTokens = pgTable(
1263 "oauth_access_tokens",
1264 {
1265 id: uuid("id").primaryKey().defaultRandom(),
1266 appId: uuid("app_id")
1267 .notNull()
1268 .references(() => oauthApps.id, { onDelete: "cascade" }),
1269 userId: uuid("user_id")
1270 .notNull()
1271 .references(() => users.id, { onDelete: "cascade" }),
1272 accessTokenHash: text("access_token_hash").notNull().unique(),
1273 refreshTokenHash: text("refresh_token_hash").unique(),
1274 scopes: text("scopes").notNull().default(""),
1275 expiresAt: timestamp("expires_at").notNull(),
1276 refreshExpiresAt: timestamp("refresh_expires_at"),
1277 revokedAt: timestamp("revoked_at"),
1278 lastUsedAt: timestamp("last_used_at"),
1279 createdAt: timestamp("created_at").defaultNow().notNull(),
1280 },
1281 (table) => [
1282 index("oauth_access_tokens_user").on(table.userId),
1283 index("oauth_access_tokens_app").on(table.appId),
1284 index("oauth_access_tokens_expires").on(table.expiresAt),
1285 ]
1286);
1287
1288export type OauthApp = typeof oauthApps.$inferSelect;
1289export type OauthAuthorization = typeof oauthAuthorizations.$inferSelect;
1290export type OauthAccessToken = typeof oauthAccessTokens.$inferSelect;
eafe8c6Claude1291
1292/**
1293 * Actions-equivalent workflow runner (Block C1).
1294 *
1295 * `workflows` rows are the YAML files discovered at `.gluecron/workflows/*.yml`
1296 * on the repo's default branch. `parsed` is the normalised JSON form used by
1297 * the runner so we don't re-parse on every trigger.
1298 *
1299 * `workflow_runs` is one execution: one row per trigger event. Status
1300 * progression: queued → running → success|failure|cancelled. `conclusion`
1301 * stays null until `status` is terminal.
1302 *
1303 * `workflow_jobs` is a single job within a run — each has its own steps
1304 * array and concatenated logs. We keep logs inline for v1 (no streaming)
1305 * to avoid a fifth table; they're truncated at the runner.
1306 *
1307 * `workflow_artifacts` persist files a job uploaded. `content` is a bytea;
1308 * we'll move this to object storage once we hit size limits.
1309 */
1310export const workflows = pgTable(
1311 "workflows",
1312 {
1313 id: uuid("id").primaryKey().defaultRandom(),
1314 repositoryId: uuid("repository_id")
1315 .notNull()
1316 .references(() => repositories.id, { onDelete: "cascade" }),
1317 name: text("name").notNull(),
1318 path: text("path").notNull(), // e.g. ".gluecron/workflows/ci.yml"
1319 yaml: text("yaml").notNull(),
1320 parsed: text("parsed").notNull(), // JSON string
1321 onEvents: text("on_events").notNull().default("[]"), // JSON array of event names
1322 disabled: boolean("disabled").default(false).notNull(),
1323 createdAt: timestamp("created_at").defaultNow().notNull(),
1324 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1325 },
1326 (table) => [
1327 index("workflows_repo").on(table.repositoryId),
1328 uniqueIndex("workflows_repo_path").on(table.repositoryId, table.path),
1329 ]
1330);
1331
1332export const workflowRuns = pgTable(
1333 "workflow_runs",
1334 {
1335 id: uuid("id").primaryKey().defaultRandom(),
1336 workflowId: uuid("workflow_id")
1337 .notNull()
1338 .references(() => workflows.id, { onDelete: "cascade" }),
1339 repositoryId: uuid("repository_id")
1340 .notNull()
1341 .references(() => repositories.id, { onDelete: "cascade" }),
1342 runNumber: integer("run_number").notNull(),
1343 event: text("event").notNull(), // "push" | "pull_request" | "manual" | ...
1344 ref: text("ref"),
1345 commitSha: text("commit_sha"),
1346 triggeredBy: uuid("triggered_by").references(() => users.id, {
1347 onDelete: "set null",
1348 }),
1349 status: text("status").notNull().default("queued"), // queued|running|success|failure|cancelled
1350 conclusion: text("conclusion"),
1351 queuedAt: timestamp("queued_at").defaultNow().notNull(),
1352 startedAt: timestamp("started_at"),
1353 finishedAt: timestamp("finished_at"),
1354 createdAt: timestamp("created_at").defaultNow().notNull(),
1355 },
1356 (table) => [
1357 index("workflow_runs_repo").on(table.repositoryId),
1358 index("workflow_runs_status").on(table.status),
1359 index("workflow_runs_workflow").on(table.workflowId),
1360 ]
1361);
1362
1363export const workflowJobs = pgTable(
1364 "workflow_jobs",
1365 {
1366 id: uuid("id").primaryKey().defaultRandom(),
1367 runId: uuid("run_id")
1368 .notNull()
1369 .references(() => workflowRuns.id, { onDelete: "cascade" }),
1370 name: text("name").notNull(),
1371 jobOrder: integer("job_order").default(0).notNull(),
1372 runsOn: text("runs_on").notNull().default("default"),
1373 status: text("status").notNull().default("queued"),
1374 conclusion: text("conclusion"),
1375 exitCode: integer("exit_code"),
1376 steps: text("steps").notNull().default("[]"), // JSON array of step results
1377 logs: text("logs").notNull().default(""),
1378 startedAt: timestamp("started_at"),
1379 finishedAt: timestamp("finished_at"),
1380 createdAt: timestamp("created_at").defaultNow().notNull(),
1381 },
1382 (table) => [index("workflow_jobs_run").on(table.runId)]
1383);
1384
1385export const workflowArtifacts = pgTable(
1386 "workflow_artifacts",
1387 {
1388 id: uuid("id").primaryKey().defaultRandom(),
1389 runId: uuid("run_id")
1390 .notNull()
1391 .references(() => workflowRuns.id, { onDelete: "cascade" }),
1392 jobId: uuid("job_id").references(() => workflowJobs.id, {
1393 onDelete: "set null",
1394 }),
1395 name: text("name").notNull(),
1396 sizeBytes: integer("size_bytes").default(0).notNull(),
1397 contentType: text("content_type")
1398 .default("application/octet-stream")
1399 .notNull(),
1400 // bytea — drizzle doesn't have a built-in bytea type at the level we use
1401 // elsewhere; store as text (base64) for v1. Migration uses real bytea so
1402 // we can swap the column type later.
1403 content: text("content"),
1404 createdAt: timestamp("created_at").defaultNow().notNull(),
1405 },
1406 (table) => [index("workflow_artifacts_run").on(table.runId)]
1407);
1408
1409export type Workflow = typeof workflows.$inferSelect;
1410export type WorkflowRun = typeof workflowRuns.$inferSelect;
1411export type WorkflowJob = typeof workflowJobs.$inferSelect;
1412export type WorkflowArtifact = typeof workflowArtifacts.$inferSelect;
e2da5c6Claude1413
1414// ---------------------------------------------------------------------------
1415// Block C2 — Package registry (npm-compatible)
1416// ---------------------------------------------------------------------------
1417
1418export const packages = pgTable(
1419 "packages",
1420 {
1421 id: uuid("id").primaryKey().defaultRandom(),
1422 repositoryId: uuid("repository_id")
1423 .notNull()
1424 .references(() => repositories.id, { onDelete: "cascade" }),
1425 ecosystem: text("ecosystem").notNull().default("npm"), // "npm" | "container"
1426 scope: text("scope"), // "@acme" for npm; null for unscoped
1427 name: text("name").notNull(), // "my-lib" (without scope)
1428 description: text("description"),
1429 readme: text("readme"),
1430 homepage: text("homepage"),
1431 license: text("license"),
1432 visibility: text("visibility").notNull().default("public"), // "public" | "private"
1433 createdAt: timestamp("created_at").defaultNow().notNull(),
1434 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1435 },
1436 (table) => [
1437 index("packages_repo").on(table.repositoryId),
1438 uniqueIndex("packages_eco_scope_name").on(
1439 table.ecosystem,
1440 table.scope,
1441 table.name
1442 ),
1443 ]
1444);
1445
1446export const packageVersions = pgTable(
1447 "package_versions",
1448 {
1449 id: uuid("id").primaryKey().defaultRandom(),
1450 packageId: uuid("package_id")
1451 .notNull()
1452 .references(() => packages.id, { onDelete: "cascade" }),
1453 version: text("version").notNull(), // "1.2.3"
1454 shasum: text("shasum").notNull(), // sha1 (for npm compat) hex
1455 integrity: text("integrity"), // "sha512-..." base64
1456 sizeBytes: integer("size_bytes").default(0).notNull(),
1457 metadata: text("metadata").notNull().default("{}"), // package.json JSON
1458 tarball: text("tarball"), // base64-encoded; bytea in migration
1459 publishedBy: uuid("published_by").references(() => users.id, {
1460 onDelete: "set null",
1461 }),
1462 yanked: boolean("yanked").default(false).notNull(),
1463 yankedReason: text("yanked_reason"),
1464 publishedAt: timestamp("published_at").defaultNow().notNull(),
1465 },
1466 (table) => [
1467 index("package_versions_pkg").on(table.packageId),
1468 uniqueIndex("package_versions_pkg_version").on(table.packageId, table.version),
1469 ]
1470);
1471
1472export const packageTags = pgTable(
1473 "package_tags",
1474 {
1475 id: uuid("id").primaryKey().defaultRandom(),
1476 packageId: uuid("package_id")
1477 .notNull()
1478 .references(() => packages.id, { onDelete: "cascade" }),
1479 tag: text("tag").notNull(), // "latest" | "beta" | ...
1480 versionId: uuid("version_id")
1481 .notNull()
1482 .references(() => packageVersions.id, { onDelete: "cascade" }),
1483 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1484 },
1485 (table) => [
1486 uniqueIndex("package_tags_pkg_tag").on(table.packageId, table.tag),
1487 ]
1488);
1489
1490export type Package = typeof packages.$inferSelect;
1491export type PackageVersion = typeof packageVersions.$inferSelect;
1492export type PackageTag = typeof packageTags.$inferSelect;
1493
1494// ---------------------------------------------------------------------------
1495// Block C3 — Pages / static hosting
1496// ---------------------------------------------------------------------------
1497
1498export const pagesDeployments = pgTable(
1499 "pages_deployments",
1500 {
1501 id: uuid("id").primaryKey().defaultRandom(),
1502 repositoryId: uuid("repository_id")
1503 .notNull()
1504 .references(() => repositories.id, { onDelete: "cascade" }),
1505 ref: text("ref").notNull().default("refs/heads/gh-pages"),
1506 commitSha: text("commit_sha").notNull(),
1507 status: text("status").notNull().default("success"), // "success" | "failed"
1508 triggeredBy: uuid("triggered_by").references(() => users.id, {
1509 onDelete: "set null",
1510 }),
1511 createdAt: timestamp("created_at").defaultNow().notNull(),
1512 },
1513 (table) => [
1514 index("pages_deployments_repo").on(table.repositoryId),
1515 index("pages_deployments_created").on(table.createdAt),
1516 ]
1517);
1518
1519export const pagesSettings = pgTable("pages_settings", {
1520 repositoryId: uuid("repository_id")
1521 .primaryKey()
1522 .references(() => repositories.id, { onDelete: "cascade" }),
1523 enabled: boolean("enabled").default(true).notNull(),
1524 sourceBranch: text("source_branch").notNull().default("gh-pages"),
1525 sourceDir: text("source_dir").notNull().default("/"), // e.g. "/" or "/docs"
1526 customDomain: text("custom_domain"),
1527 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1528});
1529
1530export type PagesDeployment = typeof pagesDeployments.$inferSelect;
1531export type PagesSettings = typeof pagesSettings.$inferSelect;
1532
1533// ---------------------------------------------------------------------------
1534// Block C4 — Environments with protected approvals
1535// ---------------------------------------------------------------------------
1536
1537export const environments = pgTable(
1538 "environments",
1539 {
1540 id: uuid("id").primaryKey().defaultRandom(),
1541 repositoryId: uuid("repository_id")
1542 .notNull()
1543 .references(() => repositories.id, { onDelete: "cascade" }),
1544 name: text("name").notNull(), // "production" | "staging" | "preview"
1545 requireApproval: boolean("require_approval").default(false).notNull(),
1546 // JSON array of user IDs that can approve deploys.
1547 reviewers: text("reviewers").notNull().default("[]"),
1548 waitTimerMinutes: integer("wait_timer_minutes").default(0).notNull(),
1549 allowedBranches: text("allowed_branches").notNull().default("[]"), // JSON glob patterns
1550 createdAt: timestamp("created_at").defaultNow().notNull(),
1551 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1552 },
1553 (table) => [
1554 uniqueIndex("environments_repo_name").on(table.repositoryId, table.name),
1555 ]
1556);
1557
1558export const deploymentApprovals = pgTable(
1559 "deployment_approvals",
1560 {
1561 id: uuid("id").primaryKey().defaultRandom(),
1562 deploymentId: uuid("deployment_id")
1563 .notNull()
1564 .references(() => deployments.id, { onDelete: "cascade" }),
1565 userId: uuid("user_id")
1566 .notNull()
1567 .references(() => users.id, { onDelete: "cascade" }),
1568 decision: text("decision").notNull(), // "approved" | "rejected"
1569 comment: text("comment"),
1570 createdAt: timestamp("created_at").defaultNow().notNull(),
1571 },
1572 (table) => [
1573 index("deployment_approvals_deployment").on(table.deploymentId),
1574 ]
1575);
1576
1577export type Environment = typeof environments.$inferSelect;
1578export type DeploymentApproval = typeof deploymentApprovals.$inferSelect;
3cbe3d6Claude1579
1580// ---------------------------------------------------------------------------
1581// Block D — AI-native differentiation (migration 0012)
1582// ---------------------------------------------------------------------------
1583
1584// D6 — cached "explain this codebase" markdown keyed on commit sha.
1585export const codebaseExplanations = pgTable(
1586 "codebase_explanations",
1587 {
1588 id: uuid("id").primaryKey().defaultRandom(),
1589 repositoryId: uuid("repository_id")
1590 .notNull()
1591 .references(() => repositories.id, { onDelete: "cascade" }),
1592 commitSha: text("commit_sha").notNull(),
1593 summary: text("summary").notNull(),
1594 markdown: text("markdown").notNull(),
1595 model: text("model").notNull(),
1596 generatedAt: timestamp("generated_at").defaultNow().notNull(),
1597 },
1598 (table) => [
1599 uniqueIndex("codebase_explanations_repo_sha").on(
1600 table.repositoryId,
1601 table.commitSha
1602 ),
1603 ]
1604);
1605
1606// D2 — AI dependency bumper run history.
1607export const depUpdateRuns = pgTable(
1608 "dep_update_runs",
1609 {
1610 id: uuid("id").primaryKey().defaultRandom(),
1611 repositoryId: uuid("repository_id")
1612 .notNull()
1613 .references(() => repositories.id, { onDelete: "cascade" }),
1614 status: text("status").notNull().default("pending"), // pending|running|success|failed|no_updates
1615 ecosystem: text("ecosystem").notNull(), // npm|bun
1616 manifestPath: text("manifest_path").notNull(),
1617 attemptedBumps: text("attempted_bumps").notNull().default("[]"), // JSON
1618 appliedBumps: text("applied_bumps").notNull().default("[]"), // JSON
1619 branchName: text("branch_name"),
1620 prNumber: integer("pr_number"),
1621 errorMessage: text("error_message"),
1622 triggeredBy: uuid("triggered_by").references(() => users.id, {
1623 onDelete: "set null",
1624 }),
1625 createdAt: timestamp("created_at").defaultNow().notNull(),
1626 completedAt: timestamp("completed_at"),
1627 },
1628 (table) => [
1629 index("dep_update_runs_repo").on(table.repositoryId),
1630 index("dep_update_runs_created").on(table.createdAt),
1631 ]
1632);
1633
1634// D1 — code chunks for semantic search. Embedding stored as JSON-encoded
1635// number array in text to avoid requiring pgvector; cosine similarity is
1636// computed in JS. Upgrade path: ALTER COLUMN embedding TYPE vector(1024).
1637export const codeChunks = pgTable(
1638 "code_chunks",
1639 {
1640 id: uuid("id").primaryKey().defaultRandom(),
1641 repositoryId: uuid("repository_id")
1642 .notNull()
1643 .references(() => repositories.id, { onDelete: "cascade" }),
1644 commitSha: text("commit_sha").notNull(),
1645 path: text("path").notNull(),
1646 startLine: integer("start_line").notNull(),
1647 endLine: integer("end_line").notNull(),
1648 content: text("content").notNull(),
1649 embedding: text("embedding"), // JSON number[]
1650 embeddingModel: text("embedding_model"),
1651 createdAt: timestamp("created_at").defaultNow().notNull(),
1652 },
1653 (table) => [
1654 index("code_chunks_repo").on(table.repositoryId),
1655 index("code_chunks_repo_path").on(table.repositoryId, table.path),
1656 ]
1657);
1658
1659export type CodebaseExplanation = typeof codebaseExplanations.$inferSelect;
1660export type DepUpdateRun = typeof depUpdateRuns.$inferSelect;
1e162a8Claude1661
1662// ---------------------------------------------------------------------------
c645a86Claude1663// Block E2 — Discussions (migration 0013 + 0077)
1e162a8Claude1664// ---------------------------------------------------------------------------
1665
c645a86Claude1666/**
1667 * Per-repo discussion categories (migration 0077).
1668 * Seeded lazily on first discussion creation: General, Q&A, Announcements, Ideas.
1669 * is_answerable = true surfaces "Mark as answer" on threads in that category.
1670 */
1671export const discussionCategories = pgTable(
1672 "discussion_categories",
1673 {
1674 id: serial("id").primaryKey(),
1675 repositoryId: uuid("repository_id")
1676 .notNull()
1677 .references(() => repositories.id, { onDelete: "cascade" }),
1678 name: text("name").notNull(),
1679 emoji: text("emoji").notNull().default("💬"),
1680 description: text("description"),
1681 isAnswerable: boolean("is_answerable").notNull().default(false),
1682 },
1683 (table) => [index("discussion_categories_repo").on(table.repositoryId)]
1684);
1685
1686export type DiscussionCategory = typeof discussionCategories.$inferSelect;
1687
1e162a8Claude1688/**
1689 * Discussions — forum-style threaded conversations attached to a repo.
1690 * Similar to GitHub Discussions: categorised + pinnable + answerable.
1691 */
1692export const discussions = pgTable(
1693 "discussions",
1694 {
1695 id: uuid("id").primaryKey().defaultRandom(),
1696 number: serial("number"),
1697 repositoryId: uuid("repository_id")
1698 .notNull()
1699 .references(() => repositories.id, { onDelete: "cascade" }),
1700 authorId: uuid("author_id")
1701 .notNull()
1702 .references(() => users.id),
1703 // one of: "general" | "q-and-a" | "ideas" | "announcements" | "show-and-tell"
1704 category: text("category").notNull().default("general"),
1705 title: text("title").notNull(),
1706 body: text("body"),
1707 state: text("state").notNull().default("open"), // open, closed
1708 locked: boolean("locked").notNull().default(false),
1709 answerCommentId: uuid("answer_comment_id"),
1710 pinned: boolean("pinned").notNull().default(false),
1711 createdAt: timestamp("created_at").defaultNow().notNull(),
1712 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1713 },
1714 (table) => [
1715 index("discussions_repo").on(table.repositoryId),
1716 uniqueIndex("discussions_repo_number").on(
1717 table.repositoryId,
1718 table.number
1719 ),
1720 ]
1721);
1722
1723export const discussionComments = pgTable(
1724 "discussion_comments",
1725 {
1726 id: uuid("id").primaryKey().defaultRandom(),
1727 discussionId: uuid("discussion_id")
1728 .notNull()
1729 .references(() => discussions.id, { onDelete: "cascade" }),
1730 parentCommentId: uuid("parent_comment_id"),
1731 authorId: uuid("author_id")
1732 .notNull()
1733 .references(() => users.id),
1734 body: text("body").notNull(),
1735 isAnswer: boolean("is_answer").notNull().default(false),
1736 createdAt: timestamp("created_at").defaultNow().notNull(),
1737 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1738 },
1739 (table) => [
1740 index("discussion_comments_discussion").on(table.discussionId),
1741 ]
1742);
1743
1744export type Discussion = typeof discussions.$inferSelect;
1745export type DiscussionComment = typeof discussionComments.$inferSelect;
3cbe3d6Claude1746export type CodeChunk = typeof codeChunks.$inferSelect;
1e162a8Claude1747
1748// ---------------------------------------------------------------------------
1749// Block E4 — Gists (migration 0014)
1750// ---------------------------------------------------------------------------
1751//
1752// User-owned small snippets/files that behave like tiny repos. DB-backed
1753// for v1 (no bare git repo): each gist owns a collection of gist_files and
1754// every edit appends a gist_revisions row with a JSON snapshot.
1755
1756export const gists = pgTable(
1757 "gists",
1758 {
1759 id: uuid("id").primaryKey().defaultRandom(),
1760 ownerId: uuid("owner_id")
1761 .notNull()
1762 .references(() => users.id, { onDelete: "cascade" }),
1763 // 8-char hex slug used in pretty URLs (e.g. /gists/a1b2c3d4).
1764 slug: text("slug").notNull().unique(),
1765 title: text("title").notNull().default(""),
1766 description: text("description").notNull().default(""),
1767 isPublic: boolean("is_public").default(true).notNull(),
1768 createdAt: timestamp("created_at").defaultNow().notNull(),
1769 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1770 },
1771 (table) => [index("gists_owner").on(table.ownerId)]
1772);
1773
1774export const gistFiles = pgTable(
1775 "gist_files",
1776 {
1777 id: uuid("id").primaryKey().defaultRandom(),
1778 gistId: uuid("gist_id")
1779 .notNull()
1780 .references(() => gists.id, { onDelete: "cascade" }),
1781 filename: text("filename").notNull(),
1782 // Optional explicit language override; falls back to filename detection.
1783 language: text("language"),
1784 content: text("content").notNull().default(""),
1785 sizeBytes: integer("size_bytes").default(0).notNull(),
1786 },
1787 (table) => [
1788 index("gist_files_gist").on(table.gistId),
1789 uniqueIndex("gist_files_gist_filename").on(table.gistId, table.filename),
1790 ]
1791);
1792
1793export const gistRevisions = pgTable(
1794 "gist_revisions",
1795 {
1796 id: uuid("id").primaryKey().defaultRandom(),
1797 gistId: uuid("gist_id")
1798 .notNull()
1799 .references(() => gists.id, { onDelete: "cascade" }),
1800 revision: integer("revision").notNull(),
1801 // JSON-encoded {filename: content} map capturing the full snapshot at
1802 // this revision. Stored as text to avoid requiring jsonb.
1803 snapshot: text("snapshot").notNull().default("{}"),
1804 authorId: uuid("author_id")
1805 .notNull()
1806 .references(() => users.id, { onDelete: "cascade" }),
1807 message: text("message"),
1808 createdAt: timestamp("created_at").defaultNow().notNull(),
1809 },
1810 (table) => [
1811 index("gist_revisions_gist_rev").on(table.gistId, table.revision),
1812 ]
1813);
1814
1815export const gistStars = pgTable(
1816 "gist_stars",
1817 {
1818 id: uuid("id").primaryKey().defaultRandom(),
1819 gistId: uuid("gist_id")
1820 .notNull()
1821 .references(() => gists.id, { onDelete: "cascade" }),
1822 userId: uuid("user_id")
1823 .notNull()
1824 .references(() => users.id, { onDelete: "cascade" }),
1825 createdAt: timestamp("created_at").defaultNow().notNull(),
1826 },
1827 (table) => [uniqueIndex("gist_stars_gist_user").on(table.gistId, table.userId)]
1828);
1829
1830export type Gist = typeof gists.$inferSelect;
1831export type GistFile = typeof gistFiles.$inferSelect;
1832export type GistRevision = typeof gistRevisions.$inferSelect;
1833export type GistStar = typeof gistStars.$inferSelect;
1834
1835// ---------------------------------------------------------------------------
1836// Block E1 — Projects / kanban (migration 0015)
1837// ---------------------------------------------------------------------------
1838
1839export const projects = pgTable(
1840 "projects",
1841 {
1842 id: uuid("id").primaryKey().defaultRandom(),
1843 number: serial("number"),
1844 repositoryId: uuid("repository_id")
1845 .notNull()
1846 .references(() => repositories.id, { onDelete: "cascade" }),
1847 ownerId: uuid("owner_id")
1848 .notNull()
1849 .references(() => users.id),
1850 title: text("title").notNull(),
1851 description: text("description").notNull().default(""),
1852 state: text("state").notNull().default("open"), // open | closed
1853 createdAt: timestamp("created_at").defaultNow().notNull(),
1854 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1855 },
1856 (table) => [
1857 index("projects_repo").on(table.repositoryId),
1858 uniqueIndex("projects_repo_number").on(table.repositoryId, table.number),
1859 ]
1860);
1861
1862export const projectColumns = pgTable(
1863 "project_columns",
1864 {
1865 id: uuid("id").primaryKey().defaultRandom(),
1866 projectId: uuid("project_id")
1867 .notNull()
1868 .references(() => projects.id, { onDelete: "cascade" }),
1869 name: text("name").notNull(),
1870 position: integer("position").notNull().default(0),
1871 createdAt: timestamp("created_at").defaultNow().notNull(),
1872 },
1873 (table) => [index("project_columns_project").on(table.projectId)]
1874);
1875
1876export const projectItems = pgTable(
1877 "project_items",
1878 {
1879 id: uuid("id").primaryKey().defaultRandom(),
1880 projectId: uuid("project_id")
1881 .notNull()
1882 .references(() => projects.id, { onDelete: "cascade" }),
1883 columnId: uuid("column_id")
1884 .notNull()
1885 .references(() => projectColumns.id, { onDelete: "cascade" }),
1886 position: integer("position").notNull().default(0),
1887 // "note" | "issue" | "pr" — application-level FK on itemId by type
1888 itemType: text("item_type").notNull().default("note"),
1889 itemId: uuid("item_id"),
1890 title: text("title").notNull().default(""),
1891 note: text("note").notNull().default(""),
1892 createdAt: timestamp("created_at").defaultNow().notNull(),
1893 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1894 },
1895 (table) => [
1896 index("project_items_project").on(table.projectId),
1897 index("project_items_column").on(table.columnId, table.position),
1898 ]
1899);
1900
1901export type Project = typeof projects.$inferSelect;
1902export type ProjectColumn = typeof projectColumns.$inferSelect;
1903export type ProjectItem = typeof projectItems.$inferSelect;
1904
1905// ---------------------------------------------------------------------------
1906// Block E3 — Wikis (migration 0016)
1907// ---------------------------------------------------------------------------
1908// DB-backed for v1; git-backed mirror is a future upgrade.
1909
1910export const wikiPages = pgTable(
1911 "wiki_pages",
1912 {
1913 id: uuid("id").primaryKey().defaultRandom(),
1914 repositoryId: uuid("repository_id")
1915 .notNull()
1916 .references(() => repositories.id, { onDelete: "cascade" }),
1917 slug: text("slug").notNull(),
1918 title: text("title").notNull(),
1919 body: text("body").notNull().default(""),
1920 revision: integer("revision").notNull().default(1),
1921 createdAt: timestamp("created_at").defaultNow().notNull(),
1922 updatedAt: timestamp("updated_at").defaultNow().notNull(),
1923 updatedBy: uuid("updated_by").references(() => users.id),
1924 },
1925 (table) => [
1926 index("wiki_pages_repo").on(table.repositoryId),
1927 uniqueIndex("wiki_pages_repo_slug").on(table.repositoryId, table.slug),
1928 ]
1929);
1930
1931export const wikiRevisions = pgTable(
1932 "wiki_revisions",
1933 {
1934 id: uuid("id").primaryKey().defaultRandom(),
1935 pageId: uuid("page_id")
1936 .notNull()
1937 .references(() => wikiPages.id, { onDelete: "cascade" }),
1938 revision: integer("revision").notNull(),
1939 title: text("title").notNull(),
1940 body: text("body").notNull().default(""),
1941 message: text("message"),
1942 authorId: uuid("author_id")
1943 .notNull()
1944 .references(() => users.id),
1945 createdAt: timestamp("created_at").defaultNow().notNull(),
1946 },
1947 (table) => [index("wiki_revisions_page").on(table.pageId, table.revision)]
1948);
1949
1950export type WikiPage = typeof wikiPages.$inferSelect;
1951export type WikiRevision = typeof wikiRevisions.$inferSelect;
a79a9edClaude1952
1953// ---------------------------------------------------------------------------
1954// Block E5 — Merge queues (migration 0017)
1955// ---------------------------------------------------------------------------
1956
1957export const mergeQueueEntries = pgTable(
1958 "merge_queue_entries",
1959 {
1960 id: uuid("id").primaryKey().defaultRandom(),
1961 repositoryId: uuid("repository_id")
1962 .notNull()
1963 .references(() => repositories.id, { onDelete: "cascade" }),
1964 pullRequestId: uuid("pull_request_id")
1965 .notNull()
1966 .references(() => pullRequests.id, { onDelete: "cascade" }),
1967 baseBranch: text("base_branch").notNull(),
1968 // queued | running | merged | failed | dequeued
1969 state: text("state").notNull().default("queued"),
1970 position: integer("position").notNull().default(0),
1971 enqueuedBy: uuid("enqueued_by").references(() => users.id),
1972 enqueuedAt: timestamp("enqueued_at").defaultNow().notNull(),
1973 startedAt: timestamp("started_at"),
1974 finishedAt: timestamp("finished_at"),
1975 errorMessage: text("error_message"),
1976 },
1977 (table) => [
1978 index("merge_queue_repo_branch").on(
1979 table.repositoryId,
1980 table.baseBranch,
1981 table.state
1982 ),
1983 ]
1984);
1985
1986export type MergeQueueEntry = typeof mergeQueueEntries.$inferSelect;
1987
1988// ---------------------------------------------------------------------------
1989// Block E6 — Required status checks matrix (migration 0018)
1990// ---------------------------------------------------------------------------
1991
1992export const branchRequiredChecks = pgTable(
1993 "branch_required_checks",
1994 {
1995 id: uuid("id").primaryKey().defaultRandom(),
1996 branchProtectionId: uuid("branch_protection_id")
1997 .notNull()
1998 .references(() => branchProtection.id, { onDelete: "cascade" }),
1999 checkName: text("check_name").notNull(),
2000 createdAt: timestamp("created_at").defaultNow().notNull(),
2001 },
2002 (table) => [
2003 index("branch_required_checks_rule").on(table.branchProtectionId),
2004 uniqueIndex("branch_required_checks_unique").on(
2005 table.branchProtectionId,
2006 table.checkName
2007 ),
2008 ]
2009);
2010
2011export type BranchRequiredCheck = typeof branchRequiredChecks.$inferSelect;
2012
2013// ---------------------------------------------------------------------------
2014// Block E7 — Protected tags (migration 0019)
2015// ---------------------------------------------------------------------------
2016
2017export const protectedTags = pgTable(
2018 "protected_tags",
2019 {
2020 id: uuid("id").primaryKey().defaultRandom(),
2021 repositoryId: uuid("repository_id")
2022 .notNull()
2023 .references(() => repositories.id, { onDelete: "cascade" }),
2024 pattern: text("pattern").notNull(),
2025 createdAt: timestamp("created_at").defaultNow().notNull(),
2026 createdBy: uuid("created_by").references(() => users.id),
2027 },
2028 (table) => [
2029 index("protected_tags_repo").on(table.repositoryId),
2030 uniqueIndex("protected_tags_repo_pattern").on(
2031 table.repositoryId,
2032 table.pattern
2033 ),
2034 ]
2035);
2036
2037export type ProtectedTag = typeof protectedTags.$inferSelect;
8f50ed0Claude2038
2039// ---------------------------------------------------------------------------
2040// Block F — Observability + admin (migration 0020)
2041// ---------------------------------------------------------------------------
2042
2043// F1 — Traffic analytics per repo
2044export const repoTrafficEvents = pgTable(
2045 "repo_traffic_events",
2046 {
2047 id: uuid("id").primaryKey().defaultRandom(),
2048 repositoryId: uuid("repository_id")
2049 .notNull()
2050 .references(() => repositories.id, { onDelete: "cascade" }),
2051 kind: text("kind").notNull(), // view | clone | api | ui
2052 path: text("path"),
2053 userId: uuid("user_id").references(() => users.id, { onDelete: "set null" }),
2054 ipHash: text("ip_hash"),
2055 userAgent: text("user_agent"),
2056 referer: text("referer"),
2057 createdAt: timestamp("created_at").defaultNow().notNull(),
2058 },
2059 (table) => [
2060 index("repo_traffic_events_repo_time").on(
2061 table.repositoryId,
2062 table.createdAt
2063 ),
2064 index("repo_traffic_events_kind").on(
2065 table.repositoryId,
2066 table.kind,
2067 table.createdAt
2068 ),
2069 ]
2070);
2071
2072export type RepoTrafficEvent = typeof repoTrafficEvents.$inferSelect;
2073
2074// F3 — Admin panel (site admins + toggleable flags)
2075export const systemFlags = pgTable("system_flags", {
2076 key: text("key").primaryKey(),
2077 value: text("value").notNull().default(""),
2078 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2079 updatedBy: uuid("updated_by").references(() => users.id),
2080});
2081
2082export type SystemFlag = typeof systemFlags.$inferSelect;
2083
2084export const siteAdmins = pgTable("site_admins", {
2085 userId: uuid("user_id")
2086 .primaryKey()
2087 .references(() => users.id, { onDelete: "cascade" }),
2088 grantedAt: timestamp("granted_at").defaultNow().notNull(),
2089 grantedBy: uuid("granted_by").references(() => users.id),
2090});
2091
2092export type SiteAdmin = typeof siteAdmins.$inferSelect;
2093
509c376Claude2094// /admin/integrations — DB-stored platform integration secrets (migration 0055).
2095// Loaded into process.env at boot so the existing synchronous config getters
2096// keep working transparently. Never exposes DATABASE_URL / SELF_HOST_REPO /
2097// PORT / GIT_REPOS_PATH — those stay env-only.
2098export const systemConfig = pgTable("system_config", {
2099 key: text("key").primaryKey(),
2100 value: text("value").notNull().default(""),
2101 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2102 updatedByUserId: uuid("updated_by_user_id").references(() => users.id, {
2103 onDelete: "set null",
2104 }),
2105});
2106
2107export type SystemConfig = typeof systemConfig.$inferSelect;
2108
8f50ed0Claude2109// F4 — Billing + quotas
2110export const billingPlans = pgTable("billing_plans", {
2111 id: uuid("id").primaryKey().defaultRandom(),
2112 slug: text("slug").notNull().unique(),
2113 name: text("name").notNull(),
2114 priceCents: integer("price_cents").notNull().default(0),
2115 repoLimit: integer("repo_limit").notNull().default(10),
2116 storageMbLimit: integer("storage_mb_limit").notNull().default(1024),
2117 aiTokensMonthly: integer("ai_tokens_monthly").notNull().default(100000),
2118 bandwidthGbMonthly: integer("bandwidth_gb_monthly").notNull().default(10),
2119 privateRepos: boolean("private_repos").notNull().default(false),
2120 createdAt: timestamp("created_at").defaultNow().notNull(),
2121});
2122
2123export type BillingPlan = typeof billingPlans.$inferSelect;
2124
2125export const userQuotas = pgTable("user_quotas", {
2126 userId: uuid("user_id")
2127 .primaryKey()
2128 .references(() => users.id, { onDelete: "cascade" }),
2129 planSlug: text("plan_slug").notNull().default("free"),
2130 storageMbUsed: integer("storage_mb_used").notNull().default(0),
2131 aiTokensUsedThisMonth: integer("ai_tokens_used_this_month")
2132 .notNull()
2133 .default(0),
2134 bandwidthGbUsedThisMonth: integer("bandwidth_gb_used_this_month")
2135 .notNull()
2136 .default(0),
2137 cycleStart: timestamp("cycle_start").defaultNow().notNull(),
2138 updatedAt: timestamp("updated_at").defaultNow().notNull(),
619109aClaude2139 // Stripe linkage (migration 0038). Null until the user first upgrades.
2140 stripeCustomerId: text("stripe_customer_id"),
2141 stripeSubscriptionId: text("stripe_subscription_id"),
2142 stripeSubscriptionStatus: text("stripe_subscription_status"),
2143 currentPeriodEnd: timestamp("current_period_end"),
8f50ed0Claude2144});
2145
2146export type UserQuota = typeof userQuotas.$inferSelect;
06139e6Claude2147
2148// Block H — App marketplace + bot identities (GitHub Apps equivalent)
2149
2150export const apps = pgTable(
2151 "apps",
2152 {
2153 id: uuid("id").primaryKey().defaultRandom(),
2154 slug: text("slug").notNull().unique(),
2155 name: text("name").notNull(),
2156 description: text("description").notNull().default(""),
2157 iconUrl: text("icon_url"),
2158 homepageUrl: text("homepage_url"),
2159 webhookUrl: text("webhook_url"),
2160 webhookSecret: text("webhook_secret"),
2161 creatorId: uuid("creator_id")
2162 .notNull()
2163 .references(() => users.id, { onDelete: "cascade" }),
2164 permissions: text("permissions").notNull().default("[]"), // JSON array
2165 defaultEvents: text("default_events").notNull().default("[]"), // JSON array
2166 isPublic: boolean("is_public").notNull().default(true),
2167 createdAt: timestamp("created_at").defaultNow().notNull(),
2168 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2169 },
2170 (table) => [index("apps_public_slug").on(table.isPublic, table.slug)]
2171);
2172
2173export type App = typeof apps.$inferSelect;
2174
2175export const appInstallations = pgTable(
2176 "app_installations",
2177 {
2178 id: uuid("id").primaryKey().defaultRandom(),
2179 appId: uuid("app_id")
2180 .notNull()
2181 .references(() => apps.id, { onDelete: "cascade" }),
2182 installedBy: uuid("installed_by")
2183 .notNull()
2184 .references(() => users.id, { onDelete: "cascade" }),
2185 targetType: text("target_type").notNull(), // user | org | repository
2186 targetId: uuid("target_id").notNull(),
2187 grantedPermissions: text("granted_permissions").notNull().default("[]"),
2188 suspendedAt: timestamp("suspended_at"),
2189 createdAt: timestamp("created_at").defaultNow().notNull(),
2190 uninstalledAt: timestamp("uninstalled_at"),
2191 },
2192 (table) => [
2193 index("app_installations_app").on(table.appId),
2194 index("app_installations_target").on(table.targetType, table.targetId),
2195 ]
2196);
2197
2198export type AppInstallation = typeof appInstallations.$inferSelect;
2199
2200export const appBots = pgTable("app_bots", {
2201 id: uuid("id").primaryKey().defaultRandom(),
2202 appId: uuid("app_id")
2203 .notNull()
2204 .unique()
2205 .references(() => apps.id, { onDelete: "cascade" }),
2206 username: text("username").notNull().unique(),
2207 displayName: text("display_name").notNull(),
2208 avatarUrl: text("avatar_url"),
2209 createdAt: timestamp("created_at").defaultNow().notNull(),
2210});
2211
2212export type AppBot = typeof appBots.$inferSelect;
2213
2214export const appInstallTokens = pgTable(
2215 "app_install_tokens",
2216 {
2217 id: uuid("id").primaryKey().defaultRandom(),
2218 installationId: uuid("installation_id")
2219 .notNull()
2220 .references(() => appInstallations.id, { onDelete: "cascade" }),
2221 tokenHash: text("token_hash").notNull().unique(),
2222 expiresAt: timestamp("expires_at").notNull(),
2223 createdAt: timestamp("created_at").defaultNow().notNull(),
2224 revokedAt: timestamp("revoked_at"),
2225 },
2226 (table) => [index("app_install_tokens_hash").on(table.tokenHash)]
2227);
2228
2229export type AppInstallToken = typeof appInstallTokens.$inferSelect;
2230
2231export const appEvents = pgTable(
2232 "app_events",
2233 {
2234 id: uuid("id").primaryKey().defaultRandom(),
2235 appId: uuid("app_id")
2236 .notNull()
2237 .references(() => apps.id, { onDelete: "cascade" }),
2238 installationId: uuid("installation_id"),
2239 kind: text("kind").notNull(), // installed | uninstalled | delivery_ok | delivery_fail
2240 payload: text("payload"),
2241 responseStatus: integer("response_status"),
2242 createdAt: timestamp("created_at").defaultNow().notNull(),
2243 },
2244 (table) => [index("app_events_app_time").on(table.appId, table.createdAt)]
2245);
2246
2247export type AppEvent = typeof appEvents.$inferSelect;
71cd5ecClaude2248
2249// ---------- Block I3 — Repository transfer history ----------
2250
2251export const repoTransfers = pgTable(
2252 "repo_transfers",
2253 {
2254 id: uuid("id").primaryKey().defaultRandom(),
2255 repositoryId: uuid("repository_id")
2256 .notNull()
2257 .references(() => repositories.id, { onDelete: "cascade" }),
2258 fromOwnerId: uuid("from_owner_id").notNull(),
2259 fromOrgId: uuid("from_org_id"),
2260 toOwnerId: uuid("to_owner_id").notNull(),
2261 toOrgId: uuid("to_org_id"),
2262 initiatedBy: uuid("initiated_by")
2263 .notNull()
2264 .references(() => users.id, { onDelete: "cascade" }),
2265 createdAt: timestamp("created_at").defaultNow().notNull(),
2266 },
2267 (table) => [
2268 index("repo_transfers_repo").on(table.repositoryId, table.createdAt),
2269 ]
2270);
2271
2272export type RepoTransfer = typeof repoTransfers.$inferSelect;
08420cdClaude2273
2274// ---------- Block I6 — Sponsors ----------
2275
2276export const sponsorshipTiers = pgTable(
2277 "sponsorship_tiers",
2278 {
2279 id: uuid("id").primaryKey().defaultRandom(),
2280 maintainerId: uuid("maintainer_id")
2281 .notNull()
2282 .references(() => users.id, { onDelete: "cascade" }),
2283 name: text("name").notNull(),
2284 description: text("description").default("").notNull(),
2285 monthlyCents: integer("monthly_cents").notNull(),
2286 oneTimeAllowed: boolean("one_time_allowed").default(true).notNull(),
2287 isActive: boolean("is_active").default(true).notNull(),
2288 createdAt: timestamp("created_at").defaultNow().notNull(),
2289 },
2290 (table) => [
2291 index("sponsor_tiers_maintainer").on(table.maintainerId, table.isActive),
2292 ]
2293);
2294
2295export type SponsorshipTier = typeof sponsorshipTiers.$inferSelect;
2296
2297export const sponsorships = pgTable(
2298 "sponsorships",
2299 {
2300 id: uuid("id").primaryKey().defaultRandom(),
2301 sponsorId: uuid("sponsor_id")
2302 .notNull()
2303 .references(() => users.id, { onDelete: "cascade" }),
2304 maintainerId: uuid("maintainer_id")
2305 .notNull()
2306 .references(() => users.id, { onDelete: "cascade" }),
2307 tierId: uuid("tier_id"),
2308 amountCents: integer("amount_cents").notNull(),
2309 kind: text("kind").notNull(), // one_time | monthly
2310 note: text("note"),
2311 isPublic: boolean("is_public").default(true).notNull(),
2312 externalRef: text("external_ref"),
2313 cancelledAt: timestamp("cancelled_at"),
2314 createdAt: timestamp("created_at").defaultNow().notNull(),
2315 },
2316 (table) => [
2317 index("sponsorships_maintainer").on(
2318 table.maintainerId,
2319 table.createdAt
2320 ),
2321 index("sponsorships_sponsor").on(table.sponsorId, table.createdAt),
2322 ]
2323);
2324
2325export type Sponsorship = typeof sponsorships.$inferSelect;
4c8f666Claude2326
2327// Block I8 — Code symbol index for xref navigation.
2328export const codeSymbols = pgTable(
2329 "code_symbols",
2330 {
2331 id: uuid("id").primaryKey().defaultRandom(),
2332 repositoryId: uuid("repository_id")
2333 .notNull()
2334 .references(() => repositories.id, { onDelete: "cascade" }),
2335 commitSha: text("commit_sha").notNull(),
2336 name: text("name").notNull(),
2337 kind: text("kind").notNull(), // function | class | interface | type | const | variable
2338 path: text("path").notNull(),
2339 line: integer("line").notNull(),
2340 signature: text("signature"),
2341 createdAt: timestamp("created_at").defaultNow().notNull(),
2342 },
2343 (table) => [
2344 index("code_symbols_repo_name_idx").on(table.repositoryId, table.name),
2345 index("code_symbols_repo_path_idx").on(table.repositoryId, table.path),
2346 ]
2347);
2348
2349export type CodeSymbol = typeof codeSymbols.$inferSelect;
4a0dea1Claude2350
2351// Block I9 — Repository mirroring. One row per mirrored repo + an
2352// append-only log of sync attempts.
2353export const repoMirrors = pgTable("repo_mirrors", {
2354 id: uuid("id").primaryKey().defaultRandom(),
2355 repositoryId: uuid("repository_id")
2356 .notNull()
2357 .unique()
2358 .references(() => repositories.id, { onDelete: "cascade" }),
2359 upstreamUrl: text("upstream_url").notNull(),
2360 intervalMinutes: integer("interval_minutes").default(1440).notNull(),
2361 lastSyncedAt: timestamp("last_synced_at"),
2362 lastStatus: text("last_status"), // "ok" | "error"
2363 lastError: text("last_error"),
2364 isEnabled: boolean("is_enabled").default(true).notNull(),
2365 createdAt: timestamp("created_at").defaultNow().notNull(),
2366 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2367});
2368
2369export type RepoMirror = typeof repoMirrors.$inferSelect;
2370
2371export const repoMirrorRuns = pgTable(
2372 "repo_mirror_runs",
2373 {
2374 id: uuid("id").primaryKey().defaultRandom(),
2375 mirrorId: uuid("mirror_id")
2376 .notNull()
2377 .references(() => repoMirrors.id, { onDelete: "cascade" }),
2378 startedAt: timestamp("started_at").defaultNow().notNull(),
2379 finishedAt: timestamp("finished_at"),
2380 status: text("status").default("running").notNull(),
2381 message: text("message"),
2382 exitCode: integer("exit_code"),
2383 },
2384 (table) => [
2385 index("repo_mirror_runs_mirror_id_idx").on(table.mirrorId, table.startedAt),
2386 ]
2387);
2388
2389export type RepoMirrorRun = typeof repoMirrorRuns.$inferSelect;
edf7c36Claude2390
2391// ----------------------------------------------------------------------------
2392// Block I10 — Enterprise SSO (OIDC)
2393// ----------------------------------------------------------------------------
2394
2395/** Site-wide SSO provider. Singleton row with id = 'default'. */
2396export const ssoConfig = pgTable("sso_config", {
2397 id: text("id").primaryKey(),
2398 enabled: boolean("enabled").default(false).notNull(),
2399 providerName: text("provider_name").default("SSO").notNull(),
2400 issuer: text("issuer"),
2401 authorizationEndpoint: text("authorization_endpoint"),
2402 tokenEndpoint: text("token_endpoint"),
2403 userinfoEndpoint: text("userinfo_endpoint"),
2404 clientId: text("client_id"),
2405 clientSecret: text("client_secret"),
2406 scopes: text("scopes").default("openid profile email").notNull(),
2407 allowedEmailDomains: text("allowed_email_domains"),
2408 autoCreateUsers: boolean("auto_create_users").default(true).notNull(),
2409 createdAt: timestamp("created_at").defaultNow().notNull(),
2410 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2411});
2412
2413export type SsoConfig = typeof ssoConfig.$inferSelect;
2414
2415/** Maps a local user to an IdP `sub` claim. */
2416export const ssoUserLinks = pgTable(
2417 "sso_user_links",
2418 {
2419 id: uuid("id").primaryKey().defaultRandom(),
2420 userId: uuid("user_id")
2421 .notNull()
2422 .references(() => users.id, { onDelete: "cascade" }),
2423 subject: text("subject").notNull().unique(),
2424 emailAtLink: text("email_at_link").notNull(),
2425 linkedAt: timestamp("linked_at").defaultNow().notNull(),
2426 },
2427 (table) => [index("sso_user_links_user_id_idx").on(table.userId)]
2428);
2429
2430export type SsoUserLink = typeof ssoUserLinks.$inferSelect;
8098672Claude2431
2432// ----------------------------------------------------------------------------
2433// Block J1 — Dependency graph
2434// ----------------------------------------------------------------------------
2435
2436/**
2437 * Last known set of dependencies parsed from manifest files. Each reindex
2438 * replaces the prior rows for that repo. One row per (ecosystem, name,
2439 * manifest_path) — same name in multiple manifests is kept.
2440 */
2441export const repoDependencies = pgTable(
2442 "repo_dependencies",
2443 {
2444 id: uuid("id").primaryKey().defaultRandom(),
2445 repositoryId: uuid("repository_id")
2446 .notNull()
2447 .references(() => repositories.id, { onDelete: "cascade" }),
2448 ecosystem: text("ecosystem").notNull(),
2449 name: text("name").notNull(),
2450 versionSpec: text("version_spec"),
2451 manifestPath: text("manifest_path").notNull(),
2452 isDev: boolean("is_dev").default(false).notNull(),
2453 commitSha: text("commit_sha").notNull(),
2454 indexedAt: timestamp("indexed_at").defaultNow().notNull(),
2455 },
2456 (table) => [
2457 index("repo_dependencies_repo_id_idx").on(
2458 table.repositoryId,
2459 table.ecosystem
2460 ),
2461 index("repo_dependencies_name_idx").on(table.name),
2462 ]
2463);
2464
2465export type RepoDependency = typeof repoDependencies.$inferSelect;
f60ccdeClaude2466
2467// ----------------------------------------------------------------------------
2468// Block J2 — Security advisories + alerts
2469// ----------------------------------------------------------------------------
2470
2471/** CVE-style package advisories. Populated via seed + admin import. */
2472export const securityAdvisories = pgTable(
2473 "security_advisories",
2474 {
2475 id: uuid("id").primaryKey().defaultRandom(),
2476 ghsaId: text("ghsa_id").unique(),
2477 cveId: text("cve_id"),
2478 summary: text("summary").notNull(),
2479 severity: text("severity").default("moderate").notNull(),
2480 ecosystem: text("ecosystem").notNull(),
2481 packageName: text("package_name").notNull(),
2482 affectedRange: text("affected_range").notNull(),
2483 fixedVersion: text("fixed_version"),
2484 referenceUrl: text("reference_url"),
2485 publishedAt: timestamp("published_at").defaultNow().notNull(),
2486 },
2487 (table) => [
2488 index("security_advisories_pkg_idx").on(
2489 table.ecosystem,
2490 table.packageName
2491 ),
2492 ]
2493);
2494
2495export type SecurityAdvisory = typeof securityAdvisories.$inferSelect;
2496
2497/** Per-repo match state. One row per (repo, advisory, manifest_path). */
2498export const repoAdvisoryAlerts = pgTable(
2499 "repo_advisory_alerts",
2500 {
2501 id: uuid("id").primaryKey().defaultRandom(),
2502 repositoryId: uuid("repository_id")
2503 .notNull()
2504 .references(() => repositories.id, { onDelete: "cascade" }),
2505 advisoryId: uuid("advisory_id")
2506 .notNull()
2507 .references(() => securityAdvisories.id, { onDelete: "cascade" }),
2508 dependencyName: text("dependency_name").notNull(),
2509 dependencyVersion: text("dependency_version"),
2510 manifestPath: text("manifest_path").notNull(),
2511 status: text("status").default("open").notNull(),
2512 dismissedReason: text("dismissed_reason"),
2513 createdAt: timestamp("created_at").defaultNow().notNull(),
2514 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2515 },
2516 (table) => [
2517 index("repo_advisory_alerts_status_idx").on(
2518 table.repositoryId,
2519 table.status
2520 ),
2521 ]
2522);
2523
2524export type RepoAdvisoryAlert = typeof repoAdvisoryAlerts.$inferSelect;
3951454Claude2525
2526// ----------------------------------------------------------------------------
2527// Block J3 — Commit signature verification (GPG + SSH)
2528// ----------------------------------------------------------------------------
2529
2530/** Per-user GPG/SSH public keys for commit signing. */
2531export const signingKeys = pgTable(
2532 "signing_keys",
2533 {
2534 id: uuid("id").primaryKey().defaultRandom(),
2535 userId: uuid("user_id")
2536 .notNull()
2537 .references(() => users.id, { onDelete: "cascade" }),
2538 keyType: text("key_type").notNull(), // 'gpg' | 'ssh'
2539 title: text("title").notNull(),
2540 fingerprint: text("fingerprint").notNull(),
2541 publicKey: text("public_key").notNull(),
2542 email: text("email"),
2543 expiresAt: timestamp("expires_at"),
2544 lastUsedAt: timestamp("last_used_at"),
2545 createdAt: timestamp("created_at").defaultNow().notNull(),
2546 },
2547 (table) => [
2548 uniqueIndex("signing_keys_fp_unique").on(table.keyType, table.fingerprint),
2549 index("signing_keys_user_idx").on(table.userId),
2550 ]
2551);
2552
2553export type SigningKey = typeof signingKeys.$inferSelect;
2554
2555/**
2556 * Cached verification result for a (repo, commit) pair. Repopulated on demand;
2557 * rows are invalidated implicitly by CASCADE when either side is removed.
2558 */
2559export const commitVerifications = pgTable(
2560 "commit_verifications",
2561 {
2562 id: uuid("id").primaryKey().defaultRandom(),
2563 repositoryId: uuid("repository_id")
2564 .notNull()
2565 .references(() => repositories.id, { onDelete: "cascade" }),
2566 commitSha: text("commit_sha").notNull(),
2567 verified: boolean("verified").default(false).notNull(),
2568 reason: text("reason").notNull(),
2569 signatureType: text("signature_type"),
2570 signerKeyId: uuid("signer_key_id").references(() => signingKeys.id, {
2571 onDelete: "set null",
2572 }),
2573 signerUserId: uuid("signer_user_id").references(() => users.id, {
2574 onDelete: "set null",
2575 }),
2576 signerFingerprint: text("signer_fingerprint"),
2577 verifiedAt: timestamp("verified_at").defaultNow().notNull(),
2578 },
2579 (table) => [
2580 uniqueIndex("commit_verifications_sha_unique").on(
2581 table.repositoryId,
2582 table.commitSha
2583 ),
2584 ]
2585);
2586
2587export type CommitVerification = typeof commitVerifications.$inferSelect;
7aa8b99Claude2588
2589// ----------------------------------------------------------------------------
2590// Block J4 — User following
2591// ----------------------------------------------------------------------------
2592
2593/**
2594 * Directed user→user follow edges. Primary key is the composite
2595 * (follower_id, following_id) at the SQL level; drizzle sees it as a
2596 * regular table with a unique index plus a secondary index on the
2597 * reverse-lookup column.
2598 */
2599export const userFollows = pgTable(
2600 "user_follows",
2601 {
2602 followerId: uuid("follower_id")
2603 .notNull()
2604 .references(() => users.id, { onDelete: "cascade" }),
2605 followingId: uuid("following_id")
2606 .notNull()
2607 .references(() => users.id, { onDelete: "cascade" }),
2608 createdAt: timestamp("created_at").defaultNow().notNull(),
2609 },
2610 (table) => [
2611 uniqueIndex("user_follows_pair_unique").on(
2612 table.followerId,
2613 table.followingId
2614 ),
2615 index("user_follows_following_idx").on(table.followingId),
2616 ]
2617);
2618
2619export type UserFollow = typeof userFollows.$inferSelect;
9ff7128Claude2620
2621// ----------------------------------------------------------------------------
2622// Block J6 — Repository rulesets
2623// ----------------------------------------------------------------------------
2624
2625/**
2626 * A ruleset groups N rules under a named policy at enforcement level active /
2627 * evaluate / disabled. Unique per (repo, name) so a repo can carry multiple
2628 * overlapping rulesets (e.g. "release branches" vs "everywhere").
2629 */
2630export const repoRulesets = pgTable(
2631 "repo_rulesets",
2632 {
2633 id: uuid("id").primaryKey().defaultRandom(),
2634 repositoryId: uuid("repository_id")
2635 .notNull()
2636 .references(() => repositories.id, { onDelete: "cascade" }),
2637 name: text("name").notNull(),
2638 enforcement: text("enforcement").default("active").notNull(),
2639 createdBy: uuid("created_by").references(() => users.id, {
2640 onDelete: "set null",
2641 }),
2642 createdAt: timestamp("created_at").defaultNow().notNull(),
2643 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2644 },
2645 (table) => [
2646 index("repo_rulesets_repo_idx").on(table.repositoryId),
2647 uniqueIndex("repo_rulesets_repo_name_unique").on(
2648 table.repositoryId,
2649 table.name
2650 ),
2651 ]
2652);
2653
2654export type RepoRuleset = typeof repoRulesets.$inferSelect;
2655
2656/** Individual rule — type tag plus JSON params. */
2657export const rulesetRules = pgTable(
2658 "ruleset_rules",
2659 {
2660 id: uuid("id").primaryKey().defaultRandom(),
2661 rulesetId: uuid("ruleset_id")
2662 .notNull()
2663 .references(() => repoRulesets.id, { onDelete: "cascade" }),
2664 ruleType: text("rule_type").notNull(),
2665 params: text("params").default("{}").notNull(),
2666 createdAt: timestamp("created_at").defaultNow().notNull(),
2667 },
2668 (table) => [index("ruleset_rules_set_idx").on(table.rulesetId)]
2669);
2670
2671export type RulesetRule = typeof rulesetRules.$inferSelect;
0cdfd89Claude2672
2673// ---------------------------------------------------------------------------
2674// Block J8 — Commit statuses.
2675// ---------------------------------------------------------------------------
2676
2677/**
2678 * External CI / automation posts per-commit (sha, context) statuses. Upsert
2679 * semantics keyed on (repository, commit_sha, context). State vocabulary:
2680 * pending | success | failure | error. Combined rollup logic lives in
2681 * `src/lib/commit-statuses.ts`.
2682 */
2683export const commitStatuses = pgTable(
2684 "commit_statuses",
2685 {
2686 id: uuid("id").primaryKey().defaultRandom(),
2687 repositoryId: uuid("repository_id")
2688 .notNull()
2689 .references(() => repositories.id, { onDelete: "cascade" }),
2690 commitSha: text("commit_sha").notNull(),
2691 state: text("state").notNull(),
2692 context: text("context").default("default").notNull(),
2693 description: text("description"),
2694 targetUrl: text("target_url"),
2695 creatorId: uuid("creator_id").references(() => users.id, {
2696 onDelete: "set null",
2697 }),
2698 createdAt: timestamp("created_at").defaultNow().notNull(),
2699 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2700 },
2701 (table) => [
2702 uniqueIndex("commit_statuses_repo_sha_context_unique").on(
2703 table.repositoryId,
2704 table.commitSha,
2705 table.context
2706 ),
2707 index("commit_statuses_repo_sha_idx").on(
2708 table.repositoryId,
2709 table.commitSha
2710 ),
2711 ]
2712);
2713
2714export type CommitStatus = typeof commitStatuses.$inferSelect;
23d1a81Claude2715
2716// ---------------------------------------------------------------------------
2717// Collaborators — per-repo role grants (read / write / admin).
2718// ---------------------------------------------------------------------------
2719
2720/**
2721 * A user granted access to a repository beyond ownership. Roles are
2722 * hierarchical — 'admin' implies write, write implies read. `invitedBy` tracks
2723 * who added them (nullable once that user is deleted); `acceptedAt` is null
2724 * until the invitee explicitly accepts. Unique per (repo, user) so a given
2725 * user has at most one role per repo.
2726 */
2727export const repoCollaborators = pgTable(
2728 "repo_collaborators",
2729 {
2730 id: uuid("id").primaryKey().defaultRandom(),
2731 repositoryId: uuid("repository_id")
2732 .notNull()
2733 .references(() => repositories.id, { onDelete: "cascade" }),
2734 userId: uuid("user_id")
2735 .notNull()
2736 .references(() => users.id, { onDelete: "cascade" }),
2737 role: text("role", { enum: ["read", "write", "admin"] })
2738 .notNull()
2739 .default("read"),
2740 invitedBy: uuid("invited_by").references(() => users.id, {
2741 onDelete: "set null",
2742 }),
2743 invitedAt: timestamp("invited_at").defaultNow().notNull(),
2744 acceptedAt: timestamp("accepted_at"),
febd4f0Claude2745 // sha256(plaintext) of the outstanding invite token. Set when the owner
2746 // sends an invite, cleared when the invitee accepts. NULL on older rows
2747 // that were auto-accepted before the email flow existed.
2748 inviteTokenHash: text("invite_token_hash"),
23d1a81Claude2749 },
2750 (table) => [
2751 uniqueIndex("repo_collaborators_repo_user_uq").on(
2752 table.repositoryId,
2753 table.userId
2754 ),
2755 index("repo_collaborators_repo_idx").on(table.repositoryId),
2756 index("repo_collaborators_user_idx").on(table.userId),
2757 ]
2758);
2759
2760export type RepoCollaborator = typeof repoCollaborators.$inferSelect;
abfa9adClaude2761
2762// ---------------------------------------------------------------------------
2763// Workflow engine v2 — Sprint 1 additions (drizzle/0037_workflow_engine_v2.sql)
2764//
2765// Strictly additive to Block C1. The original workflow tables defined above
2766// (workflows / workflow_runs / workflow_jobs / workflow_artifacts) are locked
2767// and must not be altered in-place; the four tables below extend the runner
2768// with secrets, workflow_dispatch inputs, a content-addressable cache, and a
2769// warm-runner worker pool.
2770// ---------------------------------------------------------------------------
2771
2772/**
2773 * Per-repo encrypted secrets exposed to workflow jobs as env vars.
2774 *
2775 * `encrypted_value` is base64 of `iv || authTag || ciphertext` produced by
2776 * AES-256-GCM — the crypto lives in `src/lib/workflow-crypto.ts` (Agent 2);
2777 * the DB only stores opaque ciphertext. `name` is validated against
2778 * `[A-Z_][A-Z0-9_]*` at the write-site, not the DB. Unique per (repo, name).
2779 */
2780export const workflowSecrets = pgTable(
2781 "workflow_secrets",
2782 {
2783 id: uuid("id").primaryKey().defaultRandom(),
2784 repositoryId: uuid("repository_id")
2785 .notNull()
2786 .references(() => repositories.id, { onDelete: "cascade" }),
2787 name: text("name").notNull(),
2788 encryptedValue: text("encrypted_value").notNull(),
2789 createdBy: uuid("created_by").references(() => users.id, {
2790 onDelete: "set null",
2791 }),
2792 createdAt: timestamp("created_at").defaultNow().notNull(),
2793 updatedAt: timestamp("updated_at").defaultNow().notNull(),
2794 },
2795 (table) => [
2796 uniqueIndex("workflow_secrets_repo_name_uq").on(
2797 table.repositoryId,
2798 table.name
2799 ),
2800 index("workflow_secrets_repo_idx").on(table.repositoryId),
2801 ]
2802);
2803
2804export type WorkflowSecret = typeof workflowSecrets.$inferSelect;
2805export type NewWorkflowSecret = typeof workflowSecrets.$inferInsert;
2806
2807/**
2808 * Parameter schema for the `workflow_dispatch` trigger. One row per input
2809 * declared in the workflow YAML. `type` is constrained to the four values
2810 * GitHub Actions supports; `options` is only meaningful for type='choice'
2811 * (a JSON array of allowed strings). `default_value` and `description` are
2812 * optional. Unique per (workflow, name) so two inputs on the same workflow
2813 * can't share an identifier.
2814 */
2815export const workflowDispatchInputs = pgTable(
2816 "workflow_dispatch_inputs",
2817 {
2818 id: uuid("id").primaryKey().defaultRandom(),
2819 workflowId: uuid("workflow_id")
2820 .notNull()
2821 .references(() => workflows.id, { onDelete: "cascade" }),
2822 name: text("name").notNull(),
2823 type: text("type", {
2824 enum: ["string", "boolean", "choice", "number"],
2825 }).notNull(),
2826 required: boolean("required").default(false).notNull(),
2827 defaultValue: text("default_value"),
2828 // JSON array of strings; only populated when type = 'choice'.
2829 options: jsonb("options"),
2830 description: text("description"),
2831 },
2832 (table) => [
2833 uniqueIndex("workflow_dispatch_inputs_wf_name_uq").on(
2834 table.workflowId,
2835 table.name
2836 ),
2837 ]
2838);
2839
2840export type WorkflowDispatchInput =
2841 typeof workflowDispatchInputs.$inferSelect;
2842export type NewWorkflowDispatchInput =
2843 typeof workflowDispatchInputs.$inferInsert;
2844
2845/**
2846 * Content-addressable cache backing the `gluecron/cache@v1` action. Rows are
2847 * keyed by user-chosen `cache_key` within a `scope` — 'repo' for repo-wide
2848 * entries, 'branch' or 'tag' when isolation is desired (with `scope_ref`
2849 * holding the branch/tag name). `content_hash` is the sha256 of `content`
2850 * so jobs can short-circuit redundant writes. `content` is real bytea; the
2851 * 100MB payload cap is enforced at the write-site, not by the DB. LRU
2852 * eviction reads (`repository_id`, `last_accessed_at`).
2853 */
2854export const workflowRunCache = pgTable(
2855 "workflow_run_cache",
2856 {
2857 id: uuid("id").primaryKey().defaultRandom(),
2858 repositoryId: uuid("repository_id")
2859 .notNull()
2860 .references(() => repositories.id, { onDelete: "cascade" }),
2861 cacheKey: text("cache_key").notNull(),
2862 // 'repo' | 'branch' | 'tag' — constraint enforced at the write-site.
2863 scope: text("scope").default("repo").notNull(),
2864 scopeRef: text("scope_ref"),
2865 contentHash: text("content_hash").notNull(),
2866 content: bytea("content").notNull(),
2867 sizeBytes: bigint("size_bytes", { mode: "number" }).notNull(),
2868 createdAt: timestamp("created_at").defaultNow().notNull(),
2869 lastAccessedAt: timestamp("last_accessed_at").defaultNow().notNull(),
2870 },
2871 (table) => [
2872 uniqueIndex("workflow_run_cache_repo_key_scope_uq").on(
2873 table.repositoryId,
2874 table.cacheKey,
2875 table.scope,
2876 table.scopeRef
2877 ),
2878 index("workflow_run_cache_repo_lru_idx").on(
2879 table.repositoryId,
2880 table.lastAccessedAt
2881 ),
2882 ]
2883);
2884
2885export type WorkflowRunCache = typeof workflowRunCache.$inferSelect;
2886export type NewWorkflowRunCache = typeof workflowRunCache.$inferInsert;
2887
2888/**
2889 * Warm-runner worker registry. The scheduler reads idle rows to dispatch
2890 * queued jobs without paying cold-start cost; workers heartbeat here so
2891 * stale entries (>N seconds since `last_heartbeat_at`) can be reaped.
2892 * `worker_id` is a stable string (hostname:pid or similar) and is globally
2893 * unique so a restarted worker naturally replaces its predecessor.
2894 * `current_run_id` is set when status='busy' and cleared on completion.
2895 */
2896export const workflowRunnerPool = pgTable(
2897 "workflow_runner_pool",
2898 {
2899 id: uuid("id").primaryKey().defaultRandom(),
2900 workerId: text("worker_id").notNull().unique(),
2901 status: text("status", {
2902 enum: ["idle", "busy", "draining", "dead"],
2903 }).notNull(),
2904 currentRunId: uuid("current_run_id").references(() => workflowRuns.id, {
2905 onDelete: "set null",
2906 }),
2907 warmedAt: timestamp("warmed_at").defaultNow().notNull(),
2908 lastHeartbeatAt: timestamp("last_heartbeat_at").defaultNow().notNull(),
2909 capacity: integer("capacity").default(1).notNull(),
2910 },
2911 (table) => [index("workflow_runner_pool_status_idx").on(table.status)]
2912);
2913
2914export type WorkflowRunnerPoolEntry =
2915 typeof workflowRunnerPool.$inferSelect;
2916export type NewWorkflowRunnerPoolEntry =
2917 typeof workflowRunnerPool.$inferInsert;
e6bad81Claude2918
2919
2920/**
2921 * Repair Flywheel — every auto-repair attempt is recorded here so future
2922 * failures with the same signature can short-circuit straight to the cached
2923 * patch (Tier 0). After ~5000 entries the cache dominates and AI calls drop.
2924 * Migration: 0039_repair_flywheel.sql
2925 */
2926export const repairFlywheel = pgTable(
2927 "repair_flywheel",
2928 {
2929 id: uuid("id").primaryKey().defaultRandom(),
2930 repositoryId: uuid("repository_id").references(() => repositories.id, {
2931 onDelete: "cascade",
2932 }),
2933 // Fingerprint of the normalised failure text (variables/paths stripped).
2934 failureSignature: text("failure_signature").notNull(),
2935 // Original failure text (capped at write-site, ~4KB).
2936 failureText: text("failure_text").notNull(),
2937 // Mechanical classification or NULL if AI/human-driven.
2938 failureClassification: text("failure_classification"),
2939 // 'cached' | 'mechanical' | 'ai-sonnet' | 'human'
2940 repairTier: text("repair_tier").notNull(),
2941 patchSummary: text("patch_summary").notNull(),
2942 filesChanged: jsonb("files_changed").notNull().default([]),
2943 commitSha: text("commit_sha"),
2944 // 'pending' | 'success' | 'failed' | 'reverted'
2945 outcome: text("outcome").notNull().default("pending"),
2946 appliedAt: timestamp("applied_at").defaultNow().notNull(),
2947 outcomeAt: timestamp("outcome_at"),
2948 parentPatternId: uuid("parent_pattern_id"),
2949 cacheHitCount: integer("cache_hit_count").default(0).notNull(),
2950 isPublicPattern: boolean("is_public_pattern").default(false).notNull(),
2951 createdAt: timestamp("created_at").defaultNow().notNull(),
2952 },
2953 (table) => [
2954 index("repair_flywheel_signature_idx").on(table.failureSignature),
2955 index("repair_flywheel_repo_idx").on(table.repositoryId),
2956 index("repair_flywheel_outcome_idx").on(table.outcome),
2957 index("repair_flywheel_classification_idx").on(table.failureClassification),
2958 index("repair_flywheel_lookup_idx").on(
2959 table.repositoryId,
2960 table.failureSignature,
2961 table.outcome
2962 ),
2963 ]
2964);
2965
2966export type RepairFlywheelEntry = typeof repairFlywheel.$inferSelect;
2967export type NewRepairFlywheelEntry = typeof repairFlywheel.$inferInsert;
2968
534f04aClaude2969/**
2970 * Block M3 — AI pre-merge risk score cache.
2971 *
2972 * One row per (pull_request_id, commit_sha). The score is computed by a
2973 * transparent formula over `signals`; the LLM only writes `ai_summary`.
2974 * Pinning by head SHA means a new push invalidates the cache implicitly
2975 * (the new SHA won't have a row yet → cache miss → recompute).
2976 *
2977 * Migration: 0044_pr_risk_scores.sql
2978 */
2979export const prRiskScores = pgTable(
2980 "pr_risk_scores",
2981 {
2982 id: uuid("id").primaryKey().defaultRandom(),
2983 pullRequestId: uuid("pull_request_id")
2984 .notNull()
2985 .references(() => pullRequests.id, { onDelete: "cascade" }),
2986 commitSha: text("commit_sha").notNull(),
2987 // 0..10 integer score.
2988 score: integer("score").notNull(),
2989 // 'low' | 'medium' | 'high' | 'critical'
2990 band: text("band").notNull(),
2991 // Raw signal map — see PrRiskSignals in src/lib/pr-risk.ts.
2992 signals: jsonb("signals").notNull(),
2993 aiSummary: text("ai_summary"),
2994 generatedAt: timestamp("generated_at", { withTimezone: true })
2995 .defaultNow()
2996 .notNull(),
2997 },
2998 (table) => [
2999 uniqueIndex("pr_risk_scores_pr_sha_uq").on(
3000 table.pullRequestId,
3001 table.commitSha
3002 ),
3003 index("pr_risk_scores_pr_idx").on(table.pullRequestId),
3004 ]
3005);
3006
3007export type PrRiskScoreRow = typeof prRiskScores.$inferSelect;
3008export type NewPrRiskScoreRow = typeof prRiskScores.$inferInsert;
3009
c63b860Claude3010/**
3011 * Block P1 — Password reset tokens.
3012 *
3013 * One row per outstanding reset request. `tokenHash` is a SHA-256 of the
3014 * plaintext token mailed to the user; the plaintext never persists.
3015 * `usedAt` is flipped on consume so a replayed link cannot rotate the
3016 * password twice. `expiresAt` is enforced at consume time (1-hour TTL).
3017 *
3018 * Migration: 0047_password_reset_tokens.sql
3019 */
3020export const passwordResetTokens = pgTable(
3021 "password_reset_tokens",
3022 {
3023 id: uuid("id").primaryKey().defaultRandom(),
3024 userId: uuid("user_id")
3025 .notNull()
3026 .references(() => users.id, { onDelete: "cascade" }),
3027 tokenHash: text("token_hash").notNull().unique(),
3028 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3029 usedAt: timestamp("used_at", { withTimezone: true }),
3030 requestIp: text("request_ip"),
3031 createdAt: timestamp("created_at", { withTimezone: true })
3032 .defaultNow()
3033 .notNull(),
3034 },
3035 (table) => [
3036 index("idx_password_reset_tokens_user").on(table.userId),
3037 index("idx_password_reset_tokens_expires").on(table.expiresAt),
3038 ]
3039);
3040
3041export type PasswordResetToken = typeof passwordResetTokens.$inferSelect;
3042export type NewPasswordResetToken = typeof passwordResetTokens.$inferInsert;
3043
3044/**
3045 * Block P2 — email verification tokens.
3046 *
3047 * SHA-256 hashed at rest. `email` captured at issuance so a verification
3048 * link still resolves the right address even after a profile-email change.
3049 * Migration: drizzle/0048_email_verification.sql
3050 */
3051export const emailVerificationTokens = pgTable(
3052 "email_verification_tokens",
3053 {
3054 id: uuid("id").primaryKey().defaultRandom(),
3055 userId: uuid("user_id")
3056 .notNull()
3057 .references(() => users.id, { onDelete: "cascade" }),
3058 email: text("email").notNull(),
3059 tokenHash: text("token_hash").notNull().unique(),
3060 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3061 usedAt: timestamp("used_at", { withTimezone: true }),
3062 createdAt: timestamp("created_at", { withTimezone: true })
3063 .defaultNow()
3064 .notNull(),
3065 },
3066 (table) => [index("idx_email_verify_tokens_user").on(table.userId)]
3067);
3068
3069export type EmailVerificationToken = typeof emailVerificationTokens.$inferSelect;
3070export type NewEmailVerificationToken = typeof emailVerificationTokens.$inferInsert;
3071
cd4f63bTest User3072/**
3073 * Block Q2 — Magic-link sign-in tokens.
3074 *
3075 * Structurally identical to passwordResetTokens / emailVerificationTokens:
3076 * a short plaintext token is mailed to the user, only its sha256 hash is
3077 * persisted, the token is single-use (usedAt) and time-limited (expiresAt).
3078 *
3079 * Difference: `userId` is NULLABLE. When a user enters an email that does
3080 * NOT yet have an account, we still mint a row — consume will create the
3081 * account on click (autoCreate=true), using the click itself as proof the
3082 * recipient owns the address. See `src/lib/magic-link.ts`.
3083 *
3084 * Migration: drizzle/0051_magic_link_tokens.sql
3085 */
3086export const magicLinkTokens = pgTable(
3087 "magic_link_tokens",
3088 {
3089 id: uuid("id").primaryKey().defaultRandom(),
3090 email: text("email").notNull(),
3091 userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }),
3092 tokenHash: text("token_hash").notNull().unique(),
3093 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3094 usedAt: timestamp("used_at", { withTimezone: true }),
3095 requestIp: text("request_ip"),
3096 createdAt: timestamp("created_at", { withTimezone: true })
3097 .defaultNow()
3098 .notNull(),
3099 },
3100 (table) => [
3101 index("idx_magic_link_tokens_email").on(table.email),
3102 index("idx_magic_link_tokens_user").on(table.userId),
3103 index("idx_magic_link_tokens_expires").on(table.expiresAt),
3104 ]
3105);
3106
3107export type MagicLinkToken = typeof magicLinkTokens.$inferSelect;
3108export type NewMagicLinkToken = typeof magicLinkTokens.$inferInsert;
3109
b1be050CC LABS App3110// ---------------------------------------------------------------------------
3111// BLOCK S4 — Synthetic monitor history.
3112//
3113// Every autopilot tick runs `runSyntheticChecks()` (see
3114// `src/lib/synthetic-monitor.ts`) and inserts one row per check here.
3115// The /admin/status page reads the most-recent row per check_name and
3116// renders the red/green dashboard. On a green->red transition we fire
3117// MONITOR_ALERT_WEBHOOK_URL so the owner sees the site is on fire.
3118// ---------------------------------------------------------------------------
3119export const syntheticChecks = pgTable(
3120 "synthetic_checks",
3121 {
3122 id: uuid("id").primaryKey().defaultRandom(),
3123 checkName: text("check_name").notNull(),
3124 status: text("status").notNull(), // "green" | "red" | "yellow"
3125 statusCode: integer("status_code"),
3126 durationMs: integer("duration_ms").notNull(),
3127 error: text("error"),
3128 checkedAt: timestamp("checked_at", { withTimezone: true })
3129 .defaultNow()
3130 .notNull(),
3131 },
3132 (table) => [
3133 index("idx_synthetic_checks_checked_at").on(table.checkedAt),
3134 index("idx_synthetic_checks_name_checked_at").on(
3135 table.checkName,
3136 table.checkedAt
3137 ),
3138 ]
3139);
3140
3141export type SyntheticCheckRow = typeof syntheticChecks.$inferSelect;
3142export type NewSyntheticCheckRow = typeof syntheticChecks.$inferInsert;
3143
a686079Claude3144// ---------------------------------------------------------------------------
3145// 0057 — Continuous semantic index (per-push embeddings).
e75eddcClaude3146// One row per (repository_id, file_path). `indexChangedFiles()` upserts on
3147// every push, `searchSemantic()` ranks by cosine distance via pgvector.
a686079Claude3148// ---------------------------------------------------------------------------
3149export const codeEmbeddings = pgTable(
3150 "code_embeddings",
3151 {
3152 id: uuid("id").primaryKey().defaultRandom(),
3153 repositoryId: uuid("repository_id")
3154 .notNull()
3155 .references(() => repositories.id, { onDelete: "cascade" }),
3156 filePath: text("file_path").notNull(),
3157 blobSha: text("blob_sha").notNull(),
3158 commitSha: text("commit_sha").notNull(),
3159 contentSnippet: text("content_snippet").notNull().default(""),
3160 embedding: vector("embedding", { dimensions: 1024 }),
3161 embeddingModel: text("embedding_model"),
3162 updatedAt: timestamp("updated_at", { withTimezone: true })
3163 .defaultNow()
3164 .notNull(),
3165 },
3166 (table) => [
3167 uniqueIndex("code_embeddings_repo_path_uniq").on(
3168 table.repositoryId,
3169 table.filePath
3170 ),
3171 index("code_embeddings_repo_idx").on(table.repositoryId),
3172 ]
3173);
3174
3175export type CodeEmbedding = typeof codeEmbeddings.$inferSelect;
3176export type NewCodeEmbedding = typeof codeEmbeddings.$inferInsert;
3177
e75eddcClaude3178// ---------------------------------------------------------------------------
3179// 0058 — Agent multiplayer v1: per-agent namespacing + leases + budget caps.
3180// See src/lib/agent-multiplayer.ts for helpers and 0058_agent_multiplayer.sql
3181// for the canonical column docs. UNIQUE partial index on (target_type,
3182// target_id) WHERE status='active' is enforced in SQL (drizzle exposes it
3183// as a regular index here).
3184// ---------------------------------------------------------------------------
3185export const agentSessions = pgTable(
3186 "agent_sessions",
3187 {
3188 id: uuid("id").primaryKey().defaultRandom(),
3189 name: text("name").notNull(),
3190 ownerUserId: uuid("owner_user_id")
3191 .notNull()
3192 .references(() => users.id, { onDelete: "cascade" }),
3193 repositoryId: uuid("repository_id").references(() => repositories.id, {
3194 onDelete: "cascade",
3195 }),
3196 tokenHash: text("token_hash").notNull().unique(),
3197 branchNamespace: text("branch_namespace").notNull(),
3198 budgetCentsPerDay: integer("budget_cents_per_day").default(500).notNull(),
3199 spentCentsToday: integer("spent_cents_today").default(0).notNull(),
3200 lastActiveAt: timestamp("last_active_at", { withTimezone: true }),
3201 createdAt: timestamp("created_at", { withTimezone: true })
3202 .defaultNow()
3203 .notNull(),
3204 },
3205 (table) => [
3206 uniqueIndex("agent_sessions_owner_name").on(table.ownerUserId, table.name),
3207 index("agent_sessions_owner").on(table.ownerUserId),
3208 index("agent_sessions_repo").on(table.repositoryId),
3209 ]
3210);
3211
3212export const agentLeases = pgTable(
3213 "agent_leases",
3214 {
3215 id: uuid("id").primaryKey().defaultRandom(),
3216 agentSessionId: uuid("agent_session_id")
3217 .notNull()
3218 .references(() => agentSessions.id, { onDelete: "cascade" }),
3219 targetType: text("target_type").notNull(),
3220 targetId: text("target_id").notNull(),
3221 acquiredAt: timestamp("acquired_at", { withTimezone: true })
3222 .defaultNow()
3223 .notNull(),
3224 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3225 status: text("status").default("active").notNull(),
3226 createdAt: timestamp("created_at", { withTimezone: true })
3227 .defaultNow()
3228 .notNull(),
3229 },
3230 (table) => [
3231 index("agent_leases_active_target").on(table.targetType, table.targetId),
3232 index("agent_leases_agent").on(table.agentSessionId, table.status),
3233 index("agent_leases_expires").on(table.expiresAt),
3234 ]
3235);
3236
3237export type AgentSession = typeof agentSessions.$inferSelect;
3238export type NewAgentSession = typeof agentSessions.$inferInsert;
3239export type AgentLease = typeof agentLeases.$inferSelect;
3240export type NewAgentLease = typeof agentLeases.$inferInsert;
3241
38d31d3Claude3242// ---------------------------------------------------------------------------
3c03977Claude3243// 0060 — AI repo rubber-duck chat. See src/lib/repo-chat.ts.
38d31d3Claude3244// ---------------------------------------------------------------------------
3245export const repoChats = pgTable(
3246 "repo_chats",
3247 {
3248 id: uuid("id").primaryKey().defaultRandom(),
3249 repositoryId: uuid("repository_id")
3250 .notNull()
3251 .references(() => repositories.id, { onDelete: "cascade" }),
3252 ownerUserId: uuid("owner_user_id")
3253 .notNull()
3254 .references(() => users.id, { onDelete: "cascade" }),
3255 title: text("title"),
3256 createdAt: timestamp("created_at", { withTimezone: true })
3257 .defaultNow()
3258 .notNull(),
3259 updatedAt: timestamp("updated_at", { withTimezone: true })
3260 .defaultNow()
3261 .notNull(),
3262 },
3263 (table) => [
3264 index("repo_chats_owner_updated").on(table.ownerUserId, table.updatedAt),
3265 index("repo_chats_repo").on(table.repositoryId),
3266 ]
3267);
3268
3269export const repoChatMessages = pgTable(
3270 "repo_chat_messages",
3271 {
3272 id: uuid("id").primaryKey().defaultRandom(),
3273 chatId: uuid("chat_id")
3274 .notNull()
3275 .references(() => repoChats.id, { onDelete: "cascade" }),
3276 role: text("role").notNull(),
3277 content: text("content").notNull(),
3278 citations: jsonb("citations").$type<Array<{ file_path: string; blob_sha: string }>>().notNull().default([]),
3279 tokenCost: integer("token_cost").notNull().default(0),
3280 createdAt: timestamp("created_at", { withTimezone: true })
3281 .defaultNow()
3282 .notNull(),
3283 },
3284 (table) => [
3285 index("repo_chat_messages_chat_created").on(table.chatId, table.createdAt),
3286 ]
3287);
3288
3289export type RepoChat = typeof repoChats.$inferSelect;
3290export type NewRepoChat = typeof repoChats.$inferInsert;
3291export type RepoChatMessage = typeof repoChatMessages.$inferSelect;
3292export type NewRepoChatMessage = typeof repoChatMessages.$inferInsert;
3293
ee7e577Claude3294// ---------------------------------------------------------------------------
3295// 0071 — Personal cross-repo chat. Same shape as repoChats/repoChatMessages
3296// but user-scoped, not repo-scoped. The retrieval layer
3297// (src/lib/personal-semantic.ts) runs searchSemantic across the union of
3298// repos the owner has access to, then attaches a repo_name annotation to
3299// each citation. Gated on users.personalSemanticIndexEnabled.
3300// ---------------------------------------------------------------------------
3301export const personalChats = pgTable(
3302 "personal_chats",
3303 {
3304 id: uuid("id").primaryKey().defaultRandom(),
3305 ownerUserId: uuid("owner_user_id")
3306 .notNull()
3307 .references(() => users.id, { onDelete: "cascade" }),
3308 title: text("title"),
3309 createdAt: timestamp("created_at", { withTimezone: true })
3310 .defaultNow()
3311 .notNull(),
3312 updatedAt: timestamp("updated_at", { withTimezone: true })
3313 .defaultNow()
3314 .notNull(),
3315 },
3316 (table) => [
3317 index("personal_chats_owner_updated").on(
3318 table.ownerUserId,
3319 table.updatedAt
3320 ),
3321 ]
3322);
3323
3324export const personalChatMessages = pgTable(
3325 "personal_chat_messages",
3326 {
3327 id: uuid("id").primaryKey().defaultRandom(),
3328 chatId: uuid("chat_id")
3329 .notNull()
3330 .references(() => personalChats.id, { onDelete: "cascade" }),
3331 role: text("role").notNull(),
3332 content: text("content").notNull(),
3333 citations: jsonb("citations")
3334 .$type<
3335 Array<{ file_path: string; blob_sha: string; repo_name: string }>
3336 >()
3337 .notNull()
3338 .default([]),
3339 tokenCost: integer("token_cost").notNull().default(0),
3340 createdAt: timestamp("created_at", { withTimezone: true })
3341 .defaultNow()
3342 .notNull(),
3343 },
3344 (table) => [
3345 index("personal_chat_messages_chat_created").on(
3346 table.chatId,
3347 table.createdAt
3348 ),
3349 ]
3350);
3351
3352export type PersonalChat = typeof personalChats.$inferSelect;
3353export type NewPersonalChat = typeof personalChats.$inferInsert;
3354export type PersonalChatMessage = typeof personalChatMessages.$inferSelect;
3355export type NewPersonalChatMessage = typeof personalChatMessages.$inferInsert;
3356
3c03977Claude3357// ---------------------------------------------------------------------------
3358// 0061 — Live co-editing on PRs. See src/lib/pr-live.ts.
3359// ---------------------------------------------------------------------------
3360export const prLiveSessions = pgTable(
3361 "pr_live_sessions",
3362 {
3363 id: uuid("id").primaryKey().defaultRandom(),
3364 prId: uuid("pr_id")
3365 .notNull()
3366 .references(() => pullRequests.id, { onDelete: "cascade" }),
3367 userId: uuid("user_id").references(() => users.id, {
3368 onDelete: "cascade",
3369 }),
3370 agentSessionId: uuid("agent_session_id").references(
3371 () => agentSessions.id,
3372 { onDelete: "cascade" }
3373 ),
3374 cursorPosition: jsonb("cursor_position"),
3375 color: text("color").notNull(),
3376 joinedAt: timestamp("joined_at", { withTimezone: true })
3377 .defaultNow()
3378 .notNull(),
3379 lastSeenAt: timestamp("last_seen_at", { withTimezone: true })
3380 .defaultNow()
3381 .notNull(),
3382 status: text("status").default("active").notNull(),
3383 },
3384 (table) => [
3385 index("pr_live_sessions_active_pr").on(table.prId, table.lastSeenAt),
3386 index("pr_live_sessions_status_seen").on(table.status, table.lastSeenAt),
3387 ]
3388);
3389
3390export type PrLiveSession = typeof prLiveSessions.$inferSelect;
3391export type NewPrLiveSession = typeof prLiveSessions.$inferInsert;
3392
4bbacbeClaude3393// ---------------------------------------------------------------------------
3394// ---------------------------------------------------------------------------
23d0abfClaude3395// Migration 0062 — per-branch preview URLs. See src/lib/branch-previews.ts.
3396// ---------------------------------------------------------------------------
4bbacbeClaude3397export const branchPreviews = pgTable(
3398 "branch_previews",
3399 {
3400 id: uuid("id").primaryKey().defaultRandom(),
3401 repositoryId: uuid("repository_id")
3402 .notNull()
3403 .references(() => repositories.id, { onDelete: "cascade" }),
3404 branchName: text("branch_name").notNull(),
3405 commitSha: text("commit_sha").notNull(),
3406 previewUrl: text("preview_url").notNull(),
3407 status: text("status").default("building").notNull(),
3408 buildStartedAt: timestamp("build_started_at", { withTimezone: true })
3409 .defaultNow()
3410 .notNull(),
3411 buildCompletedAt: timestamp("build_completed_at", { withTimezone: true }),
3412 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3413 errorMessage: text("error_message"),
3414 createdAt: timestamp("created_at", { withTimezone: true })
3415 .defaultNow()
3416 .notNull(),
3417 },
3418 (table) => [
3419 uniqueIndex("branch_previews_repo_branch").on(
3420 table.repositoryId,
3421 table.branchName
3422 ),
3423 index("branch_previews_repo_status").on(table.repositoryId, table.status),
3424 ]
3425);
3426
3427export type BranchPreview = typeof branchPreviews.$inferSelect;
3428export type NewBranchPreview = typeof branchPreviews.$inferInsert;
3429
23d0abfClaude3430// ---------------------------------------------------------------------------
3431// 0063 — Multi-repo refactor agent. See src/lib/multi-repo-refactor.ts.
3432// ---------------------------------------------------------------------------
3433export const multiRepoRefactors = pgTable(
3434 "multi_repo_refactors",
3435 {
3436 id: uuid("id").primaryKey().defaultRandom(),
3437 ownerUserId: uuid("owner_user_id")
3438 .notNull()
3439 .references(() => users.id, { onDelete: "cascade" }),
3440 title: text("title").notNull(),
3441 description: text("description").notNull(),
3442 status: text("status").default("planning").notNull(),
3443 createdAt: timestamp("created_at").defaultNow().notNull(),
3444 updatedAt: timestamp("updated_at").defaultNow().notNull(),
3445 },
3446 (table) => [
3447 index("multi_repo_refactors_owner").on(table.ownerUserId, table.createdAt),
3448 index("multi_repo_refactors_status").on(table.status, table.updatedAt),
3449 ]
3450);
3451
3452export const multiRepoRefactorPrs = pgTable(
3453 "multi_repo_refactor_prs",
3454 {
3455 id: uuid("id").primaryKey().defaultRandom(),
3456 refactorId: uuid("refactor_id")
3457 .notNull()
3458 .references(() => multiRepoRefactors.id, { onDelete: "cascade" }),
3459 repositoryId: uuid("repository_id")
3460 .notNull()
3461 .references(() => repositories.id, { onDelete: "cascade" }),
3462 pullRequestId: uuid("pull_request_id").references(() => pullRequests.id, {
3463 onDelete: "set null",
3464 }),
3465 status: text("status").default("pending").notNull(),
3466 errorMessage: text("error_message"),
3467 createdAt: timestamp("created_at").defaultNow().notNull(),
3468 updatedAt: timestamp("updated_at").defaultNow().notNull(),
3469 },
3470 (table) => [
3471 uniqueIndex("multi_repo_refactor_prs_unique").on(
3472 table.refactorId,
3473 table.repositoryId
3474 ),
3475 index("multi_repo_refactor_prs_refactor").on(
3476 table.refactorId,
3477 table.status
3478 ),
3479 index("multi_repo_refactor_prs_repo").on(table.repositoryId),
3480 ]
3481);
3482
3483export type MultiRepoRefactor = typeof multiRepoRefactors.$inferSelect;
3484export type NewMultiRepoRefactor = typeof multiRepoRefactors.$inferInsert;
3485export type MultiRepoRefactorPr = typeof multiRepoRefactorPrs.$inferSelect;
3486export type NewMultiRepoRefactorPr = typeof multiRepoRefactorPrs.$inferInsert;
3487
e3c90cdClaude3488// ─── Chat integrations (migration 0064) ────────────────────────────────────
3489// Slack / Discord / Teams bindings. One row per (owner, kind, team, channel).
3490// `webhook_url` is the outbound Incoming-Webhook URL for posting back into
3491// the channel; `signing_secret` is the per-install verification key (HMAC for
3492// Slack, Ed25519 public key for Discord). See drizzle/0064_chat_integrations.sql
3493// for the full schema rationale.
3494export const chatIntegrations = pgTable(
3495 "chat_integrations",
3496 {
3497 id: uuid("id").primaryKey().defaultRandom(),
3498 ownerUserId: uuid("owner_user_id")
3499 .notNull()
3500 .references(() => users.id, { onDelete: "cascade" }),
3501 kind: text("kind").notNull(), // 'slack' | 'discord' | 'teams'
3502 teamId: text("team_id"),
3503 channelId: text("channel_id"),
3504 webhookUrl: text("webhook_url"),
3505 signingSecret: text("signing_secret"),
3506 enabled: boolean("enabled").default(true).notNull(),
3507 createdAt: timestamp("created_at").defaultNow().notNull(),
3508 lastUsedAt: timestamp("last_used_at"),
3509 },
3510 (table) => [
3511 index("chat_integrations_owner").on(table.ownerUserId, table.kind),
3512 ]
3513);
3514
3515export type ChatIntegration = typeof chatIntegrations.$inferSelect;
3516export type NewChatIntegration = typeof chatIntegrations.$inferInsert;
3517
0c3eee5Claude3518// ---------------------------------------------------------------------------
3519// 0065 — Per-call AI cost tracking. See src/lib/ai-cost-tracker.ts and
3520// src/routes/billing-usage.tsx. Best-effort: insert failures must NEVER
3521// throw out of the Claude caller, so every call site wraps recordAiCost in
3522// try/catch. Cents are computed at insert time from a hardcoded pricing
3523// table so historical aggregates stay stable across price changes.
3524// ---------------------------------------------------------------------------
3525export const aiCostEvents = pgTable(
3526 "ai_cost_events",
3527 {
3528 id: uuid("id").primaryKey().defaultRandom(),
3529 occurredAt: timestamp("occurred_at", { withTimezone: true })
3530 .defaultNow()
3531 .notNull(),
3532 ownerUserId: uuid("owner_user_id").references(() => users.id, {
3533 onDelete: "set null",
3534 }),
3535 repositoryId: uuid("repository_id").references(() => repositories.id, {
3536 onDelete: "set null",
3537 }),
3538 agentSessionId: uuid("agent_session_id").references(
3539 () => agentSessions.id,
3540 { onDelete: "set null" }
3541 ),
3542 model: text("model").notNull(),
3543 inputTokens: integer("input_tokens").default(0).notNull(),
3544 outputTokens: integer("output_tokens").default(0).notNull(),
3545 centsEstimate: integer("cents_estimate").default(0).notNull(),
3546 category: text("category").default("other").notNull(),
3547 sourceId: text("source_id"),
3548 sourceKind: text("source_kind"),
3549 createdAt: timestamp("created_at", { withTimezone: true })
3550 .defaultNow()
3551 .notNull(),
3552 },
3553 (table) => [
3554 index("ai_cost_events_owner_time").on(table.ownerUserId, table.occurredAt),
3555 index("ai_cost_events_repo_time").on(table.repositoryId, table.occurredAt),
3556 index("ai_cost_events_agent_time").on(
3557 table.agentSessionId,
3558 table.occurredAt
3559 ),
3560 index("ai_cost_events_category_time").on(table.category, table.occurredAt),
3561 ]
3562);
3563
3564export type AiCostEvent = typeof aiCostEvents.$inferSelect;
3565export type NewAiCostEvent = typeof aiCostEvents.$inferInsert;
3566
3567export const aiBudgets = pgTable("ai_budgets", {
3568 userId: uuid("user_id")
3569 .primaryKey()
3570 .references(() => users.id, { onDelete: "cascade" }),
3571 monthlyCents: integer("monthly_cents").default(0).notNull(),
3572 updatedAt: timestamp("updated_at", { withTimezone: true })
3573 .defaultNow()
3574 .notNull(),
3575});
3576
3577export type AiBudget = typeof aiBudgets.$inferSelect;
3578export type NewAiBudget = typeof aiBudgets.$inferInsert;
79ed944Claude3579
3580// ---------------------------------------------------------------------------
3581// Migration 0067 — per-PR runnable sandboxes. See src/lib/pr-sandbox.ts.
3582//
3583// Each open PR can hold at most one sandbox row (UNIQUE on pr_id). The row
3584// owns the lifecycle (provisioning → ready / failed / destroyed) plus the
3585// resolved playground.yml the sandbox was provisioned from. Re-provisioning
3586// the same PR upserts onto the existing row so a force-push always points
3587// at the newest head.
3588// ---------------------------------------------------------------------------
3589export const prSandboxes = pgTable(
3590 "pr_sandboxes",
3591 {
3592 id: uuid("id").primaryKey().defaultRandom(),
3593 prId: uuid("pr_id")
3594 .notNull()
3595 .references(() => pullRequests.id, { onDelete: "cascade" }),
3596 status: text("status").default("provisioning").notNull(),
3597 sandboxUrl: text("sandbox_url").notNull(),
3598 containerId: text("container_id"),
3599 playgroundYml: text("playground_yml"),
3600 provisionedAt: timestamp("provisioned_at", { withTimezone: true })
3601 .defaultNow()
3602 .notNull(),
3603 expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
3604 destroyedAt: timestamp("destroyed_at", { withTimezone: true }),
3605 errorMessage: text("error_message"),
3606 createdAt: timestamp("created_at", { withTimezone: true })
3607 .defaultNow()
3608 .notNull(),
3609 },
3610 (table) => [
3611 uniqueIndex("pr_sandboxes_pr_id").on(table.prId),
3612 index("pr_sandboxes_status_expires").on(table.status, table.expiresAt),
3613 ]
3614);
3615
3616export type PrSandbox = typeof prSandboxes.$inferSelect;
3617export type NewPrSandbox = typeof prSandboxes.$inferInsert;
d199847Claude3618
3619// ---------------------------------------------------------------------------
3620// 0068 — AI-tracked documentation sections. See src/lib/ai-doc-updater.ts.
3621//
3622// Stores the currently claimed source-file hash for each
3623// `<!-- gluecron:doc-track src=... -->` region in a tracked markdown file.
3624// The post-receive hook re-hashes the referenced source after every push
3625// and proposes a PR (tagged `ai:doc-update`) when the prose drifts from
3626// the truth.
3627// ---------------------------------------------------------------------------
3628export const docTracking = pgTable(
3629 "doc_tracking",
3630 {
3631 id: uuid("id").primaryKey().defaultRandom(),
3632 repositoryId: uuid("repository_id")
3633 .notNull()
3634 .references(() => repositories.id, { onDelete: "cascade" }),
3635 docPath: text("doc_path").notNull(),
3636 sectionMarker: text("section_marker").notNull(),
3637 srcPath: text("src_path").notNull(),
3638 claimedHash: text("claimed_hash").notNull(),
3639 lastCheckedAt: timestamp("last_checked_at", { withTimezone: true })
3640 .defaultNow()
3641 .notNull(),
3642 lastPrId: uuid("last_pr_id").references(() => pullRequests.id, {
3643 onDelete: "set null",
3644 }),
3645 createdAt: timestamp("created_at", { withTimezone: true })
3646 .defaultNow()
3647 .notNull(),
3648 },
3649 (table) => [
3650 uniqueIndex("doc_tracking_repo_doc_marker").on(
3651 table.repositoryId,
3652 table.docPath,
3653 table.sectionMarker
3654 ),
3655 index("doc_tracking_repo").on(table.repositoryId),
3656 ]
3657);
3658
3659export type DocTracking = typeof docTracking.$inferSelect;
3660export type NewDocTracking = typeof docTracking.$inferInsert;
c6db5eeClaude3661
3662// ---------------------------------------------------------------------------
3663// 0069 — Hosted Claude tool-use loops. See src/lib/hosted-claude-loop.ts and
3664// src/routes/claude-deploy.tsx.
3665//
3666// Users paste a Claude tool-use loop, get a hosted endpoint + budget meter.
3667// Each loop is paired to an agent_sessions row so it inherits multiplayer
3668// namespacing and the daily budget mutex.
3669// ---------------------------------------------------------------------------
3670export const hostedClaudeLoops = pgTable(
3671 "hosted_claude_loops",
3672 {
3673 id: uuid("id").primaryKey().defaultRandom(),
3674 ownerUserId: uuid("owner_user_id")
3675 .notNull()
3676 .references(() => users.id, { onDelete: "cascade" }),
3677 name: text("name").notNull(),
3678 sourceCode: text("source_code").notNull(),
3679 endpointPath: text("endpoint_path").notNull().unique(),
3680 agentSessionId: uuid("agent_session_id").references(
3681 () => agentSessions.id,
3682 { onDelete: "set null" }
3683 ),
3684 status: text("status").default("paused").notNull(),
3685 isPublic: boolean("is_public").default(false).notNull(),
3686 monthlyBudgetCents: integer("monthly_budget_cents").default(500).notNull(),
3687 lastRunAt: timestamp("last_run_at", { withTimezone: true }),
3688 totalInvocations: integer("total_invocations").default(0).notNull(),
3689 totalCentsSpent: integer("total_cents_spent").default(0).notNull(),
3690 createdAt: timestamp("created_at", { withTimezone: true })
3691 .defaultNow()
3692 .notNull(),
3693 updatedAt: timestamp("updated_at", { withTimezone: true })
3694 .defaultNow()
3695 .notNull(),
3696 },
3697 (table) => [
3698 index("hosted_claude_loops_owner").on(table.ownerUserId, table.updatedAt),
3699 index("hosted_claude_loops_status").on(table.status),
3700 ]
3701);
3702
3703export const hostedClaudeLoopRuns = pgTable(
3704 "hosted_claude_loop_runs",
3705 {
3706 id: uuid("id").primaryKey().defaultRandom(),
3707 loopId: uuid("loop_id")
3708 .notNull()
3709 .references(() => hostedClaudeLoops.id, { onDelete: "cascade" }),
3710 inputPayload: jsonb("input_payload").default({}).notNull(),
3711 outputPayload: jsonb("output_payload"),
3712 stdout: text("stdout"),
3713 stderr: text("stderr"),
3714 startedAt: timestamp("started_at", { withTimezone: true })
3715 .defaultNow()
3716 .notNull(),
3717 finishedAt: timestamp("finished_at", { withTimezone: true }),
3718 status: text("status").default("running").notNull(),
3719 centsEstimate: integer("cents_estimate").default(0).notNull(),
3720 claudeInputTokens: integer("claude_input_tokens").default(0).notNull(),
3721 claudeOutputTokens: integer("claude_output_tokens").default(0).notNull(),
3722 exitCode: integer("exit_code"),
3723 errorMessage: text("error_message"),
3724 createdAt: timestamp("created_at", { withTimezone: true })
3725 .defaultNow()
3726 .notNull(),
3727 },
3728 (table) => [
3729 index("hosted_claude_loop_runs_loop_time").on(
3730 table.loopId,
3731 table.startedAt
3732 ),
3733 index("hosted_claude_loop_runs_status").on(table.status, table.startedAt),
3734 ]
3735);
3736
3737export type HostedClaudeLoop = typeof hostedClaudeLoops.$inferSelect;
3738export type NewHostedClaudeLoop = typeof hostedClaudeLoops.$inferInsert;
3739export type HostedClaudeLoopRun = typeof hostedClaudeLoopRuns.$inferSelect;
3740export type NewHostedClaudeLoopRun = typeof hostedClaudeLoopRuns.$inferInsert;
5ca514aClaude3741
3742// ---------------------------------------------------------------------------
9b3a183Claude3743// 0070 — Agent Marketplace. See src/lib/agent-marketplace.ts.
5ca514aClaude3744// ---------------------------------------------------------------------------
3745export const agentMarketplaceListings = pgTable(
3746 "agent_marketplace_listings",
3747 {
3748 id: uuid("id").primaryKey().defaultRandom(),
3749 publisherUserId: uuid("publisher_user_id")
3750 .notNull()
3751 .references(() => users.id, { onDelete: "cascade" }),
3752 slug: text("slug").notNull().unique(),
3753 name: text("name").notNull(),
3754 tagline: text("tagline").default("").notNull(),
3755 description: text("description").default("").notNull(),
3756 category: text("category").default("custom").notNull(),
3757 pricingModel: text("pricing_model").default("free").notNull(),
3758 priceCents: integer("price_cents").default(0).notNull(),
3759 agentTemplate: jsonb("agent_template")
3760 .$type<{
3761 branchNamespace?: string;
3762 budgetCentsPerDay?: number;
3763 capabilities?: string[];
3764 [k: string]: unknown;
3765 }>()
3766 .default({})
3767 .notNull(),
3768 sourceUrl: text("source_url"),
3769 status: text("status").default("draft").notNull(),
3770 installCount: integer("install_count").default(0).notNull(),
3771 ratingAvg: numeric("rating_avg", { precision: 3, scale: 2 })
3772 .default("0")
3773 .notNull(),
3774 ratingCount: integer("rating_count").default(0).notNull(),
3775 createdAt: timestamp("created_at", { withTimezone: true })
3776 .defaultNow()
3777 .notNull(),
3778 updatedAt: timestamp("updated_at", { withTimezone: true })
3779 .defaultNow()
3780 .notNull(),
3781 },
3782 (table) => [
9b3a183Claude3783 index("agent_marketplace_listings_category").on(table.category, table.status),
3784 index("agent_marketplace_listings_rating").on(table.ratingAvg, table.ratingCount),
5ca514aClaude3785 index("agent_marketplace_listings_installs").on(table.installCount),
3786 index("agent_marketplace_listings_publisher").on(table.publisherUserId),
9b3a183Claude3787 index("agent_marketplace_listings_status_created").on(table.status, table.createdAt),
5ca514aClaude3788 ]
3789);
3790
3791export const agentMarketplaceInstalls = pgTable(
3792 "agent_marketplace_installs",
3793 {
3794 id: uuid("id").primaryKey().defaultRandom(),
3795 listingId: uuid("listing_id")
3796 .notNull()
3797 .references(() => agentMarketplaceListings.id, { onDelete: "cascade" }),
3798 repositoryId: uuid("repository_id")
3799 .notNull()
3800 .references(() => repositories.id, { onDelete: "cascade" }),
3801 installedByUserId: uuid("installed_by_user_id")
3802 .notNull()
3803 .references(() => users.id, { onDelete: "cascade" }),
3804 agentSessionId: uuid("agent_session_id").references(
3805 () => agentSessions.id,
3806 { onDelete: "set null" }
3807 ),
3808 status: text("status").default("active").notNull(),
3809 installedAt: timestamp("installed_at", { withTimezone: true })
3810 .defaultNow()
3811 .notNull(),
3812 lastInvokedAt: timestamp("last_invoked_at", { withTimezone: true }),
3813 createdAt: timestamp("created_at", { withTimezone: true })
3814 .defaultNow()
3815 .notNull(),
3816 },
3817 (table) => [
9b3a183Claude3818 uniqueIndex("agent_marketplace_installs_listing_repo").on(table.listingId, table.repositoryId),
5ca514aClaude3819 index("agent_marketplace_installs_repo").on(table.repositoryId, table.status),
3820 index("agent_marketplace_installs_installer").on(table.installedByUserId),
3821 ]
3822);
3823
3824export const agentMarketplaceReviews = pgTable(
3825 "agent_marketplace_reviews",
3826 {
3827 id: uuid("id").primaryKey().defaultRandom(),
3828 listingId: uuid("listing_id")
3829 .notNull()
3830 .references(() => agentMarketplaceListings.id, { onDelete: "cascade" }),
3831 reviewerUserId: uuid("reviewer_user_id")
3832 .notNull()
3833 .references(() => users.id, { onDelete: "cascade" }),
3834 rating: integer("rating").notNull(),
3835 body: text("body").default("").notNull(),
3836 createdAt: timestamp("created_at", { withTimezone: true })
3837 .defaultNow()
3838 .notNull(),
3839 },
3840 (table) => [
9b3a183Claude3841 index("agent_marketplace_reviews_listing_created").on(table.listingId, table.createdAt),
5ca514aClaude3842 index("agent_marketplace_reviews_reviewer").on(table.reviewerUserId),
3843 ]
3844);
3845
9b3a183Claude3846export type AgentMarketplaceListing = typeof agentMarketplaceListings.$inferSelect;
3847export type NewAgentMarketplaceListing = typeof agentMarketplaceListings.$inferInsert;
3848export type AgentMarketplaceInstall = typeof agentMarketplaceInstalls.$inferSelect;
3849export type NewAgentMarketplaceInstall = typeof agentMarketplaceInstalls.$inferInsert;
3850export type AgentMarketplaceReview = typeof agentMarketplaceReviews.$inferSelect;
3851export type NewAgentMarketplaceReview = typeof agentMarketplaceReviews.$inferInsert;
3852
3853// ---------------------------------------------------------------------------
3854// 0072 — per-(repo, user) cloud dev environments. See src/lib/dev-env.ts.
3855// ---------------------------------------------------------------------------
3856export const devEnvs = pgTable(
3857 "dev_envs",
3858 {
3859 id: uuid("id").primaryKey().defaultRandom(),
3860 repositoryId: uuid("repository_id")
3861 .notNull()
3862 .references(() => repositories.id, { onDelete: "cascade" }),
3863 ownerUserId: uuid("owner_user_id")
3864 .notNull()
3865 .references(() => users.id, { onDelete: "cascade" }),
3866 status: text("status").default("cold").notNull(),
3867 previewUrl: text("preview_url"),
3868 containerId: text("container_id"),
3869 machineSize: text("machine_size").default("small").notNull(),
3870 idleMinutes: integer("idle_minutes").default(30).notNull(),
3871 devYml: text("dev_yml"),
3872 errorMessage: text("error_message"),
3873 lastActiveAt: timestamp("last_active_at", { withTimezone: true })
3874 .defaultNow()
3875 .notNull(),
3876 expiresAt: timestamp("expires_at", { withTimezone: true }),
3877 createdAt: timestamp("created_at", { withTimezone: true })
3878 .defaultNow()
3879 .notNull(),
3880 },
3881 (table) => [
3882 uniqueIndex("dev_envs_repo_owner").on(table.repositoryId, table.ownerUserId),
3883 index("dev_envs_status_active").on(table.status, table.lastActiveAt),
3884 ]
3885);
3886
3887export type DevEnv = typeof devEnvs.$inferSelect;
3888export type NewDevEnv = typeof devEnvs.$inferInsert;
783dd46Claude3889
3890// ---------------------------------------------------------------------------
3891// Block ST — Server targets (drizzle/0073_server_targets.sql)
3892//
3893// Admin-managed remote boxes Gluecron can SSH into and run a deploy script
3894// on. Each target carries an encrypted private SSH key, a pinned host
3895// fingerprint, an optional repo+branch to watch, and a per-target list of
3896// env vars materialised on the box at deploy time. Customer-facing rollout
3897// is gated to a follow-up block (Block 2) that scopes by owner_user_id.
3898// ---------------------------------------------------------------------------
3899
3900export const serverTargets = pgTable(
3901 "server_targets",
3902 {
3903 id: uuid("id").primaryKey().defaultRandom(),
3904 name: text("name").notNull(),
3905 host: text("host").notNull(),
3906 port: integer("port").default(22).notNull(),
3907 sshUser: text("ssh_user").notNull(),
3908 encryptedPrivateKey: text("encrypted_private_key").notNull(),
3909 hostFingerprint: text("host_fingerprint"),
3910 deployPath: text("deploy_path").default("/var/www/app").notNull(),
3911 deployScript: text("deploy_script").default("bash deploy.sh").notNull(),
3912 watchedRepositoryId: uuid("watched_repository_id").references(
3913 () => repositories.id,
3914 { onDelete: "set null" }
3915 ),
3916 watchedBranch: text("watched_branch"),
3917 status: text("status").default("unverified").notNull(),
3918 lastSeenAt: timestamp("last_seen_at", { withTimezone: true }),
3919 createdBy: uuid("created_by").references(() => users.id, {
3920 onDelete: "set null",
3921 }),
3922 createdAt: timestamp("created_at", { withTimezone: true })
3923 .defaultNow()
3924 .notNull(),
3925 updatedAt: timestamp("updated_at", { withTimezone: true })
3926 .defaultNow()
3927 .notNull(),
3928 },
3929 (table) => [
3930 uniqueIndex("server_targets_name_uq").on(table.name),
3931 index("server_targets_watch_idx").on(
3932 table.watchedRepositoryId,
3933 table.watchedBranch
3934 ),
3935 ]
3936);
3937
3938export type ServerTarget = typeof serverTargets.$inferSelect;
3939export type NewServerTarget = typeof serverTargets.$inferInsert;
3940
3941export const serverTargetEnv = pgTable(
3942 "server_target_env",
3943 {
3944 id: uuid("id").primaryKey().defaultRandom(),
3945 targetId: uuid("target_id")
3946 .notNull()
3947 .references(() => serverTargets.id, { onDelete: "cascade" }),
3948 name: text("name").notNull(),
3949 encryptedValue: text("encrypted_value").notNull(),
3950 isSecret: boolean("is_secret").default(true).notNull(),
3951 updatedBy: uuid("updated_by").references(() => users.id, {
3952 onDelete: "set null",
3953 }),
3954 createdAt: timestamp("created_at", { withTimezone: true })
3955 .defaultNow()
3956 .notNull(),
3957 updatedAt: timestamp("updated_at", { withTimezone: true })
3958 .defaultNow()
3959 .notNull(),
3960 },
3961 (table) => [uniqueIndex("server_target_env_uq").on(table.targetId, table.name)]
3962);
3963
3964export type ServerTargetEnv = typeof serverTargetEnv.$inferSelect;
3965export type NewServerTargetEnv = typeof serverTargetEnv.$inferInsert;
3966
3967export const serverTargetDeployments = pgTable(
3968 "server_target_deployments",
3969 {
3970 id: uuid("id").primaryKey().defaultRandom(),
3971 targetId: uuid("target_id")
3972 .notNull()
3973 .references(() => serverTargets.id, { onDelete: "cascade" }),
3974 commitSha: text("commit_sha"),
3975 ref: text("ref"),
3976 status: text("status").default("pending").notNull(),
3977 exitCode: integer("exit_code"),
3978 stdout: text("stdout"),
3979 stderr: text("stderr"),
3980 triggeredBy: uuid("triggered_by").references(() => users.id, {
3981 onDelete: "set null",
3982 }),
3983 triggerSource: text("trigger_source").default("push").notNull(),
3984 startedAt: timestamp("started_at", { withTimezone: true })
3985 .defaultNow()
3986 .notNull(),
3987 finishedAt: timestamp("finished_at", { withTimezone: true }),
3988 },
3989 (table) => [
3990 index("server_target_deployments_target_idx").on(
3991 table.targetId,
3992 table.startedAt
3993 ),
3994 ]
3995);
3996
3997export type ServerTargetDeployment =
3998 typeof serverTargetDeployments.$inferSelect;
3999export type NewServerTargetDeployment =
4000 typeof serverTargetDeployments.$inferInsert;
4001
4002export const serverTargetAudit = pgTable(
4003 "server_target_audit",
4004 {
4005 id: uuid("id").primaryKey().defaultRandom(),
4006 targetId: uuid("target_id").references(() => serverTargets.id, {
4007 onDelete: "set null",
4008 }),
4009 actorId: uuid("actor_id").references(() => users.id, {
4010 onDelete: "set null",
4011 }),
4012 action: text("action").notNull(),
4013 detail: text("detail"),
4014 ip: text("ip"),
4015 createdAt: timestamp("created_at", { withTimezone: true })
4016 .defaultNow()
4017 .notNull(),
4018 },
4019 (table) => [
4020 index("server_target_audit_target_idx").on(table.targetId, table.createdAt),
4021 ]
4022);
4023
4024export type ServerTargetAudit = typeof serverTargetAudit.$inferSelect;
4025export type NewServerTargetAudit = typeof serverTargetAudit.$inferInsert;
90c7531Claude4026
4027// ---------------------------------------------------------------------------
4028// Block CW — Claude on the web (drizzle/0074_claude_web_sessions.sql)
4029//
4030// Per-repo interactive Claude Code sessions runnable from any browser.
4031// Each session owns a working dir on the web server (a fresh git clone of
4032// the repo's bare store) and persists turn-by-turn transcripts so an iPad
4033// user can resume the same conversation later from a laptop.
4034//
4035// v1 admin-only; v2 will scope by owner_user_id and isolate per-session
4036// containers. Schema is forward-compatible with both.
4037// ---------------------------------------------------------------------------
4038
4039export const claudeWebSessions = pgTable(
4040 "claude_web_sessions",
4041 {
4042 id: uuid("id").primaryKey().defaultRandom(),
4043 repositoryId: uuid("repository_id")
4044 .notNull()
4045 .references(() => repositories.id, { onDelete: "cascade" }),
4046 ownerUserId: uuid("owner_user_id")
4047 .notNull()
4048 .references(() => users.id, { onDelete: "cascade" }),
4049 title: text("title").default("New session").notNull(),
4050 branch: text("branch").default("main").notNull(),
4051 workdirPath: text("workdir_path").notNull(),
4052 claudeSessionId: text("claude_session_id"),
4053 status: text("status").default("cold").notNull(),
4054 lastActiveAt: timestamp("last_active_at", { withTimezone: true })
4055 .defaultNow()
4056 .notNull(),
4057 createdAt: timestamp("created_at", { withTimezone: true })
4058 .defaultNow()
4059 .notNull(),
4060 },
4061 (table) => [
4062 index("claude_web_sessions_repo_idx").on(
4063 table.repositoryId,
4064 table.lastActiveAt
4065 ),
4066 index("claude_web_sessions_owner_idx").on(
4067 table.ownerUserId,
4068 table.lastActiveAt
4069 ),
4070 ]
4071);
4072
4073export type ClaudeWebSession = typeof claudeWebSessions.$inferSelect;
4074export type NewClaudeWebSession = typeof claudeWebSessions.$inferInsert;
4075
4076export const claudeWebMessages = pgTable(
4077 "claude_web_messages",
4078 {
4079 id: uuid("id").primaryKey().defaultRandom(),
4080 sessionId: uuid("session_id")
4081 .notNull()
4082 .references(() => claudeWebSessions.id, { onDelete: "cascade" }),
4083 role: text("role").notNull(),
4084 body: text("body").notNull(),
4085 exitCode: integer("exit_code"),
4086 durationMs: integer("duration_ms"),
4087 createdAt: timestamp("created_at", { withTimezone: true })
4088 .defaultNow()
4089 .notNull(),
4090 },
4091 (table) => [
4092 index("claude_web_messages_session_idx").on(
4093 table.sessionId,
4094 table.createdAt
4095 ),
4096 ]
4097);
4098
4099export type ClaudeWebMessage = typeof claudeWebMessages.$inferSelect;
873f13cClaude4100
4101// ---------------------------------------------------------------------------
4102// 0077 — Status page: incident history + subscriber list.
4103//
4104// incidents: manually-filed or autopilot-detected outage records shown on
4105// the public /status page. severity: 'minor' | 'major' | 'critical'.
4106// status: 'investigating' | 'identified' | 'monitoring' | 'resolved'.
4107//
4108// status_subscribers: email addresses that have opted-in to receive alerts
4109// when a new incident is filed.
4110// ---------------------------------------------------------------------------
4111export const incidents = pgTable(
4112 "incidents",
4113 {
4114 id: uuid("id").primaryKey().defaultRandom(),
4115 title: text("title").notNull(),
4116 severity: text("severity").notNull().default("minor"),
4117 status: text("status").notNull().default("resolved"),
4118 startedAt: timestamp("started_at", { withTimezone: true })
4119 .defaultNow()
4120 .notNull(),
4121 resolvedAt: timestamp("resolved_at", { withTimezone: true }),
4122 body: text("body"),
4123 createdAt: timestamp("created_at", { withTimezone: true })
4124 .defaultNow()
4125 .notNull(),
4126 updatedAt: timestamp("updated_at", { withTimezone: true })
4127 .defaultNow()
4128 .notNull(),
4129 },
4130 (table) => [
4131 index("idx_incidents_started_at").on(table.startedAt),
4132 index("idx_incidents_status").on(table.status),
4133 ]
4134);
4135
4136export type Incident = typeof incidents.$inferSelect;
4137export type NewIncident = typeof incidents.$inferInsert;
4138
4139export const statusSubscribers = pgTable(
4140 "status_subscribers",
4141 {
4142 id: uuid("id").primaryKey().defaultRandom(),
4143 email: text("email").notNull().unique(),
4144 confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
4145 confirmToken: text("confirm_token").unique(),
4146 unsubscribeToken: text("unsubscribe_token").unique(),
4147 createdAt: timestamp("created_at", { withTimezone: true })
4148 .defaultNow()
4149 .notNull(),
4150 },
4151 (table) => [
4152 index("idx_status_subscribers_email").on(table.email),
4153 ]
4154);
4155
4156export type StatusSubscriber = typeof statusSubscribers.$inferSelect;
4157export type NewStatusSubscriber = typeof statusSubscribers.$inferInsert;
90c7531Claude4158export type NewClaudeWebMessage = typeof claudeWebMessages.$inferInsert;
9f29b65Claude4159
4160// ---------------------------------------------------------------------------
4161// Migration 0077 — Enterprise leads (contact form submissions from /enterprise)
4162// ---------------------------------------------------------------------------
4163
4164/**
4165 * Enterprise leads — one row per /enterprise contact form submission.
4166 * Sales team reads these directly or via a future CRM sync.
4167 */
4168export const enterpriseLeads = pgTable(
4169 "enterprise_leads",
4170 {
4171 id: uuid("id").primaryKey().defaultRandom(),
4172 name: text("name").notNull(),
4173 company: text("company").notNull(),
4174 email: text("email").notNull(),
4175 teamSize: text("team_size").notNull(),
4176 message: text("message"),
4177 ip: text("ip"),
4178 createdAt: timestamp("created_at").defaultNow().notNull(),
4179 },
4180 (table) => [index("enterprise_leads_created").on(table.createdAt)]
4181);
4182
4183export type EnterpriseLead = typeof enterpriseLeads.$inferSelect;
4184export type NewEnterpriseLead = typeof enterpriseLeads.$inferInsert;