SQLCipher Encrypted Database Expert

What this skill does

# SQLCipher Encrypted Database Expert

## 0. Mandatory Reading Protocol

**CRITICAL**: Before implementing encryption operations, read the relevant reference files:

| Trigger | Reference File |
|---------|----------------|
| First-time encryption setup, key derivation, memory handling | `references/security-examples.md` |
| SQLite migration, custom PRAGMAs, performance tuning, backups | `references/advanced-patterns.md` |
| Security architecture, threat assessment, key compromise planning | `references/threat-model.md` |

---

## 1. Overview

**Risk Level: HIGH**

**Justification**: SQLCipher handles encryption of sensitive data at rest. Improper key management can lead to data exposure, weak key derivation enables brute-force attacks, and cryptographic misconfigurations can completely compromise security guarantees.

You are an expert in SQLCipher encrypted database development, specializing in:
- **Encryption key management** with secure derivation and storage
- **Key rotation** without data loss or downtime
- **Cryptographic best practices** for AES-256 configuration
- **Secure memory handling** to prevent key exposure
- **Migration strategies** from plain SQLite to encrypted databases

### Primary Use Cases
- Encrypted local storage for sensitive user data
- HIPAA/GDPR compliant data storage
- Secure credential and secret management
- Privacy-focused applications

---

## 2. Core Principles

### 2.1 Development Principles

1. **TDD First** - Write tests before implementation for all encryption operations
2. **Performance Aware** - Optimize cipher configuration and page sizes for efficiency
3. **Use strong key derivation** - PBKDF2 with high iteration counts (256000+)
4. **Never hardcode encryption keys** - Derive from user input or secure storage
5. **Secure memory handling** - Zero out keys after use
6. **Implement key rotation** - Plan for compromised keys
7. **Monitor dependencies** - Track OpenSSL and SQLite CVEs

### 2.2 Data Protection Principles

1. **Encryption at rest** with AES-256-CBC
2. **HMAC verification** for integrity checking
3. **Secure key storage** using OS keychain/credential manager
4. **Backup encryption** with independent keys
5. **Secure deletion** with PRAGMA secure_delete

---

## 3. Technical Foundation

### 3.1 Version Recommendations

| Component | Recommended | Minimum | Notes |
|-----------|-------------|---------|-------|
| SQLCipher | 4.9+ | 4.5 | Security updates |
| OpenSSL | 3.0+ | 1.1.1 | CVE patches |
| sqlcipher crate | 0.3+ | 0.3 | Rust bindings |

### 3.2 Required Dependencies (Cargo.toml)

```toml
[dependencies]
rusqlite = { version = "0.31", features = ["bundled-sqlcipher"] }
zeroize = "1.7"  # Secure memory zeroing
keyring = "2.0"  # OS credential storage
argon2 = "0.5"   # Optional: stronger KDF
```

---

## 4. Implementation Workflow (TDD)

### Step 1: Write Failing Test First

```python
# tests/test_encrypted_db.py
import pytest
from pathlib import Path

class TestEncryptedDatabase:
    def test_database_file_is_encrypted(self, tmp_path):
        db_path = tmp_path / "test.db"
        key = "x'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef'"
        db = EncryptedDatabase(db_path, key)
        db.execute("CREATE TABLE secrets (data TEXT)")
        db.execute("INSERT INTO secrets VALUES ('super-secret-value')")
        db.close()
        raw_content = db_path.read_bytes()
        assert b"super-secret-value" not in raw_content
        assert b"SQLite format" not in raw_content

    def test_wrong_key_fails_to_open(self, tmp_path):
        db_path = tmp_path / "test.db"
        correct_key = "x'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef'"
        wrong_key = "x'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'"
        db = EncryptedDatabase(db_path, correct_key)
        db.execute("CREATE TABLE test (id INTEGER)")
        db.close()
        with pytest.raises(DatabaseDecryptionError):
            EncryptedDatabase(db_path, wrong_key)

    def test_key_rotation_preserves_data(self, tmp_path):
        db_path, backup_path = tmp_path / "test.db", tmp_path / "backup.db"
        old_key = "x'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef'"
        new_key = "x'fedcba9876543210fedcba9876543210fedcba9876543210fedcba9876543210'"
        db = EncryptedDatabase(db_path, old_key)
        db.execute("CREATE TABLE data (value TEXT)")
        db.execute("INSERT INTO data VALUES ('preserved')")
        db.rotate_key(new_key, backup_path)
        db.close()
        with pytest.raises(DatabaseDecryptionError):
            EncryptedDatabase(db_path, old_key)
        db = EncryptedDatabase(db_path, new_key)
        assert db.query("SELECT value FROM data")[0][0] == "preserved"

    def test_key_derivation_produces_valid_key(self):
        password = "user-password"
        key, salt = derive_key_from_password(password)
        assert key.startswith("x'") and key.endswith("'") and len(key) == 67
        key2, _ = derive_key_from_password(password, salt)
        assert key == key2
```

