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.

70.9 KB
Raw Blame
"""Schema-integrity guards for #3299 that no other test file holds.

#3299 is a Flask -> FastAPI port that claims, in
``changelog.d/3299.breaking.md``, that "the web-layer change itself adds no
database schema migration". (The release that ships it also carries later
data-repair revisions, which is why that fragment's rollback section requires
a data backup.) ``test_upgrade_from_pre_migration_install.py``
already turns the *directory* form of that claim into assertions
(``test_the_3299_port_added_or_edited_no_migration_revision``,
``test_the_3299_port_changed_no_orm_model``, and the generalised
``test_pr_edits_no_shipped_revision_and_ships_a_migration``), and
``test_alembic_migrations.py``
already covers chain shape (``test_single_head_revision``,
``test_revision_chain_has_no_gaps``, ``test_revision_ids_are_unique``,
``test_migration_revision_ids_match_filenames``), downgrade substance
(``test_all_downgrades_are_substantive``,
``test_stairway_up_down_up_per_revision``), idempotency
(``test_idempotent_migrations``, ``test_upgrade_then_reinitialize_is_idempotent``),
schema/model parity (``test_migrations_produce_schema_matching_models``,
which on Alembic >= 1.11 compares foreign-key ``ondelete`` too) and
SQLCipher execution (``TestSQLCipherMigrationsComprehensive``). None of
that is repeated here.

What is left uncovered, and is what this file asserts:

1. **The byte-identity guarantee is scoped to a directory, not to the
   schema.** ``test_pr_edits_no_shipped_revision_and_ships_a_migration``
   watches
   ``src/local_deep_research/database/models``. But the metadata Alembic
   actually migrates -- ``Base.metadata``, wired into
   ``migrations/env.py`` as ``target_metadata`` -- is fed by 23 source
   files, and one of them, ``src/local_deep_research/domain_classifier/
   models.py``, lives outside that directory. It contributes the
   ``domain_classifications`` table via the import at
   ``database/models/__init__.py:100``. An edit there today changes the
   shipped schema and no existing guard notices.

   That blind spot is load-bearing because of *how* revision ``0001``
   works: it is not a frozen 2025 DDL script, it calls
   ``Base.metadata.create_all()``. So the baseline every fresh install
   receives is whatever the model files say **today**, while an existing
   user who upgrades receives only the revisions after ``0001``. Edit a model
   without a migration and the two populations silently diverge -- fresh
   installs get the new column, upgraders do not, and the changelog's
   claim that "the web-layer change itself adds no database schema
   migration" becomes
   false for exactly one of them.
   Tests here derive the guarded file set *from the live metadata*, so
   the guard cannot drift away from the schema again.

   Two rules follow, and they are deliberately different in scope. The
   #3299 claim itself is a statement about ONE commit, so it is checked
   against that commit's own range (``fb4e33b8d^..fb4e33b8d``) and stays
   a true, re-verified statement forever -- including long after the
   branch it was written for was merged and forgotten. What every LATER
   branch has to satisfy is the rule that claim was a special case of,
   shared with ``test_upgrade_from_pre_migration_install.py`` and defined
   in ``schema_change_rule.py``: a shipped revision is immutable, adding
   a revision is ordinary, and a file in the *guarded set* may only move
   in a branch that also adds one, unless isolated SQLite snapshots prove
   that a model edit emits the same schema. "Guarded set" and not "any file
   behind ``Base.metadata``", because that is what is actually enforced: the set
   is the mapper walk's answer plus ``DECLARATIVE_BASE_MODULE``, and a
   file that feeds the schema without appearing there is not covered.
   "Move" means its code moved -- comment-, docstring- and
   formatting-only edits are filtered out before the rule sees them.

2. **Where FK enforcement is actually turned on.** Half the ORM's
   referential integrity is ``ondelete="CASCADE"``/``"SET NULL"``, which
   SQLite ignores unless ``PRAGMA foreign_keys = ON`` is set **per
   connection**. Several test fixtures in this suite set that pragma
   themselves "to mirror production" -- which proves nothing about
   production. Nothing asserts that every production path which hands out
   a live user-database connection routes through the one helper that
   sets it. These tests enumerate every connection-producing function in
   ``src/local_deep_research/database`` and pin that inventory, so a new
   path that forgets the pragma fails here rather than in a user's
   orphaned rows.

3. **Whether a migration could assume a plaintext SQLite file.** A
   revision that opens its own ``sqlite3`` connection, ``ATTACH``es a
   file, or runs ``VACUUM INTO`` would bypass the keyed SQLCipher
   connection Alembic was handed and either fail or write plaintext.
   Migrations must only ever touch ``op.get_bind()``.

Most checks use git blob hashes, Alembic script metadata, and AST. The
general branch guard also compares fresh SQLite schemas in isolated
processes when model edits have no accompanying migration.
"""

import ast
import subprocess
import sys
from pathlib import Path

import pytest

from local_deep_research.database.alembic_runner import get_migrations_dir
from local_deep_research.database.models import Base

from tests.database.schema_change_rule import (
    DECLARATIVE_BASE_MODULE,
    MIGRATIONS_SUBTREE,
    MIN_METADATA_TABLES,
    MIN_MODEL_FILES,
    MIN_REVISIONS,
    MODELS_SUBTREE,
    classify,
    drop_comment_only_edits,
    drop_proven_schema_neutral_edits,
    is_revision,
    violations,
)
from tests.database.schema_snapshot import model_edits_preserve_schema

REPO_ROOT = Path(__file__).resolve().parents[2]

# The commit that merged #3299. ``changelog.d/3299.breaking.md`` says "the
# web-layer change itself adds no database schema migration" -- a claim
# about THIS commit and nothing else, so it is re-verified against this commit's own range
# rather than against whatever branch happens to be building.
PORT_3299_COMMIT = "fb4e33b8d8cba4d62c70cc2704007765ad9f6293"
DATABASE_PKG = REPO_ROOT / "src" / "local_deep_research" / "database"
SRC_ROOT = REPO_ROOT / "src" / "local_deep_research"

# The file the directory-scoped guard in
# test_upgrade_from_pre_migration_install.py cannot see. Named explicitly so
# that if it is ever moved under database/models the assertion below fails
# and this file's reason for existing gets re-examined rather than silently
# becoming a duplicate.
MODEL_FILE_OUTSIDE_THE_GUARDED_DIRECTORY = (
    "src/local_deep_research/domain_classifier/models.py"
)

# Discovery floors. Every "for each X" assertion below is worthless if X is
# empty, and an empty discovery is the classic way a chain-integrity test
# passes while testing nothing. ``MIN_MODEL_FILES`` and ``MIN_REVISIONS``
# come from ``schema_change_rule`` so that this gate and the one in
# ``test_upgrade_from_pre_migration_install.py`` cannot disagree about how
# empty is too empty. Deliberately well below the real numbers (69 tables /
# 23 files / 30 revisions) so ordinary growth does not trip them, but a
# collapsed discovery does.
# How many of today's guarded schema source files must still resolve at the
# #3299 range. Not a growth floor but the exact current count: every one of
# them existed at that commit, so anything less means a file has been
# renamed since and has silently dropped out of that comparison -- and a
# renamed file is absent at BOTH ends of the range, so nothing else would
# report it. Raise this deliberately when the count genuinely grows. The
# count is the 23 files the mapper walk resolves plus
# DECLARATIVE_BASE_MODULE, which the walk cannot see.
MODEL_SOURCE_FILES_AT_3299 = 24

# Tables that must be in Base.metadata. Chosen to span the model files that
# matter: the settings table the upgrade path rewrites, the research tables
# that hold user history, the library tables, and the one table contributed
# from outside database/models.
REQUIRED_TABLES = {
    "settings",
    "research_history",
    "research_resources",
    "documents",
    "collections",
    "benchmark_runs",
    "domain_classifications",
}

# Revisions that must be on the chain. Spot-checks rather than a full list
# (test_alembic_migrations.py::_REVISION_CHAIN already pins the whole
# sequence); these are the ones later assertions reason about.
REQUIRED_REVISIONS = {"0001", "0006", "0008", "0010", "0021", "0030"}

