database-drizzle

name	database-drizzle
description	SQLite database with better-sqlite3 and Drizzle ORM for Electron apps. Use when implementing database schemas, writing type-safe queries, setting up WAL mode, creating migrations, implementing closure tables for hierarchies, or managing database connections. Triggers on SQLite, Drizzle, better-sqlite3, database, schema, migration, WAL mode, closure table.

SQLite + Drizzle ORM Database Layer

Type-safe database operations with better-sqlite3 and Drizzle ORM, optimized for Electron desktop applications.

When to Use This Skill

• Designing SQLite database schemas
• Implementing type-safe database queries
• Setting up database connections with WAL mode
• Creating and running migrations
• Implementing closure tables for hierarchical data
• Managing database lifecycle in Electron

Database Setup

Connection Manager

// packages/shared/src/database/connection.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from './schema';
import path from 'path';
import { app } from 'electron';

let db: ReturnType<typeof drizzle> | null = null;
let sqlite: Database.Database | null = null;

export function initDatabase() {
  const dbPath = path.join(app.getPath('userData'), 'agentop.db');
  
  sqlite = new Database(dbPath);
  
  // Enable WAL mode for better concurrency
  sqlite.pragma('journal_mode = WAL');
  sqlite.pragma('synchronous = NORMAL');
  sqlite.pragma('foreign_keys = ON');
  sqlite.pragma('busy_timeout = 5000');
  
  db = drizzle(sqlite, { schema });
  
  return db;
}

export function getDatabase() {
  if (!db) {
    throw new Error('Database not initialized. Call initDatabase() first.');
  }
  return db;
}

export function closeDatabase() {
  if (sqlite) {
    sqlite.close();
    sqlite = null;
    db = null;
  }
}

Schema Definition

Tables with Drizzle

// packages/shared/src/database/schema.ts
import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core';
import { relations } from 'drizzle-orm';

// Tasks table
export const tasks = sqliteTable('tasks', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  description: text('description'),
  status: text('status', { 
    enum: ['pending', 'active', 'completed', 'failed'] 
  }).default('pending'),
  parentId: text('parent_id').references(() => tasks.id),
  attentionType: text('attention_type', {
    enum: ['supervised', 'delegated', 'sensitive']
  }).default('supervised'),
  orderIndex: real('order_index').notNull().default(0.5),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});

// Closure table for efficient hierarchy queries
export const taskClosure = sqliteTable('task_closure', {
  ancestorId: text('ancestor_id').notNull().references(() => tasks.id),
  descendantId: text('descendant_id').notNull().references(() => tasks.id),
  depth: integer('depth').notNull(),
}, (table) => ({
  pk: primaryKey({ columns: [table.ancestorId, table.descendantId] }),
}));

