debug-fts.js

#!/usr/bin/env node
import Database from 'better-sqlite3';
import path from 'path';
import os from 'os';

const dbPath = path.join(os.homedir(), '.claude', 'claude-history-enhanced.db');
const db = new Database(dbPath);

console.log('🔍 Debugging FTS Tables\n');

// Check what tables exist
console.log('📊 All tables:');
const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
tables.forEach(t => console.log(`  • ${t.name}`));

// Check FTS tables specifically
console.log('\n📝 FTS Tables:');
const ftsTables = tables.filter(t => t.name.includes('fts'));
ftsTables.forEach(t => {
  console.log(`\n  Table: ${t.name}`);

  // Get schema
  const schemaQuery = db.prepare(`SELECT sql FROM sqlite_master WHERE name = ?`).get(t.name);
  console.log(`  Schema: ${schemaQuery?.sql}`);

  // Count rows
  try {
    const count = db.prepare(`SELECT COUNT(*) as count FROM ${t.name}`).get();
    console.log(`  Rows: ${count.count}`);
  } catch (e) {
    console.log(`  Rows: Error - ${e.message}`);
  }
});

// Check key_interactions table
console.log('\n📋 key_interactions table:');
const kiCount = db.prepare('SELECT COUNT(*) as count FROM key_interactions').get();
console.log(`  Total rows: ${kiCount.count}`);

// Sample a few rows
const kiSample = db.prepare('SELECT id, conversation_id FROM key_interactions LIMIT 3').all();
console.log('  Sample rows:');
kiSample.forEach(row => {
  console.log(`    ID: ${row.id}, Conv ID: ${row.conversation_id}`);
});

// Test basic FTS query directly
console.log('\n🧪 Testing FTS queries:');

// Test 1: Direct FTS query
console.log('\nTest 1: Direct query on interactions_fts');
try {
  const ftsTest = db.prepare(`
    SELECT interaction_id
    FROM interactions_fts
    WHERE interactions_fts MATCH 'docker'
    LIMIT 3
  `).all();
  console.log(`  ✅ Success! Found ${ftsTest.length} results`);
} catch (e) {
  console.log(`  ❌ Error: ${e.message}`);
}

// Test 2: Direct query on errors_fts
console.log('\nTest 2: Direct query on errors_fts');
try {
  const errorFtsTest = db.prepare(`
    SELECT conversation_id
    FROM errors_fts
    WHERE errors_fts MATCH 'error'
    LIMIT 3
  `).all();
  console.log(`  ✅ Success! Found ${errorFtsTest.length} results`);
} catch (e) {
  console.log(`  ❌ Error: ${e.message}`);
}

// Test 3: Join query
console.log('\nTest 3: Join with key_interactions');
try {
  const joinTest = db.prepare(`
    SELECT DISTINCT ki.conversation_id
    FROM key_interactions ki
    WHERE ki.id IN (
      SELECT interaction_id
      FROM interactions_fts
      WHERE interactions_fts MATCH 'docker'
    )
    LIMIT 3
  `).all();
  console.log(`  ✅ Success! Found ${joinTest.length} results`);
} catch (e) {
  console.log(`  ❌ Error: ${e.message}`);
}

db.close();
console.log('\n✅ Debug complete!');