dorfteich/apps/api/prisma/schema.prisma

// Prisma schema — the single source of truth for the database structure.
// The entity documentation lives in docs/architecture/data-model.md; keep
// both in sync when the schema evolves.

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

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

/// Typed key-value configuration for the instance (registration mode,
/// default quotas, legal pages, …). Values are validated with Zod before
/// writing; see the InstanceSettings service (issue #19).
model InstanceSetting {
  key       String   @id
  value     Json
  updatedAt DateTime @updatedAt

  @@map("instance_settings")
}

enum UserStatus {
  PENDING_VERIFICATION
  ACTIVE
  DISABLED
}

/// Account profile. Login methods live in UserIdentity (OIDC-ready,
/// ADR 0007); Site Admin is a user flag, all other roles are grants.
model User {
  id                 String     @id @default(uuid())
  username           String     @unique
  email              String     @unique
  displayName        String     @map("display_name")
  locale             String     @default("en")
  isSiteAdmin        Boolean    @default(false) @map("is_site_admin")
  /// True when the flag was last SET by the IdP claim mapping (issue #217):
  /// only then may the mapping revoke it again on a later login. A manual
  /// admin toggle clears the marker, so hand-granted admins are never
  /// demoted by a missing claim.
  isSiteAdminManaged Boolean    @default(false) @map("is_site_admin_managed")
  /// Auto-watch preferences (issue #93): watch pages I create / comment on.
  autoWatchOwnPages  Boolean    @default(true) @map("auto_watch_own_pages")
  autoWatchOnComment Boolean    @default(true) @map("auto_watch_on_comment")
  /// E-mail digest cadence (issue #95): hourly | daily | off.
  digestFrequency    String     @default("hourly") @map("digest_frequency")
  status             UserStatus @default(PENDING_VERIFICATION)
  emailVerifiedAt    DateTime?  @map("email_verified_at")
  createdAt          DateTime   @default(now()) @map("created_at")
  lastLoginAt        DateTime?  @map("last_login_at")

  identities     UserIdentity[]
  sessions       Session[]
  authTokens     AuthToken[]
  apiTokens      ApiToken[]
  feedTokens     FeedToken[]
  mentionRows    PageMention[]
  ponds          Pond[]
  pages          Page[]
  attachments    Attachment[]
  conversionJobs ConversionJob[]
  auditEntries   AuditEntry[]
  comments       Comment[]
  watches        Watch[]
  notifications  Notification[]
  favorites      PageFavorite[]
  customFonts    CustomFont[]

  @@map("users")
}

/// Persistent audit trail (issue #86, security.md §Logging): auth events and
/// admin actions — grants, member roles, plugin installs, quota and settings
/// changes, setup steps, manual job triggers. Written by AuditService, which
/// also keeps emitting the established `audit: …` stdout log line. Content
/// activity (pages, files, exports) stays log-only by design.
model AuditEntry {
  id         String   @id @default(uuid())
  at         DateTime @default(now())
  /// Stable dot-namespaced action id, e.g. `grant.created`, `auth.login_failed`.
  action     String
  /// Null for anonymous events (failed login for an unknown user) and after a
  /// hard account deletion; pseudonymized accounts keep their id.
  actorId    String?  @map("actor_id")
  targetType String?  @map("target_type")
  targetId   String?  @map("target_id")
  details    Json?

  actor User? @relation(fields: [actorId], references: [id], onDelete: SetNull)

  @@index([at])
  @@index([actorId, at])
  @@index([action, at])
  @@map("audit_log")
}

