Data and migrations

Apollo Download Manager keeps its download records, settings and categories in one SQLite database (#26). This document describes the file, the schema and how it is upgraded. Checked against the code at commit 9a4f484 (2026-10-30, #80). The rules behind it are in .claude/rules/persistence.md.

The code is in crates/velocity-core/src/database/, and the migrations are in crates/velocity-core/migrations/. The app opens it when it starts, and the manager saves its downloads there (#58, ENGINE_CONTRACT.md).

The file

Where: Paths::database() gives the location, in Apollo Download Manager's own per-user data folder, separate from V1's (COEXISTENCE.md).
Permissions: a new database is created owner-only (0600 on Unix) before SQLite opens it, and SQLite's -wal and -shm files take its mode. On Windows the per-user data folder's permissions apply.
No links: a link, or anything else that isn't a plain file, in place of the database is refused (INVALID_PATH); it is never followed.
How SQLite runs:
- WAL journal mode, for atomic, crash-safe commits;
- synchronous=NORMAL;
- foreign keys on;
- a 5-second busy timeout;
- defensive mode, which refuses SQL that could corrupt the file. Both SQLite CVEs fixed in 3.53.2 needed defensive mode off.
SQLite: 3.54.1, compiled into Apollo Download Manager from the source inside libsqlite3-sys (DEPENDENCIES.md).

Schema

The schema version is PRAGMA user_version. Version 1 (0001_initial.sql) is Apollo Download Manager's clean baseline, version 2 adds checkpoints, version 3 the download queue, version 4 the verification, version 5 torrents, version 6 the built-in categories, and version 7 each download's activity. V1's data isn't imported (ADR-0007).

Table	Holds
`downloads`	One download per row. It records the engine (`velocity` or `libtorrent`), kind, status, source URL (never with a login), folder, file name and category. It also has the byte counts, the last error, the options to retry it, and `has_sensitive_options`. The history flags (`in_list`, `in_history`) are here too, along with the torrent info hashes (v1 and v2) and when seeding started. For direct downloads, nullable `etag`, `last_modified`, `validator_kind` (#45) and `checkpoint_ref` (#43) columns are reserved. They stay unused: #45 keeps what the first answer said about the file in the checkpoint
`downloads` (schema 3)	`0003_queue.sql`, #50: `queue_position`, an unfinished download's place in the queue (0 first; NULL outside it), and `scheduled`, whether it runs only while the schedule's window is open. `Database::save_queue` replaces the whole queue in one transaction and refuses an unknown download with nothing changed; `Database::queue` reads it back in order. A download added paused keeps `paused` as its status. The manager gives its queue in that shape (`Manager::queue`), and reads it back when the app starts (`manager/recovery.rs`, #58)
`downloads` (schema 4)	`0004_verification.sql`, #58: `verification`, what was proven about a completed download's bytes (#46), by its wire name; NULL until it completes. Its last error is in `error_json` (since schema 1), read back as `Stored::Unknown` when this build can't read it. `Database::update_download` saves a record's status, name, byte counts, error, verification, completion time and, since #31, its category; `update_options` its options after a limit or connections change; `hide_download` takes it out of the list (`in_list = 0`), keeping it in the history. A record is written with only the columns every schema has, so the upgrade tests can insert into older schemas
`downloads` (schema 7)	`0007_activity.sql`, #59: `started_at_ms`, the first time the engine reported the download running, and `active_ms` and `transferred_bytes`, how long it ran and the bytes it received meanwhile, for the list's "Started" and "Avg. speed". NULL and 0 for records saved before schema 7, since nothing was measured. `Database::update_download` saves them with the record
`settings` (schema 8)	`0008_two_at_once.sql`, #351: a saved `queue.maxConcurrentDownloads` of 3, 0.1.x's default, becomes 2, the new default (option B, PO decision). Nothing else in the document changes, and a document without that value is left alone
`downloads` (schema 9)	`0009_start_at.sql`, #350: `start_at`, a queued download's own start time, local `YYYY-MM-DDTHH:MM`; NULL for none and for every record saved before schema 9. `save_queue` writes it with the download's place, and clears it when the download leaves the queue
`host_memory` (schema 10)	`0010_host_memory.sql`, #325: what the transfer engine learned about each server, by `host` (`host:port`, lowercase, without a login): `connections`, the count a download settled at, `throttled_to`, the most connections it served before it throttled, and `seen_at_ms`. Nothing else. A row not seen for 7 days is not used, and is deleted at the next start; at most 500 rows are kept, the oldest go first (PO decisions). Settings, Advanced, Forget servers deletes them all. Never exported
`torrents` (schema 5)	`0005_torrents.sql`, #58: one row per torrent download, removed with it. `options_json` is how it is added again (`TorrentOptions`: where it came from, a `.torrent` file's path or the magnet link without its trackers; the file choice; the seeding policy; the limits). `metadata` is its `d4:info…e`, checked against its info hash before it is stored; `resume_data` is libtorrent's resume data without its trackers. A torrent's own `downloads` row keeps engine `libtorrent`, no direct-download options (`{}`), its info hashes and its upload. `Database::insert_torrent` writes both rows in one transaction. Details: TORRENTS.md
`categories`	Built-in and user categories, with their extensions as JSON. Schema 6 (`0006_builtin_categories.sql`, #31) adds V1's six built-in ones. Deleting a category sets `downloads.category_id` to NULL (`ON DELETE SET NULL`). The rules are in FILE_NAMING.md
`settings`	One row: the settings document as a JSON object, checked by SQLite (`json_type = 'object'`). Its sections are in SETTINGS.md, and the Settings screen saves them (#30). Its `downloads` section holds the folder settings (#31, SETTINGS.md). A section that can't be read is an error and is left as it was
`checkpoints`	Schema 2 (`0002_checkpoints.sql`, #43): one row per direct download, its last `Checkpoint` as JSON. It holds the durable byte ranges, the total, the validators and the content encoding (#45), with no credential and no URL query. A checkpoint saved before the encoding existed reads as having none. It is removed with its download. A checkpoint in another format, or one this build can't read, is `Stored::Unknown` and never used. `downloads.checkpoint_ref`, reserved in schema 1, stays unused

Kinds and statuses are stored by their wire names (http, queued, fetchingMetadata), as in CONTRACTS.md. Counts and times are integers; times are milliseconds since the Unix epoch.

Unknown values

A value this build doesn't know, read back from the database, is kept as Stored::Unknown(raw), never mapped to a default. That covers an engine, a kind, a status, or options it can't read. StoredDownload::route() hands a record to an engine only when every one of those is known. Otherwise it's ENGINE_UNAVAILABLE: the record is shown and kept, never acted on (report section 6). A record never changes engine.

Secrets

Never stored: credentials, cookies, custom header values and SSH keys. They stay in memory (#70). A download's stored options say only whether the user asked to remember its login in the system keychain (loginSaved, #70; ADR-0015); the login itself is in the keychain, never here.
By construction: StoredOptions has no field for any of them.
URL logins: without_login removes a login from a URL (https://user:pass@host/ becomes https://host/), and insert_download applies it again.
What is kept: has_sensitive_options records that a download had credentials, headers, an SSH key or a URL login. After a restart such a download comes back paused, and resumes or is retried only once they are given again (PO decision on #58).
The options to start again (StoredOptions, #58): every source without its login, the user agent, the referrer without its login, query and fragment (which can carry a session), the checksum, the piece hashes, the speed limit and the connections. Fields added since schema 1 default when absent, so older records still read.
Tested: a test adds a download with a password in its URL, a login (credentials, #40) and a cookie header. It then searches every file SQLite wrote, with the WAL and after closing, and finds none of them.

Upgrades

When the app opens the database:

A newer database is refused unmodified. The version is read from the file's header before SQLite opens it, so nothing is written. A database from a later Apollo Download Manager is DATABASE_MIGRATION, and the file and its folder are left exactly as they were. Downgrading needs the backup that the later version made.
Before an upgrade, a backup. An existing database at an older version is copied next to itself with VACUUM INTO, as <file>.bak-v<old version>-<ms>, owner-only.
The migrations run in one transaction, and user_version is set to the new version. The runner is Apollo Download Manager's own (to_latest in crates/velocity-core/src/database/mod.rs); it replaced rusqlite_migration in #446, which kept rusqlite from the SQLite fix.
A failed upgrade changes nothing. The transaction rolls back, and the database stays at its old version with its data. The error is DATABASE_MIGRATION, and the backup stays. User data is never deleted or recreated to repair a migration.

Adding a migration

Add a numbered file to crates/velocity-core/migrations/ (0002_….sql) and list it in MIGRATIONS in database/mod.rs.
Never edit a released one.
Keep it additive: new tables, new nullable columns, or columns with defaults, as schemas 2 to 7 did.
Test it from the previous schema with representative data. The tests in database/mod.rs show the pattern with a test-only second migration: the upgrade keeps the data, the backup holds the old version, a failed migration keeps both, and a newer database is refused.