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.

33.2 KB
Raw Blame
"""Explicit deletion of research rows and their foreign-key descendants.

Legacy SQLite databases retain the FK rules from table creation, and several
current download edges use NO ACTION. Derive a child-before-parent plan from
model metadata instead of relying on database cascades. The survival policy
clears links to chat, library, sync and synthesis rows that outlive a research.
Shared download trackers can move to another research's resource before the
plan runs, preserving their URL-level attempt and duplicate history.
Documents referenced by a surviving research likewise move to that resource;
their storage, collection links and indexing state remain attached to the same
document ID. Both ownership fields move together, including on legacy schemas.

Rows without an FK path to research_history, including document_chunks, are
outside this plan. Report files and UserActiveResearch remain caller concerns.
All writes use a nested savepoint; the caller owns the outer transaction.
"""

from loguru import logger
from sqlalchemy import (
    not_,
    select,
    text,
    tuple_,
)
from sqlalchemy.orm import Session

from ..constants import ResearchStatus
from .models import (
    Base,
    Document,
    DocumentChunk,
    DocumentCollection,
    DownloadDuplicates,
    DownloadTracker,
    QueuedResearch,
    ResearchHistory,
    ResearchResource,
)
from ..utilities.download_url import get_url_hash, get_url_hash_candidates


def collect_research_document_rag_targets(
    db_session: Session, research_id: str
) -> list[tuple[str, list[str]]]:
    """Snapshot owned documents with chunk rows before the research cascade.

    ``DocumentChunk.source_id`` has no foreign key. The cascade deletes the
    owning Document and its collection links, but must leave chunk rows until
    the post-commit vector purge has used their FAISS IDs. Include linked
    collections as well as those named by chunks, since an interrupted index
    can leave the two sets different.
    """
    # Legacy SQLite SELECTs do not open a database transaction. Discovery
    # must share the cascade's snapshot, or a newly committed document can
    # be deleted without appearing in these post-commit cleanup targets.
    _ensure_outer_db_transaction(db_session)
    chunk_rows = (
        db_session.query(Document.id, DocumentChunk.collection_name)
        .join(
            DocumentChunk,
            (DocumentChunk.source_id == Document.id)
            & (DocumentChunk.source_type == "document"),
        )
        .filter(Document.research_id == research_id)
        .distinct()
        .all()
    )
    targets: dict[str, set[str]] = {}
    for document_id, collection_name in chunk_rows:
        collections = targets.setdefault(document_id, set())
        if collection_name and collection_name.startswith("collection_"):
            collections.add(collection_name.removeprefix("collection_"))

    if not targets:
        return []

    links = (
        db_session.query(
            DocumentCollection.document_id, DocumentCollection.collection_id
        )
        .join(Document, Document.id == DocumentCollection.document_id)
        .filter(Document.research_id == research_id)
        .all()
    )
    for document_id, collection_id in links:
        if document_id in targets:
            targets[document_id].add(collection_id)

    return [
        (document_id, sorted(collections))
        for document_id, collections in sorted(targets.items())
    ]


_CASCADE_ROOT_TABLE = "research_history"

# (child_table, child_column) pairs whose rows SURVIVE a research
# delete with the link cleared. Every other edge reached by the
# metadata walk becomes a bulk DELETE. Keep this list to exactly the
# cases where deletion would destroy data owned by someone else:
# chat history outlives the research it discusses; library, sync and
# synthesis rows outlive any single research.
_CASCADE_SET_NULL = frozenset(
    {
        ("chat_messages", "research_id"),
        ("documents", "resource_id"),
        ("research_resources", "document_id"),
        ("download_tracker", "library_document_id"),
        ("note_syntheses", "result_document_id"),
        ("zotero_item_map", "document_id"),
    }
)

_cascade_plan_cache = None


