Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

73.1 KB
Raw Blame
"""Core backup service for encrypted database backups.

Uses sqlcipher_export() for safe atomic backups that preserve encryption
and work correctly with WAL mode.
"""

import fnmatch
import hmac
import os
import re
import shutil
import sqlite3
import tempfile
import threading
import time
from contextlib import contextmanager
from dataclasses import dataclass
from datetime import UTC, datetime, timedelta
from pathlib import Path
from stat import S_ISLNK, S_ISREG
from typing import Optional

from loguru import logger


from .backup_status import (
    BackupError,
    error_from_exception,
    persist_pending_backup_status,
    record_backup_result,
)
from ...utilities.resource_utils import safe_close

from ...config.paths import (
    get_encrypted_database_path,
    get_user_backup_directory,
    get_user_database_filename,
)
from ..sqlcipher_utils import (
    LEGACY_PBKDF2_SALT,
    apply_performance_pragmas,
    apply_sqlcipher_pragmas,
    create_sqlcipher_connection,
    get_key_from_password,
    get_salt_file_path,
    get_salt_for_database,
    get_sqlcipher_settings,
    has_per_database_salt,
    set_sqlcipher_key,
    verify_sqlcipher_connection,
)

# Module-level per-user locks to prevent concurrent backup operations for the
# same user across different BackupService instances. Entries deliberately
# retain stable identity for the process lifetime: removing a lock between a
# caller's lookup and acquire would let a later caller create a second lock and
# bypass the serialization.
_user_locks: dict[str, threading.Lock] = {}
_user_locks_lock = threading.Lock()

# Device, inode, size and modification time of one file.
_FileIdentity = tuple[int, int, int, int]
# Identity of a backup database and of its salt file (None when it has none).
_BackupIdentity = tuple[_FileIdentity, Optional[_FileIdentity]]

# Full backup verifications run in this process, per user: {username: {backup
# path: (identity of the database and salt files verified, whether they
# passed)}}. _verify_backup costs a full key derivation and a PRAGMA
# quick_check over the whole backup, so the daily limit reuses a remembered
# pass, and skips a remembered failure, only while both files keep that
# identity. A restart forgets every result; a password change forgets that
# user's. A user's entries are read and written only while holding that user's
# backup lock. They contain file metadata, no key material.
_backup_verifications: dict[str, dict[Path, tuple[_BackupIdentity, bool]]] = {}

# Characters that must never appear in the validated backup path string.
# ATTACH DATABASE takes a string literal and cannot be parameterized in
# SQLite/SQLCipher, so the path is interpolated into SQL. A single quote IS
# allowed — it is escaped by doubling (per the SQLite literal grammar) so an
# apostrophe in the data-dir path (a home dir named O'Brien, say) doesn't
# break backups. Double-quote, NUL and control chars are rejected outright. A
# backslash is in the set, but never appears in the validated string in a real
# backup path: it is the Windows separator, normalised to "/" before this check
# (see _validate_backup_path); on POSIX a real separator is always "/", so a
# literal backslash there is conservatively rejected.
_UNSAFE_BACKUP_PATH_CHARS = frozenset('"\\\0\n\r\t')


# Only names produced by _move_damaged_backup_aside belong to the managed
# quarantine. Salt files and unrelated operator files must not match.
_QUARANTINED_BACKUP_NAME_RE = re.compile(
    r"ldr_backup_.+\.db\.damaged-[0-9]{8}_[0-9]{6}_[0-9]{6}"
)


# Errors one corrupt or unreadable backup can raise while it is classified,
# moved aside or deleted. sqlcipher3's errors do not subclass sqlite3.Error,
# so its DB-API base class is listed too.
try:
    from sqlcipher3 import dbapi2 as _sqlcipher_dbapi

    _SQLCIPHER_ERRORS: tuple = (_sqlcipher_dbapi.Error,)
except ImportError:  # pragma: no cover - SQLCipher optional
    _SQLCIPHER_ERRORS = ()
_PURGE_ITEM_ERRORS = (
    OSError,
    sqlite3.Error,
    ValueError,
    *_SQLCIPHER_ERRORS,
)


def _validate_backup_path(path: Path) -> str:
    """Normalize host separators and validate the path used in ATTACH.

    Use this before reserving files and again for the exported temporary path.
    Filesystem operations keep their native Path; a literal POSIX backslash
    remains invalid instead of being redirected to another directory.
    """
    path_string = str(path)
    if os.sep == "\\":
        path_string = path_string.replace("\\", "/")
    bad_chars = _UNSAFE_BACKUP_PATH_CHARS & set(path_string)
    if bad_chars:
        raise ValueError(
            "Backup path contains characters not allowed in a "
            "SQLCipher ATTACH statement: "
            + ", ".join(sorted(repr(c) for c in bad_chars))
        )
    return path_string


def _get_user_lock(username: str) -> threading.Lock:
    """Get or create a lock for a specific user.

    Thread-safe lazy initialization of per-user locks.

    Args:
        username: The username to get lock for

    Returns:
        A threading.Lock for the specified user
    """
    with _user_locks_lock:
        if username not in _user_locks:
            _user_locks[username] = threading.Lock()
        return _user_locks[username]


def _get_backup_verifications(
    username: str,
) -> dict[Path, tuple[_BackupIdentity, bool]]:
    """Return the user's remembered verification results.

    Read or change the returned mapping only while holding the user's lock.
    """
    with _user_locks_lock:
        return _backup_verifications.setdefault(username, {})


def _file_identity(path: Path) -> _FileIdentity:
    """Return the device, inode, size and mtime of ``path`` itself."""
    status = path.lstat()
    return status.st_dev, status.st_ino, status.st_size, status.st_mtime_ns


def _backup_identity(backup_path: Path) -> Optional[_BackupIdentity]:
    """Identify the exact database and salt files a verification reads.

    ``lstat`` gives a replaced file, including one replaced by a symlink, a
    different identity. A legacy backup has no salt file. None means the
    files could not be identified, so no result for them is reused.
    """
    try:
        database = _file_identity(backup_path)
    except OSError:
        return None
    try:
        salt = _file_identity(get_salt_file_path(backup_path))
    except FileNotFoundError:
        salt = None
    except OSError:
        return None
    return database, salt


def _is_published_pair(
    published: Optional[_BackupIdentity], verified: _BackupIdentity
) -> bool:
    """Whether the published files are the complete pair that was verified.

    The database, and the salt if one was verified, must be there with the
    size they had when verified, and a salt must be absent if none was. A
    rename keeps a file's size. Device, inode and modification time are not
    compared: should a filesystem change them on rename, that only means a
    later login verifies the backup again.
    """
    if published is None:
        return False
    (database, salt), (verified_database, verified_salt) = published, verified
    if database[2] != verified_database[2]:
        return False
    if salt is None or verified_salt is None:
        return salt is verified_salt
    return salt[2] == verified_salt[2]


