-- ============================================================================
-- Krow — agent run trajectories
--
-- Phase 1. One table, and deliberately one.
--
-- WHAT THIS IS FOR
--
-- Every agent run records what happened: each message, each tool call, each
-- tool result, and the budget as it stood before each dispatch. §6 of the
-- platform contract is explicit that this is not optional telemetry — it is
-- what makes debugging and evals possible at all. A run whose trajectory was
-- dropped is a run nobody can explain afterwards, and an eval suite with no
-- trajectory to assert against cannot check `tools_called` or `must_not_leak`.
--
-- WHY ONE TABLE AND NOT TWO
--
-- The obvious alternative is `agent_runs` plus `agent_run_entries`, one row per
-- entry. It is rejected because entries are never queried independently of
-- their run: nothing asks "show me every tool call across all runs" without
-- also wanting the run it belonged to. A child table would buy relational
-- tidiness and cost a join on the one access pattern that exists — fetch one
-- run whole — plus an insert per entry instead of one insert per run.
--
-- I3 is what makes this safe. A run is bounded by a step cap, a tool-call cap,
-- a token budget and a wall-clock deadline, so `entries` cannot grow without
-- limit the way an unbounded conversation log could. The jsonb column is
-- bounded by construction rather than by hope.
--
-- WHAT IS DELIBERATELY ABSENT
--
--   agent_run_entries    see above.
--   conversations        a run is one turn. Threading runs into a conversation
--                        is a surface-layer concern and no surface asks for it
--                        yet; adding the column later is trivial, and inventing
--                        the semantics now is not.
--   confirmations        the ConfirmationPending termination exists in the
--                        vocabulary, but no write tool does, so there is
--                        nothing yet to store a pending confirmation FOR.
--                        Phase 2.
--   cost in currency     token counts are the durable fact; a price is a
--                        contract term that changes underneath stored rows.
--                        Derived at read time, never written here.
--
-- RETENTION
--
-- No policy is imposed. Trajectories carry message text, so they are subject to
-- whatever retention the deployment owes its tenants — that is a decision for
-- an operator, not a default baked into a migration. The index on started_at
-- exists so a deletion sweep can be written efficiently when that decision is
-- made.
-- ============================================================================

SET search_path = public;

CREATE TABLE agent_runs (
  -- The run id the executor generated and returned to the caller. Text, not
  -- uuid: it is opaque, it appears in support conversations, and its format is
  -- the runtime's business rather than the schema's.
  run_id         text        PRIMARY KEY,

  -- Delegation. A subagent's run links to its parent, and §6 requires the two
  -- to be separate trajectories rather than one merged log. SET NULL rather
  -- than CASCADE: deleting a parent run must not silently destroy the record
  -- of what its subagents did.
  parent_run_id  text        REFERENCES agent_runs (run_id) ON DELETE SET NULL,

  -- Tenancy, on the same terms as every other table here: NOT NULL, so the
  -- organization predicate applies to every read without a call site having to
  -- remember it. I5.
  org_id         uuid        NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,

  -- Who the run executed on behalf of. SET NULL so a departed user's runs stay
  -- auditable — the org still needs to answer for what its agents did.
  user_id        uuid        REFERENCES users (id) ON DELETE SET NULL,

  -- The agent as it was AT RUN TIME, by author-facing id and version, not by a
  -- foreign key. A trajectory must stay readable after its definition is
  -- edited, archived or deleted, and a FK would either block that deletion or
  -- cascade away the evidence.
  agent_id       text        NOT NULL,
  agent_version  integer     NOT NULL DEFAULT 1,

  -- What was asked for, and what actually answered. Both, because the mapping
  -- is a deployment decision that changes: reading a trajectory a year later
  -- must not require knowing what "balanced" was routed to that week.
  tier           text        NOT NULL,
  model          text        NOT NULL DEFAULT '',

  started_at     timestamptz NOT NULL,
  ended_at       timestamptz NOT NULL,

  -- Exactly one of six. A CHECK rather than an enum type, matching 000005's
  -- treatment of the status vocabularies — a new termination reason should be
  -- a migration, but not one that requires ALTER TYPE.
  termination    text        NOT NULL,

  -- The record itself: messages, tool calls, tool results and budget
  -- snapshots, in sequence.
  entries        jsonb       NOT NULL DEFAULT '[]'::jsonb,

  -- Token accounting, as columns rather than inside the jsonb, because these
  -- are the fields anything aggregates over — per-tenant spend, per-agent cost,
  -- budget tuning — and none of that should require unpacking a document.
  input_tokens   bigint      NOT NULL DEFAULT 0,
  output_tokens  bigint      NOT NULL DEFAULT 0,
  cached_tokens  bigint      NOT NULL DEFAULT 0,
  total_tokens   bigint      NOT NULL DEFAULT 0,
  model_calls    integer     NOT NULL DEFAULT 0,

  created_date   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT agent_runs_termination_check CHECK (termination IN (
    'Completed', 'BudgetExceeded', 'Deadline',
    'ConfirmationPending', 'ToolFailure', 'Refused'
  )),
  CONSTRAINT agent_runs_entries_is_array CHECK (jsonb_typeof(entries) = 'array'),
  CONSTRAINT agent_runs_ended_after_started CHECK (ended_at >= started_at),
  CONSTRAINT agent_runs_tokens_non_negative CHECK (
    input_tokens >= 0 AND output_tokens >= 0 AND cached_tokens >= 0 AND total_tokens >= 0
  ),
  -- A run cannot be its own parent. Deeper cycles are prevented by the depth
  -- cap in the runtime; this catches the one case a single row can express.
  CONSTRAINT agent_runs_no_self_parent CHECK (parent_run_id IS DISTINCT FROM run_id)
);

-- The list view: one tenant's runs, most recent first. Covers the retention
-- sweep too.
CREATE INDEX agent_runs_org_started_idx
  ON agent_runs (org_id, started_at DESC);

-- "Show me this agent's recent runs" — the Insights panel's actual question,
-- and the one an eval report groups by.
CREATE INDEX agent_runs_org_agent_started_idx
  ON agent_runs (org_id, agent_id, started_at DESC);

-- "Which runs failed, and how" — partial, because Completed is the common case
-- and indexing it would double the write cost to serve a query nobody makes.
CREATE INDEX agent_runs_failures_idx
  ON agent_runs (org_id, termination, started_at DESC)
  WHERE termination <> 'Completed';

-- A parent's delegated runs.
CREATE INDEX agent_runs_parent_idx
  ON agent_runs (parent_run_id)
  WHERE parent_run_id IS NOT NULL;

COMMENT ON TABLE agent_runs IS
  'One row per agent run: the full trajectory, its termination reason and its token cost. '
  'Written once at the end of a run. See §6 — this is what makes debugging and evals possible.';