Use upsert for notes to prevent UNIQUE constraint failure

andrew · andrew · commit a6562a6856b7 · 2026-03-05T16:45:35.000Z
InsertNote now uses ON CONFLICT(purl, namespace) DO UPDATE instead of
a plain INSERT. The cmd layer still checks for duplicates and shows the
"use --force" message, but this prevents failures from race conditions
or direct database API usage.
diff --git a/internal/database/database_test.go b/internal/database/database_test.go
@@ -655,6 +655,52 @@ func TestBatchWriterSharedCommitsMultiManifest(t *testing.T) {
 	}
 }
 
+func TestInsertNoteUpsert(t *testing.T) {
+	tmpDir := t.TempDir()
+	dbPath := filepath.Join(tmpDir, "pkgs.sqlite3")
+
+	db, err := database.Create(dbPath)
+	if err != nil {
+		t.Fatalf("failed to create database: %v", err)
+	}
+	defer func() { _ = db.Close() }()
+
+	note := database.Note{
+		PURL:      "pkg:npm/lodash@4.17.21",
+		Namespace: "default",
+		Message:   "first message",
+	}
+
+	// First insert should work
+	if err := db.InsertNote(note); err != nil {
+		t.Fatalf("first InsertNote failed: %v", err)
+	}
+
+	// Second insert with same purl+namespace should upsert, not fail
+	note.Message = "updated message"
+	if err := db.InsertNote(note); err != nil {
+		t.Fatalf("second InsertNote failed (should upsert): %v", err)
+	}
+
+	// Verify the note was updated
+	got, err := db.GetNote(note.PURL, note.Namespace)
+	if err != nil {
+		t.Fatalf("GetNote failed: %v", err)
+	}
+	if got.Message != "updated message" {
+		t.Errorf("expected 'updated message', got %q", got.Message)
+	}
+
+	// Verify only one note exists
+	notes, err := db.ListNotes("", "")
+	if err != nil {
+		t.Fatalf("ListNotes failed: %v", err)
+	}
+	if len(notes) != 1 {
+		t.Errorf("expected 1 note, got %d", len(notes))
+	}
+}
+
 func TestNewWriterClose(t *testing.T) {
 	tmpDir := t.TempDir()
 	dbPath := filepath.Join(tmpDir, "pkgs.sqlite3")
diff --git a/internal/database/queries.go b/internal/database/queries.go
@@ -2023,6 +2023,11 @@ func (db *DB) InsertNote(note Note) error {
 	_, err := db.Exec(`
 		INSERT INTO notes (purl, namespace, origin, message, metadata, created_at, updated_at)
 		VALUES (?, ?, ?, ?, ?, ?, ?)
+		ON CONFLICT(purl, namespace) DO UPDATE SET
+			message = excluded.message,
+			metadata = excluded.metadata,
+			origin = excluded.origin,
+			updated_at = excluded.updated_at
 	`, note.PURL, note.Namespace, origin, note.Message, metadataJSON, now, now)
 	return err
 }
