Merge branch 'main' into betterSqlite

- Replaced @libsql/client with better-sqlite3 for improved database handling.
- Updated database interactions in BaseService, MigrationService, and various repository classes to utilize better-sqlite3 methods.
- Added better-sqlite3 and its types to package.json and yarn.lock.
- Adjusted database connection logic and query execution to align with better-sqlite3 API.

package.json

@@ -77,7 +77,9 @@
     "release:aicore:alpha": "yarn workspace @cherrystudio/ai-core version prerelease --preid alpha --immediate && yarn workspace @cherrystudio/ai-core build && yarn workspace @cherrystudio/ai-core npm publish --tag alpha --access public",
     "release:aicore:beta": "yarn workspace @cherrystudio/ai-core version prerelease --preid beta --immediate && yarn workspace @cherrystudio/ai-core build && yarn workspace @cherrystudio/ai-core npm publish --tag beta --access public",
     "release:aicore": "yarn workspace @cherrystudio/ai-core version patch --immediate && yarn workspace @cherrystudio/ai-core build && yarn workspace @cherrystudio/ai-core npm publish --access public",
-    "release:ai-sdk-provider": "yarn workspace @cherrystudio/ai-sdk-provider version patch --immediate && yarn workspace @cherrystudio/ai-sdk-provider build && yarn workspace @cherrystudio/ai-sdk-provider npm publish --access public"
+    "release:ai-sdk-provider": "yarn workspace @cherrystudio/ai-sdk-provider version patch --immediate && yarn workspace @cherrystudio/ai-sdk-provider build && yarn workspace @cherrystudio/ai-sdk-provider npm publish --access public",
+    "rebuild": "electron-rebuild -f -w better-sqlite3",
+    "postinstall": "electron-builder install-app-deps"
   },
   "dependencies": {
     "@anthropic-ai/claude-agent-sdk": "patch:@anthropic-ai/claude-agent-sdk@npm%3A0.1.30#~/.yarn/patches/@anthropic-ai-claude-agent-sdk-npm-0.1.30-b50a299674.patch",
@@ -86,6 +88,7 @@
     "@napi-rs/system-ocr": "patch:@napi-rs/system-ocr@npm%3A1.0.2#~/.yarn/patches/@napi-rs-system-ocr-npm-1.0.2-59e7a78e8b.patch",
     "@paymoapp/electron-shutdown-handler": "^1.1.2",
     "@strongtz/win32-arm64-msvc": "^0.4.7",
+    "better-sqlite3": "12.4.1",
     "emoji-picker-element-data": "^1",
     "express": "^5.1.0",
     "font-list": "^2.0.0",
@@ -201,6 +204,7 @@
     "@tiptap/y-tiptap": "^3.0.0",
     "@truto/turndown-plugin-gfm": "^1.0.2",
     "@tryfabric/martian": "^1.2.4",
+    "@types/better-sqlite3": "^7.6.12",
     "@types/cli-progress": "^3",
     "@types/content-type": "^1.1.9",
     "@types/cors": "^2.8.19",

src/main/services/agents/BaseService.ts

@@ -1,11 +1,11 @@
-import { type Client, createClient } from '@libsql/client'
 import { loggerService } from '@logger'
 import { mcpApiService } from '@main/apiServer/services/mcp'
 import type { ModelValidationError } from '@main/apiServer/utils'
 import { validateModelId } from '@main/apiServer/utils'
 import type { AgentType, MCPTool, SlashCommand, Tool } from '@types'
 import { objectKeys } from '@types'
-import { drizzle, type LibSQLDatabase } from 'drizzle-orm/libsql'
+import Database from 'better-sqlite3'
+import { type BetterSQLite3Database, drizzle } from 'drizzle-orm/better-sqlite3'
 import fs from 'fs'
 import path from 'path'
 
@@ -32,8 +32,8 @@ const logger = loggerService.withContext('BaseService')
  * - Connection retry logic with exponential backoff
  */
 export abstract class BaseService {
-  protected static client: Client | null = null
-  protected static db: LibSQLDatabase<typeof schema> | null = null
+  protected static client: Database.Database | null = null
+  protected static db: BetterSQLite3Database<typeof schema> | null = null
   protected static isInitialized = false
   protected static initializationPromise: Promise<void> | null = null
   protected jsonFields: string[] = [
@@ -116,9 +116,7 @@ export abstract class BaseService {
           fs.mkdirSync(dbDir, { recursive: true })
         }
 
-        BaseService.client = createClient({
-          url: `file:${dbPath}`
-        })
+        BaseService.client = new Database(dbPath)
 
         BaseService.db = drizzle(BaseService.client, { schema })
 
@@ -165,12 +163,12 @@ export abstract class BaseService {
     }
   }
 
-  protected get database(): LibSQLDatabase<typeof schema> {
+  protected get database(): BetterSQLite3Database<typeof schema> {
     this.ensureInitialized()
     return BaseService.db!
   }
 
-  protected get rawClient(): Client {
+  protected get rawClient(): Database.Database {
     this.ensureInitialized()
     return BaseService.client!
   }

src/main/services/agents/database/MigrationService.ts

@@ -1,7 +1,7 @@
-import { type Client } from '@libsql/client'
 import { loggerService } from '@logger'
 import { getResourcePath } from '@main/utils'
-import { type LibSQLDatabase } from 'drizzle-orm/libsql'
+import type Database from 'better-sqlite3'
+import { type BetterSQLite3Database } from 'drizzle-orm/better-sqlite3'
 import fs from 'fs'
 import path from 'path'
 
@@ -23,11 +23,11 @@ interface MigrationJournal {
 }
 
 export class MigrationService {
-  private db: LibSQLDatabase<typeof schema>
-  private client: Client
+  private db: BetterSQLite3Database<typeof schema>
+  private client: Database.Database
   private migrationDir: string
 
-  constructor(db: LibSQLDatabase<typeof schema>, client: Client) {
+  constructor(db: BetterSQLite3Database<typeof schema>, client: Database.Database) {
     this.db = db
     this.client = client
     this.migrationDir = path.join(getResourcePath(), 'database', 'drizzle')
@@ -88,8 +88,8 @@ export class MigrationService {
 
   private async migrationsTableExists(): Promise<boolean> {
     try {
-      const table = await this.client.execute(`SELECT name FROM sqlite_master WHERE type='table' AND name='migrations'`)
-      return table.rows.length > 0
+      const rows = this.client.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='migrations'`).all()
+      return rows.length > 0
     } catch (error) {
       logger.error('Failed to check migrations table status:', { error })
       throw error
@@ -136,7 +136,7 @@ export class MigrationService {
 
       // Read and execute SQL
       const sqlContent = fs.readFileSync(sqlFilePath, 'utf-8')
-      await this.client.executeMultiple(sqlContent)
+      this.client.exec(sqlContent)
 
       // Record migration as applied (store journal idx as version for tracking)
       const newMigration: NewMigration = {

src/main/services/agents/database/sessionMessageRepository.ts

@@ -91,17 +91,18 @@ class AgentMessageRepository extends BaseService {
     return tx ?? this.database
   }
 
-  private async findExistingMessageRow(
+  private findExistingMessageRow(
     writer: TxClient,
     sessionId: string,
     role: string,
     messageId: string
-  ): Promise<SessionMessageRow | null> {
-    const candidateRows: SessionMessageRow[] = await writer
+  ): SessionMessageRow | null {
+    const candidateRows: SessionMessageRow[] = writer
       .select()
       .from(sessionMessagesTable)
       .where(and(eq(sessionMessagesTable.session_id, sessionId), eq(sessionMessagesTable.role, role)))
       .orderBy(asc(sessionMessagesTable.created_at))
+      .all()
 
     for (const row of candidateRows) {
       if (!row?.content) continue
@@ -119,12 +120,9 @@ class AgentMessageRepository extends BaseService {
     return null
   }
 
-  private async upsertMessage(
+  private upsertMessageSync(
     params: PersistUserMessageParams | PersistAssistantMessageParams
-  ): Promise<AgentSessionMessageEntity> {
-    await AgentMessageRepository.initialize()
-    this.ensureInitialized()
-
+  ): AgentSessionMessageEntity {
     const { sessionId, agentSessionId = '', payload, metadata, createdAt, tx } = params
 
     if (!payload?.message?.role) {
@@ -140,13 +138,13 @@ class AgentMessageRepository extends BaseService {
     const serializedPayload = this.serializeMessage(payload)
     const serializedMetadata = this.serializeMetadata(metadata)
 
-    const existingRow = await this.findExistingMessageRow(writer, sessionId, payload.message.role, payload.message.id)
+    const existingRow = this.findExistingMessageRow(writer, sessionId, payload.message.role, payload.message.id)
 
     if (existingRow) {
       const metadataToPersist = serializedMetadata ?? existingRow.metadata ?? undefined
       const agentSessionToPersist = agentSessionId || existingRow.agent_session_id || ''
 
-      await writer
+      writer
         .update(sessionMessagesTable)
         .set({
           content: serializedPayload,
@@ -155,6 +153,7 @@ class AgentMessageRepository extends BaseService {
           updated_at: now
         })
         .where(eq(sessionMessagesTable.id, existingRow.id))
+        .run()
 
       return this.deserialize({
         ...existingRow,
@@ -175,11 +174,19 @@ class AgentMessageRepository extends BaseService {
       updated_at: now
     }
 
-    const [saved] = await writer.insert(sessionMessagesTable).values(insertData).returning()
+    const [saved] = writer.insert(sessionMessagesTable).values(insertData).returning().all()
 
     return this.deserialize(saved)
   }
 
+  private async upsertMessage(
+    params: PersistUserMessageParams | PersistAssistantMessageParams
+  ): Promise<AgentSessionMessageEntity> {
+    await AgentMessageRepository.initialize()
+    this.ensureInitialized()
+    return this.upsertMessageSync(params)
+  }
+
   async persistUserMessage(params: PersistUserMessageParams): Promise<AgentSessionMessageEntity> {
     return this.upsertMessage({ ...params, agentSessionId: params.agentSessionId ?? '' })
   }
@@ -194,11 +201,11 @@ class AgentMessageRepository extends BaseService {
 
     const { sessionId, agentSessionId, user, assistant } = params
 
-    const result = await this.database.transaction(async (tx) => {
+    const result = this.database.transaction((tx) => {
       const exchangeResult: PersistExchangeResult = {}
 
       if (user?.payload) {
-        exchangeResult.userMessage = await this.persistUserMessage({
+        exchangeResult.userMessage = this.upsertMessageSync({
           sessionId,
           agentSessionId,
           payload: user.payload,
@@ -209,7 +216,7 @@ class AgentMessageRepository extends BaseService {
       }
 
       if (assistant?.payload) {
-        exchangeResult.assistantMessage = await this.persistAssistantMessage({
+        exchangeResult.assistantMessage = this.upsertMessageSync({
           sessionId,
           agentSessionId,
           payload: assistant.payload,

src/main/services/agents/drizzle.config.ts

@@ -24,7 +24,7 @@ export default defineConfig({
   schema: './src/main/services/agents/database/schema/index.ts',
   out: './resources/database/drizzle',
   dbCredentials: {
-    url: `file:${resolvedDbPath}`
+    url: resolvedDbPath
   },
   verbose: true,
   strict: true

src/main/services/agents/services/AgentService.ts

@@ -202,9 +202,9 @@ export class AgentService extends BaseService {
   async deleteAgent(id: string): Promise<boolean> {
     this.ensureInitialized()
 
-    const result = await this.database.delete(agentsTable).where(eq(agentsTable.id, id))
+    const result = this.database.delete(agentsTable).where(eq(agentsTable.id, id)).run()
 
-    return result.rowsAffected > 0
+    return result.changes > 0
   }
 
   async agentExists(id: string): Promise<boolean> {

src/main/services/agents/services/SessionMessageService.ts

@@ -148,11 +148,12 @@ export class SessionMessageService extends BaseService {
   async deleteSessionMessage(sessionId: string, messageId: number): Promise<boolean> {
     this.ensureInitialized()
 
-    const result = await this.database
+    const result = this.database
       .delete(sessionMessagesTable)
       .where(and(eq(sessionMessagesTable.id, messageId), eq(sessionMessagesTable.session_id, sessionId)))
+      .run()
 
-    return result.rowsAffected > 0
+    return result.changes > 0
   }
 
   async createSessionMessage(

src/main/services/agents/services/SessionService.ts

@@ -270,11 +270,12 @@ export class SessionService extends BaseService {
   async deleteSession(agentId: string, id: string): Promise<boolean> {
     this.ensureInitialized()
 
-    const result = await this.database
+    const result = this.database
       .delete(sessionsTable)
       .where(and(eq(sessionsTable.id, id), eq(sessionsTable.agent_id, agentId)))
+      .run()
 
-    return result.rowsAffected > 0
+    return result.changes > 0
   }
 
   async sessionExists(agentId: string, id: string): Promise<boolean> {

yarn.lock

@@ -8082,6 +8082,15 @@ __metadata:
   languageName: node
   linkType: hard
 
+"@types/better-sqlite3@npm:^7.6.12":
+  version: 7.6.13
+  resolution: "@types/better-sqlite3@npm:7.6.13"
+  dependencies:
+    "@types/node": "npm:*"
+  checksum: 10c0/c4336e7b92343eb0e988ded007c53fa9887b98a38d61175226e86124a1a2c28b1a4e3892873c5041e350b7bfa2901f85c82db1542c4f0eed1d3a899682c92106
+  languageName: node
+  linkType: hard
+
 "@types/body-parser@npm:*":
   version: 1.19.6
   resolution: "@types/body-parser@npm:1.19.6"
@@ -10000,6 +10009,7 @@ __metadata:
     "@tiptap/y-tiptap": "npm:^3.0.0"
     "@truto/turndown-plugin-gfm": "npm:^1.0.2"
     "@tryfabric/martian": "npm:^1.2.4"
+    "@types/better-sqlite3": "npm:^7.6.12"
     "@types/cli-progress": "npm:^3"
     "@types/content-type": "npm:^1.1.9"
     "@types/cors": "npm:^2.8.19"
@@ -10043,6 +10053,7 @@ __metadata:
     archiver: "npm:^7.0.1"
     async-mutex: "npm:^0.5.0"
     axios: "npm:^1.7.3"
+    better-sqlite3: "npm:12.4.1"
     browser-image-compression: "npm:^2.0.2"
     chardet: "npm:^2.1.0"
     check-disk-space: "npm:3.4.0"
@@ -10902,6 +10913,17 @@ __metadata:
   languageName: node
   linkType: hard
 
+"better-sqlite3@npm:12.4.1":
+  version: 12.4.1
+  resolution: "better-sqlite3@npm:12.4.1"
+  dependencies:
+    bindings: "npm:^1.5.0"
+    node-gyp: "npm:latest"
+    prebuild-install: "npm:^7.1.1"
+  checksum: 10c0/88773a75d996b4171e5690a38459b05dc814a792701b224bd9909ee084dc0b4c64aaffbdbcf4bbbc6d4e247faf19e91b2a56cf4175d746d3bd9ff14764eb05aa
+  languageName: node
+  linkType: hard
+
 "bignumber.js@npm:^9.0.0":
   version: 9.2.1
   resolution: "bignumber.js@npm:9.2.1"
@@ -10916,6 +10938,15 @@ __metadata:
   languageName: node
   linkType: hard
 
+"bindings@npm:^1.5.0":
+  version: 1.5.0
+  resolution: "bindings@npm:1.5.0"
+  dependencies:
+    file-uri-to-path: "npm:1.0.0"
+  checksum: 10c0/3dab2491b4bb24124252a91e656803eac24292473e56554e35bbfe3cc1875332cfa77600c3bac7564049dc95075bf6fcc63a4609920ff2d64d0fe405fcf0d4ba
+  languageName: node
+  linkType: hard
+
 "birecord@npm:^0.1.1":
   version: 0.1.1
   resolution: "birecord@npm:0.1.1"
@@ -14930,6 +14961,13 @@ __metadata:
   languageName: node
   linkType: hard
 
+"file-uri-to-path@npm:1.0.0":
+  version: 1.0.0
+  resolution: "file-uri-to-path@npm:1.0.0"
+  checksum: 10c0/3b545e3a341d322d368e880e1c204ef55f1d45cdea65f7efc6c6ce9e0c4d22d802d5629320eb779d006fe59624ac17b0e848d83cc5af7cd101f206cb704f5519
+  languageName: node
+  linkType: hard
+
 "filelist@npm:^1.0.4":
   version: 1.0.4
   resolution: "filelist@npm:1.0.4"
@@ -20660,7 +20698,7 @@ __metadata:
   languageName: node
   linkType: hard
 
-"prebuild-install@npm:^7.1.2":
+"prebuild-install@npm:^7.1.1, prebuild-install@npm:^7.1.2":
   version: 7.1.3
   resolution: "prebuild-install@npm:7.1.3"
   dependencies: