schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum ConnectionSyncStatus {
  SYNC_NEEDED
  IN_SYNC_QUEUE
  SYNCING
  SYNCED
  SYNCED_WITH_WARNINGS
  FAILED
}

enum ChatVisibility {
  PRIVATE
  PUBLIC
}

/// @note: The @map annotation is required to maintain backwards compatibility
/// with the existing database.
/// @note: In the generated client, these mapped values will be in pascalCase.
/// This behaviour will change in prisma v7. See: https://github.com/prisma/prisma/issues/8446#issuecomment-3356119713
enum CodeHostType {
  github
  gitlab
  gitea
  gerrit
  bitbucketServer         @map("bitbucket-server")
  bitbucketCloud          @map("bitbucket-cloud")
  genericGitHost          @map("generic-git-host")
  azuredevops
}

model Repo {
  id                    Int                    @id @default(autoincrement())
  name                  String /// Full repo name, including the vcs hostname (ex. github.com/sourcebot-dev/sourcebot)
  displayName           String? /// Display name of the repo for UI (ex. sourcebot-dev/sourcebot)
  createdAt             DateTime               @default(now())
  updatedAt             DateTime               @updatedAt
  isFork                Boolean
  isArchived            Boolean
  isPublic              Boolean                @default(false)
  isAutoCleanupDisabled Boolean                @default(false) /// If true, automatic cleanup of this repo when it becomes orphaned will be disabled.
  metadata              Json /// For schema see repoMetadataSchema in packages/shared/src/types.ts
  cloneUrl              String
  webUrl                String?
  connections           RepoToConnection[]
  imageUrl              String?
  defaultBranch         String?

  permittedAccounts  AccountToRepoPermission[]
  permissionSyncJobs RepoPermissionSyncJob[]
  permissionSyncedAt    DateTime? /// When the permissions were last synced successfully.

  jobs RepoIndexingJob[]
  indexedAt          DateTime? /// When the repo was last indexed successfully.
  indexedCommitHash  String? /// The commit hash of the last indexed commit (on HEAD).
  latestIndexingJobStatus RepoIndexingJobStatus? /// The status of the latest indexing job.
  pushedAt           DateTime? /// The timestamp of the most recent commit across all branches.

  external_id           String /// The id of the repo in the external service
  external_codeHostType CodeHostType /// The type of the external service (e.g., github, gitlab, etc.)
  external_codeHostUrl  String /// The base url of the external service (e.g., https://github.com)

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  searchContexts SearchContext[]
  visits RepoVisit[]

  @@unique([external_id, external_codeHostUrl, orgId])
  @@index([orgId])
  @@index([indexedAt])
}

enum RepoIndexingJobStatus {
  PENDING
  IN_PROGRESS
  COMPLETED
  FAILED
}

enum RepoIndexingJobType {
  INDEX
  CLEANUP
}

model RepoIndexingJob {
  id          String                      @id @default(cuid())
  type        RepoIndexingJobType
  status      RepoIndexingJobStatus @default(PENDING)
  createdAt   DateTime                    @default(now())
  updatedAt   DateTime                    @updatedAt
  completedAt DateTime?
  metadata    Json? /// For schema see repoIndexingJobMetadataSchema in packages/shared/src/types.ts

  errorMessage String?

  repo   Repo @relation(fields: [repoId], references: [id], onDelete: Cascade)
  repoId Int

  @@index([repoId, type, status])
}

enum RepoPermissionSyncJobStatus {
  PENDING
  IN_PROGRESS
  COMPLETED
  FAILED
}

model RepoPermissionSyncJob {
  id          String                      @id @default(cuid())
  status      RepoPermissionSyncJobStatus @default(PENDING)
  createdAt   DateTime                    @default(now())
  updatedAt   DateTime                    @updatedAt
  completedAt DateTime?

  errorMessage String?

  repo   Repo @relation(fields: [repoId], references: [id], onDelete: Cascade)
  repoId Int
}

model SearchContext {
  id Int @id @default(autoincrement())

  name        String
  description String?
  repos       Repo[]

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  @@unique([name, orgId])
}

/// Matches the union of `type` fields in the schema.
/// @see: schemas/v3/connection.type.ts
enum ConnectionType {
  github
  gitlab
  gitea
  gerrit
  bitbucket
  azuredevops
  git
}

