模板变量数据管道审计

Template Variable Data Pipeline - 从原始数据到邮件渲染的完整链路追踪与修复记录 | 2026-02-17

1. 管道架构总览

Celoria 的模板变量数据流经 5 个阶段：

原始事件
预约完成 / 交易

→

物化存储
guests 表列 / JSONB

→

SQL 查询层
SELECT + JOIN

→

上下文构建
4 套 Context Builder

→

Handlebars 渲染
{{variable}} → 文本

管道断裂可以发生在任何阶段之间。本次审计发现的问题集中在 阶段1→2（写入未触发）和 阶段3→4（字段名对接失败）。

2. 四套上下文系统

系统 1: variable-engine

通用营销模板（扁平结构）
buildVariableContext()

系统 2: promotion-context

营销推广模板（分类嵌套）
buildPromotionContext()

系统 3: service-context

预约通知模板（数组支持）
buildServiceContext()

系统 4: outreachService

研究项目外呼（扁平结构）
buildTemplateData()

3. 审计发现的管道断裂

断裂 #1：total_visits / total_spent 未写入

已修复 guests 表有 total_visits 和 total_spent 列，但 appointmentHandler 在预约完成时只更新 last_visit_date，不更新统计字段。

修复：新增 updateGuestStats() 方法，使用子查询 COUNT(*) / SUM(total_amount) 从 appointments 表重算，避免并发自增漂移。

文件：backend/services/tagging/eventHandlers/appointmentHandler.js

断裂 #2：favorite_service 声明但从未计算

已修复 promotion-context.js 声明了 favorite_service 变量，但没有任何服务计算和写入它。

修复：在 inferredPreferenceService.js 新增 calculateAndUpdateFavoriteService()，使用时间衰减加权算法（与 inferred_preferred_time 相同权重配置），将结果写入 guests.preferences JSONB。在 appointmentHandler 中于预约完成时触发（非阻塞）。

文件：backend/services/guest/inferredPreferenceService.js、appointmentHandler.js

断裂 #3：outreachService SQL 缺少行为字段

已修复 outreachService 的 3 个 SQL 查询（getPendingOutreach、getPendingFallbacks、getParticipantWithGuest）没有 SELECT guests 的行为字段，buildTemplateData 也不输出行为变量。

修复：在 3 个 SQL 中添加 g.total_visits, g.total_spent, g.last_visit_date, g.vip_status, g.preferences；在 buildTemplateData 中添加 JSONB 解析和 5 个行为变量。

文件：backend/services/research/outreachService.js

断裂 #4：variable-engine 读不到 JSONB 字段

已修复 buildVariableContext() 直接读 recipient.favorite_service 和 recipient.preferred_time_slot，但 guests 表没有这两个列。数据存在 guests.preferences JSONB 中。

修复：在函数头部添加 preferences JSONB 解析，将 favorite_service 和 preferred_time_slot 的读取改为 fallback 链。

文件：backend/services/templating/variable-engine.js

4. 系统 1: variable-engine 完整变量映射

47 个变量，用于通用营销邮件。格式：{{variable_name}}

客户基础信息（6 个）

变量	数据来源	状态
`customer_name`	guests.name / guests.first_name	OK
`first_name`	guests.first_name 或从 name 拆分	OK
`last_name`	guests.last_name 或从 name 拆分	OK
`email`	guests.email	OK
`phone`	guests.phone	OK
`preferred_language`	guests.preferred_language	OK

业务数据（7 个）

变量	数据来源	状态
`appointment_id`	由调用方传入 campaign 对象	OK
`appointment_time`	appointments.scheduled_time	OK
`appointment_date`	appointments.scheduled_date	OK
`service_name`	services.name	OK
`services`	数组，由调用方传入	OK
`technician_name`	employees.name	OK
`total_amount`	appointments.total_amount	OK

门店信息（6 个）

变量	数据来源	状态
`store_name`	centers.name	OK
`store_address`	centers.address	OK
`store_phone`	centers.phone	OK
`store_hours`	centers.business_hours	OK
`store_logo`	centers.logo_url	OK
`store_website`	centers.website	OK

营销活动（7 个）

变量	数据来源	状态
`discount_code`	campaign 配置传入	OK
`discount_amount`	campaign 配置传入	OK
`discount_percentage`	campaign 配置传入	OK
`discount_description`	campaign 配置传入	OK
`expiry_date`	campaign 配置传入	OK
`promotion_title`	campaign 配置传入	OK
`promotion_description`	campaign 配置传入	OK

忠诚度（4 个）

变量	数据来源	状态	备注
`loyalty_points`	guests.loyalty_points	OK
`loyalty_tier`	guests.vip_status	OK
`is_vip`	派生: vip_status === 'VIP'	OK
`membership_level`	guests.membership_level	空壳	guests 表无此列，永远返回 'basic'

客户行为（7 个） — 本次修复重点

