drop/server/prisma/models/community.prisma

// Community (drop-community M1, folded into the fork -- see
// server/internal/community/README.md for the full story). Only a single
// new table is needed here: Drop's own `Game` model already IS the title
// spine for Drop content (id, mName, mCoverObjectId, ...), so there is no
// separate "DropTitle" -- community pages join straight onto `Game` and
// `Playtime`, which already exist. The one foreign catalog that needs its
// own row is RomM (games.quasarke.net), a different app with its own
// database, so it gets a small anti-corruption table synced on a schedule.
//
// Deliberately NOT modeled yet (still design-only, see drop-community's
// DESIGN.md, kept as the reference doc): issues, game servers (M4). Those
// are additive on top of this file, never a rewrite of it. Mods and
// achievement definitions (M5) and friends/presence/chat (M3) are modeled
// below.
//
// Ownership boundary, so a future milestone knows where its edges are:
// Friendship/ChatRoom/ChatMessage/ChatReadState (M3), Mod*/
// AchievementDefinition (M5) live in this file; Device/DeviceToken/
// PlaySession/AchievementUnlock (M2) live in devices.prisma;
// GameServer/ServerStatus/Issue (M4) do not exist yet. Never touch a model
// you don't own.
enum CommunityTitlePlatform {
  romm
  external
}

model CommunityTitle {
  id String @id @default(uuid())

  platform   CommunityTitlePlatform
  externalId String // RomM's numeric rom id, as a string

  slug     String @unique
  name     String
  sortName String

  coverUrl String?
  deepLink String? // link out to games.quasarke.net/rom/:id

  metadata Json? // source payload kept for debugging (romm platform id/slug/name)

  isVisible Boolean @default(true) // sync never deletes; a title missing from a resync is hidden, not dropped

  firstSeenAt  DateTime @default(now())
  lastSyncedAt DateTime @default(now())

  @@unique([platform, externalId], name: "platformExternalKey")
  @@index([sortName])
  @@index([name(ops: raw("gist_trgm_ops(siglen=32)"))], type: Gist)
}

// --- M5 --- Mods catalog + achievement definitions/display.
// See drop-community DESIGN.md section 7.6 (mods) and 7.8 (achievements) --
// the field names below map onto those tables as closely as the "folded
// into Drop" reality allows. Three deliberate deviations from the design
// doc, all explained where they'd otherwise look like a mistake:
//
// 1. `title_id` (the generic platform/romm/external spine) becomes a plain
//    `gameId` string pointing at Drop's own `Game`, not a polymorphic
//    reference and NOT a Prisma `@relation`. Every mod and every
//    achievement schema we can actually source targets a Steam-era Drop
//    title (see homelab-compose/docs/game-mods-archive.md: only 3 of ~54
//    titles have a real Thunderstore ecosystem, and RomM's ROM-hacking mod
//    scene was deliberately not pursued) -- so a polymorphic
//    CommunityTitle-or-Game reference for a case that doesn't exist yet
//    would be premature abstraction the M1 author already declined once
//    (see the CommunityTitle comment above). And per devices.prisma's
//    precedent (read that header first): a Prisma relation must be declared
//    on BOTH sides, which would force an edit to content.prisma for every
//    milestone in parallel -- exactly the concurrent-edit collision this
//    fork's milestones were warned about. `gameId` is resolved at the
//    application layer instead, same as Device/PlaySession do for Game.
// 2. Same reasoning for `uploaderId`/`identityId` -> User.id: soft
//    references, no relation declared in user.prisma.
// 3. `achievement_unlock` and the `achievement_progress` rollup are NOT
//    modeled here. M2 owns AchievementUnlock (see devices.prisma) and keys
//    it on (steamAppId, apiName) specifically so this milestone can join on
//    those two columns with zero migration on either side -- no
//    definitionId FK needed or wanted. Completion % is computed on read by
//    joining AchievementDefinition against AchievementUnlock, rather than
//    maintaining a second rollup table this milestone doesn't own.

enum ModKind {
  mod
  expansion
  patch
  tool
}

enum ModArchiveFormat {
  zip
  sevenzip
  rar
}

enum ModInstallRoot {
  game_root
  documents
  appdata
}

enum ModDependencyKind {
  requires
  conflicts
  recommends
}

enum ModFileAction {
  created
  overwrote
  skipped
}

enum ModInstallationStatus {
  installed
  uninstalled
  failed
  orphaned
}

enum AchievementSource {
  goldberg_schema
  manual
  romm
}

