box-nestjs-monorepo/prisma/mongo/schema/video-sync.prisma

enum EntityType {
  VIDEO
}

enum ChangeType {
  CREATED  // type = 0
  UPDATED  // type = 1
  DELETED  // type = 2
}

model SyncState {
  id             String    @id @default(auto()) @map("_id") @db.ObjectId
  entity         EntityType
  referId        String?
  lastRunAt      DateTime?
  lastFullSyncAt DateTime?
  createdAt      Int
  updatedAt      Int

  @@unique([entity])
  @@map("syncState")
}

model SyncChangeNotification {
  id                  String     @id @default(auto()) @map("_id") @db.ObjectId
  entity              EntityType
  changeType          Int
  externalId          String?
  nonce               String?
  timestamp           BigInt?
  sign                String?
  processed           Boolean    @default(false)
  processedAt         DateTime?
  statusCode          Int?
  errorMsg            String?
  rawBody             Json?
  createdAt           Int

  // NEW: explicit notify discriminator (1=new order, 2=status update)
  notifyType          Int?
  // NEW: free-form notes / debug info
  notes               String?

  @@index([entity, externalId])
  @@map("syncChangeNotification")
}

model SyncRequestSignature {
  id         String   @id @default(auto()) @map("_id") @db.ObjectId
  nonce      String   @unique
  timestamp  BigInt
  sign       String
  endpoint   String
  createdAt  Int

  @@map("syncRequestSignature")
}

enum SyncRunType {
  FULL
  NOTIFY
  RETRY
}

enum SyncAction {
  CREATED
  UPDATED
  DELETED
  NOOP        // fetched but nothing changed
  FAILED
}

model SyncRun {
  id              String      @id @default(auto()) @map("_id") @db.ObjectId
  entity          EntityType
  type            SyncRunType
  isInitial       Boolean     @default(false)
  referIdStart    String?
  referIdEnd      String?
  pageSize        Int?
  processedCount  Int         @default(0)
  createdCount    Int         @default(0)
  updatedCount    Int         @default(0)
  deletedCount    Int         @default(0)
  failedCount     Int         @default(0)
  startedAt       DateTime
  finishedAt      DateTime?
  status          Int?        // 0 success, non-zero error code
  errorMsg        String?
  notes           String?     // NEW: free-form reason/context
  createdAt       Int
  updatedAt       Int

  records         SyncRecord[] @relation("RunToRecords")

  @@index([entity, type, startedAt])
  @@map("syncRun")
}

model SyncRecord {
  id              String       @id @default(auto()) @map("_id") @db.ObjectId
  runId           String
  run             SyncRun      @relation("RunToRecords", fields: [runId], references: [id])
  entity          EntityType
  externalId      String
  action          SyncAction
  source          SyncRunType
  processedAt     DateTime?    // CHANGED: allow pending records
  notificationId  String?

  // Snapshots / Audit
  before          Json?
  after           Json?
  diff            Json?
  checksumBefore  String?
  checksumAfter   String?

  // NEW: evidence & normalization
  payloadRaw      String?      // verbatim upstream JSON string
  payloadHash     String?      // sha256(payloadRaw)
  normalized      Json?        // our mapped DTO snapshot
  resultHash      String?      // sha256(normalized)
  mediaAudit      Json?        // { photos: [...], video?: {...} }
  hold            Boolean      @default(false) // legal hold (skip retention)
  prevRecordId    String?      @db.ObjectId    // chain to previous record for same externalId

  // Status semantics (existing)
  status          Int?         // 0=success, 1=transient_failed(pending retry), 2=permanent_failed
  errorMsg        String?
  createdAt       Int
  updatedAt       Int?


  // ---------------- NEW: Job identity & retry scheduling ----------------
  /// "SubmitOrder" | "SubmitStatus" (null for older/other record types)
  jobType         String?
  attempt         Int          @default(0)
  maxAttempts     Int          @default(3)
  /// epoch seconds for backoff scheduling
  nextRunAt       Int?
  /// De-dupe key; recommend `${jobType}:${externalId}`
  dedupeKey       String?

  // ---------------- NEW: HTTP request/response audit --------------------
  requestUrl      String?
  requestMethod   String?      // e.g., "POST"
  requestHeaders  Json?
  requestBody     Json?
  responseCode    Int?
  responseBody    String?
  latencyMs       Int?

  // ----------- NEW: Idempotency & future auth scaffolding ---------------
  idempotencyKey  String?
  securityNonce   String?
  securityTs      BigInt?
  securitySign    String?

  @@index([entity, externalId, processedAt])
  @@index([runId])
  @@index([entity, action, processedAt])
  @@index([entity, externalId, createdAt])  // quick newest lookups

  // NEW: scheduler hot-path index
  @@index([status, nextRunAt])

  // NEW: safe uniqueness for one active logical job per order per type
  // (compound unique is safe with nulls in Mongo—docs missing any field don't collide)
  @@unique([entity, externalId, jobType])

  @@map("syncRecord")
}