ruvnet--ruflo/v3/implementation/research/better-sqlite3-usage-inventory.md

better-sqlite3 Usage Inventory - Claude-Flow v3

Generated: 2026-01-03 Purpose: Complete inventory of better-sqlite3 usage across codebase for sql.js migration

---

Summary

• Total files: 17
• Direct imports: 15
• Indirect usage: 2 (patches)
• External dependencies: 2 (agentic-flow, agentdb)

---

1. Core Database Layer

1.1 Main Abstraction Layer ⭐ (Priority: Critical)

File: /home/user/claude-flow/src/memory/sqlite-wrapper.js

• Line: 14-19 (initialization)
• Usage: Dynamic require/import with error handling
• Current features:
  • Platform detection
  • Auto-rebuild on NODE_MODULE_VERSION mismatch
  • Graceful fallback messaging
• Migration: Add sql.js as alternative provider
• Impact: High - Core abstraction used by all database operations

// Lines 14-19
let Database = null;
let sqliteAvailable = false;
let loadError = null;
let rebuildAttempted = false;

// Line 54-66: Loading logic
async function tryLoadSQLite() {
  try {
    const require = createRequire(import.meta.url);
    Database = require('better-sqlite3');
    sqliteAvailable = true;
    return true;
  } catch (requireErr) {
    // Fallback to ES module import
    // ... error handling
  }
}

---

1.2 Database Service

File: /home/user/claude-flow/src/api/database-service.ts

• Line: 559
• Usage: Dynamic import for SQLite initialization
• Method: initializeSQLite()
• Migration: Indirect - uses sqlite-wrapper abstractions
• Impact: Medium - API layer uses DatabaseManager

// Line 556-570
private async initializeSQLite(): Promise<void> {
  try {
    // Import better-sqlite3 dynamically
    const Database = (await import('better-sqlite3')).default;
    this.db = new Database(this.config.database);

    // Enable WAL mode for better concurrency
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('synchronous = NORMAL');
    this.db.pragma('cache_size = 1000');
    this.db.pragma('temp_store = memory');
  } catch (error) {
    throw new DatabaseError('Failed to initialize SQLite', { error });
  }
}

---

1.3 Database Manager

File: /home/user/claude-flow/src/core/DatabaseManager.ts

• Line: 197
• Usage: CommonJS require with error recovery
• Method: SQLiteProvider constructor
• Migration: Add SqlJsProvider as alternative
• Impact: High - Core persistence layer

// Line 189-207
class SQLiteProvider implements IDatabaseProvider {
  private db: any;
  private dbPath: string;

  constructor(dbPath: string) {
    this.dbPath = dbPath;
    // Dynamic import to handle optional dependency
    try {
      const Database = require('better-sqlite3'); // Line 197
      this.db = new Database(dbPath);
    } catch (error) {
      // Check for native module version mismatch (NODE_MODULE_VERSION error)
      if (isNativeModuleVersionError(error)) {
        const recoveryMsg = getNativeModuleRecoveryMessage(error);
        throw new NativeModuleError(recoveryMsg, error as Error);
      }
      throw new Error('better-sqlite3 not available. Install with: npm install better-sqlite3');
    }
  }
}

---

1.4 Persistence Manager

File: /home/user/claude-flow/src/core/persistence.ts

• Line: 5
• Usage: ES module import (direct)
• Type: TypeScript with full type safety
• Migration: Replace with provider abstraction
• Impact: High - Manages agent/task persistence

// Line 1-8
/**
 * Persistence layer for Claude-Flow using SQLite
 */

import Database from 'better-sqlite3'; // Line 5
import { join } from 'path';
import { mkdir } from 'fs/promises';

Methods using better-sqlite3:

• initialize() - Line 44-53
• createTables() - Line 55-100
• saveAgent() - Line 103-120
• getAgent() - Line 122-139
• All CRUD operations for agents/tasks

---

2. Memory Backends

2.1 SQLite Backend