model Mod {
  id String @id @default(uuid())

  // Soft reference to Game.id -- see the file header for why this isn't a
  // Prisma relation.
  gameId String

  kind ModKind @default(mod)

  slug        String  @unique
  name        String
  summary     String?
  description String? // markdown

  author String? // the mod's actual author, per SOURCES.txt / the manifest -- may differ from uploader

  // Soft reference to User.id -- see the file header.
  uploaderId String?

  // Free text, not an enum: archive-mod.py already emits "thunderstore" and
  // "modio" (Nexus stubbed, see the archive doc), and "manual" covers
  // anything entered by hand through the admin form. A new source
  // shouldn't force a migration.
  sourcePlatform String?
  sourceRef      String? // e.g. Thunderstore full_name "Owner-Name", mod.io "game/mod" name_id pair

  // { source_url, retrieved_at, retrieved_by, original_filename?,
  //   license_note?, archive_note? } per DESIGN.md 7.6 -- required by the
  // collection pipeline, not optional metadata. A mod whose origin we
  // cannot state is a mod we cannot re-acquire or audit.
  provenance Json

  isPublished Boolean @default(false)

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

  // Installations are reachable via versions[].installations -- ModInstallation
  // relates to ModVersion (a specific version is what's installed), not to Mod.
  versions   ModVersion[]
  dependents ModDependency[] @relation("dependencyTarget")

  @@index([gameId])
  @@index([name(ops: raw("gist_trgm_ops(siglen=32)"))], type: Gist)
}

model ModVersion {
  id String @id @default(uuid())

  modId String
  mod   Mod    @relation(fields: [modId], references: [id], onDelete: Cascade)

  version     String
  versionSort String // normalized for ordering

  // Intended cdn.quasarke.net/game-mods/... path per
  // homelab-compose/docs/game-mods-archive.md's storage layout. May 404
  // today -- that vhost has no file_server wired to /mnt/local24 yet, and
  // fixing that is explicitly out of this milestone's scope. See
  // server/internal/community/README.md for the current serving story.
  archiveUrl String

  // Path relative to the /mnt/local24/game-mods mount (read-only in this
  // container), used by the local streaming-download route that serves
  // downloads today without touching Caddy. Null for versions registered
  // by hand through the admin form rather than imported from the pipeline.
  archiveRelPath String?
  archiveSha256  String
  archiveSize    BigInt
  archiveFormat  ModArchiveFormat @default(zip)

  changelog String?

  installRoot    ModInstallRoot @default(game_root)
  installSubpath String?

  minBaseVersion String?
  maxBaseVersion String?

  releasedAt DateTime?
  createdAt  DateTime  @default(now())

  dependencies  ModDependency[]   @relation("versionDependencies")
  installations ModInstallation[]

  @@unique([modId, version])
  @@index([modId])
}

model ModDependency {
  id String @id @default(uuid())

  modVersionId String
  modVersion   ModVersion @relation("versionDependencies", fields: [modVersionId], references: [id], onDelete: Cascade)

  // Free-text ref (e.g. Thunderstore full_name "Owner-Name") -- an archived
  // mod can declare a dependency on another package we have not archived
  // ourselves, so this cannot be a hard FK. dependsOnModId is filled in
  // opportunistically once/if the ref resolves to a Mod we actually have,
  // which is what lets the install-plan resolver walk real edges.
  dependsOnRef   String
  dependsOnModId String?
  dependsOnMod   Mod?    @relation("dependencyTarget", fields: [dependsOnModId], references: [id], onDelete: SetNull)

  versionRange String?
  kind         ModDependencyKind @default(requires)

  @@index([modVersionId])
  @@index([dependsOnModId])
}

// The install manifest -- the actually valuable idea taken from
// LANCommander (DESIGN.md 7.6). Recording every file at install time, with
// what the install did to it, is what makes uninstall exact: `created`
// files get deleted, `overwrote` files get RESTORED from their backup
// object (deleting them would strip a base-game file), `skipped` files are
// left alone. No client wrapper exists yet to drive this automatically
// (that's a device/session concern, M2 territory) -- these tables and their
// API are built and tested standalone; a wrapper can start calling them
// with zero schema changes once it exists.
model ModInstallation {
  id String @id @default(uuid())

  // Soft reference to User.id -- see the file header.
  identityId String

  // Soft reference to M2's Device.id (see devices.prisma). Nullable: M2's
  // table may not exist at migration time either way, and this must not
  // depend on a table owned by a different milestone.
  deviceId String?

  modVersionId String
  modVersion   ModVersion @relation(fields: [modVersionId], references: [id], onDelete: Cascade)

  // Soft reference to Game.id -- see the file header.
  gameId String

  installPath String
  status      ModInstallationStatus @default(installed)

  installedAt   DateTime  @default(now())
  uninstalledAt DateTime?

  files ModInstalledFile[]

  @@index([identityId])
  @@index([gameId])
  @@index([modVersionId])
}

