4 files changed, 301 insertions, 0 deletions
diff --git a/internal/storage/dashboard.go b/internal/storage/dashboard.go
new file mode 100644
index 0000000..e432af1
--- /dev/null
+++ b/internal/storage/dashboard.go
@@ -0,0 +1,91 @@
+package storage
+
+import "time"
+
+// EscalationBucket is one (escalation_rung, provider) aggregate over
+// executions in a window — the raw material for the budget dashboard's
+// escalation funnel (GET /api/escalation-funnel), the harness's
+// "how much falls through each rung" story made visible.
+type EscalationBucket struct {
+	Rung    int     `json:"rung"`
+	Agent   string  `json:"agent"`
+	Count   int     `json:"count"`
+	CostUSD float64 `json:"cost_usd"`
+}
+
+// QueryEscalationFunnel aggregates executions started at or after `since` by
+// (escalation_rung, agent), counting executions and summing cost. Excludes
+// executions still in flight (RUNNING/QUEUED/PENDING have no terminal
+// outcome yet to attribute to a rung).
+//
+// Rung 0 is not exclusively "resolved locally": executions.escalation_rung
+// defaults to 0 uniformly, so every non-role-typed execution (which never
+// climbs a ladder at all) also lands there alongside role-typed tasks
+// genuinely resolved at their ladder's first tier. This mirrors the
+// aggregate shape suggested for this phase and keeps the query a plain
+// GROUP BY with no join — a role-only variant would need to join tasks and
+// inspect config_json (no queryable role column on executions), which is
+// intentionally left out of scope here.
+func (s *DB) QueryEscalationFunnel(since time.Time) ([]EscalationBucket, error) {
+	rows, err := s.db.Query(`
+		SELECT escalation_rung, COALESCE(agent, ''), COUNT(*), COALESCE(SUM(cost_usd), 0)
+		FROM executions
+		WHERE start_time >= ? AND status NOT IN ('RUNNING', 'QUEUED', 'PENDING')
+		GROUP BY escalation_rung, agent
+		ORDER BY escalation_rung ASC, agent ASC`, since.UTC())
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	out := []EscalationBucket{}
+	for rows.Next() {
+		var b EscalationBucket
+		if err := rows.Scan(&b.Rung, &b.Agent, &b.Count, &b.CostUSD); err != nil {
+			return nil, err
+		}
+		out = append(out, b)
+	}
+	return out, rows.Err()
+}
+
+// SpendPoint is one (time bucket, provider) aggregate of cost — the raw
+// material for the budget dashboard's spend-over-time chart
+// (GET /api/spend-timeseries).
+type SpendPoint struct {
+	Bucket  string  `json:"bucket"` // RFC3339 truncated to the hour, or YYYY-MM-DD
+	Agent   string  `json:"agent"`
+	CostUSD float64 `json:"cost_usd"`
+}
+
+// QuerySpendTimeseries aggregates cost per provider bucketed by hour
+// (hourly=true) or by calendar day (hourly=false), for executions started at
+// or after since. Mirrors QueryDashboardStats' own Throughput (hourly) /
+// Billing (daily) bucketing expressions, just grouped additionally by
+// provider so per-provider trend lines can be drawn.
+func (s *DB) QuerySpendTimeseries(since time.Time, hourly bool) ([]SpendPoint, error) {
+	bucketExpr := "date(start_time)"
+	if hourly {
+		bucketExpr = "strftime('%Y-%m-%dT%H:00:00Z', start_time)"
+	}
+	rows, err := s.db.Query(`
+		SELECT `+bucketExpr+` as bucket, COALESCE(agent, ''), COALESCE(SUM(cost_usd), 0)
+		FROM executions
+		WHERE start_time >= ?
+		GROUP BY bucket, agent
+		ORDER BY bucket ASC, agent ASC`, since.UTC())
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	out := []SpendPoint{}
+	for rows.Next() {
+		var p SpendPoint
+		if err := rows.Scan(&p.Bucket, &p.Agent, &p.CostUSD); err != nil {
+			return nil, err
+		}
+		out = append(out, p)
+	}
+	return out, rows.Err()
+}
diff --git a/internal/storage/dashboard_test.go b/internal/storage/dashboard_test.go
new file mode 100644
index 0000000..b7e425d
--- /dev/null
+++ b/internal/storage/dashboard_test.go
@@ -0,0 +1,151 @@
+package storage
+
+import (
+	"strconv"
+	"testing"
+	"time"
+
+	"github.com/thepeterstone/claudomator/internal/task"
+)
+
+// mustCreateTask is a small helper mirroring the pattern used in db_test.go
+// (TestCreateTask_AndGetTask) — a minimal valid task row so executions can
+// reference a real task_id.
+func mustCreateTask(t *testing.T, db *DB, id string) {
+	t.Helper()
+	now := time.Now().UTC().Truncate(time.Second)
+	tk := &task.Task{
+		ID:        id,
+		Name:      "dashboard-test-" + id,
+		State:     task.StatePending,
+		DependsOn: []string{},
+		CreatedAt: now,
+		UpdatedAt: now,
+	}
+	if err := db.CreateTask(tk); err != nil {
+		t.Fatalf("CreateTask(%s): %v", id, err)
+	}
+}
+
+func mustCreateExecution(t *testing.T, db *DB, id, taskID, agent string, rung int, cost float64, start time.Time, status string) {
+	t.Helper()
+	exec := &Execution{
+		ID:             id,
+		TaskID:         taskID,
+		StartTime:      start,
+		EndTime:        start.Add(time.Minute),
+		Status:         status,
+		CostUSD:        cost,
+		Agent:          agent,
+		EscalationRung: rung,
+	}
+	if err := db.CreateExecution(exec); err != nil {
+		t.Fatalf("CreateExecution(%s): %v", id, err)
+	}
+}
+
+func TestQueryEscalationFunnel_AggregatesByRungAndAgent(t *testing.T) {
+	db := testDB(t)
+	mustCreateTask(t, db, "t1")
+
+	now := time.Now().UTC()
+	// Rung 0: 2 local executions.
+	mustCreateExecution(t, db, "e1", "t1", "local", 0, 0, now.Add(-3*time.Hour), "COMPLETED")
+	mustCreateExecution(t, db, "e2", "t1", "local", 0, 0, now.Add(-2*time.Hour), "COMPLETED")
+	// Rung 1: 1 anthropic execution.
+	mustCreateExecution(t, db, "e3", "t1", "anthropic", 1, 0.05, now.Add(-1*time.Hour), "COMPLETED")
+	// Still in flight — must be excluded.
+	mustCreateExecution(t, db, "e4", "t1", "anthropic", 1, 0, now, "RUNNING")
+	// Too old — outside the window.
+	mustCreateExecution(t, db, "e5", "t1", "local", 0, 0, now.Add(-48*time.Hour), "COMPLETED")
+
+	buckets, err := db.QueryEscalationFunnel(now.Add(-24 * time.Hour))
+	if err != nil {
+		t.Fatalf("QueryEscalationFunnel: %v", err)
+	}
+
+	want := map[string]struct {
+		count int
+		cost  float64
+	}{
+		"0|local":     {2, 0},
+		"1|anthropic": {1, 0.05},
+	}
+	if len(buckets) != len(want) {
+		t.Fatalf("expected %d buckets, got %d: %+v", len(want), len(buckets), buckets)
+	}
+	for _, b := range buckets {
+		key := strconv.Itoa(b.Rung) + "|" + b.Agent
+		w, ok := want[key]
+		if !ok {
+			t.Errorf("unexpected bucket %+v", b)
+			continue
+		}
+		if b.Count != w.count {
+			t.Errorf("bucket %s: count = %d, want %d", key, b.Count, w.count)
+		}
+		if b.CostUSD != w.cost {
+			t.Errorf("bucket %s: cost = %v, want %v", key, b.CostUSD, w.cost)
+		}
+	}
+}
+
+func TestQueryEscalationFunnel_Empty_ReturnsEmptySlice(t *testing.T) {
+	db := testDB(t)
+	buckets, err := db.QueryEscalationFunnel(time.Now().Add(-24 * time.Hour))
+	if err != nil {
+		t.Fatalf("QueryEscalationFunnel: %v", err)
+	}
+	if buckets == nil || len(buckets) != 0 {
+		t.Errorf("expected empty non-nil slice, got %v", buckets)
+	}
+}
+
+func TestQuerySpendTimeseries_HourlyBucketsByProvider(t *testing.T) {
+	db := testDB(t)
+	mustCreateTask(t, db, "t1")
+
+	// Pin two executions to the exact same hour bucket for two providers.
+	hourStart := time.Now().UTC().Truncate(time.Hour)
+	mustCreateExecution(t, db, "e1", "t1", "local", 0, 0.10, hourStart.Add(5*time.Minute), "COMPLETED")
+	mustCreateExecution(t, db, "e2", "t1", "anthropic", 0, 0.20, hourStart.Add(10*time.Minute), "COMPLETED")
+	mustCreateExecution(t, db, "e3", "t1", "anthropic", 0, 0.05, hourStart.Add(40*time.Minute), "COMPLETED")
+
+	points, err := db.QuerySpendTimeseries(hourStart.Add(-time.Hour), true)
+	if err != nil {
+		t.Fatalf("QuerySpendTimeseries: %v", err)
+	}
+
+	totals := map[string]float64{}
+	for _, p := range points {
+		totals[p.Agent] += p.CostUSD
+	}
+	if totals["local"] != 0.10 {
+		t.Errorf("local total = %v, want 0.10", totals["local"])
+	}
+	if got, want := totals["anthropic"], 0.25; got < want-1e-9 || got > want+1e-9 {
+		t.Errorf("anthropic total = %v, want %v", got, want)
+	}
+}
+
+func TestQuerySpendTimeseries_DailyBucketing(t *testing.T) {
+	db := testDB(t)
+	mustCreateTask(t, db, "t1")
+
+	today := time.Now().UTC()
+	mustCreateExecution(t, db, "e1", "t1", "local", 0, 1.5, today, "COMPLETED")
+
+	points, err := db.QuerySpendTimeseries(today.Add(-24*time.Hour), false)
+	if err != nil {
+		t.Fatalf("QuerySpendTimeseries: %v", err)
+	}
+	if len(points) != 1 {
+		t.Fatalf("expected 1 point, got %d: %+v", len(points), points)
+	}
+	if points[0].Bucket != today.Format("2006-01-02") {
+		t.Errorf("bucket = %q, want %q", points[0].Bucket, today.Format("2006-01-02"))
+	}
+	if points[0].CostUSD != 1.5 {
+		t.Errorf("cost = %v, want 1.5", points[0].CostUSD)
+	}
+}
diff --git a/internal/storage/roleconfig.go b/internal/storage/roleconfig.go
index 6ae043b..f9478f2 100644
--- a/internal/storage/roleconfig.go
+++ b/internal/storage/roleconfig.go
@@ -125,6 +125,28 @@ func (s *DB) ActivateRoleConfigVersion(roleName string, version int) error {
 	return tx.Commit()
 }
 