# Two files under src/ declare __tablename__ without joining Base.metadata.
# They are separate databases with their own DeclarativeBase, and the
# assertion below re-derives that fact from their source rather than
# trusting this list.
NON_ALEMBIC_TABLE_FILES = {
    "src/local_deep_research/journal_quality/models.py",
    "src/local_deep_research/library/download_management/models/__init__.py",
}

# The single helper that turns FK enforcement on.
PRAGMA_HELPER = "apply_performance_pragmas"

# Every function in src/local_deep_research/database that opens a database
# connection or builds an Engine, as of this branch. A pinned inventory: a
# new connection path is a new place to forget PRAGMA foreign_keys, so it
# must be added here consciously and classified.
#
# "user-db"  -> hands out a connection/Engine for a user database; must
#               reach apply_performance_pragmas.
# "exempt:*" -> justified below by a test that re-derives the justification.
KNOWN_CONNECTION_SITES = {
    "database/auth_db.py::_get_auth_engine": "exempt:auth-db",
    "database/auth_db.py::init_auth_database": "exempt:auth-db",
    "database/backup/backup_service.py::BackupService._verify_backup": (
        "exempt:backup-verify"
    ),
    "database/backup/backup_service.py::BackupService._open_backup_with_current_key": (
        "exempt:backup-verify"
    ),
    # #7443: writes a password-change purge result into the user database
    # with the password just entered, because the open engine's status path
    # takes the rekey sidecar the purge already holds. "user-db", not exempt:
    # the Session it yields writes to the user database. Every connection
    # comes from creator=create_sqlcipher_connection (salted key, cipher
    # pragmas, apply_performance_pragmas, key check), so no second open path
    # exists. It refuses a missing file, and NullPool plus engine.dispose()
    # in a finally keep no connection or key past the with block. Callers
    # run it either under the sidecar with this change's epoch still
    # committed (purge_and_refresh), or after release, where a later rekey
    # makes the keyed open fail, so nothing is written under a stale key.
    "database/backup/backup_service.py::password_status_session": "user-db",
    "database/encrypted_db.py::DatabaseManager._check_encryption_available": (
        "user-db"
    ),
    "database/encrypted_db.py::DatabaseManager._make_sqlcipher_connection": (
        "user-db"
    ),
    "database/encrypted_db.py::DatabaseManager._create_user_database_files": (
        "user-db"
    ),
    "database/encrypted_db.py::DatabaseManager._open_user_database_cold": (
        "user-db"
    ),
    "database/sqlcipher_compat.py::connect_sqlcipher": (
        "delegate:sqlcipher-wrapper"
    ),
    "database/sqlcipher_utils.py::create_sqlcipher_connection": "user-db",
}


# ---------------------------------------------------------------------------
# helpers -- plumbing only; every assertion lives in a test
# ---------------------------------------------------------------------------


def _git(*args):
    completed = subprocess.run(
        ["git", *args],
        cwd=REPO_ROOT,
        capture_output=True,
        text=True,
        check=False,
    )
    return completed.returncode, completed.stdout


def _require_merge_base():
    """The commit this branch forked from, or skip.

    The merge base, not ``origin/main``'s tip: a migration that landed on
    main after the fork is not this branch's doing. A shallow partial checkout
    can have no main ref at all, in which case there is nothing to compare
    against and saying so beats passing quietly.
    """
    for candidate in ("origin/main", "main"):
        code, _ = _git(
            "rev-parse", "--verify", "--quiet", f"{candidate}^{{commit}}"
        )
        if code != 0:
            continue
        code, out = _git("merge-base", candidate, "HEAD")
        if code == 0 and out.strip():
            return out.strip()
    pytest.skip(
        "no merge base with origin/main or main in this checkout, so the "
        "commit this branch forked from cannot be read and there is "
        "nothing to compare HEAD against; run against a full clone"
    )


def _require_commit_range(sha):
    """``(parent, commit)`` for ``sha``, or skip.

    A shallow or sparse CI checkout may not contain a months-old commit;
    saying so beats passing quietly on an empty comparison.
    """
    for rev in (f"{sha}^{{commit}}", f"{sha}^^{{commit}}"):
        code, _ = _git("rev-parse", "--verify", "--quiet", rev)
        if code != 0:
            pytest.skip(
                f"{rev} does not resolve in this checkout, so the #3299 "
                "no-schema-change claim cannot be re-verified here. A "
                "shallow clone is the usual cause: fetch-depth: 0 is "
                "required for this test to run at all."
            )
    code, out = _git("rev-parse", f"{sha}^")
    assert code == 0
    return out.strip(), sha


def _blob(ref, path):
    """The blob hash of one tracked file at ``ref``, or None if absent."""
    code, out = _git("cat-file", "-e", f"{ref}:{path}")
    if code != 0:
        return None
    code, out = _git("rev-parse", f"{ref}:{path}")
    assert code == 0, f"git rev-parse failed for {ref}:{path}"
    return out.strip()


def _blobs_under(ref, subtree):
    code, out = _git("ls-tree", "-r", ref, "--", subtree)
    assert code == 0, f"git ls-tree failed for {ref}:{subtree}"
    entries = {}
    for line in out.splitlines():
        meta, _, path = line.partition("\t")
        entries[path] = meta.split()[2]
    return entries


def _metadata_source_files():
    """Repo-relative paths of every file that defines a mapped table.

    Derived from the live registry rather than from a directory listing:
    this is the set of files that can change the schema Alembic ships.
    """
    files = {}
    outside = set()
    for mapper in Base.registry.mappers:
        module = sys.modules[mapper.class_.__module__]
        path = Path(module.__file__).resolve()
        if not path.is_relative_to(REPO_ROOT):
            outside.add(str(path))
            continue
        rel = str(path.relative_to(REPO_ROOT))
        for table in mapper.tables:
            files.setdefault(rel, set()).add(table.name)
    if outside:
        pytest.skip(
            "models resolve outside the repository "
            f"({sorted(outside)[:3]}) -- the tests are running against an "
            "installed copy, so a git comparison would be meaningless"
        )
    return files


def _guarded_source_files():
    """Every schema source file this gate watches: metadata plus the Base.

    ``_metadata_source_files()`` walks ``Base.registry.mappers``, so it
    finds every file that DEFINES a mapped class -- and nothing else.
    ``DECLARATIVE_BASE_MODULE`` defines no mapper and no ``__tablename__``,
    so that walk cannot see it, and it sits outside ``MODELS_SUBTREE`` so
    the directory guard in ``test_upgrade_from_pre_migration_install.py``
    cannot either. It is nonetheless the most schema-critical file in the
    package: it holds the canonical ``Base = declarative_base()``, and
    handing that call a ``metadata=MetaData(naming_convention=...)``
    renames every index and constraint revision ``0001``'s
    ``create_all()`` emits for a fresh install, while every existing user
    keeps the old names -- with zero violations reported. Named, not
    discovered, because there is nothing to discover it by.
    """
    files = dict(_metadata_source_files())
    files.setdefault(DECLARATIVE_BASE_MODULE, set())
    return files


def _file_text(ref, path):
    """The contents of one tracked file at ``ref``, or None if unreadable.

    Feeds ``schema_change_rule.drop_comment_only_edits``, which needs the
    source rather than the blob hash. Decoded with ``errors="strict"``: a
    file that fails to decode as UTF-8 simply stays reported, rather than
    silently comparing equal to an unrelated blob that also fails to decode.
    """
    completed = subprocess.run(
        ["git", "show", f"{ref}:{path}"],
        cwd=REPO_ROOT,
        capture_output=True,
        check=False,
    )
    if completed.returncode != 0:
        return None
    try:
        return completed.stdout.decode("utf-8", errors="strict")
    except UnicodeDecodeError:
        return None


def _source_pair_reader(before, after):
    """``read_pair`` for ``drop_comment_only_edits`` over two refs."""

    def read_pair(path):
        before_text = _file_text(before, path)
        after_text = _file_text(after, path)
        if before_text is None or after_text is None:
            return None
        return before_text, after_text

    return read_pair


def _source_file_blobs(ref, paths):
    """``{path: blob}`` at ``ref``, skipping paths that do not exist there.

    Absent-at-both-ends means "not part of this comparison" -- e.g. a
    model file added by a later PR is simply not in either map, so a
    commit-scoped comparison does not report it.
    """
    blobs = {}
    for path in paths:
        blob = _blob(ref, path)
        if blob is not None:
            blobs[path] = blob
    return blobs


