AgentServer/apps/server/drizzle/0002_usage_daily.sql

CREATE TABLE `usage_daily` (
	`date` text NOT NULL,
	`source` text NOT NULL,
	`provider` text NOT NULL,
	`model` text NOT NULL,
	`runs` integer DEFAULT 0 NOT NULL,
	`failed` integer DEFAULT 0 NOT NULL,
	`interrupted` integer DEFAULT 0 NOT NULL,
	`input_tokens` integer DEFAULT 0 NOT NULL,
	`output_tokens` integer DEFAULT 0 NOT NULL,
	`cache_read_tokens` integer DEFAULT 0 NOT NULL,
	`cache_write_tokens` integer DEFAULT 0 NOT NULL,
	`cost_usd` real DEFAULT 0 NOT NULL,
	`duration_ms` integer DEFAULT 0 NOT NULL,
	PRIMARY KEY(`date`, `source`, `provider`, `model`)
);
--> statement-breakpoint
-- 数据迁移：用现有的 Run 记录补齐历史用量（按服务器本地日期）
INSERT INTO `usage_daily` (`date`, `source`, `provider`, `model`, `runs`, `failed`, `interrupted`, `input_tokens`, `output_tokens`, `cache_read_tokens`, `cache_write_tokens`, `cost_usd`, `duration_ms`)
SELECT date(coalesce(r.`started_at`, r.`created_at`), 'localtime'), s.`source`, s.`provider`, s.`model`,
  count(*), sum(r.`status` = 'failed'), sum(r.`status` = 'interrupted'),
  coalesce(sum(r.`input_tokens`), 0), coalesce(sum(r.`output_tokens`), 0), coalesce(sum(r.`cache_read_tokens`), 0), coalesce(sum(r.`cache_write_tokens`), 0),
  coalesce(sum(r.`cost_usd`), 0),
  coalesce(sum(CASE WHEN r.`started_at` IS NOT NULL AND r.`ended_at` IS NOT NULL THEN cast((julianday(r.`ended_at`) - julianday(r.`started_at`)) * 86400000 AS integer) END), 0)
FROM `runs` r JOIN `sessions` s ON s.`id` = r.`session_id`
WHERE r.`status` IN ('completed', 'failed', 'interrupted')
GROUP BY 1, 2, 3, 4;