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

3 files changed, 71 insertions, 0 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",