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.

43.1 KB
Raw Blame
"""
Tests for migration 0021: Add notes feature tables.

Covers:
- All 5 note tables are created (note_versions, note_links, note_research,
  note_syntheses, note_synthesis_sources).
- Key columns (types, nullability) on each table.
- Named constraints exist (CHECK for change_type and self-links; UNIQUEs).
- Downgrade drops every table.
- Idempotency: re-running upgrade on an already-migrated DB is a no-op.
"""

import pytest
from alembic import command
from sqlalchemy import create_engine, event, inspect, text
from sqlalchemy.exc import IntegrityError

from local_deep_research.database.alembic_runner import get_alembic_config


NOTE_TABLES = [
    "note_versions",
    "note_links",
    "note_research",
    "note_syntheses",
    "note_synthesis_sources",
]


def _upgrade_to(engine, revision):
    config = get_alembic_config(engine)
    with engine.begin() as conn:
        config.attributes["connection"] = conn
        command.upgrade(config, revision)


def _downgrade_to(engine, revision):
    config = get_alembic_config(engine)
    with engine.begin() as conn:
        config.attributes["connection"] = conn
        command.downgrade(config, revision)


def _get_columns(engine, table):
    insp = inspect(engine)
    if not insp.has_table(table):
        return {}
    return {col["name"]: col for col in insp.get_columns(table)}


def _get_unique_constraint_names(engine, table):
    insp = inspect(engine)
    if not insp.has_table(table):
        return set()
    names = set()
    for u in insp.get_unique_constraints(table):
        if u.get("name"):
            names.add(u["name"])
    return names


def _get_check_constraint_sqls(engine, table):
    """Return raw CREATE TABLE SQL for inspecting CHECK constraints on SQLite."""
    with engine.connect() as conn:
        row = conn.execute(
            text(
                "SELECT sql FROM sqlite_master WHERE type='table' AND name=:n"
            ),
            {"n": table},
        ).first()
    return row[0] if row else ""


@pytest.fixture
def fresh_engine(tmp_path):
    db_path = tmp_path / "fresh_note_migration_test.db"
    engine = create_engine(f"sqlite:///{db_path}")
    yield engine
    engine.dispose()


@pytest.fixture
def upgraded_engine(fresh_engine):
    _upgrade_to(fresh_engine, "0021")
    return fresh_engine


# ---------------------------------------------------------------------------
# FK-ON variant of the engine fixtures.
#
# IMPORTANT: do NOT fold this into fresh_engine / upgraded_engine. Several
# enforcement tests (TestCheckConstraintEnforcement) deliberately rely on
# SQLite's default PRAGMA foreign_keys=OFF so a bogus document_id reaches the
# CHECK instead of tripping an FK error first. This is a separate engine that
# turns FK enforcement ON for every connection, so the migration-built note
# schema's ondelete actions (CASCADE / SET NULL) are actually exercised.
#
# SQLite caveat: foreign_keys is a per-connection PRAGMA, OFF by default and
# NOT persisted in the DB file. It must be re-set on every new DBAPI
# connection — hence the SQLAlchemy "connect" event below rather than a
# one-shot statement. With the connection-pool that create_engine uses, a
# fresh connection (or a recycled one) that skipped the PRAGMA would silently
# ignore the FKs, so the event handler is the only reliable hook.
# ---------------------------------------------------------------------------


@pytest.fixture
def fk_engine(tmp_path):
    db_path = tmp_path / "fk_0021_test.db"
    engine = create_engine(f"sqlite:///{db_path}")

    @event.listens_for(engine, "connect")
    def _enable_sqlite_fk(dbapi_connection, _connection_record):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()

    yield engine
    engine.dispose()


@pytest.fixture
def upgraded_fk_engine(fk_engine):
    _upgrade_to(fk_engine, "0021")
    # Verify the pragma genuinely took effect on a real connection — some
    # SQLite builds compile out FK support entirely (PRAGMA stays 0). If that
    # ever happens the cascade tests would pass vacuously, so assert it here.
    with fk_engine.connect() as conn:
        assert conn.execute(text("PRAGMA foreign_keys")).scalar() == 1, (
            "PRAGMA foreign_keys did not turn on — FK cascade tests would be "
            "vacuous on this SQLite build"
        )
    return fk_engine


