How to Migrate from v1 to the New Encrypted Database Architecture in Local-Deep-Research

To migrate from v1 to the new encrypted database architecture in Local-Deep-Research, set the LDR_ENCRYPTION_PASSWORD environment variable, execute python -m src.local_deep_research.main --migrate-db, and verify the database reports a SQLCipher version.

Local-Deep-Research (LDR) version 2.0 transitions from plaintext SQLite databases to per-user SQLCipher-encrypted storage for enhanced security. This migration process upgrades legacy v1 database files while preserving every table, index, and user-generated record through atomic operations. The migration logic resides in src/local_deep_research/database/initialize.py, which automatically invokes _run_migrations on startup and handles the low-level cipher operations via src/local_deep_research/database/sqlcipher_utils.py.

Prerequisites for Migrating to Encrypted Storage

Before initiating the migration from v1 to the new encrypted database architecture, ensure your environment meets these requirements:

• Complete Backup: Copy your entire data directory to enable rollback if errors occur. Use cp -r $LDR_DATA_DIR $LDR_DATA_DIR.backup to preserve the original state.
• SQLCipher Python Bindings: Install pysqlcipher3 or sqlcipher3 (version ≥ 0.4.0) to enable encrypted database operations. Verify installation with pip show sqlcipher3.
• Encryption Password: Define a strong passphrase in the LDR_ENCRYPTION_PASSWORD environment variable, which src/local_deep_research/settings/env_settings.py consumes via get_encryption_password().
• LDR Version 2.0 or Higher: Confirm your installation supports migration commands by running python -m src.local_deep_research.main --version.

Step-by-Step Migration Process

Configure the Encryption Password

The migration requires a per-user encryption key derived from an environment variable. Set this before running any commands:

export LDR_ENCRYPTION_PASSWORD="my-strong-passphrase"

As implemented in src/local_deep_research/settings/env_settings.py at line 74, the system emits a warning if this variable is missing during migration.

Execute the Built-in Migration Command

Run the CLI command to process all user databases:

python -m src.local_deep_research.main --migrate-db

This command triggers database.initialize.migrate_all_user_dbs() in src/local_deep_research/main.py (lines 210-225). For each user folder in ~/.local_deep_research/users/<uid>/, the system opens the legacy research.db file, creates a temporary encrypted copy, and executes PRAGMA cipher_migrate (defined in src/local_deep_research/database/sqlcipher_utils.py at line 63) to re-encrypt the content. On success, the temporary file atomically replaces the original, and the logger records Migration finished for %s with the user ID.

Verify the Encrypted Database

Confirm the database now uses SQLCipher by checking the cipher version:

from local_deep_research.database.initialize import get_user_engine

engine = get_user_engine("demo_user")  # replace with actual username

with engine.connect() as conn:
    print(conn.execute("PRAGMA cipher_version;").fetchone())

A successful migration returns a tuple like ('4.5.0',), confirming the file uses SQLCipher encryption.

Clean Up Backups (Optional)

After verifying functionality, delete the backup created during prerequisites:

rm -rf $LDR_DATA_DIR.backup

Technical Implementation Details

The core migration algorithm in src/local_deep_research/database/initialize.py performs these operations atomically:

# src/local_deep_research/database/initialize.py

def migrate_user_db(old_path: Path, new_path: Path, password: str) -> None:
    """Open a legacy DB, create an encrypted copy and replace the original."""
    # 1️⃣ Open legacy DB (plaintext)

    src_engine = create_engine(f"sqlite:///{old_path}")

    # 2️⃣ Create encrypted DB with the supplied password

    dst_uri = f"sqlite+pysqlcipher://:{password}@/{new_path}"
    dst_engine = create_engine(dst_uri)

    # 3️⃣ Copy schema + data

    with src_engine.connect() as src, dst_engine.connect() as dst:
        for line in src.execute("SELECT sql FROM sqlite_master WHERE sql NOT NULL"):
            dst.execute(line[0])

        dst.execute("ATTACH DATABASE ? AS src", (str(old_path),))
        dst.execute("INSERT INTO main.table SELECT * FROM src.table")   # repeat for each table

    # 4️⃣ Run the official cipher migration pragma (see sqlcipher_utils.py)

    with dst_engine.connect() as conn:
        conn.execute("PRAGMA cipher_migrate;")   # <‑‑ src/local_deep_research/database/sqlcipher_utils.py:L63

    # 5️⃣ Atomically replace the old file

    old_path.unlink()
    new_path.rename(old_path)

Key source references include:

• src/local_deep_research/database/sqlcipher_utils.py (line 63): Executes PRAGMA cipher_migrate, the core operation rewriting files with SQLCipher encryption.
• src/local_deep_research/database/initialize.py (lines 112-150): Orchestrates per-user migration, temporary file creation, and atomic swaps.
• src/local_deep_research/main.py (lines 210-225): Parses the --migrate-db CLI argument and dispatches to migration routines.

Summary

• Backup your data directory before starting the migration process.
• Install SQLCipher bindings (sqlcipher3 or pysqlcipher3) to enable encrypted database functionality.
• Set LDR_ENCRYPTION_PASSWORD environment variable to derive per-user encryption keys.
• Run python -m src.local_deep_research.main --migrate-db to execute atomic migrations for all users.
• Verify SQLCipher version using PRAGMA cipher_version to confirm successful encryption.

Frequently Asked Questions

Can I migrate only a single user's database instead of all users?

Yes. Call the migration function directly using python -m src.local_deep_research.database.initialize migrate_user_db <old_path> <new_path> <password>. This targets a specific user folder without processing the entire user directory tree.

What happens if the migration fails halfway through the process?

The migration is atomic because it operates on a temporary file (*.tmp) before swapping. If an error occurs, the original plaintext database remains untouched at research.db. Check the logs for the specific error, ensure your LDR_ENCRYPTION_PASSWORD is set correctly, and retry after restoring from your backup if necessary.

Do I need to modify existing code that opens database connections?

No. After migration, the get_user_engine() helper in src/local_deep_research/database/initialize.py automatically constructs the encrypted connection URI using the environment variable. Existing application code continues to function without changes to connection logic.

Why does PRAGMA cipher_migrate report "unsupported" during migration?

This error indicates your SQLite build lacks SQLCipher support. Ensure you have installed the SQLCipher Python bindings (pysqlcipher3 or sqlcipher3 version 0.4.0 or higher) and that the library is accessible in your Python environment. Standard SQLite distributions do not include cipher functionality.

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