Feature: Implement SQLite database encryption for enhanced security

## Feature Request

Implement SQLite database encryption using `better-sqlite3-multiple-ciphers` to protect sensitive data stored locally, including repository information, PR data, and cached API responses.

## Description

Add encryption to the SQLite database to ensure that sensitive data (repository info, PR details, comments, etc.) is protected at rest. This adds an additional layer of security beyond the token storage.

## Implementation Details

### 1. Core Implementation

#### Database Initialization with Encryption
```typescript
import Database from 'better-sqlite3-multiple-ciphers';
import * as crypto from 'crypto';
import { safeStorage } from 'electron';
import * as path from 'path';
import * as fs from 'fs';

class EncryptedDatabase {
  private db: Database.Database;
  private dbPassword: string;
  
  private async initializeDatabase(data: DBWorkerInitData) {
    try {
      // Ensure directory exists
      log('Initializing encrypted database in worker');
      const dbDir = path.dirname(data.dbPath);
      if (!fs.existsSync(dbDir)) {
        fs.mkdirSync(dbDir, { recursive: true });
      }

      // Initialize database with encryption support
      this.db = new Database(data.dbPath, {
        verbose: data.isDevelopment ? (msg) => log('[SQL]', msg) : undefined,
      });

      // Set encryption key if provided
      if (data.dbPassword) {
        log('Setting database encryption');
        // Use SQLCipher encryption
        this.db.pragma(`key='${data.dbPassword}'`);
        
        // Verify encryption is working
        try {
          this.db.pragma('cipher_version');
          log('Database encryption enabled successfully');
        } catch (error) {
          log('Failed to enable encryption:', error);
          throw new Error('Database encryption failed');
        }
      }

      // Set pragmas after encryption key
      this.db.pragma('journal_mode = WAL');
      this.db.pragma('synchronous = NORMAL');
      this.db.pragma('cache_size = -64000'); // 64MB
      this.db.pragma('temp_store = MEMORY');
      this.db.pragma('foreign_keys = ON');
      
      // Additional security pragmas
      this.db.pragma('cipher_page_size = 4096');
      this.db.pragma('cipher_memory_security = ON'); // Clear memory when freed
      
      // Run migrations
      await this.runMigrations();
      
      log('Database initialized successfully');
    } catch (error) {
      log('Database initialization failed:', error);
      throw error;
    }
  }
}
```

### 2. Password Management

#### Secure Password Generation and Storage
```typescript
class DatabasePasswordManager {
  private static readonly SERVICE_NAME = 'bottleneck';
  private static readonly ACCOUNT_NAME = 'db-encryption-key';
  
  /**
   * Generate or retrieve database encryption password
   */
  static async getOrCreatePassword(): Promise<string> {
    try {
      // Try to get existing password from keychain
      let password = await this.getStoredPassword();
      
      if (!password) {
        // Generate new password if none exists
        password = this.generateSecurePassword();
        await this.storePassword(password);
        log('Generated new database encryption password');
      } else {
        log('Retrieved existing database encryption password');
      }
      
      return password;
    } catch (error) {
      log('Error managing database password:', error);
      throw error;
    }
  }
  
  /**
   * Generate cryptographically secure password
   */
  private static generateSecurePassword(): string {
    // Generate 32 bytes of random data for 256-bit key
    const buffer = crypto.randomBytes(32);
    return buffer.toString('base64');
  }
  
  /**
   * Store password in OS keychain
   */
  private static async storePassword(password: string): Promise<void> {
    if (safeStorage.isEncryptionAvailable()) {
      const encrypted = safeStorage.encryptString(password);
      await keytar.setPassword(
        this.SERVICE_NAME,
        this.ACCOUNT_NAME,
        encrypted.toString('base64')
      );
    } else {
      // Fallback to keytar without additional encryption
      await keytar.setPassword(
        this.SERVICE_NAME,
        this.ACCOUNT_NAME,
        password
      );
    }
  }
  
  /**
   * Retrieve password from OS keychain
   */
  private static async getStoredPassword(): Promise<string | null> {
    const stored = await keytar.getPassword(
      this.SERVICE_NAME,
      this.ACCOUNT_NAME
    );
    
    if (!stored) return null;
    
    if (safeStorage.isEncryptionAvailable()) {
      const buffer = Buffer.from(stored, 'base64');
      return safeStorage.decryptString(buffer);
    }
    
    return stored;
  }
  
  /**
   * Rotate encryption password (requires re-encryption)
   */
  static async rotatePassword(db: Database): Promise<void> {
    const newPassword = this.generateSecurePassword();
    
    // Re-encrypt database with new password
    db.pragma(`rekey='${newPassword}'`);
    
    // Store new password
    await this.storePassword(newPassword);
    
    log('Database encryption password rotated successfully');
  }
}
```

### 3. Migration Support