def _research_cascade_plan():
    """Derive ``(set_null_steps, delete_steps, table_to_class)`` from
    ``Base.metadata``.

    Each step is ``(child_table, child_column, parent_table,
    parent_column)``. Delete steps additionally carry their runtime
    order: deepest tables first, children before the parents they
    reference (topological over the reached subgraph). The result is
    cached — model metadata is fixed per process.
    """
    global _cascade_plan_cache
    if _cascade_plan_cache is not None:
        return _cascade_plan_cache

    table_to_class = {}
    for mapper in Base.registry.mappers:
        cls = mapper.class_
        table_name = getattr(cls.__table__, "name", None)
        if table_name:
            if table_name in table_to_class:
                raise AssertionError(
                    f"cascade planner: table {table_name!r} maps to two classes"
                )
            table_to_class[table_name] = cls

    children_of: dict = {}
    for table in Base.metadata.tables.values():
        for fk in table.foreign_keys:
            children_of.setdefault(fk.column.table.name, []).append(
                (table.name, fk.parent.name, fk.column.name)
            )

    reached_edges = []
    levels = {_CASCADE_ROOT_TABLE: 0}
    queue = [_CASCADE_ROOT_TABLE]
    seen_edges = set()
    while queue:
        parent = queue.pop(0)
        for child, column, parent_column in sorted(children_of.get(parent, [])):
            key = (child, column, parent, parent_column)
            if key in seen_edges:
                continue
            seen_edges.add(key)
            if child not in table_to_class:
                raise AssertionError(
                    "cascade planner: table "
                    f"{child!r} references {parent!r} but has no "
                    "mapped class — map it or exclude it explicitly"
                )
            reached_edges.append(key)
            if child not in levels:
                levels[child] = levels[parent] + 1
                queue.append(child)

    # A table's rows are deleted only when a deleted parent references
    # them through a non-surviving edge. Tables reached solely through
    # ``_CASCADE_SET_NULL`` edges (chat history, syntheses, sync state)
    # survive: edges targeting them need no step, since a row that is
    # never deleted cannot block anything.
    deleted_tables = {_CASCADE_ROOT_TABLE}
    changed = True
    while changed:
        changed = False
        for child, column, parent, _parent_column in reached_edges:
            if (
                parent in deleted_tables
                and (child, column) not in _CASCADE_SET_NULL
                and child not in deleted_tables
            ):
                deleted_tables.add(child)
                changed = True

    set_null_steps = [
        key for key in reached_edges if (key[0], key[1]) in _CASCADE_SET_NULL
    ]
    delete_steps = [
        key
        for key in reached_edges
        if (key[0], key[1]) not in _CASCADE_SET_NULL
        and key[2] in deleted_tables
    ]

    # Order deletes so every child goes before the parents it
    # references (Kahn's algorithm, table-name tie-break for
    # determinism). The planner asserts acyclicity: a future cyclic
    # FK must fail loudly here, not as a runtime IntegrityError.
    delete_tables = {step[0] for step in delete_steps}
    waiting_on = {table: set() for table in delete_tables}
    for child, _column, parent, _parent_column in delete_steps:
        if parent in waiting_on:
            waiting_on[parent].add(child)
    ordered_tables: list = []
    ready = sorted(
        table for table, blockers in waiting_on.items() if not blockers
    )
    while ready:
        table = ready.pop(0)
        ordered_tables.append(table)
        for other, blockers in waiting_on.items():
            if table in blockers:
                blockers.discard(table)
                if not blockers and other not in ordered_tables:
                    ready.append(other)
        ready.sort()
    if len(ordered_tables) != len(waiting_on):
        raise AssertionError(
            "cascade planner: cyclic FKs among "
            f"{sorted(waiting_on)} — cannot order deletes"
        )
    table_rank = {table: rank for rank, table in enumerate(ordered_tables)}
    delete_steps.sort(key=lambda step: (table_rank[step[0]], step[0]))

    _cascade_plan_cache = (set_null_steps, delete_steps, table_to_class)
    return _cascade_plan_cache