/// Read-access trail for classified pages (issue #222, ADR 0023): one row per
/// read of a `VS_NFD` page, per channel. Separate from `audit_log` because
/// volume, purpose and legal basis all differ. Deliberately WITHOUT foreign
/// keys: evidence must survive a page purge and a hard user deletion — the
/// ids stay as recorded (pseudonymous uuids), history is never rewritten.
///
/// In migrated databases the table is RANGE-partitioned by `occurred_at`
/// (monthly, issue #224) — hence the composite id. The dedup unique pair
/// lives per partition there (a partitioned parent cannot carry it without
/// the partition key); `db push` test databases get it on the plain table.
model ReadEvent {
  id             String   @default(uuid())
  occurredAt     DateTime @default(now()) @map("occurred_at")
  /// Null = anonymous reader (public grant); `sessionKey` still names the
  /// browsing session, so the anonymous marker is explicit, not an accident.
  actorId        String?  @map("actor_id")
  /// `session:<id>` for cookie sessions, `token:<id>` for PATs, `job:<id>`
  /// for background builds (account data export), `anon` for anonymous
  /// visitors — the dedup-window key basis (#223).
  sessionKey     String   @map("session_key")
  pageId         String?  @map("page_id")
  pondId         String   @map("pond_id")
  /// Which read surface fired: `page_view` | `no_js_shell` | `public_api` |
  /// `attachment` | `export` | `collab_join` (READ_CHANNELS union in code).
  channel        String
  /// Classification at read time — a later reclassification must not
  /// rewrite history (ADR 0023).
  classification String
  details        Json?
  /// Dedup window (issue #223): `<sessionKey>:<pageId|->:<channel>` plus the
  /// aligned bucket `floor(epoch / windowSeconds)`. The unique pair makes
  /// concurrent duplicate reads collapse race-free (insert or P2002-skip).
  dedupKey       String   @map("dedup_key")
  windowBucket   BigInt   @map("window_bucket")
  /// Window length the event was recorded under — the row itself states it
  /// represents up to this many seconds, so the evidence is not overread.
  windowSeconds  Int      @map("window_seconds")

  @@id([id, occurredAt])
  @@unique([dedupKey, windowBucket])
  @@index([pageId, occurredAt])
  @@index([actorId, occurredAt])
  @@index([occurredAt])
  @@map("read_events")
}

/// Threaded page comments (issue #91, data-model.md §Comments). Threads are
/// one level deep: roots carry the optional document anchor and the resolve
/// state, replies reference the root via `parentId`. Purging a page cascades
/// its comments; trashing merely hides them (the list endpoint resolves the
/// page as a live page). Authors survive pseudonymization ("deleted user");
/// only a hard user delete nulls them.
model Comment {
  id         String    @id @default(uuid())
  pageId     String    @map("page_id")
  parentId   String?   @map("parent_id")
  authorId   String?   @map("author_id")
  /// Markdown; rendered through the shared sanitizing pipeline on read.
  body       String
  /// Opaque serialized document position (roots only).
  anchor     String?
  resolvedAt DateTime? @map("resolved_at")
  resolvedBy String?   @map("resolved_by")
  createdAt  DateTime  @default(now()) @map("created_at")
  editedAt   DateTime? @map("edited_at")

  page    Page      @relation(fields: [pageId], references: [id], onDelete: Cascade)
  parent  Comment?  @relation("thread", fields: [parentId], references: [id], onDelete: Cascade)
  replies Comment[] @relation("thread")
  author  User?     @relation(fields: [authorId], references: [id], onDelete: SetNull)

  @@index([pageId, createdAt])
  @@index([parentId])
  @@map("comments")
}

enum WatchTargetType {
  PAGE
  POND
}

/// Explicit subscription (issue #93, data-model.md §watches): the target is
/// polymorphic (no FK) — page purge removes its watches via the trash
/// service, and the list endpoint filters targets the user can no longer
/// read, so stale rows are invisible and harmless.
model Watch {
  id         String          @id @default(uuid())
  userId     String          @map("user_id")
  targetType WatchTargetType @map("target_type")
  targetId   String          @map("target_id")
  createdAt  DateTime        @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([userId, targetType, targetId])
  @@index([targetType, targetId])
  @@map("watches")
}

