~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.
Python academiaanthropicarxivbravedeep-researchencryptionhome-automationhomeserverlocallocal-deep-researchlocal-llmmistralollamaopenaipubmedresearchresearch-toolretrieval-augmented-generationsearxngself-hosted
"""
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"