#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. 端点清单

#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 共享组件

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

#7. 相关文档

• 写入对照: write-matrix/contact-analysis-writes.md(contacts 表是怎么被写的)
• 隔离设计: store-level-isolation.md(为什么 contacts PK = (phone, store_id))
• 整体功能地图: feature-map.md

::: details 实施溯源

• Issue: studio-website-monorepo#241 — /v2/contacts/* 全套 API
• PR: #254 ContactDetailSheet + Contacts API · #255 游标分页 · #256 IntersectionObserver bug fix :::