AskaEth
e8eccd2bb2
- chat_sessions 增 read_at（毫秒，与 chat_messages.created_at 同单位）；迁移 0010 并把既有会话回填为各自最新消息时间（升级当天不误报）
- ListSessions 单查询派生 unreadCount（无 N+1，复用 idx_chat_msgs_session_created 覆盖索引）
- MarkSessionRead 只前进 MAX(read_at, ?)；未知会话 ErrSessionNotFound；游标方法挂在网关消费侧窄接口（沿用 GetMessage/messageLookup 先例，避免污染 runtime 接口与其 fake）
- POST /api/v1/sessions/{id}/read：writeAuth + 400/404/501，成功广播 session_read、失败路径不广播
- Docs/api-client v0.37（§1 unreadCount / 新行 8a / §3.9 session_read / §4.2 用法）
- 含迁移回填测试、store/handler 测试与变异验证；顺带把 migrate_pinned_test 的「回退一步=0009」硬编码改为对迁移数量免疫
35 lines
1.7 KiB
SQL
35 lines
1.7 KiB
SQL
-- 0010_chat_session_read_at: per-session read cursor backing the session-list
|
|
-- unread badge (DevDocs/design/16 §2.2/§4.2, Android 协同批次 D4/B3). There is a
|
|
-- single owner account and no per-device identity at this layer, so the cursor is
|
|
-- session-scoped: reading on any one end marks the session read for every end of
|
|
-- the account (one cursor, no per-user/per-device cursor table).
|
|
--
|
|
-- read_at is a Unix MILLISECOND timestamp in the same unit as
|
|
-- chat_messages.created_at (both are INTEGER ms columns; the store assigns
|
|
-- time.Now().UnixMilli()), so the unread count is a plain
|
|
-- `created_at > read_at` comparison. 0 means "nothing read yet".
|
|
--
|
|
-- Backfill on upgrade: every existing session's cursor is set to its newest
|
|
-- message timestamp, so an upgrade does not suddenly light up the whole session
|
|
-- list as unread (a session with no messages — and a session whose messages do not
|
|
-- exist — stays at 0, which is also 0 unread).
|
|
--
|
|
-- The 0003 chat_sessions table already exists at this point, so ALTER ADD COLUMN is
|
|
-- the correct, non-destructive step. The canonical schema (including this column) is
|
|
-- also re-issued idempotently by internal/runtime/history.NewStore so a fresh
|
|
-- in-memory store used by tests stays self-contained; the migration is the
|
|
-- authoritative path for an existing database.
|
|
--
|
|
-- No new index is needed: the unread count filters
|
|
-- `session_id = ? AND created_at > ?`, which the existing
|
|
-- idx_chat_msgs_session_created (session_id, created_at DESC) already covers as an
|
|
-- equality + range scan.
|
|
|
|
ALTER TABLE chat_sessions ADD COLUMN read_at INTEGER NOT NULL DEFAULT 0;
|
|
|
|
UPDATE chat_sessions
|
|
SET read_at = COALESCE(
|
|
(SELECT MAX(m.created_at) FROM chat_messages m WHERE m.session_id = chat_sessions.id),
|
|
0
|
|
);
|