FTStore/web/prisma/schema.prisma

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-3.0.x"]
}

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

model ProcessedMessage {
  id                   Int       @id @default(autoincrement())
  channelId            String    @map("channel_id")
  messageId            BigInt    @map("message_id")
  sourceType           String    @default("telegram") @map("source_type")
  sourceUrl            String?   @map("source_url")
  sourceReleaseTag     String?   @map("source_release_tag")
  sourceReleaseId      BigInt?   @map("source_release_id")
  sourceAssetUpdatedAt DateTime? @map("source_asset_updated_at")
  hasIpa               Boolean   @default(false) @map("has_ipa")
  fileName             String?   @map("file_name")
  fileSize             BigInt?   @map("file_size")
  messageText          String?   @map("message_text")
  status               String    @default("pending") // pending, downloading, processing, completed, failed, skipped
  error                String?
  createdAt            DateTime  @default(now()) @map("created_at")
  updatedAt            DateTime  @updatedAt @map("updated_at")

  @@unique([channelId, messageId])
  @@index([status])
  @@index([channelId])
  @@index([sourceType])
  @@map("processed_messages")
}

model DownloadedIpa {
  id              Int       @id @default(autoincrement())
  bundleId        String    @map("bundle_id")
  appName         String    @map("app_name")
  version         String
  buildNumber     String?   @map("build_number")
  minOsVersion    String?   @map("min_os_version")
  fileName        String    @map("file_name")
  fileSize        BigInt    @map("file_size")
  iconUrl         String?   @map("icon_url")
  /// When the build's own icon was last looked for inside the IPA.
  ///
  /// Set whether or not one was found, which is the whole point: without it a
  /// bounded backfill re-opens the same archives that have no icon on every
  /// pass and never reaches the ones behind them. A stamp and no `iconUrl`
  /// means "we looked, there is nothing in there".
  iconCheckedAt   DateTime? @map("icon_checked_at")
  screenshotUrls  Json?     @map("screenshot_urls") // string[]
  description     String?
  developerName   String?   @map("developer_name")
  tweaks          Json? // string[] - detected dylib names
  isTweaked       Boolean   @default(false) @map("is_tweaked")
  entitlements    Json? // Record<string, string>
  privacyInfo     Json?     @map("privacy_info") // Record<string, string>
  /// Path relative to `storage_dir`, e.g. "ipa/a3/a3f2….ipa".
  storagePath     String?   @map("storage_path")
  /// Unguessable capability token used in the public download URL.
  downloadToken   String?   @unique @map("download_token")
  /// @deprecated GitHub Releases backend — no longer written or read. The
  /// columns stay until a follow-up migration drops them explicitly, because
  /// the containers run `prisma db push`, which would drop them silently.
  githubReleaseId Int?      @map("github_release_id")
  githubAssetId   Int?      @map("github_asset_id")
  githubAssetUrl  String?   @map("github_asset_url")
  downloadUrl     String?   @map("download_url")
  downloadCount   Int       @default(0) @map("download_count")
  channelId       String?   @map("channel_id")
  messageId       BigInt?   @map("message_id")
  isCorrupted     Boolean   @default(false) @map("is_corrupted")
  corruptionNote  String?   @map("corruption_note")
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")

  @@index([bundleId])
  @@index([appName])
  @@index([channelId])
  @@index([storagePath])
  @@map("downloaded_ipas")
}

model FeedAppOverride {
  id           Int      @id @default(autoincrement())
  feed         String
  groupKey     String   @map("group_key")
  bundleId     String   @map("bundle_id")
  matchedTweak String?  @map("matched_tweak")
  appName      String   @map("app_name")
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @updatedAt @map("updated_at")

  @@unique([feed, groupKey])
  @@index([bundleId])
  @@map("feed_app_overrides")
}

model GithubReleaseSource {
  id            Int       @id @default(autoincrement())
  owner         String
  repo          String
  displayName   String?   @map("display_name")
  /// The app this repository's tweak goes into, when it publishes a `.dylib`
  /// rather than a finished `.ipa`.
  ///
  /// Plenty of tweak repositories publish only the dylib, because the author has
  /// nothing to attach it to: they cannot ship the App Store app itself. Set
  /// this and a release with no IPA in it stops being nothing — the dylib is
  /// taken, a clean build of this bundle id **already in the Library** is used
  /// as the base, and the two are put together.
  ///
  /// The base comes from the Library first, and from `dylibBaseIpaUrl` when the
  /// Library has nothing current. A repository set up this way with neither
  /// simply produces nothing, and says so in the log.
  ///
  /// Null, which is nearly all of them, means "this repo publishes IPAs" and
  /// nothing changes.
  dylibBundleId String?   @map("dylib_bundle_id")
  /// Where a decrypted base build of `dylibBundleId` can be fetched, with `*`
  /// standing where the version goes.
  ///
  /// The commonest case for a tweak repository is a new dylib for an App Store
  /// release nobody has a build of yet, and without this the pipeline produces
  /// nothing until somebody puts one in the Library by hand. With it, the newest
  /// App Store version is substituted in, the build is fetched, and the tweak
  /// goes into it.
  ///
  /// The operator types this address. Nothing searches for one, keeps a list of
  /// mirrors, or tries somewhere else when it 404s. The fetched build is a build
  /// input cached under `temp_dir` and is never published: the store serves the
  /// tweaked result and never the untweaked app that went into it. A base that
  /// is still FairPlay-encrypted is refused by the injector exactly as an App
  /// Store download is.
  dylibBaseIpaUrl String? @map("dylib_base_ipa_url")
  /// The App Store version the newest dylib was last built against.
  ///
  /// What makes a new App Store release produce a build on its own: the scan
  /// compares it against what Apple is shipping now, and re-queues the newest
  /// dylib release when the two differ. Null means "never built from a URL",
  /// which is every source that takes its base from the Library.
  dylibBaseVersion String? @map("dylib_base_version")
  isPrivate     Boolean   @default(false) @map("is_private")
  username      String?
  token         String?
  isActive      Boolean   @default(true) @map("is_active")
  priority      Int       @default(0)
  lastScannedAt DateTime? @map("last_scanned_at")
  totalReleases Int       @default(0) @map("total_releases")
  ipaCount      Int       @default(0) @map("ipa_count")
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")

  @@unique([owner, repo])
  @@index([isActive])
  @@map("github_release_sources")
}

model AppStoreCache {
  id          Int      @id @default(autoincrement())
  bundleId    String   @unique @map("bundle_id")
  appName     String?  @map("app_name")
  iconUrl     String?  @map("icon_url")
  screenshots Json? // string[]
  description String?
  developer   String?
  genre       String?
  /// The storefront-independent genre number. `6014` is Games.
  ///
  /// Stored beside the name because the name is localized: with
  /// `appstore_country=de` `genre` reads "Spiele", so anything classifying on
  /// it would be right in one country and wrong in the next.
  genreId     Int?     @map("genre_id")
  price       Float?
  rating      Float?
  data        Json? // full API response
  fetchedAt   DateTime @default(now()) @map("fetched_at")
  expiresAt   DateTime @map("expires_at")

  @@map("appstore_cache")
}

