@@ -34,8 +34,12 @@ export class ProjectionMaintenanceError extends Schema.TaggedErrorClass<Projecti
3434export 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