Skip to content

Commit c1d4a58

Browse files
navidnadaliclaude
andcommitted
perf(server): message ordinals are read through an index, not a scan
Ordering a thread's messages by their first user or assistant turn item grouped every such item by its message id through a temporary B-tree, so the read grew with the thread. Migration 80 adds a partial expression index on the thread, message id and ordinal of user and assistant items with a valid JSON payload, and the ordinal read gains the matching json_valid term. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 9e85538 commit c1d4a58

7 files changed

Lines changed: 364 additions & 0 deletions

File tree

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

Lines changed: 192 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1682,6 +1682,198 @@ const subagentRecordsSelectById = Effect.gen(function* () {
16821682
assert.deepEqual(yield* read([]), []);
16831683
});
16841684

1685+
const recordsAt = (second: number) => DateTime.makeUnsafe(Date.UTC(2026, 0, 1, 0, 0, second));
1686+
const recordsThreadCreated = (threadId: ThreadId) =>
1687+
({
1688+
id: EventId.make(`event:${threadId}:created`),
1689+
type: "thread.created",
1690+
threadId,
1691+
providerInstanceId,
1692+
occurredAt: recordsAt(0),
1693+
payload: {
1694+
createdBy: "user",
1695+
creationSource: "web",
1696+
id: threadId,
1697+
projectId: ProjectId.make("project:records"),
1698+
title: threadId,
1699+
providerInstanceId,
1700+
modelSelection,
1701+
runtimeMode: "full-access",
1702+
interactionMode: "default",
1703+
branch: null,
1704+
worktreePath: null,
1705+
activeProviderThreadId: null,
1706+
lineage: { parentThreadId: null, relationshipToParent: null, rootThreadId: threadId },
1707+
forkedFrom: null,
1708+
createdAt: recordsAt(0),
1709+
updatedAt: recordsAt(0),
1710+
archivedAt: null,
1711+
settledOverride: null,
1712+
settledAt: null,
1713+
lastVisitedAt: null,
1714+
deletedAt: null,
1715+
},
1716+
}) as never;
1717+
1718+
const queryPlan = Effect.fn("queryPlan")(function* (statement: string | undefined) {
1719+
assert.isDefined(statement);
1720+
const sql = yield* SqlClient.SqlClient;
1721+
const rows = yield* sql.unsafe<{ readonly detail: string }>(`EXPLAIN QUERY PLAN ${statement}`);
1722+
return rows.map((row) => row.detail);
1723+
});
1724+
1725+
// Each message's first item orders it, not the stored message order (the assistant reply is stored
1726+
// first); the message-ordinal statement reads those ordinals through migration 80's index.
1727+
const messageOrdinalsReadThroughTheirIndex = Effect.gen(function* () {
1728+
const store = yield* ProjectionStore.ProjectionStoreV2;
1729+
const sql = yield* SqlClient.SqlClient;
1730+
const threadId = ThreadId.make("thread:message-ordinals");
1731+
yield* store.apply(recordsThreadCreated(threadId));
1732+
const conversation = (name: string, firstOrdinal: number) => {
1733+
const runId = RunId.make(`run:${name}`);
1734+
const nodeId = NodeId.make(`node:${name}:root`);
1735+
const user = MessageId.make(`message:${name}:user`);
1736+
const assistant = MessageId.make(`message:${name}:assistant`);
1737+
const item = (index: number) => ({
1738+
id: TurnItemId.make(`item:${name}:${index}`),
1739+
threadId,
1740+
runId,
1741+
nodeId,
1742+
providerThreadId: null,
1743+
providerTurnId: null,
1744+
nativeItemRef: null,
1745+
parentItemId: null,
1746+
ordinal: firstOrdinal + index,
1747+
status: "completed",
1748+
title: null,
1749+
startedAt: recordsAt(2),
1750+
completedAt: recordsAt(2),
1751+
updatedAt: recordsAt(2),
1752+
});
1753+
const message = (id: MessageId, role: "user" | "assistant", second: number) => ({
1754+
id: EventId.make(`event:${id}`),
1755+
type: "message.updated",
1756+
threadId,
1757+
occurredAt: recordsAt(second),
1758+
payload: {
1759+
createdBy: role === "user" ? "user" : "agent",
1760+
creationSource: role === "user" ? "web" : "provider",
1761+
id,
1762+
threadId,
1763+
runId,
1764+
nodeId,
1765+
role,
1766+
text: `${name} ${role}`,
1767+
attachments: [],
1768+
streaming: false,
1769+
createdAt: recordsAt(second),
1770+
updatedAt: recordsAt(second),
1771+
},
1772+
});
1773+
const turnItem = (index: number, payload: object) => ({
1774+
id: EventId.make(`event:item:${name}:${index}`),
1775+
type: "turn-item.updated",
1776+
threadId,
1777+
occurredAt: recordsAt(2),
1778+
payload: { ...item(index), ...payload },
1779+
});
1780+
const events = [
1781+
message(assistant, "assistant", 1),
1782+
message(user, "user", 2),
1783+
turnItem(0, {
1784+
type: "user_message",
1785+
createdBy: "user",
1786+
creationSource: "web",
1787+
messageId: user,
1788+
inputIntent: "turn_start",
1789+
text: `${name} user`,
1790+
attachments: [],
1791+
}),
1792+
// A message streamed over two items keeps its first ordinal.
1793+
...[1, 2].map((index) =>
1794+
turnItem(index, {
1795+
type: "assistant_message",
1796+
messageId: assistant,
1797+
text: `${name} assistant ${index}`,
1798+
streaming: false,
1799+
}),
1800+
),
1801+
turnItem(3, {
1802+
type: "dynamic_tool",
1803+
toolName: "tool",
1804+
input: {},
1805+
output: { messageId: user },
1806+
}),
1807+
];
1808+
return { events, user, assistant };
1809+
};
1810+
const first = conversation("first", 1);
1811+
const second = conversation("second", 10);
1812+
for (const event of [...first.events, ...second.events]) yield* store.apply(event as never);
1813+
1814+
const { statements, tracer } = traceSqlStatements();
1815+
const indexed = yield* store
1816+
.getThreadRecords(threadId, ["messages"])
1817+
.pipe(Effect.withTracer(tracer));
1818+
assert.deepEqual(
1819+
yield* queryPlan(statements.find((statement) => statement.includes("MIN(ordinal) AS ordinal"))),
1820+
[
1821+
"SEARCH orchestration_v2_projection_turn_items USING INDEX orchestration_v2_projection_turn_items_message_ordinal_idx (thread_id=?)",
1822+
],
1823+
);
1824+
assert.deepEqual(
1825+
indexed.messages.map((message) => message.id),
1826+
[first.user, first.assistant, second.user, second.assistant],
1827+
);
1828+
// A narrow read (ids, runs or one role) recovers only its own messages' ordinals, through the
1829+
// same index; the statement it sends, rerun with its bindings, returns just those rows.
1830+
const narrowReads = [
1831+
{
1832+
filter: { messageIds: [second.assistant, first.user] },
1833+
loaded: [first.user, second.assistant],
1834+
},
1835+
{ filter: { messageRunIds: [RunId.make("run:first")] }, loaded: [first.user, first.assistant] },
1836+
{ filter: { messageRoles: ["user" as const] }, loaded: [first.user, second.user] },
1837+
];
1838+
for (const { filter, loaded } of narrowReads) {
1839+
const narrow = traceSqlStatements();
1840+
const narrowed = yield* store
1841+
.getThreadRecords(threadId, ["messages"], filter)
1842+
.pipe(Effect.withTracer(narrow.tracer));
1843+
assert.deepEqual(
1844+
narrowed.messages.map((message) => message.id),
1845+
loaded,
1846+
);
1847+
const bounded = narrow.statements.find((statement) =>
1848+
statement.includes("MIN(ordinal) AS ordinal"),
1849+
);
1850+
assert.include(bounded, "IN (SELECT value FROM json_each(");
1851+
assert.deepEqual(yield* queryPlan(bounded), [
1852+
"SEARCH orchestration_v2_projection_turn_items USING INDEX orchestration_v2_projection_turn_items_message_ordinal_idx (thread_id=? AND <expr>=?)",
1853+
"LIST SUBQUERY 1",
1854+
"SCAN json_each VIRTUAL TABLE INDEX 1:",
1855+
]);
1856+
const rows = yield* sql.unsafe<{ readonly message_id: string }>(bounded!, [
1857+
threadId,
1858+
JSON.stringify(loaded),
1859+
]);
1860+
assert.sameMembers(
1861+
rows.map((row) => row.message_id),
1862+
loaded,
1863+
);
1864+
}
1865+
yield* sql`DROP INDEX orchestration_v2_projection_turn_items_message_ordinal_idx`;
1866+
assert.deepEqual(
1867+
(yield* store.getThreadRecords(threadId, ["messages"])).messages,
1868+
indexed.messages,
1869+
);
1870+
});
1871+
1872+
// It drops the index, so it gets its own database.
1873+
it.effect("reads message ordinals through their index and orders messages as without it", () =>
1874+
messageOrdinalsReadThroughTheirIndex.pipe(Effect.provide(layerTest)),
1875+
);
1876+
16851877
const assertProjectScopedShells = Effect.fn("assertProjectScopedShells")(function* (
16861878
suffix: string,
16871879
) {

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3732,10 +3732,13 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
37323732
(filter.messageRoles.includes("user") && filter.messageRoles.includes("assistant")))
37333733
? undefined
37343734
: messages.map((message) => message.id);
3735+
// Migration 80 indexes exactly this expression, type list and json_valid term: changing
3736+
// any, even the IN order, silently drops the index. ProjectionStore.test.ts asserts the plan.
37353737
const rows = yield* sql<{ message_id: string; ordinal: number }>`
37363738
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
37373739
FROM orchestration_v2_projection_turn_items
37383740
WHERE thread_id = ${threadId} AND type IN ('user_message', 'assistant_message')
3741+
AND json_valid(payload_json)
37393742
${loadedMessageIds === undefined ? sql`` : sql`AND json_extract(payload_json, '$.messageId') IN (SELECT value FROM json_each(${encodeIdList(loadedMessageIds)}))`}
37403743
GROUP BY json_extract(payload_json, '$.messageId')
37413744
`;

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,7 @@ import Migration0077 from "./Migrations/077_RepairBooleanSettledOverride.ts";
8383
import Migration0078 from "./Migrations/078_DropOrchestrationEventCorrelationIndex.ts";
8484

8585
import Migration0079 from "./Migrations/079_DelegatedRecoveryIndexes.ts";
86+
import Migration0080 from "./Migrations/080_TurnItemMessageOrdinalIndex.ts";
8687

8788
const migrationEntries = [
8889
[1, "OrchestrationEvents", Migration0001],
@@ -165,6 +166,7 @@ const migrationEntries = [
165166
[77, "RepairBooleanSettledOverride", Migration0077],
166167
[78, "DropOrchestrationEventCorrelationIndex", Migration0078],
167168
[79, "DelegatedRecoveryIndexes", Migration0079],
169+
[80, "TurnItemMessageOrdinalIndex", Migration0080],
168170
] as const;
169171

170172
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 138 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,138 @@
1+
import { assert, it } from "@effect/vitest";
2+
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
3+
import * as Effect from "effect/Effect";
4+
import * as SqlClient from "effect/sql/SqlClient";
5+
6+
import { runMigrations } from "../Migrations.ts";
7+
import TurnItemMessageOrdinalIndex from "./080_TurnItemMessageOrdinalIndex.ts";
8+
9+
// The statement without the index's json_valid term, which the indexed one (below) must answer
10+
// the same over JSON payloads.
11+
const unindexedOrdinalsOf = (threadId: string) => `
12+
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
13+
FROM orchestration_v2_projection_turn_items NOT INDEXED
14+
WHERE thread_id = '${threadId}' AND type IN ('user_message', 'assistant_message')
15+
GROUP BY json_extract(payload_json, '$.messageId')
16+
ORDER BY message_id
17+
`;
18+
19+
const ordinalsOf = (threadId: string, from: string) => `
20+
SELECT json_extract(payload_json, '$.messageId') AS message_id, MIN(ordinal) AS ordinal
21+
FROM ${from}
22+
WHERE thread_id = '${threadId}' AND type IN ('user_message', 'assistant_message')
23+
AND json_valid(payload_json)
24+
GROUP BY json_extract(payload_json, '$.messageId')
25+
ORDER BY message_id
26+
`;
27+
28+
const insertTurnItem = (
29+
id: string,
30+
threadId: string,
31+
ordinal: number,
32+
type: string,
33+
payloadJson: string,
34+
) =>
35+
Effect.gen(function* () {
36+
const sql = yield* SqlClient.SqlClient;
37+
yield* sql`
38+
INSERT INTO orchestration_v2_projection_turn_items (
39+
turn_item_id, thread_id, run_id, ordinal, type, status, updated_at, payload_json
40+
)
41+
VALUES (
42+
${id}, ${threadId}, NULL, ${ordinal}, ${type}, 'completed',
43+
'2026-11-01T00:00:00.000Z', ${payloadJson}
44+
)
45+
`;
46+
});
47+
48+
const usesTheIndex = (query: string) =>
49+
Effect.gen(function* () {
50+
const sql = yield* SqlClient.SqlClient;
51+
const plan = yield* sql.unsafe<{ readonly detail: string }>(`EXPLAIN QUERY PLAN ${query}`);
52+
assert.include(
53+
plan[0]?.detail,
54+
"USING INDEX orchestration_v2_projection_turn_items_message_ordinal_idx",
55+
);
56+
});
57+
58+
it.effect(
59+
"adds the message-ordinal index once, re-runs cleanly and keeps the grouped ordinals",
60+
() =>
61+
Effect.gen(function* () {
62+
const sql = yield* SqlClient.SqlClient;
63+
yield* runMigrations({ toMigrationInclusive: 79 });
64+
for (const [id, threadId, ordinal, type, messageId] of [
65+
["a1", "thread-a", 1, "user_message", "m1"],
66+
["a2", "thread-a", 3, "assistant_message", "m2"],
67+
["a3", "thread-a", 2, "assistant_message", "m2"],
68+
["a4", "thread-a", 4, "dynamic_tool", "m3"],
69+
["a5", "thread-a", 5, "user_message", "m3"],
70+
["b1", "thread-b", 1, "assistant_message", "m1"],
71+
] as const) {
72+
yield* sql`
73+
INSERT INTO orchestration_v2_projection_turn_items (
74+
turn_item_id, thread_id, run_id, ordinal, type, status, updated_at, payload_json
75+
)
76+
VALUES (
77+
${id}, ${threadId}, NULL, ${ordinal}, ${type}, 'completed',
78+
'2026-11-01T00:00:00.000Z', ${JSON.stringify({ messageId })}
79+
)
80+
`;
81+
}
82+
const expected = [
83+
{ message_id: "m1", ordinal: 1 },
84+
{ message_id: "m2", ordinal: 2 },
85+
{ message_id: "m3", ordinal: 5 },
86+
];
87+
assert.deepEqual(yield* sql.unsafe(unindexedOrdinalsOf("thread-a")), expected);
88+
89+
assert.deepEqual(
90+
(yield* runMigrations()).map(([id]) => id),
91+
[80],
92+
);
93+
const index = () => sql<{ readonly sql: string }>`
94+
SELECT sql FROM sqlite_master
95+
WHERE type = 'index' AND name = 'orchestration_v2_projection_turn_items_message_ordinal_idx'
96+
`;
97+
const created = yield* index();
98+
assert.lengthOf(created, 1);
99+
yield* TurnItemMessageOrdinalIndex;
100+
assert.deepEqual(yield* index(), created);
101+
assert.deepEqual(yield* runMigrations(), []);
102+
103+
const indexed = "orchestration_v2_projection_turn_items";
104+
yield* usesTheIndex(ordinalsOf("thread-a", indexed));
105+
assert.deepEqual(yield* sql.unsafe(ordinalsOf("thread-a", indexed)), expected);
106+
assert.deepEqual(
107+
yield* sql.unsafe(ordinalsOf("thread-b", indexed)),
108+
yield* sql.unsafe(unindexedOrdinalsOf("thread-b")),
109+
);
110+
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
111+
);
112+
113+
// Without the index a payload that is not JSON fails no write; with it, it must fail none either.
114+
it.effect("migrates over a message whose payload is not JSON, and takes one after it", () =>
115+
Effect.gen(function* () {
116+
const sql = yield* SqlClient.SqlClient;
117+
yield* runMigrations({ toMigrationInclusive: 79 });
118+
yield* insertTurnItem("bad-1", "thread-a", 1, "assistant_message", "{not json");
119+
yield* insertTurnItem("ok-1", "thread-a", 2, "user_message", '{"messageId":"m1"}');
120+
121+
assert.deepEqual(
122+
(yield* runMigrations()).map(([id]) => id),
123+
[80],
124+
);
125+
yield* insertTurnItem("bad-2", "thread-a", 3, "user_message", "{not json");
126+
yield* insertTurnItem("ok-2", "thread-a", 4, "assistant_message", '{"messageId":"m1"}');
127+
yield* sql`
128+
UPDATE orchestration_v2_projection_turn_items SET payload_json = '{still not json'
129+
WHERE turn_item_id = 'ok-2'
130+
`;
131+
132+
const indexed = "orchestration_v2_projection_turn_items";
133+
yield* usesTheIndex(ordinalsOf("thread-a", indexed));
134+
assert.deepEqual(yield* sql.unsafe(ordinalsOf("thread-a", indexed)), [
135+
{ message_id: "m1", ordinal: 2 },
136+
]);
137+
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
138+
);
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/sql/SqlClient";
3+
4+
/**
5+
* A projection read groups a thread's message items by `$.messageId` for each message's first
6+
* ordinal; without this index it parses every message item's payload. The expression and the
7+
* WHERE must stay identical to that query's, or SQLite will not use the index. `json_valid` keeps
8+
* a payload that is not JSON out of it: json_extract would fail its write.
9+
*/
10+
export default Effect.gen(function* () {
11+
const sql = yield* SqlClient.SqlClient;
12+
13+
yield* sql`
14+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_turn_items_message_ordinal_idx
15+
ON orchestration_v2_projection_turn_items(
16+
thread_id, json_extract(payload_json, '$.messageId'), ordinal
17+
)
18+
WHERE type IN ('user_message', 'assistant_message') AND json_valid(payload_json)
19+
`;
20+
});

0 commit comments

Comments
 (0)