# How Per-User SQLCipher Database Encryption Works in Local-Deep-Research: Implementation and Password Management

> Learn how Local-Deep-Research enables per-user SQLCipher encryption for secure database management. Explore implementation details and password rotation with sqlcipher_utils.py.

- Repository: [learningcircuit/local-deep-research](https://github.com/learningcircuit/local-deep-research)
- Tags: internals
- Published: 2026-03-05

---

**Local-Deep-Research implements per-user SQLCipher database encryption using PBKDF2-HMAC-SHA-512 key derivation with a unique 32-byte random salt stored separately for each database, allowing secure password management and rotation via the [`sqlcipher_utils.py`](https://github.com/learningcircuit/local-deep-research/blob/main/sqlcipher_utils.py) module.**

The repository `learningcircuit/local-deep-research` provides a robust encryption layer that protects user SQLite databases at rest. This implementation ensures each user database has its own cryptographic salt while supporting legacy databases and configurable security parameters through environment variables.

## How Per-User SQLCipher Encryption Works

The encryption system in [`src/local_deep_research/database/sqlcipher_utils.py`](https://github.com/learningcircuit/local-deep-research/blob/main/src/local_deep_research/database/sqlcipher_utils.py) creates a hardened security boundary around SQLite databases using industry-standard algorithms.

### Per-Database Salt Generation

When creating a new database, the system generates a **32-byte random salt** using `create_database_salt()`. This salt persists in a sidecar file named `<database>.salt` alongside the database file. For databases created before version 2.0, the system falls back to a constant `LEGACY_PBKDF2_SALT` value to maintain backward compatibility.

The `get_salt_for_database()` function handles this logic transparently, checking for the existence of the salt file before defaulting to the legacy constant. This per-database salting prevents rainbow-table attacks across multiple user databases.

### PBKDF2 Key Derivation

The `_get_key_from_password()` function derives encryption keys using **PBKDF2-HMAC-SHA-512**. The implementation caches results using Python's `@lru_cache` decorator to avoid recomputing expensive key derivations for repeated connections within the same process.

The derivation uses parameters from `get_sqlcipher_settings()`, including the iteration count (`kdf_iterations`) controlled via environment variables. The public `get_key_from_password()` wrapper ensures the database path is properly associated with the correct salt during derivation.

### Database Connection Lifecycle

The `create_sqlcipher_connection()` factory function orchestrates the complete connection sequence. For new databases (`creation_mode=True`), it applies cipher defaults before setting the key; for existing databases, it proceeds directly to key configuration.

The function executes `set_sqlcipher_key()` to configure the encryption key via the hex-encoded `PRAGMA key = "x'<hex>'"` syntax. This approach avoids SQL injection risks while supporting both raw `sqlcipher3` connections and SQLAlchemy engines. After key establishment, `apply_sqlcipher_pragmas()` enforces cipher settings including `cipher_page_size`, `cipher_hmac_algorithm`, and `cipher_kdf_algorithm`, followed by `apply_performance_pragmas()` for SQLite optimization.

## Managing Passwords in Local-Deep-Research

Password management follows a straightforward API that handles salt retrieval, key derivation, and secure password rotation.

### Creating New Encrypted Databases

To initialize a new encrypted database for a user, first generate the salt file, then open the connection with `creation_mode=True`:

```python
from pathlib import Path
from local_deep_research.database.sqlcipher_utils import (
    create_database_salt,
    create_sqlcipher_connection,
)

db_path = Path("data/user_alice.db")

# Generate per-database salt (execute once)

create_database_salt(db_path)

# Create encrypted database

conn = create_sqlcipher_connection(
    db_path,
    password="SecureUserPassword123!",
    creation_mode=True
)

conn.execute("CREATE TABLE research_notes (id INTEGER PRIMARY KEY, content TEXT)")
conn.commit()
conn.close()

```

### Opening Existing Databases

For subsequent connections, omit `creation_mode` to use the standard opening sequence:

```python
from pathlib import Path
from local_deep_research.database.sqlcipher_utils import create_sqlcipher_connection

db_path = Path("data/user_alice.db")
conn = create_sqlcipher_connection(db_path, password="SecureUserPassword123!")

# Execute queries

results = conn.execute("SELECT * FROM research_notes").fetchall()
conn.close()

```

The function automatically locates the `.salt` file and derives the correct key without additional configuration.

### Changing User Passwords

Password rotation uses `set_sqlcipher_rekey()` to re-encrypt the database with a new key derived from the updated password:

```python
from pathlib import Path
from local_deep_research.database.sqlcipher_utils import (
    create_sqlcipher_connection,
    set_sqlcipher_rekey,
)

db_path = Path("data/user_alice.db")
conn = create_sqlcipher_connection(db_path, password="SecureUserPassword123!")
cursor = conn.cursor()

# Rotate to new password

set_sqlcipher_rekey(cursor, new_password="NewStrongPassword456!", db_path=db_path)
cursor.close()
conn.close()

```

The rekey operation preserves the original salt while rendering the old password invalid. The database remains encrypted throughout the transition.

### Working with Pre-Derived Hex Keys

For scenarios requiring key persistence in secure vaults or session management, derive the hex key once and reuse it:

```python
from pathlib import Path
from local_deep_research.database.sqlcipher_utils import (
    get_key_from_password,
    create_sqlcipher_connection,
)

db_path = Path("data/user_alice.db")

# Derive once

key_bytes = get_key_from_password("SecureUserPassword123!", db_path)
hex_key = key_bytes.hex()

# Reuse for multiple connections without PBKDF2 overhead

conn = create_sqlcipher_connection(db_path, hex_key=hex_key)

```

This pattern eliminates repeated key derivation costs while ensuring the raw password never persists in memory longer than necessary.

## Security Configuration and Environment Variables

The encryption behavior is configurable through environment variables defined in [`src/local_deep_research/settings/env_registry.py`](https://github.com/learningcircuit/local-deep-research/blob/main/src/local_deep_research/settings/env_registry.py). Key settings include:

- **KDF Iterations**: Control PBKDF2 iteration counts (default 100,000+ in production)
- **Cipher Memory Security**: Enable `LDR_DB_CONFIG_CIPHER_MEMORY_SECURITY=ON` to lock memory pages and prevent swapping (requires `IPC_LOCK` capability)
- **Algorithm Selection**: Configure `cipher_hmac_algorithm` and `cipher_kdf_algorithm` via `get_sqlcipher_settings()`

The [`src/local_deep_research/database/sqlcipher_compat.py`](https://github.com/learningcircuit/local-deep-research/blob/main/src/local_deep_research/database/sqlcipher_compat.py) module provides the underlying `sqlcipher3` bindings, raising clear import errors if the SQLCipher library is missing from the environment.

## Summary

- **Per-database salt**: Each user database uses a unique 32-byte salt stored in `<db>.salt`, preventing cross-user rainbow table attacks while supporting legacy fallbacks.
- **PBKDF2-HMAC-SHA-512**: Keys derive from passwords using cached PBKDF2 with configurable iterations for brute-force resistance.
- **Hex-encoded PRAGMAs**: All key operations use hexadecimal encoding to eliminate SQL injection vectors.
- **Password rotation**: The `set_sqlcipher_rekey()` function enables seamless password changes without decrypting to disk.
- **Environment control**: Security parameters including memory locking and KDF iterations are configurable via `LDR_DB_CONFIG_*` variables.

## Frequently Asked Questions

### How does Local-Deep-Research handle databases created before the salt system was implemented?

Databases created prior to version 2.0 use the `LEGACY_PBKDF2_SALT` constant ("no salt") through the fallback mechanism in `get_salt_for_database()`. While these databases remain functional, new deployments should always call `create_database_salt()` to generate per-database salts for enhanced security.

### What happens if the .salt file is deleted or corrupted?

If the salt file is missing, `get_salt_for_database()` falls back to the legacy constant salt, causing key derivation to produce an incorrect key. The database will fail to open with the "file is not a database" error from SQLCipher. Restore the salt file from backup to regain access; the salt is required for decryption.

### Can I use SQLAlchemy with the SQLCipher encryption layer?

Yes. The `set_sqlcipher_key()` and `set_sqlcipher_rekey()` functions accept both raw `sqlite3` connections and SQLAlchemy engines, using `text()` wrappers where necessary. Pass SQLAlchemy connection objects to these utilities exactly as you would raw connections.

### How do I configure the encryption for maximum security?

Set `LDR_DB_CONFIG_CIPHER_MEMORY_SECURITY=ON` to enable memory locking (requires system `IPC_LOCK` capability), increase `kdf_iterations` beyond 100,000 in production environments via environment variables, and ensure each database has a unique salt file generated by `create_database_salt()`. Verify these settings through `get_sqlcipher_settings()` before opening sensitive connections.