def _revision_files():
    return sorted(
        path
        for path in (get_migrations_dir() / "versions").glob("*.py")
        if not path.name.startswith("__")
    )


def _iter_functions(tree, prefix=""):
    """(qualname, node) for every module-level function and method."""
    for node in tree.body:
        if isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef)):
            yield prefix + node.name, node
        elif isinstance(node, ast.ClassDef):
            yield from _iter_functions(node, prefix + node.name + ".")


def _mentioned_names(node):
    """Every identifier mentioned anywhere under ``node``.

    Names, not just call targets: a pragma helper is often handed to
    ``event.listen`` as a callback rather than called directly, and a
    reference is what we need to follow.
    """
    names = set()
    for child in ast.walk(node):
        if isinstance(child, ast.Name):
            names.add(child.id)
        elif isinstance(child, ast.Attribute):
            names.add(child.attr)
    return names


def _database_package_modules():
    return sorted(
        path
        for path in DATABASE_PKG.rglob("*.py")
        if "migrations" not in path.relative_to(DATABASE_PKG).parts
    )


def _connection_calls(func):
    """Calls under ``func`` that produce an Engine or a DBAPI connection."""
    found = []
    for node in ast.walk(func):
        if not isinstance(node, ast.Call):
            continue
        target = node.func
        if isinstance(target, ast.Name):
            name, base = target.id, ""
        elif isinstance(target, ast.Attribute):
            name, base = target.attr, ast.unparse(target.value).lower()
        else:
            continue
        if name == "create_engine":
            found.append(("create_engine", node))
        elif name == "connect_sqlcipher":
            found.append(("connect_sqlcipher", node))
        elif name == "connect" and (
            base == "driver"
            or any(token in base for token in ("sqlcipher", "sqlite", "dbapi"))
        ):
            found.append((f"{base}.connect", node))
    return found


def _connection_site_inventory():
    """{"<relpath>::<qualname>": [call kinds]} for the database package."""
    inventory = {}
    for path in _database_package_modules():
        tree = ast.parse(path.read_text(encoding="utf-8"))
        rel = str(path.relative_to(SRC_ROOT))
        for qualname, func in _iter_functions(tree):
            calls = _connection_calls(func)
            if calls:
                inventory[f"{rel}::{qualname}"] = (func, calls)
    return inventory


def _reaches_pragma_helper(func, helpers_by_name):
    """Does ``func`` mention PRAGMA_HELPER, directly or one hop away?

    One hop is enough for this codebase and is deliberately conservative:
    the encrypted path reaches it through ``create_sqlcipher_connection``
    and the unencrypted path through the ``_apply_pragmas`` connect
    listener. Resolution is by bare name, which can only make this check
    *more* permissive -- so a function it reports as unreachable really
    does not mention the helper anywhere near itself.
    """
    seen = set()
    frontier = [func]
    for _ in range(3):
        names = set()
        for node in frontier:
            names |= _mentioned_names(node)
        if PRAGMA_HELPER in names:
            return True
        frontier = [
            helpers_by_name[name]
            for name in sorted(names - seen)
            if name in helpers_by_name
        ]
        seen |= names
        if not frontier:
            break
    return False


def _helpers_by_name():
    helpers = {}
    for path in _database_package_modules():
        tree = ast.parse(path.read_text(encoding="utf-8"))
        for node in ast.walk(tree):
            if isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef)):
                helpers.setdefault(node.name, node)
    return helpers


def _innermost_block(root, target):
    """The smallest statement list under ``root`` that contains ``target``.

    Used to ask "was a connect listener registered in the *same branch*
    as this create_engine call", which is the question that distinguishes
    the encrypted branch from the unencrypted fallback.
    """
    best = None
    best_size = None
    for node in ast.walk(root):
        for field in ("body", "orelse", "finalbody"):
            block = getattr(node, field, None)
            if not isinstance(block, list) or not block:
                continue
            if not all(isinstance(stmt, ast.stmt) for stmt in block):
                continue
            if not any(target in ast.walk(stmt) for stmt in block):
                continue
            size = sum(len(list(ast.walk(stmt))) for stmt in block)
            if best_size is None or size < best_size:
                best, best_size = block, size
    return best


# ---------------------------------------------------------------------------
# 1. discovery -- everything below is vacuous without these
# ---------------------------------------------------------------------------


def test_the_schema_and_its_source_files_are_actually_discoverable():
    """Floors for every "for each" below, plus named members.

    A chain-integrity test that discovers nothing and passes is the
    standard failure mode for this kind of file. Counts alone are not
    enough -- a discovery that finds the wrong 60 things would still
    clear a floor -- so specific known tables and revisions are named.
    """
    tables = set(Base.metadata.tables)
    assert len(tables) >= MIN_METADATA_TABLES, (
        f"only {len(tables)} table(s) on Base.metadata -- the models did "
        "not import, so every schema assertion in this file is vacuous"
    )
    missing_tables = REQUIRED_TABLES - tables
    assert not missing_tables, (
        f"tables missing from Base.metadata: {sorted(missing_tables)}. "
        "Either the metadata is only partly populated or these tables "
        "were renamed; both invalidate the assertions below."
    )

    source_files = _metadata_source_files()
    assert len(source_files) >= MIN_MODEL_FILES, (
        f"only {len(source_files)} source file(s) resolved behind "
        f"{len(tables)} tables: {sorted(source_files)}"
    )

    # The named addition to that walk, checked against the disk rather
    # than trusted: it defines no mapper, so nothing else here would
    # notice it being renamed away and silently unguarded.
    assert (REPO_ROOT / DECLARATIVE_BASE_MODULE).is_file(), (
        f"{DECLARATIVE_BASE_MODULE} does not exist. It holds the canonical "
        "Base = declarative_base(); if it moved, update "
        "schema_change_rule.DECLARATIVE_BASE_MODULE, or the schema gates "
        "stop watching the file that names every constraint create_all() "
        "emits."
    )
    assert DECLARATIVE_BASE_MODULE not in source_files, (
        f"{DECLARATIVE_BASE_MODULE} now defines a mapped class, so the "
        "mapper walk reaches it on its own and _guarded_source_files() no "
        "longer needs to name it -- re-check that helper's reasoning"
    )
    assert DECLARATIVE_BASE_MODULE in _guarded_source_files(), (
        "the guarded set dropped the declarative_base() module"
    )

    revisions = _revision_files()
    assert len(revisions) >= MIN_REVISIONS, (
        f"only {len(revisions)} revision file(s) found under "
        f"{get_migrations_dir() / 'versions'} -- the migrations directory "
        "did not resolve, so chain assertions would pass on an empty set"
    )
    on_disk = {path.name.split("_")[0] for path in revisions}
    missing_revisions = REQUIRED_REVISIONS - on_disk
    assert not missing_revisions, (
        f"revision file(s) missing from versions/: {sorted(missing_revisions)}"
    )


# ---------------------------------------------------------------------------
# 2. the byte-identity guarantee, scoped to the schema rather than a directory
# ---------------------------------------------------------------------------


def test_the_metadata_is_fed_by_a_file_the_directory_guard_cannot_see():
    """This file's reason to exist, asserted rather than assumed.

    ``test_upgrade_from_pre_migration_install.py``'s directory guard
    diffs ``src/local_deep_research/database/models``. If every file
    behind ``Base.metadata`` were inside that directory, the guarantee
    would already be complete and the next test would be duplication. It
    is not: ``domain_classifier/models.py`` contributes
    ``domain_classifications`` from outside it.
    """
    source_files = _metadata_source_files()
    guarded_prefix = f"{MODELS_SUBTREE}/"
    unguarded = sorted(
        path for path in source_files if not path.startswith(guarded_prefix)
    )

    assert MODEL_FILE_OUTSIDE_THE_GUARDED_DIRECTORY in source_files, (
        f"{MODEL_FILE_OUTSIDE_THE_GUARDED_DIRECTORY} no longer contributes "
        "a table to Base.metadata. If it moved under database/models the "
        "directory-scoped guard now covers it -- check whether any other "
        f"file escaped instead (currently outside: {unguarded})."
    )
    assert MODEL_FILE_OUTSIDE_THE_GUARDED_DIRECTORY in unguarded, (
        "the file is inside the guarded directory after all"
    )
    assert source_files[MODEL_FILE_OUTSIDE_THE_GUARDED_DIRECTORY], (
        "the file resolved but maps no table"
    )


