koc-loop/mysql/0001_init.sql

CREATE TABLE IF NOT EXISTS partners (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  wecom_name VARCHAR(255) NOT NULL,
  owner VARCHAR(255) NOT NULL DEFAULT '运营组',
  claimed_total INT NOT NULL DEFAULT 0,
  completed_total INT NOT NULL DEFAULT 0,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS tasks (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  brand VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  claimed_quantity INT NOT NULL DEFAULT 0,
  due_at VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'active',
  source_url TEXT NOT NULL DEFAULT (''),
  source_sheet_id VARCHAR(255) NOT NULL DEFAULT '',
  source_sheet_name VARCHAR(255) NOT NULL DEFAULT '',
  source_synced_at DATETIME(3) NULL,
  share_token VARCHAR(128) NULL,
  collection_start_date VARCHAR(32) NULL,
  collection_days TEXT NOT NULL DEFAULT ('[]'),
  collection_schedule_updated_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY tasks_share_token_idx (share_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS contents (
  id VARCHAR(64) PRIMARY KEY,
  task_id VARCHAR(64) NOT NULL,
  title TEXT NOT NULL,
  body LONGTEXT NOT NULL DEFAULT (''),
  image_assets LONGTEXT NOT NULL DEFAULT ('[]'),
  status VARCHAR(32) NOT NULL DEFAULT 'available',
  source VARCHAR(255) NOT NULL DEFAULT '飞书内容表',
  source_row INT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY contents_task_status_idx (task_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS accounts (
  id VARCHAR(64) PRIMARY KEY,
  platform VARCHAR(32) NOT NULL DEFAULT '小红书',
  platform_uid VARCHAR(255) NOT NULL,
  public_account_id VARCHAR(255) NOT NULL DEFAULT '',
  nickname VARCHAR(255) NOT NULL,
  profile_url TEXT NOT NULL DEFAULT (''),
  ip_location VARCHAR(255) NOT NULL DEFAULT '待识别',
  followers INT NOT NULL DEFAULT 0,
  gender VARCHAR(16) NOT NULL DEFAULT '',
  bio TEXT NOT NULL DEFAULT (''),
  tags VARCHAR(500) NOT NULL DEFAULT '',
  post_count INT NOT NULL DEFAULT 0,
  avg_views INT NOT NULL DEFAULT 0,
  first_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  last_seen_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY accounts_platform_uid_idx (platform, platform_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS claims (
  id VARCHAR(64) PRIMARY KEY,
  task_id VARCHAR(64) NOT NULL,
  partner_id VARCHAR(64) NOT NULL,
  claimant_name VARCHAR(255) NOT NULL,
  claim_token VARCHAR(128) NOT NULL,
  quantity INT NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY claims_claim_token_idx (claim_token),
  KEY claims_task_partner_created_idx (task_id, partner_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS delegation_bundles (
  id VARCHAR(64) PRIMARY KEY,
  task_id VARCHAR(64) NOT NULL,
  claim_id VARCHAR(64) NOT NULL,
  partner_id VARCHAR(64) NOT NULL,
  label VARCHAR(255) NOT NULL,
  share_token VARCHAR(128) NOT NULL,
  quantity INT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'active',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  revoked_at DATETIME(3) NULL,
  UNIQUE KEY delegation_bundles_share_token_idx (share_token),
  KEY delegation_bundles_claim_created_idx (claim_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS distributions (
  id VARCHAR(64) PRIMARY KEY,
  task_id VARCHAR(64) NOT NULL,
  content_id VARCHAR(64) NOT NULL,
  partner_id VARCHAR(64) NOT NULL,
  claim_id VARCHAR(64) NULL,
  delegation_bundle_id VARCHAR(64) NULL,
  account_id VARCHAR(64) NULL,
  publish_url TEXT NULL,
  publish_time DATETIME(3) NULL,
  publish_screenshot_key VARCHAR(512) NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'claimed',
  claimed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  screenshot_key VARCHAR(512) NULL,
  ocr_status VARCHAR(32) NOT NULL DEFAULT 'none',
  exposure INT NULL,
  views INT NULL,
  d2_likes INT NULL,
  d2_comments INT NULL,
  d2_collects INT NULL,
  d5_likes INT NULL,
  d5_comments INT NULL,
  d5_collects INT NULL,
  d7_likes INT NULL,
  d7_comments INT NULL,
  d7_collects INT NULL,
  latest_likes INT NULL,
  latest_comments INT NULL,
  latest_collects INT NULL,
  collection_status VARCHAR(32) NOT NULL DEFAULT 'pending',
  collection_status_description TEXT NULL,
  collection_updated_at DATETIME(3) NULL,
  last_collection_day INT NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY distributions_task_status_idx (task_id, status),
  KEY distributions_content_idx (content_id),
  KEY distributions_account_idx (account_id),
  KEY distributions_claim_idx (claim_id),
  KEY distributions_delegation_idx (delegation_bundle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS collection_runs (
  id VARCHAR(64) PRIMARY KEY,
  task_id VARCHAR(64) NOT NULL,
  distribution_id VARCHAR(64) NOT NULL,
  scheduled_date VARCHAR(32) NOT NULL,
  schedule_day INT NULL,
  scheduled_at DATETIME(3) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  likes INT NULL,
  comments INT NULL,
  collects INT NULL,
  status_description TEXT NULL,
  started_at DATETIME(3) NULL,
  completed_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY collection_runs_distribution_date_idx (distribution_id, scheduled_date),
  KEY collection_runs_task_date_idx (task_id, scheduled_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(64) PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  password_salt VARCHAR(255) NOT NULL,
  password_iterations INT NOT NULL,
  role VARCHAR(32) NOT NULL,
  super_admin_guard TINYINT GENERATED ALWAYS AS (
    CASE WHEN role = 'super_admin' THEN 1 ELSE NULL END
  ) STORED,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY users_username_idx (username),
  UNIQUE KEY users_single_super_admin_idx (super_admin_guard)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS auth_sessions (
  token_hash VARCHAR(128) PRIMARY KEY,
  user_id VARCHAR(64) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY auth_sessions_user_id_idx (user_id),
  KEY auth_sessions_expires_at_idx (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS mcp_export_tokens (
  token_hash VARCHAR(128) PRIMARY KEY,
  kind VARCHAR(64) NOT NULL,
  payload LONGTEXT NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY mcp_export_tokens_expires_at_idx (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- statement-breakpoint
CREATE TABLE IF NOT EXISTS background_jobs (
  id VARCHAR(64) PRIMARY KEY,
  type VARCHAR(64) NOT NULL,
  payload LONGTEXT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  available_at DATETIME(3) NOT NULL,
  locked_at DATETIME(3) NULL,
  completed_at DATETIME(3) NULL,
  last_error TEXT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY background_jobs_pending_idx (status, available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;