openclaw-dashboard/lib/db.ts

import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

const DATA_DIR = path.join(process.cwd(), 'data');

// Ensure data directory exists
if (!fs.existsSync(DATA_DIR)) {
  fs.mkdirSync(DATA_DIR, { recursive: true });
}

const DB_PATH = path.join(DATA_DIR, 'openclaw.db');

const db = new Database(DB_PATH, {
  verbose: process.env.NODE_ENV === 'development' ? console.log : undefined,
});

// Enable WAL mode for better concurrency
db.pragma('journal_mode = WAL');

// Initialize tables
db.exec(`
  CREATE TABLE IF NOT EXISTS instances (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'unknown',
    endpoint TEXT,
    last_check INTEGER,
    metadata TEXT
  );

  CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    instance_id TEXT NOT NULL,
    level TEXT NOT NULL,
    message TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    source TEXT
  );

  CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at INTEGER NOT NULL
  );

  CREATE TABLE IF NOT EXISTS providers (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    provider_type TEXT NOT NULL,
    config TEXT NOT NULL,
    enabled INTEGER DEFAULT 1,
    created_at INTEGER NOT NULL
  );

  CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON logs(timestamp);
  CREATE INDEX IF NOT EXISTS idx_logs_instance ON logs(instance_id);
`);

export interface Instance {
  id: string;
  name: string;
  type: 'openclaw' | 'opencode' | 'gateway';
  status: 'online' | 'offline' | 'unknown' | 'starting' | 'stopping';
  endpoint?: string;
  last_check?: number;
  metadata?: Record<string, any>;
}

export interface LogEntry {
  id: number;
  instance_id: string;
  level: 'info' | 'warn' | 'error' | 'debug';
  message: string;
  timestamp: number;
  source?: string;
}

export interface Provider {
  id: string;
  name: string;
  provider_type: string;
  config: Record<string, any>;
  enabled: boolean;
  created_at: number;
}

// Instance CRUD
export const dbGetInstances = (): Instance[] => {
  const stmt = db.prepare('SELECT * FROM instances ORDER BY name');
  const rows = stmt.all() as any[];
  return rows.map(row => ({
    id: row.id,
    name: row.name,
    type: row.type,
    status: row.status,
    endpoint: row.endpoint,
    last_check: row.last_check,
    metadata: row.metadata ? JSON.parse(row.metadata) : undefined,
  })) as Instance[];
};

export const dbGetInstance = (id: string): Instance | undefined => {
  const stmt = db.prepare('SELECT * FROM instances WHERE id = ?');
  const row = stmt.get(id) as any;
  if (!row) return undefined;
  return {
    ...row,
    metadata: row.metadata ? JSON.parse(row.metadata) : undefined,
  };
};

export const dbUpsertInstance = (instance: Instance): void => {
  const stmt = db.prepare(`
    INSERT INTO instances (id, name, type, status, endpoint, last_check, metadata)
    VALUES (?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      name = excluded.name,
      type = excluded.type,
      status = excluded.status,
      endpoint = excluded.endpoint,
      last_check = excluded.last_check,
      metadata = excluded.metadata
  `);
  stmt.run(
    instance.id,
    instance.name,
    instance.type,
    instance.status,
    instance.endpoint || null,
    instance.last_check || Date.now(),
    instance.metadata ? JSON.stringify(instance.metadata) : null
  );
};

// Log CRUD
export const dbInsertLog = (log: Omit<LogEntry, 'id'>): void => {
  const stmt = db.prepare(`
    INSERT INTO logs (instance_id, level, message, timestamp, source)
    VALUES (?, ?, ?, ?, ?)
  `);
  stmt.run(log.instance_id, log.level, log.message, log.timestamp, log.source || null);
};

export const dbGetLogs = (instanceId: string, limit: number = 100, offset: number = 0): LogEntry[] => {
  const stmt = db.prepare(`
    SELECT * FROM logs
    WHERE instance_id = ?
    ORDER BY timestamp DESC
    LIMIT ? OFFSET ?
  `);
  return stmt.all(instanceId, limit, offset) as LogEntry[];
};

export const dbGetRecentLogs = (limit: number = 50): LogEntry[] => {
  const stmt = db.prepare(`
    SELECT * FROM logs
    ORDER BY timestamp DESC
    LIMIT ?
  `);
  return stmt.all(limit) as LogEntry[];
};

export const dbDeleteOldLogs = (before: number = Date.now() - 7 * 24 * 60 * 60 * 1000): number => {
  const stmt = db.prepare('DELETE FROM logs WHERE timestamp < ?');
  const info = stmt.run(before);
  return info.changes;
};

// Settings CRUD
export const dbGetSetting = (key: string): string | undefined => {
  const stmt = db.prepare('SELECT value FROM settings WHERE key = ?');
  const row = stmt.get(key) as any;
  return row?.value;
};

export const dbSetSetting = (key: string, value: string): void => {
  const stmt = db.prepare(`
    INSERT INTO settings (key, value, updated_at)
    VALUES (?, ?, ?)
    ON CONFLICT(key) DO UPDATE SET
      value = excluded.value,
      updated_at = excluded.updated_at
  `);
  stmt.run(key, value, Date.now());
};

// Provider CRUD
export const dbGetProviders = (): Provider[] => {
  const stmt = db.prepare('SELECT * FROM providers ORDER BY name');
  const rows = stmt.all() as any[];
  return rows.map(row => ({
    id: row.id,
    name: row.name,
    provider_type: row.provider_type,
    config: JSON.parse(row.config),
    enabled: Boolean(row.enabled),
    created_at: row.created_at,
  })) as Provider[];
};

export const dbUpsertProvider = (provider: Omit<Provider, 'created_at'> & { created_at?: number }): void => {
  const stmt = db.prepare(`
    INSERT INTO providers (id, name, provider_type, config, enabled, created_at)
    VALUES (?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      name = excluded.name,
      provider_type = excluded.provider_type,
      config = excluded.config,
      enabled = excluded.enabled
  `);
  stmt.run(
    provider.id,
    provider.name,
    provider.provider_type,
    JSON.stringify(provider.config),
    provider.enabled ? 1 : 0,
    provider.created_at || Date.now()
  );
};

export const dbDeleteProvider = (id: string): void => {
  const stmt = db.prepare('DELETE FROM providers WHERE id = ?');
  stmt.run(id);
};

export default db;