@@ -135,7 +135,7 @@ const ThreadIdLookupInput = Schema.Struct({
135135 threadId : ThreadId ,
136136} ) ;
137137// Windowed reads order turns by the stable keyset (anchor, turn key), where
138- // anchor is COALESCE( requested_at, started_at, '') and turn key is
138+ // anchor is requested_at and turn key is
139139// COALESCE(turn_id, ''). Both are event-derived, so cursors survive the
140140// revert projector's row-id rewrite and full projection rebuilds.
141141const ThreadTurnWindowLookupInput = Schema . Struct ( {
@@ -1083,30 +1083,63 @@ const makeProjectionSnapshotQuery = Effect.gen(function* () {
10831083 // `userTurnLimit` user-anchored turns — turns whose pending message is a
10841084 // user message; subagent/fan-out turns between them ride along — or hits the
10851085 // `maxRawTurns` ceiling that bounds pathological fan-out. The `candidates`
1086- // CTE applies the keyset bound and LIMIT before the window functions run, so
1087- // a page over a huge thread scans at most `maxRawTurns` turns instead of
1088- // every older turn. The caller derives the continuation cursor from the
1089- // oldest returned row.
1086+ // CTE applies the keyset bound and LIMIT before the window functions run;
1087+ // its ORDER BY uses raw columns so the migration-037
1088+ // (thread_id, requested_at, turn_id) index serves both range and order with
1089+ // no temp B-tree — the scan is genuinely bounded by the LIMIT. (Raw
1090+ // turn_id DESC places NULLs exactly where COALESCE-to-'' would, below every
1091+ // real id.) The caller derives the continuation cursor from the oldest
1092+ // returned row.
1093+ // Highest thread-DETAIL event sequence for this thread that the projection
1094+ // has applied (bounded by the global snapshot sequence read in the same
1095+ // transaction). This is the thread-scoped watermark a windowed page carries
1096+ // so clients can defer merging until their live subscription has caught up;
1097+ // the global sequence is not waitable per-thread. The event_type filter
1098+ // must match ws.ts's isThreadDetailEvent exactly: the subscription only
1099+ // delivers these types, so a watermark counting any other event could
1100+ // never be reached by the client and would park the page forever. Served
1101+ // by the event store's (aggregate_kind, stream_id, sequence) index.
1102+ const getThreadEventWatermarkRow = SqlSchema . findOneOption ( {
1103+ Request : Schema . Struct ( { threadId : ThreadId , maxSequence : Schema . Number } ) ,
1104+ Result : Schema . Struct ( { threadSequence : Schema . NullOr ( Schema . Number ) } ) ,
1105+ execute : ( { threadId, maxSequence } ) =>
1106+ sql `
1107+ SELECT MAX(sequence) AS "threadSequence"
1108+ FROM orchestration_events
1109+ WHERE aggregate_kind = 'thread'
1110+ AND stream_id = ${ threadId }
1111+ AND sequence <= ${ maxSequence }
1112+ AND event_type IN (
1113+ 'thread.message-sent',
1114+ 'thread.proposed-plan-upserted',
1115+ 'thread.activity-appended',
1116+ 'thread.turn-diff-completed',
1117+ 'thread.reverted',
1118+ 'thread.session-set'
1119+ )
1120+ ` ,
1121+ } ) ;
1122+
10901123 const listTurnWindowRows = SqlSchema . findAll ( {
10911124 Request : ThreadTurnWindowLookupInput ,
10921125 Result : ProjectionTurnWindowRowSchema ,
10931126 execute : ( { threadId, beforeAnchorAt, beforeTurnKey, userTurnLimit, maxRawTurns } ) =>
10941127 sql `
10951128 WITH candidates AS (
10961129 SELECT
1097- COALESCE( turns.requested_at, turns.started_at, '') AS anchor_at,
1130+ turns.requested_at AS anchor_at,
10981131 COALESCE(turns.turn_id, '') AS turn_key,
10991132 turns.pending_message_id
11001133 FROM projection_turns AS turns
11011134 WHERE turns.thread_id = ${ threadId }
11021135 AND (
1103- COALESCE( turns.requested_at, turns.started_at, '') < ${ beforeAnchorAt }
1136+ turns.requested_at < ${ beforeAnchorAt }
11041137 OR (
1105- COALESCE( turns.requested_at, turns.started_at, '') = ${ beforeAnchorAt }
1138+ turns.requested_at = ${ beforeAnchorAt }
11061139 AND COALESCE(turns.turn_id, '') < ${ beforeTurnKey }
11071140 )
11081141 )
1109- ORDER BY anchor_at DESC, turn_key DESC
1142+ ORDER BY turns.requested_at DESC, turns.turn_id DESC
11101143 LIMIT ${ maxRawTurns }
11111144 ),
11121145 walked AS (
@@ -1161,16 +1194,16 @@ const makeProjectionSnapshotQuery = Effect.gen(function* () {
11611194 WHERE thread_id = ${ threadId }
11621195 AND turn_id IS NOT NULL
11631196 AND (
1164- COALESCE( requested_at, started_at, '') > ${ minAnchorAt }
1197+ requested_at > ${ minAnchorAt }
11651198 OR (
1166- COALESCE( requested_at, started_at, '') = ${ minAnchorAt }
1199+ requested_at = ${ minAnchorAt }
11671200 AND turn_id >= ${ minTurnKey }
11681201 )
11691202 )
11701203 AND (
1171- COALESCE( requested_at, started_at, '') < ${ beforeAnchorAt }
1204+ requested_at < ${ beforeAnchorAt }
11721205 OR (
1173- COALESCE( requested_at, started_at, '') = ${ beforeAnchorAt }
1206+ requested_at = ${ beforeAnchorAt }
11741207 AND turn_id < ${ beforeTurnKey }
11751208 )
11761209 )
@@ -1208,16 +1241,16 @@ const makeProjectionSnapshotQuery = Effect.gen(function* () {
12081241 WHERE thread_id = ${ threadId }
12091242 AND turn_id IS NOT NULL
12101243 AND (
1211- COALESCE( requested_at, started_at, '') > ${ minAnchorAt }
1244+ requested_at > ${ minAnchorAt }
12121245 OR (
1213- COALESCE( requested_at, started_at, '') = ${ minAnchorAt }
1246+ requested_at = ${ minAnchorAt }
12141247 AND turn_id >= ${ minTurnKey }
12151248 )
12161249 )
12171250 AND (
1218- COALESCE( requested_at, started_at, '') < ${ beforeAnchorAt }
1251+ requested_at < ${ beforeAnchorAt }
12191252 OR (
1220- COALESCE( requested_at, started_at, '') = ${ beforeAnchorAt }
1253+ requested_at = ${ beforeAnchorAt }
12211254 AND turn_id < ${ beforeTurnKey }
12221255 )
12231256 )
@@ -2558,6 +2591,21 @@ const makeProjectionSnapshotQuery = Effect.gen(function* () {
25582591 ) ) . length > 0 ;
25592592
25602593 const { snapshotSequence } = yield * getSnapshotSequence ( ) ;
2594+ const watermarkRow = yield * getThreadEventWatermarkRow ( {
2595+ threadId,
2596+ maxSequence : snapshotSequence ,
2597+ } ) . pipe (
2598+ Effect . mapError (
2599+ toPersistenceSqlOrDecodeError (
2600+ "ProjectionSnapshotQuery.getThreadDetailSnapshot:threadWatermark:query" ,
2601+ "ProjectionSnapshotQuery.getThreadDetailSnapshot:threadWatermark:decodeRow" ,
2602+ ) ,
2603+ ) ,
2604+ ) ;
2605+ const threadSequence = Option . match ( watermarkRow , {
2606+ onNone : ( ) => 0 ,
2607+ onSome : ( row ) => row . threadSequence ?? 0 ,
2608+ } ) ;
25612609 return Option . some ( {
25622610 snapshotSequence,
25632611 thread : thread . value ,
@@ -2572,6 +2620,7 @@ const makeProjectionSnapshotQuery = Effect.gen(function* () {
25722620 : null ,
25732621 hasMore,
25742622 snapshotSequence,
2623+ threadSequence,
25752624 } ,
25762625 } ) ;
25772626 } ) ,
0 commit comments