You've already forked agentic-coding-workflow
50 lines
2.7 KiB
SQL
50 lines
2.7 KiB
SQL
-- ACP session cost accounting + budgets + reaper inputs.
|
|
-- Column-order rule: ALTER ADD COLUMN appends at the table end, so every
|
|
-- SELECT/RETURNING that reuses the table model must list the new columns LAST.
|
|
|
|
-- ============ AgentKind: model price link + default budget caps ============
|
|
ALTER TABLE acp_agent_kinds ADD COLUMN model_id UUID REFERENCES llm_models(id) ON DELETE SET NULL;
|
|
ALTER TABLE acp_agent_kinds ADD COLUMN max_cost_usd NUMERIC(12,6);
|
|
ALTER TABLE acp_agent_kinds ADD COLUMN max_tokens BIGINT;
|
|
ALTER TABLE acp_agent_kinds ADD COLUMN max_wall_clock_seconds INT;
|
|
|
|
-- ============ Sessions: running accumulators + budget snapshot + reaper inputs ============
|
|
ALTER TABLE acp_sessions ADD COLUMN prompt_tokens BIGINT NOT NULL DEFAULT 0;
|
|
ALTER TABLE acp_sessions ADD COLUMN completion_tokens BIGINT NOT NULL DEFAULT 0;
|
|
ALTER TABLE acp_sessions ADD COLUMN thinking_tokens BIGINT NOT NULL DEFAULT 0;
|
|
ALTER TABLE acp_sessions ADD COLUMN total_cost_usd NUMERIC(14,6) NOT NULL DEFAULT 0;
|
|
ALTER TABLE acp_sessions ADD COLUMN last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now();
|
|
ALTER TABLE acp_sessions ADD COLUMN budget_max_cost_usd NUMERIC(12,6);
|
|
ALTER TABLE acp_sessions ADD COLUMN budget_max_tokens BIGINT;
|
|
ALTER TABLE acp_sessions ADD COLUMN budget_max_wall_clock_seconds INT;
|
|
ALTER TABLE acp_sessions ADD COLUMN terminated_reason TEXT;
|
|
|
|
-- Index for the reaper to scan active sessions by last activity cheaply.
|
|
CREATE INDEX idx_acp_sessions_active_activity
|
|
ON acp_sessions(last_activity_at)
|
|
WHERE status IN ('starting','running');
|
|
|
|
-- ============ Per-turn usage ledger (mirrors llm_usage) ============
|
|
CREATE TABLE acp_session_usage (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
session_id UUID NOT NULL REFERENCES acp_sessions(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL,
|
|
project_id UUID NOT NULL,
|
|
agent_kind_id UUID NOT NULL,
|
|
model_id UUID,
|
|
prompt_tokens BIGINT NOT NULL DEFAULT 0,
|
|
completion_tokens BIGINT NOT NULL DEFAULT 0,
|
|
thinking_tokens BIGINT NOT NULL DEFAULT 0,
|
|
cost_usd NUMERIC(14,6) NOT NULL DEFAULT 0,
|
|
source_event_id BIGINT REFERENCES acp_events(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX idx_acp_session_usage_session ON acp_session_usage(session_id);
|
|
CREATE INDEX idx_acp_session_usage_created ON acp_session_usage(created_at);
|
|
CREATE INDEX idx_acp_session_usage_user_created ON acp_session_usage(user_id, created_at);
|
|
CREATE INDEX idx_acp_session_usage_project_created ON acp_session_usage(project_id, created_at);
|
|
-- De-dup: a given source event must produce at most one ledger row.
|
|
CREATE UNIQUE INDEX uq_acp_session_usage_source_event
|
|
ON acp_session_usage(source_event_id)
|
|
WHERE source_event_id IS NOT NULL;
|