Skip to content

DeepTutor: Lifelong Personalized Tutoring. https://deeptutor.info/.

201 KB
Raw Blame
"""
SQLite-backed unified chat session store.
"""

from __future__ import annotations

import asyncio
from collections.abc import Iterator, Sequence
from contextlib import contextmanager
from dataclasses import dataclass
import json
import os
from pathlib import Path
import re
import sqlite3
import time
from typing import Any
import uuid

try:  # POSIX is the supported production target; fallback keeps Windows dev usable.
    import fcntl
except ImportError:  # pragma: no cover - exercised only on Windows
    fcntl = None  # type: ignore[assignment]

from deeptutor.core.assessment import (
    ASSESSMENT_RESULTS,
    ASSESSMENT_SOURCES,
    ASSESSMENT_TYPES,
    QUESTION_ORIGIN_TYPES,
)
from deeptutor.services.path_service import get_path_service
from deeptutor.services.session.protocol import (
    SUBMISSION_ASSISTANT_FIELD,
    SUBMISSION_PREVIOUS_TURN_FIELD,
    SUBMISSION_REPLAY_FIELD,
    SUBMISSION_USER_FIELD,
    ActiveTurnConflict,
)
from deeptutor.utils.secret_files import ensure_private_directory, ensure_private_file

from .ask_user_trace import select_ask_user_events
from .event_preview import MAX_TRACE_PREVIEW_EVENTS, compact_trace_preview
from .provider_response_state import redact_private_message_metadata
from .search import bounded_search_excerpt, normalize_search_query
from .workspace_preferences import upgrade_workspace_preferences


def _json_dumps(value: Any) -> str:
    # default=str: a single non-serializable object inside an event payload
    # (e.g. a dataclass smuggled into tool args) must degrade to its repr,
    # never kill message/event persistence for the whole turn.
    return json.dumps(value, ensure_ascii=False, default=str)


def _escape_like(value: str) -> str:
    """Escape LIKE wildcards so a search term matches itself literally."""
    return value.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")


@contextmanager
def _migration_lock(db_path: Path) -> Iterator[None]:
    """Serialize idempotent schema upgrades across worker processes."""
    lock_path = db_path.with_name(f"{db_path.name}.migrate.lock")
    with lock_path.open("a+b") as lock_file:
        if fcntl is not None:
            fcntl.flock(lock_file.fileno(), fcntl.LOCK_EX)
        try:
            yield
        finally:
            if fcntl is not None:
                fcntl.flock(lock_file.fileno(), fcntl.LOCK_UN)


# Sentinel so ``add_message`` can distinguish "caller wants the legacy
# auto-pick-latest-message default" from "caller explicitly wants the
# message attached at the session root (parent = NULL)". Both surface as
# ``None`` in the public ``parent_message_id`` arg, which is why we need
# a sentinel separate from None.
class _Unset:
    pass


_PARENT_AUTO = _Unset()


def _trace_bounds(started_at: Any, ended_at: Any) -> dict[str, float]:
    """The turn's real wall-clock span, for a preview that cannot carry it.

    Omitted entirely when either bound is missing, so a consumer can tell
    "this turn has no recorded span" from "this turn took zero seconds".
    """
    try:
        start = float(started_at)
        end = float(ended_at)
    except (TypeError, ValueError):
        return {}
    return {"started_at": start, "ended_at": max(start, end)}


def _json_loads(value: str | None, default: Any) -> Any:
    if not value:
        return default
    try:
        return json.loads(value)
    except json.JSONDecodeError:
        return default


# Imported conversations share the session tables with native chats but carry
# this id prefix as their discriminator (see ``SQLiteSessionStore._WHERE_*``).
_IMPORTED_ID_PREFIX = "imported_"
_ID_SAFE = re.compile(r"[^A-Za-z0-9_-]")
SCORE_TRENDS = frozenset({"new", "improved", "declined", "unchanged"})
# Stored ``result=''`` is a pre-v2 row: treat it as already graded so wrong
# lists do not swallow ungraded spectacle rows, and old incorrect rows stay
# in the wrong filter.
_GRADED_RESULT_SQL = "COALESCE(NULLIF(n.result,''),'graded') NOT IN ('ungraded','voided','')"
_GRADED_RESULT_SQL_UNALIASED = (
    "COALESCE(NULLIF(result,''),'graded') NOT IN ('ungraded','voided','')"
)
# An entry whose session sits in the recycle bin is hidden from the question
# bank until the session is restored. ``{entries}`` names the notebook_entries
# alias of the surrounding query, so every listing, count and chip applies the
# same rule.
_NOT_RECYCLED_ENTRY_SQL = (
    "NOT EXISTS (SELECT 1 FROM sessions s"
    " WHERE s.id = {entries}.session_id AND s.deleted_at IS NOT NULL)"
)
ACTIVE_TURN_STATUSES = frozenset({"queued", "running", "waiting_input"})
TERMINAL_TURN_STATUSES = frozenset({"completed", "failed", "cancelled"})
ALL_TURN_STATUSES = ACTIVE_TURN_STATUSES | TERMINAL_TURN_STATUSES


def make_imported_session_id(source: str, external_id: str) -> str:
    """Build a deterministic, dedup-friendly id for an imported conversation.

    ``source`` (e.g. ``claude_code``/``codex``) namespaces the original
    session uuid so two tools that happen to reuse an id never collide; the
    determinism is what makes re-importing the same folder idempotent.
    """
    src = _ID_SAFE.sub("-", (source or "external").strip()) or "external"
    ext = _ID_SAFE.sub("-", (external_id or "").strip()) or uuid.uuid4().hex
    return f"{_IMPORTED_ID_PREFIX}{src}_{ext}"


@dataclass
class TurnRecord:
    id: str
    session_id: str
    capability: str
    status: str
    error: str
    created_at: float
    updated_at: float
    finished_at: float | None
    last_seq: int = 0
    owner_id: str = ""
    fencing_token: int = 0
    state_version: int = 1
    failure_code: str = ""
    retryable: bool = False
    assistant_message_id: int | None = None

    def to_dict(self) -> dict[str, Any]:
        return {
            "id": self.id,
            "turn_id": self.id,
            "session_id": self.session_id,
            "capability": self.capability,
            "status": self.status,
            "error": self.error,
            "created_at": self.created_at,
            "updated_at": self.updated_at,
            "finished_at": self.finished_at,
            "last_seq": self.last_seq,
            "owner_id": self.owner_id,
            "fencing_token": self.fencing_token,
            "state_version": self.state_version,
            "failure_code": self.failure_code,
            "retryable": self.retryable,
            "assistant_message_id": self.assistant_message_id,
        }


@dataclass(frozen=True)
class QuestionBankQuery:
    """One question-bank listing request.

    A value object instead of a widening positional signature: the store's
    ``_run`` helper only forwards positional args, so every new filter used
    to mean another parameter threaded through three layers. Callers build
    a query, the store reads it — adding a filter never changes an arity.

    ``category_id`` and ``uncategorized`` are mutually exclusive views of the
    same axis; when both are supplied the explicit category wins, because a
    caller asking for one category always means "show me that category".
    """

    category_id: int | None = None
    uncategorized: bool = False
    mistakes_only: bool = False
    bookmarked: bool | None = None
    is_correct: bool | None = None
    source: str = ""
    material_id: str = ""
    section_id: str = ""
    assessment_type: str = ""
    result: str = ""
    mastery_path_id: str = ""
    knowledge_point_id: str = ""
    resolved: bool | None = None
    score_trend: str = ""
    search: str = ""
    session_id: str | None = None
    session_ids: Sequence[str] | None = None
    sort: str = "recent"
    limit: int = 50
    offset: int = 0

    def normalized(self) -> "QuestionBankQuery":
        """Clamp untrusted inputs into the ranges the SQL below assumes."""
        return QuestionBankQuery(
            category_id=self.category_id,
            mistakes_only=self.mistakes_only,
            uncategorized=self.uncategorized and self.category_id is None,
            bookmarked=self.bookmarked,
            is_correct=self.is_correct,
            source=(self.source or "").strip()
            if (self.source or "").strip() in ASSESSMENT_SOURCES
            else "",
            material_id=(self.material_id or "").strip(),
            section_id=(self.section_id or "").strip(),
            assessment_type=(self.assessment_type or "").strip()
            if (self.assessment_type or "").strip() in ASSESSMENT_TYPES
            else "",
            result=(self.result or "").strip()
            if (self.result or "").strip() in ASSESSMENT_RESULTS
            else "",
            mastery_path_id=(self.mastery_path_id or "").strip(),
            knowledge_point_id=(self.knowledge_point_id or "").strip(),
            resolved=self.resolved,
            score_trend=(self.score_trend or "").strip()
            if (self.score_trend or "").strip() in SCORE_TRENDS
            else "",
            search=(self.search or "").strip()[:200],
            session_id=self.session_id,
            session_ids=None if self.session_ids is None else tuple(self.session_ids),
            sort="oldest" if self.sort == "oldest" else "recent",
            limit=max(1, min(int(self.limit), 500)),
            offset=max(0, int(self.offset)),
        )