### Step 2: Implement Minimum to Pass

```python
# src/encrypted_db.py
import sqlite3
from pathlib import Path

class DatabaseDecryptionError(Exception):
    pass

class EncryptedDatabase:
    def __init__(self, path: Path, key: str):
        self.path = path
        self.conn = sqlite3.connect(str(path))
        self.conn.execute(f"PRAGMA key = {key}")  # MUST be first
        self.conn.executescript("""
            PRAGMA cipher_compatibility = 4;
            PRAGMA cipher_memory_security = ON;
            PRAGMA foreign_keys = ON;
        """)
        try:
            self.conn.execute("SELECT count(*) FROM sqlite_master").fetchone()
        except sqlite3.DatabaseError as e:
            raise DatabaseDecryptionError(f"Failed to decrypt: {e}")

    def rotate_key(self, new_key: str, backup_path: Path) -> None:
        backup = sqlite3.connect(str(backup_path))
        self.conn.backup(backup)
        backup.close()
        self.conn.execute(f"PRAGMA rekey = {new_key}")
```

### Step 3: Refactor and Optimize

Apply performance patterns from Section 6 after tests pass.

### Step 4: Run Full Verification

```bash
# Run all tests with coverage
pytest tests/test_encrypted_db.py -v --cov=src --cov-report=term-missing

# Security-specific tests
pytest tests/test_encrypted_db.py -k "encrypted or key" -v

# Performance benchmarks
pytest tests/test_encrypted_db.py --benchmark-only
```

---

## 5. Implementation Patterns

### 5.1 Encrypted Database Initialization

```rust
use rusqlite::{Connection, Result};
use zeroize::Zeroizing;

pub struct EncryptedDatabase { conn: Connection }

impl EncryptedDatabase {
    pub fn new(path: &Path, key: &Zeroizing<String>) -> Result<Self> {
        let conn = Connection::open(path)?;
        conn.pragma_update(None, "key", key.as_str())?;  // MUST be first

        conn.execute_batch("
            PRAGMA cipher_compatibility = 4;
            PRAGMA cipher_memory_security = ON;
            PRAGMA foreign_keys = ON;
            PRAGMA journal_mode = WAL;
        ")?;

        // Verify encryption is active
        let page_size: i32 = conn.pragma_query_value(None, "cipher_page_size", |row| row.get(0))?;
        if page_size == 0 { return Err(rusqlite::Error::InvalidQuery); }

        Ok(Self { conn })
    }
}
```

### 5.2 Secure Key Derivation

```rust
use argon2::{Argon2, PasswordHasher};
use zeroize::Zeroizing;

pub fn derive_key_from_password(
    password: &str,
    stored_salt: Option<&str>
) -> Result<(Zeroizing<String>, String), argon2::password_hash::Error> {
    let salt = match stored_salt {
        Some(s) => SaltString::from_b64(s)?,
        None => SaltString::generate(&mut OsRng),
    };

    let argon2 = Argon2::new(
        argon2::Algorithm::Argon2id, argon2::Version::V0x13,
        argon2::Params::new(65536, 3, 4, Some(32)).unwrap()  // 64MB, 3 iter, 4 threads
    );

    let mut key_bytes = [0u8; 32];
    argon2.hash_p