database-drizzle

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(),
});