model SystemMetric {
  id         Int      @id @default(autoincrement())
  metric     String // total_ipas, total_downloads, storage_used, etc.
  value      Float
  metadata   Json?
  recordedAt DateTime @default(now()) @map("recorded_at")

  @@index([metric])
  @@index([recordedAt])
  @@map("system_metrics")
}

model ActivityLog {
  id        Int      @id @default(autoincrement())
  type      String // scan, download, process, upload, cleanup, generate, error
  message   String
  status    String   @default("info") // info, success, warning, error
  details   Json?
  createdAt DateTime @default(now()) @map("created_at")

  @@index([type])
  @@index([status])
  @@index([createdAt])
  @@map("activity_log")
}

model ChannelProgress {
  id                    Int       @id @default(autoincrement())
  channelId             String    @unique @map("channel_id")
  channelName           String?   @map("channel_name")
  channelDescription    String?   @map("channel_description")
  lastMessageId         BigInt    @default(0) @map("last_message_id")
  previousScanMessageId BigInt    @default(0) @map("previous_scan_message_id")
  totalMessages         Int       @default(0) @map("total_messages")
  ipaCount              Int       @default(0) @map("ipa_count")
  isActive              Boolean   @default(true) @map("is_active")
  /// `full` sweeps the whole channel on every scan. `on_demand` never sweeps:
  /// builds only arrive when a targeted search asks for a tweak by name, which
  /// is how a channel can supply one app without its whole catalog coming too.
  scanMode              String    @default("full") @map("scan_mode")
  isForum               Boolean   @default(false) @map("is_forum")
  forumTopics           Json?     @map("forum_topics")
  priority              Int       @default(0) @map("priority")
  lastScannedAt         DateTime? @map("last_scanned_at")
  createdAt             DateTime  @default(now()) @map("created_at")
  updatedAt             DateTime  @updatedAt @map("updated_at")

  @@map("channel_progress")
}

model PrivacyDescriptionCache {
  id          Int      @id @default(autoincrement())
  key         String   @unique // e.g., NSCameraUsageDescription
  description String
  usageCount  Int      @default(1) @map("usage_count")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  @@map("privacy_description_cache")
}

model Setting {
  id        Int      @id @default(autoincrement())
  key       String   @unique
  value     String
  type      String   @default("string") // string, number, boolean, json
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("settings")
}

model TelegramRuntimeState {
  id                 Int       @id
  state              String    @default("disconnected")
  sessionReady       Boolean   @default(false) @map("session_ready")
  error              String?
  passwordHint       String    @default("") @map("password_hint")
  owner              String?
  lastHeartbeatAt    DateTime? @map("last_heartbeat_at")
  lastConnectedAt    DateTime? @map("last_connected_at")
  lastAuthAt         DateTime? @map("last_auth_at")
  retryCount         Int       @default(0) @map("retry_count")
  currentCommandId   Int?      @map("current_command_id")
  currentCommandType String?   @map("current_command_type")
  progressLabel      String?   @map("progress_label")
  progressCurrent    Int?      @map("progress_current")
  progressTotal      Int?      @map("progress_total")
  createdAt          DateTime  @default(now()) @map("created_at")
  updatedAt          DateTime  @updatedAt @map("updated_at")

  @@map("telegram_runtime_state")
}

model TelegramCommand {
  id                Int       @id @default(autoincrement())
  type              String
  payload           Json?
  status            String    @default("pending")
  requestedByUserId Int?      @map("requested_by_user_id")
  requestedAt       DateTime  @default(now()) @map("requested_at")
  startedAt         DateTime? @map("started_at")
  completedAt       DateTime? @map("completed_at")
  result            Json?
  error             String?
  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  @@index([status, requestedAt])
  @@index([type, status])
  @@map("telegram_commands")
}

/// A user-submitted "please track this source" request, decided by an operator.
///
/// Private on the way in and public once it exists, which is not a contradiction
/// but a boundary: the row carries a note and an install id that stay with the
/// operators, while the wish it describes is on a board everybody can vote on
/// and reply to. `serializeBoardRequest` is where the line is drawn, and it
/// names no requester. See `TweakRequestVote` and `TweakRequestComment`.
model TweakRequest {
  id              Int       @id @default(autoincrement())
  /// Anonymous per-install UUID minted by the app. The only identity there is —
  /// it exists so the app can list its own requests back, nothing more.
  installId       String    @map("install_id")
  /// What the user is actually asking for, e.g. "YouTube + uYou".
  tweakName       String    @map("tweak_name")
  /// "telegram" | "github" — the same vocabulary as `ProcessedMessage.sourceType`.
  ///
  /// Null together with `sourceId` and `rawInput` when somebody asked for a
  /// tweak without knowing where it comes from, which is the common case for
  /// anyone who is not already in the sideloading scene. Finding the source is
  /// then the operator's job rather than a precondition for asking.
  sourceType      String?   @map("source_type")
  /// Normalised to the `lib/sources.ts` convention: `@handle` or
  /// `github:owner/repo`. This is what an approval turns into a real source.
  sourceId        String?   @map("source_id")
  /// Exactly what the user typed, kept because normalisation can surprise.
  rawInput        String?   @map("raw_input")
  /// What makes two rows the same wish.
  ///
  /// The source when there is one, because approving adds *that source* and a
  /// group has to be something an operator can say yes to exactly once. The
  /// normalised tweak name when there is not, so "YouTube + uYou" and "youtube
  /// uyou" still arrive as one wish with two voices rather than two wishes.
  ///
  /// Stored rather than derived because the board groups and counts in SQL, and
  /// neither `COALESCE` nor the name normalisation is something `groupBy` can
  /// express. `lib/tweak-requests.ts` owns how it is computed; nothing should
  /// write this column without going through `requestGroupKey`.
  ///
  /// Defaulted to the empty string only so `db push` can add it to a table that
  /// already has rows; `pre-push.sql` fills those in from their source, which is
  /// exactly what their key would have been.
  groupKey        String    @default("") @map("group_key")
  note            String?
  /// pending, approved, rejected. Validated in `lib/tweak-requests.ts`, not here:
  /// the containers run `prisma db push`, which drops anything the schema can't
  /// express (a CHECK constraint included).
  status          String    @default("pending")
  decisionReason  String?   @map("decision_reason")
  decidedAt       DateTime? @map("decided_at")
  /// No relation on purpose, mirroring `TelegramCommand.requestedByUserId`: a
  /// deleted operator account must not cascade away the decision it made.
  decidedByUserId Int?      @map("decided_by_user_id")
  /// Version off the app's User-Agent, for triage.
  clientVersion   String?   @map("client_version")
  /// Denormalised, and recomputed from the rows on every write rather than
  /// incremented, exactly as `FeedbackTicket` does it: a lost update heals
  /// itself on the next one, and "how much does anyone want this?" becomes a
  /// number the board can sort on in SQL.
  upvotes         Int       @default(0) @map("vote_up")
  downvotes       Int       @default(0) @map("vote_down")
  score           Int       @default(0) @map("vote_score")
  commentCount    Int       @default(0) @map("comment_count")
  /// Bumped by a reply, so "recently active" is a cheap sort here too.
  /// Held at the top of the board by an operator, newest pin first.
  ///
  /// On the request rather than on the source, because the board lists one row
  /// per source and that row is the canonical one — pinning anything else would
  /// be pinning a copy nobody sees.
  pinnedAt        DateTime? @map("pinned_at")
  lastActivityAt  DateTime  @default(now()) @map("last_activity_at")
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")

  votes    TweakRequestVote[]
  comments TweakRequestComment[]
  mentions TweakRequestMention[]

  @@index([status, createdAt])
  @@index([installId, createdAt])
  @@index([sourceId])
  /// The board groups on this and takes the oldest id in each group, on every
  /// listing. `sourceId` used to carry that and no longer can.
  @@index([groupKey])
  @@index([status, score])
  @@map("tweak_requests")
}

