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.

180 KB
Raw Blame
"""
Encrypted database management using SQLCipher.
Handles per-user encrypted databases with browser-friendly authentication.
"""

import hmac
import secrets
import errno
import os
import stat
import threading
import time
from collections.abc import Generator
from contextlib import contextmanager
from contextvars import ContextVar
from dataclasses import dataclass
from enum import StrEnum, unique
from pathlib import Path
from typing import Any, Dict, Never, Optional, assert_never

from loguru import logger
from sqlalchemy import create_engine, event, text
from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.pool import QueuePool, StaticPool

from ..config.paths import get_data_directory, get_user_database_filename
from ..settings.env_registry import get_env_setting

# ``redact_secrets`` stays local to the exception handlers that use it. A
# module-scope import would initialize the broad ``security`` package while
# encrypted_db is still defining its bootstrap state, unnecessarily widening
# this low-level module's dependency surface.
#
# Historically, ``security/__init__.py`` eagerly re-exported file-integrity
# symbols, which created this cycle during database bootstrap:
#
#   database/encrypted_db.py
#     → security/__init__.py
#       → security/file_integrity/integrity_manager.py
#         → database/session_context.py
#           → database/encrypted_db.py  (mid-load, ``db_manager`` not yet
#                                         defined → ImportError)
#
# File-integrity exports are now lazy, and test_integrity_bootstrap_guard.py
# locks in the current fail-loud session-context import. Keeping this import
# local avoids coupling encrypted_db bootstrap to unrelated security package
# initialization. Other files in PRs #4168/#4175/#4181 can import
# ``redact_secrets`` at module scope because they are not on that bootstrap
# path.
from .sqlcipher_compat import connect_sqlcipher, get_sqlcipher_module
from .pool_config import (
    MAX_OVERFLOW,
    POOL_PRE_PING,
    POOL_RECYCLE_SECONDS,
    POOL_SIZE,
)
from .rekey_lock import (
    DatabaseKeyCheckUnavailableError,
    RekeyEpochState,
    RekeyEpochUnreadableError,
    RekeyIndeterminateError,
    RekeyLockContentionError,
    RekeyOperationalError,
    RekeyRecoveryRequiredError,
    RekeySidecarIOError,
    RekeySidecarOperation,
    RekeyStage,
    RekeyUnavailableError,
    acquire_rekey_lock,
    begin_rekey_epoch,
    clear_pending_rekey_epoch,
    cleanup_stale_rekey_temps,
    commit_rekey_epoch,
    publish_new_database_epoch,
    read_or_initialize_rekey_epoch,
    read_rekey_epoch,
    read_rekey_epoch_state,
)
from .sqlcipher_utils import (
    set_sqlcipher_key,
    set_sqlcipher_rekey,
    apply_cipher_defaults_before_key,
    apply_sqlcipher_pragmas,
    apply_performance_pragmas,
    check_sqlcipher_key,
    is_operational_sqlite_error,
    get_sqlcipher_busy_timeout_ms,
    sqlcipher_busy_timeout_pragma,
    create_database_salt,
    get_salt_file_path,
    has_per_database_salt,
    get_key_from_password,
    get_sqlcipher_version,
    create_sqlcipher_connection,
)


@dataclass(frozen=True, slots=True)
class _ConnectionCacheEntry:
    engine: Engine
    verifier: tuple[bytes, bytes]
    epoch: str


@dataclass(frozen=True, slots=True)
class _ConnectionGeneration:
    username: str
    engine: Engine
    verifier: tuple[bytes, bytes] | None
    epoch: str | None


@dataclass(frozen=True, slots=True)
class _CurrentConnectionGeneration:
    generation: _ConnectionGeneration


@dataclass(frozen=True, slots=True)
class _StaleConnectionGenerationDetached:
    generation: _ConnectionGeneration


@dataclass(frozen=True, slots=True)
class _ConnectionGenerationChanged:
    pass


@dataclass(frozen=True, slots=True)
class _ConnectionGenerationMissing:
    pass


type _ConnectionGenerationClaim = (
    _CurrentConnectionGeneration
    | _StaleConnectionGenerationDetached
    | _ConnectionGenerationChanged
    | _ConnectionGenerationMissing
)


class LoginProof:
    """The durable rekey epoch a successful ``open_user_database`` proved.

    Filled only inside :func:`capture_login_proof`. ``epoch`` is the
    committed epoch the password was verified against UNDER the rekey
    sidecar lock (the claimed cache generation's epoch, or the epoch the
    cold open ran under). A login binds its session to this value, not
    to whatever epoch is live by the time the session is created: a rekey
    in another worker can commit in between, and an old-password login
    straddling it must not come away with a new-epoch session.
    """

    __slots__ = ("username", "epoch")

    def __init__(self) -> None:
        self.username: str | None = None
        self.epoch: str | None = None

    def proven_epoch_for(self, username: str) -> str:
        """The epoch proved for ``username``, or refuse (fail closed).

        A successful open always records a proof for the user it opened,
        so a missing proof, one recorded for a different username, or one
        without an epoch means the caller cannot show which epoch the
        password was verified against; minting a session anyway would
        skip the straddle check silently.

        Raises:
            MissingLoginProofError: no usable proof for ``username``.
        """
        if self.username != username or self.epoch is None:
            raise MissingLoginProofError(username)
        return self.epoch


class MissingLoginProofError(Exception):
    """A successful login open left no epoch proof for the logged-in user."""

    def __init__(self, username: str) -> None:
        self.username = username
        super().__init__("no login epoch proof was recorded for this login")


# Per-thread proof collector. Only set while a caller is inside
# capture_login_proof(), so opens made by every other caller (request
# middleware, sockets, background threads) record nothing and nothing
# lingers on a pooled worker thread between requests.
_login_proof_collector = threading.local()


@contextmanager
def capture_login_proof() -> Generator[LoginProof, None, None]:
    """Collect the epoch proved by ``open_user_database`` on this thread.

    The OUTERMOST successful open inside the block wins: the phase-2 RAG
    rekey re-enters ``open_user_database`` on the same thread before the
    outer call returns, and the outer call records last.
    """
    previous = getattr(_login_proof_collector, "proof", None)
    proof = LoginProof()
    _login_proof_collector.proof = proof
    try:
        yield proof
    finally:
        _login_proof_collector.proof = previous


def _record_login_proof(username: str, epoch: str | None) -> None:
    proof = getattr(_login_proof_collector, "proof", None)
    if proof is None:
        return
    proof.username = username
    proof.epoch = epoch


@dataclass(frozen=True, slots=True)
class _ConnectionGenerationUnverifiable:
    """A generation is cached, but its epoch could not be checked.

    The rekey sidecar could not be opened or locked, or the epoch read
    failed once it was. Nothing was read, so nothing proves the generation
    stale: it stays cached, and ``error`` is the typed fault for the
    callers that ask for it (``raise_unverifiable``).
    """

    error: RekeyOperationalError


@unique
class UserConnectionStatus(StrEnum):
    """Outcome of :meth:`DatabaseManager.probe_user_connection`."""

    # A cached engine that the durable rekey epoch still vouches for.
    CONNECTED = "connected"
    # No usable engine: none is cached, or the cached one was stale and has
    # been detached. Opening the database again needs the password.
    NOT_CONNECTED = "not-connected"
    # An engine is cached, but the rekey sidecar or epoch could not be read
    # (fd exhaustion, EACCES, EIO, ...), or another holder kept the sidecar
    # past the wait bound. It is kept, and the next successful probe
    # re-checks it, so the condition is temporary, not a verdict.
    UNAVAILABLE = "unavailable"


class DatabaseInitializationError(Exception):
    """Raised when a per-user database opens but its schema can't be initialised.

    Distinct from credential / decryption failures (which return ``None``
    from :py:meth:`DatabaseManager.open_user_database`) so callers — chiefly
    the login route — can avoid penalising the user's lockout counter and
    surface a different error message. The credentials are valid; the
    database state isn't.
    """


# How long change_password retries its non-blocking sidecar flock: long
# enough to ride out other processes' millisecond cache probes, short
# enough that a genuine cross-process rekey owner still fails promptly.
_REKEY_CONTENTION_RETRY_SECONDS = 1.0

# The epoch the calling thread's most recent successful ``change_password``
# committed, as ``(db_path, epoch)``. ``refresh_backups_after_password_change``
# reads it to fence the post-change backup refresh on that exact change.
# Thread-local because ``change_password`` and the refresh run back to back on
# the request's thread; another thread's change must never be mistaken for it.
_last_password_change = threading.local()

# Ceiling on each wait a request-path caller makes for the user's rekey
# sidecar: a cache claim (lifecycle lock, sidecar gate and flock together),
# a cold open and the epoch initialization at registration. A holder keeps
# the sidecar for as long as its work runs (a rekey or cold open of a large
# database: seconds to minutes) and a stopped process or a hung mount keeps
# it indefinitely, so an unbounded wait parks every request for the user,
# and the shared log-drain thread, behind it. Past the bound the caller gets
# RekeyLockContentionError, which those paths answer as a retryable 503 with
# Retry-After, like any other sidecar fault. Inside an HTTP request, a wait
# that gives up also sets a flag (``sidecar_wait_budget``): each later claim
# then tries once without waiting, until one of them gets the sidecar again.
# The flag is not a running total of the time waited.
_SIDECAR_WAIT_TIMEOUT_SECONDS = 5.0


def _seconds_until(deadline: float) -> float:
    return max(0.0, deadline - time.monotonic())


class _SidecarWaitBudget:
    """The state one ``sidecar_wait_budget`` scope shares among its waits."""

    __slots__ = ("active", "spent")

    def __init__(self) -> None:
        self.active = True
        # Set when one of the scope's sidecar waits ran out (another holder
        # kept the sidecar past the bound); cleared when a later one gets it.
        self.spent = False


_sidecar_wait_budget: ContextVar[_SidecarWaitBudget | None] = ContextVar(
    "ldr_sidecar_wait_budget", default=None
)


@contextmanager
def sidecar_wait_budget() -> Generator[None, None, None]:
    """Share a give-up flag among the rekey sidecar waits in this scope.

    Entered for every HTTP request (``request_database_scope`` in
    ``web/dependencies/auth.py``, from ``DatabaseMiddleware``). Each wait
    is bounded by ``_SIDECAR_WAIT_TIMEOUT_SECONDS``, and one authenticated
    request makes up to four of them before its route runs:
    DatabaseMiddleware's probe and its reopen of the database, then
    ``require_auth``'s probe and its classifying probe. Without the scope a
    holder that keeps the sidecar would make each of them wait out the
    bound, about 20 s before a 503 that says ``Retry-After: 5``, on the
    shared threads that serve every user. In the scope, a wait that gives
    up sets the ``spent`` flag, and each later claim then tries once
    without waiting and raises (or reports) ``RekeyLockContentionError``
    while the sidecar is still held, until one of them gets the sidecar
    again, which clears the flag. So a request waits out the bound at most
    once while the sidecar stays held, rather than once per claim.

    The flag is not a running total of the time waited: a request whose
    waits each get the sidecar just under the bound clears it every time
    and can wait close to the full bound for each claim. Waits made
    outside every scope (the log drain, for one) wait up to the bound each.

    The flag is shared with every thread the request's context is copied
    to (``run_db_sync`` and the AnyIO workers that run sync routes and
    dependencies), so their waits set and clear the same one. When the
    scope exits, copies retained by background research or indexing jobs
    stop sharing it and each subsequent claim gets its own bounded wait.
    """
    budget = _SidecarWaitBudget()
    token = _sidecar_wait_budget.set(budget)
    try:
        yield
    finally:
        # Resetting this ContextVar restores only the current context. A
        # background worker keeps the same object in its copied context,
        # so end its lifetime there too, including on request failure.
        budget.active = False
        _sidecar_wait_budget.reset(token)


def _sidecar_wait_seconds() -> float:
    """How long the current scope's next sidecar wait may last."""
    budget = _sidecar_wait_budget.get()
    if budget is not None and budget.active and budget.spent:
        return 0.0
    return _SIDECAR_WAIT_TIMEOUT_SECONDS


def _record_sidecar_wait(*, ran_out: bool) -> None:
    """Record that a sidecar wait ran out, or that one got the sidecar."""
    budget = _sidecar_wait_budget.get()
    if budget is not None and budget.active:
        budget.spent = ran_out


@contextmanager
def _bounded_rekey_lock(db_path: Path) -> Generator[None, None, None]:
    """Hold the sidecar like ``acquire_rekey_lock``, for a request-path caller.

    Waits at most ``_sidecar_wait_seconds()`` and records how the wait
    ended for the scope's later waits (``sidecar_wait_budget``). A
    ``RekeyLockContentionError`` that the protected body raises is not this
    wait's and is not recorded.
    """
    acquired = False
    try:
        with acquire_rekey_lock(db_path, timeout=_sidecar_wait_seconds()):
            acquired = True
            _record_sidecar_wait(ran_out=False)
            yield
    except RekeyLockContentionError:
        if not acquired:
            _record_sidecar_wait(ran_out=True)
        raise


# Ceiling on the busy-handler wait of a WAL checkpoint that runs while this
# process holds the user's rekey sidecar gate (the post-rekey TRUNCATE and
# the TRUNCATE before an engine disposal, which applies the same bound on
# its few ungated call sites). Every request for that user
# (is_user_connected, get_session, cold opens) queues on the gate, so a
# checkpoint that waits out the full connection busy_timeout
# (``db_config.busy_timeout_ms``, 30 s by default) behind a reader that
# pins the WAL would stall all of them for that long. Both checkpoints are
# best-effort -- the committed frames are durable in the WAL either way --
# so they give up after this bound instead.
_GATED_CHECKPOINT_BUSY_TIMEOUT_MS = 1000
# Fixed statement template; only an ``int`` is ever formatted into it.
_GATED_CHECKPOINT_BUSY_TIMEOUT_PRAGMA = "PRAGMA busy_timeout = {:d}"


def _gated_checkpoint_busy_timeout_ms() -> int:
    return min(
        get_sqlcipher_busy_timeout_ms(), _GATED_CHECKPOINT_BUSY_TIMEOUT_MS
    )


def _gated_checkpoint_busy_timeout_pragma() -> str:
    """Return the ``PRAGMA busy_timeout`` statement for a gated checkpoint.

    Built like ``sqlcipher_busy_timeout_pragma``: PRAGMA takes no bind
    parameters, so the bound is coerced with ``int()`` and formatted into a
    fixed template whose ``{:d}`` spec refuses anything that is not an
    integer, so the statement can only ever carry an integer literal.
    """
    timeout_ms = int(_gated_checkpoint_busy_timeout_ms())
    return _GATED_CHECKPOINT_BUSY_TIMEOUT_PRAGMA.format(timeout_ms)


class _RekeyVerificationError(Exception):
    check: str

    def __init__(self, check: str) -> None:
        self.check = check
        super().__init__(f"rekey verification failed: {check}")


def _raise_rekey_unavailable(db_path: Path, stage: RekeyStage) -> Never:
    raise RekeyUnavailableError(db_path, stage) from None


def _best_effort_chmod(path, mode: int, *, warn: bool = False) -> None:
    """Tighten permissions on ``path`` to ``mode``, never raising.

    Permission hardening must NOT be able to break database creation. On
    filesystems that don't support POSIX chmod — some Docker bind mounts and
    network/FUSE volumes, notably Docker Desktop on macOS/Windows — os.chmod
    can raise OSError even for the file's owner. In that case we leave the
    path at its default (umask) mode rather than failing registration/login.

    ``warn=True`` surfaces the failure at warning level: a silent downgrade
    leaves the path group/world-readable, which matters for DB files holding
    user data (the unencrypted fallback is plaintext). The owner-only
    directory chmod is defense-in-depth, so it stays at debug.
    """
    try:
        os.chmod(str(path), mode)
    except OSError:
        log = logger.warning if warn else logger.debug
        log(
            f"Could not set permissions {oct(mode)} on {path}; "
            "left at filesystem default",
            exc_info=True,
        )


def _create_private_file(path: Path) -> tuple[int, int]:
    """Create ``path`` as an empty, owner-only regular file, never via a link.

    ``O_CREAT | O_EXCL`` fails when anything exists at ``path`` — including a
    symlink, dangling or not, which is exactly the no-follow protection the
    ``.salt`` file already gets in ``create_database_salt``. ``O_NOFOLLOW`` is
    added where the platform has it. SQLite and SQLCipher treat the empty
    file as a fresh database (#6354).

    Returns the ``(st_dev, st_ino)`` of the file it created, read from the
    descriptor rather than the name, so ``_assert_reserved_file`` can tell
    afterwards whether the pathname still refers to this file.
    """
    flags = os.O_WRONLY | os.O_CREAT | os.O_EXCL | getattr(os, "O_NOFOLLOW", 0)
    try:
        fd = os.open(str(path), flags, 0o600)
    except OSError as exc:
        # A platform may report the symlink through O_NOFOLLOW (ELOOP)
        # before O_EXCL gets to say EEXIST; both mean "something is already
        # at this path", so callers see one exception type.
        if exc.errno == errno.ELOOP:
            raise FileExistsError(
                errno.EEXIST, "Path is a symlink", str(path)
            ) from exc
        raise
    try:
        reserved = os.fstat(fd)
    finally:
        os.close(fd)
    return reserved.st_dev, reserved.st_ino