model Connection {
  id                 Int                  @id @default(autoincrement())
  name               String
  config             Json
  isDeclarative      Boolean              @default(false)
  createdAt          DateTime             @default(now())
  updatedAt          DateTime             @updatedAt
  repos              RepoToConnection[]

  // The type of connection (e.g., github, gitlab, etc.)
  connectionType ConnectionType

  syncJobs ConnectionSyncJob[]
  /// When the connection was last synced successfully.
  syncedAt           DateTime?

  /// Controls whether repository permissions are enforced for this connection.
  /// When `PERMISSION_SYNC_ENABLED` is false, this setting has no effect.
  /// Defaults to the value of `PERMISSION_SYNC_ENABLED`.
  ///
  /// See https://docs.sourcebot.dev/docs/features/permission-syncing
  enforcePermissions Boolean @default(true)

  /// Controls whether repository permissions are enforced for public repositories
  /// in this connection. When true, public repositories are only visible to users
  /// with a linked account for this connection's code host. When false, public
  /// repositories are visible to all users. Has no effect when enforcePermissions
  /// is false. Defaults to false.
  ///
  /// See https://docs.sourcebot.dev/docs/features/permission-syncing
  enforcePermissionsForPublicRepos Boolean @default(false)

  // The organization that owns this connection
  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  @@unique([name, orgId])
}

enum ConnectionSyncJobStatus {
  PENDING
  IN_PROGRESS
  COMPLETED
  FAILED
}

model ConnectionSyncJob {
  id          String                      @id @default(cuid())
  status      ConnectionSyncJobStatus @default(PENDING)
  createdAt   DateTime                    @default(now())
  updatedAt   DateTime                    @updatedAt
  completedAt DateTime?

  warningMessages String[]
  errorMessage String?

  connection   Connection @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  connectionId Int
}

model RepoToConnection {
  addedAt DateTime @default(now())

  connection   Connection @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  connectionId Int

  repo   Repo @relation(fields: [repoId], references: [id], onDelete: Cascade)
  repoId Int

  @@id([connectionId, repoId])
  @@index([repoId, connectionId])
}

model Invite {
  /// The globally unique invite id
  id String @id @default(cuid())

  /// Time of invite creation
  createdAt DateTime @default(now())

  /// The email of the recipient of the invite
  recipientEmail String

  /// The user that created the invite
  host       User   @relation(fields: [hostUserId], references: [id], onDelete: Cascade)
  hostUserId String

  /// The organization the invite is for
  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  @@unique([recipientEmail, orgId])
}

model AccountRequest {
  id String @id @default(cuid())

  createdAt DateTime @default(now())

  requestedBy   User   @relation(fields: [requestedById], references: [id], onDelete: Cascade)
  requestedById String @unique

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  @@unique([requestedById, orgId])
}

model Org {
  id          Int          @id @default(autoincrement())
  name        String
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt
  members     UserToOrg[]
  connections Connection[]
  repos       Repo[]
  apiKeys     ApiKey[]
  isOnboarded Boolean      @default(false)
  imageUrl    String?

  /// @deprecated This property can be controlled by the environment
  /// variable `REQUIRE_APPROVAL_NEW_MEMBERS`. To ensure that we use
  /// the correct setting, use the helper function `isMemberApprovalRequired`
  /// in shared/src/utils.ts
  memberApprovalRequired Boolean @default(true)

  /// @deprecated This property can be controlled by the environment
  /// variable `AUTH_CREDENTIALS_LOGIN_ENABLED`. To ensure that we use
  /// the correct setting, use the helper function `isCredentialsLoginEnabled`
  /// in shared/src/utils.ts
  isCredentialsLoginEnabled Boolean @default(true)

  /// @deprecated This property can be controlled by the environment
  /// variable `AUTH_EMAIL_CODE_LOGIN_ENABLED`. To ensure that we use
  /// the correct setting, use the helper function `isEmailCodeLoginEnabled`
  /// in shared/src/utils.ts
  isEmailCodeLoginEnabled Boolean @default(false)

  /// @deprecated This property can be overriden by the environment
  /// variable `FORCE_ENABLE_ANONYMOUS_ACCESS`, as well as the org's
  /// available entitlements. Use the helper function `isAnonymousAccessEnabled`
  /// in web/src/lib/entitlements.ts
  isAnonymousAccessEnabled Boolean @default(false)

  /// List of pending invites to this organization
  invites Invite[]

  /// The invite id for this organization
  inviteLinkEnabled Boolean @default(false)
  inviteLinkId      String?

  audits Audit[]

  accountRequests AccountRequest[]

  searchContexts SearchContext[]

  chats Chat[]
  repoVisits RepoVisit[]

  mcpServers McpServer[]

  license License?
  servicePingEvents ServicePingEvent[]
}

