Skip to content

Commit 533559f

Browse files
fix(server): purge deleted threads in bounded batches and keep forked sources
Deleted threads that a surviving run-fork inherits history from, directly or through a chain of forks, keep their content. Purging deletes at most 500 rows per statement and yields between batches, so one very large thread no longer holds the writer. Co-Authored-By: Claude Opus 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_0199EKpFPvxJDRDqCwnfb8Q1
1 parent 778b271 commit 533559f

2 files changed

Lines changed: 186 additions & 91 deletions

File tree

‎apps/server/src/orchestration-v2/ProjectionMaintenance.test.ts‎

Lines changed: 120 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ import {
88
type OrchestrationV2TurnItem,
99
MessageId,
1010
ProjectId,
11+
RunId,
1112
ProviderDriverKind,
1213
ProviderInstanceId,
1314
ThreadId,
@@ -404,69 +405,76 @@ const countContent = (threadId: ThreadId) =>
404405
`,
405406
).pipe(Effect.map((rows) => rows[0]));
406407

407-
it.effect("purges content of threads deleted beyond the retained history", () =>
408-
Effect.gen(function* () {
409-
const sink = yield* EventSink.EventSinkV2;
410-
const projections = yield* ProjectionStore.ProjectionStoreV2;
411-
const maintenance = yield* ProjectionMaintenance.ProjectionMaintenanceV2;
412-
const now = yield* DateTime.now;
413-
const seed = Effect.fnUntraced(function* (name: string) {
414-
const threadId = ThreadId.make(`thread:purge-${name}`);
415-
const thread = makeThread(threadId, now);
416-
yield* sink.write({
417-
events: [{ ...threadCreated(thread, now), id: EventId.make(`event:purge:${name}:thread`) }],
418-
});
419-
yield* sink.write({
420-
events: [
421-
itemEvent(
422-
threadId,
423-
now,
424-
itemSpecs[0],
425-
0,
426-
`event:purge:${name}:item`,
427-
TurnItemId.make(`turn-item:purge:${name}`),
428-
),
429-
],
430-
});
431-
yield* sink.write({
432-
events: [
433-
{
434-
id: EventId.make(`event:purge:${name}:message`),
435-
type: "message.updated",
408+
const purgeFixture = Effect.gen(function* () {
409+
const sink = yield* EventSink.EventSinkV2;
410+
const projections = yield* ProjectionStore.ProjectionStoreV2;
411+
const maintenance = yield* ProjectionMaintenance.ProjectionMaintenanceV2;
412+
const now = yield* DateTime.now;
413+
const seed = Effect.fnUntraced(function* (
414+
name: string,
415+
forkedFrom: OrchestrationV2AppThread["forkedFrom"] = null,
416+
) {
417+
const threadId = ThreadId.make(`thread:purge-${name}`);
418+
const thread = { ...makeThread(threadId, now), forkedFrom };
419+
yield* sink.write({
420+
events: [{ ...threadCreated(thread, now), id: EventId.make(`event:purge:${name}:thread`) }],
421+
});
422+
yield* sink.write({
423+
events: [
424+
itemEvent(
425+
threadId,
426+
now,
427+
itemSpecs[0],
428+
0,
429+
`event:purge:${name}:item`,
430+
TurnItemId.make(`turn-item:purge:${name}`),
431+
),
432+
],
433+
});
434+
yield* sink.write({
435+
events: [
436+
{
437+
id: EventId.make(`event:purge:${name}:message`),
438+
type: "message.updated",
439+
threadId,
440+
occurredAt: now,
441+
payload: {
442+
id: MessageId.make(`message:purge:${name}`),
436443
threadId,
437-
occurredAt: now,
438-
payload: {
439-
id: MessageId.make(`message:purge:${name}`),
440-
threadId,
441-
runId: null,
442-
nodeId: null,
443-
createdBy: "agent",
444-
creationSource: "provider",
445-
role: "assistant",
446-
text: `purgeable${threadId.replace(/\W/g, "")}`,
447-
streaming: false,
448-
attachments: [],
449-
createdAt: now,
450-
updatedAt: now,
451-
},
444+
runId: null,
445+
nodeId: null,
446+
createdBy: "agent",
447+
creationSource: "provider",
448+
role: "assistant",
449+
text: `purgeable${threadId.replace(/\W/g, "")}`,
450+
streaming: false,
451+
attachments: [],
452+
createdAt: now,
453+
updatedAt: now,
452454
},
453-
],
454-
});
455-
return thread;
455+
},
456+
],
456457
});
457-
const remove = (thread: OrchestrationV2AppThread, at: DateTime.Utc) =>
458-
sink.write({
459-
events: [
460-
{
461-
id: EventId.make(`event:purge:${thread.id}:deleted`),
462-
type: "thread.deleted",
463-
threadId: thread.id,
464-
occurredAt: at,
465-
payload: { ...thread, deletedAt: at, updatedAt: at },
466-
},
467-
],
468-
});
458+
return thread;
459+
});
460+
const remove = (thread: OrchestrationV2AppThread, at: DateTime.Utc) =>
461+
sink.write({
462+
events: [
463+
{
464+
id: EventId.make(`event:purge:${thread.id}:deleted`),
465+
type: "thread.deleted",
466+
threadId: thread.id,
467+
occurredAt: at,
468+
payload: { ...thread, deletedAt: at, updatedAt: at },
469+
},
470+
],
471+
});
472+
return { projections, maintenance, now, seed, remove };
473+
});
469474

475+
it.effect("purges content of threads deleted beyond the retained history", () =>
476+
Effect.gen(function* () {
477+
const { projections, maintenance, now, seed, remove } = yield* purgeFixture;
470478
const old = yield* seed("old");
471479
const live = yield* seed("live");
472480
yield* remove(old, now);
@@ -519,3 +527,56 @@ it.effect("lists every thread-scoped projection table or names why it is kept",
519527
);
520528
}).pipe(Effect.provide(layerTest)),
521529
);
530+
531+
it.effect("keeps a deleted thread that a live run-fork still inherits history from", () =>
532+
Effect.gen(function* () {
533+
const { maintenance, seed, remove } = yield* purgeFixture;
534+
const root = yield* seed("root");
535+
const middle = yield* seed("middle", {
536+
type: "run",
537+
threadId: root.id,
538+
runId: RunId.make("run:purge:root"),
539+
});
540+
const fork = yield* seed("fork", {
541+
type: "run",
542+
threadId: middle.id,
543+
runId: RunId.make("run:purge:middle"),
544+
});
545+
const unrelated = yield* seed("unrelated");
546+
yield* remove(root, yield* DateTime.now);
547+
yield* remove(middle, yield* DateTime.now);
548+
yield* remove(unrelated, yield* DateTime.now);
549+
yield* TestClock.adjust("2 days");
550+
551+
yield* maintenance.pruneEventStore();
552+
assert.equal((yield* countContent(root.id))?.items, 1);
553+
assert.equal((yield* countContent(middle.id))?.items, 1);
554+
assert.equal((yield* countContent(fork.id))?.items, 1);
555+
assert.equal((yield* countContent(unrelated.id))?.items, 0);
556+
}).pipe(Effect.provide(layerTest)),
557+
);
558+
559+
it.effect("purges a very large thread in bounded batches", () =>
560+
Effect.gen(function* () {
561+
const { maintenance, seed, remove } = yield* purgeFixture;
562+
const sql = yield* SqlClient.SqlClient;
563+
const big = yield* seed("big");
564+
yield* sql`
565+
INSERT INTO orchestration_v2_turn_item_positions (thread_id, turn_item_id, ordinal)
566+
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1200)
567+
SELECT ${big.id}, 'turn-item:purge:filler:' || i, i + 100 FROM n
568+
`;
569+
yield* remove(big, yield* DateTime.now);
570+
yield* TestClock.adjust("2 days");
571+
572+
let batches = 0;
573+
const result = yield* maintenance.pruneEventStore(
574+
Effect.sync(() => {
575+
batches += 1;
576+
}),
577+
);
578+
assert.isAtLeast(result.purgedProjectionRowCount, 1200);
579+
assert.isAtLeast(batches, 3);
580+
assert.equal((yield* countContent(big.id))?.positions, 0);
581+
}).pipe(Effect.provide(layerTest)),
582+
);

‎apps/server/src/orchestration-v2/ProjectionMaintenance.ts‎

Lines changed: 66 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@ import * as Duration from "effect/Duration";
55
import * as Effect from "effect/Effect";
66
import * as Layer from "effect/Layer";
77
import * as Schema from "effect/Schema";
8+
import type * as SqlError from "effect/sql/SqlError";
89
import * as SqlClient from "effect/sql/SqlClient";
910

1011
import {
@@ -46,6 +47,8 @@ export class ProjectionMaintenanceV2 extends Context.Service<
4647
const RETAINED_EVENT_HISTORY = Duration.days(1);
4748
// Small pages bound work on the reader worker and synchronous fallback.
4849
const PAGE_SIZE = 50;
50+
// Bounds the rows one purge statement deletes, FTS trigger work included.
51+
const ROW_BATCH_SIZE = 500;
4952
const REFERENCE_SCAN_WINDOW = 1000;
5053

5154
// Thread-scoped projection tables, children first. The thread row stays as the tombstone.
@@ -277,59 +280,90 @@ export const layer: Layer.Layer<ProjectionMaintenanceV2, never, SqlClient.SqlCli
277280
});
278281

279282
// A deleted thread keeps only its tombstone row once the event history it could be replayed
280-
// from has been pruned. Idempotent: threads without content match nothing.
283+
// from has been pruned. A thread that a surviving run-fork inherits history from is kept
284+
// whole. Idempotent: threads without content match nothing.
281285
const purgeDeletedThreadContent = Effect.fnUntraced(function* (
282286
now: DateTime.Utc,
283287
betweenPages: Effect.Effect<void>,
284288
) {
285289
const cutoff = DateTime.formatIso(DateTime.subtractDuration(now, RETAINED_EVENT_HISTORY));
286290
let purged = 0;
291+
// Each batch is one statement that commits on its own, so no transaction spans a yield.
292+
const deleteBatches = Effect.fnUntraced(function* (
293+
batch: (limit: number) => Effect.Effect<unknown, SqlError.SqlError>,
294+
) {
295+
for (;;) {
296+
yield* batch(ROW_BATCH_SIZE);
297+
const changes = yield* sql<{ readonly count: number }>`SELECT changes() AS count`;
298+
const count = changes[0]?.count ?? 0;
299+
purged += count;
300+
yield* Effect.yieldNow;
301+
yield* betweenPages;
302+
if (count < ROW_BATCH_SIZE) return;
303+
}
304+
});
287305
for (let cursor = ""; ;) {
288306
const threads = yield* sql<{ readonly thread_id: string }>`
307+
WITH RECURSIVE inherited(thread_id) AS (
308+
SELECT json_extract(fork.payload_json, '$.forkedFrom.threadId')
309+
FROM orchestration_v2_projection_threads AS fork
310+
WHERE json_extract(fork.payload_json, '$.forkedFrom.type') = 'run'
311+
AND json_extract(fork.payload_json, '$.lineage.sideChat') IS NOT 1
312+
AND (fork.deleted_at IS NULL OR fork.deleted_at > ${cutoff})
313+
UNION
314+
SELECT json_extract(fork.payload_json, '$.forkedFrom.threadId')
315+
FROM inherited
316+
JOIN orchestration_v2_projection_threads AS fork ON fork.thread_id = inherited.thread_id
317+
WHERE json_extract(fork.payload_json, '$.forkedFrom.type') = 'run'
318+
AND json_extract(fork.payload_json, '$.lineage.sideChat') IS NOT 1
319+
)
289320
SELECT thread_id FROM orchestration_v2_projection_threads
290321
WHERE deleted_at IS NOT NULL AND deleted_at <= ${cutoff} AND thread_id > ${cursor}
322+
AND thread_id NOT IN (SELECT thread_id FROM inherited WHERE thread_id IS NOT NULL)
291323
ORDER BY thread_id ASC
292324
LIMIT ${PAGE_SIZE}
293325
`.pipe(NodeSqliteClient.readOnly);
294326
const threadIds = threads.map((row) => row.thread_id);
295-
// Never hold a transaction or the reader lock across a yield or a rest.
296327
yield* Effect.yieldNow;
297328
if (threadIds.length > 0) {
298-
purged += yield* sql.withTransaction(
299-
Effect.gen(function* () {
300-
let rows = 0;
301-
const countChanges = Effect.gen(function* () {
302-
const changes = yield* sql<{ readonly count: number }>`SELECT changes() AS count`;
303-
rows += changes[0]?.count ?? 0;
304-
});
305-
for (const table of THREAD_CONTENT_TABLES) {
306-
yield* sql`
307-
DELETE FROM ${sql(table)}
329+
for (const table of THREAD_CONTENT_TABLES) {
330+
yield* deleteBatches(
331+
(limit) => sql`
332+
DELETE FROM ${sql(table)}
333+
WHERE rowid IN (
334+
SELECT rowid FROM ${sql(table)}
308335
WHERE thread_id IN ${sql.in(threadIds)}
309-
`;
310-
yield* countChanges;
311-
}
312-
yield* sql`
313-
DELETE FROM orchestration_v2_projection_provider_sessions AS session
314-
WHERE session.thread_id IN ${sql.in(threadIds)}
315-
AND NOT EXISTS (
316-
SELECT 1 FROM orchestration_v2_projection_provider_session_bindings AS binding
317-
WHERE binding.provider_session_id = session.provider_session_id
318-
AND binding.thread_id NOT IN ${sql.in(threadIds)}
336+
LIMIT ${limit}
319337
)
320-
`;
321-
yield* countChanges;
322-
yield* sql`
323-
DELETE FROM orchestration_v2_projection_provider_session_bindings
338+
`,
339+
);
340+
}
341+
yield* deleteBatches(
342+
(limit) => sql`
343+
DELETE FROM orchestration_v2_projection_provider_sessions
344+
WHERE rowid IN (
345+
SELECT session.rowid FROM orchestration_v2_projection_provider_sessions AS session
346+
WHERE session.thread_id IN ${sql.in(threadIds)}
347+
AND NOT EXISTS (
348+
SELECT 1 FROM orchestration_v2_projection_provider_session_bindings AS binding
349+
WHERE binding.provider_session_id = session.provider_session_id
350+
AND binding.thread_id NOT IN ${sql.in(threadIds)}
351+
)
352+
LIMIT ${limit}
353+
)
354+
`,
355+
);
356+
yield* deleteBatches(
357+
(limit) => sql`
358+
DELETE FROM orchestration_v2_projection_provider_session_bindings
359+
WHERE rowid IN (
360+
SELECT rowid FROM orchestration_v2_projection_provider_session_bindings
324361
WHERE thread_id IN ${sql.in(threadIds)}
325-
`;
326-
yield* countChanges;
327-
return rows;
328-
}),
362+
LIMIT ${limit}
363+
)
364+
`,
329365
);
330-
yield* Effect.yieldNow;
331366
}
332-
yield* betweenPages;
333367
if (threadIds.length < PAGE_SIZE) break;
334368
cursor = threadIds.at(-1) ?? cursor;
335369
}

0 commit comments

Comments
 (0)