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。

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 时重跑下方命令并比对。

# 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
Lead Tracker 计算口径: lead-tracker-calculations.md
Lead funnel status: lead-funnel-status.md
Contacts schema: ../contacts-feature/contacts-schema.md
Tasks schema: ../tasks-feature/tasks-schema.md
Store isolation: ../../../system-design/store-level-isolation.md

#Lead Tracker Schema

#1. 表说明

#2. Schema 全字段

#3. 索引

#4. 当前 API 读法

#5. Prompt Output ↔ Schema

#6. Re-verification Commands

#7. Cross-References