model License {
  id                String      @id @default(cuid())
  orgId             Int      @unique
  org               Org      @relation(fields: [orgId], references: [id], onDelete: Cascade)
  activationCode    String
  entitlements      String[]
  seats             Int?
  status            String? /// See LicenseStatus in packages/shared/src/types.ts
  planName          String?
  unitAmount        Int?
  currency          String?
  interval          String?
  intervalCount     Int?
  nextRenewalAt     DateTime?
  nextRenewalAmount Int?
  cancelAt          DateTime?
  trialEnd          DateTime?
  hasPaymentMethod  Boolean?

  // Yearly-only fields, mirroring `yearlyTermStatus` on the lighthouse ping
  // response. All null for monthly subs and for unactivated/canceled licenses.
  yearlyTermStartedAt           DateTime?
  yearlyTermEndsAt              DateTime?
  yearlyTotalQuartersInTerm     Int?
  yearlyCurrentQuarterNumber    Int?
  yearlyCurrentQuarterStartedAt DateTime?
  yearlyCurrentQuarterEndsAt    DateTime?
  yearlyCommittedSeats          Int?
  yearlyOverageSeats            Int?
  yearlyBillableOverageSeats    Int?
  yearlyPeakSeats               Int?

  lastSyncAt        DateTime?
  lastSyncErrorCode String?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt
}

model ServicePingEvent {
  id String @id @default(cuid())
  payload Json
  createdAt DateTime @default(now())

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int
}

enum OrgRole {
  OWNER
  MEMBER
}

enum McpServerClientInfoSource {
  DYNAMIC
  STATIC
}

enum McpServerToolPermission {
  ALLOWED
  NEEDS_APPROVAL
  DISABLED
}

model UserToOrg {
  joinedAt DateTime @default(now())

  /// The linked organization
  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  /// The linked user
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String

  role OrgRole @default(MEMBER)

  @@id([orgId, userId])
}

model ApiKey {
  name String
  hash String @id @unique

  createdAt  DateTime  @default(now())
  lastUsedAt DateTime?

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  createdBy   User   @relation(fields: [createdById], references: [id], onDelete: Cascade)
  createdById String
}

model Audit {
  id        String   @id @default(cuid())
  timestamp DateTime @default(now())

  action           String
  actorId          String
  actorType        String
  targetId         String
  targetType       String
  sourcebotVersion String
  metadata         Json?

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  @@index([actorId, actorType, targetId, targetType, orgId])
  // Fast path for analytics queries – orgId is first because we assume most deployments are single tenant
  @@index([orgId, timestamp, action, actorId], map: "idx_audit_core_actions_full")
  // Fast path for analytics queries for a specific user
  @@index([actorId, timestamp], map: "idx_audit_actor_time_full")
}

