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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 {
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 `

## 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)

Start by locating database.worker.ts and the existing database initialization, then review package.json and electron-builder.json for native dependency and packaging constraints. The request also names password management, migration, verification, and encryption-status UI as separate areas; define their integration points and acceptance tests before implementation.

Written by the indexing model from the issue text.