schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "views"]
  binaryTargets   = ["native"]
}

datasource db {
  provider   = "postgresql"
  url        = env("POSTGRES_URL")
  extensions = [pg_trgm]
}

generator markdown {
  provider = "prisma-markdown"
  title    = "PROJECT"
  output   = "../docs/ERD.md"
}

//-----------------------------------------------------------
// ARTICLES
//-----------------------------------------------------------
/// Attachment File.
///
/// Every attachment files that are managed in current system.
///
/// For reference, it is possible to omit one of file name or extension like 
/// `.gitignore` or `README` case, but not possible to omit both of them.
///
/// @namespace Articles
/// @author Samchon
model attachment_files {
  //----
  // COLUMNS
  //----
  /// @format uuid
  id String @id @db.Uuid

  /// File name, except extension.
  ///
  /// If there's file `.gitignore`, then its name is an empty string.
  ///
  /// @maxLength 255
  name String @db.VarChar

  /// Extension.
  ///
  /// Possible to omit like `README` case.
  ///
  /// @minLength 1
  /// @maxLength 8
  extension String? @db.VarChar

  /// URL path of the real file.
  ///
  /// @format url
  url String @db.VarChar

  /// Creation time of file.
  ///
  /// @format date-time
  created_at DateTime @db.Timestamptz

  //----
  // RELATIONS
  //----
  bbs_article_snapshot_files          bbs_article_snapshot_files[]
  bbs_article_comment_snapshots_files bbs_article_comment_snapshot_files[]

  @@index([url])
}

/// Article entity.
/// 
/// `bbs_articles` is a super-type entity of all kinds of articles in the 
/// current backend system, literally shaping individual articles of 
/// the bulletin board.
///
/// And, as you can see, the elements that must inevitably exist in the 
/// article, such as the title or the body, do not exist in the `bbs_articles`, 
/// but exist in the subsidiary entity, {@link bbs_article_snapshots}, as a 
/// 1: N relationship, which is because a new snapshot record is published 
/// every time the article is modified.
///
/// The reason why a new snapshot record is published every time the article 
/// is modified is to preserve the evidence. Due to the nature of e-community, 
/// there is always a threat of dispute among the participants. And it can 
/// happen that disputes arise through articles or comments, and to prevent 
/// such things as modifying existing articles to manipulate the situation, 
/// the article is designed in this structure.
///
/// In other words, to keep evidence, and prevent fraud.
///
/// @namespace Articles
/// @author Samchon
model bbs_articles {
  /// @format uuid
  id String @id @db.Uuid

  /// Creation time of article.
  created_at DateTime @db.Timestamptz

  /// Deletion time of article.
  ///
  /// To keep evidence, do not delete the article, but just mark it as 
  /// deleted.
  deleted_at DateTime? @db.Timestamptz

  //----
  // RELATIONS
  //----
  /// List of snapshots.
  ///
  /// It is created for the first time when an article is created, and is
  /// accumulated every time the article is modified.
  ///
  /// @minItems 1
  snapshots bbs_article_snapshots[]

  /// List of comments.
  comments bbs_article_comments[]

  mv_last mv_bbs_article_last_snapshots?

  @@index([created_at])
}

/// Snapshot of article.
///
/// `bbs_article_snapshots` is a snapshot entity that contains the contents of
/// the article, as mentioned in {@link bbs_articles}, the contents of the 
/// article are separated from the article record to keep evidence and prevent 
/// fraud.
///
/// @namespace Articles
/// @author Samchon
model bbs_article_snapshots {
  //----
  // COLUMNS
  //----
  /// @format uuid
  id String @id @db.Uuid

  /// Belong article's {@link bbs_articles.id}
  ///
  /// @format uuid
  bbs_article_id String @db.Uuid

  /// Format of body.
  ///
  /// Same meaning with extension like `html`, `md`, `txt`.
  format String @db.VarChar

  /// Title of article.
  title String @db.VarChar

  /// Content body of article.
  body String

  /// Creation time of record.
  ///
  /// It means creation time or update time or article.
  created_at DateTime @db.Timestamptz

  //----
  // RELATIONS
  //----
  /// Belong article info.
  article bbs_articles @relation(fields: [bbs_article_id], references: [id], onDelete: Cascade)

  /// List of wrappers of attachment files.
  to_files bbs_article_snapshot_files[]

  mv_last mv_bbs_article_last_snapshots?

  @@index([bbs_article_id, created_at])
  @@index([title(ops: raw("gin_trgm_ops"))], type: Gin)
  @@index([body(ops: raw("gin_trgm_ops"))], type: Gin)
}

/// Attachment file of article snapshot.
///
/// `bbs_article_snapshot_files` is an entity that shapes the attached files of
/// the article snapshot.
///
/// `bbs_article_snapshot_files` is a typical pair relationship table to 
/// resolve the M: N relationship between {@link bbs_article_snapshots} and
/// {@link attachment_files} tables. Also, to ensure the order of the attached
/// files, it has an additional `sequence` attribute, which we will continue to
/// see in this documents.
///
/// @namespace Articles
/// @author Samchon
model bbs_article_snapshot_files {
  //----
  // COLUMNS
  //----
  /// @format uuid
  id String @id @db.Uuid

  /// Belonged snapshot's {@link bbs_article_snapshots.id}
  ///
  /// @format uuid
  bbs_article_snapshot_id String @db.Uuid

  /// Belonged file's {@link attachment_files.id}
  ///
  /// @format uuid
  attachment_file_id String @db.Uuid

  /// Sequence of attachment file in the snapshot.
  ///
  /// @format int
  sequence Int @db.Integer

  //----
  // RELATIONS
  //----
  /// Belonged article.
  snapshot bbs_article_snapshots @relation(fields: [bbs_article_snapshot_id], references: [id], onDelete: Cascade)

  /// Belonged file.
  file attachment_files @relation(fields: [attachment_file_id], references: [id], onDelete: Cascade)

  @@index([bbs_article_snapshot_id])
  @@index([attachment_file_id])
}

