From 361040939eb428f990c97ab0ab983e5360761b27 Mon Sep 17 00:00:00 2001
From: Claudomator Agent <agent@claudomator>
Date: Mon, 9 Mar 2026 07:32:26 +0000
Subject: storage: add missing indexes and ListRecentExecutions correctness
 tests

Add two schema indexes that were missing:
- idx_executions_start_time on executions(start_time): speeds up
  ListRecentExecutions WHERE start_time >= ? ORDER BY start_time DESC
- idx_tasks_parent_task_id on tasks(parent_task_id): speeds up
  ListSubtasks WHERE parent_task_id = ?

Both use CREATE INDEX IF NOT EXISTS so they are safe to apply on
existing databases without a migration version bump.

Add TestListRecentExecutions_LargeDataset (100 rows, two tasks) covering:
- returns all rows in descending start_time order
- respects the limit parameter
- filters correctly by since time
- filters correctly by task_id

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
 internal/storage/db_test.go | 88 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 88 insertions(+)

(limited to 'internal/storage/db_test.go')

diff --git a/internal/storage/db_test.go b/internal/storage/db_test.go
index e76c00a..8b10817 100644
--- a/internal/storage/db_test.go
+++ b/internal/storage/db_test.go
@@ -581,6 +581,94 @@ func TestStorage_GetLatestExecution(t *testing.T) {
 	}
 }
 
+func TestListRecentExecutions_LargeDataset(t *testing.T) {
+	db := testDB(t)
+	now := time.Now().UTC().Truncate(time.Second)
+
+	// Create two tasks so we can also test the taskID filter.
+	taskA := makeTestTask("re-task-a", now)
+	taskB := makeTestTask("re-task-b", now)
+	db.CreateTask(taskA)
+	db.CreateTask(taskB)
+
+	// Insert 100 executions spread across the two tasks, 1 minute apart.
+	for i := 0; i < 100; i++ {
+		tid := "re-task-a"
+		if i%2 == 0 {
+			tid = "re-task-b"
+		}
+		start := now.Add(time.Duration(i) * time.Minute)
+		db.CreateExecution(&Execution{
+			ID:        fmt.Sprintf("re-exec-%03d", i),
+			TaskID:    tid,
+			StartTime: start,
+			EndTime:   start.Add(30 * time.Second),
+			Status:    "COMPLETED",
+			CostUSD:   float64(i) * 0.01,
+		})
+	}
+
+	t.Run("returns all executions since epoch", func(t *testing.T) {
+		results, err := db.ListRecentExecutions(now.Add(-time.Hour), 200, "")
+		if err != nil {
+			t.Fatalf("ListRecentExecutions: %v", err)
+		}
+		if len(results) != 100 {
+			t.Errorf("want 100, got %d", len(results))
+		}
+		// Verify descending order by start_time.
+		for i := 1; i < len(results); i++ {
+			if results[i-1].StartedAt.Before(results[i].StartedAt) {
+				t.Errorf("results not in descending order at index %d/%d", i-1, i)
+				break
+			}
+		}
+	})
+
+	t.Run("respects limit", func(t *testing.T) {
+		results, err := db.ListRecentExecutions(now.Add(-time.Hour), 10, "")
+		if err != nil {
+			t.Fatalf("ListRecentExecutions: %v", err)
+		}
+		if len(results) != 10 {
+			t.Errorf("want 10, got %d", len(results))
+		}
+	})
+
+	t.Run("filters by since time", func(t *testing.T) {
+		// Only executions starting at index 50+ (minute 50 onward).
+		since := now.Add(50 * time.Minute)
+		results, err := db.ListRecentExecutions(since, 200, "")
+		if err != nil {
+			t.Fatalf("ListRecentExecutions: %v", err)
+		}
+		if len(results) != 50 {
+			t.Errorf("want 50 (indices 50–99), got %d", len(results))
+		}
+		for _, r := range results {
+			if r.StartedAt.Before(since) {
+				t.Errorf("result %q has start_time %v before since %v", r.ID, r.StartedAt, since)
+			}
+		}
+	})
+
+	t.Run("filters by task_id", func(t *testing.T) {
+		// task-a gets odd indices (1,3,5,...,99) = 50 executions.
+		results, err := db.ListRecentExecutions(now.Add(-time.Hour), 200, "re-task-a")
+		if err != nil {
+			t.Fatalf("ListRecentExecutions: %v", err)
+		}
+		if len(results) != 50 {
+			t.Errorf("want 50 for task-a, got %d", len(results))
+		}
+		for _, r := range results {
+			if r.TaskID != "re-task-a" {
+				t.Errorf("unexpected task_id %q in results", r.TaskID)
+			}
+		}
+	})
+}
+
 func TestGetTask_BackwardCompatibility(t *testing.T) {
 	db := testDB(t)
 	now := time.Now().UTC().Truncate(time.Second)
-- 
cgit v1.2.3