// @see : https://authjs.dev/concepts/database-models#user
model User {
  id              String                 @id @default(cuid())
  name            String?
  email           String                 @unique
  hashedPassword  String?
  emailVerified   DateTime?
  image           String?
  accounts        Account[]
  orgs            UserToOrg[]
  accountRequest  AccountRequest?

  /// List of pending invites that the user has created
  invites Invite[]

  apiKeys ApiKey[]

  chats Chat[]
  sharedChats ChatAccess[]
  repoVisits RepoVisit[]

  oauthTokens        OAuthToken[]
  oauthAuthCodes     OAuthAuthorizationCode[]
  oauthRefreshTokens OAuthRefreshToken[]

  /// Per-user JWT version. Incremented to invalidate every active session for
  /// this user on their next request. Compared against the `sessionVersion`
  /// claim baked into the JWT cookie at mint time.
  sessionVersion Int @default(0)

  userMcpServers       UserMcpServer[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  /// Last time the user performed an authenticated action.
  lastActiveAt DateTime?

}

enum AccountPermissionSyncJobStatus {
  PENDING
  IN_PROGRESS
  COMPLETED
  FAILED
}

model AccountPermissionSyncJob {
  id          String                      @id @default(cuid())
  status      AccountPermissionSyncJobStatus @default(PENDING)
  createdAt   DateTime                    @default(now())
  updatedAt   DateTime                    @updatedAt
  completedAt DateTime?

  errorMessage String?

  account   Account   @relation(fields: [accountId], references: [id], onDelete: Cascade)
  accountId String
}

enum PermissionSyncSource {
  ACCOUNT_DRIVEN
  REPO_DRIVEN
}

model AccountToRepoPermission {
  createdAt DateTime @default(now())

  repo   Repo @relation(fields: [repoId], references: [id], onDelete: Cascade)
  repoId Int

  account   Account   @relation(fields: [accountId], references: [id], onDelete: Cascade)
  accountId String

  source PermissionSyncSource @default(ACCOUNT_DRIVEN)

  @@id([repoId, accountId])
}

// @see : https://authjs.dev/concepts/database-models#account
model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  providerId        String
  providerAccountId String
  providerType      String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  
  /// The issuer URL of the identity provider that issued this account (e.g., https://github.com)
  ///
  /// @note For code-host providers (like GitHub, GitLab, etc.) this will match the external_codeHostUrl
  /// of a repo record. This is important for activities like permission syncing.
  ///
  /// @note This field was introduced in v4.15.4 and will not be present for accounts created prior to this version.
  /// A lazy migration is performed inside of the `jwt` callback in auth.ts to automatically populate this field.
  issuerUrl String?

  /// List of repos that this account has access to.
  accessibleRepos AccountToRepoPermission[]

  permissionSyncJobs AccountPermissionSyncJob[]
  permissionSyncedAt DateTime?

  /// Set when an OAuth token refresh fails and the account needs to be re-linked by the user.
  /// Cleared when the user successfully re-authenticates.
  tokenRefreshErrorMessage String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([providerId, providerAccountId])
}

// @see : https://authjs.dev/concepts/database-models#verificationtoken
model VerificationToken {
  identifier String
  token      String
  expires    DateTime

  @@unique([identifier, token])
}

model RepoVisit {
  id        String   @id @default(cuid())
  /// visitedAt is updated everytime a repo is visited.
  visitedAt DateTime @default(now()) @updatedAt
  // lastPromotedAt is updated only when a repo is promoted into the top k
  // most recently viewed repositories.
  lastPromotedAt DateTime @default(now())

  repo   Repo   @relation(fields: [repoId], references: [id], onDelete: Cascade)
  repoId Int

  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  @@unique([repoId, userId])
  @@index([userId, orgId, visitedAt])
}

model Chat {
  id String @id @default(cuid())

  name String?

  createdBy            User?   @relation(fields: [createdById], references: [id], onDelete: Cascade)
  createdById          String?
  anonymousCreatorId   String? // For anonymous users, stores a session ID from a cookie

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  visibility ChatVisibility @default(PRIVATE)

  messages Json // This is a JSON array of `Message` types from @ai-sdk/ui-utils.

  sharedWith ChatAccess[]
}

/// Represents a user's access to a chat that has been shared with them.
/// Unlike Invite, this is not temporary or redeemable - it grants ongoing access.
model ChatAccess {
  id String @id @default(cuid())

  chat   Chat   @relation(fields: [chatId], references: [id], onDelete: Cascade)
  chatId String

  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String

  createdAt DateTime @default(now())

  @@unique([chatId, userId])
}

// OAuth2 Authorization Server models
// @see: https://datatracker.ietf.org/doc/html/rfc6749

/// A registered OAuth2 client application (e.g. Claude Desktop, Cursor).
/// Created via dynamic client registration (RFC 7591) at POST /api/ee/oauth/register.
model OAuthClient {
  id           String   @id @default(cuid())
  name         String
  logoUri      String?
  redirectUris String[]
  createdAt    DateTime @default(now())

  authCodes     OAuthAuthorizationCode[]
  tokens        OAuthToken[]
  refreshTokens OAuthRefreshToken[]
}

/// A short-lived authorization code issued during the OAuth2 authorization code flow.
/// Single-use and expires after 10 minutes. Stores the PKCE code challenge.
model OAuthAuthorizationCode {
  codeHash      String   @id // hashSecret(rawCode)
  clientId      String
  client        OAuthClient @relation(fields: [clientId], references: [id], onDelete: Cascade)
  userId        String
  user          User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  redirectUri   String
  codeChallenge String // BASE64URL(SHA-256(codeVerifier))
  resource      String? // RFC 8707: canonical URI of the target resource server
  expiresAt     DateTime
  createdAt     DateTime    @default(now())
}

