tencent-baidu-tracking/src/main/resources/schema-kuaishou.sql

CREATE TABLE IF NOT EXISTS kuaishou_ad_bid_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    qk VARCHAR(128) NOT NULL COMMENT '唯一业务键(query key)',
    media VARCHAR(64) NOT NULL COMMENT '媒体来源(kuaishou)',
    media_trace_id VARCHAR(255) COMMENT '媒体追踪ID(callback/设备标识)',
    platform VARCHAR(16) COMMENT '平台(android/ios)',
    account_id VARCHAR(128) COMMENT '快手投放账户ID',
    event_type VARCHAR(32) COMMENT '事件类型(impression/click)，仅保留排障所需',
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    price BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '竞价价格(分)',
    package_name VARCHAR(255) COMMENT '应用包名',
    media_params JSON COMMENT '媒体参数精简白名单(回传/扣量所需设备标识等)',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    UNIQUE KEY uk_kuaishou_ad_bid_events_qk (qk),
    KEY idx_kuaishou_ad_bid_events_media_trace (media, media_trace_id),
    KEY idx_kuaishou_ad_bid_events_platform_tag (platform, tag_id),
    KEY idx_kuaishou_ad_bid_events_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快手竞价事件记录';

CREATE TABLE IF NOT EXISTS kuaishou_tracking_reports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    qk VARCHAR(128) COMMENT '关联的业务键',
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    kind VARCHAR(32) NOT NULL COMMENT '类型(impression/click)',
    tracking_url TEXT NOT NULL COMMENT '上报的目标URL',
    status INT NOT NULL COMMENT 'HTTP响应状态码',
    ok TINYINT(1) NOT NULL COMMENT '是否成功(1=成功,0=失败)',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    KEY idx_kuaishou_tracking_reports_qk_kind_created_at (qk, kind, created_at),
    KEY idx_kuaishou_tracking_reports_tag_created_at (tag_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快手曝光/点击上报结果';

CREATE TABLE IF NOT EXISTS kuaishou_media_callbacks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dedupe_key VARCHAR(255) NOT NULL COMMENT '去重键',
    qk VARCHAR(128) COMMENT '关联的业务键',
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    callback_url TEXT NOT NULL COMMENT '回传的API URL',
    event_type INT NOT NULL COMMENT '快手回传事件类型',
    event_time_ms BIGINT NOT NULL COMMENT '事件时间(毫秒时间戳)',
    purchase DOUBLE COMMENT '付费金额',
    deduction_rate INT NOT NULL DEFAULT 0 COMMENT '扣量比例(0-100)',
    is_direct_match TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否直归因(1=是,0=否)',
    dispatch_status VARCHAR(32) NOT NULL DEFAULT 'SENT' COMMENT '回传处理状态(SENT/DEDUCTED)',
    status INT NOT NULL COMMENT 'HTTP响应状态码',
    ok TINYINT(1) NOT NULL COMMENT '是否成功(1=成功,0=失败)',
    attempt INT NOT NULL DEFAULT 1 COMMENT '尝试次数',
    response_body TEXT COMMENT '响应体(截断)',
    error_message TEXT COMMENT '错误信息',
    request_body TEXT COMMENT '请求体',
    created_at TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    KEY idx_kuaishou_media_callbacks_dedupe_ok_created_at (dedupe_key, ok, created_at),
    KEY idx_kuaishou_media_callbacks_qk_created_at (qk, created_at),
    KEY idx_kuaishou_media_callbacks_tag_created_at (tag_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快手媒体转化回传记录';

CREATE TABLE IF NOT EXISTS kuaishou_tag_event (
    tag_id VARCHAR(128) NOT NULL COMMENT '百度广告位ID',
    baidu_act INT NOT NULL COMMENT '百度转化行为',
    event_type INT NOT NULL COMMENT '快手回传事件类型',
    PRIMARY KEY (tag_id, baidu_act)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快手广告位回传事件配置';