File: /home/user/claude-flow/src/memory/backends/sqlite.ts

• Line: N/A (uses wrapper)
• Usage: Indirect via sqlite-wrapper.js
• Migration: Template for sql.js backend
• Impact: Medium - Memory storage layer

// Line 32-38: Dynamic import from wrapper
if (!this.sqliteLoaded) {
  const module = await import('../sqlite-wrapper.js');
  createDatabase = module.createDatabase;
  isSQLiteAvailable = module.isSQLiteAvailable;
  this.sqliteLoaded = true;
}

---

2.2 SQLite Store

File: /home/user/claude-flow/src/memory/sqlite-store.js

• Line: 10
• Usage: Import from wrapper
• Type: JavaScript (MCP server)
• Migration: Indirect - uses wrapper
• Impact: Medium - MCP memory persistence

// Line 1-12
import path from 'path';
import { promises as fs } from 'fs';
import { fileURLToPath } from 'url';
import os from 'os';
import { createDatabase } from './sqlite-wrapper.js'; // Line 10
import { getSwarmDir } from '../utils/project-root.js';
import { sessionSerializer } from './enhanced-session-serializer.js';

---

3. CLI Commands

3.1 Hive Mind Main

File: /home/user/claude-flow/src/cli/simple-commands/hive-mind.js

• Lines: 38, 46
• Usage: Dual loading (require + dynamic import)
• Migration: Use abstraction layer
• Impact: Low - CLI utility

// Line 36-49
try {
  Database = require('better-sqlite3'); // Line 38
  DatabaseAvailable = true;
} catch (error) {
  console.warn('⚠️  better-sqlite3 not available, falling back to in-memory storage');
  try {
    // Try ES module import
    const sqlite = await import('better-sqlite3'); // Line 46
    Database = sqlite.default;
    DatabaseAvailable = true;
  } catch (importError) {
    // Fallback handled
  }
}

---

3.2 Hive Mind Wizard

File: /home/user/claude-flow/src/cli/simple-commands/hive-mind-wizard.js

• Line: 4
• Usage: Direct require
• Migration: Add try-catch with sql.js fallback
• Impact: Low - Setup wizard

// Line 1-10
const fs = require('fs-extra');
const path = require('path');
const Database = require('better-sqlite3'); // Line 4
const ora = require('ora');

---

3.3 Hive Mind Memory

File: /home/user/claude-flow/src/cli/simple-commands/hive-mind/memory.js

• Line: 7
• Usage: ES module import
• Migration: Use abstraction layer
• Impact: Low - Memory CLI utilities

// Line 1-10
/**
 * Hive Mind Memory Management CLI
 */
import fs from 'fs-extra';
import path from 'path';
import Database from 'better-sqlite3'; // Line 7

---

3.4 Metrics Reader

File: /home/user/claude-flow/src/cli/simple-commands/hive-mind/metrics-reader.js

• Line: 6
• Usage: ES module import
• Migration: Use abstraction layer
• Impact: Low - Metrics utilities

// Line 1-10
/**
 * Hive Mind Metrics Reader
 */
import fs from 'fs-extra';
import path from 'path';
import Database from 'better-sqlite3'; // Line 6

---

3.5 Database Optimizer

File: /home/user/claude-flow/src/cli/simple-commands/hive-mind/db-optimizer.js

• Line: 8
• Usage: ES module import
• Migration: Use abstraction layer
• Impact: Low - Maintenance utilities

// Line 1-12
/**
 * Hive Mind Database Optimizer
 */
import fs from 'fs-extra';
import path from 'path';
import os from 'os';
import Database from 'better-sqlite3'; // Line 8

---

3.6 MCP Wrapper

File: /home/user/claude-flow/src/cli/simple-commands/hive-mind/mcp-wrapper.js

• Line: 132
• Usage: Type annotation only (JSDoc)
• Migration: Update type annotation
• Impact: None - Type hint only

