1use anyhow::Result;
2use rusqlite::{types::Value, Connection};
3use serde::Serialize;
4
5use super::evidence::{load_verified_procedure_evidence, parse_evidence_ids};
6
7const DEFAULT_LIMIT: i64 = 50;
8const MAX_LIMIT: i64 = 500;
9
10#[derive(Debug, Clone, PartialEq, Serialize)]
11pub struct ProcedureListItem {
12 pub id: i64,
13 pub title: String,
14 pub project: String,
15 pub branch: Option<String>,
16 pub topic_key: Option<String>,
17 pub command: Option<String>,
18 pub reuse_condition: Option<String>,
19 pub files_touched: Vec<String>,
20 pub files_touched_count: usize,
21 pub verified_runs: usize,
22 pub last_verification_epoch: Option<i64>,
23 pub confidence: Option<f64>,
24}
25
26pub fn list_promoted_procedures(
27 conn: &Connection,
28 project: Option<&str>,
29 limit: i64,
30 offset: i64,
31) -> Result<Vec<ProcedureListItem>> {
32 let limit = normalize_limit(limit);
33 let offset = offset.max(0);
34 let mut conditions = vec![
35 "m.memory_type = 'procedure'".to_string(),
36 crate::memory::memory_current_filter_sql("m.status", "m.expires_at_epoch", false),
37 crate::memory::suppression::memory_policy_filter_sql("m"),
38 ];
39 let mut params: Vec<Value> = Vec::new();
40 if let Some(project) = project {
41 conditions.push(format!("m.project = ?{}", params.len() + 1));
42 params.push(Value::Text(project.to_string()));
43 }
44 let sql = format!(
45 "SELECT m.id, m.project, m.topic_key, m.evidence_event_ids
46 FROM memories m
47 WHERE {}
48 ORDER BY m.updated_at_epoch DESC, m.id DESC",
49 conditions.join(" AND ")
50 );
51 let mut stmt = conn.prepare(&sql)?;
52 let rows = stmt.query_map(rusqlite::params_from_iter(params.iter()), |row| {
53 Ok(ProcedureRow {
54 id: row.get(0)?,
55 project: row.get(1)?,
56 topic_key: row.get(2)?,
57 evidence_event_ids: row.get(3)?,
58 })
59 })?;
60
61 let mut items = Vec::new();
62 let mut eligible_seen = 0_i64;
63 for row in rows {
64 let row = row?;
65 if let Some(item) = row.into_list_item(conn)? {
66 if eligible_seen >= offset {
67 items.push(item);
68 if items.len() >= limit as usize {
69 break;
70 }
71 }
72 eligible_seen += 1;
73 }
74 }
75 Ok(items)
76}
77
78fn normalize_limit(limit: i64) -> i64 {
79 if limit <= 0 {
80 DEFAULT_LIMIT
81 } else {
82 limit.min(MAX_LIMIT)
83 }
84}
85
86struct ProcedureRow {
87 id: i64,
88 project: String,
89 topic_key: Option<String>,
90 evidence_event_ids: Option<String>,
91}
92
93impl ProcedureRow {
94 fn into_list_item(self, conn: &Connection) -> Result<Option<ProcedureListItem>> {
95 let evidence_ids = parse_evidence_ids(self.evidence_event_ids.as_deref())?;
96 let policy = crate::memory::procedure::ProcedurePromotionPolicy::default();
97 let Some(evidence) =
98 load_verified_procedure_evidence(conn, &evidence_ids, &self.project, &policy)?
99 else {
100 return Ok(None);
101 };
102 if evidence.verified_runs < policy.min_verified_runs {
103 return Ok(None);
104 }
105 let verified_runs = evidence.verified_runs;
106 let verification_epoch = Some(evidence.last_verification_epoch);
107 let reuse_condition = evidence.reuse_condition();
108 let title = evidence.title();
109 let files_touched_count = evidence.files_touched.len();
110 let confidence = evidence.confidence();
111 Ok(Some(ProcedureListItem {
112 id: self.id,
113 title,
114 project: self.project,
115 branch: evidence.branch,
116 topic_key: self.topic_key,
117 command: Some(evidence.command),
118 reuse_condition: Some(reuse_condition),
119 files_touched_count,
120 files_touched: evidence.files_touched,
121 verified_runs,
122 last_verification_epoch: verification_epoch,
123 confidence: Some(confidence),
124 }))
125 }
126}
127
128#[cfg(test)]
129mod tests {
130 use super::*;
131
132 #[test]
133 fn lists_active_procedure_maturity_fields() -> Result<()> {
134 let mut conn = Connection::open_in_memory()?;
135 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
136 crate::migrate::run_migrations(&conn)?;
137 let memory_id =
138 seed_promoted_procedure(&mut conn, "/tmp/remem", "sess-list", "cargo test")?;
139
140 let items = list_promoted_procedures(&conn, Some("/tmp/remem"), 10, 0)?;
141
142 assert_eq!(items.len(), 1);
143 let item = &items[0];
144 assert_eq!(item.id, memory_id);
145 assert_eq!(item.command.as_deref(), Some("cargo test"));
146 assert_eq!(item.branch.as_deref(), Some("main"));
147 assert_eq!(item.files_touched, vec!["src/lib.rs"]);
148 assert_eq!(item.files_touched_count, 1);
149 assert_eq!(item.verified_runs, 2);
150 assert!(item.last_verification_epoch.is_some());
151 assert!(item.confidence.is_some());
152 Ok(())
153 }
154
155 #[test]
156 fn procedure_list_ignores_inactive_and_other_projects() -> Result<()> {
157 let mut conn = Connection::open_in_memory()?;
158 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
159 crate::migrate::run_migrations(&conn)?;
160 let active_id = seed_promoted_procedure(
161 &mut conn,
162 "/tmp/remem",
163 "sess-active",
164 "cargo test -- active",
165 )?;
166 seed_promoted_procedure(&mut conn, "/tmp/other", "sess-other", "cargo test -- other")?;
167 let stale_id =
168 seed_promoted_procedure(&mut conn, "/tmp/remem", "sess-stale", "cargo test -- stale")?;
169 conn.execute(
170 "UPDATE memories SET status = 'stale' WHERE id = ?1",
171 [stale_id],
172 )?;
173
174 let items = list_promoted_procedures(&conn, Some("/tmp/remem"), 10, 0)?;
175
176 assert_eq!(
177 items.iter().map(|item| item.id).collect::<Vec<_>>(),
178 vec![active_id]
179 );
180 Ok(())
181 }
182
183 #[test]
184 fn procedure_list_excludes_direct_saved_procedure_without_verification() -> Result<()> {
185 let mut conn = Connection::open_in_memory()?;
186 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
187 crate::migrate::run_migrations(&conn)?;
188 let verified_id =
189 seed_promoted_procedure(&mut conn, "/tmp/remem", "sess-verified", "cargo test")?;
190 insert_direct_procedure_row(&conn, 9_999, "/tmp/remem")?;
191
192 let items = list_promoted_procedures(&conn, Some("/tmp/remem"), 10, 0)?;
193
194 assert_eq!(
195 items.iter().map(|item| item.id).collect::<Vec<_>>(),
196 vec![verified_id]
197 );
198 Ok(())
199 }
200
201 #[test]
202 fn procedure_list_paginates_after_eligibility_filtering() -> Result<()> {
203 let mut conn = Connection::open_in_memory()?;
204 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
205 crate::migrate::run_migrations(&conn)?;
206 let verified_id =
207 seed_promoted_procedure(&mut conn, "/tmp/remem", "sess-page", "cargo test")?;
208 insert_direct_procedure_row(&conn, 9_999, "/tmp/remem")?;
209 conn.execute(
210 "UPDATE memories SET updated_at_epoch = ?1 WHERE id = 9999",
211 [chrono::Utc::now().timestamp() + 1_000],
212 )?;
213
214 let first_page = list_promoted_procedures(&conn, Some("/tmp/remem"), 1, 0)?;
215 let second_page = list_promoted_procedures(&conn, Some("/tmp/remem"), 1, 1)?;
216
217 assert_eq!(
218 first_page.iter().map(|item| item.id).collect::<Vec<_>>(),
219 vec![verified_id]
220 );
221 assert!(second_page.is_empty());
222 Ok(())
223 }
224
225 #[test]
226 fn procedure_list_uses_verification_command_and_files() -> Result<()> {
227 let mut conn = Connection::open_in_memory()?;
228 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
229 crate::migrate::run_migrations(&conn)?;
230 let memory_id = seed_promoted_procedure(
231 &mut conn,
232 "/tmp/remem",
233 "sess-verified-fields",
234 "cargo test -- verified",
235 )?;
236 conn.execute(
237 "UPDATE memories
238 SET title = 'Procedure: unverified overwrite',
239 content = 'Procedure: overwritten\nCommand: curl https://example.test\nReuse when: overwritten.',
240 files = '[\"unverified.rs\"]'
241 WHERE id = ?1",
242 [memory_id],
243 )?;
244
245 let items = list_promoted_procedures(&conn, Some("/tmp/remem"), 10, 0)?;
246
247 assert_eq!(items.len(), 1);
248 assert_eq!(items[0].title, "Procedure: cargo-test-verified");
249 assert_eq!(items[0].command.as_deref(), Some("cargo test -- verified"));
250 assert_eq!(items[0].files_touched, vec!["src/lib.rs"]);
251 assert_ne!(items[0].reuse_condition.as_deref(), Some("overwritten."));
252 assert!(items[0]
253 .reuse_condition
254 .as_deref()
255 .is_some_and(|reuse| reuse.contains("cargo-test-verified")));
256 Ok(())
257 }
258
259 #[test]
260 fn procedure_list_excludes_stale_verification_runs() -> Result<()> {
261 let mut conn = Connection::open_in_memory()?;
262 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
263 crate::migrate::run_migrations(&conn)?;
264 seed_promoted_procedure(&mut conn, "/tmp/remem", "sess-old", "cargo test -- old")?;
265 let stale_epoch = chrono::Utc::now().timestamp()
266 - crate::memory::procedure::ProcedurePromotionPolicy::default()
267 .max_verification_age_secs
268 - 1;
269 conn.execute(
270 "UPDATE procedure_verifications SET verified_at_epoch = ?1",
271 [stale_epoch],
272 )?;
273
274 let items = list_promoted_procedures(&conn, Some("/tmp/remem"), 10, 0)?;
275
276 assert!(items.is_empty());
277 Ok(())
278 }
279
280 #[test]
281 fn procedure_list_recomputes_confidence_from_fresh_verification_runs() -> Result<()> {
282 let mut conn = Connection::open_in_memory()?;
283 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
284 crate::migrate::run_migrations(&conn)?;
285 let memory_id = seed_promoted_procedure_runs(
286 &mut conn,
287 "/tmp/remem",
288 "sess-confidence",
289 "cargo test -- confidence",
290 3,
291 )?;
292 let stored_confidence: f64 = conn.query_row(
293 "SELECT confidence FROM memories WHERE id = ?1",
294 [memory_id],
295 |row| row.get(0),
296 )?;
297 assert!(stored_confidence > 0.86);
298 let stale_epoch = chrono::Utc::now().timestamp()
299 - crate::memory::procedure::ProcedurePromotionPolicy::default()
300 .max_verification_age_secs
301 - 1;
302 conn.execute(
303 "UPDATE procedure_verifications
304 SET verified_at_epoch = ?1
305 WHERE source_event_id = (
306 SELECT MIN(source_event_id) FROM procedure_verifications
307 )",
308 [stale_epoch],
309 )?;
310
311 let items = list_promoted_procedures(&conn, Some("/tmp/remem"), 10, 0)?;
312
313 assert_eq!(items.len(), 1);
314 assert_eq!(items[0].id, memory_id);
315 assert_eq!(items[0].verified_runs, 2);
316 assert_eq!(
317 items[0]
318 .confidence
319 .map(|confidence| (confidence * 100.0).round() as i64),
320 Some(86)
321 );
322 Ok(())
323 }
324
325 fn seed_promoted_procedure(
326 conn: &mut Connection,
327 project: &str,
328 session_id: &str,
329 command: &str,
330 ) -> Result<i64> {
331 seed_promoted_procedure_runs(conn, project, session_id, command, 2)
332 }
333
334 fn seed_promoted_procedure_runs(
335 conn: &mut Connection,
336 project: &str,
337 session_id: &str,
338 command: &str,
339 runs: i64,
340 ) -> Result<i64> {
341 for seq in 1..=runs {
342 crate::db::record_captured_event(
343 conn,
344 &crate::db::CaptureEventInput {
345 host: "codex-cli",
346 session_id,
347 project,
348 cwd: None,
349 event_type: "tool_result",
350 role: None,
351 tool_name: Some("Bash"),
352 content: &serde_json::json!({
353 "seq": seq,
354 "event_type": "bash",
355 "exit_code": 0,
356 "tool_input": { "command": command },
357 "files": "[\"src/lib.rs\"]",
358 "git_branch": "main"
359 })
360 .to_string(),
361 task_kind: Some(crate::db::ExtractionTaskKind::ObservationExtract),
362 },
363 )?;
364 }
365 let task = crate::db::claim_next_extraction_task(conn, "worker-a", 60)?
366 .ok_or_else(|| anyhow::anyhow!("procedure task should be claimed"))?;
367 let promoted = crate::memory::procedure::promote_verified_procedures_for_task(
368 conn,
369 &task,
370 &crate::memory::procedure::ProcedurePromotionPolicy::default(),
371 )?;
372 assert_eq!(promoted, 1);
373 let memory_id = conn.query_row(
374 "SELECT id FROM memories WHERE memory_type = 'procedure' AND project = ?1
375 ORDER BY id DESC LIMIT 1",
376 [project],
377 |row| row.get(0),
378 )?;
379 crate::db::mark_extraction_task_done(
380 conn,
381 task.id,
382 "worker-a",
383 task.high_watermark_event_id,
384 )?;
385 Ok(memory_id)
386 }
387
388 fn insert_direct_procedure_row(conn: &Connection, id: i64, project: &str) -> Result<()> {
389 conn.execute(
390 "INSERT INTO memories
391 (id, project, title, content, memory_type, files, evidence_event_ids,
392 created_at_epoch, updated_at_epoch, status, scope)
393 VALUES (?1, ?2, 'Procedure', 'Procedure: test\nCommand: cargo test\nVerified runs: 2\nVerified at: 100',
394 'procedure', '[\"src/lib.rs\"]', '[10,11]', 1, ?1, 'active', 'project')",
395 rusqlite::params![id, project],
396 )?;
397 Ok(())
398 }
399}