-- ============================================================================
-- Krow — pending write confirmations
--
-- Phase 2. The other half of I4.
--
-- 000006 left a note saying confirmations were deliberately absent because
-- "the ConfirmationPending termination exists in the vocabulary, but no write
-- tool does". A write tool does now — assign_worker — so this is that table.
--
-- WHAT A ROW IS
--
-- A question a person has been asked and has not yet answered: "shall this
-- agent assign Maya Chen to Friday's bar shift?". It exists between the moment
-- an agent proposed a write and the moment somebody approved or ignored it.
--
-- WHY A TOKEN IS NOT A BOOLEAN
--
-- The naive implementation of "writes need confirmation" is a yes/no flag. It
-- has a hole: a person approves one write, and the flag then authorises a
-- different one. So the columns below are a FINGERPRINT of the exact call that
-- was described — tool, arguments, caller, tenant, run — and the runtime
-- validates an incoming token against all of them. Approving "assign Maya to
-- Friday" cannot become "assign Dan to Saturday", because that is a different
-- inputs_hash and the token simply does not match it.
--
-- The arguments themselves are hashed rather than stored. The rendered
-- description in `payload` is what a person read and is worth keeping; the raw
-- arguments are not independently useful, and hashing them means this table
-- never becomes a second copy of whatever the agent was about to write.
--
-- SINGLE USE
--
-- consumed_at is what makes one approval buy one write. The runtime claims a
-- token with a conditional UPDATE (`WHERE consumed_at IS NULL`), so two
-- concurrent attempts to spend the same token cannot both win — the loser sees
-- zero rows updated and is refused. A boolean checked and then written in two
-- statements would race, and the race is a duplicated write.
--
-- Consumed on ANY attempt, valid or not. A token presented against the wrong
-- call was either replayed or guessed; neither deserves a second try.
--
-- EXPIRY
--
-- An approval is a judgement about a moment. A yes clicked on a two-day-old
-- "assign Maya to Friday's shift" answers a question whose facts have moved,
-- and the person clicking has no way to know that. Expired rows are refused on
-- read regardless of the sweep, so the sweep is housekeeping rather than a
-- security control.
--
-- WHAT IS DELIBERATELY ABSENT
--
--   approved_by          the surface has no approval UI yet, so there is no
--                        honest value to write. Added with the UI, not before:
--                        a column that is always NULL is worse than no column,
--                        because it looks like an audit trail.
--   rejection            a declined confirmation is simply never spent and
--                        then expires. Recording a "no" is a product decision
--                        (does the agent get told? does it retry?) and §12
--                        says not to resolve those unilaterally.
-- ============================================================================

SET search_path = public;

CREATE TABLE agent_confirmations (
  -- The token itself, as issued. Opaque and unguessable — 24 random bytes.
  -- Primary key because looking one up IS the operation.
  token        text        PRIMARY KEY,

  -- Tenancy. I5: a confirmation belongs to an organization and is resolvable
  -- only by a caller inside it.
  org_id       uuid        NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,

  -- Who was asked. SET NULL so a departed user's pending writes stay auditable
  -- rather than vanishing along with the reason a row exists.
  user_id      uuid        REFERENCES users (id) ON DELETE SET NULL,

  -- The run that proposed it. Text and no foreign key, matching agent_runs:
  -- the trajectory is written when a run ENDS, so at the moment a confirmation
  -- is issued the run it belongs to does not exist yet. A FK here would make
  -- the natural ordering illegal.
  run_id       text        NOT NULL,

  -- The fingerprint. All four, together, are what a token authorises.
  tool         text        NOT NULL,
  inputs_hash  text        NOT NULL,

  -- What the person actually read. Kept because "what were they told they were
  -- approving" is the question an audit of an agent-initiated write asks, and
  -- re-rendering it later from the arguments would answer a subtly different
  -- one — the world moves, and the description would move with it.
  payload      jsonb       NOT NULL,

  created_date timestamptz NOT NULL DEFAULT now(),
  expires_at   timestamptz NOT NULL,

  -- NULL until spent. Set by the conditional UPDATE that claims the token.
  consumed_at  timestamptz,

  CONSTRAINT agent_confirmations_expires_after_created CHECK (expires_at > created_date),
  CONSTRAINT agent_confirmations_payload_is_object CHECK (jsonb_typeof(payload) = 'object'),
  CONSTRAINT agent_confirmations_token_not_blank CHECK (length(btrim(token)) > 0)
);

-- "What is this person waiting to approve" — the approval queue's query, and
-- the only listing this table serves. Partial: a spent or expired row is not
-- part of anyone's queue, and indexing it would grow the index without bound
-- as history accumulates.
CREATE INDEX agent_confirmations_pending_idx
  ON agent_confirmations (org_id, created_date DESC)
  WHERE consumed_at IS NULL;

-- The retention sweep, and nothing else.
CREATE INDEX agent_confirmations_expires_idx
  ON agent_confirmations (expires_at);

COMMENT ON TABLE agent_confirmations IS
  'One row per write an agent proposed and a person has not yet approved. The token is bound '
  'to a fingerprint of the exact call described, and is single-use. See I4 and tools/confirm.go.';