/// A vote on somebody's wish, from an install or from FTStore.
///
/// The same shape as `FeedbackVote` and for the same reasons, down to the team
/// having one vote however many operators are signed in. Kept as its own table
/// rather than pointed at a polymorphic column because a foreign key that can
/// mean two things is one the database cannot check.
model TweakRequestVote {
  id          Int                  @id @default(autoincrement())
  requestId   Int                  @map("request_id")
  request     TweakRequest         @relation(fields: [requestId], references: [id], onDelete: Cascade)
  /// The reply this is about, or null for the wish itself. Same shape as
  /// `FeedbackVote`, so one tally implementation serves both boards.
  commentId   Int?                 @map("comment_id")
  comment     TweakRequestComment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  /// Null on a team vote, which is cast by an operator and has no install.
  installId   String?              @map("install_id")
  /// Audit only, never serialised.
  voterUserId Int?                 @map("voter_user_id")
  voterRole   String               @default("user") @map("voter_role")
  /// `1` or `-1`. Validated in `lib/tweak-requests.ts`; `db push` would drop a
  /// CHECK constraint on the next deploy.
  value       Int
  createdAt   DateTime             @default(now()) @map("created_at")
  updatedAt   DateTime             @updatedAt @map("updated_at")

  @@index([requestId, commentId])
  @@index([installId])
  @@map("tweak_request_votes")
}

/// One reply under a wish.
///
/// Mirrors `FeedbackComment` minus the status timeline, which a request does not
/// have: its lifecycle is the operator's decision, recorded on the row itself.
model TweakRequestComment {
  id            Int          @id @default(autoincrement())
  requestId     Int          @map("request_id")
  request       TweakRequest @relation(fields: [requestId], references: [id], onDelete: Cascade)
  /// The install that wrote it. Null on a team entry.
  installId     String?      @map("install_id")
  /// The operator behind a team entry. Never serialised.
  authorUserId  Int?         @map("author_user_id")
  /// "user" | "team". This, not the name, is what draws the badge.
  authorRole    String       @default("user") @map("author_role")
  body          String
  hidden        Boolean      @default(false)
  hiddenReason  String?      @map("hidden_reason")
  /// Taken back by whoever wrote it. Gone from the app, kept for operators.
  deletedAt     DateTime?    @map("deleted_at")
  editedAt      DateTime?    @map("edited_at")
  /// An operator rewrote it. Never silent, for the reason it is not on a
  /// feedback reply either.
  editedByTeam  Boolean      @default(false) @map("edited_by_team")
  /// Somebody wrote `@FTStore` in it.
  teamMentioned Boolean      @default(false) @map("team_mentioned")
  createdAt     DateTime     @default(now()) @map("created_at")
  updatedAt     DateTime     @updatedAt @map("updated_at")

  mentions  TweakRequestMention[]
  revisions TweakRequestRevision[]
  votes     TweakRequestVote[]

  @@index([requestId, createdAt])
  @@map("tweak_request_comments")
}

/// Who a wish or one of its replies pinged.
model TweakRequestMention {
  id        Int                  @id @default(autoincrement())
  requestId Int                  @map("request_id")
  request   TweakRequest         @relation(fields: [requestId], references: [id], onDelete: Cascade)
  /// Null when the mention is in the wish itself rather than in a reply.
  commentId Int?                 @map("comment_id")
  comment   TweakRequestComment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  installId String               @map("install_id")
  author    FeedbackAuthor       @relation(fields: [installId], references: [installId], onDelete: Cascade)
  createdAt DateTime             @default(now()) @map("created_at")

  @@index([installId, requestId])
  @@index([requestId, commentId])
  @@map("tweak_request_mentions")
}

/// Who an install calls itself on the feedback board.
///
/// One row per install, and the *only* place a display name is stored — tickets
/// and comments carry the install id and the name is resolved at serialisation
/// time. That is what makes a rename retroactive: someone who picks a better
/// name sees it on everything they ever wrote, exactly like renaming an account.
///
/// The name is not unique, and deliberately so. There is no authentication here,
/// so reserving names would only hand the first person to type "Felitendo" a
/// permanent claim on it while implying an identity nobody verified. What *is*
/// refused is anything that reads as staff (`lib/feedback.ts`), and the team
/// badge is drawn from `FeedbackComment.authorRole`, never from the name.
/// What a tweak request reply said before it was edited.
///
/// The same shape as `FeedbackRevision` and deliberately a second table rather
/// than a nullable pair of columns on one: a foreign key that can point at two
/// tables is one the database cannot check, and these rows exist to be checked.
/// A reply has no title, so there is no title column to leave permanently null.
model TweakRequestRevision {
  id           Int                 @id @default(autoincrement())
  commentId    Int                 @map("comment_id")
  comment      TweakRequestComment @relation(fields: [commentId], references: [id], onDelete: Cascade)
  /// The body as it read before. Never null: an edit that changed nothing is
  /// not recorded at all.
  body         String
  /// The install that made the edit. Null when an operator did.
  installId    String?             @map("install_id")
  /// The operator who made it. Audit only, never serialised.
  editorUserId Int?                @map("editor_user_id")
  /// "user" | "team", so a reader can tell whose hand it was.
  editorRole   String              @default("user") @map("editor_role")
  createdAt    DateTime            @default(now()) @map("created_at")

  @@index([commentId, createdAt])
  @@map("tweak_request_revisions")
}

