WebOrbiton
v1.0.0.9

Publisium

← Changes All versions
121 lines · 5.5 KB
  1. SET NAMES utf8mb4;
  2. SET time_zone = '+00:00';
  3. SET FOREIGN_KEY_CHECKS = 0;
  4. ​
  5. CREATE TABLE IF NOT EXISTS user_accounts (
  6. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  7. email VARCHAR(190) NOT NULL,
  8. password_hash VARCHAR(255) NOT NULL,
  9. display_name VARCHAR(120) NULL,
  10. avatar_path VARCHAR(255) NULL,
  11. email_verified_at DATETIME NULL,
  12. status ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active',
  13. last_login_at DATETIME NULL,
  14. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  15. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  16. UNIQUE KEY uq_user_email (email)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  18. ​
  19. CREATE TABLE IF NOT EXISTS user_sessions (
  20. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  21. user_account_id INT UNSIGNED NOT NULL,
  22. session_token VARCHAR(128) NOT NULL,
  23. ip_address VARCHAR(64) NULL,
  24. user_agent VARCHAR(255) NULL,
  25. expires_at DATETIME NOT NULL,
  26. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  27. UNIQUE KEY uq_user_session_token (session_token),
  28. KEY idx_user_session_account (user_account_id),
  29. CONSTRAINT fk_user_session_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  30. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  31. ​
  32. CREATE TABLE IF NOT EXISTS user_remember_tokens (
  33. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  34. user_account_id INT UNSIGNED NOT NULL,
  35. token_hash CHAR(64) NOT NULL,
  36. expires_at DATETIME NOT NULL,
  37. rotated_at DATETIME NULL,
  38. ip_address VARCHAR(45) NULL,
  39. user_agent VARCHAR(255) NULL,
  40. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  41. UNIQUE KEY uq_remember_token_hash (token_hash),
  42. KEY idx_remember_account (user_account_id),
  43. KEY idx_remember_expires (expires_at),
  44. KEY idx_remember_rotated (rotated_at),
  45. CONSTRAINT fk_remember_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  46. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  47. ​
  48. CREATE TABLE IF NOT EXISTS user_consents (
  49. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  50. user_account_id INT UNSIGNED NOT NULL,
  51. consent_type VARCHAR(60) NOT NULL,
  52. is_granted TINYINT(1) NOT NULL DEFAULT 0,
  53. granted_at DATETIME NULL,
  54. revoked_at DATETIME NULL,
  55. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  56. KEY idx_consent_account (user_account_id),
  57. CONSTRAINT fk_consent_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  58. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  59. ​
  60. CREATE TABLE IF NOT EXISTS subscriptions (
  61. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  62. user_account_id INT UNSIGNED NOT NULL,
  63. provider VARCHAR(60) NOT NULL,
  64. provider_customer_id VARCHAR(190) NULL,
  65. provider_subscription_id VARCHAR(190) NULL,
  66. plan_code VARCHAR(60) NULL,
  67. status ENUM('active','trialing','past_due','canceled','expired') NOT NULL DEFAULT 'active',
  68. current_period_end DATETIME NULL,
  69. canceled_at DATETIME NULL,
  70. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  71. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  72. KEY idx_subscription_account (user_account_id),
  73. KEY idx_subscription_provider_ref (provider, provider_subscription_id),
  74. CONSTRAINT fk_subscription_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  75. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  76. ​
  77. CREATE TABLE IF NOT EXISTS article_purchases (
  78. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  79. user_account_id INT UNSIGNED NOT NULL,
  80. article_id INT UNSIGNED NOT NULL,
  81. provider VARCHAR(60) NOT NULL,
  82. provider_payment_id VARCHAR(190) NULL,
  83. amount_cents INT UNSIGNED NOT NULL,
  84. currency CHAR(3) NOT NULL DEFAULT 'USD',
  85. status ENUM('pending','completed','refunded','failed') NOT NULL DEFAULT 'pending',
  86. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  87. KEY idx_purchase_account (user_account_id),
  88. KEY idx_purchase_article (article_id),
  89. CONSTRAINT fk_purchase_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
  90. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  91. ​
  92. CREATE TABLE IF NOT EXISTS payment_webhook_events (
  93. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  94. provider VARCHAR(60) NOT NULL,
  95. event_id VARCHAR(190) NOT NULL,
  96. event_type VARCHAR(120) NOT NULL,
  97. payload LONGTEXT NOT NULL,
  98. processing_status ENUM('received','processed','failed','ignored') NOT NULL DEFAULT 'received',
  99. processing_error VARCHAR(500) NULL,
  100. received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  101. processed_at DATETIME NULL,
  102. UNIQUE KEY uq_webhook_provider_event (provider, event_id),
  103. KEY idx_webhook_status (processing_status)
  104. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  105. ​
  106. CREATE TABLE IF NOT EXISTS comments (
  107. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  108. article_id INT UNSIGNED NOT NULL,
  109. user_account_id INT UNSIGNED NULL,
  110. parent_id INT UNSIGNED NULL,
  111. external_thread_id VARCHAR(190) NULL,
  112. body TEXT NULL,
  113. status ENUM('visible','hidden','flagged') NOT NULL DEFAULT 'visible',
  114. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  115. KEY idx_comment_article (article_id),
  116. KEY idx_comment_parent (parent_id),
  117. CONSTRAINT fk_comment_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE SET NULL
  118. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  119. ​
  120. SET FOREIGN_KEY_CHECKS = 1;
  121. ​