// Line 132
/** @type {import('better-sqlite3').Database | null} */
this.db = null;

---

3.7 Hive Mind Init

File: /home/user/claude-flow/src/cli/simple-commands/init/hive-mind-init.js

• Lines: 304, 307
• Usage: Dynamic import with error handling
• Migration: Add sql.js as fallback
• Impact: Medium - Initialization logic

// Line 304-315
// Dynamic import for better-sqlite3 with proper error handling
let Database;
try {
  Database = (await import('better-sqlite3')).default; // Line 307
  console.log('✅ Using SQLite for persistent storage');
} catch (error) {
  console.warn('⚠️  SQLite not available, using in-memory storage');
  console.warn('   Install better-sqlite3 for persistence: npm install better-sqlite3');
  useInMemory = true;
}

---

3.8 Swarm Metrics Integration

File: /home/user/claude-flow/src/cli/simple-commands/swarm-metrics-integration.js

• Line: 8
• Usage: ES module import
• Migration: Use abstraction layer
• Impact: Low - Metrics integration

// Line 1-12
/**
 * Swarm Metrics Integration Service
 */
import fs from 'fs-extra';
import path from 'path';
import Database from 'better-sqlite3'; // Line 8

---

4. Utilities

4.1 Error Recovery

File: /home/user/claude-flow/src/utils/error-recovery.ts

• Lines: N/A (reference only)
• Usage: Detects better-sqlite3 errors
• Functions:
  • isNpmCacheError() - Line 28-34
  • isNativeModuleVersionError() - Line 40-46
  • getNativeModuleRecoveryMessage() - Line 52-80
  • verifyBetterSqlite3() - Line 267-274
• Migration: Add sql.js error detection
• Impact: High - Error handling infrastructure

// Line 28-34
export function isNpmCacheError(error: any): boolean {
  const errorStr = error?.message || String(error);
  return (
    errorStr.includes('ENOTEMPTY') &&
    (errorStr.includes('npm') || errorStr.includes('npx') || errorStr.includes('_npx'))
  ) || errorStr.includes('better-sqlite3'); // Line 33
}

---

5. Patches & Workarounds

5.1 Agentic-Flow SQLite Fix

File: /home/user/claude-flow/scripts/fix-agentic-flow-sqlite.sh

• Line: 6, 16, 25-26
• Usage: Patches external dependency
• Purpose: Fixes broken better-sqlite3 import in agentic-flow
• Migration: Keep patch, add documentation
• Impact: Low - External dependency fix

# Line 6
QUERIES_FILE="node_modules/agentic-flow/dist/reasoningbank/db/queries.js"

# Line 16
if grep -q "import Database from 'better-sqlite3'" "$QUERIES_FILE"; then

# Line 25-26
sed -i '5s/const BetterSqlite3 = null; \/\/ Not used/import Database from '\''better-sqlite3'\'';/' "$QUERIES_FILE"
sed -i 's/new BetterSqlite3(/new Database(/g' "$QUERIES_FILE"

---

5.2 Hive Mind Timezone Fix Patch

File: /home/user/claude-flow/src/patches/hive-mind-timezone-fix.patch

• Line: 77
• Usage: Patch file showing better-sqlite3 import
• Migration: Update patch if needed
• Impact: None - Documentation only

# Line 77
+ import Database from 'better-sqlite3';

---

6. External Dependencies

6.1 agentic-flow

Package: agentic-flow@^1.9.4

• Status: Optional dependency
• Uses: better-sqlite3 for ReasoningBank database
• File: dist/reasoningbank/db/queries.js
• Workaround: Postinstall patch script
• Migration:
  • Keep as-is (optional dependency)
  • Document feature unavailability without better-sqlite3
  • Future: Consider contributing sql.js support upstream

Impact: Medium - Disables ReasoningBank features on Windows

---

6.2 agentdb

Package: agentdb@^1.6.1

