# 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

## Legacy migration (historical)

LDR has since moved to a per-user encrypted SQLCipher database model.
The original `deep_research.db` + `research_history.db` → `ldr.db`
migration tooling described in earlier versions of this README has been
removed along with the deprecated `ldr` CLI. The database split it
addressed predates the per-user model by several releases, so anyone
upgrading from a version that old should start fresh rather than
attempting to recover the legacy data.
There is no supported migration path from pre-v0.x databases.

## Schema upgrades

Schema migrations run automatically on application startup via Alembic
(`src/local_deep_research/database/migrations/`). No manual command is
required.

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

## Troubleshooting

If you encounter database issues:

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`