def test_the_schema_change_rule_is_pinned_without_touching_git():
    """The rule itself, exercised on hand-written inputs.

    The git-backed tests below can only report on THIS checkout, and on a
    clean tree they see no changes at all -- so they would pass just as
    happily if the rule were "anything goes". These cases pin what the
    rule actually permits and forbids, independently of any working tree.

    Paths are built from the shared subtree constants rather than written
    short, because the shape of the path is now load-bearing: both callers
    hand the rule the WHOLE migrations subtree, which contains three files
    that are not revisions (``env.py`` and two ``__init__.py`` markers)
    alongside the thirty that are.

    Cases (a) and (c) below are the discriminators for
    ``schema_change_rule.is_revision``: delete it, so that every path
    under the subtree counts as a revision again, and both FAIL -- (a)
    because an added ``README.md`` would then satisfy "ship a migration"
    and let an unmigrated model edit through, (c) because editing
    ``env.py`` would then be reported as editing an immutable revision.
    Case (b) is the control: it passes under both rules, and it is there
    so a failure in (a) can be read as "the rule got too permissive"
    rather than "the rule now rejects everything".

    The last group is the discriminator for
    ``schema_change_rule.drop_comment_only_edits``: those cases pass the
    rule a filtered ``edited`` list, and they fail if the filter is
    removed (a reworded comment is reported again) or if it is widened
    past docstrings (a real code change goes unreported).
    """
    versions = f"{MIGRATIONS_SUBTREE}/versions"
    models = MODELS_SUBTREE

    nothing = classify({}, {})
    added_rev = classify({}, {f"{versions}/0031_x.py": "aaa"})
    edited_model = classify(
        {f"{models}/a.py": "aaa"}, {f"{models}/a.py": "bbb"}
    )

    # Touching nothing is fine, and so is a revision on its own: data
    # migrations and backfills change no ORM class.
    assert violations(nothing, nothing) == []
    assert violations(nothing, added_rev) == []

    # A model change WITH a new revision is the shape this rule exists to
    # allow -- the old gate rejected it outright.
    assert violations(edited_model, added_rev) == []

    # Only a verified schema-identical edit is exempt; the evidence cannot
    # exempt additions/removals or edits to shipped migration bodies.
    assert violations(edited_model, nothing, schema_unchanged=True) == []
    assert len(violations(edited_model, nothing)) == 1
    assert (
        len(
            violations(
                classify({}, {f"{models}/b.py": "aaa"}),
                nothing,
                schema_unchanged=True,
            )
        )
        == 1
    )
    assert (
        len(
            violations(
                classify({f"{models}/c.py": "aaa"}, {}),
                nothing,
                schema_unchanged=True,
            )
        )
        == 1
    )

    # A model change with no new revision is the dangerous shape, in all
    # three of its forms.
    assert len(violations(edited_model, nothing)) == 1
    assert (
        len(violations(classify({}, {f"{models}/b.py": "aaa"}), nothing)) == 1
    )
    assert (
        len(violations(classify({f"{models}/c.py": "aaa"}, {}), nothing)) == 1
    )

    # (a) Adding a non-revision file under the subtree does NOT satisfy
    # "ship a migration". Reverting is_revision() makes this case FAIL:
    # the added README would land in revision_changes["added"], the rule
    # would see a non-empty set, and an unmigrated model edit would ship
    # unreported.
    added_readme = classify({}, {f"{MIGRATIONS_SUBTREE}/README.md": "aaa"})
    assert len(violations(edited_model, added_readme)) == 1, (
        "adding a README under the migrations subtree is not a migration"
    )
    added_marker = classify({}, {f"{versions}/__init__.py": "aaa"})
    assert len(violations(edited_model, added_marker)) == 1, (
        "adding the versions/ package marker is not a migration"
    )

    # (b) Control, not a discriminator: the same model edit WITH a real
    # revision file is allowed. It passes under both rules, so when (a)
    # fails it is because the rule got too permissive and not because the
    # rule now rejects everything.
    added_real = classify({}, {f"{versions}/0031_x.py": "aaa"})
    assert violations(edited_model, added_real) == []

    # (c) The non-revision files under the subtree stay editable. Under
    # the reverted rule this case FAILS: editing env.py would be reported
    # as a violation, with a message making the false claim that a user's
    # alembic_version row names env.py -- so env.py would be permanently
    # unmodifiable.
    edited_env = classify(
        {f"{MIGRATIONS_SUBTREE}/env.py": "aaa"},
        {f"{MIGRATIONS_SUBTREE}/env.py": "bbb"},
    )
    assert violations(nothing, edited_env) == [], (
        "migrations/env.py is Alembic plumbing, not a shipped revision"
    )
    edited_markers = classify(
        {
            f"{MIGRATIONS_SUBTREE}/__init__.py": "aaa",
            f"{versions}/__init__.py": "aaa",
        },
        {
            f"{MIGRATIONS_SUBTREE}/__init__.py": "bbb",
            f"{versions}/__init__.py": "bbb",
        },
    )
    assert violations(nothing, edited_markers) == []

    # Editing or removing a shipped revision is never allowed, not even
    # alongside an added one.
    edited_rev = classify(
        {f"{versions}/0007_y.py": "aaa"}, {f"{versions}/0007_y.py": "bbb"}
    )
    removed_rev = classify({f"{versions}/0007_y.py": "aaa"}, {})
    assert len(violations(nothing, edited_rev)) == 1
    assert len(violations(nothing, removed_rev)) == 1
    assert len(violations(edited_model, removed_rev)) == 2
    assert len(violations(edited_model, edited_rev, schema_unchanged=True)) == 1
    assert (
        len(violations(edited_model, removed_rev, schema_unchanged=True)) == 1
    )

    # Every violation names the offending path, so the failure is
    # actionable without re-running git by hand.
    assert f"{models}/a.py" in violations(edited_model, nothing)[0]
    assert f"{versions}/0007_y.py" in violations(nothing, edited_rev)[0]

    # The canonical declarative_base() module is in the guarded set both
    # callers hand over (see _guarded_source_files), and the rule treats
    # it like any other schema source file: edited with no revision is a
    # violation. Named here because no mapper walk and no models/
    # directory listing reaches it, so nothing else would notice it being
    # dropped from the set.
    edited_base = classify(
        {DECLARATIVE_BASE_MODULE: "aaa"}, {DECLARATIVE_BASE_MODULE: "bbb"}
    )
    assert len(violations(edited_base, nothing)) == 1
    assert DECLARATIVE_BASE_MODULE in violations(edited_base, nothing)[0]
    assert violations(edited_base, added_rev) == []

    # A blob moves when a comment moves, and neither Alembic nor
    # create_all() can see a comment. drop_comment_only_edits is what
    # keeps the gate from firing on those; it is the filter both callers
    # run before asking violations(), so it is pinned at the same level.
    code = (
        "def upgrade():\n"
        '    """Add the column."""\n'
        "    # the column\n"
        '    op.add_column("t", sa.Column("a", sa.Integer()))\n'
    )
    cosmetic = (
        "def upgrade():\n"
        '    """Add the column, at last."""\n'
        "    # the new column\n"
        "    op.add_column(\n"
        '        "t", sa.Column("a", sa.Integer())\n'
        "    )\n"
    )
    one_token = code.replace('sa.Column("a"', 'sa.Column("b"')

    def edits(path, before_source, after_source):
        """``classify`` says edited; the filter gets the two sources."""
        return drop_comment_only_edits(
            classify({path: "aaa"}, {path: "bbb"}),
            lambda _path: (before_source, after_source),
        )

    revision_path = f"{versions}/0007_y.py"
    model_path = f"{models}/a.py"

    # A comment- and docstring-only edit of a SHIPPED revision is not an
    # edit: the body Alembic runs is byte-for-byte the same for every
    # user. Same for a model file: create_all() emits the same DDL.
    assert violations(nothing, edits(revision_path, code, cosmetic)) == []
    assert violations(edits(model_path, code, cosmetic), nothing) == []

    # One token of real code, and both fire again. Without this pair the
    # filter could widen to "any .py edit is cosmetic" and the whole gate
    # would go quiet.
    assert len(violations(nothing, edits(revision_path, code, one_token))) == 1
    assert len(violations(edits(model_path, code, one_token), nothing)) == 1

    # Additions and removals are never filtered, whatever is in them, and
    # a file that will not parse is reported rather than assumed cosmetic.
    added_only = drop_comment_only_edits(
        classify({}, {model_path: "aaa"}), lambda _path: (code, code)
    )
    assert len(violations(added_only, nothing)) == 1
    unparseable = edits(revision_path, code, "def upgrade(:\n")
    assert len(violations(nothing, unparseable)) == 1