def _seed_parents(conn, documents, *, with_research=False):
    """Insert the minimal parent rows the note FKs point at.

    note_* tables reference documents.id (and note_research references
    research_history.id). documents in turn requires a source_types row and a
    fistful of NOT NULL columns. Insert exactly enough to satisfy the on-disk
    NOT NULL / FK constraints so the only thing a delete can trip is the
    note-table ondelete action under test.
    """
    conn.execute(
        text(
            "INSERT INTO source_types (id, name, display_name, created_at) "
            "VALUES ('st1', 'note', 'Note', CURRENT_TIMESTAMP)"
        )
    )
    if with_research:
        conn.execute(
            text(
                "INSERT INTO research_history "
                "(id, query, mode, status, created_at) "
                "VALUES ('r1', 'q', 'm', 's', CURRENT_TIMESTAMP)"
            )
        )
    for doc_id in documents:
        # research_id is nullable on documents, so leave it NULL — the note
        # FKs only need documents.id to exist.
        conn.execute(
            text(
                "INSERT INTO documents "
                "(id, source_type_id, document_hash, file_size, file_type, "
                "status, processed_at, created_at, updated_at) VALUES "
                "(:id, 'st1', :hash, 0, 'note', 'done', CURRENT_TIMESTAMP, "
                "CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)"
            ),
            {"id": doc_id, "hash": "h-" + doc_id},
        )


# ---------------------------------------------------------------------------
# Tables + columns
# ---------------------------------------------------------------------------


class TestTablesAndColumns:
    def test_all_note_tables_exist_after_upgrade(self, upgraded_engine):
        insp = inspect(upgraded_engine)
        for table in NOTE_TABLES:
            assert insp.has_table(table), f"missing table {table}"

    def test_note_versions_columns(self, upgraded_engine):
        cols = _get_columns(upgraded_engine, "note_versions")
        expected = {
            "id",
            "document_id",
            "title",
            "content",
            "tags",
            "change_type",
            "change_summary",
            "content_hash",
            "created_at",
        }
        assert expected.issubset(set(cols)), (
            f"missing cols: {expected - set(cols)}"
        )
        # tags is NOT NULL with a default (non-null server default)
        assert cols["tags"]["nullable"] is False
        # change_type NOT NULL
        assert cols["change_type"]["nullable"] is False
        # change_summary nullable (LLM-generated, optional)
        assert cols["change_summary"]["nullable"] is True
        # content NOT NULL
        assert cols["content"]["nullable"] is False

    def test_note_links_columns(self, upgraded_engine):
        cols = _get_columns(upgraded_engine, "note_links")
        for name in (
            "source_document_id",
            "target_document_id",
            "link_text",
            "auto_suggested",
            "created_at",
        ):
            assert name in cols
            # all of these are NOT NULL
            assert cols[name]["nullable"] is False, f"{name} must be NOT NULL"

    def test_note_research_columns(self, upgraded_engine):
        cols = _get_columns(upgraded_engine, "note_research")
        for name in (
            "document_id",
            "research_id",
            "display_order",
            "is_collapsed",
            "created_at",
        ):
            assert name in cols
            assert cols[name]["nullable"] is False, f"{name} must be NOT NULL"

    def test_note_syntheses_columns(self, upgraded_engine):
        cols = _get_columns(upgraded_engine, "note_syntheses")
        # old column must be gone
        assert "source_document_ids" not in cols
        # result_document_id is nullable (SET NULL on delete)
        assert cols["result_document_id"]["nullable"] is True
        assert cols["synthesis_type"]["nullable"] is False

    def test_synthesis_type_check_constraint_present(self, upgraded_engine):
        """The migration must emit an explicit CHECK for synthesis_type
        alongside the Enum column — values_callable on the ORM enum
        suppresses the auto-CHECK, so this explicit one is the sole
        DB-level guard.
        """
        sql = _get_check_constraint_sqls(upgraded_engine, "note_syntheses")
        assert "ck_note_synthesis_type" in sql, (
            "ck_note_synthesis_type CHECK missing from note_syntheses"
        )
        for value in ("merge", "summarize", "compare"):
            assert value in sql

    def test_note_synthesis_sources_columns(self, upgraded_engine):
        cols = _get_columns(upgraded_engine, "note_synthesis_sources")
        for name in (
            "synthesis_id",
            "source_document_id",
            "created_at",
        ):
            assert name in cols
            assert cols[name]["nullable"] is False, f"{name} must be NOT NULL"


# ---------------------------------------------------------------------------
# Constraints (CHECK + UNIQUE)
# ---------------------------------------------------------------------------


