Skip to content

Commit c43eec4

Browse files
feat(orchestration): compact the full event store, not just visits
Extends startup compaction from thread.visited-only to the whole store. Projections are the working state (startup verifies, never replays), so events only serve afterSequence catch-up and disaster-recovery rebuild — for full-payload state events, everything but the newest per entity is dead weight in both. Passes: - superseded thread-state events (archive/settle/snooze/metadata/visit/ unread/...), keeping the newest per thread plus thread.created, which verify and replay ordering anchor on - superseded message.updated / node.updated per entity id (turn-item.updated is deliberately untouched: rebuild derives turn_item_positions from it with first-write-wins semantics) - legacy v1 thread events and pre-migration command receipts for threads whose v2 import completed Deletes run in paced 2k-row batches with yields so the synchronous sqlite driver never pins the event loop; each batch commits on its own, so a crash mid-way just leaves less for the next run. VACUUM is not run (it would block the connection for minutes on a multi-GB file); reclaimable free-page bytes are reported and startup logs a hint when substantial. Against the current 8.2GB state.sqlite this prunes ~2.1M rows: 113k superseded thread-state events, 1.02M v1 events (~3GB), and 1.01M legacy receipts (~400MB with indexes). Co-Authored-By: Claude Fable 5 <[email protected]>
1 parent 9952f0a commit c43eec4

3 files changed

Lines changed: 288 additions & 47 deletions

File tree

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

Lines changed: 105 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -265,49 +265,140 @@ it.layer(TestLayer)("orchestration V2 foundation persistence", (it) => {
265265
}),
266266
);
267267

