feat(db): add OpenTelemetry instrumentation for database queries

Instruments the better-sqlite3 driver so that every prepared statement
execution (run/get/all) produces an OTel span with db.system,
db.statement, and db.operation attributes. The instrumentation is a
no-op when no TracerProvider is registered (i.e. tracing is disabled).

https://claude.ai/code/session_01JZut7LqeHPUKAFbFLfVP8F

5 files changed, 83 insertions, 3 deletions

diff --git a/packages/db/drizzle.ts b/packages/db/drizzle.ts
index 42078b1b..cedd35f2 100644
--- a/packages/db/drizzle.ts
+++ b/packages/db/drizzle.ts
@@ -8,6 +8,7 @@ import { migrate } from "drizzle-orm/better-sqlite3/migrator";
 import serverConfig from "@karakeep/shared/config";
 
 import dbConfig from "./drizzle.config";
+import { instrumentDatabase } from "./instrumentation";
 import * as schema from "./schema";
 
 const sqlite = new Database(dbConfig.dbCredentials.url);
@@ -22,6 +23,8 @@ sqlite.pragma("cache_size = -65536");
 sqlite.pragma("foreign_keys = ON");
 sqlite.pragma("temp_store = MEMORY");
 
+instrumentDatabase(sqlite);
+
 export const db = drizzle(sqlite, { schema });
 export type DB = typeof db;
 
diff --git a/packages/db/instrumentation.ts b/packages/db/instrumentation.ts
new file mode 100644
index 00000000..fa5bcf94
--- /dev/null
+++ b/packages/db/instrumentation.ts
@@ -0,0 +1,67 @@
+import type Database from "better-sqlite3";
+import { SpanKind, SpanStatusCode, trace } from "@opentelemetry/api";
+
+const TRACER_NAME = "@karakeep/db";
+
+function getOperationType(sql: string): string {
+  return sql.trimStart().split(/\s/, 1)[0]?.toUpperCase() ?? "UNKNOWN";
+}
+
+/**
+ * Instruments a better-sqlite3 Database instance with OpenTelemetry tracing.
+ *
+ * Wraps `prepare()` so that every `run()`, `get()`, and `all()` call on
+ * the returned Statement produces an OTel span with db.system, db.statement,
+ * and db.operation attributes.
+ *
+ * The instrumentation is a no-op when no OTel TracerProvider is registered
+ * (i.e. when tracing is disabled), following standard OTel conventions.
+ */
+export function instrumentDatabase(
+  sqlite: Database.Database,
+): Database.Database {
+  const tracer = trace.getTracer(TRACER_NAME);
+  const origPrepare = sqlite.prepare.bind(sqlite);
+
+  sqlite.prepare = function (sql: string) {
+    const stmt = origPrepare(sql);
+    const operation = getOperationType(sql);
+    const spanName = `db.${operation.toLowerCase()}`;
+
+    for (const method of ["run", "get", "all"] as const) {
+      type QueryFn = (...args: unknown[]) => unknown;
+      const original = (stmt[method] as QueryFn).bind(stmt);
+      (stmt[method] as QueryFn) = function (...args: unknown[]) {
+        const span = tracer.startSpan(spanName, {
+          kind: SpanKind.CLIENT,
+          attributes: {
+            "db.system": "sqlite",
+            "db.statement": sql,
+            "db.operation": operation,
+          },
+        });
+
+        try {
+          const result = original(...args);
+          span.setStatus({ code: SpanStatusCode.OK });
+          return result;
+        } catch (error) {
+          span.setStatus({
+            code: SpanStatusCode.ERROR,
+            message: error instanceof Error ? error.message : String(error),
+          });
+          span.recordException(
+            error instanceof Error ? error : new Error(String(error)),
+          );
+          throw error;
+        } finally {
+          span.end();
+        }
+      };
+    }
+
+    return stmt;
+  } as typeof sqlite.prepare;
+
+  return sqlite;
+}
diff --git a/packages/db/package.json b/packages/db/package.json
index 5908f44a..80bb19ac 100644
--- a/packages/db/package.json
+++ b/packages/db/package.json
@@ -18,6 +18,7 @@
   "dependencies": {
     "@auth/core": "^0.27.0",
     "@karakeep/shared": "workspace:*",
+    "@opentelemetry/api": "^1.9.0",
     "@paralleldrive/cuid2": "^2.2.2",
     "better-sqlite3": "^11.3.0",
     "dotenv": "^16.4.1",
diff --git a/packages/shared-server/src/tracingTypes.ts b/packages/shared-server/src/tracingTypes.ts
index f397fa6f..f0926b97 100644
--- a/packages/shared-server/src/tracingTypes.ts
+++ b/packages/shared-server/src/tracingTypes.ts
@@ -29,6 +29,10 @@ export type TracingAttributeKey =
   | "crawler.getContentType.statusCode"
   | "crawler.contentType"
   | "crawler.statusCode"
+  // Database attributes
+  | "db.system"
+  | "db.statement"
+  | "db.operation"
   // Inference-specific attributes
   | "inference.tagging.numGeneratedTags"
   | "inference.tagging.style"
diff --git a/pnpm-lock.yaml b/pnpm-lock.yaml
index ba1f7bbd..e6e5c338 100644
--- a/pnpm-lock.yaml
+++ b/pnpm-lock.yaml
@@ -1160,6 +1160,9 @@ importers:
       '@karakeep/shared':
         specifier: workspace:*
         version: link:../shared
+      '@opentelemetry/api':
+        specifier: ^1.9.0
+        version: 1.9.0
       '@paralleldrive/cuid2':
         specifier: ^2.2.2
         version: 2.2.2
@@ -9316,11 +9319,13 @@ packages:
 
   glob@10.4.5:
     resolution: {integrity: sha512-7Bv8RF0k6xjo7d4A/PxYLbUCfb6c+Vpd2/mB2yRDlew7Jb5hEXiCD9ibfO7wpk8i4sevK6DFny9h7EYbM3/sHg==}
+    deprecated: Old versions of glob are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exorbitant rates) by contacting i@izs.me
     hasBin: true
 
   glob@11.0.2:
     resolution: {integrity: sha512-YT7U7Vye+t5fZ/QMkBFrTJ7ZQxInIUjwyAjVj84CYXqgBdv30MFUPGnBR6sQaVq6Is15wYJUsnzTuWaGRBhBAQ==}
     engines: {node: 20 || >=22}
