`prisma/schema.prisma`

489 lines
// GovArbitrage — data model
// Money is stored as Decimal(14,2). Scores are Float 0..100. Probabilities Float 0..1.

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

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

// ------------------------------------------------------------------ enums

enum AuctionSource {
  GOVDEALS
  PUBLIC_SURPLUS
  GSA_AUCTIONS
  COUNTY
  STATE_SURPLUS
  UNIVERSITY_SURPLUS
  MUNICIBID
  BID4ASSETS
  GOINDUSTRY
  NETWORK_INTL
  GRAYS_AU
  GOVPLANET
  CSV
  EXTENSION
  OTHER
}

enum Condition {
  NEW
  LIKE_NEW
  USED_GOOD
  USED_FAIR
  FOR_PARTS
  UNKNOWN
}

// Lifecycle of a listing on its SOURCE site (not Steve's buyer workflow —
// that's AuctionOutcome). ACTIVE = still live/biddable; ENDED = auction closed
// (past closingAt); REMOVED = pulled/sold/404 on the source. Only ACTIVE
// listings show on the live grid.
enum ListingStatus {
  ACTIVE
  ENDED
  REMOVED
}

enum ResearchStatus {
  PENDING
  QUEUED
  IN_PROGRESS
  COMPLETE
  FAILED
}

enum RiskLevel {
  LOW
  MEDIUM
  HIGH
}

enum DropShipFeasibility {
  EASY
  MODERATE
  DIFFICULT
  INFEASIBLE
}

enum ScoreProfile {
  OVERALL_OPPORTUNITY
  BEST_ARBITRAGE
  QUICK_FLIP
  COLLECTOR
  LOCAL_PICKUP
  EASY_FREIGHT
  PARTS_ONLY
  HIGH_CONFIDENCE
  HIGH_PROFIT
}

enum ComparableKind {
  SOLD
  ACTIVE
  RETAIL
}

enum OutcomeStatus {
  WATCHING
  BID_PLACED
  WON
  LOST
  ABANDONED
}

enum Role {
  ADMIN
  ANALYST
  VIEWER
}

// Subscription tier for the paid-alerts SaaS. FREE is the default for any new
// account; STANDARD/PREMIUM are set by a (TEST) Stripe subscription webhook.
enum Tier {
  FREE
  STANDARD
  PREMIUM
}

// Double-opt-in newsletter lifecycle. PENDING until the confirm link is
// clicked; UNSUBSCRIBED rows are kept (suppression list) so a re-subscribe
// re-issues a fresh confirm rather than silently re-adding.
enum SubscriberStatus {
  PENDING
  CONFIRMED
  UNSUBSCRIBED
}

enum EventType {
  IMPORTED
  RESEARCH_STARTED
  RESEARCH_COMPLETED
  RESEARCH_FAILED
  SCORED
  PRICE_CHANGED
  NOTE_ADDED
  BUYER_LEAD_ADDED
  OUTCOME_CHANGED
  STATUS_CHANGE
  HOT_ALERTED
  MANUAL_EDIT
}

// ------------------------------------------------------------------ core

model Listing {
  id              String        @id @default(cuid())
  source          AuctionSource
  sourceAuctionId String // the auction/lot number on the source site
  sourceUrl       String?
  title           String
  description     String?
  category        String?

  // AI/OCR/vision-identified product attributes
  manufacturer String?
  model        String?
  serialNumber String?
  sku          String?
  year         Int?
  condition    Condition @default(UNKNOWN)
  quantity     Int       @default(1)
  accessories  String?
  missingParts String?
  weightLbs    Float?
  dimensions   String? // "L x W x H in"
  auctionTerms String?

  // location + logistics
  locationCity  String?
  locationState String?
  locationZip   String?

  // live auction state
  currentBid   Decimal   @default(0) @db.Decimal(14, 2)
  bidCount     Int       @default(0)
  closingAt    DateTime?
  imageUrls    String[]  @default([])
  identifiedBy String? // "ollama:qwen2.5vl" | "manual" | "extension"
  identifyRaw  Json? // raw model output for auditability

  researchStatus ResearchStatus @default(PENDING)

  // source-liveness lifecycle + fast removal
  listingStatus  ListingStatus @default(ACTIVE)
  endedAt        DateTime? // when marked ENDED/REMOVED
  lastSeenAt     DateTime  @default(now()) // last time confirmed live on source
  livenessCheckedAt DateTime? // last Tier-2 network re-verify

  // HOT DEAL alerting (dedup so we alert each deal once)
  hotAlertedAt   DateTime?

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

  research       Research?
  costBreakdown  CostBreakdown?
  scores         Score[]
  comparables    Comparable[]
  notes          Note[]
  events         ListingEvent[]
  buyerPage      BuyerInterestPage?
  buyerLeads     BuyerLead[]
  outcome        AuctionOutcome?

  @@unique([source, sourceAuctionId])
  @@index([researchStatus])
  @@index([category])
  @@index([closingAt])
  @@index([listingStatus])
  @@index([hotAlertedAt])
}

