fix(index): a renamed table is a moved store too

Carrying the store FILE over was only half the rename. The tables inside it were
renamed in the same change (search_indexes/search_docs/search_terms ->
indexes/docs/terms), so migrate() created empty tables beside the populated ones
and the index read as empty while every document sat intact one identifier away.
Verified in prod: after the file moved, GET /v1/index/indexes returned nothing.

migrate() now adopts rows out of the previous names and drops them. Idempotent —
a missing table is skipped and an adopted one is dropped, so a later boot has
nothing to resurrect. INSERT OR IGNORE, so rows already written under the current
names win and the migration never overwrites live data with older rows.

The lesson is the same one the .dek taught an hour earlier: a store is the file,
its key, AND the names its rows live under. Move all three or lose the data.

Co-authored-by: Hanzo Dev <dev@hanzo.ai>

clients/index/index_test.go

@@ -7,6 +7,7 @@ import (
 	"path/filepath"
 	"strings"
 	"testing"
+	"time"
 )
 
 // newStore opens a throwaway store on a temp file. It exercises the real
@@ -743,3 +744,72 @@ func TestMigrateStoreIsIdempotentAndSafe(t *testing.T) {
 		}
 	})
 }
+
+// TestAdoptLegacyTables proves a store whose TABLES were renamed still yields its
+// documents. Moving the store file is not enough on its own: the rows stay under
+// the previous table names, nothing queries them, and the index reads as empty
+// while every document sits intact one identifier away.
+func TestAdoptLegacyTables(t *testing.T) {
+	dir := t.TempDir()
+	path := filepath.Join(dir, "index.db")
+
+	// A store in the PREVIOUS schema: same columns, previous table names.
+	first, err := openStore(path)
+	if err != nil {
+		t.Fatalf("openStore: %v", err)
+	}
+	for cur, prev := range map[string]string{"indexes": "search_indexes", "docs": "search_docs", "terms": "search_terms"} {
+		if _, err := first.db.Exec(`ALTER TABLE ` + cur + ` RENAME TO ` + prev); err != nil {
+			t.Fatalf("stage %s: %v", cur, err)
+		}
+	}
+	now := time.Now().Unix()
+	if _, err := first.db.Exec(
+		`INSERT INTO search_indexes(org, uid, primary_key, filterable, created_at, updated_at)
+		 VALUES('acme','convos','id','["user"]',?,?)`, now, now); err != nil {
+		t.Fatalf("stage index row: %v", err)
+	}
+	if _, err := first.db.Exec(
+		`INSERT INTO search_docs(org, uid, pk, usr, doc)
+		 VALUES('acme','convos','1','u1','{"id":"1","title":"kubernetes migration"}')`); err != nil {
+		t.Fatalf("stage doc row: %v", err)
+	}
+	if _, err := first.db.Exec(
+		`INSERT INTO search_terms(org, uid, term, pk) VALUES('acme','convos','kubernetes','1')`); err != nil {
+		t.Fatalf("stage term row: %v", err)
+	}
+	if err := first.Close(); err != nil {
+		t.Fatalf("close: %v", err)
+	}
+
+	// Reopening runs migrate(), which must adopt those rows.
+	s, err := openStore(path)
+	if err != nil {
+		t.Fatalf("reopen: %v", err)
+	}
+	t.Cleanup(func() { _ = s.Close() })
+	ctx := context.Background()
+
+	if idx, err := s.Index(ctx, "acme", "convos"); err != nil {
+		t.Errorf("index did not survive the table rename: %v", err)
+	} else if idx.PrimaryKey != "id" {
+		t.Errorf("primary key = %q, want id", idx.PrimaryKey)
+	}
+	hits, err := s.Search(ctx, "acme", "convos", "kubernetes", nil, 10, 0)
+	if err != nil {
+		t.Fatalf("search: %v", err)
+	}
+	if len(hits) != 1 {
+		t.Errorf("documents did not survive the table rename: %d hits", len(hits))
+	}
+
+	// The previous tables are gone, so a later boot has nothing left to adopt
+	// and cannot resurrect rows that were since deleted.
+	for _, prev := range []string{"search_indexes", "search_docs", "search_terms"} {
+		var n string
+		if err := s.db.QueryRow(
+			`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, prev).Scan(&n); err == nil {
+			t.Errorf("%s still exists after adoption", prev)
+		}
+	}
+}

clients/index/store.go

@@ -157,6 +157,45 @@ CREATE INDEX IF NOT EXISTS ix_terms_pk ON terms(org, uid, pk);
 	if _, err := s.db.Exec(ddl); err != nil {
 		return fmt.Errorf("index migrate: %w", err)
 	}
+	return s.adoptLegacyTables()
+}
+
+// legacyTables maps a table's previous name to its current one. Moving the store
+// FILE is not enough when the tables inside it were renamed too: the rows would
+// still be there, under names nothing queries, and the index would read as empty
+// while every document sat intact one identifier away.
+var legacyTables = map[string]string{
+	"search_indexes": "indexes",
+	"search_docs":    "docs",
+	"search_terms":   "terms",
+}
+
+// adoptLegacyTables carries rows out of a previous schema's tables and drops
+// them. Idempotent: a table that is not there is skipped, and one that is gets
+// emptied by the DROP, so a second boot has nothing left to adopt. INSERT OR
+// IGNORE means rows already written under the current names win — the migration
+// never overwrites live data with older rows.
+func (s *Store) adoptLegacyTables() error {
+	for previous, current := range legacyTables {
+		var name string
+		err := s.db.QueryRow(
+			`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, previous).Scan(&name)
+		if errors.Is(err, sql.ErrNoRows) {
+			continue
+		}
+		if err != nil {
+			return fmt.Errorf("index migrate: look for %s: %w", previous, err)
+		}
+		// The column lists are identical across the rename, so an unqualified
+		// INSERT…SELECT is exact.
+		if _, err := s.db.Exec(
+			`INSERT OR IGNORE INTO ` + current + ` SELECT * FROM ` + previous); err != nil {
+			return fmt.Errorf("index migrate: adopt %s into %s: %w", previous, current, err)
+		}
+		if _, err := s.db.Exec(`DROP TABLE ` + previous); err != nil {
+			return fmt.Errorf("index migrate: drop %s: %w", previous, err)
+		}
+	}
 	return nil
 }