/// Comment written on an article.
///
/// `bbs_article_comments` is an entity that shapes the comments written on an
/// article.
///
/// And for this comment, as in the previous relationship between 
/// {@link bbs_articles} and {@link bbs_article_snapshots}, the content body 
/// of the comment is stored in the sub {@link bbs_article_comment_snapshots} 
/// table for evidentialism, and a new snapshot record is issued every time 
/// the comment is modified.
///
/// Also, `bbs_article_comments` is expressing the relationship of the 
/// hierarchical reply structure through the `parent_id` attribute.
///
/// @namespace Articles
/// @author Samchon
model bbs_article_comments {
  //----
  // COLUMNS
  //----
  /// @format uuid
  id String @id @db.Uuid

  /// Belonged article's {@link bbs_articles.id}
  /// 
  /// @format uuid
  bbs_article_id String @db.Uuid

  /// Parent comment's {@link bbs_article_comments.id}
  ///
  /// Used to express the hierarchical reply structure.
  ///
  /// @format uuid
  parent_id String? @db.Uuid

  /// Creation time of comment.
  created_at DateTime @db.Timestamptz

  /// Deletion time of comment.
  ///
  /// Do not allow to delete the comment, but just mark it as deleted, 
  /// to keep evidence.
  deleted_at DateTime? @db.Timestamptz

  //----
  // RELATIONS
  //----
  /// Belonged article.
  article bbs_articles @relation(fields: [bbs_article_id], references: [id], onDelete: Cascade)

  /// Parent comment.
  ///
  /// Only when reply case.
  parent bbs_article_comments? @relation("bbs_article_comments_reply", fields: [parent_id], references: [id], onDelete: Cascade)

  /// List of children comments.
  ///
  /// Reply comments of current.
  children bbs_article_comments[] @relation("bbs_article_comments_reply")

  /// List of snapshots.
  ///
  /// It is created for the first time when a comment is created, and is
  /// accumulated every time the comment is modified.
  ///
  /// @minItems 1
  snapshots bbs_article_comment_snapshots[]

  @@index([bbs_article_id, parent_id, created_at])
}

/// Snapshot of comment.
///
/// `bbs_article_comment_snapshots` is a snapshot entity that contains the 
/// contents of the comment.
///
/// As mentioned in {@link bbs_article_comments}, designed to keep evidence 
/// and prevent fraud.
///
/// @namespace Articles
/// @author Samchon
model bbs_article_comment_snapshots {
  //----
  // COLUMNS
  //----
  /// @format uuid
  id String @id @db.Uuid

  /// Belonged article's {@link bbs_article_comments.id}
  ///
  /// @format uuid
  bbs_article_comment_id String @db.Uuid

  /// Format of content body.
  ///
  /// Same meaning with extension like `html`, `md`, `txt`.
  format String @db.VarChar

  /// Content body of comment.
  body String

  /// Creation time of record.
  ///
  /// It means creation time or update time or comment.
  created_at DateTime @db.Timestamptz

  //----
  // RELATIONS
  //----
  /// Belong comment info.
  comment bbs_article_comments @relation(fields: [bbs_article_comment_id], references: [id], onDelete: Cascade)

  /// List of wrappers of attachment files.
  to_files bbs_article_comment_snapshot_files[]

  @@index([bbs_article_comment_id, created_at])
  @@index([body(ops: raw("gin_trgm_ops"))], type: Gin)
}

/// Attachment file of comment snapshot.
/// 
/// `bbs_article_comment_snapshot_files` is an entity resolving the M:N 
/// relationship between {@link bbs_article_comment_snapshots} and 
/// {@link attachment_files} tables.
/// 
/// @namespace Articles
/// @author Samchon
model bbs_article_comment_snapshot_files {
  //----
  // COLUMNS
  //----
  /// @format uuid
  id String @id @db.Uuid

  /// Belonged snapshot's {@link bbs_article_comment_snapshots.id}
  ///
  /// @format uuid
  bbs_article_comment_snapshot_id String @db.Uuid

  /// Belonged file's {@link attachment_files.id}
  ///
  /// @format uuid
  attachment_file_id String @db.Uuid

  /// Sequence order.
  ///
  /// Sequence order of the attached file in the belonged snapshot.
  ///
  /// @type int
  sequence Int @db.Integer

  //----
  // RELATIONS
  //----
  /// Belonged article.
  snapshot bbs_article_comment_snapshots @relation(fields: [bbs_article_comment_snapshot_id], references: [id], onDelete: Cascade)

  /// Belonged file.
  file attachment_files @relation(fields: [attachment_file_id], references: [id], onDelete: Cascade)

  @@index([bbs_article_comment_snapshot_id])
  @@index([attachment_file_id])
}

/// @hidden
/// @author Samchon
model mv_bbs_article_last_snapshots {
  bbs_article_id          String @id @db.Uuid
  bbs_article_snapshot_id String @db.Uuid

  article  bbs_articles          @relation(fields: [bbs_article_id], references: [id], onDelete: Cascade)
  snapshot bbs_article_snapshots @relation(fields: [bbs_article_snapshot_id], references: [id], onDelete: Cascade)

  @@unique([bbs_article_snapshot_id])
}