WebOrbiton
v2.0.0.1

Publisium

← Changes All versions
202 lines · 9.0 KB
  1. SET NAMES utf8mb4;
  2. SET time_zone = '+00:00';
  3. SET FOREIGN_KEY_CHECKS = 0;
  4. ​
  5. CREATE TABLE IF NOT EXISTS team_accounts (
  6. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  7. username VARCHAR(64) NOT NULL,
  8. email VARCHAR(190) NULL,
  9. password_hash VARCHAR(255) NOT NULL,
  10. display_name VARCHAR(120) NOT NULL,
  11. role ENUM('super_admin','editor_in_chief','managing_editor','senior_writer','writer','proofreader') NOT NULL,
  12. avatar_path VARCHAR(255) NULL,
  13. bio TEXT NULL,
  14. totp_secret VARCHAR(64) NULL,
  15. totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  16. status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  17. last_login_at DATETIME NULL,
  18. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  19. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  20. UNIQUE KEY uq_team_username (username),
  21. UNIQUE KEY uq_team_email (email)
  22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  23. ​
  24. CREATE TABLE IF NOT EXISTS team_sessions (
  25. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  26. team_account_id INT UNSIGNED NOT NULL,
  27. session_token VARCHAR(128) NOT NULL,
  28. ip_address VARCHAR(64) NULL,
  29. user_agent VARCHAR(255) NULL,
  30. expires_at DATETIME NOT NULL,
  31. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  32. UNIQUE KEY uq_team_session_token (session_token),
  33. KEY idx_team_session_account (team_account_id),
  34. CONSTRAINT fk_team_session_account FOREIGN KEY (team_account_id) REFERENCES team_accounts(id) ON DELETE CASCADE
  35. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  36. ​
  37. CREATE TABLE IF NOT EXISTS categories (
  38. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  39. name VARCHAR(120) NOT NULL,
  40. slug VARCHAR(140) NOT NULL,
  41. description VARCHAR(255) NULL,
  42. parent_id INT UNSIGNED NULL,
  43. sort_order INT NOT NULL DEFAULT 0,
  44. show_in_menu TINYINT(1) NOT NULL DEFAULT 1,
  45. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  46. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  47. UNIQUE KEY uq_category_slug (slug),
  48. KEY idx_category_parent (parent_id),
  49. CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
  50. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  51. ​
  52. CREATE TABLE IF NOT EXISTS articles (
  53. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  54. author_id INT UNSIGNED NULL,
  55. author_name VARCHAR(120) NULL,
  56. category_id INT UNSIGNED NULL,
  57. title VARCHAR(255) NOT NULL,
  58. slug VARCHAR(255) NOT NULL,
  59. excerpt VARCHAR(500) NULL,
  60. content_blocks LONGTEXT NOT NULL,
  61. cover_image_path VARCHAR(255) NULL,
  62. access_type ENUM('free','paid') NOT NULL DEFAULT 'free',
  63. price_cents INT UNSIGNED NULL,
  64. status ENUM('draft','pending_review','scheduled','published','rejected','archived') NOT NULL DEFAULT 'draft',
  65. rejection_reason VARCHAR(500) NULL,
  66. seo_title VARCHAR(255) NULL,
  67. seo_description VARCHAR(500) NULL,
  68. noindex TINYINT(1) NOT NULL DEFAULT 0,
  69. views_count INT UNSIGNED NOT NULL DEFAULT 0,
  70. published_at DATETIME NULL,
  71. scheduled_at DATETIME NULL,
  72. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  73. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  74. UNIQUE KEY uq_article_slug (slug),
  75. KEY idx_article_author (author_id),
  76. KEY idx_article_category (category_id),
  77. KEY idx_article_status (status),
  78. KEY idx_article_published (published_at),
  79. CONSTRAINT fk_article_author FOREIGN KEY (author_id) REFERENCES team_accounts(id) ON DELETE SET NULL,
  80. CONSTRAINT fk_article_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
  81. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  82. ​
  83. CREATE TABLE IF NOT EXISTS article_revisions (
  84. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  85. article_id INT UNSIGNED NOT NULL,
  86. editor_id INT UNSIGNED NULL,
  87. content_blocks LONGTEXT NOT NULL,
  88. note VARCHAR(255) NULL,
  89. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  90. KEY idx_revision_article (article_id),
  91. CONSTRAINT fk_revision_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  92. CONSTRAINT fk_revision_editor FOREIGN KEY (editor_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  93. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  94. ​
  95. CREATE TABLE IF NOT EXISTS pages (
  96. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  97. author_id INT UNSIGNED NULL,
  98. author_name VARCHAR(120) NULL,
  99. title VARCHAR(255) NOT NULL,
  100. slug VARCHAR(255) NOT NULL,
  101. content_blocks LONGTEXT NOT NULL,
  102. status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  103. show_in_menu TINYINT(1) NOT NULL DEFAULT 0,
  104. noindex TINYINT(1) NOT NULL DEFAULT 0,
  105. sort_order INT NOT NULL DEFAULT 0,
  106. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  107. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  108. UNIQUE KEY uq_page_slug (slug),
  109. CONSTRAINT fk_page_author FOREIGN KEY (author_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  110. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  111. ​
  112. CREATE TABLE IF NOT EXISTS site_settings (
  113. setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
  114. setting_value LONGTEXT NULL,
  115. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  116. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  117. ​
  118. CREATE TABLE IF NOT EXISTS ads (
  119. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  120. name VARCHAR(120) NOT NULL,
  121. type ENUM('static_text','static_image','script') NOT NULL,
  122. placement VARCHAR(60) NOT NULL,
  123. content LONGTEXT NOT NULL,
  124. link_url VARCHAR(255) NULL,
  125. is_active TINYINT(1) NOT NULL DEFAULT 1,
  126. hide_for_subscribers TINYINT(1) NOT NULL DEFAULT 0,
  127. audience VARCHAR(10) NOT NULL DEFAULT 'all',
  128. excluded_items TEXT NULL,
  129. consent_category VARCHAR(20) NOT NULL DEFAULT 'none',
  130. created_by INT UNSIGNED NULL,
  131. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  132. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  133. CONSTRAINT fk_ad_creator FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE SET NULL
  134. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  135. ​
  136. CREATE TABLE IF NOT EXISTS analytics_scripts (
  137. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  138. name VARCHAR(120) NOT NULL,
  139. script_code LONGTEXT NOT NULL,
  140. placement ENUM('head','body_start','body_end') NOT NULL DEFAULT 'head',
  141. requires_consent TINYINT(1) NOT NULL DEFAULT 1,
  142. consent_category VARCHAR(20) NOT NULL DEFAULT 'analytics',
  143. is_active TINYINT(1) NOT NULL DEFAULT 1,
  144. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  145. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  146. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  147. ​
  148. CREATE TABLE IF NOT EXISTS audit_log (
  149. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  150. team_account_id INT UNSIGNED NULL,
  151. action VARCHAR(120) NOT NULL,
  152. entity_type VARCHAR(60) NULL,
  153. entity_id INT UNSIGNED NULL,
  154. details LONGTEXT NULL,
  155. ip_address VARCHAR(64) NULL,
  156. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  157. KEY idx_audit_account (team_account_id),
  158. KEY idx_audit_entity (entity_type, entity_id),
  159. CONSTRAINT fk_audit_account FOREIGN KEY (team_account_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  160. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  161. ​
  162. CREATE TABLE IF NOT EXISTS nav_items (
  163. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  164. item_type ENUM('category','page','custom') NOT NULL,
  165. ref_id INT UNSIGNED NULL,
  166. label VARCHAR(120) NULL,
  167. url VARCHAR(255) NULL,
  168. target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
  169. icon_key VARCHAR(30) NULL,
  170. icon_svg MEDIUMTEXT NULL,
  171. sort_order INT NOT NULL DEFAULT 0,
  172. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  173. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  174. KEY idx_nav_sort (sort_order)
  175. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  176. ​
  177. SET FOREIGN_KEY_CHECKS = 1;
  178. ​
  179. CREATE TABLE IF NOT EXISTS clean_urls (
  180. slug VARCHAR(191) NOT NULL PRIMARY KEY,
  181. entity_type VARCHAR(10) NOT NULL,
  182. entity_id INT UNSIGNED NOT NULL,
  183. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  184. UNIQUE KEY uniq_clean_entity (entity_type, entity_id)
  185. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  186. ​
  187. CREATE TABLE IF NOT EXISTS article_comments (
  188. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  189. article_id INT UNSIGNED NOT NULL,
  190. parent_id INT UNSIGNED NULL,
  191. user_account_id INT UNSIGNED NULL,
  192. team_account_id INT UNSIGNED NULL,
  193. author_name VARCHAR(80) NOT NULL,
  194. author_email VARCHAR(190) NULL,
  195. body TEXT NOT NULL,
  196. status VARCHAR(10) NOT NULL DEFAULT 'pending',
  197. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  198. KEY idx_comments_article (article_id, status, created_at),
  199. KEY idx_comments_status (status, created_at),
  200. KEY idx_comments_parent (parent_id)
  201. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  202. ​