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