> For AI agents: the complete documentation index is available at /llms.txt, the full documentation bundle is available at /llms-full.txt. # 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](/product-design/v3/tasks-feature/task-domain-lifecycle.md)，实施与环境状态见 [Task V3 Rollout 状态快照](/product-design/v3/tasks-feature/task-v3-rollout-status.md)；[Task V2+ audit](/product-design/v2/tasks-feature/task-v2-plus-engineering-audit.md) 只保留 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-tracker` | Lead 追踪 | | `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 / 卡片)。 | # | Metric | Type | Endpoint | Source table.column | Aggregation 公式 | 消费方 | | -------------------------- | --------------------------- | ------------------------------------------ | ----------------------------------- | --------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------- | | **contacts 生命周期** | | | | | | | | 1 | `total` | integer | overview | `contacts.lifecycle_stage` | `COUNT(*)`,全量(无时间窗) | Performance · 概览 | | 2 | `leads` | integer | overview | `contacts.lifecycle_stage` | `COUNT(*) FILTER (WHERE lifecycle_stage = 'lead')` | Performance · 概览 | | 3 | `members` | integer | overview | `contacts.lifecycle_stage` | `COUNT(*) FILTER (WHERE lifecycle_stage = 'member')` | Performance · 概览 | | 4 | `churned` | integer | overview | `contacts.lifecycle_stage` | `COUNT(*) FILTER (WHERE lifecycle_stage = 'churned')` | Performance · 概览 | | **calls 量(近 30 天)** | | | | | | | | 5 | `total` | integer | overview | `calls.start_time` | `COUNT(*)` (近 30 天) | Performance · Call Volume | | 6 | `inbound` | integer | overview | `calls.direction` | `COUNT(*) FILTER (WHERE direction = 'Inbound')` | Performance · Call Volume | | 7 | `outbound` | integer | overview | `calls.direction` | `COUNT(*) FILTER (WHERE direction = 'Outbound')` | Performance · Call Volume | | 8 | `missed` | integer | overview | `calls.result` | `COUNT(*) FILTER (WHERE result IN ('Missed','No Answer','Voicemail'))` | Performance · Call Volume | | 9 | `avgDuration` | integer(秒) | overview | `calls.duration` | `ROUND(AVG(duration))` | Performance · Call Volume | | 10 | `todayTotal` | integer | overview | `calls.start_time` | `COUNT(*) FILTER (WHERE start_time::date = CURRENT_DATE)` | Performance · 概览 | | 11 | `yesterdayTotal` | integer | overview | `calls.start_time` | `COUNT(*) FILTER (WHERE start_time::date = CURRENT_DATE - 1)` | Performance · 概览 | | **messages(近 30 天)** | | | | | | | | 12 | `total` | integer | overview | `messages.type` | `COUNT(*) FILTER (WHERE type = 'SMS')` | Performance · 概览 | | 13 | `unread` | integer | overview | `messages.read_status` | `COUNT(*) FILTER (WHERE read_status = 'Unread')` | Performance · 概览 | | 14 | `todayTotal` | integer | overview | `messages.creation_time` | `COUNT(*) FILTER (WHERE creation_time::date = CURRENT_DATE)` | Performance · 概览 | | **leadFunnel(本周 vs 上周)** | | | | | | | | 15 | `received` | integer | overview | `contacts.lifecycle_stage` + `contacts.created_at` | lead 且近 14 天创建,按本周 / 上周 7 天分桶 | Performance · Lead Funnel | | 16 | `contacted` | integer | overview | `contacts` + `calls` JOIN | 存在 `contact.created_at` 之后的 outbound `calls` 即算 contacted | Performance · Lead Funnel | | 17 | `booked` | integer | overview | `contacts.lead_status` | `COUNT(*) FILTER (WHERE lead_status = 'booked')` | Performance · Lead Funnel | | 18 | `speedToLead.medianMinutes` | numeric | overview | `contacts.created_at` + first outbound `calls.start_time` | `PERCENTILE_CONT(0.5)` 分钟差(近 30 天样本) | Performance · Lead Funnel | | 19 | `speedToLead.sampleSize` | integer | overview | 同上 | 中位数样本数 | Performance · Lead Funnel | | **pipeline 漏斗分布** | | | | | | | | 20 | `pipeline[]` | jsonb `[{status, count}]` | overview | `contacts.lead_status` | `GROUP BY lead_status WHERE lifecycle_stage='lead' AND lead_status IS NOT NULL` | Performance · Pipeline | | **dailyActivity(近 7 天趋势)** | | | | | | | | 21 | `dailyActivity[]` | jsonb `[{day, calls, messages, newLeads}]` | overview | `calls.start_time` + `messages.creation_time` + `contacts.created_at` | 按天分组 COUNT | Performance · Trend | | **summary KPI(当期 vs 上期)** | | | | | | | | 22 | `totalCalls` | integer | summary | `calls.start_time` | `COUNT(*)`(当期) | Performance · KPI | | 23 | `outbound` | integer | summary | `calls.direction` | `COUNT(*) FILTER (WHERE direction = 'Outbound')` | Performance · KPI | | 24 | `introsBooked` | integer | summary | `calls.primary_outcome_result` + `calls.primary_category` | `COUNT(*) FILTER (WHERE primary_outcome_result='success' AND primary_category='revenue_impacting')` ⚠️ 名字叫 introsBooked,实际口径是"成交结果为 success 的 revenue\_impacting 通话",**不**按 `primary_subcategory='intro_booking'` 筛 — 与 multi-store 的 `introBooking` 口径不同 | Performance · KPI | | 25 | `cancelSaves` | integer | summary | `calls.primary_outcome_result` | `COUNT(*) FILTER (WHERE primary_outcome_result = 'retained')` | Performance · KPI | | 26 | `tasksClosed` | integer | summary | `tasks.status` + `tasks.closed_at` | `COUNT(*) FILTER (WHERE status='closed' AND closed_at >= 当期起点)` | Performance · KPI | | 27 | `delta` | jsonb `{metric: pct}` | summary | 上述 26 指标 | `(当期 - 上期) / 上期 × 100`;上期 0 且当期 > 0 → 100;都为 0 → null | Performance · KPI | | **multi-store(跨门店)** | | | | | | | | 28 | `totalCalls` | integer | multi-store | `calls.call_state` | `COUNT(*) FILTER (WHERE call_state IS DISTINCT FROM 'voicemail')` ⚠️ **排除 voicemail** | My Stores | | 29 | `outboundCalls` | integer | multi-store | `calls.direction` | `COUNT(*) FILTER (WHERE direction='Outbound')` | My Stores | | 30 | `introBooking` | integer | multi-store | `calls.primary_subcategory` | `COUNT(*) FILTER (WHERE primary_subcategory = 'intro_booking')`(不限 outcome,与 summary 口径不同) | My Stores | | 31 | `membershipsSold` | integer | multi-store | `calls.primary_category` + `primary_subcategory` + `primary_outcome_result` | `FILTER (WHERE primary_category='revenue_impacting' AND primary_subcategory IN ('membership_purchase_related','reactivation_purchase') AND primary_outcome_result='success')` | My Stores | | 32 | `cancellationCalls` | integer | multi-store | `calls.primary_subcategory` | `FILTER (WHERE primary_subcategory = 'membership_cancel')` | My Stores | | 33 | `cancelSaved` | integer | multi-store | `calls.primary_subcategory` + `primary_outcome_result` | `FILTER (WHERE primary_subcategory='membership_cancel' AND primary_outcome_result='retained')` | My Stores | | 34 | `tasksCreated` | integer | multi-store | `tasks.created_at` | 当期 `created_at` 落在区间内 `COUNT(*)` | My Stores | | 35 | `tasksClosed` | integer | multi-store | `tasks.closed_at` | 当期 `closed_at` 落在区间内 `COUNT(*)` | My Stores | | 36 | `tasksOverdue` | integer | multi-store | `tasks.status` + `tasks.due_at` | `FILTER (WHERE status='open' AND due_at < NOW())` 实时算,**不限当期区间** | My Stores | | 37 | `trend` | text | multi-store | `calls`(当期 vs 上期 totalCalls) | `up` / `down` / `flat` | My Stores | | 38 | `delta` | numeric | multi-store | 同上 | `(当期 - 上期) / 上期 × 100` | My Stores | | 39 | `totals` | jsonb | multi-store | 上述 9 个 store-level 指标 | 求和 + `storeCount` | My Stores · 汇总 | | **staff endpoint** | | | | | | | | 40 | `calls` | integer | staff | `calls.staff_name` | `COUNT(*) per staff` ⚠️ SQL 过滤 `call_state IN ('human_conversation','voicemail') AND staff_name IS NOT NULL` | Team Tab | | 41 | `inbound` / `outbound` | integer | staff | `calls.direction` | `FILTER (WHERE direction=...)` per staff | Team Tab | | 42 | `introCalls` | integer | staff | `calls.primary_subcategory` | `FILTER (WHERE primary_subcategory='intro_booking')` per staff | Team Tab | | 43 | `membershipSales` | integer | staff | `calls.primary_category` + `primary_subcategory` + `primary_outcome_result` | 同 metric #31 但 per staff | Team Tab | | 44 | `cancellationSaves` | integer | staff | `calls.primary_subcategory` + `primary_outcome_result` | 同 metric #33 但 per staff | Team Tab | | 45 | `taskCompletionRate` | numeric | staff | `tasks.closed_by_staff_name` + `tasks.status` | `closed / total` per staff；当前 SQL 只看 `closed_by_staff_name IS NOT NULL` 的 task，见 [§3](#3-task-outcome--workload-口径注意) | Team Tab | | 46 | `coachingFlagRate` | numeric | staff | `calls.practical_coaching_*` + `calls.duration` | `coaching_worthy / eligible_calls`(eligible = duration > 60s + 有 coaching 内容) | Team Tab | | 47 | `wowDirection` | text | staff | `calls`(当期 vs 上期) | `up` / `down` / `flat` | Team Tab | | **leadFunnel 牵引** | | | | | | | | 48 | Lead Follow-up 数 | integer | overview | overview\.leadFunnel | 同 metric #15-19 | Performance · 快捷牵引 | | 49 | Task Queue 数(open tasks) | integer | tasks list endpoint(非 dashboard-\*) | `tasks.status` | `WHERE status='open'` 计数 — `/v3/dashboard/overview` 不返回 task 计数,前端从 tasks list endpoint 拉 | Performance · 快捷牵引 | | 50 | Manager Review 数 | integer | coaching-highlights | `calls.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 objectives | `tasksClosed` 只按 `status='closed'` + `closed_at` 时间窗统计 | 这是“完成了多少事项”，不等于业务赢了几单 | | Business wins | 没有统一单列；部分 metric 按 Task V2 `close_result` 拆分 | 候选方向是由 `taskKind + resultCode + policyVersion + verification` 做 versioned projection，不维护 global positive result list | | Attempt workload | Dashboard 主要看 closed / overdue Task | 从 canonical Activity ledger 按 `channel / action / actorType / creditedStaffId` 投影，不新增第二份 progress source of truth | | Staff task completion | `dashboard-staff.ts` 当前按 `closed_by_staff_name` 归因，且只纳入有 staff name 的 task | 会漏掉 `executorType='ai_agent'` / `system` 或 create-closed 等非人工关闭路径 | | AI vs human attribution | `tasks.executorType` 已在 schema 中存在 | Dashboard 还没有系统性拆出 human / AI agent / system 对比 | 详见 [tasks-schema.md](/product-design/v2/tasks-feature/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 Classify | `calls.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 Coaching | `calls.practical_coaching_*` / `review_category` | metric #46 coachingFlagRate, #50 Manager Review | | 05 Contact Profile | `contacts.lifecycle_stage` / `lead_status` | metric #1-4 contacts, #15-20 leadFunnel + pipeline | | 06 Task Decision | Task V2 snapshot：`tasks.status` / `closeResult` / `executorType` | metric #26, #35-36 tasksClosed/Overdue, #45 taskCompletionRate；V3 Target 改读 Task/Activity/Outcome projection | | 07 Task Playbook | read-only | n/a(不写库,不驱动 metric) | 详细 prompt → 上游表字段对照见 [calls-schema.md §4](/product-design/v2/calls-feature/calls-schema.md)；V3 Target Task mutation contract 见 [Task V3 Product / Domain Contract §10.2](/product-design/v3/tasks-feature/task-domain-lifecycle.md#102-commands)。 *** ## 5. Appendix A — Re-verification commands > 2026-06-15 verified。文档 stale 时重跑下方命令并比对。 ```bash # 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 - 同 feature: [dashboard-calculations.md](/product-design/v2/dashboard-feature/dashboard-calculations.md)(KPI 卡片产品定义)· [dashboard-display.md](/product-design/v2/dashboard-feature/dashboard-display.md)(UI 展示) - 上游 schema: [calls-schema.md](/product-design/v2/calls-feature/calls-schema.md) · [tasks-schema.md](/product-design/v2/tasks-feature/tasks-schema.md) · [contacts-schema.md](/product-design/v2/contacts-feature/contacts-schema.md) · [coaching-schema.md](/product-design/v2/coaching-feature/coaching-schema.md) - Historical task pipeline design: [task-pipeline-deliverable-codex.md](/product-design/v2/tasks-feature/design/task-pipeline-deliverable-codex.md)(§11 Metrics Impact) - Unified pipeline: [../unified-pipeline/unified-pipeline-final.md](/product-design/v2/unified-pipeline/unified-pipeline-final.md) - Task V3 selected Target: [Task V3 Product / Domain Contract](/product-design/v3/tasks-feature/task-domain-lifecycle.md) - Task V3 rollout boundary: [Task V3 Rollout 状态快照](/product-design/v3/tasks-feature/task-v3-rollout-status.md) - Historical Task V2+ audit: [工程审计与设计建议](/product-design/v2/tasks-feature/task-v2-plus-engineering-audit.md) - 2026-06 snapshot sources: - `studio-website-monorepo/apps/api/src/routes/v3/dashboard-*.ts`(7 个 endpoint) - `callytics-common/src/db/schema/{calls,tasks,contacts,messages,leads}.ts`