Skip to content

Commit aee76a8

Browse files
authored
fix(server): route missing read-only queries to readers
## Problem Several read-only queries in projection reads, legacy shell reconciliation, and peer link lookups ran on the SQLite writer. This added avoidable work to the writer connection and could delay writes. ## Change Marked `getThreadProjection`, the independent legacy importer selectors, and peer link reads with the SQLite `readOnly` context marker. The full projection read uses one reader transaction so concurrent commits cannot mix its local and inherited history. Reads inside existing write transactions retain the writer and see uncommitted changes. Added file-backed routing coverage for reader selection and transaction visibility. ## Scope and approval This is a focused fix for missing read-only routing boundaries identified in the parent audit. It changes no SQL selection logic or pool configuration. Full projection reads now hold one consistent snapshot. ## Verification The file-backed tests recorded projection queries, importer selectors, and peer link lookups on reader connections. They also confirmed that reads inside write transactions see uncommitted projection, repair, shell, and link updates. A deterministic concurrent-commit regression failed before the projection transaction fix and passes afterward. Focused suites passed, with 90 tests. `vp run check:changed` passed, including 72 tests in its final run, and `git diff --check` passed. No startup time saving was measured. The existing shell snapshot query cost is unchanged. GPT 6.1 Sol, Codex
1 parent bd3cc5c commit aee76a8

8 files changed

Lines changed: 280 additions & 11 deletions

File tree

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

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,11 +31,14 @@ import {
3131
} from "@t3tools/contracts";
3232
import * as DateTime from "effect/DateTime";
3333
import * as Effect from "effect/Effect";
34+
import * as Deferred from "effect/Deferred";
35+
import * as Fiber from "effect/Fiber";
3436
import * as Layer from "effect/Layer";
3537
import * as Option from "effect/Option";
3638
import * as Schema from "effect/Schema";
3739
import * as Tracer from "effect/Tracer";
3840
import * as SqlClient from "effect/sql/SqlClient";
41+
import * as Statement from "effect/sql/Statement";
3942
import { projectThreadAwarenessV2 } from "@t3tools/shared/agentAwareness";
4043

4144
import * as SqlitePersistence from "../persistence/Sqlite.ts";
@@ -51,6 +54,8 @@ import {
5154
} from "./threadHistoryPaging.ts";
5255
import { limitRecoveryCommand } from "./UsageLimitRecoveryWorker.ts";
5356

57+
import { layerReaderDatabase, traceSqlRouting } from "../persistence/testkit/SqliteRouting.ts";
58+
5459
const layerTest = Layer.mergeAll(
5560
ProjectionStore.layer.pipe(Layer.provideMerge(SqlitePersistence.layerMemory)),
5661
SqlitePersistence.layerMemory,
@@ -226,6 +231,93 @@ const addRolledBackRecoveryCandidate = Effect.fn("addRolledBackRecoveryCandidate
226231
return threadId;
227232
});
228233