def _assert_reserved_file(path: Path, reserved: tuple[int, int]) -> None:
    """Fail when ``path`` no longer names the file that was reserved.

    This is partial hardening, not a guarantee. The reservation cannot be held
    across the database open: SQLCipher and SQLite take a pathname, and
    neither ``sqlcipher3`` nor the standard ``sqlite3`` module can ask for
    ``SQLITE_OPEN_NOFOLLOW`` -- it is an open flag of the C API, not a URI
    parameter, and a ``file:...?nofollow=1`` open still follows the link.

    What the check does catch is a pathname that is still replaced when it
    runs -- by a symlink, a hard link or a freshly created file -- and the
    create then fails instead of adopting it (#6378). What it cannot catch is
    a replacement that is undone again before it runs: a swap to another
    file, open, and swap back leaves the connection on the other file while
    the name looks untouched. The protection that does not depend on timing
    is the refusal of a link that is already there and the exclusive,
    no-follow creation in :func:`_create_private_file`.
    """
    try:
        stat_result = os.lstat(path)
    except OSError as exc:
        raise ValueError(
            f"Database path {path} disappeared while the database was "
            "being created; refusing to use it"
        ) from exc

    if (
        stat.S_ISLNK(stat_result.st_mode)
        or (stat_result.st_dev, stat_result.st_ino) != reserved
    ):
        raise ValueError(
            f"Database path {path} was replaced while the database was "
            "being created; refusing to use it"
        )


def _db_path_is_symlinked(db_path: Path, username: str) -> bool:
    """True when a user-database pathname is a symlink, which is refused.

    SQLite and SQLCipher take a pathname and follow it, and neither
    ``sqlcipher3`` nor the standard library's ``sqlite3`` can ask for
    ``SQLITE_OPEN_NOFOLLOW`` -- it is an open flag of the C API, not a URI
    parameter, and a ``file:...?nofollow=1`` open still follows the link. So
    the link is refused here rather than declined at the open.

    Replacing ``ldr_user_<hash>.db`` with a link pointing outside the data
    directory otherwise makes the next open write a fresh multi-megabyte
    encrypted database at the link's target: the same one-shot primitive the
    create path refuses (#6354), on every path that opens an existing
    database (#6493).

    Called by each entry point that hands this pathname to SQLCipher, so a new
    one inherits the check by being written next to the others. Only the LEAF
    is checked: a data directory that is itself a symlink (another volume) is a
    legitimate deployment and must keep working.

    The log line carries the pathname and the link target because that is the
    only actionable evidence an operator has; the directory is 0700, so the
    disclosure is to someone who can already read it.
    """
    if not db_path.is_symlink():
        return False
    try:
        target = os.readlink(db_path)
    except OSError as exc:
        target = f"<unreadable: {exc.strerror}>"
    logger.error(
        f"Database path for user {username!r} is a symlink: {db_path} -> "
        f"{target}. Refusing to open it; replace it with the real database "
        "file."
    )
    return True


def _remove_partial_user_db_files(db_path: Path) -> None:
    """Remove a half-created user database and all of its sidecar files.

    Called from ``create_user_database``'s failure paths. The encrypted
    branch writes a per-database ``.salt`` file (via ``create_database_salt``)
    *before* the DB itself, and finalization publishes a ``.rekey.epoch``.
    SQLCipher/WAL can also leave ``-wal``/``-shm`` sidecars. Removing only the
    ``.db`` — as these cleanup paths used to —
    orphans the ``.salt``: a retry then hits ``create_database_salt``'s
    ``FileExistsError`` (it refuses to overwrite to prevent data loss), so the
    username stays permanently un-registerable even after the orphaned auth
    row is cleaned up. Delete every artifact so the on-disk state is atomic
    and the username is reusable on a subsequent retry.

    ``create_user_database`` calls this from five sites: its four
    failure-cleanup blocks (structure creation, engine build, migration,
    finalization) and its salt-creation recovery path (an
    orphan left by a prior hard-killed create or by pre-#4934 cleanup that
    removed only the ``.db``). In every case the ``db_path.exists()`` guard at
    the top of ``create_user_database`` already ran and found no *pre-existing*
    database, so any ``.db``/``.salt`` present now is this call's own partial
    work — removing it cannot destroy live data, because a valid encrypted DB
    always has both the ``.db`` and its ``.salt``.

    Both the ``-wal``/``-shm`` (WAL mode, the default) and ``-journal``
    (rollback-journal modes: DELETE/TRUNCATE/PERSIST) sidecars are removed so
    the cleanup is correct regardless of the configured ``journal_mode``.
    The stable ``.rekey.lock`` inode is deliberately preserved so processes
    already waiting on it cannot split into a second lock domain.

    Never raises — cleanup must not mask the original creation error.
    """
    # Derive every artifact path up front, guarded: the "Never raises"
    # contract has to hold even if a path helper is handed something odd
    # (e.g. a nameless path, where ``with_name``/``with_suffix`` raise), so a
    # derivation failure is logged and swallowed rather than propagating and
    # masking the original creation error.
    try:
        db_path = Path(db_path)
        artifacts = (
            db_path,
            get_salt_file_path(db_path),
            db_path.with_name(db_path.name + "-wal"),
            db_path.with_name(db_path.name + "-shm"),
            db_path.with_name(db_path.name + "-journal"),
            db_path.with_name(f"{db_path.name}.rekey.epoch"),
            db_path.with_name(f"{db_path.name}.rekey.pending"),
        )
    except Exception:
        logger.warning(
            f"Could not derive partial user DB artifact paths: {db_path!r}"
        )
        return

    for path in artifacts:
        try:
            # nosemgrep: semgrep.rules.path-traversal-risk, reason: Creation failure cleanup receives the generated user DB path and derives only its fixed sidecar names
            path.unlink(missing_ok=True)
        except OSError:
            # Best-effort: a leftover artifact is worth surfacing but must not
            # mask the original creation error. The path is enough to
            # diagnose; skip the exception detail per the sensitive-logging
            # policy.
            logger.warning(f"Could not remove partial user DB artifact {path}")


# Upper bounds of ``backup.max_count`` / ``backup.max_age_days``: the
# ``max_value`` of each in defaults/default_settings.json (a test pins
# these to that file). The settings UI and import enforce them, but env
# overrides (LDR_BACKUP_MAX_*) do not, so a value above them is not trusted
# here and the pre-migration backup then removes nothing.
_BACKUP_MAX_COUNT_LIMIT = 30
_BACKUP_MAX_AGE_DAYS_LIMIT = 90


def _read_backup_retention(engine: Engine) -> Optional[tuple[int, int]]:
    """Read ``backup.max_count`` / ``backup.max_age_days`` for a user DB.

    Reads them the way the login backup does (``SettingsManager``, env
    overrides included), but read-only: nothing is seeded or written into
    a database that has not been migrated yet. Returns ``None`` when the
    values cannot be read, are not positive integers, or exceed the
    settings' own ``max_value``, so the caller can keep every existing
    backup instead of guessing a limit.
    """
    try:
        from ..settings.manager import SettingsManager

        with sessionmaker(bind=engine)() as session:
            sm = SettingsManager(session, seed_defaults=False)
            max_count = sm.get_setting("backup.max_count", None)
            max_age_days = sm.get_setting("backup.max_age_days", None)
    except Exception as e:
        logger.warning(
            "Could not read backup retention settings before migration "
            f"({type(e).__name__}); keeping all existing backups"
        )
        return None

    values = []
    for value, limit in (
        (max_count, _BACKUP_MAX_COUNT_LIMIT),
        (max_age_days, _BACKUP_MAX_AGE_DAYS_LIMIT),
    ):
        if isinstance(value, float) and value.is_integer():
            value = int(value)
        if (
            isinstance(value, bool)
            or not isinstance(value, int)
            or not 1 <= value <= limit
        ):
            logger.warning(
                "Backup retention settings are not integers within their "
                "allowed range; keeping all existing backups"
            )
            return None
        values.append(value)
    return values[0], values[1]


