Blame · Line-by-line history

`0065_ai_cost_events.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.

0065_ai_cost_events.sqlBlame76 lines · 1 contributor

Normal view Raw

0c3eee5Claude	1	-- Per-call AI cost tracking. Every successful Claude API call inserts one
	2	-- row here so the /billing/usage dashboard can attribute cents/tokens back
	3	-- to a user, repo, agent session, and feature category. Strictly additive —
	4	-- nothing else reads or writes this table (yet); the tracker is best-effort
	5	-- and the recordAiCost call site is wrapped in try/catch.
	6	--
	7	-- Columns:
	8	-- id — primary key, gen_random_uuid()
	9	-- occurred_at — when the model returned. UTC.
	10	-- owner_user_id — nullable; the user the cost belongs to (PAT owner,
	11	-- session user, etc.). Nullable for system/cron calls
	12	-- with no human attribution (e.g. autopilot tasks
	13	-- running before a user is associated).
	14	-- repository_id — nullable; the repo the call relates to (PR review,
	15	-- repo chat, CI healer, etc.). Nullable for global
	16	-- spend (e.g. proactive monitor).
	17	-- agent_session_id — nullable; the agent session that initiated the call
	18	-- (for /settings/agents budget enforcement).
	19	-- model — Claude model id, e.g. "claude-sonnet-4-20250514".
	20	-- input_tokens — Anthropic .usage.input_tokens for this call.
	21	-- output_tokens — Anthropic .usage.output_tokens for this call.
	22	-- cents_estimate — derived from the in-process pricing table at the
	23	-- time of insert. Stored so historical aggregates
	24	-- stay stable even when pricing changes.
	25	-- category — feature classification: ai_review \| ai_patch \|
	26	-- ci_healer \| spec_to_pr \| standup \| chat \| voice \|
	27	-- test_gen \| refactor \| other
	28	-- source_id — opaque correlation id (PR id, chat id, run id…).
	29	-- source_kind — optional sub-category (e.g. "pull_request",
	30	-- "workflow_run") for the table-level grouping in
	31	-- the UI breakdown.
	32	-- created_at — row insert time (independent of occurred_at).
	33
	34	CREATE TABLE IF NOT EXISTS ai_cost_events (
	35	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	36	occurred_at timestamptz NOT NULL DEFAULT now(),
	37	owner_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
	38	repository_id uuid REFERENCES repositories(id) ON DELETE SET NULL,
	39	agent_session_id uuid REFERENCES agent_sessions(id) ON DELETE SET NULL,
	40	model text NOT NULL,
	41	input_tokens integer NOT NULL DEFAULT 0,
	42	output_tokens integer NOT NULL DEFAULT 0,
	43	cents_estimate integer NOT NULL DEFAULT 0,
	44	category text NOT NULL DEFAULT 'other',
	45	source_id text,
	46	source_kind text,
	47	created_at timestamptz NOT NULL DEFAULT now()
	48	);
	49
	50	-- Hot path: "show me this user's spend for the last 30 days bucketed by day".
	51	-- A composite (owner_user_id, occurred_at) index covers the most-common
	52	-- WHERE + ORDER BY combination on the dashboard.
	53	CREATE INDEX IF NOT EXISTS ai_cost_events_owner_time
	54	ON ai_cost_events (owner_user_id, occurred_at DESC);
	55
	56	-- Per-repo breakdown ("which repo is burning the most $?")
	57	CREATE INDEX IF NOT EXISTS ai_cost_events_repo_time
	58	ON ai_cost_events (repository_id, occurred_at DESC);
	59
	60	-- Per-agent breakdown (drives the agents budget surface).
	61	CREATE INDEX IF NOT EXISTS ai_cost_events_agent_time
	62	ON ai_cost_events (agent_session_id, occurred_at DESC);
	63
	64	-- Optional: category-rollup queries on big windows.
	65	CREATE INDEX IF NOT EXISTS ai_cost_events_category_time
	66	ON ai_cost_events (category, occurred_at DESC);
	67
	68	-- ai_budgets — per-user monthly budget cap (cents). One row per user.
	69	-- Used by /billing/usage to draw the "exceeded" warning banner when
	70	-- projected month-end spend exceeds the cap. Hard enforcement is out of
	71	-- scope for this migration; the dashboard is observational + advisory.
	72	CREATE TABLE IF NOT EXISTS ai_budgets (
	73	user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
	74	monthly_cents integer NOT NULL DEFAULT 0,
	75	updated_at timestamptz NOT NULL DEFAULT now()
	76	);