Repository Public
1-- Agent OS kernel: durable execution journal. See docs/CONTRACT.md §2.2-- Invariants: steps and events are append-only (rows are inserted, a step row is updated exactly once3-- from 'started' to a terminal status); every state write by a worker is fenced by runs.fencing_token.45CREATE TABLE projects (6 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),7 owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,8 name text NOT NULL,9 description text NOT NULL DEFAULT '',10 rel_path text NOT NULL UNIQUE, -- relative to AOS_WORKSPACE_ROOT11 git_remote text,12 instructions text NOT NULL DEFAULT '', -- short operator notes (AGENTS.md in the repo wins)13 settings jsonb NOT NULL DEFAULT '{}',14 created_at timestamptz NOT NULL DEFAULT now(),15 updated_at timestamptz NOT NULL DEFAULT now()16);1718CREATE TABLE missions (19 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),20 owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,21 project_id uuid REFERENCES projects(id) ON DELETE SET NULL,22 title text NOT NULL,23 objective text NOT NULL,24 done_criteria jsonb NOT NULL DEFAULT '[]', -- [{id, text, passes:boolean, evidence?:string}]25 status text NOT NULL DEFAULT 'queued' -- queued|running|waiting|done|partial|blocked|failed|cancelled|budget_exhausted26 CHECK (status IN ('queued','running','waiting','done','partial','blocked','failed','cancelled','budget_exhausted')),27 budget_usd numeric(12,4) NOT NULL,28 spent_usd numeric(12,6) NOT NULL DEFAULT 0,29 network text NOT NULL DEFAULT 'none' CHECK (network IN ('none','open')),30 root_run_id uuid,31 created_at timestamptz NOT NULL DEFAULT now(),32 started_at timestamptz,33 ended_at timestamptz34);35CREATE INDEX missions_owner_created_idx ON missions(owner_id, created_at DESC);36CREATE INDEX missions_status_idx ON missions(status);3738CREATE TABLE runs (39 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),40 mission_id uuid NOT NULL REFERENCES missions(id) ON DELETE CASCADE,41 parent_run_id uuid REFERENCES runs(id) ON DELETE CASCADE,42 depth int NOT NULL DEFAULT 0,43 role text NOT NULL DEFAULT 'agent', -- agent|planner|worker|evaluator|…44 contract jsonb NOT NULL, -- DelegationContract (shared)45 provider text NOT NULL, -- anthropic|openai46 model text NOT NULL,47 effort text,48 status text NOT NULL DEFAULT 'queued'49 CHECK (status IN ('queued','running','waiting','done','partial','blocked','failed','cancelled','budget_exhausted')),50 result jsonb, -- ResultEnvelope (shared) once terminal51 workspace_abs text NOT NULL,52 sandbox_tier int NOT NULL DEFAULT 1,53 budget jsonb NOT NULL, -- {usd, tokens, turns, wallSec}54 usage jsonb NOT NULL DEFAULT '{"usd":0,"inputTokens":0,"outputTokens":0,"cacheReadTokens":0,"cacheWriteTokens":0,"turns":0,"toolCalls":0}',55 engine_version text NOT NULL,56 prompt_hash text, -- sha256(system prompt + tool list) pinned at first model call57 next_seq int NOT NULL DEFAULT 0,58 -- lease + fencing59 lease_owner text,60 lease_expires_at timestamptz,61 fencing_token bigint NOT NULL DEFAULT 0,62 cancel_requested boolean NOT NULL DEFAULT false,63 wake_at timestamptz, -- for timers: earliest time the run may be claimed again64 created_at timestamptz NOT NULL DEFAULT now(),65 started_at timestamptz,66 ended_at timestamptz67);68CREATE INDEX runs_mission_idx ON runs(mission_id);69CREATE INDEX runs_parent_idx ON runs(parent_run_id);70CREATE INDEX runs_claim_idx ON runs(status, wake_at, lease_expires_at);7172CREATE TABLE steps (73 run_id uuid NOT NULL REFERENCES runs(id) ON DELETE CASCADE,74 seq int NOT NULL,75 kind text NOT NULL, -- model_call|tool_call|spawn|join|wait|timer|now|uuid|compaction|checkpoint|verdict76 name text NOT NULL, -- tool name / model id / free label77 effect text NOT NULL CHECK (effect IN ('pure','read','idempotent_write','non_idempotent','model_call')),78 status text NOT NULL CHECK (status IN ('started','completed','failed','needs_reconcile')),79 request_hash text NOT NULL, -- sha256 of canonical JSON request; replay asserts equality80 request jsonb NOT NULL,81 result jsonb,82 error jsonb,83 usage jsonb, -- {usd, inputTokens, …} for model calls84 idem_key text NOT NULL, -- sha256(run_id || ':' || seq)85 started_at timestamptz NOT NULL DEFAULT now(),86 completed_at timestamptz,87 PRIMARY KEY (run_id, seq)88);8990CREATE TABLE events (91 id bigserial PRIMARY KEY,92 mission_id uuid NOT NULL,93 run_id uuid,94 seq int,95 ts timestamptz NOT NULL DEFAULT now(),96 kind text NOT NULL, -- see ServerEvent kinds in @aos/shared97 trust text NOT NULL DEFAULT 'system' CHECK (trust IN ('owner','system','agent','tool','web')),98 payload jsonb NOT NULL DEFAULT '{}'99);100CREATE INDEX events_mission_id_idx ON events(mission_id, id);101CREATE INDEX events_run_id_idx ON events(run_id, id);102103CREATE TABLE waits (104 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),105 run_id uuid NOT NULL REFERENCES runs(id) ON DELETE CASCADE,106 step_seq int NOT NULL,107 mission_id uuid NOT NULL,108 kind text NOT NULL CHECK (kind IN ('approval','question','timer','child')),109 payload jsonb NOT NULL, -- approval: {tool, input, risk, reason, diff?} · question: {text, options?} · child: {childRunId}110 status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','resolved','expired','cancelled')),111 resolution jsonb, -- approval: {decision:'approve'|'deny', remember?:boolean, note?} · question: {answer}112 resolve_by timestamptz, -- expiry ⇒ deny / null answer113 created_at timestamptz NOT NULL DEFAULT now(),114 resolved_at timestamptz,115 UNIQUE (run_id, step_seq)116);117CREATE INDEX waits_pending_idx ON waits(status, kind, created_at);118CREATE INDEX waits_mission_idx ON waits(mission_id);119120CREATE TABLE artifacts (121 sha256 text PRIMARY KEY,122 size bigint NOT NULL,123 mime text NOT NULL DEFAULT 'application/octet-stream',124 created_at timestamptz NOT NULL DEFAULT now()125);126CREATE TABLE run_artifacts (127 run_id uuid NOT NULL REFERENCES runs(id) ON DELETE CASCADE,128 sha256 text NOT NULL REFERENCES artifacts(sha256),129 name text NOT NULL,130 step_seq int,131 created_at timestamptz NOT NULL DEFAULT now(),132 PRIMARY KEY (run_id, sha256, name)133);134135-- Owner policy: deny → ask → allow. First matching rule of the strongest kind wins.136CREATE TABLE policy_rules (137 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),138 owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,139 project_id uuid REFERENCES projects(id) ON DELETE CASCADE, -- null = global140 decision text NOT NULL CHECK (decision IN ('deny','ask','allow')),141 tool text NOT NULL, -- tool name or '*'142 match jsonb NOT NULL DEFAULT '{}', -- tool-specific matcher, e.g. {commandPrefix:"git push"}143 note text NOT NULL DEFAULT '',144 created_at timestamptz NOT NULL DEFAULT now()145);146147-- Price table (USD per million tokens), versioned by effective date. Seeded by the providers package.148CREATE TABLE model_prices (149 provider text NOT NULL,150 model text NOT NULL,151 effective_from date NOT NULL,152 input_usd numeric(10,4) NOT NULL,153 output_usd numeric(10,4) NOT NULL,154 cache_read_usd numeric(10,4),155 cache_write_usd numeric(10,4),156 PRIMARY KEY (provider, model, effective_from)157);158159-- Daily spend guard (all missions).160CREATE TABLE spend_days (161 day date PRIMARY KEY,162 usd numeric(12,6) NOT NULL DEFAULT 0163);164