#Coaching Schema

Source of truth: callytics-common/src/db/schema/calls.ts + coaching-reviews.ts + ai-feedback.ts (verified 2026-06-15) 物理表边界: AI coaching 内容仍在 calls 表；经理处理状态在 coaching_reviews；对 AI 内容质量/采纳的反馈在 ai_feedback(subject_type='coaching')。 Calls 全表: ../calls-feature/calls-schema.md

#1. 表说明

Coaching 有 3 层数据，不要混在一起：

calls 内的 4 个 coaching/review 字段拆 2 类:

• 已接线 (AI 真在写,前端真在读): practicalCoachingScenario / practicalCoachingFeedback — 给前台看的辅导建议
• 已定义未接线 (schema 有列 + 有 index, AI pipeline 零 write, 前端零 read): reviewCategory / reviewReason — 通话质量评估标签

写入者: ai (Coaching prompt) = ai-analysis-processor Stage 3 (Coaching) 调用 AI prompt 后直接写入 calls 表对应 4 列。

Coaching gate (shouldRunCoaching()): 必须 callState === 'human_conversation' 且 duration > 30 秒才跑 AI;否则填占位 sentinel,详见 coaching-calculations.md。

覆盖语义 + 反馈叠加:coaching 是 per-call 一次性产出,仅手动 reprocess 覆盖(罕见)。人工修改不动 calls 原行,而是走统一反应层 ai_feedback 的 corrected 叠加 —— 设计见 AI 产出数据架构。

#2. Schema 全字段

⚠️ = "schema 占位 + 未接线" — 6 个 reviewCategory 值是设计意图,不是可观察行为。

#3. reviewCategory enum — 6 values

reviewCategory 列单独拉出来,每个 value 有含义说明。

#4. coaching_reviews workflow 表

coaching_reviews 是一通 coaching call 在 manager inbox 里的处理状态。它不存 AI coaching 内容，只用 (store_id, telephony_session_id) 对齐 calls。

#5. ai_feedback(subject_type='coaching')

ai_feedback 是产品用户对 AI 内容的反应层。对 coaching 来说，subject_id = calls.telephony_session_id。

#6. Sentinel 占位值

practicalCoachingScenario 不只有 AI 自由文本,有 3 种 sentinel 占位,分管道路径:

⚠️ 前端 / 运营按字符串识别空 coaching 时要匹配 No coaching — (em dash) — 当前活跃路径是 staged pipeline。详细行为见 coaching-calculations.md。

#7. practicalCoachingFeedback jsonb 结构

对应 Coaching prompt 输出的 detailed_feedback_item:

AI 判断无辅导时刻时,5 个子字段全填 "N/A"(详见 §6)。

V1 coaching-highlights-spec 提过的"5 个 SA 维度打分"(Booking Ask / Objection Handling / Retention Technique / Rapport Building / Information Accuracy,各 0-10 分)当前未实现 — 代码里只有 scenario + feedback(单个最有价值的辅导时刻)。V1 spec 见 ../../v1/coaching-highlights-feature/coaching-highlights-spec.md。

#8. Prompt Output ↔ Schema 字段对照(给 reviewer 用)

Reviewer 用本表验证 prompt 输出 JSON 字段是否落在 calls 表的 coaching 子集内。

reviewCategory / reviewReason 没有 prompt 在产出 — 当前 7 个 prompt 中,reviewer 无需验证这两个字段。

#9. Appendix A — Re-verification commands

2026-06-15 verified。文档 stale(>30 天)时重跑下方命令并比对。

# 1. 4 个 coaching 字段是否还在 calls.ts 里
grep -nE "practicalCoaching|reviewCategory|reviewReason" \
  ../../../../../callytics-common/src/db/schema/calls.ts
# 期望: 4 行 column 定义 + 1 行 idx_calls_review_category index

# 1b. manager workflow 表 + AI feedback 表
grep -nE "coachingReviews|COACHING_REVIEW_STATUS|aiFeedback|AI_FEEDBACK_SUBJECT_TYPE|AI_FEEDBACK_TYPE" \
  ../../../../../callytics-common/src/db/schema/coaching-reviews.ts \
  ../../../../../callytics-common/src/db/schema/ai-feedback.ts

# 2. Codex final design 是否动 coaching
grep -i "coaching\|practicalCoaching\|reviewCategory" \
  ../../tasks-feature/design/task-pipeline-deliverable-codex.md
# 期望: 0 行 — codex 完全不提 coaching

# 3. Unified pipeline final 是否动 coaching schema
grep -i "coaching\|practicalCoaching\|reviewCategory" \
  ../../unified-pipeline/unified-pipeline-final.md
# 期望: 只 reference Coaching 是 Stage ⑤ + 写 calls 表,不改 schema

# 4. reviewCategory 真接线了吗
grep -rnE "reviewCategory|review_category" \
  ../../../../../callytics-infrastructure/lambda/ \
  ../../../../../callytics-common/src/ 2>/dev/null | \
  grep -v schema/calls.ts
# 期望: 0 行 write/read — 仍是 schema 占位

#10. Cross-References

• Calls 全表 schema(coaching 4 字段所在的物理表): ../calls-feature/calls-schema.md
• Coaching gate + sentinel 行为详解: coaching-calculations.md
• Coaching 前端展示: coaching-display.md
• Tasks 表(下游 Task Decision prompt 写入): ../tasks-feature/tasks-schema.md
• Unified pipeline (Coaching 是 Stage ⑤): ../unified-pipeline/unified-pipeline-final.md
• All 7 prompts: ../unified-pipeline/prompts/
• V1 5 维度打分(规划中,未实现): ../../v1/coaching-highlights-feature/coaching-highlights-spec.md
• Live schema: callytics-common/src/db/schema/calls.ts (lines 217, 220, 229, 232 + index line 311)