234+
it.effect("routes full projection reads to readers and retains uncommitted writer state", () =>
235+
Effect.gen(function* () {
236+
const store = yield* ProjectionStore.ProjectionStoreV2;
237+
const sql = yield* SqlClient.SqlClient;
238+
const parentId = yield* addRolledBackRecoveryCandidate("reader-routing");
239+
const parent = yield* store.getThreadProjection(parentId);
240+
const threadId = ThreadId.make("thread:reader-routing:fork");
241+
yield* store.apply({
242+
id: EventId.make("event:reader-routing:fork"),
243+
type: "thread.created",
244+
threadId,
245+
occurredAt: parent.thread.createdAt,
246+
payload: {
247+
...parent.thread,
248+
id: threadId,
249+
forkedFrom: { type: "run", threadId: parentId, runId: parent.runs[0]!.id },
250+
},
251+
});
252+
const { queries, trace } = traceSqlRouting();
253+
const projection = yield* store.getThreadProjection(threadId).pipe(trace);
254+
assert.equal(projection.thread.id, threadId);
255+
assert.deepEqual(
256+
projection.visibleTurnItems
257+
.filter((row) => row.visibility === "inherited")
258+
.map((row) => row.sourceItemId),
259+
parent.turnItems.map((item) => item.id),
260+
);
261+
assert.isAbove(queries.length, 1);
262+
assert.deepEqual([...new Set(queries.map((row) => row.connection))], ["reader"]);
263+
const commitRequested = yield* Deferred.make<void>();
264+
const writer = yield* Deferred.await(commitRequested).pipe(
265+
Effect.andThen(
266+
sql.withTransaction(
267+
Effect.gen(function* () {
268+
yield* sql`
269+
UPDATE orchestration_v2_projection_threads
270+
SET payload_json = json_set(payload_json, '$.title', 'Committed title')
271+
WHERE thread_id = ${threadId}
272+
`;
273+
yield* sql`
274+
UPDATE orchestration_v2_projection_turn_items
275+
SET payload_json = json_set(payload_json, '$.title', 'Committed command')
276+
WHERE thread_id = ${parentId}
277+
`;
278+
}),
279+
),
280+
),
281+
Effect.forkChild,
282+
);
283+
let commitInjected = false;
284+
const duringCommit = yield* store.getThreadProjection(threadId).pipe(
285+
Effect.provideService(Statement.CurrentTransformer, (statement) => {
286+
const [query] = statement.compile();
287+
if (!commitInjected && query.includes("FROM orchestration_v2_projection_turn_items")) {
288+
commitInjected = true;
289+
return Deferred.succeed(commitRequested, undefined).pipe(
290+
Effect.andThen(Fiber.join(writer).pipe(Effect.orDie)),
291+
Effect.as(statement),
292+
);
293+
}
294+
return Effect.succeed(statement);
295+
}),
296+
);
297+
assert.isTrue(commitInjected);
298+
assert.deepEqual(duringCommit, projection);
299+
const afterCommit = yield* store.getThreadProjection(threadId);
300+
assert.equal(afterCommit.thread.title, "Committed title");
301+
assert.equal(
302+
afterCommit.visibleTurnItems.find((row) => row.visibility === "inherited")?.item.title,
303+
"Committed command",
304+
);
305+
yield* sql.withTransaction(
306+
Effect.gen(function* () {
307+
yield* sql`
308+
UPDATE orchestration_v2_projection_threads
309+
SET payload_json = json_set(payload_json, '$.title', 'Uncommitted title')
310+
WHERE thread_id = ${threadId}
311+
`;
312+
assert.equal(
313+
(yield* store.getThreadProjection(threadId)).thread.title,
314+
"Uncommitted title",
315+
);
316+
}),
317+
);
318+
}).pipe(Effect.provide(ProjectionStore.layer.pipe(Layer.provideMerge(layerReaderDatabase)))),
319+
);
320+
229321
it.effect("record reads restrict provider histories to delivery identities", () =>
230322
Effect.gen(function* () {
231323
const store = yield* ProjectionStore.ProjectionStoreV2;

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

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4522,7 +4522,15 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
45224522
);
45234523

45244524
const getThreadProjection: ProjectionStoreV2Shape["getThreadProjection"] = (threadId) =>
4525-
readProjection(threadId, new Set());
4525+
sql
4526+
.withTransaction(readProjection(threadId, new Set()))
4527+
.pipe(
4528+
Effect.mapError((cause) =>
4529+
isProjectionStoreThreadNotFoundError(cause) || isProjectionStoreReadError(cause)
4530+
? cause
4531+
: new ProjectionStoreReadError({ threadId, cause }),
4532+
),
4533+
);
45264534

45274535
const getRuntimeRecoveryProjection: ProjectionStoreV2Shape["getRuntimeRecoveryProjection"] =
45284536
Effect.fn("ProjectionStore.getRuntimeRecoveryProjection")(
@@ -7252,7 +7260,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
72527260
getThreadsWithPullRequests: readOnReader(getThreadsWithPullRequests),
72537261
getThreadWorktreePaths: readOnReader(getThreadWorktreePaths),
72547262
getOpenRemoteDelegatedTasks: getOpenRemoteDelegatedTasks.pipe(NodeSqliteClient.readOnly),
7255-
getThreadProjection,
7263+
getThreadProjection: readOnReader(getThreadProjection),
72567264
getTurnStartContext: readOnReader(getTurnStartContext),
72577265
getTurnStartHistory: readOnReader(getTurnStartHistory),
72587266
getRuntimeRecoveryProjection: readOnReader(getRuntimeRecoveryProjection),

‎apps/server/src/orchestration-v2/legacy/LegacyV1ThreadImporter.test.ts‎

Lines changed: 69 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,15 +14,16 @@ import * as Layer from "effect/Layer";
1414
import * as SqlClient from "effect/sql/SqlClient";
1515
import * as Statement from "effect/sql/Statement";
1616

17-
import * as SqlitePersistence from "../../persistence/Sqlite.ts";
1817
import { listLinkedPullRequestThreads } from "../../pullRequest/linkedThreads.ts";
1918
import * as EventSink from "../EventSink.ts";
2019
import * as EventStore from "../EventStore.ts";
2120
import * as LegacyV1ThreadImporter from "./LegacyV1ThreadImporter.ts";
2221
import * as ProjectionReplay from "../testkit/ProjectionReplay.ts";
2322
import * as ProjectionStore from "../ProjectionStore.ts";
2423

25-
const layerDatabase = SqlitePersistence.layerMemory;
24+
import { layerReaderDatabase, traceSqlRouting } from "../../persistence/testkit/SqliteRouting.ts";
25+
26+
const layerDatabase = layerReaderDatabase;
2627
const layerEventStoreProvided = EventStore.layer.pipe(Layer.provideMerge(layerDatabase));
2728
const layerProjectionStoreProvided = ProjectionStore.layer.pipe(Layer.provideMerge(layerDatabase));
2829
const layerStoresProvided = Layer.mergeAll(
@@ -80,6 +81,72 @@ const seedPendingTranscripts = Effect.gen(function* () {
8081
yield* importer.reconcileShells;
8182
});
8283

84+
it.effect("routes shell selectors to readers while imports stay writable", () =>
85+
Effect.gen(function* () {
86+
const importer = yield* LegacyV1ThreadImporter.LegacyV1ThreadImporter;
87+
const sql = yield* SqlClient.SqlClient;
88+
const { queries, trace } = traceSqlRouting();
89+
yield* seedPendingTranscripts.pipe(trace);
90+
const selectors = queries.filter(
91+
({ query }) =>
92+
query.includes("FROM projection_threads AS thread") ||
93+
query.includes("FROM orchestration_v2_legacy_imports AS legacy_import") ||
94+
query.includes("FROM projection_thread_messages AS message"),
95+
);
96+
assert.lengthOf(selectors, 6);
97+
assert.deepEqual([...new Set(selectors.map((row) => row.connection))], ["reader"]);
98+
assert.deepEqual(yield* importer.reconcileShells, {
99+
importedThreadCount: 0,
100+
importedMessageCount: 0,
101+
});
102+
yield* sql.withTransaction(
103+
Effect.gen(function* () {
104+
yield* sql`
105+
INSERT INTO projection_threads (
106+
thread_id, project_id, title, model_selection_json, runtime_mode,
107+
interaction_mode, created_at, updated_at
108+
) VALUES (
109+
'thread:uncommitted', 'project:background', 'Uncommitted legacy title',
110+
'{"instanceId":"codex","model":"gpt-5.4"}', 'full-access', 'default',
111+
'2026-01-01T00:00:00.000Z', '2026-01-01T00:00:00.000Z'
112+
)
113+
`;
114+
yield* sql`
115+
INSERT INTO projection_thread_messages (
116+
message_id, thread_id, role, text, is_streaming, created_at, updated_at
117+
) VALUES (
118+
'message:uncommitted', 'thread:uncommitted', 'user', 'Uncommitted preview', 0,
119+
'2026-01-01T00:00:00.000Z', '2026-01-01T00:00:00.000Z'
120+
)
121+
`;
122+
yield* sql`
123+
UPDATE orchestration_v2_projection_threads
124+
SET payload_json = json_remove(payload_json, '$.pinnedAt')
125+
WHERE thread_id = 'thread:first'
126+
`;
127+
yield* sql`
128+
UPDATE projection_threads SET pinned_at = '2026-01-01T00:00:00.000Z'
129+
WHERE thread_id = 'thread:first'
130+
`;
131+
const result = yield* importer.reconcileShells;
132+
assert.deepEqual(result, { importedThreadCount: 2, importedMessageCount: 1 });
133+
const projections = yield* ProjectionStore.ProjectionStoreV2;
134+
const repaired = yield* projections.getThreadProjection(ThreadId.make("thread:first"));
135+
assert.equal(DateTime.formatIso(repaired.thread.pinnedAt!), "2026-01-01T00:00:00.000Z");
136+
const imported = yield* projections.getThreadProjection(
137+
ThreadId.make("thread:uncommitted"),
138+
);
139+
assert.equal(imported.messages[0]?.text, "Uncommitted preview");
140+
assert.equal(
141+
(yield* projections.getThreadProjection(ThreadId.make("thread:uncommitted"))).thread
142+
.title,
143+
"Uncommitted legacy title",
144+
);
145+
}),
146+
);
147+
}).pipe(Effect.provide(layerTest)),
148+
);
149+
83150
it.effect(
84151
"keeps hydrating later transcripts after a defect and leaves the failed thread pending",
85152
() =>

‎apps/server/src/orchestration-v2/legacy/LegacyV1ThreadImporter.ts‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -394,7 +394,7 @@ const make = Effect.gen(function* () {
394394
AND message.role IN ('user', 'assistant')
395395
ORDER BY message.created_at DESC, message.message_id DESC
396396
LIMIT 1
397-
`;
397+
`.pipe(NodeSqliteClient.readOnly);
398398
const latestUser = yield* sql<LegacyMessageRow>`
399399
SELECT
400400
message.message_id,
@@ -424,7 +424,7 @@ const make = Effect.gen(function* () {
424424
AND message.role = 'user'
425425
ORDER BY message.created_at DESC, message.message_id DESC
426426
LIMIT 1
427-
`;
427+
`.pipe(NodeSqliteClient.readOnly);
428428
return [latestUser[0], latest[0]].filter(
429429
(message, index, selected): message is LegacyMessageRow =>
430430
message !== undefined &&
@@ -476,7 +476,7 @@ const make = Effect.gen(function* () {
476476
OR json_type(projection.payload_json, '$.branchPullRequest') IS NULL
477477
OR json_type(projection.payload_json, '$.activeOrderKey') IS NULL
478478
ORDER BY thread.created_at ASC, thread.thread_id ASC
479-
`;
479+
`.pipe(NodeSqliteClient.readOnly);
480480
let repairedThreadCount = 0;
481481
for (const row of repairRows) {
482482
const decoded = decodeStoredThread(row.payload_json);
@@ -574,7 +574,7 @@ const make = Effect.gen(function* () {
574574
AND event.event_type = 'thread.created'
575575
)
576576
ORDER BY thread.created_at ASC, thread.thread_id ASC
577-
`;
577+
`.pipe(NodeSqliteClient.readOnly);
578578
let importedThreadCount = repairedThreadCount;
579579
let importedMessageCount = 0;
580580
for (const row of rows) {

‎apps/server/src/peer/PeerLinks.test.ts‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,8 @@ import {
3636
} from "./PeerLinks.testkit.ts";
3737
import * as PeerMcpClient from "./PeerMcpClient.ts";
3838

39+
import { layerReaderDatabase, traceSqlRouting } from "../persistence/testkit/SqliteRouting.ts";
40+
3941
/** What B's probe saw: the calling client and the modes it may start work with. */
4042
const Probe = Schema.Struct({ caller: Schema.String, modes: Schema.String });
4143

@@ -115,6 +117,47 @@ const serveApart = (descriptor: ExecutionEnvironmentDescriptor) =>
115117
Effect.flatMap((server) => serveB(descriptor).pipe(Effect.provideContext(server))),
116118
);
117119

120+
it.effect("routes peer reads to readers and sees uncommitted link updates", () =>
121+
Effect.scoped(
122+
Effect.gen(function* () {
123+
const context = yield* layerLinkingEnvironment(
124+
laptop,
125+
ServerSecretStore.layer,
126+
layerReaderDatabase,
127+
).pipe(Layer.build);
128+
const links = Context.get(context, PeerLinks.PeerLinks);
129+
const sql = Context.get(context, SqlClient.SqlClient);
130+
yield* sql`
131+
INSERT INTO peer_environment_links (
132+
environment_id, secret_name, label, urls_json, access, linked_at, expires_at, fenced
133+
) VALUES (
134+
${box.environmentId}, 'test-secret', 'Box', '[]', 'auto',
135+
'2026-01-01T00:00:00.000Z', '2026-01-02T00:00:00.000Z', 1
136+
)
137+
`;
138+
const { queries, trace } = traceSqlRouting();
139+
yield* links.list.pipe(trace);
140+
expect(Option.getOrThrow(yield* links.get(box.environmentId).pipe(trace)).label).toBe("Box");
141+
const reads = queries.filter(({ query }) =>
142+
query.includes("SELECT * FROM peer_environment_links"),
143+
);
144+
expect(reads).toHaveLength(2);
145+
expect(reads.map((row) => row.connection)).toEqual(["reader", "reader"]);
146+
yield* sql.withTransaction(
147+
Effect.gen(function* () {
148+
yield* sql`UPDATE peer_environment_links SET label = 'Uncommitted box'`;
149+
expect(Option.getOrThrow(yield* links.get(box.environmentId)).label).toBe(
150+
"Uncommitted box",
151+
);
152+
expect((yield* links.list)[0]?.label).toBe("Uncommitted box");
153+
}),
154+
);
155+
expect(yield* links.unlink(box.environmentId)).toBe(true);
156+
expect(yield* links.list).toEqual([]);
157+
}),
158+
),
159+
);
160+
118161
it.effect("links with a pairing code, and the peer holds its agents to the limits they carry", () =>
119162
Effect.scoped(
120163
Effect.gen(function* () {

‎apps/server/src/peer/PeerLinks.testkit.ts‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ import * as EnvironmentAuth from "../auth/EnvironmentAuth.ts";
2121
import * as AuthHttp from "../auth/http.ts";
2222
import * as McpOAuth from "../auth/McpOAuth.ts";
2323
import * as McpOAuthHttp from "../auth/mcpOAuthHttp.ts";
24+
import type * as SqlClient from "effect/sql/SqlClient";
2425
import * as ServerSecretStore from "../auth/ServerSecretStore.ts";
2526
import * as ServerConfig from "../config.ts";
2627
import * as ServerEnvironment from "../environment/ServerEnvironment.ts";
@@ -154,14 +155,18 @@ export type ServedPeer = Effect.Success<ReturnType<typeof servePeer>>;
154155
* The environment that links: its own database and secret store, reaching
155156
* peers over plain HTTP. Provide it to anything built on its links.
156157
*/
157-
export const layerLinkingEnvironment = (
158+
export const layerLinkingEnvironment = <E = never>(
158159
descriptor: ExecutionEnvironmentDescriptor,
159160
secrets: typeof ServerSecretStore.layer = ServerSecretStore.layer,
161+
database: Layer.Layer<
162+
SqlClient.SqlClient,
163+
E | Layer.Error<typeof Sqlite.layerMemory>
164+
> = Sqlite.layerMemory,
160165
) =>
161166
PeerMcpClient.layer.pipe(
162167
Layer.provideMerge(PeerLinks.layer),
163168
Layer.provideMerge(ServerSettings.layerTest({ linkedEnvironmentsEnabled: true })),
164-
Layer.provideMerge(Sqlite.layerMemory),
169+
Layer.provideMerge(database),
165170
Layer.provide(Layer.mergeAll(secrets, FetchHttpClient.layer)),
166171
// Services that act for this environment, such as delegation, name it too.
167172
Layer.provideMerge(

‎apps/server/src/peer/PeerLinks.ts‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@ import {
1212
type PeerLinkStatus,
1313
type PeerLinkSummary,
1414
} from "@t3tools/contracts";
15+
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
1516
import * as Context from "effect/Context";
1617
import * as Digest from "effect/crypto/Digest";
1718
import * as SecureRandom from "effect/crypto/SecureRandom";
@@ -222,12 +223,13 @@ const make = Effect.gen(function* () {
222223

223224
const readRows = sql<PeerLinkRow>`
224225
SELECT * FROM peer_environment_links ORDER BY label, environment_id
225-
`.pipe(Effect.catch(failStorage));
226+
`.pipe(NodeSqliteClient.readOnly, Effect.catch(failStorage));
226227

227228
const readRow = (environmentId: EnvironmentId) =>
228229
sql<PeerLinkRow>`
229230
SELECT * FROM peer_environment_links WHERE environment_id = ${environmentId}
230231
`.pipe(
232+
NodeSqliteClient.readOnly,
231233
Effect.catch(failStorage),
232234
Effect.map((rows) => Option.fromNullishOr(rows[0])),
233235
);

0 commit comments

Comments
 (0)