Blame · Line-by-line history

`0070_agent_marketplace.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.

0070_agent_marketplace.sqlBlame107 lines · 1 contributor

Normal view Raw

5ca514aClaude	1	-- Agent Marketplace — third-party AI agents that users one-click-install per
	2	-- repo. Builds on `agent_sessions` (0058): every install provisions a
	3	-- fresh agent_session whose `branch_namespace` and `budget_cents_per_day`
	4	-- are seeded from the listing's `agent_template`. We take a 30% cut on
	5	-- paid invocations (`price_cents`, charged through ai_cost_events).
	6	--
	7	-- Three tables:
	8	-- agent_marketplace_listings — the catalog row a publisher creates.
	9	-- agent_marketplace_installs — link table: which listing is wired to
	10	-- which repo (+ which agent_session it
	11	-- provisioned). UNIQUE on (listing_id,
	12	-- repository_id) so we can't double-install.
	13	-- agent_marketplace_reviews — 1-5 star ratings + body.
	14
	15	CREATE TABLE IF NOT EXISTS agent_marketplace_listings (
	16	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	17	-- The user who published this listing and is paid out (after our 30%).
	18	publisher_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	19	-- URL-safe slug. Globally unique so /marketplace/agents/<slug> is stable.
	20	slug text NOT NULL UNIQUE,
	21	name text NOT NULL,
	22	-- One-line teaser shown in the catalog grid.
	23	tagline text NOT NULL DEFAULT '',
	24	-- Markdown description shown on the detail page.
	25	description text NOT NULL DEFAULT '',
	26	-- Closed vocabulary, validated in the lib:
	27	-- reviewer \| tester \| migrator \| security \| docs \| custom
	28	category text NOT NULL DEFAULT 'custom',
	29	-- Closed vocabulary:
	30	-- per_invocation — charge once per agent action (price_cents)
	31	-- per_repo_per_month — flat monthly subscription per install
	32	-- free — price_cents is ignored
	33	pricing_model text NOT NULL DEFAULT 'free',
	34	price_cents integer NOT NULL DEFAULT 0,
	35	-- Defaults the install flow seeds into the new agent_session.
	36	-- Recognised keys: branchNamespace, budgetCentsPerDay, capabilities[].
	37	agent_template jsonb NOT NULL DEFAULT '{}'::jsonb,
	38	source_url text,
	39	-- Moderation state: draft \| pending_review \| approved \| rejected.
	40	status text NOT NULL DEFAULT 'draft',
	41	install_count integer NOT NULL DEFAULT 0,
	42	-- Aggregated rating. Updated after every review insert.
	43	rating_avg numeric(3, 2) NOT NULL DEFAULT 0,
	44	rating_count integer NOT NULL DEFAULT 0,
	45	created_at timestamptz NOT NULL DEFAULT now(),
	46	updated_at timestamptz NOT NULL DEFAULT now()
	47	);
	48
	49	CREATE INDEX IF NOT EXISTS agent_marketplace_listings_category
	50	ON agent_marketplace_listings (category, status);
	51
	52	CREATE INDEX IF NOT EXISTS agent_marketplace_listings_rating
	53	ON agent_marketplace_listings (rating_avg DESC, rating_count DESC);
	54
	55	CREATE INDEX IF NOT EXISTS agent_marketplace_listings_installs
	56	ON agent_marketplace_listings (install_count DESC);
	57
	58	CREATE INDEX IF NOT EXISTS agent_marketplace_listings_publisher
	59	ON agent_marketplace_listings (publisher_user_id);
	60
	61	CREATE INDEX IF NOT EXISTS agent_marketplace_listings_status_created
	62	ON agent_marketplace_listings (status, created_at DESC);
	63
	64	CREATE TABLE IF NOT EXISTS agent_marketplace_installs (
	65	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	66	listing_id uuid NOT NULL
	67	REFERENCES agent_marketplace_listings(id) ON DELETE CASCADE,
	68	repository_id uuid NOT NULL
	69	REFERENCES repositories(id) ON DELETE CASCADE,
	70	installed_by_user_id uuid NOT NULL
	71	REFERENCES users(id) ON DELETE CASCADE,
	72	-- The agent_session provisioned at install time. Killed when the install
	73	-- flips to 'uninstalled'.
	74	agent_session_id uuid
	75	REFERENCES agent_sessions(id) ON DELETE SET NULL,
	76	status text NOT NULL DEFAULT 'active', -- active \| paused \| uninstalled
	77	installed_at timestamptz NOT NULL DEFAULT now(),
	78	last_invoked_at timestamptz,
	79	created_at timestamptz NOT NULL DEFAULT now()
	80	);
	81
	82	-- Can't install the same listing on the same repo twice.
	83	CREATE UNIQUE INDEX IF NOT EXISTS agent_marketplace_installs_listing_repo
	84	ON agent_marketplace_installs (listing_id, repository_id);
	85
	86	CREATE INDEX IF NOT EXISTS agent_marketplace_installs_repo
	87	ON agent_marketplace_installs (repository_id, status);
	88
	89	CREATE INDEX IF NOT EXISTS agent_marketplace_installs_installer
	90	ON agent_marketplace_installs (installed_by_user_id);
	91
	92	CREATE TABLE IF NOT EXISTS agent_marketplace_reviews (
	93	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	94	listing_id uuid NOT NULL
	95	REFERENCES agent_marketplace_listings(id) ON DELETE CASCADE,
	96	reviewer_user_id uuid NOT NULL
	97	REFERENCES users(id) ON DELETE CASCADE,
	98	rating integer NOT NULL,
	99	body text NOT NULL DEFAULT '',
	100	created_at timestamptz NOT NULL DEFAULT now()
	101	);
	102
	103	CREATE INDEX IF NOT EXISTS agent_marketplace_reviews_listing_created
	104	ON agent_marketplace_reviews (listing_id, created_at DESC);
	105
	106	CREATE INDEX IF NOT EXISTS agent_marketplace_reviews_reviewer
	107	ON agent_marketplace_reviews (reviewer_user_id);