sehuatang/server/sql/001_init.sql

CREATE DATABASE IF NOT EXISTS magnet_cloud CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE magnet_cloud;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(191) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  device_name VARCHAR(191) NOT NULL,
  device_fingerprint VARCHAR(191) NOT NULL,
  last_seen_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_devices_user_fp (user_id, device_fingerprint),
  CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS auth_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  device_fingerprint VARCHAR(191) DEFAULT NULL,
  expires_at TIMESTAMP NOT NULL,
  last_seen_at TIMESTAMP NULL DEFAULT NULL,
  revoked_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_auth_tokens_hash (token_hash),
  KEY idx_auth_tokens_user (user_id),
  KEY idx_auth_tokens_exp (expires_at),
  CONSTRAINT fk_auth_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_keyrings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  wrapped_dek TEXT NOT NULL,
  kdf_salt VARCHAR(255) NOT NULL,
  kdf_params JSON NULL,
  key_version INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_keyrings_user (user_id),
  CONSTRAINT fk_user_keyrings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vault_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  item_type VARCHAR(64) NOT NULL,
  item_key VARCHAR(191) NOT NULL,
  payload_ciphertext LONGTEXT NOT NULL,
  payload_iv VARCHAR(64) NOT NULL,
  payload_tag VARCHAR(64) NOT NULL,
  payload_hash CHAR(64) NOT NULL,
  key_version INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_vault_user_item (user_id, item_type, item_key),
  KEY idx_vault_user_type (user_id, item_type),
  CONSTRAINT fk_vault_items_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shared_thread_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  forum_key VARCHAR(128) NOT NULL,
  thread_key VARCHAR(191) NOT NULL,
  url_hash CHAR(64) NOT NULL,
  title_hash CHAR(64) NOT NULL,
  magnet_count INT NOT NULL DEFAULT 0,
  payload_ciphertext LONGTEXT NOT NULL,
  payload_iv VARCHAR(64) NOT NULL,
  payload_tag VARCHAR(64) NOT NULL,
  payload_hash CHAR(64) NOT NULL,
  last_seen_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_shared_thread (forum_key, thread_key),
  KEY idx_shared_thread_seen (forum_key, last_seen_at),
  KEY idx_shared_thread_hash (payload_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shared_coverage_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  forum_key VARCHAR(128) NOT NULL,
  start_page INT NOT NULL,
  end_page INT NOT NULL,
  strategy VARCHAR(64) NOT NULL,
  thread_count INT NOT NULL DEFAULT 0,
  crawled_at TIMESTAMP NULL DEFAULT NULL,
  payload_ciphertext LONGTEXT NOT NULL,
  payload_iv VARCHAR(64) NOT NULL,
  payload_tag VARCHAR(64) NOT NULL,
  payload_hash CHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_shared_coverage (forum_key, start_page, end_page, strategy),
  KEY idx_shared_coverage_crawled (forum_key, crawled_at),
  KEY idx_shared_coverage_hash (payload_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shared_page_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  forum_key VARCHAR(128) NOT NULL,
  page INT NOT NULL,
  thread_count INT NOT NULL DEFAULT 0,
  crawled_at TIMESTAMP NULL DEFAULT NULL,
  payload_ciphertext LONGTEXT NOT NULL,
  payload_iv VARCHAR(64) NOT NULL,
  payload_tag VARCHAR(64) NOT NULL,
  payload_hash CHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_shared_page (forum_key, page),
  KEY idx_shared_page_crawled (forum_key, crawled_at),
  KEY idx_shared_page_hash (payload_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;