@@ -260,14 +260,16 @@ export const layer: Layer.Layer<
260260 COALESCE((SELECT MAX(sequence) FROM orchestration_events), 0) AS event_sequence,
261261 COALESCE((SELECT MAX(rowid) FROM orchestration_command_receipts), 0) AS receipt_row_id
262262 ` . pipe ( NodeSqliteClient . readOnly ) ;
263- let throughSequence = bounds [ 0 ] ?. event_sequence ?? 0 ;
263+ const initialHighWater = bounds [ 0 ] ?. event_sequence ?? 0 ;
264+ let throughSequence = initialHighWater ;
264265 let throughReceiptRowId = bounds [ 0 ] ?. receipt_row_id ?? 0 ;
265266 // Older doctor builds without migration 77 can compact only superseded history.
266267 const retentionCutoff = ( yield * hasReplayFloorTable )
267268 ? DateTime . formatIso (
268269 DateTime . subtractDuration ( yield * DateTime . now , EVENT_HISTORY_RETENTION ) ,
269270 )
270271 : "" ;
272+ const retainedPromptSequences = new Set < number > ( ) ;
271273 const newestStreamKeys = new Set < string > ( ) ;
272274 const retainedThreadIds = new Set < string > ( ) ;
273275 const retainedReadStateThreadIds = new Set < string > ( ) ;
@@ -287,6 +289,7 @@ export const layer: Layer.Layer<
287289 readonly imported_legacy_thread : number ;
288290 readonly expired : number ;
289291 readonly retention_anchor : number ;
292+ readonly prompt_sequence : number | null ;
290293 } > `
291294 SELECT
292295 event.sequence,
@@ -295,6 +298,9 @@ export const layer: Layer.Layer<
295298 event.stream_id,
296299 event.event_type,
297300 event.occurred_at < ${ retentionCutoff } AS expired,
301+ CASE WHEN event.event_type IN ('subagent.updated', 'turn-item.updated')
302+ THEN json_extract(event.payload_json, '$.promptSequence')
303+ ELSE NULL END AS prompt_sequence,
298304 CASE
299305 WHEN event.occurred_at >= ${ retentionCutoff } THEN 0
300306 WHEN event.command_id IS NOT NULL AND EXISTS (
@@ -309,10 +315,6 @@ export const layer: Layer.Layer<
309315 WHEN event.event_type = 'run.updated'
310316 AND json_extract(event.payload_json, '$.status') = 'running'
311317 THEN 1
312- WHEN event.event_type IN ('subagent.updated', 'turn-item.updated')
313- AND json_type(event.payload_json, '$.prompt') = 'text'
314- AND json_type(event.payload_json, '$.promptSequence') IS NULL
315- THEN 1
316318 ELSE 0
317319 END AS retention_anchor,
318320 CASE
@@ -355,6 +357,7 @@ export const layer: Layer.Layer<
355357 if (
356358 row . expired === 1 &&
357359 row . retention_anchor === 0 &&
360+ ! retainedPromptSequences . has ( row . sequence ) &&
358361 row . application_event_version === 2 &&
359362 row . aggregate_kind === "thread" &&
360363 ! isNewestInStream &&
@@ -365,6 +368,7 @@ export const layer: Layer.Layer<
365368 if ( ! replayFloors . has ( row . stream_id ) ) replayFloors . set ( row . stream_id , row . sequence ) ;
366369 continue ;
367370 }
371+ if ( row . prompt_sequence !== null ) retainedPromptSequences . add ( row . prompt_sequence ) ;
368372 if ( row . imported_legacy_thread === 1 ) {
369373 obsolete . push ( row . sequence ) ;
370374 } else if ( row . application_event_version === 2 ) {
@@ -412,7 +416,25 @@ export const layer: Layer.Layer<
412416 // Replay readers must see a deletion and its floor from the same commit.
413417 yield * sql . withTransaction (
414418 Effect . gen ( function * ( ) {
415- yield * sql `DELETE FROM orchestration_events WHERE sequence IN ${ sql . in ( obsolete ) } ` ;
419+ // A writer can add a prompt reference after the read page was released.
420+ const deleted = yield * sql < { sequence : number } > `
421+ DELETE FROM orchestration_events AS candidate
422+ WHERE sequence IN ${ sql . in ( obsolete ) }
423+ AND NOT (
424+ candidate.event_type IN ('subagent.updated', 'turn-item.updated')
425+ AND COALESCE(json_type(candidate.payload_json, '$.prompt') = 'text', 0)
426+ AND json_type(candidate.payload_json, '$.promptSequence') IS NULL
427+ AND EXISTS (
428+ SELECT 1 FROM orchestration_events AS reference
429+ WHERE reference.sequence > ${ initialHighWater }
430+ AND reference.stream_id = candidate.stream_id
431+ AND reference.event_type = candidate.event_type
432+ AND json_extract(reference.payload_json, '$.promptSequence') = candidate.sequence
433+ )
434+ )
435+ RETURNING sequence
436+ ` ;
437+ deletedEventCount += deleted . length ;
416438 for ( const [ threadId , floor ] of replayFloors ) {
417439 yield * sql `
418440 INSERT INTO orchestration_v2_event_retention (thread_id, replay_floor)
@@ -423,7 +445,7 @@ export const layer: Layer.Layer<
423445 }
424446 } ) ,
425447 ) ;
426- deletedEventCount += obsolete . length ;
448+
427449 yield * Effect . yieldNow ;
428450 }
429451 throughSequence =
0 commit comments