fix(usage): bucket workspace usage by task_usage.created_at, not enqueue time

GetWorkspaceUsageByDay and GetWorkspaceUsageSummary had the same date
attribution bug as the runtime endpoint fixed in #1167: they bucketed
and filtered on agent_task_queue.created_at (enqueue time), so a task
that queued at 23:58 and reported usage at 00:05 was attributed to the
prior day, and ?days=N became a rolling now()-N window that clipped the
morning of the earliest day returned.

Switch both queries to task_usage.created_at (~= task completion time)
and snap the since cutoff to start-of-day via DATE_TRUNC, mirroring
ListRuntimeUsage.

These endpoints have no frontend caller today, but per offline
discussion they will back the upcoming workspace-level usage dashboard.
Fix preemptively so the dashboard inherits correct numbers.

Add a regression test covering both endpoints with the same
cross-midnight + earliest-day-cutoff scenarios used for runtime usage.

server/internal/handler/usage_test.go

@@ -0,0 +1,138 @@
+package handler
+
+import (
+	"context"
+	"encoding/json"
+	"net/http"
+	"net/http/httptest"
+	"testing"
+	"time"
+)
+
+// TestWorkspaceUsage_BucketsByUsageTime mirrors the runtime usage test for
+// the workspace-level aggregations: a task that queues one calendar day and
+// reports usage the next must attribute to the day tokens were produced, and
+// `?days=N` must cover the full earliest day, not a rolling window starting
+// at "now minus N days".
+func TestWorkspaceUsage_BucketsByUsageTime(t *testing.T) {
+	if testHandler == nil {
+		t.Skip("database not available")
+	}
+	ctx := context.Background()
+
+	var runtimeID, agentID string
+	if err := testPool.QueryRow(ctx, `
+		SELECT id FROM agent_runtime WHERE workspace_id = $1 LIMIT 1
+	`, testWorkspaceID).Scan(&runtimeID); err != nil {
+		t.Fatalf("fetch runtime: %v", err)
+	}
+	if err := testPool.QueryRow(ctx, `
+		SELECT id FROM agent WHERE workspace_id = $1 LIMIT 1
+	`, testWorkspaceID).Scan(&agentID); err != nil {
+		t.Fatalf("fetch agent: %v", err)
+	}
+
+	var issueID string
+	if err := testPool.QueryRow(ctx, `
+		INSERT INTO issue (workspace_id, title, creator_id, creator_type)
+		VALUES ($1, 'workspace usage test', $2, 'member')
+		RETURNING id
+	`, testWorkspaceID, testUserID).Scan(&issueID); err != nil {
+		t.Fatalf("create issue: %v", err)
+	}
+	t.Cleanup(func() {
+		testPool.Exec(ctx, `DELETE FROM issue WHERE id = $1`, issueID)
+	})
+
+	now := time.Now().UTC()
+	today := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, time.UTC)
+	yesterdayLate := today.Add(-2 * time.Minute)
+	todayEarly := today.Add(5 * time.Minute)
+	yesterdayMorning := today.Add(-19 * time.Hour)
+
+	insertTaskWithUsage := func(enqueueAt, usageAt time.Time, inputTokens int64) {
+		var taskID string
+		if err := testPool.QueryRow(ctx, `
+			INSERT INTO agent_task_queue (agent_id, issue_id, runtime_id, status, created_at)
+			VALUES ($1, $2, $3, 'completed', $4)
+			RETURNING id
+		`, agentID, issueID, runtimeID, enqueueAt).Scan(&taskID); err != nil {
+			t.Fatalf("insert task: %v", err)
+		}
+		if _, err := testPool.Exec(ctx, `
+			INSERT INTO task_usage (task_id, provider, model, input_tokens, output_tokens, created_at)
+			VALUES ($1, 'claude', 'claude-3-5-sonnet', $2, 0, $3)
+		`, taskID, inputTokens, usageAt); err != nil {
+			t.Fatalf("insert task_usage: %v", err)
+		}
+		t.Cleanup(func() {
+			testPool.Exec(ctx, `DELETE FROM agent_task_queue WHERE id = $1`, taskID)
+		})
+	}
+
+	insertTaskWithUsage(yesterdayLate, todayEarly, 1000)         // cross-midnight
+	insertTaskWithUsage(yesterdayMorning, yesterdayMorning, 2000) // full-day yesterday
+
+	// /api/usage/daily — daily breakdown.
+	w := httptest.NewRecorder()
+	req := newRequest("GET", "/api/usage/daily?days=1", nil)
+	testHandler.GetWorkspaceUsageByDay(w, req)
+	if w.Code != http.StatusOK {
+		t.Fatalf("GetWorkspaceUsageByDay: expected 200, got %d: %s", w.Code, w.Body.String())
+	}
+
+	type dailyRow struct {
+		Date             string `json:"date"`
+		Model            string `json:"model"`
+		TotalInputTokens int64  `json:"total_input_tokens"`
+	}
+	var dailyResp []dailyRow
+	if err := json.NewDecoder(w.Body).Decode(&dailyResp); err != nil {
+		t.Fatalf("decode daily: %v", err)
+	}
+	byDate := make(map[string]int64)
+	for _, r := range dailyResp {
+		byDate[r.Date] += r.TotalInputTokens
+	}
+	todayKey := today.Format("2006-01-02")
+	yesterdayKey := today.Add(-24 * time.Hour).Format("2006-01-02")
+	if byDate[todayKey] < 1000 {
+		t.Errorf("daily: today bucket expected >=1000 input tokens (cross-midnight task), got %d (full map: %v)", byDate[todayKey], byDate)
+	}
+	if byDate[yesterdayKey] < 2000 {
+		t.Errorf("daily: yesterday bucket expected >=2000 input tokens (yesterday morning task), got %d (full map: %v)", byDate[yesterdayKey], byDate)
+	}
+
+	// /api/usage/summary — aggregate across the full window. Both rows must
+	// be included; if the cutoff were a rolling window, yesterday morning's
+	// 2000 would be missing depending on time of day.
+	w = httptest.NewRecorder()
+	req = newRequest("GET", "/api/usage/summary?days=1", nil)
+	testHandler.GetWorkspaceUsageSummary(w, req)
+	if w.Code != http.StatusOK {
+		t.Fatalf("GetWorkspaceUsageSummary: expected 200, got %d: %s", w.Code, w.Body.String())
+	}
+	type summaryRow struct {
+		Model            string `json:"model"`
+		TotalInputTokens int64  `json:"total_input_tokens"`
+		TaskCount        int32  `json:"task_count"`
+	}
+	var summaryResp []summaryRow
+	if err := json.NewDecoder(w.Body).Decode(&summaryResp); err != nil {
+		t.Fatalf("decode summary: %v", err)
+	}
+	var totalInput int64
+	var totalTasks int32
+	for _, r := range summaryResp {
+		if r.Model == "claude-3-5-sonnet" {
+			totalInput += r.TotalInputTokens
+			totalTasks += r.TaskCount
+		}
+	}
+	if totalInput < 3000 {
+		t.Errorf("summary: claude-3-5-sonnet input tokens expected >=3000 (1000 + 2000), got %d (full resp: %v)", totalInput, summaryResp)
+	}
+	if totalTasks < 2 {
+		t.Errorf("summary: claude-3-5-sonnet task_count expected >=2, got %d (full resp: %v)", totalTasks, summaryResp)
+	}
+}

