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,
    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_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调控动作审计';