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

feat(db): add missing sqlite indices to help with performance
2026-03-25 05:10:12 +01:00 · 2026-03-22 09:22:55 -07:00 · 2026-03-22 09:14:33 -07:00
19 changed files with 525 additions and 626 deletions
--- a/.nvmrc
+++ b/.nvmrc
@@ -1 +1 @@
-24.14.1
+24.14.0
--- a/apps/build-docs/package.json
+++ b/apps/build-docs/package.json
@@ -22,7 +22,7 @@
    "js-yaml": "4.1.1",
    "react": "19.2.4",
    "react-dom": "19.2.4",
-    "typedoc": "0.28.18",
+    "typedoc": "0.28.17",
    "typedoc-plugin-missing-exports": "4.1.2"
  }
 }
--- a/apps/client/package.json
+++ b/apps/client/package.json
@@ -43,7 +43,7 @@
    "@univerjs/preset-sheets-note": "0.18.0",
    "@univerjs/preset-sheets-sort": "0.18.0",
    "@univerjs/presets": "0.18.0",
-    "@zumer/snapdom": "2.6.0",
+    "@zumer/snapdom": "2.5.0",
    "autocomplete.js": "0.38.1",
    "bootstrap": "5.3.8",
    "boxicons": "2.1.4",
@@ -53,7 +53,7 @@
    "draggabilly": "3.0.0",
    "force-graph": "1.51.2",
    "globals": "17.4.0",
-    "i18next": "25.10.5",
+    "i18next": "25.10.3",
    "i18next-http-backend": "3.0.2",
    "jquery": "4.0.0",
    "jquery.fancytree": "2.38.5",
@@ -68,7 +68,7 @@
    "normalize.css": "8.0.1",
    "panzoom": "9.4.3",
    "preact": "10.29.0",
-    "react-i18next": "16.6.2",
+    "react-i18next": "16.6.0",
    "react-window": "2.2.7",
    "reveal.js": "6.0.0",
    "rrule": "2.8.1",
@@ -86,9 +86,9 @@
    "@types/mark.js": "8.11.12",
    "@types/tabulator-tables": "6.3.1",
    "copy-webpack-plugin": "14.0.0",
-    "happy-dom": "20.8.7",
+    "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"
  }
 }
--- a/apps/client/src/translations/de/translation.json
+++ b/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>",
--- a/apps/client/src/translations/ga/translation.json
+++ b/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>",
--- a/apps/db-compare/package.json
+++ b/apps/db-compare/package.json
@@ -5,7 +5,7 @@
  "description": "Tool to compare content of Trilium databases. Useful for debugging sync problems.",
  "dependencies": {
    "colors": "1.4.0",
-    "diff": "8.0.4",
+    "diff": "8.0.3",
    "sqlite": "5.1.1",
    "sqlite3": "6.0.1"
  },
--- a/apps/server/package.json
+++ b/apps/server/package.json
@@ -99,7 +99,7 @@
    "html2plaintext": "2.1.4",
    "http-proxy-agent": "8.0.0",
    "https-proxy-agent": "8.0.0",
-    "i18next": "25.10.5",
+    "i18next": "25.10.3",
    "i18next-fs-backend": "2.6.1",
    "image-type": "6.0.0",
    "ini": "6.0.0",
@@ -126,7 +126,7 @@
    "tmp": "0.2.5",
    "turnish": "1.8.0",
    "unescape": "1.0.1",
-    "vite": "8.0.2",
+    "vite": "8.0.1",
    "ws": "8.20.0",
    "xml2js": "0.6.2",
    "yauzl": "3.2.1"
--- a/apps/server/src/assets/db/schema.sql
+++ b/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,
--- a/apps/server/src/migrations/migrations.ts
+++ b/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,
--- a/apps/server/src/services/app_info.ts
+++ b/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";

