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[]
  backupRuns     BackupRun[]
  repairData     RepairDataCache[]

  @@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")
}

/// Labor times or fluid specs a repair data source answered for one model
/// year, kept for as long as the key that fetched them is on a plan whose
/// terms allow it (Open Labor Project's paid plans), so a vehicle looked up
/// once is not paid for again. Deleted when the key's plan drops to free, the
/// key is refused, or the workshop disconnects.
model RepairDataCache {
  id           String                 @id @default(cuid())
  /// Connector id, such as "openlaborproject".
  source       String
  /// Whose key fetched it: the connection id for a workshop's own key, or
  /// "platform" for the key the server holds, whose rows serve every
  /// workshop that uses it.
  owner        String
  /// Set for a workshop's own key, so its rows go when it disconnects.
  connectionId String?
  connection   IntegrationConnection? @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  /// labor | fluids
  kind         String
  /// "make|model|year" as the workshop wrote them, lower-cased, or
  /// "vin:<sha-256>" when only the VIN found the vehicle. No VIN is stored.
  lookupKey    String
  /// Bumped when the stored shape changes; an older row is fetched again.
  version      Int
  data         Json
  fetchedAt    DateTime               @default(now())
  createdAt    DateTime               @default(now())

  @@unique([source, owner, kind, lookupKey])
  @@index([connectionId])
  @@map("repair_data_cache")
}

/// One backup of one workshop to one destination: a scheduled or manual
/// restic run, what it produced and how it ended. Kept so the connection page
/// can show the last good backup and the cron can tell when one is overdue.
model BackupRun {
  id               String                 @id @default(cuid())
  organizationId   String
  organization     Organization           @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  /// destination: the workshop's own bucket or folder (connectionId set).
  /// platform: the repository Torqvoice keeps for the workshop on the cloud
  /// instance (no connection).
  kind             String                 @default("destination")
  connectionId     String?
  connection       IntegrationConnection? @relation(fields: [connectionId], references: [id], onDelete: Cascade)
  /// scheduled | manual | restore
  trigger          String
  /// queued | running | done | failed | skipped
  status           String                 @default("queued")
  startedAt        DateTime?
  finishedAt       DateTime?
  /// restic snapshot id: the one a backup made, or the one a restore put back.
  snapshotId       String?
  /// The snapshot of the workshop as it was taken right before a restore
  /// replaced it, so a restore can itself be undone.
  safetySnapshotId String?
  /// Who asked: a member's user id, or null for a run the schedule or
  /// support (through torqvoice.com) started.
  requestedBy      String?
  /// Why support restored, as typed on torqvoice.com; shown to the workshop.
  reason           String?
  filesNew         Int?
  bytesAdded       BigInt?
  totalBytes       BigInt?
  error            String?
  createdAt        DateTime               @default(now())

  @@index([connectionId, createdAt])
  @@index([organizationId, kind, createdAt])
  @@index([status, createdAt])
  @@map("backup_runs")
}

/// The backups Torqvoice runs for a workshop on the cloud instance: one
/// restic repository per workshop in a bucket of ours, hourly, Pro and up.
/// One row per workshop that has ever been in the cycle; the run rows carry
/// the history, this row carries where the schedule stands.
model PlatformBackup {
  organizationId     String       @id
  organization       Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  lastScheduledAt    DateTime?
  lastGoodAt         DateTime?
  lastPruneAt        DateTime?
  lastCheckAt        DateTime?
  /// One retry half an hour after a failed or skipped run.
  retryAt            DateTime?
  /// Set while the workshop's plan no longer includes platform backups.
  /// Runs stop; the repository is kept for a grace period, then forgotten.
  pausedAt           DateTime?
  /// The repository's snapshots were forgotten after the grace period.
  purgedAt           DateTime?
  repositorySize     BigInt?
  /// The restore points in the repository after the last run, newest first:
  /// [{ id, shortId, time, bytes, trigger, preRestore }]. What the Backups
  /// card and torqvoice.com list, so neither has to open the repository.
  snapshots          Json         @default("[]")
  lastFailureMailAt  DateTime?
  lastOverdueAlertAt DateTime?
  createdAt          DateTime     @default(now())
  updatedAt          DateTime     @updatedAt

  @@index([pausedAt])
  @@map("platform_backups")
}