Database

Elftia uses better-sqlite3 + Drizzle ORM as its local database solution. All database operations execute asynchronously through a Worker Thread.

Technology Choices

Item	Choice	Reason
Database engine	better-sqlite3	Embedded, zero-config, high performance
ORM	Drizzle ORM	Type-safe, zero runtime overhead, SQL-like API
Mode	WAL (Write-Ahead Logging)	Supports concurrent reads; writes do not block reads
Access pattern	Worker Thread RPC	Avoids blocking the main thread

Database Location

{userData}/elftia.db          # Main database file
{userData}/elftia.db-wal      # WAL log
{userData}/elftia.db-shm      # Shared memory file

{userData} paths by platform:

Windows: %APPDATA%/elftia/
macOS: ~/Library/Application Support/elftia/
Linux: ~/.config/elftia/

Worker Thread Architecture

All database operations execute through the DbClient -> db.worker.ts RPC pattern:

sequenceDiagram
    participant S as Service Layer
    participant C as DbClient (main thread)
    participant W as db.worker.ts (Worker Thread)
    participant DB as SQLite (WAL)

    Note over S,DB: Initialization
    C->>W: Create Worker({dbPath})
    W->>DB: new Database(dbPath)
    W->>DB: PRAGMA journal_mode = WAL
    W->>DB: drizzle(db) + migrate()
    W-->>C: ready signal

    Note over S,DB: Normal operation
    S->>C: db.chatSessionsList()
    C->>C: id = ++seq
    C->>W: postMessage({id, method: 'chatSessions:list', params})
    W->>DB: SELECT * FROM chat_sessions ...
    DB-->>W: Result rows
    W-->>C: postMessage({id, result: rows})
    C->>C: pending[id].resolve(rows)
    C-->>S: Promise<Session[]>

DbClient Core Implementation

{/* packages/desktop/app/main/workers/DbClient.ts */}
class DbClient extends EventEmitter implements DbRpc {
  private worker: Worker;
  private seq = 0;
  private pending = Map<number, { resolve, reject }>;

  constructor(dbPath: string, options?: DbClientOptions) {
    this.worker = new Worker(workerPath, {
      workerData: { dbPath, diagnosticsDir },
    });
    this.worker.on('message', (msg) => this.handleMessage(msg));
  }

  async ready(): Promise<void>;

  private async send(method: string, params: unknown): Promise<unknown> {
    const id = ++this.seq;
    return new Promise((resolve, reject) => {
      this.pending.set(id, { resolve, reject });
      this.worker.postMessage({ id, method, params });
    });
  }

  // 100+ typed methods
  chatSessionsList(): Promise<ChatSession[]>;
  chatMessagesInsert(msg: ChatMessageInput): Promise<void>;
  auditLogInsert(entry: AuditLogEntry): Promise<void>;
}

RPC Type Definitions

{/* packages/desktop/app/main/workers/types.ts */}
interface DbRequest {
  id: number;
  method: string;
  params: unknown;
}

interface DbResponse {
  id: number;
  result?: unknown;
  error?: { message: string; stack?: string };
}

interface DbRpc {
  chatSessionsList(): Promise<ChatSession[]>;
  chatSessionsGet(id: string): Promise<ChatSession | null>;
  chatSessionsCreate(input: ChatSessionInput): Promise<ChatSession>;
  chatSessionsUpdate(id: string, data: Partial<ChatSession>): Promise<void>;
  chatSessionsDelete(id: string): Promise<void>;
  // ... 100+ methods
}

Schema Organization

The database schema is defined using Drizzle ORM, located in packages/desktop/app/main/db/schema/:

packages/desktop/app/main/db/schema/
├── index.ts          # Unified export
├── accounts.ts       # User accounts
├── chat.ts           # Chat messages
├── sessions.ts       # Chat sessions
├── llm.ts            # LLM providers/models/keys
├── projects.ts       # Projects
├── settings.ts       # Application settings
├── attachments.ts    # Attachments
├── media.ts          # Media resources
└── templates.ts      # Templates

Core Data Tables

