torqvoice/prisma/schema/integrations.prisma

/// One installed connector for one organisation: the account it is linked
/// to, its sealed credentials, and the settings the workshop chose. Rows in
/// the other integration tables hang off this one.
model IntegrationConnection {
  id                  String       @id @default(cuid())
  organizationId      String
  organization        Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  /// Connector id from the registry, for example 'google-calendar'.
  connectorId         String
  /// pending | active | error | disconnected
  status              String       @default("pending")
  label               String?
  /// Tokens or keys as one sealed string; only the vault reads it.
  credentials         String?
  settings            Json         @default("{}")
  /// Connector-owned state such as sync cursors and last-run times.
  state               Json         @default("{}")
  externalAccountId   String?
  externalAccountName String?
  scopes              String?
  /// OAuth handshake nonce, set while the connection is pending.
  oauthState          String?      @unique
  lastHealthAt        DateTime?
  lastSyncAt          DateTime?
  lastError           String?
  createdById         String
  createdAt           DateTime     @default(now())
  updatedAt           DateTime     @updatedAt

  links          IntegrationLink[]
  jobs           IntegrationJob[]
  logs           IntegrationLog[]
  externalEvents ExternalCalendarEvent[]

  @@unique([organizationId, connectorId])
  @@index([organizationId])
  @@map("integration_connections")
}

/// The remote id a local record has in one connected system, which is what
/// makes every push an update instead of a duplicate.
model IntegrationLink {
  id              String                @id @default(cuid())
  connectionId    String
  connection      IntegrationConnection @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  /// Local model name, for example 'ServiceRecord'.
  entityType      String
  entityId        String
  remoteId        String
  remoteUrl       String?
  /// Connector-owned extras, such as a meeting link.
  metadata        Json?
  checksum        String?
  remoteUpdatedAt DateTime?
  lastSyncedAt    DateTime              @default(now())
  createdAt       DateTime              @default(now())

  @@unique([connectionId, entityType, entityId])
  @@index([connectionId, remoteId])
  @@index([entityType, entityId])
  @@map("integration_links")
}

/// A unit of work for a connector, claimed and retried by the cron.
model IntegrationJob {
  id             String                @id @default(cuid())
  connectionId   String
  connection     IntegrationConnection @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  organizationId String
  kind           String
  payload        Json?
  /// Set while the job is pending so the same work is queued once; cleared when it runs.
  idempotencyKey String?
  /// queued | running | done | failed | dead
  status         String                @default("queued")
  attempts       Int                   @default(0)
  maxAttempts    Int                   @default(5)
  runAfter       DateTime              @default(now())
  lockedAt       DateTime?
  startedAt      DateTime?
  finishedAt     DateTime?
  error          String?
  createdAt      DateTime              @default(now())
  updatedAt      DateTime              @updatedAt

  @@unique([connectionId, idempotencyKey])
  @@index([status, runAfter])
  @@index([connectionId, createdAt])
  @@index([organizationId, createdAt])
  @@map("integration_jobs")
}

/// What a connector did and why it failed, per connection, kept for 30 days.
model IntegrationLog {
  id           String                @id @default(cuid())
  connectionId String
  connection   IntegrationConnection @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  jobId        String?
  /// info | warn | error
  level        String                @default("info")
  message      String
  details      Json?
  createdAt    DateTime              @default(now())

  @@index([connectionId, createdAt])
  @@map("integration_logs")
}

/// Busy time pulled from a connected calendar, shown read-only on the
/// workshop calendar. A cache: rebuilt by the pull job, never edited.
model ExternalCalendarEvent {
  id              String                @id @default(cuid())
  connectionId    String
  connection      IntegrationConnection @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  organizationId  String
  organization    Organization          @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  remoteId        String
  calendarId      String
  title           String
  startAt         DateTime
  endAt           DateTime
  allDay          Boolean               @default(false)
  /// The event's page in the vendor's own calendar, when the vendor gives one.
  remoteUrl       String?
  remoteUpdatedAt DateTime?
  createdAt       DateTime              @default(now())
  updatedAt       DateTime              @updatedAt

  @@unique([connectionId, remoteId])
  @@index([organizationId, startAt])
  @@map("external_calendar_events")
}

/// What a safety authority knows about one model year: recalls, owner
/// complaints and crash ratings, as the connector shaped them. Public data,
/// keyed by model rather than by workshop or vehicle, so a 2003 Accord is
/// fetched once for everyone. A cache: rebuilt by the connector, never edited.
model VehicleSafetyReport {
  id             String   @id @default(cuid())
  /// Connector id, such as "nhtsa".
  source         String
  /// Make, model and year as the workshop wrote them, upper-cased and trimmed.
  make           String
  model          String
  year           Int
  /// False when the authority answered but had no such model.
  found          Boolean  @default(true)
  data           Json
  /// Counts lifted out of data so a list can show a badge without parsing it.
  recallCount    Int      @default(0)
  complaintCount Int      @default(0)
  fetchedAt      DateTime @default(now())
  createdAt      DateTime @default(now())

  @@unique([source, make, model, year])
  @@index([fetchedAt])
  @@map("vehicle_safety_reports")
}