class DatabaseManager:
    """Manages encrypted SQLCipher databases for each user."""

    def __init__(self):
        self.connections: Dict[str, Engine] = {}
        # username -> (salt, digest) proving which password opened the cached
        # engine. `connections` is keyed by username ALONE, so a cache hit
        # says nothing about the credential presented on THIS call; without a
        # verifier, returning the cached engine silently accepts any password.
        # See _password_matches_cached().
        #
        # A fast keyed digest is the right primitive here: this is an
        # in-process equality check, not at-rest storage, and the plaintext
        # password already lives in memory in session_password_store, so a
        # slow KDF would add cost without reducing exposure.
        self._password_verifiers: Dict[str, tuple[bytes, bytes]] = {}
        self._connection_epochs: dict[str, str] = {}
        self._verifier_key = secrets.token_bytes(32)
        self._connections_lock = threading.RLock()
        # Per-user locks serializing the cold-open (engine build + migration)
        # so two concurrent first-opens of one user never run alembic against
        # the same database file at once. Created lazily under
        # _connections_lock; see open_user_database / _get_init_lock.
        self._init_locks: Dict[str, threading.Lock] = {}
        self._rekey_locks: dict[str, threading.Lock] = {}
        # Per-user gate serializing change_password CALLS against each
        # other. Deliberately separate from _rekey_locks (the lifecycle
        # lock ordinary opens/claims take briefly): sharing one lock made
        # every cache-hit open a potential false "rekey already in
        # progress" for a concurrent password change. The cross-exclusion
        # between a rekey and opens is provided by the on-disk sidecar,
        # which change_password holds across its whole staged sequence.
        self._password_change_locks: dict[str, threading.Lock] = {}
        self._data_dir_override: Optional[Path] = None
        self._initialized_data_dirs: set[Path] = set()

        # Check SQLCipher availability
        self.has_encryption = self._check_encryption_available()

        # ----------------------------------------------------------------
        # Pool class selection — see ADR-0004
        #
        # We use QueuePool (pool_size=20, max_overflow=40,
        # pool_timeout=10) for production and StaticPool for tests.
        #
        # Why pool_size=20:
        #
        # 1. SQLCipher + WAL mode can leak file handles when connections
        #    close out of open-order. Fewer pooled connections = fewer
        #    opportunities for out-of-order closes during pool_recycle.
        #    See: https://github.com/sqlcipher/android-database-sqlcipher/issues/6
        #    See: https://github.com/dotnet/efcore/issues/35010
        #
        # 2. SQLite serializes all writes through a single file lock.
        #    Multiple pooled connections don't improve throughput — they
        #    just hold FDs (up to 3 per connection in WAL mode).
        #
        # 3. The cleanup scheduler periodically calls engine.dispose()
        #    to release all pooled connections, preventing long-lived
        #    handles from accumulating over days of idle operation.
        #
        # Why pool_size=20 and not 1: inject_current_user() creates a
        # QueuePool session on every request via g.db_session. With the
        # UI polling /api/research/<id>/status every 1-2s plus other
        # API calls and before_request middleware, pool_size=1
        # (max_overflow=2, so 3 total) is easily exhausted — causing
        # 30-second timeouts and PendingRollbackError cascades.
        # pool_size=20 + max_overflow=40 (60 total) provides ample
        # headroom for concurrent requests and multiple browser tabs.
        #
        # Why not NullPool: SQLCipher's PRAGMA key adds ~0.2ms per
        # connection open. With 20-30 queries per page load, NullPool
        # adds a noticeable 4-6ms overhead vs QueuePool's ~1.5ms.
        # ----------------------------------------------------------------
        self._use_static_pool = bool(os.environ.get("TESTING"))
        self._pool_class = StaticPool if self._use_static_pool else QueuePool

    def _ensure_data_dir(self, path: Path) -> None:
        """Create and harden one resolved encrypted-database directory."""
        path = Path(path)
        with self._connections_lock:
            if path in self._initialized_data_dirs:
                return
            # Lazy import: avoid a security -> ... -> database import cycle at
            # module load time (this can run during DatabaseManager
            # construction, which happens early in app bootstrap).
            from ..security.directory_creation import create_directory

            create_directory(
                path,
                context="encrypted database storage",
            )
            # Restrict the per-user DB directory to the owner (0o700).
            # This covers SQLite WAL/SHM sidecars and plaintext fallback temp
            # files even when SQLite recreates them with umask permissions.
            _best_effort_chmod(path, 0o700)
            self._initialized_data_dirs.add(path)

    @property
    def data_dir(self) -> Path:
        """Return the encrypted-DB directory for the live data root.

        The default path is resolved on every access so a process that imports
        ``db_manager`` before ``LDR_DATA_DIR`` is set does not stay bound to
        the platform default. Explicit assignments use the setter below and
        remain stable for tests and programmatic callers.
        """
        path = self._data_dir_override
        if path is None:
            path = get_data_directory() / "encrypted_databases"
        self._ensure_data_dir(path)
        return path

    @data_dir.setter
    def data_dir(self, value: Path) -> None:
        """Set an explicit data-directory override for this manager."""
        path = Path(value)
        self._data_dir_override = path
        self._ensure_data_dir(path)

    def _get_pool_kwargs(self) -> Dict[str, Any]:
        """Get pool configuration kwargs based on pool type.

        StaticPool doesn't support pool_size or max_overflow.
        QueuePool uses moderate sizing to handle concurrent web requests
        while limiting FD usage. See ADR-0004 for rationale.
        """
        if self._use_static_pool:
            return {}
        return {
            "pool_size": POOL_SIZE,
            "max_overflow": MAX_OVERFLOW,
            "pool_timeout": 10,
            "pool_pre_ping": POOL_PRE_PING,
            "pool_recycle": POOL_RECYCLE_SECONDS,
        }

    def _is_valid_encryption_key(self, password: str) -> bool:
        """
        Check if the provided password is valid (not None, empty, or whitespace-only).

        Args:
            password: The password to check

        Returns:
            True if the password is valid, False otherwise
        """
        return password is not None and password.strip() != ""

    def is_user_connected(self, username: str) -> bool:
        """Check if a user has an active database connection.

        Thread-safe accessor for external callers.

        Args:
            username: The username to check

        Returns:
            True if the user has an active connection. False also covers a
            cached engine that cannot be checked right now because the rekey
            sidecar or epoch could not be read, or the sidecar stayed held
            past the wait bound (``_SIDECAR_WAIT_TIMEOUT_SECONDS``; once a
            request's wait has given up, its later claims try once without
            waiting until one gets the sidecar again:
            ``sidecar_wait_budget``); use
            :meth:`probe_user_connection` to tell that temporary case apart.
        """
        with self._acquire_current_connection_generation(
            username
        ) as generation:
            return generation is not None

    def probe_user_connection(self, username: str) -> UserConnectionStatus:
        """Classify ``username``'s cached connection, separating a fault.

        ``is_user_connected`` answers False both when no usable engine is
        cached and when a cached engine could not be checked because the
        rekey sidecar could not be opened or locked, or the epoch read
        failed (an fd-exhaustion spike, EACCES, EIO, another holder keeping
        the sidecar past the wait bound). The engine is kept in
        the second case and re-checked by the next probe, so it calls for a
        retry, not a new login. Request authentication uses this to answer
        such a fault with a retryable 503 instead of a 401.

        Never raises a ``RekeyOperationalError``: a fault while releasing
        the sidecar after the claim is reported as ``UNAVAILABLE`` too.
        """
        try:
            with self._acquire_current_connection_generation(
                username, raise_unverifiable=True
            ) as generation:
                connected = generation is not None
        except RekeyOperationalError:
            return UserConnectionStatus.UNAVAILABLE
        if connected:
            return UserConnectionStatus.CONNECTED
        return UserConnectionStatus.NOT_CONNECTED

    def _check_encryption_available(self) -> bool:
        """Probe SQLCipher before considering the development fallback.

        Working SQLCipher always returns True, even when
        ``LDR_BOOTSTRAP_ALLOW_UNENCRYPTED`` is enabled. The flag is consulted
        only if the probe fails; it permits plaintext SQLite instead of a
        startup error. It neither forces plaintext storage nor converts an
        existing encrypted database.

        Returning False also removes password verification from database
        opens: SQLite has no credential to check. This fallback is for
        isolated local development, not authenticated shared deployments.
        """
        try:
            import os as os_module
            import tempfile

            # Test if SQLCipher actually works, not just if it imports
            with tempfile.NamedTemporaryFile(delete=False) as tmp:
                tmp_path = tmp.name

            try:
                # Try to create a test encrypted database
                sqlcipher_module = get_sqlcipher_module()
                sqlcipher = sqlcipher_module.dbapi2

                conn = connect_sqlcipher(sqlcipher, tmp_path)
                try:
                    cursor = conn.cursor()
                    # Use creation_mode=True since we're creating a new test database
                    apply_cipher_defaults_before_key(cursor)
                    # Use centralized key setting
                    set_sqlcipher_key(cursor, "testpass")
                    # Apply post-key pragmas (kdf_iter for new DB)
                    apply_sqlcipher_pragmas(cursor, creation_mode=True)
                    apply_performance_pragmas(cursor)

                    # Check SQLCipher version
                    version = get_sqlcipher_version(cursor)
                    if version:
                        major = (
                            version.split(".")[0]
                            if "." in version
                            else version[0]
                        )
                        if major.isdigit() and int(major) < 4:
                            logger.warning(
                                f"SQLCipher version {version} detected. "
                                "Version 4.x+ is recommended for proper PRAGMA ordering."
                            )

                    cursor.close()
                    # Now use the connection for table operations
                    conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
                    conn.execute("INSERT INTO test VALUES (1)")
                    result = conn.execute("SELECT * FROM test").fetchone()

                    if result != (1,):
                        raise RuntimeError("SQLCipher encryption test failed")
                    logger.info(
                        "SQLCipher available and working - databases will be encrypted"
                    )
                    return True
                finally:
                    from ..utilities.resource_utils import safe_close

                    safe_close(conn, "SQLCipher test connection")
            except Exception:
                logger.warning("SQLCipher module found but not working")
                raise ImportError("SQLCipher not functional")
            finally:
                # Clean up test file
                try:
                    os_module.unlink(tmp_path)
                except OSError as e:
                    logger.debug(
                        f"Failed to clean up temp file {tmp_path}: {e}"
                    )

        except ImportError:
            # Check if user has explicitly allowed unencrypted databases.
            # Registry handles deprecated LDR_ALLOW_UNENCRYPTED fallback automatically.
            allow_unencrypted = get_env_setting(
                "bootstrap.allow_unencrypted", False
            )

            if not allow_unencrypted:
                # No ``password`` is in scope here (this runs at
                # bootstrap, before any user authenticates), and the
                # caught ``ImportError`` is purely about the SQLCipher
                # package being missing. Still drop the traceback to
                # keep this file's logging discipline uniform with the
                # rest of the #4182 sweep: no exception traceback is
                # written to the production log from this module. The
                # install-hint message is the user-facing diagnostic;
                # the traceback adds no value beyond it. ``warning``
                # rather than ``error``/``exception`` so the custom
                # "logger.exception in except blocks" lint passes
                # without re-introducing a traceback render.
                logger.warning(
                    "SECURITY ERROR: SQLCipher is not installed!\n"
                    "Your databases will NOT be encrypted.\n"
                    "To fix this:\n"
                    "1. Install SQLCipher: sudo apt install sqlcipher libsqlcipher-dev\n"
                    "2. Reinstall project: pdm install\n"
                    "Or use Docker with SQLCipher pre-installed.\n\n"
                    "To explicitly allow unencrypted databases (NOT RECOMMENDED):\n"
                    "export LDR_BOOTSTRAP_ALLOW_UNENCRYPTED=true"
                )
                raise RuntimeError(
                    "SQLCipher not available. Set LDR_BOOTSTRAP_ALLOW_UNENCRYPTED=true to proceed without encryption (NOT RECOMMENDED)"
                )
            logger.warning(
                "WARNING: Running with UNENCRYPTED databases!\n"
                "This means:\n"
                "- Passwords don't protect data access\n"
                "- API keys are stored in plain text\n"
                "- Anyone with file access can read all data\n"
                "Install SQLCipher for secure operation!"
            )
            return False

    def _get_user_db_path(self, username: str) -> Path:
        """Get the path for a user's encrypted database.

        Raises:
            ValueError: Propagated from ``get_user_database_filename`` if
                ``username`` is empty/None -- that's the actual sha256
                chokepoint, so this method fails closed the same way rather
                than resolving to a phantom shared ``sha256("")`` filename.
                Registration already enforces a non-empty username; this is
                defense-in-depth at the path-resolution chokepoint.
        """
        return self.data_dir / get_user_database_filename(username)

    def _apply_pragmas(self, connection, connection_record):
        """Apply pragmas for optimal performance."""
        # Check if this is SQLCipher or regular SQLite
        is_encrypted = self.has_encryption

        # Use centralized performance pragma application

        apply_performance_pragmas(connection)

        # SQLCipher-specific pragmas
        if is_encrypted:
            from .sqlcipher_utils import get_sqlcipher_settings

            settings = get_sqlcipher_settings()
            pragmas = [
                f"PRAGMA kdf_iter = {settings['kdf_iterations']}",
                f"PRAGMA cipher_page_size = {settings['page_size']}",
            ]
            for pragma in pragmas:
                try:
                    # nosemgrep: semgrep.rules.sql-string-concatenation, reason: Both PRAGMA values come from get_sqlcipher_settings numeric range and page-size validation
                    connection.execute(pragma)
                except Exception as e:
                    logger.debug(f"Could not apply pragma '{pragma}': {e}")
        else:
            # Regular SQLite pragma
            try:
                connection.execute(
                    "PRAGMA mmap_size = 268435456"
                )  # 256MB memory mapping
            except Exception as e:
                logger.debug(f"Could not apply mmap_size pragma: {e}")

    @staticmethod
    def _make_sqlcipher_connection(
        db_path: Path,
        password: str,
        isolation_level: Optional[str] = "IMMEDIATE",
        check_same_thread: bool = False,
    ) -> Any:
        """Create a properly initialized SQLCipher connection.

        Follows the canonical SQLCipher initialization order: set key,
        apply cipher pragmas, verify, then apply performance pragmas. The
        busy timeout alone is set before verification, so a lock held
        elsewhere is waited out instead of failing the key check.
        Cipher pragmas (page size, HMAC algorithm, KDF iterations) must
        be configured before the first query (verification) because that
        query triggers page decryption with the active cipher settings.

        Args:
            db_path: Path to the database file
            password: The database encryption passphrase
            isolation_level: SQLite isolation level (``""`` for deferred
                transactions, ``None`` for autocommit)
            check_same_thread: SQLite check_same_thread flag

        Returns:
            A raw ``sqlcipher3`` connection ready for use.

        Raises:
            ValueError: If the database key cannot be verified.
        """
        sqlcipher3 = get_sqlcipher_module()
        conn = connect_sqlcipher(
            sqlcipher3,
            str(db_path),
            isolation_level=isolation_level,
            check_same_thread=check_same_thread,
        )
        cursor = conn.cursor()

        try:
            set_sqlcipher_key(cursor, password, db_path=db_path)
            apply_sqlcipher_pragmas(cursor, creation_mode=False)
            # Wait out a lock held by another connection before the
            # verification read: with only the driver's shorter default
            # wait, a busy read failed verification and a CORRECT password
            # was reported as wrong (and, during pending-rekey recovery,
            # counted toward the login lockout).
            busy_timeout_sql = sqlcipher_busy_timeout_pragma()
            cursor.execute(busy_timeout_sql)

            if not check_sqlcipher_key(cursor):
                raise ValueError("Failed to verify database key")  # noqa: TRY301 — cleanup in except before re-raise

            apply_performance_pragmas(cursor)
        except Exception as error:
            try:
                cursor.close()
            except Exception:  # noqa: BLE001
                logger.warning("Failed to close cursor during cleanup")
            from ..utilities.resource_utils import safe_close

            safe_close(conn, "encrypted DB connection")
            # A lock held past the busy timeout (or an I/O error) says
            # nothing about the password: it must not become the ValueError
            # every caller reads as a wrong password.
            if is_operational_sqlite_error(error):
                raise DatabaseKeyCheckUnavailableError(
                    db_path, type(error).__name__
                ) from None
            raise

        cursor.close()
        return conn

    def create_user_database(self, username: str, password: str) -> Engine:
        """Create a new database for a user (encrypted, or the unencrypted
        fallback when SQLCipher is unavailable).

        In the plaintext fallback, accepting a nonblank ``password`` does
        not establish a credential for later logins. Subsequent database
        opens do not verify it; see :meth:`open_user_database`.

        Serialized per user by the same init lock that guards cold opens:
        two concurrent creates for one username used to both pass the
        ``db_path.exists()`` guard, and the loser's orphaned-salt recovery
        then unlinked the winner's in-flight ``.db``/``.salt`` (#6355).
        """

        # Validate the encryption key
        if not self._is_valid_encryption_key(password):
            logger.error(
                f"Invalid encryption key for user {username}: password is None or empty"
            )
            raise ValueError(
                "Invalid encryption key: password cannot be None or empty"
            )

        # Everything up to and including the cache publish runs under the
        # lock, as in _open_user_database_cold: a concurrent first open of
        # this user then waits here instead of migrating the same file at
        # the same time as the create.
        with self._get_init_lock(username):
            db_path, engine, reserved = self._create_user_database_files(
                username, password
            )
            self._initialize_new_user_database(
                username, password, db_path, engine, reserved
            )

        logger.info(f"Created encrypted database for user {username}")
        return engine

    def _initialize_new_user_database(
        self,
        username: str,
        password: str,
        db_path: Path,
        engine: Engine,
        reserved: tuple[int, int],
    ) -> None:
        """Migrate a just-created user database and publish its engine.

        Called by ``create_user_database`` while holding this user's init
        lock. Removes the partial database when the migrations fail.

        ``reserved`` identifies the file ``_create_user_database_files``
        materialized, so the first real open below -- the unencrypted
        branch's first, since SQLAlchemy opens lazily -- is checked against it
        before the migrations write anything.
        """
        # Initialize database tables using centralized initialization
        from .initialize import initialize_database

        # Mirror of the fail-loud change #3635 made to open_user_database.
        # Previously this swallowed the exception with "tables exist but
        # schema version not stamped — migrations will be retried on next
        # process restart". That left a half-broken DB on disk: tables
        # present, no alembic_version row. The next login then re-ran
        # alembic, hit the same error, and (post-#3635) 503'd — so the
        # user could register but never log in again. Better to fail
        # registration loudly with the partial DB removed, so the real
        # cause (e.g. world-writable migrations dir) gets fixed instead
        # of producing a permanently-locked-out account.
        try:
            # SQLAlchemy opens the file lazily, so for the unencrypted branch
            # this is the first real open. It is checked before the migrations
            # write anything, and the pool hands the checked connection back
            # to the session below instead of opening the pathname again.
            with engine.connect():
                _assert_reserved_file(db_path, reserved)
            Session = sessionmaker(bind=engine)
            with Session() as session:
                initialize_database(engine, session)
            # Again afterwards, for a replacement made while they ran.
            _assert_reserved_file(db_path, reserved)
        except Exception as e:
            # ``password`` is in scope and was passed into the engine
            # creator closure above. Drop the traceback to avoid leaking
            # frame locals under ``diagnose=True`` and redact the
            # password from str(e) defensively.
            from ..security.log_sanitizer import redact_secrets

            safe_msg = redact_secrets(str(e), password)
            logger.warning(
                f"Database migration failed for {username} during creation"
                f" — removing partial DB: {safe_msg}"
            )
            engine.dispose()
            # Remove the partial DB *and* its salt/WAL/journal sidecars so a
            # retry of the same username isn't blocked by a leftover salt.
            _remove_partial_user_db_files(db_path)
            raise

        # Restrict DB file to owner-only (0o600). The encrypted branch
        # chmod's right after create_sqlcipher_connection, but the
        # unencrypted fallback creates the file lazily on first connect
        # (during initialize_database above), so it is only guaranteed to
        # exist here. This file holds PLAINTEXT user data — leaving it at
        # umask-default perms (commonly 0o644) would expose it to other
        # local accounts.
        if not self.has_encryption and db_path.exists():
            _best_effort_chmod(db_path, 0o600, warn=True)

        # Store connection AFTER migrations complete. The sidecar keeps epoch
        # initialization ordered with any cross-process cold-open or rekey.
        # Its wait is bounded (and, once a request's wait has given up, the
        # request's later claims try once without waiting until one gets the
        # sidecar again: sidecar_wait_budget): a holder past the bound fails
        # the creation below, which removes the partial database.
        try:
            with _bounded_rekey_lock(db_path):
                # A fresh epoch, never one left beside an earlier database
                # at this path (see publish_new_database_epoch).
                epoch = publish_new_database_epoch(db_path)
                entry = self._make_connection_cache_entry(
                    engine, password, epoch
                )
                replaced = self._cache_connection(username, entry)
            self._dispose_detached_connection(username, replaced)
        except Exception:
            detached = self._detach_connection(username)
            self._dispose_detached_connection(username, detached)
            if detached is not engine:
                self._dispose_detached_connection(username, engine)
            _remove_partial_user_db_files(db_path)
            raise

    def _create_user_database_files(
        self, username: str, password: str
    ) -> tuple[Path, Engine, tuple[int, int]]:
        """Create the on-disk database and its engine; caller holds the init lock.

        Returns the database path, an engine whose tables exist but whose
        migrations have not run yet, and the identity of the file this method
        materialized — see :func:`_assert_reserved_file`.
        """
        db_path = self._get_user_db_path(username)

        # ``Path.exists()`` follows symlinks and is False for a dangling one,
        # so a planted ``ldr_user_<hash>.db -> /elsewhere`` link passed this
        # guard and the create wrote the database at the link target, outside
        # the data dir (#6354). A user database is always a regular file this
        # code created, so refuse any link, dangling or not.
        if _db_path_is_symlinked(db_path, username):
            raise ValueError(
                f"Database path for user {username} is a symlink; "
                "refusing to create"
            )

        if db_path.exists():
            raise ValueError(f"Database already exists for user {username}")

        # Create connection string - use regular SQLite when SQLCipher not available
        if self.has_encryption:
            # Create directory if it doesn't exist
            from ..security.directory_creation import create_directory

            create_directory(
                db_path.parent,
                context="per-user encrypted database directory",
            )

            # Create per-database salt for new databases (v2 security improvement).
            #
            # A pre-existing salt here is orphaned from its primary DB: the
            # ``db_path.exists()`` guard above already established that no
            # database file exists, and a valid encrypted DB always has BOTH
            # the ``.db`` and its ``.salt``. Such an orphan is left by a create
            # that died before completing — a hard process death (SIGKILL/OOM/
            # power loss) mid-creation, or the pre-#4934 cleanup code that
            # removed only the ``.db``. Without recovery, ``create_database_salt``
            # raises ``FileExistsError`` (it refuses to overwrite to protect
            # live data) on every retry, so the username stays permanently
            # un-registerable. Remove the stale artifacts and create fresh;
            # no live primary database at this path depends on the salt.
            #
            # New backups retain their own matching salt. Backups created
            # before that change can still depend on this source salt, so an
            # operator recovering an older backup must preserve it if the
            # source .db was removed out of band. The app does not remove a
            # live .db while retaining its salt (there is no in-app restore).
            #
            # SAFETY INVARIANT: this deletion is safe only while a salt-without-
            # a-.db always means garbage. That holds today because create_user_
            # database is the sole salt writer/caller and nothing restores or
            # imports a .db from a salt. If an in-app restore/import that stages
            # a .salt ahead of its .db is ever added, RE-EVALUATE this branch —
            # it could delete a salt whose database is legitimately incoming.
            try:
                create_database_salt(db_path)
            except FileExistsError:
                logger.warning(
                    f"Removing orphaned salt (no matching database) for "
                    f"{username} before creating a fresh one"
                )
                _remove_partial_user_db_files(db_path)
                create_database_salt(db_path)
            logger.info(f"Created per-database salt for {username}")

            # Create database structure using raw SQLCipher outside SQLAlchemy
            try:
                # Pre-derive key before closures to avoid capturing plaintext
                # password. Kept inside the try so a derivation failure here
                # also triggers the partial-file cleanup below, rather than
                # orphaning the just-created salt file.
                hex_key = get_key_from_password(password, db_path=db_path).hex()

                # Materialize the .db ourselves, no-follow and exclusive, so
                # a link planted between the guard above and this open cannot
                # redirect SQLCipher's create either.
                reserved = _create_private_file(db_path)

                conn = create_sqlcipher_connection(
                    db_path,
                    password=password,
                    creation_mode=True,
                    connect_kwargs={
                        # DEFERRED (empty string) so pure-SELECT transactions
                        # acquire only SQLite's SHARED lock, letting WAL-mode
                        # concurrent readers proceed while a writer is active.
                        # IMMEDIATE was previously set "defensively" and made
                        # every transaction (even reads) take a RESERVED lock,
                        # which was the single biggest contention source on
                        # the login-hang path. Race-prone check-then-insert
                        # call sites were made race-free at the application
                        # layer in the preceding prerequisite PR.
                        "isolation_level": "",
                        "check_same_thread": False,
                    },
                )
                # The open above is where a swapped pathname would have
                # taken effect, so this is the first point the reservation
                # can be checked against what SQLCipher actually opened.
                # The check and the chmod run INSIDE the try below: a
                # refusal must close the raw connection the open just
                # returned, not leak it past the block that owns it (#6608).
                try:
                    _assert_reserved_file(db_path, reserved)
                    _best_effort_chmod(db_path, 0o600, warn=True)

                    # Get the CREATE TABLE statements from SQLAlchemy models
                    from sqlalchemy.dialects import sqlite
                    from sqlalchemy.schema import CreateIndex, CreateTable

                    from .models import Base

                    # Indexes must be emitted explicitly — SQLAlchemy compiles
                    # `index=True`/`unique=True` and `Index(...)` to separate
                    # CREATE [UNIQUE] INDEX statements, not inline.
                    sqlite_dialect = sqlite.dialect()
                    for table in Base.metadata.sorted_tables:
                        if table.name == "users":
                            continue
                        create_sql = str(
                            CreateTable(table, if_not_exists=True).compile(
                                dialect=sqlite_dialect
                            )
                        )
                        logger.debug(f"Creating table {table.name}")
                        conn.execute(create_sql)
                        for index in table.indexes:
                            index_sql = str(
                                CreateIndex(index, if_not_exists=True).compile(
                                    dialect=sqlite_dialect
                                )
                            )
                            conn.execute(index_sql)

                    conn.commit()
                finally:
                    from ..utilities.resource_utils import safe_close

                    safe_close(conn, "user DB setup connection")

                logger.info(
                    f"Database structure created successfully for {username}"
                )

            except Exception as e:
                # ``password`` is in lexical scope (function parameter) and
                # is passed into ``create_sqlcipher_connection`` /
                # ``set_sqlcipher_key`` above. A traceback rendered with
                # loguru ``diagnose=True`` would dump frame locals — which
                # includes the plaintext SQLCipher master password. Use
                # ``logger.warning`` to drop the traceback chain and
                # redact the password from str(e) for defense in depth.
                # SQLCipher master passwords are unrecoverable (see
                # TRUST.md §5) so the impact of a leak is permanent.
                from ..security.log_sanitizer import redact_secrets

                safe_msg = redact_secrets(str(e), password)
                logger.warning(f"Error creating database structure: {safe_msg}")
                # Remove the partial DB *and* its salt/WAL/journal sidecars so
                # a retry of the same username isn't blocked by a leftover salt.
                _remove_partial_user_db_files(db_path)
                raise

            # Small delay to ensure file is fully written
            import time

            time.sleep(0.1)

            # Now create SQLAlchemy engine using custom connection creator
            def create_engine_connection():
                """Create a properly initialized SQLCipher connection."""
                return create_sqlcipher_connection(
                    db_path,
                    hex_key=hex_key,
                    creation_mode=False,
                    connect_kwargs={
                        # DEFERRED (empty string) so pure-SELECT transactions
                        # acquire only SQLite's SHARED lock, letting WAL-mode
                        # concurrent readers proceed while a writer is active.
                        # IMMEDIATE was previously set "defensively" and made
                        # every transaction (even reads) take a RESERVED lock,
                        # which was the single biggest contention source on
                        # the login-hang path. Race-prone check-then-insert
                        # call sites were made race-free at the application
                        # layer in the preceding prerequisite PR.
                        "isolation_level": "",
                        "check_same_thread": False,
                    },
                )

            # Create engine with custom creator function and optimized cache.
            # The .db and .salt already exist on disk (structure creation
            # succeeded above). This step sits between the structure-creation
            # and migration cleanup blocks; without its own cleanup a failure
            # here would orphan those files, and unlike every other failure
            # path it would NOT be self-healing — the retry trips the
            # db_path.exists() guard and bricks the username. So clean up on
            # any escape. (create_engine is lazy so this is near-unreachable,
            # but it closes the one gap in the otherwise-uniform coverage.)
            try:
                engine = create_engine(
                    "sqlite://",
                    creator=create_engine_connection,
                    # Match the creator's DBAPI so SQLAlchemy can wrap errors.
                    module=get_sqlcipher_module().dbapi2,
                    poolclass=self._pool_class,
                    echo=False,
                    hide_parameters=True,
                    query_cache_size=1000,
                    **self._get_pool_kwargs(),
                )
            except Exception:
                _remove_partial_user_db_files(db_path)
                raise
        else:
            logger.warning(
                f"SQLCipher not available - creating UNENCRYPTED database for user {username}"
            )
            # Fall back to regular SQLite with query cache. Materialize the
            # file no-follow and exclusive first, as the encrypted branch
            # does, so SQLite's lazy create cannot follow a planted link.
            reserved = _create_private_file(db_path)
            # The file now exists, so a failure here must clean it up or the
            # retry trips the db_path.exists() guard (same reasoning as the
            # encrypted branch's engine cleanup above).
            try:
                engine = create_engine(
                    f"sqlite:///{db_path}",
                    connect_args={"check_same_thread": False, "timeout": 30},
                    poolclass=self._pool_class,
                    echo=False,
                    hide_parameters=True,
                    query_cache_size=1000,
                    **self._get_pool_kwargs(),
                )
                # For unencrypted databases, just apply pragmas. Registered
                # here, next to the create_engine it belongs to, so the
                # engine never leaves this block unconfigured and the
                # branch-level guard in test_migration_chain_integrity can
                # see the listener for this call.
                event.listen(engine, "connect", self._apply_pragmas)
            except Exception:
                _remove_partial_user_db_files(db_path)
                raise

        # Tables have already been created using raw SQLCipher above
        # No need to create them again with SQLAlchemy

        return db_path, engine, reserved

    def _compute_verifier_digest(self, salt: bytes, password: str) -> bytes:
        """Keyed digest binding ``password`` to a per-entry ``salt``.

        A fast keyed HMAC, deliberately not a slow KDF: this is an in-process
        equality check, never persisted, and the plaintext password already
        lives in memory in session_password_store, so a KDF would add cost
        without reducing exposure.

        NOTE: CodeQL py/weak-sensitive-data-hashing flags the hash below as
        weak password hashing. It is a reviewed FALSE POSITIVE: this app stores
        no password hashes at rest (SQLCipher's PBKDF2 is the real KDF on the
        cold-open path); this keyed digest only proves, in memory, that the SAME
        password opened the cached engine, so a slow KDF here would add login
        latency without reducing exposure. The py/weak-sensitive-data-hashing
        alert on ``_compute_verifier_digest`` (previously #8140; it may reopen
        under a new number after merge) must be dismissed per-alert in code
        scanning as a false positive (an HMAC verifier, not password storage)
        once main is re-scanned, rather than excluded repo-wide (see
        .github/codeql/codeql-config.yml and #5621).
        """
        return hmac.new(
            self._verifier_key, salt + password.encode("utf-8"), "sha256"
        ).digest()

    def _make_verifier(self, password: str) -> tuple[bytes, bytes]:
        """Build a ``(salt, digest)`` verifier for ``password``. Takes no lock."""
        salt = secrets.token_bytes(16)
        return salt, self._compute_verifier_digest(salt, password)

    def _make_connection_cache_entry(
        self, engine: Engine, password: str, epoch: str
    ) -> _ConnectionCacheEntry:
        return _ConnectionCacheEntry(
            engine=engine,
            verifier=self._make_verifier(password),
            epoch=epoch,
        )

    def _cache_connection(
        self, username: str, entry: _ConnectionCacheEntry
    ) -> Engine | None:
        """Publish an engine, password verifier, and durable epoch atomically.

        Called only on paths that have ALREADY proved the password by opening
        (or creating) the SQLCipher database with it. Storing the engine and
        its verifier under a single lock keeps the invariant "a cached
        connection always has a matching verifier" true by construction, so a
        concurrent open can never observe a connection with a missing or stale
        verifier.
        """
        with self._connections_lock:
            replaced = self.connections.get(username)
            self.connections[username] = entry.engine
            self._password_verifiers[username] = entry.verifier
            self._connection_epochs[username] = entry.epoch
        return replaced if replaced is not entry.engine else None

    def _record_password_verifier(self, username: str, password: str) -> None:
        """Arm the verifier for a connection cached by other means.

        Prefer :meth:`_cache_connection` when publishing the engine too — it
        stores both atomically. This standalone form arms a verifier against an
        already-cached engine. Caller must not hold ``_connections_lock``.
        """
        verifier = self._make_verifier(password)
        epoch = read_rekey_epoch(self._get_user_db_path(username))
        with self._connections_lock:
            self._password_verifiers[username] = verifier
            if epoch is None:
                self._connection_epochs.pop(username, None)
            else:
                self._connection_epochs[username] = epoch

    def _connection_epoch_matches(
        self, username: str, current_epoch: str | None
    ) -> bool:
        """Compare a disk epoch while the caller holds ``_connections_lock``."""
        return (
            current_epoch is not None
            and self._connection_epochs.get(username) == current_epoch
        )

    def _detach_connection_locked(self, username: str) -> Engine | None:
        """Detach all cache state while the caller holds ``_connections_lock``."""
        engine = self.connections.pop(username, None)
        self._password_verifiers.pop(username, None)
        self._connection_epochs.pop(username, None)
        return engine

    def _detach_connection(self, username: str) -> Engine | None:
        with self._connections_lock:
            return self._detach_connection_locked(username)

    def _snapshot_connection_generation(
        self, username: str
    ) -> _ConnectionGeneration | None:
        with self._connections_lock:
            engine = self.connections.get(username)
            if engine is None:
                return None
            return _ConnectionGeneration(
                username,
                engine,
                self._password_verifiers.get(username),
                self._connection_epochs.get(username),
            )

    def _connection_generation_matches_locked(
        self, generation: _ConnectionGeneration
    ) -> bool:
        return (
            self.connections.get(generation.username) is generation.engine
            and self._password_verifiers.get(generation.username)
            is generation.verifier
            and self._connection_epochs.get(generation.username)
            == generation.epoch
        )

    def _detach_connection_generation(
        self, generation: _ConnectionGeneration
    ) -> bool:
        with self._connections_lock:
            if not self._connection_generation_matches_locked(generation):
                return False
            self._detach_connection_locked(generation.username)
            return True

    def _claim_connection_generation(
        self,
        generation: _ConnectionGeneration,
        state: RekeyEpochState,
    ) -> _ConnectionGenerationClaim:
        with self._connections_lock:
            if self.connections.get(generation.username) is None:
                return _ConnectionGenerationMissing()
            if not self._connection_generation_matches_locked(generation):
                return _ConnectionGenerationChanged()
            if (
                state.pending_epoch is not None
                or state.committed_epoch is None
                or generation.epoch != state.committed_epoch
            ):
                self._detach_connection_locked(generation.username)
                return _StaleConnectionGenerationDetached(generation)
            return _CurrentConnectionGeneration(generation)

    @contextmanager
    def _acquire_current_connection_generation(
        self, username: str, *, raise_unverifiable: bool = False
    ) -> Generator[_ConnectionGeneration | None, None, None]:
        """Yield the epoch-validated cached generation, or None.

        The body of the ``with`` runs while this thread holds the user's
        lifecycle lock, the in-process sidecar gate and the sidecar flock.
        Every other probe for the same user waits on them, including the
        single shared log-drain thread (``is_user_connected``), which
        persists log entries for ALL users, and the idle-connection sweep
        skips the user meanwhile. Keep the body to cache bookkeeping
        (snapshot the engine, build a sessionmaker, compare a verifier) and
        do database work after the context exits;
        ``check_database_integrity`` runs its scans that way.

        Other holders of the same sidecar are long by nature: a cold open
        (``_open_user_database_cold``, including the pre-migration backup
        and alembic migrations) and the rekey inside ``change_password``,
        in this process or another one, and a stopped process or a hung
        mount holds it indefinitely. So a claim waits for its locks at most
        ``_SIDECAR_WAIT_TIMEOUT_SECONDS`` in total, retries after a changed
        generation included, and not at all when an earlier wait of the
        same request ran out and none has got the sidecar since
        (``sidecar_wait_budget``). The lifecycle lock counts against the
        same budget, because the claim ahead of this one holds it while that
        claim waits for the sidecar. Past the bound the sidecar is one that
        could not be locked (``RekeyLockContentionError``).

        A cached generation whose sidecar cannot be opened or locked, or
        whose epoch read fails, is kept and yields None. With
        ``raise_unverifiable`` the typed fault (``RekeySidecarIOError``,
        ``RekeyLockContentionError`` or ``RekeyEpochUnreadableError``) is
        raised instead, after every lock is released, for the callers that
        must not treat it as a miss: ``open_user_database``,
        ``probe_user_connection`` and ``get_session``.

        Releasing the sidecar afterwards can fail too (unlock or close).
        When the body of the ``with`` finished cleanly, that raises
        ``RekeySidecarIOError`` for every caller, also when the probe runs
        inside a caller's ``except`` block. When the body raised, its own
        exception propagates and the release failure is added to it as a
        note instead of replacing it.
        """
        if not username:
            # Fail closed BEFORE touching the filesystem: an empty or None
            # username must not even resolve a database path
            # (test_db_connection_username_trust).
            yield None
            return
        db_path = self._get_user_db_path(username)
        deadline = time.monotonic() + _sidecar_wait_seconds()
        while True:
            generation = self._snapshot_connection_generation(username)
            if generation is None:
                yield None
                return

            lifecycle_lock = self._get_rekey_lock(username)
            if not lifecycle_lock.acquire(timeout=_seconds_until(deadline)):
                # Still held at the deadline, by the claim ahead of this one
                # while it waits for the sidecar, or by a disposal under it:
                # the sidecar could not be locked in time, the same
                # unverifiable outcome as the sidecar fault below.
                _record_sidecar_wait(ran_out=True)
                fault = RekeyLockContentionError(
                    db_path.with_name(f"{db_path.name}.rekey.lock")
                )
                if raise_unverifiable:
                    raise fault from None
                yield None
                return
            try:
                sidecar_lock = acquire_rekey_lock(
                    db_path, timeout=_seconds_until(deadline)
                )
                try:
                    sidecar_lock.__enter__()
                except (
                    OSError,
                    RekeyLockContentionError,
                    RekeySidecarIOError,
                ) as error:
                    # The sidecar cannot be opened or locked (fd exhaustion,
                    # EROFS/EACCES/ENOSPC, a vanished data directory, or a
                    # refused planted sidecar -- the lock module normalizes
                    # all of these to RekeySidecarIOError), or another
                    # holder kept it past the deadline above
                    # (RekeyLockContentionError). Nothing can be
                    # proved about the durable epoch, so THIS request fails
                    # closed. The callers that treat None as "no usable
                    # connection" (is_user_connected, which the log sink
                    # gates on, _password_matches_cached, the metrics
                    # session's cache hit and the integrity check) see None
                    # and never have to catch the typed error. The three
                    # that must not read it as a miss get it raised below
                    # instead: open_user_database, whose cold path needs
                    # this same sidecar (so it fails before deriving a
                    # key); probe_user_connection, which reports it as
                    # UNAVAILABLE so request authentication answers a
                    # retryable 503 rather than a 401; and get_session,
                    # whose None get_settings_manager reads as "this user
                    # has no database" and answers from the defaults file,
                    # so a stored strict egress scope would read as the
                    # default. (The metrics session falls through to
                    # open_user_database on that None, so it raises the
                    # typed error too.)
                    #
                    # The cached engine is deliberately left in place, not
                    # evicted: a transient fault (an EMFILE spike) would
                    # otherwise dispose every user's healthy engine at once.
                    # Leaving it is safe because the cache never hands an
                    # engine out without a successful claim: the next probe
                    # re-takes the sidecar and re-checks the epoch, and a
                    # rekey committed meanwhile detaches it there.
                    if isinstance(error, RekeyLockContentionError):
                        _record_sidecar_wait(ran_out=True)
                    claim = _ConnectionGenerationUnverifiable(
                        error
                        if isinstance(error, RekeyOperationalError)
                        else RekeySidecarIOError(
                            db_path, RekeySidecarOperation.ACQUIRE, error.errno
                        )
                    )
                else:
                    _record_sidecar_wait(ran_out=False)
                    # Whatever the body raises (the caller's with-block,
                    # thrown in at the yield, or the disposal) is handed to
                    # the lock as a with statement would hand it, so a
                    # failure to release the sidecar is added to it as a
                    # note instead of replacing it. Recorded here, not left
                    # for the lock to infer: after a clean body it would
                    # see whatever exception a caller is handling (a probe
                    # made inside an except block) and attach the release
                    # failure to that instead of raising it.
                    body_failure: BaseException | None = None
                    try:
                        try:
                            state = read_rekey_epoch_state(db_path)
                        except (OSError, RekeyEpochUnreadableError) as error:
                            # The sidecar opened but the epoch read itself
                            # failed (EMFILE right after the sidecar took
                            # the last fd, EIO, ...): nothing was read, so
                            # treat it like the sidecar fault above. THIS
                            # request sees "not connected" (or the typed
                            # error, for the three callers above); the cached
                            # engine is kept and the next successful probe
                            # re-checks the epoch before any hand-out.
                            claim = _ConnectionGenerationUnverifiable(
                                error
                                if isinstance(error, RekeyEpochUnreadableError)
                                else RekeyEpochUnreadableError(
                                    db_path, error.errno
                                )
                            )
                        except RekeyRecoveryRequiredError:
                            # Read, and does not vouch (malformed token,
                            # pending without committed): detach below.
                            claim = self._claim_connection_generation(
                                generation, RekeyEpochState(None, None)
                            )
                        else:
                            claim = self._claim_connection_generation(
                                generation, state
                            )
                        match claim:
                            case _CurrentConnectionGeneration(
                                generation=current
                            ):
                                yield current
                                return
                            case _StaleConnectionGenerationDetached(
                                generation=stale
                            ):
                                self._dispose_detached_connection(
                                    stale.username,
                                    stale.engine,
                                    checkpoint=False,
                                )
                            case (
                                _ConnectionGenerationChanged()
                                | _ConnectionGenerationMissing()
                                | _ConnectionGenerationUnverifiable()
                            ):
                                pass
                            case unreachable:
                                assert_never(unreachable)
                    except BaseException as failure:
                        body_failure = failure
                        raise
                    finally:
                        if body_failure is None:
                            sidecar_lock.__exit__(None, None, None)
                        else:
                            sidecar_lock.__exit__(
                                type(body_failure),
                                body_failure,
                                body_failure.__traceback__,
                            )
            finally:
                lifecycle_lock.release()

            match claim:
                case _ConnectionGenerationChanged():
                    continue
                case _ConnectionGenerationUnverifiable(error=fault):
                    if raise_unverifiable:
                        raise fault from None
                    yield None
                    return
                case (
                    _StaleConnectionGenerationDetached()
                    | _ConnectionGenerationMissing()
                ):
                    yield None
                    return
                case _CurrentConnectionGeneration():
                    raise AssertionError(
                        "current generation escaped its ownership scope"
                    )
                case unreachable:
                    assert_never(unreachable)

    def _verifier_entry_matches(
        self, username: str, entry: object, password: str
    ) -> bool:
        """Compare ``password`` against one ``(salt, digest)`` verifier entry.

        Fails CLOSED: a missing entry returns False so the caller falls
        through to a real key-validating open. Returning True on a missing
        verifier would reopen exactly the hole the verifier closes.

        A malformed entry (anything that is not a ``(salt, expected)`` tuple
        of bytes, including a two-tuple with wrong-typed members such as
        ``("x", "y")`` or ``(b"salt", 123)``) also returns False rather than
        propagating. Every caller sits on a cache-hit or cold-open path that
        holds the per-user lifecycle locks: a ``TypeError`` / ``ValueError``
        escaping from here would surface as an unexplained login or session
        failure instead of the ordinary fall-through to a key-validating
        open. The invariants that produce well-formed entries
        (``_make_verifier`` via ``_make_connection_cache_entry``, and
        ``_record_password_verifier``) are unit-locked, so a corrupt entry
        can only arrive through external dict mutation; degrading to
        no-match is safer than letting that exception escape.
        """
        if entry is None:
            return False
        try:
            salt, expected = entry  # type: ignore[misc]
            if not isinstance(salt, bytes) or not isinstance(expected, bytes):
                logger.warning(
                    f"Malformed verifier entry for {username}; treating as no-match"
                )
                return False
            return hmac.compare_digest(
                self._compute_verifier_digest(salt, password), expected
            )
        except (TypeError, ValueError, AttributeError):
            logger.warning(
                f"Malformed verifier entry for {username}; treating as no-match"
            )
            return False

    def _verifier_matches(self, username: str, password: str) -> bool:
        """Lock-free verifier comparison. **Caller must hold _connections_lock.**

        Fails CLOSED on a missing or malformed verifier; see
        :meth:`_verifier_entry_matches`.
        """
        return self._verifier_entry_matches(
            username, self._password_verifiers.get(username), password
        )

    def _connection_generation_password_matches(
        self, generation: _ConnectionGeneration, password: str
    ) -> bool:
        """Check ``password`` against a claimed generation's verifier.

        Fails CLOSED on a missing or malformed verifier, exactly like
        :meth:`_verifier_matches`; see :meth:`_verifier_entry_matches`.
        """
        if not self.has_encryption:
            return True
        return self._verifier_entry_matches(
            generation.username, generation.verifier, password
        )

    def _cached_engine_trusted(
        self, username: str, password: str, current_epoch: str | None
    ) -> bool:
        """May the cached engine for ``username`` be returned to a caller
        presenting ``password``? **Caller must hold _connections_lock.**

        Unencrypted mode has no credential to verify — the cold path opens the
        plain-SQLite database with any password too — so the verifier is
        meaningless there and would only force needless cold opens against the
        placeholder passwords the app uses (session_context /
        database_middleware). Trust the cache directly. Encrypted mode requires
        a verifier match.
        """
        if not self._connection_epoch_matches(username, current_epoch):
            return False
        if not self.has_encryption:
            return True
        return self._verifier_matches(username, password)

    def _password_matches_cached(self, username: str, password: str) -> bool:
        """Check ``password`` against the exact current cache generation."""
        with self._acquire_current_connection_generation(
            username
        ) as generation:
            return generation is not None and (
                self._connection_generation_password_matches(
                    generation, password
                )
            )

    def _get_init_lock(self, username: str) -> threading.Lock:
        """Return the per-user cold-open lock, creating it on first use.

        Guards the engine-build + migration in ``open_user_database`` so two
        concurrent first-opens of the same user serialize instead of running
        migrations against one database file simultaneously. Per-user (not the
        global ``_connections_lock``) so different users still open in
        parallel.
        """
        with self._connections_lock:
            lock = self._init_locks.get(username)
            if lock is None:
                lock = threading.Lock()
                self._init_locks[username] = lock
            return lock

    def _get_password_change_lock(self, username: str) -> threading.Lock:
        """Return the per-user change_password gate, creating it on first use.

        Serializes concurrent change_password CALLS for one user (the loser
        gets the typed 409 contention error). Must stay a separate registry
        from _rekey_locks: the lifecycle lock is taken briefly by ordinary
        opens/claims, so gating on it here would turn every racing open
        into a false contention.

        The backing dict is reached through ``getattr`` because several
        tests build a ``DatabaseManager`` via ``__new__`` and set only the
        attributes the method under test touches, so it can legitimately
        be absent.
        """
        with self._connections_lock:
            locks = getattr(self, "_password_change_locks", None)
            if locks is None:
                locks = {}
                self._password_change_locks = locks
            lock = locks.get(username)
            if lock is None:
                lock = threading.Lock()
                locks[username] = lock
            return lock

    def _get_rekey_lock(self, username: str) -> threading.Lock:
        """Return the dedicated per-user password-rekey lifecycle lock.

        Serializes the whole rekey sequence for one user in-process; the
        on-disk sidecar lock (``acquire_rekey_lock``) extends the same
        exclusion across processes. Deliberately a separate registry from
        ``_init_locks``: change-password-era code paths acquire both, and
        sharing one non-reentrant lock would self-deadlock.

        The backing dict is reached through ``getattr`` because several
        tests build a ``DatabaseManager`` via ``__new__`` and set only the
        attributes the method under test touches, so it can legitimately
        be absent.
        """
        with self._connections_lock:
            locks = getattr(self, "_rekey_locks", None)
            if locks is None:
                locks = {}
                self._rekey_locks = locks
            lock = locks.get(username)
            if lock is None:
                lock = threading.Lock()
                locks[username] = lock
            return lock

    def open_user_database(
        self, username: str, password: str
    ) -> Optional[Engine]:
        """Open a user's database, verifying the password only with SQLCipher.

        With encryption enabled, cold opens validate the derived key and
        cached opens require a matching password verifier. The plaintext
        development fallback does not verify account passwords: its returned
        engine and epoch are not evidence of authentication. Shared plaintext
        accounts require independent authentication.

        When an engine is cached but its rekey sidecar cannot be opened or
        locked, or its epoch cannot be read, the typed fault
        (``RekeySidecarIOError`` / ``RekeyLockContentionError`` /
        ``RekeyEpochUnreadableError``, all ``RekeyOperationalError``) is
        raised before any key is derived, and the engine stays cached. A
        cold open whose per-user init lock stays held past
        ``_SIDECAR_WAIT_TIMEOUT_SECONDS`` raises ``RekeyLockContentionError``
        before the derivation, and one whose sidecar stays held past it
        raises it after the derivation (without waiting, in a request whose earlier
        wait ran out: ``sidecar_wait_budget``). None stays reserved for a
        credential or missing-database verdict.

        Inside :func:`capture_login_proof` the epoch the password was
        proved against is recorded for the caller (the login route binds
        the new session to it).
        """
        engine, proven_epoch, cold_opened = self._open_user_database_proving(
            username, password
        )
        # Phase 2 of the RAG vector-store cutover runs OUTSIDE init_lock (only
        # on the path that actually did the cold-open — the early returns
        # belong to a thread that already ran it). It re-enters
        # open_user_database() via get_user_db_session on a session-cache miss;
        # doing it while still holding the non-reentrant init_lock would
        # self-deadlock if a concurrent close_user_database() evicted the
        # freshly-cached connection between the cache write and that re-entry.
        if cold_opened:
            self._run_phase2_rekey(username, password)
        # Recorded AFTER phase 2 so this (outer) call's proof wins over any
        # proof the phase-2 re-entry recorded on this thread.
        _record_login_proof(
            username, proven_epoch if engine is not None else None
        )
        return engine

    def _open_user_database_proving(
        self, username: str, password: str
    ) -> tuple[Optional[Engine], Optional[str], bool]:
        """Open (or claim) the user's engine and report what was proved.

        Returns ``(engine, proven_epoch, cold_opened)``. ``proven_epoch`` is
        the committed durable epoch the password was verified against while
        the rekey sidecar lock was held; ``cold_opened`` is True only when
        this call built and cached a new engine (phase 2 must then run).
        """
        open_start = time.perf_counter()

        # Validate the encryption key
        if not self._is_valid_encryption_key(password):
            logger.error(
                f"Invalid encryption key when opening database for user {username}: password is None or empty"
            )
            # TODO: Fix the root cause - research threads are not getting the correct password
            logger.error(
                "TODO: This usually means the research thread is not receiving the user's "
                "password for database encryption. Need to ensure password is passed from "
                "the main thread to research threads."
            )
            raise ValueError(
                "Invalid encryption key: password cannot be None or empty"
            )

        db_path = self._get_user_db_path(username)

        # Check if already open.
        #
        # `connections` is keyed by username ALONE, so a hit proves only that
        # SOMEONE opened this user's database -- not that the caller supplied
        # the right password. Returning the engine on a bare hit made login
        # accept ANY password for any user with a live cached connection,
        # which is the normal state whenever they are logged in anywhere.
        # Verify the credential against the one that actually opened it, and
        # fall through to a real (key-validating) open when it does not match.
        # Claim the engine and check its verifier in one ownership scope so it
        # cannot be evicted between validation and return.
        #
        # raise_unverifiable: when an engine is cached but the rekey sidecar
        # cannot be opened or locked, or the epoch cannot be read, raise that
        # typed fault here, BEFORE the key derivation below. The cold path
        # needs the same sidecar, so it would only fail the same way after a
        # full PBKDF2 derivation, and every caller (each request's
        # DatabaseMiddleware, socket handshakes, background sessions) would
        # pay one per attempt for as long as an fd-exhaustion spike lasts.
        # The engine stays cached for the next successful probe. Only an
        # existing user can have a cached engine, and the login route
        # answers this fault with a 503 that an unknown username never
        # gets, so skipping the derivation reveals nothing the status code
        # does not already reveal.
        with self._acquire_current_connection_generation(
            username, raise_unverifiable=True
        ) as generation:
            if generation is not None and (
                self._connection_generation_password_matches(
                    generation, password
                )
            ):
                return generation.engine, generation.epoch, False

        # Serialize the cold-open (engine build + migration) per user so two
        # concurrent first-opens never run alembic against the same database
        # file at once -- alembic's module-level proxy and the version-row
        # UPDATE are not safe under concurrent migration of one DB.
        init_lock = self._get_init_lock(username)
        recovered_pending_rekey = False
        with self._bounded_init_lock(init_lock, db_path):
            # Derive the key UNCONDITIONALLY (an absent database must cost
            # the same as an existing one -- username-enumeration timing
            # parity, see _open_user_database_cold), then refuse symlinked
            # and absent databases BEFORE any sidecar file is created: an
            # unauthenticated caller probing usernames must not be able to
            # materialize on-disk state for a user that does not exist.
            prederived_hex_key = get_key_from_password(
                password, db_path=db_path
            ).hex()
            if _db_path_is_symlinked(db_path, username):
                return None, None, False
            if not db_path.exists():
                logger.error(f"No database found for user {username}")
                return None, None, False

            # The stable sidecar serializes cold opens with cross-process rekeys.
            # Epoch repair and publication happen only while this inode is locked.
            # The wait is bounded (once a request's wait has given up, its
            # later claims try once without waiting until one gets the
            # sidecar again: sidecar_wait_budget): a holder that keeps it
            # longer (a rekey of a large database, a stopped process) makes
            # this open raise RekeyLockContentionError, which a login
            # answers with 503 (not counted toward lockout), a signed-in
            # request's reopen (ensure_user_database) with 503 and the
            # session kept, and the other callers as any sidecar fault.
            with _bounded_rekey_lock(db_path):
                # Re-checked under the lock (cheap now): the pre-gate above
                # ran without any exclusion.
                if _db_path_is_symlinked(db_path, username):
                    return None, None, False
                if not db_path.exists():
                    logger.error(f"No database found for user {username}")
                    return None, None, False
                cleanup_stale_rekey_temps(db_path)
                rekey_state = read_rekey_epoch_state(db_path)
                if rekey_state.pending_epoch is not None:
                    pending_engine = self._detach_connection(username)
                    if pending_engine is not None and not (
                        self._dispose_detached_connection(
                            username, pending_engine, checkpoint=False
                        )
                    ):
                        raise RekeyRecoveryRequiredError(db_path)
                    if not self._recover_pending_rekey(
                        db_path, password, rekey_state
                    ):
                        return None, None, False
                    recovered_pending_rekey = True
                try:
                    current_epoch = read_or_initialize_rekey_epoch(db_path)
                except OSError as error:
                    # First open after upgrade publishes the epoch file;
                    # an I/O failure there (ENOSPC, EIO, mkstemp EACCES)
                    # is operational, not a credential verdict -- type it
                    # so the login route answers 503 without counting it
                    # toward lockout instead of escaping as a raw 500.
                    raise RekeySidecarIOError(
                        db_path, RekeySidecarOperation.PUBLISH, error.errno
                    ) from None
                detached = None
                with self._connections_lock:
                    cached = self.connections.get(username)
                    if cached is not None and self._cached_engine_trusted(
                        username, password, current_epoch
                    ):
                        return cached, current_epoch, False
                    if (
                        cached is not None
                        and not self._connection_epoch_matches(
                            username, current_epoch
                        )
                    ):
                        detached = self._detach_connection_locked(username)
                self._dispose_detached_connection(
                    username, detached, checkpoint=False
                )

                engine = self._open_user_database_cold(
                    username, password, open_start, prederived_hex_key
                )
                if engine is None:
                    return None, None, False

                # Re-read the durable state the password was just proved
                # under. No outcome other than the same stable committed
                # epoch publishes the engine, and none of the others says
                # anything against the credential: a read that fails
                # (EMFILE, EIO, persistent EACCES:
                # RekeyEpochUnreadableError), durable state that does not
                # vouch (a malformed token, a missing committed epoch, or
                # a pending epoch), and a different committed epoch (none
                # of the last four can appear while this open holds the
                # sidecar unless a writer bypassed it). All of them
                # propagate as the RekeyOperationalError the reads above
                # already raise on this path, so the login route answers
                # 503 and leaves the lockout counter alone, instead of
                # returning the ``(None, None, False)`` that a wrong
                # password returns (401, counted toward lockout).
                try:
                    rechecked_state = read_rekey_epoch_state(db_path)
                except RekeyRecoveryRequiredError:
                    self._dispose_detached_connection(
                        username, engine, checkpoint=False
                    )
                    raise
                if (
                    rechecked_state.pending_epoch is not None
                    or rechecked_state.committed_epoch is None
                ):
                    self._dispose_detached_connection(
                        username, engine, checkpoint=False
                    )
                    raise RekeyRecoveryRequiredError(db_path)
                if rechecked_state.committed_epoch != current_epoch:
                    # A different committed epoch was read: the engine was
                    # built under a superseded generation; discard it
                    # unpublished. Every epoch writer runs under the
                    # sidecar this open holds, so only a writer that
                    # bypassed it (an operator editing the epoch file, a
                    # foreign tool) gets here. The password DID open the
                    # database, so this is not a wrong password either:
                    # fail closed with the same RekeyRecoveryRequiredError
                    # (login answers 503, lockout counter untouched)
                    # rather than the (None, None, False) that reads as
                    # invalid credentials (401, counted toward lockout).
                    self._dispose_detached_connection(
                        username, engine, checkpoint=False
                    )
                    raise RekeyRecoveryRequiredError(db_path)

                entry = self._make_connection_cache_entry(
                    engine, password, current_epoch
                )
                replaced = self._cache_connection(username, entry)
                self._dispose_detached_connection(username, replaced)

        if engine is not None and recovered_pending_rekey:
            self._purge_superseded_key_backups(
                username, password, expected_epoch=current_epoch
            )
        return engine, current_epoch, True

    def _purge_superseded_key_backups(
        self, username: str, password: str, *, expected_epoch: str
    ) -> None:
        """Delete backups left under the old key by an interrupted rekey.

        Runs after an open resolved a pending password change with
        ``password``. A change that completed normally purges old-key
        backups in the change-password route; one whose outcome was
        reported as indeterminate never reached that step, so this is
        where the old-key backups go once the active key is proven.

        The cold open released its locks before reaching here. Reacquire
        the rekey sidecar and require the same stable epoch before deleting
        anything: a later password change may already have created backups
        under its newer key, which this password would mistake for a
        superseded one. Keep the sidecar through classification and deletion,
        acquiring the backup service's per-user lock inside it (the same
        order as pre-migration backups). A fault only skips this best-effort
        cleanup; it never fails the open.
        """
        try:
            from .backup.backup_service import BackupService

            db_path = self._get_user_db_path(username)
            with _bounded_rekey_lock(db_path):
                state = read_rekey_epoch_state(db_path)
                if state != RekeyEpochState(expected_epoch, None):
                    return
                # record_failure=False: this purge records no status, and
                # the default failure record would go through the open
                # engine, whose claim waits on the sidecar held here.
                backup_service = BackupService(
                    username=username, password=password, record_failure=False
                )
                # A pinned manager data_dir can differ from the backup
                # service's configured directory. The fence must vouch for
                # the database whose backups would actually be deleted.
                if backup_service.db_path != db_path:
                    return
                purged = backup_service.purge_backups_under_superseded_key()
            if purged:
                logger.info(
                    f"Purged {purged} backup(s) under the superseded key "
                    f"for {username} after pending rekey recovery"
                )
        except Exception as error:
            logger.warning(
                f"Backup purge after pending rekey recovery failed for "
                f"{username}: {type(error).__name__}"
            )

    @staticmethod
    @contextmanager
    def _bounded_init_lock(
        init_lock: threading.Lock, db_path: Path
    ) -> Generator[None, None, None]:
        """Hold a user's init lock, waiting no longer than a sidecar wait.

        The init lock's holder derives the key and then waits up to the
        sidecar bound itself, so while another holder (a rekey of a large
        database, a stopped process) keeps the sidecar, an unbounded wait
        here made N concurrent opens fail one after another, each about one
        derivation plus one bound later than the previous, while occupying
        the shared worker threads that serve every user's requests. Waiting
        at most the same bound (and recording a give-up for the request's
        later waits, like a sidecar wait) makes them all fail within about
        one bound instead. This is only a queue bound: the sidecar acquire
        inside stays the authoritative exclusion against a rekey. The same
        bound applies when the holder is a sibling cold open of this user
        that runs longer (a first-open migration of a large database): the
        waiting open answers with the retryable contention error instead of
        parking its thread until the migration ends.
        """
        if not init_lock.acquire(timeout=_sidecar_wait_seconds()):
            _record_sidecar_wait(ran_out=True)
            raise RekeyLockContentionError(
                db_path.with_name(f"{db_path.name}.rekey.lock")
            )
        try:
            yield
        finally:
            init_lock.release()

    def _run_phase2_rekey(self, username: str, password: str) -> None:
        """Re-key a user's legacy FAISS indices (uuid-keyed -> DocumentChunk.id
        -keyed), once, at login. See vector_stores/legacy_rekey.py.

        A fast no-op (one get_setting call) once the per-user marker
        (research_library.rag_index_rekeyed_v2) is set, or when the user has no
        legacy sidecars — the common case. Wrapped so a re-key problem never
        blocks login; it logs its own errors. Must be called AFTER the init_lock
        is released (see open_user_database) to avoid a re-entrant self-deadlock.
        """
        try:
            from ..vector_stores.legacy_rekey import rekey_user_indexes

            rekey_user_indexes(username, password)
        except Exception as e:
            # NO rendered traceback + redacted message — the password-redaction
            # invariant (tests/security/test_password_redaction_invariant.py).
            # `password` (the unrecoverable SQLCipher master key) is live in this
            # frame and every frame of the rekey chain, so ANY traceback render
            # leaks it under LDR_LOGURU_DIAGNOSE. logger.opt(exception=True) does
            # NOT suppress that (it renders identically to logger.exception) and
            # also evades the AST guardrail, so log a redacted message with no
            # stack, matching every other handler in this file.
            from ..security.log_sanitizer import redact_secrets

            safe_msg = redact_secrets(str(e), password)
            logger.warning(
                f"RAG index re-key failed for user {username}: {safe_msg}"
            )

    def _open_user_database_cold(
        self,
        username: str,
        password: str,
        open_start: float,
        prederived_hex_key: str | None = None,
    ) -> Optional[Engine]:
        """Build the engine and run migrations for a not-yet-cached user DB.

        Must be called while holding this user's init lock (acquired in
        ``open_user_database``) so concurrent first-opens of the same user do
        not migrate one database file simultaneously.

        ``prederived_hex_key`` lets the caller (which derives the key
        unconditionally for username-enumeration timing parity before its
        cheap pre-gates) avoid paying the derivation twice.
        """
        db_path = self._get_user_db_path(username)

        # Prevent timing attacks: always derive key before checking file existence
        # This ensures both existing and non-existent users take the same amount of time,
        # preventing username enumeration via timing analysis.
        # Pre-derive key before closures to avoid capturing plaintext password
        hex_key = (
            prederived_hex_key
            if prederived_hex_key is not None
            else get_key_from_password(password, db_path=db_path).hex()
        )

        # After the key derivation, not before: the derivation is deliberately
        # unconditional so a non-existent user costs the same as an existing
        # one, and a check that returned early would time the difference.
        # Before the exists() check below, which follows the link and would
        # report a tampered pathname as a missing account.
        if _db_path_is_symlinked(db_path, username):
            return None

        if not db_path.exists():
            logger.error(f"No database found for user {username}")
            return None

        # Warn if this is a legacy database without per-database salt
        if self.has_encryption and not has_per_database_salt(db_path):
            logger.warning(
                f"Database for user '{username}' uses the legacy shared salt "
                f"(deprecated). For improved security, consider creating a new "
                f"account to get a per-database salt. Legacy databases remain "
                f"fully functional but are less resistant to multi-target attacks."
            )

        # Create connection string - use regular SQLite when SQLCipher not available
        if self.has_encryption:

            def create_open_connection():
                """Create a properly initialized SQLCipher connection."""
                return create_sqlcipher_connection(
                    db_path,
                    hex_key=hex_key,
                    creation_mode=False,
                    connect_kwargs={
                        # DEFERRED (empty string) so pure-SELECT transactions
                        # acquire only SQLite's SHARED lock, letting WAL-mode
                        # concurrent readers proceed while a writer is active.
                        # IMMEDIATE was previously set "defensively" and made
                        # every transaction (even reads) take a RESERVED lock,
                        # which was the single biggest contention source on
                        # the login-hang path. Race-prone check-then-insert
                        # call sites were made race-free at the application
                        # layer in the preceding prerequisite PR.
                        "isolation_level": "",
                        "check_same_thread": False,
                    },
                )

            # Create engine with custom creator function and optimized cache
            engine = create_engine(
                "sqlite://",
                creator=create_open_connection,
                module=get_sqlcipher_module().dbapi2,
                poolclass=self._pool_class,
                echo=False,
                hide_parameters=True,
                query_cache_size=1000,
                **self._get_pool_kwargs(),
            )
        else:
            logger.warning(
                f"SQLCipher not available - opening UNENCRYPTED database for user {username}"
            )
            # SQLite does not use hex_key. Deriving it above is not password
            # verification: this fallback opens the database independently
            # of the supplied nonblank password.
            engine = create_engine(
                f"sqlite:///{db_path}",
                connect_args={"check_same_thread": False, "timeout": 30},
                poolclass=self._pool_class,
                echo=False,
                hide_parameters=True,
                query_cache_size=1000,
                **self._get_pool_kwargs(),
            )

            # For unencrypted databases, just apply pragmas
            event.listen(engine, "connect", self._apply_pragmas)

        try:
            # Test connection by running a simple query
            with engine.connect() as conn:
                conn.execute(text("SELECT 1"))

            # Run database initialization (creates missing tables and runs migrations)
            from .initialize import initialize_database

            # Create backup before migration to protect against schema change failures
            from .alembic_runner import needs_migration

            if needs_migration(engine):
                try:
                    from .backup.backup_service import BackupService

                    # Apply the user's own retention settings, the same
                    # ones the login backup uses. The library defaults
                    # (keep 1, max 7 days) would delete backups the user
                    # chose to keep. If the settings cannot be read from
                    # the not-yet-migrated schema, or are not positive
                    # integers within the settings' maximums, delete
                    # nothing.
                    retention = _read_backup_retention(engine)
                    # record_failure=False: this cold open holds the rekey
                    # sidecar, which the default failure record (through
                    # the open-engine status path) would wait on. The
                    # failure is logged below and the next login backup
                    # records it.
                    if retention is None:
                        result = BackupService(
                            username=username,
                            password=password,
                            record_failure=False,
                        ).create_backup(force=True, prune=False)
                    else:
                        max_backups, max_age_days = retention
                        result = BackupService(
                            username=username,
                            password=password,
                            max_backups=max_backups,
                            max_age_days=max_age_days,
                            record_failure=False,
                        ).create_backup(force=True)
                    if result.success:
                        logger.info(
                            f"Pre-migration backup created: {result.backup_path}"
                        )
                    else:
                        logger.error(
                            f"Pre-migration backup failed: {result.error}"
                        )
                except Exception as e:
                    # ``password`` is in scope and was passed into
                    # ``BackupService`` above. Drop traceback + redact
                    # so the upstream error (which can carry the
                    # password through ``set_sqlcipher_key`` frames) is
                    # not written to log sinks.
                    from ..security.log_sanitizer import redact_secrets

                    safe_msg = redact_secrets(str(e), password)
                    logger.warning(
                        f"Pre-migration backup failed — proceeding with migration: {safe_msg}"
                    )

            # Init failures need to be distinguishable from credential
            # failures at the call site: the credentials worked (we
            # decrypted and ran SELECT 1), but the schema couldn't be
            # brought up. Re-raise as a typed error so the login route
            # can skip the lockout counter and surface a server-error
            # message instead of "Invalid username or password".
            try:
                initialize_database(engine)
            except Exception as init_err:
                elapsed_ms = (time.perf_counter() - open_start) * 1000
                # ``password`` is in scope (function parameter). The
                # initializer ran on an engine whose creator captured
                # ``hex_key`` derived from the password — a SQLAlchemy
                # ``OperationalError`` traceback could surface frames
                # holding the plaintext password under ``diagnose=True``.
                from ..security.log_sanitizer import redact_secrets

                safe_msg = redact_secrets(str(init_err), password)
                logger.warning(
                    f"Database migration failed for {username} "
                    f"after {elapsed_ms:.0f}ms — refusing login: {safe_msg}"
                )
                engine.dispose()
                # Sanitised message + ``from None``: ``init_err`` (its
                # str() and its chained traceback frames) can carry the
                # plaintext password, and the caller that catches this
                # typed error logs it with ``logger.exception``
                # (thread_local_session) — which would render the chain
                # and defeat the redaction applied just above. Break the
                # chain per ADR-0003; the redacted detail is already
                # logged at this site.
                raise DatabaseInitializationError(
                    f"Database initialisation failed for {username}: {safe_msg}"
                ) from None

            # NOTE: the phase-2 RAG index re-key does NOT run here. It re-enters
            # open_user_database() (via get_user_db_session on a session-cache
            # miss), and this method runs while holding the non-reentrant
            # per-user init_lock — so a concurrent close_user_database() evicting
            # the just-cached connection would make that re-entrant open miss the
            # fast-path and self-deadlock on the lock this thread already holds.
            # open_user_database() runs it AFTER releasing the lock instead.

            elapsed_ms = (time.perf_counter() - open_start) * 1000
            if elapsed_ms > 100:
                logger.info(
                    f"Opened encrypted database for user {username} "
                    f"(cold-open wall clock: {elapsed_ms:.0f}ms)"
                )
            else:
                logger.info(
                    f"Opened encrypted database for user {username} "
                    f"({elapsed_ms:.0f}ms)"
                )
            return engine

        except DatabaseInitializationError:
            # Already logged + engine disposed at the raise site. Re-raise
            # past the catch-all below so callers see the typed error.
            raise
        except Exception as e:
            elapsed_ms = (time.perf_counter() - open_start) * 1000
            # Catches connection errors during ``engine.connect()`` —
            # the connection creator passes the password into
            # ``set_sqlcipher_key`` so a failure there can carry the
            # password in the traceback's frame locals. Drop the
            # traceback and redact str(e).
            from ..security.log_sanitizer import redact_secrets

            safe_msg = redact_secrets(str(e), password)
            logger.warning(
                f"Failed to open database for user {username} "
                f"after {elapsed_ms:.0f}ms: {safe_msg}"
            )
            engine.dispose()
            if is_operational_sqlite_error(e):
                # Locked or busy past the busy timeout, an I/O error: the
                # password was not checked, so this is not the None that
                # callers (the login lockout counter) read as a wrong one.
                raise DatabaseKeyCheckUnavailableError(
                    db_path, type(e).__name__
                ) from None
            return None

    def get_session(self, username: str) -> Optional[Session]:
        """Bind a Session to ``username``'s ALREADY-open engine. Post-auth accessor.

        SECURITY INVARIANT -- this intentionally takes NO password and performs
        NO credential check, unlike ``open_user_database`` /
        ``create_thread_safe_session_for_metrics`` (which gate their cache hit
        through ``_cached_engine_trusted``). That is correct ONLY because this
        is a post-authentication accessor, never an authentication entry point:

          * It NEVER opens a database -- a cache miss returns ``None``. The
            engine it hands back was published to ``connections`` solely by a
            credential-verified create/open, so no unverified password can ever
            reach the cache via this method.
          * Every caller supplies a server-trusted ``username`` -- the signed
            Flask-session identity (``session["username"]`` / ``g.current_user``)
            or an internal server value (settings-snapshot thread, log drain)
            -- and most also gate on ``is_user_connected`` (the never-open-on-
            miss property above is the load-bearing guarantee, not that gate).
            The username is never an attacker-supplied login attempt paired with
            an unverified password, which is the shape that made the cached-
            connection bypass exploitable (see ``open_user_database``).

        DO NOT add a lazy open-on-miss here, and DO NOT call this from an
        auth-decision path: either turns this accessor into an unauthenticated
        bypass. Credential-taking entry points must go through
        ``open_user_database`` instead.

        ``None`` means only that no usable engine is cached: none is, or
        the cached one was stale and has just been detached. A cached
        engine whose rekey sidecar cannot be opened or locked (within the
        wait bound), or whose epoch cannot be read, is not a miss: the typed
        ``RekeySidecarIOError`` / ``RekeyLockContentionError`` /
        ``RekeyEpochUnreadableError`` is raised
        instead (the engine stays cached), as is a failure to release the
        sidecar afterwards. ``None`` would fail open here:
        ``get_settings_manager`` (``utilities/db_utils.py``) reads it as
        "this user has no database" and answers every setting from the
        defaults file, so during the fault a stored ``strict`` egress scope
        or a custom library ``storage_path`` would read as its default. A
        route that leaves the fault unhandled gets the app's retryable 503.
        """
        with self._acquire_current_connection_generation(
            username, raise_unverifiable=True
        ) as generation:
            if generation is not None:
                SessionLocal = sessionmaker(bind=generation.engine)
                return SessionLocal()
        logger.debug(f"No open database for user {username}")
        return None

    def get_connected_usernames(self) -> set:
        """Return a snapshot of usernames with open connections."""
        with self._connections_lock:
            return set(self.connections.keys())

    def _checkpoint_wal(self, engine, context: str = ""):
        """Checkpoint WAL before disposing engine to flush pending writes.

        Most callers hold the user's rekey sidecar gate (fenced disposal of
        a current generation, cache replacement during a cold open), so the
        TRUNCATE's busy wait is bounded by
        ``_GATED_CHECKPOINT_BUSY_TIMEOUT_MS`` (see there) for every caller;
        the checkpoint is best-effort either way. The pooled connection's
        own busy_timeout is restored before it goes back to the pool.
        """
        try:
            with engine.connect() as conn:
                gated_timeout_sql = _gated_checkpoint_busy_timeout_pragma()
                conn.execute(text(gated_timeout_sql))
                try:
                    # TRUNCATE returns (busy, log_pages, checkpointed_pages).
                    # busy=1 means a reader/writer held a WAL lock and the
                    # WAL was NOT truncated — surface that so we can tell
                    # from logs whether the helper actually shrank the file.
                    result = conn.execute(
                        text("PRAGMA wal_checkpoint(TRUNCATE)")
                    ).fetchone()
                finally:
                    restore_timeout_sql = sqlcipher_busy_timeout_pragma()
                    conn.execute(text(restore_timeout_sql))
                if result and result[0] == 1:
                    logger.debug(
                        f"WAL checkpoint busy {context} — WAL not truncated"
                    )
        except Exception:
            logger.debug(f"WAL checkpoint failed {context}", exc_info=True)

    @staticmethod
    def _require_full_synchronous(connection) -> None:
        connection.execute("PRAGMA synchronous = FULL")
        result = connection.execute("PRAGMA synchronous").fetchone()
        if result != (2,):
            raise _RekeyVerificationError("synchronous mode")

    @staticmethod
    def _pre_rekey_checkpoint(connection) -> None:
        """Fold committed WAL frames into the main file; best-effort.

        PASSIVE never invokes the busy handler and never waits for a
        reader, so a reader pinning the WAL (a research thread's open read
        transaction, another worker's request) cannot stall the rekey here
        while the sidecar gate is held. Completeness is not required for
        correctness: ``PRAGMA rekey`` reads every page through the pager,
        WAL included, and the new key is proven afterwards by
        ``_verify_rekey_candidate``. A SQLite error still propagates and is
        an operational preparation failure.
        """
        connection.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()

    @staticmethod
    def _best_effort_rekey_checkpoint(connection) -> bool:
        """Checkpoint after the new key is proven; True only when complete.

        A concurrent reader (another process's open read transaction, a
        backup connection) legitimately keeps a TRUNCATE checkpoint from
        completing. That is not an ambiguity about which key is active:
        the rekey transaction already committed under synchronous=FULL,
        so its WAL frames are durable and the next complete checkpoint
        folds them in (see ``_verify_rekey_candidate`` for what that
        leaves on disk meanwhile). The busy wait is bounded by
        ``_GATED_CHECKPOINT_BUSY_TIMEOUT_MS`` because the sidecar gate is
        held; the connection is closed right after verification, so its
        busy_timeout is not restored.
        """
        gated_timeout_sql = _gated_checkpoint_busy_timeout_pragma()
        connection.execute(gated_timeout_sql)
        result = connection.execute(
            "PRAGMA wal_checkpoint(TRUNCATE)"
        ).fetchone()
        return (
            result is not None
            and len(result) == 3
            and result[0] == 0
            and result[1] == result[2]
        )

    @staticmethod
    def _warn_rekey_integrity(db_path: Path, check: str, rows) -> None:
        """Log an integrity finding loudly without failing the rekey.

        Only the number of findings and the first one (SQLite's own
        message: page numbers, table and index names, never row data or
        key material), truncated, are logged.
        """
        first = repr(rows[0])[:200] if rows else "no result"
        logger.warning(
            f"{check} reported {len(rows)} problem(s) for {db_path.name} "
            f"after the active key was proven (first: {first}); the key "
            "verdict stands, but the database needs an integrity review "
            "(see docs/security/rekey-recovery.md)"
        )

    def _verify_rekey_candidate(self, connection, db_path: Path) -> None:
        """Prove that ``connection``'s key is the database's active key.

        The proof is that the key decrypts the schema page: SQLCipher
        authenticates every page it reads (HMAC), and page 1 is written
        under exactly one key, so a database state never opens under both
        the old and the new key. Which key is active is decided here, and
        only a failure of that proof raises.

        The integrity scans after it (``quick_check`` and
        ``cipher_integrity_check``) say whether the file is healthy, not
        which key it is under. A database that was already damaged (a bad
        page elsewhere, an inconsistent schema) fails them under whichever
        key is active, so failing the rekey or its recovery on them left
        the pending state in place for good and fenced the user out of a
        database that opens. They are logged loudly instead.
        """
        self._require_full_synchronous(connection)
        try:
            schema = connection.execute(
                "SELECT count(*) FROM sqlite_master"
            ).fetchone()
        except Exception:
            raise _RekeyVerificationError("database key") from None
        if schema is None:
            raise _RekeyVerificationError("database key")
        try:
            quick_rows = list(connection.execute("PRAGMA quick_check"))
        except Exception as error:
            quick_rows = [(type(error).__name__,)]
        if not quick_rows or any(row != ("ok",) for row in quick_rows):
            self._warn_rekey_integrity(
                db_path, "PRAGMA quick_check", quick_rows
            )
        # The new key is now proven against the committed, durable file, so
        # a checkpoint that cannot complete (busy reader) must not make the
        # outcome indeterminate -- that stranded users behind a pending
        # epoch whose key had in fact changed.
        #
        # Checkpoint BEFORE the cipher integrity scan: after the rekey
        # commit the WAL holds the pages re-encrypted under the NEW key,
        # while the main database file still holds them under the OLD key
        # until a checkpoint copies the frames back. cipher_integrity_check
        # reads the main file directly (not the WAL), so a new-key scan
        # reports phantom "HMAC verification failed" rows for every page
        # not yet checkpointed. Pinning the canary with a real-SQLCipher
        # duel showed the pre-checkpoint scan failing and the
        # post-checkpoint scan passing on the identical file. The scan is
        # therefore only meaningful (and only run) after a complete
        # checkpoint.
        try:
            checkpointed = self._best_effort_rekey_checkpoint(connection)
        except Exception as error:
            # A checkpoint that fails (SQLITE_CORRUPT on a damaged page, an
            # I/O error) leaves the committed WAL frames in place exactly
            # like a busy one; the key verdict above stands.
            logger.warning(
                f"Post-rekey WAL checkpoint for {db_path.name} failed: "
                f"{type(error).__name__}"
            )
            checkpointed = False
        if checkpointed:
            try:
                cipher_rows = list(
                    connection.execute("PRAGMA cipher_integrity_check")
                )
            except Exception as error:
                cipher_rows = [(type(error).__name__,)]
            if cipher_rows:
                self._warn_rekey_integrity(
                    db_path, "PRAGMA cipher_integrity_check", cipher_rows
                )
        else:
            # Until a later checkpoint completes, the re-encrypted pages
            # live only in the WAL and the main database file still holds
            # every page under the OLD key (a copy of the main file alone
            # opens with the old password). The live database (main file
            # plus WAL) already rejects the old key. The window closes at
            # the first complete checkpoint after the pinning reader ends:
            # SQLite's own checkpoint when the last connection to the file
            # closes, the TRUNCATE before any engine disposal (logout, idle
            # cleanup, shutdown), or an automatic checkpoint after later
            # writes.
            logger.warning(
                f"Post-rekey WAL checkpoint for {db_path.name} did not "
                "complete (a concurrent reader holds the WAL); the new key "
                "is verified, but the main database file keeps old-key "
                "pages until a later checkpoint folds the WAL in"
            )
        # No raw fsync of the database or its WAL here, and nowhere else
        # while this process may hold a connection to the file: closing ANY
        # descriptor to it releases every POSIX advisory (fcntl) lock this
        # process holds on that inode, including the SHARED lock SQLite's
        # WAL mode relies on (sqlite.org/howtocorrupt.html, section 2.2).
        # SQLite's per-inode lock bookkeeping would not notice, so another
        # process could checkpoint and delete the WAL underneath a live
        # connection here and its later commits would be lost. Durability
        # comes from SQLite itself instead: the rekey transaction committed
        # under synchronous=FULL (enforced by ``_require_full_synchronous``
        # on the old-key connection), which fsyncs the WAL at commit, and a
        # complete checkpoint on this synchronous=FULL connection fsyncs
        # the main database file.

    @staticmethod
    def _classify_rekey_failure(
        db_path: Path, stage: RekeyStage
    ) -> RekeyUnavailableError | RekeyIndeterminateError:
        try:
            state = read_rekey_epoch_state(db_path)
        except Exception:
            state = None
        if (
            stage is RekeyStage.PENDING_PUBLICATION
            and state is not None
            and state.pending_epoch is None
        ):
            return RekeyUnavailableError(db_path, stage)
        return RekeyIndeterminateError(db_path, stage, state)

    @staticmethod
    def _clear_committed_rekey_pending(
        db_path: Path, pending_epoch: str, username: str
    ) -> None:
        try:
            clear_pending_rekey_epoch(db_path, pending_epoch)
        except Exception as error:
            logger.warning(
                f"Pending rekey cleanup failed for {username}: "
                f"{type(error).__name__}"
            )

    @staticmethod
    def _close_rekey_connection(connection, username: str, phase: str) -> None:
        try:
            connection.close()
        except Exception as error:
            logger.warning(
                f"Rekey {phase} connection close failed for {username}: "
                f"{type(error).__name__}"
            )

    def _recover_pending_rekey(
        self,
        db_path: Path,
        password: str,
        state: RekeyEpochState,
    ) -> bool:
        """Verify the surviving SQLCipher key before finalizing pending state.

        Returns False when ``password`` does not open the surviving key: an
        ordinary wrong password, which callers must treat exactly like any
        other failed credential (the login route counts it toward the
        lockout). Other failures while opening or verifying the surviving key
        or publishing the pending epoch raise ``RekeyRecoveryRequiredError``.
        This includes transient SQLite busy/locked errors during key proof,
        before a password verdict is available. The error type alone proves
        neither successful password verification nor malformed epoch metadata;
        the login route answers it with 503 and no lockout strike.
        """
        pending_epoch = state.pending_epoch
        if pending_epoch is None:
            return True
        connection = None
        try:
            try:
                connection = self._make_sqlcipher_connection(db_path, password)
            except ValueError:
                # Neither surviving key accepted this password; disk and
                # pending metadata stay untouched.
                return False
            self._verify_rekey_candidate(connection, db_path)
            if state.committed_epoch != pending_epoch:
                commit_rekey_epoch(db_path, pending_epoch)
            clear_pending_rekey_epoch(db_path, pending_epoch)
        except Exception:
            # This also wraps key-proof contention before a password verdict;
            # the type alone does not establish malformed epoch metadata.
            raise RekeyRecoveryRequiredError(db_path) from None
        finally:
            if connection is not None:
                self._close_rekey_connection(
                    connection, db_path.name, "recovery"
                )
        return True

    def _resolve_unapplied_rekey(
        self, db_path: Path, old_password: str, pending_epoch: str
    ) -> Never:
        """Classify a rekey whose new key does not open the database.

        SQLCipher's ``PRAGMA rekey`` ignores the result of the underlying
        rekey: when another connection's write transaction outlasts the
        busy timeout it answers ``ok``, raises nothing, and leaves the file
        under the OLD key. The new-key open in ``change_password`` is what
        catches that (a key that does not match the file fails SQLCipher's
        page HMAC), so no epoch is ever committed for a key that did not
        take effect.

        If the old password still opens and verifies the database, the
        rekey provably did not apply: the pending generation is resolved
        exactly as a login with the surviving password resolves it
        (``_recover_pending_rekey``), and the change fails as unavailable
        (retryable; the old password stays valid and no pending state is
        left behind). If neither password opens it, the pending generation
        is no longer the one this call published, or the recovery itself
        fails, the outcome stays indeterminate as before.
        """
        try:
            state = read_rekey_epoch_state(db_path)
            old_key_survived = (
                state.pending_epoch == pending_epoch
                and self._recover_pending_rekey(db_path, old_password, state)
            )
        except RekeyRecoveryRequiredError:
            old_key_survived = False
        if old_key_survived:
            logger.warning(
                f"PRAGMA rekey did not change the key of {db_path.name} "
                "(another connection likely held a write transaction past "
                "the busy timeout, or a damaged page stopped it); the old "
                "password remains active"
            )
            raise RekeyUnavailableError(db_path, RekeyStage.PHYSICAL_REKEY)
        raise self._classify_rekey_failure(
            db_path, RekeyStage.CANDIDATE_VERIFICATION
        )

    def _dispose_detached_connection(
        self,
        username: str,
        engine: Engine | None,
        *,
        checkpoint: bool = True,
    ) -> bool:
        """Dispose an engine outside cache locks, checkpointing when still current."""
        if engine is None:
            return False
        if checkpoint:
            self._checkpoint_wal(engine, f"for {username}")
        try:
            engine.dispose()
        except Exception as error:
            logger.warning(
                f"Failed to dispose engine for {username}: "
                f"{type(error).__name__}"
            )
            return False
        return True

    def _dispose_connection_generation(
        self,
        generation: _ConnectionGeneration,
        *,
        detach: bool,
    ) -> bool:
        """Dispose one cache generation with epoch and identity fencing."""
        db_path = self._get_user_db_path(generation.username)
        lifecycle_lock = self._get_rekey_lock(generation.username)
        with lifecycle_lock:
            try:
                with acquire_rekey_lock(db_path):
                    return (
                        self._dispose_connection_generation_under_sidecar_lock(
                            generation, detach=detach
                        )
                    )
            except (OSError, RekeyLockContentionError, RekeyOperationalError):
                # Contention, or the sidecar cannot even be created (the
                # user's data directory was removed out from under a still
                # cached engine -- test teardown, operator cleanup). Either
                # way there is nothing durable left to fence: fall back to
                # disposing the engine directly, without a WAL checkpoint
                # (checkpointing a vanished database is moot).
                if not detach or not self._detach_connection_generation(
                    generation
                ):
                    return False
                return self._dispose_detached_connection(
                    generation.username,
                    generation.engine,
                    checkpoint=False,
                )

    def _dispose_connection_generation_under_sidecar_lock(
        self,
        generation: _ConnectionGeneration,
        *,
        detach: bool,
    ) -> bool:
        """Dispose one generation while its database sidecar lock is held."""
        db_path = self._get_user_db_path(generation.username)
        try:
            state = read_rekey_epoch_state(db_path)
        except RekeyEpochUnreadableError:
            # The sidecar opened but the epoch read itself failed (EMFILE
            # right after the sidecar took the last fd, EIO, persistent
            # EACCES): nothing was read, so nothing proves this generation
            # stale. Propagate it like a sidecar fault, exactly as the
            # probe path keeps the engine on the same error: the periodic
            # reset skips this generation for this sweep (engine kept,
            # nothing disposed), and an explicit close still detaches and
            # disposes it, without a WAL checkpoint. Substituting an empty
            # state here would detach and dispose every signed-in user's
            # engine on one transient read fault during the sweep.
            raise
        except RekeyRecoveryRequiredError:
            # Read, and does not vouch (malformed token, pending without
            # committed): the generation is stale, detach it below.
            state = RekeyEpochState(None, None)
        claim = self._claim_connection_generation(generation, state)
        match claim:
            case _CurrentConnectionGeneration(generation=current):
                if detach and not self._detach_connection_generation(current):
                    return False
                return self._dispose_detached_connection(
                    current.username,
                    current.engine,
                    checkpoint=True,
                )
            case _StaleConnectionGenerationDetached(generation=stale):
                return self._dispose_detached_connection(
                    stale.username,
                    stale.engine,
                    checkpoint=False,
                )
            case (
                _ConnectionGenerationChanged() | _ConnectionGenerationMissing()
            ):
                return False
            case unreachable:
                assert_never(unreachable)

    def close_user_database(self, username: str):
        """Close a user's database connection."""
        generation = self._snapshot_connection_generation(username)
        if generation is not None:
            if self._dispose_connection_generation(generation, detach=True):
                # Per-user init/rekey locks intentionally retain stable identities.
                logger.info(f"Closed database for user {username}")

    def close_all_databases(self):
        """Close all open user database connections and release file locks."""
        with self._connections_lock:
            detached = [
                _ConnectionGeneration(
                    username,
                    engine,
                    self._password_verifiers.get(username),
                    self._connection_epochs.get(username),
                )
                for username, engine in self.connections.items()
            ]
        for generation in detached:
            self._dispose_connection_generation(generation, detach=True)
        with self._connections_lock:
            connected = set(self.connections)
            for username in set(self._password_verifiers) - connected:
                self._password_verifiers.pop(username, None)
            for username in set(self._connection_epochs) - connected:
                self._connection_epochs.pop(username, None)

    def reset_connection_pools(self) -> int:
        """Reset cached pools safely and return the number successfully disposed."""
        # _init_locks is deliberately NOT cleared above: a concurrent
        # open_user_database may already hold a reference to a user's
        # cold-open lock (fetched via _get_init_lock) and be about to
        # enter it; dropping the entry would let a later open create a
        # SECOND lock for that user, so two cold-opens could migrate one
        # DB file at once -- the exact race the lock exists to stop.
        # _rekey_locks is likewise retained wholesale -- an entry may be
        # held by a change_password still in flight, and dropping it would
        # let the next caller build a SECOND gate for that user and rekey
        # the same file concurrently. Both registries are bounded by the
        # number of distinct usernames, one small Lock each.
        with self._connections_lock:
            generations = [
                _ConnectionGeneration(
                    username,
                    engine,
                    self._password_verifiers.get(username),
                    self._connection_epochs.get(username),
                )
                for username, engine in self.connections.items()
            ]
        return sum(
            self._reset_connection_generation(generation)
            for generation in generations
        )

    def _reset_connection_generation(
        self, generation: _ConnectionGeneration
    ) -> bool:
        lifecycle_lock = self._get_rekey_lock(generation.username)
        if not lifecycle_lock.acquire(blocking=False):
            return False
        try:
            try:
                with acquire_rekey_lock(
                    self._get_user_db_path(generation.username),
                    blocking=False,
                ):
                    return (
                        self._dispose_connection_generation_under_sidecar_lock(
                            generation, detach=False
                        )
                    )
            except (OSError, RekeyLockContentionError, RekeyOperationalError):
                # Includes a vanished data directory: skip this generation
                # this sweep; nothing durable remains to fence.
                return False
        finally:
            lifecycle_lock.release()

    @staticmethod
    def _wal_fully_checkpointed(conn, username: str) -> bool:
        """Run a PASSIVE checkpoint; True when no WAL frame is left behind.

        A database not in WAL mode answers ``(0, -1, -1)`` and counts as
        checkpointed. A checkpoint that cannot run, or leaves frames that a
        reader still needs, returns False so the caller skips scans that
        read only the main database file.
        """
        try:
            row = conn.execute(
                text("PRAGMA wal_checkpoint(PASSIVE)")
            ).fetchone()
        except Exception as error:
            logger.warning(
                f"WAL checkpoint before the integrity scan failed for "
                f"{username}: {type(error).__name__}"
            )
            return False
        if (
            row is not None
            and len(row) == 3
            and row[0] == 0
            and row[1] == row[2]
        ):
            return True
        logger.warning(
            f"Skipping the cipher integrity scan for {username}: WAL frames "
            "are not yet checkpointed into the main database file"
        )
        return False

    def check_database_integrity(self, username: str) -> bool:
        """Check database structure and encrypted page authentication.

        Plain SQLite uses the full integrity check to verify that indexes
        match table contents. SQLCipher retains the quick structural check
        and its independent page-HMAC verification; the latter reads the
        main database file only, so it runs only once a PASSIVE checkpoint
        has folded every WAL frame into that file.

        Runs only against the current, epoch-validated cached engine
        (``_acquire_current_connection_generation``); with no trusted open
        connection it returns False rather than opening one.

        Only the engine snapshot happens inside that claim; the scans run
        after it is released. The claim holds the user's lifecycle lock,
        in-process sidecar gate and sidecar flock, and every other probe for
        this user (the shared log-drain thread, the idle-connection sweep,
        each request's session lookup) waits on them, so a full-file
        ``cipher_integrity_check`` under the claim stalled them all for the
        length of the scan. A disposal that runs during the scan (a close,
        the idle-connection sweep) still holds those locks while its
        TRUNCATE checkpoint waits for the scan's read transaction, bounded
        by ``_GATED_CHECKPOINT_BUSY_TIMEOUT_MS``. Accepted residual, the same as for any session
        handed out by ``get_session``: a password change that commits after
        the snapshot detaches and disposes the engine but does not stop this
        check, whose connection keeps the old key. It can then return False
        (an error or HMAC failures caused by the key change, not by
        corruption); the engine itself is never handed to a caller.
        """
        try:
            with self._acquire_current_connection_generation(
                username
            ) as generation:
                if generation is None:
                    return False
                engine = generation.engine

            with engine.connect() as conn:
                # quick_check omits index/table consistency and UNIQUE
                # checks. Check those too for the unencrypted fallback: the
                # page HMAC that SQLCipher verifies below catches page-level
                # bit damage, but not a cryptographically valid page whose
                # index and table contents have become logically
                # inconsistent with each other — quick_check's cheaper
                # structural check misses that class regardless of
                # encryption, so the SQLCipher path deliberately keeps it
                # rather than paying for the full scan on every check.
                integrity_pragma = (
                    "PRAGMA quick_check"
                    if self.has_encryption
                    else "PRAGMA integrity_check"
                )
                result = conn.execute(text(integrity_pragma))
                if result.fetchone()[0] != "ok":
                    return False

                # SQLCipher integrity check. Plain SQLite (the unencrypted
                # fallback) has no cipher pages to verify and answers the
                # pragma with a closed result whose iteration raises; the
                # except below turned that into "integrity check failed"
                # for every healthy fallback database (#6357).
                #
                # cipher_integrity_check reads the main database file,
                # not the WAL. After a password change whose checkpoint
                # a reader kept busy, the WAL holds new-key frames while
                # the main file still holds those pages under the old
                # key, so the scan reported every such page as an HMAC
                # failure. Fold the WAL in first (PASSIVE: never waits
                # on readers or writers) and scan only when every frame
                # reached the main file; otherwise quick_check above,
                # which reads through the WAL, is the verdict.
                if self.has_encryption and self._wal_fully_checkpointed(
                    conn, username
                ):
                    result = conn.execute(text("PRAGMA cipher_integrity_check"))
                    # If this returns any rows, there are HMAC failures
                    failures = list(result)
                    if failures:
                        logger.error(
                            f"Integrity check failed for {username}: "
                            f"{len(failures)} HMAC failures"
                        )
                        return False

                return True

        except Exception as e:
            # No ``password`` parameter on this method and the engine
            # was created via a hex-key closure (the plaintext password
            # is not retained on the engine itself), so the traceback
            # frames in this method do not hold a credential. Still
            # drop the traceback to stay uniform with the rest of the
            # #4182 sweep and to guard against a caller frame that
            # happens to hold one (e.g., a route handler that
            # retrieved the password from the session store).
            from ..security.log_sanitizer import redact_secrets

            safe_msg = redact_secrets(str(e), None)
            logger.warning(
                f"Integrity check error for user: {username}: {safe_msg}"
            )
            return False

    def change_password(
        self, username: str, old_password: str, new_password: str
    ) -> bool:
        """Change the encryption password for a user's database.

        This rekeys the SQLCipher database — no separate auth-DB
        password-hash update is needed because passwords are never
        stored.  Login verification is done by attempting decryption.

        Returns:
            bool: True once the new password is verified as active, even
            if publishing its rekey epoch must be retried on the next
            successful open. False when SQLCipher is unavailable, the
            database path is refused as a symlink, or the old password
            fails key verification, including pending-rekey recovery.
            The username is validated first; without SQLCipher, False is
            returned before either password is validated.

        Raises:
            ValueError: If the username is None or empty, or SQLCipher is
                available and either password is None, empty, or
                whitespace-only. Invalid keys are rejected before the
                database is accessed (#6353).
            RekeyLockContentionError: If another password change owns the
                lock or the rekey lock cannot be acquired in time.
            RekeyUnavailableError: If the database is missing or an
                operational failure prevents the change without an
                indeterminate rekey outcome.
            RekeyRecoveryRequiredError: If durable rekey metadata requires
                verified recovery before the change can proceed.
            RekeyIndeterminateError: If rekey has started and durable state
                cannot establish which password remains active.
        """
        db_path = self._get_user_db_path(username)
        if not self.has_encryption:
            logger.warning(
                "Cannot change password - SQLCipher not available (databases are unencrypted)"
            )
            # Unencrypted mode is a static deployment mode, not an
            # operational failure: keep the documented boolean contract
            # (tests/security/test_db_connection_unencrypted_mode.py) for
            # the rekey path rather than mapping it to 503.
            return False

        # Validate BOTH keys before anything touches the database, mirroring
        # the guards on create_user_database / open_user_database. Rekeying to
        # an empty or whitespace-only password used to succeed and return
        # True, after which the open-path guard rejected that same password
        # and the database was permanently unopenable (#6353).
        if not self._is_valid_encryption_key(old_password):
            logger.error(
                f"Invalid encryption key when changing password for user {username}: "
                "old password is None or empty"
            )
            raise ValueError(
                "Invalid encryption key: old password cannot be None or empty"
            )
        if not self._is_valid_encryption_key(new_password):
            logger.error(
                f"Invalid encryption key when changing password for user {username}: "
                "new password is None or empty"
            )
            raise ValueError(
                "Invalid encryption key: new password cannot be None or empty"
            )

        rekey_lock = self._get_password_change_lock(username)
        if not rekey_lock.acquire(blocking=False):
            lock_path = db_path.with_name(f"{db_path.name}.rekey.lock")
            raise RekeyLockContentionError(lock_path)

        # Forget an earlier change on this thread: only a change that
        # commits below may vouch for a backup refresh.
        _last_password_change.value = None
        stage = RekeyStage.PREPARATION
        pending_may_exist = False
        committed = False
        old_connection = None
        new_connection = None
        try:
            # Same refusal as every other path that hands this pathname to
            # SQLCipher (#6493): the rekey must not follow a planted link
            # and write the rekeyed database outside the data directory.
            # _db_path_is_symlinked logs the actionable evidence (path ->
            # target) itself; nothing has been touched at this point.
            if _db_path_is_symlinked(db_path, username):
                return False
            if not db_path.exists():
                _raise_rekey_unavailable(db_path, stage)

            try:
                # Bounded wait for in-process holders on the sidecar gate
                # (cache probes, but also cold opens and disposals, which
                # can hold it for seconds), then a non-blocking flock
                # retried for a short window so another process's
                # millisecond cache probe is not reported as contention
                # while a genuine cross-process owner still fails with
                # the typed contention error. The dedicated
                # change-password gate above already fast-fails
                # concurrent rekeys in-process.
                with acquire_rekey_lock(
                    db_path,
                    local_wait=10.0,
                    contention_retry=_REKEY_CONTENTION_RETRY_SECONDS,
                ):
                    try:
                        state = read_rekey_epoch_state(db_path)
                        if (
                            state.pending_epoch is not None
                            and not self._recover_pending_rekey(
                                db_path, old_password, state
                            )
                        ):
                            return False
                        read_or_initialize_rekey_epoch(db_path)

                        cached_engine = self._detach_connection(username)
                        if cached_engine is not None and not (
                            self._dispose_detached_connection(
                                username, cached_engine, checkpoint=False
                            )
                        ):
                            _raise_rekey_unavailable(db_path, stage)

                        try:
                            old_connection = self._make_sqlcipher_connection(
                                db_path, old_password
                            )
                        except ValueError:
                            return False
                        self._require_full_synchronous(old_connection)
                        self._pre_rekey_checkpoint(old_connection)

                        stage = RekeyStage.PENDING_PUBLICATION
                        pending_may_exist = True
                        pending_epoch = begin_rekey_epoch(db_path)

                        stage = RekeyStage.PHYSICAL_REKEY
                        set_sqlcipher_rekey(
                            old_connection, new_password, db_path=db_path
                        )
                        old_connection.commit()
                        # Never write through old_connection after the
                        # rekey: when PRAGMA rekey did not apply (see
                        # _resolve_unapplied_rekey) its write context is
                        # already on the NEW key while the file stays under
                        # the old one, and one INSERT there leaves pages
                        # that neither key opens. It is closed unused.
                        self._close_rekey_connection(
                            old_connection, username, "old-key"
                        )
                        old_connection = None

                        stage = RekeyStage.CANDIDATE_VERIFICATION
                        try:
                            new_connection = self._make_sqlcipher_connection(
                                db_path, new_password
                            )
                        except ValueError:
                            self._resolve_unapplied_rekey(
                                db_path, old_password, pending_epoch
                            )
                        self._verify_rekey_candidate(new_connection, db_path)

                        stage = RekeyStage.COMMIT_PUBLICATION
                        # The new key is proven at this point: it opened
                        # and decrypted the database, and a database state
                        # never opens under both keys. The password change
                        # has therefore happened, whatever the epoch files
                        # say, and reporting it as not completed would send
                        # the user back to an old password that no longer
                        # works while skipping the caller's revocation of
                        # every old-password session and credential.
                        epoch_published = True
                        try:
                            commit_rekey_epoch(db_path, pending_epoch)
                        except Exception as error:
                            try:
                                committed_state = read_rekey_epoch_state(
                                    db_path
                                )
                            except Exception:
                                committed_state = None
                            if committed_state != RekeyEpochState(
                                committed_epoch=pending_epoch,
                                pending_epoch=pending_epoch,
                            ):
                                # The pending file stays on disk (only this
                                # call removes it, after the commit), so the
                                # state remains fenced: read_rekey_epoch
                                # reports no stable epoch while it exists,
                                # which invalidates every cached engine, and
                                # every cold open runs _recover_pending_rekey
                                # first. That recovery rejects the old
                                # password (it no longer opens the file) and,
                                # for the new one, re-proves the key and
                                # publishes this same pending epoch.
                                epoch_published = False
                                logger.warning(
                                    f"Rekey epoch publication failed for "
                                    f"{username} after the new key was "
                                    f"verified ({type(error).__name__}); "
                                    "the password change is complete and "
                                    "the pending generation will be "
                                    "committed by the next successful open"
                                )

                        committed = True
                        _last_password_change.value = (db_path, pending_epoch)
                        if epoch_published:
                            self._clear_committed_rekey_pending(
                                db_path, pending_epoch, username
                            )
                        self._close_rekey_connection(
                            new_connection, username, "new-key"
                        )
                        new_connection = None
                    except (
                        RekeyIndeterminateError,
                        RekeyUnavailableError,
                    ):
                        raise
                    except RekeyRecoveryRequiredError:
                        if pending_may_exist:
                            raise self._classify_rekey_failure(
                                db_path, stage
                            ) from None
                        raise
                    except Exception:
                        if pending_may_exist:
                            raise self._classify_rekey_failure(
                                db_path, stage
                            ) from None
                        _raise_rekey_unavailable(db_path, stage)

                logger.info(f"Password changed for user {username}")
                return True
            except RekeyLockContentionError:
                raise
            except (
                RekeyIndeterminateError,
                RekeyRecoveryRequiredError,
                RekeyUnavailableError,
            ):
                raise
            except Exception as error:
                if committed:
                    logger.warning(
                        f"Post-commit rekey cleanup failed for {username}: "
                        f"{type(error).__name__}"
                    )
                    logger.info(f"Password changed for user {username}")
                    return True

                logger.warning(
                    f"Failed to change password for user {username} during "
                    f"{stage.value}: {type(error).__name__}"
                )
                if pending_may_exist:
                    raise self._classify_rekey_failure(db_path, stage) from None
                _raise_rekey_unavailable(db_path, stage)
            finally:
                if old_connection is not None:
                    self._close_rekey_connection(
                        old_connection, username, "old-key"
                    )
                if new_connection is not None:
                    self._close_rekey_connection(
                        new_connection, username, "new-key"
                    )
        finally:
            rekey_lock.release()

    def refresh_backups_after_password_change(
        self, username: str, new_password: str
    ):
        """Replace old-key backups after this thread's ``change_password``.

        ``change_password`` released the rekey sidecar before returning, so
        another process may have changed the password again, and minted a
        backup under its newer key, before this runs. Purging by this
        password's view would then delete that fresh backup. Reacquire the
        sidecar and require the exact stable epoch this change committed
        before touching any file, and keep the sidecar through the purge and
        the new backup (the same order as ``_purge_superseded_key_backups``,
        which fences the login recovery path).

        Returns the ``BackupResult`` of the fresh backup, or ``None`` when
        the refresh was skipped (this change no longer owns the epoch, was
        not fenced, or the backup service targets another database): a later change purges and refreshes for itself, and a change
        whose epoch was never published is resolved by the next successful
        open, which purges the superseded backups. Raises
        ``RekeyLockContentionError`` if the sidecar stays held, so the
        caller can report that old-key backups may remain.

        Every outcome except a purge that ran records an incomplete purge in
        the backup status: each skip, and every exception raised before
        ``purge_and_refresh`` started (contention, a sidecar or epoch fault,
        a failure to set up the backup service), which is then re-raised.
        Not recorded: a failure to resolve this user's database path, the
        first statement here, because there is then no database to record
        into; the route logs it.
        This refresh removed nothing in those cases, so old-key backups may
        remain. The status is written after the sidecar is released, through
        a connection opened with ``new_password``, never through a
        ``BackupService`` that could itself fail. A purge that ran records
        its own result, including an error after it (``purge_and_refresh``).
        The warning is cleared only by a later ``purge_and_refresh`` that
        verifies removal, i.e. another password change. The login-time
        recovery purge (``_purge_superseded_key_backups``) records no status,
        so it neither sets nor clears it: it lists with ``Path.glob`` and
        keeps backups it cannot classify, so it cannot vouch for removal.
        """
        from .backup.backup_service import BackupService

        db_path = self._get_user_db_path(username)
        recorded = getattr(_last_password_change, "value", None)
        _last_password_change.value = None
        if recorded is None or recorded[0] != db_path:
            logger.warning(
                f"Backup refresh skipped for {username}: no committed "
                "password change on this thread to fence it against"
            )
            self._record_backup_refresh_skipped(username, db_path, new_password)
            return None
        expected_epoch = recorded[1]

        refresh_started = False
        skipped = False
        try:
            with acquire_rekey_lock(
                db_path,
                local_wait=10.0,
                contention_retry=_REKEY_CONTENTION_RETRY_SECONDS,
            ):
                state = read_rekey_epoch_state(db_path)
                if state != RekeyEpochState(expected_epoch, None):
                    logger.info(
                        f"Backup refresh skipped for {username}: the rekey "
                        "epoch moved or is unresolved since this change"
                    )
                    skipped = True
                else:
                    backup_service = BackupService(
                        username=username,
                        password=new_password,
                        record_failure=False,
                    )
                    # A pinned manager data_dir can differ from the backup
                    # service's configured directory. The fence must vouch
                    # for the database whose backups would actually be
                    # deleted.
                    if backup_service.db_path != db_path:
                        logger.warning(
                            f"Backup refresh skipped for {username}: backup "
                            "service targets a different database"
                        )
                        skipped = True
                    else:
                        refresh_started = True
                        return backup_service.purge_and_refresh()
        except Exception:
            if not refresh_started:
                self._record_backup_refresh_skipped(
                    username, db_path, new_password
                )
            raise
        if skipped:
            self._record_backup_refresh_skipped(username, db_path, new_password)
        return None

    @staticmethod
    def _record_backup_refresh_skipped(
        username: str, db_path: Path, new_password: str
    ) -> None:
        """Report that old-key backups may remain; never raises.

        Builds no ``BackupService``: its setup may be what failed.
        """
        try:
            from .backup.backup_service import password_status_session
            from .backup.backup_status import (
                BackupError,
                record_backup_result,
            )

            record_backup_result(
                username,
                False,
                BackupError.PURGE_INCOMPLETE,
                purge_incomplete=True,
                direct_session=lambda: password_status_session(
                    db_path, new_password
                ),
            )
        except Exception as error:
            logger.warning(
                "Could not record the skipped backup refresh: "
                f"{type(error).__name__}"
            )

    def user_exists(self, username: str) -> bool:
        """Check if a user exists in the auth database."""
        from .auth_db import auth_db_session
        from .models.auth import User

        with auth_db_session() as session:
            user = session.query(User).filter_by(username=username).first()
            return user is not None

    def get_memory_usage(self) -> Dict[str, Any]:
        """Get memory usage statistics."""
        with self._connections_lock:
            num_connections = len(self.connections)
        return {
            "active_connections": num_connections,
            "active_sessions": 0,  # Sessions are created on-demand, not tracked
            "estimated_memory_mb": num_connections
            * 3.5,  # ~3.5MB per connection
        }

    def create_thread_safe_session_for_metrics(
        self, username: str, password: str
    ):
        """
        Create a new database session safe for use in background threads.

        Previously this method created a dedicated NullPool engine per
        (username, thread_id) pair, which leaked file descriptors under
        load (SQLCipher + WAL holds 3 FDs per active connection and
        orphaned engines accumulated when @thread_cleanup did not fire).

        It now routes through the shared per-user QueuePool engine at
        ``self.connections[username]``. That engine is already created
        with ``check_same_thread=False`` (so background threads are
        safe), is bounded by ``pool_size + max_overflow``, and is
        subject to the periodic ``dispose()`` workaround in
        ``connection_cleanup.py`` that mitigates the SQLCipher+WAL
        out-of-order-close FD leak.

        Args:
            username: The username
            password: The user's password (encryption key). Verified against
                the cached connection's recorded verifier on a cache hit, and
                used to open the user database on a cache miss or mismatch.

        Returns:
            A SQLAlchemy Session bound to the per-user QueuePool engine.

        Raises:
            RekeyOperationalError: The database is unavailable for an
                operational reason (the rekey sidecar or an epoch file cannot
                be read, or the durable rekey state needs recovery), raised
                the way ``open_user_database`` raises it: before any key is
                derived when a cached engine cannot be checked. Not a
                credential verdict, so it is not folded into a None that
                reads like one. ``ThreadSafeMetricsWriter.get_session``
                reports it as its own "no session" failure;
                ``ThreadLocalSessionManager.get_session`` lets it reach
                ``get_user_db_session``, which raises
                ``DatabaseSessionUnavailableError``.
            ValueError: The database path is a symlink or missing, or
                ``password`` does not open the database.
        """
        db_path = self._get_user_db_path(username)

        if _db_path_is_symlinked(db_path, username):
            raise ValueError(
                f"Database path for user {username!r} is a symlink; refusing "
                "to open it"
            )

        if not db_path.exists():
            raise ValueError(f"No database found for user {username}")

        # Trust the cached engine only when the presented password matches
        # the one that opened it -- the same fail-closed rule as
        # open_user_database. Returning a session on a bare cache hit here
        # would be a second instance of exactly the cached-connection
        # password bypass this change closes; falling through to
        # open_user_database on a mismatch re-validates the key against
        # SQLCipher.
        with self._acquire_current_connection_generation(
            username
        ) as generation:
            if generation is not None and (
                self._connection_generation_password_matches(
                    generation, password
                )
            ):
                Session = sessionmaker(bind=generation.engine)
                return Session()

        # A cached engine that the probe above could not check (a rekey
        # sidecar or epoch-read fault) makes this raise that typed fault
        # without deriving a key. It propagates (see Raises): each caller
        # decides what an operational fault means for it.
        engine = self.open_user_database(username, password)
        if engine is None:
            raise ValueError(f"Failed to open database for user {username}")

        Session = sessionmaker(bind=engine)
        return Session()


# Global instance
db_manager = DatabaseManager()