model ModInstalledFile {
  id String @id @default(uuid())

  installationId String
  installation   ModInstallation @relation(fields: [installationId], references: [id], onDelete: Cascade)

  relativePath String
  sha256       String
  sizeBytes    BigInt
  actionTaken  ModFileAction

  // Drop's own object store id -- the original bytes, when actionTaken=overwrote.
  backupObjectId String?

  @@index([installationId])
}

// Achievement DEFINITIONS + DISPLAY only -- see the file header for why
// unlocks/progress are not modeled here.
model AchievementDefinition {
  id String @id @default(uuid())

  // Soft reference to Game.id -- see the file header.
  gameId     String
  steamAppId Int // denormalized for the import join -- Game.metadataId when metadataSource=Steam; also the join key against M2's AchievementUnlock

  apiName     String // Goldberg's key, e.g. ACH_WIN_ONE_GAME
  displayName String
  description String?

  iconObjectId     String? // unlocked icon, copied into Drop's own object store at import time
  iconGrayObjectId String? // locked icon

  isHidden  Boolean @default(false)
  sortOrder Int     @default(0)

  source     AchievementSource @default(goldberg_schema)
  schemaHash String? // lets a re-import skip an unchanged schema

  importedAt DateTime @default(now())

  @@unique([gameId, apiName])
  @@index([steamAppId])
}

// --- M3: friends, presence, chat -----------------------------------------
//
// No new identity table (User already is identity, per the comment above)
// and deliberately no `presence` table either: presence is derived, not
// stored. "Online" is whoever currently holds a chat/community websocket
// connection (see server/internal/community/chatTransport.ts, same
// in-memory-map shape as server/internal/notifications). "Playing X" reads
// M2's PlaySession table (devices.prisma) through an isolated query module
// (server/internal/community/presenceService.ts) -- PlaySession landed
// partway through this milestone with exactly the join contract its own
// header comment promises M3, so that module reads it directly rather than
// through a defensive fallback.
//
// Product redirect mid-milestone: chat's primary job is coordinating the
// persistent-world servers (EQEmu, WoW/TrinityCore, DAoC), not generic
// per-title discussion -- "who's on, who needs a healer, logging on in 10."
// So ChatRoomKind gets a `server` case that's first-class alongside `title`,
// and presenceService.ts treats "who's on <server> right now" as the
// priority query, not a generic online/offline dot. See ChatRoom's
// serverKey/gameServerId fields below for how server rooms are identified
// ahead of M4's GameServer registry landing.
//
// This fork never adopted DESIGN.md's unified `title` table (see
// browseService.ts's header) -- Drop's own Game IS the title spine for
// Drop content, and CommunityTitle (above) is the one for RomM. A chat room
// therefore carries a plain, Prisma-relation-less `gameId` / `communityTitleId`
// pair rather than one FK to a shared table. They're deliberately NOT
// declared as Prisma `@relation`s (which would require adding a back-reference
// field to `Game` in content.prisma and to `CommunityTitle` above -- models
// this milestone doesn't own the surrounding context of, and a concurrent
// milestone touching either one becomes an unnecessary merge conflict).
// Referential integrity is still real: both columns get a hand-written FK
// in this migration's migration.sql, enforced by Postgres, just not
// type-checked by Prisma. Existence is additionally checked in
// chatService.ts before a room is created.

enum FriendshipStatus {
  pending
  accepted
  declined
}

model Friendship {
  id String @id @default(uuid(7))

  requesterId String
  requester   User   @relation("FriendshipRequester", fields: [requesterId], references: [id], onDelete: Cascade)
  addresseeId String
  addressee   User   @relation("FriendshipAddressee", fields: [addresseeId], references: [id], onDelete: Cascade)

  status FriendshipStatus @default(pending)

  createdAt   DateTime  @default(now())
  respondedAt DateTime?

  // Blocks an exact same-direction duplicate. The "crossing requests" case
  // (A -> B pending, then B -> A) needs a direction-agnostic uniqueness --
  // least(requesterId, addresseeId)/greatest(...) -- which the Prisma schema
  // language can't express as an expression index; it's hand-added in
  // migration.sql and handled explicitly in friendsService.ts (a crossing
  // request resolves onto the existing row as an accept, not a new row).
  @@unique([requesterId, addresseeId], name: "requesterAddresseeKey")
  @@index([addresseeId, status])
  @@index([requesterId, status])
}