model FeedbackAuthor {
  /// Anonymous per-install UUID, the same one `TweakRequest` uses.
  installId      String    @id @map("install_id")
  /// The number behind `Anonymous_12`: every author has one from the moment the
  /// row exists, so somebody who never picked a name is still someone the board
  /// can name and somebody else can mention. A sequence rather than a hash of
  /// the install id, because it has to be short enough to type after an `@`.
  handleNumber   Int       @unique @default(autoincrement()) @map("handle_number")
  displayName    String?   @map("display_name")
  /// `displayNameKey(displayName)`, and the reason a name belongs to exactly one
  /// install: the uniqueness is what makes `@name` resolve to one person.
  nameKey        String?   @unique @map("name_key")
  /// scrypt of the four-digit PIN that used to be the whole of a claim, in the
  /// `salt:hash` format `lib/auth.ts` uses. Kept only so somebody who secured a
  /// name before accounts existed can still prove it is theirs, once, on the way
  /// to setting a password. Nothing mints one any more.
  pinHash        String?   @map("pin_hash")
  /// scrypt of the account password, same shape. This is the credential now: a
  /// PIN was four digits and was never meant to be more than a way to keep a
  /// name, which is not enough for something a person signs in to.
  ///
  /// Null on a row that predates accounts, and that is exactly what "has not
  /// registered yet" means everywhere it is asked.
  passwordHash   String?   @map("password_hash")
  /// The profile picture, repo-relative and in the same `shots/<xy>/<32-hex>.<ext>`
  /// shape as a feedback attachment, so it goes through the same validation,
  /// metadata stripping and serving route rather than a second set of them.
  /// Optional: a picture is a nicety and never a condition of taking part.
  avatarPath     String?   @unique @map("avatar_path")
  /// Consecutive wrong PINs against **this name**, and until when it is locked.
  /// Kept on the row being claimed rather than on the claimant, so re-minting an
  /// install id does not reset the throttle.
  pinFailures    Int       @default(0) @map("pin_failures")
  pinLockedUntil DateTime? @map("pin_locked_until")
  /// When this install last *changed* handle, which is what the rename cooldown
  /// counts from. Null until it has, so a first claim is never held up.
  ///
  /// Not `updatedAt`: that moves for a wrong PIN or an operator's mute too, and
  /// a cooldown somebody else can extend is not a cooldown.
  renamedAt      DateTime? @map("renamed_at")
  /// The last time this install was heard from on any app route that carries
  /// its id.
  ///
  /// A recency stamp and nothing more: no path, no address, no count. It exists
  /// so an operator can answer "is anyone actually using this?" without the
  /// server keeping a log of who did what when, which is the version of that
  /// question nobody should have to trust us about.
  lastSeenAt     DateTime? @map("last_seen_at")
  /// The account was deleted by an operator, or by whoever held it.
  ///
  /// The row survives on purpose, and this is the field that says why it looks
  /// empty. Deleting clears the identity — the name, the password, the picture,
  /// the sessions, the backup — and keeps `handleNumber`, so everything the
  /// person wrote stays readable under a distinct `Anonymous_12` instead of
  /// collapsing into the one nameless fallback shared by every other row that
  /// never registered. That distinction is the whole reason handles are numbers.
  ///
  /// Not the same as "never registered", which is `passwordHash: null` with this
  /// unset, and the console says which of the two it is looking at.
  deletedAt      DateTime? @map("deleted_at")
  /// Muted by an operator. Everything already posted stays up; nothing new is
  /// accepted. Hiding the posts is a separate, per-row decision.
  ///
  /// Deliberately survives a deletion: silencing an install and closing an
  /// account are separate decisions, and clearing this on the way out would let
  /// somebody who was muted come straight back by registering again.
  blocked        Boolean   @default(false)
  blockedNote    String?   @map("blocked_note")
  createdAt      DateTime  @default(now()) @map("created_at")
  updatedAt      DateTime  @updatedAt @map("updated_at")

  mentions             FeedbackMention[]
  tweakRequestMentions TweakRequestMention[]
  sessions             AppSession[]
  backup               AppBackup?
  reviews              AppReview[]
  resets               AccountReset[]

  @@map("feedback_authors")
}

/// What somebody had set up, so a reinstall does not start from nothing.
///
/// **The certificate is deliberately not in it, and that is the whole shape of
/// this feature.** A signing certificate is the thing that signs apps in
/// somebody's Apple ID's name, and a server holding everyone's would be a single
/// place worth breaking into for a prize far larger than anything else here.
/// What is worth restoring is the tedious part: the sources, the signing
/// options, the preferences. The certificate is asked for once on the way back
/// in, which somebody has to hand anyway.
///
/// One row per account, replaced rather than versioned. This answers "put it
/// back the way it was", and a history of the way it was last week is a
/// different feature with a different cost.
model AppBackup {
  installId     String         @id @map("install_id")
  author        FeedbackAuthor @relation(fields: [installId], references: [installId], onDelete: Cascade)
  /// The app's own JSON, opaque here. The server does not read it, does not
  /// migrate it and does not validate its contents beyond its size: the app owns
  /// the shape and `formatVersion` is how it tells its future self what it wrote.
  payload       String
  /// Bumped by the app when the shape changes, so an older app can decline a
  /// newer backup rather than restoring half of it.
  formatVersion Int            @map("format_version")
  /// Which device last wrote it, so somebody restoring can see whether it is the
  /// one they expected. A model name, like `AppSession.deviceName`.
  deviceName    String?        @map("device_name")
  bytes         Int
  createdAt     DateTime       @default(now()) @map("created_at")
  updatedAt     DateTime       @updatedAt @map("updated_at")

  @@map("app_backups")
}

/// One signed-in device.
///
/// The reason accounts are worth having at all: `installId` says "the same phone
/// as last time" and cannot survive a reinstall, so before this a name and
/// everything posted under it were tied to one copy of the app. A session lets a
/// person prove on a *new* device that the author row is theirs, and from then
/// on that row is what they write as. The install id stops being the identity.
///
/// A row rather than a signed token: signing out, or an operator cutting a
/// device off, takes effect on the next request instead of whenever a token
/// would have run out on its own. It is also what makes the device list in
/// FTStore Admin possible at all, which is the actual answer to "is this being
/// passed around?" on a private deployment.
model AppSession {
  /// SHA-256 of the token the app holds, hex.
  ///
  /// Deliberately a fast hash, and deliberately not the plaintext the operator
  /// `Session` stores. The token is 256 bits from the CSPRNG, so there is no
  /// candidate list to work through and nothing scrypt would buy; what the hash
  /// does buy is that a leaked table is not a pile of working credentials.
  tokenHash  String         @id @map("token_hash")
  /// The author row this device writes as.
  installId  String         @map("install_id")
  author     FeedbackAuthor @relation(fields: [installId], references: [installId], onDelete: Cascade)
  /// What the person sees in the device list, so signing one out is a decision
  /// they can make rather than a guess. Model name only, never an identifier.
  deviceName String?        @map("device_name")
  /// `app` for FTStore, `web` for a browser signed in to a private deployment's
  /// landing page. Kept apart because signing every browser out is a different
  /// decision from locking every phone out of the store, which is the one thing
  /// worth keeping from the pass rows this replaced.
  kind       String         @default("app")
  /// ISO 3166-1 alpha-2 from the reverse proxy, or null when nothing supplied
  /// one. Never looked up from a third party, so this is blank rather than wrong
  /// on a deployment whose proxy says nothing.
  country    String?
  createdAt  DateTime       @default(now()) @map("created_at")
  expiresAt  DateTime       @map("expires_at")
  /// Stamped at most once an hour, like `FeedbackAuthor.lastSeenAt`, and for the
  /// same reason: enough to tell a live device from a forgotten one, and not a
  /// log of what it did.
  lastSeenAt DateTime?      @map("last_seen_at")

  @@index([installId])
  @@map("app_sessions")
}

