1import * as SqlClient from "effect/sql/SqlClient";
2import * as Effect from "effect/Effect";
3
4export default Effect.gen(function* () {
5 const sql = yield* SqlClient.SqlClient;
6
7 yield* sql`
8 INSERT OR IGNORE INTO projection_pending_approvals (
9 request_id,
10 thread_id,
11 turn_id,
12 status,
13 decision,
14 created_at,
15 resolved_at
16 )
17 SELECT
18 requested.request_id,
19 requested.thread_id,
20 requested.turn_id,
21 'pending',
22 NULL,
23 requested.created_at,
24 NULL
25 FROM (
26 SELECT
27 json_extract(payload_json, '$.requestId') AS request_id,
28 thread_id,
29 turn_id,
30 created_at,
31 ROW_NUMBER() OVER (
32 PARTITION BY json_extract(payload_json, '$.requestId')
33 ORDER BY created_at ASC, activity_id ASC
34 ) AS row_number
35 FROM projection_thread_activities
36 WHERE kind = 'approval.requested'
37 AND json_extract(payload_json, '$.requestId') IS NOT NULL
38 ) AS requested
39 WHERE requested.row_number = 1
40 `;
41
42 yield* sql`
43 WITH latest_resolutions AS (
44 SELECT
45 resolved.request_id,
46 resolved.resolved_at,
47 resolved.decision
48 FROM (
49 SELECT
50 json_extract(payload_json, '$.requestId') AS request_id,
51 created_at AS resolved_at,
52 CASE
53 WHEN json_extract(payload_json, '$.decision') IN (
54 'accept',
55 'acceptForSession',
56 'decline',
57 'cancel'
58 )
59 THEN json_extract(payload_json, '$.decision')
60 ELSE NULL
61 END AS decision,
62 ROW_NUMBER() OVER (
63 PARTITION BY json_extract(payload_json, '$.requestId')
64 ORDER BY created_at DESC, activity_id DESC
65 ) AS row_number
66 FROM projection_thread_activities
67 WHERE kind = 'approval.resolved'
68 AND json_extract(payload_json, '$.requestId') IS NOT NULL
69 ) AS resolved
70 WHERE resolved.row_number = 1
71 )
72 UPDATE projection_pending_approvals
73 SET
74 status = 'resolved',
75 decision = (
76 SELECT latest_resolutions.decision
77 FROM latest_resolutions
78 WHERE latest_resolutions.request_id = projection_pending_approvals.request_id
79 ),
80 resolved_at = (
81 SELECT latest_resolutions.resolved_at
82 FROM latest_resolutions
83 WHERE latest_resolutions.request_id = projection_pending_approvals.request_id
84 )
85 WHERE EXISTS (
86 SELECT 1
87 FROM latest_resolutions
88 WHERE latest_resolutions.request_id = projection_pending_approvals.request_id
89 )
90 `;
91
92 yield* sql`
93 WITH latest_response_events AS (
94 SELECT
95 response.request_id,
96 response.resolved_at,
97 response.decision
98 FROM (
99 SELECT
100 json_extract(payload_json, '$.requestId') AS request_id,
101 occurred_at AS resolved_at,
102 CASE
103 WHEN json_extract(payload_json, '$.decision') IN (
104 'accept',
105 'acceptForSession',
106 'decline',
107 'cancel'
108 )
109 THEN json_extract(payload_json, '$.decision')
110 ELSE NULL
111 END AS decision,
112 ROW_NUMBER() OVER (
113 PARTITION BY json_extract(payload_json, '$.requestId')
114 ORDER BY occurred_at DESC, sequence DESC
115 ) AS row_number
116 FROM orchestration_events
117 WHERE event_type = 'thread.approval-response-requested'
118 AND json_extract(payload_json, '$.requestId') IS NOT NULL
119 ) AS response
120 WHERE response.row_number = 1
121 )
122 UPDATE projection_pending_approvals
123 SET
124 status = 'resolved',
125 decision = (
126 SELECT latest_response_events.decision
127 FROM latest_response_events
128 WHERE latest_response_events.request_id = projection_pending_approvals.request_id
129 ),
130 resolved_at = (
131 SELECT latest_response_events.resolved_at
132 FROM latest_response_events
133 WHERE latest_response_events.request_id = projection_pending_approvals.request_id
134 )
135 WHERE EXISTS (
136 SELECT 1
137 FROM latest_response_events
138 WHERE latest_response_events.request_id = projection_pending_approvals.request_id
139 )
140 `;
141
142 yield* sql`
143 WITH latest_stale_failures AS (
144 SELECT
145 failure.request_id,
146 failure.resolved_at
147 FROM (
148 SELECT
149 json_extract(payload_json, '$.requestId') AS request_id,
150 created_at AS resolved_at,
151 ROW_NUMBER() OVER (
152 PARTITION BY json_extract(payload_json, '$.requestId')
153 ORDER BY created_at DESC, activity_id DESC
154 ) AS row_number
155 FROM projection_thread_activities
156 WHERE kind = 'provider.approval.respond.failed'
157 AND json_extract(payload_json, '$.requestId') IS NOT NULL
158 AND (
159 lower(COALESCE(json_extract(payload_json, '$.detail'), ''))
160 LIKE '%stale pending approval request%'
161 OR lower(COALESCE(json_extract(payload_json, '$.detail'), ''))
162 LIKE '%unknown pending approval request%'
163 OR lower(COALESCE(json_extract(payload_json, '$.detail'), ''))
164 LIKE '%unknown pending permission request%'
165 )
166 ) AS failure
167 WHERE failure.row_number = 1
168 )
169 UPDATE projection_pending_approvals
170 SET
171 status = 'resolved',
172 decision = NULL,
173 resolved_at = (
174 SELECT latest_stale_failures.resolved_at
175 FROM latest_stale_failures
176 WHERE latest_stale_failures.request_id = projection_pending_approvals.request_id
177 )
178 WHERE status = 'pending'
179 AND EXISTS (
180 SELECT 1
181 FROM latest_stale_failures
182 WHERE latest_stale_failures.request_id = projection_pending_approvals.request_id
183 )
184 `;
185
186 yield* sql`
187 UPDATE projection_threads
188 SET
189 latest_user_message_at = (
190 SELECT MAX(message.created_at)
191 FROM projection_thread_messages AS message
192 WHERE message.thread_id = projection_threads.thread_id
193 AND message.role = 'user'
194 ),
195 pending_approval_count = COALESCE((
196 SELECT COUNT(*)
197 FROM projection_pending_approvals
198 WHERE projection_pending_approvals.thread_id = projection_threads.thread_id
199 AND projection_pending_approvals.status = 'pending'
200 ), 0),
201 pending_user_input_count = COALESCE((
202 WITH latest_user_input_states AS (
203 SELECT
204 latest.request_id,
205 latest.kind,
206 latest.detail
207 FROM (
208 SELECT
209 json_extract(activity.payload_json, '$.requestId') AS request_id,
210 activity.kind,
211 lower(COALESCE(json_extract(activity.payload_json, '$.detail'), '')) AS detail,
212 ROW_NUMBER() OVER (
213 PARTITION BY json_extract(activity.payload_json, '$.requestId')
214 ORDER BY activity.created_at DESC, activity.activity_id DESC
215 ) AS row_number
216 FROM projection_thread_activities AS activity
217 WHERE activity.thread_id = projection_threads.thread_id
218 AND json_extract(activity.payload_json, '$.requestId') IS NOT NULL
219 AND activity.kind IN (
220 'user-input.requested',
221 'user-input.resolved',
222 'provider.user-input.respond.failed'
223 )
224 ) AS latest
225 WHERE latest.row_number = 1
226 )
227 SELECT COUNT(*)
228 FROM latest_user_input_states
229 WHERE latest_user_input_states.kind = 'user-input.requested'
230 OR (
231 latest_user_input_states.kind = 'provider.user-input.respond.failed'
232 AND latest_user_input_states.detail NOT LIKE '%stale pending user-input request%'
233 AND latest_user_input_states.detail NOT LIKE '%unknown pending user-input request%'
234 )
235 ), 0),
236 has_actionable_proposed_plan = COALESCE((
237 SELECT CASE
238 WHEN projection_threads.latest_turn_id IS NOT NULL
239 AND EXISTS (
240 SELECT 1
241 FROM projection_thread_proposed_plans AS latest_turn_plan_exists
242 WHERE latest_turn_plan_exists.thread_id = projection_threads.thread_id
243 AND latest_turn_plan_exists.turn_id = projection_threads.latest_turn_id
244 )
245 THEN CASE
246 WHEN (
247 SELECT latest_turn_plan.implemented_at
248 FROM projection_thread_proposed_plans AS latest_turn_plan
249 WHERE latest_turn_plan.thread_id = projection_threads.thread_id
250 AND latest_turn_plan.turn_id = projection_threads.latest_turn_id
251 ORDER BY latest_turn_plan.updated_at DESC, latest_turn_plan.plan_id DESC
252 LIMIT 1
253 ) IS NULL
254 THEN 1
255 ELSE 0
256 END
257 WHEN EXISTS (
258 SELECT 1
259 FROM projection_thread_proposed_plans AS any_plan
260 WHERE any_plan.thread_id = projection_threads.thread_id
261 )
262 THEN CASE
263 WHEN (
264 SELECT latest_plan.implemented_at
265 FROM projection_thread_proposed_plans AS latest_plan
266 WHERE latest_plan.thread_id = projection_threads.thread_id
267 ORDER BY latest_plan.updated_at DESC, latest_plan.plan_id DESC
268 LIMIT 1
269 ) IS NULL
270 THEN 1
271 ELSE 0
272 END
273 ELSE 0
274 END
275 ), 0)
276 `;
277});