class TestConstraints:
    def test_change_type_check_constraint_present(self, upgraded_engine):
        sql = _get_check_constraint_sqls(upgraded_engine, "note_versions")
        assert "ck_note_change_type" in sql, "ck_note_change_type CHECK missing"
        # values present in the literal
        for value in (
            "initial",
            "auto_save",
            "manual_save",
            "pre_restore",
            "restore",
        ):
            assert value in sql

    def test_no_self_link_check_constraint_present(self, upgraded_engine):
        sql = _get_check_constraint_sqls(upgraded_engine, "note_links")
        assert "ck_note_link_no_self" in sql
        assert (
            "source_document_id != target_document_id" in sql
            or "source_document_id<>target_document_id" in sql
        )

    def test_note_links_unique(self, upgraded_engine):
        names = _get_unique_constraint_names(upgraded_engine, "note_links")
        assert "uix_note_link" in names

    def test_note_versions_unique(self, upgraded_engine):
        names = _get_unique_constraint_names(upgraded_engine, "note_versions")
        assert "uix_note_version_content" in names

    def test_note_research_unique(self, upgraded_engine):
        names = _get_unique_constraint_names(upgraded_engine, "note_research")
        assert "uix_note_research" in names
        # The display-order UNIQUE was folded into this migration when the
        # feature's two migrations were consolidated to one (it formerly
        # lived in 0021). Pin it here so a regression that drops it from
        # the note_research create_table is caught at the migration level,
        # not only by the concurrency stress test.
        assert "uix_note_research_display_order" in names

    def test_note_synthesis_sources_unique(self, upgraded_engine):
        names = _get_unique_constraint_names(
            upgraded_engine, "note_synthesis_sources"
        )
        assert "uix_note_synthesis_source" in names


# ---------------------------------------------------------------------------
# Downgrade
# ---------------------------------------------------------------------------


class TestDowngrade:
    def test_downgrade_to_0020_drops_all_note_tables(self, upgraded_engine):
        # Downgrade to 0020 — one step back from 0021 (this notes
        # migration), so only this migration's downgrade() runs. Stop at
        # 0020 (add_zotero_tables): going further eventually hits chat's
        # NotImplementedError downgrade, which is out of scope here.
        _downgrade_to(upgraded_engine, "0020")
        insp = inspect(upgraded_engine)
        for table in NOTE_TABLES:
            assert not insp.has_table(table), (
                f"table {table} was not dropped on downgrade"
            )

    def test_downgrade_also_clears_legacy_note_templates(self, fresh_engine):
        """A stray `note_templates` from the pre-cleanup version of this
        migration (in an earlier numbering) should be removed by the new
        downgrade, so devs don't end up with an orphan.
        """
        _upgrade_to(fresh_engine, "0021")
        # Simulate a legacy orphan.
        with fresh_engine.begin() as conn:
            conn.execute(
                text(
                    "CREATE TABLE IF NOT EXISTS note_templates ("
                    "id TEXT PRIMARY KEY)"
                )
            )
        insp = inspect(fresh_engine)
        assert insp.has_table("note_templates")

        _downgrade_to(fresh_engine, "0020")

        insp = inspect(fresh_engine)
        assert not insp.has_table("note_templates")


# ---------------------------------------------------------------------------
# Idempotency
# ---------------------------------------------------------------------------


class TestIdempotency:
    def test_re_running_upgrade_is_safe(self, upgraded_engine):
        """Running the upgrade again (has_table guards) should not error."""
        # Re-run upgrade explicitly; already at head, so no-op, but
        # re-running the step-up path via downgrade/upgrade exercises guards.
        _downgrade_to(upgraded_engine, "0020")
        _upgrade_to(upgraded_engine, "0021")
        _upgrade_to(upgraded_engine, "0021")  # second call — no-op
        insp = inspect(upgraded_engine)
        for table in NOTE_TABLES:
            assert insp.has_table(table)


# ---------------------------------------------------------------------------
# Drift guards — Enum ⇄ model CHECK ⇄ migration CHECK parity
# ---------------------------------------------------------------------------