/// One `@handle` somebody wrote, resolved to the install behind it.
///
/// A row rather than a `contains` query over the bodies: `@ann` is a prefix of
/// `@anna`, so searching the text for your own handle finds threads that never
/// mentioned you, and a rename would silently rewrite who was pinged. Resolving
/// once at write time also means the mention keeps pointing at the person who
/// was meant even after they change their name.
///
/// Rewritten wholesale whenever the text it came from is edited, which is why
/// there is no "still current" flag: the rows for a post are the mentions in it.
model FeedbackMention {
  id        Int              @id @default(autoincrement())
  ticketId  Int              @map("ticket_id")
  ticket    FeedbackTicket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  /// Null when the mention is in the report itself rather than in a reply.
  commentId Int?             @map("comment_id")
  comment   FeedbackComment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  /// Who was mentioned.
  installId String           @map("install_id")
  author    FeedbackAuthor   @relation(fields: [installId], references: [installId], onDelete: Cascade)
  createdAt DateTime         @default(now()) @map("created_at")

  @@index([installId, ticketId])
  @@index([ticketId, commentId])
  @@map("feedback_mentions")
}

/// One feature request or bug report on the public in-app board.
///
/// Unlike `TweakRequest`, which is a private queue between one install and the
/// operators, this is readable by every FTStore user. Everything written here is
/// published to the app, so the composer says so and the moderation fields below
/// exist to take something down.
model FeedbackTicket {
  id             Int       @id @default(autoincrement())
  /// The install that opened it. Never serialised to the app.
  installId      String    @map("install_id")
  /// "bug" | "feature". Validated in `lib/feedback.ts`, not here: the containers
  /// run `prisma db push`, which drops anything the schema can't express.
  kind           String
  title          String
  body           String
  /// open, planned, in_progress, resolved, declined, duplicate. The last three
  /// count as closed; `lib/feedback.ts` owns that table.
  status         String    @default("open")
  /// Taken down by an operator. Gone from the board, still visible to its author
  /// (marked as removed) and to operators, so a takedown is never silent.
  hidden         Boolean   @default(false)
  hiddenReason   String?   @map("hidden_reason")
  /// Set the first time the author edits, so the app can mark it "edited".
  editedAt       DateTime? @map("edited_at")
  /// Triage only, and operator-only on the wire: a bug report attaches these so
  /// nobody has to ask "which version?", but the board does not publish the
  /// reporter's device.
  clientVersion  String?   @map("client_version")
  systemVersion  String?   @map("system_version")
  deviceModel    String?   @map("device_model")
  /// Denormalised counters. Recomputed from the rows on every write rather than
  /// incremented, so a lost update heals itself on the next one. They exist
  /// because the board sorts and pages on them in SQL, and neither "distinct
  /// installs that voted this up" nor "up minus down" is something Prisma's
  /// `orderBy` can express.
  ///
  /// `score` is the one the board ranks on and it is stored rather than computed
  /// for exactly that reason. All three are columns the reaction era did not
  /// have, and their names are new on purpose: `upvotes` counted glyphs, and a
  /// counter that kept that value while the rows behind it were replaced would
  /// be a number with nothing to click through to. `prisma/pre-push.sql` seeds
  /// them from the votes it carries over, once, on the deploy that drops it.
  upvotes        Int       @default(0) @map("vote_up")
  downvotes      Int       @default(0) @map("vote_down")
  score          Int       @default(0) @map("vote_score")
  commentCount   Int       @default(0) @map("comment_count")
  /// Bumped by a reply, so "recently active" is a cheap sort.
  lastActivityAt DateTime  @default(now()) @map("last_activity_at")
  /// Held at the top of the board by an operator. A timestamp rather than a
  /// flag, so several pinned reports keep an order of their own and "since
  /// when" is answerable without a second column.
  pinnedAt       DateTime? @map("pinned_at")
  /// Somebody wrote `@FTStore` in the report. The team is not an install, so it
  /// cannot own a `FeedbackMention` row; this is how being asked for reaches an
  /// operator instead.
  teamMentioned  Boolean   @default(false) @map("team_mentioned")
  /// The last edit was an operator's, not the author's. `editedAt` alone would
  /// let the board be rewritten in somebody else's voice without saying so,
  /// which is the one thing a public board must never do quietly.
  editedByTeam   Boolean   @default(false) @map("edited_by_team")
  createdAt      DateTime  @default(now()) @map("created_at")
  updatedAt      DateTime  @updatedAt @map("updated_at")

  comments    FeedbackComment[]
  votes       FeedbackVote[]
  mentions    FeedbackMention[]
  revisions   FeedbackRevision[]
  attachments FeedbackAttachment[]

  @@index([hidden, status, lastActivityAt])
  @@index([hidden, score])
  @@index([hidden, pinnedAt])
  @@index([installId, createdAt])
  @@map("feedback_tickets")
}

/// One entry in a ticket's thread: a reply, a status change, or both.
///
/// Status changes are stored here rather than in a table of their own so the
/// thread is one ordered list and the history survives. An operator who changes
/// the status and writes a note produces a single entry carrying both.
model FeedbackComment {
  id            Int            @id @default(autoincrement())
  ticketId      Int            @map("ticket_id")
  ticket        FeedbackTicket @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  /// The install that wrote it. Null on a team entry.
  installId     String?        @map("install_id")
  /// The operator behind a team entry. Stored for the audit trail and **never**
  /// serialised to the app: replies are shown as "FTStore Team", full stop. No
  /// relation, mirroring `TweakRequest.decidedByUserId` — deleting an operator
  /// account must not cascade away what they wrote.
  authorUserId  Int?           @map("author_user_id")
  /// "user" | "team". This, not the display name, is what draws the team badge.
  authorRole    String         @default("user") @map("author_role")
  /// Null on a bare status change, which renders as a timeline chip.
  body          String?
  statusFrom    String?        @map("status_from")
  statusTo      String?        @map("status_to")
  hidden        Boolean        @default(false)
  hiddenReason  String?        @map("hidden_reason")
  /// Taken back by the person who wrote it. Gone from the app for everyone,
  /// including its author, and still here for operators, who see it struck
  /// through: a thread other people answered must not be rewritable after the
  /// fact by deleting the line they were answering.
  ///
  /// Distinct from `hidden`, which is a takedown by an operator, and the two are
  /// never folded together: one says "we removed this", the other "they did".
  deletedAt     DateTime?      @map("deleted_at")
  editedAt      DateTime?      @map("edited_at")
  /// See `FeedbackTicket.editedByTeam`. A reply an operator rewrote says so.
  editedByTeam  Boolean        @default(false) @map("edited_by_team")
  /// See `FeedbackTicket.teamMentioned`.
  teamMentioned Boolean        @default(false) @map("team_mentioned")
  createdAt     DateTime       @default(now()) @map("created_at")
  updatedAt     DateTime       @updatedAt @map("updated_at")

  votes       FeedbackVote[]
  mentions    FeedbackMention[]
  revisions   FeedbackRevision[]
  attachments FeedbackAttachment[]

  @@index([ticketId, createdAt])
  @@map("feedback_comments")
}

