- commit
- cf7aa3a
- parent
- 5580526
- author
- codex@macbookpro
- date
- 2026-03-30 18:19:27 +0800 CST
fix: dedupe null remote conversation links
9 files changed,
+612,
-11
+55,
-0
1@@ -6878,6 +6878,61 @@ test("observeRenewalConversation ignores inactive remote links and creates a new
2 }
3 });
4
5+test("observeRenewalConversation reuses the same link when remote conversation id is missing", async () => {
6+ const rootDir = mkdtempSync(join(tmpdir(), "baa-conductor-renewal-observe-null-remote-"));
7+ const stateDir = join(rootDir, "state");
8+ const store = new ArtifactStore({
9+ artifactDir: join(stateDir, ARTIFACTS_DIRNAME),
10+ databasePath: join(stateDir, ARTIFACT_DB_FILENAME)
11+ });
12+ const firstObservedAt = Date.UTC(2026, 2, 30, 12, 3, 0);
13+
14+ try {
15+ const firstObservation = await observeRenewalConversation({
16+ assistantMessageId: "msg-renewal-null-remote-1",
17+ clientId: "firefox-gemini",
18+ observedAt: firstObservedAt,
19+ pageTitle: "Gemini Thread",
20+ pageUrl: "https://gemini.google.com/app",
21+ platform: "gemini",
22+ store
23+ });
24+ const secondObservation = await observeRenewalConversation({
25+ assistantMessageId: "msg-renewal-null-remote-2",
26+ clientId: "firefox-gemini",
27+ observedAt: firstObservedAt + 60_000,
28+ pageTitle: "Gemini Thread Updated",
29+ pageUrl: "https://gemini.google.com/app",
30+ platform: "gemini",
31+ store
32+ });
33+
34+ assert.equal(firstObservation.created, true);
35+ assert.equal(firstObservation.resolvedBy, "new");
36+ assert.equal(secondObservation.created, false);
37+ assert.equal(secondObservation.resolvedBy, "active_link");
38+ assert.equal(
39+ secondObservation.conversation.localConversationId,
40+ firstObservation.conversation.localConversationId
41+ );
42+ assert.equal(secondObservation.link.linkId, firstObservation.link.linkId);
43+ assert.equal(secondObservation.link.remoteConversationId, null);
44+ assert.equal(secondObservation.link.routePath, "/app");
45+ assert.equal(secondObservation.link.pageTitle, "Gemini Thread Updated");
46+ assert.deepEqual(await store.listConversationLinks({ platform: "gemini" }), [secondObservation.link]);
47+
48+ const conversation = await store.getLocalConversation(firstObservation.conversation.localConversationId);
49+ assert.ok(conversation);
50+ assert.equal(conversation.lastMessageId, "msg-renewal-null-remote-2");
51+ } finally {
52+ store.close();
53+ rmSync(rootDir, {
54+ force: true,
55+ recursive: true
56+ });
57+ }
58+});
59+
60 test("observeRenewalConversation gives targetId absolute priority over weaker page signals", async () => {
61 const rootDir = mkdtempSync(join(tmpdir(), "baa-conductor-renewal-target-priority-"));
62 const stateDir = join(rootDir, "state");
+1,
-0
1@@ -50,6 +50,7 @@ bugs/
2 | BUG-026 | FIXED | repo 根路径现在会正确 fallback 到默认 `log.html` |
3 | BUG-029 | FIXED | 已停用 conversation link 不会再被远端对话查询命中 |
4 | BUG-030 | FIXED | `targetId` 匹配现在绝对优先于弱信号叠加 |
5+| BUG-035 | FIXED | `remote_conversation_id = NULL` 的 link 现在会按 route/page identity 收敛为唯一 canonical row |
6 | MISSING-001 | FIXED | 执行结果已经接到 AI 对话 delivery 主链 |
7 | MISSING-002 | FIXED | 插件侧 delivery plan 执行器已落地 |
8 | MISSING-003 | FIXED | Phase 1 已补齐 browser.claude target |
+45,
-0
1@@ -0,0 +1,45 @@
2+# FIX-BUG-035: null remote_conversation_id 仍保持 conversation link 唯一
3+
4+## 执行状态
5+
6+- 已完成（2026-03-30，代码 + 自动化验证已落地）
7+
8+## 关联 Bug
9+
10+BUG-035-conversation-links-null-unique-index.md
11+
12+## 实际修改文件
13+
14+- `packages/artifact-db/src/schema.ts`
15+- `packages/artifact-db/src/store.ts`
16+- `packages/artifact-db/src/index.test.js`
17+- `packages/d1-client/src/d1-setup.sql`
18+- `packages/d1-client/src/index.test.js`
19+- `apps/conductor-daemon/src/index.test.js`
20+
21+## 实际修改
22+
23+- 把 `conversation_links` 的唯一约束拆成两类：
24+ - `remote_conversation_id IS NOT NULL` 时继续按 `(platform, remote_conversation_id)` 唯一
25+ - `remote_conversation_id IS NULL` 时按 `(platform, client scope, route/page/target/client fallback)` 级联唯一
26+- 在 `ArtifactStore` 初始化时增加一次轻量迁移：
27+ - 读取历史 `remote_conversation_id = NULL` 的重复 link
28+ - 以最新记录为 canonical row
29+ - 合并缺失字段并删除同 identity 的旧重复行
30+ - 再创建新的部分唯一索引
31+- 更新 store 的 null-remote resolve/upsert 逻辑：
32+ - 优先按 `route_path`
33+ - 其次按 `page_url`
34+ - 再退到 `target_id`
35+ - 最后才是仅 `client_id` 的兜底
36+- 新增回归测试，覆盖：
37+ - null remote 的重复 route upsert 只保留一条 link
38+ - 旧 schema 下已写出的重复 null-remote link 能被启动迁移收敛为一条 canonical row
39+ - `observeRenewalConversation` 在缺失 `remote_conversation_id` 时重复 observe 会稳定命中同一条 link
40+
41+## 验收标准
42+
43+1. `remote_conversation_id = NULL` 时，同一平台/同一路由不会继续写出多条冲突 link
44+2. 缺失 remote conversation 的 repeated observe / upsert 会复用同一条 canonical link
45+3. 旧库中已存在的重复 null-remote link 不会阻塞新索引创建
46+4. `pnpm -C packages/artifact-db test`、`pnpm -C packages/d1-client test`、`pnpm -C apps/conductor-daemon test` 通过
+2,
-1
1@@ -3,7 +3,7 @@
2 本目录保留 `已关闭` 或 `已修复` 的 `BUG-*`、`FIX-BUG-*`、`MISSING-*` 和 `OPT-*` 文档。
3
4 - 归档时间：`2026-03-30`
5-- 最近新增归档：`BUG-018`、`BUG-019`、`BUG-020`、`BUG-021`、`BUG-022`、`BUG-023`、`BUG-024`、`BUG-025`、`BUG-026`、`BUG-029`、`BUG-030`、`MISSING-003`
6+- 最近新增归档：`BUG-018`、`BUG-019`、`BUG-020`、`BUG-021`、`BUG-022`、`BUG-023`、`BUG-024`、`BUG-025`、`BUG-026`、`BUG-029`、`BUG-030`、`BUG-035`、`MISSING-003`
7
8 最近归档的问题：
9
10@@ -18,4 +18,5 @@
11 - `BUG-026`：repo 根路径现在会正确 fallback 到默认 `log.html`
12 - `BUG-029`：已停用 conversation link 不会再被远端对话查询命中
13 - `BUG-030`：`targetId` 匹配现在绝对优先于弱信号叠加
14+- `BUG-035`：`remote_conversation_id = NULL` 的 conversation link 现在按 route/page identity 收敛为唯一 canonical row
15 - `MISSING-003`：Phase 1 已补齐 `browser.claude` target
+229,
-0
1@@ -1,12 +1,14 @@
2 import assert from "node:assert/strict";
3 import {
4 existsSync,
5+ mkdirSync,
6 mkdtempSync,
7 readFileSync,
8 rmSync
9 } from "node:fs";
10 import { tmpdir } from "node:os";
11 import { join } from "node:path";
12+import { DatabaseSync } from "node:sqlite";
13 import test from "node:test";
14
15 import {
16@@ -377,3 +379,230 @@ test("ArtifactStore findConversationLinkByRemoteConversation only returns active
17 });
18 }
19 });
20+
21+test("ArtifactStore reuses the same null-remote conversation link for repeated route upserts", async () => {
22+ const rootDir = mkdtempSync(join(tmpdir(), "artifact-db-null-remote-upsert-test-"));
23+ const stateDir = join(rootDir, "state");
24+ const databasePath = join(stateDir, ARTIFACT_DB_FILENAME);
25+ const artifactDir = join(stateDir, ARTIFACTS_DIRNAME);
26+ const store = new ArtifactStore({
27+ artifactDir,
28+ databasePath
29+ });
30+ const firstObservedAt = Date.UTC(2026, 2, 30, 18, 0, 0);
31+
32+ try {
33+ await store.upsertLocalConversation({
34+ localConversationId: "lc_null_remote_1",
35+ platform: "gemini"
36+ });
37+ await store.upsertLocalConversation({
38+ localConversationId: "lc_null_remote_2",
39+ platform: "gemini"
40+ });
41+
42+ const firstLink = await store.upsertConversationLink({
43+ clientId: "firefox-gemini",
44+ linkId: "link_null_remote_1",
45+ localConversationId: "lc_null_remote_1",
46+ observedAt: firstObservedAt,
47+ pageTitle: "Gemini",
48+ platform: "gemini",
49+ routePath: "/app",
50+ routePattern: "/app",
51+ targetKind: "browser.proxy_delivery"
52+ });
53+ const secondLink = await store.upsertConversationLink({
54+ clientId: "firefox-gemini",
55+ linkId: "link_null_remote_2",
56+ localConversationId: "lc_null_remote_2",
57+ observedAt: firstObservedAt + 60_000,
58+ pageTitle: "Gemini Updated",
59+ platform: "gemini",
60+ routePath: "/app",
61+ routePattern: "/app",
62+ targetKind: "browser.proxy_delivery"
63+ });
64+
65+ assert.equal(firstLink.linkId, "link_null_remote_1");
66+ assert.equal(secondLink.linkId, "link_null_remote_1");
67+ assert.equal(secondLink.localConversationId, "lc_null_remote_2");
68+ assert.equal(secondLink.pageTitle, "Gemini Updated");
69+ assert.equal(
70+ (await store.listConversationLinks({
71+ localConversationId: "lc_null_remote_1"
72+ })).length,
73+ 0
74+ );
75+ assert.deepEqual(await store.listConversationLinks({ platform: "gemini" }), [secondLink]);
76+ } finally {
77+ store.close();
78+ rmSync(rootDir, {
79+ force: true,
80+ recursive: true
81+ });
82+ }
83+});
84+
85+test("ArtifactStore migrates duplicate null-remote conversation links into one canonical route record", async () => {
86+ const rootDir = mkdtempSync(join(tmpdir(), "artifact-db-null-remote-migration-test-"));
87+ const stateDir = join(rootDir, "state");
88+ mkdirSync(stateDir, {
89+ recursive: true
90+ });
91+ const databasePath = join(stateDir, ARTIFACT_DB_FILENAME);
92+ const artifactDir = join(stateDir, ARTIFACTS_DIRNAME);
93+ const bootstrapDb = new DatabaseSync(databasePath);
94+ let bootstrapClosed = false;
95+
96+ try {
97+ bootstrapDb.exec(`
98+ CREATE TABLE IF NOT EXISTS local_conversations (
99+ local_conversation_id TEXT PRIMARY KEY,
100+ platform TEXT NOT NULL,
101+ automation_status TEXT NOT NULL DEFAULT 'manual',
102+ title TEXT,
103+ summary TEXT,
104+ last_message_id TEXT,
105+ last_message_at INTEGER,
106+ cooldown_until INTEGER,
107+ paused_at INTEGER,
108+ created_at INTEGER NOT NULL,
109+ updated_at INTEGER NOT NULL
110+ );
111+
112+ CREATE TABLE IF NOT EXISTS conversation_links (
113+ link_id TEXT PRIMARY KEY,
114+ local_conversation_id TEXT NOT NULL,
115+ platform TEXT NOT NULL,
116+ remote_conversation_id TEXT,
117+ client_id TEXT,
118+ page_url TEXT,
119+ page_title TEXT,
120+ route_path TEXT,
121+ route_pattern TEXT,
122+ route_params TEXT,
123+ target_kind TEXT,
124+ target_id TEXT,
125+ target_payload TEXT,
126+ is_active INTEGER NOT NULL DEFAULT 1,
127+ observed_at INTEGER NOT NULL,
128+ created_at INTEGER NOT NULL,
129+ updated_at INTEGER NOT NULL
130+ );
131+
132+ CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_remote
133+ ON conversation_links(platform, remote_conversation_id);
134+ `);
135+ bootstrapDb.exec(`
136+ INSERT INTO local_conversations (
137+ local_conversation_id,
138+ platform,
139+ automation_status,
140+ created_at,
141+ updated_at
142+ ) VALUES
143+ ('lc_migration_null_remote_1', 'gemini', 'manual', 1000, 1000),
144+ ('lc_migration_null_remote_2', 'gemini', 'manual', 2000, 2000);
145+
146+ INSERT INTO conversation_links (
147+ link_id,
148+ local_conversation_id,
149+ platform,
150+ remote_conversation_id,
151+ client_id,
152+ page_url,
153+ page_title,
154+ route_path,
155+ route_pattern,
156+ route_params,
157+ target_kind,
158+ target_id,
159+ target_payload,
160+ is_active,
161+ observed_at,
162+ created_at,
163+ updated_at
164+ ) VALUES
165+ (
166+ 'link_migration_null_remote_older',
167+ 'lc_migration_null_remote_1',
168+ 'gemini',
169+ NULL,
170+ 'firefox-gemini',
171+ 'https://gemini.google.com/app?first=1',
172+ 'Gemini Older',
173+ '/app',
174+ '/app',
175+ NULL,
176+ 'browser.proxy_delivery',
177+ 'tab:1',
178+ NULL,
179+ 1,
180+ 1000,
181+ 1000,
182+ 1000
183+ ),
184+ (
185+ 'link_migration_null_remote_newer',
186+ 'lc_migration_null_remote_2',
187+ 'gemini',
188+ NULL,
189+ 'firefox-gemini',
190+ 'https://gemini.google.com/app?second=1',
191+ 'Gemini Newer',
192+ '/app',
193+ '/app',
194+ NULL,
195+ 'browser.proxy_delivery',
196+ 'tab:2',
197+ NULL,
198+ 1,
199+ 2000,
200+ 2000,
201+ 2000
202+ );
203+ `);
204+ bootstrapDb.close();
205+ bootstrapClosed = true;
206+
207+ const store = new ArtifactStore({
208+ artifactDir,
209+ databasePath
210+ });
211+
212+ try {
213+ const links = await store.listConversationLinks({ platform: "gemini" });
214+
215+ assert.equal(links.length, 1);
216+ assert.equal(links[0].linkId, "link_migration_null_remote_newer");
217+ assert.equal(links[0].localConversationId, "lc_migration_null_remote_2");
218+ assert.equal(links[0].createdAt, 1000);
219+ assert.equal(links[0].routePath, "/app");
220+ assert.equal(links[0].pageTitle, "Gemini Newer");
221+
222+ const reused = await store.upsertConversationLink({
223+ clientId: "firefox-gemini",
224+ linkId: "link_migration_null_remote_reused",
225+ localConversationId: "lc_migration_null_remote_2",
226+ observedAt: 3000,
227+ platform: "gemini",
228+ routePath: "/app",
229+ routePattern: "/app"
230+ });
231+
232+ assert.equal(reused.linkId, "link_migration_null_remote_newer");
233+ assert.equal((await store.listConversationLinks({ platform: "gemini" })).length, 1);
234+ } finally {
235+ store.close();
236+ }
237+ } finally {
238+ if (bootstrapClosed === false) {
239+ bootstrapDb.close();
240+ }
241+ rmSync(rootDir, {
242+ force: true,
243+ recursive: true
244+ });
245+ }
246+});
+34,
-7
1@@ -1,3 +1,37 @@
2+export const CONVERSATION_LINK_INDEX_SQL = `
3+DROP INDEX IF EXISTS idx_conversation_links_remote;
4+
5+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_remote
6+ ON conversation_links(platform, remote_conversation_id)
7+ WHERE remote_conversation_id IS NOT NULL;
8+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_route
9+ ON conversation_links(platform, COALESCE(client_id, ''), route_path)
10+ WHERE remote_conversation_id IS NULL
11+ AND route_path IS NOT NULL;
12+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_page
13+ ON conversation_links(platform, COALESCE(client_id, ''), page_url)
14+ WHERE remote_conversation_id IS NULL
15+ AND route_path IS NULL
16+ AND page_url IS NOT NULL;
17+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_target
18+ ON conversation_links(platform, COALESCE(client_id, ''), target_id)
19+ WHERE remote_conversation_id IS NULL
20+ AND route_path IS NULL
21+ AND page_url IS NULL
22+ AND target_id IS NOT NULL;
23+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_client
24+ ON conversation_links(platform, COALESCE(client_id, ''))
25+ WHERE remote_conversation_id IS NULL
26+ AND route_path IS NULL
27+ AND page_url IS NULL
28+ AND target_id IS NULL
29+ AND client_id IS NOT NULL;
30+CREATE INDEX IF NOT EXISTS idx_conversation_links_local
31+ ON conversation_links(local_conversation_id, is_active, updated_at DESC);
32+CREATE INDEX IF NOT EXISTS idx_conversation_links_client
33+ ON conversation_links(client_id, updated_at DESC);
34+`;
35+
36 export const ARTIFACT_SCHEMA_SQL = `
37 CREATE TABLE IF NOT EXISTS messages (
38 id TEXT PRIMARY KEY,
39@@ -104,13 +138,6 @@ CREATE TABLE IF NOT EXISTS conversation_links (
40 FOREIGN KEY (local_conversation_id) REFERENCES local_conversations(local_conversation_id)
41 );
42
43-CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_remote
44- ON conversation_links(platform, remote_conversation_id);
45-CREATE INDEX IF NOT EXISTS idx_conversation_links_local
46- ON conversation_links(local_conversation_id, is_active, updated_at DESC);
47-CREATE INDEX IF NOT EXISTS idx_conversation_links_client
48- ON conversation_links(client_id, updated_at DESC);
49-
50 CREATE TABLE IF NOT EXISTS renewal_jobs (
51 job_id TEXT PRIMARY KEY,
52 local_conversation_id TEXT NOT NULL,
+219,
-2
1@@ -8,7 +8,7 @@ import {
2 import { dirname, join } from "node:path";
3 import { DatabaseSync } from "node:sqlite";
4
5-import { ARTIFACT_SCHEMA_SQL } from "./schema.js";
6+import { ARTIFACT_SCHEMA_SQL, CONVERSATION_LINK_INDEX_SQL } from "./schema.js";
7 import {
8 buildArtifactRelativePath,
9 buildExecutionArtifactFiles,
10@@ -408,6 +408,7 @@ export class ArtifactStore {
11 this.db = new DatabaseSync(databasePath);
12 this.db.exec("PRAGMA foreign_keys = ON;");
13 this.db.exec(ARTIFACT_SCHEMA_SQL);
14+ this.ensureConversationLinkIndexes();
15 }
16
17 close(): void {
18@@ -896,6 +897,19 @@ export class ArtifactStore {
19 }
20 }
21
22+ private ensureConversationLinkIndexes(): void {
23+ this.db.exec("BEGIN;");
24+
25+ try {
26+ this.deduplicateNullRemoteConversationLinks();
27+ this.db.exec(CONVERSATION_LINK_INDEX_SQL);
28+ this.db.exec("COMMIT;");
29+ } catch (error) {
30+ this.rollbackQuietly();
31+ throw error;
32+ }
33+ }
34+
35 private getRow<T>(query: string, ...params: Array<number | string | null>): T | null {
36 const statement = this.db.prepare(query);
37 return (statement.get(...params) as T | undefined) ?? null;
38@@ -906,6 +920,39 @@ export class ArtifactStore {
39 return statement.all(...params) as T[];
40 }
41
42+ private deduplicateNullRemoteConversationLinks(): void {
43+ const rows = this.getRows<ConversationLinkRow>(
44+ `
45+ SELECT *
46+ FROM conversation_links
47+ WHERE remote_conversation_id IS NULL
48+ ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC;
49+ `
50+ );
51+ const canonicalByKey = new Map<string, ConversationLinkRecord>();
52+
53+ for (const row of rows) {
54+ const candidate = mapConversationLinkRow(row);
55+ const dedupeKey = buildNullRemoteConversationLinkDedupeKey(candidate);
56+
57+ if (dedupeKey == null) {
58+ continue;
59+ }
60+
61+ const canonical = canonicalByKey.get(dedupeKey);
62+
63+ if (canonical == null) {
64+ canonicalByKey.set(dedupeKey, candidate);
65+ continue;
66+ }
67+
68+ const merged = mergeConversationLinkRecords(canonical, candidate);
69+ canonicalByKey.set(dedupeKey, merged);
70+ this.run(UPSERT_CONVERSATION_LINK_SQL, conversationLinkParams(merged));
71+ this.run("DELETE FROM conversation_links WHERE link_id = ?;", [candidate.linkId]);
72+ }
73+ }
74+
75 private resolveExistingConversationLink(
76 input: UpsertConversationLinkInput
77 ): ConversationLinkRecord | null {
78@@ -916,7 +963,12 @@ export class ArtifactStore {
79
80 const remoteConversationId = normalizeOptionalString(input.remoteConversationId);
81 if (remoteConversationId == null) {
82- return byId == null ? null : mapConversationLinkRow(byId);
83+ const byNullRemoteIdentity = this.findConversationLinkByNullRemoteIdentity(input);
84+ return byNullRemoteIdentity == null
85+ ? byId == null
86+ ? null
87+ : mapConversationLinkRow(byId)
88+ : byNullRemoteIdentity;
89 }
90
91 const byRemote = this.getRow<ConversationLinkRow>(
92@@ -938,6 +990,112 @@ export class ArtifactStore {
93 : mapConversationLinkRow(byRemote);
94 }
95
96+ private findConversationLinkByNullRemoteIdentity(
97+ input: Pick<
98+ UpsertConversationLinkInput,
99+ "clientId" | "pageUrl" | "platform" | "routePath" | "targetId"
100+ >
101+ ): ConversationLinkRecord | null {
102+ const platform = normalizeRequiredString(input.platform, "platform");
103+ const clientId = normalizeOptionalString(input.clientId);
104+ const clientScope = normalizeConversationLinkClientScope(clientId);
105+ const routePath = normalizeOptionalString(input.routePath);
106+ const pageUrl = normalizeOptionalString(input.pageUrl);
107+ const targetId = normalizeOptionalString(input.targetId);
108+
109+ if (routePath != null) {
110+ const row = this.getRow<ConversationLinkRow>(
111+ `
112+ SELECT *
113+ FROM conversation_links
114+ WHERE platform = ?
115+ AND remote_conversation_id IS NULL
116+ AND COALESCE(client_id, '') = ?
117+ AND route_path = ?
118+ ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
119+ LIMIT 1;
120+ `,
121+ platform,
122+ clientScope,
123+ routePath
124+ );
125+
126+ if (row != null) {
127+ return mapConversationLinkRow(row);
128+ }
129+ }
130+
131+ if (pageUrl != null) {
132+ const row = this.getRow<ConversationLinkRow>(
133+ `
134+ SELECT *
135+ FROM conversation_links
136+ WHERE platform = ?
137+ AND remote_conversation_id IS NULL
138+ AND COALESCE(client_id, '') = ?
139+ AND route_path IS NULL
140+ AND page_url = ?
141+ ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
142+ LIMIT 1;
143+ `,
144+ platform,
145+ clientScope,
146+ pageUrl
147+ );
148+
149+ if (row != null) {
150+ return mapConversationLinkRow(row);
151+ }
152+ }
153+
154+ if (targetId != null) {
155+ const row = this.getRow<ConversationLinkRow>(
156+ `
157+ SELECT *
158+ FROM conversation_links
159+ WHERE platform = ?
160+ AND remote_conversation_id IS NULL
161+ AND COALESCE(client_id, '') = ?
162+ AND route_path IS NULL
163+ AND page_url IS NULL
164+ AND target_id = ?
165+ ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
166+ LIMIT 1;
167+ `,
168+ platform,
169+ clientScope,
170+ targetId
171+ );
172+
173+ if (row != null) {
174+ return mapConversationLinkRow(row);
175+ }
176+ }
177+
178+ if (clientId == null) {
179+ return null;
180+ }
181+
182+ const row = this.getRow<ConversationLinkRow>(
183+ `
184+ SELECT *
185+ FROM conversation_links
186+ WHERE platform = ?
187+ AND remote_conversation_id IS NULL
188+ AND COALESCE(client_id, '') = ?
189+ AND route_path IS NULL
190+ AND page_url IS NULL
191+ AND target_id IS NULL
192+ ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
193+ LIMIT 1;
194+ `,
195+ platform,
196+ clientScope
197+ );
198+
199+ return row == null ? null : mapConversationLinkRow(row);
200+ }
201+
202 private readLatestMessageForSession(session: SessionRecord): LatestMessageRow | null {
203 const { clause, params } = buildConversationFilters(session.platform, session.conversationId);
204 return this.getRow<LatestMessageRow>(
205@@ -1350,6 +1508,65 @@ function buildConversationLinkRecord(
206 };
207 }
208
209+function buildNullRemoteConversationLinkDedupeKey(
210+ record: Pick<
211+ ConversationLinkRecord,
212+ "clientId" | "pageUrl" | "platform" | "remoteConversationId" | "routePath" | "targetId"
213+ >
214+): string | null {
215+ if (record.remoteConversationId != null) {
216+ return null;
217+ }
218+
219+ const clientScope = normalizeConversationLinkClientScope(record.clientId);
220+ const routePath = normalizeOptionalString(record.routePath);
221+
222+ if (routePath != null) {
223+ return `${record.platform}\u0000${clientScope}\u0000route\u0000${routePath}`;
224+ }
225+
226+ const pageUrl = normalizeOptionalString(record.pageUrl);
227+
228+ if (pageUrl != null) {
229+ return `${record.platform}\u0000${clientScope}\u0000page\u0000${pageUrl}`;
230+ }
231+
232+ const targetId = normalizeOptionalString(record.targetId);
233+
234+ if (targetId != null) {
235+ return `${record.platform}\u0000${clientScope}\u0000target\u0000${targetId}`;
236+ }
237+
238+ if (clientScope === "") {
239+ return null;
240+ }
241+
242+ return `${record.platform}\u0000${clientScope}\u0000client`;
243+}
244+
245+function mergeConversationLinkRecords(
246+ canonical: ConversationLinkRecord,
247+ duplicate: ConversationLinkRecord
248+): ConversationLinkRecord {
249+ return {
250+ ...canonical,
251+ clientId: canonical.clientId ?? duplicate.clientId,
252+ createdAt: Math.min(canonical.createdAt, duplicate.createdAt),
253+ pageTitle: canonical.pageTitle ?? duplicate.pageTitle,
254+ pageUrl: canonical.pageUrl ?? duplicate.pageUrl,
255+ routeParams: canonical.routeParams ?? duplicate.routeParams,
256+ routePath: canonical.routePath ?? duplicate.routePath,
257+ routePattern: canonical.routePattern ?? duplicate.routePattern,
258+ targetId: canonical.targetId ?? duplicate.targetId,
259+ targetKind: canonical.targetKind ?? duplicate.targetKind,
260+ targetPayload: canonical.targetPayload ?? duplicate.targetPayload
261+ };
262+}
263+
264+function normalizeConversationLinkClientScope(value: string | null | undefined): string {
265+ return normalizeOptionalString(value) ?? "";
266+}
267+
268 function buildRenewalJobRecord(input: InsertRenewalJobInput): RenewalJobRecord {
269 const createdAt = input.createdAt ?? Date.now();
270
+25,
-1
1@@ -122,8 +122,32 @@ CREATE TABLE IF NOT EXISTS conversation_links (
2 FOREIGN KEY (local_conversation_id) REFERENCES local_conversations(local_conversation_id)
3 );
4
5+DROP INDEX IF EXISTS idx_conversation_links_remote;
6 CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_remote
7- ON conversation_links(platform, remote_conversation_id);
8+ ON conversation_links(platform, remote_conversation_id)
9+ WHERE remote_conversation_id IS NOT NULL;
10+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_route
11+ ON conversation_links(platform, COALESCE(client_id, ''), route_path)
12+ WHERE remote_conversation_id IS NULL
13+ AND route_path IS NOT NULL;
14+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_page
15+ ON conversation_links(platform, COALESCE(client_id, ''), page_url)
16+ WHERE remote_conversation_id IS NULL
17+ AND route_path IS NULL
18+ AND page_url IS NOT NULL;
19+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_target
20+ ON conversation_links(platform, COALESCE(client_id, ''), target_id)
21+ WHERE remote_conversation_id IS NULL
22+ AND route_path IS NULL
23+ AND page_url IS NULL
24+ AND target_id IS NOT NULL;
25+CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_client
26+ ON conversation_links(platform, COALESCE(client_id, ''))
27+ WHERE remote_conversation_id IS NULL
28+ AND route_path IS NULL
29+ AND page_url IS NULL
30+ AND target_id IS NULL
31+ AND client_id IS NOT NULL;
32 CREATE INDEX IF NOT EXISTS idx_conversation_links_local
33 ON conversation_links(local_conversation_id, is_active, updated_at DESC);
34 CREATE INDEX IF NOT EXISTS idx_conversation_links_client
+2,
-0
1@@ -505,6 +505,8 @@ describe("d1-setup.sql", () => {
2 assert.match(setupSql, /CREATE TABLE IF NOT EXISTS local_conversations/u);
3 assert.match(setupSql, /CREATE TABLE IF NOT EXISTS conversation_links/u);
4 assert.match(setupSql, /CREATE TABLE IF NOT EXISTS renewal_jobs/u);
5+ assert.match(setupSql, /idx_conversation_links_null_route/u);
6+ assert.match(setupSql, /idx_conversation_links_null_page/u);
7 assert.match(setupSql, /idx_renewal_jobs_status_due/u);
8 });
9 });