1import * as NodeServices from "@effect/platform-node/NodeServices";
2import { assert, it } from "@effect/vitest";
3import * as HostProcess from "@t3tools/shared/HostProcess";
4import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
5import * as Clock from "effect/Clock";
6import * as Duration from "effect/Duration";
7import * as Effect from "effect/Effect";
8import * as FileSystem from "effect/FileSystem";
9import * as Layer from "effect/Layer";
10import * as Path from "effect/Path";
11import * as SqlClient from "effect/sql/SqlClient";
12
13import * as DatabaseMaintenance from "./DatabaseMaintenance.ts";
14import * as Sqlite from "./Sqlite.ts";
15
16it.layer(DatabaseMaintenance.layer.pipe(Layer.provideMerge(NodeServices.layer)))(
17 "DatabaseMaintenance",
18 (it) => {
19 it.effect("reports an unreadable database as an inspection failure", () =>
20 Effect.gen(function* () {
21 const fs = yield* FileSystem.FileSystem;
22 const path = yield* Path.Path;
23 const maintenance = yield* DatabaseMaintenance.DatabaseMaintenance;
24 const dbPath = path.join(
25 yield* fs.makeTempDirectoryScoped({ prefix: "t3-inspect-" }),
26 "statev2.sqlite",
27 );
28 yield* fs.writeFileString(dbPath, "not a database");
29 const failure = yield* Effect.flip(maintenance.inspect(dbPath));
30 assert.equal(failure._tag, "DatabaseInspectError");
31 }),
32 );
33
34 it.effect("reports and deletes only the database from before 0.1.0", () =>
35 Effect.gen(function* () {
36 const fs = yield* FileSystem.FileSystem;
37 const path = yield* Path.Path;
38 const maintenance = yield* DatabaseMaintenance.DatabaseMaintenance;
39 const userdata = yield* fs.makeTempDirectoryScoped({ prefix: "t3-legacy-" });
40 const dbPath = path.join(userdata, "statev2.sqlite");
41 const legacy = path.join(userdata, "state.sqlite");
42 yield* Effect.gen(function* () {
43 const sql = yield* SqlClient.SqlClient;
44 yield* sql`CREATE TABLE retained_data (value TEXT)`;
45 yield* sql`INSERT INTO retained_data VALUES ('keep')`;
46 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath })));
47 assert.isNull((yield* maintenance.inspect(dbPath)).legacyDatabaseBytes);
48
49 const openLegacy = NodeSqliteClient.layer({ filename: legacy });
50 yield* Effect.gen(function* () {
51 const sql = yield* SqlClient.SqlClient;
52 yield* sql`PRAGMA journal_mode = WAL`;
53 yield* sql`CREATE TABLE v1_data (value TEXT)`;
54 yield* sql`INSERT INTO v1_data VALUES ('rollback')`;
55 }).pipe(Effect.provide(openLegacy));
56 assert.isAbove((yield* maintenance.inspect(dbPath)).legacyDatabaseBytes ?? 0, 0);
57
58 const refused = yield* Effect.gen(function* () {
59 const sql = yield* SqlClient.SqlClient;
60 yield* sql`SELECT value FROM v1_data`;
61 const failure = yield* Effect.flip(maintenance.removeLegacyDatabase(dbPath));
62 assert.deepEqual(yield* sql`SELECT value FROM v1_data`, [{ value: "rollback" }]);
63 return failure;
64 }).pipe(Effect.provide(openLegacy));
65 assert.equal(refused._tag, "DatabaseInUseError");
66 assert.isTrue(yield* fs.exists(legacy));
67
68 yield* Effect.flatMap(DatabaseMaintenance.DatabaseMaintenance, (service) =>
69 service.removeLegacyDatabase(dbPath),
70 ).pipe(
71 Effect.provide(Layer.fresh(DatabaseMaintenance.layer)),
72 Effect.provideService(HostProcess.Platform, "win32"),
73 Effect.provideService(FileSystem.FileSystem, {
74 ...fs,
75 remove: (target, options) =>
76 Effect.gen(function* () {
77 if (target === legacy) {
78 yield* Effect.gen(function* () {
79 const sql = yield* SqlClient.SqlClient;
80 yield* sql`PRAGMA busy_timeout = 0`;
81 yield* sql`BEGIN EXCLUSIVE`;
82 yield* sql`COMMIT`;
83 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: legacy })));
84 // Create sidecars after the probe closes so only Doctor can remove them.
85 yield* fs.writeFileString(`${legacy}-wal`, "leftover WAL");
86 yield* fs.writeFileString(`${legacy}-shm`, "leftover SHM");
87 }
88 yield* fs.remove(target, options);
89 }),
90 }),
91 );
92 assert.isFalse(yield* fs.exists(legacy));
93 assert.isFalse(yield* fs.exists(`${legacy}-wal`));
94 assert.isFalse(yield* fs.exists(`${legacy}-shm`));
95 assert.isTrue(yield* fs.exists(dbPath));
96 yield* Effect.gen(function* () {
97 const sql = yield* SqlClient.SqlClient;
98 assert.deepEqual(yield* sql`SELECT value FROM retained_data`, [{ value: "keep" }]);
99 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath, readonly: true })));
100 assert.isNull((yield* maintenance.inspect(dbPath)).legacyDatabaseBytes);
101 }),
102 );
103
104 it.effect("runs a repair requested from Settings once and keeps its failure for Settings", () =>
105 Effect.gen(function* () {
106 const fs = yield* FileSystem.FileSystem;
107 const path = yield* Path.Path;
108 const baseDir = yield* fs.makeTempDirectoryScoped({ prefix: "t3-requested-reclaim-" });
109 const dbPath = path.join(baseDir, "userdata", "statev2.sqlite");
110 const files = DatabaseMaintenance.reclaimFiles(path, dbPath);
111 yield* Effect.void.pipe(Effect.provide(Sqlite.layerFromPath(dbPath)));
112 const autoVacuum = Effect.flatMap(
113 SqlClient.SqlClient,
114 (sql) => sql<{ readonly auto_vacuum: number }>`PRAGMA auto_vacuum`,
115 ).pipe(
116 Effect.map((rows) => rows[0]?.auto_vacuum),
117 Effect.provide(NodeSqliteClient.layer({ filename: dbPath, readonly: true })),
118 );
119 yield* Effect.gen(function* () {
120 const sql = yield* SqlClient.SqlClient;
121 yield* sql`PRAGMA auto_vacuum = NONE`;
122 yield* sql`VACUUM`;
123 yield* sql`CREATE TABLE retained_data (value TEXT)`;
124 yield* sql`INSERT INTO retained_data VALUES ('keep')`;
125 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath })));
126
127 // Every server exit runs this; without a request it must leave the database alone.
128 yield* DatabaseMaintenance.runRequestedReclaim(dbPath);
129 assert.equal(yield* autoVacuum, 0);
130
131 yield* fs.writeFileString(files.request, "");
132 yield* fs.writeFileString(files.failure, "an earlier failure");
133 yield* DatabaseMaintenance.runRequestedReclaim(dbPath);
134 assert.isFalse(yield* fs.exists(files.request));
135 assert.isFalse(yield* fs.exists(files.failure));
136 assert.equal(yield* autoVacuum, 2);
137 yield* Effect.gen(function* () {
138 const sql = yield* SqlClient.SqlClient;
139 assert.deepEqual(yield* sql`SELECT value FROM retained_data`, [{ value: "keep" }]);
140 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath, readonly: true })));
141
142 const brokenPath = path.join(baseDir, "broken", "statev2.sqlite");
143 const broken = DatabaseMaintenance.reclaimFiles(path, brokenPath);
144 yield* fs.makeDirectory(path.dirname(brokenPath));
145 yield* fs.writeFileString(brokenPath, "not a database");
146 yield* fs.writeFileString(broken.request, "");
147 yield* DatabaseMaintenance.runRequestedReclaim(brokenPath);
148 assert.isFalse(
149 yield* fs.exists(broken.request),
150 "a failed repair must not rerun on every exit",
151 );
152 assert.equal(
153 yield* fs.readFileString(broken.failure),
154 new DatabaseMaintenance.DatabaseCompactError({ path: brokenPath, cause: null }).message,
155 );
156 }),
157 );
158
159 it.effect("refuses a repair whose copies would not fit on disk", () =>
160 Effect.gen(function* () {
161 const fs = yield* FileSystem.FileSystem;
162 const path = yield* Path.Path;
163 const dbPath = path.join(
164 yield* fs.makeTempDirectoryScoped({ prefix: "t3-reclaim-space-" }),
165 "statev2.sqlite",
166 );
167 assert.isNull(yield* DatabaseMaintenance.reclaimSpaceShortfall(dbPath, 1024));
168 const huge = 2 ** 60;
169 const shortfall = yield* DatabaseMaintenance.reclaimSpaceShortfall(dbPath, huge);
170 assert.isAtLeast(shortfall?.neededBytes ?? 0, huge);
171 assert.isBelow(shortfall?.freeBytes ?? Infinity, huge);
172 }),
173 );
174
175 it.effect("compacts an existing database offline only once its server has stopped", () =>
176 Effect.gen(function* () {
177 const fs = yield* FileSystem.FileSystem;
178 const path = yield* Path.Path;
179 const maintenance = yield* DatabaseMaintenance.DatabaseMaintenance;
180 const baseDir = yield* fs.makeTempDirectoryScoped({ prefix: "t3-compact-" });
181 const dbPath = path.join(baseDir, "userdata", "statev2.sqlite");
182 // Opening the database migrates it.
183 yield* Effect.void.pipe(Effect.provide(Sqlite.layerFromPath(dbPath)));
184 yield* Effect.gen(function* () {
185 const sql = yield* SqlClient.SqlClient;
186 yield* sql`PRAGMA auto_vacuum = NONE`;
187 yield* sql`VACUUM`;
188 yield* sql`CREATE TABLE retained_data (value TEXT)`;
189 yield* sql`INSERT INTO retained_data VALUES ('keep')`;
190 yield* sql`CREATE TABLE discarded_data (value BLOB)`;
191 yield* sql`INSERT INTO discarded_data VALUES (zeroblob(4194304))`;
192 yield* sql`DROP TABLE discarded_data`;
193 yield* sql`
194 INSERT INTO orchestration_v2_projection_messages (
195 message_id, thread_id, run_id, node_id, role, streaming, created_at, updated_at, payload_json
196 )
197 VALUES (
198 'message-1', 'thread-1', NULL, NULL, 'user', 0,
199 '2026-09-01T00:00:00.000Z', '2026-09-01T00:00:00.000Z', '{"text":"needle"}'
200 )
201 `;
202 // An index that drifted from its rows, as a renumbering VACUUM would leave it.
203 yield* sql`
204 INSERT INTO orchestration_v2_message_search (orchestration_v2_message_search)
205 VALUES ('delete-all')
206 `;
207 yield* sql`PRAGMA wal_checkpoint(TRUNCATE)`;
208 yield* sql`INSERT INTO orchestration_v2_legacy_imports (
209 thread_id, source_updated_at, shell_imported_at, transcript_imported_at, imported_message_count
210 ) VALUES ('imported', '2026-10-17', '2026-10-17', '2026-10-17', 0)`;
211 yield* sql`WITH RECURSIVE history(n) AS (
212 SELECT 1 UNION ALL SELECT n + 1 FROM history WHERE n < 51
213 ) INSERT INTO orchestration_command_receipts (
214 command_id, aggregate_kind, aggregate_id, accepted_at, result_sequence, status, command_type
215 ) SELECT 'legacy-' || n, 'thread', 'imported', '2026-10-17', 0, 'accepted', 'legacy' FROM history`;
216 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath })));
217
218 const sizeBefore = (yield* fs.stat(dbPath)).size;
219 const refused = yield* Effect.gen(function* () {
220 const sql = yield* SqlClient.SqlClient;
221 yield* sql`SELECT value FROM retained_data`;
222 return yield* maintenance.compact(dbPath).pipe(Effect.flip);
223 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath })));
224 assert.equal(refused._tag, "DatabaseInUseError");
225 assert.equal((yield* fs.stat(dbPath)).size, sizeBefore);
226
227 const clock = yield* Clock.Clock;
228 const pauses: number[] = [];
229 let activeNanos = 0n;
230 const steps: string[] = [];
231 const result = yield* maintenance
232 .compact(dbPath, (step) => Effect.sync(() => steps.push(step)))
233 .pipe(
234 Effect.provideService(Clock.Clock, {
235 ...clock,
236 // Exceed the background budget whenever the compactor checks elapsed time.
237 monotonicTimeNanos: Effect.sync(() => (activeNanos += 30_000_000n)),
238 sleep: (duration) =>
239 Effect.sync(() => {
240 pauses.push(Duration.toMillis(duration));
241 }),
242 }),
243 );
244 assert.deepEqual(pauses, [], "offline doctor must not throttle compaction");
245 assert.deepEqual(steps, ["prune-events", "vacuum", "rebuild-search", "checkpoint"]);
246 assert.equal(result.deletedReceiptCount, 51);
247 assert.isBelow(result.afterBytes, result.beforeBytes - 3 * 1024 * 1024);
248 yield* Effect.gen(function* () {
249 const sql = yield* SqlClient.SqlClient;
250 assert.deepEqual(yield* sql`PRAGMA auto_vacuum`, [{ auto_vacuum: 2 }]);
251 assert.deepEqual(yield* sql`SELECT value FROM retained_data`, [{ value: "keep" }]);
252 assert.deepEqual(
253 yield* sql`
254 SELECT messages.message_id AS "messageId"
255 FROM orchestration_v2_message_search AS search
256 INNER JOIN orchestration_v2_projection_messages AS messages
257 ON messages.rowid = search.rowid
258 WHERE orchestration_v2_message_search MATCH '"needle"'
259 `,
260 [{ messageId: "message-1" }],
261 );
262 }).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath, readonly: true })));
263 }),
264 );
265 },
266);