/// One install's vote on a ticket (`commentId` null) or on one of its replies.
///
/// `value` is `1` or `-1` and nothing else: an install either wants this or it
/// does not, and taking the vote back deletes the row rather than storing a
/// third value. That is what keeps `score` a plain sum.
///
/// There is no unique constraint, and that is not an oversight: `commentId` is
/// nullable, and Postgres treats NULLs in a unique index as distinct, so the one
/// index that matters here cannot be expressed. One-vote-per-install is enforced
/// in `lib/feedback.ts`, which replaces inside a transaction, and every tally
/// folds by install so a lost race still counts one person once.
/// What a report or a reply said before somebody changed it.
///
/// The board is public and people answer each other on it, so an edit that left
/// no trace would let the thing a reply was answering be swapped out from under
/// it. GitHub shows the same history for the same reason.
///
/// One row per edit, holding the text as it read **before** that edit, so the
/// live row is always the current version and the trail behind it is these,
/// newest last. An author's edit and an operator's are both recorded; who made
/// it is `editorRole`, and which operator is kept for the log and never served.
///
/// Both relations cascade. Taking a post down has to take its history with it,
/// or editing would stop being a way to retract something and the trail would
/// outlive the post it belongs to.
model FeedbackRevision {
  id           Int              @id @default(autoincrement())
  ticketId     Int              @map("ticket_id")
  ticket       FeedbackTicket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  /// Null when the snapshot is of the report itself rather than of a reply.
  commentId    Int?             @map("comment_id")
  comment      FeedbackComment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  /// The title as it read before. Null on a reply, which has none.
  title        String?
  /// The body as it read before. Never null: an edit that changed nothing is
  /// not recorded at all.
  body         String
  /// The install that made the edit. Null when an operator did.
  installId    String?          @map("install_id")
  /// The operator who made it. Audit only, never serialised.
  editorUserId Int?             @map("editor_user_id")
  /// "user" | "team", so a reader can tell whose hand it was.
  editorRole   String           @default("user") @map("editor_role")
  createdAt    DateTime         @default(now()) @map("created_at")

  @@index([ticketId, commentId, createdAt])
  @@map("feedback_revisions")
}

model FeedbackVote {
  id          Int              @id @default(autoincrement())
  ticketId    Int              @map("ticket_id")
  ticket      FeedbackTicket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  commentId   Int?             @map("comment_id")
  comment     FeedbackComment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  /// The install that cast it. Null on a team vote, which is cast by an
  /// operator and has no install behind it.
  installId   String?          @map("install_id")
  /// The operator behind a team vote. Audit only and **never** serialised: the
  /// board says FTStore backed something, never which operator did. No relation,
  /// mirroring `FeedbackComment.authorUserId` — deleting an account must not
  /// cascade away the standing it gave a request.
  voterUserId Int?             @map("voter_user_id")
  /// "user" | "team". This, not the absent install, is what marks the vote, for
  /// the same reason `FeedbackComment.authorRole` exists: one column to read
  /// rather than a null to interpret.
  voterRole   String           @default("user") @map("voter_role")
  /// `1` for up, `-1` for down. Validated in `lib/feedback.ts`, since `db push`
  /// would drop a CHECK constraint on the next deploy.
  value       Int
  createdAt   DateTime         @default(now()) @map("created_at")
  updatedAt   DateTime         @updatedAt @map("updated_at")

  @@index([ticketId, commentId])
  @@index([installId])
  @@map("feedback_votes")
}

/// A screenshot somebody attached to a report, or to a reply on one.
///
/// The picture lives on disk under `shots/`, exactly like an IPA does, and this
/// row is the only thing that says it is still wanted. `sweepOrphans()` deletes
/// files no row points at, so a cascade is enough to reclaim the bytes — which
/// matters, because emptying a board takes thousands of these with it and
/// nothing else would ever go looking for them.
///
/// `bytes` is stored rather than measured from disk because the storage cap is a
/// database aggregate (see `lib/storage/quota.ts`) and a cap that had to walk a
/// directory could not be checked on every write.
///
/// `installId` is kept even though the ticket already has one: a reply's author
/// is not the report's author, and "who uploaded this" is the question a
/// takedown needs answered.
model FeedbackAttachment {
  id           Int              @id @default(autoincrement())
  ticketId     Int              @map("ticket_id")
  ticket       FeedbackTicket   @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  /// Null when the picture hangs on the report itself rather than on a reply.
  commentId    Int?             @map("comment_id")
  comment      FeedbackComment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  /// The install that uploaded it. Never serialised to the app.
  installId    String           @map("install_id")
  /// Repo-relative, `shots/<xy>/<32-hex>.<ext>`. Validated on the way out of the
  /// database rather than trusted, like `DownloadedIpa.storagePath`.
  ///
  /// Unique, because it already is by construction — the name is 128 bits of
  /// randomness minted per file — and saying so gives the serving route an index
  /// to seek. That route runs on every image request, so the alternative is a
  /// sequential scan per thumbnail.
  storagePath  String           @unique @map("storage_path")
  /// Size on disk after metadata stripping, which is what the cap counts.
  bytes        Int
  /// What the app measured before uploading, so a thumbnail can reserve the
  /// right shape instead of jumping when the picture lands. Null when the
  /// uploader did not say.
  width        Int?
  height       Int?
  /// Taken down by an operator. The row and the file stay, so a takedown is
  /// reversible and the bytes keep counting against the cap: hiding a picture is
  /// not a way to store it for free.
  hidden       Boolean          @default(false)
  hiddenReason String?          @map("hidden_reason")
  createdAt    DateTime         @default(now()) @map("created_at")

  @@index([ticketId, commentId])
  @@index([installId])
  @@map("feedback_attachments")
}