• Status: Optional dependency
• Uses: better-sqlite3 for vector database
• Patch: patches/agentdb-fix-imports.patch
• Migration:
  • Keep as-is (optional dependency)
  • Unlikely to support sql.js (performance requirements)
  • Document vector search unavailability without better-sqlite3

Impact: Medium - Disables vector search on Windows

---

7. Install Scripts

7.1 ARM64 Installation Helper

File: /home/user/claude-flow/scripts/install-arm64.js

• Lines: 9, 11
• Usage: Test and rebuild better-sqlite3 on macOS ARM64
• Migration: Add sql.js check as fallback
• Impact: Low - Platform-specific helper

// Line 7-16
async function checkSqliteBindings() {
  try {
    const Database = await import('better-sqlite3'); // Line 9
    const db = new Database.default(':memory:'); // Line 11
    db.close();
    return true;
  } catch (error) {
    // Silently fail - this is expected when better-sqlite3 doesn't compile
    return false;
  }
}

---

Migration Priority Matrix

Critical Priority (Week 1)

1. sqlite-wrapper.js - Core abstraction, affects all database operations
2. DatabaseManager.ts - Provider selection logic
3. backends/sqlite.ts - Template for sql.js backend

High Priority (Week 2)

4. persistence.ts - Agent/task persistence
5. database-service.ts - API layer
6. error-recovery.ts - Error handling infrastructure

Medium Priority (Week 3)

7. hive-mind-init.js - Initialization logic
8. sqlite-store.js - MCP memory persistence
9. All CLI commands (9 files) - Utilities

Low Priority (Week 4)

10. Patches - Update documentation only
11. install-arm64.js - Platform helper
12. External dependencies - Document limitations

---

Testing Requirements

Per-File Testing

Each modified file should have:

• Unit tests with sql.js provider
• Unit tests with better-sqlite3 provider
• Unit tests with JSON fallback
• Integration tests for provider switching
• Error handling tests

Platform Testing

• Windows 10/11 (sql.js primary)
• macOS Intel (better-sqlite3 primary)
• macOS ARM64 (better-sqlite3 with rebuild)
• Linux Ubuntu (better-sqlite3 primary)
• Alpine Linux (sql.js fallback)
• WSL (mixed environment)

---

Estimated Migration Effort

File	Lines to Change	Complexity	Hours
sqlite-wrapper.js	~100	High	8
DatabaseManager.ts	~50	Medium	4
backends/sqljs.ts (new)	~350	High	12
persistence.ts	~20	Low	2
database-service.ts	~10	Low	1
error-recovery.ts	~30	Medium	3
CLI files (9)	~5 each	Low	5
Tests (new)	~500	High	16
Documentation	N/A	Medium	8
Total	~1065	-	59 hours

Estimated timeline: ~8 working days (1 developer)

---

Code Patterns to Replace

Pattern 1: Direct Import

// Before
import Database from 'better-sqlite3';
const db = new Database('path/to/db.sqlite');

// After
import { createDatabase } from './sqlite-wrapper.js';
const db = await createDatabase('path/to/db.sqlite');

Pattern 2: Dynamic Import

// Before
const Database = (await import('better-sqlite3')).default;

// After
const { createDatabase } = await import('./sqlite-wrapper.js');
const db = await createDatabase(dbPath);

Pattern 3: Require

// Before
const Database = require('better-sqlite3');

// After
const { createDatabase } = require('./sqlite-wrapper.js');
// (or migrate to ESM)

---

Validation Checklist

Pre-Migration

• All 17 files catalogued
• Dependencies mapped
• Test coverage baseline established
• Performance benchmarks collected

During Migration

• sqlite-wrapper.js supports sql.js
• Provider selection logic implemented
• All files migrated to use abstraction
• Tests passing on all platforms

Post-Migration

• Windows installation success rate >95%
• Performance within 2-5x of better-sqlite3
• No regressions on Linux/macOS
• Documentation updated

---

Document Version: 1.0 Last Updated: 2026-01-03 Status: Complete inventory for migration planning