// Valuation research output for a listing
model Research {
  id        String   @id @default(cuid())
  listingId String   @unique
  listing   Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)

  // retail estimates
  newRetail        Decimal? @db.Decimal(14, 2)
  newReplacement   Decimal? @db.Decimal(14, 2)
  avgRetail        Decimal? @db.Decimal(14, 2)

  // used market
  usedSoldPrice    Decimal? @db.Decimal(14, 2)
  usedAskingPrice  Decimal? @db.Decimal(14, 2)
  usedLow          Decimal? @db.Decimal(14, 2)
  usedHigh         Decimal? @db.Decimal(14, 2)

  // channel values
  wholesaleValue   Decimal? @db.Decimal(14, 2)
  liquidationValue Decimal? @db.Decimal(14, 2)
  sellTodayValue   Decimal? @db.Decimal(14, 2)

  // time-horizon values
  value7Day        Decimal? @db.Decimal(14, 2)
  value30Day       Decimal? @db.Decimal(14, 2)
  value90Day       Decimal? @db.Decimal(14, 2)

  // outcome estimates
  expectedSalePrice Decimal? @db.Decimal(14, 2)
  probabilityOfSale Float? // 0..1
  daysUntilSold     Int?
  confidenceScore   Float? // 0..100

  sources   Json? // [{name,url,kind,price}]
  summary   String?
  model     String? // which model produced this
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// All acquisition + resale costs for a listing
model CostBreakdown {
  id        String  @id @default(cuid())
  listingId String  @unique
  listing   Listing @relation(fields: [listingId], references: [id], onDelete: Cascade)

  winningBid       Decimal @default(0) @db.Decimal(14, 2)
  buyerPremium     Decimal @default(0) @db.Decimal(14, 2)
  salesTax         Decimal @default(0) @db.Decimal(14, 2)
  shipping         Decimal @default(0) @db.Decimal(14, 2)
  freight          Decimal @default(0) @db.Decimal(14, 2)
  insurance        Decimal @default(0) @db.Decimal(14, 2)
  packing          Decimal @default(0) @db.Decimal(14, 2)
  pickupLabor      Decimal @default(0) @db.Decimal(14, 2)
  testing          Decimal @default(0) @db.Decimal(14, 2)
  repairs          Decimal @default(0) @db.Decimal(14, 2)
  certification    Decimal @default(0) @db.Decimal(14, 2)
  marketplaceFees  Decimal @default(0) @db.Decimal(14, 2)
  paymentFees      Decimal @default(0) @db.Decimal(14, 2)
  storage          Decimal @default(0) @db.Decimal(14, 2)
  photography      Decimal @default(0) @db.Decimal(14, 2)
  listingLabor     Decimal @default(0) @db.Decimal(14, 2)

  expectedReturns  Decimal @default(0) @db.Decimal(14, 2)
  totalInvestment  Decimal @default(0) @db.Decimal(14, 2)
  expectedNetProfit Decimal @default(0) @db.Decimal(14, 2)
  roi              Float   @default(0) // ratio, e.g. 0.42 = 42%
  annualizedReturn Float   @default(0)
  recommendedMaxBid Decimal @default(0) @db.Decimal(14, 2)

  assumptions Json? // basis for each estimate (freight table row, fee %, etc.)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

// One row per scoring profile for a listing, each with a human explanation
model Score {
  id        String       @id @default(cuid())
  listingId String
  listing   Listing      @relation(fields: [listingId], references: [id], onDelete: Cascade)
  profile   ScoreProfile
  value     Float // 0..100

  // component sub-scores (0..100) the profile is composed from
  arbitrage   Float @default(0)
  demand      Float @default(0)
  velocity    Float @default(0)
  logistics   Float @default(0)
  condition   Float @default(0)
  competition Float @default(0)
  buyer       Float @default(0)

  risk        RiskLevel           @default(MEDIUM)
  dropShip    DropShipFeasibility @default(MODERATE)
  explanation String // "why it got this score", plain English
  factors     Json? // structured [{label, weight, contribution}]

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

  @@unique([listingId, profile])
  @@index([profile, value])
}

model Comparable {
  id        String         @id @default(cuid())
  listingId String
  listing   Listing        @relation(fields: [listingId], references: [id], onDelete: Cascade)
  kind      ComparableKind
  title     String
  price     Decimal        @db.Decimal(14, 2)
  url       String?
  source    String? // "ebay" | "amazon" | manufacturer domain
  soldAt    DateTime?
  createdAt DateTime       @default(now())

  @@index([listingId, kind])
}

// ------------------------------------------------------------------ buyer workflow (compliant)

model BuyerInterestPage {
  id            String   @id @default(cuid())
  listingId     String   @unique
  listing       Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)
  slug          String   @unique
  headline      String
  // Compliance: contingent offers only, no ownership representation pre-win.
  contingent    Boolean  @default(true)
  published     Boolean  @default(false)
  estDelivered  Decimal? @db.Decimal(14, 2)
  disclaimer    String   @default("This item is not yet owned. Any offer is contingent on winning the government auction and is non-binding until the auction is won and the item is in hand.")
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
}