/// One person's review of one app in the store, App Store style: a star rating,
/// a headline, a body, and a public answer from the FTStore Team underneath it.
///
/// Read this next to `FeedbackTicket`, because the two are public boards with
/// the same moderation vocabulary and one structural difference that decides
/// everything else: **a ticket is a thread, a review is a verdict.** Nobody
/// replies to a review, so it has no comment table, no status and no vote — what
/// it has instead is a number that the store aggregates and prints next to the
/// app's name. That number is the whole reason the rules below are stricter than
/// the board's.
///
/// **`appKey` is the identity, and it is the feed's own grouping key.** The
/// store publishes one listing per `bundleId::tweak` variant, so YouTube with
/// uYou+ and YouTube with Cercube are two apps to a reader and must be two
/// apps to a reviewer. Keying on `bundleId` alone would pool every tweak of one
/// app into one rating, which is the single worst thing this table could get
/// wrong: it would put a verdict about somebody else's build under yours.
/// `lib/reviews.ts` owns the key and resolves it through the known-tweak aliases
/// on the way in, so a build matched by an alias lands on the primary name.
///
/// **One review per account per app**, and that one is a real unique index
/// rather than a rule in the library. It is expressible here — neither column is
/// nullable — and it is what makes "write a review" and "edit your review" the
/// same operation, exactly as the App Store has it. A review its author deleted
/// keeps its row (see `deletedAt`) and is revived rather than duplicated if they
/// write again.
model AppReview {
  id             Int            @id @default(autoincrement())
  /// The feed's grouping key: `bundleId` or `bundleId::TweakName`. Canonical by
  /// the time it is stored — see `canonicalAppKey` in `lib/reviews.ts`.
  appKey         String         @map("app_key")
  /// The two halves of the key, kept apart so the operator console can filter by
  /// app without parsing, and so a key whose tweak was later renamed still says
  /// what it was about.
  bundleId       String         @map("bundle_id")
  tweakName      String?        @map("tweak_name")
  /// What the store called the app when this was written.
  ///
  /// A snapshot and never the authority: the feed is. It exists because a review
  /// outlives the build it is about — an operator reading the moderation queue
  /// needs to know which app this is even after every IPA of it has been swept,
  /// and "app_key: com.burbn.instagram::Rocket" is not a name.
  appName        String         @map("app_name")
  /// Which build the reviewer had, as the store advertised it. Printed beside
  /// the review the way the App Store prints it, because a one-star review of a
  /// version from a year ago says something different from one of today's.
  /// Null when the app did not say.
  appVersion     String?        @map("app_version")
  /// The account that wrote it. Never serialised to the app: the byline is the
  /// author's handle, resolved at read time, exactly as on the feedback board.
  installId      String         @map("install_id")
  author         FeedbackAuthor @relation(fields: [installId], references: [installId], onDelete: Cascade)
  /// 1 to 5, and **only on a listing that names a tweak**. Null on a plain one,
  /// which has no rating at all: a signed build of Duolingo is Duolingo, and
  /// five stars for it says nothing about this copy of it.
  ///
  /// **This column is what says which scale a review is on.** `works` used to
  /// be, back when exactly one of the two was ever set; it cannot be any more,
  /// because a rated review may carry one too.
  ///
  /// Validated in `lib/reviews.ts`, not here: the containers boot with
  /// `prisma db push`, which drops a CHECK constraint without a word.
  rating         Int?
  /// Whether the build the reviewer had actually runs.
  ///
  /// **On either kind of listing, and that is the point.** On a plain one it is
  /// the whole verdict: there is nothing to have an opinion about, and the one
  /// open question is whether this copy installs and runs. On a tweak it sits
  /// *beside* the stars and answers a different question — the stars are about
  /// the tweak, this is about the build in front of you, and "great tweak, this
  /// build does not launch" is both coherent and the thing somebody about to
  /// install it most needs to know.
  ///
  /// Optional beside a rating and required without one. A review of a tweak is
  /// about the tweak; answering for today's build as well is worth asking for
  /// and not worth refusing the review over.
  works          Boolean?
  /// The headline. Null on a thumb review, which has none: a thumb is the whole
  /// verdict and asking for a title on top of it would be asking somebody to
  /// name a yes.
  title          String?
  /// What they wrote. Required beside a rating, optional beside a thumb, where
  /// it is the "what went wrong" that makes a thumbs down worth reading.
  body           String?
  /// They said this build is broken and asked an operator to look.
  ///
  /// Only reachable from a thumbs down, on either scale, and it is a *request*
  /// rather than a state:
  /// nobody else sees anything until an operator confirms it into
  /// `AppBuildFlag`. That gap is the whole point — a build that anyone could
  /// flag as broken from the app is a build anyone could take out of the store.
  reportedBroken Boolean        @default(false) @map("reported_broken")
  /// Taken down by an operator. Gone from the store, still visible to its author
  /// marked as removed, and still in both consoles, so a takedown is never
  /// silent and is always reversible.
  ///
  /// A hidden review is out of the average as well as out of the list. That is
  /// the point of hiding one: a rating nobody may read should not go on shaping
  /// the number under the app's name.
  hidden         Boolean        @default(false)
  hiddenReason   String?        @map("hidden_reason")
  /// Taken back by the person who wrote it. Distinct from `hidden` for the same
  /// reason it is on `FeedbackComment`: one says "we removed this", the other
  /// "they did", and only the first is the operator's to undo.
  ///
  /// Soft, because a review can have been answered in public by the team, and a
  /// hard delete would let somebody provoke an answer and then remove the line
  /// it was answering. Writing a review again revives this row.
  deletedAt      DateTime?      @map("deleted_at")
  editedAt       DateTime?      @map("edited_at")
  /// The last edit was an operator's. See `FeedbackTicket.editedByTeam`: a store
  /// where somebody else can rewrite your verdict without it showing is not one
  /// anybody should trust.
  editedByTeam   Boolean        @default(false) @map("edited_by_team")

  /// The FTStore Team's public answer, which is the App Store's developer
  /// response and behaves like one: one per review, shown underneath it, and
  /// editable.
  ///
  /// Columns rather than a table because there is exactly one and it belongs to
  /// the review — a second answer would be a conversation, and a review is not
  /// one. Its history goes in `AppReviewRevision` under `target: "response"`.
  responseBody     String?   @map("response_body")
  responseAt       DateTime? @map("response_at")
  /// The operator who wrote it. Audit only and **never** serialised: an answer
  /// is from FTStore, full stop, exactly like a team reply on the board.
  responseUserId   Int?      @map("response_user_id")
  responseEditedAt DateTime? @map("response_edited_at")

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  revisions AppReviewRevision[]

  @@unique([appKey, installId])
  /// The store's own read: one app's visible reviews, newest first.
  @@index([appKey, hidden, deletedAt, createdAt])
  /// And the same list ordered by stars, which is what "Most Favourable" and
  /// "Most Critical" are.
  @@index([appKey, hidden, deletedAt, rating])
  /// The queue an operator works: builds somebody says are broken.
  @@index([reportedBroken, createdAt])
  @@index([installId])
  @@index([bundleId])
  @@map("app_reviews")
}

/// An operator's verdict on whether a listing's build actually works.
///
/// The confirmed half of a broken report. Users can say a build is broken
/// (`AppReview.reportedBroken`) and nobody sees that but an operator; this row
/// is what an operator writes after looking, and it is what the store publishes.
/// The gap between the two is deliberate: a flag anyone could raise from the app
/// is a way for anyone to put a warning on somebody else's build.
///
/// **One row per listing, naming one version**, and that shape is what makes the
/// rule below expressible. A build is confirmed broken *at a version*. Anything
/// newer than that has not been looked at, so it is not called broken and it is
/// not called fine either — the store says "may be broken" until an operator
/// says otherwise. Clearing the flag deletes the row, which takes both states
/// with it in one act.
model AppBuildFlag {
  id              Int      @id @default(autoincrement())
  /// The feed's grouping key, exactly as `AppReview.appKey`.
  appKey          String   @unique @map("app_key")
  /// The version an operator confirmed broken. Compared against whatever the
  /// feed is publishing now, which is what turns one row into two states.
  version         String
  /// What is wrong with it, in the operator's words. Published, because "this
  /// build is broken" with no reason is the kind of warning people learn to
  /// ignore.
  note            String?
  /// How many people had reported it when the operator decided. A snapshot for
  /// the log rather than a counter: the reports themselves are still on their
  /// reviews and can be counted again at any time.
  reports         Int      @default(0)
  /// Who decided. Audit only and never serialised: the store says a build is
  /// broken, never which operator said so.
  decidedByUserId Int?     @map("decided_by_user_id")
  decidedAt       DateTime @default(now()) @map("decided_at")
  createdAt       DateTime @default(now()) @map("created_at")
  updatedAt       DateTime @updatedAt @map("updated_at")

  @@map("app_build_flags")
}