def test_schema_neutral_json_reader_exception_is_byte_exact():
    """A later model edit must not inherit #7012's no-migration exception."""
    path = "src/local_deep_research/database/models/research.py"
    before = {path: "17688ead0b4210d9e5e82db4e851f3de516aad7b"}
    after = {path: "ca9a78dcfc5bc631ce74b4cca60a025edf652171"}
    exact = classify(before, after)
    assert (
        drop_proven_schema_neutral_edits(exact, before, after)["edited"] == []
    )

    later = {path: "different-model-blob"}
    assert drop_proven_schema_neutral_edits(
        classify(before, later), before, later
    )["edited"] == [path]
    other_path = "src/local_deep_research/database/models/other.py"
    other_before = {other_path: before[path]}
    other_after = {other_path: after[path]}
    assert drop_proven_schema_neutral_edits(
        classify(other_before, other_after), other_before, other_after
    )["edited"] == [other_path]


def test_only_files_alembic_can_load_as_revisions_count_as_revisions():
    """``is_revision`` itself, since both halves of the rule turn on it.

    Alembic loads ``versions/*.py``; everything else in the subtree is
    plumbing. Getting this wrong in the permissive direction lets an
    unmigrated model change through (a ``README.md`` counts as the
    migration); getting it wrong in the restrictive direction makes
    ordinary files permanently unmodifiable and, worse, would exempt a
    real revision from the immutability rule.
    """
    versions = f"{MIGRATIONS_SUBTREE}/versions"

    for path in (
        f"{versions}/0001_initial_schema.py",
        f"{versions}/0030_default_time_period_all.py",
        f"{versions}/no_number_in_this_name.py",
    ):
        assert is_revision(path), path

    for path in (
        f"{MIGRATIONS_SUBTREE}/env.py",
        f"{MIGRATIONS_SUBTREE}/__init__.py",
        f"{MIGRATIONS_SUBTREE}/README.md",
        f"{versions}/__init__.py",
        f"{versions}/notes.md",
        f"{versions}/subdir/0031_x.py",
        f"{MODELS_SUBTREE}/research.py",
    ):
        assert not is_revision(path), path

    # The live directory agrees with the predicate: applied to every file
    # the migrations tree actually holds, is_revision() picks out exactly
    # the chain Alembic walks -- no more (the three plumbing files) and no
    # less. Read from disk rather than from git, so this stays a
    # git-independent pin.
    migrations_dir = get_migrations_dir()
    on_disk = {
        f"{MIGRATIONS_SUBTREE}/{path.relative_to(migrations_dir).as_posix()}"
        for path in migrations_dir.rglob("*")
        if path.is_file() and "__pycache__" not in path.parts
    }
    assert len(on_disk) > MIN_REVISIONS, (
        f"only {len(on_disk)} file(s) under {migrations_dir} -- the "
        "migrations directory did not resolve, so the cross-check below "
        "would compare two empty sets"
    )
    classified = {path for path in on_disk if is_revision(path)}
    expected = {
        f"{MIGRATIONS_SUBTREE}/versions/{path.name}"
        for path in _revision_files()
    }
    assert classified == expected, (
        "is_revision() and _revision_files() disagree about what the "
        f"chain contains: {sorted(classified ^ expected)}"
    )
    assert on_disk - classified, (
        "the migrations directory holds nothing but revisions, so this "
        "test no longer demonstrates that the plumbing files (env.py, the "
        "package markers) are excluded -- re-check the rule"
    )


def test_the_3299_port_changed_no_file_behind_the_alembic_metadata():
    """The no-migration claim, re-verified at the commit that made it.

    ``changelog.d/3299.breaking.md`` says "the web-layer change itself adds
    no database schema migration". That is a fact about one merge commit, so it is checked against that
    commit's own range -- which keeps it verifiable forever AND keeps it
    from misfiring on every later branch, which the generalised rule
    below governs instead.

    Scoped to the schema rather than to a directory, which is this
    file's reason to exist: the guarded set includes
    ``domain_classifier/models.py`` and ``DECLARATIVE_BASE_MODULE``, both
    outside the directory the guard in
    ``test_upgrade_from_pre_migration_install.py`` watches. Paths are
    today's; a file absent at both ends of the range is simply not part of
    the comparison.

    Byte-identity, not AST-identity: the claim is that #3299 left the
    schema alone entirely, so a reworded docstring in a model file would
    contradict it too. The generalised rule below is the one that filters
    cosmetic edits out.
    """
    before, after = _require_commit_range(PORT_3299_COMMIT)
    source_files = _guarded_source_files()
    assert len(source_files) >= MIN_MODEL_FILES, (
        "discovery collapsed; see "
        "test_the_schema_and_its_source_files_are_actually_discoverable"
    )

    baseline = _source_file_blobs(before, source_files)
    assert len(baseline) >= MODEL_SOURCE_FILES_AT_3299, (
        f"only {len(baseline)} of {len(source_files)} schema source file(s) "
        f"resolved at {before[:12]}, expected at least "
        f"{MODEL_SOURCE_FILES_AT_3299}. This comparison applies TODAY's "
        "path list to a months-old range, so a file renamed since then is "
        "absent at both ends and drops out of it silently -- which is what "
        "this floor is for. Confirm the missing file(s) were renamed and "
        "not deleted, then update MODEL_SOURCE_FILES_AT_3299."
    )

    changes = classify(baseline, _source_file_blobs(after, source_files))
    assert changes == {"added": [], "removed": [], "edited": []}, (
        f"#3299 ({PORT_3299_COMMIT[:12]}) DID change the schema its "
        f"changelog says it does not touch: {changes}. Either the commit "
        "sha above is wrong, or changelog.d/3299.breaking.md's 'The "
        "web-layer change itself adds no database schema migration' "
        "sentence is false and must be rewritten to describe that "
        "migration and its rollback."
    )


# The migrations-subtree half of the same claim is NOT repeated here.
# ``test_upgrade_from_pre_migration_install.py::
# test_the_3299_port_added_or_edited_no_migration_revision`` runs exactly
# that comparison -- same subtree, same range, same rule -- and this file
# exists for what a subtree listing cannot see.


def test_this_branch_edits_no_revision_and_migrates_every_model_change():
    """The generalised rule, applied to whatever this branch changed.

    Scoped to the schema rather than to a directory: the file set comes
    from the live ``Base.metadata`` registry plus
    ``DECLARATIVE_BASE_MODULE`` (see ``_guarded_source_files``), so an
    edit to ``domain_classifier/models.py`` or to the canonical
    ``declarative_base()`` call counts even though both sit outside
    ``database/models``.

    Why a schema-changing model edit needs a revision is revision ``0001``:
    it calls ``Base.metadata.create_all()`` rather than shipping frozen DDL,
    so the baseline a FRESH install receives tracks the model files as
    they are today, while an existing user only replays the numbered
    revisions. Without the revision the two populations silently diverge.
    A model edit that creates exactly the same SQLite schema is exempt,
    verified from both committed source trees in isolated processes.

    An added revision on its own is fine and is not reported -- data
    migrations and backfills touch no ORM class. So is an edit that
    changes no code: ``drop_comment_only_edits`` compares the two ASTs
    with docstrings stripped, so rewording a comment in a model file or in
    a shipped revision is not a schema change. Both callers filter first,
    for the same reason they share the rule.

    What this does NOT check, in either caller, is whether an added
    revision has anything to do with the model change it is paired with;
    nothing in this repository does. See
    ``test_upgrade_from_pre_migration_install.py::
    test_pr_edits_no_shipped_revision_and_ships_a_migration``.
    """
    base = _require_merge_base()
    source_files = _guarded_source_files()
    assert len(source_files) >= MIN_MODEL_FILES, (
        "discovery collapsed; see "
        "test_the_schema_and_its_source_files_are_actually_discoverable"
    )

    revisions_before = _blobs_under(base, MIGRATIONS_SUBTREE)
    assert len(revisions_before) >= MIN_REVISIONS, (
        f"only {len(revisions_before)} file(s) under {MIGRATIONS_SUBTREE} "
        f"at {base} -- the baseline tree did not resolve, so an edited "
        "migration would go unnoticed"
    )

    read_pair = _source_pair_reader(base, "HEAD")
    model_before = _source_file_blobs(base, source_files)
    model_after = _source_file_blobs("HEAD", source_files)
    model_changes = drop_proven_schema_neutral_edits(
        drop_comment_only_edits(classify(model_before, model_after), read_pair),
        model_before,
        model_after,
    )
    revision_changes = drop_comment_only_edits(
        classify(revisions_before, _blobs_under("HEAD", MIGRATIONS_SUBTREE)),
        read_pair,
    )

    problems = violations(
        model_changes,
        revision_changes,
        schema_unchanged=model_edits_preserve_schema(
            REPO_ROOT, base, "HEAD", model_changes, revision_changes
        ),
    )
    assert problems == [], "\n".join(
        ["this branch breaks the schema-change rule:", *problems]
    )


