| 123456789101112131415161718192021222324252627282930 |
- -- 数字员工用户画像表（长期记忆核心载体）
- CREATE TABLE IF NOT EXISTS lobster_user_profile (
- id BIGINT AUTO_INCREMENT PRIMARY KEY,
- company_id BIGINT NOT NULL,
- external_user_id VARCHAR(128) NOT NULL,
- channel_type VARCHAR(20) NOT NULL DEFAULT 'QW' COMMENT '渠道类型 QW/WX/IM',
- nickname VARCHAR(128) DEFAULT NULL COMMENT '客户昵称',
- persona VARCHAR(500) DEFAULT NULL COMMENT 'AI生成的人物画像描述',
- current_state VARCHAR(100) DEFAULT NULL COMMENT '当前阶段',
- lifecycle_stage VARCHAR(30) DEFAULT 'NEW' COMMENT '客户生命周期(NEW/ACTIVE/SLEEP/DORMANT/CHURN)',
- value_score INT DEFAULT 10 COMMENT '客户价值分(0-100)',
- total_purchase DECIMAL(12,2) DEFAULT 0 COMMENT '累计消费金额',
- interaction_count INT DEFAULT 0 COMMENT '交互次数',
- last_active_time DATETIME DEFAULT NULL COMMENT '最后活跃时间',
- internal_tags VARCHAR(1000) DEFAULT NULL COMMENT '内部标签(JSON数组)',
- variable_snapshot VARCHAR(2000) DEFAULT NULL COMMENT '关键变量快照(JSON)',
- latest_stage VARCHAR(64) DEFAULT NULL COMMENT '最新会话阶段',
- latest_customer_attitude VARCHAR(32) DEFAULT NULL COMMENT '最新客户情绪/态度',
- latest_summary_text VARCHAR(512) DEFAULT NULL COMMENT '最新摘要片段',
- profile_aggregate_preview VARCHAR(420) DEFAULT NULL COMMENT '列表聚合预览',
- latest_summary_time DATETIME DEFAULT NULL COMMENT '最新摘要时间',
- deleted INT DEFAULT 0,
- create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
- update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- UNIQUE KEY uk_user (company_id, channel_type, external_user_id),
- KEY idx_lifecycle (lifecycle_stage),
- KEY idx_profile_latest_stage (company_id, latest_stage),
- KEY idx_profile_latest_attitude (company_id, latest_customer_attitude),
- KEY idx_profile_latest_summary_time (company_id, latest_summary_time)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数字员工用户画像表(长期记忆)';
|
