Skip to content

Commit 7485dd6

Browse files
vincentkocvincentkoc
committed
fix(sqlite): migrate legacy memory and proxy state
1 parent 8413984 commit 7485dd6

9 files changed

Lines changed: 1286 additions & 3 deletions

File tree

‎docs/refactor/database-first.md‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -389,6 +389,9 @@ The branch already has a real shared SQLite base:
389389
path/source row shape and serialized embedding compatibility. These tables
390390
are derived/search cache, not canonical transcript storage; they can be
391391
deleted and rebuilt from memory workspace files and configured sources.
392+
Opening a shipped generic-name memory index migrates its metadata, sources,
393+
chunks, and embedding cache into the canonical tables; derived FTS/vector
394+
tables are rebuilt under their canonical names.
392395
- Subagent run recovery state now lives in typed shared `subagent_runs` rows
393396
with indexed child, requester, and controller session keys. The old
394397
`subagents/runs.json` file is doctor migration input only.
@@ -1724,6 +1727,9 @@ Keep shared coordination state in `state/openclaw.sqlite`:
17241727
WAL, and busy-timeout settings. Payload bytes are gzip-compressed in
17251728
`capture_blobs.data`; there is no debug proxy runtime sidecar DB override,
17261729
blob directory, or proxy-capture-only generated schema/codegen target.
1730+
Doctor/startup migration imports shipped `debug-proxy/capture.sqlite` rows
1731+
and referenced payload blobs, including active legacy DB/blob environment
1732+
overrides, then archives those sources while leaving CA certificates intact.
17271733

17281734
This phase also deletes duplicate sidecar openers, permission helpers, WAL
17291735
setup, filesystem pruning, and compatibility writers from those subsystems.

‎extensions/memory-core/src/memory/index.test.ts‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1669,6 +1669,24 @@ describe("memory index", () => {
16691669
expect(status.vector?.available).toBe(available);
16701670
});
16711671

