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

> Easily migrate from v1 to the new encrypted database architecture in Local-Deep-Research. Set the LDR_ENCRYPTION_PASSWORD, run the migration script, and verify your SQLCipher update.

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

---

**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`](https://github.com/learningcircuit/local-deep-research/blob/main/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`](https://github.com/learningcircuit/local-deep-research/blob/main/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`](https://github.com/learningcircuit/local-deep-research/blob/main/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:

```bash
export LDR_ENCRYPTION_PASSWORD="my-strong-passphrase"

```

As implemented in [`src/local_deep_research/settings/env_settings.py`](https://github.com/learningcircuit/local-deep-research/blob/main/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:

```bash
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`](https://github.com/learningcircuit/local-deep-research/blob/main/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`](https://github.com/learningcircuit/local-deep-research/blob/main/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:

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

```bash
rm -rf $LDR_DATA_DIR.backup

```

## Technical Implementation Details

The core migration algorithm in [`src/local_deep_research/database/initialize.py`](https://github.com/learningcircuit/local-deep-research/blob/main/src/local_deep_research/database/initialize.py) performs these operations atomically:

```python

# 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`](https://github.com/learningcircuit/local-deep-research/blob/main/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`](https://github.com/learningcircuit/local-deep-research/blob/main/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`](https://github.com/learningcircuit/local-deep-research/blob/main/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`](https://github.com/learningcircuit/local-deep-research/blob/main/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.