def test_revision_0001_builds_the_baseline_from_live_model_metadata():
    """Why the byte-identity guarantee above is load-bearing.

    If 0001 were a frozen DDL script, a model edit would only ever cause
    ordinary schema drift, caught by
    ``test_migrations_produce_schema_matching_models``. It is not: it
    calls ``Base.metadata.create_all()``, so the fresh-install baseline
    tracks the models silently and drift appears only *between* fresh and
    upgraded installs -- which no single-database test can see. Should
    0001 ever be rewritten as explicit ``op.create_table`` calls, this
    fails and the reasoning in this file needs revisiting.
    """
    source = (
        get_migrations_dir() / "versions" / "0001_initial_schema.py"
    ).read_text(encoding="utf-8")
    tree = ast.parse(source)

    upgrade = next(
        (
            node
            for node in tree.body
            if isinstance(node, ast.FunctionDef) and node.name == "upgrade"
        ),
        None,
    )
    assert upgrade is not None, "0001 has no upgrade() function"

    calls = {
        ast.unparse(node.func)
        for node in ast.walk(upgrade)
        if isinstance(node, ast.Call)
    }
    creates_from_metadata = any(
        call.endswith("metadata.create_all") or call.endswith("tables.create")
        for call in calls
    )
    assert creates_from_metadata, (
        "0001 no longer creates the baseline from Base.metadata "
        f"(calls: {sorted(calls)}). That is a good change, but the "
        "fresh-vs-upgraded divergence argument in this module's docstring, "
        "in schema_change_rule.violations() and in test_this_branch_edits_"
        "no_revision_and_migrates_every_model_change was written against "
        "create_all(); re-check it."
    )


def test_every_table_declared_under_src_is_either_migrated_or_a_separate_db():
    """A model file that is never imported is a schema that never ships.

    ``Base.metadata`` only contains what got imported. A new model file
    that nobody imports contributes no table, so Alembic cannot see it,
    ``0001`` will not create it, and the first query against it fails at
    runtime. Scanning the source for ``__tablename__`` and reconciling
    against the live metadata catches that, and the two files that
    legitimately sit outside the chain are re-justified from their own
    source rather than trusted.
    """
    declared = {}
    for path in SRC_ROOT.rglob("*.py"):
        text = path.read_text(encoding="utf-8")
        if "__tablename__" not in text:
            continue
        tree = ast.parse(text)
        names = {
            node.value.value
            for node in ast.walk(tree)
            if isinstance(node, ast.Assign)
            and any(
                isinstance(t, ast.Name) and t.id == "__tablename__"
                for t in node.targets
            )
            and isinstance(node.value, ast.Constant)
            and isinstance(node.value.value, str)
        }
        if names:
            declared[str(path.relative_to(REPO_ROOT))] = names

    assert len(declared) >= MIN_MODEL_FILES, (
        f"only {len(declared)} file(s) under {SRC_ROOT} declare "
        "__tablename__ -- the source scan found nothing to reconcile"
    )

    known_tables = set(Base.metadata.tables)
    unreachable = {
        path: sorted(names - known_tables)
        for path, names in declared.items()
        if names - known_tables and path not in NON_ALEMBIC_TABLE_FILES
    }
    assert unreachable == {}, (
        f"table(s) declared in source but absent from Base.metadata: "
        f"{unreachable}. Alembic migrates Base.metadata, so these tables "
        "are never created by 0001 and never seen by autogenerate. Import "
        "the model in database/models/__init__.py, or -- if it belongs to "
        "a different database -- give it its own DeclarativeBase and add "
        "it to NON_ALEMBIC_TABLE_FILES."
    )

    # Re-derive the exemption instead of trusting the list: a file is only
    # off the Alembic chain if it declares its own DeclarativeBase.
    for path in sorted(NON_ALEMBIC_TABLE_FILES):
        full = REPO_ROOT / path
        assert full.exists(), f"exempt file {path} no longer exists"
        tree = ast.parse(full.read_text(encoding="utf-8"))
        declares_own_base = any(
            isinstance(node, ast.ClassDef)
            and any(
                isinstance(b, ast.Name) and b.id == "DeclarativeBase"
                for b in node.bases
            )
            for node in ast.walk(tree)
        )
        assert declares_own_base, (
            f"{path} is exempted from the Alembic chain but no longer "
            "declares its own DeclarativeBase -- if its models now sit on "
            "the shared Base they belong in a migration."
        )


# ---------------------------------------------------------------------------
# 3. PRAGMA foreign_keys = ON, on every path that opens a user database
# ---------------------------------------------------------------------------


def test_the_places_that_toggle_foreign_key_enforcement_are_the_known_three():
    """Pin every executed ``PRAGMA foreign_keys`` toggle in the package.

    SQLite defaults ``foreign_keys`` to OFF, per connection, so the 49
    ``ondelete=`` clauses on the models depend entirely on who sets it.
    Four places legitimately move it, and they have to be told apart:
    ``apply_performance_pragmas`` turns it ON at connect time for every
    connection; ``_disable_fk_for_migration`` turns it OFF for the
    migration window only; revision ``0007``'s ``upgrade()`` turns it OFF
    defensively for its orphan scrub and relies on the runner to put it
    back; ``_restore_fk_after_migration`` is what puts it back, before
    the connection goes to the pool. A fifth would mean some connection
    is configured somewhere nobody is tracking.

    Prose in docstrings is excluded by construction: only string
    constants passed as call arguments count.
    """
    toggles = {}
    for path in SRC_ROOT.rglob("*.py"):
        tree = ast.parse(path.read_text(encoding="utf-8"))
        for qualname, func in _iter_functions(tree):
            for node in ast.walk(func):
                if not isinstance(node, ast.Call):
                    continue
                for arg in node.args:
                    if not (
                        isinstance(arg, ast.Constant)
                        and isinstance(arg.value, str)
                    ):
                        continue
                    lowered = arg.value.lower().replace(" ", "")
                    if lowered in (
                        "pragmaforeign_keys=on",
                        "pragmaforeign_keys=off",
                    ):
                        key = f"{path.relative_to(REPO_ROOT)}::{qualname}"
                        value = "OFF" if lowered.endswith("=off") else "ON"
                        toggles.setdefault(key, set()).add(value)

    expected = {
        "src/local_deep_research/database/sqlcipher_utils.py"
        "::apply_performance_pragmas": {"ON"},
        "src/local_deep_research/database/alembic_runner.py"
        "::_disable_fk_for_migration": {"OFF"},
        "src/local_deep_research/database/alembic_runner.py"
        "::_restore_fk_after_migration": {"ON"},
        "src/local_deep_research/database/migrations/versions"
        "/0007_backfill_missing_indexes.py::upgrade": {"OFF"},
    }
    assert toggles == expected, (
        "the set of functions that toggle PRAGMA foreign_keys changed.\n"
        f"  found:    {dict(sorted(toggles.items()))}\n"
        f"  expected: {dict(sorted(expected.items()))}\n"
        "A new place that turns it ON means connections are configured "
        "outside the one connect-time helper; a new place that turns it "
        "OFF means some connection may go back to the pool with "
        "enforcement disabled, silently ignoring every ondelete= on the "
        "models for the rest of the process."
    )

    helper = _helpers_by_name().get(PRAGMA_HELPER)
    assert helper is not None, (
        f"{PRAGMA_HELPER}() no longer exists in the database package"
    )


