Agent/examples/tencent_realtime_control/schema.sql

CREATE TABLE IF NOT EXISTS realtime_control_daily_state (
    control_date DATE NOT NULL PRIMARY KEY,
    morning_recovery_done BOOLEAN NOT NULL DEFAULT FALSE,
    cutoff_done BOOLEAN NOT NULL DEFAULT FALSE,
    last_observed_partition VARCHAR(20) DEFAULT NULL,
    last_observed_cpm DECIMAL(12, 4) DEFAULT NULL,
    last_decision VARCHAR(32) DEFAULT NULL,
    last_inventory_refresh_at DATETIME DEFAULT NULL,
    last_evaluated_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控每日状态';

CREATE TABLE IF NOT EXISTS realtime_control_ad_state (
    account_id BIGINT NOT NULL,
    adgroup_id BIGINT NOT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    bid_field VARCHAR(32) NOT NULL,
    base_bid_fen INT NOT NULL,
    boosted_date DATE DEFAULT NULL,
    paused_by_strategy BOOLEAN NOT NULL DEFAULT FALSE,
    pause_reason VARCHAR(32) DEFAULT NULL,
    operator_pause_mode VARCHAR(32) DEFAULT NULL,
    operator_resume_at DATETIME DEFAULT NULL,
    operator_command_id VARCHAR(64) DEFAULT NULL,
    operator_paused_from_status VARCHAR(50) DEFAULT NULL,
    operator_paused_at DATETIME DEFAULT NULL,
    last_action VARCHAR(32) DEFAULT NULL,
    last_action_at DATETIME DEFAULT NULL,
    last_seen_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (account_id, adgroup_id),
    KEY idx_strategy_pause (paused_by_strategy),
    KEY idx_operator_pause (operator_pause_mode, operator_resume_at),
    KEY idx_boosted_date (boosted_date),
    KEY idx_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控广告基准与状态';

CREATE TABLE IF NOT EXISTS realtime_control_action_log (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    run_id VARCHAR(36) NOT NULL,
    control_date DATE NOT NULL,
    observed_partition VARCHAR(20) DEFAULT NULL,
    observed_cpm DECIMAL(12, 4) DEFAULT NULL,
    decision VARCHAR(32) NOT NULL,
    account_id BIGINT NOT NULL,
    adgroup_id BIGINT NOT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    bid_field VARCHAR(32) DEFAULT NULL,
    base_bid_fen INT DEFAULT NULL,
    before_bid_fen INT DEFAULT NULL,
    target_bid_fen INT DEFAULT NULL,
    before_status VARCHAR(50) DEFAULT NULL,
    target_status VARCHAR(50) DEFAULT NULL,
    apply_mode BOOLEAN NOT NULL DEFAULT FALSE,
    execution_status VARCHAR(32) NOT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_run_id (run_id),
    KEY idx_control_date (control_date),
    KEY idx_adgroup (account_id, adgroup_id),
    KEY idx_execution_status (execution_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实时CPM调控动作审计';

CREATE TABLE IF NOT EXISTS operator_command (
    command_id VARCHAR(64) NOT NULL PRIMARY KEY,
    source_message_id VARCHAR(128) NOT NULL,
    chat_id VARCHAR(128) NOT NULL,
    sender_open_id VARCHAR(128) NOT NULL,
    sender_name VARCHAR(255) DEFAULT NULL,
    action VARCHAR(32) NOT NULL,
    scope_type VARCHAR(32) NOT NULL,
    target_account_ids TEXT NOT NULL,
    status VARCHAR(32) NOT NULL,
    preview_account_count INT NOT NULL DEFAULT 0,
    preview_ad_count INT NOT NULL DEFAULT 0,
    expires_at DATETIME DEFAULT NULL,
    confirmed_at DATETIME DEFAULT NULL,
    executed_at DATETIME DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_operator_source_message (source_message_id),
    KEY idx_operator_status_expiry (status, expires_at),
    KEY idx_operator_sender (sender_open_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='飞书运营控制命令';

CREATE TABLE IF NOT EXISTS operator_command_item (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    command_id VARCHAR(64) NOT NULL,
    account_id BIGINT NOT NULL,
    audience_name VARCHAR(200) DEFAULT NULL,
    adgroup_id BIGINT DEFAULT NULL,
    adgroup_name VARCHAR(255) DEFAULT NULL,
    before_status VARCHAR(50) DEFAULT NULL,
    target_status VARCHAR(50) DEFAULT NULL,
    readback_status VARCHAR(50) DEFAULT NULL,
    execution_status VARCHAR(32) NOT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_operator_item_command (command_id),
    KEY idx_operator_item_ad (account_id, adgroup_id),
    KEY idx_operator_item_status (execution_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='飞书运营控制命令广告明细';