/// In-app notification (issue #94, data-model.md §notifications). `payload`
/// carries the denormalized display data (page/pond names, actor names) so
/// the list renders without joins; permission is re-checked at generation
/// time, not at read time. `mailedAt` is the e-mail digest's bookkeeping
/// (issue #95) — independent of `readAt`.
model Notification {
  id        String    @id @default(uuid())
  userId    String    @map("user_id")
  type      String
  payload   Json
  createdAt DateTime  @default(now()) @map("created_at")
  readAt    DateTime? @map("read_at")
  mailedAt  DateTime? @map("mailed_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId, readAt, createdAt])
  @@map("notifications")
}

enum PondType {
  PERSONAL
  SHARED
}

/// Top-level content container (data-model.md §ponds). Personal ponds are
/// created automatically on e-mail verification; `settings` stores only
/// deviations from the defaults (pondSettingsSchema in @dorfteich/shared).
/// `deletedAt`/`deletedBy` implement the pond-level trash (ADR 0013).
model Pond {
  id          String    @id @default(uuid())
  slug        String    @unique
  name        String
  description String    @default("")
  type        PondType
  ownerId     String    @map("owner_id")
  settings    Json      @default("{}")
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")
  deletedAt   DateTime? @map("deleted_at")
  deletedBy   String?   @map("deleted_by")

  owner          User            @relation(fields: [ownerId], references: [id])
  usage          PondUsage?
  pages          Page[]
  attachments    Attachment[]
  labels         Label[]
  grants         RoleGrant[]
  conversionJobs ConversionJob[]
  pondPlugins    PondPlugin[]

  @@index([ownerId])
  @@map("ponds")
}

enum GrantSubjectType {
  USER
  AUTHENTICATED
  PUBLIC
}

enum GrantRole {
  POND_ADMIN
  EDITOR
  READER
}

enum GrantScopeType {
  POND
  LABEL
  PAGE
}

enum GrantEffect {
  ALLOW
  DENY
}

/// The permission table (permissions.md, data-model.md §role_grants, issue #51).
/// One grant `(subject, role, scope, effect)` inside a pond. The shared
/// resolution algorithm (`@dorfteich/shared` permissions) decides access from
/// these; the API/collab enforce it. Structural rules: `POND_ADMIN` only at
/// `POND` scope with a `USER` subject (a CHECK constraint backs this up, added
/// in the migration); personal ponds allow only their owner as admin (enforced
/// in the service, needs the pond type). `subjectId` is set only for `USER`
/// subjects; `scopeId` is the label/page id for `LABEL`/`PAGE` scopes.
model RoleGrant {
  id          String           @id @default(uuid())
  pondId      String           @map("pond_id")
  subjectType GrantSubjectType @map("subject_type")
  subjectId   String?          @map("subject_id")
  role        GrantRole
  scopeType   GrantScopeType   @map("scope_type")
  scopeId     String?          @map("scope_id")
  effect      GrantEffect
  /// `manual` (admin-created) or `idp` (written by the claim mapping,
  /// issue #217). The mapping only ever creates and revokes ITS OWN rows —
  /// manual grants are never touched, which is the documented precedence.
  origin      String           @default("manual")
  createdBy   String           @map("created_by")
  createdAt   DateTime         @default(now()) @map("created_at")

  pond Pond @relation(fields: [pondId], references: [id], onDelete: Cascade)

  @@unique([pondId, subjectType, subjectId, role, scopeType, scopeId])
  @@index([pondId])
  @@map("role_grants")
}

/// A wiki page (data-model.md §pages). Carries a Yjs document from day one
/// (ADR 0003) even though M2 saves it wholesale over REST; `ydocState` is
/// the merged state Y.Doc, decoded by the API to derive `PageContentCache`
/// on every save (issue #23). `sortKey` uses fractional indexing so pages
/// can be reordered without rewriting siblings (sidebar reorder is #26).
/// `parentId` nests pages into a tree (issue #106), mirroring the label
/// hierarchy (max 6 levels, enforced in the service; cycles rejected at write
/// time). Purely organizational: slugs stay flat and pond-unique, so moving a
/// page never changes its URL or breaks wikilinks. Trashed pages keep their
/// `parentId` (restore re-attaches to the nearest live ancestor, issue #107);
/// `SetNull` is only the FK backstop — purge promotes children explicitly.
/// VS-NfD marking level of a page (ADR 0022). Deliberately an enum on Page,
/// not a label: instance-wide meaning, not user-deletable in routine content
/// work, inherits down the tree (#205), reaches every output channel
/// (#206–#212). It is a MARKING, not a protection mechanism — separation of
/// levels happens outside the application (one instance per level).
enum PageClassification {
  UNCLASSIFIED
  VS_NFD
}

model Page {
  id             String             @id @default(uuid())
  pondId         String             @map("pond_id")
  parentId       String?            @map("parent_id")
  title          String
  slug           String
  ydocState      Bytes              @map("ydoc_state")
  sortKey        String             @map("sort_key")
  classification PageClassification @default(UNCLASSIFIED)
  createdBy      String             @map("created_by")
  createdAt      DateTime           @default(now()) @map("created_at")
  updatedAt      DateTime           @updatedAt @map("updated_at")
  deletedAt      DateTime?          @map("deleted_at")
  deletedBy      String?            @map("deleted_by")

  pond                Pond                     @relation(fields: [pondId], references: [id])
  parent              Page?                    @relation("PageHierarchy", fields: [parentId], references: [id], onDelete: SetNull)
  children            Page[]                   @relation("PageHierarchy")
  creator             User                     @relation(fields: [createdBy], references: [id])
  updates             PageUpdate[]
  contentCache        PageContentCache?
  attachments         Attachment[]
  versions            PageVersion[]
  pendingContributors PagePendingContributor[]
  mentionRows         PageMention[]
  labels              PageLabel[]
  outgoingLinks       PageLink[]               @relation("outgoingLinks")
  comments            Comment[]
  incomingLinks       PageLink[]               @relation("incomingLinks")
  conversionJobs      ConversionJob[]
  favorites           PageFavorite[]

  @@unique([pondId, slug])
  @@index([pondId])
  @@index([parentId])
  // Time-filtered listings (issue #148): "pages of this pond created/updated
  // since X" hit these instead of scanning the pond.
  @@index([pondId, createdAt])
  @@index([pondId, updatedAt])
  @@map("pages")
}

/// Append log for incremental Yjs updates (data-model.md), compacted
/// periodically. Unused by M2's whole-state REST saves; the collab
/// server's persistence hooks (#35) are the first real writer.
model PageUpdate {
  id     String @id @default(uuid())
  pageId String @map("page_id")
  seq    Int
  update Bytes

  page Page @relation(fields: [pageId], references: [id], onDelete: Cascade)

  @@unique([pageId, seq])
  @@map("page_updates")
}

enum PageVersionTrigger {
  AUTO
  MANUAL
  PRE_RESTORE
}

/// Version snapshot of a page (ADR 0013, data-model.md). `ydocSnapshot` is a
/// full, self-contained encoded Yjs state — restore never depends on the
/// update log, so compaction (#40) cannot lose restorable history.
/// `contributorIds` is the set of users who edited since the previous version
/// (derived from the live session, #41). Created automatically at session end
/// and on an active-editing interval by collab, and on demand (named) by the
/// api; `PRE_RESTORE` snapshots are written before a restore (#42).
model PageVersion {
  id             String             @id @default(uuid())
  pageId         String             @map("page_id")
  ydocSnapshot   Bytes              @map("ydoc_snapshot")
  trigger        PageVersionTrigger
  label          String?
  /// Who created this version: the editor for `MANUAL`/`PRE_RESTORE`, null for
  /// automatic snapshots (which have a contributor set instead of one author).
  createdBy      String?            @map("created_by")
  contributorIds String[]           @map("contributor_ids")
  createdAt      DateTime           @default(now()) @map("created_at")

  page Page @relation(fields: [pageId], references: [id], onDelete: Cascade)

  @@index([pageId, createdAt])
  @@map("page_versions")
}

/// Accumulator of users who have edited a page since its last version (#41).
/// Collab flushes the current session's contributors here (deduplicated by the
/// composite key); version creation on either side reads and clears it in the
/// same transaction as writing the snapshot. Cascades on page purge (ADR 0013).
/// Derived mention index (issue #151): one row per user currently
/// mentioned in the page's document. Rewritten on every collab persist;
/// the diff against the previous rows drives the `mentioned` notifications.
model PageMention {
  pageId String @map("page_id")
  userId String @map("user_id")

  page Page @relation(fields: [pageId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@id([pageId, userId])
  @@index([userId])
  @@map("page_mentions")
}

model PagePendingContributor {
  pageId String @map("page_id")
  userId String @map("user_id")

  page Page @relation(fields: [pageId], references: [id], onDelete: Cascade)

  @@id([pageId, userId])
  @@map("page_pending_contributors")
}

/// Live-session registry the collab server keeps current: one row per page
/// with an open collaboration session, refreshed by a heartbeat (issue #40).
/// The compaction job reads it to skip pages that are being edited; a stale
/// row (collab crashed without unloading) ages out via the heartbeat window,
/// so no FK to `pages` is needed and a leftover row is harmless. Collab is the
/// only writer, over raw SQL (it does not use Prisma).
model CollabOpenSession {
  pageId      String   @id @map("page_id")
  heartbeatAt DateTime @map("heartbeat_at")

  @@index([heartbeatAt])
  @@map("collab_open_sessions")
}

/// Derived plain representation refreshed on every state save (issue #23),
/// built from the Yjs state via the shared editor schema. `outline` is the
/// heading tree (`OutlineEntry[]` from @dorfteich/shared) as jsonb.
model PageContentCache {
  pageId       String                   @id @map("page_id")
  plainText    String                   @map("plain_text")
  markdown     String
  html         String
  outline      Json
  updatedAt    DateTime                 @updatedAt @map("updated_at")
  /// Weighted full-text search vector (title A, labels B, body C; issue #49,
  /// ADR 0010). Maintained by the SearchProvider and the collab persistence
  /// hook (both write it with the same weighting). The GIN index is added in
  /// the migration (raw SQL — Prisma cannot index an Unsupported column).
  searchVector Unsupported("tsvector")? @map("search_vector")

  page Page @relation(fields: [pageId], references: [id], onDelete: Cascade)

  @@map("page_content_cache")
}

/// A hierarchical label within a pond (data-model.md §labels, issue #43).
/// Labels organize pages and later scope permissions (M5): a grant on a label
/// applies to it and all its descendants, so the hierarchy must be sound now.
/// `parentId` builds the tree (max 6 levels, enforced in the service); cycles
/// are rejected at write time. Names are unique per (pond, parent) — the
/// unique index below covers nested labels; root-label uniqueness (parent_id
/// NULL, which Postgres treats as distinct) is enforced in the service under a
/// per-pond advisory lock. Deleting a label cascades to its whole subtree and
/// detaches page assignments; the service gates that behind `?force=true`.
model Label {
  id        String   @id @default(uuid())
  pondId    String   @map("pond_id")
  parentId  String?  @map("parent_id")
  name      String
  color     String
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  pond     Pond        @relation(fields: [pondId], references: [id])
  parent   Label?      @relation("LabelHierarchy", fields: [parentId], references: [id], onDelete: Cascade)
  children Label[]     @relation("LabelHierarchy")
  pages    PageLabel[]

  @@unique([pondId, parentId, name])
  @@index([pondId])
  @@map("labels")
}

/// Wikilink index maintained on every content change (data-model.md §page_links,
/// issue #47). One row per (source page, distinct target slug). `toPageId` is
/// the resolved target within the same pond, or null for a "phantom" link whose
/// target does not exist yet; creating/renaming a page to that slug resolves the
/// row. Backlinks query by `toPageId`. Collab (the content writer) maintains the
/// outgoing rows over raw SQL; it does not use Prisma.
model PageLink {
  id         String  @id @default(uuid())
  fromPageId String  @map("from_page_id")
  toPageId   String? @map("to_page_id")
  targetSlug String  @map("target_slug")

  fromPage Page  @relation("outgoingLinks", fields: [fromPageId], references: [id], onDelete: Cascade)
  toPage   Page? @relation("incomingLinks", fields: [toPageId], references: [id], onDelete: SetNull)

  @@unique([fromPageId, targetSlug])
  @@index([toPageId])
  @@index([targetSlug])
  @@map("page_links")
}

/// Assignment of a label to a page (data-model.md §labels). Cascades on both
/// sides: purging a page or deleting a label removes the assignment.
model PageLabel {
  pageId  String @map("page_id")
  labelId String @map("label_id")

  page  Page  @relation(fields: [pageId], references: [id], onDelete: Cascade)
  label Label @relation(fields: [labelId], references: [id], onDelete: Cascade)

  @@id([pageId, labelId])
  @@index([labelId])
  @@map("page_labels")
}

/// Personal page favorites (issue #132) — per user, deliberately NOT
/// pond-wide (planning pivot documented on the issue). Trashed pages keep
/// their rows, so a restore keeps the star; a purge cascades them away.
model PageFavorite {
  userId    String   @map("user_id")
  pageId    String   @map("page_id")
  createdAt DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  page Page @relation(fields: [pageId], references: [id], onDelete: Cascade)

  @@id([userId, pageId])
  @@index([pageId])
  @@map("page_favorites")
}

enum QuotaSubjectType {
  USER
  POND
}

/// Per-user/per-pond quota values (ADR 0011). Resolution: pond override →
/// user override → instance default (QuotaService). `value` is BigInt so
/// storage limits beyond 2 GiB fit.
model QuotaOverride {
  id          String           @id @default(uuid())
  subjectType QuotaSubjectType @map("subject_type")
  subjectId   String           @map("subject_id")
  quotaKey    String           @map("quota_key")
  value       BigInt
  updatedAt   DateTime         @updatedAt @map("updated_at")

  @@unique([subjectType, subjectId, quotaKey])
  @@map("quota_overrides")
}

/// Cached usage counters per pond, updated transactionally with the
/// guarded writes (uploads land in M2 #27, membership in M5); reconciled
/// nightly by a maintenance job (operations.md).
model PondUsage {
  pondId           String   @id @map("pond_id")
  storageBytesUsed BigInt   @default(0) @map("storage_bytes_used")
  editorCount      Int      @default(0) @map("editor_count")
  readerCount      Int      @default(0) @map("reader_count")
  updatedAt        DateTime @updatedAt @map("updated_at")

  pond Pond @relation(fields: [pondId], references: [id], onDelete: Cascade)

  @@map("pond_usage")
}

/// Uploaded file (ADR 0011, issue #27). Bytes live on the uploads volume at
/// `<uploadsDir>/<pondId>/<id>` (FileStorageService); this row carries the
/// metadata needed to serve and account for it. `pageId` starts unset —
/// images are uploaded before the page referencing them is known
/// (paste-then-insert, issue #28) — and is claimed on every collab persist
/// by whichever page's document embeds the file (issue #31), or at upload
/// for the page attachments panel (#61); the trash-purge job uses that
/// link to delete a purged page's files. A row whose `pageId` is STILL
/// null after a grace period was claimed by nothing and is reclaimed by
/// the nightly orphan-file sweep (issue #194, OrphanSweepService).
/// Claimed files are deliberately NOT auto-reclaimed when the content
/// stops referencing them: the page attachments panel lists them as
/// user-managed objects (insert is optional there), so "not embedded" is
/// not "unused" — the pond file manager is the human cleanup path.
/// Deletion is hard everywhere (sweep, purge, manual) — there is no
/// soft-delete state on attachments (issue #194 removed `deletedAt`).
model Attachment {
  id          String   @id @default(uuid())
  pondId      String   @map("pond_id")
  pageId      String?  @map("page_id")
  fileName    String   @map("file_name")
  mimeType    String   @map("mime_type")
  sizeBytes   Int      @map("size_bytes")
  storagePath String   @map("storage_path")
  uploadedBy  String   @map("uploaded_by")
  /// SHA-256 (hex) of the stored bytes (issue #199), computed from the
  /// in-memory upload buffer as it is written — never by re-reading disk.
  /// Downloads verify against it and fail closed on mismatch. Null only
  /// for rows that predate #199 until the nightly backfill hashes them.
  sha256      String?
  createdAt   DateTime @default(now()) @map("created_at")

  pond     Pond  @relation(fields: [pondId], references: [id])
  page     Page? @relation(fields: [pageId], references: [id])
  uploader User  @relation(fields: [uploadedBy], references: [id])

  @@index([pondId])
  @@index([pageId])
  @@map("attachments")
}

/// One row per login method. `provider` is "password" today and
/// "oidc:<issuer>" later; `credential` holds the Argon2id hash for
/// password identities.
model UserIdentity {
  id         String   @id @default(uuid())
  userId     String   @map("user_id")
  provider   String
  subject    String
  credential String?
  createdAt  DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, subject])
  @@index([userId])
  @@map("user_identities")
}

/// Server-side browser sessions (ADR 0007). `id` is the SHA-256 hash of
/// the opaque cookie token — the raw token is never stored.
model Session {
  id         String   @id
  userId     String   @map("user_id")
  createdAt  DateTime @default(now()) @map("created_at")
  expiresAt  DateTime @map("expires_at")
  lastSeenAt DateTime @default(now()) @map("last_seen_at")
  userAgent  String?  @map("user_agent")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

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

enum AuthTokenPurpose {
  EMAIL_VERIFICATION
  PASSWORD_RESET
}

/// Single-use, expiring tokens for e-mail flows. Stored hashed; consuming
/// sets `consumedAt` so replays are detectable.
model AuthToken {
  id         String           @id @default(uuid())
  tokenHash  String           @unique @map("token_hash")
  userId     String           @map("user_id")
  purpose    AuthTokenPurpose
  expiresAt  DateTime         @map("expires_at")
  consumedAt DateTime?        @map("consumed_at")
  createdAt  DateTime         @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId, purpose])
  @@map("auth_tokens")
}