268-
it.effect("compacts superseded thread.visited events, keeping the newest per thread", () =>
268+
it.effect("compacts superseded state events, imported v1 events, and legacy receipts", () =>
269269
Effect.gen(function* () {
270270
const eventSink = yield* EventSinkV2;
271271
const maintenance = yield* ProjectionMaintenanceV2;
272272
const sql = yield* SqlClient.SqlClient;
273273
const now = yield* DateTime.now;
274-
const threadId = ThreadId.make("thread:foundation-compact-visits");
274+
const nowIso = DateTime.formatIso(now);
275+
const threadId = ThreadId.make("thread:foundation-compact");
275276
const thread = makeThread(threadId, now);
276-
const visitedEvent = (suffix: string): OrchestrationV2DomainEvent => ({
277-
id: EventId.make(`event:foundation-compact-visits:${suffix}`),
278-
type: "thread.visited",
277+
const messageId = MessageId.make("message:foundation-compact");
278+
const threadStateEvent = (
279+
suffix: string,
280+
type: "thread.visited" | "thread.metadata-updated",
281+
): OrchestrationV2DomainEvent => ({
282+
id: EventId.make(`event:foundation-compact:${suffix}`),
283+
type,
279284
threadId,
280285
providerInstanceId,
281286
occurredAt: now,
282287
payload: { ...thread, lastVisitedAt: now },
283288
});
289+
const messageEvent = (suffix: string, text: string): OrchestrationV2DomainEvent => ({
290+
id: EventId.make(`event:foundation-compact:${suffix}`),
291+
type: "message.updated",
292+
threadId,
293+
occurredAt: now,
294+
payload: {
295+
createdBy: "user",
296+
creationSource: "web",
297+
id: messageId,
298+
threadId,
299+
runId: null,
300+
nodeId: null,
301+
role: "user",
302+
text,
303+
attachments: [],
304+
streaming: false,
305+
createdAt: now,
306+
updatedAt: now,
307+
},
308+
});
284309

285310
yield* eventSink.write({
286311
events: [
287-
threadCreatedEvent({ id: "event:foundation-compact-visits:create", thread, now }),
288-
visitedEvent("first"),
289-
visitedEvent("second"),
290-
visitedEvent("third"),
312+
threadCreatedEvent({ id: "event:foundation-compact:create", thread, now }),
313+
threadStateEvent("meta", "thread.metadata-updated"),
314+
threadStateEvent("visit-1", "thread.visited"),
315+
threadStateEvent("visit-2", "thread.visited"),
316+
messageEvent("message-1", "streaming"),
317+
messageEvent("message-2", "final"),
291318
],
292319
});
293320

294-
const summary = yield* maintenance.compactReadStateEvents;
295-
assert.isAtLeast(summary.deletedEventCount, 2);
321+
// A fully imported legacy thread: its v1 events and pre-migration
322+
// receipts are dead weight; a still-pending import keeps its rows.
323+
const importedV1ThreadId = "thread:foundation-compact-v1-imported";
324+
const pendingV1ThreadId = "thread:foundation-compact-v1-pending";
325+
const insertV1Event = (threadIdValue: string, version: number) => sql`
326+
INSERT INTO orchestration_events (
327+
event_id, aggregate_kind, stream_id, stream_version, event_type,
328+
occurred_at, actor_kind, payload_json, metadata_json, application_event_version
329+
)
330+
VALUES (
331+
${`event:v1:${threadIdValue}:${version}`}, 'thread', ${threadIdValue}, ${version},
332+
'thread.message-appended', ${nowIso}, 'user', '{}', '{}', 1
333+
)
334+
`;
335+
yield* insertV1Event(importedV1ThreadId, 1);
336+
yield* insertV1Event(importedV1ThreadId, 2);
337+
yield* insertV1Event(pendingV1ThreadId, 1);
338+
yield* sql`
339+
INSERT INTO orchestration_v2_legacy_imports (
340+
thread_id, source_updated_at, shell_imported_at, transcript_imported_at,
341+
imported_message_count, last_error
342+
)
343+
VALUES
344+
(${importedV1ThreadId}, ${nowIso}, ${nowIso}, ${nowIso}, 2, NULL),
345+
(${pendingV1ThreadId}, ${nowIso}, ${nowIso}, NULL, 0, NULL)
346+
ON CONFLICT(thread_id) DO NOTHING
347+
`;
348+
yield* sql`
349+
INSERT INTO orchestration_command_receipts (
350+
command_id, aggregate_kind, aggregate_id, accepted_at, result_sequence, status, command_type
351+
)
352+
VALUES
353+
('command:foundation-compact:legacy', 'thread', ${importedV1ThreadId}, ${nowIso}, 1, 'accepted', 'legacy'),
354+
('command:foundation-compact:pending', 'thread', ${pendingV1ThreadId}, ${nowIso}, 1, 'accepted', 'legacy')
355+
ON CONFLICT(command_id) DO NOTHING
356+
`;
357+
358+
const summary = yield* maintenance.compactEventStore;
359+
// meta + visit-1 (superseded thread state), message-1, and the two
360+
// imported v1 events.
361+
assert.isAtLeast(summary.deletedEventCount, 5);
362+
assert.isAtLeast(summary.deletedReceiptCount, 1);
296363

297364
const remaining = yield* sql<{ readonly event_id: string }>`
298365
SELECT event_id
299366
FROM orchestration_events
300367
WHERE aggregate_kind = 'thread'
301368
AND stream_id = ${threadId}
302-
AND event_type = 'thread.visited'
303369
ORDER BY sequence ASC
304370
`;
305371
assert.deepEqual(
306372
remaining.map((row) => row.event_id),
307-
["event:foundation-compact-visits:third"],
373+
[
374+
"event:foundation-compact:create",
375+
"event:foundation-compact:visit-2",
376+
"event:foundation-compact:message-2",
377+
],
378+
);
379+
380+
const remainingV1 = yield* sql<{ readonly stream_id: string }>`
381+
SELECT stream_id
382+
FROM orchestration_events
383+
WHERE application_event_version = 1
384+
AND stream_id IN (${importedV1ThreadId}, ${pendingV1ThreadId})
385+
`;
386+
assert.deepEqual(
387+
remainingV1.map((row) => row.stream_id),
388+
[pendingV1ThreadId],
389+
);
390+
391+
const remainingReceipts = yield* sql<{ readonly command_id: string }>`
392+
SELECT command_id
393+
FROM orchestration_command_receipts
394+
WHERE command_id IN ('command:foundation-compact:legacy', 'command:foundation-compact:pending')
395+
`;
396+
assert.deepEqual(
397+
remainingReceipts.map((row) => row.command_id),
398+
["command:foundation-compact:pending"],
308399
);
309400

310-
// Replay across the deletion gap must still produce a valid projection.
401+
// Replay across the deletion gaps must still produce a valid projection.
311402
assert.isTrue((yield* maintenance.verify).valid);
312403
assert.isTrue((yield* maintenance.rebuild).valid);
313404
}),

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

Lines changed: 165 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -34,8 +34,12 @@ export class ProjectionMaintenanceError extends Schema.TaggedErrorClass<Projecti
3434
export interface ProjectionMaintenanceV2Shape {
3535
readonly verify: Effect.Effect<ProjectionVerificationV2, ProjectionMaintenanceError>;
3636
readonly rebuild: Effect.Effect<ProjectionVerificationV2, ProjectionMaintenanceError>;
37-
readonly compactReadStateEvents: Effect.Effect<
38-
{ readonly deletedEventCount: number },
37+
readonly compactEventStore: Effect.Effect<
38+
{
39+
readonly deletedEventCount: number;
40+
readonly deletedReceiptCount: number;
41+
readonly reclaimableBytes: number;
42+
},
3943
ProjectionMaintenanceError
4044
>;
4145
}
@@ -214,37 +218,171 @@ export const layer: Layer.Layer<
214218
Effect.mapError((cause) => new ProjectionMaintenanceError({ operation, cause })),
215219
);
216220

221+
// Thread-state events whose payload is the complete thread: only the
222+
// newest per thread can influence a replay or an afterSequence catch-up.
223+
// thread.created stays out — verify derives the expected thread set from
224+
// it, and it anchors replay ordering.
225+
const SUPERSEDABLE_THREAD_EVENT_TYPES = [
226+
"thread.archived",
227+
"thread.unarchived",
228+
"thread.deleted",
229+
"thread.settled",
230+
"thread.unsettled",
231+
"thread.snoozed",
232+
"thread.unsnoozed",
233+
"thread.metadata-updated",
234+
"thread.runtime-mode-updated",
235+
"thread.interaction-mode-updated",
236+
"thread.model-selection-updated",
237+
"thread.provider-switched",
238+
"thread.visited",
239+
"thread.marked-unread",
240+
];
241+
242+
const chunksOf = <A>(items: ReadonlyArray<A>, size: number): Array<ReadonlyArray<A>> => {
243+
const out: Array<ReadonlyArray<A>> = [];
244+
for (let index = 0; index < items.length; index += size) {
245+
out.push(items.slice(index, index + size));
246+
}
247+
return out;
248+
};
249+
250+
// The sqlite driver is synchronous: one statement over millions of rows
251+
// would pin the event loop for its whole duration. Small auto-committed
252+
// batches with a yield between them keep the server responsive and bound
253+
// WAL growth; each batch is independently durable, so a crash mid-way
254+
// just leaves less garbage for the next run.
255+
const COMPACTION_DELETE_BATCH_SIZE = 2_000;
256+
const deleteRowsBatched = (input: {
257+
readonly table: "orchestration_events" | "orchestration_command_receipts";
258+
readonly keys: ReadonlyArray<number | string>;
259+
}) =>
260+
Effect.gen(function* () {
261+
let deleted = 0;
262+
for (const batch of chunksOf(input.keys, COMPACTION_DELETE_BATCH_SIZE)) {
263+
if (input.table === "orchestration_events") {
264+
yield* sql`DELETE FROM orchestration_events WHERE sequence IN ${sql.in(batch)}`;
265+
} else {
266+
yield* sql`DELETE FROM orchestration_command_receipts WHERE command_id IN ${sql.in(batch)}`;
267+
}
268+
deleted += batch.length;
269+
yield* Effect.yieldNow;
270+
}
271+
return deleted;
272+
});
273+
217274
/**
218-
* thread.visited events carry the full thread payload, so only the newest
219-
* one per thread can influence a replay or an afterSequence catch-up; the
220-
* rest are dead weight. Visits fire on every activity bump while a thread
221-
* is open, so without compaction they dominate the event store.
275+
* Full event-store compaction. Projections are the working state (startup
276+
* verifies rather than replays), so events only serve afterSequence
277+
* catch-up and disaster-recovery rebuild — and for full-payload "state of
278+
* the entity" events, anything but the newest per entity is dead weight
279+
* in both. Three passes:
280+
*
281+
* 1. Superseded thread-state events (visits alone accumulate at multiple
282+
* per minute while a thread is open).
283+
* 2. Superseded message.updated / node.updated events per entity id —
284+
* streaming rewrites the same message many times. turn-item.updated is
285+
* deliberately NOT compacted: rebuild derives turn_item_positions from
286+
* those events with first-write-wins semantics, so keep-latest would
287+
* change replay outcomes for reordered items.
288+
* 3. Legacy v1 thread events and their pre-migration command receipts for
289+
* threads whose v2 import completed — the v1 store is only read to
290+
* import from, and imports never re-run once transcript_imported_at is
291+
* set.
292+
*
293+
* VACUUM is deliberately not run here: on the synchronous driver it would
294+
* block every query for minutes on a multi-GB file. The reclaimable page
295+
* count is reported so the caller can surface a hint instead; freed pages
296+
* are reused, so the file stops growing either way.
222297
*/
223-
const compactReadStateEvents = sql.withTransaction(
224-
Effect.gen(function* () {
225-
yield* sql`
226-
DELETE FROM orchestration_events
227-
WHERE application_event_version = 2
228-
AND aggregate_kind = 'thread'
229-
AND event_type = 'thread.visited'
230-
AND sequence NOT IN (
231-
SELECT MAX(sequence)
232-
FROM orchestration_events
233-
WHERE application_event_version = 2
234-
AND aggregate_kind = 'thread'
235-
AND event_type = 'thread.visited'
236-
GROUP BY stream_id
237-
)
238-
`;
239-
const rows = yield* sql<{ readonly deleted: number }>`SELECT changes() AS deleted`;
240-
return { deletedEventCount: rows[0]?.deleted ?? 0 };
241-
}),
242-
);
298+
const compactEventStore = Effect.gen(function* () {
299+
const supersededThreadStateRows = yield* sql<{ readonly sequence: number }>`
300+
SELECT sequence
301+
FROM orchestration_events
302+
WHERE application_event_version = 2
303+
AND aggregate_kind = 'thread'
304+
AND event_type IN ${sql.in(SUPERSEDABLE_THREAD_EVENT_TYPES)}
305+
AND sequence NOT IN (
306+
SELECT MAX(sequence)
307+
FROM orchestration_events
308+
WHERE application_event_version = 2
309+
AND aggregate_kind = 'thread'
310+
AND event_type IN ${sql.in(SUPERSEDABLE_THREAD_EVENT_TYPES)}
311+
GROUP BY stream_id
312+
)
313+
`;
314+
315+
const supersededEntityRows = (eventType: "message.updated" | "node.updated") => sql<{
316+
readonly sequence: number;
317+
}>`
318+
SELECT sequence
319+
FROM orchestration_events
320+
WHERE application_event_version = 2
321+
AND event_type = ${eventType}
322+
AND sequence NOT IN (
323+
SELECT MAX(sequence)
324+
FROM orchestration_events
325+
WHERE application_event_version = 2
326+
AND event_type = ${eventType}
327+
GROUP BY stream_id, json_extract(payload_json, '$.id')
328+
)
329+
`;
330+
const supersededMessageRows = yield* supersededEntityRows("message.updated");
331+
const supersededNodeRows = yield* supersededEntityRows("node.updated");
332+
333+
const importedLegacyEventRows = yield* sql<{ readonly sequence: number }>`
334+
SELECT sequence
335+
FROM orchestration_events
336+
WHERE application_event_version = 1
337+
AND aggregate_kind = 'thread'
338+
AND stream_id IN (
339+
SELECT thread_id
340+
FROM orchestration_v2_legacy_imports
341+
WHERE transcript_imported_at IS NOT NULL
342+
)
343+
`;
344+
345+
const deletedEventCount = yield* deleteRowsBatched({
346+
table: "orchestration_events",
347+
keys: [
348+
...supersededThreadStateRows,
349+
...supersededMessageRows,
350+
...supersededNodeRows,
351+
...importedLegacyEventRows,
352+
].map((row) => row.sequence),
353+
});
354+
355+
// Receipts written before the command_type column existed default to
356+
// 'legacy'; for fully imported v1 threads they guard idempotency of
357+
// commands that can no longer be re-sent.
358+
const legacyReceiptRows = yield* sql<{ readonly command_id: string }>`
359+
SELECT command_id
360+
FROM orchestration_command_receipts
361+
WHERE command_type = 'legacy'
362+
AND aggregate_kind = 'thread'
363+
AND aggregate_id IN (
364+
SELECT thread_id
365+
FROM orchestration_v2_legacy_imports
366+
WHERE transcript_imported_at IS NOT NULL
367+
)
368+
`;
369+
const deletedReceiptCount = yield* deleteRowsBatched({
370+
table: "orchestration_command_receipts",
371+
keys: legacyReceiptRows.map((row) => row.command_id),
372+
});
373+
374+
const freelistRows = yield* sql<{ readonly freelist_count: number }>`PRAGMA freelist_count`;
375+
const pageSizeRows = yield* sql<{ readonly page_size: number }>`PRAGMA page_size`;
376+
const reclaimableBytes =
377+
(freelistRows[0]?.freelist_count ?? 0) * (pageSizeRows[0]?.page_size ?? 0);
378+
379+
return { deletedEventCount, deletedReceiptCount, reclaimableBytes };
380+
});
243381

244382
return ProjectionMaintenanceV2.of({
245383
verify: mapError("verify")(verify),
246384
rebuild: mapError("rebuild")(rebuild),
247-
compactReadStateEvents: mapError("compact read-state events")(compactReadStateEvents),
385+
compactEventStore: mapError("compact event store")(compactEventStore),
248386
});
249387
}),
250388
);

0 commit comments

Comments
 (0)