server/pkg/db/generated/task_usage.sql.go

@@ -82,7 +82,7 @@ func (q *Queries) GetTaskUsage(ctx context.Context, taskID pgtype.UUID) ([]TaskU
 
 const getWorkspaceUsageByDay = `-- name: GetWorkspaceUsageByDay :many
 SELECT
-    DATE(atq.created_at) AS date,
+    DATE(tu.created_at) AS date,
     tu.model,
     SUM(tu.input_tokens)::bigint AS total_input_tokens,
     SUM(tu.output_tokens)::bigint AS total_output_tokens,
@@ -93,9 +93,9 @@ FROM task_usage tu
 JOIN agent_task_queue atq ON atq.id = tu.task_id
 JOIN agent a ON a.id = atq.agent_id
 WHERE a.workspace_id = $1
-  AND atq.created_at >= $2::timestamptz
-GROUP BY DATE(atq.created_at), tu.model
-ORDER BY DATE(atq.created_at) DESC, tu.model
+  AND tu.created_at >= DATE_TRUNC('day', $2::timestamptz)
+GROUP BY DATE(tu.created_at), tu.model
+ORDER BY DATE(tu.created_at) DESC, tu.model
 `
 
 type GetWorkspaceUsageByDayParams struct {
@@ -113,6 +113,10 @@ type GetWorkspaceUsageByDayRow struct {
 	TaskCount             int32       `json:"task_count"`
 }
 
+// Bucket by tu.created_at (usage report time, ~= task completion time), not
+// atq.created_at (task enqueue time), so tasks that queue one day and execute
+// the next are attributed to the day tokens were actually produced. The since
+// cutoff is truncated to start-of-day so `days=N` yields full calendar days.
 func (q *Queries) GetWorkspaceUsageByDay(ctx context.Context, arg GetWorkspaceUsageByDayParams) ([]GetWorkspaceUsageByDayRow, error) {
 	rows, err := q.db.Query(ctx, getWorkspaceUsageByDay, arg.WorkspaceID, arg.Since)
 	if err != nil {
@@ -153,7 +157,7 @@ FROM task_usage tu
 JOIN agent_task_queue atq ON atq.id = tu.task_id
 JOIN agent a ON a.id = atq.agent_id
 WHERE a.workspace_id = $1
-  AND atq.created_at >= $2::timestamptz
+  AND tu.created_at >= DATE_TRUNC('day', $2::timestamptz)
 GROUP BY tu.model
 ORDER BY (SUM(tu.input_tokens) + SUM(tu.output_tokens)) DESC
 `
@@ -172,6 +176,8 @@ type GetWorkspaceUsageSummaryRow struct {
 	TaskCount             int32  `json:"task_count"`
 }
 
+// Filter by tu.created_at (usage report time), aligned to start-of-day, so
+// `days=N` is interpreted as N full calendar days like the other usage queries.
 func (q *Queries) GetWorkspaceUsageSummary(ctx context.Context, arg GetWorkspaceUsageSummaryParams) ([]GetWorkspaceUsageSummaryRow, error) {
 	rows, err := q.db.Query(ctx, getWorkspaceUsageSummary, arg.WorkspaceID, arg.Since)
 	if err != nil {

server/pkg/db/queries/task_usage.sql

@@ -14,8 +14,12 @@ WHERE task_id = $1
 ORDER BY model;
 
 -- name: GetWorkspaceUsageByDay :many
+-- Bucket by tu.created_at (usage report time, ~= task completion time), not
+-- atq.created_at (task enqueue time), so tasks that queue one day and execute
+-- the next are attributed to the day tokens were actually produced. The since
+-- cutoff is truncated to start-of-day so `days=N` yields full calendar days.
 SELECT
-    DATE(atq.created_at) AS date,
+    DATE(tu.created_at) AS date,
     tu.model,
     SUM(tu.input_tokens)::bigint AS total_input_tokens,
     SUM(tu.output_tokens)::bigint AS total_output_tokens,
@@ -26,11 +30,13 @@ FROM task_usage tu
 JOIN agent_task_queue atq ON atq.id = tu.task_id
 JOIN agent a ON a.id = atq.agent_id
 WHERE a.workspace_id = $1
-  AND atq.created_at >= @since::timestamptz
-GROUP BY DATE(atq.created_at), tu.model
-ORDER BY DATE(atq.created_at) DESC, tu.model;
+  AND tu.created_at >= DATE_TRUNC('day', @since::timestamptz)
+GROUP BY DATE(tu.created_at), tu.model
+ORDER BY DATE(tu.created_at) DESC, tu.model;
 
 -- name: GetWorkspaceUsageSummary :many
+-- Filter by tu.created_at (usage report time), aligned to start-of-day, so
+-- `days=N` is interpreted as N full calendar days like the other usage queries.
 SELECT
     tu.model,
     SUM(tu.input_tokens)::bigint AS total_input_tokens,
@@ -42,7 +48,7 @@ FROM task_usage tu
 JOIN agent_task_queue atq ON atq.id = tu.task_id
 JOIN agent a ON a.id = atq.agent_id
 WHERE a.workspace_id = $1
-  AND atq.created_at >= @since::timestamptz
+  AND tu.created_at >= DATE_TRUNC('day', @since::timestamptz)
 GROUP BY tu.model
 ORDER BY (SUM(tu.input_tokens) + SUM(tu.output_tokens)) DESC;