> For AI agents: the complete documentation index is available at /llms.txt, the full documentation bundle is available at /llms-full.txt.

# Lead Tracker Schema

> **Source of truth**: `callytics-common/src/db/schema/leads.ts` (verified 2026-06-15)
> **表名**: `leads` ｜ **主键**: `id` varchar(512) — composite dedup key
> **Lead 页面主体提醒**: 当前 `leads-v3` 页面列表和 pipeline status 主要读 `contacts.lifecycle_stage='lead'`；`leads` 表是 email intake / dedup / cadence 的原始来源表。
> **Lead 漏斗状态**: `leadStatus` 不在 `leads` 表，存在 `contacts.leadStatus`。见 [lead-funnel-status.md](/product-design/v2/lead-tracker-feature/lead-funnel-status.md)。

***

## 1. 表说明

`leads` 表存从 email 来源摄取的 lead：Google Apps Script HTTP handler 或 Lark Suite IMAP Poller 收到邮件后解析、去重、写入。原始存储曾是 DynamoDB `LeadTracking-v2`，现在双写到 Neon，供 studio-api 和分析 pipeline 查询。

| 事实            | 说明                                                                                |
| ------------- | --------------------------------------------------------------------------------- |
| 写入者           | 只有 code：lead-tracking 管道                                                          |
| AI 写入         | 无 AI 写入字段                                                                         |
| 主要用途          | intake audit、dedup、malformed/duplicated 统计、speed-to-lead cadence                  |
| 与当前 Lead 页面关系 | Received list 不是直接按 `leads` 行展示，而是展示 `contacts` 中的 lead contact                   |
| ID 格式         | `{customerEmail}#{customerPhone}#{dateOnly}`；正文解析失败时兜底 `{leadEmail}#{receivedAt}` |

***

## 2. Schema 全字段

| #  | Field                   | Type         | Nullable | Values                        | Writer | 用途                                             |
| -- | ----------------------- | ------------ | -------- | ----------------------------- | ------ | ---------------------------------------------- |
| 1  | `id`                    | varchar(512) | NOT NULL | PK                            | code   | Composite dedup key，防 Lambda retry / IMAP 重复轮询 |
| 2  | `leadEmail`             | varchar(255) | NOT NULL | —                             | code   | Lead 收件邮箱，也是 store 解析输入                        |
| 3  | `leadType`              | varchar(50)  | NOT NULL | 无 DB enum                     | code   | Lead 类型，合法值由 lead-tracking 代码约束                |
| 4  | `firstName`             | varchar(255) | NULL     | —                             | code   | 名                                              |
| 5  | `lastName`              | varchar(255) | NULL     | —                             | code   | 姓                                              |
| 6  | `phone`                 | text         | NULL     | —                             | code   | 归一化电话号码；部分 lead 无电话                            |
| 7  | `bookedDate`            | varchar(50)  | NULL     | —                             | code   | 预约日期                                           |
| 8  | `bookedTime`            | varchar(50)  | NULL     | —                             | code   | 预约时间                                           |
| 9  | `emailSubject`          | text         | NULL     | —                             | code   | 邮件主题                                           |
| 10 | `emailFrom`             | varchar(255) | NULL     | —                             | code   | 发件人                                            |
| 11 | `emailRecipient`        | varchar(512) | NULL     | —                             | code   | 收件人                                            |
| 12 | `extractedTrackingId`   | varchar(255) | NULL     | —                             | code   | 从 `lead-tracking+{ID}@...` 提取的 tracking id     |
| 13 | `isForwarded`           | boolean      | NULL     | default `false`               | code   | 是否为转发邮件                                        |
| 14 | `forwardedOriginalFrom` | varchar(255) | NULL     | —                             | code   | 原始发件人                                          |
| 15 | `forwardedOriginalTo`   | text         | NULL     | —                             | code   | 原始收件人                                          |
| 16 | `forwardedOriginalDate` | text         | NULL     | —                             | code   | 原始日期                                           |
| 17 | `rawBody`               | text         | NULL     | —                             | code   | 完整邮件正文，用于调试和重新解析                               |
| 18 | `processedBy`           | varchar(50)  | NULL     | `http-handler`, `imap-poller` | code   | 摄取路径                                           |
| 19 | `franchiseId`           | text         | NULL     | —                             | code   | 品牌审计字段                                         |
| 20 | `accountId`             | text         | NULL     | —                             | code   | account / site 审计字段                            |
| 21 | `storeId`               | text         | NULL     | —                             | code   | Store UUID；由 `leadEmail` 映射 StoresV2 写入        |
| 22 | `tenantId`              | uuid         | NULL     | —                             | code   | control-plane tenant UUID，multi-tenant V1 铺路   |
| 23 | `receivedAt`            | timestamptz  | NOT NULL | —                             | code   | 邮件接收时间                                         |
| 24 | `syncedAt`              | timestamptz  | NOT NULL | default `now()`               | code   | 首次同步到 Neon 的时间                                 |

