petfeed/prisma/schema.prisma

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

datasource db {
  provider = "postgresql"
}

model Owner {
  id        String   @id // = Clerk userId (e.g. "user_xxx")
  createdAt DateTime @default(now())
  pets      Pet[]
}

model Species {
  id     String  @id @default(cuid())
  name   String  @unique // "Dog" | "Cat" | "Bird"
  breeds Breed[]
  pets   Pet[]
}

model Breed {
  id        String  @id @default(cuid())
  name      String
  species   Species @relation(fields: [speciesId], references: [id])
  speciesId String
  pets      Pet[]

  @@unique([speciesId, name])
}

model Pet {
  id            String   @id @default(cuid())
  owner         Owner    @relation(fields: [ownerId], references: [id])
  ownerId       String
  species       Species  @relation(fields: [speciesId], references: [id])
  speciesId     String
  breed         Breed?   @relation(fields: [breedId], references: [id])
  breedId       String?
  name          String
  bio           String?
  adoptionStory String?
  avatarKey     String? // Supabase Storage object key, NOT a full URL
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  // Phase 2 back-relations
  posts      Post[]
  stories    Story[]
  storyViews StoryView[] @relation("StoryViewer")
  following  Follow[]    @relation("Follower")
  followers  Follow[]    @relation("Followee")
  blocking   Block[]     @relation("Blocker")
  blockedBy  Block[]     @relation("Blocked")
  reports    Report[]    @relation("Reporter")

  @@index([ownerId])
}

// ─── Phase 2 Enums ───────────────────────────────────────────────────────────

enum PostType {
  PHOTO
  MILESTONE
}

enum MilestoneType {
  BIRTHDAY
  ADOPTION_DAY
  FIRST_OUTING
  VET_VISIT
  LITTER
  FOLLOWERS_500
  FOLLOWERS_1K
  FOLLOWERS_2_5K
  FOLLOWERS_5K
  FOLLOWERS_10K
  FOLLOWERS_25K
  FOLLOWERS_50K
  FOLLOWERS_100K
  FOLLOWERS_250K
  FOLLOWERS_500K
  FOLLOWERS_1M
}

enum ReportReason {
  SPAM
  HARASSMENT
  INAPPROPRIATE
  MISINFORMATION
  OTHER
}

// ─── Phase 2 Models ──────────────────────────────────────────────────────────

model Post {
  id        String      @id @default(cuid())
  pet       Pet         @relation(fields: [petId], references: [id], onDelete: Cascade)
  petId     String
  type      PostType    @default(PHOTO)
  caption   String?     @db.VarChar(500)
  images    PostImage[]
  milestone Milestone?
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt

  @@index([petId, createdAt(sort: Desc)])
}

model PostImage {
  id         String @id @default(cuid())
  post       Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId     String
  storageKey String // Supabase Storage object key
  position   Int // 0-based order in carousel
  width      Int?
  height     Int?

  @@unique([postId, position])
  @@index([postId])
}

model Milestone {
  id            String        @id @default(cuid())
  post          Post          @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId        String        @unique
  milestoneType MilestoneType
  notes         String?       @db.VarChar(500) // free text for 'litter' type (D-09)
}

model Story {
  id         String      @id @default(cuid())
  pet        Pet         @relation(fields: [petId], references: [id], onDelete: Cascade)
  petId      String
  storageKey String // Supabase Storage object key
  expiresAt  DateTime // set to NOW() + 24 hours on create (D-07)
  views      StoryView[]
  createdAt  DateTime    @default(now())

  @@index([petId, expiresAt]) // supports lazy-delete filter query
}

model StoryView {
  story       Story    @relation(fields: [storyId], references: [id]) // NO onDelete:Cascade (D-08)
  storyId     String
  viewerPet   Pet      @relation("StoryViewer", fields: [viewerPetId], references: [id])
  viewerPetId String
  viewedAt    DateTime @default(now())

  @@id([storyId, viewerPetId]) // deduplication: one view per viewer per story
  @@index([viewedAt]) // supports 30-day cleanup job (D-08)
}

model Follow {
  follower      Pet      @relation("Follower", fields: [followerPetId], references: [id], onDelete: Cascade)
  followerPetId String
  followee      Pet      @relation("Followee", fields: [followeePetId], references: [id], onDelete: Cascade)
  followeePetId String
  createdAt     DateTime @default(now())

  @@id([followerPetId, followeePetId])
  @@index([followerPetId])
  @@index([followeePetId])
}

model Block {
  blocker      Pet      @relation("Blocker", fields: [blockerPetId], references: [id], onDelete: Cascade)
  blockerPetId String
  blocked      Pet      @relation("Blocked", fields: [blockedPetId], references: [id], onDelete: Cascade)
  blockedPetId String
  createdAt    DateTime @default(now())

  @@id([blockerPetId, blockedPetId])
  @@index([blockerPetId])
}

model Report {
  id            String       @id @default(cuid())
  reporter      Pet          @relation("Reporter", fields: [reporterPetId], references: [id])
  reporterPetId String
  targetPostId  String? // null if reporting a profile
  targetPetId   String? // null if reporting a post
  reason        ReportReason
  notes         String?      @db.VarChar(500)
  createdAt     DateTime     @default(now())

  @@index([reporterPetId])
}