Skip to content

Commit 25a8b33

Browse files
fuller-stack-devclaude
fuller-stack-dev
and
claude
authored
fix(state): make managed-image additive migration portable across SQLite versions (#111167)
Co-authored-by: Claude Fable 5 <[email protected]>
1 parent 29b4b74 commit 25a8b33

2 files changed

Lines changed: 211 additions & 41 deletions

File tree

‎src/state/openclaw-state-db-schema-additive.ts‎

Lines changed: 59 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,6 @@
1+
import path from "node:path";
12
import type { DatabaseSync } from "node:sqlite";
3+
import { isRecord } from "@openclaw/normalization-core/record-coerce";
24
import {
35
backfillAcpReplayEstimatedBytes,
46
backfillCronJobsFromJobJson,
@@ -11,6 +13,53 @@ import {
1113
} from "./openclaw-state-db-legacy-backfills.js";
1214
import { ensureColumn } from "./openclaw-state-db-schema-helpers.js";
1315

16+
function resolveLegacyManagedImageRoot(recordJson: unknown): string | null {
17+
if (typeof recordJson !== "string") {
18+
return null;
19+
}
20+
let record: unknown;
21+
try {
22+
record = JSON.parse(recordJson) as unknown;
23+
} catch {
24+
return null;
25+
}
26+
if (!isRecord(record) || !isRecord(record.original)) {
27+
return null;
28+
}
29+
const mediaRoot = record.original.mediaRoot;
30+
if (typeof mediaRoot === "string" && mediaRoot.trim()) {
31+
return path.resolve(mediaRoot);
32+
}
33+
const originalPath = record.original.path;
34+
if (typeof originalPath !== "string" || !originalPath.trim()) {
35+
return null;
36+
}
37+
const resolvedOriginalPath = path.resolve(originalPath);
38+
return path.dirname(path.dirname(path.dirname(resolvedOriginalPath)));
39+
}
40+
41+
function backfillLegacyManagedImageRoots(db: DatabaseSync): void {
42+
const rows = db
43+
.prepare("SELECT attachment_id, record_json FROM managed_outgoing_image_records")
44+
.all() as Array<{ attachment_id: string; record_json: unknown }>;
45+
const updateRoot = db.prepare(
46+
"UPDATE managed_outgoing_image_records SET original_media_root = ? WHERE attachment_id = ?",
47+
);
48+
const deleteRecord = db.prepare(
49+
"DELETE FROM managed_outgoing_image_records WHERE attachment_id = ?",
50+
);
51+
for (const row of rows) {
52+
const mediaRoot = resolveLegacyManagedImageRoot(row.record_json);
53+
if (mediaRoot) {
54+
updateRoot.run(mediaRoot, row.attachment_id);
55+
} else {
56+
// This table had no shipped writer. Discard malformed unexpected rows
57+
// instead of retaining unusable empty roots or wedging every database open.
58+
deleteRecord.run(row.attachment_id);
59+
}
60+
}
61+
}
62+
1463
export function ensureAdditiveStateColumns(db: DatabaseSync): void {
1564
const addedDiagnosticEventSequence = ensureColumn(
1665
db,
@@ -190,8 +239,16 @@ export function ensureAdditiveStateColumns(db: DatabaseSync): void {
190239
ensureColumn(db, "commitments", "snoozed_until_ms INTEGER");
191240
ensureColumn(db, "commitments", "expired_at_ms INTEGER");
192241
// The shipped JSON runtime predeclared this table but never populated it.
193-
// Add required typed columns before Doctor or runtime can insert canonical rows.
194-
ensureColumn(db, "managed_outgoing_image_records", "original_media_root TEXT NOT NULL");
242+
// The transitional default makes ADD COLUMN portable; schema-v2 tables are
243+
// rebuilt from canonical STRICT SQL immediately afterward, removing it.
244+
const addedOriginalMediaRoot = ensureColumn(
245+
db,
246+
"managed_outgoing_image_records",
247+
"original_media_root TEXT NOT NULL DEFAULT ''",
248+
);
249+
if (addedOriginalMediaRoot) {
250+
backfillLegacyManagedImageRoots(db);
251+
}
195252
ensureColumn(db, "managed_outgoing_image_records", "agent_id TEXT");
196253
ensureColumn(
197254
db,

‎src/state/openclaw-state-db.test.ts‎

Lines changed: 152 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,90 @@ function createTempStateDir(): string {
4848
return makeTempDir(stateDbTempDirs, "openclaw-state-db-");
4949
}
5050

51+
function replaceManagedImageRecordsWithLegacyTable(
52+
database: DatabaseSync,
53+
options: { withRow: boolean },
54+
): void {
55+
database.exec(`
56+
DROP TABLE managed_outgoing_image_records;
57+
CREATE TABLE managed_outgoing_image_records (
58+
attachment_id TEXT NOT NULL PRIMARY KEY,
59+
session_key TEXT NOT NULL,
60+
message_id TEXT,
61+
created_at TEXT NOT NULL,
62+
updated_at TEXT,
63+
retention_class TEXT,
64+
alt TEXT NOT NULL,
65+
original_media_id TEXT NOT NULL,
66+
original_media_subdir TEXT NOT NULL,
67+
original_content_type TEXT NOT NULL,
68+
original_width INTEGER,
69+
original_height INTEGER,
70+
original_size_bytes INTEGER,
71+
original_filename TEXT,
72+
record_json TEXT NOT NULL
73+
);
74+
CREATE INDEX idx_managed_outgoing_images_session
75+
ON managed_outgoing_image_records(session_key, created_at DESC, attachment_id);
76+
CREATE INDEX idx_managed_outgoing_images_message
77+
ON managed_outgoing_image_records(session_key, message_id, attachment_id)
78+
WHERE message_id IS NOT NULL;
79+
PRAGMA user_version = 2;
80+
UPDATE schema_meta SET schema_version = 2 WHERE meta_key = 'primary';
81+
`);
82+
if (!options.withRow) {
83+
return;
84+
}
85+
const record = {
86+
attachmentId: "legacy-attachment",
87+
sessionKey: "agent:main:legacy",
88+
messageId: "legacy-message",
89+
createdAt: "2026-07-17T00:00:00.000Z",
90+
alt: "legacy image",
91+
original: {
92+
path: "/legacy/media/outgoing/originals/legacy-media",
93+
contentType: "image/png",
94+
width: 640,
95+
height: 480,
96+
sizeBytes: 1234,
97+
filename: "legacy.png",
98+
},
99+
};
100+
database
101+
.prepare(
102+
`INSERT INTO managed_outgoing_image_records (
103+
attachment_id,
104+
session_key,
105+
message_id,
106+
created_at,
107+
alt,
108+
original_media_id,
109+
original_media_subdir,
110+
original_content_type,
111+
original_width,
112+
original_height,
113+
original_size_bytes,
114+
original_filename,
115+
record_json
116+
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
117+
)
118+
.run(
119+
record.attachmentId,
120+
record.sessionKey,
121+
record.messageId,
122+
record.createdAt,
123+
record.alt,
124+
"legacy-media",
125+
"outgoing/originals",
126+
record.original.contentType,
127+
record.original.width,
128+
record.original.height,
129+
record.original.sizeBytes,
130+
record.original.filename,
131+
JSON.stringify(record),
132+
);
133+
}
134+
51135
const LEGACY_SESSION_WATCH_SCHEMA_VERSION = 3;
52136
const LEGACY_AMBIENT_WATCH_PREFIX = "ambient-group-watch:";
53137

@@ -2404,47 +2488,76 @@ INSERT INTO macos_port_guardian_records VALUES (4242, 18789, '/usr/bin/ssh', 're
24042488
expect(result.warnings[0]).not.toContain("run openclaw doctor --fix");
24052489
});
24062490

2407-
it("adds managed-image typed columns before creating canonical indexes", () => {
2408-
const stateDir = createTempStateDir();
2409-
const options = { env: { OPENCLAW_STATE_DIR: stateDir } };
2410-
const databasePath = openOpenClawStateDatabase(options).path;
2411-
closeOpenClawStateDatabaseForTest();
2491+
it.each([
2492+
{ migrationPath: "runtime open", withRow: false },
2493+
{ migrationPath: "doctor repair", withRow: true },
2494+
])(
2495+
"restores the true legacy managed-image table through $migrationPath",
2496+
({ migrationPath, withRow }) => {
2497+
const stateDir = createTempStateDir();
2498+
const options = { env: { OPENCLAW_STATE_DIR: stateDir } };
2499+
const databasePath = openOpenClawStateDatabase(options).path;
2500+
closeOpenClawStateDatabaseForTest();
24122501

2413-
const { DatabaseSync } = requireNodeSqlite();
2414-
const legacyDb = new DatabaseSync(databasePath);
2415-
legacyDb.exec(`
2416-
DROP INDEX idx_managed_outgoing_images_session;
2417-
DROP INDEX idx_managed_outgoing_images_message;
2418-
DROP INDEX idx_managed_outgoing_images_agent_session;
2419-
DROP INDEX idx_managed_outgoing_images_agent_message;
2420-
ALTER TABLE managed_outgoing_image_records DROP COLUMN original_media_root;
2421-
ALTER TABLE managed_outgoing_image_records DROP COLUMN agent_id;
2422-
ALTER TABLE managed_outgoing_image_records DROP COLUMN cleanup_pending;
2423-
`);
2424-
legacyDb.close();
2502+
const { DatabaseSync } = requireNodeSqlite();
2503+
const legacyDb = new DatabaseSync(databasePath);
2504+
replaceManagedImageRecordsWithLegacyTable(legacyDb, { withRow });
2505+
legacyDb.close();
24252506

2426-
const reopened = openOpenClawStateDatabase(options);
2427-
const columns = reopened.db
2428-
.prepare("PRAGMA table_info(managed_outgoing_image_records)")
2429-
.all() as Array<{ name?: unknown; notnull?: unknown }>;
2430-
expect(columns).toContainEqual(
2431-
expect.objectContaining({ name: "original_media_root", notnull: 1 }),
2432-
);
2433-
expect(columns).toContainEqual(expect.objectContaining({ name: "agent_id" }));
2434-
expect(columns).toContainEqual(expect.objectContaining({ name: "cleanup_pending" }));
2435-
assertOpenClawStateDatabaseForMaintenance(reopened.db, { pathname: reopened.path });
2436-
const indexes = reopened.db
2437-
.prepare("PRAGMA index_list(managed_outgoing_image_records)")
2438-
.all() as Array<{ name?: unknown }>;
2439-
expect(indexes).toEqual(
2440-
expect.arrayContaining([
2441-
expect.objectContaining({ name: "idx_managed_outgoing_images_session" }),
2442-
expect.objectContaining({ name: "idx_managed_outgoing_images_message" }),
2443-
expect.objectContaining({ name: "idx_managed_outgoing_images_agent_session" }),
2444-
expect.objectContaining({ name: "idx_managed_outgoing_images_agent_message" }),
2445-
]),
2446-
);
2447-
});
2507+
if (migrationPath === "doctor repair") {
2508+
expect(repairOpenClawStateDatabaseSchema(options).warnings).toEqual([]);
2509+
}
2510+
const reopened = openOpenClawStateDatabase(options);
2511+
const columns = reopened.db
2512+
.prepare("PRAGMA table_info(managed_outgoing_image_records)")
2513+
.all() as Array<{ dflt_value?: unknown; name?: unknown; notnull?: unknown }>;
2514+
expect(columns).toContainEqual(
2515+
expect.objectContaining({ dflt_value: null, name: "original_media_root", notnull: 1 }),
2516+
);
2517+
expect(columns).toContainEqual(expect.objectContaining({ name: "agent_id" }));
2518+
expect(columns).toContainEqual(expect.objectContaining({ name: "cleanup_pending" }));
2519+
assertOpenClawStateDatabaseForMaintenance(reopened.db, { pathname: reopened.path });
2520+
const tableSql = reopened.db
2521+
.prepare(
2522+
"SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = 'managed_outgoing_image_records'",
2523+
)
2524+
.get() as { sql: string };
2525+
expect(
2526+
tableSql.sql
2527+
.split("\n")
2528+
.find((line) => line.includes("original_media_root"))
2529+
?.trim()
2530+
.replace(/,$/u, ""),
2531+
).toBe("original_media_root TEXT NOT NULL");
2532+
expect(tableSql.sql).toMatch(/\) STRICT$/u);
2533+
const indexes = reopened.db
2534+
.prepare("PRAGMA index_list(managed_outgoing_image_records)")
2535+
.all() as Array<{ name?: unknown }>;
2536+
expect(indexes).toEqual(
2537+
expect.arrayContaining([
2538+
expect.objectContaining({ name: "idx_managed_outgoing_images_session" }),
2539+
expect.objectContaining({ name: "idx_managed_outgoing_images_message" }),
2540+
expect.objectContaining({ name: "idx_managed_outgoing_images_agent_session" }),
2541+
expect.objectContaining({ name: "idx_managed_outgoing_images_agent_message" }),
2542+
]),
2543+
);
2544+
if (withRow) {
2545+
expect(
2546+
reopened.db
2547+
.prepare(
2548+
`SELECT attachment_id, original_media_root, agent_id, cleanup_pending
2549+
FROM managed_outgoing_image_records`,
2550+
)
2551+
.get(),
2552+
).toEqual({
2553+
agent_id: null,
2554+
attachment_id: "legacy-attachment",
2555+
cleanup_pending: 0,
2556+
original_media_root: "/legacy/media",
2557+
});
2558+
}
2559+
},
2560+
);
24482561

24492562
it("backfills diagnostic event sequences in legacy creation order", () => {
24502563
const stateDir = createTempStateDir();

0 commit comments

Comments
 (0)