The DatabaseService manages all SQLite database operations in Emdash. It handles CRUD operations for projects, tasks, conversations, messages, line comments, and SSH connections.
Overview
Emdash uses SQLite with Drizzle ORM for type-safe database operations. The service automatically applies migrations on startup and validates the schema contract.
Key features:
- Type-safe ORM: Drizzle provides compile-time type checking
- Automatic migrations: Schema updates applied on app startup
- Schema validation: Ensures required tables/columns exist
- Foreign key constraints: Enforced via Drizzle relations
- Platform-agnostic paths: Works across macOS, Linux, and Windows
Database Locations
macOS
~/Library/Application Support/emdash/emdash.db
Linux
~/.config/emdash/emdash.db
Windows
%APPDATA%\emdash\emdash.db
Override Location
Set the EMDASH_DB_FILE environment variable:
export EMDASH_DB_FILE=/custom/path/to/emdash.db
Disable Native Driver
Use JavaScript-only SQLite implementation:
export EMDASH_DISABLE_NATIVE_DB=1
Schema
Projects
Stores project metadata and Git/GitHub configuration.
interface Project {
id: string;
name: string;
path: string;
isRemote?: boolean;
sshConnectionId?: string | null;
remotePath?: string | null;
gitInfo: {
isGitRepo: boolean;
remote?: string;
branch?: string;
baseRef?: string;
};
githubInfo?: {
repository: string;
connected: boolean;
};
createdAt: string;
updatedAt: string;
}
Tasks
Stores task metadata and worktree paths.
interface Task {
id: string;
projectId: string;
name: string;
branch: string;
path: string;
status: 'active' | 'idle' | 'running';
agentId?: string | null;
metadata?: any;
useWorktree?: boolean;
archivedAt?: string | null;
createdAt: string;
updatedAt: string;
}
Conversations
Stores multi-chat conversation metadata.
interface Conversation {
id: string;
taskId: string;
title: string;
provider?: string | null;
isActive?: boolean;
isMain?: boolean;
displayOrder?: number;
metadata?: string | null;
createdAt: string;
updatedAt: string;
}
Messages
Stores chat messages.
interface Message {
id: string;
conversationId: string;
content: string;
sender: 'user' | 'agent';
timestamp: string;
metadata?: string;
}
Schema Location
Source: src/main/db/schema.ts
Migrations: drizzle/ (auto-generated by drizzle-kit)
Initialization
initialize
Initialize the database connection and apply migrations.
async initialize(): Promise<void>
- Dynamically imports
sqlite3 native module
- Opens database connection
- Applies missing migrations (with foreign key enforcement disabled)
- Validates schema contract (ensures required tables/columns exist)
- Re-enables foreign key constraints
Migration recovery:
If a previous run partially applied the workspace→task migration, the service automatically:
- Detects incomplete state (e.g.,
__new_conversations exists)
- Completes the migration
- Marks the migration as applied
Example:
import { databaseService } from './services/DatabaseService';
await databaseService.initialize();
Project Operations
saveProject
Save or update a project.
async saveProject(project: Omit<Project, 'createdAt' | 'updatedAt'>): Promise<void>
Project data (without timestamps)
- Inserts new project or updates existing (based on
id or path)
- Removes stale rows with conflicting
id or path
- Computes
baseRef from gitInfo.baseRef, gitInfo.remote, and gitInfo.branch
- Sets
updatedAt to CURRENT_TIMESTAMP
Example:
await databaseService.saveProject({
id: 'proj_abc123',
name: 'My Project',
path: '/Users/dev/my-project',
isRemote: false,
gitInfo: {
isGitRepo: true,
remote: 'origin',
branch: 'main',
baseRef: 'origin/main',
},
githubInfo: {
repository: 'user/repo',
connected: true,
},
});
getProjects
Get all projects, ordered by most recently updated.
async getProjects(): Promise<Project[]>
const projects = await databaseService.getProjects();
// [
// { id: 'proj_abc', name: 'Project A', ... },
// { id: 'proj_def', name: 'Project B', ... }
// ]
getProjectById
Get a single project by ID.
async getProjectById(projectId: string): Promise<Project | null>
const project = await databaseService.getProjectById('proj_abc123');
if (project) {
console.log(project.name);
}
updateProjectBaseRef
Update the base branch reference for a project.
async updateProjectBaseRef(
projectId: string,
nextBaseRef: string
): Promise<Project | null>
New base ref (e.g., origin/develop, main)
- Strips
refs/remotes/ and remotes/ prefixes
- Prepends remote alias if missing (e.g.,
main → origin/main)
- Validates remote name against actual Git remotes
- Falls back to local branch if no remote configured
Example:
const project = await databaseService.updateProjectBaseRef(
'proj_abc123',
'develop'
);
// Updates baseRef to 'origin/develop' (if remote exists)
deleteProject
Delete a project by ID.
async deleteProject(projectId: string): Promise<void>
- All tasks for the project
- All conversations for those tasks
- All messages for those conversations
Task Operations
saveTask
Save or update a task.
async saveTask(task: Omit<Task, 'createdAt' | 'updatedAt'>): Promise<void>
Task data (without timestamps)
await databaseService.saveTask({
id: 'task_abc',
projectId: 'proj_123',
name: 'Fix login bug',
branch: 'emdash/fix-login-bug-a7f',
path: '/Users/dev/worktrees/fix-login-bug-a7f',
status: 'active',
agentId: 'claude',
useWorktree: true,
metadata: { provider: 'claude', autoApprove: true },
});
getTasks
Get all active (non-archived) tasks for a project, ordered by most recently updated.
async getTasks(projectId?: string): Promise<Task[]>
Optional project ID filter (omit to get tasks for all projects)
const tasks = await databaseService.getTasks('proj_abc123');
// [
// { id: 'task_1', name: 'Feature X', ... },
// { id: 'task_2', name: 'Bugfix Y', ... }
// ]
getArchivedTasks
Get all archived tasks for a project, ordered by most recently archived.
async getArchivedTasks(projectId?: string): Promise<Task[]>
archiveTask
Archive a task (sets archivedAt timestamp and resets status to idle).
async archiveTask(taskId: string): Promise<void>
restoreTask
Restore an archived task (clears archivedAt timestamp).
async restoreTask(taskId: string): Promise<void>
getTaskByPath
Get a task by its worktree path.
async getTaskByPath(taskPath: string): Promise<Task | null>
getTaskById
Get a task by ID.
async getTaskById(taskId: string): Promise<Task | null>
deleteTask
Delete a task by ID.
async deleteTask(taskId: string): Promise<void>
- All conversations for the task
- All messages for those conversations
Conversation Operations
saveConversation
Save or update a conversation.
async saveConversation(
conversation: Omit<Conversation, 'createdAt' | 'updatedAt'>
): Promise<void>
getConversations
Get all conversations for a task, ordered by displayOrder and updatedAt.
async getConversations(taskId: string): Promise<Conversation[]>
getOrCreateDefaultConversation
Get the first conversation for a task, or create a default “main” conversation.
async getOrCreateDefaultConversation(taskId: string): Promise<Conversation>
- Returns earliest conversation if any exist
- Otherwise creates a new conversation with
isMain: true
createConversation
Create a new conversation (e.g., for multi-chat).
async createConversation(
taskId: string,
title: string,
provider?: string,
isMain?: boolean
): Promise<Conversation>
AI provider (e.g., claude, codex)
Whether this is the main conversation (only one per task)
- Assigns next
displayOrder (max + 1)
- Deactivates other conversations for the task
- Prevents multiple main conversations (sets
isMain: false if one exists)
setActiveConversation
Set a conversation as active (deactivates others).
async setActiveConversation(taskId: string, conversationId: string): Promise<void>
getActiveConversation
Get the active conversation for a task.
async getActiveConversation(taskId: string): Promise<Conversation | null>
reorderConversations
Reorder conversations (updates displayOrder).
async reorderConversations(taskId: string, conversationIds: string[]): Promise<void>
await databaseService.reorderConversations('task_abc', [
'conv_1', // displayOrder: 0
'conv_2', // displayOrder: 1
'conv_3', // displayOrder: 2
]);
updateConversationTitle
Update a conversation’s title.
async updateConversationTitle(conversationId: string, title: string): Promise<void>
deleteConversation
Delete a conversation by ID.
async deleteConversation(conversationId: string): Promise<void>
Message Operations
saveMessage
Save a message (inserts only, doesn’t update existing).
async saveMessage(message: Omit<Message, 'timestamp'>): Promise<void>
- Inserts message with
timestamp: CURRENT_TIMESTAMP
- Updates conversation
updatedAt timestamp (in a transaction)
- Uses
onConflictDoNothing() to prevent duplicate inserts
getMessages
Get all messages for a conversation, ordered by timestamp.
async getMessages(conversationId: string): Promise<Message[]>
async saveLineComment(
input: Omit<LineCommentInsert, 'id' | 'createdAt' | 'updatedAt'>
): Promise<string>
comment-1234567890-a1b2c3)
Get line comments for a task, optionally filtered by file.
async getLineComments(taskId: string, filePath?: string): Promise<LineCommentRow[]>
async updateLineComment(id: string, content: string): Promise<void>
async deleteLineComment(id: string): Promise<void>
sentAt timestamp).
async markCommentsSent(commentIds: string[]): Promise<void>
async getUnsentComments(taskId: string): Promise<LineCommentRow[]>
SSH Connection Operations
saveSshConnection
Save or update an SSH connection.
async saveSshConnection(
connection: Omit<SshConnectionInsert, 'id' | 'createdAt' | 'updatedAt'> & { id?: string }
): Promise<SshConnectionRow>
getSshConnections
Get all SSH connections, ordered by name.
async getSshConnections(): Promise<SshConnectionRow[]>
getSshConnection
Get an SSH connection by ID.
async getSshConnection(id: string): Promise<SshConnectionRow | null>
deleteSshConnection
Delete an SSH connection by ID.
async deleteSshConnection(id: string): Promise<void>
- Updates projects using this connection (sets
sshConnectionId: null, isRemote: 0)
- Deletes the connection record
Error Handling
DatabaseSchemaMismatchError
Thrown when the database schema doesn’t match expectations.
class DatabaseSchemaMismatchError extends Error {
readonly code = 'DB_SCHEMA_MISMATCH';
readonly dbPath: string;
readonly missingInvariants: string[];
}
try {
await databaseService.initialize();
} catch (error) {
if (error instanceof DatabaseSchemaMismatchError) {
console.error('Schema mismatch:', error.missingInvariants);
// e.g., ['projects.base_ref', 'tasks table', 'conversations.task_id']
}
}
Migration Recovery
The service automatically recovers from partially-applied migrations:
if (
(await this.tableExists('tasks')) &&
(await this.tableExists('conversations')) &&
(await this.tableExists('__new_conversations')) &&
(await this.tableHasColumn('conversations', 'workspace_id')) &&
!(await this.tableHasColumn('conversations', 'task_id'))
) {
// Incomplete workspace→task migration detected — complete it
await this.execSql(`
INSERT INTO "__new_conversations"("id", "task_id", "title", "created_at", "updated_at")
SELECT "id", "workspace_id", "title", "created_at", "updated_at" FROM "conversations"
`);
await this.execSql(`DROP TABLE "conversations";`);
await this.execSql(`ALTER TABLE "__new_conversations" RENAME TO "conversations";`);
// Mark migration as applied
}
Migration Summary
getLastMigrationSummary
Get a summary of the last migration run.
getLastMigrationSummary(): MigrationSummary | null
Number of migrations applied in this run
Total number of migrations in the folder
Whether the service recovered from a partial migration
Generating Migrations
Update Schema
- Edit
src/main/db/schema.ts
- Run
pnpm exec drizzle-kit generate
- Commit both
schema.ts and new migration files in drizzle/
NEVER manually edit files in drizzle/meta/ or numbered SQL migration files.
Browse Database
Run Drizzle Studio:
pnpm exec drizzle-kit studio
https://local.drizzle.studio/.
Location
Source: src/main/services/DatabaseService.ts
Schema: src/main/db/schema.ts
Migrations: drizzle/
Singleton export:
export const databaseService = new DatabaseService();
- Drizzle Client (
src/main/db/drizzleClient.ts): Type-safe query builder
- ProjectSettingsService (
src/main/services/ProjectSettingsService.ts): Per-project settings (separate from database)
- TaskLifecycleService (
src/main/services/TaskLifecycleService.ts): Task orchestration
See Also
