← back to Homesonspec

packages/database/prisma/schema.prisma

560 lines

// HomesOnSpec data model.
//
// Two strictly separated sides:
//   PUBLISHED side — consumer-facing tables. Only the publish pipeline stage
//     writes here; collectors and extractors never do.
//   RAW/PIPELINE side — source registry, snapshots, staged records, evidence,
//     validation events, review queue. Everything upstream of publish.
//
// Every published fact must be traceable to SourceEvidence rows. Missing
// values stay null — they are never inferred.

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

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

// ───────────────────────── PUBLISHED SIDE ─────────────────────────

model Builder {
  id               String    @id @default(cuid())
  slug             String    @unique
  name             String
  legalName        String?
  websiteUrl       String?
  logoRightsStatus String?   // e.g. "none", "licensed", "builder-provided"
  coverageArea     String?
  contactEmail     String?
  contactPhone     String?
  partnershipStatus String?  // never implies sponsorship without a commercial relationship
  isDemo           Boolean   @default(false)
  createdAt        DateTime  @default(now())
  updatedAt        DateTime  @updatedAt

  divisions   Division[]
  communities Community[]
  floorPlans  FloorPlan[]
  homes       InventoryHome[]
  incentives  Incentive[]
  sources     SourceRegistry[]
}

model Division {
  id        String   @id @default(cuid())
  builderId String
  name      String
  region    String?
  states    String[]
  createdAt DateTime @default(now())

  builder     Builder     @relation(fields: [builderId], references: [id])
  communities Community[]

  @@index([builderId])
}

model Community {
  id             String   @id @default(cuid())
  builderId      String
  divisionId     String?
  slug           String   @unique
  name           String
  description    String?  // only with confirmed media/text rights
  street         String?
  city           String
  state          String
  zip            String
  county         String?
  metro          String?
  lat            Decimal? @db.Decimal(9, 6)
  lon            Decimal? @db.Decimal(9, 6)
  status         CommunityStatus @default(ACTIVE)
  hoaFeeMonthly  Decimal? @db.Decimal(8, 2)
  hoaRequired    Boolean?
  schoolDistrict String?  // objective district name only — no subjective ratings
  ageRestricted  Boolean  @default(false)
  salesPhone     String?  // community sales-office phone (the "broker" contact on listings)
  amenities      Json?
  searchText     String?  // app-maintained: "name city state zip metro county" for trgm
  priceMin       Decimal? @db.Decimal(12, 2) // cached rollup from active homes
  priceMax       Decimal? @db.Decimal(12, 2)
  sourceUrl      String?
  isDemo         Boolean  @default(false)
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  builder     Builder      @relation(fields: [builderId], references: [id])
  division    Division?    @relation(fields: [divisionId], references: [id])
  floorPlans  FloorPlan[]
  homes       InventoryHome[]
  lots        Lot[]
  incentives  Incentive[]
  salesOffices SalesOffice[]

  @@index([state, city])
  @@index([zip])
  @@index([lat])
  @@index([lon])
}

enum CommunityStatus {
  ACTIVE
  COMING_SOON
  SOLD_OUT
  CLOSED
}

enum HomeType {
  SINGLE_FAMILY
  TOWNHOME
  CONDO
  DUPLEX
  OTHER
}

model FloorPlan {
  id             String   @id @default(cuid())
  communityId    String
  builderId      String
  builderPlanId  String?
  name           String
  beds           Int?
  bedsMax        Int?
  bathsFull      Int?
  bathsHalf      Int?
  sqft           Int?
  sqftMax        Int?
  stories        Int?
  garageSpaces   Int?
  basePrice      Decimal? @db.Decimal(12, 2)
  homeType       HomeType @default(SINGLE_FAMILY)
  planMediaRights String? // registry-confirmed rights before any media renders
  sourceUrl      String?
  isDemo         Boolean  @default(false)
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  community Community @relation(fields: [communityId], references: [id])
  builder   Builder   @relation(fields: [builderId], references: [id])
  homes     InventoryHome[]

  @@index([communityId])
}

