~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
"""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