Skip to main content

orch/store/
tasks.rs

1/// The SQL behind each replicated task write, run with the proposer's time
2pub(crate) mod apply;
3
4use std::collections::HashMap;
5use std::collections::HashSet;
6
7use super::{Context, MemoryEntry, SqlitePool, TaskStore};
8use crate::backends::ExternalTask;
9use crate::cluster;
10use crate::cluster::Command;
11use crate::security;
12use serde::{Deserialize, Serialize};
13use sqlx::Row;
14use sqlx::sqlite::SqliteRow;
15
16/// Explicit column list for `SELECT` queries on the `tasks` table.
17///
18/// Using `SELECT *` with `sqlx-sqlite`'s persistent prepared statements can
19/// cause OOB panics when the schema changes (new migration columns) because
20/// `sqlite3_column_count()` returns the updated count but the cached column
21/// metadata vector was built at prepare time. Explicit columns prevent the
22/// mismatch.
23pub(crate) const TASK_COLS: &str = "id, external_id, repo, origin, title, body, status, \
24    source, source_id, author, url, labels, agent, model, complexity, estimate, \
25    route_reason, agent_profile, selected_skills, route_attempts, attempts, \
26    branch, worktree, worktree_cleaned, summary, last_error, parent_id, \
27    block_reason, pr_number, pr_review_context, last_review_ts, review_ts_map, \
28    last_comment_review_ts, merge_conflict_retries, ci_merge_failures, \
29    pr_create_failures, review_agent_failures, review_resets, review_cycles, input_tokens, \
30    output_tokens, input_cost_usd, output_cost_usd, total_cost_usd, \
31    model_reroute_chain, limit_reroute_chain, \
32    memory, delegations, created_at, updated_at, \
33     review_invocations, needs_review_refires, push_failures, auto_unblock_count, auto_unblock_last_at, \
34     ci_recovery_count, auto_unblock_last_reason, no_code_reroutes, network_retries, no_code_last_agent, \
35     owner_node, timeout_retries";
36
37/// Number of columns in TASK_COLS (used for diagnostic verification).
38pub(crate) const TASK_COLS_COUNT: usize = 62;
39
40/// Explicit column list for `SELECT` queries on the `task_runs` table.
41const TASK_RUN_COLS: &str = "id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, \
42    exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, \
43    total_cost_usd, duration_secs, started_at, completed_at";
44
45/// Explicit column list for `SELECT` queries on the `task_activity` table.
46const TASK_ACTIVITY_COLS: &str =
47    "id, task_id, timestamp, event_type, from_status, to_status, agent, model, details";
48
49/// Allowlist of columns that can be updated via `set_fields` and `batch_set_fields`.
50const ALLOWED_FIELDS: &[&str] = &[
51    "agent",
52    "model",
53    "complexity",
54    "route_reason",
55    "agent_profile",
56    "selected_skills",
57    "route_attempts",
58    "attempts",
59    "branch",
60    "worktree",
61    "worktree_cleaned",
62    "owner_node",
63    "summary",
64    "last_error",
65    "parent_id",
66    "block_reason",
67    "pr_number",
68    "pr_review_context",
69    "last_review_ts",
70    "review_ts_map",
71    "last_comment_review_ts",
72    "merge_conflict_retries",
73    "ci_merge_failures",
74    "pr_create_failures",
75    "push_failures",
76    "review_agent_failures",
77    "review_resets",
78    "review_cycles",
79    "review_invocations",
80    "needs_review_refires",
81    "input_tokens",
82    "output_tokens",
83    "input_cost_usd",
84    "output_cost_usd",
85    "total_cost_usd",
86    "model_reroute_chain",
87    "limit_reroute_chain",
88    "memory",
89    "delegations",
90    "source",
91    "source_id",
92    "labels",
93    "auto_unblock_count",
94    "auto_unblock_last_at",
95    "auto_unblock_last_reason",
96    "ci_recovery_count",
97    "no_code_reroutes",
98    "network_retries",
99    "timeout_retries",
100    "no_code_last_agent",
101];
102
103/// Allowlist of fields that can be incremented via `increment` and `batch_increment`.
104const INCREMENTABLE_FIELDS: &[&str] = &[
105    "attempts",
106    "route_attempts",
107    "merge_conflict_retries",
108    "ci_merge_failures",
109    "pr_create_failures",
110    "push_failures",
111    "review_agent_failures",
112    "review_resets",
113    "review_cycles",
114    "review_invocations",
115    "needs_review_refires",
116    "auto_unblock_count",
117    "ci_recovery_count",
118    "no_code_reroutes",
119    "network_retries",
120    "timeout_retries",
121];
122
123fn sanitize_activity_details(value: &serde_json::Value) -> serde_json::Value {
124    match value {
125        serde_json::Value::String(text) => serde_json::Value::String(security::redact(text)),
126        serde_json::Value::Array(items) => serde_json::Value::Array(
127            items
128                .iter()
129                .map(sanitize_activity_details)
130                .collect::<Vec<_>>(),
131        ),
132        serde_json::Value::Object(map) => serde_json::Value::Object(
133            map.iter()
134                .map(|(key, value)| (key.clone(), sanitize_activity_details(value)))
135                .collect(),
136        ),
137        other => other.clone(),
138    }
139}
140
141fn sanitize_task_field_value(col: &str, value: &serde_json::Value) -> serde_json::Value {
142    match (col, value) {
143        ("last_error" | "block_reason", serde_json::Value::String(text)) => {
144            serde_json::Value::String(security::redact(text))
145        }
146        _ => value.clone(),
147    }
148}
149
150fn sql_value_for_task_field(
151    col: &str,
152    value: &serde_json::Value,
153) -> anyhow::Result<Option<String>> {
154    let sanitized = sanitize_task_field_value(col, value);
155    match sanitized {
156        serde_json::Value::String(text) => Ok(Some(text)),
157        serde_json::Value::Number(number) => Ok(Some(number.to_string())),
158        serde_json::Value::Bool(boolean) => Ok(Some(if boolean { "1" } else { "0" }.to_string())),
159        serde_json::Value::Null => Ok(None),
160        other => Ok(Some(serde_json::to_string(&other)?)),
161    }
162}
163
164#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
165#[serde(rename_all = "snake_case")]
166pub enum TaskStatus {
167    New,
168    Routed,
169    InProgress,
170    Done,
171    Blocked,
172    InReview,
173    Approved,
174    NeedsReview,
175    Waiting,
176}
177
178impl TaskStatus {
179    pub fn as_str(&self) -> &'static str {
180        match self {
181            Self::New => "new",
182            Self::Routed => "routed",
183            Self::InProgress => "in_progress",
184            Self::Done => "done",
185            Self::Blocked => "blocked",
186            Self::InReview => "in_review",
187            Self::Approved => "approved",
188            Self::NeedsReview => "needs_review",
189            Self::Waiting => "waiting",
190        }
191    }
192
193    pub fn from_str(s: &str) -> Option<Self> {
194        match s {
195            "new" => Some(Self::New),
196            "routed" => Some(Self::Routed),
197            "in_progress" => Some(Self::InProgress),
198            "done" => Some(Self::Done),
199            "blocked" => Some(Self::Blocked),
200            "in_review" => Some(Self::InReview),
201            "approved" => Some(Self::Approved),
202            "needs_review" => Some(Self::NeedsReview),
203            "waiting" => Some(Self::Waiting),
204            _ => None,
205        }
206    }
207}
208
209/// Unified task record — single row in the `tasks` table.
210#[derive(Debug, Clone, Serialize, Deserialize)]
211pub struct Task {
212    pub id: i64,
213    pub external_id: Option<String>,
214    pub repo: String,
215    pub origin: String,
216
217    pub title: String,
218    pub body: String,
219    pub status: TaskStatus,
220    pub source: String,
221    pub source_id: String,
222    pub author: String,
223    pub url: String,
224    pub labels: Vec<String>,
225
226    // Routing
227    pub agent: Option<String>,
228    pub model: Option<String>,
229    pub complexity: String,
230    /// Fibonacci effort estimate (1, 2, 3, 5, 8, 13, or 21). 0 means not provided.
231    pub estimate: u8,
232    pub route_reason: String,
233    pub agent_profile: String,
234    pub selected_skills: String,
235    pub route_attempts: i32,
236
237    // Execution
238    pub attempts: i32,
239    pub branch: String,
240    pub worktree: String,
241    pub worktree_cleaned: bool,
242    /// The node that runs the task, None until one starts it
243    #[serde(default, skip_serializing_if = "Option::is_none")]
244    pub owner_node: Option<String>,
245    pub summary: String,
246    pub last_error: String,
247    pub parent_id: Option<i64>,
248    pub block_reason: Option<String>,
249
250    // PR Review
251    pub pr_number: Option<i32>,
252    pub pr_review_context: String,
253    pub last_review_ts: String,
254    pub review_ts_map: String,
255    pub last_comment_review_ts: String,
256    pub merge_conflict_retries: i32,
257    pub ci_merge_failures: i32,
258    pub pr_create_failures: i32,
259    pub push_failures: i32,
260    pub network_retries: i32,
261    /// Runs that ended in a timeout, reset by retry.
262    pub timeout_retries: i32,
263    pub review_agent_failures: i32,
264    // Review retries that ended without a decision and without counting as a failure
265    pub review_resets: i32,
266    pub review_cycles: i32,
267    pub review_invocations: i32,
268    // NeedsReview re-fire counter (incremented by sync catch-up and reset on successful review cycles)
269    pub needs_review_refires: i32,
270
271    // Tokens & Cost
272    pub input_tokens: i64,
273    pub output_tokens: i64,
274    // Sum of the costs the task's runs reported, 0 when none did
275    pub total_cost_usd: f64,
276
277    // Recovery
278    pub model_reroute_chain: String,
279    pub limit_reroute_chain: String,
280
281    // Structured data
282    pub memory: Vec<MemoryEntry>,
283    pub delegations: Vec<serde_json::Value>,
284
285    // Auto-unblock tracking
286    pub auto_unblock_count: i32,
287    pub auto_unblock_last_at: String,
288    pub auto_unblock_last_reason: String,
289
290    // CI-based auto-recovery tracking (separate from auto_unblock_count)
291    pub ci_recovery_count: i32,
292
293    // Done-without-PR circuit breaker
294    pub no_code_reroutes: i32,
295    /// Which agent produced the last no-code failure (used to detect same-agent loops).
296    pub no_code_last_agent: String,
297
298    // Timestamps
299    pub created_at: String,
300    pub updated_at: String,
301}
302
303impl Task {
304    /// The cost the task's runs reported, `None` when no run reported one.
305    pub fn reported_cost_usd(&self) -> Option<f64> {
306        (self.total_cost_usd > 0.0).then_some(self.total_cost_usd)
307    }
308}
309
310/// Parameters for creating a new task.
311#[derive(Debug, Clone, Default, PartialEq, Serialize, Deserialize)]
312pub struct NewTask {
313    pub external_id: Option<String>,
314    pub repo: String,
315    pub origin: String,
316    pub title: String,
317    pub body: String,
318    pub source: String,
319    pub source_id: String,
320    pub author: String,
321    pub url: String,
322    pub labels: Vec<String>,
323    /// Optional link to a parent task (e.g. the issue/PR a mention was posted on).
324    pub parent_id: Option<i64>,
325}
326
327/// How an operator names a task on the CLI.
328#[derive(Debug, Clone, PartialEq, Eq)]
329pub enum CliTaskSpec {
330    /// `internal:58`
331    Internal(i64),
332    /// `owner/repo#58`
333    Issue { repo: String, number: i64 },
334    /// `58` or `#58`
335    Bare(i64),
336}
337
338/// A task picked by [`TaskStore::resolve_cli_id`].
339#[derive(Debug, Clone, PartialEq, Eq)]
340pub struct CliTaskRef {
341    pub store_id: Option<i64>,
342    pub repo: String,
343    pub origin: String,
344    pub title: String,
345    pub external_id: String,
346    pub status: String,
347}
348
349impl CliTaskRef {
350    pub fn is_internal(&self) -> bool {
351        self.origin == "internal"
352    }
353
354    pub fn unsynced_issue(repo: String, number: i64) -> Self {
355        Self {
356            store_id: None,
357            repo,
358            origin: "github".to_string(),
359            title: String::new(),
360            external_id: number.to_string(),
361            status: String::new(),
362        }
363    }
364
365    pub fn display_spec(&self) -> String {
366        if self.is_internal() {
367            match self.store_id {
368                Some(id) => format!("internal:{id}"),
369                None => self.external_id.clone(),
370            }
371        } else {
372            format!("{}#{}", self.repo, self.external_id)
373        }
374    }
375
376    fn from_row(row: &SqliteRow) -> anyhow::Result<Self> {
377        let id: i64 = row.try_get("id")?;
378        let origin: String = row.try_get("origin")?;
379        let external_id: Option<String> = row.try_get("external_id")?;
380        let external_id = external_id.unwrap_or_else(|| {
381            if origin == "internal" {
382                format!("internal:{id}")
383            } else {
384                String::new()
385            }
386        });
387        Ok(Self {
388            store_id: Some(id),
389            repo: row.try_get("repo")?,
390            origin,
391            title: row.try_get("title")?,
392            external_id,
393            status: row.try_get("status")?,
394        })
395    }
396
397    fn format_match(&self) -> String {
398        format!(
399            "  {}  [{}]  {}",
400            self.display_spec(),
401            if self.status.is_empty() {
402                "unknown"
403            } else {
404                &self.status
405            },
406            if self.title.is_empty() {
407                "-"
408            } else {
409                &self.title
410            }
411        )
412    }
413}
414
415/// Parse `internal:N`, `owner/repo#N`, `#N`, or a bare number.
416pub fn parse_cli_task_spec(raw: &str) -> anyhow::Result<CliTaskSpec> {
417    let spec = raw.trim();
418    if spec.is_empty() {
419        anyhow::bail!("empty task id");
420    }
421    if let Some(suffix) = spec.strip_prefix("internal:") {
422        let number: i64 = suffix
423            .parse()
424            .map_err(|_| anyhow::anyhow!("internal task id '{suffix}' is not numeric"))?;
425        if number <= 0 {
426            anyhow::bail!("internal task id must be a positive number");
427        }
428        return Ok(CliTaskSpec::Internal(number));
429    }
430    if let Some((repo, number)) = parse_repo_issue(spec) {
431        return Ok(CliTaskSpec::Issue { repo, number });
432    }
433    let bare = spec.strip_prefix('#').unwrap_or(spec);
434    if let Ok(number) = bare.parse::<i64>()
435        && number > 0
436    {
437        return Ok(CliTaskSpec::Bare(number));
438    }
439    anyhow::bail!("invalid task id '{spec}': use a number, owner/repo#N, or internal:N")
440}
441
442fn parse_repo_issue(spec: &str) -> Option<(String, i64)> {
443    let (repo, number) = spec.rsplit_once('#')?;
444    if repo.is_empty() {
445        return None;
446    }
447    let number: i64 = number.parse().ok()?;
448    if number <= 0 {
449        return None;
450    }
451    let mut parts = repo.split('/');
452    let owner = parts.next()?;
453    let name = parts.next()?;
454    if parts.next().is_some() || owner.is_empty() || name.is_empty() {
455        return None;
456    }
457    let ok = |part: &str| {
458        part.chars()
459            .all(|ch| ch.is_ascii_alphanumeric() || matches!(ch, '_' | '.' | '-'))
460    };
461    if !ok(owner) || !ok(name) {
462        return None;
463    }
464    Some((repo.to_string(), number))
465}
466
467fn ambiguous_cli_id_error(spec: &str, matches: &[CliTaskRef]) -> anyhow::Error {
468    let mut lines = vec![format!("task ID {spec} matches more than one task:")];
469    for task in matches {
470        lines.push(task.format_match());
471    }
472    lines.push(ambiguous_cli_id_hint(matches));
473    anyhow::anyhow!(lines.join("\n"))
474}
475
476fn ambiguous_cli_id_hint(matches: &[CliTaskRef]) -> String {
477    let github = matches
478        .iter()
479        .find(|task| !task.is_internal())
480        .map(CliTaskRef::display_spec);
481    let internal = matches
482        .iter()
483        .find(|task| task.is_internal())
484        .map(CliTaskRef::display_spec);
485    match (github, internal) {
486        (Some(github), Some(internal)) => {
487            format!("Use {github} for the GitHub task or {internal} for the internal one")
488        }
489        _ => "Use owner/repo#N for an issue, or internal:N for an internal task.".to_string(),
490    }
491}
492
493fn dedup_cli_refs(rows: Vec<SqliteRow>) -> anyhow::Result<Vec<CliTaskRef>> {
494    let mut seen = Vec::new();
495    let mut out = Vec::new();
496    for row in rows {
497        let task = CliTaskRef::from_row(&row)?;
498        if let Some(id) = task.store_id {
499            if seen.contains(&id) {
500                continue;
501            }
502            seen.push(id);
503        }
504        out.push(task);
505    }
506    Ok(out)
507}
508
509/// A single run (agent execution, review, or routing attempt).
510#[derive(Debug, Clone, Serialize, Deserialize)]
511pub struct TaskRun {
512    pub id: i64,
513    pub task_id: i64,
514    pub attempt: i32,
515    pub run_type: String,
516    pub agent: String,
517    pub model: String,
518    pub command: String,
519    pub prompt: String,
520    pub env_vars: String,
521    pub exit_code: Option<i32>,
522    pub stdout: String,
523    pub stderr: String,
524    pub parsed_response: String,
525    pub outcome: String,
526    pub error: String,
527    pub input_tokens: i64,
528    pub output_tokens: i64,
529    pub total_cost_usd: f64,
530    pub duration_secs: f64,
531    pub started_at: String,
532    pub completed_at: Option<String>,
533}
534
535/// A single task lifecycle activity event.
536#[derive(Debug, Clone, Serialize, Deserialize)]
537pub struct TaskActivity {
538    pub id: i64,
539    pub task_id: i64,
540    pub timestamp: String,
541    pub event_type: String,
542    pub from_status: Option<String>,
543    pub to_status: Option<String>,
544    pub agent: Option<String>,
545    pub model: Option<String>,
546    pub details: serde_json::Value,
547}
548
549/// Token usage for a run.
550#[derive(Debug, Clone, Copy, Default)]
551pub struct RunTokenUsage {
552    pub input_tokens: u64,
553    pub output_tokens: u64,
554    pub total_cost_usd: f64,
555    pub duration_secs: f64,
556}
557
558/// Parameters for starting a new run.
559#[derive(Debug, Clone)]
560pub struct StartRun<'a> {
561    pub task_id: i64,
562    pub attempt: i32,
563    pub run_type: &'a str,
564    pub agent: &'a str,
565    pub model: &'a str,
566    pub command: &'a str,
567    pub prompt: &'a str,
568}
569
570/// Parameters for completing a run.
571#[derive(Debug, Clone)]
572pub struct CompleteRun<'a> {
573    pub run_id: i64,
574    pub exit_code: Option<i32>,
575    pub stdout: &'a str,
576    pub stderr: &'a str,
577    pub parsed: &'a str,
578    pub outcome: &'a str,
579    pub error: &'a str,
580    pub tokens: RunTokenUsage,
581}
582
583/// Parameters for storing a route result.
584#[derive(Debug, Clone)]
585pub struct StoreRoute<'a> {
586    pub id: i64,
587    pub agent: &'a str,
588    pub model: Option<&'a str>,
589    pub complexity: &'a str,
590    /// Fibonacci effort estimate (1, 2, 3, 5, 8, 13, or 21). 0 means not provided.
591    pub estimate: u8,
592    pub reason: &'a str,
593    pub profile: &'a str,
594    pub skills: &'a str,
595}
596
597/// Parameters for upserting an external task.
598#[derive(Debug, Clone)]
599pub struct UpsertExternal<'a> {
600    pub repo: &'a str,
601    pub ext_id: &'a str,
602    pub title: &'a str,
603    pub body: &'a str,
604    pub author: &'a str,
605    pub url: &'a str,
606    pub labels: &'a [String],
607    pub origin: &'a str,
608}
609
610fn parent_external_id_from_labels(labels: &[String]) -> Option<&str> {
611    labels
612        .iter()
613        .find_map(|label| label.strip_prefix("parent:"))
614        .filter(|parent| !parent.is_empty())
615}
616
617fn owned_updates(
618    updates: &[(&str, serde_json::Value)],
619) -> anyhow::Result<Vec<(String, serde_json::Value)>> {
620    updates
621        .iter()
622        .map(|(col, val)| {
623            anyhow::ensure!(
624                ALLOWED_FIELDS.contains(col),
625                "column {col} is not in the update allowlist"
626            );
627            Ok((col.to_string(), val.clone()))
628        })
629        .collect()
630}
631
632impl TaskStore {
633    /// Append a lifecycle activity event for a task.
634    #[expect(
635        clippy::too_many_arguments,
636        reason = "one argument per column of the activity row"
637    )]
638    pub async fn append_activity(
639        &self,
640        task_id: i64,
641        event_type: &str,
642        from_status: Option<&str>,
643        to_status: Option<&str>,
644        agent: Option<&str>,
645        model: Option<&str>,
646        details: Option<&serde_json::Value>,
647    ) -> anyhow::Result<()> {
648        let details_json = match details {
649            Some(v) => serde_json::to_string(&sanitize_activity_details(v))?,
650            None => "{}".to_string(),
651        };
652        sqlx::query(
653            "INSERT INTO task_activity
654             (task_id, event_type, from_status, to_status, agent, model, details)
655             VALUES (?, ?, ?, ?, ?, ?, ?)",
656        )
657        .bind(task_id)
658        .bind(event_type)
659        .bind(from_status)
660        .bind(to_status)
661        .bind(agent)
662        .bind(model)
663        .bind(details_json)
664        .execute(&self.pool)
665        .await?;
666        Ok(())
667    }
668
669    /// When the task last entered `status`
670    pub async fn status_entered_at(
671        &self,
672        task_id: i64,
673        status: TaskStatus,
674    ) -> anyhow::Result<Option<String>> {
675        let row: Option<(String,)> = sqlx::query_as(
676            "SELECT timestamp FROM task_activity
677             WHERE task_id = ? AND event_type = 'status_change' AND to_status = ?
678             ORDER BY id DESC LIMIT 1",
679        )
680        .bind(task_id)
681        .bind(status.as_str())
682        .fetch_optional(&self.pool)
683        .await?;
684        Ok(row.map(|(ts,)| ts))
685    }
686
687    /// When the task last entered in_review, and whether a review decision was recorded since
688    pub async fn in_review_entry(&self, task_id: i64) -> anyhow::Result<Option<(String, bool)>> {
689        let row: Option<(String, bool)> = sqlx::query_as(
690            "SELECT a.timestamp, EXISTS (
691                 SELECT 1 FROM task_activity d
692                 WHERE d.task_id = a.task_id AND d.event_type = 'review_decision' AND d.id > a.id
693             )
694             FROM task_activity a
695             WHERE a.task_id = ? AND a.event_type = 'status_change' AND a.to_status = 'in_review'
696             ORDER BY a.id DESC LIMIT 1",
697        )
698        .bind(task_id)
699        .fetch_optional(&self.pool)
700        .await?;
701        Ok(row)
702    }
703
704    /// Get task activity timeline in chronological order.
705    pub async fn get_activity(
706        &self,
707        task_id: i64,
708        limit: Option<usize>,
709    ) -> anyhow::Result<Vec<TaskActivity>> {
710        let rows = if let Some(limit) = limit {
711            let limit_i64 = i64::try_from(limit).unwrap_or(i64::MAX);
712            sqlx::query(sqlx::AssertSqlSafe(format!(
713                "SELECT {TASK_ACTIVITY_COLS} FROM task_activity WHERE task_id = ? ORDER BY timestamp ASC, id ASC LIMIT ?"
714            )))
715            .bind(task_id)
716            .bind(limit_i64)
717            .fetch_all(&self.pool)
718            .await?
719        } else {
720            sqlx::query(sqlx::AssertSqlSafe(format!(
721                "SELECT {TASK_ACTIVITY_COLS} FROM task_activity WHERE task_id = ? ORDER BY timestamp ASC, id ASC"
722            )))
723            .bind(task_id)
724            .fetch_all(&self.pool)
725            .await?
726        };
727
728        rows.iter().map(Self::row_to_activity).collect()
729    }
730
731    pub async fn create(&self, new: &NewTask) -> anyhow::Result<i64> {
732        self.write_int(Command::TaskCreate {
733            new: new.clone(),
734            internal: false,
735        })
736        .await
737    }
738
739    /// Create an internal task, returning its auto-generated ID.
740    ///
741    /// Convenience wrapper around `create()` that sets origin = "internal"
742    /// and generates `external_id = "internal:{id}"` after creation.
743    pub async fn create_internal(
744        &self,
745        repo: &str,
746        title: &str,
747        body: &str,
748        source: &str,
749        source_id: &str,
750        parent_id: Option<i64>,
751    ) -> anyhow::Result<i64> {
752        let new = NewTask {
753            external_id: None,
754            repo: repo.to_string(),
755            origin: "internal".to_string(),
756            title: title.to_string(),
757            body: body.to_string(),
758            source: source.to_string(),
759            source_id: source_id.to_string(),
760            author: String::new(),
761            url: String::new(),
762            labels: vec![],
763            parent_id,
764        };
765        self.write_int(Command::TaskCreate {
766            new,
767            internal: true,
768        })
769        .await
770    }
771
772    /// Get a task by its internal ID.
773    pub async fn get(&self, id: i64) -> anyhow::Result<Task> {
774        let row = sqlx::query(sqlx::AssertSqlSafe(format!(
775            "SELECT {TASK_COLS} FROM tasks WHERE id = ?"
776        )))
777        .bind(id)
778        .fetch_optional(&self.pool)
779        .await?
780        .with_context(|| format!("task {id} not found"))?;
781
782        Self::row_to_task(&row)
783    }
784
785    /// Get a task by its external ID within a repo.
786    pub async fn get_by_external_id(
787        &self,
788        repo: &str,
789        ext_id: &str,
790    ) -> anyhow::Result<Option<Task>> {
791        let row = sqlx::query(sqlx::AssertSqlSafe(format!(
792            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND external_id = ?"
793        )))
794        .bind(repo)
795        .bind(ext_id)
796        .fetch_optional(&self.pool)
797        .await?;
798
799        match row {
800            Some(r) => Ok(Some(Self::row_to_task(&r)?)),
801            None => Ok(None),
802        }
803    }
804
805    /// Lightweight lookup: return only the numeric `id` for a given repo + external_id.
806    ///
807    /// Many call sites only need the internal store ID and were previously calling
808    /// `get_by_external_id()` which deserializes the full 60-column `Task` row. This
809    /// method avoids that work by selecting only `id`.
810    pub async fn resolve_id_by_external(
811        &self,
812        repo: &str,
813        ext_id: &str,
814    ) -> anyhow::Result<Option<i64>> {
815        let row = sqlx::query("SELECT id FROM tasks WHERE repo = ? AND external_id = ?")
816            .bind(repo)
817            .bind(ext_id)
818            .fetch_optional(&self.pool)
819            .await?;
820        let id = row.map(|r| r.try_get::<i64, _>("id")).transpose()?;
821        Ok(id)
822    }
823
824    /// The task whose worktree on this node is `path`
825    pub async fn resolve_id_by_worktree(&self, path: &str) -> anyhow::Result<Option<i64>> {
826        if path.is_empty() {
827            return Ok(None);
828        }
829
830        let row = sqlx::query("SELECT id FROM tasks WHERE worktree = ? LIMIT 1")
831            .bind(path)
832            .fetch_optional(&self.pool)
833            .await?;
834        Ok(row.map(|r| r.try_get::<i64, _>("id")).transpose()?)
835    }
836
837    /// Resolve the parent task ID by PR number.
838    ///
839    /// When a mention arrives on a PR, we need to find the issue task that owns
840    /// this PR. The task store tracks `pr_number` on tasks, so we look up the
841    /// task whose `pr_number` matches the given PR number.
842    pub async fn resolve_id_by_pr_number(
843        &self,
844        repo: &str,
845        pr_number: i32,
846    ) -> anyhow::Result<Option<i64>> {
847        let row = sqlx::query("SELECT id FROM tasks WHERE repo = ? AND pr_number = ? LIMIT 1")
848            .bind(repo)
849            .bind(pr_number)
850            .fetch_optional(&self.pool)
851            .await?;
852        let id = row.map(|r| r.try_get::<i64, _>("id")).transpose()?;
853        Ok(id)
854    }
855
856    /// Lightweight lookup: return only the `agent` column for a given repo + external_id.
857    /// Returns None when no row matches or the agent is NULL/empty.
858    pub async fn get_agent_by_external_id(
859        &self,
860        repo: &str,
861        ext_id: &str,
862    ) -> anyhow::Result<Option<String>> {
863        let row = sqlx::query("SELECT agent FROM tasks WHERE repo = ? AND external_id = ?")
864            .bind(repo)
865            .bind(ext_id)
866            .fetch_optional(&self.pool)
867            .await?;
868        let agent = row
869            .map(|r| r.try_get::<Option<String>, _>("agent"))
870            .transpose()?
871            .flatten();
872        Ok(agent)
873    }
874
875    /// Upsert an external task — insert if new, update title/body/labels if exists.
876    pub async fn upsert_external(&self, ext: &UpsertExternal<'_>) -> anyhow::Result<i64> {
877        let parent_id = match parent_external_id_from_labels(ext.labels) {
878            Some(parent_external_id) => self.resolve_task_id(ext.repo, parent_external_id).await?,
879            None => None,
880        };
881        self.write_int(Command::TaskUpsertExternal(UpsertExternalCmd {
882            repo: ext.repo.to_string(),
883            ext_id: ext.ext_id.to_string(),
884            title: ext.title.to_string(),
885            body: ext.body.to_string(),
886            author: ext.author.to_string(),
887            url: ext.url.to_string(),
888            labels: ext.labels.to_vec(),
889            origin: ext.origin.to_string(),
890            parent_id,
891        }))
892        .await
893    }
894
895    // ---------------------------------------------------------------
896    // Status
897    // ---------------------------------------------------------------
898
899    pub async fn update_status_and_fields(
900        &self,
901        id: i64,
902        status: TaskStatus,
903        updates: &[(&str, serde_json::Value)],
904    ) -> anyhow::Result<()> {
905        let mut updates = owned_updates(updates)?;
906        // Local columns stay on this node, as set_fields keeps them
907        if self.replicates() {
908            let local: Vec<_> = updates
909                .iter()
910                .filter(|(col, _)| is_local_column(col))
911                .cloned()
912                .collect();
913            updates.retain(|(col, _)| !is_local_column(col));
914
915            if !local.is_empty() {
916                let mut tx = self.begin_write().await?;
917                apply::set_fields(&mut tx, &cluster::now_stamp(), id, &local, true).await?;
918                tx.commit().await?;
919            }
920        }
921
922        self.write(Command::TaskUpdateStatusAndFields {
923            id,
924            status,
925            updates,
926        })
927        .await
928        .map(|_| ())
929    }
930
931    /// Update the status of a task.
932    pub async fn update_status(&self, id: i64, status: TaskStatus) -> anyhow::Result<()> {
933        self.write_bool(Command::TaskUpdateStatus {
934            id,
935            status,
936            expected: None,
937        })
938        .await
939        .map(|_| ())
940    }
941
942    /// Persist the external_id (e.g. a newly-created GitHub issue number) onto an existing task row.
943    pub async fn update_external_id(&self, id: i64, external_id: &str) -> anyhow::Result<()> {
944        self.write(Command::TaskUpdateExternalId {
945            id,
946            external_id: external_id.to_string(),
947        })
948        .await
949        .map(|_| ())
950    }
951
952    /// Conditionally update the status of a task only if it currently has `expected` status.
953    ///
954    /// Returns `true` if the row was updated, `false` if the current status did not match
955    /// `expected` (i.e. a concurrent transition already moved the task elsewhere).
956    pub async fn update_status_if(
957        &self,
958        id: i64,
959        status: TaskStatus,
960        expected: TaskStatus,
961    ) -> anyhow::Result<bool> {
962        self.write_bool(Command::TaskUpdateStatus {
963            id,
964            status,
965            expected: Some(expected),
966        })
967        .await
968    }
969
970    /// Touch `updated_at` to now without changing any other field.
971    ///
972    /// Called by the runner immediately after a tmux session exits so that the
973    /// stuck-task recovery timer is reset before response_handler post-processing
974    /// (git push, PR creation, status update) begins.  Without this, a session
975    /// that ran longer than `no_session_stuck_timeout` (default 10 min) would be
976    /// re-dispatched by the tick while the runner is still writing results.
977    pub async fn touch_updated_at(&self, id: i64) -> anyhow::Result<()> {
978        self.write(Command::TaskTouch { id }).await.map(|_| ())
979    }
980
981    /// Reset a task back to `new`.
982    pub async fn reset_to_new(&self, id: i64) -> anyhow::Result<()> {
983        self.write(Command::TaskResetToNew { id }).await.map(|_| ())
984    }
985
986    /// Update the block reason for a task.
987    pub async fn set_block_reason(&self, id: i64, reason: Option<&str>) -> anyhow::Result<()> {
988        let value = reason.map_or(serde_json::Value::Null, |r| {
989            serde_json::Value::String(r.to_string())
990        });
991        self.set_fields(id, &[("block_reason", value)]).await
992    }
993
994    /// List tasks by status within a repo.
995    pub async fn list_by_status(
996        &self,
997        repo: &str,
998        status: TaskStatus,
999    ) -> anyhow::Result<Vec<Task>> {
1000        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1001            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND status = ? ORDER BY created_at DESC"
1002        )))
1003        .bind(repo)
1004        .bind(status.as_str())
1005        .fetch_all(&self.pool)
1006        .await?;
1007
1008        rows.iter().map(Self::row_to_task).collect()
1009    }
1010
1011    /// List tasks that are linked to the given parent task.
1012    pub async fn list_children(&self, repo: &str, parent_id: i64) -> anyhow::Result<Vec<Task>> {
1013        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1014            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND parent_id = ? ORDER BY created_at ASC"
1015        )))
1016        .bind(repo)
1017        .bind(parent_id)
1018        .fetch_all(&self.pool)
1019        .await?;
1020
1021        rows.iter().map(Self::row_to_task).collect()
1022    }
1023
1024    /// List external tasks by status within a repo (origin != 'internal').
1025    pub async fn list_external_by_status(
1026        &self,
1027        repo: &str,
1028        status: TaskStatus,
1029    ) -> anyhow::Result<Vec<Task>> {
1030        let rows = sqlx::query(
1031            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin != 'internal' AND status = ? ORDER BY created_at DESC")),
1032        )
1033        .bind(repo)
1034        .bind(status.as_str())
1035        .fetch_all(&self.pool)
1036        .await?;
1037
1038        rows.iter().map(Self::row_to_task).collect()
1039    }
1040
1041    /// List routable tasks (status = 'new') within a repo.
1042    pub async fn list_routable(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1043        self.list_by_status(repo, TaskStatus::New).await
1044    }
1045
1046    /// List internal tasks by status within a repo (origin = 'internal').
1047    pub async fn list_internal_by_status(
1048        &self,
1049        repo: &str,
1050        status: TaskStatus,
1051    ) -> anyhow::Result<Vec<Task>> {
1052        let rows = sqlx::query(
1053        sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin = 'internal' AND status = ? ORDER BY created_at DESC")),
1054    )
1055    .bind(repo)
1056    .bind(status.as_str())
1057    .fetch_all(&self.pool)
1058    .await?;
1059
1060        rows.iter().map(Self::row_to_task).collect()
1061    }
1062
1063    /// List all internal tasks for a repo (origin = 'internal').
1064    pub async fn list_all_internal(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1065        let rows = sqlx::query(
1066            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin = 'internal' ORDER BY created_at DESC")),
1067        )
1068        .bind(repo)
1069        .fetch_all(&self.pool)
1070        .await?;
1071
1072        rows.iter().map(Self::row_to_task).collect()
1073    }
1074
1075    /// List internal tasks with a specific source value (origin = 'internal' AND source = ?).
1076    /// More efficient than `list_all_internal` + in-memory filter when only a subset is needed.
1077    pub async fn list_internal_by_source(
1078        &self,
1079        repo: &str,
1080        source: &str,
1081    ) -> anyhow::Result<Vec<Task>> {
1082        let rows = sqlx::query(
1083            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin = 'internal' AND source = ? ORDER BY created_at DESC")),
1084        )
1085        .bind(repo)
1086        .bind(source)
1087        .fetch_all(&self.pool)
1088        .await?;
1089
1090        rows.iter().map(Self::row_to_task).collect()
1091    }
1092
1093    /// Return only the `source_id` values for internal tasks with a specific source,
1094    /// created within the last `since_days` days.
1095    /// Use this instead of `list_internal_by_source` when only deduplication is needed —
1096    /// avoids fetching all 57 task columns and bounds memory usage to a rolling window.
1097    pub async fn list_source_ids_by_source(
1098        &self,
1099        repo: &str,
1100        source: &str,
1101        since_days: i64,
1102    ) -> anyhow::Result<Vec<String>> {
1103        let cutoff = format!("-{since_days} days");
1104        let rows = sqlx::query(
1105            "SELECT source_id FROM tasks WHERE repo = ? AND origin = 'internal' AND source = ? AND created_at >= datetime('now', ?)",
1106        )
1107        .bind(repo)
1108        .bind(source)
1109        .bind(&cutoff)
1110        .fetch_all(&self.pool)
1111        .await?;
1112
1113        let mut ids = Vec::with_capacity(rows.len());
1114        for row in &rows {
1115            ids.push(row.try_get::<String, _>("source_id").map_err(|e| {
1116                anyhow::anyhow!(
1117                    "list_source_ids_by_source: failed to decode source_id for repo={repo}, source={source}: {e}"
1118                )
1119            })?);
1120        }
1121        Ok(ids)
1122    }
1123
1124    /// List all active (non-done) tasks for a repo.
1125    pub async fn list_active(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1126        let rows = sqlx::query(
1127            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND status != 'done' ORDER BY created_at DESC")),
1128        )
1129        .bind(repo)
1130        .fetch_all(&self.pool)
1131        .await?;
1132
1133        rows.iter().map(Self::row_to_task).collect()
1134    }
1135
1136    /// List all external tasks for a repo (origin != 'internal').
1137    pub async fn list_all_external(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1138        let rows = sqlx::query(
1139            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin != 'internal' ORDER BY created_at DESC")),
1140        )
1141        .bind(repo)
1142        .fetch_all(&self.pool)
1143        .await?;
1144
1145        rows.iter().map(Self::row_to_task).collect()
1146    }
1147
1148    /// Check whether the store has any external tasks for a repo.
1149    pub async fn has_external_tasks(&self, repo: &str) -> anyhow::Result<bool> {
1150        let has_external = sqlx::query_scalar::<_, i32>(
1151            "SELECT 1 FROM tasks WHERE repo = ? AND origin != 'internal' LIMIT 1",
1152        )
1153        .bind(repo)
1154        .fetch_optional(&self.pool)
1155        .await?
1156        .is_some();
1157        Ok(has_external)
1158    }
1159
1160    /// Return a set of external IDs for all external tasks in a repo.
1161    ///
1162    /// Used to eliminate N+1 queries when ingesting lists of external tasks.
1163    pub async fn existing_external_ids(&self, repo: &str) -> anyhow::Result<HashSet<String>> {
1164        let rows: Vec<(String,)> = sqlx::query_as(
1165            "SELECT external_id FROM tasks WHERE repo = ? AND origin != 'internal' AND external_id IS NOT NULL",
1166        )
1167        .bind(repo)
1168        .fetch_all(&self.pool)
1169        .await?;
1170
1171        Ok(rows
1172            .into_iter()
1173            .filter_map(|(id,)| if id.is_empty() { None } else { Some(id) })
1174            .collect())
1175    }
1176
1177    /// List tasks for `orch service doctor`: active tasks + done tasks updated since `cutoff_str`.
1178    /// Much cheaper than `list_all` on repos with many historical done tasks.
1179    pub async fn list_for_doctor(&self, repo: &str, cutoff_str: &str) -> anyhow::Result<Vec<Task>> {
1180        let rows = sqlx::query(
1181            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND (status != 'done' OR updated_at >= ?) ORDER BY created_at DESC")),
1182        )
1183        .bind(repo)
1184        .bind(cutoff_str)
1185        .fetch_all(&self.pool)
1186        .await?;
1187        rows.iter().map(Self::row_to_task).collect()
1188    }
1189
1190    /// List all tasks for a repo, ordered by creation time descending.
1191    pub async fn list_all(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1192        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1193            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? ORDER BY created_at DESC"
1194        )))
1195        .bind(repo)
1196        .fetch_all(&self.pool)
1197        .await?;
1198        rows.iter().map(Self::row_to_task).collect()
1199    }
1200
1201    /// Return the distinct repo slugs present in the tasks table.
1202    pub async fn distinct_repos(&self) -> anyhow::Result<Vec<String>> {
1203        let rows =
1204            sqlx::query("SELECT DISTINCT repo FROM tasks WHERE repo IS NOT NULL AND repo != ''")
1205                .fetch_all(&self.pool)
1206                .await?;
1207        let mut repos = Vec::new();
1208        for row in &rows {
1209            repos.push(row.try_get::<String, _>("repo").map_err(|e| {
1210                anyhow::anyhow!("distinct_repos: failed to decode 'repo' column: {e}")
1211            })?);
1212        }
1213        Ok(repos)
1214    }
1215
1216    /// List all active (non-done) tasks across all repos.
1217    ///
1218    /// Used by the CLI when no project context is available (e.g. running from
1219    /// a worktree without a `.orch.yml`). Does not require a repo argument.
1220    pub async fn list_all_active_global(&self) -> anyhow::Result<Vec<Task>> {
1221        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1222            "SELECT {TASK_COLS} FROM tasks WHERE status != 'done' ORDER BY created_at DESC"
1223        )))
1224        .fetch_all(&self.pool)
1225        .await?;
1226        rows.iter().map(Self::row_to_task).collect()
1227    }
1228
1229    /// List all tasks across all repos regardless of status.
1230    ///
1231    /// Used by the CLI when `--status all` is passed.
1232    pub async fn list_all_global(&self) -> anyhow::Result<Vec<Task>> {
1233        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1234            "SELECT {TASK_COLS} FROM tasks ORDER BY created_at DESC"
1235        )))
1236        .fetch_all(&self.pool)
1237        .await?;
1238        rows.iter().map(Self::row_to_task).collect()
1239    }
1240
1241    /// List all active tasks matching an optional status filter, across all repos.
1242    ///
1243    /// Used by the CLI fallback path when no project context is available.
1244    pub async fn list_all_by_status_global(&self, status: TaskStatus) -> anyhow::Result<Vec<Task>> {
1245        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1246            "SELECT {TASK_COLS} FROM tasks WHERE status = ? ORDER BY created_at DESC"
1247        )))
1248        .bind(status.as_str())
1249        .fetch_all(&self.pool)
1250        .await?;
1251        rows.iter().map(Self::row_to_task).collect()
1252    }
1253
1254    /// Aggregate cost and token data for a repo.
1255    /// Returns (total_input_tokens, total_output_tokens, total_cost_usd).
1256    pub async fn set_fields(
1257        &self,
1258        id: i64,
1259        updates: &[(&str, serde_json::Value)],
1260    ) -> anyhow::Result<()> {
1261        self.set_fields_split(id, updates, false).await
1262    }
1263
1264    /// Like `set_fields` but does NOT touch `updated_at`.
1265    ///
1266    /// Use this for internal bookkeeping writes (probe timestamps, counters) that
1267    /// must not make a stale task appear recently-modified to triage queries.
1268    pub async fn set_fields_silent(
1269        &self,
1270        id: i64,
1271        updates: &[(&str, serde_json::Value)],
1272    ) -> anyhow::Result<()> {
1273        self.set_fields_split(id, updates, true).await
1274    }
1275
1276    /// Local columns are written on this node only, the rest go through the log
1277    async fn set_fields_split(
1278        &self,
1279        id: i64,
1280        updates: &[(&str, serde_json::Value)],
1281        silent: bool,
1282    ) -> anyhow::Result<()> {
1283        let all = owned_updates(updates)?;
1284        // On one machine nothing is replicated, so the whole update stays one statement
1285        if !self.replicates() {
1286            let mut tx = self.begin_write().await?;
1287            apply::set_fields(&mut tx, &cluster::now_stamp(), id, &all, silent).await?;
1288            tx.commit().await?;
1289            return Ok(());
1290        }
1291
1292        let (local, replicated): (Vec<_>, Vec<_>) =
1293            all.into_iter().partition(|(col, _)| is_local_column(col));
1294        if !local.is_empty() {
1295            let mut tx = self.begin_write().await?;
1296            apply::set_fields(&mut tx, &cluster::now_stamp(), id, &local, silent).await?;
1297            tx.commit().await?;
1298        }
1299
1300        if replicated.is_empty() {
1301            return Ok(());
1302        }
1303
1304        self.write(Command::TaskSetFields {
1305            id,
1306            updates: replicated,
1307            silent,
1308        })
1309        .await
1310        .map(|_| ())
1311    }
1312
1313    /// Increment an integer field by 1, returning the new value.
1314    pub async fn increment(&self, id: i64, field: &str) -> anyhow::Result<i32> {
1315        anyhow::ensure!(
1316            INCREMENTABLE_FIELDS.contains(&field),
1317            "field {field} is not incrementable"
1318        );
1319        let n = self
1320            .write_int(Command::TaskIncrement {
1321                id,
1322                field: field.to_string(),
1323            })
1324            .await?;
1325        Ok(i32::try_from(n)?)
1326    }
1327
1328    /// Reset all failure/retry counters to zero.
1329    pub async fn reset_counters(&self, id: i64) -> anyhow::Result<()> {
1330        self.write(Command::TaskResetCounters {
1331            id,
1332            failure_only: false,
1333        })
1334        .await
1335        .map(|_| ())
1336    }
1337
1338    /// Reset transient failure/retry counters to zero, preserving
1339    /// `review_cycles`, `ci_merge_failures`, and `merge_conflict_retries`.
1340    ///
1341    /// Use this after `RequestChanges` to clear per-attempt noise without
1342    /// undoing the review-cycle count that `handle_review_changes` just set.
1343    /// `ci_merge_failures` and `merge_conflict_retries` are preserved here because
1344    /// they must accumulate across attempts to enforce their circuit breakers,
1345    /// just like `review_cycles`.
1346    ///
1347    /// NOTE: `attempts` and `review_invocations` are intentionally NOT reset here.
1348    /// Both must increase monotonically across the task's lifetime so that
1349    /// `(task_id, attempt, run_type)` keys in `task_runs` remain unique. Resetting
1350    /// either causes subsequent retries to overwrite earlier audit trail records via
1351    /// the ON CONFLICT UPSERT in `start_run()`.
1352    pub async fn reset_failure_counters(&self, id: i64) -> anyhow::Result<()> {
1353        self.write(Command::TaskResetCounters {
1354            id,
1355            failure_only: true,
1356        })
1357        .await
1358        .map(|_| ())
1359    }
1360
1361    // ---------------------------------------------------------------
1362    // Routing
1363    // ---------------------------------------------------------------
1364
1365    /// Store routing results for a task.
1366    pub async fn store_route(&self, route: &StoreRoute<'_>) -> anyhow::Result<()> {
1367        self.write(Command::TaskStoreRoute(StoreRouteCmd {
1368            id: route.id,
1369            agent: route.agent.to_string(),
1370            model: route.model.map(str::to_string),
1371            complexity: route.complexity.to_string(),
1372            estimate: route.estimate,
1373            reason: route.reason.to_string(),
1374            profile: route.profile.to_string(),
1375            skills: route.skills.to_string(),
1376        }))
1377        .await
1378        .map(|_| ())
1379    }
1380
1381    /// Return `(external_id, estimate)` for all non-internal tasks that have a
1382    /// positive Fibonacci estimate stored in the database.
1383    ///
1384    /// Used by startup estimate reconciliation to push orch-stored estimates to
1385    /// the GitHub Projects board for tasks that were routed before the estimate
1386    /// sync feature was available or before `project_estimate_field_id` was
1387    /// configured.
1388    pub async fn list_external_tasks_with_estimates(&self) -> anyhow::Result<Vec<(String, u8)>> {
1389        let rows = sqlx::query(
1390            "SELECT external_id, estimate FROM tasks \
1391             WHERE estimate > 0 AND external_id NOT LIKE 'internal:%' \
1392             AND status NOT IN ('done', 'blocked')",
1393        )
1394        .fetch_all(&self.pool)
1395        .await?;
1396
1397        let mut result = Vec::new();
1398        for row in &rows {
1399            let external_id: String = row.try_get("external_id")?;
1400            let estimate_raw: i64 = row.try_get("estimate")?;
1401            if let Ok(estimate @ 1..) = u8::try_from(estimate_raw) {
1402                result.push((external_id, estimate));
1403            }
1404        }
1405        Ok(result)
1406    }
1407
1408    /// Set the Fibonacci estimate for a task (only if currently 0).
1409    ///
1410    /// Used during ingestion to populate `tasks.estimate` from GitHub Projects
1411    /// when the orch estimate is still 0. This avoids overwriting estimates
1412    /// already set by the router.
1413    pub async fn set_estimate_if_zero(&self, id: i64, estimate: u8) -> anyhow::Result<bool> {
1414        self.write_bool(Command::TaskSetEstimateIfZero { id, estimate })
1415            .await
1416    }
1417
1418    // ---------------------------------------------------------------
1419    // Memory
1420    // ---------------------------------------------------------------
1421
1422    /// Append a memory entry to the task's memory JSON array.
1423    ///
1424    /// Uses a transaction to prevent lost-update races when concurrent
1425    /// callers append simultaneously.
1426    pub async fn append_memory(&self, id: i64, entry: &MemoryEntry) -> anyhow::Result<()> {
1427        self.write(Command::TaskAppendMemory {
1428            id,
1429            entry: entry.clone(),
1430        })
1431        .await
1432        .map(|_| ())
1433    }
1434
1435    /// Get the N most recent memory entries for a task.
1436    pub async fn recent_memory(&self, id: i64, max: usize) -> anyhow::Result<Vec<MemoryEntry>> {
1437        let row = sqlx::query("SELECT memory FROM tasks WHERE id = ?")
1438            .bind(id)
1439            .fetch_one(&self.pool)
1440            .await?;
1441
1442        let memory_opt: Option<String> = row
1443            .try_get::<Option<String>, _>("memory")
1444            .map_err(|e| anyhow::anyhow!("failed to read memory column for task {id}: {e}"))?;
1445        let memory_str = memory_opt.unwrap_or_default();
1446
1447        let mut memory: Vec<MemoryEntry> = if memory_str.trim().is_empty() {
1448            Vec::new()
1449        } else {
1450            serde_json::from_str(&memory_str)
1451                .map_err(|e| anyhow::anyhow!("failed to parse memory JSON for task {id}: {e}"))?
1452        };
1453
1454        memory.sort_by_key(|m| m.attempt);
1455        if memory.len() > max {
1456            memory = memory.split_off(memory.len() - max);
1457        }
1458        Ok(memory)
1459    }
1460
1461    // ---------------------------------------------------------------
1462    // Tokens
1463    // ---------------------------------------------------------------
1464
1465    /// Add a run's token usage to a task, and its cost when the agent reported one.
1466    pub async fn store_tokens(
1467        &self,
1468        id: i64,
1469        input: u64,
1470        output: u64,
1471        cost_usd: Option<f64>,
1472    ) -> anyhow::Result<()> {
1473        sqlx::query(
1474            "UPDATE tasks SET
1475            input_tokens = COALESCE(input_tokens, 0) + ?,
1476            output_tokens = COALESCE(output_tokens, 0) + ?,
1477            total_cost_usd = COALESCE(total_cost_usd, 0.0) + COALESCE(?, 0.0),
1478            updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1479         WHERE id = ?",
1480        )
1481        .bind(i64::try_from(input).unwrap_or_else(|_| {
1482            tracing::warn!(input, "input_tokens overflows i64, clamping to MAX");
1483            i64::MAX
1484        }))
1485        .bind(i64::try_from(output).unwrap_or_else(|_| {
1486            tracing::warn!(output, "output_tokens overflows i64, clamping to MAX");
1487            i64::MAX
1488        }))
1489        .bind(cost_usd)
1490        .bind(id)
1491        .execute(&self.pool)
1492        .await?;
1493
1494        Ok(())
1495    }
1496
1497    // ---------------------------------------------------------------
1498    // Cleanup
1499    // ---------------------------------------------------------------
1500
1501    /// Mark a task's worktree as cleaned.
1502    pub async fn mark_cleaned(&self, id: i64) -> anyhow::Result<()> {
1503        self.write(Command::TaskMarkCleaned { id })
1504            .await
1505            .map(|_| ())
1506    }
1507
1508    /// Batch-update fields for multiple tasks in a single transaction.
1509    ///
1510    /// Each entry is `(id, updates)` where `updates` is a slice of `(column, value)` pairs.
1511    /// Skips entries with empty update slices.
1512    /// Test-only helper / bulk operations used by admin paths and tests.
1513    #[cfg(test)]
1514    pub async fn batch_set_fields(
1515        &self,
1516        updates: &[(i64, &[(&str, serde_json::Value)])],
1517    ) -> anyhow::Result<()> {
1518        if updates.is_empty() {
1519            return Ok(());
1520        }
1521
1522        for (_, entry_updates) in updates {
1523            for (col, _) in *entry_updates {
1524                anyhow::ensure!(
1525                    ALLOWED_FIELDS.contains(col),
1526                    "column {col} is not in the update allowlist"
1527                );
1528            }
1529        }
1530
1531        let mut tx = self.begin_write().await?;
1532        for (id, entry_updates) in updates {
1533            if entry_updates.is_empty() {
1534                continue;
1535            }
1536            let mut set_parts = Vec::new();
1537            let mut values: Vec<Option<String>> = Vec::new();
1538            for (col, val) in *entry_updates {
1539                set_parts.push(format!("{col} = ?"));
1540                values.push(sql_value_for_task_field(col, val)?);
1541            }
1542            set_parts.push("updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')".to_string());
1543            let sql = format!("UPDATE tasks SET {} WHERE id = ?", set_parts.join(", "));
1544            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1545            for v in &values {
1546                query = query.bind(v.as_deref());
1547            }
1548            query = query.bind(*id);
1549            query.execute(&mut *tx).await?;
1550        }
1551        tx.commit().await?;
1552        Ok(())
1553    }
1554
1555    /// Batch-increment a field for multiple tasks in a single transaction.
1556    ///
1557    /// Each entry is `(id, field)`. All fields must be in the incrementable allowlist.
1558    /// Test-only helper for bulk increments used by tests.
1559    #[cfg(test)]
1560    pub async fn batch_increment(&self, entries: &[(i64, &str)]) -> anyhow::Result<()> {
1561        if entries.is_empty() {
1562            return Ok(());
1563        }
1564
1565        for (_, field) in entries {
1566            anyhow::ensure!(
1567                INCREMENTABLE_FIELDS.contains(field),
1568                "field {field} is not incrementable"
1569            );
1570        }
1571
1572        let mut tx = self.begin_write().await?;
1573        for (id, field) in entries {
1574            let sql = format!(
1575                "UPDATE tasks SET {field} = {field} + 1, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = ?"
1576            );
1577            sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
1578                .bind(*id)
1579                .execute(&mut *tx)
1580                .await?;
1581        }
1582        tx.commit().await?;
1583        Ok(())
1584    }
1585
1586    /// Reset transient failure/retry counters for multiple tasks in a single transaction.
1587    ///
1588    /// Preserves `review_cycles`, `ci_merge_failures`, and
1589    /// `merge_conflict_retries` (same semantics as
1590    /// [`reset_failure_counters`]).
1591    /// Test-only helper for bulk counter resets used by tests.
1592    #[cfg(test)]
1593    pub async fn batch_reset_failure_counters(&self, ids: &[i64]) -> anyhow::Result<()> {
1594        // SQLite has a hard limit on host parameters (commonly 999). Chunk the
1595        // IN-list into smaller batches to avoid exceeding the limit.
1596        const CHUNK_SIZE: usize = 500;
1597        if ids.is_empty() {
1598            return Ok(());
1599        }
1600
1601        let mut tx = self.begin_write().await?;
1602
1603        for chunk in ids.chunks(CHUNK_SIZE) {
1604            // Build WHERE id IN (?, ?, ...) placeholders for this chunk
1605            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
1606            let sql = format!(
1607                "UPDATE tasks SET
1608            route_attempts = 0,
1609            review_agent_failures = 0,
1610            review_resets = 0,
1611            review_invocations = 0,
1612            pr_create_failures = 0,
1613            push_failures = 0,
1614            network_retries = 0,
1615            timeout_retries = 0,
1616            auto_unblock_count = 0,
1617            auto_unblock_last_at = '',
1618            auto_unblock_last_reason = '',
1619            ci_recovery_count = 0,
1620            no_code_reroutes = 0,
1621            needs_review_refires = 0,
1622            updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1623         WHERE id IN ({placeholders})",
1624            );
1625            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1626            for id in chunk {
1627                query = query.bind(*id);
1628            }
1629            query.execute(&mut *tx).await?;
1630        }
1631
1632        tx.commit().await?;
1633        Ok(())
1634    }
1635
1636    /// Mark multiple tasks' worktrees as cleaned in a single transaction.
1637    ///
1638    /// Appends a `branch_delete` activity event for each task.
1639    /// Test-only helper for bulk cleaning used by tests.
1640    #[cfg(test)]
1641    pub async fn batch_mark_cleaned(&self, ids: &[i64]) -> anyhow::Result<()> {
1642        // Chunk updates to avoid exceeding SQLite's parameter limit.
1643        const CHUNK_SIZE: usize = 500;
1644        if ids.is_empty() {
1645            return Ok(());
1646        }
1647
1648        let mut tx = self.begin_write().await?;
1649
1650        for chunk in ids.chunks(CHUNK_SIZE) {
1651            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
1652            let sql = format!(
1653                "UPDATE tasks SET worktree_cleaned = 1, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id IN ({placeholders})",
1654            );
1655            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1656            for id in chunk {
1657                query = query.bind(*id);
1658            }
1659            query.execute(&mut *tx).await?;
1660        }
1661
1662        // Append activity for each task (must be done individually — activity is per-task JSON)
1663        // Insert using the same transaction to avoid connection pool exhaustion
1664        // / deadlocks in tests.
1665        let details = serde_json::json!({ "worktree_cleaned": true });
1666        let details_json = serde_json::to_string(&details)?;
1667        for id in ids {
1668            sqlx::query(
1669                "INSERT INTO task_activity (task_id, event_type, from_status, to_status, agent, model, details) VALUES (?, ?, ?, ?, ?, ?, ?)",
1670            )
1671            .bind(*id)
1672            .bind("branch_delete")
1673            .bind(None::<&str>)
1674            .bind(None::<&str>)
1675            .bind(None::<&str>)
1676            .bind(None::<&str>)
1677            .bind(details_json.as_str())
1678            .execute(&mut *tx)
1679            .await?;
1680        }
1681
1682        tx.commit().await?;
1683        Ok(())
1684    }
1685
1686    /// List tasks that are done/blocked with worktrees that haven't been cleaned.
1687    /// Test-only helper and used by cleanup tests.
1688    #[cfg(test)]
1689    pub async fn list_cleanable(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1690        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1691            "SELECT {TASK_COLS} FROM tasks
1692         WHERE repo = ?
1693           AND worktree != ''
1694           AND worktree_cleaned = 0
1695           AND status IN ('done', 'blocked')
1696         ORDER BY updated_at ASC"
1697        )))
1698        .bind(repo)
1699        .fetch_all(&self.pool)
1700        .await?;
1701
1702        rows.iter().map(Self::row_to_task).collect()
1703    }
1704
1705    /// Return external IDs of tasks whose worktrees have already been cleaned.
1706    ///
1707    /// Used by `cleanup_done_worktrees` to skip already-cleaned tasks without
1708    /// issuing one `resolve_task_id + get` per task (N+1 elimination).
1709    pub async fn cleaned_external_ids(&self, repo: &str) -> anyhow::Result<HashSet<String>> {
1710        let rows: Vec<(String,)> =
1711            sqlx::query_as("SELECT external_id FROM tasks WHERE repo = ? AND worktree_cleaned = 1 AND external_id IS NOT NULL")
1712                .bind(repo)
1713                .fetch_all(&self.pool)
1714                .await?;
1715
1716        Ok(rows.into_iter().map(|(id,)| id).collect())
1717    }
1718
1719    // ---------------------------------------------------------------
1720    // Task Runs (audit trail)
1721    // ---------------------------------------------------------------
1722
1723    /// Start a new run, returning its ID.
1724    pub async fn start_run(&self, run: &StartRun<'_>) -> anyhow::Result<i64> {
1725        // Self-heal: an earlier attempt of the same run_type may have been
1726        // abandoned by a crash or SIGKILL that never reached
1727        // complete_run/finalize_incomplete_runs. Close those out now so they
1728        // don't leak as permanently-open rows. Scoped to the same run_type so
1729        // a still-open run of a *different* run_type (e.g. an agent run left
1730        // open when a review run starts) is untouched here — that case is
1731        // handled by the explicit finalize_incomplete_runs calls in the
1732        // engine's recovery paths, not this opportunistic self-heal. The
1733        // current attempt is excluded so a legitimate retry of the same run
1734        // still goes through the ON CONFLICT upsert below instead of being
1735        // finalized out from under itself.
1736        let orphaned: Vec<(i64,)> = sqlx::query_as(
1737            "SELECT id FROM task_runs
1738             WHERE task_id = ? AND run_type = ? AND attempt != ?
1739               AND outcome IS NULL AND completed_at IS NULL",
1740        )
1741        .bind(run.task_id)
1742        .bind(run.run_type)
1743        .bind(run.attempt)
1744        .fetch_all(&self.pool)
1745        .await?;
1746
1747        for (run_id,) in orphaned {
1748            self.complete_run(&CompleteRun {
1749                run_id,
1750                exit_code: Some(-1),
1751                stdout: "",
1752                stderr: "",
1753                parsed: "",
1754                outcome: "aborted",
1755                error: "superseded by a new run start before this one completed",
1756                tokens: RunTokenUsage::default(),
1757            })
1758            .await?;
1759        }
1760
1761        let row = sqlx::query(
1762            "INSERT INTO task_runs (task_id, attempt, run_type, agent, model, command, prompt, outcome)
1763         VALUES (?, ?, ?, ?, ?, ?, ?, NULL)
1764         ON CONFLICT(task_id, attempt, run_type) DO UPDATE SET
1765            agent = excluded.agent, model = excluded.model,
1766            command = excluded.command, prompt = excluded.prompt,
1767            outcome = NULL,
1768            started_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now'),
1769            completed_at = NULL, error = NULL, exit_code = NULL,
1770            stdout = NULL, stderr = NULL, parsed_response = NULL,
1771            duration_secs = NULL, input_tokens = NULL, output_tokens = NULL,
1772            total_cost_usd = NULL
1773         RETURNING id",
1774        )
1775        .bind(run.task_id)
1776        .bind(run.attempt)
1777        .bind(run.run_type)
1778        .bind(run.agent)
1779        .bind(run.model)
1780        .bind(run.command)
1781        .bind(run.prompt)
1782        .fetch_one(&self.pool)
1783        .await?;
1784
1785        let run_id: i64 = row
1786            .try_get("id")
1787            .map_err(|e| anyhow::anyhow!("INSERT RETURNING id returned NULL or invalid: {e}"))?;
1788        let event_type = match run.run_type {
1789            "review" => "review_start",
1790            _ => "dispatch",
1791        };
1792        let details = serde_json::json!({
1793            "run_type": run.run_type,
1794            "attempt": run.attempt,
1795            "command": run.command,
1796        });
1797        self.append_activity(
1798            run.task_id,
1799            event_type,
1800            None,
1801            None,
1802            Some(run.agent),
1803            Some(run.model),
1804            Some(&details),
1805        )
1806        .await?;
1807
1808        Ok(run_id)
1809    }
1810
1811    /// Complete a run with results.
1812    pub async fn complete_run(&self, run: &CompleteRun<'_>) -> anyhow::Result<()> {
1813        let run_ctx =
1814            sqlx::query("SELECT task_id, run_type, agent, model FROM task_runs WHERE id = ?")
1815                .bind(run.run_id)
1816                .fetch_optional(&self.pool)
1817                .await?;
1818        let updated = sqlx::query(
1819            "UPDATE task_runs SET
1820            exit_code = ?, stdout = ?, stderr = ?, parsed_response = ?,
1821            outcome = ?, error = NULLIF(?, ''),
1822            input_tokens = ?, output_tokens = ?, total_cost_usd = ?,
1823            duration_secs = CASE WHEN ? > 0 THEN ?
1824                ELSE MAX(0, (julianday('now') - julianday(started_at)) * 86400.0) END,
1825            completed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1826         WHERE id = ? AND outcome IS NULL",
1827        )
1828        .bind(run.exit_code)
1829        .bind(run.stdout)
1830        .bind(security::redact(run.stderr))
1831        .bind(run.parsed)
1832        .bind(run.outcome)
1833        .bind(security::redact(run.error))
1834        .bind(i64::try_from(run.tokens.input_tokens).unwrap_or_else(|_| {
1835            tracing::warn!(
1836                run.tokens.input_tokens,
1837                "input_tokens overflows i64, clamping to MAX"
1838            );
1839            i64::MAX
1840        }))
1841        .bind(i64::try_from(run.tokens.output_tokens).unwrap_or_else(|_| {
1842            tracing::warn!(
1843                run.tokens.output_tokens,
1844                "output_tokens overflows i64, clamping to MAX"
1845            );
1846            i64::MAX
1847        }))
1848        .bind(run.tokens.total_cost_usd)
1849        .bind(run.tokens.duration_secs)
1850        .bind(run.tokens.duration_secs)
1851        .bind(run.run_id)
1852        .execute(&self.pool)
1853        .await?;
1854        if updated.rows_affected() == 0 && run_ctx.is_some() {
1855            // The first writer knows the cause, so a closed run keeps its outcome and error
1856            tracing::debug!(
1857                run_id = run.run_id,
1858                "run already closed, keeping its outcome"
1859            );
1860            return Ok(());
1861        }
1862        if let Some(run_ctx) = run_ctx {
1863            let task_id: i64 = run_ctx.try_get("task_id").map_err(|e| {
1864                anyhow::anyhow!("failed to decode task_id from run_id {}: {e}", run.run_id)
1865            })?;
1866            let run_type: String = run_ctx.try_get("run_type").map_err(|e| {
1867                anyhow::anyhow!("failed to decode run_type from run_id {}: {e}", run.run_id)
1868            })?;
1869            let agent: String = run_ctx.try_get("agent").map_err(|e| {
1870                anyhow::anyhow!("failed to decode agent from run_id {}: {e}", run.run_id)
1871            })?;
1872            let model: String = run_ctx.try_get("model").map_err(|e| {
1873                anyhow::anyhow!("failed to decode model from run_id {}: {e}", run.run_id)
1874            })?;
1875            if run.outcome == "timeout" {
1876                self.append_activity(
1877                    task_id,
1878                    "timeout",
1879                    None,
1880                    None,
1881                    Some(&agent),
1882                    Some(&model),
1883                    Some(&serde_json::json!({
1884                        "run_type": run_type,
1885                        "error": run.error,
1886                        "stderr": run.stderr,
1887                    })),
1888                )
1889                .await?;
1890            } else if run.outcome != "success" {
1891                self.append_activity(
1892                    task_id,
1893                    "error",
1894                    None,
1895                    None,
1896                    Some(&agent),
1897                    Some(&model),
1898                    Some(&serde_json::json!({
1899                        "run_type": run_type,
1900                        "outcome": run.outcome,
1901                        "error": run.error,
1902                    })),
1903                )
1904                .await?;
1905            }
1906        } else if run.outcome != "success" {
1907            tracing::warn!(
1908                run_id = run.run_id,
1909                outcome = run.outcome,
1910                "run_id not found in task_runs; skipping timeout/error activity log"
1911            );
1912        }
1913        Ok(())
1914    }
1915
1916    /// Finalize all incomplete runs for a task.
1917    ///
1918    /// This is used on runner early-return/error paths and engine shutdown
1919    /// paths to ensure every `start_run` has a terminal record.
1920    pub async fn finalize_incomplete_runs(
1921        &self,
1922        task_id: i64,
1923        outcome: &str,
1924        error: &str,
1925    ) -> anyhow::Result<u64> {
1926        let rows: Vec<(i64,)> = sqlx::query_as(
1927            "SELECT id FROM task_runs
1928             WHERE task_id = ?
1929               AND outcome IS NULL
1930               AND completed_at IS NULL
1931             ORDER BY id ASC",
1932        )
1933        .bind(task_id)
1934        .fetch_all(&self.pool)
1935        .await?;
1936
1937        for (run_id,) in &rows {
1938            self.complete_run(&CompleteRun {
1939                run_id: *run_id,
1940                exit_code: Some(-1),
1941                stdout: "",
1942                stderr: "",
1943                parsed: "",
1944                outcome,
1945                error,
1946                tokens: RunTokenUsage::default(),
1947            })
1948            .await?;
1949        }
1950
1951        Ok(rows.len() as u64)
1952    }
1953
1954    /// Get all runs for a task, ordered by attempt.
1955    pub async fn get_runs(&self, task_id: i64) -> anyhow::Result<Vec<TaskRun>> {
1956        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1957            "SELECT {TASK_RUN_COLS} FROM task_runs WHERE task_id = ? ORDER BY attempt ASC, run_type ASC"
1958        )))
1959        .bind(task_id)
1960        .fetch_all(&self.pool)
1961        .await?;
1962
1963        rows.iter().map(Self::row_to_run).collect()
1964    }
1965
1966    /// Get all runs for a batch of task IDs in a single query, grouped by task ID.
1967    /// Returns an empty map if `task_ids` is empty.
1968    pub async fn get_runs_batch(
1969        &self,
1970        task_ids: &[i64],
1971    ) -> anyhow::Result<HashMap<i64, Vec<TaskRun>>> {
1972        // SQLite has a hard limit on host parameters (commonly 999). Chunk the
1973        // IN-list into smaller batches to avoid exceeding the limit.
1974        const CHUNK_SIZE: usize = 500;
1975        if task_ids.is_empty() {
1976            return Ok(HashMap::new());
1977        }
1978
1979        let mut result: HashMap<i64, Vec<TaskRun>> = HashMap::new();
1980
1981        for chunk in task_ids.chunks(CHUNK_SIZE) {
1982            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
1983            let sql = format!(
1984                "SELECT {TASK_RUN_COLS} FROM task_runs WHERE task_id IN ({placeholders}) ORDER BY task_id ASC, attempt ASC, run_type ASC"
1985            );
1986            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1987            for id in chunk {
1988                query = query.bind(*id);
1989            }
1990            let rows = query.fetch_all(&self.pool).await?;
1991            for row in &rows {
1992                let run = Self::row_to_run(row)?;
1993                result.entry(run.task_id).or_default().push(run);
1994            }
1995        }
1996        Ok(result)
1997    }
1998
1999    /// Get multiple tasks by ID in a single query, returning a map from task ID to Task.
2000    /// Returns an empty map if `ids` is empty.
2001    pub async fn get_batch(&self, ids: &[i64]) -> anyhow::Result<HashMap<i64, Task>> {
2002        // SQLite has a hard limit on host parameters (commonly 999). Chunk the
2003        // IN-list into smaller batches to avoid exceeding the limit.
2004        const CHUNK_SIZE: usize = 500;
2005        if ids.is_empty() {
2006            return Ok(HashMap::new());
2007        }
2008
2009        let mut result = HashMap::new();
2010
2011        for chunk in ids.chunks(CHUNK_SIZE) {
2012            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
2013            let sql = format!("SELECT {TASK_COLS} FROM tasks WHERE id IN ({placeholders})");
2014            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
2015            for id in chunk {
2016                query = query.bind(*id);
2017            }
2018            let rows = query.fetch_all(&self.pool).await?;
2019            for row in &rows {
2020                let task = Self::row_to_task(row)?;
2021                result.insert(task.id, task);
2022            }
2023        }
2024        Ok(result)
2025    }
2026
2027    /// Get statuses for a batch of external IDs in a single query.
2028    /// Returns a map from external_id -> TaskStatus for rows found in the DB.
2029    /// Missing external_ids are simply not present in the returned map.
2030    /// Uses chunking to avoid SQLite parameter limits.
2031    pub async fn get_statuses_by_external_ids(
2032        &self,
2033        repo: &str,
2034        ext_ids: &[&str],
2035    ) -> anyhow::Result<HashMap<String, TaskStatus>> {
2036        const CHUNK_SIZE: usize = 500;
2037        let mut result = HashMap::new();
2038        if ext_ids.is_empty() {
2039            return Ok(result);
2040        }
2041
2042        for chunk in ext_ids.chunks(CHUNK_SIZE) {
2043            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
2044            let sql = format!(
2045                "SELECT external_id, status FROM tasks WHERE repo = ? AND external_id IN ({placeholders})"
2046            );
2047            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
2048            query = query.bind(repo);
2049            for id in chunk {
2050                query = query.bind(id);
2051            }
2052            let rows = query.fetch_all(&self.pool).await?;
2053            for row in &rows {
2054                let external_id: Result<String, _> = row.try_get("external_id");
2055                let status_str: Result<String, _> = row.try_get("status");
2056                match (external_id, status_str) {
2057                    (Ok(ext_id), Ok(sts)) => {
2058                        if ext_id.is_empty() {
2059                            tracing::warn!(status = %sts, "skipping task row with empty external_id");
2060                            continue;
2061                        }
2062                        if let Some(status) = TaskStatus::from_str(&sts) {
2063                            result.insert(ext_id, status);
2064                        } else {
2065                            tracing::warn!(external_id = %ext_id, status = %sts, "unknown status value in tasks table");
2066                        }
2067                    }
2068                    (Err(e), _) => {
2069                        tracing::warn!(error = %e, "skipping task row with decode error for external_id");
2070                    }
2071                    (_, Err(e)) => {
2072                        tracing::warn!(error = %e, "skipping task row with decode error for status");
2073                    }
2074                }
2075            }
2076        }
2077
2078        Ok(result)
2079    }
2080
2081    /// Get the last run of a specific type for a task.
2082    /// Used by tests to assert run ordering.
2083    #[cfg(test)]
2084    pub async fn get_last_run(
2085        &self,
2086        task_id: i64,
2087        run_type: &str,
2088    ) -> anyhow::Result<Option<TaskRun>> {
2089        let row = sqlx::query(sqlx::AssertSqlSafe(format!(
2090        "SELECT {TASK_RUN_COLS} FROM task_runs WHERE task_id = ? AND run_type = ? ORDER BY attempt DESC LIMIT 1"
2091    )))
2092    .bind(task_id)
2093    .bind(run_type)
2094    .fetch_optional(&self.pool)
2095    .await?;
2096
2097        match row {
2098            Some(r) => Ok(Some(Self::row_to_run(&r)?)),
2099            None => Ok(None),
2100        }
2101    }
2102
2103    /// List distinct agents that have previously reviewed this task.
2104    /// Used to exclude them when picking the next review agent, so a different
2105    /// agent reviews each cycle (avoids the same agent hitting the same issues).
2106    pub async fn previous_review_agents(&self, task_id: i64) -> anyhow::Result<Vec<String>> {
2107        let rows: Vec<(String,)> = sqlx::query_as(
2108            "SELECT DISTINCT agent FROM task_runs \
2109             WHERE task_id = ? AND run_type = 'review' \
2110             ORDER BY agent",
2111        )
2112        .bind(task_id)
2113        .fetch_all(&self.pool)
2114        .await?;
2115        Ok(rows.into_iter().map(|(a,)| a).collect())
2116    }
2117
2118    /// Clear the text columns of runs for tasks that entered `done` over `days` days ago
2119    /// Metrics stay, and blocked tasks keep every run
2120    pub async fn prune_old_runs(&self, days: i32) -> anyhow::Result<u64> {
2121        let result = sqlx::query(
2122            "UPDATE task_runs
2123             SET stdout = '', stderr = '', prompt = '', parsed_response = ''
2124             WHERE (stdout != '' OR stderr != '' OR prompt != '' OR parsed_response != '')
2125               AND task_id IN (
2126                 SELECT t.id FROM tasks t
2127                 WHERE t.status = 'done'
2128                   AND (SELECT MAX(a.timestamp) FROM task_activity a
2129                        WHERE a.task_id = t.id
2130                          AND a.event_type = 'status_change'
2131                          AND a.to_status = 'done')
2132                       < strftime('%Y-%m-%dT%H:%M:%SZ', 'now', ? || ' days')
2133               )",
2134        )
2135        .bind(format!("-{days}"))
2136        .execute(&self.pool)
2137        .await?;
2138
2139        if result.rows_affected() > 0 {
2140            sqlx::query("PRAGMA incremental_vacuum")
2141                .execute(&self.pool)
2142                .await?;
2143        }
2144
2145        Ok(result.rows_affected())
2146    }
2147
2148    // ---------------------------------------------------------------
2149    // Row mapping helpers
2150    // ---------------------------------------------------------------
2151
2152    fn row_to_task(row: &SqliteRow) -> anyhow::Result<Task> {
2153        // Diagnostic: verify column count matches expected to prevent OOB panics
2154        // when schema changes without updating TASK_COLS
2155        let col_count = row.len();
2156        if col_count != TASK_COLS_COUNT {
2157            tracing::warn!(
2158                expected = TASK_COLS_COUNT,
2159                actual = col_count,
2160                "TASK_COLS column count mismatch - schema may have changed"
2161            );
2162        }
2163
2164        let status_str: String = row
2165            .try_get("status")
2166            .map_err(|e| anyhow::anyhow!("task row missing status column: {e}"))?;
2167        let labels_str: String = row.try_get("labels").unwrap_or_default();
2168        let memory_str: String = row.try_get("memory").unwrap_or_default();
2169        let delegations_str: String = row.try_get("delegations").unwrap_or_default();
2170
2171        Ok(Task {
2172            id: row
2173                .try_get("id")
2174                .map_err(|e| anyhow::anyhow!("task row missing id: {e}"))?,
2175            external_id: row.try_get("external_id").unwrap_or(None),
2176            repo: row
2177                .try_get("repo")
2178                .map_err(|e| anyhow::anyhow!("task row missing or invalid repo: {e}"))?,
2179            origin: row
2180                .try_get("origin")
2181                .map_err(|e| anyhow::anyhow!("task row missing or invalid origin: {e}"))?,
2182            title: row
2183                .try_get("title")
2184                .map_err(|e| anyhow::anyhow!("task row missing or invalid title: {e}"))?,
2185            body: row
2186                .try_get("body")
2187                .map_err(|e| anyhow::anyhow!("task row missing or invalid body: {e}"))?,
2188            status: TaskStatus::from_str(&status_str)
2189                .ok_or_else(|| anyhow::anyhow!("unknown task status: '{status_str}'"))?,
2190            source: row
2191                .try_get("source")
2192                .map_err(|e| anyhow::anyhow!("task row missing or invalid source: {e}"))?,
2193            source_id: row
2194                .try_get("source_id")
2195                .map_err(|e| anyhow::anyhow!("task row missing or invalid source_id: {e}"))?,
2196            author: row
2197                .try_get("author")
2198                .map_err(|e| anyhow::anyhow!("task row missing or invalid author: {e}"))?,
2199            url: row
2200                .try_get("url")
2201                .map_err(|e| anyhow::anyhow!("task row missing or invalid url: {e}"))?,
2202            labels: serde_json::from_str(&labels_str)
2203                .inspect_err(
2204                    |e| tracing::warn!(error = %e, "corrupt labels JSON, defaulting to empty"),
2205                )
2206                .unwrap_or_default(),
2207            agent: row.try_get("agent").unwrap_or(None),
2208            model: row.try_get("model").unwrap_or(None),
2209            complexity: row.try_get("complexity").unwrap_or_default(),
2210            estimate: {
2211                let raw: i32 = row.try_get("estimate").unwrap_or(0);
2212                u8::try_from(raw).unwrap_or_else(|_| {
2213                    tracing::warn!(
2214                        raw_estimate = raw,
2215                        "estimate value out of u8 range; defaulting to 0"
2216                    );
2217                    0
2218                })
2219            },
2220            route_reason: row.try_get("route_reason").unwrap_or_default(),
2221            agent_profile: row.try_get("agent_profile").unwrap_or_default(),
2222            selected_skills: row.try_get("selected_skills").unwrap_or_default(),
2223            route_attempts: row.try_get("route_attempts").unwrap_or(0),
2224            attempts: row.try_get("attempts").unwrap_or(0),
2225            branch: row.try_get("branch").unwrap_or_default(),
2226            worktree: row.try_get("worktree").unwrap_or_default(),
2227            worktree_cleaned: row.try_get::<i32, _>("worktree_cleaned").unwrap_or(0) != 0,
2228            owner_node: row.try_get("owner_node").unwrap_or(None),
2229            summary: row.try_get("summary").unwrap_or_default(),
2230            last_error: row.try_get("last_error").unwrap_or_default(),
2231            parent_id: row.try_get("parent_id").unwrap_or(None),
2232            block_reason: row.try_get("block_reason").unwrap_or(None),
2233            pr_number: row.try_get("pr_number").unwrap_or(None),
2234            pr_review_context: row.try_get("pr_review_context").unwrap_or_default(),
2235            last_review_ts: row.try_get("last_review_ts").unwrap_or_default(),
2236            review_ts_map: row.try_get("review_ts_map").unwrap_or_default(),
2237            last_comment_review_ts: row.try_get("last_comment_review_ts").unwrap_or_default(),
2238            merge_conflict_retries: row.try_get("merge_conflict_retries").unwrap_or(0),
2239            ci_merge_failures: row.try_get("ci_merge_failures").unwrap_or(0),
2240            pr_create_failures: row.try_get("pr_create_failures").unwrap_or(0),
2241            push_failures: row.try_get::<i32, _>("push_failures").unwrap_or(0),
2242            network_retries: row.try_get::<i32, _>("network_retries").unwrap_or(0),
2243            timeout_retries: row.try_get::<i32, _>("timeout_retries").unwrap_or(0),
2244            review_agent_failures: row.try_get("review_agent_failures").unwrap_or(0),
2245            review_resets: row.try_get::<i32, _>("review_resets").unwrap_or(0),
2246            review_cycles: row.try_get("review_cycles").unwrap_or(0),
2247            review_invocations: row.try_get::<i32, _>("review_invocations").unwrap_or(0),
2248            needs_review_refires: row.try_get::<i32, _>("needs_review_refires").unwrap_or(0),
2249            input_tokens: row.try_get("input_tokens").unwrap_or(0),
2250            output_tokens: row.try_get("output_tokens").unwrap_or(0),
2251            total_cost_usd: row.try_get("total_cost_usd").unwrap_or(0.0),
2252            model_reroute_chain: row.try_get("model_reroute_chain").unwrap_or_default(),
2253            limit_reroute_chain: row.try_get("limit_reroute_chain").unwrap_or_default(),
2254            memory: serde_json::from_str(&memory_str)
2255                .inspect_err(
2256                    |e| tracing::warn!(error = %e, "corrupt memory JSON, defaulting to empty"),
2257                )
2258                .unwrap_or_default(),
2259            delegations: serde_json::from_str(&delegations_str)
2260                .inspect_err(
2261                    |e| tracing::warn!(error = %e, "corrupt delegations JSON, defaulting to empty"),
2262                )
2263                .unwrap_or_default(),
2264            // Some deployments may be running databases that haven't had
2265            // the most recent migrations applied. Use `try_get` with
2266            // sensible defaults for recently-added columns so a missing
2267            // column doesn't cause a panic in the sqlx row accessor.
2268            auto_unblock_count: row.try_get::<i32, _>("auto_unblock_count").unwrap_or(0),
2269            auto_unblock_last_at: row
2270                .try_get::<String, _>("auto_unblock_last_at")
2271                .unwrap_or_default(),
2272            auto_unblock_last_reason: row
2273                .try_get::<String, _>("auto_unblock_last_reason")
2274                .unwrap_or_default(),
2275            ci_recovery_count: row.try_get::<i32, _>("ci_recovery_count").unwrap_or(0),
2276            no_code_reroutes: row.try_get::<i32, _>("no_code_reroutes").unwrap_or(0),
2277            no_code_last_agent: row
2278                .try_get::<String, _>("no_code_last_agent")
2279                .unwrap_or_default(),
2280            created_at: row
2281                .try_get::<String, _>("created_at")
2282                .map_err(|e| anyhow::anyhow!("task row missing created_at: {e}"))?,
2283            updated_at: row
2284                .try_get::<String, _>("updated_at")
2285                .map_err(|e| anyhow::anyhow!("task row missing updated_at: {e}"))?,
2286        })
2287    }
2288    pub async fn ensure_external_task(
2289        &self,
2290        repo: &str,
2291        ext: &ExternalTask,
2292    ) -> anyhow::Result<i64> {
2293        self.upsert_external(&UpsertExternal {
2294            repo,
2295            ext_id: &ext.id.0,
2296            title: &ext.title,
2297            body: &ext.body,
2298            author: &ext.author,
2299            url: &ext.url,
2300            labels: &ext.labels,
2301            origin: "github",
2302        })
2303        .await
2304    }
2305
2306    /// Resolve a task_id (e.g. "42" or "internal:3") to a store internal ID.
2307    /// Returns None if the task is not in the store yet.
2308    pub async fn resolve_task_id(&self, repo: &str, task_id: &str) -> anyhow::Result<Option<i64>> {
2309        if let Some(suffix) = task_id.strip_prefix("internal:") {
2310            // Internal tasks: look up by external_id="internal:{n}" with origin='internal'
2311            if let Some(id) = self.resolve_id_by_external(repo, task_id).await? {
2312                return Ok(Some(id));
2313            }
2314            // Fallback: try the numeric suffix as a direct store ID.
2315            // Must include the repo filter to avoid resolving a task that
2316            // belongs to a different repository in a multi-repo setup.
2317            if let Ok(id) = suffix.parse::<i64>() {
2318                let exists = sqlx::query("SELECT id FROM tasks WHERE id = ? AND repo = ?")
2319                    .bind(id)
2320                    .bind(repo)
2321                    .fetch_optional(&self.pool)
2322                    .await?;
2323                if exists.is_some() {
2324                    return Ok(Some(id));
2325                }
2326            }
2327            return Ok(None);
2328        }
2329        // External tasks: look up by external_id using the lightweight id-only
2330        // lookup to avoid deserializing the full Task row.
2331        self.resolve_id_by_external(repo, task_id).await
2332    }
2333
2334    /// Resolve a task_id globally (across all repos) when a repo-scoped lookup fails.
2335    ///
2336    /// Returns `(internal_id, repo)` if found unambiguously, `None` if not found,
2337    /// or an error if the external_id matches tasks in multiple repos (GitHub issue
2338    /// numbers are not globally unique).
2339    pub async fn resolve_task_id_globally(
2340        &self,
2341        task_id: &str,
2342    ) -> anyhow::Result<Option<(i64, String)>> {
2343        if let Some(suffix) = task_id.strip_prefix("internal:") {
2344            // internal:N — external_id is globally unique; primary key is also unique.
2345            let row = sqlx::query("SELECT id, repo FROM tasks WHERE external_id = ?")
2346                .bind(task_id)
2347                .fetch_optional(&self.pool)
2348                .await?;
2349            if let Some(r) = row {
2350                return Ok(Some((r.try_get("id")?, r.try_get("repo")?)));
2351            }
2352            if let Ok(id) = suffix.parse::<i64>() {
2353                let row = sqlx::query("SELECT id, repo FROM tasks WHERE id = ?")
2354                    .bind(id)
2355                    .fetch_optional(&self.pool)
2356                    .await?;
2357                if let Some(r) = row {
2358                    return Ok(Some((r.try_get("id")?, r.try_get("repo")?)));
2359                }
2360            }
2361            return Ok(None);
2362        }
2363        // External IDs (e.g. GitHub issue numbers) may collide across repos.
2364        let rows = sqlx::query("SELECT id, repo FROM tasks WHERE external_id = ?")
2365            .bind(task_id)
2366            .fetch_all(&self.pool)
2367            .await?;
2368        match rows.len() {
2369            0 => Ok(None),
2370            1 => Ok(Some((rows[0].try_get("id")?, rows[0].try_get("repo")?))),
2371            _ => {
2372                let repos: Vec<String> = rows
2373                    .iter()
2374                    .filter_map(|r| r.try_get::<String, _>("repo").ok())
2375                    .collect();
2376                anyhow::bail!(
2377                    "task ID {task_id:?} is ambiguous — found in multiple repos: {}. \
2378                     Run from the repo worktree so the current-repo lookup resolves it.",
2379                    repos.join(", ")
2380                )
2381            }
2382        }
2383    }
2384
2385    /// Resolve a CLI task id. A bare number in a project is that project's issue first
2386    pub async fn resolve_cli_id(
2387        &self,
2388        spec: &str,
2389        current_repo: Option<&str>,
2390    ) -> anyhow::Result<Option<CliTaskRef>> {
2391        let current_repo = current_repo.filter(|repo| !repo.is_empty());
2392        match parse_cli_task_spec(spec)? {
2393            CliTaskSpec::Internal(number) => self.lookup_internal_cli_id(number).await,
2394            CliTaskSpec::Issue { repo, number } => self.lookup_issue_cli_id(&repo, number).await,
2395            CliTaskSpec::Bare(number) => {
2396                if let Some(repo) = current_repo {
2397                    if let Some(task) = self.lookup_issue_cli_id(repo, number).await? {
2398                        return Ok(Some(task));
2399                    }
2400                    if let Some(task) = self.lookup_internal_in_repo(number, repo).await? {
2401                        return Ok(Some(task));
2402                    }
2403                    return Ok(None);
2404                }
2405                let mut matches = self.lookup_bare_cli_id_matches(number).await?;
2406                match matches.len() {
2407                    0 => Ok(None),
2408                    1 => Ok(matches.pop()),
2409                    _ => Err(ambiguous_cli_id_error(spec, &matches)),
2410                }
2411            }
2412        }
2413    }
2414
2415    async fn lookup_internal_cli_id(&self, number: i64) -> anyhow::Result<Option<CliTaskRef>> {
2416        let ext = format!("internal:{number}");
2417        let rows = sqlx::query(
2418            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2419             WHERE origin = 'internal' AND (id = ? OR external_id = ?) \
2420             ORDER BY id",
2421        )
2422        .bind(number)
2423        .bind(&ext)
2424        .fetch_all(&self.pool)
2425        .await?;
2426        let mut matches = dedup_cli_refs(rows)?;
2427        match matches.len() {
2428            0 => Ok(None),
2429            1 => Ok(matches.pop()),
2430            _ => Err(ambiguous_cli_id_error(&ext, &matches)),
2431        }
2432    }
2433
2434    async fn lookup_internal_in_repo(
2435        &self,
2436        number: i64,
2437        repo: &str,
2438    ) -> anyhow::Result<Option<CliTaskRef>> {
2439        let row = sqlx::query(
2440            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2441             WHERE origin = 'internal' AND id = ? AND repo = ?",
2442        )
2443        .bind(number)
2444        .bind(repo)
2445        .fetch_optional(&self.pool)
2446        .await?;
2447        match row {
2448            Some(row) => Ok(Some(CliTaskRef::from_row(&row)?)),
2449            None => Ok(None),
2450        }
2451    }
2452
2453    async fn lookup_issue_cli_id(
2454        &self,
2455        repo: &str,
2456        number: i64,
2457    ) -> anyhow::Result<Option<CliTaskRef>> {
2458        let ext = number.to_string();
2459        let rows = sqlx::query(
2460            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2461             WHERE repo = ? AND origin != 'internal' AND external_id = ? \
2462             ORDER BY id",
2463        )
2464        .bind(repo)
2465        .bind(&ext)
2466        .fetch_all(&self.pool)
2467        .await?;
2468        let mut matches = dedup_cli_refs(rows)?;
2469        match matches.len() {
2470            0 => Ok(None),
2471            1 => Ok(matches.pop()),
2472            _ => Err(ambiguous_cli_id_error(
2473                &format!("{repo}#{number}"),
2474                &matches,
2475            )),
2476        }
2477    }
2478
2479    async fn lookup_bare_cli_id_matches(&self, number: i64) -> anyhow::Result<Vec<CliTaskRef>> {
2480        let ext = number.to_string();
2481        let rows = sqlx::query(
2482            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2483             WHERE (origin = 'internal' AND id = ?) \
2484                OR (origin != 'internal' AND external_id = ?) \
2485             ORDER BY repo, id",
2486        )
2487        .bind(number)
2488        .bind(&ext)
2489        .fetch_all(&self.pool)
2490        .await?;
2491        dedup_cli_refs(rows)
2492    }
2493
2494    fn row_to_run(row: &SqliteRow) -> anyhow::Result<TaskRun> {
2495        Ok(TaskRun {
2496            id: row
2497                .try_get("id")
2498                .map_err(|e| anyhow::anyhow!("run row decode error for id: {e}"))?,
2499            task_id: row
2500                .try_get("task_id")
2501                .map_err(|e| anyhow::anyhow!("run row decode error for task_id: {e}"))?,
2502            attempt: row
2503                .try_get("attempt")
2504                .map_err(|e| anyhow::anyhow!("run row decode error for attempt: {e}"))?,
2505            run_type: row
2506                .try_get("run_type")
2507                .map_err(|e| anyhow::anyhow!("run row decode error for run_type: {e}"))?,
2508            agent: row
2509                .try_get("agent")
2510                .map_err(|e| anyhow::anyhow!("run row decode error for agent: {e}"))?,
2511            model: row
2512                .try_get("model")
2513                .map_err(|e| anyhow::anyhow!("run row decode error for model: {e}"))?,
2514            command: row
2515                .try_get("command")
2516                .map_err(|e| anyhow::anyhow!("run row decode error for command: {e}"))?,
2517            prompt: row
2518                .try_get("prompt")
2519                .map_err(|e| anyhow::anyhow!("run row decode error for prompt: {e}"))?,
2520            env_vars: row
2521                .try_get("env_vars")
2522                .map_err(|e| anyhow::anyhow!("run row decode error for env_vars: {e}"))?,
2523            exit_code: row
2524                .try_get("exit_code")
2525                .map_err(|e| anyhow::anyhow!("run row decode error for exit_code: {e}"))?,
2526            stdout: row
2527                .try_get("stdout")
2528                .map_err(|e| anyhow::anyhow!("run row decode error for stdout: {e}"))?,
2529            stderr: row
2530                .try_get("stderr")
2531                .map_err(|e| anyhow::anyhow!("run row decode error for stderr: {e}"))?,
2532            parsed_response: row
2533                .try_get("parsed_response")
2534                .map_err(|e| anyhow::anyhow!("run row decode error for parsed_response: {e}"))?,
2535            outcome: row
2536                .try_get("outcome")
2537                .map_err(|e| anyhow::anyhow!("run row decode error for outcome: {e}"))?,
2538            error: row
2539                .try_get("error")
2540                .map_err(|e| anyhow::anyhow!("run row decode error for error: {e}"))?,
2541            input_tokens: row
2542                .try_get("input_tokens")
2543                .map_err(|e| anyhow::anyhow!("run row decode error for input_tokens: {e}"))?,
2544            output_tokens: row
2545                .try_get("output_tokens")
2546                .map_err(|e| anyhow::anyhow!("run row decode error for output_tokens: {e}"))?,
2547            total_cost_usd: row
2548                .try_get("total_cost_usd")
2549                .map_err(|e| anyhow::anyhow!("run row decode error for total_cost_usd: {e}"))?,
2550            duration_secs: row
2551                .try_get("duration_secs")
2552                .map_err(|e| anyhow::anyhow!("run row decode error for duration_secs: {e}"))?,
2553            started_at: row
2554                .try_get("started_at")
2555                .map_err(|e| anyhow::anyhow!("run row decode error for started_at: {e}"))?,
2556            completed_at: row.try_get("completed_at").unwrap_or(None),
2557        })
2558    }
2559
2560    fn row_to_activity(row: &SqliteRow) -> anyhow::Result<TaskActivity> {
2561        let details_str: String = row
2562            .try_get("details")
2563            .map_err(|e| anyhow::anyhow!("activity row decode error for details: {e}"))?;
2564        let details = serde_json::from_str::<serde_json::Value>(&details_str).inspect_err(
2565            |e| tracing::warn!(error = %e, "corrupt activity details JSON, skipping field"),
2566        )?;
2567        Ok(TaskActivity {
2568            id: row
2569                .try_get("id")
2570                .map_err(|e| anyhow::anyhow!("activity row decode error for id: {e}"))?,
2571            task_id: row
2572                .try_get("task_id")
2573                .map_err(|e| anyhow::anyhow!("activity row decode error for task_id: {e}"))?,
2574            timestamp: row
2575                .try_get("timestamp")
2576                .map_err(|e| anyhow::anyhow!("activity row decode error for timestamp: {e}"))?,
2577            event_type: row
2578                .try_get("event_type")
2579                .map_err(|e| anyhow::anyhow!("activity row decode error for event_type: {e}"))?,
2580            from_status: row
2581                .try_get("from_status")
2582                .map_err(|e| anyhow::anyhow!("activity row decode error for from_status: {e}"))?,
2583            to_status: row
2584                .try_get("to_status")
2585                .map_err(|e| anyhow::anyhow!("activity row decode error for to_status: {e}"))?,
2586            agent: row
2587                .try_get("agent")
2588                .map_err(|e| anyhow::anyhow!("activity row decode error for agent: {e}"))?,
2589            model: row
2590                .try_get("model")
2591                .map_err(|e| anyhow::anyhow!("activity row decode error for model: {e}"))?,
2592            details,
2593        })
2594    }
2595}
2596
2597/// Columns that describe this node's own copy of a task, never sent through the log
2598const LOCAL_TASK_COLUMNS: &[&str] = &[
2599    "worktree",
2600    "input_tokens",
2601    "output_tokens",
2602    "input_cost_usd",
2603    "output_cost_usd",
2604    "total_cost_usd",
2605];
2606
2607pub(crate) fn is_local_column(col: &str) -> bool {
2608    LOCAL_TASK_COLUMNS.contains(&col)
2609}
2610
2611/// Owned copy of [`UpsertExternal`], so it can travel in a Raft entry
2612#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
2613pub struct UpsertExternalCmd {
2614    pub repo: String,
2615    pub ext_id: String,
2616    pub title: String,
2617    pub body: String,
2618    pub author: String,
2619    pub url: String,
2620    pub labels: Vec<String>,
2621    pub origin: String,
2622    pub parent_id: Option<i64>,
2623}
2624
2625/// Owned copy of [`StoreRoute`], so it can travel in a Raft entry
2626#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
2627pub struct StoreRouteCmd {
2628    pub id: i64,
2629    pub agent: String,
2630    pub model: Option<String>,
2631    pub complexity: String,
2632    pub estimate: u8,
2633    pub reason: String,
2634    pub profile: String,
2635    pub skills: String,
2636}
2637
2638/// The columns a done task's tombstone keeps. Nodes that had the task keep the rest locally
2639const TOMBSTONE_COLUMNS: &[&str] = &[
2640    "id",
2641    "external_id",
2642    "repo",
2643    "origin",
2644    "title",
2645    "status",
2646    "pr_number",
2647    "parent_id",
2648    "created_at",
2649    "updated_at",
2650];
2651
2652async fn replicated_columns(conn: &mut sqlx::SqliteConnection) -> anyhow::Result<Vec<String>> {
2653    let cols: Vec<(String,)> = sqlx::query_as("SELECT name FROM pragma_table_info('tasks')")
2654        .fetch_all(conn)
2655        .await?;
2656    Ok(cols
2657        .into_iter()
2658        .map(|(c,)| c)
2659        .filter(|c| !is_local_column(c))
2660        .collect())
2661}
2662
2663/// Every task row as JSON with its replicated columns, done tasks as tombstones
2664pub(crate) async fn replicated_rows(pool: &SqlitePool) -> anyhow::Result<Vec<serde_json::Value>> {
2665    let mut conn = pool.acquire().await?;
2666    let cols = replicated_columns(&mut conn).await?;
2667    let full: Vec<&String> = cols.iter().collect();
2668    let tomb: Vec<&String> = cols
2669        .iter()
2670        .filter(|c| TOMBSTONE_COLUMNS.contains(&c.as_str()))
2671        .collect();
2672    let sql = format!(
2673        "SELECT CASE WHEN status = 'done' THEN {} ELSE {} END FROM tasks ORDER BY id",
2674        super::replica::json_row_sql(&tomb),
2675        super::replica::json_row_sql(&full)
2676    );
2677    let rows: Vec<(String,)> = sqlx::query_as(sqlx::AssertSqlSafe(sql.as_str()))
2678        .fetch_all(&mut *conn)
2679        .await?;
2680    rows.into_iter()
2681        .map(|(r,)| Ok(serde_json::from_str(&r)?))
2682        .collect()
2683}
2684
2685/// Make the task rows match `rows`. Local columns, and a tombstone's missing columns, keep the local
2686/// value. Tasks missing from `rows` go, with this node's runs and activity for them
2687pub(crate) async fn replace_replicated(
2688    conn: &mut sqlx::SqliteConnection,
2689    rows: &[serde_json::Value],
2690) -> anyhow::Result<()> {
2691    let allowed = replicated_columns(conn).await?;
2692    sqlx::query("PRAGMA defer_foreign_keys = ON")
2693        .execute(&mut *conn)
2694        .await?;
2695    sqlx::query("CREATE TEMP TABLE IF NOT EXISTS snapshot_ids (id INTEGER PRIMARY KEY)")
2696        .execute(&mut *conn)
2697        .await?;
2698    sqlx::query("DELETE FROM snapshot_ids")
2699        .execute(&mut *conn)
2700        .await?;
2701    for row in rows {
2702        let id = row["id"]
2703            .as_i64()
2704            .context("snapshot task row without an id")?;
2705        sqlx::query("INSERT INTO snapshot_ids (id) VALUES (?)")
2706            .bind(id)
2707            .execute(&mut *conn)
2708            .await?;
2709    }
2710
2711    for table in ["task_runs", "task_activity"] {
2712        sqlx::query(sqlx::AssertSqlSafe(format!(
2713            "DELETE FROM {table} WHERE task_id NOT IN (SELECT id FROM snapshot_ids)"
2714        )))
2715        .execute(&mut *conn)
2716        .await?;
2717    }
2718
2719    sqlx::query("DELETE FROM tasks WHERE id NOT IN (SELECT id FROM snapshot_ids)")
2720        .execute(&mut *conn)
2721        .await?;
2722
2723    for row in rows {
2724        let obj = row
2725            .as_object()
2726            .context("snapshot task row is not an object")?;
2727        let cols: Vec<&String> = obj.keys().collect();
2728        for c in &cols {
2729            anyhow::ensure!(
2730                allowed.contains(c),
2731                "snapshot names unknown task column {c}"
2732            );
2733        }
2734
2735        let values: Vec<String> = cols
2736            .iter()
2737            .map(|c| format!("json_extract(?1, '$.\"{c}\"')"))
2738            .collect();
2739        let updates: Vec<String> = cols.iter().map(|c| format!("{c} = excluded.{c}")).collect();
2740        let names: Vec<&str> = cols.iter().map(|c| c.as_str()).collect();
2741        let sql = format!(
2742            "INSERT INTO tasks ({}) VALUES ({}) ON CONFLICT(id) DO UPDATE SET {}",
2743            names.join(", "),
2744            values.join(", "),
2745            updates.join(", ")
2746        );
2747        sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2748            .bind(serde_json::to_string(row)?)
2749            .execute(&mut *conn)
2750            .await?;
2751    }
2752
2753    sqlx::query("DROP TABLE snapshot_ids")
2754        .execute(&mut *conn)
2755        .await?;
2756    Ok(())
2757}
2758
2759#[cfg(test)]
2760mod row_to_task_tests {
2761    use super::*;
2762    use crate::store;
2763    use crate::store::TaskStatus;
2764
2765    async fn store_with_task() -> (store::TaskStore, i64) {
2766        let store = store::TaskStore::open_memory().await.unwrap();
2767        let id = store
2768            .create(&NewTask {
2769                external_id: None,
2770                repo: "owner/repo".to_string(),
2771                origin: "internal".to_string(),
2772                title: "Test task".to_string(),
2773                body: "Test body".to_string(),
2774                source: "cron".to_string(),
2775                source_id: "test-1".to_string(),
2776                author: "tester".to_string(),
2777                url: "https://example.com/1".to_string(),
2778                labels: vec![],
2779                parent_id: None,
2780            })
2781            .await
2782            .unwrap();
2783        (store, id)
2784    }
2785
2786    // Each test selects all previously-required columns so that exactly the
2787    // column under test is absent and triggers the Err path.
2788    macro_rules! missing_column_test {
2789        ($name:ident, $col:literal, $select:literal) => {
2790            #[tokio::test]
2791            async fn $name() {
2792                let (store, id) = store_with_task().await;
2793                let sql = format!("{} WHERE id = {id}", $select);
2794                let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2795                    .fetch_one(store.pool())
2796                    .await
2797                    .unwrap();
2798                let err = TaskStore::row_to_task(&row).unwrap_err();
2799                assert!(
2800                    err.to_string().contains($col),
2801                    "expected '{}' in error message, got: {err}",
2802                    $col
2803                );
2804            }
2805        };
2806    }
2807
2808    missing_column_test!(
2809        row_to_task_fails_on_missing_repo,
2810        "repo",
2811        "SELECT id, status FROM tasks"
2812    );
2813    missing_column_test!(
2814        row_to_task_fails_on_missing_origin,
2815        "origin",
2816        "SELECT id, repo, status FROM tasks"
2817    );
2818    missing_column_test!(
2819        row_to_task_fails_on_missing_title,
2820        "title",
2821        "SELECT id, repo, origin, status FROM tasks"
2822    );
2823    missing_column_test!(
2824        row_to_task_fails_on_missing_body,
2825        "body",
2826        "SELECT id, repo, origin, title, status FROM tasks"
2827    );
2828    missing_column_test!(
2829        row_to_task_fails_on_missing_source,
2830        "source",
2831        "SELECT id, repo, origin, title, body, status FROM tasks"
2832    );
2833    missing_column_test!(
2834        row_to_task_fails_on_missing_source_id,
2835        "source_id",
2836        "SELECT id, repo, origin, title, body, source, status FROM tasks"
2837    );
2838    missing_column_test!(
2839        row_to_task_fails_on_missing_author,
2840        "author",
2841        "SELECT id, repo, origin, title, body, source, source_id, status FROM tasks"
2842    );
2843    missing_column_test!(
2844        row_to_task_fails_on_missing_url,
2845        "url",
2846        "SELECT id, repo, origin, title, body, source, source_id, author, status FROM tasks"
2847    );
2848
2849    // Decode-error test: inject a BLOB with invalid UTF-8 bytes for the `repo`
2850    // column. SQLite's type coercion converts NULL and integers to strings, so
2851    // they don't produce a decode error. A raw BLOB that is not valid UTF-8
2852    // (X'DEADBEEF') does cause `try_get::<String>` to fail with a ColumnDecode
2853    // error, exercising the decode-error-propagation path that is distinct from
2854    // the missing-column (ColumnNotFound) path tested above.
2855    #[tokio::test]
2856    async fn row_to_task_fails_on_decode_error_invalid_utf8_repo() {
2857        let (store, id) = store_with_task().await;
2858        // X'DEADBEEF' is a 4-byte BLOB; bytes 0xDE 0xAD 0xBE 0xEF are not
2859        // valid UTF-8, so try_get::<String>("repo") will fail with a decode error.
2860        let sql = format!(
2861            "SELECT id, X'DEADBEEF' as repo, origin, title, body, status, \
2862             source, source_id, author, url FROM tasks WHERE id = {id}"
2863        );
2864        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2865            .fetch_one(store.pool())
2866            .await
2867            .unwrap();
2868        let err = TaskStore::row_to_task(&row).unwrap_err();
2869        assert!(
2870            err.to_string().contains("repo"),
2871            "expected 'repo' in error message, got: {err}"
2872        );
2873    }
2874
2875    // ── distinct_repos decode-error tests ─────────────────────────────────
2876
2877    #[tokio::test]
2878    async fn distinct_repos_fails_on_decode_error() {
2879        let store = store::TaskStore::open_memory().await.unwrap();
2880        let id = store
2881            .create(&NewTask {
2882                external_id: None,
2883                repo: "owner/repo".to_string(),
2884                origin: "internal".to_string(),
2885                title: "Test task".to_string(),
2886                body: "Test body".to_string(),
2887                source: "cron".to_string(),
2888                source_id: "test-1".to_string(),
2889                author: "tester".to_string(),
2890                url: "https://example.com/1".to_string(),
2891                labels: vec![],
2892                parent_id: None,
2893            })
2894            .await
2895            .unwrap();
2896
2897        // Inject an invalid UTF-8 BLOB as repo — try_get::<String> will fail.
2898        let sql = format!("UPDATE tasks SET repo = X'DEADBEEF' WHERE id = {id}");
2899        sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2900            .execute(store.pool())
2901            .await
2902            .unwrap();
2903
2904        let err = store.distinct_repos().await.unwrap_err();
2905        assert!(
2906            err.to_string().contains("distinct_repos") && err.to_string().contains("repo"),
2907            "expected 'distinct_repos' and 'repo' in error message, got: {err}"
2908        );
2909    }
2910
2911    // ── row_to_run decode-error tests ────────────────────────────────────────
2912
2913    async fn store_with_run() -> (store::TaskStore, i64, i64) {
2914        let store = store::TaskStore::open_memory().await.unwrap();
2915        let task_id = store
2916            .create(&NewTask {
2917                external_id: None,
2918                repo: "owner/repo".to_string(),
2919                origin: "internal".to_string(),
2920                title: "Test task".to_string(),
2921                body: "Test body".to_string(),
2922                source: "cron".to_string(),
2923                source_id: "test-1".to_string(),
2924                author: "tester".to_string(),
2925                url: "https://example.com/1".to_string(),
2926                labels: vec![],
2927                parent_id: None,
2928            })
2929            .await
2930            .unwrap();
2931        let run_id = store
2932            .start_run(&crate::store::StartRun {
2933                task_id,
2934                attempt: 1,
2935                run_type: "agent",
2936                agent: "claude",
2937                model: "sonnet",
2938                command: "claude -p ...",
2939                prompt: "system prompt",
2940            })
2941            .await
2942            .unwrap();
2943        (store, task_id, run_id)
2944    }
2945
2946    #[tokio::test]
2947    async fn start_run_upsert_clears_stale_completion_fields() {
2948        let (store, task_id, run_id) = store_with_run().await;
2949        store
2950            .complete_run(&CompleteRun {
2951                run_id,
2952                exit_code: Some(0),
2953                stdout: "",
2954                stderr: "",
2955                parsed: "",
2956                outcome: "success",
2957                error: "boom",
2958                tokens: RunTokenUsage::default(),
2959            })
2960            .await
2961            .unwrap();
2962
2963        // Re-dispatch the same (task_id, attempt, run_type) key.
2964        let same_run_id = store
2965            .start_run(&crate::store::StartRun {
2966                task_id,
2967                attempt: 1,
2968                run_type: "agent",
2969                agent: "claude",
2970                model: "sonnet",
2971                command: "claude -p ...",
2972                prompt: "system prompt",
2973            })
2974            .await
2975            .unwrap();
2976        assert_eq!(run_id, same_run_id, "upsert must reuse the same row");
2977
2978        let row = sqlx::query(
2979            "SELECT outcome, completed_at, exit_code, error FROM task_runs WHERE id = ?",
2980        )
2981        .bind(run_id)
2982        .fetch_one(store.pool())
2983        .await
2984        .unwrap();
2985        let outcome: Option<String> = row.try_get("outcome").unwrap();
2986        let completed_at: Option<String> = row.try_get("completed_at").unwrap();
2987        let exit_code: Option<i64> = row.try_get("exit_code").unwrap();
2988        let error: Option<String> = row.try_get("error").unwrap();
2989        assert_eq!(outcome, None);
2990        assert_eq!(
2991            completed_at, None,
2992            "stale completion timestamp must be cleared"
2993        );
2994        assert_eq!(exit_code, None);
2995        assert_eq!(error, None);
2996    }
2997
2998    // NOTE: Unlike the task table (where all required columns are TEXT and NULL
2999    // propagates a ColumnNotFound error), the task_runs table has INTEGER and REAL
3000    // columns (id, task_id, attempt, started_at). SQLite coerces NULL/INTEGER to
3001    // String when try_get::<String> is called, so we cannot use the missing-column
3002    // pattern here. Instead, we use BLOB injection to trigger actual decode errors
3003    // that propagate through map_err.
3004    macro_rules! run_blob_decode_test {
3005        ($name:ident, $col:literal, $select:literal) => {
3006            #[tokio::test]
3007            async fn $name() {
3008                let (store, _task_id, run_id) = store_with_run().await;
3009                let sql = format!($select, run_id = run_id);
3010                let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3011                    .fetch_one(store.pool())
3012                    .await
3013                    .unwrap();
3014                let err = TaskStore::row_to_run(&row).unwrap_err();
3015                assert!(
3016                    err.to_string().contains($col),
3017                    "expected '{0}' in error message, got: {err}",
3018                    $col
3019                );
3020            }
3021        };
3022    }
3023
3024    run_blob_decode_test!(
3025        row_to_run_fails_on_decode_error_invalid_utf8_id,
3026        "id",
3027        "SELECT X'DEADBEEF' as id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3028    );
3029    run_blob_decode_test!(
3030        row_to_run_fails_on_decode_error_invalid_utf8_task_id,
3031        "task_id",
3032        "SELECT id, X'DEADBEEF' as task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3033    );
3034    run_blob_decode_test!(
3035        row_to_run_fails_on_decode_error_invalid_utf8_started_at,
3036        "started_at",
3037        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, X'DEADBEEF' as started_at, completed_at FROM task_runs WHERE id = {run_id}"
3038    );
3039
3040    // ── row_to_activity decode-error tests ───────────────────────────────────
3041
3042    async fn store_with_activity() -> (store::TaskStore, i64, i64) {
3043        let store = store::TaskStore::open_memory().await.unwrap();
3044        let task_id = store
3045            .create(&NewTask {
3046                external_id: None,
3047                repo: "owner/repo".to_string(),
3048                origin: "internal".to_string(),
3049                title: "Test task".to_string(),
3050                body: "Test body".to_string(),
3051                source: "cron".to_string(),
3052                source_id: "test-1".to_string(),
3053                author: "tester".to_string(),
3054                url: "https://example.com/1".to_string(),
3055                labels: vec![],
3056                parent_id: None,
3057            })
3058            .await
3059            .unwrap();
3060        store
3061            .append_activity(task_id, "created", None, Some("new"), None, None, None)
3062            .await
3063            .unwrap();
3064        (store, task_id, 1)
3065    }
3066
3067    // Each test drops exactly one column from the full TASK_ACTIVITY_COLS so that
3068    // the first missing-column try_get (id) propagates the error.
3069    #[tokio::test]
3070    async fn row_to_activity_fails_on_decode_error_invalid_utf8_id() {
3071        let (store, _task_id, activity_id) = store_with_activity().await;
3072        let sql = format!(
3073            "SELECT X'DEADBEEF' as id, task_id, timestamp, event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3074        );
3075        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3076            .fetch_one(store.pool())
3077            .await
3078            .unwrap();
3079        let err = TaskStore::row_to_activity(&row).unwrap_err();
3080        assert!(
3081            err.to_string().contains("id"),
3082            "expected 'id' in error message, got: {err}"
3083        );
3084    }
3085
3086    #[tokio::test]
3087    async fn row_to_activity_fails_on_decode_error_invalid_utf8_task_id() {
3088        let (store, _task_id, activity_id) = store_with_activity().await;
3089        let sql = format!(
3090            "SELECT id, X'DEADBEEF' as task_id, timestamp, event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3091        );
3092        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3093            .fetch_one(store.pool())
3094            .await
3095            .unwrap();
3096        let err = TaskStore::row_to_activity(&row).unwrap_err();
3097        assert!(
3098            err.to_string().contains("task_id"),
3099            "expected 'task_id' in error message, got: {err}"
3100        );
3101    }
3102
3103    #[tokio::test]
3104    async fn row_to_activity_fails_on_decode_error_invalid_utf8_timestamp() {
3105        let (store, _task_id, activity_id) = store_with_activity().await;
3106        let sql = format!(
3107            "SELECT id, task_id, X'DEADBEEF' as timestamp, event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3108        );
3109        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3110            .fetch_one(store.pool())
3111            .await
3112            .unwrap();
3113        let err = TaskStore::row_to_activity(&row).unwrap_err();
3114        assert!(
3115            err.to_string().contains("timestamp"),
3116            "expected 'timestamp' in error message, got: {err}"
3117        );
3118    }
3119
3120    #[tokio::test]
3121    async fn row_to_activity_fails_on_decode_error_invalid_utf8_event_type() {
3122        let (store, _task_id, activity_id) = store_with_activity().await;
3123        let sql = format!(
3124            "SELECT id, task_id, timestamp, X'DEADBEEF' as event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3125        );
3126        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3127            .fetch_one(store.pool())
3128            .await
3129            .unwrap();
3130        let err = TaskStore::row_to_activity(&row).unwrap_err();
3131        assert!(
3132            err.to_string().contains("event_type"),
3133            "expected 'event_type' in error message, got: {err}"
3134        );
3135    }
3136
3137    #[tokio::test]
3138    async fn row_to_activity_fails_on_corrupt_details_json() {
3139        let (store, task_id, _activity_id) = store_with_activity().await;
3140        // Insert a row with invalid JSON in details
3141        sqlx::query("INSERT INTO task_activity (task_id, event_type, details) VALUES (?, ?, ?)")
3142            .bind(task_id)
3143            .bind("test")
3144            .bind("not valid json {{{")
3145            .execute(store.pool())
3146            .await
3147            .unwrap();
3148        let row = sqlx::query(sqlx::AssertSqlSafe(format!(
3149            "SELECT {TASK_ACTIVITY_COLS} FROM task_activity WHERE task_id = {task_id} ORDER BY id DESC LIMIT 1"
3150        )))
3151        .fetch_one(store.pool())
3152        .await
3153        .unwrap();
3154        let err = TaskStore::row_to_activity(&row).unwrap_err();
3155        assert!(
3156            err.to_string().contains("details")
3157                || err.to_string().contains("json")
3158                || err.to_string().contains("expected"),
3159            "expected 'details', 'json', or 'expected' in error message, got: {err}"
3160        );
3161    }
3162
3163    // ── row_to_run: remaining fields with map_err ──────────────────────────────
3164
3165    run_blob_decode_test!(
3166        row_to_run_fails_on_decode_error_invalid_utf8_model,
3167        "model",
3168        "SELECT id, task_id, attempt, run_type, agent, X'DeadbEEF' as model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3169    );
3170    run_blob_decode_test!(
3171        row_to_run_fails_on_decode_error_invalid_utf8_command,
3172        "command",
3173        "SELECT id, task_id, attempt, run_type, agent, model, X'DeadbEEF' as command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3174    );
3175    run_blob_decode_test!(
3176        row_to_run_fails_on_decode_error_invalid_utf8_prompt,
3177        "prompt",
3178        "SELECT id, task_id, attempt, run_type, agent, model, command, X'DeadbEEF' as prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3179    );
3180    run_blob_decode_test!(
3181        row_to_run_fails_on_decode_error_invalid_utf8_env_vars,
3182        "env_vars",
3183        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, X'DeadbEEF' as env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3184    );
3185    run_blob_decode_test!(
3186        row_to_run_fails_on_decode_error_invalid_utf8_stdout,
3187        "stdout",
3188        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, X'DeadbEEF' as stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3189    );
3190    run_blob_decode_test!(
3191        row_to_run_fails_on_decode_error_invalid_utf8_stderr,
3192        "stderr",
3193        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, X'DeadbEEF' as stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3194    );
3195    run_blob_decode_test!(
3196        row_to_run_fails_on_decode_error_invalid_utf8_parsed_response,
3197        "parsed_response",
3198        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, X'DeadbEEF' as parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3199    );
3200    run_blob_decode_test!(
3201        row_to_run_fails_on_decode_error_invalid_utf8_outcome,
3202        "outcome",
3203        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, X'DeadbEEF' as outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3204    );
3205    run_blob_decode_test!(
3206        row_to_run_fails_on_decode_error_invalid_utf8_error,
3207        "error",
3208        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, X'DeadbEEF' as error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3209    );
3210    run_blob_decode_test!(
3211        row_to_run_fails_on_decode_error_invalid_utf8_input_tokens,
3212        "input_tokens",
3213        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, X'DeadbEEF' as input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3214    );
3215    run_blob_decode_test!(
3216        row_to_run_fails_on_decode_error_invalid_utf8_output_tokens,
3217        "output_tokens",
3218        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, X'DeadbEEF' as output_tokens, total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3219    );
3220    run_blob_decode_test!(
3221        row_to_run_fails_on_decode_error_invalid_utf8_total_cost_usd,
3222        "total_cost_usd",
3223        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, X'DeadbEEF' as total_cost_usd, duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3224    );
3225    run_blob_decode_test!(
3226        row_to_run_fails_on_decode_error_invalid_utf8_duration_secs,
3227        "duration_secs",
3228        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, X'DeadbEEF' as duration_secs, started_at, completed_at FROM task_runs WHERE id = {run_id}"
3229    );
3230    run_blob_decode_test!(
3231        row_to_run_fails_on_decode_error_invalid_utf8_exit_code,
3232        "exit_code",
3233        "SELECT id, task_id, attempt, run_type, agent, model, command, prompt, env_vars, exit_code, stdout, stderr, parsed_response, outcome, error, input_tokens, output_tokens, total_cost_usd, duration_secs, started_at, X'DeadbEEF' as exit_code, completed_at FROM task_runs WHERE id = {run_id}"
3234    );
3235
3236    // ── row_to_activity: nullable columns with map_err ────────────────────────
3237
3238    // from_status, to_status, agent, model are nullable (Option<String>) but their
3239    // decode errors should still propagate via map_err, not be masked as None.
3240    // Each test injects BLOB into exactly one column and NULL into the rest so
3241    // the error always references the intended column (the first failing map_err).
3242    #[tokio::test]
3243    async fn row_to_activity_fails_on_decode_error_invalid_utf8_from_status() {
3244        let (store, _task_id, activity_id) = store_with_activity().await;
3245        let sql = format!(
3246            "SELECT id, task_id, timestamp, event_type, \
3247             X'DeadbEEF' as from_status, NULL as to_status, \
3248             NULL as agent, NULL as model, details \
3249             FROM task_activity WHERE id = {activity_id}"
3250        );
3251        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3252            .fetch_one(store.pool())
3253            .await
3254            .unwrap();
3255        let err = TaskStore::row_to_activity(&row).unwrap_err();
3256        assert!(
3257            err.to_string().contains("from_status"),
3258            "expected 'from_status' in error message, got: {err}"
3259        );
3260    }
3261
3262    #[tokio::test]
3263    async fn row_to_activity_fails_on_decode_error_invalid_utf8_to_status() {
3264        let (store, _task_id, activity_id) = store_with_activity().await;
3265        let sql = format!(
3266            "SELECT id, task_id, timestamp, event_type, \
3267             NULL as from_status, X'DeadbEEF' as to_status, \
3268             NULL as agent, NULL as model, details \
3269             FROM task_activity WHERE id = {activity_id}"
3270        );
3271        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3272            .fetch_one(store.pool())
3273            .await
3274            .unwrap();
3275        let err = TaskStore::row_to_activity(&row).unwrap_err();
3276        assert!(
3277            err.to_string().contains("to_status"),
3278            "expected 'to_status' in error message, got: {err}"
3279        );
3280    }
3281
3282    #[tokio::test]
3283    async fn row_to_activity_fails_on_decode_error_invalid_utf8_agent() {
3284        let (store, _task_id, activity_id) = store_with_activity().await;
3285        let sql = format!(
3286            "SELECT id, task_id, timestamp, event_type, \
3287             NULL as from_status, NULL as to_status, \
3288             X'DeadbEEF' as agent, NULL as model, details \
3289             FROM task_activity WHERE id = {activity_id}"
3290        );
3291        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3292            .fetch_one(store.pool())
3293            .await
3294            .unwrap();
3295        let err = TaskStore::row_to_activity(&row).unwrap_err();
3296        assert!(
3297            err.to_string().contains("agent"),
3298            "expected 'agent' in error message, got: {err}"
3299        );
3300    }
3301
3302    #[tokio::test]
3303    async fn row_to_activity_fails_on_decode_error_invalid_utf8_model() {
3304        let (store, _task_id, activity_id) = store_with_activity().await;
3305        let sql = format!(
3306            "SELECT id, task_id, timestamp, event_type, \
3307             NULL as from_status, NULL as to_status, \
3308             NULL as agent, X'DeadbEEF' as model, details \
3309             FROM task_activity WHERE id = {activity_id}"
3310        );
3311        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3312            .fetch_one(store.pool())
3313            .await
3314            .unwrap();
3315        let err = TaskStore::row_to_activity(&row).unwrap_err();
3316        assert!(
3317            err.to_string().contains("model"),
3318            "expected 'model' in error message, got: {err}"
3319        );
3320    }
3321
3322    // ── get_statuses_by_external_ids: decode-failure rows are skipped ───────────
3323
3324    #[tokio::test]
3325    async fn get_statuses_by_external_ids_skips_rows_with_decode_error() {
3326        // Insert a task row with a BLOB value in the external_id column (valid
3327        // SQLite but not valid UTF-8 TEXT), so row.try_get("external_id") fails
3328        // with a decode error. The method should skip this row (not panic) and
3329        // continue processing the remaining rows.
3330        let store = store::TaskStore::open_memory().await.unwrap();
3331        sqlx::query("INSERT INTO tasks (repo, external_id, status, title, origin) VALUES ('owner/repo', X'DeadbEEF', 'new', '', 'internal')")
3332            .execute(store.pool())
3333            .await
3334            .unwrap();
3335        // A second row with a valid external_id should be returned.
3336        sqlx::query("INSERT INTO tasks (repo, external_id, status, title, origin) VALUES ('owner/repo', 'valid-id', 'done', '', 'internal')")
3337            .execute(store.pool())
3338            .await
3339            .unwrap();
3340
3341        let result = store
3342            .get_statuses_by_external_ids("owner/repo", &["valid-id"])
3343            .await
3344            .unwrap();
3345        assert_eq!(result.len(), 1, "expected exactly one result for valid-id");
3346        assert_eq!(result.get("valid-id"), Some(&TaskStatus::Done));
3347    }
3348}