class TestEnumCheckParity:
    """Prevent the class of drift where a Python enum member is added but
    the matching DB-level CHECK constraints are forgotten. Adding a 6th
    NoteChangeType value without updating both CHECK literals would let
    Python-level writes succeed and DB inserts silently IntegrityError in
    production only — this test catches that by comparing all three sets.
    """

    def _extract_values_from_check_sql(self, sql, column):
        """Pull 'a', 'b', ... from  `column IN ('a', 'b', ...)` case-agnostic."""
        import re

        m = re.search(
            rf"{re.escape(column)}\s+IN\s*\(([^)]*)\)",
            sql or "",
            re.IGNORECASE,
        )
        if not m:
            return set()
        return set(re.findall(r"'([^']+)'", m.group(1)))

    def test_note_change_type_values_match_model_and_migration(
        self, upgraded_engine
    ):
        from local_deep_research.database.models.note import NoteChangeType

        enum_values = {m.value for m in NoteChangeType}

        # Migration-declared CHECK (DB-level ground truth on fresh installs
        # where 0001 create_all skips 0021's create_table bodies).
        migration_sql = _get_check_constraint_sqls(
            upgraded_engine, "note_versions"
        )
        migration_values = self._extract_values_from_check_sql(
            migration_sql, "change_type"
        )

        # Model CHECK text (string source-of-truth in note.py __table_args__).
        from local_deep_research.database.models.note import NoteVersion

        model_values = set()
        for constraint in NoteVersion.__table_args__:
            # CheckConstraint exposes its SQL via .sqltext
            sqltext = getattr(constraint, "sqltext", None)
            if sqltext is not None:
                model_values |= self._extract_values_from_check_sql(
                    str(sqltext), "change_type"
                )

        assert enum_values == model_values, (
            f"NoteChangeType members {enum_values} != "
            f"model CHECK {model_values}"
        )
        assert enum_values == migration_values, (
            f"NoteChangeType members {enum_values} != "
            f"migration CHECK {migration_values}"
        )

    def test_note_synthesis_type_values_match_model_and_migration(
        self, upgraded_engine
    ):
        """Same three-way parity guard as change_type above: Python enum ⇄
        model CHECK text ⇄ migration-emitted CHECK text. When a new
        NoteSynthesisType value lands, all three must be updated together
        or this test fails.
        """
        from local_deep_research.database.models.note import (
            NoteSynthesis,
            NoteSynthesisType,
        )

        enum_values = {m.value for m in NoteSynthesisType}

        migration_sql = _get_check_constraint_sqls(
            upgraded_engine, "note_syntheses"
        )
        migration_values = self._extract_values_from_check_sql(
            migration_sql, "synthesis_type"
        )

        model_values = set()
        for constraint in NoteSynthesis.__table_args__:
            sqltext = getattr(constraint, "sqltext", None)
            if sqltext is not None:
                model_values |= self._extract_values_from_check_sql(
                    str(sqltext), "synthesis_type"
                )

        assert enum_values == model_values, (
            f"NoteSynthesisType members {enum_values} != "
            f"model CHECK {model_values}"
        )
        assert enum_values == migration_values, (
            f"NoteSynthesisType members {enum_values} != "
            f"migration CHECK {migration_values}"
        )


class TestNullabilityRegressions:
    """Belt-and-braces on specific columns whose nullability previously
    drifted between model and migration. These are redundant with the
    general nullability checks in TestTablesAndColumns but call out the
    exact column by name so a future breaking change is grep-visible.
    """

    def test_auto_suggested_is_not_null(self, upgraded_engine):
        # Original model had `Column(Boolean, default=False)` — nullable=True
        # by default — while the migration said nullable=False. Fresh installs
        # got the nullable-True version from 0001's create_all. Don't regress.
        cols = _get_columns(upgraded_engine, "note_links")
        assert cols["auto_suggested"]["nullable"] is False


# ---------------------------------------------------------------------------
# CHECK constraints — actual DB-level enforcement (not just literal parity)
# ---------------------------------------------------------------------------


