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`, clearing `branch` and `worktree`
979    pub async fn reset_to_new(&self, id: i64) -> anyhow::Result<()> {
980        self.write(Command::TaskResetToNew {
981            id,
982            keep_branch: false,
983        })
984        .await
985        .map(|_| ())
986    }
987
988    /// Reset a task back to `new` and keep `branch` when cleanup left the local ref
989    pub async fn reset_to_new_keep_branch(&self, id: i64) -> anyhow::Result<()> {
990        self.write(Command::TaskResetToNew {
991            id,
992            keep_branch: true,
993        })
994        .await
995        .map(|_| ())
996    }
997
998    /// Update the block reason for a task.
999    pub async fn set_block_reason(&self, id: i64, reason: Option<&str>) -> anyhow::Result<()> {
1000        let value = reason.map_or(serde_json::Value::Null, |r| {
1001            serde_json::Value::String(r.to_string())
1002        });
1003        self.set_fields(id, &[("block_reason", value)]).await
1004    }
1005
1006    /// List tasks by status within a repo.
1007    pub async fn list_by_status(
1008        &self,
1009        repo: &str,
1010        status: TaskStatus,
1011    ) -> anyhow::Result<Vec<Task>> {
1012        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1013            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND status = ? ORDER BY created_at DESC"
1014        )))
1015        .bind(repo)
1016        .bind(status.as_str())
1017        .fetch_all(&self.pool)
1018        .await?;
1019
1020        rows.iter().map(Self::row_to_task).collect()
1021    }
1022
1023    /// List tasks that are linked to the given parent task.
1024    pub async fn list_children(&self, repo: &str, parent_id: i64) -> anyhow::Result<Vec<Task>> {
1025        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1026            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND parent_id = ? ORDER BY created_at ASC"
1027        )))
1028        .bind(repo)
1029        .bind(parent_id)
1030        .fetch_all(&self.pool)
1031        .await?;
1032
1033        rows.iter().map(Self::row_to_task).collect()
1034    }
1035
1036    /// List external tasks by status within a repo (origin != 'internal').
1037    pub async fn list_external_by_status(
1038        &self,
1039        repo: &str,
1040        status: TaskStatus,
1041    ) -> anyhow::Result<Vec<Task>> {
1042        let rows = sqlx::query(
1043            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin != 'internal' AND status = ? ORDER BY created_at DESC")),
1044        )
1045        .bind(repo)
1046        .bind(status.as_str())
1047        .fetch_all(&self.pool)
1048        .await?;
1049
1050        rows.iter().map(Self::row_to_task).collect()
1051    }
1052
1053    /// List routable tasks (status = 'new') within a repo.
1054    pub async fn list_routable(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1055        self.list_by_status(repo, TaskStatus::New).await
1056    }
1057
1058    /// List internal tasks by status within a repo (origin = 'internal').
1059    pub async fn list_internal_by_status(
1060        &self,
1061        repo: &str,
1062        status: TaskStatus,
1063    ) -> anyhow::Result<Vec<Task>> {
1064        let rows = sqlx::query(
1065        sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin = 'internal' AND status = ? ORDER BY created_at DESC")),
1066    )
1067    .bind(repo)
1068    .bind(status.as_str())
1069    .fetch_all(&self.pool)
1070    .await?;
1071
1072        rows.iter().map(Self::row_to_task).collect()
1073    }
1074
1075    /// List all internal tasks for a repo (origin = 'internal').
1076    pub async fn list_all_internal(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1077        let rows = sqlx::query(
1078            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin = 'internal' ORDER BY created_at DESC")),
1079        )
1080        .bind(repo)
1081        .fetch_all(&self.pool)
1082        .await?;
1083
1084        rows.iter().map(Self::row_to_task).collect()
1085    }
1086
1087    /// List internal tasks with a specific source value (origin = 'internal' AND source = ?).
1088    /// More efficient than `list_all_internal` + in-memory filter when only a subset is needed.
1089    pub async fn list_internal_by_source(
1090        &self,
1091        repo: &str,
1092        source: &str,
1093    ) -> anyhow::Result<Vec<Task>> {
1094        let rows = sqlx::query(
1095            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin = 'internal' AND source = ? ORDER BY created_at DESC")),
1096        )
1097        .bind(repo)
1098        .bind(source)
1099        .fetch_all(&self.pool)
1100        .await?;
1101
1102        rows.iter().map(Self::row_to_task).collect()
1103    }
1104
1105    /// Return only the `source_id` values for internal tasks with a specific source,
1106    /// created within the last `since_days` days.
1107    /// Use this instead of `list_internal_by_source` when only deduplication is needed —
1108    /// avoids fetching all 57 task columns and bounds memory usage to a rolling window.
1109    pub async fn list_source_ids_by_source(
1110        &self,
1111        repo: &str,
1112        source: &str,
1113        since_days: i64,
1114    ) -> anyhow::Result<Vec<String>> {
1115        let cutoff = format!("-{since_days} days");
1116        let rows = sqlx::query(
1117            "SELECT source_id FROM tasks WHERE repo = ? AND origin = 'internal' AND source = ? AND created_at >= datetime('now', ?)",
1118        )
1119        .bind(repo)
1120        .bind(source)
1121        .bind(&cutoff)
1122        .fetch_all(&self.pool)
1123        .await?;
1124
1125        let mut ids = Vec::with_capacity(rows.len());
1126        for row in &rows {
1127            ids.push(row.try_get::<String, _>("source_id").map_err(|e| {
1128                anyhow::anyhow!(
1129                    "list_source_ids_by_source: failed to decode source_id for repo={repo}, source={source}: {e}"
1130                )
1131            })?);
1132        }
1133        Ok(ids)
1134    }
1135
1136    /// List all active (non-done) tasks for a repo.
1137    pub async fn list_active(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1138        let rows = sqlx::query(
1139            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND status != 'done' ORDER BY created_at DESC")),
1140        )
1141        .bind(repo)
1142        .fetch_all(&self.pool)
1143        .await?;
1144
1145        rows.iter().map(Self::row_to_task).collect()
1146    }
1147
1148    /// List all external tasks for a repo (origin != 'internal').
1149    pub async fn list_all_external(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1150        let rows = sqlx::query(
1151            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND origin != 'internal' ORDER BY created_at DESC")),
1152        )
1153        .bind(repo)
1154        .fetch_all(&self.pool)
1155        .await?;
1156
1157        rows.iter().map(Self::row_to_task).collect()
1158    }
1159
1160    /// Check whether the store has any external tasks for a repo.
1161    pub async fn has_external_tasks(&self, repo: &str) -> anyhow::Result<bool> {
1162        let has_external = sqlx::query_scalar::<_, i32>(
1163            "SELECT 1 FROM tasks WHERE repo = ? AND origin != 'internal' LIMIT 1",
1164        )
1165        .bind(repo)
1166        .fetch_optional(&self.pool)
1167        .await?
1168        .is_some();
1169        Ok(has_external)
1170    }
1171
1172    /// Return a set of external IDs for all external tasks in a repo.
1173    ///
1174    /// Used to eliminate N+1 queries when ingesting lists of external tasks.
1175    pub async fn existing_external_ids(&self, repo: &str) -> anyhow::Result<HashSet<String>> {
1176        let rows: Vec<(String,)> = sqlx::query_as(
1177            "SELECT external_id FROM tasks WHERE repo = ? AND origin != 'internal' AND external_id IS NOT NULL",
1178        )
1179        .bind(repo)
1180        .fetch_all(&self.pool)
1181        .await?;
1182
1183        Ok(rows
1184            .into_iter()
1185            .filter_map(|(id,)| if id.is_empty() { None } else { Some(id) })
1186            .collect())
1187    }
1188
1189    /// List tasks for `orch service doctor`: active tasks + done tasks updated since `cutoff_str`.
1190    /// Much cheaper than `list_all` on repos with many historical done tasks.
1191    pub async fn list_for_doctor(&self, repo: &str, cutoff_str: &str) -> anyhow::Result<Vec<Task>> {
1192        let rows = sqlx::query(
1193            sqlx::AssertSqlSafe(format!("SELECT {TASK_COLS} FROM tasks WHERE repo = ? AND (status != 'done' OR updated_at >= ?) ORDER BY created_at DESC")),
1194        )
1195        .bind(repo)
1196        .bind(cutoff_str)
1197        .fetch_all(&self.pool)
1198        .await?;
1199        rows.iter().map(Self::row_to_task).collect()
1200    }
1201
1202    /// List all tasks for a repo, ordered by creation time descending.
1203    pub async fn list_all(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1204        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1205            "SELECT {TASK_COLS} FROM tasks WHERE repo = ? ORDER BY created_at DESC"
1206        )))
1207        .bind(repo)
1208        .fetch_all(&self.pool)
1209        .await?;
1210        rows.iter().map(Self::row_to_task).collect()
1211    }
1212
1213    /// Return the distinct repo slugs present in the tasks table.
1214    pub async fn distinct_repos(&self) -> anyhow::Result<Vec<String>> {
1215        let rows =
1216            sqlx::query("SELECT DISTINCT repo FROM tasks WHERE repo IS NOT NULL AND repo != ''")
1217                .fetch_all(&self.pool)
1218                .await?;
1219        let mut repos = Vec::new();
1220        for row in &rows {
1221            repos.push(row.try_get::<String, _>("repo").map_err(|e| {
1222                anyhow::anyhow!("distinct_repos: failed to decode 'repo' column: {e}")
1223            })?);
1224        }
1225        Ok(repos)
1226    }
1227
1228    /// List all active (non-done) tasks across all repos.
1229    ///
1230    /// Used by the CLI when no project context is available (e.g. running from
1231    /// a worktree without a `.orch.yml`). Does not require a repo argument.
1232    pub async fn list_all_active_global(&self) -> anyhow::Result<Vec<Task>> {
1233        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1234            "SELECT {TASK_COLS} FROM tasks WHERE status != 'done' ORDER BY created_at DESC"
1235        )))
1236        .fetch_all(&self.pool)
1237        .await?;
1238        rows.iter().map(Self::row_to_task).collect()
1239    }
1240
1241    /// List all tasks across all repos regardless of status.
1242    ///
1243    /// Used by the CLI when `--status all` is passed.
1244    pub async fn list_all_global(&self) -> anyhow::Result<Vec<Task>> {
1245        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1246            "SELECT {TASK_COLS} FROM tasks ORDER BY created_at DESC"
1247        )))
1248        .fetch_all(&self.pool)
1249        .await?;
1250        rows.iter().map(Self::row_to_task).collect()
1251    }
1252
1253    /// List all active tasks matching an optional status filter, across all repos.
1254    ///
1255    /// Used by the CLI fallback path when no project context is available.
1256    pub async fn list_all_by_status_global(&self, status: TaskStatus) -> anyhow::Result<Vec<Task>> {
1257        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1258            "SELECT {TASK_COLS} FROM tasks WHERE status = ? ORDER BY created_at DESC"
1259        )))
1260        .bind(status.as_str())
1261        .fetch_all(&self.pool)
1262        .await?;
1263        rows.iter().map(Self::row_to_task).collect()
1264    }
1265
1266    /// Aggregate cost and token data for a repo.
1267    /// Returns (total_input_tokens, total_output_tokens, total_cost_usd).
1268    pub async fn set_fields(
1269        &self,
1270        id: i64,
1271        updates: &[(&str, serde_json::Value)],
1272    ) -> anyhow::Result<()> {
1273        self.set_fields_split(id, updates, false).await
1274    }
1275
1276    /// Like `set_fields` but does NOT touch `updated_at`.
1277    ///
1278    /// Use this for internal bookkeeping writes (probe timestamps, counters) that
1279    /// must not make a stale task appear recently-modified to triage queries.
1280    pub async fn set_fields_silent(
1281        &self,
1282        id: i64,
1283        updates: &[(&str, serde_json::Value)],
1284    ) -> anyhow::Result<()> {
1285        self.set_fields_split(id, updates, true).await
1286    }
1287
1288    /// Local columns are written on this node only, the rest go through the log
1289    async fn set_fields_split(
1290        &self,
1291        id: i64,
1292        updates: &[(&str, serde_json::Value)],
1293        silent: bool,
1294    ) -> anyhow::Result<()> {
1295        let all = owned_updates(updates)?;
1296        // On one machine nothing is replicated, so the whole update stays one statement
1297        if !self.replicates() {
1298            let mut tx = self.begin_write().await?;
1299            apply::set_fields(&mut tx, &cluster::now_stamp(), id, &all, silent).await?;
1300            tx.commit().await?;
1301            return Ok(());
1302        }
1303
1304        let (local, replicated): (Vec<_>, Vec<_>) =
1305            all.into_iter().partition(|(col, _)| is_local_column(col));
1306        if !local.is_empty() {
1307            let mut tx = self.begin_write().await?;
1308            apply::set_fields(&mut tx, &cluster::now_stamp(), id, &local, silent).await?;
1309            tx.commit().await?;
1310        }
1311
1312        if replicated.is_empty() {
1313            return Ok(());
1314        }
1315
1316        self.write(Command::TaskSetFields {
1317            id,
1318            updates: replicated,
1319            silent,
1320        })
1321        .await
1322        .map(|_| ())
1323    }
1324
1325    /// Increment an integer field by 1, returning the new value.
1326    pub async fn increment(&self, id: i64, field: &str) -> anyhow::Result<i32> {
1327        anyhow::ensure!(
1328            INCREMENTABLE_FIELDS.contains(&field),
1329            "field {field} is not incrementable"
1330        );
1331        let n = self
1332            .write_int(Command::TaskIncrement {
1333                id,
1334                field: field.to_string(),
1335            })
1336            .await?;
1337        Ok(i32::try_from(n)?)
1338    }
1339
1340    /// Reset all failure/retry counters to zero.
1341    pub async fn reset_counters(&self, id: i64) -> anyhow::Result<()> {
1342        self.write(Command::TaskResetCounters {
1343            id,
1344            failure_only: false,
1345        })
1346        .await
1347        .map(|_| ())
1348    }
1349
1350    /// Reset transient failure/retry counters to zero, preserving
1351    /// `review_cycles`, `ci_merge_failures`, and `merge_conflict_retries`.
1352    ///
1353    /// Use this after `RequestChanges` to clear per-attempt noise without
1354    /// undoing the review-cycle count that `handle_review_changes` just set.
1355    /// `ci_merge_failures` and `merge_conflict_retries` are preserved here because
1356    /// they must accumulate across attempts to enforce their circuit breakers,
1357    /// just like `review_cycles`.
1358    ///
1359    /// NOTE: `attempts` and `review_invocations` are intentionally NOT reset here.
1360    /// Both must increase monotonically across the task's lifetime so that
1361    /// `(task_id, attempt, run_type)` keys in `task_runs` remain unique. Resetting
1362    /// either causes subsequent retries to overwrite earlier audit trail records via
1363    /// the ON CONFLICT UPSERT in `start_run()`.
1364    pub async fn reset_failure_counters(&self, id: i64) -> anyhow::Result<()> {
1365        self.write(Command::TaskResetCounters {
1366            id,
1367            failure_only: true,
1368        })
1369        .await
1370        .map(|_| ())
1371    }
1372
1373    // ---------------------------------------------------------------
1374    // Routing
1375    // ---------------------------------------------------------------
1376
1377    /// Store routing results for a task.
1378    pub async fn store_route(&self, route: &StoreRoute<'_>) -> anyhow::Result<()> {
1379        self.write(Command::TaskStoreRoute(StoreRouteCmd {
1380            id: route.id,
1381            agent: route.agent.to_string(),
1382            model: route.model.map(str::to_string),
1383            complexity: route.complexity.to_string(),
1384            estimate: route.estimate,
1385            reason: route.reason.to_string(),
1386            profile: route.profile.to_string(),
1387        }))
1388        .await
1389        .map(|_| ())
1390    }
1391
1392    /// Return `(external_id, estimate)` for all non-internal tasks that have a
1393    /// positive Fibonacci estimate stored in the database.
1394    ///
1395    /// Used by startup estimate reconciliation to push orch-stored estimates to
1396    /// the GitHub Projects board for tasks that were routed before the estimate
1397    /// sync feature was available or before `project_estimate_field_id` was
1398    /// configured.
1399    pub async fn list_external_tasks_with_estimates(&self) -> anyhow::Result<Vec<(String, u8)>> {
1400        let rows = sqlx::query(
1401            "SELECT external_id, estimate FROM tasks \
1402             WHERE estimate > 0 AND external_id NOT LIKE 'internal:%' \
1403             AND status NOT IN ('done', 'blocked')",
1404        )
1405        .fetch_all(&self.pool)
1406        .await?;
1407
1408        let mut result = Vec::new();
1409        for row in &rows {
1410            let external_id: String = row.try_get("external_id")?;
1411            let estimate_raw: i64 = row.try_get("estimate")?;
1412            if let Ok(estimate @ 1..) = u8::try_from(estimate_raw) {
1413                result.push((external_id, estimate));
1414            }
1415        }
1416        Ok(result)
1417    }
1418
1419    /// Set the Fibonacci estimate for a task (only if currently 0).
1420    ///
1421    /// Used during ingestion to populate `tasks.estimate` from GitHub Projects
1422    /// when the orch estimate is still 0. This avoids overwriting estimates
1423    /// already set by the router.
1424    pub async fn set_estimate_if_zero(&self, id: i64, estimate: u8) -> anyhow::Result<bool> {
1425        self.write_bool(Command::TaskSetEstimateIfZero { id, estimate })
1426            .await
1427    }
1428
1429    // ---------------------------------------------------------------
1430    // Memory
1431    // ---------------------------------------------------------------
1432
1433    /// Append a memory entry to the task's memory JSON array.
1434    ///
1435    /// Uses a transaction to prevent lost-update races when concurrent
1436    /// callers append simultaneously.
1437    pub async fn append_memory(&self, id: i64, entry: &MemoryEntry) -> anyhow::Result<()> {
1438        self.write(Command::TaskAppendMemory {
1439            id,
1440            entry: entry.clone(),
1441        })
1442        .await
1443        .map(|_| ())
1444    }
1445
1446    /// Get the N most recent memory entries for a task.
1447    pub async fn recent_memory(&self, id: i64, max: usize) -> anyhow::Result<Vec<MemoryEntry>> {
1448        let row = sqlx::query("SELECT memory FROM tasks WHERE id = ?")
1449            .bind(id)
1450            .fetch_one(&self.pool)
1451            .await?;
1452
1453        let memory_opt: Option<String> = row
1454            .try_get::<Option<String>, _>("memory")
1455            .map_err(|e| anyhow::anyhow!("failed to read memory column for task {id}: {e}"))?;
1456        let memory_str = memory_opt.unwrap_or_default();
1457
1458        let mut memory: Vec<MemoryEntry> = if memory_str.trim().is_empty() {
1459            Vec::new()
1460        } else {
1461            serde_json::from_str(&memory_str)
1462                .map_err(|e| anyhow::anyhow!("failed to parse memory JSON for task {id}: {e}"))?
1463        };
1464
1465        memory.sort_by_key(|m| m.attempt);
1466        if memory.len() > max {
1467            memory = memory.split_off(memory.len() - max);
1468        }
1469        Ok(memory)
1470    }
1471
1472    // ---------------------------------------------------------------
1473    // Tokens
1474    // ---------------------------------------------------------------
1475
1476    /// Add a run's token usage to a task, and its cost when the agent reported one.
1477    pub async fn store_tokens(
1478        &self,
1479        id: i64,
1480        input: u64,
1481        output: u64,
1482        cost_usd: Option<f64>,
1483    ) -> anyhow::Result<()> {
1484        sqlx::query(
1485            "UPDATE tasks SET
1486            input_tokens = COALESCE(input_tokens, 0) + ?,
1487            output_tokens = COALESCE(output_tokens, 0) + ?,
1488            total_cost_usd = COALESCE(total_cost_usd, 0.0) + COALESCE(?, 0.0),
1489            updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1490         WHERE id = ?",
1491        )
1492        .bind(i64::try_from(input).unwrap_or_else(|_| {
1493            tracing::warn!(input, "input_tokens overflows i64, clamping to MAX");
1494            i64::MAX
1495        }))
1496        .bind(i64::try_from(output).unwrap_or_else(|_| {
1497            tracing::warn!(output, "output_tokens overflows i64, clamping to MAX");
1498            i64::MAX
1499        }))
1500        .bind(cost_usd)
1501        .bind(id)
1502        .execute(&self.pool)
1503        .await?;
1504
1505        Ok(())
1506    }
1507
1508    // ---------------------------------------------------------------
1509    // Cleanup
1510    // ---------------------------------------------------------------
1511
1512    /// Mark a task's worktree as cleaned.
1513    pub async fn mark_cleaned(&self, id: i64) -> anyhow::Result<()> {
1514        self.write(Command::TaskMarkCleaned { id })
1515            .await
1516            .map(|_| ())
1517    }
1518
1519    /// Batch-update fields for multiple tasks in a single transaction.
1520    ///
1521    /// Each entry is `(id, updates)` where `updates` is a slice of `(column, value)` pairs.
1522    /// Skips entries with empty update slices.
1523    /// Test-only helper / bulk operations used by admin paths and tests.
1524    #[cfg(test)]
1525    pub async fn batch_set_fields(
1526        &self,
1527        updates: &[(i64, &[(&str, serde_json::Value)])],
1528    ) -> anyhow::Result<()> {
1529        if updates.is_empty() {
1530            return Ok(());
1531        }
1532
1533        for (_, entry_updates) in updates {
1534            for (col, _) in *entry_updates {
1535                anyhow::ensure!(
1536                    ALLOWED_FIELDS.contains(col),
1537                    "column {col} is not in the update allowlist"
1538                );
1539            }
1540        }
1541
1542        let mut tx = self.begin_write().await?;
1543        for (id, entry_updates) in updates {
1544            if entry_updates.is_empty() {
1545                continue;
1546            }
1547            let mut set_parts = Vec::new();
1548            let mut values: Vec<Option<String>> = Vec::new();
1549            for (col, val) in *entry_updates {
1550                set_parts.push(format!("{col} = ?"));
1551                values.push(sql_value_for_task_field(col, val)?);
1552            }
1553            set_parts.push("updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')".to_string());
1554            let sql = format!("UPDATE tasks SET {} WHERE id = ?", set_parts.join(", "));
1555            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1556            for v in &values {
1557                query = query.bind(v.as_deref());
1558            }
1559            query = query.bind(*id);
1560            query.execute(&mut *tx).await?;
1561        }
1562        tx.commit().await?;
1563        Ok(())
1564    }
1565
1566    /// Batch-increment a field for multiple tasks in a single transaction.
1567    ///
1568    /// Each entry is `(id, field)`. All fields must be in the incrementable allowlist.
1569    /// Test-only helper for bulk increments used by tests.
1570    #[cfg(test)]
1571    pub async fn batch_increment(&self, entries: &[(i64, &str)]) -> anyhow::Result<()> {
1572        if entries.is_empty() {
1573            return Ok(());
1574        }
1575
1576        for (_, field) in entries {
1577            anyhow::ensure!(
1578                INCREMENTABLE_FIELDS.contains(field),
1579                "field {field} is not incrementable"
1580            );
1581        }
1582
1583        let mut tx = self.begin_write().await?;
1584        for (id, field) in entries {
1585            let sql = format!(
1586                "UPDATE tasks SET {field} = {field} + 1, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = ?"
1587            );
1588            sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
1589                .bind(*id)
1590                .execute(&mut *tx)
1591                .await?;
1592        }
1593        tx.commit().await?;
1594        Ok(())
1595    }
1596
1597    /// Reset transient failure/retry counters for multiple tasks in a single transaction.
1598    ///
1599    /// Preserves `review_cycles`, `ci_merge_failures`, and
1600    /// `merge_conflict_retries` (same semantics as
1601    /// [`reset_failure_counters`]).
1602    /// Test-only helper for bulk counter resets used by tests.
1603    #[cfg(test)]
1604    pub async fn batch_reset_failure_counters(&self, ids: &[i64]) -> anyhow::Result<()> {
1605        // SQLite has a hard limit on host parameters (commonly 999). Chunk the
1606        // IN-list into smaller batches to avoid exceeding the limit.
1607        const CHUNK_SIZE: usize = 500;
1608        if ids.is_empty() {
1609            return Ok(());
1610        }
1611
1612        let mut tx = self.begin_write().await?;
1613
1614        for chunk in ids.chunks(CHUNK_SIZE) {
1615            // Build WHERE id IN (?, ?, ...) placeholders for this chunk
1616            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
1617            let sql = format!(
1618                "UPDATE tasks SET
1619            route_attempts = 0,
1620            review_agent_failures = 0,
1621            review_resets = 0,
1622            review_invocations = 0,
1623            pr_create_failures = 0,
1624            push_failures = 0,
1625            network_retries = 0,
1626            timeout_retries = 0,
1627            auto_unblock_count = 0,
1628            auto_unblock_last_at = '',
1629            auto_unblock_last_reason = '',
1630            ci_recovery_count = 0,
1631            no_code_reroutes = 0,
1632            needs_review_refires = 0,
1633            updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1634         WHERE id IN ({placeholders})",
1635            );
1636            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1637            for id in chunk {
1638                query = query.bind(*id);
1639            }
1640            query.execute(&mut *tx).await?;
1641        }
1642
1643        tx.commit().await?;
1644        Ok(())
1645    }
1646
1647    /// Mark multiple tasks' worktrees as cleaned in a single transaction.
1648    ///
1649    /// Appends a `branch_delete` activity event for each task.
1650    /// Test-only helper for bulk cleaning used by tests.
1651    #[cfg(test)]
1652    pub async fn batch_mark_cleaned(&self, ids: &[i64]) -> anyhow::Result<()> {
1653        // Chunk updates to avoid exceeding SQLite's parameter limit.
1654        const CHUNK_SIZE: usize = 500;
1655        if ids.is_empty() {
1656            return Ok(());
1657        }
1658
1659        let mut tx = self.begin_write().await?;
1660
1661        for chunk in ids.chunks(CHUNK_SIZE) {
1662            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
1663            let sql = format!(
1664                "UPDATE tasks SET worktree_cleaned = 1, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id IN ({placeholders})",
1665            );
1666            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1667            for id in chunk {
1668                query = query.bind(*id);
1669            }
1670            query.execute(&mut *tx).await?;
1671        }
1672
1673        // Append activity for each task (must be done individually — activity is per-task JSON)
1674        // Insert using the same transaction to avoid connection pool exhaustion
1675        // / deadlocks in tests.
1676        let details = serde_json::json!({ "worktree_cleaned": true });
1677        let details_json = serde_json::to_string(&details)?;
1678        for id in ids {
1679            sqlx::query(
1680                "INSERT INTO task_activity (task_id, event_type, from_status, to_status, agent, model, details) VALUES (?, ?, ?, ?, ?, ?, ?)",
1681            )
1682            .bind(*id)
1683            .bind("branch_delete")
1684            .bind(None::<&str>)
1685            .bind(None::<&str>)
1686            .bind(None::<&str>)
1687            .bind(None::<&str>)
1688            .bind(details_json.as_str())
1689            .execute(&mut *tx)
1690            .await?;
1691        }
1692
1693        tx.commit().await?;
1694        Ok(())
1695    }
1696
1697    /// List tasks that are done/blocked with worktrees that haven't been cleaned.
1698    /// Test-only helper and used by cleanup tests.
1699    #[cfg(test)]
1700    pub async fn list_cleanable(&self, repo: &str) -> anyhow::Result<Vec<Task>> {
1701        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1702            "SELECT {TASK_COLS} FROM tasks
1703         WHERE repo = ?
1704           AND worktree != ''
1705           AND worktree_cleaned = 0
1706           AND status IN ('done', 'blocked')
1707         ORDER BY updated_at ASC"
1708        )))
1709        .bind(repo)
1710        .fetch_all(&self.pool)
1711        .await?;
1712
1713        rows.iter().map(Self::row_to_task).collect()
1714    }
1715
1716    /// Return external IDs of tasks whose worktrees have already been cleaned.
1717    ///
1718    /// Used by `cleanup_done_worktrees` to skip already-cleaned tasks without
1719    /// issuing one `resolve_task_id + get` per task (N+1 elimination).
1720    pub async fn cleaned_external_ids(&self, repo: &str) -> anyhow::Result<HashSet<String>> {
1721        let rows: Vec<(String,)> =
1722            sqlx::query_as("SELECT external_id FROM tasks WHERE repo = ? AND worktree_cleaned = 1 AND external_id IS NOT NULL")
1723                .bind(repo)
1724                .fetch_all(&self.pool)
1725                .await?;
1726
1727        Ok(rows.into_iter().map(|(id,)| id).collect())
1728    }
1729
1730    // ---------------------------------------------------------------
1731    // Task Runs (audit trail)
1732    // ---------------------------------------------------------------
1733
1734    /// Start a new run, returning its ID.
1735    pub async fn start_run(&self, run: &StartRun<'_>) -> anyhow::Result<i64> {
1736        // Self-heal: an earlier attempt of the same run_type may have been
1737        // abandoned by a crash or SIGKILL that never reached
1738        // complete_run/finalize_incomplete_runs. Close those out now so they
1739        // don't leak as permanently-open rows. Scoped to the same run_type so
1740        // a still-open run of a *different* run_type (e.g. an agent run left
1741        // open when a review run starts) is untouched here — that case is
1742        // handled by the explicit finalize_incomplete_runs calls in the
1743        // engine's recovery paths, not this opportunistic self-heal. The
1744        // current attempt is excluded so a legitimate retry of the same run
1745        // still goes through the ON CONFLICT upsert below instead of being
1746        // finalized out from under itself.
1747        let orphaned: Vec<(i64,)> = sqlx::query_as(
1748            "SELECT id FROM task_runs
1749             WHERE task_id = ? AND run_type = ? AND attempt != ?
1750               AND outcome IS NULL AND completed_at IS NULL",
1751        )
1752        .bind(run.task_id)
1753        .bind(run.run_type)
1754        .bind(run.attempt)
1755        .fetch_all(&self.pool)
1756        .await?;
1757
1758        for (run_id,) in orphaned {
1759            self.complete_run(&CompleteRun {
1760                run_id,
1761                exit_code: Some(-1),
1762                stdout: "",
1763                stderr: "",
1764                parsed: "",
1765                outcome: "aborted",
1766                error: "superseded by a new run start before this one completed",
1767                tokens: RunTokenUsage::default(),
1768            })
1769            .await?;
1770        }
1771
1772        let row = sqlx::query(
1773            "INSERT INTO task_runs (task_id, attempt, run_type, agent, model, command, prompt, outcome)
1774         VALUES (?, ?, ?, ?, ?, ?, ?, NULL)
1775         ON CONFLICT(task_id, attempt, run_type) DO UPDATE SET
1776            agent = excluded.agent, model = excluded.model,
1777            command = excluded.command, prompt = excluded.prompt,
1778            outcome = NULL,
1779            started_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now'),
1780            completed_at = NULL, error = NULL, exit_code = NULL,
1781            stdout = NULL, stderr = NULL, parsed_response = NULL,
1782            duration_secs = NULL, input_tokens = NULL, output_tokens = NULL,
1783            total_cost_usd = NULL
1784         RETURNING id",
1785        )
1786        .bind(run.task_id)
1787        .bind(run.attempt)
1788        .bind(run.run_type)
1789        .bind(run.agent)
1790        .bind(run.model)
1791        .bind(run.command)
1792        .bind(run.prompt)
1793        .fetch_one(&self.pool)
1794        .await?;
1795
1796        let run_id: i64 = row
1797            .try_get("id")
1798            .map_err(|e| anyhow::anyhow!("INSERT RETURNING id returned NULL or invalid: {e}"))?;
1799        let event_type = match run.run_type {
1800            "review" => "review_start",
1801            _ => "dispatch",
1802        };
1803        let details = serde_json::json!({
1804            "run_type": run.run_type,
1805            "attempt": run.attempt,
1806            "command": run.command,
1807        });
1808        self.append_activity(
1809            run.task_id,
1810            event_type,
1811            None,
1812            None,
1813            Some(run.agent),
1814            Some(run.model),
1815            Some(&details),
1816        )
1817        .await?;
1818
1819        Ok(run_id)
1820    }
1821
1822    /// Complete a run with results.
1823    pub async fn complete_run(&self, run: &CompleteRun<'_>) -> anyhow::Result<()> {
1824        let run_ctx =
1825            sqlx::query("SELECT task_id, run_type, agent, model FROM task_runs WHERE id = ?")
1826                .bind(run.run_id)
1827                .fetch_optional(&self.pool)
1828                .await?;
1829        let updated = sqlx::query(
1830            "UPDATE task_runs SET
1831            exit_code = ?, stdout = ?, stderr = ?, parsed_response = ?,
1832            outcome = ?, error = NULLIF(?, ''),
1833            input_tokens = ?, output_tokens = ?, total_cost_usd = ?,
1834            duration_secs = CASE WHEN ? > 0 THEN ?
1835                ELSE MAX(0, (julianday('now') - julianday(started_at)) * 86400.0) END,
1836            completed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
1837         WHERE id = ? AND outcome IS NULL",
1838        )
1839        .bind(run.exit_code)
1840        .bind(run.stdout)
1841        .bind(security::redact(run.stderr))
1842        .bind(run.parsed)
1843        .bind(run.outcome)
1844        .bind(security::redact(run.error))
1845        .bind(i64::try_from(run.tokens.input_tokens).unwrap_or_else(|_| {
1846            tracing::warn!(
1847                run.tokens.input_tokens,
1848                "input_tokens overflows i64, clamping to MAX"
1849            );
1850            i64::MAX
1851        }))
1852        .bind(i64::try_from(run.tokens.output_tokens).unwrap_or_else(|_| {
1853            tracing::warn!(
1854                run.tokens.output_tokens,
1855                "output_tokens overflows i64, clamping to MAX"
1856            );
1857            i64::MAX
1858        }))
1859        .bind(run.tokens.total_cost_usd)
1860        .bind(run.tokens.duration_secs)
1861        .bind(run.tokens.duration_secs)
1862        .bind(run.run_id)
1863        .execute(&self.pool)
1864        .await?;
1865        if updated.rows_affected() == 0 && run_ctx.is_some() {
1866            // The first writer knows the cause, so a closed run keeps its outcome and error
1867            tracing::debug!(
1868                run_id = run.run_id,
1869                "run already closed, keeping its outcome"
1870            );
1871            return Ok(());
1872        }
1873        if let Some(run_ctx) = run_ctx {
1874            let task_id: i64 = run_ctx.try_get("task_id").map_err(|e| {
1875                anyhow::anyhow!("failed to decode task_id from run_id {}: {e}", run.run_id)
1876            })?;
1877            let run_type: String = run_ctx.try_get("run_type").map_err(|e| {
1878                anyhow::anyhow!("failed to decode run_type from run_id {}: {e}", run.run_id)
1879            })?;
1880            let agent: String = run_ctx.try_get("agent").map_err(|e| {
1881                anyhow::anyhow!("failed to decode agent from run_id {}: {e}", run.run_id)
1882            })?;
1883            let model: String = run_ctx.try_get("model").map_err(|e| {
1884                anyhow::anyhow!("failed to decode model from run_id {}: {e}", run.run_id)
1885            })?;
1886            if run.outcome == "timeout" {
1887                self.append_activity(
1888                    task_id,
1889                    "timeout",
1890                    None,
1891                    None,
1892                    Some(&agent),
1893                    Some(&model),
1894                    Some(&serde_json::json!({
1895                        "run_type": run_type,
1896                        "error": run.error,
1897                        "stderr": run.stderr,
1898                    })),
1899                )
1900                .await?;
1901            } else if run.outcome != "success" {
1902                self.append_activity(
1903                    task_id,
1904                    "error",
1905                    None,
1906                    None,
1907                    Some(&agent),
1908                    Some(&model),
1909                    Some(&serde_json::json!({
1910                        "run_type": run_type,
1911                        "outcome": run.outcome,
1912                        "error": run.error,
1913                    })),
1914                )
1915                .await?;
1916            }
1917        } else if run.outcome != "success" {
1918            tracing::warn!(
1919                run_id = run.run_id,
1920                outcome = run.outcome,
1921                "run_id not found in task_runs; skipping timeout/error activity log"
1922            );
1923        }
1924        Ok(())
1925    }
1926
1927    /// Finalize all incomplete runs for a task.
1928    ///
1929    /// This is used on runner early-return/error paths and engine shutdown
1930    /// paths to ensure every `start_run` has a terminal record.
1931    pub async fn finalize_incomplete_runs(
1932        &self,
1933        task_id: i64,
1934        outcome: &str,
1935        error: &str,
1936    ) -> anyhow::Result<u64> {
1937        let rows: Vec<(i64,)> = sqlx::query_as(
1938            "SELECT id FROM task_runs
1939             WHERE task_id = ?
1940               AND outcome IS NULL
1941               AND completed_at IS NULL
1942             ORDER BY id ASC",
1943        )
1944        .bind(task_id)
1945        .fetch_all(&self.pool)
1946        .await?;
1947
1948        for (run_id,) in &rows {
1949            self.complete_run(&CompleteRun {
1950                run_id: *run_id,
1951                exit_code: Some(-1),
1952                stdout: "",
1953                stderr: "",
1954                parsed: "",
1955                outcome,
1956                error,
1957                tokens: RunTokenUsage::default(),
1958            })
1959            .await?;
1960        }
1961
1962        Ok(rows.len() as u64)
1963    }
1964
1965    /// Get all runs for a task, ordered by attempt.
1966    pub async fn get_runs(&self, task_id: i64) -> anyhow::Result<Vec<TaskRun>> {
1967        let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
1968            "SELECT {TASK_RUN_COLS} FROM task_runs WHERE task_id = ? ORDER BY attempt ASC, run_type ASC"
1969        )))
1970        .bind(task_id)
1971        .fetch_all(&self.pool)
1972        .await?;
1973
1974        rows.iter().map(Self::row_to_run).collect()
1975    }
1976
1977    /// Get all runs for a batch of task IDs in a single query, grouped by task ID.
1978    /// Returns an empty map if `task_ids` is empty.
1979    pub async fn get_runs_batch(
1980        &self,
1981        task_ids: &[i64],
1982    ) -> anyhow::Result<HashMap<i64, Vec<TaskRun>>> {
1983        // SQLite has a hard limit on host parameters (commonly 999). Chunk the
1984        // IN-list into smaller batches to avoid exceeding the limit.
1985        const CHUNK_SIZE: usize = 500;
1986        if task_ids.is_empty() {
1987            return Ok(HashMap::new());
1988        }
1989
1990        let mut result: HashMap<i64, Vec<TaskRun>> = HashMap::new();
1991
1992        for chunk in task_ids.chunks(CHUNK_SIZE) {
1993            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
1994            let sql = format!(
1995                "SELECT {TASK_RUN_COLS} FROM task_runs WHERE task_id IN ({placeholders}) ORDER BY task_id ASC, attempt ASC, run_type ASC"
1996            );
1997            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
1998            for id in chunk {
1999                query = query.bind(*id);
2000            }
2001            let rows = query.fetch_all(&self.pool).await?;
2002            for row in &rows {
2003                let run = Self::row_to_run(row)?;
2004                result.entry(run.task_id).or_default().push(run);
2005            }
2006        }
2007        Ok(result)
2008    }
2009
2010    /// Get multiple tasks by ID in a single query, returning a map from task ID to Task.
2011    /// Returns an empty map if `ids` is empty.
2012    pub async fn get_batch(&self, ids: &[i64]) -> anyhow::Result<HashMap<i64, Task>> {
2013        // SQLite has a hard limit on host parameters (commonly 999). Chunk the
2014        // IN-list into smaller batches to avoid exceeding the limit.
2015        const CHUNK_SIZE: usize = 500;
2016        if ids.is_empty() {
2017            return Ok(HashMap::new());
2018        }
2019
2020        let mut result = HashMap::new();
2021
2022        for chunk in ids.chunks(CHUNK_SIZE) {
2023            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
2024            let sql = format!("SELECT {TASK_COLS} FROM tasks WHERE id IN ({placeholders})");
2025            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
2026            for id in chunk {
2027                query = query.bind(*id);
2028            }
2029            let rows = query.fetch_all(&self.pool).await?;
2030            for row in &rows {
2031                let task = Self::row_to_task(row)?;
2032                result.insert(task.id, task);
2033            }
2034        }
2035        Ok(result)
2036    }
2037
2038    /// Get statuses for a batch of external IDs in a single query.
2039    /// Returns a map from external_id -> TaskStatus for rows found in the DB.
2040    /// Missing external_ids are simply not present in the returned map.
2041    /// Uses chunking to avoid SQLite parameter limits.
2042    pub async fn get_statuses_by_external_ids(
2043        &self,
2044        repo: &str,
2045        ext_ids: &[&str],
2046    ) -> anyhow::Result<HashMap<String, TaskStatus>> {
2047        const CHUNK_SIZE: usize = 500;
2048        let mut result = HashMap::new();
2049        if ext_ids.is_empty() {
2050            return Ok(result);
2051        }
2052
2053        for chunk in ext_ids.chunks(CHUNK_SIZE) {
2054            let placeholders = chunk.iter().map(|_| "?").collect::<Vec<_>>().join(", ");
2055            let sql = format!(
2056                "SELECT external_id, status FROM tasks WHERE repo = ? AND external_id IN ({placeholders})"
2057            );
2058            let mut query = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
2059            query = query.bind(repo);
2060            for id in chunk {
2061                query = query.bind(id);
2062            }
2063            let rows = query.fetch_all(&self.pool).await?;
2064            for row in &rows {
2065                let external_id: Result<String, _> = row.try_get("external_id");
2066                let status_str: Result<String, _> = row.try_get("status");
2067                match (external_id, status_str) {
2068                    (Ok(ext_id), Ok(sts)) => {
2069                        if ext_id.is_empty() {
2070                            tracing::warn!(status = %sts, "skipping task row with empty external_id");
2071                            continue;
2072                        }
2073                        if let Some(status) = TaskStatus::from_str(&sts) {
2074                            result.insert(ext_id, status);
2075                        } else {
2076                            tracing::warn!(external_id = %ext_id, status = %sts, "unknown status value in tasks table");
2077                        }
2078                    }
2079                    (Err(e), _) => {
2080                        tracing::warn!(error = %e, "skipping task row with decode error for external_id");
2081                    }
2082                    (_, Err(e)) => {
2083                        tracing::warn!(error = %e, "skipping task row with decode error for status");
2084                    }
2085                }
2086            }
2087        }
2088
2089        Ok(result)
2090    }
2091
2092    /// Get the last run of a specific type for a task.
2093    /// Used by tests to assert run ordering.
2094    #[cfg(test)]
2095    pub async fn get_last_run(
2096        &self,
2097        task_id: i64,
2098        run_type: &str,
2099    ) -> anyhow::Result<Option<TaskRun>> {
2100        let row = sqlx::query(sqlx::AssertSqlSafe(format!(
2101        "SELECT {TASK_RUN_COLS} FROM task_runs WHERE task_id = ? AND run_type = ? ORDER BY attempt DESC LIMIT 1"
2102    )))
2103    .bind(task_id)
2104    .bind(run_type)
2105    .fetch_optional(&self.pool)
2106    .await?;
2107
2108        match row {
2109            Some(r) => Ok(Some(Self::row_to_run(&r)?)),
2110            None => Ok(None),
2111        }
2112    }
2113
2114    /// List distinct agents that have previously reviewed this task.
2115    /// Used to exclude them when picking the next review agent, so a different
2116    /// agent reviews each cycle (avoids the same agent hitting the same issues).
2117    pub async fn previous_review_agents(&self, task_id: i64) -> anyhow::Result<Vec<String>> {
2118        let rows: Vec<(String,)> = sqlx::query_as(
2119            "SELECT DISTINCT agent FROM task_runs \
2120             WHERE task_id = ? AND run_type = 'review' \
2121             ORDER BY agent",
2122        )
2123        .bind(task_id)
2124        .fetch_all(&self.pool)
2125        .await?;
2126        Ok(rows.into_iter().map(|(a,)| a).collect())
2127    }
2128
2129    /// Clear the text columns of runs for tasks that entered `done` over `days` days ago
2130    /// Metrics stay, and blocked tasks keep every run
2131    pub async fn prune_old_runs(&self, days: i32) -> anyhow::Result<u64> {
2132        let result = sqlx::query(
2133            "UPDATE task_runs
2134             SET stdout = '', stderr = '', prompt = '', parsed_response = ''
2135             WHERE (stdout != '' OR stderr != '' OR prompt != '' OR parsed_response != '')
2136               AND task_id IN (
2137                 SELECT t.id FROM tasks t
2138                 WHERE t.status = 'done'
2139                   AND (SELECT MAX(a.timestamp) FROM task_activity a
2140                        WHERE a.task_id = t.id
2141                          AND a.event_type = 'status_change'
2142                          AND a.to_status = 'done')
2143                       < strftime('%Y-%m-%dT%H:%M:%SZ', 'now', ? || ' days')
2144               )",
2145        )
2146        .bind(format!("-{days}"))
2147        .execute(&self.pool)
2148        .await?;
2149
2150        if result.rows_affected() > 0 {
2151            sqlx::query("PRAGMA incremental_vacuum")
2152                .execute(&self.pool)
2153                .await?;
2154        }
2155
2156        Ok(result.rows_affected())
2157    }
2158
2159    // ---------------------------------------------------------------
2160    // Row mapping helpers
2161    // ---------------------------------------------------------------
2162
2163    fn row_to_task(row: &SqliteRow) -> anyhow::Result<Task> {
2164        // Diagnostic: verify column count matches expected to prevent OOB panics
2165        // when schema changes without updating TASK_COLS
2166        let col_count = row.len();
2167        if col_count != TASK_COLS_COUNT {
2168            tracing::warn!(
2169                expected = TASK_COLS_COUNT,
2170                actual = col_count,
2171                "TASK_COLS column count mismatch - schema may have changed"
2172            );
2173        }
2174
2175        let status_str: String = row
2176            .try_get("status")
2177            .map_err(|e| anyhow::anyhow!("task row missing status column: {e}"))?;
2178        let labels_str: String = row.try_get("labels").unwrap_or_default();
2179        let memory_str: String = row.try_get("memory").unwrap_or_default();
2180        let delegations_str: String = row.try_get("delegations").unwrap_or_default();
2181
2182        Ok(Task {
2183            id: row
2184                .try_get("id")
2185                .map_err(|e| anyhow::anyhow!("task row missing id: {e}"))?,
2186            external_id: row.try_get("external_id").unwrap_or(None),
2187            repo: row
2188                .try_get("repo")
2189                .map_err(|e| anyhow::anyhow!("task row missing or invalid repo: {e}"))?,
2190            origin: row
2191                .try_get("origin")
2192                .map_err(|e| anyhow::anyhow!("task row missing or invalid origin: {e}"))?,
2193            title: row
2194                .try_get("title")
2195                .map_err(|e| anyhow::anyhow!("task row missing or invalid title: {e}"))?,
2196            body: row
2197                .try_get("body")
2198                .map_err(|e| anyhow::anyhow!("task row missing or invalid body: {e}"))?,
2199            status: TaskStatus::from_str(&status_str)
2200                .ok_or_else(|| anyhow::anyhow!("unknown task status: '{status_str}'"))?,
2201            source: row
2202                .try_get("source")
2203                .map_err(|e| anyhow::anyhow!("task row missing or invalid source: {e}"))?,
2204            source_id: row
2205                .try_get("source_id")
2206                .map_err(|e| anyhow::anyhow!("task row missing or invalid source_id: {e}"))?,
2207            author: row
2208                .try_get("author")
2209                .map_err(|e| anyhow::anyhow!("task row missing or invalid author: {e}"))?,
2210            url: row
2211                .try_get("url")
2212                .map_err(|e| anyhow::anyhow!("task row missing or invalid url: {e}"))?,
2213            labels: serde_json::from_str(&labels_str)
2214                .inspect_err(
2215                    |e| tracing::warn!(error = %e, "corrupt labels JSON, defaulting to empty"),
2216                )
2217                .unwrap_or_default(),
2218            agent: row.try_get("agent").unwrap_or(None),
2219            model: row.try_get("model").unwrap_or(None),
2220            complexity: row.try_get("complexity").unwrap_or_default(),
2221            estimate: {
2222                let raw: i32 = row.try_get("estimate").unwrap_or(0);
2223                u8::try_from(raw).unwrap_or_else(|_| {
2224                    tracing::warn!(
2225                        raw_estimate = raw,
2226                        "estimate value out of u8 range; defaulting to 0"
2227                    );
2228                    0
2229                })
2230            },
2231            route_reason: row.try_get("route_reason").unwrap_or_default(),
2232            agent_profile: row.try_get("agent_profile").unwrap_or_default(),
2233            route_attempts: row.try_get("route_attempts").unwrap_or(0),
2234            attempts: row.try_get("attempts").unwrap_or(0),
2235            branch: row.try_get("branch").unwrap_or_default(),
2236            worktree: row.try_get("worktree").unwrap_or_default(),
2237            worktree_cleaned: row.try_get::<i32, _>("worktree_cleaned").unwrap_or(0) != 0,
2238            owner_node: row.try_get("owner_node").unwrap_or(None),
2239            summary: row.try_get("summary").unwrap_or_default(),
2240            last_error: row.try_get("last_error").unwrap_or_default(),
2241            parent_id: row.try_get("parent_id").unwrap_or(None),
2242            block_reason: row.try_get("block_reason").unwrap_or(None),
2243            pr_number: row.try_get("pr_number").unwrap_or(None),
2244            pr_review_context: row.try_get("pr_review_context").unwrap_or_default(),
2245            last_review_ts: row.try_get("last_review_ts").unwrap_or_default(),
2246            review_ts_map: row.try_get("review_ts_map").unwrap_or_default(),
2247            last_comment_review_ts: row.try_get("last_comment_review_ts").unwrap_or_default(),
2248            merge_conflict_retries: row.try_get("merge_conflict_retries").unwrap_or(0),
2249            ci_merge_failures: row.try_get("ci_merge_failures").unwrap_or(0),
2250            pr_create_failures: row.try_get("pr_create_failures").unwrap_or(0),
2251            push_failures: row.try_get::<i32, _>("push_failures").unwrap_or(0),
2252            network_retries: row.try_get::<i32, _>("network_retries").unwrap_or(0),
2253            timeout_retries: row.try_get::<i32, _>("timeout_retries").unwrap_or(0),
2254            review_agent_failures: row.try_get("review_agent_failures").unwrap_or(0),
2255            review_resets: row.try_get::<i32, _>("review_resets").unwrap_or(0),
2256            review_cycles: row.try_get("review_cycles").unwrap_or(0),
2257            review_invocations: row.try_get::<i32, _>("review_invocations").unwrap_or(0),
2258            needs_review_refires: row.try_get::<i32, _>("needs_review_refires").unwrap_or(0),
2259            input_tokens: row.try_get("input_tokens").unwrap_or(0),
2260            output_tokens: row.try_get("output_tokens").unwrap_or(0),
2261            total_cost_usd: row.try_get("total_cost_usd").unwrap_or(0.0),
2262            model_reroute_chain: row.try_get("model_reroute_chain").unwrap_or_default(),
2263            limit_reroute_chain: row.try_get("limit_reroute_chain").unwrap_or_default(),
2264            memory: serde_json::from_str(&memory_str)
2265                .inspect_err(
2266                    |e| tracing::warn!(error = %e, "corrupt memory JSON, defaulting to empty"),
2267                )
2268                .unwrap_or_default(),
2269            delegations: serde_json::from_str(&delegations_str)
2270                .inspect_err(
2271                    |e| tracing::warn!(error = %e, "corrupt delegations JSON, defaulting to empty"),
2272                )
2273                .unwrap_or_default(),
2274            // Some deployments may be running databases that haven't had
2275            // the most recent migrations applied. Use `try_get` with
2276            // sensible defaults for recently-added columns so a missing
2277            // column doesn't cause a panic in the sqlx row accessor.
2278            auto_unblock_count: row.try_get::<i32, _>("auto_unblock_count").unwrap_or(0),
2279            auto_unblock_last_at: row
2280                .try_get::<String, _>("auto_unblock_last_at")
2281                .unwrap_or_default(),
2282            auto_unblock_last_reason: row
2283                .try_get::<String, _>("auto_unblock_last_reason")
2284                .unwrap_or_default(),
2285            ci_recovery_count: row.try_get::<i32, _>("ci_recovery_count").unwrap_or(0),
2286            no_code_reroutes: row.try_get::<i32, _>("no_code_reroutes").unwrap_or(0),
2287            no_code_last_agent: row
2288                .try_get::<String, _>("no_code_last_agent")
2289                .unwrap_or_default(),
2290            created_at: row
2291                .try_get::<String, _>("created_at")
2292                .map_err(|e| anyhow::anyhow!("task row missing created_at: {e}"))?,
2293            updated_at: row
2294                .try_get::<String, _>("updated_at")
2295                .map_err(|e| anyhow::anyhow!("task row missing updated_at: {e}"))?,
2296        })
2297    }
2298    pub async fn ensure_external_task(
2299        &self,
2300        repo: &str,
2301        ext: &ExternalTask,
2302    ) -> anyhow::Result<i64> {
2303        self.upsert_external(&UpsertExternal {
2304            repo,
2305            ext_id: &ext.id.0,
2306            title: &ext.title,
2307            body: &ext.body,
2308            author: &ext.author,
2309            url: &ext.url,
2310            labels: &ext.labels,
2311            origin: "github",
2312        })
2313        .await
2314    }
2315
2316    /// Resolve a task_id (e.g. "42" or "internal:3") to a store internal ID.
2317    /// Returns None if the task is not in the store yet.
2318    pub async fn resolve_task_id(&self, repo: &str, task_id: &str) -> anyhow::Result<Option<i64>> {
2319        if let Some(suffix) = task_id.strip_prefix("internal:") {
2320            // Internal tasks: look up by external_id="internal:{n}" with origin='internal'
2321            if let Some(id) = self.resolve_id_by_external(repo, task_id).await? {
2322                return Ok(Some(id));
2323            }
2324            // Fallback: try the numeric suffix as a direct store ID.
2325            // Must include the repo filter to avoid resolving a task that
2326            // belongs to a different repository in a multi-repo setup.
2327            if let Ok(id) = suffix.parse::<i64>() {
2328                let exists = sqlx::query("SELECT id FROM tasks WHERE id = ? AND repo = ?")
2329                    .bind(id)
2330                    .bind(repo)
2331                    .fetch_optional(&self.pool)
2332                    .await?;
2333                if exists.is_some() {
2334                    return Ok(Some(id));
2335                }
2336            }
2337            return Ok(None);
2338        }
2339        // External tasks: look up by external_id using the lightweight id-only
2340        // lookup to avoid deserializing the full Task row.
2341        self.resolve_id_by_external(repo, task_id).await
2342    }
2343
2344    /// Resolve a task_id globally (across all repos) when a repo-scoped lookup fails.
2345    ///
2346    /// Returns `(internal_id, repo)` if found unambiguously, `None` if not found,
2347    /// or an error if the external_id matches tasks in multiple repos (GitHub issue
2348    /// numbers are not globally unique).
2349    pub async fn resolve_task_id_globally(
2350        &self,
2351        task_id: &str,
2352    ) -> anyhow::Result<Option<(i64, String)>> {
2353        if let Some(suffix) = task_id.strip_prefix("internal:") {
2354            // internal:N — external_id is globally unique; primary key is also unique.
2355            let row = sqlx::query("SELECT id, repo FROM tasks WHERE external_id = ?")
2356                .bind(task_id)
2357                .fetch_optional(&self.pool)
2358                .await?;
2359            if let Some(r) = row {
2360                return Ok(Some((r.try_get("id")?, r.try_get("repo")?)));
2361            }
2362            if let Ok(id) = suffix.parse::<i64>() {
2363                let row = sqlx::query("SELECT id, repo FROM tasks WHERE id = ?")
2364                    .bind(id)
2365                    .fetch_optional(&self.pool)
2366                    .await?;
2367                if let Some(r) = row {
2368                    return Ok(Some((r.try_get("id")?, r.try_get("repo")?)));
2369                }
2370            }
2371            return Ok(None);
2372        }
2373        // External IDs (e.g. GitHub issue numbers) may collide across repos.
2374        let rows = sqlx::query("SELECT id, repo FROM tasks WHERE external_id = ?")
2375            .bind(task_id)
2376            .fetch_all(&self.pool)
2377            .await?;
2378        match rows.len() {
2379            0 => Ok(None),
2380            1 => Ok(Some((rows[0].try_get("id")?, rows[0].try_get("repo")?))),
2381            _ => {
2382                let repos: Vec<String> = rows
2383                    .iter()
2384                    .filter_map(|r| r.try_get::<String, _>("repo").ok())
2385                    .collect();
2386                anyhow::bail!(
2387                    "task ID {task_id:?} is ambiguous — found in multiple repos: {}. \
2388                     Run from the repo worktree so the current-repo lookup resolves it.",
2389                    repos.join(", ")
2390                )
2391            }
2392        }
2393    }
2394
2395    /// Resolve a CLI task id. A bare number in a project is that project's issue first
2396    pub async fn resolve_cli_id(
2397        &self,
2398        spec: &str,
2399        current_repo: Option<&str>,
2400    ) -> anyhow::Result<Option<CliTaskRef>> {
2401        let current_repo = current_repo.filter(|repo| !repo.is_empty());
2402        match parse_cli_task_spec(spec)? {
2403            CliTaskSpec::Internal(number) => self.lookup_internal_cli_id(number).await,
2404            CliTaskSpec::Issue { repo, number } => self.lookup_issue_cli_id(&repo, number).await,
2405            CliTaskSpec::Bare(number) => {
2406                if let Some(repo) = current_repo {
2407                    if let Some(task) = self.lookup_issue_cli_id(repo, number).await? {
2408                        return Ok(Some(task));
2409                    }
2410                    if let Some(task) = self.lookup_internal_in_repo(number, repo).await? {
2411                        return Ok(Some(task));
2412                    }
2413                    return Ok(None);
2414                }
2415                let mut matches = self.lookup_bare_cli_id_matches(number).await?;
2416                match matches.len() {
2417                    0 => Ok(None),
2418                    1 => Ok(matches.pop()),
2419                    _ => Err(ambiguous_cli_id_error(spec, &matches)),
2420                }
2421            }
2422        }
2423    }
2424
2425    async fn lookup_internal_cli_id(&self, number: i64) -> anyhow::Result<Option<CliTaskRef>> {
2426        let ext = format!("internal:{number}");
2427        let rows = sqlx::query(
2428            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2429             WHERE origin = 'internal' AND (id = ? OR external_id = ?) \
2430             ORDER BY id",
2431        )
2432        .bind(number)
2433        .bind(&ext)
2434        .fetch_all(&self.pool)
2435        .await?;
2436        let mut matches = dedup_cli_refs(rows)?;
2437        match matches.len() {
2438            0 => Ok(None),
2439            1 => Ok(matches.pop()),
2440            _ => Err(ambiguous_cli_id_error(&ext, &matches)),
2441        }
2442    }
2443
2444    async fn lookup_internal_in_repo(
2445        &self,
2446        number: i64,
2447        repo: &str,
2448    ) -> anyhow::Result<Option<CliTaskRef>> {
2449        let row = sqlx::query(
2450            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2451             WHERE origin = 'internal' AND id = ? AND repo = ?",
2452        )
2453        .bind(number)
2454        .bind(repo)
2455        .fetch_optional(&self.pool)
2456        .await?;
2457        match row {
2458            Some(row) => Ok(Some(CliTaskRef::from_row(&row)?)),
2459            None => Ok(None),
2460        }
2461    }
2462
2463    async fn lookup_issue_cli_id(
2464        &self,
2465        repo: &str,
2466        number: i64,
2467    ) -> anyhow::Result<Option<CliTaskRef>> {
2468        let ext = number.to_string();
2469        let rows = sqlx::query(
2470            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2471             WHERE repo = ? AND origin != 'internal' AND external_id = ? \
2472             ORDER BY id",
2473        )
2474        .bind(repo)
2475        .bind(&ext)
2476        .fetch_all(&self.pool)
2477        .await?;
2478        let mut matches = dedup_cli_refs(rows)?;
2479        match matches.len() {
2480            0 => Ok(None),
2481            1 => Ok(matches.pop()),
2482            _ => Err(ambiguous_cli_id_error(
2483                &format!("{repo}#{number}"),
2484                &matches,
2485            )),
2486        }
2487    }
2488
2489    async fn lookup_bare_cli_id_matches(&self, number: i64) -> anyhow::Result<Vec<CliTaskRef>> {
2490        let ext = number.to_string();
2491        let rows = sqlx::query(
2492            "SELECT id, repo, origin, title, external_id, status FROM tasks \
2493             WHERE (origin = 'internal' AND id = ?) \
2494                OR (origin != 'internal' AND external_id = ?) \
2495             ORDER BY repo, id",
2496        )
2497        .bind(number)
2498        .bind(&ext)
2499        .fetch_all(&self.pool)
2500        .await?;
2501        dedup_cli_refs(rows)
2502    }
2503
2504    fn row_to_run(row: &SqliteRow) -> anyhow::Result<TaskRun> {
2505        Ok(TaskRun {
2506            id: row
2507                .try_get("id")
2508                .map_err(|e| anyhow::anyhow!("run row decode error for id: {e}"))?,
2509            task_id: row
2510                .try_get("task_id")
2511                .map_err(|e| anyhow::anyhow!("run row decode error for task_id: {e}"))?,
2512            attempt: row
2513                .try_get("attempt")
2514                .map_err(|e| anyhow::anyhow!("run row decode error for attempt: {e}"))?,
2515            run_type: row
2516                .try_get("run_type")
2517                .map_err(|e| anyhow::anyhow!("run row decode error for run_type: {e}"))?,
2518            agent: row
2519                .try_get("agent")
2520                .map_err(|e| anyhow::anyhow!("run row decode error for agent: {e}"))?,
2521            model: row
2522                .try_get("model")
2523                .map_err(|e| anyhow::anyhow!("run row decode error for model: {e}"))?,
2524            command: row
2525                .try_get("command")
2526                .map_err(|e| anyhow::anyhow!("run row decode error for command: {e}"))?,
2527            prompt: row
2528                .try_get("prompt")
2529                .map_err(|e| anyhow::anyhow!("run row decode error for prompt: {e}"))?,
2530            env_vars: row
2531                .try_get("env_vars")
2532                .map_err(|e| anyhow::anyhow!("run row decode error for env_vars: {e}"))?,
2533            exit_code: row
2534                .try_get("exit_code")
2535                .map_err(|e| anyhow::anyhow!("run row decode error for exit_code: {e}"))?,
2536            stdout: row
2537                .try_get("stdout")
2538                .map_err(|e| anyhow::anyhow!("run row decode error for stdout: {e}"))?,
2539            stderr: row
2540                .try_get("stderr")
2541                .map_err(|e| anyhow::anyhow!("run row decode error for stderr: {e}"))?,
2542            parsed_response: row
2543                .try_get("parsed_response")
2544                .map_err(|e| anyhow::anyhow!("run row decode error for parsed_response: {e}"))?,
2545            outcome: row
2546                .try_get("outcome")
2547                .map_err(|e| anyhow::anyhow!("run row decode error for outcome: {e}"))?,
2548            error: row
2549                .try_get("error")
2550                .map_err(|e| anyhow::anyhow!("run row decode error for error: {e}"))?,
2551            input_tokens: row
2552                .try_get("input_tokens")
2553                .map_err(|e| anyhow::anyhow!("run row decode error for input_tokens: {e}"))?,
2554            output_tokens: row
2555                .try_get("output_tokens")
2556                .map_err(|e| anyhow::anyhow!("run row decode error for output_tokens: {e}"))?,
2557            total_cost_usd: row
2558                .try_get("total_cost_usd")
2559                .map_err(|e| anyhow::anyhow!("run row decode error for total_cost_usd: {e}"))?,
2560            duration_secs: row
2561                .try_get("duration_secs")
2562                .map_err(|e| anyhow::anyhow!("run row decode error for duration_secs: {e}"))?,
2563            started_at: row
2564                .try_get("started_at")
2565                .map_err(|e| anyhow::anyhow!("run row decode error for started_at: {e}"))?,
2566            completed_at: row.try_get("completed_at").unwrap_or(None),
2567        })
2568    }
2569
2570    fn row_to_activity(row: &SqliteRow) -> anyhow::Result<TaskActivity> {
2571        let details_str: String = row
2572            .try_get("details")
2573            .map_err(|e| anyhow::anyhow!("activity row decode error for details: {e}"))?;
2574        let details = serde_json::from_str::<serde_json::Value>(&details_str).inspect_err(
2575            |e| tracing::warn!(error = %e, "corrupt activity details JSON, skipping field"),
2576        )?;
2577        Ok(TaskActivity {
2578            id: row
2579                .try_get("id")
2580                .map_err(|e| anyhow::anyhow!("activity row decode error for id: {e}"))?,
2581            task_id: row
2582                .try_get("task_id")
2583                .map_err(|e| anyhow::anyhow!("activity row decode error for task_id: {e}"))?,
2584            timestamp: row
2585                .try_get("timestamp")
2586                .map_err(|e| anyhow::anyhow!("activity row decode error for timestamp: {e}"))?,
2587            event_type: row
2588                .try_get("event_type")
2589                .map_err(|e| anyhow::anyhow!("activity row decode error for event_type: {e}"))?,
2590            from_status: row
2591                .try_get("from_status")
2592                .map_err(|e| anyhow::anyhow!("activity row decode error for from_status: {e}"))?,
2593            to_status: row
2594                .try_get("to_status")
2595                .map_err(|e| anyhow::anyhow!("activity row decode error for to_status: {e}"))?,
2596            agent: row
2597                .try_get("agent")
2598                .map_err(|e| anyhow::anyhow!("activity row decode error for agent: {e}"))?,
2599            model: row
2600                .try_get("model")
2601                .map_err(|e| anyhow::anyhow!("activity row decode error for model: {e}"))?,
2602            details,
2603        })
2604    }
2605}
2606
2607/// Columns that describe this node's own copy of a task, never sent through the log
2608const LOCAL_TASK_COLUMNS: &[&str] = &[
2609    "worktree",
2610    "input_tokens",
2611    "output_tokens",
2612    "input_cost_usd",
2613    "output_cost_usd",
2614    "total_cost_usd",
2615];
2616
2617pub(crate) fn is_local_column(col: &str) -> bool {
2618    LOCAL_TASK_COLUMNS.contains(&col)
2619}
2620
2621/// Owned copy of [`UpsertExternal`], so it can travel in a Raft entry
2622#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
2623pub struct UpsertExternalCmd {
2624    pub repo: String,
2625    pub ext_id: String,
2626    pub title: String,
2627    pub body: String,
2628    pub author: String,
2629    pub url: String,
2630    pub labels: Vec<String>,
2631    pub origin: String,
2632    pub parent_id: Option<i64>,
2633}
2634
2635/// Owned copy of [`StoreRoute`], so it can travel in a Raft entry
2636#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
2637pub struct StoreRouteCmd {
2638    pub id: i64,
2639    pub agent: String,
2640    pub model: Option<String>,
2641    pub complexity: String,
2642    pub estimate: u8,
2643    pub reason: String,
2644    pub profile: String,
2645}
2646
2647/// The columns a done task's tombstone keeps. Nodes that had the task keep the rest locally
2648const TOMBSTONE_COLUMNS: &[&str] = &[
2649    "id",
2650    "external_id",
2651    "repo",
2652    "origin",
2653    "title",
2654    "status",
2655    "pr_number",
2656    "parent_id",
2657    "created_at",
2658    "updated_at",
2659];
2660
2661async fn replicated_columns(conn: &mut sqlx::SqliteConnection) -> anyhow::Result<Vec<String>> {
2662    let cols: Vec<(String,)> = sqlx::query_as("SELECT name FROM pragma_table_info('tasks')")
2663        .fetch_all(conn)
2664        .await?;
2665    Ok(cols
2666        .into_iter()
2667        .map(|(c,)| c)
2668        .filter(|c| !is_local_column(c))
2669        .collect())
2670}
2671
2672/// Every task row as JSON with its replicated columns, done tasks as tombstones
2673pub(crate) async fn replicated_rows(pool: &SqlitePool) -> anyhow::Result<Vec<serde_json::Value>> {
2674    let mut conn = pool.acquire().await?;
2675    let cols = replicated_columns(&mut conn).await?;
2676    let full: Vec<&String> = cols.iter().collect();
2677    let tomb: Vec<&String> = cols
2678        .iter()
2679        .filter(|c| TOMBSTONE_COLUMNS.contains(&c.as_str()))
2680        .collect();
2681    let sql = format!(
2682        "SELECT CASE WHEN status = 'done' THEN {} ELSE {} END FROM tasks ORDER BY id",
2683        super::replica::json_row_sql(&tomb),
2684        super::replica::json_row_sql(&full)
2685    );
2686    let rows: Vec<(String,)> = sqlx::query_as(sqlx::AssertSqlSafe(sql.as_str()))
2687        .fetch_all(&mut *conn)
2688        .await?;
2689    rows.into_iter()
2690        .map(|(r,)| Ok(serde_json::from_str(&r)?))
2691        .collect()
2692}
2693
2694/// Make the task rows match `rows`. Local columns, and a tombstone's missing columns, keep the local
2695/// value. Tasks missing from `rows` go, with this node's runs and activity for them
2696pub(crate) async fn replace_replicated(
2697    conn: &mut sqlx::SqliteConnection,
2698    rows: &[serde_json::Value],
2699) -> anyhow::Result<()> {
2700    let allowed = replicated_columns(conn).await?;
2701    sqlx::query("PRAGMA defer_foreign_keys = ON")
2702        .execute(&mut *conn)
2703        .await?;
2704    sqlx::query("CREATE TEMP TABLE IF NOT EXISTS snapshot_ids (id INTEGER PRIMARY KEY)")
2705        .execute(&mut *conn)
2706        .await?;
2707    sqlx::query("DELETE FROM snapshot_ids")
2708        .execute(&mut *conn)
2709        .await?;
2710    for row in rows {
2711        let id = row["id"]
2712            .as_i64()
2713            .context("snapshot task row without an id")?;
2714        sqlx::query("INSERT INTO snapshot_ids (id) VALUES (?)")
2715            .bind(id)
2716            .execute(&mut *conn)
2717            .await?;
2718    }
2719
2720    for table in ["task_runs", "task_activity"] {
2721        sqlx::query(sqlx::AssertSqlSafe(format!(
2722            "DELETE FROM {table} WHERE task_id NOT IN (SELECT id FROM snapshot_ids)"
2723        )))
2724        .execute(&mut *conn)
2725        .await?;
2726    }
2727
2728    sqlx::query("DELETE FROM tasks WHERE id NOT IN (SELECT id FROM snapshot_ids)")
2729        .execute(&mut *conn)
2730        .await?;
2731
2732    for row in rows {
2733        let obj = row
2734            .as_object()
2735            .context("snapshot task row is not an object")?;
2736        let cols: Vec<&String> = obj.keys().collect();
2737        for c in &cols {
2738            anyhow::ensure!(
2739                allowed.contains(c),
2740                "snapshot names unknown task column {c}"
2741            );
2742        }
2743
2744        let values: Vec<String> = cols
2745            .iter()
2746            .map(|c| format!("json_extract(?1, '$.\"{c}\"')"))
2747            .collect();
2748        let updates: Vec<String> = cols.iter().map(|c| format!("{c} = excluded.{c}")).collect();
2749        let names: Vec<&str> = cols.iter().map(|c| c.as_str()).collect();
2750        let sql = format!(
2751            "INSERT INTO tasks ({}) VALUES ({}) ON CONFLICT(id) DO UPDATE SET {}",
2752            names.join(", "),
2753            values.join(", "),
2754            updates.join(", ")
2755        );
2756        sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2757            .bind(serde_json::to_string(row)?)
2758            .execute(&mut *conn)
2759            .await?;
2760    }
2761
2762    sqlx::query("DROP TABLE snapshot_ids")
2763        .execute(&mut *conn)
2764        .await?;
2765    Ok(())
2766}
2767
2768#[cfg(test)]
2769mod row_to_task_tests {
2770    use super::*;
2771    use crate::store;
2772    use crate::store::TaskStatus;
2773
2774    async fn store_with_task() -> (store::TaskStore, i64) {
2775        let store = store::TaskStore::open_memory().await.unwrap();
2776        let id = store
2777            .create(&NewTask {
2778                external_id: None,
2779                repo: "owner/repo".to_string(),
2780                origin: "internal".to_string(),
2781                title: "Test task".to_string(),
2782                body: "Test body".to_string(),
2783                source: "cron".to_string(),
2784                source_id: "test-1".to_string(),
2785                author: "tester".to_string(),
2786                url: "https://example.com/1".to_string(),
2787                labels: vec![],
2788                parent_id: None,
2789            })
2790            .await
2791            .unwrap();
2792        (store, id)
2793    }
2794
2795    // Each test selects all previously-required columns so that exactly the
2796    // column under test is absent and triggers the Err path.
2797    macro_rules! missing_column_test {
2798        ($name:ident, $col:literal, $select:literal) => {
2799            #[tokio::test]
2800            async fn $name() {
2801                let (store, id) = store_with_task().await;
2802                let sql = format!("{} WHERE id = {id}", $select);
2803                let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2804                    .fetch_one(store.pool())
2805                    .await
2806                    .unwrap();
2807                let err = TaskStore::row_to_task(&row).unwrap_err();
2808                assert!(
2809                    err.to_string().contains($col),
2810                    "expected '{}' in error message, got: {err}",
2811                    $col
2812                );
2813            }
2814        };
2815    }
2816
2817    missing_column_test!(
2818        row_to_task_fails_on_missing_repo,
2819        "repo",
2820        "SELECT id, status FROM tasks"
2821    );
2822    missing_column_test!(
2823        row_to_task_fails_on_missing_origin,
2824        "origin",
2825        "SELECT id, repo, status FROM tasks"
2826    );
2827    missing_column_test!(
2828        row_to_task_fails_on_missing_title,
2829        "title",
2830        "SELECT id, repo, origin, status FROM tasks"
2831    );
2832    missing_column_test!(
2833        row_to_task_fails_on_missing_body,
2834        "body",
2835        "SELECT id, repo, origin, title, status FROM tasks"
2836    );
2837    missing_column_test!(
2838        row_to_task_fails_on_missing_source,
2839        "source",
2840        "SELECT id, repo, origin, title, body, status FROM tasks"
2841    );
2842    missing_column_test!(
2843        row_to_task_fails_on_missing_source_id,
2844        "source_id",
2845        "SELECT id, repo, origin, title, body, source, status FROM tasks"
2846    );
2847    missing_column_test!(
2848        row_to_task_fails_on_missing_author,
2849        "author",
2850        "SELECT id, repo, origin, title, body, source, source_id, status FROM tasks"
2851    );
2852    missing_column_test!(
2853        row_to_task_fails_on_missing_url,
2854        "url",
2855        "SELECT id, repo, origin, title, body, source, source_id, author, status FROM tasks"
2856    );
2857
2858    // Decode-error test: inject a BLOB with invalid UTF-8 bytes for the `repo`
2859    // column. SQLite's type coercion converts NULL and integers to strings, so
2860    // they don't produce a decode error. A raw BLOB that is not valid UTF-8
2861    // (X'DEADBEEF') does cause `try_get::<String>` to fail with a ColumnDecode
2862    // error, exercising the decode-error-propagation path that is distinct from
2863    // the missing-column (ColumnNotFound) path tested above.
2864    #[tokio::test]
2865    async fn row_to_task_fails_on_decode_error_invalid_utf8_repo() {
2866        let (store, id) = store_with_task().await;
2867        // X'DEADBEEF' is a 4-byte BLOB; bytes 0xDE 0xAD 0xBE 0xEF are not
2868        // valid UTF-8, so try_get::<String>("repo") will fail with a decode error.
2869        let sql = format!(
2870            "SELECT id, X'DEADBEEF' as repo, origin, title, body, status, \
2871             source, source_id, author, url FROM tasks WHERE id = {id}"
2872        );
2873        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2874            .fetch_one(store.pool())
2875            .await
2876            .unwrap();
2877        let err = TaskStore::row_to_task(&row).unwrap_err();
2878        assert!(
2879            err.to_string().contains("repo"),
2880            "expected 'repo' in error message, got: {err}"
2881        );
2882    }
2883
2884    // ── distinct_repos decode-error tests ─────────────────────────────────
2885
2886    #[tokio::test]
2887    async fn distinct_repos_fails_on_decode_error() {
2888        let store = store::TaskStore::open_memory().await.unwrap();
2889        let id = store
2890            .create(&NewTask {
2891                external_id: None,
2892                repo: "owner/repo".to_string(),
2893                origin: "internal".to_string(),
2894                title: "Test task".to_string(),
2895                body: "Test body".to_string(),
2896                source: "cron".to_string(),
2897                source_id: "test-1".to_string(),
2898                author: "tester".to_string(),
2899                url: "https://example.com/1".to_string(),
2900                labels: vec![],
2901                parent_id: None,
2902            })
2903            .await
2904            .unwrap();
2905
2906        // Inject an invalid UTF-8 BLOB as repo — try_get::<String> will fail.
2907        let sql = format!("UPDATE tasks SET repo = X'DEADBEEF' WHERE id = {id}");
2908        sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
2909            .execute(store.pool())
2910            .await
2911            .unwrap();
2912
2913        let err = store.distinct_repos().await.unwrap_err();
2914        assert!(
2915            err.to_string().contains("distinct_repos") && err.to_string().contains("repo"),
2916            "expected 'distinct_repos' and 'repo' in error message, got: {err}"
2917        );
2918    }
2919
2920    // ── row_to_run decode-error tests ────────────────────────────────────────
2921
2922    async fn store_with_run() -> (store::TaskStore, i64, i64) {
2923        let store = store::TaskStore::open_memory().await.unwrap();
2924        let task_id = store
2925            .create(&NewTask {
2926                external_id: None,
2927                repo: "owner/repo".to_string(),
2928                origin: "internal".to_string(),
2929                title: "Test task".to_string(),
2930                body: "Test body".to_string(),
2931                source: "cron".to_string(),
2932                source_id: "test-1".to_string(),
2933                author: "tester".to_string(),
2934                url: "https://example.com/1".to_string(),
2935                labels: vec![],
2936                parent_id: None,
2937            })
2938            .await
2939            .unwrap();
2940        let run_id = store
2941            .start_run(&crate::store::StartRun {
2942                task_id,
2943                attempt: 1,
2944                run_type: "agent",
2945                agent: "claude",
2946                model: "sonnet",
2947                command: "claude -p ...",
2948                prompt: "system prompt",
2949            })
2950            .await
2951            .unwrap();
2952        (store, task_id, run_id)
2953    }
2954
2955    #[tokio::test]
2956    async fn start_run_upsert_clears_stale_completion_fields() {
2957        let (store, task_id, run_id) = store_with_run().await;
2958        store
2959            .complete_run(&CompleteRun {
2960                run_id,
2961                exit_code: Some(0),
2962                stdout: "",
2963                stderr: "",
2964                parsed: "",
2965                outcome: "success",
2966                error: "boom",
2967                tokens: RunTokenUsage::default(),
2968            })
2969            .await
2970            .unwrap();
2971
2972        // Re-dispatch the same (task_id, attempt, run_type) key.
2973        let same_run_id = store
2974            .start_run(&crate::store::StartRun {
2975                task_id,
2976                attempt: 1,
2977                run_type: "agent",
2978                agent: "claude",
2979                model: "sonnet",
2980                command: "claude -p ...",
2981                prompt: "system prompt",
2982            })
2983            .await
2984            .unwrap();
2985        assert_eq!(run_id, same_run_id, "upsert must reuse the same row");
2986
2987        let row = sqlx::query(
2988            "SELECT outcome, completed_at, exit_code, error FROM task_runs WHERE id = ?",
2989        )
2990        .bind(run_id)
2991        .fetch_one(store.pool())
2992        .await
2993        .unwrap();
2994        let outcome: Option<String> = row.try_get("outcome").unwrap();
2995        let completed_at: Option<String> = row.try_get("completed_at").unwrap();
2996        let exit_code: Option<i64> = row.try_get("exit_code").unwrap();
2997        let error: Option<String> = row.try_get("error").unwrap();
2998        assert_eq!(outcome, None);
2999        assert_eq!(
3000            completed_at, None,
3001            "stale completion timestamp must be cleared"
3002        );
3003        assert_eq!(exit_code, None);
3004        assert_eq!(error, None);
3005    }
3006
3007    // NOTE: Unlike the task table (where all required columns are TEXT and NULL
3008    // propagates a ColumnNotFound error), the task_runs table has INTEGER and REAL
3009    // columns (id, task_id, attempt, started_at). SQLite coerces NULL/INTEGER to
3010    // String when try_get::<String> is called, so we cannot use the missing-column
3011    // pattern here. Instead, we use BLOB injection to trigger actual decode errors
3012    // that propagate through map_err.
3013    macro_rules! run_blob_decode_test {
3014        ($name:ident, $col:literal, $select:literal) => {
3015            #[tokio::test]
3016            async fn $name() {
3017                let (store, _task_id, run_id) = store_with_run().await;
3018                let sql = format!($select, run_id = run_id);
3019                let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3020                    .fetch_one(store.pool())
3021                    .await
3022                    .unwrap();
3023                let err = TaskStore::row_to_run(&row).unwrap_err();
3024                assert!(
3025                    err.to_string().contains($col),
3026                    "expected '{0}' in error message, got: {err}",
3027                    $col
3028                );
3029            }
3030        };
3031    }
3032
3033    run_blob_decode_test!(
3034        row_to_run_fails_on_decode_error_invalid_utf8_id,
3035        "id",
3036        "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}"
3037    );
3038    run_blob_decode_test!(
3039        row_to_run_fails_on_decode_error_invalid_utf8_task_id,
3040        "task_id",
3041        "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}"
3042    );
3043    run_blob_decode_test!(
3044        row_to_run_fails_on_decode_error_invalid_utf8_started_at,
3045        "started_at",
3046        "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}"
3047    );
3048
3049    // ── row_to_activity decode-error tests ───────────────────────────────────
3050
3051    async fn store_with_activity() -> (store::TaskStore, i64, i64) {
3052        let store = store::TaskStore::open_memory().await.unwrap();
3053        let task_id = store
3054            .create(&NewTask {
3055                external_id: None,
3056                repo: "owner/repo".to_string(),
3057                origin: "internal".to_string(),
3058                title: "Test task".to_string(),
3059                body: "Test body".to_string(),
3060                source: "cron".to_string(),
3061                source_id: "test-1".to_string(),
3062                author: "tester".to_string(),
3063                url: "https://example.com/1".to_string(),
3064                labels: vec![],
3065                parent_id: None,
3066            })
3067            .await
3068            .unwrap();
3069        store
3070            .append_activity(task_id, "created", None, Some("new"), None, None, None)
3071            .await
3072            .unwrap();
3073        (store, task_id, 1)
3074    }
3075
3076    // Each test drops exactly one column from the full TASK_ACTIVITY_COLS so that
3077    // the first missing-column try_get (id) propagates the error.
3078    #[tokio::test]
3079    async fn row_to_activity_fails_on_decode_error_invalid_utf8_id() {
3080        let (store, _task_id, activity_id) = store_with_activity().await;
3081        let sql = format!(
3082            "SELECT X'DEADBEEF' as id, task_id, timestamp, event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3083        );
3084        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3085            .fetch_one(store.pool())
3086            .await
3087            .unwrap();
3088        let err = TaskStore::row_to_activity(&row).unwrap_err();
3089        assert!(
3090            err.to_string().contains("id"),
3091            "expected 'id' in error message, got: {err}"
3092        );
3093    }
3094
3095    #[tokio::test]
3096    async fn row_to_activity_fails_on_decode_error_invalid_utf8_task_id() {
3097        let (store, _task_id, activity_id) = store_with_activity().await;
3098        let sql = format!(
3099            "SELECT id, X'DEADBEEF' as task_id, timestamp, event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3100        );
3101        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3102            .fetch_one(store.pool())
3103            .await
3104            .unwrap();
3105        let err = TaskStore::row_to_activity(&row).unwrap_err();
3106        assert!(
3107            err.to_string().contains("task_id"),
3108            "expected 'task_id' in error message, got: {err}"
3109        );
3110    }
3111
3112    #[tokio::test]
3113    async fn row_to_activity_fails_on_decode_error_invalid_utf8_timestamp() {
3114        let (store, _task_id, activity_id) = store_with_activity().await;
3115        let sql = format!(
3116            "SELECT id, task_id, X'DEADBEEF' as timestamp, event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3117        );
3118        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3119            .fetch_one(store.pool())
3120            .await
3121            .unwrap();
3122        let err = TaskStore::row_to_activity(&row).unwrap_err();
3123        assert!(
3124            err.to_string().contains("timestamp"),
3125            "expected 'timestamp' in error message, got: {err}"
3126        );
3127    }
3128
3129    #[tokio::test]
3130    async fn row_to_activity_fails_on_decode_error_invalid_utf8_event_type() {
3131        let (store, _task_id, activity_id) = store_with_activity().await;
3132        let sql = format!(
3133            "SELECT id, task_id, timestamp, X'DEADBEEF' as event_type, from_status, to_status, agent, model, details FROM task_activity WHERE id = {activity_id}"
3134        );
3135        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3136            .fetch_one(store.pool())
3137            .await
3138            .unwrap();
3139        let err = TaskStore::row_to_activity(&row).unwrap_err();
3140        assert!(
3141            err.to_string().contains("event_type"),
3142            "expected 'event_type' in error message, got: {err}"
3143        );
3144    }
3145
3146    #[tokio::test]
3147    async fn row_to_activity_fails_on_corrupt_details_json() {
3148        let (store, task_id, _activity_id) = store_with_activity().await;
3149        // Insert a row with invalid JSON in details
3150        sqlx::query("INSERT INTO task_activity (task_id, event_type, details) VALUES (?, ?, ?)")
3151            .bind(task_id)
3152            .bind("test")
3153            .bind("not valid json {{{")
3154            .execute(store.pool())
3155            .await
3156            .unwrap();
3157        let row = sqlx::query(sqlx::AssertSqlSafe(format!(
3158            "SELECT {TASK_ACTIVITY_COLS} FROM task_activity WHERE task_id = {task_id} ORDER BY id DESC LIMIT 1"
3159        )))
3160        .fetch_one(store.pool())
3161        .await
3162        .unwrap();
3163        let err = TaskStore::row_to_activity(&row).unwrap_err();
3164        assert!(
3165            err.to_string().contains("details")
3166                || err.to_string().contains("json")
3167                || err.to_string().contains("expected"),
3168            "expected 'details', 'json', or 'expected' in error message, got: {err}"
3169        );
3170    }
3171
3172    // ── row_to_run: remaining fields with map_err ──────────────────────────────
3173
3174    run_blob_decode_test!(
3175        row_to_run_fails_on_decode_error_invalid_utf8_model,
3176        "model",
3177        "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}"
3178    );
3179    run_blob_decode_test!(
3180        row_to_run_fails_on_decode_error_invalid_utf8_command,
3181        "command",
3182        "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}"
3183    );
3184    run_blob_decode_test!(
3185        row_to_run_fails_on_decode_error_invalid_utf8_prompt,
3186        "prompt",
3187        "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}"
3188    );
3189    run_blob_decode_test!(
3190        row_to_run_fails_on_decode_error_invalid_utf8_env_vars,
3191        "env_vars",
3192        "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}"
3193    );
3194    run_blob_decode_test!(
3195        row_to_run_fails_on_decode_error_invalid_utf8_stdout,
3196        "stdout",
3197        "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}"
3198    );
3199    run_blob_decode_test!(
3200        row_to_run_fails_on_decode_error_invalid_utf8_stderr,
3201        "stderr",
3202        "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}"
3203    );
3204    run_blob_decode_test!(
3205        row_to_run_fails_on_decode_error_invalid_utf8_parsed_response,
3206        "parsed_response",
3207        "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}"
3208    );
3209    run_blob_decode_test!(
3210        row_to_run_fails_on_decode_error_invalid_utf8_outcome,
3211        "outcome",
3212        "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}"
3213    );
3214    run_blob_decode_test!(
3215        row_to_run_fails_on_decode_error_invalid_utf8_error,
3216        "error",
3217        "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}"
3218    );
3219    run_blob_decode_test!(
3220        row_to_run_fails_on_decode_error_invalid_utf8_input_tokens,
3221        "input_tokens",
3222        "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}"
3223    );
3224    run_blob_decode_test!(
3225        row_to_run_fails_on_decode_error_invalid_utf8_output_tokens,
3226        "output_tokens",
3227        "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}"
3228    );
3229    run_blob_decode_test!(
3230        row_to_run_fails_on_decode_error_invalid_utf8_total_cost_usd,
3231        "total_cost_usd",
3232        "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}"
3233    );
3234    run_blob_decode_test!(
3235        row_to_run_fails_on_decode_error_invalid_utf8_duration_secs,
3236        "duration_secs",
3237        "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}"
3238    );
3239    run_blob_decode_test!(
3240        row_to_run_fails_on_decode_error_invalid_utf8_exit_code,
3241        "exit_code",
3242        "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}"
3243    );
3244
3245    // ── row_to_activity: nullable columns with map_err ────────────────────────
3246
3247    // from_status, to_status, agent, model are nullable (Option<String>) but their
3248    // decode errors should still propagate via map_err, not be masked as None.
3249    // Each test injects BLOB into exactly one column and NULL into the rest so
3250    // the error always references the intended column (the first failing map_err).
3251    #[tokio::test]
3252    async fn row_to_activity_fails_on_decode_error_invalid_utf8_from_status() {
3253        let (store, _task_id, activity_id) = store_with_activity().await;
3254        let sql = format!(
3255            "SELECT id, task_id, timestamp, event_type, \
3256             X'DeadbEEF' as from_status, NULL as to_status, \
3257             NULL as agent, NULL as model, details \
3258             FROM task_activity WHERE id = {activity_id}"
3259        );
3260        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3261            .fetch_one(store.pool())
3262            .await
3263            .unwrap();
3264        let err = TaskStore::row_to_activity(&row).unwrap_err();
3265        assert!(
3266            err.to_string().contains("from_status"),
3267            "expected 'from_status' in error message, got: {err}"
3268        );
3269    }
3270
3271    #[tokio::test]
3272    async fn row_to_activity_fails_on_decode_error_invalid_utf8_to_status() {
3273        let (store, _task_id, activity_id) = store_with_activity().await;
3274        let sql = format!(
3275            "SELECT id, task_id, timestamp, event_type, \
3276             NULL as from_status, X'DeadbEEF' as to_status, \
3277             NULL as agent, NULL as model, details \
3278             FROM task_activity WHERE id = {activity_id}"
3279        );
3280        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3281            .fetch_one(store.pool())
3282            .await
3283            .unwrap();
3284        let err = TaskStore::row_to_activity(&row).unwrap_err();
3285        assert!(
3286            err.to_string().contains("to_status"),
3287            "expected 'to_status' in error message, got: {err}"
3288        );
3289    }
3290
3291    #[tokio::test]
3292    async fn row_to_activity_fails_on_decode_error_invalid_utf8_agent() {
3293        let (store, _task_id, activity_id) = store_with_activity().await;
3294        let sql = format!(
3295            "SELECT id, task_id, timestamp, event_type, \
3296             NULL as from_status, NULL as to_status, \
3297             X'DeadbEEF' as agent, NULL as model, details \
3298             FROM task_activity WHERE id = {activity_id}"
3299        );
3300        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3301            .fetch_one(store.pool())
3302            .await
3303            .unwrap();
3304        let err = TaskStore::row_to_activity(&row).unwrap_err();
3305        assert!(
3306            err.to_string().contains("agent"),
3307            "expected 'agent' in error message, got: {err}"
3308        );
3309    }
3310
3311    #[tokio::test]
3312    async fn row_to_activity_fails_on_decode_error_invalid_utf8_model() {
3313        let (store, _task_id, activity_id) = store_with_activity().await;
3314        let sql = format!(
3315            "SELECT id, task_id, timestamp, event_type, \
3316             NULL as from_status, NULL as to_status, \
3317             NULL as agent, X'DeadbEEF' as model, details \
3318             FROM task_activity WHERE id = {activity_id}"
3319        );
3320        let row = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()))
3321            .fetch_one(store.pool())
3322            .await
3323            .unwrap();
3324        let err = TaskStore::row_to_activity(&row).unwrap_err();
3325        assert!(
3326            err.to_string().contains("model"),
3327            "expected 'model' in error message, got: {err}"
3328        );
3329    }
3330
3331    // ── get_statuses_by_external_ids: decode-failure rows are skipped ───────────
3332
3333    #[tokio::test]
3334    async fn get_statuses_by_external_ids_skips_rows_with_decode_error() {
3335        // Insert a task row with a BLOB value in the external_id column (valid
3336        // SQLite but not valid UTF-8 TEXT), so row.try_get("external_id") fails
3337        // with a decode error. The method should skip this row (not panic) and
3338        // continue processing the remaining rows.
3339        let store = store::TaskStore::open_memory().await.unwrap();
3340        sqlx::query("INSERT INTO tasks (repo, external_id, status, title, origin) VALUES ('owner/repo', X'DeadbEEF', 'new', '', 'internal')")
3341            .execute(store.pool())
3342            .await
3343            .unwrap();
3344        // A second row with a valid external_id should be returned.
3345        sqlx::query("INSERT INTO tasks (repo, external_id, status, title, origin) VALUES ('owner/repo', 'valid-id', 'done', '', 'internal')")
3346            .execute(store.pool())
3347            .await
3348            .unwrap();
3349
3350        let result = store
3351            .get_statuses_by_external_ids("owner/repo", &["valid-id"])
3352            .await
3353            .unwrap();
3354        assert_eq!(result.len(), 1, "expected exactly one result for valid-id");
3355        assert_eq!(result.get("valid-id"), Some(&TaskStatus::Done));
3356    }
3357}