enum ConstructionStatus {
  PLANNED
  UNDER_CONSTRUCTION
  MOVE_IN_READY
}

enum PublishStatus {
  PUBLISHED
  INACTIVE
}

enum Freshness {
  FRESH
  AGING
  REVIEW_REQUIRED
  STALE
  INACTIVE
}

enum VerificationLabel {
  VERIFIED_FROM_BUILDER   // direct feed / partnership
  BUILDER_WEBSITE         // verified from builder website
  BUILDER_SUBMITTED
  NEEDS_REVERIFICATION
  NO_LONGER_AVAILABLE
  DEMONSTRATION           // synthetic demo inventory — never a production claim
}

model InventoryHome {
  id                 String   @id @default(cuid())
  canonicalKey       String   @unique // sha256(builder|community|addr|lot|invId|lat5|lon5|plan)
  communityId        String
  builderId          String
  floorPlanId        String?
  builderInventoryId String?
  street             String?
  city               String
  state              String
  zip                String
  normalizedAddress  String?
  lotNumber          String?
  lat                Decimal? @db.Decimal(9, 6)
  lon                Decimal? @db.Decimal(9, 6)
  price              Decimal? @db.Decimal(12, 2) // null when source omits — never guessed
  previousPrice      Decimal? @db.Decimal(12, 2)
  beds               Int?
  bathsTotal         Decimal? @db.Decimal(3, 1)
  sqft               Int?
  stories            Int?
  garageSpaces       Int?
  images             String[] @default([]) // builder-supplied listing photo URLs (largest variant)
  homeType           HomeType @default(SINGLE_FAMILY)
  constructionStatus ConstructionStatus @default(UNDER_CONSTRUCTION)
  estCompletionDate  DateTime?
  availabilityStatus String?
  status             PublishStatus @default(PUBLISHED)
  freshness          Freshness     @default(FRESH)
  verificationLabel  VerificationLabel
  lastVerifiedAt     DateTime?
  sourceId           String?
  sourceUrl          String?
  sourceUpdatedAt    DateTime?
  publishedAt        DateTime @default(now())
  isDemo             Boolean  @default(false)
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  community Community  @relation(fields: [communityId], references: [id])
  builder   Builder    @relation(fields: [builderId], references: [id])
  floorPlan FloorPlan? @relation(fields: [floorPlanId], references: [id])
  source    SourceRegistry? @relation(fields: [sourceId], references: [id])
  incentives Incentive[]
  leads      Lead[]

  @@index([lat])
  @@index([lon])
  @@index([price])
  @@index([beds])
  @@index([sqft])
  @@index([status, freshness])
  @@index([communityId])
  @@index([builderId])
  @@index([constructionStatus])
}

model Lot {
  id          String   @id @default(cuid())
  communityId String
  lotNumber   String
  street      String?
  sizeSqft    Int?
  status      String?  // available / reserved / sold
  price       Decimal? @db.Decimal(12, 2)
  isDemo      Boolean  @default(false)
  createdAt   DateTime @default(now())

  community Community @relation(fields: [communityId], references: [id])

  @@unique([communityId, lotNumber])
}

enum IncentiveScope {
  BUILDER
  COMMUNITY
  HOME
}

model Incentive {
  id             String   @id @default(cuid())
  scope          IncentiveScope
  builderId      String?
  communityId    String?
  homeId         String?
  title          String
  description    String?
  valueUsd       Decimal? @db.Decimal(12, 2)
  expiresAt      DateTime?
  evergreenLabel String?  // "expiration not provided" when source omits a date
  sourceId       String?
  sourceUrl      String?
  isDemo         Boolean  @default(false)
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  builder   Builder?       @relation(fields: [builderId], references: [id])
  community Community?     @relation(fields: [communityId], references: [id])
  home      InventoryHome? @relation(fields: [homeId], references: [id])

  @@index([communityId])
  @@index([homeId])
  @@index([expiresAt])
}

