eks_relay/prisma/schema.prisma

// EKS Support Relay — persistence layer.
// SQLite for now; the schema deliberately avoids SQLite-only features so a
// later move to PostgreSQL only needs a datasource/provider change.

generator client {
  provider = "prisma-client-js"
  output   = "../node_modules/.prisma/client"
}

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

/// One row per inbound source message. Primary idempotency guard for the
/// Chatwoot webhook: (source, messageId) is unique.
model ProcessedMessage {
  id             String   @id @default(cuid())
  source         String
  messageId      String
  conversationId Int
  /// queued | processing | replied | skipped | ticket | failed | spam
  status         String
  reason         String?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  @@unique([source, messageId])
  @@index([conversationId])
  @@index([status])
}

/// Async work item. The webhook persists a job and returns 202; the in-process
/// worker picks it up, with retries and exponential backoff.
model Job {
  id          String    @id @default(cuid())
  /// chatwoot_message
  type        String
  /// queued | processing | done | failed | dead
  status      String    @default("queued")
  attempts    Int       @default(0)
  maxAttempts Int       @default(3)
  /// Redacted, minimal payload — never full secrets.
  payloadJson String
  /// Redacted error text.
  lastError   String?
  runAfter    DateTime  @default(now())
  startedAt   DateTime?
  finishedAt  DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([status, runAfter])
}

/// Handoff record. conversationId is unique so repeated new_ticket calls for
/// the same conversation always resolve to the same ticket number.
model Ticket {
  id             String   @id @default(cuid())
  conversationId Int      @unique
  ticketNumber   String
  reason         String?
  createdAt      DateTime @default(now())
}

/// Non-secret audit trail of decisions taken per message/conversation.
model AuditEvent {
  id             String   @id @default(cuid())
  conversationId Int?
  messageId      String?
  /// Set when the event belongs to a queued job, so the ops panel can join the
  /// audit trail to the queue without parsing summary text.
  jobId          String?
  eventType      String
  summary        String
  /// Redacted JSON metadata.
  metaJson       String?
  createdAt      DateTime @default(now())

  @@index([conversationId])
  @@index([eventType])
  @@index([createdAt])
  @@index([jobId])
}

/// Cached AI translations for agent UI. Keyed by text hash rather than raw text
/// to avoid duplicating customer content unnecessarily in lookup keys.
model TranslationCache {
  id              String   @id @default(cuid())
  conversationId  Int?
  messageId       String?
  sourceHash      String
  sourceLanguage  String?
  targetLanguage  String
  translatedText  String
  provider        String
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  @@unique([sourceHash, targetLanguage])
  @@index([conversationId])
  @@index([messageId])
}