Skip to content

Commit 9bc5966

Browse files
t3dotggclaude
andcommitted
fix: thread-scoped page watermark and index-served keyset scan
Two blockers from external review: - Pages now carry threadSequence, the highest thread-detail event sequence applied at read time (filtered to the exact event types the subscription delivers, so the watermark is always reachable). A page read ahead of the client's live state parks until events catch up, closing the race where a streaming turn outside the loaded window had its deltas replayed on top of page content that already included them, duplicating text. Pages from pre-watermark servers merge immediately (old behavior). - The window query's candidates CTE now orders by raw (requested_at, turn_id) — requested_at is NOT NULL by schema — and migration 037 adds a (thread_id, requested_at, turn_id) index, so the keyset range and order are both index-served with no temp B-tree: the scan is genuinely bounded by the page LIMIT. Keyset comparisons keep COALESCE only on the turn_id tiebreak, which does not affect index use. Co-Authored-By: Claude Fable 5 <[email protected]>
1 parent 9d8dfcc commit 9bc5966

6 files changed

Lines changed: 201 additions & 17 deletions

File tree

‎apps/server/src/orchestration/Layers/ProjectionSnapshotQuery.ts‎

Lines changed: 66 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -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.
141141
const 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
}),

‎apps/server/src/persistence/Migrations.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,7 @@ import Migration0033 from "./Migrations/033_ProjectionThreadsSettled.ts";
4949
import Migration0034 from "./Migrations/034_ProjectionThreadsSnoozed.ts";
5050
import Migration0035 from "./Migrations/035_ProjectionThreadTitleRegeneration.ts";
5151
import Migration0036 from "./Migrations/036_ProjectionThreadsPinned.ts";
52+
import Migration0037 from "./Migrations/037_ProjectionTurnsKeysetIndex.ts";
5253

5354
/**
5455
* Migration loader with all migrations defined inline.
@@ -97,6 +98,7 @@ export const migrationEntries = [
9798
[34, "ProjectionThreadsSnoozed", Migration0034],
9899
[35, "ProjectionThreadTitleRegeneration", Migration0035],
99100
[36, "ProjectionThreadsPinned", Migration0036],
101+
[37, "ProjectionTurnsKeysetIndex", Migration0037],
100102
] as const;
101103

102104
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/unstable/sql/SqlClient";
3+
4+
/**
5+
* Composite index for windowed thread detail reads. Pagination orders turns by
6+
* the stable keyset (requested_at, turn_id); the pre-existing
7+
* (thread_id, requested_at) index cannot serve the tiebreak order, forcing a
8+
* temp B-tree over all of a thread's turns before the page LIMIT applies.
9+
* With this index the candidates scan is genuinely bounded by the page size.
10+
*/
11+
export default Effect.gen(function* () {
12+
const sql = yield* SqlClient.SqlClient;
13+
yield* sql`
14+
CREATE INDEX IF NOT EXISTS idx_projection_turns_thread_keyset
15+
ON projection_turns(thread_id, requested_at, turn_id)
16+
`;
17+
});

‎packages/client-runtime/src/state/threads-pagination.test.ts‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,6 +241,27 @@ const hasMessage = (state: EnvironmentThreadState, id: string): boolean =>
241241
onSome: (thread) => thread.messages.some((entry) => entry.id === id),
242242
});
243243