{/* Simplified table structure definitions */}

// Chat session
interface ChatSession {
  id: string;                // UUID
  title: string;             // Session title
  type: 'chat' | 'roleplay' | 'agent';
  agentId?: string;          // Associated Agent ID
  personaId?: string;        // Associated Persona ID
  providerId?: string;       // LLM provider ID
  modelId?: string;          // Model ID
  rpConfig?: RPConfig;       // RP config (JSON)
  createdAt: number;
  updatedAt: number;
  lastMessageAt?: number;
  messageCount: number;
  pinned: boolean;
  folderId?: string;
}

// Chat message
interface ChatMessage {
  id: string;                // UUID
  sessionId: string;         // Owning session ID
  role: 'user' | 'assistant' | 'system';
  content: string;           // Message content
  parentId?: string;         // Parent message ID (branch support)
  reasoning?: string;        // Reasoning content (thinking)
  toolCalls?: ToolCall[];    // Tool calls (JSON)
  meta?: MessageMeta;        // Metadata (providerId, modelId, tokenCount)
  createdAt: number;
}

// User account
interface Account {
  id: string;
  email: string;
  displayName?: string;
  avatarUrl?: string;
  createdAt: number;
}

// Account token
interface AccountToken {
  id: string;
  accountId: string;
  provider: string;          // OAuth provider
  accessToken: string;       // Encrypted at rest
  refreshToken?: string;
  expiresAt?: number;
}

LLM Configuration

// LLM provider
interface LLMProvider {
  id: string;
  name: string;
  type: string;              // openai, anthropic, google, etc.
  apiKey?: string;           // Encrypted at rest
  baseUrl?: string;          // Custom API endpoint
  enabled: boolean;
  models: string[];          // Available model list
}

// API key pool (multi-key support)
interface ApiKeyEntry {
  id: string;
  providerId: string;
  label?: string;
  apiKey: string;            // Encrypted at rest
  weight: number;            // Round-robin weight
  enabled: boolean;
  createdAt: number;
}

// Project
interface Project {
  id: string;
  name: string;
  path: string;              // Filesystem path
  description?: string;
  createdAt: number;
  updatedAt: number;
}

Security Audit

// Audit log entry
interface AuditLogEntry {
  id: string;
  timestamp: number;
  eventType: string;         // tool_executed, injection_detected, etc.
  severity: 'info' | 'warning' | 'critical';
  channelId?: string;
  userId?: string;
  toolName?: string;
  details: string;           // JSON-serialized details
}

Media and Resources

// Media resource
interface MediaResource {
  id: string;
  sessionId: string;
  messageId: string;
  filePath: string;
  mimeType: string;
  thumbnailPath?: string;
  size: number;
  createdAt: number;
}

// Attachment
interface Attachment {
  id: string;
  sessionId: string;
  messageId: string;
  name: string;
  type: string;
  url?: string;
  size: number;
}

Other Core Tables

Table	Purpose	Schema File
`settings`	Application settings (key-value)	`schema/settings.ts`
`templates`	Image templates	`schema/templates.ts`
`channel_instances`	Channel instances	Worker `db/channels.ts`
`channel_users`	Channel users	Worker `db/channelUsers.ts`
`character_cards`	Character cards	Worker `db/characterCards.ts`
`world_info_books`	World info books	Worker `db/worldInfo.ts`
`world_info_entries`	World info entries	Worker `db/worldInfo.ts`
`custom_agents`	Custom Agents	Worker `db/customAgents.ts`
`personas`	Persona definitions	Worker `db/personas.ts`
`user_skills`	User Skills	Worker `db/userSkills.ts`
`tags`	Tags	Worker `db/tags.ts`
`notes`	Note index	Worker `db/notes.ts`
`note_folders`	Note folders	Worker `db/noteFolders.ts`
`cron_jobs`	Scheduled tasks	CronService filesystem

DB Worker Modules

Concrete database operation implementations are spread across multiple modules under packages/desktop/app/main/workers/db/:

