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

apps/client/package.json

@@ -89,6 +89,6 @@
     "happy-dom": "20.8.4",
     "lightningcss": "1.32.0",
     "script-loader": "0.7.2",
-    "vite-plugin-static-copy": "3.4.0"
+    "vite-plugin-static-copy": "3.3.0"
   }
 }

apps/client/src/translations/de/translation.json

@@ -446,8 +446,7 @@
     "and_more": "... und {{count}} mehr.",
     "print_landscape": "Beim Export als PDF, wird die Seitenausrichtung Querformat anstatt Hochformat verwendet.",
     "print_page_size": "Beim Export als PDF, wird die Größe der Seite angepasst. Unterstützte Größen: <code>A0</code>, <code>A1</code>, <code>A2</code>, <code>A3</code>, <code>A4</code>, <code>A5</code>, <code>A6</code>, <code>Legal</code>, <code>Letter</code>, <code>Tabloid</code>, <code>Ledger</code>.",
-    "color_type": "Farbe",
-    "textarea": "Mehrzeilen-Text"
+    "color_type": "Farbe"
   },
   "attribute_editor": {
     "help_text_body1": "Um ein Label hinzuzufügen, gebe einfach z.B. ein. <code>#rock</code> oder wenn du auch einen Wert hinzufügen möchten, dann z.B. <code>#year = 2024</code>",

apps/client/src/translations/ga/translation.json

@@ -477,8 +477,7 @@
     "and_more": "... agus {{count}} eile.",
     "print_landscape": "Agus é á onnmhairiú go PDF, athraítear treoshuíomh an leathanaigh go tírdhreach seachas portráid.",
     "print_page_size": "Agus é á easpórtáil go PDF, athraítear méid an leathanaigh. Luachanna tacaithe: <code>A0</code>, <code>A1</code>, <code>A2</code>, <code>A3</code>, <code>A4</code>, <code>A5</code>, <code>A6</code>, <code>Legal</code>, <code>Letter</code>, <code>Tabloid</code>, <code>Ledger</code>.",
-    "color_type": "Dath",
-    "textarea": "Téacs Il-líne"
+    "color_type": "Dath"
   },
   "attribute_editor": {
     "help_text_body1": "Chun lipéad a chur leis, clóscríobh m.sh. <code>#rock</code> nó más mian leat luach a chur leis freisin ansin m.sh. <code>#year = 2020</code>",

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";
 

pnpm-lock.yaml

@@ -400,8 +400,8 @@ importers:
         specifier: 0.7.2
         version: 0.7.2
       vite-plugin-static-copy:
-        specifier: 3.4.0
-        version: 3.4.0(vite@8.0.1(@types/node@24.12.0)(esbuild@0.27.4)(jiti@2.6.1)(less@4.1.3)(sass-embedded@1.91.0)(sass@1.91.0)(terser@5.44.0)(tsx@4.21.0)(yaml@2.8.2))
+        specifier: 3.3.0
+        version: 3.3.0(vite@8.0.1(@types/node@24.12.0)(esbuild@0.27.4)(jiti@2.6.1)(less@4.1.3)(sass-embedded@1.91.0)(sass@1.91.0)(terser@5.44.0)(tsx@4.21.0)(yaml@2.8.2))
 
   apps/db-compare:
     dependencies:
@@ -15264,8 +15264,8 @@ packages:
       vite:
         optional: true
 
-  vite-plugin-static-copy@3.4.0:
-    resolution: {integrity: sha512-ekryzCw0ouAOE8tw4RvVL/dfqguXzumsV3FBKoKso4MQ1MUUrUXtl5RI4KpJQUNGqFEsg9kxl4EvDl02YtA9VQ==}
+  vite-plugin-static-copy@3.3.0:
+    resolution: {integrity: sha512-XiAtZcev7nppxNFgKoD55rfL+ukVp/RtrnTJONRwRuzv/B2FK2h2ZRCYjvxhwBV/Oarse83SiyXBSxMTfeEM0Q==}
     engines: {node: ^18.0.0 || >=20.0.0}
     peerDependencies:
       vite: ^5.0.0 || ^6.0.0 || ^7.0.0 || ^8.0.0
@@ -34045,7 +34045,7 @@ snapshots:
       - rollup
       - supports-color
 
-  vite-plugin-static-copy@3.4.0(vite@8.0.1(@types/node@24.12.0)(esbuild@0.27.4)(jiti@2.6.1)(less@4.1.3)(sass-embedded@1.91.0)(sass@1.91.0)(terser@5.44.0)(tsx@4.21.0)(yaml@2.8.2)):
+  vite-plugin-static-copy@3.3.0(vite@8.0.1(@types/node@24.12.0)(esbuild@0.27.4)(jiti@2.6.1)(less@4.1.3)(sass-embedded@1.91.0)(sass@1.91.0)(terser@5.44.0)(tsx@4.21.0)(yaml@2.8.2)):
     dependencies:
       chokidar: 3.6.0
       p-map: 7.0.4