def _ensure_outer_db_transaction(db_session: Session) -> None:
    """Start a DB-level transaction when none exists yet.

    SQLite (and SQLCipher) in legacy transaction mode — the mode this
    app uses on every production connection (``isolation_level=""``,
    DEFERRED) — only opens a database transaction implicitly before
    DML. Pure ``SELECT``s leave the driver with no transaction even
    though the SQLAlchemy ``Session`` reports ``in_transaction() ==
    True``. Calling ``Session.begin_nested()`` in that state issues a
    standalone ``SAVEPOINT`` outside any ``BEGIN``; its ``RELEASE``
    then commits immediately instead of joining the caller's
    transaction, so a later ``db_session.rollback()`` cannot restore
    the rows (``clear_history`` ends up partly applied after a later
    failure). See the SQLAlchemy SQLite dialect docs on legacy
    transaction mode with the pysqlite driver.

    Probing the driver-level ``in_transaction`` flag and issuing a
    plain (DEFERRED) ``BEGIN`` when it is ``False`` makes the
    following ``begin_nested()`` a true nested savepoint: ``RELEASE``
    keeps the outer transaction open and the caller still owns
    ``commit`` / ``rollback``. DEFERRED (not IMMEDIATE) preserves the
    read-concurrency property production relies on (SELECTs take only
    a SHARED lock). If the transaction cannot be established, refuse
    deletion rather than risk using a stale snapshot or committing early.
    ``session_context.telemetry_savepoint`` reads the same driver flag
    but deliberately issues no ``BEGIN``: best-effort telemetry joins a
    transaction that is already open and otherwise commits on its own.
    Keep the two probes in step.
    """
    connection = db_session.connection()
    raw = getattr(
        getattr(connection, "connection", None), "driver_connection", None
    )
    if raw is None:
        raise RuntimeError("research cascade: driver_connection unavailable")
    in_txn = getattr(raw, "in_transaction", None)
    if in_txn is None:
        raise RuntimeError(
            "research cascade: driver does not expose in_transaction"
        )
    is_in_txn = in_txn() if callable(in_txn) else bool(in_txn)
    if not is_in_txn:
        db_session.execute(text("BEGIN"))


# Bound on bind parameters per IN list; SQLite builds before 3.32 cap a
# statement at 999.
_IN_CHUNK = 500


def _chunks(values):
    values = list(values)
    for start in range(0, len(values), _IN_CHUNK):
        yield values[start : start + _IN_CHUNK]


def _owned_trackers(db_session: Session, research_ids):
    """``(id, url, url_hash)`` of trackers whose first resource belongs to
    one of ``research_ids``."""
    owned = []
    for chunk in _chunks(research_ids):
        owned.extend(
            db_session.query(
                DownloadTracker.id,
                DownloadTracker.url,
                DownloadTracker.url_hash,
            )
            .filter(
                DownloadTracker.first_resource_id.in_(
                    select(ResearchResource.id).where(
                        ResearchResource.research_id.in_(chunk)
                    )
                )
            )
            .all()
        )
    return owned


def _tracker_references(db_session: Session, trackers):
    """Map each tracker id to the sorted ``(resource_id, research_id)`` of
    every existing resource that references it.

    A resource references a tracker when its URL is canonically equivalent
    to the tracker's URL, when its URL has the tracker's ``url_hash`` under the
    download service's current or legacy normalization (the service reuses
    trackers this way without recording a duplicate), or when a
    ``DownloadDuplicates`` row links it. Stored URLs and hashes are only
    read, never rewritten.

    Cost: ``research_resources.url`` has no index and the normalization
    cannot use one, so the URL match scans that table once for the whole
    ``trackers`` set. Stream rows in bounded batches and check the same
    hash candidates as download lookups in Python. The old SQL base filter
    cannot express canonical URL identity (userinfo, ports, fragments and
    tracking parameters), and could omit a surviving reference.
    """
    if not trackers:
        return {}
    references: dict = {tracker_id: set() for tracker_id, _u, _h in trackers}
    by_hash = {url_hash: tracker_id for tracker_id, _url, url_hash in trackers}
    by_url: dict = {}
    by_canonical_hash: dict = {}
    for tracker_id, url, _url_hash in trackers:
        by_url.setdefault(url, []).append(tracker_id)
        # A canonical resource URL cannot reconstruct a legacy hash of the
        # original decorated URL. Normalize both URLs without re-keying rows.
        # Several legacy trackers can represent the same canonical URL.
        by_canonical_hash.setdefault(get_url_hash(url), []).append(tracker_id)

    for chunk in _chunks(by_hash):
        for url_hash, resource_id, research_id in (
            db_session.query(
                DownloadDuplicates.url_hash,
                ResearchResource.id,
                ResearchResource.research_id,
            )
            .join(
                ResearchResource,
                ResearchResource.id == DownloadDuplicates.resource_id,
            )
            .join(
                ResearchHistory,
                ResearchHistory.id == ResearchResource.research_id,
            )
            .filter(DownloadDuplicates.url_hash.in_(chunk))
        ):
            references[by_hash[url_hash]].add((resource_id, research_id))

    url_column = ResearchResource.url
    candidates = (
        db_session.query(
            ResearchResource.id, ResearchResource.research_id, url_column
        )
        .join(
            ResearchHistory, ResearchHistory.id == ResearchResource.research_id
        )
        .filter(url_column.isnot(None))
    )
    for resource_id, research_id, url in candidates.yield_per(_IN_CHUNK):
        tracker_ids = set(by_url.get(url, ()))
        url_hashes = get_url_hash_candidates(url)
        tracker_ids.update(by_canonical_hash.get(url_hashes[0], ()))
        for url_hash in url_hashes:
            tracker_id = by_hash.get(url_hash)
            if tracker_id is not None:
                tracker_ids.add(tracker_id)
        for tracker_id in tracker_ids:
            references[tracker_id].add((resource_id, research_id))
    return {tracker_id: sorted(refs) for tracker_id, refs in references.items()}


