Function createSQLiteStore

mcp/src/server/sqlite.ts:238–565

(dbPath?: string)

// -----------------------------------------------------------------------------

export function createSQLiteStore(dbPath?: string): AFSStore {
  const db = new Database(dbPath ?? getDbPath());
  db.pragma("journal_mode = WAL");
  initDatabase(db);

  // Safe migrations for new columns (no-ops if already exist)
  try { db.exec("ALTER TABLE annotations ADD COLUMN kind TEXT DEFAULT 'feedback'"); } catch {}
  try { db.exec("ALTER TABLE annotations ADD COLUMN extra TEXT"); } catch {}

  // Restore event sequence from last event
  const lastEvent = db.prepare("SELECT MAX(sequence) as seq FROM events").get() as { seq: number | null };
  if (lastEvent?.seq) {
    eventBus.setSequence(lastEvent.seq);
  }

  // Prepared statements
  const stmts = {
    // Sessions
    insertSession: db.prepare(`
      INSERT INTO sessions (id, url, status, created_at, project_id, metadata)
      VALUES (@id, @url, @status, @createdAt, @projectId, @metadata)
    `),
    getSession: db.prepare("SELECT * FROM sessions WHERE id = ?"),
    updateSessionStatus: db.prepare(`
      UPDATE sessions SET status = @status, updated_at = @updatedAt WHERE id = @id
    `),
    listSessions: db.prepare("SELECT * FROM sessions ORDER BY created_at DESC"),

    // Annotations
    insertAnnotation: db.prepare(`
      INSERT INTO annotations (
        id, session_id, x, y, comment, element, element_path, timestamp,
        selected_text, bounding_box, nearby_text, css_classes, nearby_elements,
        computed_styles, full_path, accessibility, is_multi_select, is_fixed,
        react_components, url, intent, severity, status, thread, created_at,
        updated_at, resolved_at, resolved_by, author_id, kind, extra
      ) VALUES (
        @id, @sessionId, @x, @y, @comment, @element, @elementPath, @timestamp,
        @selectedText, @boundingBox, @nearbyText, @cssClasses, @nearbyElements,
        @computedStyles, @fullPath, @accessibility, @isMultiSelect, @isFixed,
        @reactComponents, @url, @intent, @severity, @status, @thread, @createdAt,
        @updatedAt, @resolvedAt, @resolvedBy, @authorId, @kind, @extra
      )
    `),
    getAnnotation: db.prepare("SELECT * FROM annotations WHERE id = ?"),
    getAnnotationsBySession: db.prepare("SELECT * FROM annotations WHERE session_id = ? ORDER BY timestamp"),
    getPendingAnnotations: db.prepare("SELECT * FROM annotations WHERE session_id = ? AND status = 'pending' ORDER BY timestamp"),
    deleteAnnotation: db.prepare("DELETE FROM annotations WHERE id = ?"),
    updateAnnotation: db.prepare(`
      UPDATE annotations SET
        comment = COALESCE(@comment, comment),
        status = COALESCE(@status, status),
        updated_at = @updatedAt,
        resolved_at = COALESCE(@resolvedAt, resolved_at),
        resolved_by = COALESCE(@resolvedBy, resolved_by),
        thread = COALESCE(@thread, thread),
        intent = COALESCE(@intent, intent),
        severity = COALESCE(@severity, severity)