# Database Architecture

## Overview

Local Deep Research now uses a unified database architecture with a single SQLite database file (`ldr.db`) that replaces the previous split database approach (`deep_research.db` and `research_history.db`).

The database is located at `src/data/ldr.db` within the project directory structure.

## Database-First Settings

The application now follows a "database-first" approach for settings:

1. All settings are stored in the database, in the `settings` table
2. Settings from TOML files are used only as fallbacks if a setting doesn't exist in the database
3. The web UI settings page modifies the database values directly

## Migration

If you have existing data in the legacy databases, you need to migrate it to the new unified database.

### Automatic Migration

When you start the application for the first time after updating, it will check if migration is needed:

1. If legacy databases exist and `ldr.db` doesn't exist, you'll see a warning message
2. You can run migration using the command: `python -m src.local_deep_research.main --migrate-db`
3. Alternatively, start the application with auto-migration: `python -m src.local_deep_research.main --auto-migrate`

### Manual Migration

If automatic migration doesn't work, you can:

1. Run the migration script directly: `python -m src.local_deep_research.web.database.migrate_to_ldr_db`
2. Check migration results in the log output

### Schema Upgrades

If you have already migrated your database but need to update its schema:

1. The application automatically runs schema upgrades on startup
2. You can manually run schema upgrades with: `python -m src.local_deep_research.main --schema-upgrade`
3. Current schema upgrades include:
   - Removing the redundant `research_log` table (consolidated into `research_logs`)

## Database Schema

The unified database contains:

* `research_history` - Research history entries (from research_history.db)
* `research_logs` - Consolidated logs for all research activities (merged from research_history.db)
* `research_resources` - Resources found during research (from research_history.db)
* `settings` - Application settings (from deep_research.db)
* `research` - Research data (from deep_research.db)
* `research_report` - Generated research reports (from deep_research.db)

## Thread Safety & Connection Model

The application uses per-user encrypted SQLite databases (via [SQLCipher](https://www.zetetic.net/sqlcipher/)) with a single shared [SQLAlchemy QueuePool](https://docs.sqlalchemy.org/en/20/core/pooling.html) per user:

- **QueuePool** (shared per-user engine in `DatabaseManager.connections`): Serves both Flask request handlers AND background threads (research workers, scheduler jobs, metric writers). `pool_size=20`, `max_overflow=40`, `pool_timeout=10`. Engines are created with `check_same_thread=False` so they're safe to share across threads. FD usage is bounded by `pool_size + max_overflow` per user, not by the number of active background threads.

Cleanup is handled by the `@thread_cleanup` decorator (closes sessions at thread exit, returning connections to the pool) and by a periodic `engine.dispose()` sweep every 30 minutes that mitigates a SQLCipher+WAL handle leak triggered by out-of-order connection closes.

Note: The core database module is at `src/local_deep_research/database/`, separate from this `web/database/` directory. See [Architecture - Thread & Resource Lifecycle](../../../../docs/architecture.md#thread--resource-lifecycle) for the full cleanup architecture.

## Rollback

If you need to roll back to the previous database architecture:

1. Keep backup copies of your original `deep_research.db` and `research_history.db` files
2. In case of issues, restore them and modify the database paths in the code

## Troubleshooting

If you encounter issues with database migration:

1. Check the application logs for detailed error messages
2. Ensure you have write permissions to the data directory
3. Make sure SQLite is functioning properly
4. If necessary, start with a fresh database by removing `ldr.db`