model SalesOffice {
  id          String  @id @default(cuid())
  communityId String
  street      String?
  city        String?
  state       String?
  zip         String?
  phone       String?
  hours       Json?
  isDemo      Boolean @default(false)
  createdAt   DateTime @default(now())

  community Community @relation(fields: [communityId], references: [id])

  @@index([communityId])
}

model Lead {
  id          String   @id @default(cuid())
  homeId      String?
  communityId String?
  name        String
  email       String
  phone       String?
  message     String?
  createdAt   DateTime @default(now())

  home InventoryHome? @relation(fields: [homeId], references: [id])

  @@index([createdAt])
}

// Schema present; delivery deferred (no sender in v1).
model SavedSearch {
  id        String   @id @default(cuid())
  email     String
  criteria  Json
  createdAt DateTime @default(now())
}

model Alert {
  id        String   @id @default(cuid())
  email     String
  homeId    String?
  kind      String   // price_change | availability
  active    Boolean  @default(true)
  createdAt DateTime @default(now())
}

// ─────────────────────── RAW / PIPELINE SIDE ───────────────────────

enum CollectionMethod {
  FEED       // builder-provided feed or API
  CRAWL      // permissioned crawling only
  MANUAL     // builder-submitted / hand-entered
  FIXTURE    // local fixture files (tests + demo pipeline)
  SYNTHETIC  // generated demonstration data
}

enum MediaRights {
  NONE       // facts only — no photos, renderings, plans, or descriptions
  LINK_ONLY
  LICENSED
}

enum SourceHealth {
  HEALTHY
  DEGRADED
  FAILING
  PAUSED
}

model SourceRegistry {
  id                String   @id @default(cuid())
  key               String   @unique // adapter key, e.g. "meridian-homes-fixtures"
  name              String
  builderId         String?
  baseUrl           String?
  collectionMethod  CollectionMethod
  termsUrl          String?
  permissions       Json?    // recorded permissions / partnership terms
  attribution       String?  // required attribution text, if any
  mediaRights       MediaRights @default(NONE)
  rateLimitRpm      Int?
  freshIntervalHours Int     @default(24)
  agingIntervalHours Int     @default(72)
  staleIntervalHours Int     @default(168) // beyond this a home is STALE + needs re-verification
  health            SourceHealth @default(HEALTHY)
  active            Boolean  @default(true)
  // Verification-engine run tracking (Stage 3): drives source-health auto-pause.
  lastRunAt              DateTime?
  lastSuccessAt          DateTime?
  consecutiveFailures    Int     @default(0)
  maxConsecutiveFailures Int     @default(3) // trip to PAUSED once reached
  notes             String?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  builder   Builder? @relation(fields: [builderId], references: [id])
  snapshots RawSnapshot[]
  staged    StagedRecord[]
  homes     InventoryHome[]
  runs      SourceRun[]
}

// Append-only log of every verification-engine / pipeline run per source.
// The health evaluator reads the recent tail to decide auto-pause / recovery.
model SourceRun {
  id           String   @id @default(cuid())
  sourceId     String
  kind         String   @default("pipeline") // "pipeline" | "freshness" | "refresh"
  ok           Boolean
  pages        Int      @default(0)
  changed      Int      @default(0)
  published    Int      @default(0)
  needsReview  Int      @default(0)
  rejected     Int      @default(0)
  errorCount   Int      @default(0)
  message      String?
  durationMs   Int?
  startedAt    DateTime @default(now())

  source SourceRegistry @relation(fields: [sourceId], references: [id])

  @@index([sourceId, startedAt])
}