def is_safe_glob_result(path: Path, base_dir: Path) -> bool:
    """Validate that a glob result is safe to use.

    Rejects symlinks and paths that resolve outside the expected
    base directory, preventing path-traversal via crafted filenames
    or symlink attacks.

    This is a best-effort pre-check, not atomic with the subsequent
    stat()/unlink()/open() on the path (a classic TOCTOU window). It is
    acceptable here because the backup directory is per-user and
    server-owned with 0o600 backups — an attacker who could swap a file
    for a symlink already has write access to that directory.

    Args:
        path: The path returned by glob to validate.
        base_dir: The directory the path must reside within.

    Both sides are resolved with ``resolve_allow_missing()``, never a bare
    non-strict ``resolve()``, so a path that cannot be fully resolved (a
    loop, a symlink chain whose resolved form outgrows PATH_MAX) is
    rejected instead of being compared half resolved.

    Returns:
        True if the path is a regular (non-symlink) file that resolves
        to a location within base_dir.
    """
    # Lazy import: keeps the security package out of this database
    # module's import-time graph (the database package loads at bootstrap).
    from ...security.path_validator import resolve_allow_missing

    if path.is_symlink():
        return False
    try:
        return resolve_allow_missing(path).is_relative_to(
            resolve_allow_missing(base_dir)
        )
    except OSError:
        return False


def list_backup_files(
    backup_dir: Path, *, include_auxiliary: bool = False
) -> list[Path]:
    """List the ``ldr_backup_*.db`` entries of ``backup_dir``, or raise.

    ``Path.glob`` swallows the ``PermissionError`` and other ``OSError`` that
    scanning a directory raises, so an unreadable directory looks exactly like
    an empty one. ``Path.iterdir`` raises instead, which lets the settings
    panel tell "could not be read" from "no backups yet". Like ``glob`` this
    returns every matching name, symlinks included, so callers still apply
    ``is_safe_glob_result``.

    Raises:
        OSError: ``backup_dir`` is missing, is not a directory or cannot be
            read.
    """
    patterns = ["ldr_backup_*.db"]
    if include_auxiliary:
        patterns += [
            "ldr_backup_*.db.salt",
            "ldr_backup_*.db.tmp",
            "ldr_backup_*.db.tmp.salt",
        ]
    return [
        entry
        for entry in backup_dir.iterdir()
        if any(fnmatch.fnmatch(entry.name, pattern) for pattern in patterns)
        or (
            include_auxiliary
            and _QUARANTINED_BACKUP_NAME_RE.fullmatch(
                entry.name.removesuffix(".salt")
            )
        )
    ]


def pop_user_lock(username: str) -> None:
    """Compatibility no-op: per-user backup locks keep stable identity.

    User-close cleanup can race both a held lock and a caller that has looked
    up the lock but not acquired it yet. Removing the registry entry in either
    case permits a later backup operation to create a second lock and enter the
    same user's backup critical section concurrently.

    Keep one small lock per distinct username for the process lifetime. The
    function remains as a no-op for existing user-close cleanup callers.
    """
    del username


@contextmanager
def password_status_session(db_path: Path, password: str):
    """Yield a session on an existing user database keyed with ``password``.

    Only for recording a password-change purge result with the new password
    the user just entered (see ``BackupService._password_status_session``).
    Raises ``FileNotFoundError`` for a missing database, and whatever opening
    raises for a wrong key. The existence check is best effort, not atomic
    with the open: a file removed in between is created empty by SQLite,
    keyed with ``password`` and passes the key check, so the status row
    would land in a new encrypted database at that path. Nothing in the app
    deletes a user database, so only an outside actor can open that window.
    (``create_sqlcipher_connection`` needs a plain path for the salt lookup,
    so a ``mode=rw`` URI is not used.)
    """
    from sqlalchemy import create_engine
    from sqlalchemy.orm import Session
    from sqlalchemy.pool import NullPool

    from ..sqlcipher_compat import get_sqlcipher_module

    if not db_path.is_file():
        raise FileNotFoundError("User database not found")
    engine = create_engine(
        "sqlite://",
        creator=lambda: create_sqlcipher_connection(str(db_path), password),
        module=get_sqlcipher_module().dbapi2,
        poolclass=NullPool,
        hide_parameters=True,
    )
    try:
        with Session(engine) as session:
            yield session
    finally:
        engine.dispose()


@dataclass
class BackupResult:
    """Result of a backup operation."""

    success: bool
    backup_path: Optional[Path] = None
    error: Optional[str] = None
    size_bytes: int = 0
    error_code: Optional[BackupError] = None
    # Set by purge_and_refresh: whether old-password files may remain.
    purge_incomplete: Optional[bool] = None


@contextmanager
def _report_backup_errors(username: str):
    """Record an unexpected exception raised outside the export, then re-raise it.

    The export reports its own outcome. This covers the work around it, such
    as checking today's backups or purging old ones, so a failure there does
    not leave an older successful backup looking current. It is not a check
    of the backup directory: ``Path.glob`` swallows directory-scan errors, so
    an unreadable directory is not raised here. The settings panel detects
    that with ``list_backup_files``.
    """
    try:
        yield
    except Exception as exc:
        record_backup_result(username, False, error_from_exception(exc))
        raise


