community-rule/prisma/schema.prisma

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

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  sessions          Session[]
  draft             RuleDraft?
  rules             PublishedRule[]
  /// At most one pending verified email change (CR-103).
  emailChangeToken  EmailChangeToken?
  /// Rules this user was invited to as a stakeholder (after accepting invite).
  ruleStakeholders  RuleStakeholder[] @relation("RuleStakeholderUser")
  /// Stakeholder rows where this user sent the invite.
  stakeholderInvitesSent RuleStakeholder[] @relation("RuleStakeholderInvitedBy")
}

/// Pending email change: user must open verify link sent to `newEmail` (CR-103).
/// Separate from `MagicLinkToken` so sign-in and email-change flows cannot be confused.
model EmailChangeToken {
  id        String   @id @default(cuid())
  userId    String   @unique
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  newEmail  String
  tokenHash String   @unique
  expiresAt DateTime
  createdAt DateTime @default(now())

  @@index([newEmail])
}

model Session {
  id        String   @id @default(cuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  tokenHash String   @unique
  expiresAt DateTime
  createdAt DateTime @default(now())

  @@index([userId])
}

model MagicLinkToken {
  id           String   @id @default(cuid())
  email        String
  tokenHash    String   @unique
  expiresAt    DateTime
  nextPath     String?
  /// Optional create-flow draft captured at magic-link request (save-progress).
  draftPayload Json?
  createdAt    DateTime @default(now())

  @@index([email])
}

model RuleDraft {
  id        String   @id @default(cuid())
  userId    String   @unique
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  payload   Json
  updatedAt DateTime @updatedAt
}

model PublishedRule {
  id        String   @id @default(cuid())
  userId    String?
  user      User?    @relation(fields: [userId], references: [id], onDelete: SetNull)
  title     String
  summary   String?
  document  Json
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  stakeholders RuleStakeholder[]

  @@index([userId])
}

/// Invite + access for a published rule: email invite at publish; userId set after magic-link-style accept.
model RuleStakeholder {
  id        String   @id @default(cuid())
  ruleId    String
  rule      PublishedRule @relation(fields: [ruleId], references: [id], onDelete: Cascade)
  /// Normalized lowercase email (invite target).
  email     String
  /// Publisher at invite time; null if that account was removed.
  invitedByUserId String?
  invitedBy User? @relation("RuleStakeholderInvitedBy", fields: [invitedByUserId], references: [id], onDelete: SetNull)
  /// Set when the invitee completes the verify link (same account as `email`).
  userId    String?
  user      User? @relation("RuleStakeholderUser", fields: [userId], references: [id], onDelete: SetNull)
  /// One-time invite token (hashed); null after accept or revoke path (consume on verify).
  inviteTokenHash String? @unique
  inviteExpiresAt DateTime?
  invitedAt DateTime @default(now())
  acceptedAt DateTime?

  @@unique([ruleId, email])
  @@index([userId])
  @@index([email])
}

model RuleTemplate {
  id          String   @id @default(cuid())
  slug        String   @unique
  title       String
  category    String?
  description String?
  body        Json
  sortOrder   Int      @default(0)
  featured    Boolean  @default(false)
}

/// Recommendation matrix (CR-88).
/// JSON in `data/create/customRule/<section>.json` is canonical; this table is
/// rebuilt from those files at `prisma db seed` time so the API can join.
/// See `docs/guides/template-recommendation-matrix.md` §7.
model MethodFacet {
  id      String  @id @default(cuid())
  /// One of "communication" | "membership" | "decisionApproaches" | "conflictManagement".
  section String
  /// Matches the `id` of an entry in `messages/en/create/customRule/<section>.json#/methods`.
  slug    String
  /// One of "size" | "orgType" | "scale" | "maturity".
  group   String
  /// Canonical facet value id, e.g. "workersCoop", "earlyStage".
  value   String
  /// `true` iff the JSON marks this method as matching the facet (`✓` cell).
  matches Boolean
  /// Optional per-cell weight; reserved for a future weighted-rank pass (v1 ignores).
  weight  Float?

  @@unique([section, slug, group, value])
  @@index([section])
  @@index([group, value, matches])
}

/// Template-level recommendation matrix (Template Composition, cols G–Y). Canonical
/// JSON in `data/templates/templateFacet.json`; rebuilt at seed like
/// `MethodFacet`. One row per `(templateSlug, group, value)` where the matrix
/// marks a fit (✓). `GET /api/templates?facet.*` joins these rows to user facets.
/// See `docs/guides/template-recommendation-matrix.md` (parallel to `MethodFacet` §7).
model TemplateFacet {
  id           String  @id @default(cuid())
  /// `RuleTemplate.slug` (e.g. `consensus`, `do-ocracy`).
  templateSlug String
  /// `size` | `orgType` | `scale` | `maturity` — same as `MethodFacet.group`.
  group        String
  /// Canonical facet value id, e.g. `workersCoop`, `local`.
  value        String
  /// `true` iff the JSON marks a fit; seed only writes `true` rows.
  matches      Boolean

  @@unique([templateSlug, group, value])
  @@index([templateSlug])
  @@index([group, value, matches])
}