/// What a review, or the team's answer to it, said before somebody changed it.
///
/// The same trail `FeedbackRevision` keeps and for a sharper reason: a review
/// carries a number, and an edit that could quietly turn one star into five with
/// nothing to show for it would make every rating in the store worth less. So
/// the rating is snapshotted alongside the text, an author's edit and an
/// operator's are both recorded, and `editorRole` says whose hand it was.
///
/// `target` is which of the two texts this is a version of. One table rather
/// than two because they are the same three columns and the same audience: a
/// reader opening the history wants the whole story of the row, not two of them.
model AppReviewRevision {
  id           Int       @id @default(autoincrement())
  reviewId     Int       @map("review_id")
  review       AppReview @relation(fields: [reviewId], references: [id], onDelete: Cascade)
  /// `review` or `response`.
  target       String    @default("review")
  /// The headline as it read before. Null on a response, which has none.
  title        String?
  /// The text as it read before. Never null: an edit that changed nothing is
  /// not recorded at all.
  body         String
  /// The stars as they stood before. Null on a response.
  rating       Int?
  /// Whether the build was said to work before. Null on a response, and null on
  /// a review whose author never answered.
  ///
  /// Recorded for the same reason the rating is: on a tweak this is a claim
  /// about a build that other people are deciding whether to install, and an
  /// edit that turns "it does not launch" into "it works" with the words left
  /// alone is exactly the edit a text-only trail records as nothing at all.
  works        Boolean?
  /// The account that made the edit. Null when an operator did.
  installId    String?   @map("install_id")
  /// The operator who made it. Audit only, never serialised.
  editorUserId Int?      @map("editor_user_id")
  /// `user` or `team`, so a reader can tell whose edit followed this version.
  editorRole   String    @default("user") @map("editor_role")
  createdAt    DateTime  @default(now()) @map("created_at")

  @@index([reviewId, target, createdAt])
  @@map("app_review_revisions")
}

model User {
  id           Int       @id @default(autoincrement())
  username     String    @unique
  passwordHash String    @map("password_hash")
  role         String    @default("admin") // all approved users are admin
  approved     Boolean   @default(false)
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")
  sessions     Session[]

  @@map("users")
}

model Session {
  id        Int      @id @default(autoincrement())
  token     String   @unique
  userId    Int      @map("user_id")
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("sessions")
}

/// An invitation to create an account on a private deployment.
///
/// Private mode used to be a six-digit code per person, verified against every
/// active code in turn and traded for a pass row. Accounts made that a
/// second credential doing a worse version of the same job: a code said "you may
/// look", an account says who you are, and only one of them can carry what
/// somebody wrote. So the code is gone and this is what replaced it — not a way
/// past the door, but a way to be given a key of your own.
///
/// **One invite, one account.** `usedAt` is set the moment it is redeemed and it
/// never opens a second one, which is what keeps a link that was forwarded from
/// quietly becoming a shared secret. Everything else about it stays the shape
/// the codes had, and for the same reasons: a `label` so an operator knows whose
/// it was, `revokedAt` so withdrawing it is one action, and an expiry so a link
/// that was never used stops being a loose end.
///
/// The token is a 128-bit capability in the URL, like `/d/<token>` and the
/// install slots, and it is **stored as SHA-256**. A fast hash rather than
/// scrypt: 128 bits from the CSPRNG has no candidate list to work through, and
/// the digest is what keeps a leaked table from being a pile of working
/// invitations. That is also why it can carry a unique index where the codes
/// could not — there is nothing to salt against.
model RegistrationInvite {
  id        Int       @id @default(autoincrement())
  /// SHA-256 of the token, hex. The token itself is shown once, in the response
  /// that mints it, and is never stored.
  tokenHash String    @unique @map("token_hash")
  /// Who it is for, in the operator's own words. Unique, because two invites
  /// called the same thing are two rows nobody can tell apart on the screen
  /// that revokes them — the same rule the codes had.
  label     String    @unique
  /// Set when it is redeemed, together with the account it created. Both stay
  /// afterwards, so the account list can say which invitation let somebody in.
  usedAt    DateTime? @map("used_at")
  usedBy    String?   @map("used_by")
  /// Set when an operator withdraws it. A used invite cannot be withdrawn in any
  /// meaningful sense — the account exists — so this only ever stops one that
  /// has not been redeemed.
  revokedAt DateTime? @map("revoked_at")
  expiresAt DateTime  @map("expires_at")
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")

  @@index([expiresAt])
  @@map("registration_invites")
}

/// A one-time link that lets somebody set a new password on their account.
///
/// The answer to "I forgot it" on a server with no email to send anything to.
/// An operator mints one from the user's row and hands the link over however
/// they already talk to that person; opening it asks for a new password and
/// nothing else.
///
/// **Shaped exactly like `RegistrationInvite`, and for the same reasons.** The
/// token is 128 bits from the CSPRNG stored as SHA-256, which is a fast hash on
/// purpose: there is no candidate list to work through, so scrypt would buy
/// nothing and a digest is what keeps a leaked table from being a pile of
/// working links. It is shown once, in the response that mints it.
///
/// **One link, one use.** `usedAt` is set the moment it is spent and never opens
/// a second reset, which is what keeps a forwarded link from becoming a way back
/// in later. The redeem is an `updateMany` filtered on `usedAt: null`, so two
/// people racing one link end with one password and one refusal.
///
/// Minting a new one **invalidates every earlier link for that account**. An
/// operator who sends a second because the first went astray should not be
/// leaving the first one live, and there is no reason to be able to have two.
model AccountReset {
  id              Int            @id @default(autoincrement())
  /// SHA-256 of the token, hex. The token itself is never stored.
  tokenHash       String         @unique @map("token_hash")
  /// The account it resets.
  installId       String         @map("install_id")
  author          FeedbackAuthor @relation(fields: [installId], references: [installId], onDelete: Cascade)
  /// The operator who minted it. Audit only, never served.
  createdByUserId Int?           @map("created_by_user_id")
  usedAt          DateTime?      @map("used_at")
  expiresAt       DateTime       @map("expires_at")
  createdAt       DateTime       @default(now()) @map("created_at")

  @@index([installId])
  @@index([expiresAt])
  @@map("account_resets")
}

/// How many requests reached the gate, by day, country and outcome.
///
/// Deliberately a counter and not a log. "Which countries are opening the site"
/// is answerable from three small columns, and the version of it that keeps a
/// row per visit would be a record of who loaded what and when — which is the
/// thing nobody should have to trust us about, and which the board's
/// `lastSeenAt` comment already refuses to keep for the app.
///
/// `country` is `ZZ` when the proxy supplied none, so the unknown share is
/// visible instead of silently missing.
model AccessStat {
  id      Int      @id @default(autoincrement())
  day     DateTime @db.Date
  country String
  /// `opened` (a signed-in visitor got through, or an invitation was just
  /// redeemed) or `refused` (nobody signed in, wrong credentials, a throttle).
  outcome String
  count   Int      @default(0)

  @@unique([day, country, outcome])
  @@index([day])
  @@map("access_stats")
}