跳转至

Contacts API — Read Patterns

/v2/contacts/* 端点的读取模式。和 write-matrix 互为读写对照。

1. 一句话总览

/v2/contacts/profile 读 contacts 表的客户档案(姓名、生命周期、AI 分析摘要)。 /v2/contacts/communications 读 calls + messages 表的通话/SMS 历史(UNION ALL,游标分页)。

两个端点共同支撑前端 ContactDetailSheet 共享组件(目前 Tasks 页用,未来 Contacts 页也用,组件在 apps/web/src/components/contact-detail/)。

2. 端点清单

端点 文件 数据源 隔离键 备注
GET /v2/contacts/profile?contactPhone=&storeId= routes/contacts/profile.ts Neon contacts 表 account_id(从 storeId 解析 providerAccountId) 客户级数据,跨店共享(同 RC 账号下的同一客户看到同一份 profile)
GET /v2/contacts/communications?contactPhone=&storeId=&before=&limit= routes/contacts/communications.ts Neon calls + messages 表 UNION ALL store_phone IN (...) hybrid(等 backfill 完切纯 store_id) 店级数据,游标分页

3. Profile 端点细节

3.1 SQL pattern

SELECT phone, first_name, last_name, lifecycle_stage, customer_summary,
       do_not_contact, created_at AS acquired_at, last_activity_at,
       lead_status, purchase_intent, goals -> 0 ->> 'goal' AS goal,
       action_needed, action_needed_reason, suggested_actions
FROM contacts
WHERE phone = $1 AND account_id = $2
ORDER BY updated_at DESC NULLS LAST
LIMIT 1

3.2 为什么用 account_id 不用 store_id?

客户级属性(姓名、do-not-contact 旗标、生命周期阶段、AI 画像)跨店共享。Allen 同时是 Devon 和 White Plains 客户时,后端 contacts 表确实是 2 条 row(由于 contacts PK = (phone, store_id),migration 0014),但前端展示的 Profile 应该是同一份客户档案 — 取最近更新的那条(LATERAL ORDER BY updated_at DESC NULLS LAST LIMIT 1)。

3.3 LATERAL pattern 的复用

跟 routes/tasks/list.ts 的 LATERAL 子查询是同一个 pattern,目的是处理 legacy 数据中同 phone+account 多 contact rows 的情况(由于早期 franchise_id 不一致引入的 dup,见 store-level-isolation.md Critical Bug)。

4. Communications 端点细节

4.1 SQL pattern(简化)

(SELECT 'call' AS comm_type, telephony_session_id AS id,
        start_time AS time, direction, duration, result, ...
 FROM calls cl
 WHERE (cl.from_phone_number = $1 OR cl.to_phone_number = $1)
   AND (cl.from_phone_number IN (:storePhones) OR cl.to_phone_number IN (:storePhones))
   AND start_time < :before  -- cursor)
UNION ALL
(SELECT 'sms' AS comm_type, m.id::text AS id,
        creation_time AS time, direction, ...
 FROM messages m
 WHERE m.type = 'SMS'
   AND (m.from_phone_number = $1 OR m.to_phone_number = $1)
   AND (m.from_phone_number IN (:storePhones) OR m.to_phone_number IN (:storePhones))
   AND creation_time < :before)
ORDER BY time DESC
LIMIT :limit + 1  -- +1 to detect hasMore

4.2 游标分页

  • Cursor = before(ISO timestamp),空时取最新
  • 取 limit + 1 行,如果实际 >limit 则 hasMore = true,返回 limit 行 + 下一页 cursor
  • 前端 useContactCommunications composable 配合 IntersectionObserver 触发下拉加载

4.3 hybrid store_phone IN (...) 的临时性

跟 routes/tasks/communications.ts 是同一个 pattern。原因:calls 和 messages 表的 store_id 列虽然写入端已经全部写(infra #642/#647),但 schema 还是 nullable,老数据 backfill 中。等 backfill 全部完成 + 把 store_id 改成 NOT NULL 后,可以切到纯 WHERE store_id = ? 查询,跟 routes/tasks/list.ts:78 一样。

5. 隔离 + 授权链

前端 ContactDetailSheet
  │ contactPhone + storeId
  ▼
GET /v2/contacts/profile
  │ getAuthorizedStore(userId, storeId)  ← UserStore GSI 校验,403 if no access
  ▼ providerAccountId
WHERE phone = $1 AND account_id = $2  ← 跨店看同一客户档案
  │
  ▼
Neon contacts 表(LATERAL pick latest)

GET /v2/contacts/communications
  │ getAuthorizedStore(userId, storeId)  ← 同上
  │ getPhoneNumbersForStore(storeId)   ← 当前店的所有电话
  ▼
calls + messages UNION ALL
WHERE phone = $1 AND store_phone IN (:storePhones)  ← 当前店的通话/SMS 才显示

6. 前端 ContactDetailSheet 共享组件

文件 用途
apps/web/src/components/contact-detail/ContactDetailSheet.vue 抽屉容器(Tasks 页 + 未来 Contacts 页共用)
apps/web/src/components/contact-detail/ContactProfilePanel.vue 客户档案 panel(姓名、AI 分析、目标、do-not-contact)
apps/web/src/components/contact-detail/CommunicationLog.vue 通话/SMS 历史(IntersectionObserver 无限滚动)
apps/web/src/components/contact-detail/CommEntryItem.vue 单条通话/SMS 卡片
apps/web/src/components/contact-detail/CommEntryAudio.vue 录音播放器
apps/web/src/components/contact-detail/LeadPipeline.vue Lead 阶段进度条
apps/web/src/components/contact-detail/index.ts 桶式导出
apps/web/src/composables/queries/useContactProfile.ts TanStack Query hook → /v2/contacts/profile
apps/web/src/composables/queries/useContactCommunications.ts TanStack Query hook → /v2/contacts/communications
apps/web/src/api/contacts-client.ts typed API client

设计意图: Tasks 页和未来 Contacts 页都需要"看一个具体客户的档案 + 最近通话历史",抽出共享组件 + 共享 API endpoint,避免两套实现。Tasks 页通过 task-to-contact.ts adapter 把 task → contact 入参映射。

7. 相关文档

实施溯源