The best way to control Claude Code, Codex and more from any computer or phone

main
ts 112 lines 4.75 KB
Raw
1 import * as Effect from "effect/Effect";
2 import * as SqlClient from "effect/sql/SqlClient";
3
4 /**
5 * One total per thread beside 085's per-run counts, so a shell read does not sum every run the
6 * thread has had. It holds the items without a run or with an existing run, the rows the shell
7 * read used to sum; the read still applies the run status rules. Triggers keep it exact.
8 */
9 export default Effect.gen(function* () {
10 const sql = yield* SqlClient.SqlClient;
11
12 yield* sql`
13 CREATE TABLE orchestration_v2_projection_thread_item_counts (
14 thread_id TEXT PRIMARY KEY,
15 item_count INTEGER NOT NULL
16 ) WITHOUT ROWID
17 `;
18 yield* sql`
19 INSERT INTO orchestration_v2_projection_thread_item_counts (thread_id, item_count)
20 SELECT counts.thread_id, SUM(counts.item_count)
21 FROM orchestration_v2_projection_turn_item_counts counts
22 WHERE counts.run_key = ''
23 OR EXISTS (SELECT 1 FROM orchestration_v2_projection_runs WHERE run_id = counts.run_key)
24 GROUP BY counts.thread_id
25 `;
26
27 // The shell read subtracts only these runs. ProjectionStore.selectShellItemCounts must spell
28 // the same predicate for SQLite to use this partial index.
29 yield* sql`
30 CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_hidden_items_idx
31 ON orchestration_v2_projection_runs(thread_id, status, run_id)
32 WHERE status IN ('rolled_back', 'queued', 'cancelled')
33 `;
34
35 // 085's triggers upsert per-run counts, so an item write fires the insert or the update here.
36 yield* sql`
37 CREATE TRIGGER orchestration_v2_thread_item_counts_count_insert
38 AFTER INSERT ON orchestration_v2_projection_turn_item_counts
39 WHEN new.run_key = ''
40 OR EXISTS (SELECT 1 FROM orchestration_v2_projection_runs WHERE run_id = new.run_key)
41 BEGIN
42 INSERT INTO orchestration_v2_projection_thread_item_counts (thread_id, item_count)
43 VALUES (new.thread_id, new.item_count)
44 ON CONFLICT (thread_id) DO UPDATE SET item_count = item_count + excluded.item_count;
45 END
46 `;
47 yield* sql`
48 CREATE TRIGGER orchestration_v2_thread_item_counts_count_update
49 AFTER UPDATE ON orchestration_v2_projection_turn_item_counts
50 BEGIN
51 UPDATE orchestration_v2_projection_thread_item_counts
52 SET item_count = item_count - old.item_count
53 WHERE thread_id = old.thread_id
54 AND (old.run_key = ''
55 OR EXISTS (SELECT 1 FROM orchestration_v2_projection_runs WHERE run_id = old.run_key));
56 INSERT INTO orchestration_v2_projection_thread_item_counts (thread_id, item_count)
57 SELECT new.thread_id, new.item_count
58 WHERE new.run_key = ''
59 OR EXISTS (SELECT 1 FROM orchestration_v2_projection_runs WHERE run_id = new.run_key)
60 ON CONFLICT (thread_id) DO UPDATE SET item_count = item_count + excluded.item_count;
61 DELETE FROM orchestration_v2_projection_thread_item_counts
62 WHERE thread_id = old.thread_id AND item_count <= 0;
63 END
64 `;
65 yield* sql`
66 CREATE TRIGGER orchestration_v2_thread_item_counts_count_delete
67 AFTER DELETE ON orchestration_v2_projection_turn_item_counts
68 WHEN old.run_key = ''
69 OR EXISTS (SELECT 1 FROM orchestration_v2_projection_runs WHERE run_id = old.run_key)
70 BEGIN
71 UPDATE orchestration_v2_projection_thread_item_counts
72 SET item_count = item_count - old.item_count
73 WHERE thread_id = old.thread_id;
74 DELETE FROM orchestration_v2_projection_thread_item_counts
75 WHERE thread_id = old.thread_id AND item_count <= 0;
76 END
77 `;
78
79 // Items can be written before their run; they count from when it exists.
80 yield* sql`
81 CREATE TRIGGER orchestration_v2_thread_item_counts_run_insert
82 AFTER INSERT ON orchestration_v2_projection_runs
83 BEGIN
84 INSERT INTO orchestration_v2_projection_thread_item_counts (thread_id, item_count)
85 SELECT thread_id, COUNT(*)
86 FROM orchestration_v2_projection_turn_items
87 WHERE run_id = new.run_id
88 GROUP BY thread_id
89 ON CONFLICT (thread_id) DO UPDATE SET item_count = item_count + excluded.item_count;
90 END
91 `;
92 yield* sql`
93 CREATE TRIGGER orchestration_v2_thread_item_counts_run_delete
94 AFTER DELETE ON orchestration_v2_projection_runs
95 BEGIN
96 UPDATE orchestration_v2_projection_thread_item_counts
97 SET item_count = item_count - (
98 SELECT COUNT(*)
99 FROM orchestration_v2_projection_turn_items item
100 WHERE item.run_id = old.run_id
101 AND item.thread_id = orchestration_v2_projection_thread_item_counts.thread_id
102 )
103 WHERE thread_id IN (
104 SELECT thread_id FROM orchestration_v2_projection_turn_items WHERE run_id = old.run_id
105 );
106 DELETE FROM orchestration_v2_projection_thread_item_counts
107 WHERE item_count <= 0 AND thread_id IN (
108 SELECT thread_id FROM orchestration_v2_projection_turn_items WHERE run_id = old.run_id
109 );
110 END
111 `;
112 });