Blame · Line-by-line history

`0058_agent_multiplayer.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0058_agent_multiplayer.sqlBlame72 lines · 1 contributor

Normal view Raw

e75eddcClaude	1	-- Agent multiplayer v1 — per-agent namespacing + leases + budget caps.
	2	--
	3	-- Premise: when 10-100 AI agents push to the same repo, they must not step
	4	-- on each other. Each agent gets a stable `agent_sessions` row with a
	5	-- token, a branch-namespace prefix the git plumbing must enforce, and a
	6	-- daily budget. Coordination on shared resources (issues, PRs, file
	7	-- paths, branches) flows through `agent_leases` — a soft mutex with a
	8	-- TTL. The autopilot resets `spent_cents_today` at midnight UTC and
	9	-- expires stale leases.
	10
	11	CREATE TABLE IF NOT EXISTS agent_sessions (
	12	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	13	-- Human-facing handle: "claude-1", "holden-mercer", "release-bot". Not
	14	-- globally unique — uniqueness is enforced per owner_user_id via the
	15	-- partial index below so multiple humans can each have a "claude-1".
	16	name text NOT NULL,
	17	-- The human who manages this agent and pays for its actions.
	18	owner_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	19	-- Optional repo scope. NULL means the agent is global to the owner.
	20	repository_id uuid REFERENCES repositories(id) ON DELETE CASCADE,
	21	-- SHA-256 of the plaintext "agt_..." token. Never store plaintext.
	22	token_hash text NOT NULL UNIQUE,
	23	-- Branch prefix the git plumbing must enforce on every ref update,
	24	-- e.g. "agents/claude-1/". Should end with "/" so simple startsWith
	25	-- checks are unambiguous, but the helper normalises it.
	26	branch_namespace text NOT NULL,
	27	-- Daily spend cap in cents. Default $5.00 — enough for routine PR
	28	-- work, low enough that a runaway agent gets caught fast.
	29	budget_cents_per_day integer NOT NULL DEFAULT 500,
	30	spent_cents_today integer NOT NULL DEFAULT 0,
	31	last_active_at timestamptz,
	32	created_at timestamptz NOT NULL DEFAULT now()
	33	);
	34
	35	-- An owner can't have two agents with the same name.
	36	CREATE UNIQUE INDEX IF NOT EXISTS agent_sessions_owner_name
	37	ON agent_sessions (owner_user_id, name);
	38
	39	CREATE INDEX IF NOT EXISTS agent_sessions_owner
	40	ON agent_sessions (owner_user_id);
	41
	42	CREATE INDEX IF NOT EXISTS agent_sessions_repo
	43	ON agent_sessions (repository_id)
	44	WHERE repository_id IS NOT NULL;
	45
	46	CREATE TABLE IF NOT EXISTS agent_leases (
	47	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	48	agent_session_id uuid NOT NULL REFERENCES agent_sessions(id) ON DELETE CASCADE,
	49	-- What kind of thing the lease covers — keeps the (type, id) space
	50	-- naturally partitioned. Vocabulary is open-ended; the lib enforces
	51	-- the known set.
	52	target_type text NOT NULL, -- 'issue' \| 'pr' \| 'file_path' \| 'branch'
	53	target_id text NOT NULL,
	54	acquired_at timestamptz NOT NULL DEFAULT now(),
	55	expires_at timestamptz NOT NULL,
	56	status text NOT NULL DEFAULT 'active', -- 'active' \| 'released' \| 'expired'
	57	created_at timestamptz NOT NULL DEFAULT now()
	58	);
	59
	60	-- Per-target lookup — answers "who holds the lease on issue 42?" in O(1).
	61	-- Partial unique on the active row prevents two agents holding the same
	62	-- target simultaneously. Released/expired rows don't conflict.
	63	CREATE UNIQUE INDEX IF NOT EXISTS agent_leases_active_target
	64	ON agent_leases (target_type, target_id)
	65	WHERE status = 'active';
	66
	67	CREATE INDEX IF NOT EXISTS agent_leases_agent
	68	ON agent_leases (agent_session_id, status);
	69
	70	CREATE INDEX IF NOT EXISTS agent_leases_expires
	71	ON agent_leases (expires_at)
	72	WHERE status = 'active';