enum ApiTokenScope {
  READ
  WRITE
}

/// Personal access tokens for the public API (issue #104). Only the SHA-256
/// hash of the secret is stored (auth-tokens pattern); a token acts AS its
/// user — the whole permission model applies — narrowed by `scope` and the
/// optional pond restriction. Revoking keeps the row so the settings UI can
/// show history; validation skips revoked/expired rows.
/// Read-only feed authentication (issue #149): a `dt_feed_…` secret carried as
/// a query parameter in Atom feed URLs, so feed readers can subscribe to
/// non-public ponds/pages. Deliberately much narrower than an ApiToken —
/// it can only ever authenticate the two feed endpoints, never the API.
model FeedToken {
  id         String    @id @default(uuid())
  tokenHash  String    @unique @map("token_hash")
  userId     String    @map("user_id")
  name       String
  lastUsedAt DateTime? @map("last_used_at")
  createdAt  DateTime  @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

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

model ApiToken {
  id         String        @id @default(uuid())
  tokenHash  String        @unique @map("token_hash")
  userId     String        @map("user_id")
  name       String
  scope      ApiTokenScope
  /// Empty = every pond the user may access; else only these pond ids.
  pondIds    String[]      @default([]) @map("pond_ids")
  expiresAt  DateTime?     @map("expires_at")
  revokedAt  DateTime?     @map("revoked_at")
  lastUsedAt DateTime?     @map("last_used_at")
  createdAt  DateTime      @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

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

/// Fixed-window rate-limit counters (ADR 0002: no Redis). `key` encodes
/// scope and subject, e.g. "login:ip:203.0.113.7".
model RateLimit {
  key         String   @id
  windowStart DateTime @map("window_start")
  count       Int      @default(0)

  @@map("rate_limits")
}

enum MailStatus {
  PENDING
  SENT
  FAILED
}

/// Outbox for reliable e-mail delivery with retry (issue #12).
model MailOutbox {
  id            String     @id @default(uuid())
  toAddress     String     @map("to_address")
  subject       String
  textBody      String     @map("text_body")
  htmlBody      String     @map("html_body")
  status        MailStatus @default(PENDING)
  attempts      Int        @default(0)
  nextAttemptAt DateTime   @default(now()) @map("next_attempt_at")
  lastError     String?    @map("last_error")
  createdAt     DateTime   @default(now()) @map("created_at")
  sentAt        DateTime?  @map("sent_at")

  @@index([status, nextAttemptAt])
  @@map("mail_outbox")
}

enum JobStatus {
  IDLE
  RUNNING
  FAILED
}

/// Generic maintenance-job bookkeeping (data-model.md, operations.md;
/// issue #31). One row per named job; `SchedulerService` is the only
/// writer. `status`/`lockedAt` double as the run-mutex: claiming a due job
/// is a single atomic `UPDATE ... WHERE status != 'RUNNING'`, which is safe
/// under concurrent processes without needing a session-scoped advisory
/// lock (Prisma doesn't guarantee one connection across separate calls).
/// `lastRunAt` is what makes the schedule survive an api restart — cadence
/// is computed from it, not from an in-memory timer start time. The mail
/// outbox worker (#12) predates this table and still runs its own loop;
/// folding it in is left for whenever that file is next touched, not this
/// issue's job to do.
model Job {
  name           String    @id
  cadenceSeconds Int       @map("cadence_seconds")
  status         JobStatus @default(IDLE)
  lastRunAt      DateTime? @map("last_run_at")
  lockedAt       DateTime? @map("locked_at")
  lastError      String?   @map("last_error")
  /// Wall-clock time of the last completed run (issue #86 admin panel).
  lastDurationMs Int?      @map("last_duration_ms")
  updatedAt      DateTime  @updatedAt @map("updated_at")

  @@map("jobs")
}

enum ConversionJobStatus {
  PENDING
  RUNNING
  SUCCEEDED
  FAILED
}

/// One import/export conversion (ADR 0009, issue #62). Unlike the name-keyed
/// maintenance `Job` table, this is a per-request work queue: a row is
/// enqueued PENDING, a worker claims it (`FOR UPDATE SKIP LOCKED`, `lockedAt`
/// recovers a crashed run), calls the pandoc sidecar with a timeout, and
/// stores the output bytes or an `errorCode`. `input`/`result` are the raw
/// document bytes — kept small by the request size limit and transient, not
/// the durable copy an Attachment is: the daily `conversion-payload-prune`
/// job (#233) nulls both once a finished job passes
/// `conversion.payloadRetentionDays`; the row survives for status/audit.
/// The polling endpoint `GET /jobs/:id` is owner-scoped.
model ConversionJob {
  id             String              @id @default(uuid())
  ownerId        String              @map("owner_id")
  /// Free-form label for the higher-level operation (e.g. 'export_docx',
  /// 'import_docx') that later stories (#63/#65) set; #62 uses it only for logs.
  kind           String
  sourceFormat   String              @map("source_format")
  targetFormat   String              @map("target_format")
  standalone     Boolean             @default(true)
  /// Null once the retention job (#233) pruned a finished job's payload —
  /// never while the job is PENDING/RUNNING (incl. stale-lock recovery).
  input          Bytes?
  status         ConversionJobStatus @default(PENDING)
  attempts       Int                 @default(0)
  result         Bytes?
  resultMimeType String?             @map("result_mime_type")
  errorCode      String?             @map("error_code")
  lockedAt       DateTime?           @map("locked_at")
  /// For a data-export job (#68): when its stored result stops being
  /// downloadable and is purged (GDPR data minimization). Null for every
  /// other job kind, whose payload the general retention (#233) prunes.
  expiresAt      DateTime?           @map("expires_at")
  /// Kind-specific job options (issue #117): a vault import carries
  /// `{parentPageId, labelIds, frontmatterMode}`; a PDF/DOCX/ODT export of a
  /// classified page carries `{marking}` (issues #208/#209). Null otherwise.
  options        Json?
  createdAt      DateTime            @default(now()) @map("created_at")
  updatedAt      DateTime            @updatedAt @map("updated_at")

  /// Import jobs (#63) carry the pond the document is imported into, the
  /// original upload file name (title fallback), and the page they produced.
  /// All null for a plain byte→byte conversion (export, #62/#65).
  pondId       String? @map("pond_id")
  sourceName   String? @map("source_name")
  resultPageId String? @map("result_page_id")

  owner User  @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  pond  Pond? @relation(fields: [pondId], references: [id], onDelete: Cascade)
  page  Page? @relation(fields: [resultPageId], references: [id], onDelete: SetNull)

  @@index([status, createdAt])
  @@map("conversion_jobs")
}

/// Instance-level activation a Site Admin sets per installed plugin
/// (ADR 0008 lifecycle, issue #71). `optional` plugins are then toggled per
/// pond via PondPlugin; `required` plugins cannot be uninstalled.
enum PluginInstanceMode {
  DISABLED
  OPTIONAL
  REQUIRED
}

/// An installed plugin package (ADR 0008, issue #71). The validated manifest is
/// stored verbatim so serving and admin views never re-read disk; the unpacked
/// bundle lives under `<PLUGINS_DIR>/<id>/<version>/`. Uninstall is a soft
/// delete (`removedAt` set, files removed) so existing plugin_block nodes can
/// still resolve the manifest fallback.
model Plugin {
  id          String             @id
  name        String
  version     String
  apiVersion  String             @map("api_version")
  kind        String
  mode        PluginInstanceMode @default(DISABLED)
  /// The full manifest as validated at install time (@dorfteich/plugin-sdk).
  manifest    Json
  /// SHA-256 (hex) of the installed bundle ZIP (#232); null = pre-#232 install.
  bundleHash  String?            @map("bundle_hash")
  installedAt DateTime           @default(now()) @map("installed_at")
  updatedAt   DateTime           @updatedAt @map("updated_at")
  /// Set when uninstalled; active queries filter `removedAt: null`.
  removedAt   DateTime?          @map("removed_at")

  pondPlugins PondPlugin[]

  @@map("plugins")
}

/// Per-pond activation of an `optional` plugin, toggled by a Pond Admin
/// (issue #71 model; the toggle UI/endpoint is #72). A row's presence with
/// `enabled = true` means the plugin is active in that pond.
model PondPlugin {
  pondId    String   @map("pond_id")
  pluginId  String   @map("plugin_id")
  enabled   Boolean  @default(true)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  pond   Pond   @relation(fields: [pondId], references: [id], onDelete: Cascade)
  plugin Plugin @relation(fields: [pluginId], references: [id], onDelete: Cascade)

  @@id([pondId, pluginId])
  @@map("pond_plugins")
}

/// An operator-uploaded font family (issue #303, ADR 0016 §#303). The bytes
/// live on disk under CUSTOM_FONTS_DIR — this row only records what the
/// upload form stated, because the api never parses the font file itself.
/// Additive to the compile-time catalog: a family whose name or slug
/// collides with a catalog entry is rejected, so `fonts.<slot>.family` in a
/// pond's settings stays unambiguous.
model CustomFont {
  id         String   @id @default(uuid())
  /// CSS `font-family` name, as typed by the uploader.
  family     String   @unique
  /// URL/file-safe form; names the directory under CUSTOM_FONTS_DIR.
  slug       String   @unique
  /// Drives the system fallback stack, like FontCatalogEntry.category.
  category   String
  /// Free-text licence label, e.g. "Commercial — Foundry XY". Required so
  /// an attribution obligation can be met on the font catalogue page.
  licence    String
  licenceUrl String?  @map("licence_url")
  uploadedBy String   @map("uploaded_by")
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @updatedAt @map("updated_at")

  uploader User               @relation(fields: [uploadedBy], references: [id])
  weights  CustomFontWeight[]

  @@map("custom_fonts")
}

/// One weight of a custom family. Style is always `normal`: the PDF
/// `@font-face` builder emits only that, and browsers synthesise oblique —
/// italic uploads are a follow-up, not a silent half-feature.
model CustomFontWeight {
  id       String  @id @default(uuid())
  fontId   String  @map("font_id")
  weight   Int
  /// Whether a legacy WOFF was supplied next to the required WOFF2.
  hasWoff  Boolean @default(false) @map("has_woff")
  byteSize Int     @map("byte_size")

  font CustomFont @relation(fields: [fontId], references: [id], onDelete: Cascade)

  @@unique([fontId, weight])
  @@map("custom_font_weights")
}