agentic-coding-workflow/internal/acp/queries/sessions.sql

-- name: InsertSession :one
INSERT INTO acp_sessions (
  id, workspace_id, project_id, agent_kind_id, user_id,
  issue_id, requirement_id, branch, cwd_path, is_main_worktree, status,
  orchestrator_step_id,
  budget_max_cost_usd, budget_max_tokens, budget_max_wall_clock_seconds
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
RETURNING id, workspace_id, project_id, agent_kind_id, user_id,
          issue_id, requirement_id, agent_session_id,
          branch, cwd_path, is_main_worktree, status,
          pid, exit_code, last_error, started_at, ended_at, last_stop_reason,
          orchestrator_step_id,
          prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd,
          last_activity_at, budget_max_cost_usd, budget_max_tokens,
          budget_max_wall_clock_seconds, terminated_reason, sandbox_mode,
          cost_usd, tokens_total;

-- name: GetSessionByID :one
SELECT id, workspace_id, project_id, agent_kind_id, user_id,
       issue_id, requirement_id, agent_session_id,
       branch, cwd_path, is_main_worktree, status,
       pid, exit_code, last_error, started_at, ended_at, last_stop_reason,
       orchestrator_step_id,
       prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd,
       last_activity_at, budget_max_cost_usd, budget_max_tokens,
       budget_max_wall_clock_seconds, terminated_reason, sandbox_mode,
       cost_usd, tokens_total
FROM acp_sessions
WHERE id = $1;

-- name: GetSessionByStepID :one
SELECT id, workspace_id, project_id, agent_kind_id, user_id,
       issue_id, requirement_id, agent_session_id,
       branch, cwd_path, is_main_worktree, status,
       pid, exit_code, last_error, started_at, ended_at, last_stop_reason,
       orchestrator_step_id,
       prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd,
       last_activity_at, budget_max_cost_usd, budget_max_tokens,
       budget_max_wall_clock_seconds, terminated_reason, sandbox_mode,
       cost_usd, tokens_total
FROM acp_sessions
WHERE orchestrator_step_id = $1
ORDER BY started_at DESC
LIMIT 1;

-- name: GetCrashedSessionForResume :one
-- 返回某 step 最近一次处于可恢复终态（crashed/exited）的 session。
SELECT id, workspace_id, project_id, agent_kind_id, user_id,
       issue_id, requirement_id, agent_session_id,
       branch, cwd_path, is_main_worktree, status,
       pid, exit_code, last_error, started_at, ended_at, last_stop_reason,
       orchestrator_step_id,
       prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd,
       last_activity_at, budget_max_cost_usd, budget_max_tokens,
       budget_max_wall_clock_seconds, terminated_reason, sandbox_mode,
       cost_usd, tokens_total
FROM acp_sessions
WHERE orchestrator_step_id = $1
  AND status IN ('crashed','exited')
ORDER BY started_at DESC
LIMIT 1;

-- name: ListSessionsByUser :many
SELECT id, workspace_id, project_id, agent_kind_id, user_id,
       issue_id, requirement_id, agent_session_id,
       branch, cwd_path, is_main_worktree, status,
       pid, exit_code, last_error, started_at, ended_at, last_stop_reason,
       orchestrator_step_id,
       prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd,
       last_activity_at, budget_max_cost_usd, budget_max_tokens,
       budget_max_wall_clock_seconds, terminated_reason, sandbox_mode,
       cost_usd, tokens_total
FROM acp_sessions
WHERE user_id = $1
  AND ($2::uuid IS NULL OR requirement_id = $2)
ORDER BY started_at DESC;

-- name: ListAllSessions :many
SELECT id, workspace_id, project_id, agent_kind_id, user_id,
       issue_id, requirement_id, agent_session_id,
       branch, cwd_path, is_main_worktree, status,
       pid, exit_code, last_error, started_at, ended_at, last_stop_reason,
       orchestrator_step_id,
       prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd,
       last_activity_at, budget_max_cost_usd, budget_max_tokens,
       budget_max_wall_clock_seconds, terminated_reason, sandbox_mode,
       cost_usd, tokens_total
FROM acp_sessions
WHERE ($1::uuid IS NULL OR requirement_id = $1)
ORDER BY started_at DESC;

-- name: UpdateSessionRunning :exec
UPDATE acp_sessions
SET status = 'running', agent_session_id = $2, pid = $3
WHERE id = $1;

-- name: UpdateSessionLastStopReason :exec
UPDATE acp_sessions
SET last_stop_reason = $2
WHERE id = $1;

-- name: UpdateSessionSandboxMode :exec
-- 记录本 session 运行所用的沙箱模式（审计/取证）。
UPDATE acp_sessions
SET sandbox_mode = $2
WHERE id = $1;

-- name: UpdateSessionFinished :exec
UPDATE acp_sessions
SET status = $2, exit_code = $3, last_error = $4, ended_at = now()
WHERE id = $1;

-- name: MarkSessionFailedIfActive :execrows
UPDATE acp_sessions
SET status = 'crashed', last_error = $2, ended_at = now()
WHERE id = $1 AND status IN ('starting', 'running');

-- name: ResetSessionForResume :exec
-- 把崩溃/退出的 session 复用为新一轮：回到 starting、清空上次运行时字段。
-- 编排器 resume 在重新 Spawn 前调用，使同一行 session 在同 cwd/worktree 上复用。
UPDATE acp_sessions
SET status = 'starting',
    agent_session_id = NULL,
    pid = NULL,
    exit_code = NULL,
    last_error = NULL,
    ended_at = NULL,
    terminated_reason = NULL,
    last_activity_at = now()
WHERE id = $1;

-- name: CountActiveSessions :one
SELECT COUNT(*) FROM acp_sessions WHERE status IN ('starting', 'running');

-- name: CountActiveSessionsByUser :one
SELECT COUNT(*) FROM acp_sessions
WHERE user_id = $1 AND status IN ('starting', 'running');

-- name: ResetStuckSessionsOnRestart :many
UPDATE acp_sessions
SET status = 'crashed',
    last_error = 'process_aborted_on_restart',
    ended_at = now()
WHERE status IN ('starting', 'running')
RETURNING id, workspace_id, user_id, branch, agent_kind_id, is_main_worktree;

-- name: ReleaseMainWorktree :execrows
UPDATE workspaces SET active_main_session_id = NULL
WHERE id = $1 AND active_main_session_id = $2;

-- name: AcquireMainWorktree :execrows
UPDATE workspaces SET active_main_session_id = $1
WHERE id = $2 AND active_main_session_id IS NULL;

-- name: AddSessionUsageTotals :one
-- 单 round-trip 累加 session 运行时 token/cost 总量并刷新 last_activity_at，
-- 返回累加后的权威总量供预算判定。
-- 同时把 dashboard rollup 列（cost_usd / tokens_total）与运行时累加器对齐，
-- 使 run-history dashboard 在会话进行中及退出后都能读到 agent 上报的成本。
UPDATE acp_sessions
SET prompt_tokens     = prompt_tokens + $2,
    completion_tokens = completion_tokens + $3,
    thinking_tokens   = thinking_tokens + $4,
    total_cost_usd    = total_cost_usd + $5,
    cost_usd          = cost_usd + $5,
    tokens_total      = tokens_total + $2 + $3 + $4,
    last_activity_at  = now()
WHERE id = $1
RETURNING prompt_tokens, completion_tokens, thinking_tokens, total_cost_usd;

-- name: SumProjectCostUSD :one
-- 某 project 自 since 起的 ACP 累计花费（per-project 软预算）。
SELECT COALESCE(SUM(cost_usd), 0)::numeric AS cost_usd
FROM acp_session_usage
WHERE project_id = $1 AND created_at >= $2;

-- name: MarkSessionTerminatedReason :exec
-- 写入终止原因；已有非空 terminated_reason 时不覆盖（reaper / budget 互斥保护）。
UPDATE acp_sessions
SET terminated_reason = $2
WHERE id = $1 AND terminated_reason IS NULL;

-- name: ListSessionsForReaper :many
-- 返回应被回收的活跃 session：
--   超过自身 wall-clock 上限（budget_max_wall_clock_seconds 非空且 started_at 过早），或
--   超过全局 wall-clock 上限（$1::timestamptz = now - 全局上限），或
--   空闲超时（last_activity_at < $2::timestamptz）。
SELECT id, user_id, started_at, last_activity_at, budget_max_wall_clock_seconds
FROM acp_sessions
WHERE status IN ('starting','running')
  AND (
    (budget_max_wall_clock_seconds IS NOT NULL
       AND started_at < now() - make_interval(secs => budget_max_wall_clock_seconds))
    OR ($1::timestamptz IS NOT NULL AND started_at < $1::timestamptz)
    OR last_activity_at < $2::timestamptz
  );

-- name: SummarizeAcpUsageByUser :many
SELECT user_id,
       SUM(prompt_tokens)::bigint     AS prompt_tokens,
       SUM(completion_tokens)::bigint AS completion_tokens,
       SUM(thinking_tokens)::bigint   AS thinking_tokens,
       SUM(cost_usd)::numeric         AS cost_usd
FROM acp_session_usage
WHERE created_at >= $1 AND created_at < $2
GROUP BY user_id
ORDER BY cost_usd DESC;

-- name: SummarizeAcpUsageByModel :many
SELECT model_id,
       SUM(prompt_tokens)::bigint     AS prompt_tokens,
       SUM(completion_tokens)::bigint AS completion_tokens,
       SUM(thinking_tokens)::bigint   AS thinking_tokens,
       SUM(cost_usd)::numeric         AS cost_usd
FROM acp_session_usage
WHERE created_at >= $1 AND created_at < $2
GROUP BY model_id
ORDER BY cost_usd DESC;

-- name: SummarizeAcpUsageByDay :many
SELECT date_trunc('day', created_at)::timestamptz AS day,
       SUM(prompt_tokens)::bigint     AS prompt_tokens,
       SUM(completion_tokens)::bigint AS completion_tokens,
       SUM(thinking_tokens)::bigint   AS thinking_tokens,
       SUM(cost_usd)::numeric         AS cost_usd
FROM acp_session_usage
WHERE created_at >= $1 AND created_at < $2
GROUP BY day
ORDER BY day;

-- name: ListSessionUsageBySession :many
SELECT id, session_id, user_id, project_id, agent_kind_id, model_id,
       prompt_tokens, completion_tokens, thinking_tokens, cost_usd,
       source_event_id, created_at
FROM acp_session_usage
WHERE session_id = $1
ORDER BY created_at DESC
LIMIT $2;