model BuyerLead {
  id           String   @id @default(cuid())
  listingId    String
  listing      Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)
  name         String
  email        String
  phone        String?
  offer        Decimal? @db.Decimal(14, 2)
  contingent   Boolean  @default(true)
  notes        String?
  createdAt    DateTime @default(now())

  @@index([listingId])
}

model AuctionOutcome {
  id          String        @id @default(cuid())
  listingId   String        @unique
  listing     Listing       @relation(fields: [listingId], references: [id], onDelete: Cascade)
  status      OutcomeStatus @default(WATCHING)
  maxBidSet   Decimal?      @db.Decimal(14, 2)
  wonPrice    Decimal?      @db.Decimal(14, 2)
  actualSale  Decimal?      @db.Decimal(14, 2)
  soldAt      DateTime?
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt
}

// ------------------------------------------------------------------ workspace

model Note {
  id        String   @id @default(cuid())
  listingId String
  listing   Listing  @relation(fields: [listingId], references: [id], onDelete: Cascade)
  body      String
  author    String?
  createdAt DateTime @default(now())

  @@index([listingId])
}

model ListingEvent {
  id        String    @id @default(cuid())
  listingId String
  listing   Listing   @relation(fields: [listingId], references: [id], onDelete: Cascade)
  type      EventType
  message   String
  meta      Json?
  createdAt DateTime  @default(now())

  @@index([listingId, createdAt])
}

model SavedView {
  id        String   @id @default(cuid())
  name      String
  ownerId   String?
  config    Json // { filters, sort, columns, pinned, grouping }
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// ------------------------------------------------------------------ security

model User {
  id           String          @id @default(cuid())
  email        String          @unique
  name         String?
  passwordHash String
  role         Role            @default(ANALYST)
  // Paid-alerts SaaS subscription
  tier                 Tier    @default(FREE)
  stripeCustomerId     String?
  stripeSubscriptionId String?
  createdAt    DateTime        @default(now())
  credentials  ApiCredential[]
  auditLogs    AuditLog[]
}

// Public newsletter subscriber for the twice-daily Top-10 digest.
// email is stored lowercase; tokens are single-purpose URL-safe secrets.
model Subscriber {
  id               String           @id @default(cuid())
  email            String           @unique
  status           SubscriberStatus @default(PENDING)
  confirmToken     String           @unique
  unsubscribeToken String           @unique
  source           String           @default("newsletter_page")
  createdAt        DateTime         @default(now())
  confirmedAt      DateTime?
  unsubscribedAt   DateTime?

  @@index([status])
}

model ApiCredential {
  id        String   @id @default(cuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  provider  String // "anthropic" | "ebay" | ...
  // Stored encrypted at rest (AES-256-GCM). Never returned to the client.
  ciphertext String
  iv         String
  authTag    String
  createdAt DateTime @default(now())

  @@unique([userId, provider])
}

model AuditLog {
  id        String   @id @default(cuid())
  userId    String?
  user      User?    @relation(fields: [userId], references: [id], onDelete: SetNull)
  action    String
  entity    String?
  entityId  String?
  ip        String?
  meta      Json?
  createdAt DateTime @default(now())

  @@index([createdAt])
  @@index([action])
}

// ------------------------------------------------------------------ digest archive

enum DigestSlot {
  AM
  PM
}

// Frozen record of each digest run. The public /deals archive pages render
// these snapshots so archive URLs stay stable and indexable instead of
// re-querying live listings that expire out from under a crawled page.
model DigestSnapshot {
  id        String     @id @default(cuid())
  date      String // YYYY-MM-DD in America/Los_Angeles (the digest's home timezone)
  slot      DigestSlot
  dealsJson Json // public-safe serialized Top-10 (see src/lib/digest-snapshot.ts)
  dealCount Int
  isSeed    Boolean    @default(false) // fabricated demo data — excluded from all public reads
  createdAt DateTime   @default(now())

  @@unique([date, slot])
  @@index([date])
}