// Outcomes table
export const outcomes = sqliteTable('outcomes', {
  id: text('id').primaryKey(),
  taskId: text('task_id').notNull().references(() => tasks.id),
  title: text('title').notNull(),
  summary: text('summary'),
  status: text('status', {
    enum: ['pending', 'running', 'completed', 'failed']
  }).default('pending'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});

// Agent sessions table
export const agentSessions = sqliteTable('agent_sessions', {
  id: text('id').primaryKey(),
  outcomeId: text('outcome_id').notNull().references(() => outcomes.id),
  provider: text('provider', {
    enum: ['copilot', 'anthropic', 'openai']
  }).notNull(),
  status: text('status', {
    enum: ['idle', 'running', 'paused', 'completed', 'error']
  }).default('idle'),
  startedAt: integer('started_at', { mode: 'timestamp' }),
  completedAt: integer('completed_at', { mode: 'timestamp' }),
});

// Agent steps (mind map nodes)
export const agentSteps = sqliteTable('agent_steps', {
  id: text('id').primaryKey(),
  sessionId: text('session_id').notNull().references(() => agentSessions.id),
  parentStepId: text('parent_step_id').references(() => agentSteps.id),
  type: text('type', {
    enum: ['thinking', 'tool_call', 'tool_result', 'decision_point', 'output']
  }).notNull(),
  content: text('content', { mode: 'json' }).notNull(),
  status: text('status', {
    enum: ['pending', 'running', 'completed', 'error']
  }).default('pending'),
  positionX: real('position_x'),
  positionY: real('position_y'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});

// Decision points
export const decisionPoints = sqliteTable('decision_points', {
  id: text('id').primaryKey(),
  stepId: text('step_id').notNull().references(() => agentSteps.id).unique(),
  confidence: text('confidence', {
    enum: ['high', 'medium', 'low']
  }).notNull(),
  options: text('options', { mode: 'json' }).notNull(),
  tradeOffs: text('trade_offs', { mode: 'json' }),
  suggestedQuestions: text('suggested_questions', { mode: 'json' }),
  resolution: text('resolution', {
    enum: ['approved', 'rejected', 'cancelled']
  }),
  resolvedOption: text('resolved_option'),
  resolvedAt: integer('resolved_at', { mode: 'timestamp' }),
});

// Event log for replay
export const eventLog = sqliteTable('event_log', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  sessionId: text('session_id').references(() => agentSessions.id),
  eventType: text('event_type').notNull(),
  payload: text('payload', { mode: 'json' }).notNull(),
  timestamp: integer('timestamp', { mode: 'timestamp' }).notNull(),
});

Relations

// packages/shared/src/database/schema.ts (continued)

export const tasksRelations = relations(tasks, ({ one, many }) => ({
  parent: one(tasks, {
    fields: [tasks.parentId],
    references: [tasks.id],
    relationName: 'task_children',
  }),
  children: many(tasks, { relationName: 'task_children' }),
  outcomes: many(outcomes),
}));

export const outcomesRelations = relations(outcomes, ({ one, many }) => ({
  task: one(tasks, {
    fields: [outcomes.taskId],
    references: [tasks.id],
  }),
  sessions: many(agentSessions),
}));

export const agentSessionsRelations = relations(agentSessions, ({ one, many }) => ({
  outcome: one(outcomes, {
    fields: [agentSessions.outcomeId],
    references: [outcomes.id],
  }),
  steps: many(agentSteps),
  events: many(eventLog),
}));

export const agentStepsRelations = relations(agentSteps, ({ one, many }) => ({
  session: one(agentSessions, {
    fields: [agentSteps.sessionId],
    references: [agentSessions.id],
  }),
  parent: one(agentSteps, {
    fields: [agentSteps.parentStepId],
    references: [agentSteps.id],
    relationName: 'step_children',
  }),
  children: many(agentSteps, { relationName: 'step_children' }),
  decisionPoint: one(decisionPoints),
}));

Query Patterns

Type-Safe Queries

// packages/shared/src/database/queries/tasks.ts
import { eq, desc, and, isNull } from 'drizzle-orm';
import { getDatabase } from '../connection';
import { tasks, taskClosure, outcomes } from '../schema';
import { nanoid } from 'nanoid';

export async function createTask(input: {
  title: string;
  description?: string;
  parentId?: string;
  attentionType?: 'supervised' | 'delegated' | 'sensitive';
}) {
  const db = getDatabase();
  const now = new Date();
  const id = nanoid();

  // Insert task
  await db.insert(tasks).values({
    id,
    title: input.title,
    description: input.description,
    parentId: input.parentId,
    attentionType: input.attentionType ?? 'supervised',
    orderIndex: await getNextOrderIndex(input.parentId),
    createdAt: now,
    updatedAt: now,
  });

  // Update closure table
  await updateClosureTable(id, input.parentId);

  return db.query.tasks.findFirst({
    where: eq(tasks.id, id),
    with: { outcomes: true },
  });
}

export async function getTaskTree() {
  const db = getDatabase();
  
  // Get root tasks with their descendants
  return db.query.tasks.findMany({
    where: isNull(tasks.parentId),
    orderBy: [tasks.orderIndex],
    with: {
      outcomes: {
        with: {
          sessions: true,
        },
      },
      children: {
        with: {
          outcomes: true,
          children: true,
        },
      },
    },
  });
}

export async function getTaskWithDescendants(taskId: string) {
  const db = getDatabase();
  
  // Use closure table for efficient hierarchy query
  const descendantIds = await db
    .select({ id: taskClosure.descendantId })
    .from(taskClosure)
    .where(eq(taskClosure.ancestorId, taskId));

  return db.query.tasks.findMany({
    where: inArray(tasks.id, descendantIds.map(d => d.id)),
    orderBy: [tasks.orderIndex],
    with: { outcomes: true },
  });
}

Closure Table Helpers

// packages/shared/src/database/queries/closure.ts
import { eq, and } from 'drizzle-orm';
import { getDatabase } from '../connection';
import { taskClosure } from '../schema';

export async function updateClosureTable(taskId: string, parentId?: string) {
  const db = getDatabase();

  // Self-reference (depth 0)
  await db.insert(taskClosure).values({
    ancestorId: taskId,
    descendantId: taskId,
    depth: 0,
  });

  // Copy parent's ancestors
  if (parentId) {
    const parentAncestors = await db
      .select()
      .from(taskClosure)
      .where(eq(taskClosure.descendantId, parentId));

    for (const ancestor of parentAncestors) {
      await db.insert(taskClosure).values({
        ancestorId: ancestor.ancestorId,
        descendantId: taskId,
        depth: ancestor.depth + 1,
      });
    }
  }
}

export async function moveTask(taskId: string, newParentId?: string) {
  const db = getDatabase();

  // Get all descendants of the task
  const descendants = await db
    .select({ id: taskClosure.descendantId })
    .from(taskClosure)
    .where(eq(taskClosure.ancestorId, taskId));

  const descendantIds = descendants.map(d => d.id);

  // Delete old ancestry (except self-references)
  await db
    .delete(taskClosure)
    .where(
      and(
        inArray(taskClosure.descendantId, descendantIds),
        not(inArray(taskClosure.ancestorId, descendantIds))
      )
    );

  // Create new ancestry for each descendant
  if (newParentId) {
    const newAncestors = await db
      .select()
      .from(taskClosure)
      .where(eq(taskClosure.descendantId, newParentId));

    for (const desc of descendants) {
      const descDepth = descendants.find(d => d.id === desc.id)?.depth ?? 0;
      
      for (const ancestor of newAncestors) {
        await db.insert(taskClosure).values({
          ancestorId: ancestor.ancestorId,
          descendantId: desc.id,
          depth: ancestor.depth + descDepth + 1,
        });
      }
    }
  }
}

Fractional Indexing for Drag-Reorder

// packages/shared/src/database/queries/ordering.ts
import { eq, and, gt, lt, asc, desc } from 'drizzle-orm';
import { getDatabase } from '../connection';
import { tasks } from '../schema';

export async function getNextOrderIndex(parentId?: string): Promise<number> {
  const db = getDatabase();
  
  const lastTask = await db.query.tasks.findFirst({
    where: parentId ? eq(tasks.parentId, parentId) : isNull(tasks.parentId),
    orderBy: [desc(tasks.orderIndex)],
  });

  return lastTask ? lastTask.orderIndex + 1 : 0.5;
}

export async function reorderTask(
  taskId: string,
  beforeId?: string,
  afterId?: string
) {
  const db = getDatabase();

  let newIndex: number;

  if (!beforeId && !afterId) {
    newIndex = 0.5;
  } else if (!beforeId) {
    const afterTask = await db.query.tasks.findFirst({
      where: eq(tasks.id, afterId!),
    });
    newIndex = afterTask ? afterTask.orderIndex / 2 : 0.5;
  } else if (!afterId) {
    const beforeTask = await db.query.tasks.findFirst({
      where: eq(tasks.id, beforeId),
    });
    newIndex = beforeTask ? beforeTask.orderIndex + 1 : 0.5;
  } else {
    const [beforeTask, afterTask] = await Promise.all([
      db.query.tasks.findFirst({ where: eq(tasks.id, beforeId) }),
      db.query.tasks.findFirst({ where: eq(tasks.id, afterId!) }),
    ]);
    
    if (beforeTask && afterTask) {
      newIndex = (beforeTask.orderIndex + afterTask.orderIndex) / 2;
    } else {
      newIndex = 0.5;
    }
  }

  await db
    .update(tasks)
    .set({ orderIndex: newIndex, updatedAt: new Date() })
    .where(eq(tasks.id, taskId));
}

Migrations

Migration System

// packages/shared/src/database/migrations/index.ts
import { getDatabase } from '../connection';

interface Migration {
  version: number;
  name: string;
  up: (db: ReturnType<typeof getDatabase>) => Promise<void>;
}

const migrations: Migration[] = [
  {
    version: 1,
    name: 'initial_schema',
    up: async (db) => {
      // Create tables using raw SQL for initial schema
      await db.run(sql`
        CREATE TABLE IF NOT EXISTS tasks (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          description TEXT,
          status TEXT CHECK(status IN ('pending', 'active', 'completed', 'failed')) DEFAULT 'pending',
          parent_id TEXT REFERENCES tasks(id),
          attention_type TEXT CHECK(attention_type IN ('supervised', 'delegated', 'sensitive')) DEFAULT 'supervised',
          order_index REAL NOT NULL DEFAULT 0.5,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL
        )
      `);
      // ... more table creation
    },
  },
  {
    version: 2,
    name: 'add_discussions',
    up: async (db) => {
      await db.run(sql`
        CREATE TABLE IF NOT EXISTS discussions (
          id TEXT PRIMARY KEY,
          decision_point_id TEXT NOT NULL REFERENCES decision_points(id),
          status TEXT CHECK(status IN ('active', 'resolved', 'spawned_task')) DEFAULT 'active',
          spawned_task_id TEXT REFERENCES tasks(id),
          created_at INTEGER NOT NULL,
          resolved_at INTEGER
        )
      `);
    },
  },
];

export async function runMigrations() {
  const db = getDatabase();
  
  // Create migrations table
  await db.run(sql`
    CREATE TABLE IF NOT EXISTS _migrations (
      version INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      applied_at INTEGER NOT NULL
    )
  `);

  // Get applied migrations
  const applied = await db.all(sql`
    SELECT version FROM _migrations ORDER BY version
  `);
  const appliedVersions = new Set(applied.map(m => m.version));

  // Run pending migrations
  for (const migration of migrations) {
    if (!appliedVersions.has(migration.version)) {
      console.log(`Running migration: ${migration.name}`);
      await migration.up(db);
      await db.run(sql`
        INSERT INTO _migrations (version, name, applied_at)
        VALUES (${migration.version}, ${migration.name}, ${Date.now()})
      `);
    }
  }
}

Best Practices

1. Always use WAL mode - Enables concurrent reads during writes
2. Enable foreign keys - Enforces referential integrity
3. Use transactions - Group related operations
4. Leverage Drizzle relations - Type-safe eager loading
5. Index frequently queried columns - Especially foreign keys
6. Use closure tables - For efficient hierarchy queries
7. Fractional indexing - For smooth drag-reorder without renumbering

Troubleshooting

Issue	Solution
Database locked	Increase busy_timeout, check for long transactions
Slow queries	Add indexes, use closure table for hierarchies
Type errors	Ensure schema types match TypeScript interfaces
Migration fails	Check for schema conflicts, backup before migration