workers/db/
├── index.ts              # Main dispatcher; routes by method prefix to the appropriate module
├── chatSessions.ts       # chatSessions:* methods
├── chatMessages.ts       # chatMessages:* methods
├── customAgents.ts       # customAgents:* methods
├── personas.ts           # personas:* methods
├── characterCards.ts     # characterCards:* methods
├── worldInfo.ts          # worldInfo:* methods
├── groupChat.ts          # groupChat:* methods
├── channels.ts           # channels:* methods
├── channelUsers.ts       # channelUsers:* methods
├── auditLog.ts           # auditLog:* methods
├── apiKeys.ts            # apiKeys:* methods
├── tags.ts               # tags:* methods
├── notes.ts              # notes:* methods
├── noteFolders.ts        # noteFolders:* methods
├── userSkills.ts         # userSkills:* methods
├── rpDefaults.ts         # rpDefaults:* methods
└── characterSprites.ts   # sprites:* methods

Migration System

Drizzle Migrations

Schema changes are applied automatically via Drizzle's migrate():

{/* packages/desktop/app/main/db/index.ts */}
function initDatabase() {
  const sqlite = new Database(dbPath);
  sqlite.pragma('journal_mode = WAL');
  sqlite.pragma('foreign_keys = ON');
  const db = drizzle(sqlite);
  migrate(db, { migrationsFolder: 'drizzle' });
}

Custom Migrations

Complex data migration logic is implemented in migrations.ts:

{/* Custom migration example */}
async function migrateApiKeyPool(db: DbRpc) {
  const providers = await db.llmProvidersList();
  for (const p of providers) {
    if (p.apiKey) {
      await db.apiKeysInsert({
        providerId: p.id,
        apiKey: p.apiKey,
        weight: 1,
        enabled: true,
      });
    }
  }
}

Initialization Sequence

sequenceDiagram
    participant M as Main Process
    participant C as DbClient
    participant W as db.worker.ts
    participant D as SQLite

    M->>C: new DbClient(dbPath)
    C->>W: Create Worker Thread
    W->>D: new Database(dbPath)
    W->>D: PRAGMA journal_mode = WAL
    W->>D: PRAGMA foreign_keys = ON
    W->>W: Register all RPC methods
    W-->>C: ready signal

    M->>C: await db.ready()
    Note over M: Worker initialization complete; continue

    M->>M: initDatabase()
    Note over M: Drizzle ORM initialization + migrate

    M->>M: Create services that depend on db

Key point: await db.ready() must complete before creating other services, to prevent SQLITE_BUSY errors during Worker initialization.

Database Optimization

The DatabaseOptimizer service periodically performs maintenance operations:

Operation	Description	Frequency
`PRAGMA optimize`	Update statistics	On app close
`VACUUM`	Reclaim space	Manual trigger
`ANALYZE`	Update query plan	Periodic
WAL checkpoint	Merge WAL into main file	SQLite automatic

File	Description
`packages/desktop/app/main/db/index.ts`	Database initialization (WAL + Drizzle + migrate)
`packages/desktop/app/main/db/schema/`	Drizzle ORM schema definition directory
`packages/desktop/app/main/workers/DbClient.ts`	Database Worker client
`packages/desktop/app/main/workers/db.worker.ts`	Database Worker implementation
`packages/desktop/app/main/workers/db/`	Domain-split DB operation modules
`packages/desktop/app/main/workers/types.ts`	DbRpc interface definitions
`packages/desktop/app/main/services/persistence/db-optimizer/DatabaseOptimizer.ts`	Database optimization service
`packages/desktop/app/main/services/platform/migration/MigrationService.ts`	Custom migration service

Technology Choices​

Database Location​

Worker Thread Architecture​

DbClient Core Implementation​

RPC Type Definitions​

Schema Organization​

Core Data Tables​

Chat-Related​

Account-Related​

LLM Configuration​

Project-Related​

Security Audit​

Media and Resources​

Other Core Tables​

DB Worker Modules​

Migration System​

Drizzle Migrations​

Custom Migrations​

Initialization Sequence​

Database Optimization​

Related Files​