learningcircuit--local-deep-research/docs/security/database-backup.md

# Database Backup System

Local Deep Research includes an automatic database backup system that creates encrypted backups of your user database after each successful login.

## Overview

- **Automatic**: Backups run in the background after login without blocking the UI
- **Encrypted**: Backups use the same encryption as your main database
- **Safe**: Uses SQLCipher's `sqlcipher_export()` for atomic backups that work correctly with WAL mode
- **Configurable**: Enable/disable and configure retention via settings
- **Pre-migration**: A backup is automatically created before any database schema migration

## How It Works

1. When you log in successfully, a background backup is scheduled
2. Only one backup per calendar day is created — subsequent logins the same day are skipped to prevent a corrupted database from overwriting all good backups
3. The backup runs in a separate thread (non-blocking)
4. Uses `sqlcipher_export()` to create an encrypted copy preserving all cipher settings
5. Old backups are automatically cleaned up based on your retention settings
6. Before database migrations, a backup is always created regardless of the daily limit

## Backup Location

Backups are stored in:
```
{data_directory}/encrypted_databases/backups/{user_hash}/
```

Where `{user_hash}` is the first 16 hex characters of the SHA-256 hash of your username.

Each backup file is named with a timestamp:
```
ldr_backup_20250125_143022.db
```

## Settings

Configure backup behavior in Settings > Backup:

| Setting | Default | Description |
|---------|---------|-------------|
| **Enable Auto-Backup** | `true` | Enable/disable automatic backups on login. Disable if disk space is limited. |
| **Max Backups** | `1` | Maximum number of backup files to keep (1-30) |
| **Backup Retention (days)** | `7` | Delete backups older than this many days |

**Note**: Each backup is a full encrypted copy of your database and cannot be compressed. With the default of 1 backup, disk usage equals your database size. Users with large databases (e.g., containing uploaded PDFs) should monitor disk usage and can reduce the backup count or disable backups entirely via the **Enable Auto-Backup** setting if disk space is limited.

## Why sqlcipher_export()?

We use SQLCipher's `sqlcipher_export()` instead of `VACUUM INTO` or simple file copy because:

1. **Encryption Preservation**: `VACUUM INTO` does not preserve SQLCipher encryption settings. `sqlcipher_export()` correctly copies data while maintaining the same encryption key and cipher configuration
2. **WAL Safety**: Regular file copy can corrupt databases using WAL (Write-Ahead Logging) mode
3. **Atomic Operation**: The backup uses ATTACH + export + DETACH, and is written to a temporary file then atomically renamed
4. **Integrity Verification**: Each backup is verified with `PRAGMA quick_check` before being finalized

## Restoring from Backup

To restore from a backup:

1. Stop the application
2. Locate your backup in the backup directory
3. Copy it to replace your current database file (keep the `.salt` file alongside it)
4. Restart the application

**Important**: The backup uses the same password as when it was created. If you've changed your password since the backup, you'll need to use the old password to access it.

## Troubleshooting

### Backups not being created

1. Check if backups are enabled in Settings
2. Check the logs for backup-related errors
3. Verify sufficient disk space (requires 2x database size)

### Disk space issues

The system checks for available disk space before creating a backup. If you see "Insufficient disk space" errors:

1. Free up disk space
2. Reduce the max backup count setting
3. Reduce the retention days setting

### Backup verification failed

If you see "Backup verification failed" in logs, the backup may be corrupted. This can happen if:

1. The disk ran out of space during backup
2. There was a system crash during backup
3. The source database is corrupted

In this case, the corrupted backup file is automatically deleted.