class TestCheckConstraintEnforcement:
    """The explicit CheckConstraints (change_type, synthesis_type,
    no-self-link) are the SOLE DB-level guard because values_callable on the
    ORM Enum suppresses SQLAlchemy's auto-CHECK. TestEnumCheckParity only
    compares literal SETS, so it stays green even if the constraints were
    removed (empty == empty). These tests prove actual enforcement: bogus /
    self-link rows are rejected, while valid rows commit.

    upgraded_engine does NOT enable PRAGMA foreign_keys, so a bogus
    document_id won't trigger an FK error — the CHECK fires first. Each
    INSERT supplies every NOT NULL column explicitly (the models now carry
    matching server_default= so create_all and the migration DDL agree, but
    supplying values keeps these tests independent of any default) so the
    ONLY constraint a bogus row can violate is the CHECK under test.
    """

    def test_change_type_check_rejects_out_of_enum_value(self, upgraded_engine):
        with pytest.raises(IntegrityError, match="ck_note_change_type"):
            with upgraded_engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_versions "
                        "(id, document_id, title, content, tags, "
                        "change_type, content_hash, created_at) VALUES "
                        "('v1','doc1','t','c','[]','bogus','h1',"
                        "CURRENT_TIMESTAMP)"
                    )
                )

    def test_synthesis_type_check_rejects_out_of_enum_value(
        self, upgraded_engine
    ):
        with pytest.raises(IntegrityError, match="ck_note_synthesis_type"):
            with upgraded_engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_syntheses "
                        "(synthesis_type, created_at) VALUES "
                        "('bogus', CURRENT_TIMESTAMP)"
                    )
                )

    def test_no_self_link_check_rejects_source_equals_target(
        self, upgraded_engine
    ):
        with pytest.raises(IntegrityError, match="ck_note_link_no_self"):
            with upgraded_engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_links "
                        "(source_document_id, target_document_id, link_text, "
                        "auto_suggested, created_at) VALUES "
                        "('same','same','x',0,CURRENT_TIMESTAMP)"
                    )
                )

    def test_note_research_display_order_unique_rejects_duplicate(
        self, upgraded_engine
    ):
        """uix_note_research_display_order (document_id, display_order) is
        the constraint link_research_to_note's retry loop is built around —
        two concurrent links computing the same MAX(display_order)+1 must not
        both commit. Prove the schema actually rejects a duplicate
        (document_id, display_order). The two rows use DIFFERENT research_ids
        so the other UNIQUE (document_id, research_id) can't be what fires.
        SQLite reports the offending COLUMNS (not the constraint name) for a
        UNIQUE violation, so match on the column.
        """
        with upgraded_engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO note_research "
                    "(document_id, research_id, display_order, is_collapsed, "
                    "created_at) VALUES "
                    "('doc1','r1',0,0,CURRENT_TIMESTAMP)"
                )
            )
        with pytest.raises(IntegrityError, match="display_order"):
            with upgraded_engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_research "
                        "(document_id, research_id, display_order, is_collapsed, "
                        "created_at) VALUES "
                        "('doc1','r2',0,0,CURRENT_TIMESTAMP)"
                    )
                )

    def test_note_research_distinct_display_order_commits_positive_control(
        self, upgraded_engine
    ):
        """Positive control: the SAME document with DIFFERENT
        display_order values commits cleanly — the constraint isn't rejecting
        every second row for a document."""
        with upgraded_engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO note_research "
                    "(document_id, research_id, display_order, is_collapsed, "
                    "created_at) VALUES "
                    "('docA','rA',0,0,CURRENT_TIMESTAMP)"
                )
            )
        with upgraded_engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO note_research "
                    "(document_id, research_id, display_order, is_collapsed, "
                    "created_at) VALUES "
                    "('docA','rB',1,0,CURRENT_TIMESTAMP)"
                )
            )
        with upgraded_engine.connect() as conn:
            assert (
                conn.execute(
                    text(
                        "SELECT COUNT(*) FROM note_research "
                        "WHERE document_id='docA'"
                    )
                ).scalar()
                == 2
            )

    def test_valid_rows_commit_positive_control(self, upgraded_engine):
        """Positive control: valid values insert cleanly — proves the
        constraints aren't rejecting everything."""
        with upgraded_engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO note_versions "
                    "(id, document_id, title, content, tags, "
                    "change_type, content_hash, created_at) VALUES "
                    "('v-ok','doc1','t','c','[]','manual_save','h-ok',"
                    "CURRENT_TIMESTAMP)"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO note_syntheses "
                    "(synthesis_type, created_at) VALUES "
                    "('merge', CURRENT_TIMESTAMP)"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO note_links "
                    "(source_document_id, target_document_id, link_text, "
                    "auto_suggested, created_at) VALUES "
                    "('src','tgt','x',0,CURRENT_TIMESTAMP)"
                )
            )

        with upgraded_engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_versions")
                ).scalar()
                == 1
            )
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_syntheses")
                ).scalar()
                == 1
            )
            assert (
                conn.execute(text("SELECT COUNT(*) FROM note_links")).scalar()
                == 1
            )


# ---------------------------------------------------------------------------
# Foreign-key ondelete actions — actual DB-level CASCADE / SET NULL behavior
# ---------------------------------------------------------------------------