+// ListRoleNames returns every distinct role name that has at least one
+// role_configs row, alphabetically. There is no other way to discover which
+// roles exist short of guessing names — this backs GET /api/roles, the
+// "which roles exist" entry point the role/config management panel needs.
+func (s *DB) ListRoleNames() ([]string, error) {
+	rows, err := s.db.Query(`SELECT DISTINCT role FROM role_configs ORDER BY role ASC`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	out := []string{}
+	for rows.Next() {
+		var r string
+		if err := rows.Scan(&r); err != nil {
+			return nil, err
+		}
+		out = append(out, r)
+	}
+	return out, rows.Err()
+}
+
 func scanRoleConfigRow(row scanner) (*RoleConfigRow, error) {
 	var r RoleConfigRow
 	var createdAt time.Time
diff --git a/internal/storage/roleconfig_test.go b/internal/storage/roleconfig_test.go
index b18eaef..e5c8242 100644
--- a/internal/storage/roleconfig_test.go
+++ b/internal/storage/roleconfig_test.go
@@ -136,6 +136,43 @@ func TestActivateRoleConfigVersion_ExactlyOneActive(t *testing.T) {
 	}
 }
 
+func TestListRoleNames_ReturnsDistinctRolesAlphabetically(t *testing.T) {
+	db := testDB(t)
+
+	if _, err := db.CreateRoleConfig("coder", `{"role":"coder"}`, "human"); err != nil {
+		t.Fatalf("CreateRoleConfig coder: %v", err)
+	}
+	// A second version for the same role must not produce a duplicate entry.
+	if _, err := db.CreateRoleConfig("coder", `{"role":"coder","system_prompt":"v2"}`, "human"); err != nil {
+		t.Fatalf("CreateRoleConfig coder v2: %v", err)
+	}
+	if _, err := db.CreateRoleConfig("builder", `{"role":"builder"}`, "human"); err != nil {
+		t.Fatalf("CreateRoleConfig builder: %v", err)
+	}
+
+	names, err := db.ListRoleNames()
+	if err != nil {
+		t.Fatalf("ListRoleNames: %v", err)
+	}
+	if len(names) != 2 {
+		t.Fatalf("expected 2 distinct roles, got %d: %v", len(names), names)
+	}
+	if names[0] != "builder" || names[1] != "coder" {
+		t.Errorf("expected [builder coder] alphabetically, got %v", names)
+	}
+}
+
+func TestListRoleNames_Empty_ReturnsEmptySlice(t *testing.T) {
+	db := testDB(t)
+	names, err := db.ListRoleNames()
+	if err != nil {
+		t.Fatalf("ListRoleNames: %v", err)
+	}
+	if names == nil || len(names) != 0 {
+		t.Errorf("expected empty (non-nil) slice, got %v", names)
+	}
+}
+
 func TestActivateRoleConfigVersion_UnknownVersion_Errors(t *testing.T) {
 	db := testDB(t)
 	if _, err := db.CreateRoleConfig("coder", `{"role":"coder"}`, "human"); err != nil {