WebOrbiton
v1.0.0.6

Publisium

← Changes All versions
199 lines · 8.9 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. views_count INT UNSIGNED NOT NULL DEFAULT 0,
  69. published_at DATETIME NULL,
  70. scheduled_at DATETIME NULL,
  71. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  72. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  73. UNIQUE KEY uq_article_slug (slug),
  74. KEY idx_article_author (author_id),
  75. KEY idx_article_category (category_id),
  76. KEY idx_article_status (status),
  77. KEY idx_article_published (published_at),
  78. CONSTRAINT fk_article_author FOREIGN KEY (author_id) REFERENCES team_accounts(id) ON DELETE SET NULL,
  79. CONSTRAINT fk_article_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
  80. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  81. ​
  82. CREATE TABLE IF NOT EXISTS article_revisions (
  83. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  84. article_id INT UNSIGNED NOT NULL,
  85. editor_id INT UNSIGNED NULL,
  86. content_blocks LONGTEXT NOT NULL,
  87. note VARCHAR(255) NULL,
  88. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  89. KEY idx_revision_article (article_id),
  90. CONSTRAINT fk_revision_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  91. CONSTRAINT fk_revision_editor FOREIGN KEY (editor_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  92. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  93. ​
  94. CREATE TABLE IF NOT EXISTS pages (
  95. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  96. author_id INT UNSIGNED NULL,
  97. author_name VARCHAR(120) NULL,
  98. title VARCHAR(255) NOT NULL,
  99. slug VARCHAR(255) NOT NULL,
  100. content_blocks LONGTEXT NOT NULL,
  101. status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  102. show_in_menu TINYINT(1) NOT NULL DEFAULT 0,
  103. sort_order INT NOT NULL DEFAULT 0,
  104. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  105. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  106. UNIQUE KEY uq_page_slug (slug),
  107. CONSTRAINT fk_page_author FOREIGN KEY (author_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  108. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  109. ​
  110. CREATE TABLE IF NOT EXISTS site_settings (
  111. setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
  112. setting_value LONGTEXT NULL,
  113. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  114. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  115. ​
  116. CREATE TABLE IF NOT EXISTS ads (
  117. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  118. name VARCHAR(120) NOT NULL,
  119. type ENUM('static_text','static_image','script') NOT NULL,
  120. placement VARCHAR(60) NOT NULL,
  121. content LONGTEXT NOT NULL,
  122. link_url VARCHAR(255) NULL,
  123. is_active TINYINT(1) NOT NULL DEFAULT 1,
  124. hide_for_subscribers TINYINT(1) NOT NULL DEFAULT 0,
  125. audience VARCHAR(10) NOT NULL DEFAULT 'all',
  126. excluded_items TEXT NULL,
  127. consent_category VARCHAR(20) NOT NULL DEFAULT 'none',
  128. created_by INT UNSIGNED NULL,
  129. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  130. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  131. CONSTRAINT fk_ad_creator FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE SET NULL
  132. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  133. ​
  134. CREATE TABLE IF NOT EXISTS analytics_scripts (
  135. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  136. name VARCHAR(120) NOT NULL,
  137. script_code LONGTEXT NOT NULL,
  138. placement ENUM('head','body_start','body_end') NOT NULL DEFAULT 'head',
  139. requires_consent TINYINT(1) NOT NULL DEFAULT 1,
  140. consent_category VARCHAR(20) NOT NULL DEFAULT 'analytics',
  141. is_active TINYINT(1) NOT NULL DEFAULT 1,
  142. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  143. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  144. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  145. ​
  146. CREATE TABLE IF NOT EXISTS audit_log (
  147. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  148. team_account_id INT UNSIGNED NULL,
  149. action VARCHAR(120) NOT NULL,
  150. entity_type VARCHAR(60) NULL,
  151. entity_id INT UNSIGNED NULL,
  152. details LONGTEXT NULL,
  153. ip_address VARCHAR(64) NULL,
  154. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  155. KEY idx_audit_account (team_account_id),
  156. KEY idx_audit_entity (entity_type, entity_id),
  157. CONSTRAINT fk_audit_account FOREIGN KEY (team_account_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  158. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  159. ​
  160. CREATE TABLE IF NOT EXISTS nav_items (
  161. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  162. item_type ENUM('category','page','custom') NOT NULL,
  163. ref_id INT UNSIGNED NULL,
  164. label VARCHAR(120) NULL,
  165. url VARCHAR(255) NULL,
  166. target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
  167. icon_key VARCHAR(30) NULL,
  168. icon_svg MEDIUMTEXT NULL,
  169. sort_order INT NOT NULL DEFAULT 0,
  170. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  171. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  172. KEY idx_nav_sort (sort_order)
  173. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  174. ​
  175. SET FOREIGN_KEY_CHECKS = 1;
  176. ​
  177. CREATE TABLE IF NOT EXISTS clean_urls (
  178. slug VARCHAR(191) NOT NULL PRIMARY KEY,
  179. entity_type VARCHAR(10) NOT NULL,
  180. entity_id INT UNSIGNED NOT NULL,
  181. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  182. UNIQUE KEY uniq_clean_entity (entity_type, entity_id)
  183. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  184. ​
  185. CREATE TABLE IF NOT EXISTS article_comments (
  186. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  187. article_id INT UNSIGNED NOT NULL,
  188. parent_id INT UNSIGNED NULL,
  189. user_account_id INT UNSIGNED NULL,
  190. author_name VARCHAR(80) NOT NULL,
  191. author_email VARCHAR(190) NULL,
  192. body TEXT NOT NULL,
  193. status VARCHAR(10) NOT NULL DEFAULT 'pending',
  194. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  195. KEY idx_comments_article (article_id, status, created_at),
  196. KEY idx_comments_status (status, created_at),
  197. KEY idx_comments_parent (parent_id)
  198. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  199. ​