变量	数据来源	状态	写入方
`last_visit_date`	guests.last_visit_date	OK	appointmentHandler.updateGuestLastVisitDate()
`days_since_visit`	运行时计算	OK	Math.floor((now - last_visit_date) / day_ms)
`visit_count`	guests.total_visits	已修复	appointmentHandler.updateGuestStats() — COUNT(*)
`total_spend`	guests.total_spent	已修复	appointmentHandler.updateGuestStats() — SUM(total_amount)
`favorite_service`	guests.preferences JSONB	已修复	inferredPreferenceService.calculateAndUpdateFavoriteService() — 时间衰减加权
`preferred_time_slot`	guests.preferences JSONB	已修复	inferredPreferenceService.calculateAndUpdateInferredPreferredTime()
`tags`	guest_tags 关联表	依赖调用方	需调用方 JOIN guest_tags 后传入数组

系统链接（7 个）+ 日期（3 个）

变量	来源	状态
`unsubscribe_link`	运行时: `${API_BASE_URL}/api/promotion/unsubscribe/${id}`	OK
`view_in_browser_link`	运行时: `${API_BASE_URL}/api/promotion/view/${id}`	OK
`booking_link`	运行时: `${WEB_BASE_URL}/booking`	OK
`booking_link_with_service`	运行时: `booking?service=${id}`	OK
`feedback_link`	运行时拼接	OK
`rewards_link`	静态: `${WEB_BASE_URL}/rewards`	OK
`account_link`	静态: `${WEB_BASE_URL}/account`	OK
`current_year`	运行时	OK
`current_date`	运行时	OK
`current_time`	运行时	OK

5. 时间衰减加权算法

favorite_service 和 preferred_time_slot 的计算都使用同一套时间衰减权重：

时间范围	权重	说明
近 1 个月	1.0	最近行为权重最高
1 - 3 个月	0.7
3 - 6 个月	0.5
6 个月以上	0.3	远期行为仍有参考价值

favorite_service 算法：查询该客户所有已完成预约 JOIN services，按服务名分组，对每笔预约按时间衰减加权累加分数，取最高分服务写入 guests.preferences.favorite_service。

preferred_time_slot 算法：查询已完成预约的时间，按时段（morning 9-12 / afternoon 12-17 / evening 17-21）分组，同样加权累加，取最高分时段写入 guests.preferences.inferred_preferred_time。

6. 修复时间线

2026-02-17 Step 1

appointmentHandler: 新增 updateGuestStats()

预约完成时用子查询重算 total_visits / total_spent，同时触发 favorite_service 计算

2026-02-17 Step 2

inferredPreferenceService: 新增 calculateAndUpdateFavoriteService()

时间衰减加权算法计算客户最常做的服务，写入 preferences JSONB

2026-02-17 Step 3

outreachService: SQL + buildTemplateData 补全行为字段

3 个 SQL 查询补全 5 个 SELECT 字段，buildTemplateData 补全 JSONB 解析和 5 个行为变量

2026-02-17 Step 4

promotion-context: favorite_service fallback 链

先解析 guest.preferences JSONB，再做 guest.favorite_service || preferences.favorite_service 兜底

2026-02-17 Step 5

variable-engine: JSONB 解析 + 两字段修复

buildVariableContext() 头部解析 preferences JSONB，favorite_service 和 preferred_time_slot 改为 fallback 链

2026-02-17 Step 6

历史数据回填

tenant_qqnails (49 guests) + tenant_ppnails (3,902 guests) 的 total_visits / total_spent / last_visit_date / favorite_service 全部回填完成

2026-02-17 Step 7

promotion.js: 批量发送 SQL 扩充行为字段

recipients SQL 从 SELECT id, name, email, phone 扩充为包含 first_name, last_name, total_visits, total_spent, last_visit_date, vip_status, loyalty_points, preferences

2026-02-17 Step 8

promotion.js: sendEmail 传递完整 recipient

sendEmail() 从只传 {email, name, id} 改为传递完整 recipient 对象，使 buildVariableContext() 能读取全部行为字段

2026-02-17 Step 9

notification-dispatcher: guest 查询扩充行为字段

SQL 从 5 字段扩充为包含 total_visits, total_spent, last_visit_date, vip_status, loyalty_points, preferences，guest 对象透传全部字段

2026-02-17 Step 10

membership_level: 映射到 vip_status

variable-engine + promotion-context 的 membership_level 改为读取 vip_status || 'basic'，不再是空壳硬编码

2026-02-17 Step 11

tags: SQL 子查询聚合 + 全系统支持

promotion.js + outreachService 3 个 SQL 加入 guest_tags 子查询聚合。promotion-context + variable-engine 支持 {{tags}} 和 {{#each tags}}

2026-02-17 Step 12

4 套系统变量对齐

outreachService 补齐 days_since_visit, loyalty_points, membership_level, is_vip, preferred_time_slot, tags 6 个缺失变量，与系统 1/2 对齐

7. 残留问题

问题	严重度	说明
service-context 未加行为字段	已评估	预约通知模板使用 `{{#each guests}}` 数组结构，行为字段对其场景无意义，不修改
tenant_perftest 已清除	已完成	Schema 已 DROP（12 GB / 995K guests / 105 tables）。功能定型后需重新生成性能测试数据