class BackupService:
    """Service for creating and managing encrypted database backups.

    Uses sqlcipher_export() for safe backups that:
    - Work correctly with WAL mode
    - Preserve encryption with the same key
    - Create atomic copies via ATTACH + export + DETACH
    - Never corrupt the source database
    """

    def __init__(
        self,
        username: str,
        password: str,
        max_backups: int = 1,
        max_age_days: int = 7,
        *,
        record_failure: bool = True,
    ):
        """Initialize backup service.

        Args:
            username: User's username
            password: User's password (for encryption)
            max_backups: Maximum number of backup files to keep. A value
                below 1 is treated as 1 when backups are pruned.
            max_age_days: Delete backups older than this many days. Zero,
                a negative value, or a value too large to turn into a date
                means no age limit.
            record_failure: If False, a failure to resolve the paths is
                only raised, not recorded. The password-change refresh
                records it itself: it holds the rekey sidecar, which the
                default (open-engine) status write would wait on.
        """
        self.username = username
        self.password = password
        self.max_backups = max_backups
        self.max_age_days = max_age_days

        # Get paths
        try:
            self.db_filename = get_user_database_filename(username)
            self.db_path = get_encrypted_database_path() / self.db_filename
            self.backup_dir = get_user_backup_directory(username)
        except Exception as exc:
            if record_failure:
                record_backup_result(username, False, error_from_exception(exc))
            raise

    def create_backup(
        self, force: bool = False, prune: bool = True
    ) -> BackupResult:
        """Create an encrypted backup of the user's database.

        Uses sqlcipher_export() to create a safe, atomic backup that inherits
        the encryption key from the source database. The matching salt is
        copied alongside it. The database is published only after its salt,
        so a completed .db file never depends on the source's salt file.
        If either file is removed or changed while the copy is verified, or
        a file of the pair is missing, or no longer the size verified, when
        it is published, the new backup fails and is removed, and no older
        backup is deleted.

        By default, only one backup per calendar day is created to prevent
        a corrupted database from rapidly overwriting all good backups.
        Only a backup that passed the full verification counts. That runs
        when a backup is created. An existing backup is verified again only
        when none of today's backups has a pass this process remembers for
        its current database and salt files, and only if no failure is
        remembered for those files either (see _find_verified_backup).
        Use force=True to bypass this check (used by pre-migration backups).

        This method is protected by a per-user lock to prevent concurrent
        backup operations for the same user.

        Args:
            force: If True, skip the daily limit check.
            prune: If False, keep every existing backup instead of applying
                this service's ``max_backups`` / ``max_age_days`` limits
                after the new backup is written. Used by the pre-migration
                backup when the user's own retention settings could not be
                read or are out of range. With True, the new backup itself
                is never deleted by this cleanup.

        Returns:
            BackupResult with success status and backup path
        """
        # Acquire per-user lock to prevent concurrent backup operations
        with (
            _get_user_lock(self.username),
            _report_backup_errors(self.username),
        ):
            # Skip if a backup already exists for today (unless forced)
            if not force:
                today = datetime.now(UTC).strftime("%Y%m%d")
                # After globbing, verify each result is a direct child of backup_dir
                todays_backups = [
                    f
                    for f in self.backup_dir.glob(f"ldr_backup_{today}_*.db")
                    if is_safe_glob_result(f, self.backup_dir)
                ]
                existing = self._find_verified_backup(todays_backups)
                if existing is not None:
                    existing_path, existing_size = existing
                    logger.debug(
                        "Backup already exists for today "
                        f"({existing_path.name}), skipping"
                    )
                    # No new attempt is recorded here, so save any status
                    # observed while the database was closed (such as an
                    # incomplete password-change purge) before a restart
                    # can lose it.
                    persist_pending_backup_status(self.username)
                    return BackupResult(
                        success=True,
                        backup_path=existing_path,
                        size_bytes=existing_size,
                    )

            start = time.perf_counter()
            result = self._create_backup_impl(prune=prune)
            elapsed_ms = (time.perf_counter() - start) * 1000
            size_info = (
                f"{result.size_bytes / (1024 * 1024):.1f}MB"
                if result.size_bytes
                else "unknown size"
            )
            if elapsed_ms > 1000:
                logger.info(
                    f"Backup for user {self.username} "
                    f"({size_info}) took {elapsed_ms:.0f}ms"
                )
            else:
                logger.debug(
                    f"Backup for user {self.username} "
                    f"({size_info}) took {elapsed_ms:.0f}ms"
                )
            return result

    def _create_backup_impl(
        self, prune: bool = True, purge_incomplete: bool | None = None
    ) -> BackupResult:
        """Internal implementation of backup creation (must be called with lock held).

        Ordinary backups preserve an incomplete password-change purge. Only
        a purge verified to have removed all old files clears that warning.

        A purge result (``purge_incomplete`` given) is written over a
        connection opened with this service's password, the same way the
        export itself opens the database, before the open engine is tried:
        the purge runs under the rekey sidecar, which the open-engine status
        path would wait on, and the engine may not be open at all.
        """
        direct = (
            self._password_status_session
            if purge_incomplete is not None
            else None
        )
        try:
            result = self._export_backup(prune=prune)
        except Exception as exc:
            record_backup_result(
                self.username,
                False,
                error_from_exception(exc),
                purge_incomplete=purge_incomplete,
                direct_session=direct,
            )
            raise
        record_backup_result(
            self.username,
            result.success,
            result.error_code,
            purge_incomplete=purge_incomplete,
            direct_session=direct,
        )
        return result

    @contextmanager
    def _password_status_session(self):
        """Yield a session on the user database keyed with this password.

        Used only to record a password-change purge result. That runs under
        the rekey sidecar (``refresh_backups_after_password_change``), so the
        open-engine status path, which takes the same sidecar, would stall
        until its wait bound. Refuses a database that is already missing; see
        ``password_status_session`` for the remaining race.
        """
        with password_status_session(self.db_path, self.password) as session:
            yield session

    def _export_backup(self, prune: bool = True) -> BackupResult:
        """Export and verify a new backup while the caller holds the user lock."""
        if not self.db_path.exists():
            return BackupResult(
                success=False,
                error=f"Database not found: {self.db_path}",
                error_code=BackupError.SOURCE_MISSING,
            )

        # Check available disk space
        try:
            db_size = self.db_path.stat().st_size
            free_space = shutil.disk_usage(self.backup_dir).free
            # Require at least 2x the database size as free space
            if free_space < db_size * 2:
                return BackupResult(
                    success=False,
                    error=f"Insufficient disk space. Need {db_size * 2} bytes, have {free_space}",
                    error_code=BackupError.INSUFFICIENT_SPACE,
                )
        except OSError as e:
            # Fail closed - don't proceed with backup if we can't verify disk space
            logger.warning("Could not check disk space, skipping backup")
            return BackupResult(
                success=False,
                error=f"Could not verify disk space: {e}",
                error_code=error_from_exception(e),
            )

        # Reserve a unique, owner-only temporary file. Forced backups can run
        # within the same second; neither a new export nor failed cleanup may
        # overwrite a previous successful backup.
        timestamp = datetime.now(UTC).strftime("%Y%m%d_%H%M%S")
        temp_path = None
        backup_path = None

        try:
            _validate_backup_path(self.backup_dir)
            fd, temp_name = (
                tempfile.mkstemp(  # Safe: empty owner-only file; SQLCipher writes ciphertext
                    prefix=f"ldr_backup_{timestamp}_",
                    suffix=".db.tmp",
                    dir=self.backup_dir,
                )
            )
            os.close(fd)
            temp_path = Path(temp_name)
            destination = temp_path.with_suffix("")
            destination_salt = get_salt_file_path(destination)
            if (
                destination.exists()
                or destination.is_symlink()
                or destination_salt.exists()
                or destination_salt.is_symlink()
            ):
                raise FileExistsError(  # noqa: TRY301 - outer handler removes our reserved temp file
                    "Backup destination already exists"
                )
            backup_path = destination
            salt_copied = self._copy_source_salt(temp_path)

            # Create connection to source database
            conn = create_sqlcipher_connection(str(self.db_path), self.password)
            cursor = conn.cursor()

            # Honour the operator-configured ``db_config.busy_timeout_ms``
            # (default 30 s) instead of the previous hardcoded 10 s.
            # The source DB already had this applied on its own
            # connection; this backup connection is a separate one and
            # the SQLCipher `creator` only sets the cipher defaults
            # before the key -- a backup racing the background document
            # scheduler's PDF-download transaction used to die at
            # 10 s while the source DB's writer rode out 30 s, leaving
            # the backup silently failing for no operator-tunable
            # reason.
            apply_performance_pragmas(cursor)

            try:
                # Use sqlcipher_export() to create an encrypted backup
                # VACUUM INTO doesn't preserve encryption in SQLCipher
                # Security: the ATTACH path below is interpolated (not
                # parameterizable). Single quotes are escaped (see below); any
                # other unsafe chars were rejected before file creation.
                # Use the same normalization for the reserved temporary path.
                temp_path_str = _validate_backup_path(temp_path)

                # Get the hex key for ATTACH (same key derivation as source)
                hex_key = get_key_from_password(
                    self.password, db_path=temp_path
                ).hex()

                # Defensive: ensure hex_key is strictly hexadecimal
                if not hex_key or not all(
                    c in "0123456789abcdef" for c in hex_key
                ):
                    raise ValueError("Derived key is not valid hex")

                # Attach backup database with encryption (using temp path).
                # ATTACH DATABASE can't be parameterized in SQLite/SQLCipher, so
                # the path is interpolated as a string literal — escape any
                # single quote by doubling it per the SQLite literal grammar
                # (all other unsafe chars were rejected above).
                attach_path = temp_path_str.replace("'", "''")
                # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: ATTACH cannot be bound; unsafe path chars rejected; quotes doubled; key checked as hex
                cursor.execute(
                    # nosemgrep: semgrep.rules.sql-string-concatenation, reason: ATTACH path rejects unsafe characters and doubles quotes; the derived key is validated as ASCII hex
                    f"ATTACH DATABASE '{attach_path}' AS backup KEY \"x'{hex_key}'\""
                )

                try:
                    # Apply cipher settings to the backup database (must match source)
                    # Note: PRAGMA statements do not support parameter binding
                    # in SQLite — f-string is required. Values are validated
                    # upstream by get_sqlcipher_settings() against allow-lists.
                    settings = get_sqlcipher_settings()
                    page_size = int(settings["page_size"])
                    kdf_iter = int(settings["kdf_iterations"])
                    hmac_alg = str(settings["hmac_algorithm"])
                    # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; value coerced with int() above
                    cursor.execute(
                        # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA page size is converted to int before interpolation
                        f"PRAGMA backup.cipher_page_size = {page_size}"
                    )
                    # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; hmac_algorithm is an EnumSetting allow-list value
                    cursor.execute(
                        # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA HMAC algorithm comes from the EnumSetting allowlist
                        f"PRAGMA backup.cipher_hmac_algorithm = {hmac_alg}"
                    )
                    # nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; value coerced with int() above
                    cursor.execute(f"PRAGMA backup.kdf_iter = {kdf_iter}")

                    # Export all data to the backup database
                    cursor.execute("SELECT sqlcipher_export('backup')")
                finally:
                    # Always detach to release the backup file handle
                    try:
                        cursor.execute("DETACH DATABASE backup")
                    except Exception:
                        logger.warning(
                            "DETACH failed (connection will release on close)"
                        )
            finally:
                safe_close(cursor, "backup cursor")
                safe_close(conn, "backup connection")

            # Verify the backup is valid (still using temp path)
            passed, verified_identity = self._verify_and_identify(temp_path)
            if not passed or verified_identity is None:
                # Delete corrupted backup
                self._remove_backup_files(temp_path)
                return BackupResult(
                    success=False,
                    error="Backup verification failed - backup was corrupted",
                    error_code=BackupError.VERIFICATION,
                )

            # Set restrictive permissions (owner read/write only)
            # SECURITY: Backup files contain sensitive user data
            os.chmod(temp_path, 0o600)

            # Get backup size before rename
            backup_size = temp_path.stat().st_size

            # Renaming keeps both identities, so later logins today reuse
            # this verification while the published files stay unchanged.
            # Record it before publishing: an error after the renames would
            # make the handler below delete the new backup. If publication
            # fails, the entry matches no file.
            _get_backup_verifications(self.username)[
                self.backup_dir / backup_path.name
            ] = (verified_identity, True)

            # Publish the salt first, then the database as the completion
            # marker. A crash between renames leaves only an orphan salt,
            # which retention cleanup can remove after the stale-file delay.
            # The salt rename does not depend on the salt still existing:
            # if it has gone since the verification, the rename raises and
            # the handler below removes the partial pair before any older
            # backup is deleted.
            if salt_copied:
                # nosemgrep: semgrep.rules.path-traversal-risk, reason: The source is the fixed .salt sidecar of this call's mkstemp file in the server-owned sha256-named per-user backup directory
                get_salt_file_path(temp_path).rename(
                    # nosemgrep: semgrep.rules.path-traversal-risk, reason: The target is the fixed .salt sidecar of the same mkstemp name without .tmp in that directory
                    get_salt_file_path(backup_path)
                )
            # nosemgrep: semgrep.rules.path-traversal-risk, reason: temp_path is this call's mkstemp file in the server-owned sha256-named per-user backup directory and backup_path is that name without .tmp
            temp_path.rename(backup_path)

            # Delete older backups only while the complete pair that was
            # verified is in place under its final names.
            if not _is_published_pair(
                _backup_identity(backup_path), verified_identity
            ):
                logger.warning(
                    f"Backup {backup_path.name} changed during "
                    "publication; removing it"
                )
                self._remove_backup_files(backup_path)
                return BackupResult(
                    success=False,
                    error="Backup files changed during publication",
                    error_code=BackupError.PUBLICATION,
                )

            logger.info(
                f"Created backup for user: {backup_path.name} ({backup_size} bytes)"
            )

            # Cleanup old backups (safe now - new backup is finalized).
            # The new backup is passed as ``keep`` so no retention value
            # can delete it. A cleanup failure must not reach the except
            # below, which would delete the backup that was just finalized.
            if prune:
                try:
                    self._cleanup_old_backups(keep=backup_path)
                except Exception as cleanup_error:
                    logger.warning(
                        "Old-backup cleanup failed "
                        f"({type(cleanup_error).__name__}); keeping the new "
                        "backup"
                    )

            return BackupResult(
                success=True,
                backup_path=backup_path,
                size_bytes=backup_size,
            )

        except Exception as e:
            logger.exception("Backup creation failed")
            # Clean up any partial backup (temp file)
            if temp_path is not None:
                try:
                    self._remove_backup_files(temp_path)
                except OSError:
                    pass
            # Also clean up final path in case rename partially succeeded
            if backup_path is not None:
                try:
                    self._remove_backup_files(backup_path)
                except OSError:
                    pass
            return BackupResult(
                success=False,
                error=str(e),
                error_code=error_from_exception(e),
            )

    def _verify_and_identify(
        self, backup_path: Path
    ) -> tuple[bool, Optional[_BackupIdentity]]:
        """Run the full verification and identify the files it covered.

        A backup passes only if its database file, and its salt file or the
        absence of one, are the same before and after the verification: a
        file that is removed, replaced or written to during the check fails
        it, whatever the check found. Opening a path that was removed during
        the check makes SQLite create an empty database there, which the
        checks accept, and a salt removed after the key was derived would
        otherwise let the database be published without it. An empty
        database never passes. The identity is returned only for files that
        were the same before and after, and is None otherwise, so a result
        is never remembered for files it did not read.
        """
        identity = _backup_identity(backup_path)
        passed = self._verify_backup(backup_path)
        after = _backup_identity(backup_path)
        if after is None or after != identity:
            return False, None
        if after[0][2] == 0:
            passed = False
        return passed, identity

    def _find_verified_backup(
        self, backups: list[Path]
    ) -> Optional[tuple[Path, int]]:
        """Return one of ``backups`` that passes the full verification.

        Returns that backup and its size, or None when none passes. Results
        remembered in this process come first and cost no key derivation: a
        pass is reused, and a failure skipped, while the backup's database
        and salt files keep the device, inode, size and modification time
        they had when verified. If any backup has a remembered pass, the
        newest of those is returned and nothing is verified. Otherwise the
        backups without a remembered result, such as after a restart or once
        either file is replaced, removed or written to, are verified newest
        first until one passes, and each result is remembered. A change that
        preserves all four values is not detected. A backup that has been
        removed counts as absent, and one whose database or salt file is
        removed or changed during its verification does not pass, with no
        result remembered. Must be called with the user's backup lock held.
        """
        results = _get_backup_verifications(self.username)
        # Forget results for files that are not candidates any more.
        for path in results.keys() - set(backups):
            del results[path]

        passes: list[tuple[Path, _BackupIdentity]] = []
        unknown: list[tuple[Path, _BackupIdentity]] = []
        for path in backups:
            identity = _backup_identity(path)
            remembered = results.get(path)
            if identity is None:
                # Removed since the glob, or its files cannot be identified:
                # there is no backup to count.
                results.pop(path, None)
            elif remembered is not None and remembered[0] == identity:
                if remembered[1]:
                    passes.append((path, identity))
            else:
                results.pop(path, None)
                unknown.append((path, identity))

        def modified_ns(candidate: tuple[Path, _BackupIdentity]) -> int:
            return candidate[1][0][3]

        if passes:
            newest, newest_identity = max(passes, key=modified_ns)
            return newest, newest_identity[0][2]
        for candidate, scanned in sorted(
            unknown, key=modified_ns, reverse=True
        ):
            passed, verified = self._verify_and_identify(candidate)
            if verified is not None:
                results[candidate] = (verified, passed)
            if passed:
                return candidate, (verified or scanned)[0][2]
        return None

    def _copy_source_salt(self, temp_path: Path) -> bool:
        """Preserve the original salt, never generate a replacement.

        Legacy databases use a fixed salt built into the key derivation and
        need no sidecar. Opening the source still verifies that this is
        actually a legacy database, rather than a database with a lost salt.

        Returns:
            True if a salt file was written for ``temp_path``, False for a
            legacy database.
        """
        source_salt = get_salt_file_path(self.db_path)
        if source_salt.is_symlink():
            raise ValueError("Database salt must not be a symlink")
        salt = get_salt_for_database(self.db_path)
        if salt == LEGACY_PBKDF2_SALT:
            return False
        fd = os.open(
            get_salt_file_path(temp_path),
            os.O_WRONLY | os.O_CREAT | os.O_EXCL,
            0o600,
        )
        # The "wb" wrapper (io.FileIO) sets the descriptor to binary mode on
        # Windows, so no salt byte is translated as a line ending.
        with os.fdopen(
            fd, "wb"
        ) as salt_file:  # Safe: non-secret salt for encrypted backup recovery
            salt_file.write(salt)
            salt_file.flush()
            os.fsync(salt_file.fileno())
        return True

    @staticmethod
    def _remove_backup_files(backup_path: Path) -> None:
        """Remove a database and its salt without following salt symlinks."""
        backup_path.unlink(missing_ok=True)
        get_salt_file_path(backup_path).unlink(missing_ok=True)

    def _verify_backup(self, backup_path: Path) -> bool:
        """Verify that a backup file is valid and readable.

        Args:
            backup_path: Path to the backup file

        Returns:
            True if backup is valid, False otherwise
        """
        backup_salt = get_salt_file_path(backup_path)
        if (
            not backup_path.is_file()
            or backup_path.is_symlink()
            or backup_salt.is_symlink()
        ):
            return False

        try:
            backup_size = backup_path.stat().st_size
        except OSError:
            # Removed since the check above: there is no backup to verify.
            return False
        if backup_size == 0:
            logger.warning("Backup file is empty (0 bytes)")
            return False

        # _copy_source_salt gives every new backup of a database that has its
        # own salt a copy of it, and the application never adds a salt to a
        # legacy database. A backup without a salt, of a database with one,
        # is therefore incomplete (made before salts were copied, or damaged)
        # or belongs to an earlier database: reject it before any key
        # derivation, and before the legacy-salt warning that deriving its
        # key would log. Only the presence of the source's salt is checked,
        # so it can make this check stricter, never weaker.
        if not backup_salt.exists() and has_per_database_salt(self.db_path):
            logger.warning(
                f"Backup {backup_path.name} has no salt file, but its "
                "database has one"
            )
            return False

        try:
            # Import SQLCipher module
            from ..sqlcipher_compat import (
                connect_sqlcipher,
                get_sqlcipher_module,
            )

            sqlcipher3 = get_sqlcipher_module()

            # Open the backup with the same password
            conn = connect_sqlcipher(sqlcipher3, str(backup_path))
            cursor = conn.cursor()

            try:
                # Verify using only recovery inputs next to the backup.
                # Never fall back to the source's salt: doing so would hide
                # an incomplete backup until the original data is lost.
                set_sqlcipher_key(cursor, self.password, db_path=backup_path)
                apply_sqlcipher_pragmas(cursor, creation_mode=False)

                # Run quick integrity check
                cursor.execute("PRAGMA quick_check")
                result = cursor.fetchone()

                if result and result[0] == "ok":
                    # Additional verification: try to read a table
                    if verify_sqlcipher_connection(cursor):
                        return True

                logger.warning(f"Backup integrity check failed: {result}")
                return False

            finally:
                safe_close(cursor, "backup cursor")
                safe_close(conn, "backup connection")

        except Exception:
            logger.warning("Backup verification failed")
            return False

    def _age_cutoff(self) -> Optional[datetime]:
        """Return the time before which backups are too old, or None.

        None means no age limit. That is the case when ``max_age_days``
        is zero or negative (``LDR_BACKUP_MAX_AGE_DAYS=0`` is a common way
        to say "keep forever", and would otherwise put the cutoff at or
        after now, so every backup would count as too old), and when it
        cannot be turned into a date: an env override such as
        ``LDR_BACKUP_MAX_AGE_DAYS=1000000`` or ``inf`` overflows the
        subtraction, and ``nan`` or a non-number cannot be converted. The
        count limit and stale ``.tmp`` removal still run.
        """
        try:
            if self.max_age_days <= 0:
                logger.info(
                    f"backup.max_age_days={self.max_age_days!r} is not "
                    "positive; not removing backups by age"
                )
                return None
            return datetime.now(UTC) - timedelta(days=self.max_age_days)
        except (OverflowError, ValueError, TypeError):
            logger.warning(
                f"backup.max_age_days={self.max_age_days!r} is out of "
                "range; not removing backups by age"
            )
            return None

    def _count_limit(self) -> Optional[float]:
        """Return how many backups to keep, or None for no count limit.

        A ``max_backups`` below 1 (``LDR_BACKUP_MAX_COUNT=0`` or a negative
        value) is treated as 1, the settings' minimum: the newest backup is
        always kept and older ones are still pruned, so the setting cannot
        delete every backup and cannot silently stop limiting disk use.
        A value that cannot be compared with a count (``nan`` or a
        non-number) means no count limit, as with ``_age_cutoff``; ``inf``
        also means no count limit.
        """
        limit = self.max_backups
        try:
            below_one = limit < 1
            is_nan = limit != limit  # only NaN is unequal to itself
        except TypeError:
            is_nan = True
        else:
            if below_one:
                logger.warning(
                    f"backup.max_count={limit!r} is below 1; keeping only "
                    "the newest backup"
                )
                return 1
        if is_nan:
            logger.warning(
                f"backup.max_count={limit!r} is not a usable number; not "
                "removing backups by count"
            )
            return None
        return limit

    def _cleanup_old_backups(self, keep: Optional[Path] = None) -> int:
        """Remove old backups based on age and count limits.

        Also cleans up stale .tmp files from interrupted backups. A
        ``max_age_days`` that is not positive or is out of range disables
        only the age limit (see ``_age_cutoff``); the count limit still
        applies. A ``max_backups`` below 1 is treated as 1 (see
        ``_count_limit``).

        Args:
            keep: A backup that is never deleted and always takes the
                first slot of the count limit. ``_create_backup_impl``
                passes the backup it has just finalized, so cleanup can
                never delete it, whatever the retention values or file
                modification times are.

        Returns:
            Number of backups deleted
        """
        deleted_count = 0
        cutoff_time = self._age_cutoff()
        count_limit = self._count_limit()

        try:
            stale_tmp_cutoff = datetime.now(UTC) - timedelta(hours=1)

            # Clean up stale .tmp files from interrupted/crashed backups
            for tmp_file in [
                f
                for f in self.backup_dir.glob("ldr_backup_*.db.tmp")
                if is_safe_glob_result(f, self.backup_dir)
            ]:
                try:
                    mtime = datetime.fromtimestamp(
                        tmp_file.stat().st_mtime, tz=UTC
                    )
                    if mtime < stale_tmp_cutoff:
                        self._remove_backup_files(tmp_file)
                        logger.info(
                            f"Cleaned up stale temp file: {tmp_file.name}"
                        )
                except (OSError, FileNotFoundError):
                    pass

            # Interrupted publication can leave a salt without a database.
            # Keep recent files alone; they may belong to an active export.
            for pattern in (
                "ldr_backup_*.db.salt",
                "ldr_backup_*.db.tmp.salt",
                "ldr_backup_*.db.damaged-*.salt",
            ):
                for salt_file in self.backup_dir.glob(pattern):
                    if not is_safe_glob_result(salt_file, self.backup_dir):
                        continue
                    if pattern == "ldr_backup_*.db.damaged-*.salt" and not (
                        _QUARANTINED_BACKUP_NAME_RE.fullmatch(
                            salt_file.with_suffix("").name
                        )
                    ):
                        continue
                    try:
                        if (
                            not salt_file.with_suffix("").exists()
                            and datetime.fromtimestamp(
                                salt_file.stat().st_mtime, tz=UTC
                            )
                            < stale_tmp_cutoff
                        ):
                            salt_file.unlink()
                    except OSError:
                        pass

            # Get all backup files sorted by modification time (newest first)
            def _safe_mtime(p: Path) -> float:
                try:
                    return p.stat().st_mtime
                except FileNotFoundError:
                    return 0.0

            # After globbing, verify each result is a direct child of backup_dir
            backups = [
                p
                for p in sorted(
                    self.backup_dir.glob("ldr_backup_*.db"),
                    key=_safe_mtime,
                    reverse=True,
                )
                if p.exists() and is_safe_glob_result(p, self.backup_dir)
            ]

            # Damaged copies share the retention budget, but must never
            # displace a usable restore candidate just because they are newer.
            backups.extend(
                sorted(
                    self._quarantined_backup_paths(),
                    key=_safe_mtime,
                    reverse=True,
                )
            )

            # The backup to keep goes first, so it fills the first slot
            # of the count limit even if another file has a later mtime.
            if keep is not None:
                kept = [p for p in backups if p == keep]
                backups = kept + [p for p in backups if p != keep]

            for i, backup in enumerate(backups):
                should_delete = False

                if keep is not None and backup == keep:
                    continue

                # Delete if beyond max count
                if count_limit is not None and i >= count_limit:
                    should_delete = True
                    reason = "exceeds max count"

                # Delete if too old (skipped when there is no age limit)
                elif cutoff_time is not None:
                    try:
                        mtime = datetime.fromtimestamp(
                            backup.stat().st_mtime, tz=UTC
                        )
                        if mtime < cutoff_time:
                            should_delete = True
                            reason = f"older than {self.max_age_days} days"
                    except FileNotFoundError:
                        continue

                if should_delete:
                    try:
                        self._remove_backup_files(backup)
                        deleted_count += 1
                        logger.debug(
                            f"Deleted old backup {backup.name}: {reason}"
                        )
                    except OSError:
                        logger.warning(f"Could not delete backup {backup.name}")

        except Exception:
            logger.exception("Error during backup cleanup")

        if deleted_count > 0:
            logger.info(f"Cleaned up {deleted_count} old backups")

        return deleted_count

    def list_backups(self) -> list[dict]:
        """List all backups for this user.

        Returns:
            List of backup info dictionaries with path, size, and timestamp
        """
        backups = []

        try:

            def _safe_mtime_list(p: Path) -> float:
                try:
                    return p.stat().st_mtime
                except FileNotFoundError:
                    return 0.0

            # After globbing, verify each result is a direct child of backup_dir
            for backup_file in sorted(
                [
                    f
                    for f in self.backup_dir.glob("ldr_backup_*.db")
                    if is_safe_glob_result(f, self.backup_dir)
                ],
                key=_safe_mtime_list,
                reverse=True,
            ):
                try:
                    stat = backup_file.stat()
                except FileNotFoundError:
                    continue
                backups.append(
                    {
                        "filename": backup_file.name,
                        "path": backup_file.name,  # Only expose filename, not full server path
                        "size_bytes": stat.st_size,
                        "created_at": datetime.fromtimestamp(
                            stat.st_mtime, tz=UTC
                        ).isoformat(),
                    }
                )
        except Exception:
            logger.exception("Error listing backups")

        return backups

    def _quarantined_backup_paths(self) -> list[Path]:
        """Return contained damaged copies for key cleanup and retention.

        These files remain encrypted, but are not usable restore candidates
        and must stay out of list_backups(), get_latest_backup() and daily
        backup verification. They still belong to the password lifecycle.
        """
        return [
            path
            for path in self.backup_dir.glob("ldr_backup_*.db.damaged-*")
            if _QUARANTINED_BACKUP_NAME_RE.fullmatch(path.name)
            and is_safe_glob_result(path, self.backup_dir)
            and path.is_file()
        ]

    def _managed_backup_paths(self) -> list[Path]:
        """Include quarantined copies without exposing them for restore.

        The superseded-key purge deletes what this returns, so each joined
        path is re-checked here (purge_and_refresh enumerates with
        list_backup_files instead and re-checks each entry itself).
        list_backups() reports only bare names from a filtered glob, but an
        entry that is not a bare in-root name (a poisoned listing or a
        future caller) must not redirect a purge outside backup_dir. is_safe_glob_result drops a symlink entry and
        an entry that resolves outside backup_dir. The resolved entry must
        also be a direct child of backup_dir: an entry of "." or "" joins
        to backup_dir itself, which is_relative_to() accepts, and its salt
        path would then name a sibling outside the directory. An entry
        that cannot be inspected (an OSError, or a ValueError such as an
        embedded NUL byte) is dropped too. The check is a snapshot, not
        atomic with the caller's unlink. A refused entry is logged without
        its name.
        """
        # Lazy import, as in is_safe_glob_result.
        from ...security.path_validator import resolve_allow_missing

        candidates = [
            self.backup_dir / info["path"] for info in self.list_backups()
        ] + self._quarantined_backup_paths()
        contained = []
        for path in candidates:
            try:
                safe = is_safe_glob_result(path, self.backup_dir) and (
                    resolve_allow_missing(path).parent
                    == resolve_allow_missing(self.backup_dir)
                )
            except (OSError, ValueError):
                safe = False
            if safe:
                contained.append(path)
            else:
                logger.warning(
                    "Skipping a backup entry that is a symlink or is not "
                    "directly inside the backup directory"
                )
        return contained

    def purge_and_refresh(self) -> "BackupResult":
        """Remove old-key backup files and create a current-password backup.

        The result describes creation of the fresh backup. Status separately
        retains any incomplete purge until a later verified purge succeeds.
        Listing or unlink errors never imply that old-key files were removed.
        """
        # Hold per-user lock for the entire purge+create operation to
        # prevent a concurrent backup from writing an old-key backup
        # between the purge and the fresh backup creation.
        # Failures are recorded here and in _create_backup_impl, through the
        # password session; _report_backup_errors would record again through
        # the open engine, which waits on the sidecar this caller holds.
        try:
            user_lock = _get_user_lock(self.username)
        except Exception as exc:
            self.record_purge_incomplete(error_from_exception(exc))
            raise
        with user_lock:
            try:
                purge_incomplete = self._purge_old_backup_files()
            except Exception as exc:
                # Nothing proves the old-password files are gone.
                self.record_purge_incomplete(error_from_exception(exc))
                raise
            result = self._create_backup_impl(purge_incomplete=purge_incomplete)
            result.purge_incomplete = purge_incomplete
            return result

    def record_purge_incomplete(
        self, error_code: BackupError = BackupError.PURGE_INCOMPLETE
    ) -> None:
        """Record that old-password backups may remain after a password change.

        For a purge that failed or was skipped. Writes through this
        service's password first (see ``_password_status_session``).
        """
        record_backup_result(
            self.username,
            False,
            error_code,
            purge_incomplete=True,
            direct_session=self._password_status_session,
        )

    def _purge_old_backup_files(self) -> bool:
        """Delete old-password backup files; return whether any may remain.

        Must be called with the user's backup lock held.
        """
        # Forget results obtained with the old password, so a backup the
        # purge cannot delete is checked again with the new one.
        _get_backup_verifications(self.username).clear()

        # Use one raising enumeration for the actual deletion set;
        # glob/list_backups can silently turn a scan failure into [].
        purge_incomplete = False
        try:
            old_files = list_backup_files(
                self.backup_dir, include_auxiliary=True
            )
        except FileNotFoundError:
            old_files = []
        except OSError:
            old_files = []
            purge_incomplete = True
            logger.warning("Could not list old backups after a password change")

        for old_file in old_files:
            if not self._is_purgeable(old_file):
                continue
            # Re-check containment before deleting, so a listing entry that
            # is a symlink or lies outside backup_dir (a poisoned listing or
            # a future caller) cannot redirect the unlink. _is_purgeable
            # counts an entry it cannot inspect, so it can pass one that this
            # check cannot confirm; leave that entry in place. On Python
            # 3.12/3.13 Path.is_symlink raises on EACCES rather than
            # returning False, so an error here is a refusal too. The check
            # is a snapshot, not atomic with the unlink.
            try:
                contained = is_safe_glob_result(old_file, self.backup_dir)
            except OSError:
                contained = False
            if not contained:
                purge_incomplete = True
                logger.warning(
                    "Refusing to delete an old backup that could not be "
                    "confirmed inside the backup directory"
                )
                continue
            try:
                if old_file.name.endswith(".salt"):
                    old_file.unlink(missing_ok=True)
                else:
                    self._remove_backup_files(old_file)
            except OSError:
                purge_incomplete = True
                logger.warning(
                    "Could not remove an old backup after a password change"
                )

        # An unlink failure, a changed directory, or an unreadable
        # post-purge listing must not become a clean success.
        try:
            remaining = [
                entry
                for entry in list_backup_files(
                    self.backup_dir, include_auxiliary=True
                )
                if self._is_purgeable(entry)
            ]
            purge_incomplete = purge_incomplete or bool(remaining)
        except FileNotFoundError:
            pass
        except OSError:
            purge_incomplete = True

        return purge_incomplete

    def _is_purgeable(self, entry: Path) -> bool:
        """Whether ``entry`` counts as an old backup the purge must remove.

        Only regular files directly in the backup directory are backups the
        purge removes. A symlink or a directory that merely matches a backup
        name is never followed or deleted, so it is not counted as a
        remaining old-password backup either: counting it would keep the
        purge warning up forever with nothing a repeated purge could fix.

        Anything that cannot be inspected counts. This uses one ``os.lstat``
        rather than ``Path.is_symlink``/``is_file``/``exists`` or
        ``is_safe_glob_result``: on Python 3.14 those return False on any
        ``OSError``, so in a directory that can be listed but not searched
        every old backup would look absent and the purge would be recorded
        as verified clean.

        A ``ValueError`` (such as an embedded NUL byte) means the entry
        cannot name a file on disk at all, so it is neither deleted nor
        counted; the entry is skipped and the purge carries on.
        """
        try:
            info = os.lstat(entry)
        except FileNotFoundError:
            return False
        except OSError:
            return True
        except ValueError:
            logger.warning(
                "Ignoring an entry that is not a valid path during a "
                "password-change purge"
            )
            return False
        if S_ISLNK(info.st_mode):
            logger.warning(
                "Ignoring a symlink with a backup name during a "
                "password-change purge"
            )
            return False
        if not S_ISREG(info.st_mode):
            logger.warning(
                "Ignoring a non-file entry with a backup name during a "
                "password-change purge"
            )
            return False
        from ...security.path_validator import resolve_allow_missing

        # Not dead: entries normally come from iterdir() of backup_dir, but
        # this keeps the purge from deleting (or counting) a regular file
        # elsewhere if a listing ever yields one (tests/security/
        # test_identity_paths_and_host_effects.py injects such paths).
        try:
            inside = entry.name not in ("", ".", "..") and (
                resolve_allow_missing(entry.parent)
                == resolve_allow_missing(self.backup_dir)
            )
        except OSError:
            return True
        except ValueError:
            inside = False
        if not inside:
            logger.warning(
                "Ignoring an entry outside the backup directory during a "
                "password-change purge"
            )
        return inside

    def _open_backup_with_current_key(self, backup_path: Path):
        """Return a raw connection to ``backup_path`` keyed with this password."""
        from ..sqlcipher_compat import connect_sqlcipher, get_sqlcipher_module

        conn = connect_sqlcipher(get_sqlcipher_module(), str(backup_path))
        try:
            cursor = conn.cursor()
            try:
                set_sqlcipher_key(cursor, self.password, db_path=backup_path)
                apply_sqlcipher_pragmas(cursor, creation_mode=False)
            finally:
                safe_close(cursor, "backup cursor")
        except Exception:
            safe_close(conn, "backup connection")
            raise
        return conn

    def _classify_backup_key(self, backup_path: Path) -> str:
        """Say whether a backup is under the current password's key.

        Returns one of:

        ``"current"``
            The current password decrypts its schema page. Kept.
        ``"superseded"``
            SQLite answers "file is not a database" and SQLCipher's page
            authentication fails for EVERY page of the file: no page was
            written under this key, so the backup is under another key,
            which for this user's own backup directory is a superseded
            password. Deleted.
        ``"damaged"``
            The schema page fails but other pages authenticate under the
            current key: a backup under the CURRENT key whose first page
            is damaged. Kept for salvage, subject to retention and later
            password changes like the other encrypted backup copies.
        ``"unknown"``
            Anything else (a lock, an I/O error, an unexpected size or
            scan result). Kept; nothing proves it superseded.

        The old password is not known where this runs (a login resolving
        a pending change has only the password that opened the database),
        so "under another key" is proven from the current key's side.
        Only SQLite's fixed messages are inspected; they never carry key
        material.
        """
        try:
            conn = self._open_backup_with_current_key(backup_path)
        except Exception:
            return "unknown"
        try:
            try:
                conn.execute("SELECT count(*) FROM sqlite_master").fetchone()
            except Exception as error:
                if "not a database" not in str(error):
                    return "unknown"
            else:
                return "current"
        finally:
            safe_close(conn, "backup connection")

        # The verdicts below are about the KEY, and the key is derived from
        # the password AND the backup's salt. A rekey never changes a
        # database's salt (only creation writes it), so a backup under a
        # superseded password shares the live database's salt. A backup
        # whose salt differs (missing, damaged, from another database)
        # failed for a reason restoring the salt may fix: keep it.
        if not self._backup_salt_matches_live_database(backup_path):
            return "unknown"

        page_size = get_sqlcipher_settings()["page_size"]
        try:
            size = backup_path.stat().st_size
        except OSError:
            return "unknown"
        if size == 0 or size % page_size:
            return "unknown"
        page_count = size // page_size
        try:
            conn = self._open_backup_with_current_key(backup_path)
        except Exception:
            return "unknown"
        try:
            rows = conn.execute("PRAGMA cipher_integrity_check").fetchall()
        except Exception:
            return "unknown"
        finally:
            safe_close(conn, "backup connection")
        failed_pages = set()
        prefix = "HMAC verification failed for page "
        for row in rows:
            text = str(row[0]) if row else ""
            if not text.startswith(prefix):
                return "unknown"
            try:
                failed_pages.add(int(text[len(prefix) :]))
            except ValueError:
                return "unknown"
        if 1 not in failed_pages:
            return "unknown"
        if failed_pages >= set(range(1, page_count + 1)):
            return "superseded"
        return "damaged"

    def _backup_salt_matches_live_database(self, backup_path: Path) -> bool:
        """True when the backup derives its key from the live database's salt.

        Both sides are read the way key derivation reads them
        (``get_salt_for_database``: the legacy salt when no ``.salt`` file
        exists, Windows CR/LF damage undone). A backup without a salt file
        while the live database has one, a salt that cannot be read, or one
        with different bytes does not match.
        """
        try:
            live_has_salt = has_per_database_salt(self.db_path)
            if live_has_salt and not get_salt_file_path(backup_path).exists():
                return False
            live_salt = get_salt_for_database(self.db_path)
            backup_salt = get_salt_for_database(backup_path)
        except Exception:
            return False
        return hmac.compare_digest(live_salt, backup_salt)

    def _move_damaged_backup_aside(self, backup_path: Path) -> Optional[Path]:
        """Rename a damaged current-key backup out of restore candidates.

        The new name ends in ``.damaged-<UTC timestamp>`` (its salt keeps
        the ``.salt`` suffix after that). Internal key cleanup and retention
        still include this copy; ordinary listing and restore selection do
        not. Mode 0600. Returns the new path, or None when the move failed.
        """
        stamp = datetime.now(UTC).strftime("%Y%m%d_%H%M%S_%f")
        target = backup_path.with_name(f"{backup_path.name}.damaged-{stamp}")
        salt = get_salt_file_path(backup_path)
        try:
            if target.exists():
                return None
            os.replace(backup_path, target)
            os.chmod(target, 0o600)
            if salt.exists() and not salt.is_symlink():
                salt_target = target.with_name(f"{target.name}.salt")
                os.replace(salt, salt_target)
                os.chmod(salt_target, 0o600)
        except OSError:
            return None
        return target

    def purge_backups_under_superseded_key(self) -> int:
        """Delete backups that are under a superseded password's key.

        Used after a pending password change is resolved on login: if the
        rekey took effect, every earlier backup is still encrypted under
        the superseded (possibly compromised) password and is deleted, as
        ``purge_and_refresh`` does after a completed change. If the old
        key survived, every backup still opens and nothing is deleted.
        A backup proven to be under the current key but with a damaged
        first page is moved aside instead (see ``_classify_backup_key``);
        one whose key cannot be determined is kept. A backup without the
        salt its database needs is left to the regular verification,
        which already rejects it.

        A backup that raises while it is examined (an I/O error, a
        SQLite/SQLCipher error, an unreadable salt) is kept and logged;
        the purge carries on with the remaining backups. Errors outside
        ``_PURGE_ITEM_ERRORS`` are bugs and propagate.

        Application callers must hold this database's rekey sidecar lock
        and establish that this password still belongs to its current
        stable epoch, through the entire purge. The per-user backup lock
        below does not exclude a password change.

        Classification can require walking all pages of a backup for HMAC
        verification to establish that its key is superseded. The caller
        holds the database's rekey sidecar across classification and deletion
        so a concurrent password change cannot invalidate that key proof
        before removal. During recovery cleanup, same-user requests requiring
        that sidecar can exhaust their lock-wait budget and receive HTTP 503.
        This is an intentional correctness trade-off.

        Returns:
            The number of backups deleted.
        """
        deleted = 0
        with _get_user_lock(self.username):
            _get_backup_verifications(self.username).clear()
            source_has_salt = has_per_database_salt(self.db_path)
            for backup_path in self._managed_backup_paths():
                # One unreadable or corrupt backup must not shield the rest
                # from the purge: it is kept (fail-safe) and the loop moves
                # on. Only the errors a bad backup can raise are caught;
                # anything else is a bug and still propagates.
                try:
                    if self._purge_one_superseded_backup(
                        backup_path, source_has_salt
                    ):
                        deleted += 1
                except _PURGE_ITEM_ERRORS as error:
                    logger.warning(
                        f"Skipping backup {backup_path.name} in the "
                        f"superseded-key purge: {type(error).__name__}"
                    )
        return deleted

    def _purge_one_superseded_backup(
        self, backup_path: Path, source_has_salt: bool
    ) -> bool:
        """Classify one backup and delete it or move it aside.

        Returns True only when the backup was deleted as superseded.
        """
        backup_salt = get_salt_file_path(backup_path)
        if (
            not backup_path.is_file()
            or backup_path.is_symlink()
            or backup_salt.is_symlink()
            or (source_has_salt and not backup_salt.exists())
        ):
            return False
        verdict = self._classify_backup_key(backup_path)
        if verdict == "damaged":
            if _QUARANTINED_BACKUP_NAME_RE.fullmatch(backup_path.name):
                return False
            moved = self._move_damaged_backup_aside(backup_path)
            if moved is None:
                logger.warning(
                    f"Backup {backup_path.name} is under the current "
                    "key but damaged, and could not be moved aside"
                )
            else:
                logger.warning(
                    f"Backup {backup_path.name} is under the current "
                    f"key but its first page is damaged; moved aside "
                    f"as {moved.name}"
                )
            return False
        if verdict != "superseded":
            return False
        try:
            self._remove_backup_files(backup_path)
        except OSError:
            logger.warning(f"Could not delete backup {backup_path.name}")
            return False
        logger.info(f"Purged backup under superseded key: {backup_path.name}")
        return True

    def get_latest_backup(self) -> Optional[Path]:
        """Get the path to the most recent backup.

        Returns:
            Path to latest backup, or None if no backups exist
        """
        try:

            def _safe_mtime_latest(p: Path) -> float:
                try:
                    return p.stat().st_mtime
                except FileNotFoundError:
                    return 0.0

            # After globbing, verify each result is a direct child of backup_dir
            backups = [
                p
                for p in sorted(
                    self.backup_dir.glob("ldr_backup_*.db"),
                    key=_safe_mtime_latest,
                    reverse=True,
                )
                if p.exists() and is_safe_glob_result(p, self.backup_dir)
            ]
            return backups[0] if backups else None
        except Exception:
            logger.exception("Error finding latest backup")
            return None