Open History to see the last commit
1import * as Effect from "effect/Effect";
2import * as Predicate from "effect/Predicate";
3import * as SqlClient from "effect/sql/SqlClient";
4
5import {
6 createMessageSearchTriggers,
7 indexedMessageCondition,
8 indexedMessageText,
9} from "../../orchestration-v2/messageSearchIndex.ts";
10
11/**
12 * Trigrams match 3+ character substrings without a scan, ignoring case and accents.
13 * Generated user-role notices would flood results and appear as "You".
14 * VACUUM can change rowids, so `t3 doctor` must rebuild this contentless index afterward.
15 * Index only finished messages, folding apostrophes as queries do; scan if the index is missing.
16 */
17export default Effect.gen(function* () {
18 const sql = yield* SqlClient.SqlClient;
19
20 // Older system SQLite builds may lack these FTS5 options; search can scan messages instead.
21 // Only SQLITE_ERROR signals unsupported options. Disk and I/O failures must fail the migration
22 // so the next startup retries creating the index.
23 const created = yield* sql`
24 CREATE VIRTUAL TABLE orchestration_v2_message_search USING fts5(
25 text,
26 content = '',
27 contentless_delete = 1,
28 tokenize = 'trigram remove_diacritics 1'
29 )
30 `.pipe(
31 Effect.as(true),
32 Effect.catchIf(
33 (cause) =>
34 Predicate.hasProperty(cause.reason.cause, "errcode") && cause.reason.cause.errcode === 1,
35 (cause) =>
36 Effect.logWarning(
37 "Message search index unavailable; search will scan messages.",
38 cause,
39 ).pipe(Effect.as(false)),
40 ),
41 );
42 if (!created) return;
43 yield* createMessageSearchTriggers(sql);
44 yield* sql`
45 INSERT INTO orchestration_v2_message_search (rowid, text)
46 SELECT messages.rowid, ${indexedMessageText(sql, "messages.payload_json")}
47 FROM orchestration_v2_projection_messages AS messages
48 WHERE ${indexedMessageCondition(sql)}
49 AND json_extract(messages.payload_json, '$.text') IS NOT NULL
50 `;
51});