--- a/apps/website/package.json
+++ b/apps/website/package.json
@@ -9,12 +9,12 @@
 		"preview": "pnpm build && vite preview"
 	},
 	"dependencies": {
-		"i18next": "25.10.5",
+		"i18next": "25.10.3",
 		"i18next-http-backend": "3.0.2",
 		"preact": "10.29.0",
 		"preact-iso": "2.11.1",
 		"preact-render-to-string": "6.6.6",
-		"react-i18next": "16.6.2"
+		"react-i18next": "16.6.0"
 	},
 	"devDependencies": {
 		"@preact/preset-vite": "2.10.5",
@@ -22,8 +22,8 @@
 		"eslint-config-preact": "2.0.0",
 		"typescript": "5.9.3",
 		"user-agent-data-types": "0.4.2",
-		"vite": "8.0.2",
-		"vitest": "4.1.1"
+		"vite": "8.0.1",
+		"vitest": "4.1.0"
 	},
 	"eslintConfig": {
 		"extends": "preact"
--- a/package.json
+++ b/package.json
@@ -52,9 +52,9 @@
    "@types/express": "5.0.6",
    "@types/js-yaml": "4.0.9",
    "@types/node": "24.12.0",
-    "@vitest/browser-webdriverio": "4.1.1",
-    "@vitest/coverage-v8": "4.1.1",
-    "@vitest/ui": "4.1.1",
+    "@vitest/browser-webdriverio": "4.1.0",
+    "@vitest/coverage-v8": "4.1.0",
+    "@vitest/ui": "4.1.0",
    "chalk": "5.6.2",
    "cross-env": "10.1.0",
    "dpdm": "4.0.1",
@@ -64,7 +64,7 @@
    "eslint-config-prettier": "10.1.8",
    "eslint-plugin-playwright": "2.10.1",
    "eslint-plugin-simple-import-sort": "12.1.1",
-    "happy-dom": "20.8.7",
+    "happy-dom": "20.8.4",
    "http-server": "14.1.1",
    "jiti": "2.6.1",
    "js-yaml": "4.1.1",
@@ -74,11 +74,11 @@
    "tslib": "2.8.1",
    "tsx": "4.21.0",
    "typescript": "5.9.3",
-    "typescript-eslint": "8.57.2",
+    "typescript-eslint": "8.57.1",
    "upath": "2.0.1",
-    "vite": "8.0.2",
+    "vite": "8.0.1",
    "vite-plugin-dts": "4.5.4",
-    "vitest": "4.1.1"
+    "vitest": "4.1.0"
  },
  "license": "AGPL-3.0-only",
  "author": {
--- a/packages/ckeditor5-admonition/package.json
+++ b/packages/ckeditor5-admonition/package.json
@@ -24,10 +24,10 @@
    "@ckeditor/ckeditor5-dev-build-tools": "55.2.0",
    "@ckeditor/ckeditor5-inspector": ">=4.1.0",
    "@ckeditor/ckeditor5-package-tools": "5.1.0",
-    "@typescript-eslint/eslint-plugin": "8.57.2",
-    "@typescript-eslint/parser": "8.57.2",
-    "@vitest/browser": "4.1.1",
-    "@vitest/coverage-istanbul": "4.1.1",
+    "@typescript-eslint/eslint-plugin": "8.57.1",
+    "@typescript-eslint/parser": "8.57.1",
+    "@vitest/browser": "4.1.0",
+    "@vitest/coverage-istanbul": "4.1.0",
    "ckeditor5": "47.6.1",
    "eslint": "10.1.0",
    "eslint-config-ckeditor5": ">=9.1.0",
@@ -38,8 +38,8 @@
    "ts-node": "10.9.2",
    "typescript": "5.9.3",
    "vite-plugin-svgo": "2.0.0",
-    "vitest": "4.1.1",
-    "webdriverio": "9.27.0"
+    "vitest": "4.1.0",
+    "webdriverio": "9.26.1"
  },
  "peerDependencies": {
    "ckeditor5": "47.6.1"
--- a/packages/ckeditor5-footnotes/package.json
+++ b/packages/ckeditor5-footnotes/package.json
@@ -25,10 +25,10 @@
    "@ckeditor/ckeditor5-dev-build-tools": "55.2.0",
    "@ckeditor/ckeditor5-inspector": ">=4.1.0",
    "@ckeditor/ckeditor5-package-tools": "5.1.0",
-    "@typescript-eslint/eslint-plugin": "8.57.2",
-    "@typescript-eslint/parser": "8.57.2",
-    "@vitest/browser": "4.1.1",
-    "@vitest/coverage-istanbul": "4.1.1",
+    "@typescript-eslint/eslint-plugin": "8.57.1",
+    "@typescript-eslint/parser": "8.57.1",
+    "@vitest/browser": "4.1.0",
+    "@vitest/coverage-istanbul": "4.1.0",
    "ckeditor5": "47.6.1",
    "eslint": "10.1.0",
    "eslint-config-ckeditor5": ">=9.1.0",
@@ -39,8 +39,8 @@
    "ts-node": "10.9.2",
    "typescript": "5.9.3",
    "vite-plugin-svgo": "2.0.0",
-    "vitest": "4.1.1",
-    "webdriverio": "9.27.0"
+    "vitest": "4.1.0",
+    "webdriverio": "9.26.1"
  },
  "peerDependencies": {
    "ckeditor5": "47.6.1"
--- a/packages/ckeditor5-keyboard-marker/package.json
+++ b/packages/ckeditor5-keyboard-marker/package.json
@@ -27,10 +27,10 @@
    "@ckeditor/ckeditor5-dev-build-tools": "55.2.0",
    "@ckeditor/ckeditor5-inspector": ">=4.1.0",
    "@ckeditor/ckeditor5-package-tools": "5.1.0",
-    "@typescript-eslint/eslint-plugin": "8.57.2",
-    "@typescript-eslint/parser": "8.57.2",
-    "@vitest/browser": "4.1.1",
-    "@vitest/coverage-istanbul": "4.1.1",
+    "@typescript-eslint/eslint-plugin": "8.57.1",
+    "@typescript-eslint/parser": "8.57.1",
+    "@vitest/browser": "4.1.0",
+    "@vitest/coverage-istanbul": "4.1.0",
    "ckeditor5": "47.6.1",
    "eslint": "10.1.0",
    "eslint-config-ckeditor5": ">=9.1.0",
@@ -41,8 +41,8 @@
    "ts-node": "10.9.2",
    "typescript": "5.9.3",
    "vite-plugin-svgo": "2.0.0",
-    "vitest": "4.1.1",
-    "webdriverio": "9.27.0"
+    "vitest": "4.1.0",
+    "webdriverio": "9.26.1"
  },
  "peerDependencies": {
    "ckeditor5": "47.6.1"
--- a/packages/ckeditor5-math/package.json
+++ b/packages/ckeditor5-math/package.json
@@ -27,10 +27,10 @@
    "@ckeditor/ckeditor5-dev-build-tools": "55.2.0",
    "@ckeditor/ckeditor5-inspector": ">=4.1.0",
    "@ckeditor/ckeditor5-package-tools": "5.1.0",
-    "@typescript-eslint/eslint-plugin": "8.57.2",
-    "@typescript-eslint/parser": "8.57.2",
-    "@vitest/browser": "4.1.1",
-    "@vitest/coverage-istanbul": "4.1.1",
+    "@typescript-eslint/eslint-plugin": "8.57.1",
+    "@typescript-eslint/parser": "8.57.1",
+    "@vitest/browser": "4.1.0",
+    "@vitest/coverage-istanbul": "4.1.0",
    "ckeditor5": "47.6.1",
    "eslint": "10.1.0",
    "eslint-config-ckeditor5": ">=9.1.0",
@@ -41,8 +41,8 @@
    "ts-node": "10.9.2",
    "typescript": "5.9.3",
    "vite-plugin-svgo": "2.0.0",
-    "vitest": "4.1.1",
-    "webdriverio": "9.27.0"
+    "vitest": "4.1.0",
+    "webdriverio": "9.26.1"
  },
  "peerDependencies": {
    "ckeditor5": "47.6.1"
--- a/packages/ckeditor5-mermaid/package.json
+++ b/packages/ckeditor5-mermaid/package.json
@@ -27,10 +27,10 @@
    "@ckeditor/ckeditor5-dev-build-tools": "55.2.0",
    "@ckeditor/ckeditor5-inspector": ">=4.1.0",
    "@ckeditor/ckeditor5-package-tools": "5.1.0",
-    "@typescript-eslint/eslint-plugin": "8.57.2",
-    "@typescript-eslint/parser": "8.57.2",
-    "@vitest/browser": "4.1.1",
-    "@vitest/coverage-istanbul": "4.1.1",
+    "@typescript-eslint/eslint-plugin": "8.57.1",
+    "@typescript-eslint/parser": "8.57.1",
+    "@vitest/browser": "4.1.0",
+    "@vitest/coverage-istanbul": "4.1.0",
    "ckeditor5": "47.6.1",
    "eslint": "10.1.0",
    "eslint-config-ckeditor5": ">=9.1.0",
@@ -41,8 +41,8 @@
    "ts-node": "10.9.2",
    "typescript": "5.9.3",
    "vite-plugin-svgo": "2.0.0",
-    "vitest": "4.1.1",
-    "webdriverio": "9.27.0"
+    "vitest": "4.1.0",
+    "webdriverio": "9.26.1"
  },
  "peerDependencies": {
    "ckeditor5": "47.6.1"
--- a/packages/share-theme/package.json
+++ b/packages/share-theme/package.json
@@ -31,8 +31,8 @@
  "devDependencies": {
    "@digitak/esrun": "3.2.26",
    "@triliumnext/ckeditor5": "workspace:*",
-    "@typescript-eslint/eslint-plugin": "8.57.2",
-    "@typescript-eslint/parser": "8.57.2",
+    "@typescript-eslint/eslint-plugin": "8.57.1",
+    "@typescript-eslint/parser": "8.57.1",
    "dotenv": "17.3.1",
    "esbuild": "0.27.4",
    "eslint": "10.1.0",
--- a/pnpm-lock.yaml
+++ b/pnpm-lock.yaml
Author	SHA1	Message	Date
perfectra1n	81f02209ea	feat(db): update index and fix suggestion from gemini	2026-03-22 09:22:55 -07:00
perfectra1n	124d456c60	feat(db): add missing sqlite indices to help with performance	2026-03-22 09:14:33 -07:00
@@ -1 +1 @@
 .14.1
 .14.0