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