#### Migrate Existing Unencrypted Database
```typescript
class DatabaseMigration {
  /**
   * Migrate from unencrypted to encrypted database
   */
  static async migrateToEncrypted(
    unencryptedPath: string,
    encryptedPath: string,
    password: string
  ): Promise<void> {
    try {
      log('Starting database encryption migration');
      
      // Open unencrypted database
      const sourceDb = new Database(unencryptedPath, { readonly: true });
      
      // Create new encrypted database
      const targetDb = new Database(encryptedPath);
      targetDb.pragma(`key='${password}'`);
      
      // Copy schema and data
      const tables = sourceDb
        .prepare("SELECT name FROM sqlite_master WHERE type='table'")
        .all();
      
      targetDb.exec('BEGIN TRANSACTION');
      
      for (const table of tables) {
        // Get table schema
        const schema = sourceDb
          .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name=?`)
          .get(table.name);
        
        // Create table in encrypted db
        targetDb.exec(schema.sql);
        
        // Copy data
        const data = sourceDb.prepare(`SELECT * FROM ${table.name}`).all();
        if (data.length > 0) {
          const columns = Object.keys(data[0]);
          const placeholders = columns.map(() => '?').join(',');
          const insert = targetDb.prepare(
            `INSERT INTO ${table.name} (${columns.join(',')}) VALUES (${placeholders})`
          );
          
          for (const row of data) {
            insert.run(...columns.map(col => row[col]));
          }
        }
      }
      
      targetDb.exec('COMMIT');
      
      // Close databases
      sourceDb.close();
      targetDb.close();
      
      // Backup unencrypted database
      const backupPath = `${unencryptedPath}.backup.${Date.now()}`;
      fs.renameSync(unencryptedPath, backupPath);
      
      // Move encrypted database to original location
      fs.renameSync(encryptedPath, unencryptedPath);
      
      log('Database encryption migration completed');
    } catch (error) {
      log('Database migration failed:', error);
      throw error;
    }
  }
  
  /**
   * Check if database needs migration
   */
  static async needsMigration(dbPath: string): Promise<boolean> {
    try {
      const db = new Database(dbPath, { readonly: true });
      
      // Try to read without password - if it works, it's unencrypted
      try {
        db.pragma('user_version');
        db.close();
        return true; // Needs encryption
      } catch {
        db.close();
        return false; // Already encrypted
      }
    } catch {
      return false; // Database doesn't exist yet
    }
  }
}
```

### 4. Worker Thread Implementation

#### Database Worker with Encryption
```typescript
// database.worker.ts
import { parentPort, workerData } from 'worker_threads';
import Database from 'better-sqlite3-multiple-ciphers';

interface DBWorkerInitData {
  dbPath: string;
  dbPassword?: string;
  isDevelopment: boolean;
}

class DatabaseWorker {
  private db: Database.Database | null = null;
  
  constructor() {
    this.initialize();
  }
  
  private async initialize() {
    if (!parentPort) throw new Error('Not in worker thread');
    
    parentPort.on('message', async (message) => {
      const { type, data, id } = message;
      
      try {
        let result;
        
        switch (type) {
          case 'init':
            result = await this.initializeDatabase(data as DBWorkerInitData);
            break;
          case 'query':
            result = await this.executeQuery(data);
            break;
          case 'execute':
            result = await this.executeStatement(data);
            break;
          case 'backup':
            result = await this.backupDatabase(data);
            break;
          case 'verify':
            result = await this.verifyEncryption();
            break;
          default:
            throw new Error(`Unknown message type: ${type}`);
        }
        
        parentPort.postMessage({ id, result });
      } catch (error) {
        parentPort.postMessage({ 
          id, 
          error: error instanceof Error ? error.message : String(error) 
        });
      }
    });
  }
  
  private async verifyEncryption(): Promise<boolean> {
    try {
      // Check if encryption is enabled
      const cipherVersion = this.db?.pragma('cipher_version');
      return !!cipherVersion;
    } catch {
      return false;
    }
  }
}

// Start worker
new DatabaseWorker();
```

### 5. Configuration

#### Package.json Dependencies
```json
{
  "dependencies": {
    "better-sqlite3-multiple-ciphers": "^9.0.0",
    "keytar": "^7.9.0"
  },
  "devDependencies": {
    "@types/better-sqlite3": "^7.6.0"
  }
}
```

#### Electron Builder Configuration
```javascript
// electron-builder.json
{
  "build": {
    "npmRebuild": true,
    "nodeGypRebuild": true,
    "buildDependenciesFromSource": true,
    "nativeRebuilds": true
  },
  "mac": {
    "entitlements": "./build/entitlements.mac.plist",
    "entitlementsInherit": "./build/entitlements.mac.plist"
  }
}
```

### 6. Security Considerations

#### Best Practices
```typescript
class SecurityBestPractices {
  /**
   * Clear sensitive data from memory
   */
  static clearSensitiveData(data: any) {
    if (typeof data === 'string') {
      // Overwrite string in memory (where possible)
      const buffer = Buffer.from(data);
      crypto.randomFillSync(buffer);
    } else if (Buffer.isBuffer(data)) {
      crypto.randomFillSync(data);
    }
  }
  