class TestForeignKeyCascades:
    """The note schema's ondelete actions are the *only* thing keeping the
    auxiliary note tables from accumulating orphans when a Document is deleted.
    Every other test in this file runs with PRAGMA foreign_keys=OFF (by SQLite
    default), so the ``ondelete=`` clauses in migration 0021 had ZERO runtime
    coverage. These tests use ``upgraded_fk_engine`` (PRAGMA foreign_keys=ON)
    and assert each ondelete action fires.

    Each test is revert-detecting: flipping a migration FK from CASCADE to
    NO ACTION would leave the child row in place (count stays 1) and fail the
    ==0 assertion; flipping the result_document_id SET NULL to CASCADE would
    delete the synthesis row and fail the surviving-row assertion.

    A self-contained ``_helper``-style positive count is asserted right after
    seeding so a test can never pass vacuously by inserting nothing.
    """

    # -- 1. Deleting a document CASCADE-deletes all of its note children -----

    def test_delete_document_cascades_all_note_children(
        self, upgraded_fk_engine
    ):
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["doc1", "doc2", "docres"], with_research=True)
            # note_versions -> doc1
            conn.execute(
                text(
                    "INSERT INTO note_versions "
                    "(id, document_id, title, content, tags, change_type, "
                    "content_hash, created_at) VALUES "
                    "('v1', 'doc1', 't', 'c', '[]', 'manual_save', 'h1', "
                    "CURRENT_TIMESTAMP)"
                )
            )
            # note_links with doc1 on the SOURCE side (-> doc2)
            conn.execute(
                text(
                    "INSERT INTO note_links "
                    "(source_document_id, target_document_id, link_text, "
                    "auto_suggested, created_at) VALUES "
                    "('doc1', 'doc2', 'x', 0, CURRENT_TIMESTAMP)"
                )
            )
            # note_research -> doc1 + r1
            conn.execute(
                text(
                    "INSERT INTO note_research "
                    "(document_id, research_id, display_order, is_collapsed, "
                    "created_at) VALUES "
                    "('doc1', 'r1', 0, 0, CURRENT_TIMESTAMP)"
                )
            )
            # a synthesis whose result lives elsewhere (docres) ...
            conn.execute(
                text(
                    "INSERT INTO note_syntheses "
                    "(id, result_document_id, synthesis_type, created_at) "
                    "VALUES (1, 'docres', 'merge', CURRENT_TIMESTAMP)"
                )
            )
            # ... with doc1 as a SOURCE document via the junction
            conn.execute(
                text(
                    "INSERT INTO note_synthesis_sources "
                    "(synthesis_id, source_document_id, created_at) VALUES "
                    "(1, 'doc1', CURRENT_TIMESTAMP)"
                )
            )

        # Guard against vacuous pass: everything is present before the delete.
        with engine.connect() as conn:
            for table in (
                "note_versions",
                "note_links",
                "note_research",
                "note_synthesis_sources",
            ):
                assert (
                    conn.execute(
                        text(f"SELECT COUNT(*) FROM {table}")  # noqa: S608
                    ).scalar()
                    == 1
                ), f"seed failed for {table}"

        with engine.begin() as conn:
            conn.execute(text("DELETE FROM documents WHERE id = 'doc1'"))

        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_versions")
                ).scalar()
                == 0
            ), "note_versions not CASCADE-deleted"
            assert (
                conn.execute(text("SELECT COUNT(*) FROM note_links")).scalar()
                == 0
            ), "note_links (source side) not CASCADE-deleted"
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_research")
                ).scalar()
                == 0
            ), "note_research not CASCADE-deleted"
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_synthesis_sources")
                ).scalar()
                == 0
            ), "note_synthesis_sources not CASCADE-deleted"
            # The synthesis itself referenced docres, not doc1, so it survives.
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_syntheses")
                ).scalar()
                == 1
            ), "note_syntheses wrongly removed by unrelated document delete"

    def test_delete_document_cascades_note_link_target_side(
        self, upgraded_fk_engine
    ):
        """note_links has TWO FKs to documents.id (source + target), both
        CASCADE. The combined test above exercises the source side; this one
        deletes the TARGET document to prove the second FK cascades too.
        """
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["doc1", "doc2"])
            conn.execute(
                text(
                    "INSERT INTO note_links "
                    "(source_document_id, target_document_id, link_text, "
                    "auto_suggested, created_at) VALUES "
                    "('doc1', 'doc2', 'x', 0, CURRENT_TIMESTAMP)"
                )
            )
        with engine.connect() as conn:
            assert (
                conn.execute(text("SELECT COUNT(*) FROM note_links")).scalar()
                == 1
            )

        with engine.begin() as conn:
            # Delete the TARGET document (doc2), not the source.
            conn.execute(text("DELETE FROM documents WHERE id = 'doc2'"))

        with engine.connect() as conn:
            assert (
                conn.execute(text("SELECT COUNT(*) FROM note_links")).scalar()
                == 0
            ), "note_links not CASCADE-deleted via target_document_id FK"

    def test_delete_research_history_cascades_note_research(
        self, upgraded_fk_engine
    ):
        """note_research.research_id -> research_history.id is ON DELETE
        CASCADE, but the other cascade tests only ever delete documents. Prove
        the research_history SIDE fires too — deleting a research run drops its
        note_research link rows while leaving the linked note (document)
        intact. Flipping that FK off CASCADE fails the ==0 assertion.
        """
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["doc1"], with_research=True)
            conn.execute(
                text(
                    "INSERT INTO note_research "
                    "(document_id, research_id, display_order, is_collapsed, "
                    "created_at) VALUES "
                    "('doc1', 'r1', 0, 0, CURRENT_TIMESTAMP)"
                )
            )
        # Guard against vacuous pass.
        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_research")
                ).scalar()
                == 1
            ), "seed failed for note_research"

        with engine.begin() as conn:
            conn.execute(text("DELETE FROM research_history WHERE id = 'r1'"))

        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_research")
                ).scalar()
                == 0
            ), (
                "note_research not CASCADE-deleted when its research row was deleted"
            )
            # The linked note (document) must survive the research delete.
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM documents WHERE id = 'doc1'")
                ).scalar()
                == 1
            ), "deleting research_history wrongly removed the linked note"

    # -- 2. result_document_id SET NULL vs. source CASCADE -------------------

    def test_delete_result_document_set_nulls_synthesis_not_cascade(
        self, upgraded_fk_engine
    ):
        """Deleting the synthesis RESULT document must SET NULL the
        note_syntheses.result_document_id, preserving the synthesis row as an
        audit record. It must NOT cascade-delete the synthesis, and must NOT
        touch unrelated source junction rows.
        """
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["docres", "docsrc"])
            conn.execute(
                text(
                    "INSERT INTO note_syntheses "
                    "(id, result_document_id, synthesis_type, created_at) "
                    "VALUES (1, 'docres', 'merge', CURRENT_TIMESTAMP)"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO note_synthesis_sources "
                    "(synthesis_id, source_document_id, created_at) VALUES "
                    "(1, 'docsrc', CURRENT_TIMESTAMP)"
                )
            )

        with engine.begin() as conn:
            conn.execute(text("DELETE FROM documents WHERE id = 'docres'"))

        with engine.connect() as conn:
            # synthesis row preserved (audit) ...
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_syntheses")
                ).scalar()
                == 1
            ), "note_syntheses was cascade-deleted instead of SET NULL"
            # ... with its result_document_id nulled out.
            assert (
                conn.execute(
                    text("SELECT result_document_id FROM note_syntheses")
                ).scalar()
                is None
            ), "result_document_id was not SET NULL on result-document delete"
            # source junction untouched (its document still exists).
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_synthesis_sources")
                ).scalar()
                == 1
            ), "synthesis source junction wrongly removed"

    def test_delete_source_document_cascades_junction_not_synthesis(
        self, upgraded_fk_engine
    ):
        """Deleting a SOURCE document CASCADE-deletes the
        note_synthesis_sources junction row but leaves the synthesis itself.
        """
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["docres", "docsrc"])
            conn.execute(
                text(
                    "INSERT INTO note_syntheses "
                    "(id, result_document_id, synthesis_type, created_at) "
                    "VALUES (1, 'docres', 'merge', CURRENT_TIMESTAMP)"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO note_synthesis_sources "
                    "(synthesis_id, source_document_id, created_at) VALUES "
                    "(1, 'docsrc', CURRENT_TIMESTAMP)"
                )
            )
        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_synthesis_sources")
                ).scalar()
                == 1
            )

        with engine.begin() as conn:
            conn.execute(text("DELETE FROM documents WHERE id = 'docsrc'"))

        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_synthesis_sources")
                ).scalar()
                == 0
            ), "junction not CASCADE-deleted on source-document delete"
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_syntheses")
                ).scalar()
                == 1
            ), "synthesis wrongly removed when a source document was deleted"

    # -- 3. Deleting a synthesis CASCADE-deletes its junction rows ----------

    def test_delete_synthesis_cascades_its_sources(self, upgraded_fk_engine):
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["docsrc1", "docsrc2"])
            conn.execute(
                text(
                    "INSERT INTO note_syntheses "
                    "(id, synthesis_type, created_at) VALUES "
                    "(99, 'merge', CURRENT_TIMESTAMP)"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO note_synthesis_sources "
                    "(synthesis_id, source_document_id, created_at) VALUES "
                    "(99, 'docsrc1', CURRENT_TIMESTAMP), "
                    "(99, 'docsrc2', CURRENT_TIMESTAMP)"
                )
            )
        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_synthesis_sources")
                ).scalar()
                == 2
            )

        with engine.begin() as conn:
            conn.execute(text("DELETE FROM note_syntheses WHERE id = 99"))

        with engine.connect() as conn:
            assert (
                conn.execute(
                    text("SELECT COUNT(*) FROM note_synthesis_sources")
                ).scalar()
                == 0
            ), "junction not CASCADE-deleted on synthesis delete"

    # -- 4. CHECK constraints still fire under FK-ON (they coexist) ---------

    def test_change_type_check_still_enforced_under_fk_on(
        self, upgraded_fk_engine
    ):
        """With FK enforcement ON and a VALID parent document, the bad
        change_type must still be rejected by the CHECK — proving the CHECK
        and FK constraints coexist (the FK is satisfied, so only the CHECK can
        fire).
        """
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["doc1"])
        with pytest.raises(IntegrityError, match="ck_note_change_type"):
            with engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_versions "
                        "(id, document_id, title, content, tags, change_type, "
                        "content_hash, created_at) VALUES "
                        "('v1', 'doc1', 't', 'c', '[]', 'bogus', 'h1', "
                        "CURRENT_TIMESTAMP)"
                    )
                )

    def test_synthesis_type_check_still_enforced_under_fk_on(
        self, upgraded_fk_engine
    ):
        engine = upgraded_fk_engine
        with pytest.raises(IntegrityError, match="ck_note_synthesis_type"):
            with engine.begin() as conn:
                # result_document_id NULL is allowed; only the CHECK can fire.
                conn.execute(
                    text(
                        "INSERT INTO note_syntheses "
                        "(synthesis_type, created_at) VALUES "
                        "('bogus', CURRENT_TIMESTAMP)"
                    )
                )

    def test_no_self_link_check_still_enforced_under_fk_on(
        self, upgraded_fk_engine
    ):
        """Both FKs are satisfied (same valid document on each side), so the
        only constraint a self-link can violate is the no-self CHECK.
        """
        engine = upgraded_fk_engine
        with engine.begin() as conn:
            _seed_parents(conn, ["doc1"])
        with pytest.raises(IntegrityError, match="ck_note_link_no_self"):
            with engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_links "
                        "(source_document_id, target_document_id, link_text, "
                        "auto_suggested, created_at) VALUES "
                        "('doc1', 'doc1', 'x', 0, CURRENT_TIMESTAMP)"
                    )
                )

    def test_bad_document_fk_is_rejected_under_fk_on(self, upgraded_fk_engine):
        """Sanity check that the fixture's FK enforcement is real: inserting a
        note_version pointing at a non-existent document must raise an FK
        IntegrityError (this same insert succeeds under the FK-OFF engine).
        """
        engine = upgraded_fk_engine
        with pytest.raises(IntegrityError):
            with engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO note_versions "
                        "(id, document_id, title, content, tags, change_type, "
                        "content_hash, created_at) VALUES "
                        "('v1', 'no-such-doc', 't', 'c', '[]', 'manual_save', "
                        "'h1', CURRENT_TIMESTAMP)"
                    )
                )


class TestMigration0021ChainGuard:
    """Chain guard for 0021 (the head-alignment guard moved to the 0022
    test file per the repo convention of keeping it in the newest
    migration's test)."""

    def test_0021_chains_correctly_to_0020(self):
        from alembic.config import Config
        from alembic.script import ScriptDirectory

        from local_deep_research.database.alembic_runner import (
            get_migrations_dir,
        )

        config = Config()
        config.set_main_option("script_location", str(get_migrations_dir()))
        script = ScriptDirectory.from_config(config)
        assert script.get_revision("0021").down_revision == "0020"