class SharedDownloadIndex:
    """Shared-download references for a batch of research deletes.

    ``clear_history`` deletes many researches in one transaction. Scanning
    ``research_resources`` once per tracker-owning research would repeat a
    full-table scan per research while holding the user database's write
    lock. :meth:`build` collects, in one scan, every resource that
    references a tracker owned by the batch. Trackers only move to
    resources that reference them, so this covers every tracker a batch
    member can own while the batch runs.

    Each delete then hands a tracker to the lowest-id referencing resource
    of a research that is neither being deleted nor already deleted
    (:meth:`mark_deleted`). This is the choice a fresh per-research scan
    would make, so a tracker handed to a later batch member moves again
    when that member is deleted, and stays with it when its guarded delete
    is refused. Researches outside the batch, including protected ones,
    are never deleted, so they stay eligible. A tracker the index has not
    seen (for example one created after :meth:`build`) is looked up with
    its own scan.
    """

    def __init__(self):
        self._references: dict = {}
        self._first_live: dict = {}
        self._deleted: set = set()

    @classmethod
    def build(cls, db_session: Session, research_ids) -> "SharedDownloadIndex":
        # The snapshot must belong to the same database transaction as the
        # deletes. Legacy SQLite SELECTs alone do not begin a transaction.
        _ensure_outer_db_transaction(db_session)
        index = cls()
        owned = _owned_trackers(db_session, list(dict.fromkeys(research_ids)))
        index._references = _tracker_references(db_session, owned)
        return index

    def mark_deleted(self, research_id: str) -> None:
        self._deleted.add(research_id)

    def replacements(self, db_session: Session, research_id: str) -> dict:
        """Map each tracker this research owns to its surviving resource.

        Returns early, after one tracker query, when the research owns no
        tracker. Trackers without a surviving reference are left out and
        deleted with the research.
        """
        owned = _owned_trackers(db_session, [research_id])
        if not owned:
            return {}
        unseen = [row for row in owned if row[0] not in self._references]
        if unseen:
            self._references.update(_tracker_references(db_session, unseen))
        replacements = {}
        for tracker_id, _url, _url_hash in owned:
            refs = self._references[tracker_id]
            start = self._first_live.get(tracker_id, 0)
            # Deleted researches never come back; skip them for good.
            while start < len(refs) and refs[start][1] in self._deleted:
                start += 1
            self._first_live[tracker_id] = start
            for resource_id, ref_research_id in refs[start:]:
                if (
                    ref_research_id != research_id
                    and ref_research_id not in self._deleted
                ):
                    replacements[tracker_id] = resource_id
                    break
        return replacements


def _shared_download_replacements(db_session: Session, research_id: str):
    """Choose a surviving resource for each tracker owned by this research.

    One-off form of :meth:`SharedDownloadIndex.replacements`: no scan when
    the research owns no tracker, otherwise one scan of
    ``research_resources``.
    """
    return SharedDownloadIndex().replacements(db_session, research_id)