def test_disabling_fk_for_a_migration_is_always_paired_with_restoring_it():
    """The OFF window must not survive a failed migration.

    ``run_migrations`` turns enforcement off for the whole upgrade. The
    connection then goes back to the pool. If the restore only ran on the
    success path, a migration that raised would leave an FK-disabled
    handle in the pool, and every ``ondelete=`` would be inert for
    whichever request picked it up next -- with no error anywhere.
    """
    tree = ast.parse(
        (DATABASE_PKG / "alembic_runner.py").read_text(encoding="utf-8")
    )
    callers = [
        (qualname, func)
        for qualname, func in _iter_functions(tree)
        if "_disable_fk_for_migration"
        in {
            node.func.id
            for node in ast.walk(func)
            if isinstance(node, ast.Call) and isinstance(node.func, ast.Name)
        }
    ]
    assert callers, (
        "nothing calls _disable_fk_for_migration any more -- either the "
        "migration FK window was removed (good, delete this test) or the "
        "scan broke"
    )

    unpaired = []
    for qualname, func in callers:
        paired = False
        for node in ast.walk(func):
            if not isinstance(node, ast.Try):
                continue
            disabled_here = any(
                isinstance(inner, ast.Call)
                and isinstance(inner.func, ast.Name)
                and inner.func.id == "_disable_fk_for_migration"
                for stmt in node.body
                for inner in ast.walk(stmt)
            )
            if not disabled_here:
                continue
            for branch in (node.handlers, node.orelse, node.finalbody):
                restored = any(
                    "_restore_fk_after_migration" in ast.unparse(stmt)
                    for stmt in branch
                )
                if restored:
                    paired = True
        if not paired:
            unpaired.append(qualname)

    assert unpaired == [], (
        f"{unpaired} disables FK enforcement without restoring it on the "
        "failure path. A migration that raises then returns an "
        "FK-disabled connection to the pool, and every ondelete= on the "
        "models is silently inert for whatever runs next."
    )


def test_the_set_of_connection_opening_functions_is_the_known_one():
    """A ratchet: a new connection path must be classified, not assumed.

    ``PRAGMA foreign_keys`` is connection state, so "it is set" is only
    true of paths that set it. Enumerating the paths is the only way to
    say "every". A new ``create_engine`` or ``.connect()`` in this
    package fails here until it is listed and classified.
    """
    inventory = _connection_site_inventory()
    assert len(inventory) >= 5, (
        f"the AST scan found only {len(inventory)} connection site(s) in "
        f"{DATABASE_PKG} -- the scan is broken, not the code"
    )

    found = set(inventory)
    known = set(KNOWN_CONNECTION_SITES)
    assert found == known, (
        f"connection sites appeared or vanished.\n"
        f"  new (classify these): {sorted(found - known)}\n"
        f"  gone (drop these):    {sorted(known - found)}\n"
        "A new path that opens a user database must call "
        f"{PRAGMA_HELPER}() or every ondelete= on the models is inert on "
        "the connections it hands out."
    )


def test_sqlcipher_wrapper_has_only_classified_callers():
    """The lifecycle wrapper delegates one open to classified call sites."""
    inventory = _connection_site_inventory()
    wrapper_site = "database/sqlcipher_compat.py::connect_sqlcipher"
    _wrapper, calls = inventory[wrapper_site]
    assert [kind for kind, _call in calls] == ["driver.connect"]

    callers = {
        site
        for site, (_func, site_calls) in inventory.items()
        if any(kind == "connect_sqlcipher" for kind, _call in site_calls)
    }
    expected_callers = {
        "database/backup/backup_service.py::BackupService._open_backup_with_current_key",
        "database/backup/backup_service.py::BackupService._verify_backup",
        "database/encrypted_db.py::DatabaseManager._check_encryption_available",
        "database/encrypted_db.py::DatabaseManager._make_sqlcipher_connection",
        "database/sqlcipher_utils.py::create_sqlcipher_connection",
    }
    assert callers == expected_callers
    assert all(
        KNOWN_CONNECTION_SITES[site] in ("user-db", "exempt:backup-verify")
        for site in callers
    )


def test_every_user_database_connection_path_reaches_the_pragma_helper():
    """The routing question, answered for each user-database path."""
    inventory = _connection_site_inventory()
    helpers = _helpers_by_name()

    user_db_sites = [
        site
        for site, kind in KNOWN_CONNECTION_SITES.items()
        if kind == "user-db"
    ]
    assert len(user_db_sites) >= 4, (
        "the user-db classification collapsed; nothing would be checked"
    )

    unreachable = []
    for site in sorted(user_db_sites):
        func, _ = inventory[site]
        if not _reaches_pragma_helper(func, helpers):
            unreachable.append(site)

    assert unreachable == [], (
        f"user-database connection path(s) that never reach {PRAGMA_HELPER}: "
        f"{unreachable}. Connections handed out by these functions run with "
        "SQLite's default PRAGMA foreign_keys = OFF, so every "
        "ondelete='CASCADE'/'SET NULL' on the models does nothing: deleting "
        "a parent row leaves orphans behind instead of cascading."
    )


def test_both_branches_of_each_user_database_engine_configure_connections():
    """The unencrypted fallback is a second path, not a footnote.

    ``_create_user_database_files`` and ``_open_user_database_cold`` each build
    an Engine twice: once with a SQLCipher ``creator=`` and once, when
    SQLCipher is unavailable, over a plain ``sqlite:///`` URL. Reaching
    the pragma helper *somewhere* in the function would be satisfied by
    the encrypted branch alone, so check each ``create_engine`` call
    separately: it must either supply its own ``creator=`` or have a
    ``connect`` listener registered in the same branch.
    """
    inventory = _connection_site_inventory()
    checked = 0
    unconfigured = []

    for site, kind in sorted(KNOWN_CONNECTION_SITES.items()):
        if kind != "user-db":
            continue
        func, calls = inventory[site]
        for call_kind, call in calls:
            if call_kind != "create_engine":
                continue
            checked += 1
            has_creator = any(kw.arg == "creator" for kw in call.keywords)
            block = _innermost_block(func, call)
            has_listener = block is not None and any(
                isinstance(node, ast.Call)
                and isinstance(node.func, ast.Attribute)
                and node.func.attr == "listen"
                and any(
                    isinstance(arg, ast.Constant) and arg.value == "connect"
                    for arg in node.args
                )
                for stmt in block
                for node in ast.walk(stmt)
            )
            if not (has_creator or has_listener):
                unconfigured.append(f"{site} line {call.lineno}")

    assert checked >= 4, (
        f"only {checked} create_engine call(s) inspected on user-database "
        "paths -- expected the encrypted and unencrypted branches of both "
        "_create_user_database_files and _open_user_database_cold"
    )
    assert unconfigured == [], (
        "create_engine call(s) on a user-database path with neither a "
        f"creator= nor a 'connect' listener: {unconfigured}. Connections "
        "from these engines are configured by nothing, so PRAGMA "
        "foreign_keys stays OFF on them."
    )


def test_the_auth_database_exemption_is_still_justified():
    """auth_db skips the helper; that is only safe while it has no FKs.

    The auth database holds the ``users`` table alone -- deliberately
    excluded from the user-database migrations. It needs no FK
    enforcement because it declares no foreign keys. Derive that rather
    than assert it: the day someone adds an FK there, the exemption is
    wrong and this fails.
    """
    from local_deep_research.database.models.auth import User

    exempt = [
        site
        for site, kind in KNOWN_CONNECTION_SITES.items()
        if kind == "exempt:auth-db"
    ]
    assert exempt, "the auth-db exemption vanished; re-check the inventory"

    foreign_keys = sorted(
        str(fk.target_fullname) for fk in User.__table__.foreign_keys
    )
    assert foreign_keys == [], (
        f"the auth database's users table now declares foreign keys "
        f"({foreign_keys}), but its engines in auth_db.py never call "
        f"{PRAGMA_HELPER}() -- so those constraints are unenforced. Either "
        "apply the pragma there or drop the FK."
    )


