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

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 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 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:

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:

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:

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:

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. 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}