244+
const titleEvent = (title: string, sequence: number): OrchestrationThreadStreamItem => ({
245+
kind: "event",
246+
event: {
247+
eventId: EventId.make(`event-title-${sequence}`),
248+
sequence,
249+
occurredAt: "2026-04-01T01:30:00.000Z",
250+
commandId: null,
251+
causationEventId: null,
252+
correlationId: null,
253+
metadata: {},
254+
aggregateKind: "thread",
255+
aggregateId: THREAD_ID,
256+
type: "thread.meta-updated",
257+
payload: {
258+
threadId: THREAD_ID,
259+
title,
260+
updatedAt: "2026-04-01T01:30:00.000Z",
261+
},
262+
},
263+
});
264+
244265
// Reverting to turnCount 1 retains only turns whose checkpoint count is <= 1:
245266
// turn-1 survives, turn-2 (the loaded window's newest turn) is discarded.
246267
const revertEvent = (sequence: number): OrchestrationThreadStreamItem => ({
@@ -426,6 +447,37 @@ describe("thread pagination state", () => {
426447
}),
427448
);
428449

450+
it.effect("parks a page read ahead of the live state until events catch up", () =>
451+
Effect.gen(function* () {
452+
// A page whose thread watermark is ahead of the loaded state may
453+
// contain streaming content the subscription has not delivered yet
454+
// (e.g. an out-of-window subagent turn mid-stream); merging it
455+
// immediately and then replaying those deltas would duplicate text.
456+
// The page parks until the live state reaches the watermark.
457+
const harness = yield* makeHarness({ initialResponse: Option.some(WINDOWED_SNAPSHOT) });
458+
yield* harness.awaitState((value) => Option.isSome(value.page));
459+
460+
requestOlderThreadTurns(TARGET.environmentId, THREAD_ID);
461+
yield* harness.awaitState((value) =>
462+
Option.match(value.page, { onNone: () => false, onSome: (page) => page.loadingOlder }),
463+
);
464+
// Page watermark 11 > loaded sequence 10: must park, not merge.
465+
yield* harness.resolveNextPage(
466+
Option.some({
467+
...OLDER_PAGE,
468+
snapshotSequence: 11,
469+
page: { beforeCursor: null, hasMore: false, snapshotSequence: 11, threadSequence: 11 },
470+
}),
471+
);
472+
473+
// A live event at sequence 11 arrives; only then does the page merge.
474+
yield* Queue.offer(harness.inputs, titleEvent("Advanced past watermark", 11));
475+
const state = yield* harness.awaitState((value) => hasMessage(value, "message-old"));
476+
expect(hasMessage(state, "message-recent")).toBe(true);
477+
expect(Option.getOrThrow(state.page).loadingOlder).toBe(false);
478+
}),
479+
);
480+
429481
it.effect("a revert keeps the page cursor and triggers no refresh fetch", () =>
430482
Effect.gen(function* () {
431483
// Cursors are an (anchor, turnId) keyset derived from event content, so

‎packages/client-runtime/src/state/threads.ts‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -178,6 +178,13 @@ export const makeEnvironmentThreadState = Effect.fn("EnvironmentThreadState.make
178178
// from the session config. Gates loadOlderTurns so a reconnect to a
179179
// pre-pagination server never sends unsupported window parameters.
180180
const paginationSupported = yield* Ref.make(false);
181+
// An older page whose thread watermark is ahead of the live state, parked
182+
// until the subscription catches up (see mergeOlderPage's caller). At most
183+
// one can exist because loadOlderTurns no-ops while loadingOlder is true.
184+
const pendingOlderPage = yield* Ref.make<{
185+
readonly snapshot: OrchestrationThreadDetailSnapshot;
186+
readonly epoch: number;
187+
} | null>(null);
181188
const persistence = yield* Queue.sliding<OrchestrationThreadDetailSnapshot>(1);
182189

183190
const persist = Effect.fn("EnvironmentThreadState.persist")(function* (
@@ -359,8 +366,39 @@ export const makeEnvironmentThreadState = Effect.fn("EnvironmentThreadState.make
359366
} else if (result.kind === "deleted") {
360367
yield* setDeleted();
361368
}
369+
// The event may have advanced the live state past a parked page's
370+
// watermark; merge it as soon as that happens.
371+
yield* tryMergePendingOlderPage();
362372
});
363373

374+
// Merges a parked older page once the live state has caught up to the
375+
// page's thread watermark, or discards it if history was rewritten
376+
// (epoch advanced) while it waited. Must run under applyLock.
377+
const tryMergePendingOlderPage = Effect.fn("EnvironmentThreadState.tryMergePendingOlderPage")(
378+
function* () {
379+
const pending = yield* Ref.get(pendingOlderPage);
380+
if (pending === null) {
381+
return;
382+
}
383+
const epochNow = yield* Ref.get(historyEpoch);
384+
if (epochNow !== pending.epoch) {
385+
yield* Ref.set(pendingOlderPage, null);
386+
yield* SubscriptionRef.update(state, (value) => ({
387+
...value,
388+
page: Option.map(value.page, (existing) => ({ ...existing, loadingOlder: false })),
389+
}));
390+
return;
391+
}
392+
const watermark = pending.snapshot.page?.threadSequence;
393+
const loadedSequence = yield* SubscriptionRef.get(lastSequence);
394+
if (watermark !== undefined && watermark > loadedSequence) {
395+
return;
396+
}
397+
yield* Ref.set(pendingOlderPage, null);
398+
yield* mergeOlderPage(pending.snapshot);
399+
},
400+
);
401+
364402
const applyItem = Effect.fn("EnvironmentThreadState.applyItem")(function* (
365403
item: OrchestrationThreadStreamItem,
366404
) {
@@ -472,6 +510,22 @@ export const makeEnvironmentThreadState = Effect.fn("EnvironmentThreadState.make
472510
}));
473511
return;
474512
}
513+
// A page read AHEAD of the live state may include content (e.g.
514+
// streaming deltas of an out-of-window turn) the subscription has
515+
// not delivered yet; merging now and then replaying those events
516+
// would duplicate them. Park the page until the live state reaches
517+
// the page's thread-scoped watermark; loadingOlder stays true so
518+
// the UI shows progress and no second fetch starts. Pages from
519+
// pre-watermark servers (threadSequence absent) merge immediately,
520+
// preserving the old behavior.
521+
const watermark = response.value.page?.threadSequence;
522+
if (watermark !== undefined && watermark > loadedSequence) {
523+
yield* Ref.set(pendingOlderPage, {
524+
snapshot: response.value,
525+
epoch: epochNow,
526+
});
527+
return;
528+
}
475529
yield* mergeOlderPage(response.value);
476530
}),
477531
);

‎packages/contracts/src/orchestration.ts‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -563,6 +563,16 @@ export const OrchestrationThreadDetailPage = Schema.Struct({
563563
beforeCursor: Schema.NullOr(TrimmedNonEmptyString),
564564
hasMore: Schema.Boolean,
565565
snapshotSequence: NonNegativeInt,
566+
/**
567+
* Highest event sequence applied to THIS thread at page read time. The
568+
* global `snapshotSequence` advances with every thread's events, so a
569+
* client cannot wait for it via its per-thread subscription; this
570+
* thread-scoped watermark is reachable. A client merging an older page
571+
* must first have applied live events up to it — otherwise a streaming
572+
* turn outside the loaded window could have deltas replayed on top of
573+
* page content that already includes them, duplicating text.
574+
*/
575+
threadSequence: Schema.optionalKey(NonNegativeInt),
566576
});
567577
export type OrchestrationThreadDetailPage = typeof OrchestrationThreadDetailPage.Type;
568578

0 commit comments

Comments
 (0)