dashboard/app: add spanner Jobs.Correct field

Lay down foundation for spanner DB migrations by adding Jobs.Correct field.
This will allow us to test deployment of such changes.
The field will be used to record results of manual assessment of AI job results.

5 files changed, 36 insertions, 8 deletions

diff --git a/dashboard/app/ai_test.go b/dashboard/app/ai_test.go
index e378e77b4..86ede7901 100644
--- a/dashboard/app/ai_test.go
+++ b/dashboard/app/ai_test.go
@@ -19,9 +19,9 @@ func TestAIMigrations(t *testing.T) {
 	c := NewSpannerCtx(t)
 	defer c.Close()
 
-	up, err := loadDDLStatements("1_initialize.up.sql")
+	up, err := loadUpDDLStatements()
 	require.NoError(t, err)
-	down, err := loadDDLStatements("1_initialize.down.sql")
+	down, err := loadDownDDLStatements()
 	require.NoError(t, err)
 
 	require.NoError(t, executeSpannerDDL(c.ctx, down))
diff --git a/dashboard/app/aidb/entities.go b/dashboard/app/aidb/entities.go
index 49a69d0c4..23df884df 100644
--- a/dashboard/app/aidb/entities.go
+++ b/dashboard/app/aidb/entities.go
@@ -33,6 +33,7 @@ type Job struct {
 	Error        string // for finished jobs
 	Args         spanner.NullJSON
 	Results      spanner.NullJSON
+	Correct      spanner.NullBool
 }
 
 type TrajectorySpan struct {
diff --git a/dashboard/app/aidb/migrations/2_add_jobs_correct.down.sql b/dashboard/app/aidb/migrations/2_add_jobs_correct.down.sql
new file mode 100644
index 000000000..f19d39006
--- /dev/null
+++ b/dashboard/app/aidb/migrations/2_add_jobs_correct.down.sql
@@ -0,0 +1 @@
+ALTER TABLE Jobs DROP COLUMN Correct;
diff --git a/dashboard/app/aidb/migrations/2_add_jobs_correct.up.sql b/dashboard/app/aidb/migrations/2_add_jobs_correct.up.sql
new file mode 100644
index 000000000..0c0e2ae9b
--- /dev/null
+++ b/dashboard/app/aidb/migrations/2_add_jobs_correct.up.sql
@@ -0,0 +1 @@
+ALTER TABLE Jobs ADD COLUMN Correct BOOL;
diff --git a/dashboard/app/util_test.go b/dashboard/app/util_test.go
index 71a78a7f1..55d8acc91 100644
--- a/dashboard/app/util_test.go
+++ b/dashboard/app/util_test.go
@@ -20,6 +20,7 @@ import (
 	"path/filepath"
 	"reflect"
 	"runtime"
+	"slices"
 	"strings"
 	"sync"
 	"sync/atomic"
@@ -107,7 +108,7 @@ func newCtx(t *testing.T, appID string) *Ctx {
 var appIDSeq = uint32(0)
 
 func NewSpannerCtx(t *testing.T) *Ctx {
-	ddlStatements, err := loadDDLStatements("1_initialize.up.sql")
+	ddlStatements, err := loadUpDDLStatements()
 	if err != nil {
 		t.Fatal(err)
 	}
@@ -140,14 +141,38 @@ func executeSpannerDDL(ctx context.Context, statements []string) error {
 	return nil
 }
 
-func loadDDLStatements(file string) ([]string, error) {
-	data, err := os.ReadFile(filepath.Join("aidb", "migrations", file))
+func loadUpDDLStatements() ([]string, error) {
+	return loadDDLStatements("*.up.sql", 1)
+}
+
+func loadDownDDLStatements() ([]string, error) {
+	return loadDDLStatements("*.down.sql", -1)
+}
+
+func loadDDLStatements(wildcard string, sortOrder int) ([]string, error) {
+	files, err := filepath.Glob(filepath.Join("aidb", "migrations", wildcard))
 	if err != nil {
 		return nil, err
 	}
-	// We need individual statements. Assume semicolon is not used in other places than statements end.
-	statements := strings.Split(string(data), ";")
-	return statements[:len(statements)-1], nil
+	if len(files) == 0 {
+		return nil, fmt.Errorf("loadDDLStatements: wildcard did not match any files: %q", wildcard)
+	}
+	// We prefix DDL file names with sequence numbers.
+	slices.SortFunc(files, func(a, b string) int {
+		return strings.Compare(a, b) * sortOrder
+	})
+	var all []string
+	for _, file := range files {
+		data, err := os.ReadFile(file)
+		if err != nil {
+			return nil, err
+		}
+		// We need individual statements. Assume semicolon is not used in other places than statements end.
+		statements := strings.Split(string(data), ";")
+		statements = statements[:len(statements)-1]
+		all = append(all, statements...)
+	}
+	return all, nil
 }
 
 func (c *Ctx) config() *GlobalConfig {