def test_the_backup_verify_exemption_is_still_justified():
    """``_verify_backup`` opens a backup file, not a user database.

    It exists to prove a backup decrypts, and closes the connection
    inside the function. FK enforcement is irrelevant to a connection
    that never leaves. Derived from the source: if it ever starts
    returning or storing the connection, the exemption stops holding.
    """
    inventory = _connection_site_inventory()
    site = "database/backup/backup_service.py::BackupService._verify_backup"
    assert site in inventory, "the backup-verify site vanished"

    func, calls = inventory[site]
    assert calls, "no connection call found in _verify_backup"

    returns_a_connection = any(
        isinstance(node, ast.Return)
        and node.value is not None
        and "conn" in ast.unparse(node.value)
        for node in ast.walk(func)
    )
    assert not returns_a_connection, (
        "_verify_backup now hands its connection back to a caller, so it is "
        "no longer a self-contained check and must configure the connection "
        f"through {PRAGMA_HELPER}()"
    )
    closes = _mentioned_names(func) & {"close", "safe_close"}
    assert closes, (
        "_verify_backup no longer closes the connection it opens, so it can "
        "escape the function after all and the exemption stops holding"
    )


# ---------------------------------------------------------------------------
# 4. no migration may assume a plaintext SQLite file
# ---------------------------------------------------------------------------


def test_backup_key_probe_connection_never_leaves_its_classifier():
    """``_open_backup_with_current_key`` opens a backup, not a user database.

    It returns its raw connection, so the backup-verify exemption holds only
    while its sole caller is ``_classify_backup_key``, which closes every
    connection it gets and never returns or stores one. FK enforcement is
    irrelevant to a connection that never leaves that classifier.
    """
    path = DATABASE_PKG / "backup" / "backup_service.py"
    tree = ast.parse(path.read_text(encoding="utf-8"))
    callers = {
        func.name
        for func in ast.walk(tree)
        if isinstance(func, (ast.FunctionDef, ast.AsyncFunctionDef))
        for node in ast.walk(func)
        if isinstance(node, ast.Attribute)
        and node.attr == "_open_backup_with_current_key"
    }
    assert callers == {"_classify_backup_key"}, (
        "the backup key probe gained a caller; it hands back a raw backup "
        f"connection, so classify that caller first: {sorted(callers)}"
    )
    classifier = next(
        func
        for func in ast.walk(tree)
        if isinstance(func, ast.FunctionDef)
        and func.name == "_classify_backup_key"
    )
    leaks = [
        ast.unparse(node)
        for node in ast.walk(classifier)
        if (
            isinstance(node, ast.Return)
            and node.value is not None
            and "conn" in ast.unparse(node.value)
        )
        or (
            isinstance(node, ast.Assign)
            and any(
                isinstance(target, ast.Attribute) for target in node.targets
            )
            and "conn" in ast.unparse(node.value)
        )
    ]
    assert not leaks, (
        "_classify_backup_key now returns or stores its backup connection, "
        f"so the exemption stops holding: {leaks}"
    )
    closes = [
        node
        for node in ast.walk(classifier)
        if isinstance(node, ast.Call)
        and ast.unparse(node.func) in {"safe_close", "conn.close"}
    ]
    opens = [
        node
        for node in ast.walk(classifier)
        if isinstance(node, ast.Call)
        and ast.unparse(node.func).endswith("_open_backup_with_current_key")
    ]
    assert len(closes) >= len(opens) > 0, (
        "_classify_backup_key no longer closes every backup connection it opens"
    )


def test_no_migration_opens_a_database_of_its_own():
    """Every user database on this project is (or may be) SQLCipher.

    Alembic hands each revision a keyed connection via ``op.get_bind()``.
    A revision that opens its own ``sqlite3`` connection, builds an
    Engine, ``ATTACH``es a second file or runs ``VACUUM INTO`` steps
    outside that key: against an encrypted database the connection fails
    to decrypt, and the file it writes would be plaintext.
    """
    banned_calls = {"create_engine", "connect", "sessionmaker"}
    banned_sql = ("attach database", "vacuum into", "pragma key")
    offenders = []
    scanned = 0
    uses_get_bind = 0

    for path in _revision_files():
        scanned += 1
        text = path.read_text(encoding="utf-8")
        tree = ast.parse(text)

        for node in ast.walk(tree):
            if isinstance(node, (ast.Import, ast.ImportFrom)):
                module = getattr(node, "module", None) or ""
                names = [alias.name for alias in node.names]
                if module == "sqlite3" or "sqlite3" in names:
                    offenders.append(f"{path.name}: imports sqlite3")
            if isinstance(node, ast.Call):
                target = node.func
                name = (
                    target.id
                    if isinstance(target, ast.Name)
                    else getattr(target, "attr", None)
                )
                if name in banned_calls:
                    offenders.append(
                        f"{path.name}:{node.lineno}: calls {name}()"
                    )
            if isinstance(node, ast.Constant) and isinstance(node.value, str):
                lowered = node.value.lower()
                for phrase in banned_sql:
                    if phrase in lowered:
                        offenders.append(
                            f"{path.name}:{node.lineno}: SQL contains "
                            f"{phrase!r}"
                        )

        if "get_bind()" in text:
            uses_get_bind += 1

    assert scanned >= MIN_REVISIONS, (
        f"only {scanned} revision file(s) scanned -- the scan found nothing "
        "to check, so it would pass on an empty directory"
    )
    assert uses_get_bind >= MIN_REVISIONS // 2, (
        f"only {uses_get_bind} of {scanned} revisions mention op.get_bind() "
        "-- the scan is not reading real migration bodies"
    )
    assert offenders == [], (
        "migration(s) that step outside the connection Alembic supplied: "
        f"{offenders}. On a SQLCipher database these either fail to open "
        "the file or write an unencrypted one. Use op.get_bind()."
    )


def _declared_revisions(versions_dir):
    """Map each ``revision`` id declared under *versions_dir* to its files.

    Read with ``ast`` rather than through Alembic's ``ScriptDirectory``.
    When two files declare the same revision id, Alembic only emits a
    warning and keeps whichever file it loaded last, so a chain walked
    through Alembic shows one revision and the other file's migration is
    silently never run. A file whose ``revision`` is missing or is not a
    string literal maps to ``None``, so it cannot slip past the check.
    """
    declared = {}
    for path in sorted(Path(versions_dir).glob("*.py")):
        if path.name.startswith("__"):
            continue
        tree = ast.parse(path.read_text(encoding="utf-8"), filename=str(path))
        values = []
        for node in tree.body:
            if isinstance(node, ast.Assign):
                targets, value = node.targets, node.value
            elif isinstance(node, ast.AnnAssign) and node.value is not None:
                targets, value = [node.target], node.value
            else:
                continue
            if any(
                isinstance(t, ast.Name) and t.id == "revision" for t in targets
            ):
                values.append(
                    value.value
                    if isinstance(value, ast.Constant)
                    and isinstance(value.value, str)
                    else None
                )
        revision = values[-1] if len(values) == 1 else None
        declared.setdefault(revision, []).append(path.name)
    return declared


def test_no_two_migration_files_declare_the_same_revision_id():
    """Every file under ``versions/`` declares its own revision id.

    Two branches that each add the next-numbered migration (for example
    two PRs that both ship revision ``"0032"`` on ``down_revision
    "0031"``) merge without a textual conflict because the filenames
    differ. Alembic then warns about the duplicate and loads only one of
    them, so the other migration never runs and every chain-shape check
    that walks Alembic's script directory still passes. Parsing the files
    directly makes the collision a test failure.
    """
    declared = _declared_revisions(get_migrations_dir() / "versions")

    assert sum(len(files) for files in declared.values()) >= MIN_REVISIONS, (
        f"only {sum(len(f) for f in declared.values())} revision file(s) "
        "parsed -- the versions directory did not resolve, so the check "
        "below would pass on nothing"
    )
    assert None not in declared, (
        "migration file(s) without exactly one string-literal "
        f"'revision = \"...\"' assignment: {declared.get(None)}"
    )
    duplicates = {
        revision: files
        for revision, files in declared.items()
        if len(files) > 1
    }
    assert duplicates == {}, (
        "migration files that share a revision id; Alembic keeps only one "
        f"of each and silently drops the rest: {duplicates}. Renumber the "
        "later one onto the current head."
    )