/// An opaque OAuth2 refresh token. Single-use with rotation (RFC 6749 Section 10.4, OAuth 2.1 Section 4.3.1).
model OAuthRefreshToken {
  hash      String   @id // hashSecret(rawToken secret portion)
  clientId  String
  client    OAuthClient @relation(fields: [clientId], references: [id], onDelete: Cascade)
  userId    String
  user      User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  scope     String      @default("")
  resource  String? // RFC 8707
  expiresAt DateTime
  createdAt DateTime    @default(now())

  @@index([clientId, userId])
}

/// An opaque OAuth2 access token. The raw token is never stored — only its HMAC-SHA256 hash.
model OAuthToken {
  hash       String   @id // hashSecret(rawToken secret portion)
  clientId   String
  client     OAuthClient @relation(fields: [clientId], references: [id], onDelete: Cascade)
  userId     String
  user       User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  scope      String      @default("")
  resource   String? // RFC 8707: canonical URI of the target resource server
  expiresAt  DateTime
  createdAt  DateTime    @default(now())
  lastUsedAt DateTime?
}

/// Local cache of changelog entries fetched from the public feed at
/// `CHANGELOG_FEED_URL`. Shared across all users of an instance.
model ChangelogEntry {
  slug         String   @id
  title        String
  publishedAt  DateTime
  summary      String
  version      String
  bodyMarkdown String

  /// Updated each time the entry is upserted from the feed.
  fetchedAt DateTime @default(now()) @updatedAt

  @@index([publishedAt])
}

/// An external MCP server endpoint, unique per org.
/// Stores the dynamic client registration (client_id/client_secret) once per org.
model McpServer {
  id            String   @id @default(cuid())
  name          String   /// Org-approved display name (e.g., "Linear")
  sanitizedName String   /// Stable tool-name prefix (e.g., "linear")
  serverUrl     String   /// MCP server endpoint (e.g., "https://mcp.linear.app/mcp")

  /// Dynamic client registration result (RFC 7591) or admin-provided static OAuth client credentials.
  /// Encrypted JSON of OAuthClientInformation: { client_id, client_secret, client_id_issued_at, client_secret_expires_at }
  /// Null for DYNAMIC rows until first user in the org triggers registration.
  clientInfo       String?
  clientInfoSource McpServerClientInfoSource @default(DYNAMIC)

  org   Org @relation(fields: [orgId], references: [id], onDelete: Cascade)
  orgId Int

  userMcpServers  UserMcpServer[]
  tools           McpServerTool[]
  oauthScopes     McpServerOAuthScope[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([serverUrl, orgId])
  @@unique([orgId, sanitizedName])
}

/// OAuth scope configuration for an MCP server.
model McpServerOAuthScope {
  mcpServer   McpServer @relation(fields: [mcpServerId], references: [id], onDelete: Cascade)
  mcpServerId String
  scope       String
  enabled     Boolean   @default(false)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([mcpServerId, scope])
}

/// Tool metadata for an MCP server.
model McpServerTool {
  mcpServer   McpServer @relation(fields: [mcpServerId], references: [id], onDelete: Cascade)
  mcpServerId String
  toolName    String
  callCount   Int                     @default(0)
  permission  McpServerToolPermission @default(NEEDS_APPROVAL)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([mcpServerId, toolName])
}

/// A user's personal connection to an MCP server.
/// Stores per-user OAuth tokens and ephemeral auth-flow state.
model UserMcpServer {
  user     User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId   String

  server   McpServer @relation(fields: [serverId], references: [id], onDelete: Cascade)
  serverId String

  /// OAuth tokens (access_token, refresh_token, etc.) — encrypted JSON of OAuthTokens.
  tokens String?

  /// Absolute expiry time of the access token, computed at issuance from expires_in.
  /// Null when no tokens are stored or the provider did not include expires_in.
  tokensExpiresAt DateTime?

  /// PKCE code verifier — ephemeral, only used between redirect and callback.
  codeVerifier String?

  /// OAuth state parameter — ephemeral, for CSRF protection during auth flow.
  state String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([userId, serverId])
  @@index([serverId])
  @@index([state])
}