Neo-ZQYY/docs/database/BD_Manual_member_retention_clue.md

BD 手册：维客线索表（member_retention_clue）

概述

在 zqyy_app / test_zqyy_app 业务库中新建 member_retention_clue 表，替代原 member_birthday_manual 表。维客线索是助教为会员记录的销售/维护线索，采用"大类 + 摘要 + 详情"三层结构，覆盖客户基础、消费习惯、玩法偏好、促销偏好、社交关系、重要反馈六个维度。

生日信息不再单独建表，作为"客户基础"大类下的一条线索记录。

变更说明

库	Schema	表	变更类型	说明
zqyy_app / test_zqyy_app	public	member_birthday_manual	删除	旧表，生日单独记录方案废弃
zqyy_app / test_zqyy_app	public	member_retention_clue	新建	维客线索表
zqyy_app / test_zqyy_app	public	member_retention_clue.source	新增列	2026-02-27 补齐线索来源字段
zqyy_app / test_zqyy_app	public	member_retention_clue.category	约束变更	2026-03-08 枚举对齐：客户基础信息 → 客户基础
zqyy_app / test_zqyy_app	public	member_retention_clue.is_hidden	新增列	2026-03-20 NS4 租户管理后台：隐藏/显示控制

表结构

列名	类型	约束	说明
id	BIGSERIAL	PRIMARY KEY	自增主键
member_id	BIGINT	NOT NULL	会员 ID
category	VARCHAR(20)	NOT NULL, CHECK	线索大类枚举（6 值，见下方枚举表）
summary	VARCHAR(200)	NOT NULL	摘要：重点信息
detail	TEXT	可为空	详情：分析及扩展说明
recorded_by_assistant_id	BIGINT	—	记录助教 ID
recorded_by_name	VARCHAR(50)	—	记录助教姓名
recorded_at	TIMESTAMPTZ	NOT NULL DEFAULT NOW()	记录时间
site_id	BIGINT	NOT NULL	门店 ID（多门店隔离）
source	VARCHAR(20)	NOT NULL DEFAULT 'manual'	线索来源（2026-02-27 新增）
is_hidden	BOOLEAN	NOT NULL DEFAULT false	是否隐藏（true=管理后台保留但小程序不展示）（2026-03-20 新增）

category 枚举值

2026-03-08 枚举对齐：客户基础信息 → 客户基础（P5 spec 评审决定，与 AI 应用 Prompt 统一）

值	说明
客户基础	生日、职业、偏好时段等基础画像
消费习惯	消费频次、客单价、消费时段等
玩法偏好	中式/斯诺克/美式偏好、技术水平等
促销偏好	对储值活动、套餐、折扣的敏感度
社交关系	常带朋友、固定球搭子、社交圈等
重要反馈	客户提出的需求、投诉、建议等

source 枚举值（2026-02-27 新增）

值	说明
manual	助教手动录入（默认值）
ai_consumption	应用 3：消费分析自动生成
ai_note	应用 6：备注分析自动提取

约束与索引

名称	类型	列	说明
member_retention_clue_pkey	PRIMARY KEY	id	主键
chk_retention_clue_category	CHECK	category	限制大类枚举值
idx_retention_clue_member	INDEX (btree)	member_id	按会员查询
idx_retention_clue_site	INDEX (btree)	site_id	按门店查询
idx_retention_clue_category	INDEX (btree)	(member_id, category)	按会员+大类查询

兼容性

• 后端 API：POST /api/member-birthday 废弃，替换为 POST /api/retention-clue、GET /api/retention-clue/{member_id}、DELETE /api/retention-clue/{clue_id}
• source 字段（2026-02-27）：POST /api/retention-clue 接受可选 source 参数，默认 manual；GET 返回中包含 source 字段。已有数据自动填充 DEFAULT 'manual'，向后兼容
• is_hidden 字段（2026-03-20 NS4）：租户管理后台线索隐藏/显示控制。DEFAULT false 保证已有数据兼容。小程序端线索查询已追加 WHERE is_hidden = false 条件，隐藏线索仅在管理后台可见。租户管理后台路由 tenant_clues.py 提供 PATCH /api/tenant/clues/{id}/visibility 端点切换状态
• ETL Connector：DWS 任务移除 FDW 读取 member_birthday_manual 的逻辑，生日仅从 dim_member.birthday（API 来源）读取
• FDW：fdw_app.member_birthday_manual 外部表需在 ETL 库侧同步更新为 fdw_app.member_retention_clue（含 source 列）
• 小程序：助教端调用新 API 提交维客线索
• H5 原型：customer-detail 和 task-detail 页面"消费习惯"板块改为"维客线索"