+    deprecated: Old versions of glob are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exorbitant rates) by contacting i@izs.me
     hasBin: true
 
   glob@13.0.0:
@@ -9329,7 +9334,7 @@ packages:
 
   glob@7.2.3:
     resolution: {integrity: sha512-nFR0zLpU2YCaRxwoCJvL6UvCH2JFyFVIvwTLsIf21AuHlMskA1hhTdk+LlYJtOlYt9v6dvszD2BGRqBL+iQK9Q==}
-    deprecated: Glob versions prior to v9 are no longer supported
+    deprecated: Old versions of glob are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exorbitant rates) by contacting i@izs.me
 
   global-dirs@0.1.1:
     resolution: {integrity: sha512-NknMLn7F2J7aflwFOlGdNIuCDpN3VGoSoB+aap3KABFWbHVn1TCgFC+np23J8W2BiZbjfEw3BFBycSMv1AFblg==}
@@ -14407,12 +14412,12 @@ packages:
   tar@7.4.3:
     resolution: {integrity: sha512-5S7Va8hKfV7W5U6g3aYxXmlPoZVAwUMy9AOKyF2fVuZa2UD3qZjg578OrLRt8PcNN1PleVaL/5/yYATNL0ICUw==}
     engines: {node: '>=18'}
-    deprecated: Old versions of tar are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exhorbitant rates) by contacting i@izs.me
+    deprecated: Old versions of tar are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exorbitant rates) by contacting i@izs.me
 
   tar@7.5.3:
     resolution: {integrity: sha512-ENg5JUHUm2rDD7IvKNFGzyElLXNjachNLp6RaGf4+JOgxXHkqA+gq81ZAMCUmtMtqBsoU62lcp6S27g1LCYGGQ==}
     engines: {node: '>=18'}
-    deprecated: Old versions of tar are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exhorbitant rates) by contacting i@izs.me
+    deprecated: Old versions of tar are not supported, and contain widely publicized security vulnerabilities, which have been fixed in the current version. Please update. Support for old versions may be purchased (at exorbitant rates) by contacting i@izs.me
 
   tdigest@0.1.2:
     resolution: {integrity: sha512-+G0LLgjjo9BZX2MfdvPfH+MKLCrxlXSYec5DaPYP1fe6Iyhf0/fSmJ0bFiZ1F8BT6cGXl2LpltQptzjXKWEkKA==}