model RawSnapshot {
  id          String   @id @default(cuid())
  sourceId    String
  url         String
  retrievedAt DateTime @default(now())
  contentHash String   // sha256 of body; new hash on same url = change detected
  storagePath String   // body on disk under var/snapshots/, never in PG
  contentType String?
  httpStatus  Int?
  createdAt   DateTime @default(now())

  source SourceRegistry @relation(fields: [sourceId], references: [id])
  staged StagedRecord[]

  @@unique([sourceId, contentHash])
  @@index([sourceId, retrievedAt])
}

enum EntityType {
  BUILDER
  COMMUNITY
  FLOOR_PLAN
  INVENTORY_HOME
  INCENTIVE
  SALES_OFFICE
  LOT
}

enum StagedStatus {
  EXTRACTED
  VALIDATED
  NEEDS_REVIEW
  APPROVED
  REJECTED
  PUBLISHED
}

model StagedRecord {
  id               String   @id @default(cuid())
  sourceId         String
  snapshotId       String?
  entityType       EntityType
  canonicalKey     String
  payload          Json     // per-field FieldValue envelope {value,raw,evidenceText,sourceUrl,confidence}
  hints            Json?    // CanonicalHints captured at extract time (builderSlug, communityName, address…)
  extractorVersion String
  status           StagedStatus @default(EXTRACTED)
  publishedEntityId String?
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt

  source   SourceRegistry @relation(fields: [sourceId], references: [id])
  snapshot RawSnapshot?   @relation(fields: [snapshotId], references: [id])
  evidence SourceEvidence[]
  events   ValidationEvent[]
  reviews  ReviewItem[]

  @@unique([sourceId, snapshotId, canonicalKey]) // one staged row per (source, snapshot, home) → FORCE_REEXTRACT is idempotent
  @@index([status])
  @@index([canonicalKey])
}

model SourceEvidence {
  id               String   @id @default(cuid())
  stagedRecordId   String?
  entityType       EntityType?
  entityId         String?  // set at publish so consumer pages can cite evidence
  field            String
  sourceUrl        String
  retrievedAt      DateTime
  contentHash      String?
  extractorVersion String
  rawValue         String?
  normalizedValue  String?
  evidenceText     String?
  confidence       Float
  createdAt        DateTime @default(now())

  stagedRecord StagedRecord? @relation(fields: [stagedRecordId], references: [id])

  @@index([entityType, entityId])
  @@index([stagedRecordId])
}

model ValidationEvent {
  id               String   @id @default(cuid())
  stagedRecordId   String
  ruleId           String
  severity         String   // error | review | warn
  passed           Boolean
  message          String?
  details          Json?
  validatorVersion String
  runAt            DateTime @default(now())

  stagedRecord StagedRecord @relation(fields: [stagedRecordId], references: [id])

  @@index([stagedRecordId])
  @@index([ruleId, passed])
}

model ChangeEvent {
  id             String   @id @default(cuid())
  entityType     EntityType
  entityId       String
  field          String
  oldValue       String?
  newValue       String?
  sourceId       String?
  requiresReview Boolean  @default(false)
  detectedAt     DateTime @default(now())

  @@index([entityType, entityId])
  @@index([detectedAt])
}

enum ReviewStatus {
  OPEN
  APPROVED
  REJECTED
}

model ReviewItem {
  id             String   @id @default(cuid())
  stagedRecordId String?
  changeEventId  String?
  reason         String
  status         ReviewStatus @default(OPEN)
  reviewer       String?
  resolutionNote String?
  resolvedAt     DateTime?
  createdAt      DateTime @default(now())

  stagedRecord StagedRecord? @relation(fields: [stagedRecordId], references: [id])

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

// Consumer-facing correction reports ("report a problem with this listing").
model CorrectionReport {
  id         String   @id @default(cuid())
  entityType EntityType
  entityId   String
  field      String?
  message    String
  reporterEmail String?
  status     ReviewStatus @default(OPEN)
  createdAt  DateTime @default(now())

  @@index([status])
}