Agent Jobs

The agent task-queue data model — queued/running/done jobs with verdicts, schedules and artifacts. Idempotent DDL.

-- ---------------------------------------------------------------------------
-- Agents Store — the agent_jobs queue + artifacts.
--
-- The web app GOVERNS (a human admin enqueues, views, cancels); the GitHub
-- Actions runner EXECUTES (claims a queued job with its act-as bearer, drives
-- Playwright / hits APIs against the DEPLOYED target, posts redacted results +
-- screenshots back). Status walk: queued → running → done|failed, with
-- cancelled reachable from queued/running (admin). `verdict` is the human-
-- readable outcome of a finished job (pass | fail | risk).
--
--   agent_jobs          — one row per launched task (spec jsonb carries the
--                         checklist / target base URL / flags like testMarker).
--   agent_job_artifacts — screenshots etc.; bytes live in the PRIVATE
--                         `agent-artifacts` Storage bucket (npm run
--                         agents:setup-storage), served only via short-TTL
--                         signed URLs behind the admin gate.
--
-- RLS on, NO anon — service-role only. Apply to the MAIN project
-- (diziibeymowzpondeqxz). Additive, idempotent. Reuses iam_set_updated_at().
-- ---------------------------------------------------------------------------

create table if not exists public.agent_jobs (
  id            uuid primary key default gen_random_uuid(),
  principal_key text not null,                                  -- the agent that runs it
  kind          text not null,                                  -- e.g. 'uat.quick-task'
  spec          jsonb not null default '{}'::jsonb,             -- task spec / checklist / target
  status        text not null default 'queued'
                check (status in ('queued', 'running', 'done', 'failed', 'cancelled')),
  verdict       text check (verdict in ('pass', 'fail', 'risk')),
  results       jsonb,                                          -- redacted per-check results
  error         text,
  run_ref       text,                                           -- GH Actions run URL
  queued_at     timestamptz not null default now(),
  started_at    timestamptz,
  finished_at   timestamptz,
  created_by    text,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);
comment on table public.agent_jobs is
  'Agents Store job queue: the web app enqueues/governs, the GitHub Actions runner claims (agent bearer) and posts redacted results back.';

create index if not exists agent_jobs_principal_status_idx
  on public.agent_jobs (principal_key, status, queued_at);
create index if not exists agent_jobs_status_idx
  on public.agent_jobs (status, queued_at);

drop trigger if exists agent_jobs_set_updated_at on public.agent_jobs;
create trigger agent_jobs_set_updated_at
  before update on public.agent_jobs
  for each row execute function public.iam_set_updated_at();

alter table public.agent_jobs enable row level security;

-- --- artifacts (screenshots etc.) -------------------------------------------
create table if not exists public.agent_job_artifacts (
  id            uuid primary key default gen_random_uuid(),
  job_id        uuid not null references public.agent_jobs (id) on delete cascade,
  name          text not null,
  content_type  text not null default 'image/png',
  storage_path  text not null,                                  -- key in the agent-artifacts bucket
  size_bytes    integer not null default 0,
  created_at    timestamptz not null default now()
);
comment on table public.agent_job_artifacts is
  'Screenshots/files a runner attached to an agent_jobs row. Bytes in the private agent-artifacts bucket; read via short-TTL signed URLs behind the admin gate.';

create index if not exists agent_job_artifacts_job_idx
  on public.agent_job_artifacts (job_id);

alter table public.agent_job_artifacts enable row level security;