`isForwarded=false` 时，`forwardedOriginalFrom` / `forwardedOriginalTo` / `forwardedOriginalDate` 通常为 NULL。

***

## 3. 索引

| Index                          | Column(s)                                 | 用途                        |
| ------------------------------ | ----------------------------------------- | ------------------------- |
| `idx_leads_phone`              | `phone`                                   | 按客户电话查 lead               |
| `idx_leads_email_time`         | `lead_email`, `received_at`               | 按 lead 邮箱时序查询             |
| `idx_leads_tracking_id`        | `extracted_tracking_id`                   | 按 tracking id 反查来源        |
| `idx_leads_received_at`        | `received_at`                             | 全局时序扫描                    |
| `idx_leads_franchise_site`     | `franchise_id`, `account_id`              | 历史 account-level 查询       |
| `idx_leads_store_id`           | `store_id` where not NULL                 | store-level filter        |
| `idx_leads_tenant_received_at` | `tenant_id`, `received_at` where not NULL | tenant-level reporting 预留 |

***

## 4. 当前 API 读法

| Endpoint                 | 主数据源                 | 用途                                                               |
| ------------------------ | -------------------- | ---------------------------------------------------------------- |
| `GET /v3/leads`          | `contacts` + `calls` | Received tab list：展示 `contacts.lifecycle_stage='lead'` 的 contact |
| `GET /v3/leads/pipeline` | `contacts` + `leads` | statusCounts 来自 contacts；incoming/malformed/duplicated 来自 leads  |
| `GET /v3/leads/funnel`   | `contacts` + `calls` | 7-day funnel：received / contacted / booked                       |
| `GET /v3/leads/cadence`  | `leads` + `calls`    | speed-to-lead bucket：从 `leads.received_at` 到首个 outbound call     |

重点边界：`leads` 表回答“系统收到了什么 lead email”；`contacts` 表回答“现在这个人作为 lead 处在什么状态、需要怎么跟进”。

***

## 5. Prompt Output ↔ Schema

Lead intake 没有 AI prompt 写 `leads` 表。`leads` 只是后续 AI / Task pipeline 的 read-only evidence。

| Prompt             | 与 `leads` 表关系                               |
| ------------------ | ------------------------------------------- |
| 01 Triage          | 不读不写                                        |
| 02 Classify        | 不读不写                                        |
| 03 Verify          | 不读不写                                        |
| 04 Coaching        | 不读不写                                        |
| 05 Contact Profile | 可作为 recent activity evidence 输入，不写 `leads`  |
| 06 Task Decision   | `sourceType='lead'` task 可引用 `sourceLeadId` |
| 07 Task Playbook   | 不读不写                                        |

***

## 6. Re-verification Commands

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

```bash
# leads 表所有 column + index
grep -nE "varchar\\(|text\\(|timestamp\\(|boolean\\(|uuid\\(|index\\(" \
  ../../../../../callytics-common/src/db/schema/leads.ts

# 当前 Lead API 数据源
grep -nE "FROM contacts|FROM leads|JOIN calls|l\\.received_at|c\\.lifecycle_stage = 'lead'" \
  ../../../../../studio-website-monorepo/apps/api/src/routes/v3/leads.ts \
  ../../../../../studio-website-monorepo/apps/api/src/routes/v3/lead-funnel.ts \
  ../../../../../studio-website-monorepo/apps/api/src/routes/v3/lead-cadence.ts
```

***

## 7. Cross-References

- Lead Tracker 页面展示: [lead-tracker-display.md](/product-design/v2/lead-tracker-feature/lead-tracker-display.md)
- Lead Tracker 计算口径: [lead-tracker-calculations.md](/product-design/v2/lead-tracker-feature/lead-tracker-calculations.md)
- Lead funnel status: [lead-funnel-status.md](/product-design/v2/lead-tracker-feature/lead-funnel-status.md)
- Contacts schema: [../contacts-feature/contacts-schema.md](/product-design/v2/contacts-feature/contacts-schema.md)
- Tasks schema: [../tasks-feature/tasks-schema.md](/product-design/v2/tasks-feature/tasks-schema.md)
- Store isolation: [../../../system-design/store-level-isolation.md](/system-design/store-level-isolation.md)