Retaintive

    Dashboard Schema

    Status boundary：本文主要保留 Dashboard metric lineage 的 2026-06 snapshot。closeResult / task_progress_events 混合了当时的 implementation 与未落地 proposal，实施前必须重新核查 endpoint SQL。当前 selected Target 见 Task V3 Product / Domain Contract，实施与环境状态见 Task V3 Rollout 状态快照；Task V2+ audit 只保留 historical context。

    2026-06 snapshot sources:

    • Endpoint SQL: studio-website-monorepo/apps/api/src/routes/v3/dashboard-*.ts(7 个 endpoint)
    • 上游 schema: callytics-common/src/db/schema/{calls,tasks,contacts,leads,messages}.ts

    (verified 2026-06-15)

    阅读顺序:§1 Dashboard 是什么 → §2 Metric → Source 血缘大表 → §3 Task outcome/workload 口径注意 → §4 Prompt 对照 → §5 重新验证命令 → §6 Cross-refs。

    1. Dashboard 是什么

    Dashboard 没有自己的 DB 表,7 个 v3 endpoint 全部读时实时聚合 calls / tasks / contacts / leads / messages / coaching 字段,按 store_id 隔离。本页的"schema"实际是字段血缘(每个指标来自哪张源表哪一列、用什么公式算)而不是表结构。

    7 个 endpoint:

    Endpoint一句话用途
    GET /v3/dashboard/overview单店总览(contacts 生命周期 + calls 漏接 + messages + leadFunnel + dailyActivity + pipeline)
    GET /v3/dashboard/summary单店当期 vs 上期 KPI(周报 / 月报)
    GET /v3/dashboard/multi-store跨门店 per-store 明细 + totals(My Stores 总览)
    GET /v3/dashboard/staff员工维度绩效
    GET /v3/dashboard/lead-trackerLead 追踪
    GET /v3/dashboard/coaching-highlights辅导亮点
    GET /v3/dashboard/analytics通话分析深钻

    Task 状态读时算:Dashboard 里 tasks_overdue 这类指标用 NOW() 即时计算(status='open' AND due_at < NOW()),不读持久化的 overdue 状态 — tasks 表只存 open / closed。所有 calls / tasks 指标都加 WHERE store_id = $1 做多租户隔离。

    2. Metric → Source 血缘大表

    每一行回答:这个指标是哪个 endpoint 出的、对应源表哪个 column、用什么 SQL 公式算、谁消费(UI Tab / 卡片)。

    #MetricTypeEndpointSource table.columnAggregation 公式消费方
    contacts 生命周期
    1totalintegeroverviewcontacts.lifecycle_stageCOUNT(*),全量(无时间窗)Performance · 概览
    2leadsintegeroverviewcontacts.lifecycle_stageCOUNT(*) FILTER (WHERE lifecycle_stage = 'lead')Performance · 概览
    3membersintegeroverviewcontacts.lifecycle_stageCOUNT(*) FILTER (WHERE lifecycle_stage = 'member')Performance · 概览
    4churnedintegeroverviewcontacts.lifecycle_stageCOUNT(*) FILTER (WHERE lifecycle_stage = 'churned')Performance · 概览
    calls 量(近 30 天)
    5totalintegeroverviewcalls.start_timeCOUNT(*) (近 30 天)Performance · Call Volume
    6inboundintegeroverviewcalls.directionCOUNT(*) FILTER (WHERE direction = 'Inbound')Performance · Call Volume
    7outboundintegeroverviewcalls.directionCOUNT(*) FILTER (WHERE direction = 'Outbound')Performance · Call Volume
    8missedintegeroverviewcalls.resultCOUNT(*) FILTER (WHERE result IN ('Missed','No Answer','Voicemail'))Performance · Call Volume
    9avgDurationinteger(秒)overviewcalls.durationROUND(AVG(duration))Performance · Call Volume
    10todayTotalintegeroverviewcalls.start_timeCOUNT(*) FILTER (WHERE start_time::date = CURRENT_DATE)Performance · 概览
    11yesterdayTotalintegeroverviewcalls.start_timeCOUNT(*) FILTER (WHERE start_time::date = CURRENT_DATE - 1)Performance · 概览
    messages(近 30 天)
    12totalintegeroverviewmessages.typeCOUNT(*) FILTER (WHERE type = 'SMS')Performance · 概览
    13unreadintegeroverviewmessages.read_statusCOUNT(*) FILTER (WHERE read_status = 'Unread')Performance · 概览
    14todayTotalintegeroverviewmessages.creation_timeCOUNT(*) FILTER (WHERE creation_time::date = CURRENT_DATE)Performance · 概览
    leadFunnel(本周 vs 上周)
    15receivedintegeroverviewcontacts.lifecycle_stage + contacts.created_atlead 且近 14 天创建,按本周 / 上周 7 天分桶Performance · Lead Funnel
    16contactedintegeroverviewcontacts + calls JOIN存在 contact.created_at 之后的 outbound calls 即算 contactedPerformance · Lead Funnel
    17bookedintegeroverviewcontacts.lead_statusCOUNT(*) FILTER (WHERE lead_status = 'booked')Performance · Lead Funnel
    18speedToLead.medianMinutesnumericoverviewcontacts.created_at + first outbound calls.start_timePERCENTILE_CONT(0.5) 分钟差(近 30 天样本)Performance · Lead Funnel
    19speedToLead.sampleSizeintegeroverview同上中位数样本数Performance · Lead Funnel
    pipeline 漏斗分布
    20pipeline[]jsonb [{status, count}]overviewcontacts.lead_statusGROUP BY lead_status WHERE lifecycle_stage='lead' AND lead_status IS NOT NULLPerformance · Pipeline
    dailyActivity(近 7 天趋势)
    21dailyActivity[]jsonb [{day, calls, messages, newLeads}]overviewcalls.start_time + messages.creation_time + contacts.created_at按天分组 COUNTPerformance · Trend
    summary KPI(当期 vs 上期)
    22totalCallsintegersummarycalls.start_timeCOUNT(*)(当期)Performance · KPI
    23outboundintegersummarycalls.directionCOUNT(*) FILTER (WHERE direction = 'Outbound')Performance · KPI
    24introsBookedintegersummarycalls.primary_outcome_result + calls.primary_categoryCOUNT(*) FILTER (WHERE primary_outcome_result='success' AND primary_category='revenue_impacting') ⚠️ 名字叫 introsBooked,实际口径是"成交结果为 success 的 revenue_impacting 通话",不按 primary_subcategory='intro_booking' 筛 — 与 multi-store 的 introBooking 口径不同Performance · KPI
    25cancelSavesintegersummarycalls.primary_outcome_resultCOUNT(*) FILTER (WHERE primary_outcome_result = 'retained')Performance · KPI
    26tasksClosedintegersummarytasks.status + tasks.closed_atCOUNT(*) FILTER (WHERE status='closed' AND closed_at >= 当期起点)Performance · KPI
    27deltajsonb {metric: pct}summary上述 26 指标(当期 - 上期) / 上期 × 100;上期 0 且当期 > 0 → 100;都为 0 → nullPerformance · KPI
    multi-store(跨门店)
    28totalCallsintegermulti-storecalls.call_stateCOUNT(*) FILTER (WHERE call_state IS DISTINCT FROM 'voicemail') ⚠️ 排除 voicemailMy Stores
    29outboundCallsintegermulti-storecalls.directionCOUNT(*) FILTER (WHERE direction='Outbound')My Stores
    30introBookingintegermulti-storecalls.primary_subcategoryCOUNT(*) FILTER (WHERE primary_subcategory = 'intro_booking')(不限 outcome,与 summary 口径不同)My Stores
    31membershipsSoldintegermulti-storecalls.primary_category + primary_subcategory + primary_outcome_resultFILTER (WHERE primary_category='revenue_impacting' AND primary_subcategory IN ('membership_purchase_related','reactivation_purchase') AND primary_outcome_result='success')My Stores
    32cancellationCallsintegermulti-storecalls.primary_subcategoryFILTER (WHERE primary_subcategory = 'membership_cancel')My Stores
    33cancelSavedintegermulti-storecalls.primary_subcategory + primary_outcome_resultFILTER (WHERE primary_subcategory='membership_cancel' AND primary_outcome_result='retained')My Stores
    34tasksCreatedintegermulti-storetasks.created_at当期 created_at 落在区间内 COUNT(*)My Stores
    35tasksClosedintegermulti-storetasks.closed_at当期 closed_at 落在区间内 COUNT(*)My Stores
    36tasksOverdueintegermulti-storetasks.status + tasks.due_atFILTER (WHERE status='open' AND due_at < NOW()) 实时算,不限当期区间My Stores
    37trendtextmulti-storecalls(当期 vs 上期 totalCalls)up / down / flatMy Stores
    38deltanumericmulti-store同上(当期 - 上期) / 上期 × 100My Stores
    39totalsjsonbmulti-store上述 9 个 store-level 指标求和 + storeCountMy Stores · 汇总
    staff endpoint
    40callsintegerstaffcalls.staff_nameCOUNT(*) per staff ⚠️ SQL 过滤 call_state IN ('human_conversation','voicemail') AND staff_name IS NOT NULLTeam Tab
    41inbound / outboundintegerstaffcalls.directionFILTER (WHERE direction=...) per staffTeam Tab
    42introCallsintegerstaffcalls.primary_subcategoryFILTER (WHERE primary_subcategory='intro_booking') per staffTeam Tab
    43membershipSalesintegerstaffcalls.primary_category + primary_subcategory + primary_outcome_result同 metric #31 但 per staffTeam Tab
    44cancellationSavesintegerstaffcalls.primary_subcategory + primary_outcome_result同 metric #33 但 per staffTeam Tab
    45taskCompletionRatenumericstafftasks.closed_by_staff_name + tasks.statusclosed / total per staff；当前 SQL 只看 closed_by_staff_name IS NOT NULL 的 task，见 §3Team Tab
    46coachingFlagRatenumericstaffcalls.practical_coaching_* + calls.durationcoaching_worthy / eligible_calls(eligible = duration > 60s + 有 coaching 内容)Team Tab
    47wowDirectiontextstaffcalls(当期 vs 上期)up / down / flatTeam Tab
    leadFunnel 牵引
    48Lead Follow-up 数integeroverviewoverview.leadFunnel同 metric #15-19Performance · 快捷牵引
    49Task Queue 数(open tasks)integertasks list endpoint(非 dashboard-*)tasks.statusWHERE status='open' 计数 — /v3/dashboard/overview 不返回 task 计数,前端从 tasks list endpoint 拉Performance · 快捷牵引
    50Manager Review 数integercoaching-highlightscalls.practical_coaching_scenario + practical_coaching_feedback待 review 通话计数(实际 SQL 用 practical_coaching_scenario IS NOT NULL AND NOT ILIKE 'No coaching%',见 dashboard-coaching-highlights.ts:54-87)。Planned: 切到 calls.review_category 一旦该字段接线(coaching-schema 标"未接线")Performance · 快捷牵引

    4 个 endpoint(analytics / lead-tracker / coaching-highlights / 部分 staff 字段)指标随产品迭代频繁,本表只登记代表性字段。逐字段口径直接读 apps/api/src/routes/v3/dashboard-*.ts 源码。

    3. Task Outcome / Workload 口径注意

    本文 2026-06 snapshot 使用 Task V2 tasks.status / close_result 统计部分工作与业务结果；曾提议用 task_progress_events 表示过程工作量，但它不是 current source of truth，也没有被批准为 Target。未来设计必须把 Task lifecycle、Activity、Task-specific Outcome 和 Revenue projection 分开。

    指标问题本文记录的 Task V2 / historical 口径V3 评审 / 后续改进点
    Completed objectivestasksClosed 只按 status='closed' + closed_at 时间窗统计这是“完成了多少事项”，不等于业务赢了几单
    Business wins没有统一单列；部分 metric 按 Task V2 close_result 拆分候选方向是由 taskKind + resultCode + policyVersion + verification 做 versioned projection，不维护 global positive result list
    Attempt workloadDashboard 主要看 closed / overdue Task从 canonical Activity ledger 按 channel / action / actorType / creditedStaffId 投影，不新增第二份 progress source of truth
    Staff task completiondashboard-staff.ts 当前按 closed_by_staff_name 归因，且只纳入有 staff name 的 task会漏掉 executorType='ai_agent' / system 或 create-closed 等非人工关闭路径
    AI vs human attributiontasks.executorType 已在 schema 中存在Dashboard 还没有系统性拆出 human / AI agent / system 对比

    详见 tasks-schema.md。

    4. Prompt Output ↔ Dashboard 字段对照

    n/a — Prompt 不直接写 dashboard。Dashboard 是 7 个 v3 endpoint 实时聚合上游表(calls / tasks / contacts / messages / leads),prompt 通过写上游表(calls 经 Classify、tasks 经 Task Decision、contacts 经 Contact Profile)间接驱动 dashboard 指标。

    如要验证某个 prompt 字段如何影响 dashboard,沿下面链条找:

    Prompt写哪张表的什么字段影响哪个 dashboard metric
    02 Classifycalls.primary_outcome_result / primary_category / primary_subcategory / direction 间接metric #24 introsBooked, #25 cancelSaves, #30 introBooking, #31 membershipsSold, #32 cancellationCalls, #33 cancelSaved, #42 introCalls, #43 membershipSales, #44 cancellationSaves
    04 Coachingcalls.practical_coaching_* / review_categorymetric #46 coachingFlagRate, #50 Manager Review
    05 Contact Profilecontacts.lifecycle_stage / lead_statusmetric #1-4 contacts, #15-20 leadFunnel + pipeline
    06 Task DecisionTask V2 snapshot：tasks.status / closeResult / executorTypemetric #26, #35-36 tasksClosed/Overdue, #45 taskCompletionRate；V3 Target 改读 Task/Activity/Outcome projection
    07 Task Playbookread-onlyn/a(不写库,不驱动 metric)

    详细 prompt → 上游表字段对照见 calls-schema.md §4；V3 Target Task mutation contract 见 Task V3 Product / Domain Contract §10.2。

    5. Appendix A — Re-verification commands

    2026-06-15 verified。文档 stale 时重跑下方命令并比对。

    # 1. 7 个 dashboard endpoint 源文件
ls ../../../../../studio-website-monorepo/apps/api/src/routes/v3/dashboard-*.ts

# 2. 各 endpoint 的 SQL FILTER 子句(找指标口径)
grep -nE "FILTER \(WHERE|COUNT\(\*\)|PERCENTILE_CONT|GROUP BY|JOIN" \
  ../../../../../studio-website-monorepo/apps/api/src/routes/v3/dashboard-*.ts

# 3. 上游 schema 关键列存在
grep -nE "lifecycle_stage|lead_status|primary_outcome_result|primary_subcategory|call_state|review_category|closed_by_staff" \
  ../../../../../callytics-common/src/db/schema/*.ts

# 4. Task outcome / progress 字段是否还在 schema
grep -nE "TASK_CLOSE_RESULT|executorType|taskProgressEvents|TASK_PROGRESS_TYPE|closedByStaffName" \
  ../../../../../callytics-common/src/db/schema/tasks.ts \
  ../../../../../callytics-common/src/db/schema/task-progress-events.ts

# 5. Unified pipeline 是否提到 dashboard 影响
grep -ni "dashboard\|metric" \
  ../../unified-pipeline/unified-pipeline-final.md

    6. Cross-References