回滚策略

仅回滚 source 列（2026-02-27 变更）

BEGIN;
ALTER TABLE member_retention_clue DROP COLUMN IF EXISTS source;
COMMIT;

完整回滚（整表）

BEGIN;
DROP TABLE IF EXISTS member_retention_clue CASCADE;
-- 如需恢复旧表，执行归档的 2026-02-22__C2_member_birthday_manual.sql
COMMIT;

验证步骤

-- 1. 确认旧表已删除
SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'public' AND table_name = 'member_birthday_manual';
-- 预期：0 行

-- 2. 确认新表存在
SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'public' AND table_name = 'member_retention_clue';
-- 预期：1 行

-- 3. 确认列结构完整（11 列）
SELECT column_name, data_type, is_nullable
  FROM information_schema.columns
 WHERE table_schema = 'public' AND table_name = 'member_retention_clue'
 ORDER BY ordinal_position;
-- 预期：id, member_id, category, summary, detail,
--        recorded_by_assistant_id, recorded_by_name, recorded_at, site_id, source, is_hidden

-- 4. 确认 CHECK 约束
SELECT conname FROM pg_constraint
 WHERE conrelid = 'member_retention_clue'::regclass AND contype = 'c';
-- 预期：chk_retention_clue_category

-- 5. 确认索引（3 + 主键）
SELECT indexname FROM pg_indexes
 WHERE tablename = 'member_retention_clue';
-- 预期：4 行

-- 6. 确认表注释
SELECT obj_description('member_retention_clue'::regclass, 'pg_class');
-- 预期：包含"维客线索"

-- 7. 确认 source 列存在且默认值正确（2026-02-27）
SELECT column_name, data_type, column_default, is_nullable
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name = 'member_retention_clue'
   AND column_name = 'source';
-- 预期：1 行，varchar, 'manual'::character varying, NO

-- 8. 确认 source 列注释
SELECT col_description(
    (SELECT oid FROM pg_class WHERE relname = 'member_retention_clue'),
    (SELECT ordinal_position FROM information_schema.columns
     WHERE table_name = 'member_retention_clue' AND column_name = 'source')
);
-- 预期：包含 'manual' / 'ai_consumption' / 'ai_note'

-- 9. 确认已有数据的 source 分布
SELECT source, COUNT(*) FROM member_retention_clue GROUP BY source;
-- 预期：全部为 'manual'（或空表）

-- 10. 确认 is_hidden 列存在且默认值正确（2026-03-20）
SELECT column_name, data_type, column_default, is_nullable
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name = 'member_retention_clue'
   AND column_name = 'is_hidden';
-- 预期：1 行，boolean, false, NO

-- 11. 确认已有数据的 is_hidden 分布（2026-03-20）
SELECT is_hidden, COUNT(*) FROM member_retention_clue GROUP BY is_hidden;
-- 预期：全部为 false（DEFAULT false 保证兼容）

-- 12. 确认列结构完整（11 列，含 is_hidden）
SELECT COUNT(*) FROM information_schema.columns
 WHERE table_schema = 'public' AND table_name = 'member_retention_clue';
-- 预期：11

关联文件

• 迁移脚本（建表）：db/zqyy_app/migrations/2026-02-26__refactor_birthday_to_retention_clue.sql
• 迁移脚本（source 列）：db/zqyy_app/migrations/2026-02-27__add_source_to_retention_clue.sql
• 迁移脚本（category 枚举对齐）：db/zqyy_app/migrations/2026-03-08__align_retention_clue_category_enum.sql
• 迁移脚本（is_hidden 列）：db/zqyy_app/migrations/2026-03-xx__ns4_member_clue_is_hidden.sql
• FDW 反向映射（生产）：db/fdw/setup_fdw_reverse.sql
• FDW 反向映射（测试）：db/fdw/setup_fdw_reverse_test.sql
• 后端路由：apps/backend/app/routers/member_retention_clue.py
• 后端路由（租户管理后台）：apps/backend/app/routers/tenant_clues.py
• 后端模型：apps/backend/app/schemas/member_retention_clue.py
• H5 原型：docs/h5_ui/pages/customer-detail.html、docs/h5_ui/pages/task-detail.html
• 旧表文档（已归档）：docs/database/_archived/BD_Manual_member_birthday_manual.md
• 旧 FDW 文档（已归档）：docs/database/_archived/BD_Manual_fdw_reverse_member_birthday.md