  /**
   * Validate database integrity
   */
  static async validateIntegrity(db: Database): Promise<boolean> {
    try {
      const result = db.pragma('integrity_check');
      return result[0].integrity_check === 'ok';
    } catch (error) {
      log('Integrity check failed:', error);
      return false;
    }
  }
  
  /**
   * Setup security event logging
   */
  static logSecurityEvent(event: string, details?: any) {
    const logEntry = {
      timestamp: new Date().toISOString(),
      event,
      details: details || {},
      // Never log passwords or sensitive data
    };
    
    // Write to secure log file
    fs.appendFileSync(
      path.join(app.getPath('userData'), 'security.log'),
      JSON.stringify(logEntry) + '\n'
    );
  }
}
```

### 7. User Interface

#### Encryption Status Display
```typescript
interface EncryptionStatus {
  enabled: boolean;
  algorithm: string;
  keyDerivation: string;
  pageSize: number;
  lastRotated?: Date;
}

class EncryptionStatusUI {
  static async getStatus(): Promise<EncryptionStatus> {
    const db = await getDatabase();
    
    return {
      enabled: await db.isEncrypted(),
      algorithm: 'SQLCipher 4 (AES-256-CBC)',
      keyDerivation: 'PBKDF2-HMAC-SHA512',
      pageSize: 4096,
      lastRotated: await this.getLastRotationDate()
    };
  }
  
  static renderStatus(status: EncryptionStatus) {
    return `
      <div class="encryption-status">
        <h3>Database Encryption</h3>
        <div class="status-indicator ${status.enabled ? 'secure' : 'warning'}">
          ${status.enabled ? '🔒 Encrypted' : '⚠️ Not Encrypted'}
        </div>
        
        ${status.enabled ? `
          <div class="encryption-details">
            <p>Algorithm: ${status.algorithm}</p>
            <p>Key Derivation: ${status.keyDerivation}</p>
            <p>Page Size: ${status.pageSize} bytes</p>
            ${status.lastRotated ? `
              <p>Key Last Rotated: ${status.lastRotated.toLocaleDateString()}</p>
            ` : ''}
          </div>
          
          <button onclick="rotateEncryptionKey()">
            Rotate Encryption Key
          </button>
        ` : `
          <button onclick="enableEncryption()">
            Enable Encryption
          </button>
        `}
      </div>
    `;
  }
}
```

## Benefits

- **Data Protection**: All cached data is encrypted at rest
- **Compliance**: Helps meet data protection requirements
- **Defense in Depth**: Additional security layer beyond token encryption
- **Performance**: SQLCipher is optimized for performance
- **Transparent**: Encryption is transparent to the application layer

## Acceptance Criteria

- [ ] Database is encrypted using SQLCipher
- [ ] Encryption password is securely generated and stored
- [ ] Existing databases can be migrated to encrypted format
- [ ] Encryption status is visible in UI
- [ ] Performance impact is minimal (<10% overhead)
- [ ] Database can be decrypted with correct password
- [ ] Backup mechanism works with encrypted databases
- [ ] Key rotation functionality works
- [ ] Memory is properly cleared after use
- [ ] Works on all platforms (Windows, macOS, Linux)
- [ ] Native dependencies build correctly
- [ ] Error handling for corruption/wrong password

## Testing Considerations

- Test migration from unencrypted to encrypted
- Test wrong password handling
- Test database corruption recovery
- Performance benchmarks with encryption
- Cross-platform testing
- Memory leak testing

## Resources

- [better-sqlite3-multiple-ciphers](https://github.com/m4heshd/better-sqlite3-multiple-ciphers)
- [SQLCipher Documentation](https://www.zetetic.net/sqlcipher/)
- [Electron safeStorage API](https://www.electronjs.org/docs/latest/api/safe-storage)

🤖 Generated with [Claude Code](https://claude.ai/code)

Navigation Menu

Feature: Implement SQLite database encryption for enhanced security #28

Feature Request

Description

Implementation Details

1. Core Implementation

Database Initialization with Encryption

2. Password Management

Secure Password Generation and Storage

3. Migration Support

Migrate Existing Unencrypted Database

4. Worker Thread Implementation

Database Worker with Encryption

5. Configuration

Package.json Dependencies

Electron Builder Configuration

6. Security Considerations

Best Practices

7. User Interface

Encryption Status Display

Benefits

Acceptance Criteria

Testing Considerations

Resources

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Feature: Implement SQLite database encryption for enhanced security #28

Description

Feature Request

Description

Implementation Details

1. Core Implementation

Database Initialization with Encryption

2. Password Management

Secure Password Generation and Storage

3. Migration Support

Migrate Existing Unencrypted Database

4. Worker Thread Implementation

Database Worker with Encryption

5. Configuration

Package.json Dependencies

Electron Builder Configuration

6. Security Considerations

Best Practices

7. User Interface

Encryption Status Display

Benefits

Acceptance Criteria

Testing Considerations

Resources

Activity

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions