Skip to content
Source
Sign inSign up
pingdotgg/t3code
Open History to see the last commit

081_ProjectionMessageSearchText.ts

44 lines1.6 KB

1import * as Effect from "effect/Effect";
2import * as SqlClient from "effect/sql/SqlClient";
3​
4import {
5 readableSearchText,
6 recreateMessageSearchTriggers,
7} from "../../orchestration-v2/messageSearchIndex.ts";
8​
9export default Effect.gen(function* () {
10 const sql = yield* SqlClient.SqlClient;
11 yield* sql`ALTER TABLE orchestration_v2_projection_messages ADD COLUMN search_text TEXT`;
12​
13 // Migration 74's triggers index the stored text, where a quote's words are
14 // percent-encoded. Recreated now that search_text exists, they index it, and
15 // the backfill below re-indexes every message that reads differently.
16 yield* recreateMessageSearchTriggers(sql);
17​
18 // Only messages holding a citation link read differently. The raw check
19 // first skips parsing the JSON of every other message.
20 const pageSize = 500;
21 let afterMessageId = "";
22 while (true) {
23 const rows = yield* sql<{ readonly message_id: string; readonly text: string }>`
24 SELECT message_id, json_extract(payload_json, '$.text') AS text
25 FROM orchestration_v2_projection_messages
26 WHERE message_id > ${afterMessageId}
27 AND payload_json LIKE '%t3-citation://%'
28 AND json_type(payload_json, '$.text') = 'text'
29 ORDER BY message_id
30 LIMIT ${pageSize}
31 `;
32 for (const row of rows) {
33 const searchText = readableSearchText(row.text);
34 if (searchText === null) continue;
35 yield* sql`
36 UPDATE orchestration_v2_projection_messages
37 SET search_text = ${searchText}
38 WHERE message_id = ${row.message_id}
39 `;
40 }
41 if (rows.length < pageSize) break;
42 afterMessageId = rows.at(-1)!.message_id;
43 }
44});