fix-memory-database.js

/**
 * Fix Memory Database Schema
 * 
 * This script fixes the embedding_cache table schema to match OpenClaw architecture
 */

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

const dataDir = path.join(process.cwd(), 'data', 'chat-memory', 'users');

console.log('🔧 Fixing memory database schemas...\n');

if (!fs.existsSync(dataDir)) {
  console.log('❌ No chat memory data found');
  process.exit(0);
}

const userDirs = fs.readdirSync(dataDir);
let fixed = 0;
let errors = 0;

for (const userId of userDirs) {
  const dbPath = path.join(dataDir, userId, 'memory-index.db');
  
  if (!fs.existsSync(dbPath)) {
    continue;
  }
  
  try {
    console.log(`📂 Processing user: ${userId}`);
    
    const db = new Database(dbPath);
    
    // Check if embedding_cache table exists
    const tableExists = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name='embedding_cache'
    `).get();
    
    if (tableExists) {
      // Check if provider column exists
      const columns = db.pragma('table_info(embedding_cache)');
      const hasProvider = columns.some(col => col.name === 'provider');
      
      if (!hasProvider) {
        console.log('  ⚠️  Old schema detected - recreating table...');
        
        // Backup old data
        const oldData = db.prepare('SELECT * FROM embedding_cache').all();
        
        // Drop old table
        db.exec('DROP TABLE embedding_cache');
        
        // Create new table with correct schema
        db.exec(`
          CREATE TABLE embedding_cache (
            provider TEXT NOT NULL DEFAULT 'gemini',
            model TEXT NOT NULL DEFAULT 'gemini-embedding-001',
            provider_key TEXT NOT NULL DEFAULT 'default',
            hash TEXT NOT NULL,
            embedding TEXT NOT NULL,
            dims INTEGER DEFAULT 768,
            updated_at INTEGER NOT NULL,
            PRIMARY KEY (provider, model, provider_key, hash)
          )
        `);
        
        // Migrate old data if any
        if (oldData.length > 0) {
          const insert = db.prepare(`
            INSERT INTO embedding_cache 
            (provider, model, provider_key, hash, embedding, dims, updated_at)
            VALUES ('gemini', 'gemini-embedding-001', 'default', ?, ?, 768, ?)
          `);
          
          for (const row of oldData) {
            insert.run(row.hash, row.embedding, row.updated_at);
          }
          
          console.log(`  ✅ Migrated ${oldData.length} cached embeddings`);
        } else {
          console.log('  ✅ Table recreated (no data to migrate)');
        }
        
        fixed++;
      } else {
        console.log('  ✅ Schema already correct');
      }
    } else {
      console.log('  ℹ️  No embedding_cache table (will be created on first use)');
    }
    
    db.close();
    
  } catch (error) {
    console.error(`  ❌ Error: ${error.message}`);
    errors++;
  }
}

console.log(`\n📊 Summary:`);
console.log(`  Fixed: ${fixed}`);
console.log(`  Errors: ${errors}`);
console.log(`  Total users: ${userDirs.length}`);

if (fixed > 0) {
  console.log('\n✅ Database schemas fixed! Restart the server to apply changes.');
} else if (errors === 0) {
  console.log('\n✅ All databases are up to date!');
}