[FEATURE] Enable SQLite WAL Mode

[phernandez]

Feature Request: Enable SQLite WAL Mode

Background

Follow-up from issue #97 - while the core Windows 11 database synchronization issues have been resolved in v0.13.x with the unified database architecture, we identified that enabling SQLite WAL (Write-Ahead Logging) mode could provide additional performance and reliability benefits, particularly on Windows.

Current Behavior

• SQLite database uses default rollback journal mode
• No explicit busy timeout or WAL mode configuration
• Basic database operations work but could be optimized for concurrent access

Proposed Enhancement

Enable SQLite WAL mode with Windows-optimized settings:

1. WAL Mode Configuration

   • PRAGMA journal_mode=WAL
   • PRAGMA synchronous=NORMAL (for better performance)
   • PRAGMA busy_timeout=5000 (5 second timeout for file locks)

2. Windows-Specific Optimizations

   • PRAGMA temp_store=MEMORY (avoid temp file conflicts)
   • PRAGMA cache_size=10000 (increase cache for better performance)

3. Implementation Location

   • Modify database initialization in src/basic_memory/models/database.py
   • Add WAL mode configuration after database creation
   • Ensure compatibility with existing database files

Benefits

• Improved concurrent access - WAL mode allows readers while writing
• Better Windows performance - Reduced file locking conflicts
• Enhanced reliability - WAL mode is more robust against crashes
• Backward compatibility - Existing databases automatically convert

Implementation Details

# Add to database initialization
async def configure_wal_mode(engine: AsyncEngine) -> None:
    """Configure SQLite WAL mode for better performance"""
    async with engine.begin() as conn:
        await conn.execute(text("PRAGMA journal_mode=WAL"))
        await conn.execute(text("PRAGMA synchronous=NORMAL"))
        await conn.execute(text("PRAGMA busy_timeout=5000"))
        await conn.execute(text("PRAGMA temp_store=MEMORY"))
        await conn.execute(text("PRAGMA cache_size=10000"))

Testing Requirements

• Test on Windows 11, macOS, and Linux
• Verify database migration from existing installations
• Performance benchmarks for concurrent operations
• Test database recovery scenarios

Acceptance Criteria

• WAL mode enabled by default for new installations
• Existing databases migrate to WAL mode automatically
• Windows-specific optimizations applied
• Performance tests show improvement
• No breaking changes to existing functionality

Created from #97