def _shared_document_replacements(
    db_session: Session, research_id: str
) -> dict[str, tuple[int, str]]:
    """Choose a surviving explicit reference for each shared document.

    Resource URLs may differ for identical downloaded bytes. The canonical
    document link is the evidence of sharing, not URL equality. Choose the
    lowest resource ID deterministically and keep resource/research ownership
    aligned, preserving ADR-0012's protection on legacy resource-CASCADE DBs.
    """
    references = (
        db_session.query(
            Document.id, ResearchResource.id, ResearchResource.research_id
        )
        .join(ResearchResource, ResearchResource.document_id == Document.id)
        .join(
            ResearchHistory, ResearchHistory.id == ResearchResource.research_id
        )
        .filter(
            Document.research_id == research_id,
            ResearchResource.research_id != research_id,
        )
        .order_by(ResearchResource.id)
        .all()
    )
    replacements = {}
    for document_id, resource_id, surviving_research_id in references:
        replacements.setdefault(
            document_id, (resource_id, surviving_research_id)
        )
    return replacements


def delete_research_with_dependents(
    db_session: Session,
    research_id: str,
    *,
    require_status_not_in_progress: bool = True,
    require_no_claimed_queue_row: bool = True,
    shared_downloads: SharedDownloadIndex | None = None,
) -> bool:
    """Delete a research and its owned FK descendants atomically.

    Callers deleting research outside the wired entry points must also
    collect the per-research RAG targets inside the same transaction and
    run ``DocumentDeletionService.purge_deleted_research_documents``
    after the guarded commit (never after a refusal or rollback) — see
    ``web/routers/research.py`` for the pattern. Skipping it leaves
    orphaned, still-searchable vector and chunk rows behind.

    URL-level download trackers move to a surviving resource when one exists;
    their shared attempt history and other resources' duplicate links survive.
    Shared documents transfer both owner fields to a surviving resource,
    preserving their ID, stored PDF, collections and existing index entries.
    Deleting the last referencing research retains ordinary cascade behavior;
    explicit global document deletion remains a separate operation.
    A caller deleting many researches in one transaction passes one
    ``shared_downloads`` index built for the whole set
    (:meth:`SharedDownloadIndex.build`); the helper records each successful
    delete in it. Without one, a research that owns a tracker pays its own
    scan of ``research_resources``.

    All child-table deletes and the parent delete are issued against the
    same SQLAlchemy session without an intervening commit, so the row
    is removed in one transaction. The two ``require_*`` flags gate the
    parent DELETE on the same predicates the original ``#5074`` guard
    embedded inline:

    * ``require_status_not_in_progress`` — refuses rows still
      ``IN_PROGRESS`` (a worker owns them).
    * ``require_no_claimed_queue_row`` — refuses rows whose
      ``QueuedResearch`` entry has ``is_processing = True`` (a worker is
      about to start or has not finished writing back the row).

    When either flag is ``False`` the corresponding predicate is dropped
    entirely. Both default to ``True`` so the helper applies the full
    guard by default — callers that genuinely want an unconditional
    cascade (e.g. the chat service's per-attempt teardown, where a
    pre-loaded liveness check has already ruled out a live worker) opt
    out explicitly. The predicates run **inside** the parent ``DELETE``
    so a concurrent claim that lands between the helper's read and its
    delete is observed by SQL and the row is preserved.

    Atomicity per research: child writes run inside a ``SAVEPOINT``
    (``Session.begin_nested``). When the parent ``DELETE`` deletes zero
    rows — ghost row or a guard refusal — the savepoint is rolled back
    so the ``False`` return leaves parent *and* children untouched. A
    ``True`` return releases the savepoint into the caller's outer
    transaction (the caller still owns ``commit`` / ``rollback``). This
    is what makes ``clear_history``'s multi-row loop safe: one
    protected row's rollback does not discard its siblings' deletes.

    The reference lookup and savepoint share a real database transaction.
    In legacy transaction mode (production SQLCipher/SQLite with
    ``isolation_level=""``), SELECTs do not open one, so a DEFERRED
    ``BEGIN`` precedes Phase A (and the batch index's initial lookup).
    This both prevents a standalone ``RELEASE`` from committing early
    and prevents a concurrent new reference from being silently missed.
    If a WAL writer changes the snapshot, upgrading it to a write fails;
    the caller must roll back and retry with a fresh index.

    The helper performs an explicit, application-level cascade driven
    by ``_research_cascade_plan()`` (derived from ``Base.metadata``,
    so new plain-data tables are picked up with no code change). See
    the module docstring for why we do not rely on the DB-level
    ``ON DELETE CASCADE``. Caller is responsible for the surrounding
    transaction (``commit`` / ``rollback``).

    Returns ``True`` iff the ``research_history`` row was deleted.
    """
    _ensure_outer_db_transaction(db_session)
    set_null_steps, delete_steps, table_to_class = _research_cascade_plan()

    def _pk_columns(table):
        return [table.c[name] for name in table.primary_key.columns.keys()]

    def _pk_filter(table, pk_values):
        pk_cols = _pk_columns(table)
        if len(pk_cols) == 1:
            return pk_cols[0].in_([row[0] for row in pk_values])
        return tuple_(*pk_cols).in_(pk_values)

    # Phase A: resolve scopes top-down. ``scopes[table]`` holds the PK
    # tuples reachable from the named research; each step's parent
    # values are the referenced-column values over those rows. Edges
    # resolve in rounds (a step resolves once its parent table's scope
    # is final), so same-level parents — e.g. ``note_references``
    # scoped by both ``research_history`` and ``documents`` — work.
    # Read-only SELECTs only; no writes happen before Phase B.
    root_table = table_to_class[_CASCADE_ROOT_TABLE].__table__
    scopes = {
        _CASCADE_ROOT_TABLE: db_session.query(*_pk_columns(root_table))
        .filter(root_table.c.id == research_id)
        .all()
    }
    # Ghost row fast-path: no writes, no savepoint.
    if not scopes[_CASCADE_ROOT_TABLE]:
        return False

    if shared_downloads is None:
        shared_downloads = SharedDownloadIndex()
    download_replacements = shared_downloads.replacements(
        db_session, research_id
    )
    document_replacements = _shared_document_replacements(
        db_session, research_id
    )
    edge_parent_values: dict = {}

    def _resolve_step(child, column, parent, parent_column):
        parent_table = table_to_class[parent].__table__
        parent_pks = scopes.get(parent, [])
        if not parent_pks:
            edge_parent_values[(child, column, parent, parent_column)] = []
            return []
        values = [
            value
            for (value,) in db_session.query(parent_table.c[parent_column])
            .filter(_pk_filter(parent_table, parent_pks))
            .distinct()
            .all()
            if value is not None
        ]
        edge_parent_values[(child, column, parent, parent_column)] = values
        return values

    # A table's scope is final once every DELETE edge into it has
    # resolved (SET NULL edges never extend a scope — nulled rows
    # survive, so nothing traverses from them). DELETE edges form a
    # DAG (asserted by the planner), and every such edge is processed
    # parent-first, so this loop always terminates; the AssertionError
    # below is a safety net, not an expected path.
    all_steps = set_null_steps + delete_steps
    delete_step_set = set(delete_steps)
    resolved_tables = {_CASCADE_ROOT_TABLE}
    remaining_steps = set(all_steps)
    while remaining_steps:
        resolvable = sorted(
            step for step in remaining_steps if step[2] in resolved_tables
        )
        if not resolvable:
            raise AssertionError(
                "cascade planner: unresolvable scope chain from "
                f"{sorted(remaining_steps)} — cyclic FKs?"
            )
        for step in resolvable:
            child, column, parent, parent_column = step
            values = _resolve_step(child, column, parent, parent_column)
            if step in delete_step_set and values:
                child_table = table_to_class[child].__table__
                children = db_session.query(*_pk_columns(child_table)).filter(
                    child_table.c[column].in_(values)
                )
                if child == "download_tracker" and download_replacements:
                    # These trackers will move before deleting resources.
                    # Exclude them from descendant scopes so their shared
                    # attempts and other researches' duplicates survive.
                    children = children.filter(
                        child_table.c.id.notin_(download_replacements)
                    )
                if child == "documents" and document_replacements:
                    # Exclude retained documents before computing descendant
                    # scopes, or blobs/collection links would still be deleted
                    # even though the document's owner changes below.
                    children = children.filter(
                        child_table.c.id.notin_(document_replacements)
                    )
                scopes.setdefault(child, []).extend(
                    tuple(row) for row in children.all()
                )
            remaining_steps.discard(step)
        # Mark a table resolved once every DELETE edge into it has
        # resolved. NB: this must scan tables, not remaining steps —
        # a table whose edges all resolved in an earlier round is no
        # longer in ``remaining_steps`` but still needs marking so
        # deeper edges become resolvable.
        done_steps = set(all_steps) - remaining_steps
        for table in {step[0] for step in delete_steps}:
            if table not in resolved_tables and all(
                incoming in done_steps
                for incoming in delete_steps
                if incoming[0] == table
            ):
                resolved_tables.add(table)

    # Phases B–D run inside a SAVEPOINT so a zero-row parent DELETE
    # (ghost or guard refusal) rolls back the child writes for *this*
    # research only. Without this, ``clear_history`` would commit the
    # orphaned children of a skipped row along with its siblings.
    # Phase A already started the outer DB transaction, so RELEASE
    # cannot commit ahead of the caller's commit / rollback.
    savepoint = db_session.begin_nested()
    try:
        for document_id, (
            resource_id,
            owner_id,
        ) in document_replacements.items():
            db_session.query(Document).filter(
                Document.id == document_id, Document.research_id == research_id
            ).update(
                {"resource_id": resource_id, "research_id": owner_id},
                synchronize_session=False,
            )

        # Keep URL-level tracking with a surviving research. These updates
        # share the savepoint with the cascade, so a refused parent delete
        # restores the original ownership along with all child rows.
        for tracker_id, resource_id in download_replacements.items():
            db_session.query(DownloadTracker).filter(
                DownloadTracker.id == tracker_id
            ).update(
                {"first_resource_id": resource_id}, synchronize_session=False
            )

        # Phase B: clear the surviving links first (all such columns are
        # nullable). Nulling before deleting means the deletes below never
        # meet a row that still points at them, on any schema.
        for child, column, parent, parent_column in set_null_steps:
            values = edge_parent_values[(child, column, parent, parent_column)]
            if not values:
                continue
            cls = table_to_class[child]
            db_session.query(cls).filter(
                getattr(cls, column).in_(values)
            ).update({column: None}, synchronize_session=False)

        # Phase C: bulk deletes in planner order (deepest first), each
        # scoped to its own edge. Scopes were fully resolved in Phase A,
        # so no reads are needed here.
        for child, column, parent, parent_column in delete_steps:
            values = edge_parent_values[(child, column, parent, parent_column)]
            if not values:
                continue
            cls = table_to_class[child]
            child_table = cls.__table__
            db_session.query(cls).filter(
                child_table.c[column].in_(values)
            ).delete(synchronize_session=False)

        # Phase D: finally, the parent row itself. The ``#5074``
        # predicates are applied INSIDE this DELETE so a concurrent queue
        # claim or status write observed by the row sees them atomically —
        # mirroring the old ``DELETE ... WHERE id = ? AND status !=
        # IN_PROGRESS AND ~claimed_queue_row`` shape. A zero-row return
        # means the row did not exist or one of the guards blocked it;
        # callers that map the latter to a 400 must check the row
        # externally (the helper deliberately does not distinguish).
        parent_filters = [ResearchHistory.id == research_id]
        if require_status_not_in_progress:
            parent_filters.append(
                ResearchHistory.status != ResearchStatus.IN_PROGRESS
            )
        if require_no_claimed_queue_row:
            claimed_queue_row = db_session.query(QueuedResearch.id).filter(
                QueuedResearch.research_id == research_id,
                QueuedResearch.is_processing.is_(True),
            )
            parent_filters.append(not_(claimed_queue_row.exists()))

        deleted = (
            db_session.query(ResearchHistory)
            .filter(*parent_filters)
            .delete(synchronize_session=False)
        )

        if not deleted:
            # Guard refused (or ghost raced away): undo this research's
            # child writes, keep the outer transaction for siblings.
            savepoint.rollback()
            return False
        savepoint.commit()
        shared_downloads.mark_deleted(research_id)
        return True
    except Exception:
        try:
            savepoint.rollback()
        except Exception as rollback_exc:
            logger.debug(
                f"research cascade savepoint rollback failed: {rollback_exc}"
            )
        raise