feat(db): update index and fix suggestion from gemini

apps/server/src/assets/db/schema.sql

@@ -79,7 +79,7 @@ CREATE UNIQUE INDEX `IDX_entityChanges_entityName_entityId` ON "entity_changes"
                                                                                  `entityId`
     );
 CREATE INDEX `IDX_branches_noteId_parentNoteId` ON `branches` (`noteId`,`parentNoteId`);
-CREATE INDEX IDX_branches_parentNoteId ON branches (parentNoteId);
+CREATE INDEX IDX_branches_parentNoteId_isDeleted_notePosition ON branches (parentNoteId, isDeleted, notePosition);
 CREATE INDEX `IDX_notes_title` ON `notes` (`title`);
 CREATE INDEX `IDX_notes_type` ON `notes` (`type`);
 CREATE INDEX `IDX_notes_dateCreated` ON `notes` (`dateCreated`);
@@ -146,6 +146,13 @@ CREATE INDEX IDX_notes_blobId on notes (blobId);
 CREATE INDEX IDX_revisions_blobId on revisions (blobId);
 CREATE INDEX IDX_attachments_blobId on attachments (blobId);
 
+CREATE INDEX IDX_entity_changes_isSynced_id ON entity_changes (isSynced, id);
+CREATE INDEX IDX_entity_changes_isErased_entityName ON entity_changes (isErased, entityName);
+CREATE INDEX IDX_notes_isDeleted_utcDateModified ON notes (isDeleted, utcDateModified);
+CREATE INDEX IDX_branches_isDeleted_utcDateModified ON branches (isDeleted, utcDateModified);
+CREATE INDEX IDX_attributes_isDeleted_utcDateModified ON attributes (isDeleted, utcDateModified);
+CREATE INDEX IDX_attachments_isDeleted_utcDateModified ON attachments (isDeleted, utcDateModified);
+CREATE INDEX IDX_attachments_utcDateScheduledForErasureSince ON attachments (utcDateScheduledForErasureSince);
 
 CREATE TABLE IF NOT EXISTS sessions (
     id TEXT PRIMARY KEY,

apps/server/src/migrations/migrations.ts

@@ -6,6 +6,27 @@
 
 // Migrations should be kept in descending order, so the latest migration is first.
 const MIGRATIONS: (SqlMigration | JsMigration)[] = [
+    // Add missing database indices for query performance
+    {
+        version: 235,
+        sql: /*sql*/`
+            CREATE INDEX IF NOT EXISTS IDX_entity_changes_isSynced_id
+                ON entity_changes (isSynced, id);
+            CREATE INDEX IF NOT EXISTS IDX_entity_changes_isErased_entityName
+                ON entity_changes (isErased, entityName);
+            CREATE INDEX IF NOT EXISTS IDX_notes_isDeleted_utcDateModified
+                ON notes (isDeleted, utcDateModified);
+            CREATE INDEX IF NOT EXISTS IDX_branches_isDeleted_utcDateModified
+                ON branches (isDeleted, utcDateModified);
+            CREATE INDEX IF NOT EXISTS IDX_attributes_isDeleted_utcDateModified
+                ON attributes (isDeleted, utcDateModified);
+            CREATE INDEX IF NOT EXISTS IDX_attachments_isDeleted_utcDateModified
+                ON attachments (isDeleted, utcDateModified);
+            DROP INDEX IF EXISTS IDX_branches_parentNoteId;
+            CREATE INDEX IF NOT EXISTS IDX_branches_parentNoteId_isDeleted_notePosition
+                ON branches (parentNoteId, isDeleted, notePosition);
+        `
+    },
     // Migrate aiChat notes to code notes since LLM integration has been removed
     {
         version: 234,

apps/server/src/services/app_info.ts

@@ -5,7 +5,7 @@ import packageJson from "../../package.json" with { type: "json" };
 import build from "./build.js";
 import dataDir from "./data_dir.js";
 
-const APP_DB_VERSION = 234;
+const APP_DB_VERSION = 235;
 const SYNC_VERSION = 37;
 const CLIPPER_PROTOCOL_VERSION = "1.0";