class SQLiteSessionStore:
    """Persist unified chat sessions and messages in a SQLite database."""

    def __init__(self, db_path: Path | None = None) -> None:
        path_service = get_path_service()
        self.db_path = db_path or path_service.get_chat_history_db()
        ensure_private_directory(self.db_path.parent)
        self._migrate_legacy_db(path_service)
        self._lock = asyncio.Lock()
        with _migration_lock(self.db_path):
            self._initialize()
        ensure_private_file(self.db_path)

    def _migrate_legacy_db(self, path_service) -> None:
        """Move the legacy ``data/chat_history.db`` into ``data/user/`` once."""
        from deeptutor.multi_user.paths import get_account_path_service

        if self.db_path != get_account_path_service().get_chat_history_db():
            return
        legacy_path = path_service.project_root / "data" / "chat_history.db"
        if self.db_path.exists() or not legacy_path.exists() or legacy_path == self.db_path:
            return
        try:
            os.replace(legacy_path, self.db_path)
        except OSError:
            # Fall back to leaving the legacy DB in place if an OS-level move
            # is not possible; the new DB path will be initialized empty.
            pass

    def _initialize(self) -> None:
        with self._connect() as conn:
            # WAL permits readers while another worker commits a turn/event
            # batch.  It is persistent for this database once enabled.
            conn.execute("PRAGMA journal_mode = WAL")
            conn.executescript(
                """
                CREATE TABLE IF NOT EXISTS sessions (
                    id TEXT PRIMARY KEY,
                    title TEXT NOT NULL DEFAULT 'New conversation',
                    created_at REAL NOT NULL,
                    updated_at REAL NOT NULL,
                    compressed_summary TEXT DEFAULT '',
                    summary_up_to_msg_id INTEGER DEFAULT 0,
                    preferences_json TEXT DEFAULT '{}'
                );

                CREATE TABLE IF NOT EXISTS messages (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
                    role TEXT NOT NULL,
                    content TEXT NOT NULL DEFAULT '',
                    capability TEXT DEFAULT '',
                    events_json TEXT DEFAULT '',
                    attachments_json TEXT DEFAULT '',
                    metadata_json TEXT DEFAULT '{}',
                    created_at REAL NOT NULL,
                    -- Edit-branching: NULL for the first message in a session;
                    -- otherwise the immediately preceding message on the path
                    -- this row continues. Siblings (same parent) are alternate
                    -- branches the user can switch between.
                    parent_message_id INTEGER
                );

                CREATE INDEX IF NOT EXISTS idx_messages_session_created
                    ON messages(session_id, created_at, id);
                -- ``idx_messages_parent`` is created after the
                -- parent_message_id migration runs (see below). Putting it
                -- in this script would fail on legacy DBs where the column
                -- gets added by ALTER TABLE further down.

                CREATE INDEX IF NOT EXISTS idx_sessions_updated_at
                    ON sessions(updated_at DESC);

                CREATE TABLE IF NOT EXISTS turns (
                    id TEXT PRIMARY KEY,
                    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
                    capability TEXT DEFAULT '',
                    status TEXT NOT NULL DEFAULT 'running',
                    error TEXT DEFAULT '',
                    created_at REAL NOT NULL,
                    updated_at REAL NOT NULL,
                    finished_at REAL,
                    owner_id TEXT DEFAULT '',
                    fencing_token INTEGER NOT NULL DEFAULT 0,
                    state_version INTEGER NOT NULL DEFAULT 1,
                    failure_code TEXT DEFAULT '',
                    retryable INTEGER NOT NULL DEFAULT 0,
                    assistant_message_id INTEGER
                );

                CREATE INDEX IF NOT EXISTS idx_turns_session_updated
                    ON turns(session_id, updated_at DESC);

                CREATE INDEX IF NOT EXISTS idx_turns_session_status
                    ON turns(session_id, status, updated_at DESC);

                CREATE TABLE IF NOT EXISTS turn_submissions (
                    client_submission_id TEXT PRIMARY KEY,
                    workspace_id TEXT NOT NULL DEFAULT '',
                    request_digest TEXT NOT NULL,
                    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
                    turn_id TEXT REFERENCES turns(id) ON DELETE SET NULL
                );

                CREATE TABLE IF NOT EXISTS turn_events (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    turn_id TEXT NOT NULL REFERENCES turns(id) ON DELETE CASCADE,
                    seq INTEGER NOT NULL,
                    type TEXT NOT NULL,
                    source TEXT DEFAULT '',
                    stage TEXT DEFAULT '',
                    content TEXT DEFAULT '',
                    metadata_json TEXT DEFAULT '',
                    timestamp REAL NOT NULL,
                    created_at REAL NOT NULL,
                    UNIQUE(turn_id, seq)
                );

                CREATE INDEX IF NOT EXISTS idx_turn_events_turn_seq
                    ON turn_events(turn_id, seq);

                CREATE TABLE IF NOT EXISTS notebook_entries (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id TEXT REFERENCES sessions(id) ON DELETE SET NULL,
                    origin_type TEXT NOT NULL DEFAULT 'conversation',
                    origin_ref TEXT NOT NULL,
                    turn_id TEXT NOT NULL DEFAULT '',
                    question_id TEXT NOT NULL,
                    question TEXT NOT NULL,
                    question_type TEXT DEFAULT '',
                    options_json TEXT DEFAULT '{}',
                    correct_answer TEXT DEFAULT '',
                    explanation TEXT DEFAULT '',
                    difficulty TEXT DEFAULT '',
                    user_answer TEXT DEFAULT '',
                    user_answer_images_json TEXT DEFAULT '[]',
                    source TEXT NOT NULL DEFAULT 'deep_question',
                    material_id TEXT NOT NULL DEFAULT '',
                    material_title TEXT NOT NULL DEFAULT '',
                    section_id TEXT NOT NULL DEFAULT '',
                    section_title TEXT NOT NULL DEFAULT '',
                    score_trend TEXT NOT NULL DEFAULT 'new',
                    assessment_type TEXT NOT NULL DEFAULT '',
                    result TEXT NOT NULL DEFAULT '',
                    mastery_path_id TEXT NOT NULL DEFAULT '',
                    knowledge_point_id TEXT NOT NULL DEFAULT '',
                    attempt_count INTEGER NOT NULL DEFAULT 1,
                    hints_used INTEGER NOT NULL DEFAULT 0,
                    confidence REAL,
                    response_time REAL,
                    quality REAL,
                    is_correct INTEGER DEFAULT 0,
                    resolved INTEGER DEFAULT 0,
                    bookmarked INTEGER DEFAULT 0,
                    followup_session_id TEXT DEFAULT '',
                    ai_judgment TEXT DEFAULT '',
                    created_at REAL NOT NULL,
                    updated_at REAL NOT NULL,
                    UNIQUE(origin_type, origin_ref, turn_id, question_id)
                );

                CREATE INDEX IF NOT EXISTS idx_notebook_entries_session
                    ON notebook_entries(session_id, created_at DESC);

                CREATE INDEX IF NOT EXISTS idx_notebook_entries_bookmarked
                    ON notebook_entries(bookmarked, created_at DESC);

                CREATE TABLE IF NOT EXISTS assessment_attempts (
                    attempt_id TEXT PRIMARY KEY,
                    notebook_entry_id INTEGER,
                    session_id TEXT REFERENCES sessions(id) ON DELETE SET NULL,
                    origin_type TEXT NOT NULL DEFAULT 'conversation',
                    origin_ref TEXT NOT NULL,
                    turn_id TEXT NOT NULL DEFAULT '',
                    question_id TEXT NOT NULL,
                    source TEXT NOT NULL,
                    assessment_type TEXT NOT NULL,
                    result TEXT NOT NULL,
                    mastery_path_id TEXT NOT NULL DEFAULT '',
                    knowledge_point_id TEXT NOT NULL DEFAULT '',
                    occurred_at REAL NOT NULL,
                    assessment_json TEXT NOT NULL,
                    linked_applied INTEGER NOT NULL DEFAULT 0
                );

                CREATE INDEX IF NOT EXISTS idx_assessment_attempts_session_time
                    ON assessment_attempts(session_id, occurred_at DESC);

                CREATE INDEX IF NOT EXISTS idx_assessment_attempts_linkage
                    ON assessment_attempts(mastery_path_id, knowledge_point_id, occurred_at DESC);

                CREATE TABLE IF NOT EXISTS reading_quiz_pending (
                    material_id TEXT NOT NULL,
                    locator INTEGER NOT NULL,
                    question_id TEXT NOT NULL,
                    question_json TEXT NOT NULL,
                    created_at REAL NOT NULL,
                    PRIMARY KEY (material_id, locator, question_id)
                );

                CREATE TABLE IF NOT EXISTS reading_quiz_rewards (
                    material_id TEXT NOT NULL,
                    locator INTEGER NOT NULL,
                    stars INTEGER NOT NULL,
                    updated_at REAL NOT NULL,
                    PRIMARY KEY (material_id, locator)
                );

                CREATE TABLE IF NOT EXISTS notebook_categories (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    created_at REAL NOT NULL
                );

                CREATE TABLE IF NOT EXISTS notebook_entry_categories (
                    entry_id INTEGER NOT NULL REFERENCES notebook_entries(id) ON DELETE CASCADE,
                    category_id INTEGER NOT NULL REFERENCES notebook_categories(id) ON DELETE CASCADE,
                    PRIMARY KEY (entry_id, category_id)
                );
                """
            )
            submission_columns = {
                row[1] for row in conn.execute("PRAGMA table_info(turn_submissions)")
            }
            if "superseded_by" not in submission_columns:
                conn.execute("ALTER TABLE turn_submissions ADD COLUMN superseded_by TEXT")
            columns = {row[1] for row in conn.execute("PRAGMA table_info(sessions)").fetchall()}
            if "preferences_json" not in columns:
                conn.execute("ALTER TABLE sessions ADD COLUMN preferences_json TEXT DEFAULT '{}'")
            if "deleted_at" not in columns:
                conn.execute("ALTER TABLE sessions ADD COLUMN deleted_at REAL DEFAULT NULL")
            self._migrate_workspace_preferences(conn)
            if "kind" in columns:
                try:
                    conn.execute("ALTER TABLE sessions DROP COLUMN kind")
                except sqlite3.OperationalError:
                    # Older SQLite builds may not support DROP COLUMN. The
                    # application no longer reads or writes this legacy field.
                    pass
            message_columns = {
                row[1] for row in conn.execute("PRAGMA table_info(messages)").fetchall()
            }
            if "metadata_json" not in message_columns:
                conn.execute("ALTER TABLE messages ADD COLUMN metadata_json TEXT DEFAULT '{}'")
            if "parent_message_id" not in message_columns:
                conn.execute("ALTER TABLE messages ADD COLUMN parent_message_id INTEGER")
                # Backfill: for every existing session, treat the message stream
                # as a single linear path — each row's parent is the previous
                # row (by id) in the same session. Rows with no predecessor stay
                # NULL. We do this per session in pure Python to avoid relying
                # on window functions, which older SQLite builds may not have.
                sessions_rows = conn.execute("SELECT id FROM sessions").fetchall()
                for srow in sessions_rows:
                    prev_id: int | None = None
                    msg_rows = conn.execute(
                        "SELECT id FROM messages WHERE session_id = ? ORDER BY id ASC",
                        (srow[0],),
                    ).fetchall()
                    for mrow in msg_rows:
                        if prev_id is not None:
                            conn.execute(
                                "UPDATE messages SET parent_message_id = ? WHERE id = ?",
                                (prev_id, mrow[0]),
                            )
                        prev_id = mrow[0]
            # Always ensure the parent-lookup index exists — covers both
            # the legacy-migration case (just added the column) and the
            # fresh-DB case (created above without the index inline).
            conn.execute(
                "CREATE INDEX IF NOT EXISTS idx_messages_parent "
                "ON messages(session_id, parent_message_id)"
            )
            self._migrate_notebook_entries_add_turn_id(conn)
            self._migrate_notebook_entries_add_user_answer_images(conn)
            self._migrate_notebook_entries_add_ai_judgment(conn)
            self._migrate_notebook_entries_add_assessment_review(conn)
            self._migrate_notebook_entries_add_assessment_v2(conn)
            self._migrate_notebook_entry_origins(conn)
            self._migrate_assessment_attempt_origins(conn)
            self._migrate_assessment_attempt_link_state(conn)
            self._migrate_turn_runtime_columns(conn)
            from deeptutor.services.practice.storage import initialize_practice

            initialize_practice(conn)
            conn.commit()

    @staticmethod
    def _migrate_workspace_preferences(conn: sqlite3.Connection) -> int:
        """Upgrade workspace metadata and preserve legacy deleted chats as archives.

        Clearing deleted_at only after setting archived keeps old recoverable
        conversations out of the sidebar without discarding their contents.
        Logical timestamps stay unchanged.
        """

        migrated = 0
        rows = conn.execute("SELECT id, preferences_json, deleted_at FROM sessions").fetchall()
        for row in rows:
            current = _json_loads(row["preferences_json"], {})
            if not isinstance(current, dict):
                continue
            upgraded = upgrade_workspace_preferences(current)
            was_deleted = row["deleted_at"] is not None
            if was_deleted:
                upgraded = {**upgraded, "archived": True}
            if upgraded == current and not was_deleted:
                continue
            conn.execute(
                "UPDATE sessions SET preferences_json = ?, deleted_at = NULL WHERE id = ?",
                (_json_dumps(upgraded), row["id"]),
            )
            migrated += 1
        return migrated

    def _migrate_workspace_preferences_sync(self) -> int:
        """Run the idempotent data migration under the cross-process lock."""

        with _migration_lock(self.db_path), self._connect() as conn:
            migrated = self._migrate_workspace_preferences(conn)
            conn.commit()
        return migrated

    async def migrate_workspace_preferences(self) -> int:
        """Upgrade legacy Reading/Mastery session metadata for this user scope."""

        return await self._run(self._migrate_workspace_preferences_sync)

    @staticmethod
    def _migrate_turn_runtime_columns(conn: sqlite3.Connection) -> None:
        """Upgrade legacy turn rows to the v2 concurrency contract.

        A partial unique index is the durable last line of defence against two
        processes starting work for the same session.  Legacy databases may
        already contain duplicate running rows, so resolve those deterministically
        before creating the index.
        """
        columns = {row[1] for row in conn.execute("PRAGMA table_info(turns)").fetchall()}
        additions = {
            "owner_id": "TEXT DEFAULT ''",
            "fencing_token": "INTEGER NOT NULL DEFAULT 0",
            "state_version": "INTEGER NOT NULL DEFAULT 1",
            "failure_code": "TEXT DEFAULT ''",
            "retryable": "INTEGER NOT NULL DEFAULT 0",
        }
        for name, definition in additions.items():
            if name not in columns:
                conn.execute(f"ALTER TABLE turns ADD COLUMN {name} {definition}")
        if "assistant_message_id" not in columns:
            conn.execute("ALTER TABLE turns ADD COLUMN assistant_message_id INTEGER")
            # v1.6.3 already persisted DONE with the assistant row id. Recover
            # those links once so old traces can be served from turn_events.
            conn.execute(
                """
                UPDATE turns
                SET assistant_message_id = (
                    SELECT m.id
                    FROM messages AS m, json_each(m.events_json) AS event
                    WHERE m.session_id = turns.session_id
                      AND m.role = 'assistant'
                      AND json_extract(event.value, '$.type') = 'done'
                      AND json_extract(event.value, '$.turn_id') = turns.id
                      AND json_extract(event.value, '$.metadata.assistant_message_id') IS NOT NULL
                    LIMIT 1
                )
                WHERE assistant_message_id IS NULL
                """
            )
        conn.execute(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS idx_turns_assistant_message
            ON turns(assistant_message_id)
            WHERE assistant_message_id IS NOT NULL
            """
        )

        duplicate_rows = conn.execute(
            """
            SELECT id, session_id
            FROM turns
            WHERE status IN ('queued', 'running', 'waiting_input')
            ORDER BY session_id, updated_at DESC, id DESC
            """
        ).fetchall()
        seen_sessions: set[str] = set()
        now = time.time()
        for row in duplicate_rows:
            session_id = str(row["session_id"])
            if session_id not in seen_sessions:
                seen_sessions.add(session_id)
                continue
            conn.execute(
                """
                UPDATE turns
                SET status = 'failed', error = ?, failure_code = ?,
                    updated_at = ?, finished_at = ?, state_version = state_version + 1
                WHERE id = ?
                """,
                (
                    "Duplicate active turn resolved during migration",
                    "migration_duplicate_running",
                    now,
                    now,
                    row["id"],
                ),
            )
        conn.execute(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS idx_turns_one_active_session
            ON turns(session_id)
            WHERE status IN ('queued', 'running', 'waiting_input')
            """
        )

    @staticmethod
    def _migrate_notebook_entries_add_turn_id(conn: sqlite3.Connection) -> None:
        """Add ``turn_id`` to legacy notebook_entries and re-scope the UNIQUE
        constraint to ``(session_id, turn_id, question_id)``.

        The old unique constraint conflated quizzes generated in the same chat
        (issue #487): regenerating a quiz with the same positional
        ``question_id`` (e.g. ``q_1``) would collide with the previous quiz's
        notebook entries and the UI hydrated stale answers. Scoping by
        ``turn_id`` keeps each quiz isolated.
        """
        notebook_cols = {
            row[1] for row in conn.execute("PRAGMA table_info(notebook_entries)").fetchall()
        }
        if not notebook_cols:
            return
        if "turn_id" not in notebook_cols:
            conn.execute("ALTER TABLE notebook_entries ADD COLUMN turn_id TEXT NOT NULL DEFAULT ''")
        # SQLite stores table-level UNIQUE constraints as auto-indexes whose
        # names start with ``sqlite_autoindex_notebook_entries_``; the columns
        # they cover live in PRAGMA index_info. Detect whether any existing
        # auto-index still covers only (session_id, question_id) and, if so,
        # rebuild the table to swap in the new scope.
        needs_rebuild = False
        for idx_row in conn.execute("PRAGMA index_list(notebook_entries)").fetchall():
            idx_name = idx_row[1]
            if not idx_name.startswith("sqlite_autoindex_notebook_entries_"):
                continue
            cols = [r[2] for r in conn.execute(f"PRAGMA index_info({idx_name})").fetchall()]
            if cols == ["session_id", "question_id"]:
                needs_rebuild = True
                break
        if not needs_rebuild:
            return
        conn.executescript(
            """
            CREATE TABLE notebook_entries_new (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
                turn_id TEXT NOT NULL DEFAULT '',
                question_id TEXT NOT NULL,
                question TEXT NOT NULL,
                question_type TEXT DEFAULT '',
                options_json TEXT DEFAULT '{}',
                correct_answer TEXT DEFAULT '',
                explanation TEXT DEFAULT '',
                difficulty TEXT DEFAULT '',
                user_answer TEXT DEFAULT '',
                is_correct INTEGER DEFAULT 0,
                bookmarked INTEGER DEFAULT 0,
                followup_session_id TEXT DEFAULT '',
                created_at REAL NOT NULL,
                updated_at REAL NOT NULL,
                UNIQUE(session_id, turn_id, question_id)
            );

            INSERT INTO notebook_entries_new (
                id, session_id, turn_id, question_id, question, question_type,
                options_json, correct_answer, explanation, difficulty,
                user_answer, is_correct, bookmarked, followup_session_id,
                created_at, updated_at
            )
            SELECT
                id, session_id, COALESCE(turn_id, ''), question_id, question,
                question_type, options_json, correct_answer, explanation,
                difficulty, user_answer, is_correct, bookmarked,
                followup_session_id, created_at, updated_at
            FROM notebook_entries;

            DROP TABLE notebook_entries;
            ALTER TABLE notebook_entries_new RENAME TO notebook_entries;

            CREATE INDEX IF NOT EXISTS idx_notebook_entries_session
                ON notebook_entries(session_id, created_at DESC);

            CREATE INDEX IF NOT EXISTS idx_notebook_entries_bookmarked
                ON notebook_entries(bookmarked, created_at DESC);
            """
        )

    @staticmethod
    def _migrate_notebook_entries_add_user_answer_images(
        conn: sqlite3.Connection,
    ) -> None:
        """Back-fill ``user_answer_images_json`` on legacy DBs.

        The column stores a JSON array of ``{id, url, filename, mime_type}``
        records for image attachments uploaded as part of the learner's
        answer. The bytes themselves live in the AttachmentStore; we only
        keep references in the row so notebook_entries stays lean.
        """
        cols = {row[1] for row in conn.execute("PRAGMA table_info(notebook_entries)").fetchall()}
        if not cols:
            return
        if "user_answer_images_json" not in cols:
            conn.execute(
                "ALTER TABLE notebook_entries ADD COLUMN user_answer_images_json TEXT DEFAULT '[]'"
            )

    @staticmethod
    def _migrate_notebook_entries_add_ai_judgment(
        conn: sqlite3.Connection,
    ) -> None:
        """Back-fill ``ai_judgment`` on legacy DBs.

        Stores the latest AI-judge text per entry as plain markdown. Empty
        string means the learner has not run the AI judge for this entry
        yet.
        """
        cols = {row[1] for row in conn.execute("PRAGMA table_info(notebook_entries)").fetchall()}
        if not cols:
            return
        if "ai_judgment" not in cols:
            conn.execute("ALTER TABLE notebook_entries ADD COLUMN ai_judgment TEXT DEFAULT ''")

    @staticmethod
    def _migrate_notebook_entries_add_assessment_review(
        conn: sqlite3.Connection,
    ) -> None:
        """Add provenance and review-state columns without rewriting history."""
        cols = {row[1] for row in conn.execute("PRAGMA table_info(notebook_entries)").fetchall()}
        if not cols:
            return
        additions = {
            "source": "TEXT NOT NULL DEFAULT 'deep_question'",
            "material_id": "TEXT NOT NULL DEFAULT ''",
            "material_title": "TEXT NOT NULL DEFAULT ''",
            "section_id": "TEXT NOT NULL DEFAULT ''",
            "section_title": "TEXT NOT NULL DEFAULT ''",
            "score_trend": "TEXT NOT NULL DEFAULT 'new'",
            "resolved": "INTEGER DEFAULT 0",
        }
        for name, definition in additions.items():
            if name not in cols:
                conn.execute(f"ALTER TABLE notebook_entries ADD COLUMN {name} {definition}")
        conn.execute(
            """
            CREATE INDEX IF NOT EXISTS idx_notebook_entries_review
            ON notebook_entries(source, material_id, resolved, created_at DESC)
            """
        )

    @staticmethod
    def _migrate_notebook_entries_add_assessment_v2(
        conn: sqlite3.Connection,
    ) -> None:
        """Add the cross-surface review columns without rewriting history."""
        cols = {row[1] for row in conn.execute("PRAGMA table_info(notebook_entries)").fetchall()}
        if not cols:
            return
        additions = {
            "assessment_type": "TEXT NOT NULL DEFAULT ''",
            "result": "TEXT NOT NULL DEFAULT ''",
            "mastery_path_id": "TEXT NOT NULL DEFAULT ''",
            "knowledge_point_id": "TEXT NOT NULL DEFAULT ''",
            "attempt_count": "INTEGER NOT NULL DEFAULT 1",
            "hints_used": "INTEGER NOT NULL DEFAULT 0",
            "confidence": "REAL",
            "response_time": "REAL",
            "quality": "REAL",
        }
        for name, definition in additions.items():
            if name not in cols:
                conn.execute(f"ALTER TABLE notebook_entries ADD COLUMN {name} {definition}")
        conn.execute(
            """
            CREATE INDEX IF NOT EXISTS idx_notebook_entries_linkage
            ON notebook_entries(mastery_path_id, knowledge_point_id)
            """
        )
        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS reading_quiz_pending (
                material_id TEXT NOT NULL,
                locator INTEGER NOT NULL,
                question_id TEXT NOT NULL,
                question_json TEXT NOT NULL,
                created_at REAL NOT NULL,
                PRIMARY KEY (material_id, locator, question_id)
            )
            """
        )
        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS reading_quiz_rewards (
                material_id TEXT NOT NULL,
                locator INTEGER NOT NULL,
                stars INTEGER NOT NULL,
                updated_at REAL NOT NULL,
                PRIMARY KEY (material_id, locator)
            )
            """
        )

    @staticmethod
    def _migrate_notebook_entry_origins(conn: sqlite3.Connection) -> None:
        """Detach Question Notebook identity from conversation ownership.

        SQLite cannot relax a ``NOT NULL`` foreign key or replace a table-level
        unique constraint in place, so this migration rebuilds the table once.
        IDs are copied verbatim, which preserves categories, practice state,
        bookmarks, answer images and external references. Existing rows become
        conversation origins; future non-conversation rows may keep
        ``session_id`` NULL without manufacturing a chat.
        """
        info = {
            row[1]: row for row in conn.execute("PRAGMA table_info(notebook_entries)").fetchall()
        }
        if not info:
            return
        origin_unique = False
        for index in conn.execute("PRAGMA index_list(notebook_entries)").fetchall():
            if not index[2]:
                continue
            columns = [row[2] for row in conn.execute(f"PRAGMA index_info({index[1]})").fetchall()]
            if columns == ["origin_type", "origin_ref", "turn_id", "question_id"]:
                origin_unique = True
                break
        if (
            "origin_type" in info
            and "origin_ref" in info
            and not bool(info["session_id"][3])
            and origin_unique
        ):
            conn.execute(
                "CREATE INDEX IF NOT EXISTS idx_notebook_entries_origin "
                "ON notebook_entries(origin_type, origin_ref, created_at DESC)"
            )
            return

        origin_type = (
            "CASE WHEN origin_type IN ('conversation','external_import','document_analysis') "
            "THEN origin_type WHEN session_id IS NOT NULL AND session_id != '' "
            "THEN 'conversation' ELSE 'external_import' END"
            if "origin_type" in info
            else "CASE WHEN session_id IS NOT NULL AND session_id != '' "
            "THEN 'conversation' ELSE 'external_import' END"
        )
        origin_ref = (
            "COALESCE(NULLIF(origin_ref, ''), NULLIF(session_id, ''), 'legacy:' || id)"
            if "origin_ref" in info
            else "COALESCE(NULLIF(session_id, ''), 'legacy:' || id)"
        )

        conn.commit()
        conn.execute("PRAGMA foreign_keys = OFF")
        try:
            conn.execute("BEGIN IMMEDIATE")
            conn.execute("DROP TABLE IF EXISTS notebook_entries_new")
            conn.execute(
                """
                CREATE TABLE notebook_entries_new (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id TEXT REFERENCES sessions(id) ON DELETE SET NULL,
                    origin_type TEXT NOT NULL DEFAULT 'conversation',
                    origin_ref TEXT NOT NULL,
                    turn_id TEXT NOT NULL DEFAULT '',
                    question_id TEXT NOT NULL,
                    question TEXT NOT NULL,
                    question_type TEXT DEFAULT '',
                    options_json TEXT DEFAULT '{}',
                    correct_answer TEXT DEFAULT '',
                    explanation TEXT DEFAULT '',
                    difficulty TEXT DEFAULT '',
                    user_answer TEXT DEFAULT '',
                    user_answer_images_json TEXT DEFAULT '[]',
                    source TEXT NOT NULL DEFAULT 'deep_question',
                    material_id TEXT NOT NULL DEFAULT '',
                    material_title TEXT NOT NULL DEFAULT '',
                    section_id TEXT NOT NULL DEFAULT '',
                    section_title TEXT NOT NULL DEFAULT '',
                    score_trend TEXT NOT NULL DEFAULT 'new',
                    assessment_type TEXT NOT NULL DEFAULT '',
                    result TEXT NOT NULL DEFAULT '',
                    mastery_path_id TEXT NOT NULL DEFAULT '',
                    knowledge_point_id TEXT NOT NULL DEFAULT '',
                    attempt_count INTEGER NOT NULL DEFAULT 1,
                    hints_used INTEGER NOT NULL DEFAULT 0,
                    confidence REAL,
                    response_time REAL,
                    quality REAL,
                    is_correct INTEGER DEFAULT 0,
                    resolved INTEGER DEFAULT 0,
                    bookmarked INTEGER DEFAULT 0,
                    followup_session_id TEXT DEFAULT '',
                    ai_judgment TEXT DEFAULT '',
                    created_at REAL NOT NULL,
                    updated_at REAL NOT NULL,
                    UNIQUE(origin_type, origin_ref, turn_id, question_id)
                )
                """
            )
            conn.execute(
                f"""
                INSERT INTO notebook_entries_new (
                    id, session_id, origin_type, origin_ref, turn_id, question_id,
                    question, question_type, options_json, correct_answer,
                    explanation, difficulty, user_answer, user_answer_images_json,
                    source, material_id, material_title, section_id, section_title,
                    score_trend, assessment_type, result, mastery_path_id,
                    knowledge_point_id, attempt_count, hints_used, confidence,
                    response_time, quality, is_correct, resolved, bookmarked,
                    followup_session_id, ai_judgment, created_at, updated_at
                )
                SELECT
                    id, NULLIF(session_id, ''), {origin_type}, {origin_ref},
                    COALESCE(turn_id, ''), question_id, question, question_type,
                    options_json, correct_answer, explanation, difficulty,
                    user_answer, user_answer_images_json, source, material_id,
                    material_title, section_id, section_title, score_trend,
                    assessment_type, result, mastery_path_id, knowledge_point_id,
                    attempt_count, hints_used, confidence, response_time, quality,
                    is_correct, resolved, bookmarked, followup_session_id,
                    ai_judgment, created_at, updated_at
                FROM notebook_entries
                """  # nosec B608 - expressions depend only on inspected schema columns
            )
            conn.execute("DROP TABLE notebook_entries")
            conn.execute("ALTER TABLE notebook_entries_new RENAME TO notebook_entries")
            conn.execute(
                "CREATE INDEX idx_notebook_entries_session "
                "ON notebook_entries(session_id, created_at DESC)"
            )
            conn.execute(
                "CREATE INDEX idx_notebook_entries_origin "
                "ON notebook_entries(origin_type, origin_ref, created_at DESC)"
            )
            conn.execute(
                "CREATE INDEX idx_notebook_entries_bookmarked "
                "ON notebook_entries(bookmarked, created_at DESC)"
            )
            conn.execute(
                "CREATE INDEX idx_notebook_entries_review "
                "ON notebook_entries(source, material_id, resolved, created_at DESC)"
            )
            conn.execute(
                "CREATE INDEX idx_notebook_entries_linkage "
                "ON notebook_entries(mastery_path_id, knowledge_point_id)"
            )
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.execute("PRAGMA foreign_keys = ON")

    @staticmethod
    def _migrate_assessment_attempt_origins(conn: sqlite3.Connection) -> None:
        """Give immutable attempts the same independent origin as their projection."""
        info = {
            row[1]: row for row in conn.execute("PRAGMA table_info(assessment_attempts)").fetchall()
        }
        if not info:
            return
        if "origin_type" in info and "origin_ref" in info and not bool(info["session_id"][3]):
            conn.execute(
                "CREATE INDEX IF NOT EXISTS idx_assessment_attempts_origin_time "
                "ON assessment_attempts(origin_type, origin_ref, occurred_at DESC)"
            )
            return
        origin_type = (
            "CASE WHEN origin_type IN ('conversation','external_import','document_analysis') "
            "THEN origin_type WHEN session_id IS NOT NULL AND session_id != '' "
            "THEN 'conversation' ELSE 'external_import' END"
            if "origin_type" in info
            else "CASE WHEN session_id IS NOT NULL AND session_id != '' "
            "THEN 'conversation' ELSE 'external_import' END"
        )
        origin_ref = (
            "COALESCE(NULLIF(origin_ref, ''), NULLIF(session_id, ''), 'attempt:' || attempt_id)"
            if "origin_ref" in info
            else "COALESCE(NULLIF(session_id, ''), 'attempt:' || attempt_id)"
        )
        conn.commit()
        conn.execute("PRAGMA foreign_keys = OFF")
        try:
            conn.execute("BEGIN IMMEDIATE")
            conn.execute("DROP TABLE IF EXISTS assessment_attempts_new")
            conn.execute(
                """
                CREATE TABLE assessment_attempts_new (
                    attempt_id TEXT PRIMARY KEY,
                    notebook_entry_id INTEGER,
                    session_id TEXT REFERENCES sessions(id) ON DELETE SET NULL,
                    origin_type TEXT NOT NULL DEFAULT 'conversation',
                    origin_ref TEXT NOT NULL,
                    turn_id TEXT NOT NULL DEFAULT '',
                    question_id TEXT NOT NULL,
                    source TEXT NOT NULL,
                    assessment_type TEXT NOT NULL,
                    result TEXT NOT NULL,
                    mastery_path_id TEXT NOT NULL DEFAULT '',
                    knowledge_point_id TEXT NOT NULL DEFAULT '',
                    occurred_at REAL NOT NULL,
                    assessment_json TEXT NOT NULL,
                    linked_applied INTEGER NOT NULL DEFAULT 0
                )
                """
            )
            conn.execute(
                f"""
                INSERT INTO assessment_attempts_new (
                    attempt_id, notebook_entry_id, session_id, origin_type,
                    origin_ref, turn_id, question_id, source, assessment_type,
                    result, mastery_path_id, knowledge_point_id, occurred_at,
                    assessment_json
                )
                SELECT
                    attempt_id, notebook_entry_id, NULLIF(session_id, ''),
                    {origin_type}, {origin_ref}, turn_id, question_id, source,
                    assessment_type, result, mastery_path_id,
                    knowledge_point_id, occurred_at, assessment_json
                FROM assessment_attempts
                """  # nosec B608 - expressions depend only on inspected schema columns
            )
            conn.execute("DROP TABLE assessment_attempts")
            conn.execute("ALTER TABLE assessment_attempts_new RENAME TO assessment_attempts")
            conn.execute(
                "CREATE INDEX idx_assessment_attempts_session_time "
                "ON assessment_attempts(session_id, occurred_at DESC)"
            )
            conn.execute(
                "CREATE INDEX idx_assessment_attempts_origin_time "
                "ON assessment_attempts(origin_type, origin_ref, occurred_at DESC)"
            )
            conn.execute(
                "CREATE INDEX idx_assessment_attempts_linkage "
                "ON assessment_attempts(mastery_path_id, knowledge_point_id, occurred_at DESC)"
            )
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.execute("PRAGMA foreign_keys = ON")

    @staticmethod
    def _migrate_assessment_attempt_link_state(conn: sqlite3.Connection) -> None:
        columns = {
            row[1] for row in conn.execute("PRAGMA table_info(assessment_attempts)").fetchall()
        }
        if "linked_applied" not in columns:
            conn.execute(
                "ALTER TABLE assessment_attempts ADD COLUMN linked_applied INTEGER NOT NULL DEFAULT 0"
            )
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_assessment_attempts_pending_link "
            "ON assessment_attempts(occurred_at) WHERE linked_applied = 0 "
            "AND mastery_path_id != '' AND knowledge_point_id != '' "
            "AND source != 'mastery_path' AND result NOT IN ('ungraded', 'voided')"
        )

    async def _run(self, fn, *args):
        async with self._lock:
            return await asyncio.to_thread(fn, *args)

    @contextmanager
    def _connect(self) -> Iterator[sqlite3.Connection]:
        # sqlite3.Connection's own context manager commits/rolls back but does
        # NOT close the connection — so naked `with sqlite3.connect(...)` leaks
        # one FD per call until GC. Wrap it so each call site gets both
        # transaction semantics and deterministic close. The inner `with conn`
        # commits on clean exit and rolls back on exception, so call sites do
        # NOT need an explicit conn.commit() (any remaining ones are no-ops).
        conn = sqlite3.connect(self.db_path, timeout=30.0)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON")
        conn.execute("PRAGMA busy_timeout = 30000")
        try:
            with conn:
                yield conn
        finally:
            conn.close()

    def _create_session_sync(
        self,
        title: str | None = None,
        session_id: str | None = None,
    ) -> dict[str, Any]:
        with self._connect() as conn:
            return self._insert_session(conn, title, session_id)

    def _insert_session(
        self, conn: sqlite3.Connection, title: str | None = None, session_id: str | None = None
    ) -> dict[str, Any]:
        now = time.time()
        resolved_id = session_id or f"unified_{int(now * 1000)}_{uuid.uuid4().hex[:8]}"
        resolved_title = (title or "New conversation").strip() or "New conversation"
        from deeptutor.services.workspace.context import get_workspace_scope

        scope = get_workspace_scope()
        preferences = {"workspace_id": scope.workspace_id} if scope is not None else {}
        conn.execute(
            """INSERT INTO sessions (
                id, title, created_at, updated_at,
                compressed_summary, summary_up_to_msg_id, preferences_json
            ) VALUES (?, ?, ?, ?, '', 0, ?)""",
            (resolved_id, resolved_title[:100], now, now, _json_dumps(preferences)),
        )
        return {
            "id": resolved_id,
            "session_id": resolved_id,
            "title": resolved_title[:100],
            "created_at": now,
            "updated_at": now,
            "compressed_summary": "",
            "summary_up_to_msg_id": 0,
            "preferences": preferences,
        }

    async def create_session(
        self,
        title: str | None = None,
        session_id: str | None = None,
    ) -> dict[str, Any]:
        return await self._run(self._create_session_sync, title, session_id)

    def _reserve_submission_sync(
        self, submission_id: str, digest: str, session_id: str | None
    ) -> str:
        from deeptutor.services.workspace.context import get_workspace_scope

        scope = get_workspace_scope()
        workspace_id = scope.workspace_id if scope is not None else ""
        with self._connect() as conn:
            # #1793: bind a first submission and its conversation atomically,
            # before the transport can lose the SESSION acknowledgement.
            conn.execute("BEGIN IMMEDIATE")
            prior = conn.execute(
                "SELECT * FROM turn_submissions WHERE client_submission_id = ?", (submission_id,)
            ).fetchone()
            if prior is not None:
                if prior["superseded_by"]:
                    raise RuntimeError("This submission was superseded by an edited message.")
                if prior["request_digest"] != digest or prior["workspace_id"] != workspace_id:
                    raise RuntimeError(
                        "This submission ID belongs to a different request or workspace."
                    )
                if session_id and session_id != prior["session_id"]:
                    raise RuntimeError("This submission ID belongs to another conversation.")
                row = conn.execute(
                    "SELECT deleted_at FROM sessions WHERE id = ?", (prior["session_id"],)
                ).fetchone()
                if row is None or row["deleted_at"] is not None:
                    raise RuntimeError(
                        "Restore the original conversation before resending this message."
                    )
                return str(prior["session_id"])
            if session_id:
                row = conn.execute(
                    "SELECT * FROM sessions WHERE id = ? AND deleted_at IS NULL", (session_id,)
                ).fetchone()
                if row is None:
                    raise RuntimeError("Conversation not found in this workspace.")
                preferences = _json_loads(row["preferences_json"], {})
                if str(preferences.get("workspace_id") or "") != workspace_id:
                    raise RuntimeError("The conversation belongs to another workspace.")
            else:
                session_id = str(self._insert_session(conn)["id"])
            conn.execute(
                "INSERT INTO turn_submissions (client_submission_id, workspace_id, request_digest, session_id) VALUES (?, ?, ?, ?)",
                (submission_id, workspace_id, digest, session_id),
            )
            return session_id

    async def reserve_submission(
        self, submission_id: str, digest: str, session_id: str | None = None
    ) -> str:
        return await self._run(self._reserve_submission_sync, submission_id, digest, session_id)

    def _replace_submission_sync(
        self, original_id: str, replacement_id: str, digest: str, session_id: str | None
    ) -> dict[str, Any]:
        """Fence an unadmitted request and reserve its edit in one transaction.

        A transcript is deliberately not consulted to decide admission: a turn
        can already own execution before its user row is written.
        """
        from deeptutor.services.workspace.context import get_workspace_scope

        if original_id == replacement_id:
            raise RuntimeError("An edit requires a new submission ID.")
        scope = get_workspace_scope()
        workspace_id = scope.workspace_id if scope is not None else ""
        with self._connect() as conn:
            conn.execute("BEGIN IMMEDIATE")
            old = conn.execute(
                "SELECT * FROM turn_submissions WHERE client_submission_id=?", (original_id,)
            ).fetchone()
            if old is not None:
                if old["workspace_id"] != workspace_id or (
                    session_id and old["session_id"] != session_id
                ):
                    raise RuntimeError(
                        "The submission belongs to another conversation or workspace."
                    )
                session_id = old["session_id"]
            if session_id:
                session = conn.execute(
                    "SELECT * FROM sessions WHERE id=?", (session_id,)
                ).fetchone()
                if session is None or session["deleted_at"] is not None:
                    raise RuntimeError(
                        "Restore the original conversation before editing this message."
                    )
                if (
                    str(_json_loads(session["preferences_json"], {}).get("workspace_id") or "")
                    != workspace_id
                ):
                    raise RuntimeError("The conversation belongs to another workspace.")
            else:
                session_id = str(self._insert_session(conn)["id"])
            if old is not None and old["turn_id"]:
                turn = conn.execute("SELECT * FROM turns WHERE id=?", (old["turn_id"],)).fetchone()
                user = conn.execute(
                    "SELECT id FROM messages WHERE session_id=? AND role='user' AND json_valid(metadata_json) AND json_extract(metadata_json, '$.client_submission_id')=? ORDER BY id LIMIT 1",
                    (session_id, original_id),
                ).fetchone()
                return {
                    "status": "admitted",
                    "session_id": session_id,
                    "turn": self._serialize_turn(turn),
                    "user_message_id": user["id"] if user else None,
                }
            replacement = conn.execute(
                "SELECT * FROM turn_submissions WHERE client_submission_id=?", (replacement_id,)
            ).fetchone()
            if old is not None and old["superseded_by"]:
                if old["superseded_by"] != replacement_id:
                    raise RuntimeError("This submission was already superseded by another edit.")
                if (
                    replacement is None
                    or replacement["request_digest"] != digest
                    or replacement["superseded_by"]
                ):
                    raise RuntimeError("This replacement ID belongs to a different request.")
            else:
                if replacement is not None:
                    raise RuntimeError("This replacement ID is already in use.")
                conn.execute(
                    "INSERT INTO turn_submissions (client_submission_id, workspace_id, request_digest, session_id) VALUES (?, ?, ?, ?)",
                    (replacement_id, workspace_id, digest, session_id),
                )
                if old is None:
                    # The original may still be in a delayed transport queue.
                    conn.execute(
                        "INSERT INTO turn_submissions (client_submission_id, workspace_id, request_digest, session_id, superseded_by) VALUES (?, ?, '', ?, ?)",
                        (original_id, workspace_id, session_id, replacement_id),
                    )
                else:
                    conn.execute(
                        "UPDATE turn_submissions SET superseded_by=? WHERE client_submission_id=?",
                        (replacement_id, original_id),
                    )
            return {"status": "reserved", "session_id": session_id, "submission_id": replacement_id}

    async def replace_submission(
        self, original_id: str, replacement_id: str, digest: str, session_id: str | None = None
    ) -> dict[str, Any]:
        return await self._run(
            self._replace_submission_sync, original_id, replacement_id, digest, session_id
        )

    def _submission_turn_sync(self, submission_id: str) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                "SELECT t.* FROM turns t JOIN turn_submissions s ON s.turn_id=t.id WHERE s.client_submission_id=?",
                (submission_id,),
            ).fetchone()
        return self._serialize_turn(row) if row is not None else None

    async def submission_turn(self, submission_id: str) -> dict[str, Any] | None:
        return await self._run(self._submission_turn_sync, submission_id)

    async def ensure_notebook_session(self, session_id: str, title: str) -> bool:
        """Insert a placeholder session row so notebook-only sources can file
        entries against it (partner chat has no rows in this store otherwise).

        Returns ``True`` when a row was created, ``False`` when it existed.
        """
        return await self._run(self._ensure_notebook_session_sync, session_id, title)

    def _ensure_notebook_session_sync(self, session_id: str, title: str) -> bool:
        resolved_id = (session_id or "").strip()
        if not resolved_id:
            raise ValueError("ensure_notebook_session requires a session_id")
        now = time.time()
        with self._connect() as conn:
            exists = conn.execute("SELECT id FROM sessions WHERE id = ?", (resolved_id,)).fetchone()
            if exists is not None:
                return False
            conn.execute(
                """
                INSERT INTO sessions (id, title, created_at, updated_at)
                VALUES (?, ?, ?, ?)
                """,
                (resolved_id, (title or "New conversation").strip()[:100], now, now),
            )
            conn.commit()
        return True

    def _get_session_sync(self, session_id: str) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                """
                SELECT
                    s.id,
                    s.title,
                    s.created_at,
                    s.updated_at,
                    s.compressed_summary,
                    s.summary_up_to_msg_id,
                    s.preferences_json,
                    COALESCE(
                        (
                            SELECT t.status
                            FROM turns t
                            WHERE t.session_id = s.id
                            ORDER BY t.updated_at DESC
                            LIMIT 1
                        ),
                        'idle'
                    ) AS status,
                    COALESCE(
                        (
                            SELECT t.id
                            FROM turns t
                            WHERE t.session_id = s.id
                              AND t.status IN ('queued', 'running', 'waiting_input')
                            ORDER BY t.updated_at DESC
                            LIMIT 1
                        ),
                        ''
                    ) AS active_turn_id,
                    COALESCE(
                        (
                            SELECT t.capability
                            FROM turns t
                            WHERE t.session_id = s.id
                            ORDER BY t.updated_at DESC
                            LIMIT 1
                        ),
                        ''
                    ) AS capability
                FROM sessions
                s
                WHERE s.id = ?
                """,
                (session_id,),
            ).fetchone()
        if not row:
            return None
        payload = dict(row)
        payload["session_id"] = payload["id"]
        payload["preferences"] = _json_loads(payload.pop("preferences_json", ""), {})
        return payload

    async def get_session(self, session_id: str) -> dict[str, Any] | None:
        return await self._run(self._get_session_sync, session_id)

    async def ensure_session(
        self,
        session_id: str | None = None,
    ) -> dict[str, Any]:
        if session_id:
            session = await self.get_session(session_id)
            if session is not None:
                return session
        return await self.create_session()

    @staticmethod
    def _serialize_turn(row: sqlite3.Row) -> dict[str, Any]:
        return TurnRecord(
            id=row["id"],
            session_id=row["session_id"],
            capability=row["capability"] or "",
            status=row["status"] or "running",
            error=row["error"] or "",
            created_at=row["created_at"],
            updated_at=row["updated_at"],
            finished_at=row["finished_at"],
            last_seq=row["last_seq"] if "last_seq" in row.keys() else 0,
            owner_id=row["owner_id"] if "owner_id" in row.keys() else "",
            fencing_token=(int(row["fencing_token"] or 0) if "fencing_token" in row.keys() else 0),
            state_version=(int(row["state_version"] or 1) if "state_version" in row.keys() else 1),
            failure_code=(row["failure_code"] or "" if "failure_code" in row.keys() else ""),
            retryable=(bool(row["retryable"]) if "retryable" in row.keys() else False),
            assistant_message_id=(
                row["assistant_message_id"] if "assistant_message_id" in row.keys() else None
            ),
        ).to_dict()

    def _begin_turn_sync(
        self,
        session_id: str,
        capability: str = "",
        turn_id: str | None = None,
        owner_id: str = "",
        fencing_token: int = 0,
        submission_id: str | None = None,
    ) -> dict[str, Any]:
        now = time.time()
        resolved_turn_id = turn_id or f"turn_{int(now * 1000)}_{uuid.uuid4().hex[:10]}"
        replay_fields: dict[str, Any] = {}
        try:
            with self._connect() as conn:
                # Serialize the active-turn check and insert across processes.
                conn.execute("BEGIN IMMEDIATE")
                session = conn.execute(
                    "SELECT id FROM sessions WHERE id = ?", (session_id,)
                ).fetchone()
                if session is None:
                    raise ValueError(f"Session not found: {session_id}")
                if submission_id:
                    reservation = conn.execute(
                        "SELECT * FROM turn_submissions WHERE client_submission_id=?",
                        (submission_id,),
                    ).fetchone()
                    if reservation is not None and reservation["superseded_by"]:
                        raise RuntimeError("This submission was superseded by an edited message.")
                    if reservation is not None and reservation["session_id"] != session_id:
                        raise RuntimeError("This submission belongs to another conversation.")
                    old = conn.execute(
                        "SELECT t.* FROM turns t JOIN turn_submissions s ON s.turn_id=t.id WHERE s.client_submission_id=? AND s.session_id=?",
                        (submission_id, session_id),
                    ).fetchone()
                    if old is not None:
                        if old["status"] not in {"failed", "cancelled"}:
                            return {**self._serialize_turn(old), SUBMISSION_REPLAY_FIELD: True}
                        user = conn.execute(
                            "SELECT id FROM messages WHERE session_id=? AND role='user' AND json_valid(metadata_json) AND json_extract(metadata_json, '$.client_submission_id')=? ORDER BY id LIMIT 1",
                            (session_id, submission_id),
                        ).fetchone()
                        if user is not None:
                            replay_fields = {
                                SUBMISSION_USER_FIELD: user["id"],
                                SUBMISSION_ASSISTANT_FIELD: old["assistant_message_id"],
                                SUBMISSION_PREVIOUS_TURN_FIELD: old["id"],
                            }
                active = conn.execute(
                    """
                    SELECT id
                    FROM turns
                    WHERE session_id = ?
                      AND status IN ('queued', 'running', 'waiting_input')
                    ORDER BY updated_at DESC
                    LIMIT 1
                    """,
                    (session_id,),
                ).fetchone()
                if active is not None:
                    raise ActiveTurnConflict(
                        f"Session already has an active turn: {active['id']}",
                        turn_id=str(active["id"]),
                    )
                conn.execute(
                    """
                    INSERT INTO turns (
                        id, session_id, capability, status, error,
                        created_at, updated_at, finished_at, owner_id,
                        fencing_token, state_version, failure_code, retryable
                    ) VALUES (?, ?, ?, 'running', '', ?, ?, NULL, ?, ?, 1, '', 0)
                    """,
                    (
                        resolved_turn_id,
                        session_id,
                        capability or "",
                        now,
                        now,
                        owner_id or "",
                        max(0, int(fencing_token)),
                    ),
                )
                if submission_id:
                    conn.execute(
                        "UPDATE turn_submissions SET turn_id=? WHERE client_submission_id=? AND session_id=?",
                        (resolved_turn_id, submission_id, session_id),
                    )
        except sqlite3.IntegrityError as exc:
            # The partial unique index wins races where another process inserts
            # after our read but before our insert.
            raise ActiveTurnConflict(f"Session already has an active turn: {session_id}") from exc
        return {
            **replay_fields,
            "id": resolved_turn_id,
            "turn_id": resolved_turn_id,
            "session_id": session_id,
            "capability": capability or "",
            "status": "running",
            "error": "",
            "created_at": now,
            "updated_at": now,
            "finished_at": None,
            "last_seq": 0,
            "owner_id": owner_id or "",
            "fencing_token": max(0, int(fencing_token)),
            "state_version": 1,
            "failure_code": "",
            "retryable": False,
        }

    async def begin_turn(
        self,
        session_id: str,
        capability: str = "",
        *,
        turn_id: str | None = None,
        owner_id: str = "",
        fencing_token: int = 0,
        submission_id: str | None = None,
    ) -> dict[str, Any]:
        return await self._run(
            self._begin_turn_sync,
            session_id,
            capability,
            turn_id,
            owner_id,
            fencing_token,
            submission_id,
        )

    async def create_turn(
        self, session_id: str, capability: str = "", *, submission_id: str | None = None
    ) -> dict[str, Any]:
        return await self.begin_turn(session_id, capability, submission_id=submission_id)

    def _get_turn_sync(self, turn_id: str) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                """
                SELECT
                    t.*,
                    COALESCE((SELECT MAX(seq) FROM turn_events te WHERE te.turn_id = t.id), 0) AS last_seq
                FROM turns t
                WHERE t.id = ?
                """,
                (turn_id,),
            ).fetchone()
        if row is None:
            return None
        return self._serialize_turn(row)

    async def get_turn(self, turn_id: str) -> dict[str, Any] | None:
        return await self._run(self._get_turn_sync, turn_id)

    def _get_active_turn_sync(self, session_id: str) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                """
                SELECT
                    t.*,
                    COALESCE((SELECT MAX(seq) FROM turn_events te WHERE te.turn_id = t.id), 0) AS last_seq
                FROM turns t
                WHERE t.session_id = ?
                  AND t.status IN ('queued', 'running', 'waiting_input')
                ORDER BY t.updated_at DESC
                LIMIT 1
                """,
                (session_id,),
            ).fetchone()
        if row is None:
            return None
        return self._serialize_turn(row)

    async def get_active_turn(self, session_id: str) -> dict[str, Any] | None:
        return await self._run(self._get_active_turn_sync, session_id)

    def _list_active_turns_sync(self, session_id: str) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                """
                SELECT
                    t.*,
                    COALESCE((SELECT MAX(seq) FROM turn_events te WHERE te.turn_id = t.id), 0) AS last_seq
                FROM turns t
                WHERE t.session_id = ?
                  AND t.status IN ('queued', 'running', 'waiting_input')
                ORDER BY t.updated_at DESC
                """,
                (session_id,),
            ).fetchall()
        return [self._serialize_turn(row) for row in rows]

    async def list_active_turns(self, session_id: str) -> list[dict[str, Any]]:
        return await self._run(self._list_active_turns_sync, session_id)

    def _list_orphaned_failed_turns_sync(self, session_id: str) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                """
                SELECT t.*, 0 AS last_seq
                FROM turns t
                WHERE t.session_id = ? AND t.status = 'failed'
                  AND t.assistant_message_id IS NULL
                ORDER BY t.created_at, t.id
                """,
                (session_id,),
            ).fetchall()
        return [self._serialize_turn(row) for row in rows]

    async def list_orphaned_failed_turns(self, session_id: str) -> list[dict[str, Any]]:
        return await self._run(self._list_orphaned_failed_turns_sync, session_id)

    def _list_nonterminal_turns_sync(self) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                """
                SELECT
                    t.*,
                    COALESCE((SELECT MAX(seq) FROM turn_events te WHERE te.turn_id = t.id), 0)
                        AS last_seq
                FROM turns t
                WHERE t.status IN ('queued', 'running', 'waiting_input')
                ORDER BY t.updated_at ASC
                """
            ).fetchall()
        return [self._serialize_turn(row) for row in rows]

    async def list_nonterminal_turns(self) -> list[dict[str, Any]]:
        return await self._run(self._list_nonterminal_turns_sync)

    def _transition_turn_sync(
        self,
        turn_id: str,
        status: str,
        expected_status: str | None = None,
        fencing_token: int | None = None,
        error: str = "",
        failure_code: str = "",
        retryable: bool = False,
    ) -> bool:
        if status not in ALL_TURN_STATUSES:
            raise ValueError(f"Unsupported turn status: {status}")
        now = time.time()
        finished_at = now if status in TERMINAL_TURN_STATUSES else None
        with self._connect() as conn:
            conn.execute("BEGIN IMMEDIATE")
            current = conn.execute(
                "SELECT status, fencing_token FROM turns WHERE id = ?", (turn_id,)
            ).fetchone()
            if current is None:
                return False
            if expected_status is not None and current["status"] != expected_status:
                return False
            if fencing_token is not None and int(current["fencing_token"] or 0) != int(
                fencing_token
            ):
                return False
            if current["status"] in TERMINAL_TURN_STATUSES and current["status"] != status:
                return False
            cur = conn.execute(
                """
                UPDATE turns
                SET status = ?, error = ?, failure_code = ?, updated_at = ?,
                    finished_at = ?, state_version = state_version + 1, retryable = ?
                WHERE id = ?
                """,
                (
                    status,
                    error or "",
                    failure_code or "",
                    now,
                    finished_at,
                    int(bool(retryable)),
                    turn_id,
                ),
            )
        return cur.rowcount > 0

    async def transition_turn(
        self,
        turn_id: str,
        status: str,
        *,
        expected_status: str | None = None,
        fencing_token: int | None = None,
        error: str = "",
        failure_code: str = "",
        retryable: bool = False,
    ) -> bool:
        return await self._run(
            self._transition_turn_sync,
            turn_id,
            status,
            expected_status,
            fencing_token,
            error,
            failure_code,
            retryable,
        )

    async def update_turn_status(self, turn_id: str, status: str, error: str = "") -> bool:
        return await self.transition_turn(turn_id, status, error=error)

    @staticmethod
    def _event_matches_row(event: dict[str, Any], row: sqlite3.Row) -> bool:
        return (
            str(event.get("type", "")) == (row["type"] or "")
            and str(event.get("source", "")) == (row["source"] or "")
            and str(event.get("stage", "")) == (row["stage"] or "")
            and str(event.get("content", "") or "") == (row["content"] or "")
            and (event.get("metadata") or {}) == _json_loads(row["metadata_json"], {})
        )

    @staticmethod
    def _turn_event_row_to_payload(row: sqlite3.Row) -> dict[str, Any]:
        return {
            "type": row["type"],
            "source": row["source"] or "",
            "stage": row["stage"] or "",
            "content": row["content"] or "",
            "metadata": _json_loads(row["metadata_json"], {}),
            "session_id": "",
            "turn_id": row["turn_id"],
            "seq": row["seq"],
            "timestamp": row["timestamp"],
        }

    def _append_turn_events_sync(
        self,
        turn_id: str,
        events: list[dict[str, Any]],
        fencing_token: int | None = None,
    ) -> list[dict[str, Any]]:
        # Batch variant of _append_turn_event_sync: one transaction for the whole
        # post-stream flush instead of one fsync'd commit per event. On slow
        # storage (e.g. NAS spinning disks) per-event commits stretch a turn's
        # finalisation to minutes while the client spinner keeps running.
        now = time.time()
        with self._connect() as conn:
            conn.execute("BEGIN IMMEDIATE")
            turn = conn.execute(
                "SELECT id, session_id, fencing_token FROM turns WHERE id = ?", (turn_id,)
            ).fetchone()
            if turn is None:
                raise ValueError(f"Turn not found: {turn_id}")
            if fencing_token is not None and int(turn["fencing_token"] or 0) != int(fencing_token):
                raise RuntimeError(f"Turn lease lost: {turn_id}")
            row = conn.execute(
                "SELECT COALESCE(MAX(seq), 0) AS last_seq FROM turn_events WHERE turn_id = ?",
                (turn_id,),
            ).fetchone()
            next_seq = (int(row["last_seq"]) if row else 0) + 1
            payloads: list[dict[str, Any]] = []
            for event in events:
                provided_seq = int(event.get("seq") or 0)
                if provided_seq > 0:
                    seq = provided_seq
                    next_seq = max(next_seq, provided_seq + 1)
                else:
                    seq = next_seq
                    next_seq += 1
                payload = dict(event)
                payload["seq"] = seq
                payload["turn_id"] = payload.get("turn_id") or turn_id
                payload["session_id"] = payload.get("session_id") or turn["session_id"]
                existing = conn.execute(
                    """
                    SELECT type, source, stage, content, metadata_json, timestamp
                    FROM turn_events WHERE turn_id = ? AND seq = ?
                    """,
                    (turn_id, seq),
                ).fetchone()
                if existing is not None:
                    if not self._event_matches_row(payload, existing):
                        raise ValueError(f"Turn event conflict: {turn_id} seq={seq}")
                    payload["timestamp"] = existing["timestamp"]
                    payloads.append(payload)
                    continue
                timestamp = float(payload.get("timestamp") or now)
                payload["timestamp"] = timestamp
                payloads.append(payload)
                conn.execute(
                    """
                    INSERT INTO turn_events (
                        turn_id, seq, type, source, stage, content,
                        metadata_json, timestamp, created_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (
                        turn_id,
                        seq,
                        payload.get("type", ""),
                        payload.get("source", ""),
                        payload.get("stage", ""),
                        payload.get("content", "") or "",
                        _json_dumps(payload.get("metadata", {})),
                        timestamp,
                        now,
                    ),
                )
            if events:
                conn.execute(
                    "UPDATE turns SET updated_at = ? WHERE id = ?",
                    (now, turn_id),
                )
        return payloads

    async def append_events(
        self,
        turn_id: str,
        events: list[dict[str, Any]],
        *,
        fencing_token: int | None = None,
    ) -> list[dict[str, Any]]:
        return await self._run(
            self._append_turn_events_sync,
            turn_id,
            events,
            fencing_token,
        )

    async def append_turn_event(self, turn_id: str, event: dict[str, Any]) -> dict[str, Any]:
        payloads = await self.append_events(turn_id, [event])
        return payloads[0]

    async def append_turn_events(
        self, turn_id: str, events: list[dict[str, Any]]
    ) -> list[dict[str, Any]]:
        return await self.append_events(turn_id, events)

    def _get_turn_events_sync(self, turn_id: str, after_seq: int = 0) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                """
                SELECT turn_id, seq, type, source, stage, content, metadata_json, timestamp
                FROM turn_events
                WHERE turn_id = ? AND seq > ?
                ORDER BY seq ASC
                """,
                (turn_id, max(0, int(after_seq))),
            ).fetchall()
            turn = conn.execute("SELECT session_id FROM turns WHERE id = ?", (turn_id,)).fetchone()
        session_id = turn["session_id"] if turn else ""
        return [
            {
                "type": row["type"],
                "source": row["source"] or "",
                "stage": row["stage"] or "",
                "content": row["content"] or "",
                "metadata": _json_loads(row["metadata_json"], {}),
                "session_id": session_id,
                "turn_id": row["turn_id"],
                "seq": row["seq"],
                "timestamp": row["timestamp"],
            }
            for row in rows
        ]

    async def get_turn_events(self, turn_id: str, after_seq: int = 0) -> list[dict[str, Any]]:
        return await self._run(self._get_turn_events_sync, turn_id, after_seq)

    async def get_events(self, turn_id: str, after_seq: int = 0) -> list[dict[str, Any]]:
        return await self.get_turn_events(turn_id, after_seq)

    def _link_turn_message_sync(self, turn_id: str, assistant_message_id: int) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                """
                UPDATE turns
                SET assistant_message_id = ?, updated_at = ?
                WHERE id = ?
                  AND session_id = (SELECT session_id FROM messages WHERE id = ?)
                  AND assistant_message_id IS NULL
                """,
                (int(assistant_message_id), time.time(), turn_id, int(assistant_message_id)),
            )
            conn.commit()
        return cur.rowcount > 0

    async def link_turn_message(self, turn_id: str, assistant_message_id: int | str) -> bool:
        # Widened to match SessionStoreProtocol: PocketBase hands out string
        # ids, and the coercion below already accepts either spelling.
        return await self._run(self._link_turn_message_sync, turn_id, int(assistant_message_id))

    def _get_message_trace_sync(
        self,
        session_id: str,
        message_id: int,
        after_seq: int = 0,
        limit: int | None = None,
    ) -> dict[str, Any] | None:
        row_limit = 500 if limit is None else min(1000, max(1, int(limit)))
        with self._connect() as conn:
            message = conn.execute(
                """
                SELECT m.id, m.role, t.id AS turn_id
                FROM messages AS m
                LEFT JOIN turns AS t ON t.assistant_message_id = m.id
                WHERE m.id = ? AND m.session_id = ?
                """,
                (int(message_id), session_id),
            ).fetchone()
            if message is None:
                return None
            turn_id = message["turn_id"]
            if turn_id is None:
                return {
                    "session_id": session_id,
                    "message_id": int(message_id),
                    "turn_id": None,
                    "events": [],
                    "total": 0,
                    "last_seq": 0,
                    "next_seq": None,
                    "complete": True,
                }
            turn = conn.execute("SELECT session_id FROM turns WHERE id = ?", (turn_id,)).fetchone()
            stats = conn.execute(
                """
                SELECT COUNT(*) AS total, COALESCE(MAX(seq), 0) AS last_seq
                FROM turn_events WHERE turn_id = ?
                """,
                (turn_id,),
            ).fetchone()
            rows = conn.execute(
                """
                SELECT turn_id, seq, type, source, stage, content, metadata_json, timestamp
                FROM turn_events
                WHERE turn_id = ? AND seq > ?
                ORDER BY seq ASC
                LIMIT ?
                """,
                (turn_id, max(0, int(after_seq)), row_limit),
            ).fetchall()
            events = [self._turn_event_row_to_payload(row) for row in rows]
            for event in events:
                event["session_id"] = turn["session_id"] if turn else ""
            last_loaded = events[-1]["seq"] if events else int(after_seq)
            complete = last_loaded >= int(stats["last_seq"])
            return {
                "session_id": session_id,
                "message_id": int(message_id),
                "turn_id": turn_id,
                "events": events,
                "total": int(stats["total"]),
                "last_seq": int(stats["last_seq"]),
                "next_seq": None if complete else last_loaded,
                "complete": complete,
            }

    async def get_message_trace(
        self,
        session_id: str,
        message_id: int | str,
        after_seq: int = 0,
        limit: int | None = None,
    ) -> dict[str, Any] | None:
        try:
            resolved_message_id = int(message_id)
        except (TypeError, ValueError):
            return None
        return await self._run(
            self._get_message_trace_sync,
            session_id,
            resolved_message_id,
            after_seq,
            limit,
        )

    def _update_session_title_sync(self, session_id: str, title: str) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                """
                UPDATE sessions
                SET title = ?, updated_at = ?
                WHERE id = ?
                """,
                ((title.strip() or "New conversation")[:100], time.time(), session_id),
            )
            conn.commit()
        return cur.rowcount > 0

    async def update_session_title(self, session_id: str, title: str) -> bool:
        return await self._run(self._update_session_title_sync, session_id, title)

    def _delete_session_sync(self, session_id: str) -> bool:
        with self._connect() as conn:
            cur = conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,))
            conn.commit()
        return cur.rowcount > 0

    async def delete_session(self, session_id: str) -> bool:
        """Permanently remove a conversation and its stored messages."""
        return await self._run(self._delete_session_sync, session_id)

    def _soft_delete_session_sync(self, session_id: str) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                "UPDATE sessions SET deleted_at = ? WHERE id = ? AND deleted_at IS NULL",
                (time.time(), session_id),
            )
            conn.commit()
        return cur.rowcount > 0

    async def soft_delete_session(self, session_id: str) -> bool:
        """Move a session to the recycle bin, where a restore can reach it."""
        return await self._run(self._soft_delete_session_sync, session_id)

    def _restore_session_sync(self, session_id: str) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                "UPDATE sessions SET deleted_at = NULL WHERE id = ? AND deleted_at IS NOT NULL",
                (session_id,),
            )
            conn.commit()
        return cur.rowcount > 0

    async def restore_session(self, session_id: str) -> bool:
        return await self._run(self._restore_session_sync, session_id)

    def _hard_delete_session_sync(self, session_id: str) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                "DELETE FROM sessions WHERE id = ? AND deleted_at IS NOT NULL",
                (session_id,),
            )
            conn.commit()
        return cur.rowcount > 0

    async def hard_delete_session(self, session_id: str) -> bool:
        """Delete a session that is already in the recycle bin, permanently."""
        return await self._run(self._hard_delete_session_sync, session_id)

    def _list_deleted_sessions_sync(
        self,
        limit: int = 50,
        offset: int = 0,
    ) -> list[dict[str, Any]]:
        return self._list_session_summaries_sync("WHERE s.deleted_at IS NOT NULL", limit, offset)

    async def list_deleted_sessions(
        self,
        limit: int = 50,
        offset: int = 0,
    ) -> list[dict[str, Any]]:
        return await self._run(self._list_deleted_sessions_sync, limit, offset)

    def _add_message_sync(
        self,
        session_id: str,
        role: str,
        content: str,
        capability: str = "",
        events: list[dict[str, Any]] | None = None,
        attachments: list[dict[str, Any]] | None = None,
        metadata: dict[str, Any] | None = None,
        parent_message_id: int | str | None | _Unset = _PARENT_AUTO,
    ) -> int:
        now = time.time()
        with self._connect() as conn:
            session = conn.execute(
                "SELECT id, title FROM sessions WHERE id = ?", (session_id,)
            ).fetchone()
            if session is None:
                raise ValueError(f"Session not found: {session_id}")

            resolved_parent_id: int | None
            if isinstance(parent_message_id, _Unset):
                # Legacy auto-append path: chain off the latest row in the
                # session so the linear thread stays connected.
                last_row = conn.execute(
                    "SELECT id FROM messages WHERE session_id = ? ORDER BY id DESC LIMIT 1",
                    (session_id,),
                ).fetchone()
                resolved_parent_id = int(last_row["id"]) if last_row is not None else None
            else:
                # Caller pinned a parent explicitly — including ``None``,
                # which means "attach at the session root" (used by edits
                # of the very first message in a session).
                resolved_parent_id = (
                    int(parent_message_id) if parent_message_id is not None else None
                )

            cur = conn.execute(
                """
                INSERT INTO messages (
                    session_id, role, content, capability, events_json,
                    attachments_json, metadata_json, created_at, parent_message_id
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    session_id,
                    role,
                    content or "",
                    capability or "",
                    _json_dumps(events or []),
                    _json_dumps(attachments or []),
                    _json_dumps(metadata or {}),
                    now,
                    resolved_parent_id,
                ),
            )

            # Title is no longer derived from the first user message — the
            # turn runtime calls an LLM to generate a real summary title
            # once the first user+assistant pair is complete. Until then
            # the session keeps the default sentinel ``New conversation``
            # which the frontend renders as a breathing "New chat" chip.
            conn.execute(
                "UPDATE sessions SET updated_at = ? WHERE id = ?",
                (now, session_id),
            )
            conn.commit()
            return int(cur.lastrowid)

    async def add_message(
        self,
        session_id: str,
        role: str,
        content: str,
        capability: str = "",
        events: list[dict[str, Any]] | None = None,
        attachments: list[dict[str, Any]] | None = None,
        metadata: dict[str, Any] | None = None,
        # ``str`` satisfies SessionStoreProtocol (PocketBase parents are string
        # record ids); on the SQLite backend a non-None parent is always the
        # integer rowid this store itself returned.
        parent_message_id: int | str | None | _Unset = _PARENT_AUTO,
    ) -> int:
        return await self._run(
            self._add_message_sync,
            session_id,
            role,
            content,
            capability,
            events,
            attachments,
            metadata,
            parent_message_id,
        )

    @staticmethod
    def _backfill_import_meta_sync(
        conn: sqlite3.Connection,
        session_id: str,
        current_prefs_json: str | None,
        incoming_prefs: dict[str, Any],
    ) -> bool:
        """Merge agent attribution from a re-import into an existing session's
        ``preferences.import`` block, leaving everything else untouched. Returns
        whether anything changed (so the caller can skip a needless write)."""
        incoming_import = (incoming_prefs or {}).get("import") or {}
        if not incoming_import:
            return False
        prefs = _json_loads(current_prefs_json, {})
        if not isinstance(prefs, dict):
            prefs = {}
        meta = dict(prefs.get("import") or {})
        changed = False
        # Only attribution fields propagate on re-import; source/external_id are
        # part of the dedup identity and never change for a given session.
        for key in ("agent_id", "agent_name", "source_cwd"):
            value = incoming_import.get(key)
            if value and meta.get(key) != value:
                meta[key] = value
                changed = True
        if not changed:
            return False
        prefs["import"] = meta
        conn.execute(
            "UPDATE sessions SET preferences_json = ? WHERE id = ?",
            (_json_dumps(prefs), session_id),
        )
        return True

    def _import_session_sync(
        self,
        session_id: str,
        title: str,
        created_at: float,
        updated_at: float,
        preferences: dict[str, Any],
        messages: list[dict[str, Any]],
    ) -> dict[str, Any]:
        with self._connect() as conn:
            existing = conn.execute(
                "SELECT preferences_json FROM sessions WHERE id = ?", (session_id,)
            ).fetchone()
            if existing is not None:
                # Idempotent on content: a session imported before keeps its
                # (possibly already-continued) state — re-importing the same
                # folder never duplicates rows or clobbers the user's edits.
                # We do, however, backfill agent attribution (agent_id /
                # agent_name) so re-syncing re-tags conversations that were
                # imported before the agent model existed, and an agent rename
                # propagates. This only touches the ``import`` metadata block.
                updated = self._backfill_import_meta_sync(
                    conn, session_id, existing["preferences_json"], preferences
                )
                if updated:
                    conn.commit()
                return {
                    "session_id": session_id,
                    "imported": False,
                    "updated": updated,
                    "message_count": 0,
                }
            safe_title = (title or "").strip()[:100] or "Imported conversation"
            conn.execute(
                """
                INSERT INTO sessions (
                    id, title, created_at, updated_at,
                    compressed_summary, summary_up_to_msg_id, preferences_json
                ) VALUES (?, ?, ?, ?, '', 0, ?)
                """,
                (session_id, safe_title, created_at, updated_at, _json_dumps(preferences or {})),
            )
            prev_id: int | None = None
            count = 0
            for msg in messages:
                cur = conn.execute(
                    """
                    INSERT INTO messages (
                        session_id, role, content, capability, events_json,
                        attachments_json, metadata_json, created_at, parent_message_id
                    ) VALUES (?, ?, ?, '', '[]', '[]', ?, ?, ?)
                    """,
                    (
                        session_id,
                        msg.get("role") or "user",
                        msg.get("content") or "",
                        _json_dumps(msg.get("metadata") or {}),
                        float(msg.get("created_at") or created_at),
                        prev_id,
                    ),
                )
                prev_id = int(cur.lastrowid)
                count += 1
            conn.commit()
        return {"session_id": session_id, "imported": True, "message_count": count}

    async def import_session(
        self,
        session_id: str,
        title: str,
        created_at: float,
        updated_at: float,
        preferences: dict[str, Any] | None,
        messages: list[dict[str, Any]],
    ) -> dict[str, Any]:
        """Persist a pre-existing conversation (imported from an external CLI
        such as Claude Code or Codex) as a normal session, so the chat loop can
        re-open and continue it. ``session_id`` must carry the ``imported_``
        prefix (see :data:`_IMPORTED_ID_PREFIX`). Idempotent by id: a session
        already present is left untouched.
        """
        return await self._run(
            self._import_session_sync,
            session_id,
            title,
            created_at,
            updated_at,
            preferences or {},
            messages,
        )

    async def import_legacy_session(
        self,
        session_id: str,
        title: str,
        created_at: float,
        updated_at: float,
        preferences: dict[str, Any],
        messages: list[dict[str, Any]],
    ) -> dict[str, Any]:
        """Import one v1 JSON chat without overwriting an existing session."""

        return await self.import_session(
            session_id,
            title,
            created_at,
            updated_at,
            preferences,
            messages,
        )

    def _delete_message_sync(self, message_id: int | str) -> bool:
        with self._connect() as conn:
            cur = conn.execute("DELETE FROM messages WHERE id = ?", (int(message_id),))
            conn.commit()
        return cur.rowcount > 0

    async def delete_message(self, message_id: int | str) -> bool:
        return await self._run(self._delete_message_sync, message_id)

    def _delete_turn_by_message_sync(self, session_id: str, message_id: int) -> dict[str, Any]:
        with self._connect() as conn:
            msg = conn.execute(
                """
                SELECT id, session_id, role, attachments_json, metadata_json, created_at, parent_message_id
                FROM messages
                WHERE id = ?
                """,
                (int(message_id),),
            ).fetchone()
            if msg is None or msg["session_id"] != session_id:
                return {
                    "deleted": False,
                    "attachment_ids": [],
                    "turn_id": None,
                    "was_active": False,
                }

            role = msg["role"]
            paired_msg = None
            if role == "user":
                paired_msg = conn.execute(
                    """
                    SELECT id, session_id, role, attachments_json, metadata_json, created_at, parent_message_id
                    FROM messages
                    WHERE session_id = ? AND role = 'assistant' AND parent_message_id = ?
                    ORDER BY id ASC
                    LIMIT 1
                    """,
                    (session_id, int(message_id)),
                ).fetchone()
            elif role == "assistant":
                paired_msg = conn.execute(
                    """
                    SELECT id, session_id, role, attachments_json, metadata_json, created_at, parent_message_id
                    FROM messages
                    WHERE session_id = ? AND role = 'user' AND id = ?
                    ORDER BY id DESC
                    LIMIT 1
                    """,
                    (session_id, msg["parent_message_id"]),
                ).fetchone()

            user_msg = msg if role == "user" else paired_msg
            turn_row = None
            if role == "assistant":
                # An assistant row is linked to the turn that produced it,
                # which stays authoritative when ordering alone cannot tell
                # (a regenerated answer shares its user row with the old turn).
                turn_row = conn.execute(
                    """
                    SELECT id, status
                    FROM turns
                    WHERE session_id = ? AND assistant_message_id = ?
                    LIMIT 1
                    """,
                    (session_id, int(message_id)),
                ).fetchone()

            if turn_row is None and user_msg is not None:
                # Production stamps the owning turn id on the user row
                # (executor writes metadata.turn_id), including the recovered
                # worker_lost case that never gets an assistant row.
                stamped_turn_id = ""
                metadata = _json_loads(user_msg["metadata_json"], {})
                if isinstance(metadata, dict):
                    stamped_turn_id = str(metadata.get("turn_id") or "").strip()
                if stamped_turn_id:
                    turn_row = conn.execute(
                        """
                        SELECT id, status
                        FROM turns
                        WHERE session_id = ? AND id = ?
                        LIMIT 1
                        """,
                        (session_id, stamped_turn_id),
                    ).fetchone()

            if turn_row is None and user_msg is not None:
                # Legacy rows predate the stamp: the owning turn is the last
                # one created at or before the user row. A turn row is always
                # written before its user row, so a later turn must never match.
                turn_row = conn.execute(
                    """
                    SELECT id, status
                    FROM turns
                    WHERE session_id = ? AND created_at <= ?
                    ORDER BY created_at DESC
                    LIMIT 1
                    """,
                    (session_id, user_msg["created_at"]),
                ).fetchone()

            turn_id = None
            was_active = False
            if turn_row is not None:
                turn_id = turn_row["id"]
                was_active = turn_row["status"] in ACTIVE_TURN_STATUSES

            if was_active:
                return {
                    "deleted": False,
                    "attachment_ids": [],
                    "turn_id": turn_id,
                    "was_active": True,
                }

            if role == "assistant" and paired_msg is not None:
                # Regenerated answers share their question with sibling answers.
                # Deleting one branch must keep the question those siblings use.
                sibling = conn.execute(
                    """
                    SELECT 1 FROM messages
                    WHERE session_id = ? AND role = 'assistant'
                      AND parent_message_id = ? AND id != ?
                    LIMIT 1
                    """,
                    (session_id, paired_msg["id"], int(message_id)),
                ).fetchone()
                if sibling is not None:
                    paired_msg = None

            attachment_ids: list[str] = []
            for m in [msg, paired_msg]:
                if m is not None:
                    atts = _json_loads(m["attachments_json"], [])
                    for att in atts:
                        aid = att.get("id") or att.get("attachment_id")
                        if aid:
                            attachment_ids.append(aid)

            if turn_id is not None:
                conn.execute("DELETE FROM turn_events WHERE turn_id = ?", (turn_id,))
                conn.execute("DELETE FROM turns WHERE id = ?", (turn_id,))

            ids_to_delete = [int(message_id)]
            if paired_msg is not None:
                ids_to_delete.append(int(paired_msg["id"]))

            # Splice the deleted rows out of the parent-pointer tree: children
            # of a deleted row inherit its parent, in descending id order so a
            # pair's subtree rides up to the pair's parent in one pass (#912).
            for mid in sorted(ids_to_delete, reverse=True):
                conn.execute(
                    """
                    UPDATE messages
                    SET parent_message_id = (
                        SELECT parent_message_id FROM messages WHERE id = ?
                    )
                    WHERE session_id = ? AND parent_message_id = ?
                    """,
                    (mid, session_id, mid),
                )

            conn.execute(
                f"DELETE FROM messages WHERE id IN ({','.join('?' * len(ids_to_delete))})",  # nosec B608
                tuple(ids_to_delete),
            )

            session_row = conn.execute(
                "SELECT summary_up_to_msg_id FROM sessions WHERE id = ?",
                (session_id,),
            ).fetchone()
            if session_row is not None:
                summary_up_to = int(session_row["summary_up_to_msg_id"])
                if any(mid <= summary_up_to for mid in ids_to_delete):
                    conn.execute(
                        "UPDATE sessions SET summary_up_to_msg_id = 0 WHERE id = ?",
                        (session_id,),
                    )

            conn.execute(
                "UPDATE sessions SET updated_at = ? WHERE id = ?",
                (time.time(), session_id),
            )
            conn.commit()

        return {
            "deleted": True,
            "attachment_ids": attachment_ids,
            "turn_id": turn_id,
            "was_active": was_active,
        }

    async def delete_turn_by_message(self, session_id: str, message_id: int) -> dict[str, Any]:
        return await self._run(self._delete_turn_by_message_sync, session_id, message_id)

    def _get_last_message_sync(
        self, session_id: str, role: str | None = None
    ) -> dict[str, Any] | None:
        with self._connect() as conn:
            if role is None:
                row = conn.execute(
                    """
                    SELECT id, session_id, role, content, capability, events_json,
                           attachments_json, metadata_json, created_at, parent_message_id
                    FROM messages
                    WHERE session_id = ?
                    ORDER BY id DESC
                    LIMIT 1
                    """,
                    (session_id,),
                ).fetchone()
            else:
                row = conn.execute(
                    """
                    SELECT id, session_id, role, content, capability, events_json,
                           attachments_json, metadata_json, created_at, parent_message_id
                    FROM messages
                    WHERE session_id = ? AND role = ?
                    ORDER BY id DESC
                    LIMIT 1
                    """,
                    (session_id, role),
                ).fetchone()
        if row is None:
            return None
        return self._serialize_message(row)

    async def get_last_message(
        self, session_id: str, role: str | None = None
    ) -> dict[str, Any] | None:
        return await self._run(self._get_last_message_sync, session_id, role)

    def _serialize_message(
        self,
        row: sqlite3.Row,
        events: list[dict[str, Any]] | None = None,
        trace: dict[str, Any] | None = None,
    ) -> dict[str, Any]:
        row_keys = row.keys()
        parent_id = row["parent_message_id"] if "parent_message_id" in row_keys else None
        return {
            "id": row["id"],
            "session_id": row["session_id"],
            "role": row["role"],
            "content": row["content"],
            "capability": row["capability"] or "",
            "events": _json_loads(row["events_json"], []) if events is None else events,
            "attachments": _json_loads(row["attachments_json"], []),
            "metadata": _json_loads(row["metadata_json"], {}),
            "created_at": row["created_at"],
            "parent_message_id": int(parent_id) if parent_id is not None else None,
            **({"trace": trace} if trace is not None else {}),
        }

    def _canonical_trace_preview_sync(
        self,
        conn: sqlite3.Connection,
        turn_id: str,
        session_id: str,
    ) -> tuple[list[dict[str, Any]], dict[str, Any]]:
        stats = conn.execute(
            """
            SELECT COUNT(*) AS total, COALESCE(MAX(seq), 0) AS last_seq,
                   MIN(timestamp) AS started_at, MAX(timestamp) AS ended_at
            FROM turn_events WHERE turn_id = ?
            """,
            (turn_id,),
        ).fetchone()
        # Preview queries stay bounded even for a very long agentic turn. The
        # critical query reserves older terminal/result/card state; the
        # semantic query fills the remaining tail with tool activity. Both card
        # channels are named: the generic ask_user one and the mastery course's
        # own question card, which a settled message cannot render without.
        columns = "turn_id, seq, type, source, stage, content, metadata_json, timestamp"
        cards = """
            json_extract(metadata_json, '$.ask_user') IS NOT NULL
            OR json_extract(metadata_json, '$.ask_user_resolved') IS NOT NULL
            OR json_extract(metadata_json, '$.tool_metadata.ask_user') IS NOT NULL
            OR json_extract(metadata_json, '$.mastery_question') IS NOT NULL
            OR json_extract(metadata_json, '$.tool_metadata.mastery_question') IS NOT NULL
            OR json_extract(metadata_json, '$.trace_kind') = 'user_input'
        """
        conditions = (
            f"type IN ('done', 'error', 'cancelled', 'result') OR {cards}",
            f"type IN ('done', 'error', 'cancelled', 'result', 'tool_call', 'tool_result') OR {cards}",
        )
        rows_by_seq: dict[int, sqlite3.Row] = {}
        for condition in conditions:
            rows = conn.execute(
                f"""
                SELECT {columns}
                FROM turn_events
                WHERE turn_id = ? AND ({condition})
                ORDER BY seq DESC
                LIMIT ?
                """,  # nosec B608 - columns/condition are literals; every value binds via ?
                (turn_id, MAX_TRACE_PREVIEW_EVENTS),
            ).fetchall()
            rows_by_seq.update({int(row["seq"]): row for row in rows})
        events = [self._turn_event_row_to_payload(rows_by_seq[seq]) for seq in sorted(rows_by_seq)]
        for event in events:
            event["session_id"] = session_id
        preview, compacted = compact_trace_preview(events)
        total = int(stats["total"])
        metadata = {
            "turn_id": turn_id,
            "total": total,
            "last_seq": int(stats["last_seq"]),
            "truncated": compacted or total != len(preview),
            # How long the turn actually took, measured over *every* event
            # rather than the preview. The preview deliberately keeps only
            # tool and terminal events, so the moment the turn began — the
            # ``session`` / ``stage_start`` pair — is never in it. Timing the
            # preview therefore starts the clock at the first tool call and
            # silently drops however long the model spent thinking before it,
            # which for a round that thinks and then answers in one burst is
            # the whole turn (it renders as "0s").
            **_trace_bounds(stats["started_at"], stats["ended_at"]),
        }
        return preview, metadata

    def _legacy_trace_preview_sync(
        self, events: list[dict[str, Any]]
    ) -> tuple[list[dict[str, Any]], dict[str, Any]]:
        preview, omitted = compact_trace_preview(events)
        stamps = [
            value for event in events if isinstance(value := event.get("timestamp"), (int, float))
        ]
        return preview, {
            "turn_id": None,
            "total": len(events),
            "last_seq": max([int(event.get("seq") or 0) for event in events] or [0]),
            "truncated": omitted,
            **_trace_bounds(min(stamps, default=None), max(stamps, default=None)),
        }

    def _ask_user_events_sync(
        self, conn: sqlite3.Connection, turn_id: str, session_id: str
    ) -> list[dict[str, Any]]:
        rows = conn.execute(
            """
            SELECT turn_id, seq, type, source, stage, content, metadata_json, timestamp
            FROM turn_events
            WHERE turn_id = ?
              AND (
                json_extract(metadata_json, '$.ask_user') IS NOT NULL
                OR json_extract(metadata_json, '$.ask_user_resolved') IS NOT NULL
                OR json_extract(metadata_json, '$.tool_metadata.ask_user') IS NOT NULL
              )
            ORDER BY seq DESC
            LIMIT ?
            """,
            (turn_id, MAX_TRACE_PREVIEW_EVENTS),
        ).fetchall()
        events = [self._turn_event_row_to_payload(row) for row in reversed(rows)]
        for event in events:
            event["session_id"] = session_id
        return events

    def _get_messages_sync(self, session_id: str) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                """
                SELECT m.id, m.session_id, m.role, m.content, m.capability,
                       m.events_json, m.attachments_json, m.metadata_json,
                       m.created_at, m.parent_message_id, t.id AS trace_turn_id
                FROM messages AS m
                LEFT JOIN turns AS t ON t.assistant_message_id = m.id
                WHERE m.session_id = ?
                ORDER BY m.id ASC
                """,
                (session_id,),
            ).fetchall()
            result: list[dict[str, Any]] = []
            for row in rows:
                turn_id = row["trace_turn_id"]
                if row["role"] == "assistant" and turn_id is not None:
                    events, trace = self._canonical_trace_preview_sync(conn, turn_id, session_id)
                    result.append(self._serialize_message(row, events=events, trace=trace))
                    continue
                legacy_events = _json_loads(row["events_json"], [])
                if row["role"] == "assistant" and isinstance(legacy_events, list):
                    events, trace = self._legacy_trace_preview_sync(legacy_events)
                    result.append(
                        self._serialize_message(
                            row,
                            events=events,
                            trace=trace,
                        )
                    )
                    continue
                result.append(self._serialize_message(row))
        return result

    def _get_message_path_sync(self, session_id: str, leaf_message_id: int) -> list[dict[str, Any]]:
        """Return the chain of messages from the session root down to
        ``leaf_message_id`` (inclusive), in chronological order.

        Used by the turn runtime to build LLM context for a branched
        re-run: only ancestors of the new user message are included, so
        sibling branches at any depth are excluded.
        """
        with self._connect() as conn:
            chain: list[dict[str, Any]] = []
            current: int | None = int(leaf_message_id)
            # Bound the walk defensively in case of corrupted parent pointers.
            safety = 10_000
            while current is not None and safety > 0:
                row = conn.execute(
                    """
                    SELECT id, session_id, role, content, capability, events_json,
                           attachments_json, metadata_json, created_at, parent_message_id
                    FROM messages
                    WHERE id = ? AND session_id = ?
                    """,
                    (current, session_id),
                ).fetchone()
                if row is None:
                    break
                chain.append(self._serialize_message(row))
                parent = row["parent_message_id"]
                current = int(parent) if parent is not None else None
                safety -= 1
        chain.reverse()
        return chain

    async def get_message_path(self, session_id: str, leaf_message_id: int) -> list[dict[str, Any]]:
        return await self._run(self._get_message_path_sync, session_id, int(leaf_message_id))

    async def usage_records(self, start_at: float, end_at: float) -> list[dict[str, Any]]:
        """Read usage from canonical turn events, with legacy message fallback."""
        from .usage_statistics import summaries_from_events

        def read() -> list[dict[str, Any]]:
            with self._connect() as conn:
                rows = conn.execute(
                    """SELECT m.id, m.session_id, m.created_at, m.events_json, m.metadata_json AS message_metadata, t.id AS turn_id,
                              e.type, e.metadata_json
                       FROM messages AS m
                       LEFT JOIN turns AS t
                         ON t.assistant_message_id = m.id AND t.session_id = m.session_id
                       LEFT JOIN turn_events AS e
                         ON e.turn_id = t.id AND (e.type IN ('result', 'done') OR json_extract(e.metadata_json, '$.model') IS NOT NULL)
                       WHERE m.role = 'assistant' AND m.created_at >= ? AND m.created_at < ?
                         AND substr(m.session_id, 1, 9) != 'imported_'
                       ORDER BY m.created_at, m.id, e.seq""",
                    (start_at, end_at),
                )
                records: dict[int, dict[str, Any]] = {}
                events: dict[int, list[dict[str, Any]]] = {}
                metadata: dict[int, dict[str, Any]] = {}
                for row in rows:
                    mid = row["id"]
                    if mid not in records:
                        records[mid] = {
                            "session_id": row["session_id"],
                            "turn_id": row["turn_id"],
                            "created_at": row["created_at"],
                            "summaries": summaries_from_events(
                                _json_loads(row["events_json"], []),
                                _json_loads(row["message_metadata"], {}),
                            ),
                        }
                        events[mid] = []
                        metadata[mid] = _json_loads(row["message_metadata"], {})
                    if row["type"]:
                        events[mid].append(
                            {
                                "type": row["type"],
                                "metadata": _json_loads(row["metadata_json"], {}),
                            }
                        )
                for mid, record in records.items():
                    canonical = summaries_from_events(events[mid], metadata[mid])
                    if canonical:
                        record["summaries"] = canonical
                return list(records.values())

        return await self._run(read)

    async def get_messages(self, session_id: str) -> list[dict[str, Any]]:
        return await self._run(self._get_messages_sync, session_id)

    def _get_messages_for_context_sync(
        self, session_id: str, leaf_message_id: int | None = None
    ) -> list[dict[str, Any]]:
        with self._connect() as conn:
            if leaf_message_id is None:
                rows = conn.execute(
                    """
                    SELECT m.id, m.role, m.content, m.events_json, m.metadata_json, t.id AS turn_id
                    FROM messages AS m
                    LEFT JOIN turns AS t ON t.assistant_message_id = m.id
                    WHERE m.session_id = ?
                      AND m.role IN ('user', 'assistant', 'system')
                    ORDER BY m.id ASC
                    """,
                    (session_id,),
                ).fetchall()
                return [
                    {
                        "id": row["id"],
                        "role": row["role"],
                        "content": row["content"] or "",
                        "events": (
                            self._ask_user_events_sync(conn, row["turn_id"], session_id)
                            if row["turn_id"] is not None
                            else select_ask_user_events(row["events_json"])
                        ),
                        "metadata": _json_loads(row["metadata_json"], {}),
                    }
                    for row in rows
                ]
            # Branch-aware path walk: include only ancestors (+ leaf) so
            # sibling branches at any depth are excluded from LLM context.
            chain: list[dict[str, Any]] = []
            current: int | None = int(leaf_message_id)
            safety = 10_000
            while current is not None and safety > 0:
                row = conn.execute(
                    """
                    SELECT m.id, m.role, m.content, m.events_json, m.metadata_json,
                           m.parent_message_id, t.id AS turn_id
                    FROM messages AS m
                    LEFT JOIN turns AS t ON t.assistant_message_id = m.id
                    WHERE m.id = ? AND m.session_id = ?
                      AND m.role IN ('user', 'assistant', 'system')
                    """,
                    (current, session_id),
                ).fetchone()
                if row is None:
                    break
                chain.append(
                    {
                        "id": row["id"],
                        "role": row["role"],
                        "content": row["content"] or "",
                        "events": (
                            self._ask_user_events_sync(conn, row["turn_id"], session_id)
                            if row["turn_id"] is not None
                            else select_ask_user_events(row["events_json"])
                        ),
                        "metadata": _json_loads(row["metadata_json"], {}),
                    }
                )
                parent = row["parent_message_id"]
                current = int(parent) if parent is not None else None
                safety -= 1
        chain.reverse()
        return chain

    async def get_messages_for_context(
        self, session_id: str, leaf_message_id: int | None = None
    ) -> list[dict[str, Any]]:
        return await self._run(self._get_messages_for_context_sync, session_id, leaf_message_id)

    # Imported conversations live in the same tables as native chats (so the
    # chat loop can re-open and continue them) but carry an ``imported_`` id
    # prefix. That prefix is the discriminator — it travels with the primary
    # key, so we filter on it instead of adding a column + migration.
    _SESSION_SUMMARY_SQL = """
        SELECT
            s.id,
            s.title,
            s.created_at,
            s.updated_at,
            s.compressed_summary,
            s.summary_up_to_msg_id,
            s.preferences_json,
            COUNT(CASE WHEN m.role != 'system' THEN 1 END) AS message_count,
            COALESCE(
                (SELECT t.status FROM turns t WHERE t.session_id = s.id
                 ORDER BY t.updated_at DESC LIMIT 1),
                'idle'
            ) AS status,
            COALESCE(
                (SELECT t.id FROM turns t WHERE t.session_id = s.id
                    AND t.status IN ('queued', 'running', 'waiting_input')
                 ORDER BY t.updated_at DESC LIMIT 1),
                ''
            ) AS active_turn_id,
            COALESCE(
                (SELECT t.capability FROM turns t WHERE t.session_id = s.id
                 ORDER BY t.updated_at DESC LIMIT 1),
                ''
            ) AS capability,
            COALESCE(
                (SELECT m2.content FROM messages m2
                 WHERE m2.session_id = s.id AND m2.role != 'system'
                   AND TRIM(COALESCE(m2.content, '')) != ''
                 ORDER BY m2.id DESC LIMIT 1),
                ''
            ) AS last_message
        FROM sessions s
        LEFT JOIN messages m ON m.session_id = s.id
        {where}
        GROUP BY s.id
        ORDER BY s.updated_at DESC, s.id ASC
        LIMIT ? OFFSET ?
    """

    # ``ESCAPE '\'`` makes the underscore in ``imported_`` literal rather than
    # the LIKE single-char wildcard.
    #
    # Reading conversations used to be filtered out here. They were hidden
    # because a flat "Recents" list mixed them in with ordinary chats and
    # clicking one dropped the reader into the generic chat surface without
    # their material — but
    # hiding them meant a learner had no way back to a reading conversation
    # except by reopening its collection. The sidebar now files them under
    # their collection and ``sessionRoute`` sends a click back to the reader,
    # so they belong in the list like everything else.
    _WHERE_NATIVE = r"""
        WHERE s.id NOT LIKE 'imported\_%' ESCAPE '\' AND s.deleted_at IS NULL
    """
    _WHERE_IMPORTED = r"WHERE s.id LIKE 'imported\_%' ESCAPE '\' AND s.deleted_at IS NULL"

    def _list_session_summaries_sync(
        self, where_sql: str, limit: int, offset: int
    ) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                self._SESSION_SUMMARY_SQL.format(where=where_sql),
                (limit, offset),
            ).fetchall()
        return [self._session_summary_payload(row) for row in rows]

    @staticmethod
    def _session_summary_payload(row: sqlite3.Row) -> dict[str, Any]:
        payload = dict(row)
        payload["session_id"] = payload["id"]
        payload["preferences"] = _json_loads(payload.pop("preferences_json", ""), {})
        return payload

    def _get_session_summaries_sync(
        self,
        session_ids: list[str],
    ) -> list[dict[str, Any]]:
        ids = list(dict.fromkeys(str(item or "").strip() for item in session_ids))
        ids = [item for item in ids if item]
        if not ids:
            return []
        placeholders = ",".join("?" for _ in ids)
        where = f"WHERE s.id IN ({placeholders})"
        with self._connect() as conn:
            rows = conn.execute(
                self._SESSION_SUMMARY_SQL.format(where=where),
                (*ids, len(ids), 0),
            ).fetchall()
        return [self._session_summary_payload(row) for row in rows]

    def _list_sessions_sync(
        self,
        limit: int = 50,
        offset: int = 0,
        workspace_id: str | None = None,
    ) -> list[dict[str, Any]]:
        if workspace_id is None:
            return self._list_session_summaries_sync(self._WHERE_NATIVE, limit, offset)
        where = (
            self._WHERE_NATIVE
            + """
            AND COALESCE(json_extract(s.preferences_json, '$.workspace_id'), '') = ?
            AND COALESCE(json_extract(s.preferences_json, '$.archived'), 0) = 0
            AND COALESCE(json_extract(s.preferences_json, '$.parent_session_id'), '') = ''
        """
        )
        with self._connect() as conn:
            rows = conn.execute(
                self._SESSION_SUMMARY_SQL.format(where=where),
                (workspace_id, limit, offset),
            ).fetchall()
        return [self._session_summary_payload(row) for row in rows]

    def _list_imported_sessions_sync(
        self,
        limit: int = 50,
        offset: int = 0,
    ) -> list[dict[str, Any]]:
        return self._list_session_summaries_sync(self._WHERE_IMPORTED, limit, offset)

    async def list_sessions(
        self,
        limit: int = 50,
        offset: int = 0,
        *,
        workspace_id: str | None = None,
    ) -> list[dict[str, Any]]:
        return await self._run(self._list_sessions_sync, limit, offset, workspace_id)

    def _search_sessions_sync(
        self,
        query: str,
        limit: int = 50,
        offset: int = 0,
    ) -> dict[str, Any]:
        """Search native session titles and visible message text literally."""
        normalized = normalize_search_query(query)
        if not normalized:
            return {"sessions": [], "total": 0}

        match_condition = r"""
            s.id NOT LIKE 'imported\_%' ESCAPE '\'
            AND s.deleted_at IS NULL
            AND (
                INSTR(LOWER(COALESCE(s.title, '')), LOWER(?)) > 0
                OR EXISTS (
                    SELECT 1 FROM messages candidate
                    WHERE candidate.session_id = s.id
                      AND candidate.role IN ('user', 'assistant')
                      AND INSTR(LOWER(COALESCE(candidate.content, '')), LOWER(?)) > 0
                )
            )
        """
        with self._connect() as conn:
            total = int(
                conn.execute(
                    f"SELECT COUNT(*) FROM sessions s WHERE {match_condition}",  # nosec B608
                    (normalized, normalized),
                ).fetchone()[0]
            )
            rows = conn.execute(
                f"""
                WITH matched_sessions AS (
                    SELECT s.*
                    FROM sessions s
                    WHERE {match_condition}
                    ORDER BY s.updated_at DESC, s.id ASC
                    LIMIT ? OFFSET ?
                ),
                best_messages AS (
                    SELECT m.session_id, m.id, m.role, m.created_at,
                           SUBSTR(
                               m.content,
                               MAX(1, INSTR(LOWER(m.content), LOWER(?)) - 160),
                               520
                           ) AS content
                    FROM messages m
                    JOIN matched_sessions s ON s.id = m.session_id
                    WHERE m.role IN ('user', 'assistant')
                      AND INSTR(LOWER(COALESCE(m.content, '')), LOWER(?)) > 0
                      AND m.id = (
                          SELECT newest.id FROM messages newest
                          WHERE newest.session_id = m.session_id
                            AND newest.role IN ('user', 'assistant')
                            AND INSTR(
                                LOWER(COALESCE(newest.content, '')), LOWER(?)
                            ) > 0
                          ORDER BY newest.created_at DESC, newest.id DESC
                          LIMIT 1
                      )
                )
                SELECT
                    s.id, s.title, s.created_at, s.updated_at,
                    s.compressed_summary, s.summary_up_to_msg_id,
                    s.preferences_json,
                    COUNT(CASE WHEN m.role != 'system' THEN 1 END) AS message_count,
                    COALESCE(
                        (SELECT t.status FROM turns t WHERE t.session_id = s.id
                         ORDER BY t.updated_at DESC LIMIT 1),
                        'idle'
                    ) AS status,
                    COALESCE(
                        (SELECT t.id FROM turns t WHERE t.session_id = s.id
                         AND t.status IN ('queued', 'running', 'waiting_input')
                         ORDER BY t.updated_at DESC LIMIT 1),
                        ''
                    ) AS active_turn_id,
                    COALESCE(
                        (SELECT t.capability FROM turns t WHERE t.session_id = s.id
                         ORDER BY t.updated_at DESC LIMIT 1),
                        ''
                    ) AS capability,
                    COALESCE(
                        (SELECT latest.content FROM messages latest
                         WHERE latest.session_id = s.id AND latest.role != 'system'
                           AND TRIM(COALESCE(latest.content, '')) != ''
                         ORDER BY latest.id DESC LIMIT 1),
                        ''
                    ) AS last_message,
                    bm.id AS match_message_id,
                    bm.role AS match_role,
                    bm.created_at AS match_created_at,
                    COALESCE(bm.content, s.title, '') AS match_content
                FROM matched_sessions s
                LEFT JOIN messages m ON m.session_id = s.id
                LEFT JOIN best_messages bm ON bm.session_id = s.id
                GROUP BY s.id
                ORDER BY s.updated_at DESC, s.id ASC
                """,  # nosec B608 - match_condition is a module-owned SQL literal
                (
                    normalized,
                    normalized,
                    max(1, min(int(limit), 100)),
                    max(0, int(offset)),
                    normalized,
                    normalized,
                    normalized,
                ),
            ).fetchall()

        sessions: list[dict[str, Any]] = []
        for row in rows:
            payload = self._session_summary_payload(row)
            content = str(payload.pop("match_content", "") or "")
            payload["match_excerpt"] = bounded_search_excerpt(content, normalized)
            sessions.append(payload)
        return {"sessions": sessions, "total": total}

    async def search_sessions(
        self,
        query: str,
        limit: int = 50,
        offset: int = 0,
    ) -> dict[str, Any]:
        """Return a bounded page of native sessions matching a literal query."""
        return await self._run(self._search_sessions_sync, query, limit, offset)

    async def get_session_summaries(
        self,
        session_ids: list[str],
    ) -> list[dict[str, Any]]:
        return await self._run(self._get_session_summaries_sync, session_ids)

    async def list_imported_sessions(
        self,
        limit: int = 50,
        offset: int = 0,
    ) -> list[dict[str, Any]]:
        return await self._run(self._list_imported_sessions_sync, limit, offset)

    def _update_summary_sync(self, session_id: str, summary: str, up_to_msg_id: int) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                """
                UPDATE sessions
                SET compressed_summary = ?, summary_up_to_msg_id = ?, updated_at = updated_at
                WHERE id = ?
                """,
                (summary, max(0, int(up_to_msg_id)), session_id),
            )
            conn.commit()
        return cur.rowcount > 0

    async def update_summary(self, session_id: str, summary: str, up_to_msg_id: int | str) -> bool:
        return await self._run(self._update_summary_sync, session_id, summary, int(up_to_msg_id))

    def _update_session_preferences_sync(
        self, session_id: str, preferences: dict[str, Any], touch: bool = True
    ) -> bool:
        with self._connect() as conn:
            current = conn.execute(
                "SELECT preferences_json FROM sessions WHERE id = ?",
                (session_id,),
            ).fetchone()
            if current is None:
                return False
            merged = {
                **_json_loads(current["preferences_json"], {}),
                **(preferences or {}),
            }
            merged = upgrade_workspace_preferences(merged)
            # ``touch=False`` records metadata (a link, a migration) without
            # making the conversation look recently used.
            cur = conn.execute(
                """
                UPDATE sessions
                SET preferences_json = ?, updated_at = COALESCE(?, updated_at)
                WHERE id = ?
                """,
                (_json_dumps(merged), time.time() if touch else None, session_id),
            )
            conn.commit()
        return cur.rowcount > 0

    async def update_session_preferences(
        self, session_id: str, preferences: dict[str, Any], *, touch: bool = True
    ) -> bool:
        return await self._run(
            self._update_session_preferences_sync, session_id, preferences, touch
        )

    async def get_session_with_messages(self, session_id: str) -> dict[str, Any] | None:
        session = await self.get_session(session_id)
        if session is None:
            return None
        session["messages"] = await self.get_messages(session_id)
        redact_private_message_metadata(session["messages"])
        session["active_turns"] = await self.list_active_turns(session_id)
        return session

    # ── Notebook entries ──────────────────────────────────────────────

    @staticmethod
    def _optional_float(value: Any) -> float | None:
        if value is None or value == "":
            return None
        try:
            return float(value)
        except (TypeError, ValueError):
            return None

    @classmethod
    def _notebook_assessment_values(cls, item: dict[str, Any], is_correct: int) -> tuple[Any, ...]:
        assessment_type = str(item.get("assessment_type") or "").strip()
        if assessment_type not in ASSESSMENT_TYPES:
            assessment_type = ""
        result = str(item.get("result") or "").strip()
        if result not in ASSESSMENT_RESULTS:
            result = "correct" if is_correct else "incorrect"
        try:
            raw_attempts = item.get("attempt_count")
            attempt_count = max(1, int(raw_attempts if raw_attempts is not None else 1))
        except (TypeError, ValueError):
            attempt_count = 1
        try:
            raw_hints = item.get("hints_used")
            hints_used = max(0, int(raw_hints if raw_hints is not None else 0))
        except (TypeError, ValueError):
            hints_used = 0
        return (
            assessment_type,
            result,
            str(item.get("mastery_path_id") or ""),
            str(item.get("knowledge_point_id") or ""),
            attempt_count,
            hints_used,
            cls._optional_float(item.get("confidence")),
            cls._optional_float(item.get("response_time")),
            cls._optional_float(item.get("quality")),
        )

    @staticmethod
    def _notebook_origin(
        session_id: str | None,
        item: dict[str, Any],
    ) -> tuple[str | None, str, str]:
        """Return the optional navigation session and durable entry identity."""
        navigation_session = str(session_id or item.get("session_id") or "").strip() or None
        origin_type = str(item.get("origin_type") or "conversation").strip()
        if origin_type not in QUESTION_ORIGIN_TYPES:
            raise ValueError(f"Unsupported question origin: {origin_type}")
        origin_ref = str(item.get("origin_ref") or "").strip()
        if origin_type == "conversation":
            if navigation_session is None:
                raise ValueError("Conversation question origins require session_id")
            if origin_ref and origin_ref != navigation_session:
                raise ValueError("Conversation origin_ref must match session_id")
            origin_ref = navigation_session
        elif not origin_ref:
            raise ValueError(f"{origin_type} question origins require origin_ref")
        return navigation_session, origin_type, origin_ref

    def _upsert_notebook_entries_in_conn(
        self,
        conn: sqlite3.Connection,
        session_id: str | None,
        items: list[dict[str, Any]],
    ) -> int:
        if not items:
            return 0
        now = time.time()
        upserted = 0
        for item in items:
            navigation_session, origin_type, origin_ref = self._notebook_origin(session_id, item)
            if navigation_session is not None and (
                conn.execute(
                    "SELECT id FROM sessions WHERE id = ?", (navigation_session,)
                ).fetchone()
                is None
            ):
                raise ValueError(f"Session not found: {navigation_session}")
            question = (item.get("question") or "").strip()
            question_id = (item.get("question_id") or "").strip()
            if not question or not question_id:
                continue
            turn_id = (item.get("turn_id") or "").strip()
            # ``user_answer_images`` is an optional list of records
            # ``[{id, url, filename, mime_type}, …]``. We serialise it
            # here so callers that only know about text don't need to
            # know JSON. ``None`` keeps the existing column value on
            # UPDATE (avoid clobbering stored images on a partial
            # upsert that only changes ``is_correct``).
            images_value = item.get("user_answer_images")
            images_json = _json_dumps(images_value) if isinstance(images_value, list) else None
            source = str(item.get("source") or "deep_question")
            if source not in ASSESSMENT_SOURCES:
                source = "deep_question"
            is_correct = 1 if item.get("is_correct") else 0
            existing = conn.execute(
                """
                SELECT is_correct FROM notebook_entries
                WHERE origin_type = ? AND origin_ref = ?
                  AND turn_id = ? AND question_id = ?
                """,
                (origin_type, origin_ref, turn_id, question_id),
            ).fetchone()
            previous = bool(existing["is_correct"]) if existing is not None else None
            if previous is None or previous == bool(is_correct):
                score_trend = "new" if previous is None else "unchanged"
            else:
                score_trend = "improved" if is_correct else "declined"
            provenance = (
                source,
                str(item.get("material_id") or ""),
                str(item.get("material_title") or ""),
                str(item.get("section_id") or ""),
                str(item.get("section_title") or ""),
                score_trend,
            )
            assessment = self._notebook_assessment_values(item, is_correct)
            resolved_on_insert = (
                0
                if assessment[1] == "ungraded"
                else 1
                if assessment[1] == "voided" or is_correct
                else 0
            )
            conn.execute(
                """
                INSERT INTO notebook_entries (
                    session_id, origin_type, origin_ref, turn_id, question_id,
                    question, question_type, options_json, correct_answer,
                    explanation, difficulty, user_answer,
                    user_answer_images_json, source, material_id,
                    material_title, section_id, section_title, score_trend,
                    assessment_type, result, mastery_path_id, knowledge_point_id,
                    attempt_count, hints_used, confidence, response_time, quality,
                    is_correct, resolved, bookmarked, followup_session_id,
                    created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, '', ?, ?)
                ON CONFLICT(origin_type, origin_ref, turn_id, question_id) DO UPDATE SET
                    session_id = excluded.session_id,
                    question = excluded.question,
                    question_type = excluded.question_type,
                    options_json = excluded.options_json,
                    correct_answer = excluded.correct_answer,
                    explanation = excluded.explanation,
                    difficulty = excluded.difficulty,
                    user_answer = excluded.user_answer,
                    user_answer_images_json = CASE
                        WHEN ? THEN notebook_entries.user_answer_images_json
                        ELSE excluded.user_answer_images_json
                    END,
                    source = excluded.source,
                    material_id = excluded.material_id,
                    material_title = excluded.material_title,
                    section_id = excluded.section_id,
                    section_title = excluded.section_title,
                    score_trend = excluded.score_trend,
                    assessment_type = excluded.assessment_type,
                    result = excluded.result,
                    mastery_path_id = excluded.mastery_path_id,
                    knowledge_point_id = excluded.knowledge_point_id,
                    attempt_count = excluded.attempt_count,
                    hints_used = excluded.hints_used,
                    confidence = excluded.confidence,
                    response_time = excluded.response_time,
                    quality = excluded.quality,
                    is_correct = excluded.is_correct,
                    resolved = CASE
                        WHEN excluded.result = 'ungraded' THEN notebook_entries.resolved
                        WHEN excluded.result = 'voided' THEN 1
                        WHEN excluded.is_correct = 1 THEN 1
                        WHEN excluded.is_correct = 0 AND notebook_entries.is_correct = 1 THEN 0
                        ELSE notebook_entries.resolved
                    END,
                    updated_at = excluded.updated_at
                """,
                (
                    navigation_session,
                    origin_type,
                    origin_ref,
                    turn_id,
                    question_id,
                    question,
                    item.get("question_type") or "",
                    _json_dumps(item.get("options") or {}),
                    item.get("correct_answer") or "",
                    item.get("explanation") or "",
                    item.get("difficulty") or "",
                    item.get("user_answer") or "",
                    images_json if images_json is not None else "[]",
                    *provenance,
                    *assessment,
                    is_correct,
                    resolved_on_insert,
                    now,
                    now,
                    images_json is None,
                ),
            )
            upserted += 1
        return upserted

    def _upsert_notebook_entries_sync(
        self,
        session_id: str | None,
        items: list[dict[str, Any]],
    ) -> int:
        with self._connect() as conn:
            return self._upsert_notebook_entries_in_conn(conn, session_id, items)

    async def upsert_notebook_entries(
        self,
        session_id: str | None,
        items: list[dict[str, Any]],
    ) -> int:
        return await self._run(self._upsert_notebook_entries_sync, session_id, items)

    def _append_assessment_attempt_sync(
        self,
        session_id: str | None,
        notebook_entry_id: int | None,
        attempt: dict[str, Any],
    ) -> bool:
        attempt_id = str(attempt.get("attempt_id") or "").strip()
        question_id = str(attempt.get("question_id") or "").strip()
        if not attempt_id or not question_id:
            raise ValueError("attempt_id and question_id are required")
        navigation_session, origin_type, origin_ref = self._notebook_origin(session_id, attempt)
        with self._connect() as conn:
            if navigation_session is not None and (
                conn.execute(
                    "SELECT id FROM sessions WHERE id = ?", (navigation_session,)
                ).fetchone()
                is None
            ):
                raise ValueError(f"Session not found: {navigation_session}")
            conn.execute(
                """
                INSERT OR IGNORE INTO assessment_attempts (
                    attempt_id, notebook_entry_id, session_id, origin_type,
                    origin_ref, turn_id, question_id, source, assessment_type, result,
                    mastery_path_id, knowledge_point_id, occurred_at,
                    assessment_json
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    attempt_id,
                    notebook_entry_id,
                    navigation_session,
                    origin_type,
                    origin_ref,
                    str(attempt.get("turn_id") or ""),
                    question_id,
                    str(attempt.get("source") or "deep_question"),
                    str(attempt.get("assessment_type") or "quiz"),
                    str(attempt.get("result") or "ungraded"),
                    str(attempt.get("mastery_path_id") or ""),
                    str(attempt.get("knowledge_point_id") or ""),
                    float(attempt.get("occurred_at") or time.time()),
                    _json_dumps(attempt),
                ),
            )
            inserted = bool(conn.execute("SELECT changes()").fetchone()[0])
            conn.commit()
        return inserted

    async def append_assessment_attempt(
        self,
        session_id: str | None,
        notebook_entry_id: int | None,
        attempt: dict[str, Any],
    ) -> bool:
        """Append one immutable assessment event; duplicate ids are idempotent."""
        return await self._run(
            self._append_assessment_attempt_sync,
            session_id,
            notebook_entry_id,
            attempt,
        )

    def _record_assessment_sync(
        self,
        session_id: str | None,
        item: dict[str, Any],
        attempt: dict[str, Any],
    ) -> tuple[int, bool, bool]:
        """Commit the evidence event and its latest-card projection together.

        The event is inserted first inside the transaction. A retry with the
        same submission id leaves the latest card alone, even if a newer
        attempt has since changed it.
        """
        attempt_id = str(attempt.get("attempt_id") or "").strip()
        question_id = str(attempt.get("question_id") or "").strip()
        if not attempt_id or not question_id:
            raise ValueError("attempt_id and question_id are required")
        navigation_session, origin_type, origin_ref = self._notebook_origin(session_id, attempt)
        turn_id = str(attempt.get("turn_id") or "").strip()
        with self._connect() as conn:
            conn.execute("BEGIN IMMEDIATE")
            existing = conn.execute(
                "SELECT assessment_json, notebook_entry_id FROM assessment_attempts "
                "WHERE attempt_id = ?",
                (attempt_id,),
            ).fetchone()
            if existing is not None:
                original = _json_loads(str(existing["assessment_json"]), {})
                identity_fields = (
                    "origin_type",
                    "origin_ref",
                    "turn_id",
                    "question_id",
                    "source",
                    "assessment_type",
                    "user_answer",
                    "result",
                    "mastery_path_id",
                    "knowledge_point_id",
                )
                if any(original.get(key) != attempt.get(key) for key in identity_fields):
                    raise ValueError(f"Conflicting assessment submission id: {attempt_id}")
                entry_id = existing["notebook_entry_id"]
                if entry_id is None:
                    row = conn.execute(
                        "SELECT id FROM notebook_entries WHERE origin_type = ? "
                        "AND origin_ref = ? AND turn_id = ? AND question_id = ?",
                        (origin_type, origin_ref, turn_id, question_id),
                    ).fetchone()
                    entry_id = row["id"] if row is not None else None
                return int(entry_id) if entry_id is not None else 0, False, False

            if (
                navigation_session is not None
                and conn.execute(
                    "SELECT id FROM sessions WHERE id = ?", (navigation_session,)
                ).fetchone()
                is None
            ):
                raise ValueError(f"Session not found: {navigation_session}")
            if str(attempt.get("source") or "") == "immersive_reading":
                row = conn.execute(
                    "SELECT COUNT(*) AS count FROM assessment_attempts "
                    "WHERE origin_type = ? AND origin_ref = ? AND turn_id = ? AND question_id = ?",
                    (origin_type, origin_ref, turn_id, question_id),
                ).fetchone()
                count = int(row["count"]) + 1
                item = {**item, "attempt_count": count}
                attempt = {**attempt, "attempt_count": count}
            latest = conn.execute(
                "SELECT occurred_at, attempt_id FROM assessment_attempts "
                "WHERE origin_type = ? AND origin_ref = ? AND turn_id = ? AND question_id = ? "
                "ORDER BY occurred_at DESC, attempt_id DESC LIMIT 1",
                (origin_type, origin_ref, turn_id, question_id),
            ).fetchone()
            occurred_at = float(attempt.get("occurred_at") or time.time())
            is_latest = latest is None or (occurred_at, attempt_id) >= (
                float(latest["occurred_at"]),
                str(latest["attempt_id"]),
            )
            conn.execute(
                """
                INSERT INTO assessment_attempts (
                    attempt_id, notebook_entry_id, session_id, origin_type,
                    origin_ref, turn_id, question_id, source, assessment_type, result,
                    mastery_path_id, knowledge_point_id, occurred_at,
                    assessment_json
                ) VALUES (?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    attempt_id,
                    navigation_session,
                    origin_type,
                    origin_ref,
                    turn_id,
                    question_id,
                    str(attempt.get("source") or "deep_question"),
                    str(attempt.get("assessment_type") or "quiz"),
                    str(attempt.get("result") or "ungraded"),
                    str(attempt.get("mastery_path_id") or ""),
                    str(attempt.get("knowledge_point_id") or ""),
                    occurred_at,
                    _json_dumps(attempt),
                ),
            )
            upserted = (
                self._upsert_notebook_entries_in_conn(conn, session_id, [item]) if is_latest else 0
            )
            row = conn.execute(
                "SELECT id FROM notebook_entries WHERE origin_type = ? "
                "AND origin_ref = ? AND turn_id = ? AND question_id = ?",
                (origin_type, origin_ref, turn_id, question_id),
            ).fetchone()
            if row is None:
                raise ValueError("Assessment projection was not persisted")
            entry_id = int(row["id"])
            conn.execute(
                "UPDATE assessment_attempts SET notebook_entry_id = ? WHERE attempt_id = ?",
                (entry_id, attempt_id),
            )
        return entry_id, bool(upserted), True

    async def record_assessment(
        self,
        session_id: str | None,
        item: dict[str, Any],
        attempt: dict[str, Any],
    ) -> tuple[int, bool, bool]:
        """Atomically append one assessment and refresh its Question Bank card."""
        return await self._run(self._record_assessment_sync, session_id, item, attempt)

    def _get_assessment_attempt_sync(self, attempt_id: str) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                "SELECT assessment_json FROM assessment_attempts WHERE attempt_id = ?",
                (attempt_id,),
            ).fetchone()
        if row is None:
            return None
        value = _json_loads(str(row["assessment_json"]), {})
        return value if isinstance(value, dict) else None

    async def get_assessment_attempt(self, attempt_id: str) -> dict[str, Any] | None:
        return await self._run(self._get_assessment_attempt_sync, attempt_id)

    def _mark_assessment_link_applied_sync(self, attempt_id: str) -> None:
        with self._connect() as conn:
            conn.execute(
                "UPDATE assessment_attempts SET linked_applied = 1 WHERE attempt_id = ?",
                (attempt_id,),
            )

    async def mark_assessment_link_applied(self, attempt_id: str) -> None:
        await self._run(self._mark_assessment_link_applied_sync, attempt_id)

    def _pending_linked_assessments_sync(self) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                "SELECT assessment_json FROM assessment_attempts WHERE linked_applied = 0 "
                "AND mastery_path_id != '' AND knowledge_point_id != '' "
                "AND source != 'mastery_path' AND result NOT IN ('ungraded', 'voided') "
                "ORDER BY occurred_at, attempt_id"
            ).fetchall()
        return [
            value
            for row in rows
            if isinstance(value := _json_loads(str(row["assessment_json"]), {}), dict)
        ]

    async def pending_linked_assessments(self) -> list[dict[str, Any]]:
        return await self._run(self._pending_linked_assessments_sync)

    def _list_assessment_attempts_sync(
        self,
        session_id: str,
        question_id: str,
    ) -> list[dict[str, Any]]:
        clauses = ["session_id = ?"]
        params: list[Any] = [session_id]
        if question_id:
            clauses.append("question_id = ?")
            params.append(question_id)
        with self._connect() as conn:
            rows = conn.execute(
                "SELECT assessment_json FROM assessment_attempts "
                f"WHERE {' AND '.join(clauses)} ORDER BY occurred_at, rowid",  # nosec B608
                tuple(params),
            ).fetchall()
        return [_json_loads(str(row["assessment_json"]), {}) for row in rows]

    async def list_assessment_attempts(
        self,
        session_id: str,
        *,
        question_id: str = "",
    ) -> list[dict[str, Any]]:
        """Return immutable attempts in replay order for one session."""
        return await self._run(
            self._list_assessment_attempts_sync,
            session_id,
            question_id,
        )

    def _put_reading_quiz_pending_sync(
        self, material_id: str, locator: int, questions: list[Any]
    ) -> int:
        material = str(material_id or "").strip()
        if not material:
            return 0
        now = time.time()
        with self._connect() as conn:
            conn.execute(
                "DELETE FROM reading_quiz_pending WHERE material_id = ? AND locator = ?",
                (material, int(locator)),
            )
            stored = 0
            for index, question in enumerate(questions or [], start=1):
                if not isinstance(question, dict):
                    continue
                question_id = str(question.get("id") or f"q_{index}").strip()
                if not question_id:
                    continue
                conn.execute(
                    """
                    INSERT INTO reading_quiz_pending (
                        material_id, locator, question_id, question_json, created_at
                    ) VALUES (?, ?, ?, ?, ?)
                    """,
                    (material, int(locator), question_id, _json_dumps(question), now),
                )
                stored += 1
            conn.commit()
        return stored

    async def put_reading_quiz_pending(
        self, material_id: str, locator: int, questions: list[Any]
    ) -> int:
        """Replace the server-side answer keys for one reading quiz."""
        return await self._run(
            self._put_reading_quiz_pending_sync, material_id, locator, list(questions or [])
        )

    def _get_reading_quiz_pending_sync(
        self,
        material_id: str,
        locator: int,
        question_ids: Sequence[str] | None = None,
    ) -> dict[str, dict[str, Any]]:
        material = str(material_id or "").strip()
        sql = """
            SELECT question_id, question_json FROM reading_quiz_pending
            WHERE material_id = ? AND locator = ?
        """
        params: list[Any] = [material, int(locator)]
        if question_ids is not None:
            wanted = [str(qid).strip() for qid in question_ids if str(qid).strip()]
            if not wanted:
                return {}
            placeholders = ",".join("?" for _ in wanted)
            sql += f" AND question_id IN ({placeholders})"  # nosec B608
            params.extend(wanted)
        with self._connect() as conn:
            rows = conn.execute(sql, tuple(params)).fetchall()
        pending: dict[str, dict[str, Any]] = {}
        for row in rows:
            parsed = _json_loads(row["question_json"], {})
            if isinstance(parsed, dict):
                pending[str(row["question_id"])] = parsed
        return pending

    async def get_reading_quiz_pending(
        self,
        material_id: str,
        locator: int,
        question_ids: Sequence[str] | None = None,
    ) -> dict[str, dict[str, Any]]:
        """Return stored reading-quiz items keyed by question id (includes answer keys)."""
        return await self._run(
            self._get_reading_quiz_pending_sync,
            material_id,
            locator,
            None if question_ids is None else tuple(question_ids),
        )

    def _best_reading_quiz_results_sync(
        self, material_id: str, locator: int, question_ids: Sequence[str]
    ) -> dict[str, dict[str, bool]]:
        material = str(material_id or "").strip()
        section = str(int(locator))
        wanted = list(dict.fromkeys(str(qid).strip() for qid in question_ids if str(qid).strip()))
        if not material or not wanted:
            return {}
        placeholders = ",".join("?" for _ in wanted)
        with self._connect() as conn:
            query = f"""
                SELECT question_id, assessment_json FROM assessment_attempts
                WHERE source = 'immersive_reading'
                  AND question_id IN ({placeholders})
                """  # nosec B608 - fixed SQL columns with bound values
            rows = conn.execute(
                query,
                tuple(wanted),
            ).fetchall()
        results: dict[str, dict[str, bool]] = {}
        for row in rows:
            value = _json_loads(str(row["assessment_json"]), {})
            if not isinstance(value, dict):
                continue
            if str(value.get("material_id") or "") != material:
                continue
            if str(value.get("section_id") or "") != section:
                continue
            question_id = str(row["question_id"])
            correct = str(value.get("result") or "") == "correct"
            current = results.setdefault(question_id, {"attempted": True, "correct": False})
            current["correct"] = current["correct"] or correct
        return results

    async def best_reading_quiz_results(
        self, material_id: str, locator: int, question_ids: Sequence[str]
    ) -> dict[str, dict[str, bool]]:
        """Return the best immutable result for each current quiz question."""
        return await self._run(
            self._best_reading_quiz_results_sync,
            material_id,
            locator,
            tuple(question_ids),
        )

    def _upsert_reading_quiz_reward_sync(
        self, material_id: str, locator: int, stars: int
    ) -> dict[str, Any]:
        material = str(material_id or "").strip()
        normalized_locator = int(locator)
        normalized_stars = max(1, int(stars))
        if not material:
            raise ValueError("material_id is required")
        now = time.time()
        with self._connect() as conn:
            conn.execute("BEGIN IMMEDIATE")
            row = conn.execute(
                "SELECT stars FROM reading_quiz_rewards WHERE material_id = ? AND locator = ?",
                (material, normalized_locator),
            ).fetchone()
            previous = int(row["stars"]) if row is not None else 0
            awarded = normalized_stars > previous
            conn.execute(
                """
                INSERT INTO reading_quiz_rewards (
                    material_id, locator, stars, updated_at
                ) VALUES (?, ?, ?, ?)
                ON CONFLICT(material_id, locator) DO UPDATE SET
                    stars = excluded.stars,
                    updated_at = excluded.updated_at
                WHERE excluded.stars > reading_quiz_rewards.stars
                """,
                (material, normalized_locator, normalized_stars, now),
            )
            conn.commit()
        return {
            "locator": normalized_locator,
            "stars": max(normalized_stars, previous),
            "updated_at": now,
            "awarded": awarded,
        }

    async def upsert_reading_quiz_reward(
        self, material_id: str, locator: int, stars: int
    ) -> dict[str, Any]:
        """Raise, never lower, a chapter's server-owned star watermark."""
        return await self._run(self._upsert_reading_quiz_reward_sync, material_id, locator, stars)

    def _list_reading_quiz_rewards_sync(self, material_id: str) -> list[dict[str, Any]]:
        material = str(material_id or "").strip()
        if not material:
            return []
        with self._connect() as conn:
            rows = conn.execute(
                "SELECT locator, stars, updated_at FROM reading_quiz_rewards "
                "WHERE material_id = ? ORDER BY locator",
                (material,),
            ).fetchall()
        return [
            {
                "locator": int(row["locator"]),
                "stars": int(row["stars"]),
                "updated_at": float(row["updated_at"]),
            }
            for row in rows
        ]

    async def list_reading_quiz_rewards(self, material_id: str) -> list[dict[str, Any]]:
        return await self._run(self._list_reading_quiz_rewards_sync, material_id)

    def _reading_quiz_reward_totals_sync(self, material_ids: Sequence[str]) -> dict[str, int]:
        wanted = list(
            dict.fromkeys(
                str(material_id).strip() for material_id in material_ids if str(material_id).strip()
            )
        )
        if not wanted:
            return {}
        placeholders = ",".join("?" for _ in wanted)
        with self._connect() as conn:
            query = f"""
                SELECT material_id, SUM(stars) AS stars
                FROM reading_quiz_rewards
                WHERE material_id IN ({placeholders})
                GROUP BY material_id
                """  # nosec B608 - fixed SQL columns with bound values
            rows = conn.execute(
                query,
                tuple(wanted),
            ).fetchall()
        return {str(row["material_id"]): int(row["stars"]) for row in rows}

    async def reading_quiz_reward_totals(self, material_ids: Sequence[str]) -> dict[str, int]:
        """Return total reward stars for a library page in one query."""
        return await self._run(self._reading_quiz_reward_totals_sync, tuple(material_ids))

    @staticmethod
    def _serialize_notebook_entry(row: sqlite3.Row) -> dict[str, Any]:
        keys = set(row.keys())
        images: list[dict[str, Any]] = []
        if "user_answer_images_json" in keys:
            raw_images = _json_loads(row["user_answer_images_json"], [])
            if isinstance(raw_images, list):
                images = [r for r in raw_images if isinstance(r, dict)]
        is_correct = bool(row["is_correct"])
        stored_result = (row["result"] or "") if "result" in keys else ""
        if stored_result in ASSESSMENT_RESULTS:
            result = stored_result
        else:
            result = "correct" if is_correct else "incorrect"
        assessment_type = (row["assessment_type"] or "") if "assessment_type" in keys else ""
        return {
            "id": int(row["id"]),
            "session_id": (row["session_id"] or "") if "session_id" in keys else "",
            "session_title": row["session_title"] or "" if "session_title" in keys else "",
            "origin_type": (row["origin_type"] or "conversation")
            if "origin_type" in keys
            else "conversation",
            "origin_ref": (row["origin_ref"] or row["session_id"] or "")
            if "origin_ref" in keys
            else (row["session_id"] or ""),
            "turn_id": (row["turn_id"] or "") if "turn_id" in keys else "",
            "question_id": row["question_id"] or "",
            "question": row["question"],
            "question_type": row["question_type"] or "",
            "options": _json_loads(row["options_json"], {}),
            "correct_answer": row["correct_answer"] or "",
            "explanation": row["explanation"] or "",
            "difficulty": row["difficulty"] or "",
            "user_answer": row["user_answer"] or "",
            "user_answer_images": images,
            "is_correct": is_correct,
            "source": (row["source"] or "deep_question") if "source" in keys else "deep_question",
            "material_id": (row["material_id"] or "") if "material_id" in keys else "",
            "material_title": (row["material_title"] or "") if "material_title" in keys else "",
            "section_id": (row["section_id"] or "") if "section_id" in keys else "",
            "section_title": (row["section_title"] or "") if "section_title" in keys else "",
            "score_trend": (row["score_trend"] or "new") if "score_trend" in keys else "new",
            "assessment_type": assessment_type,
            "result": result,
            "mastery_path_id": (row["mastery_path_id"] or "") if "mastery_path_id" in keys else "",
            "knowledge_point_id": (row["knowledge_point_id"] or "")
            if "knowledge_point_id" in keys
            else "",
            "attempt_count": int(row["attempt_count"]) if "attempt_count" in keys else 1,
            "hints_used": int(row["hints_used"]) if "hints_used" in keys else 0,
            "confidence": SQLiteSessionStore._optional_float(
                row["confidence"] if "confidence" in keys else None
            ),
            "response_time": SQLiteSessionStore._optional_float(
                row["response_time"] if "response_time" in keys else None
            ),
            "quality": SQLiteSessionStore._optional_float(
                row["quality"] if "quality" in keys else None
            ),
            "resolved": bool(row["resolved"]) if "resolved" in keys else is_correct,
            "bookmarked": bool(row["bookmarked"]),
            "followup_session_id": row["followup_session_id"] or "",
            "ai_judgment": (row["ai_judgment"] or "") if "ai_judgment" in keys else "",
            "created_at": float(row["created_at"]),
            "updated_at": float(row["updated_at"]),
        }

    # ---- question bank (notebook_entries) queries -----------------------

    @staticmethod
    def _question_bank_filters(query: QuestionBankQuery) -> tuple[str, str, list[Any]]:
        """Build the ``(join, where, params)`` triple shared by list + count.

        Kept apart from the SELECT so the row query and the COUNT query can
        never drift into filtering different sets — the bug that makes a
        paginated list report a total it does not contain.
        """
        joins: list[str] = []
        conditions: list[str] = []
        params: list[Any] = []

        conditions.append(_NOT_RECYCLED_ENTRY_SQL.format(entries="n"))
        if query.mistakes_only:
            conditions.append(
                "EXISTS (SELECT 1 FROM practice_review_state r WHERE r.entry_id = n.id AND r.is_mistake = 1)"
            )
        if query.category_id is not None:
            joins.append(" INNER JOIN notebook_entry_categories ec ON ec.entry_id = n.id")
            conditions.append("ec.category_id = ?")
            params.append(query.category_id)
        elif query.uncategorized:
            conditions.append(
                "NOT EXISTS (SELECT 1 FROM notebook_entry_categories ec WHERE ec.entry_id = n.id)"
            )
        if query.bookmarked is not None:
            conditions.append("n.bookmarked = ?")
            params.append(1 if query.bookmarked else 0)
        if query.is_correct is not None:
            conditions.append("n.is_correct = ?")
            params.append(1 if query.is_correct else 0)
            if not query.is_correct:
                conditions.append(_GRADED_RESULT_SQL)
        if query.assessment_type:
            conditions.append("n.assessment_type = ?")
            params.append(query.assessment_type)
        if query.result:
            if query.result in {"correct", "incorrect"}:
                conditions.append(
                    "(n.result = ? OR (COALESCE(n.result, '') = '' AND n.is_correct = ?))"
                )
                params.extend([query.result, 1 if query.result == "correct" else 0])
            else:
                conditions.append("n.result = ?")
                params.append(query.result)
        if query.mastery_path_id:
            conditions.append("n.mastery_path_id = ?")
            params.append(query.mastery_path_id)
        if query.knowledge_point_id:
            conditions.append("n.knowledge_point_id = ?")
            params.append(query.knowledge_point_id)
        if query.source:
            conditions.append("n.source = ?")
            params.append(query.source)
        if query.material_id:
            conditions.append("n.material_id = ?")
            params.append(query.material_id)
        if query.section_id:
            conditions.append("n.section_id = ?")
            params.append(query.section_id)
        if query.resolved is not None:
            conditions.append("n.resolved = ?")
            params.append(1 if query.resolved else 0)
        if query.score_trend:
            conditions.append("n.score_trend = ?")
            params.append(query.score_trend)
        if query.session_id is not None:
            conditions.append("n.session_id = ?")
            params.append(query.session_id)
        if query.session_ids is not None:
            # Only the placeholder shape is interpolated; session ids remain
            # bound parameters. ``IN (NULL)`` is the explicit empty-set case.
            placeholders = ",".join("?" for _ in query.session_ids) or "NULL"
            conditions.append(f"n.session_id IN ({placeholders})")
            params.extend(query.session_ids)
        if query.search:
            # ESCAPE so a learner searching for "50%" or "a_b" gets literal
            # matches instead of the wildcards those characters would be.
            needle = f"%{_escape_like(query.search)}%"
            conditions.append(
                "(n.question LIKE ? ESCAPE '\\' OR n.user_answer LIKE ? ESCAPE '\\' "
                "OR n.correct_answer LIKE ? ESCAPE '\\' OR n.explanation LIKE ? ESCAPE '\\')"
            )
            params.extend([needle] * 4)

        join_sql = "".join(joins)
        where_sql = (" WHERE " + " AND ".join(conditions)) if conditions else ""
        return join_sql, where_sql, params

    @staticmethod
    def _load_categories_for(
        conn: sqlite3.Connection, entry_ids: list[int]
    ) -> dict[int, list[dict[str, Any]]]:
        """Fetch every entry's categories in one query (never N+1)."""
        if not entry_ids:
            return {}
        placeholders = ",".join("?" * len(entry_ids))
        rows = conn.execute(
            f"""
            SELECT ec.entry_id, c.id, c.name
            FROM notebook_entry_categories ec
            INNER JOIN notebook_categories c ON c.id = ec.category_id
            WHERE ec.entry_id IN ({placeholders})
            ORDER BY c.name
            """,  # nosec B608 - only `?` placeholders are interpolated; every value binds
            tuple(entry_ids),
        ).fetchall()
        grouped: dict[int, list[dict[str, Any]]] = {}
        for row in rows:
            grouped.setdefault(int(row["entry_id"]), []).append(
                {"id": row["id"], "name": row["name"]}
            )
        return grouped

    @staticmethod
    def _load_practice_for(
        conn: sqlite3.Connection, entry_ids: list[int]
    ) -> dict[int, dict[str, Any]]:
        if not entry_ids:
            return {}
        placeholders = ",".join("?" for _ in entry_ids)
        rows = conn.execute(
            f"""SELECT r.*,
                (SELECT e.answer FROM practice_review_events e WHERE e.entry_id=r.entry_id
                 ORDER BY e.reviewed_at DESC, e.rowid DESC LIMIT 1) AS last_answer,
                (SELECT e.rating FROM practice_review_events e WHERE e.entry_id=r.entry_id
                 ORDER BY e.reviewed_at DESC, e.rowid DESC LIMIT 1) AS last_rating
                FROM practice_review_state r WHERE r.entry_id IN ({placeholders})""",  # nosec B608
            entry_ids,
        ).fetchall()
        return {int(row["entry_id"]): dict(row) for row in rows}

    def _list_notebook_entries_sync(self, query: QuestionBankQuery) -> dict[str, Any]:
        query = query.normalized()
        join_sql, where_sql, params = self._question_bank_filters(query)
        order = "ASC" if query.sort == "oldest" else "DESC"
        base = f"""
            SELECT
                n.id, n.session_id, COALESCE(s.title, '') AS session_title,
                n.origin_type, n.origin_ref, n.turn_id, n.question_id,
                n.question, n.question_type, n.options_json,
                n.correct_answer, n.explanation, n.difficulty,
                n.user_answer, n.user_answer_images_json, n.source, n.material_id,
                n.material_title, n.section_id, n.section_title, n.score_trend,
                n.assessment_type, n.result, n.mastery_path_id, n.knowledge_point_id,
                n.attempt_count, n.hints_used, n.confidence, n.response_time, n.quality,
                n.is_correct, n.resolved, n.bookmarked,
                n.followup_session_id, n.ai_judgment, n.created_at, n.updated_at
            FROM notebook_entries n
            LEFT JOIN sessions s ON s.id = n.session_id{join_sql}
        """  # nosec B608 - join_sql/where_sql are literal fragments; every value binds via ?
        count_sql = (
            # join_sql/where_sql are literal fragments; every value binds via ?
            f"SELECT COUNT(*) AS cnt FROM notebook_entries n{join_sql}{where_sql}"  # nosec B608
        )
        with self._connect() as conn:
            total_row = conn.execute(count_sql, tuple(params)).fetchone()
            total = int(total_row["cnt"]) if total_row else 0
            rows = conn.execute(
                base
                + where_sql
                # ``n.id`` breaks ties: a batch upserted in one call shares a
                # created_at, and an unstable order duplicates/drops rows across pages.
                + f" ORDER BY n.created_at {order}, n.id {order} LIMIT ? OFFSET ?",
                tuple(params) + (query.limit, query.offset),
            ).fetchall()
            items = [self._serialize_notebook_entry(r) for r in rows]
            grouped = self._load_categories_for(conn, [int(i["id"]) for i in items])
            practice = self._load_practice_for(conn, [int(i["id"]) for i in items])
        for item in items:
            item["categories"] = grouped.get(int(item["id"]), [])
            item["practice"] = practice.get(int(item["id"]))
        return {"items": items, "total": total}

    async def list_notebook_navigation(self) -> list[dict[str, Any]]:
        """Read small question identities for navigation, without hydrating answers.

        Uses the same recycle predicate as the Question Bank. No new schema or
        migration: old entry IDs remain the IDs used by practice and review.
        """

        def read() -> list[dict[str, Any]]:
            with self._connect() as conn:
                return [
                    dict(row)
                    for row in conn.execute(
                        # Only a fixed table alias is substituted into the local predicate.
                        "SELECT id, substr(question, 1, 160) AS question, created_at, updated_at "  # nosec B608
                        "FROM notebook_entries WHERE "
                        + _NOT_RECYCLED_ENTRY_SQL.format(entries="notebook_entries")
                        + " ORDER BY updated_at DESC, id DESC"
                    ).fetchall()
                ]

        return await self._run(read)

    async def list_notebook_entries(
        self,
        category_id: int | None = None,
        bookmarked: bool | None = None,
        is_correct: bool | None = None,
        limit: int = 50,
        offset: int = 0,
        *,
        session_id: str | None = None,
        session_ids: Sequence[str] | None = None,
        source: str = "",
        material_id: str = "",
        section_id: str = "",
        assessment_type: str = "",
        result: str = "",
        mastery_path_id: str = "",
        knowledge_point_id: str = "",
        resolved: bool | None = None,
        score_trend: str = "",
        search: str = "",
        uncategorized: bool = False,
        mistakes_only: bool = False,
        sort: str = "recent",
    ) -> dict[str, Any]:
        """List question-bank entries. Every row carries its categories."""
        return await self._run(
            self._list_notebook_entries_sync,
            QuestionBankQuery(
                category_id=category_id,
                mistakes_only=mistakes_only,
                uncategorized=uncategorized,
                bookmarked=bookmarked,
                is_correct=is_correct,
                source=source,
                material_id=material_id,
                section_id=section_id,
                assessment_type=assessment_type,
                result=result,
                mastery_path_id=mastery_path_id,
                knowledge_point_id=knowledge_point_id,
                resolved=resolved,
                score_trend=score_trend,
                search=search,
                session_id=session_id,
                session_ids=session_ids,
                sort=sort,
                limit=limit,
                offset=offset,
            ),
        )

    def _question_bank_stats_sync(self, session_ids: Sequence[str] | None = None) -> dict[str, int]:
        with self._connect() as conn:
            # Same ``None`` vs ``[]`` contract as the listing: absent means "do
            # not scope", empty means "scoped to nothing". The rail's counts sit
            # beside the list, so anything the list excludes must not be counted
            # here either.
            where = "WHERE " + _NOT_RECYCLED_ENTRY_SQL.format(entries="notebook_entries")
            params: list[str] = []
            if session_ids is not None:
                placeholders = ",".join("?" for _ in session_ids) or "NULL"
                where += f" AND session_id IN ({placeholders})"
                params = list(session_ids)
            row = conn.execute(
                f"""
                SELECT
                    COUNT(*) AS total,
                    COALESCE(SUM(CASE WHEN is_correct = 0 AND {_GRADED_RESULT_SQL_UNALIASED} THEN 1 ELSE 0 END), 0) AS wrong,
                    COALESCE(SUM(CASE WHEN is_correct = 0 AND resolved = 0 AND {_GRADED_RESULT_SQL_UNALIASED} THEN 1 ELSE 0 END), 0) AS unresolved,
                    COALESCE(SUM(CASE WHEN bookmarked = 1 THEN 1 ELSE 0 END), 0) AS bookmarked,
                    COALESCE(SUM(
                        CASE WHEN NOT EXISTS (
                            SELECT 1 FROM notebook_entry_categories ec
                            WHERE ec.entry_id = notebook_entries.id
                        ) THEN 1 ELSE 0 END
                    ), 0) AS uncategorized
                FROM notebook_entries
                {where}
                """,  # noqa: S608  # nosec B608 - placeholders only; every value stays bound
                params,
            ).fetchone()
        if row is None:
            return {
                "total": 0,
                "wrong": 0,
                "unresolved": 0,
                "bookmarked": 0,
                "uncategorized": 0,
            }
        return {
            "total": int(row["total"]),
            "wrong": int(row["wrong"]),
            "unresolved": int(row["unresolved"]),
            "bookmarked": int(row["bookmarked"]),
            "uncategorized": int(row["uncategorized"]),
        }

    def has_question_bank_entries(self) -> bool:
        """Whether the bank holds anything at all — the tool's mount gate.

        Deliberately synchronous: tool composition runs inside a sync
        policy function, and a bool probe is one indexed row read. Fails
        closed so a missing/locked db never mounts a tool with no data.
        """
        try:
            with self._connect() as conn:
                row = conn.execute("SELECT 1 FROM notebook_entries LIMIT 1").fetchone()
            return row is not None
        except Exception:
            return False

    async def question_bank_stats(
        self,
        session_ids: Sequence[str] | None = None,
    ) -> dict[str, int]:
        """Counts behind the bank's filter chips (and the agent's overview)."""
        return await self._run(
            self._question_bank_stats_sync,
            None if session_ids is None else tuple(session_ids),
        )

    def _list_question_bank_materials_sync(
        self,
        session_ids: Sequence[str] | None = None,
    ) -> list[dict[str, Any]]:
        where = "material_id != '' AND " + _NOT_RECYCLED_ENTRY_SQL.format(
            entries="notebook_entries"
        )
        params: list[str] = []
        if session_ids is not None:
            placeholders = ",".join("?" for _ in session_ids) or "NULL"
            where += f" AND session_id IN ({placeholders})"
            params.extend(session_ids)
        with self._connect() as conn:
            rows = conn.execute(
                f"""
                SELECT
                    source,
                    material_id,
                    COALESCE(NULLIF(MAX(material_title), ''), material_id, 'Unnamed material') AS material_title,
                    COUNT(*) AS entry_count,
                    COALESCE(SUM(CASE WHEN is_correct = 0 AND resolved = 0 AND {_GRADED_RESULT_SQL_UNALIASED} THEN 1 ELSE 0 END), 0) AS unresolved_count
                FROM notebook_entries
                WHERE {where}
                GROUP BY source, material_id
                ORDER BY material_title COLLATE NOCASE
                """,  # nosec B608 - only placeholder shape is interpolated
                params,
            ).fetchall()
        return [dict(row) for row in rows]

    async def list_question_bank_materials(
        self,
        session_ids: Sequence[str] | None = None,
    ) -> list[dict[str, Any]]:
        """Distinct materials for review filters, with wrong-review counts."""
        return await self._run(
            self._list_question_bank_materials_sync,
            None if session_ids is None else tuple(session_ids),
        )

    def _get_notebook_entry_sync(self, entry_id: int) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                """
                SELECT
                    n.*, COALESCE(s.title, '') AS session_title
                FROM notebook_entries n
                LEFT JOIN sessions s ON s.id = n.session_id
                WHERE n.id = ?
                """,
                (entry_id,),
            ).fetchone()
            if row is None:
                return None
            entry = self._serialize_notebook_entry(row)
            entry["practice"] = self._load_practice_for(conn, [entry_id]).get(entry_id)
            cats = conn.execute(
                """
                SELECT c.id, c.name
                FROM notebook_categories c
                INNER JOIN notebook_entry_categories ec ON ec.category_id = c.id
                WHERE ec.entry_id = ?
                ORDER BY c.name
                """,
                (entry_id,),
            ).fetchall()
            entry["categories"] = [{"id": c["id"], "name": c["name"]} for c in cats]
        return entry

    async def get_notebook_entry(self, entry_id: int) -> dict[str, Any] | None:
        return await self._run(self._get_notebook_entry_sync, entry_id)

    def _find_notebook_entry_sync(
        self,
        session_id: str,
        question_id: str,
        turn_id: str | None = None,
    ) -> dict[str, Any] | None:
        # A missing turn_id only ever matches the legacy namespace (rows
        # persisted before turn scoping, migrated with turn_id=''). It must
        # never fall back to other turns' rows: positional question ids
        # (``q_1``..``q_N``) repeat across quizzes in one session, so a
        # cross-turn match would leak a previous quiz's answers into a new
        # quiz (issues #487 / #677).
        return self._find_notebook_entry_by_origin_sync(
            "conversation",
            session_id,
            question_id,
            turn_id,
        )

    async def find_notebook_entry(
        self,
        session_id: str,
        question_id: str,
        turn_id: str | None = None,
    ) -> dict[str, Any] | None:
        return await self._run(self._find_notebook_entry_sync, session_id, question_id, turn_id)

    def _find_notebook_entry_by_origin_sync(
        self,
        origin_type: str,
        origin_ref: str,
        question_id: str,
        turn_id: str | None = None,
    ) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                """
                SELECT n.*, COALESCE(s.title, '') AS session_title
                FROM notebook_entries n
                LEFT JOIN sessions s ON s.id = n.session_id
                WHERE n.origin_type = ?
                  AND n.origin_ref = ?
                  AND n.turn_id = ?
                  AND n.question_id = ?
                """,
                (origin_type, origin_ref, turn_id if turn_id is not None else "", question_id),
            ).fetchone()
        if row is None:
            return None
        return self._serialize_notebook_entry(row)

    async def find_notebook_entry_by_origin(
        self,
        origin_type: str,
        origin_ref: str,
        question_id: str,
        turn_id: str | None = None,
    ) -> dict[str, Any] | None:
        return await self._run(
            self._find_notebook_entry_by_origin_sync,
            origin_type,
            origin_ref,
            question_id,
            turn_id,
        )

    def _update_notebook_entry_sync(self, entry_id: int, updates: dict[str, Any]) -> bool:
        allowed = {
            "bookmarked",
            "followup_session_id",
            "user_answer",
            "is_correct",
            "ai_judgment",
            "resolved",
        }
        fields = {k: v for k, v in updates.items() if k in allowed}
        if not fields:
            return False
        fields["updated_at"] = time.time()
        if "bookmarked" in fields:
            fields["bookmarked"] = 1 if fields["bookmarked"] else 0
        if "is_correct" in fields:
            fields["is_correct"] = 1 if fields["is_correct"] else 0
        if "resolved" in fields:
            fields["resolved"] = 1 if fields["resolved"] else 0
        set_clause = ", ".join(f"{k} = ?" for k in fields)
        values = list(fields.values()) + [entry_id]
        with self._connect() as conn:
            cur = conn.execute(
                f"UPDATE notebook_entries SET {set_clause} WHERE id = ?",  # nosec B608
                tuple(values),
            )
            conn.commit()
        return cur.rowcount > 0

    async def update_notebook_entry(self, entry_id: int, updates: dict[str, Any]) -> bool:
        return await self._run(self._update_notebook_entry_sync, entry_id, updates)

    def _delete_notebook_entry_sync(self, entry_id: int) -> bool:
        with self._connect() as conn:
            cur = conn.execute("DELETE FROM notebook_entries WHERE id = ?", (entry_id,))
            conn.commit()
        return cur.rowcount > 0

    async def delete_notebook_entry(self, entry_id: int) -> bool:
        return await self._run(self._delete_notebook_entry_sync, entry_id)

    # ── Notebook categories ────────────────────────────────────────

    @staticmethod
    def _name_taken(conn: sqlite3.Connection, name: str, *, excluding: int | None = None) -> bool:
        """Whether a category already claims this name, ignoring case.

        The table's UNIQUE index is case-sensitive, so "Math" and "math"
        both fit — but they read as one pile to a learner, and the agent
        resolves names case-insensitively, so it would file into the first
        and report a name the learner sees twice in the rail.
        """
        row = conn.execute(
            "SELECT id FROM notebook_categories WHERE name = ? COLLATE NOCASE",
            (name.strip(),),
        ).fetchone()
        return row is not None and (excluding is None or int(row["id"]) != excluding)

    def _create_category_sync(self, name: str) -> dict[str, Any]:
        now = time.time()
        cleaned = name.strip()
        with self._connect() as conn:
            if self._name_taken(conn, cleaned):
                raise ValueError(f"A category named {cleaned!r} already exists.")
            cur = conn.execute(
                "INSERT INTO notebook_categories (name, created_at) VALUES (?, ?)",
                (cleaned, now),
            )
            conn.commit()
        return {"id": int(cur.lastrowid), "name": cleaned, "created_at": now}

    async def create_category(self, name: str) -> dict[str, Any]:
        return await self._run(self._create_category_sync, name)

    def _list_categories_sync(
        self,
        session_ids: Sequence[str] | None = None,
    ) -> list[dict[str, Any]]:
        # The scope narrows the *count*, never the list: a category the learner
        # created still exists inside a course that has not filled it yet, and
        # dropping the row would make it look deleted. Hence the condition rides
        # on the join instead of a WHERE clause.
        not_recycled = _NOT_RECYCLED_ENTRY_SQL.format(entries="e")
        join = f"LEFT JOIN notebook_entries e ON e.id = ec.entry_id AND {not_recycled}"
        params: list[str] = []
        if session_ids is not None:
            placeholders = ",".join("?" for _ in session_ids) or "NULL"
            join += f" AND e.session_id IN ({placeholders})"
            params = list(session_ids)
        with self._connect() as conn:
            rows = conn.execute(
                f"""
                SELECT c.id, c.name, c.created_at,
                       COUNT(e.id) AS entry_count
                FROM notebook_categories c
                LEFT JOIN notebook_entry_categories ec ON ec.category_id = c.id
                {join}
                GROUP BY c.id
                ORDER BY c.name
                """,  # noqa: S608  # nosec B608 - placeholders only; every value stays bound
                params,
            ).fetchall()
        return [
            {
                "id": r["id"],
                "name": r["name"],
                "created_at": float(r["created_at"]),
                "entry_count": int(r["entry_count"]),
            }
            for r in rows
        ]

    async def list_categories(
        self,
        session_ids: Sequence[str] | None = None,
    ) -> list[dict[str, Any]]:
        return await self._run(
            self._list_categories_sync,
            None if session_ids is None else tuple(session_ids),
        )

    def _rename_category_sync(self, category_id: int, name: str) -> bool:
        cleaned = name.strip()
        with self._connect() as conn:
            # Without this the UNIQUE index raises straight through the
            # router as a 500; a name clash is a request problem, not a bug.
            if self._name_taken(conn, cleaned, excluding=category_id):
                raise ValueError(f"A category named {cleaned!r} already exists.")
            cur = conn.execute(
                "UPDATE notebook_categories SET name = ? WHERE id = ?",
                (cleaned, category_id),
            )
            conn.commit()
        return cur.rowcount > 0

    async def rename_category(self, category_id: int, name: str) -> bool:
        return await self._run(self._rename_category_sync, category_id, name)

    def _delete_category_sync(self, category_id: int) -> bool:
        with self._connect() as conn:
            cur = conn.execute("DELETE FROM notebook_categories WHERE id = ?", (category_id,))
            conn.commit()
        return cur.rowcount > 0

    async def delete_category(self, category_id: int) -> bool:
        return await self._run(self._delete_category_sync, category_id)

    def _add_entry_to_category_sync(self, entry_id: int, category_id: int) -> bool:
        with self._connect() as conn:
            try:
                conn.execute(
                    "INSERT OR IGNORE INTO notebook_entry_categories (entry_id, category_id) VALUES (?, ?)",
                    (entry_id, category_id),
                )
                conn.commit()
            except sqlite3.IntegrityError:
                return False
        return True

    async def add_entry_to_category(self, entry_id: int, category_id: int) -> bool:
        return await self._run(self._add_entry_to_category_sync, entry_id, category_id)

    def _remove_entry_from_category_sync(self, entry_id: int, category_id: int) -> bool:
        with self._connect() as conn:
            cur = conn.execute(
                "DELETE FROM notebook_entry_categories WHERE entry_id = ? AND category_id = ?",
                (entry_id, category_id),
            )
            conn.commit()
        return cur.rowcount > 0

    async def remove_entry_from_category(self, entry_id: int, category_id: int) -> bool:
        return await self._run(self._remove_entry_from_category_sync, entry_id, category_id)

    def _get_entry_categories_sync(self, entry_id: int) -> list[dict[str, Any]]:
        with self._connect() as conn:
            rows = conn.execute(
                """
                SELECT c.id, c.name FROM notebook_categories c
                INNER JOIN notebook_entry_categories ec ON ec.category_id = c.id
                WHERE ec.entry_id = ?
                ORDER BY c.name
                """,
                (entry_id,),
            ).fetchall()
        return [{"id": r["id"], "name": r["name"]} for r in rows]

    async def get_entry_categories(self, entry_id: int) -> list[dict[str, Any]]:
        return await self._run(self._get_entry_categories_sync, entry_id)

    def _link_entries_to_category_sync(
        self, entry_ids: list[int], category_id: int, link: bool
    ) -> int:
        """Add/remove many entries to one category in a single transaction.

        Returns the number of links actually changed, so a caller that asked
        to file 20 questions can tell the learner "18 filed, 2 already there"
        instead of claiming a no-op succeeded.
        """
        if not entry_ids:
            return 0
        with self._connect() as conn:
            exists = conn.execute(
                "SELECT 1 FROM notebook_categories WHERE id = ?", (category_id,)
            ).fetchone()
            if exists is None:
                return 0
            placeholders = ",".join("?" * len(entry_ids))
            # Filter to entries that really exist: a stale id from the agent
            # or a concurrently-deleted row must not abort the whole batch.
            known = [
                int(r["id"])
                for r in conn.execute(
                    f"SELECT id FROM notebook_entries WHERE id IN ({placeholders})",  # nosec B608 - only `?` placeholders are interpolated; every value binds
                    tuple(entry_ids),
                ).fetchall()
            ]
            if not known:
                return 0
            if link:
                cur = conn.executemany(
                    "INSERT OR IGNORE INTO notebook_entry_categories "
                    "(entry_id, category_id) VALUES (?, ?)",
                    [(eid, category_id) for eid in known],
                )
            else:
                cur = conn.executemany(
                    "DELETE FROM notebook_entry_categories WHERE entry_id = ? AND category_id = ?",
                    [(eid, category_id) for eid in known],
                )
            return int(cur.rowcount or 0)

    async def link_entries_to_category(
        self, entry_ids: list[int], category_id: int, *, link: bool = True
    ) -> int:
        return await self._run(
            self._link_entries_to_category_sync, list(entry_ids), category_id, link
        )

    def _find_category_by_name_sync(self, name: str) -> dict[str, Any] | None:
        with self._connect() as conn:
            row = conn.execute(
                "SELECT id, name, created_at FROM notebook_categories "
                "WHERE name = ? COLLATE NOCASE ORDER BY id LIMIT 1",
                (name.strip(),),
            ).fetchone()
        if row is None:
            return None
        return {"id": int(row["id"]), "name": row["name"], "created_at": float(row["created_at"])}

    async def find_category_by_name(self, name: str) -> dict[str, Any] | None:
        """Look a category up by display name — the only handle an agent has."""
        return await self._run(self._find_category_by_name_sync, name)


_instances: dict[str, SQLiteSessionStore] = {}


def get_sqlite_session_store() -> SQLiteSessionStore:
    db_path = get_path_service().get_chat_history_db().resolve()
    key = str(db_path)
    if key not in _instances:
        _instances[key] = SQLiteSessionStore(db_path=db_path)
    return _instances[key]


def get_sqlite_session_store_for(path_service: Any) -> SQLiteSessionStore:
    """Store for an explicit path service (a scope other than the current one).

    Partner turns run inside the partner's synthetic scope but record question
    bank entries into the learner-facing bank; the target scope's path service
    resolves its chat-history db, and instances stay cached per resolved path.
    """
    db_path = path_service.get_chat_history_db().resolve()
    key = str(db_path)
    if key not in _instances:
        _instances[key] = SQLiteSessionStore(db_path=db_path)
    return _instances[key]


__all__ = [
    "QuestionBankQuery",
    "SQLiteSessionStore",
    "get_sqlite_session_store",
    "get_sqlite_session_store_for",
    "make_imported_session_id",
]