enum ChatRoomKind {
  server // persistent-world server coordination (EQEmu, WoW, DAoC) -- see below, this is the primary use case
  title
  direct
  global
}

enum ChatRoomVisibility {
  public
  private
}

model ChatRoom {
  id String @id @default(uuid(7))

  kind ChatRoomKind

  // Set only for kind=server: a stable slug ("eqemu" | "wow" | "daoc" ...),
  // NOT a foreign key, because chat landed before M4's GameServer registry
  // did. M4 owns GameServer/ServerStatus/Issue and is concurrent with this
  // milestone; per the mid-milestone product redirect ("make per-server
  // rooms first-class... reference M4's model rather than duplicating it;
  // if it's not there yet, key rooms by a stable identifier and wire it up
  // after"), chatService.ts ships a small static registry of the three
  // persistent-world servers keyed the same way (everquest.quasarke.net,
  // wow.quasarke.net, daoc.quasarke.net) rather than waiting on M4.
  // `gameServerId` is reserved for that wiring-up pass: once GameServer
  // exists, backfill it from `serverKey` (matched by hand, since this
  // milestone doesn't know M4's key scheme) and prefer it over serverKey
  // everywhere serverKey is read today. Both nullable, not a Prisma
  // relation, same reasoning as gameId/communityTitleId below.
  serverKey    String? @unique
  gameServerId String? @unique // TODO(M4 integration): populate once GameServer exists; unused until then

  // Set only for kind=title (exactly one of the two); see file header for
  // why these aren't Prisma relations.
  gameId           String? @unique
  communityTitleId String? @unique

  // Set only for kind=direct: least(userIdA)||':'||greatest(userIdA,userIdB),
  // computed in chatService.ts, so two users clicking "message" at the same
  // instant land in the one room a unique constraint guarantees.
  dmKey String? @unique

  name       String?
  topic      String?
  visibility ChatRoomVisibility @default(public)

  createdById String?
  createdBy   User?   @relation("ChatRoomCreatedBy", fields: [createdById], references: [id], onDelete: SetNull)

  isArchived Boolean @default(false)
  createdAt  DateTime @default(now())

  messages   ChatMessage[]
  readStates ChatReadState[]

  @@index([kind, isArchived])
}

enum ChatMessageKind {
  text
  system
}

model ChatMessage {
  id String @id @default(uuid(7)) // UUIDv7: time-sortable, makes history paging a plain indexed range scan

  roomId String
  room   ChatRoom @relation(fields: [roomId], references: [id], onDelete: Cascade)

  senderId String? // null for system messages
  sender   User?   @relation("ChatMessageSender", fields: [senderId], references: [id], onDelete: SetNull)

  kind ChatMessageKind @default(text)
  body String

  clientNonce String? // dedup key for optimistic send + reconnect replay

  replyToId String?
  replyTo   ChatMessage?  @relation("ChatMessageReplyTo", fields: [replyToId], references: [id], onDelete: SetNull)
  replies   ChatMessage[] @relation("ChatMessageReplyTo")

  editedAt  DateTime?
  deletedAt DateTime? // soft delete; body blanked, row kept

  createdAt DateTime @default(now())

  // Both senderId and clientNonce are nullable; Postgres never treats a NULL
  // as equal to another NULL in a unique constraint, so this behaves exactly
  // like DESIGN.md's `where client_nonce is not null` partial index without
  // needing one.
  @@unique([roomId, senderId, clientNonce], name: "dedupeKey")
  @@index([roomId, id])
}

// Doubles as room membership: a row's existence means "this user has this
// room open", created on first read/visit. Public title/global rooms don't
// gate posting on membership (see chatService.ts authorizeRoom); DM rooms
// are membership-gated implicitly by dmKey containing exactly these two
// user ids. This is the "unread state" DESIGN.md put on chat_room_member --
// folded in here directly since this milestone doesn't own a separate
// membership model.
model ChatReadState {
  roomId String
  room   ChatRoom @relation(fields: [roomId], references: [id], onDelete: Cascade)
  userId String
  user   User     @relation("ChatReadState", fields: [userId], references: [id], onDelete: Cascade)

  lastReadMessageId String?
  joinedAt          DateTime @default(now())
  updatedAt         DateTime @updatedAt

  @@id([roomId, userId])
  @@index([userId])
}