1672+
it("drops the shipped legacy vector table after loading sqlite-vec", async () => {
1673+
const cfg = createCfg({ vectorEnabled: true });
1674+
const manager = await getPersistentManager(cfg);
1675+
const db = Reflect.get(manager, "db") as DatabaseSync;
1676+
db.exec("CREATE TABLE chunks_vec (id TEXT PRIMARY KEY, embedding BLOB)");
1677+
1678+
const available = await manager.probeVectorStoreAvailability?.();
1679+
if (!available) {
1680+
return;
1681+
}
1682+
1683+
expect(
1684+
db
1685+
.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'chunks_vec'")
1686+
.get(),
1687+
).toBeUndefined();
1688+
});
1689+
16721690
it("probes sqlite vector store availability without initializing embeddings", async () => {
16731691
forceNoProvider = true;
16741692
const cfg = createCfg({

‎extensions/memory-core/src/memory/manager-sync-ops.ts‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -145,6 +145,7 @@ type MemoryReindexRetryState = {
145145

146146
const META_KEY = "memory_index_meta_v1";
147147
const VECTOR_TABLE = MEMORY_INDEX_VECTOR_TABLE;
148+
const LEGACY_VECTOR_TABLE = "chunks_vec";
148149
const FTS_TABLE = MEMORY_INDEX_FTS_TABLE;
149150
const EMBEDDING_CACHE_TABLE = MEMORY_EMBEDDING_CACHE_TABLE;
150151
const SESSION_DIRTY_DEBOUNCE_MS = 5000;
@@ -167,6 +168,12 @@ const log = createSubsystemLogger("memory");
167168
const TEST_MEMORY_WATCH_FACTORY_KEY = Symbol.for("openclaw.test.memoryWatchFactory");
168169
const TEST_MEMORY_NATIVE_WATCH_FACTORY_KEY = Symbol.for("openclaw.test.memoryNativeWatchFactory");
169170

171+
function memoryTableExists(db: DatabaseSync, tableName: string): boolean {
172+
return Boolean(
173+
db.prepare("SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?").get(tableName),
174+
);
175+
}
176+
170177
type NativeMemoryWatchPair = {
171178
dir: string;
172179
main: fsSync.FSWatcher;
@@ -642,6 +649,9 @@ export abstract class MemoryManagerSyncOps {
642649
}
643650
this.vector.extensionPath = loaded.extensionPath;
644651
this.vector.available = true;
652+
if (this.dropLegacyVectorTable()) {
653+
this.dirty = true;
654+
}
645655
return true;
646656
} catch (err) {
647657
const message = formatErrorMessage(err);
@@ -653,7 +663,7 @@ export abstract class MemoryManagerSyncOps {
653663
}
654664

655665
private ensureVectorTable(dimensions: number): void {
656-
if (this.vector.dims === dimensions) {
666+
if (this.vector.dims === dimensions && memoryTableExists(this.db, VECTOR_TABLE)) {
657667
return;
658668
}
659669
if (!this.dropVectorTable()) {
@@ -668,6 +678,19 @@ export abstract class MemoryManagerSyncOps {
668678
this.vector.dims = dimensions;
669679
}
670680

681+
private dropLegacyVectorTable(): boolean {
682+
if (!memoryTableExists(this.db, LEGACY_VECTOR_TABLE)) {
683+
return false;
684+
}
685+
try {
686+
this.db.exec(`DROP TABLE ${LEGACY_VECTOR_TABLE}`);
687+
return true;
688+
} catch (err) {
689+
log.debug(`Failed to drop ${LEGACY_VECTOR_TABLE}: ${formatErrorMessage(err)}`);
690+
return false;
691+
}
692+
}
693+
671694
private dropVectorTable(): boolean {
672695
try {
673696
this.db.exec(`DROP TABLE IF EXISTS ${VECTOR_TABLE}`);

‎packages/memory-host-sdk/src/host/memory-schema.test.ts‎

Lines changed: 175 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,175 @@
1+
// Memory schema tests cover canonical table creation and shipped-name migration.
2+
import { DatabaseSync } from "node:sqlite";
3+
import { describe, expect, it } from "vitest";
4+
import { ensureMemoryIndexSchema } from "./memory-schema.js";
5+
6+
describe("memory index schema", () => {
7+
it("migrates shipped generic tables into canonical memory tables", () => {
8+
const db = new DatabaseSync(":memory:");
9+
try {
10+
db.exec(`
11+
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
12+
CREATE TABLE files (
13+
path TEXT PRIMARY KEY,
14+
source TEXT NOT NULL DEFAULT 'memory',
15+
hash TEXT NOT NULL,
16+
mtime INTEGER NOT NULL,
17+
size INTEGER NOT NULL
18+
);
19+
CREATE TABLE chunks (
20+
id TEXT PRIMARY KEY,
21+
path TEXT NOT NULL,
22+
source TEXT NOT NULL DEFAULT 'memory',
23+
start_line INTEGER NOT NULL,
24+
end_line INTEGER NOT NULL,
25+
hash TEXT NOT NULL,
26+
model TEXT NOT NULL,
27+
text TEXT NOT NULL,
28+
embedding TEXT NOT NULL,
29+
updated_at INTEGER NOT NULL
30+
);
31+
CREATE TABLE embedding_cache (
32+
provider TEXT NOT NULL,
33+
model TEXT NOT NULL,
34+
provider_key TEXT NOT NULL,
35+
hash TEXT NOT NULL,
36+
embedding TEXT NOT NULL,
37+
dims INTEGER,
38+
updated_at INTEGER NOT NULL,
39+
PRIMARY KEY (provider, model, provider_key, hash)
40+
);
41+
CREATE VIRTUAL TABLE chunks_fts USING fts5(
42+
text, id UNINDEXED, path UNINDEXED, source UNINDEXED, model UNINDEXED,
43+
start_line UNINDEXED, end_line UNINDEXED
44+
);
45+
INSERT INTO meta VALUES ('memory_index_meta_v1', '{"vectorDims":3}');
46+
INSERT INTO files VALUES ('MEMORY.md', 'memory', 'file-hash', 10, 20);
47+
INSERT INTO chunks VALUES (
48+
'chunk-1', 'MEMORY.md', 'memory', 1, 2, 'chunk-hash', 'embed-model',
49+
'remember this', '[1,0,0]', 30
50+
);
51+
INSERT INTO embedding_cache VALUES (
52+
'openai', 'embed-model', 'key', 'chunk-hash', '[1,0,0]', 3, 40
53+
);
54+
INSERT INTO chunks_fts VALUES (
55+
'remember this', 'chunk-1', 'MEMORY.md', 'memory', 'embed-model', 1, 2
56+
);
57+
`);
58+
59+
const result = ensureMemoryIndexSchema({
60+
db,
61+
cacheEnabled: true,
62+
ftsEnabled: true,
63+
});
64+
65+
expect(result.ftsAvailable).toBe(true);
66+
expect(db.prepare("SELECT * FROM memory_index_sources").all()).toEqual([
67+
{ path: "MEMORY.md", source: "memory", hash: "file-hash", mtime: 10, size: 20 },
68+
]);
69+
expect(db.prepare("SELECT id, text FROM memory_index_chunks").all()).toEqual([
70+
{ id: "chunk-1", text: "remember this" },
71+
]);
72+
expect(db.prepare("SELECT provider, hash FROM memory_embedding_cache").all()).toEqual([
73+
{ provider: "openai", hash: "chunk-hash" },
74+
]);
75+
expect(
76+
db
77+
.prepare(
78+
"SELECT name FROM sqlite_master WHERE type = 'table' AND name IN ('meta', 'files', 'chunks', 'embedding_cache', 'chunks_fts')",
79+
)
80+
.all(),
81+
).toEqual([]);
82+
} finally {
83+
db.close();
84+
}
85+
});
86+
87+
it("leaves unrelated generic tables untouched", () => {
88+
const db = new DatabaseSync(":memory:");
89+
try {
90+
db.exec(`
91+
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL, owner TEXT);
92+
CREATE TABLE files (
93+
path TEXT PRIMARY KEY,
94+
source TEXT NOT NULL,
95+
hash TEXT NOT NULL,
96+
mtime INTEGER NOT NULL,
97+
size INTEGER NOT NULL
98+
);
99+
CREATE TABLE chunks (
100+
id TEXT PRIMARY KEY,
101+
path TEXT NOT NULL,
102+
source TEXT NOT NULL,
103+
start_line INTEGER NOT NULL,
104+
end_line INTEGER NOT NULL,
105+
hash TEXT NOT NULL,
106+
model TEXT NOT NULL,
107+
text TEXT NOT NULL,
108+
embedding TEXT NOT NULL,
109+
updated_at INTEGER NOT NULL
110+
);
111+
`);
112+
113+
ensureMemoryIndexSchema({
114+
db,
115+
cacheEnabled: false,
116+
ftsEnabled: false,
117+
});
118+
119+
expect(
120+
db
121+
.prepare(
122+
"SELECT name FROM sqlite_master WHERE type = 'table' AND name IN ('meta', 'files', 'chunks') ORDER BY name",
123+
)
124+
.all(),
125+
).toEqual([{ name: "chunks" }, { name: "files" }, { name: "meta" }]);
126+
} finally {
127+
db.close();
128+
}
129+
});
130+
131+
it("keeps legacy tables when canonical rows conflict", () => {
132+
const db = new DatabaseSync(":memory:");
133+
try {
134+
db.exec(`
135+
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
136+
CREATE TABLE files (
137+
path TEXT PRIMARY KEY,
138+
source TEXT NOT NULL DEFAULT 'memory',
139+
hash TEXT NOT NULL,
140+
mtime INTEGER NOT NULL,
141+
size INTEGER NOT NULL
142+
);
143+
CREATE TABLE chunks (
144+
id TEXT PRIMARY KEY,
145+
path TEXT NOT NULL,
146+
source TEXT NOT NULL DEFAULT 'memory',
147+
start_line INTEGER NOT NULL,
148+
end_line INTEGER NOT NULL,
149+
hash TEXT NOT NULL,
150+
model TEXT NOT NULL,
151+
text TEXT NOT NULL,
152+
embedding TEXT NOT NULL,
153+
updated_at INTEGER NOT NULL
154+
);
155+
CREATE TABLE memory_index_meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
156+
INSERT INTO meta VALUES ('memory_index_meta_v1', 'legacy');
157+
INSERT INTO memory_index_meta VALUES ('memory_index_meta_v1', 'canonical');
158+
`);
159+
160+
expect(() =>
161+
ensureMemoryIndexSchema({
162+
db,
163+
cacheEnabled: false,
164+
ftsEnabled: false,
165+
}),
166+
).toThrow("legacy memory meta rows conflict");
167+
expect(db.prepare("SELECT value FROM meta").get()).toEqual({ value: "legacy" });
168+
expect(db.prepare("SELECT value FROM memory_index_meta").get()).toEqual({
169+
value: "canonical",
170+
});
171+
} finally {
172+
db.close();
173+
}
174+
});
175+
});

0 commit comments

Comments
 (0)