Skip to content

Local UI to run and train LLMs and diffusion models. Supports GGUF, MLX, Qwen3.8, DeepSeek-V4.1, MiniMax-H3, Gemma 4, FLUX and more.

228 KB
Raw Blame
# SPDX-License-Identifier: AGPL-3.0-only
# Copyright 2026-present the Unsloth AI Inc. team. All rights reserved. See /studio/LICENSE.AGPL-3.0

"""SQLite storage for training run history and metrics.

Like auth/storage.py (module-level functions, raw sqlite3, per-function connections) plus WAL mode
and PRAGMA foreign_keys = ON for CASCADE deletes.
"""

import base64
import binascii
import hashlib
import json
import logging
import os
import platform
import re
import shutil
import sqlite3
import stat
import threading
import time
from contextlib import contextmanager
from datetime import datetime, timezone
from pathlib import Path

logger = logging.getLogger(__name__)
from typing import Any, Callable, Iterable, Iterator, Mapping, NamedTuple, Optional


from core import chat_originals
from storage import sqlite_gate
from utils.account_context import is_owner_context
from utils.paths import (
    ensure_account_dir,
    ensure_dir,
    project_workspaces_root,
    studio_db_path,
)
from utils.paths.external_media import is_linux_run_media_path, is_local_filesystem_root
from utils.paths import path_utils as _path_utils
from utils.paths.path_utils import macos_volume_ignores_case
from utils.paths.scan_folder_health import is_readable_dir
from utils.paths.sensitive import (
    contains_sensitive_path_component as _shared_contains_sensitive_path_component,
)
from utils.training_runs import extract_project_name


def _extract_project_name_from_config_json(config_json: Optional[str]) -> Optional[str]:
    if not config_json:
        return None
    try:
        return extract_project_name(json.loads(config_json))
    except (json.JSONDecodeError, TypeError):
        return None


def _denied_path_prefixes() -> list[str]:
    system = platform.system()
    if system == "Linux":
        return ["/proc", "/sys", "/dev", "/etc", "/boot", "/run"]
    if system == "Darwin":
        # macOS realpath() resolves /etc -> /private/etc etc; include /private variants.
        return [
            "/System",
            "/Library",
            "/dev",
            "/etc",
            "/private/etc",
            "/tmp",
            "/private/tmp",
            "/var",
            "/private/var",
        ]
    if system == "Windows":
        win = os.environ.get("SystemRoot", r"C:\Windows")
        pf = os.environ.get("ProgramFiles", r"C:\Program Files")
        pf86 = os.environ.get("ProgramFiles(x86)", r"C:\Program Files (x86)")
        return [os.path.normcase(p) for p in [win, pf, pf86]]
    return []


_WSL_WINDOWS_SYSTEM_DIRS = ("windows", "program files", "program files (x86)")


def _is_wsl_windows_system_path(path: str) -> bool:
    """``/mnt/<drive>/Windows`` and ``Program Files`` under WSL: the native-Windows denylist, case-folded like DrvFs."""
    root = _path_utils._WSL_AUTOMOUNT_ROOT
    if not _path_utils._IS_WSL or not path.startswith(root):
        return False
    parts = path[len(root) :].split("/")
    return (
        len(parts) >= 2
        and len(parts[0]) == 1
        and parts[0].isalpha()
        and parts[1].casefold() in _WSL_WINDOWS_SYSTEM_DIRS
    )


def is_denied_system_path(path: str) -> bool:
    """Expects a realpath-resolved path, so a symlink cannot escape a denied system subtree; /run is carved out so Linux removable media stays browseable."""
    system = platform.system()
    fold = system == "Darwin" and macos_volume_ignores_case(path)
    if system == "Windows":
        check = os.path.normcase(path)
        # realpath() keeps the extended-length prefix. Self-contained: tests lift this function out.
        for extended, plain in (("\\\\?\\unc\\", "\\\\"), ("\\\\?\\", "")):
            if check.startswith(extended):
                check = plain + check[len(extended) :]
                break
    elif fold:
        # APFS and HFS+ ignore case unless formatted case-sensitive: /LIBRARY is /Library.
        check = path.casefold()
    else:
        check = path
    for prefix in _denied_path_prefixes():
        if fold:
            prefix = prefix.casefold()
        if check == prefix or check.startswith(prefix + os.sep):
            if prefix == "/run" and is_linux_run_media_path(check):
                continue
            return True
    return system == "Linux" and _is_wsl_windows_system_path(path)


def _contains_sensitive_path_component(path: str) -> bool:
    return _shared_contains_sensitive_path_component(path)


def contains_sensitive_path_component(path: str) -> bool:
    return _contains_sensitive_path_component(path)


_schema_lock = threading.Lock()
_schema_ready: set[Path] = set()

# Opens and closes serialized process-wide against the SQLite 3.51.0-3.51.1 deadlock (#10022).
connect_studio_db = sqlite_gate.connect


_SQLITE_IN_CHUNK_SIZE = 900
_PROJECT_WORKSPACE_SUBDIRS = ("sandbox",)
_CHAT_ATTACHMENT_INVENTORY_VERSION = 5


def _project_slug(name: str) -> str:
    slug = re.sub(r"[^A-Za-z0-9._-]+", "-", name.strip()).strip(".-_")
    return slug[:48] or "project"


def _default_project_root(project: dict) -> str:
    project_id = str(project["id"])
    suffix = re.sub(r"[^A-Za-z0-9_-]+", "-", project_id)[:8].strip("-_") or "project"
    folder_name = f"{_project_slug(str(project.get('name') or 'Project'))}-{suffix}"
    return str(project_workspaces_root() / folder_name)


class ProjectWorkspaceError(OSError):
    """Raised when a project's workspace folder cannot be created.

    Tagged, and carrying the folder, so a caller can name it: the same upsert also touches the
    database directory, which is a different path with a different fix.
    """

    def __init__(self, path: str, cause: OSError):
        super().__init__(str(cause))
        self.path = path


def _ensure_project_workspace(root_path: str) -> str:
    root = Path(root_path).expanduser()
    try:
        root_resolved = ensure_dir(root).resolve()
        for subdir in _PROJECT_WORKSPACE_SUBDIRS:
            ensure_dir(root_resolved / subdir)
    except OSError as exc:
        raise ProjectWorkspaceError(str(root), exc) from exc
    return str(root_resolved)


def sandbox_is_referenced_elsewhere(
    session_id: str, exclude_thread_id: "str | None" = None
) -> bool:
    """Forks keep the source's session id in copied cards, so a surviving chat still needs its sandbox."""
    if not session_id:
        return False
    conn = get_connection()
    try:
        # LIKE only narrows, on the JSON-escaped id; every hit is parsed and must be a sessionId value, since a short id is a substring of ordinary prose.
        escaped = json.dumps(session_id)[1:-1]
        rows = conn.execute(
            """
            SELECT content_json FROM chat_messages
            WHERE (? IS NULL OR thread_id != ?) AND content_json LIKE ? ESCAPE '\\'
            """,
            (exclude_thread_id, exclude_thread_id, f"%{_like_escape(escaped)}%"),
        )
        for row in rows:
            if _mentions_session(row["content_json"], session_id):
                return True
        return False
    except sqlite3.Error:
        # A locked db is not an answer: callers delete on False, so keep the files.
        logger.warning("Could not check references for sandbox %s; keeping it", session_id)
        return True
    finally:
        conn.close()


def _mentions_session(content_json: str, session_id: str) -> bool:
    try:
        content = json.loads(content_json)
    except (TypeError, ValueError):
        return False
    stack = [content]
    while stack:
        node = stack.pop()
        if isinstance(node, dict):
            if node.get("sessionId") == session_id:
                return True
            stack.extend(node.values())
        elif isinstance(node, list):
            stack.extend(node)
    return False


def _like_escape(value: str) -> str:
    for char in ("\\", "%", "_"):
        value = value.replace(char, "\\" + char)
    return value


def delete_project_workspace(project: dict) -> None:
    """Remove a deleted project's workspace directory. Separate from the row delete so the caller can
    stop the tool calls running in there first: pulling the working directory out from under a live
    subprocess is how a half-written file ends up outside any project."""
    _delete_project_workspace(project)


def _delete_project_workspace(project: dict) -> None:
    root_path = project.get("rootPath")
    if not root_path:
        return
    root = Path(root_path).expanduser()
    try:
        root_resolved = root.resolve(strict = False)
    except (OSError, RuntimeError, ValueError):
        logger.warning("Skipping project workspace delete for invalid path %r", root_path)
        return

    project_id = str(project["id"])
    suffix = re.sub(r"[^A-Za-z0-9_-]+", "-", project_id)[:8].strip("-_") or "project"
    if not root_resolved.name.endswith(f"-{suffix}"):
        logger.warning(
            "Skipping project workspace delete for unexpected project path %s",
            root_resolved,
        )
        return
    if root_resolved.parent == root_resolved or root_resolved == Path.home().resolve():
        logger.warning(
            "Skipping project workspace delete for unsafe project path %s",
            root_resolved,
        )
        return
    check = (
        os.path.normcase(str(root_resolved))
        if platform.system() == "Windows"
        else str(root_resolved)
    )
    for prefix in _denied_path_prefixes():
        if check == prefix or check.startswith(prefix + os.sep):
            logger.warning(
                "Skipping project workspace delete under denied path %s",
                root_resolved,
            )
            return
    if not root_resolved.exists():
        return
    if root_resolved.is_symlink() or not root_resolved.is_dir():
        logger.warning(
            "Skipping project workspace delete for non-directory path %s",
            root_resolved,
        )
        return
    shutil.rmtree(root_resolved)


def delete_chat_project_workspace(project: dict) -> None:
    """Backward-compatible name for callers predating sandbox-aware cleanup."""
    delete_project_workspace(project)


_INVENTORY_UPDATE_TRIGGER = "chat_attachment_inventory_dirty_update"

_INVENTORY_UPDATE_TRIGGER_SQL = f"""
    CREATE TRIGGER IF NOT EXISTS {_INVENTORY_UPDATE_TRIGGER}
    AFTER UPDATE OF attachments_json, content_json ON chat_messages
    BEGIN
        INSERT INTO chat_attachment_inventory_state
            (singleton, inventory_version, dirty, backfilled_at)
        VALUES (1, 0, 1, 0)
        ON CONFLICT(singleton) DO UPDATE SET dirty = 1;
    END
"""


def _replace_inventory_update_trigger(conn: sqlite3.Connection) -> None:
    """DDL opens no transaction under legacy sqlite3, so DROP + CREATE must run under the writer lock, or interleaved openers make the second CREATE raise."""
    row = conn.execute(
        "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = ?",
        (_INVENTORY_UPDATE_TRIGGER,),
    ).fetchone()
    # row[0]: some connections still use the default tuple row_factory.
    if row is not None and "UPDATE OF" in (row[0] or ""):
        return
    if conn.in_transaction:
        conn.commit()
    conn.execute("BEGIN IMMEDIATE")
    try:
        conn.execute(f"DROP TRIGGER IF EXISTS {_INVENTORY_UPDATE_TRIGGER}")
        conn.execute(_INVENTORY_UPDATE_TRIGGER_SQL)
        conn.commit()
    except BaseException:
        conn.rollback()
        raise


def _ensure_schema(conn: sqlite3.Connection) -> None:
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS training_runs (
            id TEXT NOT NULL PRIMARY KEY,
            status TEXT NOT NULL DEFAULT 'running',
            model_name TEXT NOT NULL,
            dataset_name TEXT NOT NULL,
            config_json TEXT NOT NULL,
            started_at TEXT NOT NULL,
            ended_at TEXT,
            total_steps INTEGER,
            final_step INTEGER,
            final_loss REAL,
            output_dir TEXT,
            error_message TEXT,
            duration_seconds REAL,
            loss_sparkline TEXT,
            display_name TEXT,
            resume_blocked INTEGER NOT NULL DEFAULT 0,
            resumed_from_run_id TEXT
        )
        """
    )
    existing_cols = {row[1] for row in conn.execute("PRAGMA table_info(training_runs)").fetchall()}
    if "display_name" not in existing_cols:
        conn.execute("ALTER TABLE training_runs ADD COLUMN display_name TEXT")
    if "resume_blocked" not in existing_cols:
        conn.execute(
            "ALTER TABLE training_runs ADD COLUMN resume_blocked INTEGER NOT NULL DEFAULT 0"
        )
    # Nullable, so older rows stay NULL and fall back to the output_dir heuristic.
    if "resumed_from_run_id" not in existing_cols:
        conn.execute("ALTER TABLE training_runs ADD COLUMN resumed_from_run_id TEXT")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS training_metrics (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            run_id TEXT NOT NULL REFERENCES training_runs(id) ON DELETE CASCADE,
            step INTEGER NOT NULL,
            loss REAL,
            learning_rate REAL,
            grad_norm REAL,
            eval_loss REAL,
            epoch REAL,
            num_tokens INTEGER,
            elapsed_seconds REAL,
            UNIQUE(run_id, step)
        )
        """
    )
    conn.execute("CREATE INDEX IF NOT EXISTS idx_metrics_run_id ON training_metrics(run_id)")
    # Partial on the resumed_later subquery's exact status list so no other query (or its tie order) changes plan.
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_training_runs_resumed_later "
        "ON training_runs(output_dir, started_at) "
        "WHERE status IN ('stopped', 'completed', 'error', 'running')"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS api_usage_events (
            id TEXT NOT NULL PRIMARY KEY,
            subject TEXT NOT NULL,
            endpoint TEXT NOT NULL,
            model TEXT NOT NULL,
            status TEXT NOT NULL,
            prompt_tokens INTEGER NOT NULL,
            completion_tokens INTEGER NOT NULL,
            total_tokens INTEGER NOT NULL,
            created_at INTEGER NOT NULL
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_api_usage_events_created_at "
        "ON api_usage_events(created_at)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_api_usage_events_subject_created_at "
        "ON api_usage_events(subject, created_at)"
    )
    # Windows: COLLATE NOCASE so C:\Models and c:\models dedup; elsewhere BINARY keeps them distinct.
    collation = "COLLATE NOCASE" if platform.system() == "Windows" else ""
    conn.execute(
        f"""
        CREATE TABLE IF NOT EXISTS scan_folders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            path TEXT NOT NULL UNIQUE {collation},
            created_at TEXT NOT NULL,
            recursive INTEGER NOT NULL DEFAULT 0
        )
        """
    )
    if "recursive" not in {
        row[1] for row in conn.execute("PRAGMA table_info(scan_folders)").fetchall()
    }:
        conn.execute("ALTER TABLE scan_folders ADD COLUMN recursive INTEGER NOT NULL DEFAULT 0")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_projects (
            id TEXT NOT NULL PRIMARY KEY,
            name TEXT NOT NULL,
            instructions TEXT,
            root_path TEXT,
            archived INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        )
        """
    )
    chat_project_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(chat_projects)").fetchall()
    }
    if "root_path" not in chat_project_cols:
        conn.execute("ALTER TABLE chat_projects ADD COLUMN root_path TEXT")
    # The user's folder, never deleted; only root_path (Studio's own) is removed on project delete.
    if "working_folder" not in chat_project_cols:
        conn.execute("ALTER TABLE chat_projects ADD COLUMN working_folder TEXT")
    if "working_folder_identity" not in chat_project_cols:
        conn.execute("ALTER TABLE chat_projects ADD COLUMN working_folder_identity TEXT")
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_projects_archived_updated_at ON chat_projects(archived, updated_at)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_threads (
            id TEXT NOT NULL PRIMARY KEY,
            title TEXT NOT NULL,
            model_type TEXT NOT NULL,
            model_id TEXT,
            model_gguf_variant TEXT,
            pair_id TEXT,
            project_id TEXT,
            archived INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL,
            updated_at INTEGER,
            openai_code_exec_container_id TEXT,
            anthropic_code_exec_container_id TEXT,
            forked_from_thread_id TEXT,
            forked_from_message_id TEXT,
            fork_boundary_message_id TEXT,
            fork_title_base TEXT,
            settings_json TEXT,
            modified_at INTEGER,
            scheduled_task_id TEXT,
            FOREIGN KEY(project_id) REFERENCES chat_projects(id) ON DELETE CASCADE
        )
        """
    )
    chat_thread_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(chat_threads)").fetchall()
    }
    if "settings_json" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN settings_json TEXT")
    if "model_gguf_variant" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN model_gguf_variant TEXT")
    # Per-writer {writer id: highest seq} so a late keepalive cannot overwrite a newer PATCH; one shared watermark is overwritten by writes from other tabs.
    if "settings_seqs" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN settings_seqs TEXT")
    if "project_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN project_id TEXT")
    if "openai_code_exec_container_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN openai_code_exec_container_id TEXT")
    if "anthropic_code_exec_container_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN anthropic_code_exec_container_id TEXT")
    if "forked_from_thread_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN forked_from_thread_id TEXT")
    if "forked_from_message_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN forked_from_message_id TEXT")
    # Null on forks taken before this column: those threads simply show no divider.
    if "fork_boundary_message_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN fork_boundary_message_id TEXT")
    # Base name a fork numbers from; cleared on rename. Null rows number from the full title.
    if "fork_title_base" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN fork_title_base TEXT")
    # The task whose run made this chat; kept after the task is deleted.
    if "scheduled_task_id" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN scheduled_task_id TEXT")
    # Last rename, move or (un)archive. Null until then.
    if "modified_at" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN modified_at INTEGER")
    if "updated_at" not in chat_thread_cols:
        conn.execute("ALTER TABLE chat_threads ADD COLUMN updated_at INTEGER")
        # Floor at created_at: forked threads copy older ancestor messages, so the fork's creation time wins.
        conn.execute(
            """
            UPDATE chat_threads SET updated_at = MAX(
                COALESCE(
                    (
                        SELECT MAX(m.created_at) FROM chat_messages m
                        WHERE m.thread_id = chat_threads.id
                    ),
                    created_at
                ),
                created_at
            )
            """
        )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_thread_tombstones (
            id TEXT NOT NULL PRIMARY KEY,
            deleted_at INTEGER NOT NULL
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_clear_operations (
            id TEXT NOT NULL PRIMARY KEY,
            active_research_run_ids_json TEXT NOT NULL,
            deleted_thread_ids_json TEXT NOT NULL DEFAULT '[]',
            cleared_at INTEGER NOT NULL,
            reapable_image_ids_json TEXT,
            caches_cleared_at INTEGER
        ) WITHOUT ROWID
        """
    )
    chat_clear_operation_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(chat_clear_operations)").fetchall()
    }
    if "deleted_thread_ids_json" not in chat_clear_operation_cols:
        conn.execute(
            "ALTER TABLE chat_clear_operations ADD COLUMN deleted_thread_ids_json TEXT NOT NULL DEFAULT '[]'"
        )
    # Thumbnail reap runs after commit, so these let a replay finish it after a crash.
    # NULL (older build) is treated as done: a replay must never reap on a guess.
    if "reapable_image_ids_json" not in chat_clear_operation_cols:
        conn.execute("ALTER TABLE chat_clear_operations ADD COLUMN reapable_image_ids_json TEXT")
    if "caches_cleared_at" not in chat_clear_operation_cols:
        conn.execute("ALTER TABLE chat_clear_operations ADD COLUMN caches_cleared_at INTEGER")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_messages (
            id TEXT NOT NULL PRIMARY KEY,
            thread_id TEXT NOT NULL REFERENCES chat_threads(id) ON DELETE CASCADE,
            parent_id TEXT,
            role TEXT NOT NULL,
            content_json TEXT NOT NULL,
            attachments_json TEXT,
            metadata_json TEXT,
            created_at INTEGER NOT NULL
        )
        """
    )
    tombstone_schema = """
        CREATE TABLE chat_attachment_tombstones (
            thread_id TEXT NOT NULL REFERENCES chat_threads(id) ON DELETE CASCADE,
            message_id TEXT NOT NULL,
            attachment_id TEXT NOT NULL,
            deleted_at INTEGER NOT NULL,
            PRIMARY KEY(thread_id, message_id, attachment_id)
        ) WITHOUT ROWID
    """
    tombstone_table = conn.execute(
        """
        SELECT 1 FROM sqlite_master
        WHERE type = 'table' AND name = 'chat_attachment_tombstones'
        """
    ).fetchone()
    if tombstone_table is None:
        conn.execute(tombstone_schema)
    else:
        tombstone_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(chat_attachment_tombstones)")
        }
        tombstone_fk_targets = {
            row[2] for row in conn.execute("PRAGMA foreign_key_list(chat_attachment_tombstones)")
        }
        if "thread_id" not in tombstone_columns or "chat_threads" not in tombstone_fk_targets:
            # The first version cascaded and lost tombstones; rebuild once keeping those whose thread exists.
            conn.execute("SAVEPOINT migrate_chat_attachment_tombstones")
            try:
                conn.execute(
                    "ALTER TABLE chat_attachment_tombstones "
                    "RENAME TO chat_attachment_tombstones_legacy"
                )
                conn.execute(tombstone_schema)
                if "thread_id" in tombstone_columns:
                    conn.execute(
                        """
                        INSERT OR IGNORE INTO chat_attachment_tombstones
                            (thread_id, message_id, attachment_id, deleted_at)
                        SELECT legacy.thread_id, legacy.message_id,
                               legacy.attachment_id, legacy.deleted_at
                        FROM chat_attachment_tombstones_legacy legacy
                        JOIN chat_threads thread ON thread.id = legacy.thread_id
                        """
                    )
                else:
                    conn.execute(
                        """
                        INSERT OR IGNORE INTO chat_attachment_tombstones
                            (thread_id, message_id, attachment_id, deleted_at)
                        SELECT message.thread_id, legacy.message_id,
                               legacy.attachment_id, legacy.deleted_at
                        FROM chat_attachment_tombstones_legacy legacy
                        JOIN chat_messages message ON message.id = legacy.message_id
                        """
                    )
                conn.execute("DROP TABLE chat_attachment_tombstones_legacy")
                conn.execute("RELEASE SAVEPOINT migrate_chat_attachment_tombstones")
            except Exception:
                conn.execute("ROLLBACK TO SAVEPOINT migrate_chat_attachment_tombstones")
                conn.execute("RELEASE SAVEPOINT migrate_chat_attachment_tombstones")
                raise
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_attachment_inventory (
            message_id TEXT NOT NULL REFERENCES chat_messages(id) ON DELETE CASCADE,
            attachment_id TEXT NOT NULL,
            name TEXT NOT NULL,
            type TEXT,
            content_type TEXT,
            size_bytes INTEGER,
            original_sha256 TEXT,
            text_bytes INTEGER,
            PRIMARY KEY(message_id, attachment_id)
        ) WITHOUT ROWID
        """
    )
    inventory_columns = {
        row[1] for row in conn.execute("PRAGMA table_info(chat_attachment_inventory)")
    }
    if "original_sha256" not in inventory_columns:
        conn.execute("ALTER TABLE chat_attachment_inventory ADD COLUMN original_sha256 TEXT")
    if "text_bytes" not in inventory_columns:
        conn.execute("ALTER TABLE chat_attachment_inventory ADD COLUMN text_bytes INTEGER")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_attachment_inventory_state (
            singleton INTEGER NOT NULL PRIMARY KEY CHECK(singleton = 1),
            inventory_version INTEGER NOT NULL DEFAULT 0,
            dirty INTEGER NOT NULL DEFAULT 1,
            backfilled_at INTEGER NOT NULL
        )
        """
    )
    inventory_state_columns = {
        row[1] for row in conn.execute("PRAGMA table_info(chat_attachment_inventory_state)")
    }
    if "inventory_version" not in inventory_state_columns:
        conn.execute(
            "ALTER TABLE chat_attachment_inventory_state "
            "ADD COLUMN inventory_version INTEGER NOT NULL DEFAULT 0"
        )
    if "dirty" not in inventory_state_columns:
        conn.execute(
            "ALTER TABLE chat_attachment_inventory_state "
            "ADD COLUMN dirty INTEGER NOT NULL DEFAULT 1"
        )
    conn.execute(
        """
        CREATE TRIGGER IF NOT EXISTS chat_attachment_inventory_dirty_insert
        AFTER INSERT ON chat_messages
        BEGIN
            INSERT INTO chat_attachment_inventory_state
                (singleton, inventory_version, dirty, backfilled_at)
            VALUES (1, 0, 1, 0)
            ON CONFLICT(singleton) DO UPDATE SET dirty = 1;
        END
        """
    )
    # Scoped to inventory columns so status-only changes skip rehashing; the old trigger is dropped first, since a create would keep it.
    _replace_inventory_update_trigger(conn)
    conn.execute(
        """
        CREATE TRIGGER IF NOT EXISTS chat_attachment_inventory_dirty_delete
        AFTER DELETE ON chat_messages
        BEGIN
            INSERT INTO chat_attachment_inventory_state
                (singleton, inventory_version, dirty, backfilled_at)
            VALUES (1, 0, 1, 0)
            ON CONFLICT(singleton) DO UPDATE SET dirty = 1;
        END
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_threads_model_type_created_at ON chat_threads(model_type, created_at)"
    )
    conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_threads_pair_id ON chat_threads(pair_id)")
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_threads_project_id ON chat_threads(project_id)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_threads_forked_from "
        "ON chat_threads(forked_from_thread_id, forked_from_message_id)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_messages_thread_id_created_at ON chat_messages(thread_id, created_at)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_settings (
            key TEXT NOT NULL PRIMARY KEY,
            value_json TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        """
    )
    # Sidebar IDs (incl. comparison pair IDs), so no foreign key to one thread row.
    conn.execute("CREATE TABLE IF NOT EXISTS chat_pin_tombstones (id TEXT NOT NULL PRIMARY KEY)")
    conn.execute("CREATE TABLE IF NOT EXISTS chat_pin_deletions (id TEXT NOT NULL PRIMARY KEY)")
    conn.execute("CREATE TABLE IF NOT EXISTS chat_pin_operations (id TEXT NOT NULL PRIMARY KEY)")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS app_settings (
            key TEXT NOT NULL PRIMARY KEY,
            value_json TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_settings_quarantine (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            key TEXT NOT NULL,
            value_json TEXT NOT NULL,
            reason TEXT NOT NULL,
            quarantined_at TEXT NOT NULL
        )
        """
    )
    # Ledger lives in studio.db so a db wipe re-triggers the legacy Dexie import, instead of a localStorage flag silently hiding threads.
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_legacy_imports (
            legacy_thread_id TEXT NOT NULL PRIMARY KEY,
            imported_at INTEGER NOT NULL
        ) WITHOUT ROWID
        """
    )
    # Import ledger: without it a re-import cannot tell a turn deleted in Studio from a newly appended one.
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS external_import_sessions (
            source TEXT NOT NULL,
            session_id TEXT NOT NULL,
            turns_imported INTEGER NOT NULL,
            revision TEXT NOT NULL DEFAULT '',
            PRIMARY KEY (source, session_id)
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS prompt_entries (
            id TEXT NOT NULL PRIMARY KEY,
            name TEXT NOT NULL,
            text TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        )
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_prompt_entries_created_at ON prompt_entries(created_at)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS prompt_lists (
            id TEXT NOT NULL PRIMARY KEY,
            name TEXT NOT NULL,
            items_json TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        )
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_prompt_lists_created_at ON prompt_lists(created_at)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS data_recipes (
            id TEXT NOT NULL PRIMARY KEY,
            name TEXT NOT NULL,
            payload_json TEXT NOT NULL,
            learning_recipe_id TEXT,
            learning_recipe_title TEXT,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        )
        """
    )
    # Deleted ids stay here so a stale tab or a re-run legacy import cannot bring them back.
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS data_recipe_tombstones (
            id TEXT NOT NULL PRIMARY KEY,
            deleted_at INTEGER NOT NULL
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS data_recipe_executions (
            id TEXT NOT NULL PRIMARY KEY,
            recipe_id TEXT NOT NULL REFERENCES data_recipes(id) ON DELETE CASCADE,
            created_at INTEGER NOT NULL,
            record_json TEXT NOT NULL
        )
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_data_recipe_executions_recipe"
        " ON data_recipe_executions(recipe_id, created_at)"
    )
    # next_run_at NULL = nothing left to run; notify = all|failures|none; named=0 = title follows the instructions.
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS scheduled_tasks (
            id TEXT NOT NULL PRIMARY KEY,
            owner_subject TEXT NOT NULL,
            title TEXT NOT NULL,
            prompt TEXT NOT NULL,
            schedule_json TEXT NOT NULL,
            timezone TEXT NOT NULL,
            enabled INTEGER NOT NULL DEFAULT 1,
            next_run_at INTEGER,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            notify TEXT NOT NULL DEFAULT 'all',
            named INTEGER NOT NULL DEFAULT 1,
            model TEXT,
            reasoning_effort TEXT,
            allow_model_load INTEGER NOT NULL DEFAULT 0,
            archive_successful INTEGER NOT NULL DEFAULT 0,
            project_id TEXT
        )
        """
    )
    scheduled_task_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(scheduled_tasks)").fetchall()
    }
    if "notify" not in scheduled_task_cols:
        conn.execute("ALTER TABLE scheduled_tasks ADD COLUMN notify TEXT NOT NULL DEFAULT 'all'")
    if "named" not in scheduled_task_cols:
        conn.execute("ALTER TABLE scheduled_tasks ADD COLUMN named INTEGER NOT NULL DEFAULT 1")
    if "model" not in scheduled_task_cols:
        conn.execute("ALTER TABLE scheduled_tasks ADD COLUMN model TEXT")
    if "reasoning_effort" not in scheduled_task_cols:
        conn.execute("ALTER TABLE scheduled_tasks ADD COLUMN reasoning_effort TEXT")
    if "allow_model_load" not in scheduled_task_cols:
        conn.execute(
            "ALTER TABLE scheduled_tasks ADD COLUMN allow_model_load INTEGER NOT NULL DEFAULT 0"
        )
    if "archive_successful" not in scheduled_task_cols:
        conn.execute(
            "ALTER TABLE scheduled_tasks ADD COLUMN archive_successful INTEGER NOT NULL DEFAULT 0"
        )
    if "project_id" not in scheduled_task_cols:
        conn.execute("ALTER TABLE scheduled_tasks ADD COLUMN project_id TEXT")
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_scheduled_tasks_next_run ON scheduled_tasks(next_run_at)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS scheduled_task_runs (
            id TEXT NOT NULL PRIMARY KEY,
            task_id TEXT NOT NULL REFERENCES scheduled_tasks(id) ON DELETE CASCADE,
            source TEXT NOT NULL,
            status TEXT NOT NULL,
            scheduled_for INTEGER,
            created_at INTEGER NOT NULL,
            started_at INTEGER,
            finished_at INTEGER,
            heartbeat_at INTEGER,
            worker_id TEXT,
            thread_id TEXT REFERENCES chat_threads(id) ON DELETE SET NULL,
            model TEXT,
            error_code TEXT,
            error TEXT,
            allow_model_load INTEGER NOT NULL DEFAULT 0
        )
        """
    )
    scheduled_run_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(scheduled_task_runs)").fetchall()
    }
    if "allow_model_load" not in scheduled_run_cols:
        conn.execute(
            "ALTER TABLE scheduled_task_runs ADD COLUMN allow_model_load INTEGER NOT NULL DEFAULT 0"
        )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_scheduled_task_runs_task"
        " ON scheduled_task_runs(task_id, created_at)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_scheduled_task_runs_finished"
        " ON scheduled_task_runs(finished_at)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_scheduled_task_runs_thread ON scheduled_task_runs(thread_id)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS research_runs (
            id TEXT NOT NULL PRIMARY KEY,
            owner_subject TEXT NOT NULL,
            thread_id TEXT NOT NULL REFERENCES chat_threads(id) ON DELETE CASCADE,
            user_message_id TEXT NOT NULL REFERENCES chat_messages(id) ON DELETE CASCADE,
            assistant_message_id TEXT REFERENCES chat_messages(id) ON DELETE SET NULL,
            status TEXT NOT NULL CHECK(status IN (
                'planning', 'awaiting_approval', 'queued', 'running', 'paused',
                'cancelling', 'cancelled', 'completed', 'failed'
            )),
            plan_json TEXT,
            plan_revision INTEGER NOT NULL DEFAULT 0,
            plan_hash TEXT,
            config_json TEXT NOT NULL,
            cancel_requested INTEGER NOT NULL DEFAULT 0,
            lease_owner TEXT,
            lease_expires_at INTEGER,
            heartbeat_at INTEGER,
            retry_count INTEGER NOT NULL DEFAULT 0,
            error_message TEXT,
            report_text TEXT,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            started_at INTEGER,
            completed_at INTEGER,
            next_event_seq INTEGER NOT NULL DEFAULT 1
        )
        """
    )
    research_run_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(research_runs)").fetchall()
    }
    if "report_text" not in research_run_cols:
        conn.execute("ALTER TABLE research_runs ADD COLUMN report_text TEXT")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS research_thread_claims (
            owner_subject TEXT NOT NULL,
            thread_id TEXT NOT NULL PRIMARY KEY REFERENCES chat_threads(id) ON DELETE CASCADE,
            created_at INTEGER NOT NULL
        ) WITHOUT ROWID
        """
    )
    claim_pk = [
        row[1]
        for row in sorted(
            conn.execute("PRAGMA table_info(research_thread_claims)").fetchall(),
            key = lambda row: int(row[5] or 0),
        )
        if int(row[5] or 0) > 0
    ]
    if claim_pk != ["thread_id"]:
        # Rebuild atomically: in autocommit an interruption orphaned rows in _legacy.
        conn.commit()
        conn.execute("BEGIN IMMEDIATE")
        try:
            conn.execute(
                "ALTER TABLE research_thread_claims RENAME TO research_thread_claims_legacy"
            )
            conn.execute(
                """
                CREATE TABLE research_thread_claims (
                    owner_subject TEXT NOT NULL,
                    thread_id TEXT NOT NULL PRIMARY KEY REFERENCES chat_threads(id) ON DELETE CASCADE,
                    created_at INTEGER NOT NULL
                ) WITHOUT ROWID
                """
            )
            conn.execute(
                """INSERT OR IGNORE INTO research_thread_claims
                   (owner_subject, thread_id, created_at)
                   SELECT owner_subject, thread_id, created_at
                   FROM research_thread_claims_legacy
                   ORDER BY created_at, owner_subject"""
            )
            conn.execute("DROP TABLE research_thread_claims_legacy")
            conn.commit()
        except Exception:
            conn.rollback()
            raise
    conn.execute(
        """INSERT OR IGNORE INTO research_thread_claims
           (owner_subject, thread_id, created_at)
           SELECT owner_subject, thread_id, created_at
           FROM research_runs ORDER BY created_at, id"""
    )
    conn.execute(
        """UPDATE research_runs
           SET status='failed', error_message='Superseded by the global thread research claim',
               lease_owner=NULL, lease_expires_at=NULL, completed_at=COALESCE(completed_at, updated_at)
           WHERE status IN ('planning','awaiting_approval','queued','running','paused','cancelling')
             AND EXISTS (
                 SELECT 1 FROM research_thread_claims c
                 WHERE c.thread_id=research_runs.thread_id
                   AND c.owner_subject<>research_runs.owner_subject
             )"""
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS research_plan_steps (
            run_id TEXT NOT NULL REFERENCES research_runs(id) ON DELETE CASCADE,
            position INTEGER NOT NULL,
            title TEXT NOT NULL,
            query TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'pending',
            result_json TEXT,
            started_at INTEGER,
            completed_at INTEGER,
            PRIMARY KEY(run_id, position)
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS research_sources (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            run_id TEXT NOT NULL REFERENCES research_runs(id) ON DELETE CASCADE,
            step_position INTEGER,
            url TEXT NOT NULL,
            title TEXT,
            snippet TEXT,
            fetched_at INTEGER NOT NULL,
            UNIQUE(run_id, url)
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS research_document_sources (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            run_id TEXT NOT NULL REFERENCES research_runs(id) ON DELETE CASCADE,
            step_position INTEGER,
            source_key TEXT NOT NULL,
            document_id TEXT,
            chunk_id TEXT,
            filename TEXT NOT NULL,
            page INTEGER,
            score REAL,
            snippet TEXT,
            fetched_at INTEGER NOT NULL,
            UNIQUE(run_id, source_key)
        )
        """
    )
    document_source_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(research_document_sources)").fetchall()
    }
    if "kind" not in document_source_cols:
        conn.execute("ALTER TABLE research_document_sources ADD COLUMN kind TEXT")
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS research_events (
            run_id TEXT NOT NULL REFERENCES research_runs(id) ON DELETE CASCADE,
            seq INTEGER NOT NULL,
            event_type TEXT NOT NULL,
            data_json TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            PRIMARY KEY(run_id, seq)
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_research_runs_owner_thread_status "
        "ON research_runs(owner_subject, thread_id, status)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_research_runs_lease "
        "ON research_runs(status, lease_expires_at)"
    )
    conn.execute("CREATE INDEX IF NOT EXISTS idx_research_runs_thread ON research_runs(thread_id)")
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_research_runs_user_message "
        "ON research_runs(user_message_id)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_research_runs_assistant_message "
        "ON research_runs(assistant_message_id)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_research_sources_run ON research_sources(run_id, id)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_research_document_sources_run "
        "ON research_document_sources(run_id, id)"
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_generation_runs (
            id TEXT NOT NULL PRIMARY KEY,
            owner_subject TEXT NOT NULL,
            thread_id TEXT NOT NULL REFERENCES chat_threads(id) ON DELETE CASCADE,
            user_message_id TEXT NOT NULL REFERENCES chat_messages(id) ON DELETE CASCADE,
            assistant_message_id TEXT NOT NULL REFERENCES chat_messages(id) ON DELETE CASCADE,
            request_hash TEXT NOT NULL,
            request_json TEXT NOT NULL,
            worker_token TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status IN (
                'queued', 'running', 'cancelling', 'cancelled', 'completed', 'failed'
            )),
            cancel_requested INTEGER NOT NULL DEFAULT 0,
            last_event_seq INTEGER NOT NULL DEFAULT 0,
            finish_reason TEXT,
            error_message TEXT,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL,
            started_at INTEGER,
            completed_at INTEGER
        )
        """
    )
    chat_generation_run_cols = {
        row[1] for row in conn.execute("PRAGMA table_info(chat_generation_runs)").fetchall()
    }
    if "worker_token" not in chat_generation_run_cols:
        conn.execute("ALTER TABLE chat_generation_runs ADD COLUMN worker_token TEXT")
    conn.execute(
        """UPDATE chat_generation_runs SET worker_token=lower(hex(randomblob(16)))
           WHERE worker_token IS NULL"""
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS chat_generation_events (
            run_id TEXT NOT NULL REFERENCES chat_generation_runs(id) ON DELETE CASCADE,
            seq INTEGER NOT NULL,
            event_type TEXT NOT NULL,
            payload_json TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            PRIMARY KEY(run_id, seq)
        ) WITHOUT ROWID
        """
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_generation_runs_owner_thread_status "
        "ON chat_generation_runs(owner_subject, thread_id, status)"
    )
    conn.execute("DROP INDEX IF EXISTS idx_chat_generation_runs_one_active_thread")
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_generation_runs_thread_status "
        "ON chat_generation_runs(thread_id, status)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_generation_runs_user_message "
        "ON chat_generation_runs(user_message_id)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_generation_runs_assistant_message "
        "ON chat_generation_runs(assistant_message_id)"
    )
    # Partial on the reconcile sweep's exact status list, so the periodic sweep skips terminal rows.
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_chat_generation_runs_active "
        "ON chat_generation_runs(created_at, id) "
        "WHERE status IN ('queued','running','cancelling')"
    )
    conn.execute(
        """
        CREATE TRIGGER IF NOT EXISTS reject_second_active_chat_generation_insert
        BEFORE INSERT ON chat_generation_runs
        WHEN NEW.status IN ('queued','running','cancelling')
         AND EXISTS (
             SELECT 1 FROM chat_generation_runs
             WHERE thread_id = NEW.thread_id
               AND status IN ('queued','running','cancelling')
         )
        BEGIN
            SELECT RAISE(ABORT, 'thread already has an active chat generation');
        END
        """
    )
    conn.execute(
        """
        CREATE TRIGGER IF NOT EXISTS reject_second_active_chat_generation_update
        BEFORE UPDATE OF thread_id, status ON chat_generation_runs
        WHEN NEW.status IN ('queued','running','cancelling')
         AND EXISTS (
             SELECT 1 FROM chat_generation_runs
             WHERE thread_id = NEW.thread_id
               AND id != NEW.id
               AND status IN ('queued','running','cancelling')
         )
        BEGIN
            SELECT RAISE(ABORT, 'thread already has an active chat generation');
        END
        """
    )
    conn.execute(
        """
        CREATE TRIGGER IF NOT EXISTS tombstone_chat_generation_run_id
        BEFORE DELETE ON chat_generation_runs
        BEGIN
            INSERT OR REPLACE INTO app_settings (key, value_json, updated_at)
            VALUES (
                'chat-generation-run-tombstone:' || OLD.id,
                'true',
                CAST(strftime('%s', 'now') AS INTEGER) * 1000
            );
        END
        """
    )
    inventory_state = conn.execute(
        """
        SELECT inventory_version, dirty
        FROM chat_attachment_inventory_state
        WHERE singleton = 1
        """
    ).fetchone()
    # Positional read: works for raw tuple or sqlite3.Row (no row_factory precondition).
    if (
        inventory_state is None
        or inventory_state[0] != _CHAT_ATTACHMENT_INVENTORY_VERSION
        or inventory_state[1]
    ):
        _rebuild_chat_attachment_inventory(conn)
        _mark_chat_attachment_inventory_clean(conn)
    conn.commit()


def _prompt_entry_from_row(row: sqlite3.Row) -> dict:
    return {
        "id": row["id"],
        "name": row["name"],
        "text": row["text"],
        "createdAt": row["created_at"],
        "updatedAt": row["updated_at"],
    }


def list_prompt_entries() -> list[dict]:
    conn = get_connection()
    try:
        rows = conn.execute("SELECT * FROM prompt_entries ORDER BY created_at DESC").fetchall()
        return [_prompt_entry_from_row(r) for r in rows]
    finally:
        conn.close()


def upsert_prompt_entry(entry: dict) -> dict:
    conn = get_connection()
    try:
        conn.execute(
            """
            INSERT INTO prompt_entries (id, name, text, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                name = excluded.name,
                text = excluded.text,
                updated_at = excluded.updated_at
            """,
            (
                entry["id"],
                entry["name"],
                entry["text"],
                entry["createdAt"],
                entry["updatedAt"],
            ),
        )
        conn.commit()
        return entry
    finally:
        conn.close()


def delete_prompt_entry(entry_id: str) -> None:
    conn = get_connection()
    try:
        conn.execute("DELETE FROM prompt_entries WHERE id = ?", (entry_id,))
        conn.commit()
    finally:
        conn.close()


def bulk_upsert_prompt_entries(entries: list[dict]) -> int:
    if not entries:
        return 0
    conn = get_connection()
    try:
        conn.executemany(
            """
            INSERT INTO prompt_entries (id, name, text, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                name = excluded.name,
                text = excluded.text,
                updated_at = excluded.updated_at
            """,
            [(e["id"], e["name"], e["text"], e["createdAt"], e["updatedAt"]) for e in entries],
        )
        conn.commit()
        return len(entries)
    finally:
        conn.close()


def _prompt_list_from_row(row: sqlite3.Row) -> dict:
    return {
        "id": row["id"],
        "name": row["name"],
        "items": json.loads(row["items_json"]),
        "createdAt": row["created_at"],
        "updatedAt": row["updated_at"],
    }


def list_prompt_lists_db() -> list[dict]:
    conn = get_connection()
    try:
        rows = conn.execute("SELECT * FROM prompt_lists ORDER BY created_at DESC").fetchall()
        return [_prompt_list_from_row(r) for r in rows]
    finally:
        conn.close()


def upsert_prompt_list(lst: dict) -> dict:
    conn = get_connection()
    try:
        conn.execute(
            """
            INSERT INTO prompt_lists (id, name, items_json, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                name = excluded.name,
                items_json = excluded.items_json,
                updated_at = excluded.updated_at
            """,
            (
                lst["id"],
                lst["name"],
                json.dumps(lst["items"]),
                lst["createdAt"],
                lst["updatedAt"],
            ),
        )
        conn.commit()
        return lst
    finally:
        conn.close()


def delete_prompt_list_db(list_id: str) -> None:
    conn = get_connection()
    try:
        conn.execute("DELETE FROM prompt_lists WHERE id = ?", (list_id,))
        conn.commit()
    finally:
        conn.close()


def bulk_upsert_prompt_lists(lists: list[dict]) -> int:
    if not lists:
        return 0
    conn = get_connection()
    try:
        conn.executemany(
            """
            INSERT INTO prompt_lists (id, name, items_json, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                name = excluded.name,
                items_json = excluded.items_json,
                updated_at = excluded.updated_at
            """,
            [
                (
                    lst["id"],
                    lst["name"],
                    json.dumps(lst["items"]),
                    lst["createdAt"],
                    lst["updatedAt"],
                )
                for lst in lists
            ],
        )
        conn.commit()
        return len(lists)
    finally:
        conn.close()


# Old names kept: storage.api_usage_db and core.research_runs import them from here.
is_sqlite_busy_error = sqlite_gate.is_busy_error
_apply_wal_synchronous = sqlite_gate.apply_wal_synchronous


# sqlite's own default, kept for every caller that may run on the event loop
_BUSY_TIMEOUT_SECONDS = 5.0

# Only for chat history transactions in the threadpool: a large clear can hold the writer lock past the default and escape as an unmapped 500.
_CONTENDED_BUSY_TIMEOUT_SECONDS = 30.0


def reset_schema_state_for_tests() -> None:
    with _schema_lock:
        _schema_ready.clear()


def get_connection(
    busy_timeout_seconds: float = _BUSY_TIMEOUT_SECONDS,
    *,
    check_same_thread: bool = True,
    _manage_keeper: bool = True,
) -> sqlite3.Connection:
    db_path = studio_db_path()
    ensure_account_dir(db_path.parent)
    conn = connect_studio_db(
        db_path, timeout = busy_timeout_seconds, check_same_thread = check_same_thread
    )
    conn.row_factory = sqlite3.Row
    # foreign_keys is session-scoped; set per connection
    conn.execute("PRAGMA foreign_keys=ON")
    if db_path not in _schema_ready:
        with _schema_lock:
            schema_path = db_path.resolve()
            if schema_path not in _schema_ready:
                try:
                    _ensure_schema(conn)
                    conn.commit()
                    # Both spellings, so a symlinked path skips the resolve next time.
                    _schema_ready.update((schema_path, db_path))
                except Exception:
                    conn.close()
                    raise
    _apply_wal_synchronous(conn)
    # main.py only keeps the owner's DB open, so a managed account would checkpoint on every close.
    if (
        _manage_keeper
        and not is_owner_context()
        and db_path not in _wal_keepers
        and db_path not in _wal_unsupported
    ):
        try:
            open_wal_keeper(replace = False)
        except Exception:
            conn.close()
            raise
    return conn


# Held open so the last-writer close does not checkpoint the WAL several times a second (#9934).
_wal_keepers: dict[Path, sqlite3.Connection] = {}
_wal_keeper_lock = threading.Lock()
_wal_unsupported: set[Path] = set()


def open_wal_keeper(*, replace: bool = True) -> bool:
    """Hold this database's WAL open. ``replace = False`` leaves an existing keeper alone."""
    db_path = studio_db_path().resolve()
    with _wal_keeper_lock:
        if not replace:
            if db_path in _wal_keepers:
                return True
            if db_path in _wal_unsupported:
                return False
        previous = _wal_keepers.pop(db_path, None)
        if previous is not None:
            _close_keeper(previous)
        # check_same_thread off so a keeper stranded by an earlier lifespan can be closed here.
        conn = get_connection(check_same_thread = False, _manage_keeper = False)
        try:
            # Read the mode in force: WAL declines silently on filesystems without shared memory.
            mode = conn.execute("PRAGMA journal_mode").fetchone()[0]
        except (sqlite3.Error, TypeError, IndexError) as exc:
            conn.close()
            logger.warning("Could not read studio.db journal mode: %s", exc)
            return False
        if not isinstance(mode, str) or mode.lower() != "wal":
            conn.close()
            _wal_unsupported.add(db_path)
            logger.info("studio.db is in %s mode, not WAL; WAL keeper not engaged.", mode)
            return False
        _wal_unsupported.discard(db_path)
        _wal_keepers[db_path] = conn
        return True


def _close_keeper(conn: sqlite3.Connection) -> None:
    try:
        conn.close()
    except Exception as exc:
        logger.warning("Could not close the studio.db WAL keeper: %s", exc)


# Listeners drop their own long-lived connections so the keeper close can remove -wal (#9934).
_keeper_close_listeners: list[Callable[[], None]] = []


def on_wal_keeper_closed(listener: Callable[[], None]) -> None:
    _keeper_close_listeners.append(listener)


def _notify_keeper_closed() -> None:
    for listener in tuple(_keeper_close_listeners):
        try:
            listener()
        except Exception:
            logger.warning("A WAL keeper close listener failed", exc_info = True)


def close_wal_keeper_for(path: str | Path) -> None:
    db_path = Path(path).resolve()
    with _wal_keeper_lock:
        conn = _wal_keepers.pop(db_path, None)
        if conn is not None:
            _close_keeper(conn)
    # Unconditionally: installs without WAL never have a keeper but still mean 'let go'.
    _notify_keeper_closed()


def close_wal_keeper() -> None:
    with _wal_keeper_lock:
        for conn in _wal_keepers.values():
            _close_keeper(conn)
        _wal_keepers.clear()
        _wal_unsupported.clear()
    _notify_keeper_closed()


def create_run(
    id: str,
    model_name: str,
    dataset_name: str,
    config_json: str,
    started_at: str,
    total_steps: Optional[int],
    *,
    output_dir: Optional[str] = None,
    cancel_requested: bool = False,
    resumed_from_run_id: Optional[str] = None,
) -> None:
    conn = get_connection()
    try:
        conn.execute(
            """
            INSERT INTO training_runs (
                id, model_name, dataset_name, config_json, started_at, total_steps,
                output_dir, resume_blocked, resumed_from_run_id
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                id,
                model_name,
                dataset_name,
                config_json,
                started_at,
                total_steps,
                None if cancel_requested else output_dir,
                int(cancel_requested),
                resumed_from_run_id,
            ),
        )
        if resumed_from_run_id:
            claimed = conn.execute(
                """
                UPDATE training_runs SET resume_blocked = 1
                WHERE id = ? AND status IN ('stopped', 'error')
                  AND output_dir = ? AND resume_blocked = 0
                """,
                (resumed_from_run_id, output_dir),
            )
            if claimed.rowcount != 1:
                raise RuntimeError("Resume source is no longer available")
        conn.commit()
    finally:
        conn.close()


def update_run_total_steps(id: str, total_steps: int) -> None:
    conn = get_connection()
    try:
        conn.execute(
            "UPDATE training_runs SET total_steps = ? WHERE id = ?",
            (total_steps, id),
        )
        conn.commit()
    finally:
        conn.close()


def update_run_progress(
    id: str, step: int, loss: Optional[float], duration_seconds: Optional[float]
) -> None:
    conn = get_connection()
    try:
        conn.execute(
            "UPDATE training_runs SET final_step = ?, final_loss = ?, duration_seconds = ? WHERE id = ?",
            (step, loss, duration_seconds, id),
        )
        conn.commit()
    finally:
        conn.close()


def finish_run(
    id: str,
    status: str,
    ended_at: str,
    final_step: Optional[int],
    final_loss: Optional[float],
    duration_seconds: Optional[float],
    loss_sparkline: Optional[str] = None,
    output_dir: Optional[str] = None,
    error_message: Optional[str] = None,
    clear_output_dir: bool = False,
    resume_blocked: bool = False,
    config_json: Optional[str] = None,
) -> None:
    conn = get_connection()
    try:
        conn.execute(
            """
            UPDATE training_runs
            SET status = ?, ended_at = ?, final_step = ?, final_loss = ?,
                duration_seconds = ?, loss_sparkline = ?,
                config_json = COALESCE(?, config_json),
                output_dir = CASE
                    WHEN resume_blocked = 1 OR ? = 1 THEN NULL
                    WHEN ? IS NOT NULL THEN ?
                    WHEN ? IN ('error', 'stopped') THEN output_dir
                    ELSE NULL
                END,
                error_message = ?,
                resume_blocked = CASE WHEN resume_blocked = 1 OR ? = 1 THEN 1 ELSE ? END
            WHERE id = ? AND status = 'running'
            """,
            (
                status,
                ended_at,
                final_step,
                final_loss,
                duration_seconds,
                loss_sparkline,
                config_json,
                int(clear_output_dir),
                output_dir,
                output_dir,
                status,
                error_message,
                int(clear_output_dir),
                int(resume_blocked),
                id,
            ),
        )
        conn.commit()
    finally:
        conn.close()


_INSERT_METRICS_SQL = """
    INSERT INTO training_metrics
        (run_id, step, loss, learning_rate, grad_norm, eval_loss, epoch, num_tokens, elapsed_seconds)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(run_id, step) DO UPDATE SET
        loss = COALESCE(excluded.loss, loss),
        learning_rate = COALESCE(excluded.learning_rate, learning_rate),
        grad_norm = COALESCE(excluded.grad_norm, grad_norm),
        eval_loss = COALESCE(excluded.eval_loss, eval_loss),
        epoch = COALESCE(excluded.epoch, epoch),
        num_tokens = COALESCE(excluded.num_tokens, num_tokens),
        elapsed_seconds = COALESCE(excluded.elapsed_seconds, elapsed_seconds)
"""


def _insert_metrics_rows(conn: sqlite3.Connection, run_id: str, metrics: list[dict]) -> None:
    conn.executemany(
        _INSERT_METRICS_SQL,
        [
            (
                run_id,
                m.get("step"),
                m.get("loss"),
                m.get("learning_rate"),
                m.get("grad_norm"),
                m.get("eval_loss"),
                m.get("epoch"),
                m.get("num_tokens"),
                m.get("elapsed_seconds"),
            )
            for m in metrics
        ],
    )


def insert_metrics_batch(run_id: str, metrics: list[dict]) -> None:
    if not metrics:
        return
    conn = get_connection()
    try:
        _insert_metrics_rows(conn, run_id, metrics)
        conn.commit()
    finally:
        conn.close()


def insert_metrics_with_progress(
    run_id: str,
    metrics: list[dict],
    *,
    step: int,
    loss: Optional[float],
    duration_seconds: Optional[float],
) -> None:
    """insert_metrics_batch + update_run_progress in one commit: a failure writes neither."""
    conn = get_connection()
    try:
        if metrics:
            _insert_metrics_rows(conn, run_id, metrics)
        conn.execute(
            "UPDATE training_runs SET final_step = ?, final_loss = ?, duration_seconds = ? WHERE id = ?",
            (step, loss, duration_seconds, run_id),
        )
        conn.commit()
    finally:
        conn.close()


def update_run_display_name(id: str, display_name: Optional[str]) -> None:
    conn = get_connection()
    try:
        conn.execute(
            "UPDATE training_runs SET display_name = ? WHERE id = ?",
            (display_name, id),
        )
        conn.commit()
    finally:
        conn.close()


def update_run_output_dir(id: str, output_dir: Optional[str]) -> None:
    conn = get_connection()
    try:
        conn.execute(
            """
            UPDATE training_runs SET output_dir = ?
            WHERE id = ? AND status = 'running' AND resume_blocked = 0
            """,
            (output_dir, id),
        )
        conn.commit()
    finally:
        conn.close()


def update_run_config_json(id: str, config_json: str) -> bool:
    conn = get_connection()
    try:
        cursor = conn.execute(
            """
            UPDATE training_runs SET config_json = ?
            WHERE id = ? AND status = 'running'
            """,
            (config_json, id),
        )
        conn.commit()
        return cursor.rowcount == 1
    finally:
        conn.close()


def mark_run_cancel_requested(id: str) -> bool:
    conn = get_connection()
    try:
        cursor = conn.execute(
            """
            UPDATE training_runs SET output_dir = NULL, resume_blocked = 1
            WHERE id = ? AND status = 'running'
            """,
            (id,),
        )
        conn.commit()
        return cursor.rowcount > 0
    finally:
        conn.close()


def list_runs(limit: int = 50, offset: int = 0) -> dict:
    conn = get_connection()
    try:
        total = conn.execute("SELECT COUNT(*) FROM training_runs").fetchone()[0]
        rows = conn.execute(
            """
            SELECT r.id, r.status, r.model_name, r.dataset_name, r.started_at,
                   r.ended_at, r.total_steps, r.final_step, r.final_loss,
                   r.output_dir, r.duration_seconds, r.error_message,
                   r.loss_sparkline, r.display_name, r.config_json, r.resume_blocked,
                   CASE
                       WHEN r.status IN ('stopped', 'error')
                            AND r.output_dir IS NOT NULL
                            AND EXISTS (
                                SELECT 1
                                FROM training_runs newer
                                WHERE newer.output_dir = r.output_dir
                                  AND newer.status IN ('stopped', 'completed', 'error', 'running')
                                  AND newer.started_at > r.started_at
                            )
                       THEN 1 ELSE 0
                   END AS resumed_later
            FROM training_runs r
            ORDER BY started_at DESC
            LIMIT ? OFFSET ?
            """,
            (limit, offset),
        ).fetchall()
        runs = []
        for row in rows:
            run = dict(row)
            run["project_name"] = _extract_project_name_from_config_json(run.get("config_json"))
            sparkline = run.get("loss_sparkline")
            if sparkline:
                try:
                    run["loss_sparkline"] = json.loads(sparkline)
                except (json.JSONDecodeError, TypeError):
                    logger.debug("Failed to parse loss_sparkline for run %s", run.get("id"))
                    run["loss_sparkline"] = None
            runs.append(run)
        return {"runs": runs, "total": total}
    finally:
        conn.close()


def get_run(id: str) -> Optional[dict]:
    conn = get_connection()
    try:
        row = conn.execute(
            """
            SELECT r.*,
                   CASE
                       WHEN r.status IN ('stopped', 'error')
                            AND r.output_dir IS NOT NULL
                            AND EXISTS (
                                SELECT 1
                                FROM training_runs newer
                                WHERE newer.output_dir = r.output_dir
                                  AND newer.status IN ('stopped', 'completed', 'error', 'running')
                                  AND newer.started_at > r.started_at
                            )
                       THEN 1 ELSE 0
                   END AS resumed_later
            FROM training_runs r
            WHERE r.id = ?
            """,
            (id,),
        ).fetchone()
        if row is None:
            return None
        run = dict(row)
        run["project_name"] = _extract_project_name_from_config_json(run.get("config_json"))
        sparkline = run.get("loss_sparkline")
        if sparkline:
            try:
                run["loss_sparkline"] = json.loads(sparkline)
            except (json.JSONDecodeError, TypeError):
                logger.debug("Failed to parse loss_sparkline for run %s", id)
                run["loss_sparkline"] = None
        return run
    finally:
        conn.close()


def get_resumable_run_by_output_dir(output_dir: str) -> Optional[dict]:
    conn = get_connection()
    try:
        row = conn.execute(
            """
            SELECT r.*,
                   0 AS resumed_later
            FROM training_runs r
            WHERE r.output_dir = ?
              AND r.status IN ('stopped', 'error')
              AND NOT EXISTS (
                  SELECT 1
                  FROM training_runs newer
                  WHERE newer.output_dir = r.output_dir
                    AND newer.status IN ('stopped', 'completed', 'error', 'running')
                    AND newer.started_at > r.started_at
              )
            ORDER BY r.started_at DESC
            LIMIT 1
            """,
            (output_dir,),
        ).fetchone()
        if row is None:
            return None
        run = dict(row)
        sparkline = run.get("loss_sparkline")
        if sparkline:
            try:
                run["loss_sparkline"] = json.loads(sparkline)
            except (json.JSONDecodeError, TypeError):
                logger.debug("Failed to parse loss_sparkline for output_dir %s", output_dir)
                run["loss_sparkline"] = None
        return run
    finally:
        conn.close()


def get_run_metrics(id: str) -> dict:
    conn = get_connection()
    try:
        rows = conn.execute(
            """
            SELECT step, loss, learning_rate, grad_norm, eval_loss, epoch,
                   num_tokens, elapsed_seconds
            FROM training_metrics
            WHERE run_id = ?
            ORDER BY step
            """,
            (id,),
        ).fetchall()

        step_history: list[int] = []
        loss_history: list[float] = []
        loss_step_history: list[int] = []
        lr_history: list[float] = []
        lr_step_history: list[int] = []
        grad_norm_history: list[float] = []
        grad_norm_step_history: list[int] = []
        eval_loss_history: list[float] = []
        eval_step_history: list[int] = []
        final_epoch: float | None = None
        final_num_tokens: int | None = None

        for row in rows:
            step = row["step"]
            step_history.append(step)
            if step > 0 and row["loss"] is not None:
                loss_history.append(row["loss"])
                loss_step_history.append(step)
            if step > 0 and row["learning_rate"] is not None:
                lr_history.append(row["learning_rate"])
                lr_step_history.append(step)
            if step > 0 and row["grad_norm"] is not None:
                grad_norm_history.append(row["grad_norm"])
                grad_norm_step_history.append(step)
            if step > 0 and row["eval_loss"] is not None:
                eval_loss_history.append(row["eval_loss"])
                eval_step_history.append(step)
            if row["epoch"] is not None:
                final_epoch = row["epoch"]
            if row["num_tokens"] is not None:
                final_num_tokens = row["num_tokens"]

        return {
            "step_history": step_history,
            "loss_history": loss_history,
            "loss_step_history": loss_step_history,
            "lr_history": lr_history,
            "lr_step_history": lr_step_history,
            "grad_norm_history": grad_norm_history,
            "grad_norm_step_history": grad_norm_step_history,
            "eval_loss_history": eval_loss_history,
            "eval_step_history": eval_step_history,
            "final_epoch": final_epoch,
            "final_num_tokens": final_num_tokens,
        }
    finally:
        conn.close()


def delete_run(id: str) -> None:
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        source = conn.execute(
            "SELECT resumed_from_run_id FROM training_runs WHERE id = ?",
            (id,),
        ).fetchone()
        if source is not None:
            # Keep a resume chain connected: the tail carries cumulative counters profile totals trace.
            conn.execute(
                """
                UPDATE training_runs
                SET resumed_from_run_id = ?
                WHERE resumed_from_run_id = ?
                """,
                (source["resumed_from_run_id"], id),
            )
        conn.execute("DELETE FROM training_runs WHERE id = ?", (id,))
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def list_other_run_output_dirs(exclude_id: str) -> list[str]:
    conn = get_connection()
    try:
        rows = conn.execute(
            "SELECT output_dir FROM training_runs WHERE output_dir IS NOT NULL AND id != ?",
            (exclude_id,),
        ).fetchall()
        return [str(row[0]) for row in rows]
    finally:
        conn.close()


def cleanup_orphaned_runs() -> None:
    """Mark any 'running' rows as errored on startup (server restarted mid-training)."""
    conn = get_connection()
    try:
        conn.execute(
            """
            UPDATE training_runs
            SET status = CASE WHEN resume_blocked = 1 THEN 'stopped' ELSE 'error' END,
                error_message = CASE
                    WHEN resume_blocked = 1 THEN NULL
                    ELSE 'Server restarted during training'
                END,
                output_dir = CASE WHEN resume_blocked = 1 THEN NULL ELSE output_dir END,
                ended_at = ?
            WHERE status = 'running'
            """,
            (datetime.now(timezone.utc).isoformat(),),
        )
        conn.commit()
    finally:
        conn.close()


def list_scan_folders() -> list[dict]:
    conn = get_connection()
    try:
        rows = conn.execute(
            "SELECT id, path, created_at, recursive FROM scan_folders ORDER BY created_at"
        ).fetchall()
        return [{**dict(row), "recursive": bool(row["recursive"])} for row in rows]
    finally:
        conn.close()


def add_scan_folder_with_status(path: str, recursive: bool | None = None) -> tuple[dict, bool]:
    if not path or not path.strip():
        raise ValueError("Path cannot be empty")
    normalized = os.path.realpath(os.path.expanduser(path.strip()))

    if not os.path.exists(normalized):
        raise ValueError("Path does not exist")
    if not os.path.isdir(normalized):
        raise ValueError("Path must be a directory, not a file")
    # Reject a local filesystem root (it would seed the browse allowlist); UNC share roots are fine.
    # Mirrors scan_folders.py.
    if is_local_filesystem_root(normalized):
        raise ValueError("The filesystem root cannot be registered")
    if _contains_sensitive_path_component(normalized):
        raise ValueError("Credential or configuration directories are not allowed")
    # The folder joins the browse allowlist and model index, so it must be the account's own.
    from utils.paths.storage_roots import within_account

    if not within_account(Path(normalized)):
        raise ValueError("Path is outside this account's workspace")

    # Windows: normcase for the denylist check but store original casing (e.g. C:\Models).
    is_win = platform.system() == "Windows"
    check = os.path.normcase(normalized) if is_win else normalized
    for prefix in _denied_path_prefixes():
        if check == prefix or check.startswith(prefix + os.sep):
            if prefix == "/run" and is_linux_run_media_path(check):
                continue
            raise ValueError(f"Path under {prefix} is not allowed")

    # Last, so a denied path is never opened; os.access misses macOS TCC and Windows ACLs.
    if not is_readable_dir(normalized):
        raise ValueError("Path is not readable")

    conn = get_connection()
    try:
        now = datetime.now(timezone.utc).isoformat()
        # Windows: case-insensitive lookup so C:\Models and c:\models dedup.
        if is_win:
            existing = conn.execute(
                "SELECT id, path, created_at, recursive FROM scan_folders WHERE path = ? COLLATE NOCASE",
                (normalized,),
            ).fetchone()
        else:
            existing = conn.execute(
                "SELECT id, path, created_at, recursive FROM scan_folders WHERE path = ?",
                (normalized,),
            ).fetchone()
        if existing is not None:
            # None keeps the stored flag, so a re-add from export registration never resets it.
            if recursive is None or bool(existing["recursive"]) == recursive:
                return dict(existing), False
            conn.execute(
                "UPDATE scan_folders SET recursive = ? WHERE id = ?",
                (int(recursive), existing["id"]),
            )
            conn.commit()
            return {**dict(existing), "recursive": int(recursive)}, True
        inserted = False
        try:
            conn.execute(
                "INSERT INTO scan_folders (path, created_at, recursive) VALUES (?, ?, ?)",
                (normalized, now, int(bool(recursive))),
            )
            conn.commit()
            inserted = True
        except sqlite3.IntegrityError:
            pass  # duplicate; fall through to SELECT
        # Same collation as the pre-check to catch concurrent writes (Windows).
        fallback_sql = (
            "SELECT id, path, created_at, recursive FROM scan_folders WHERE path = ? COLLATE NOCASE"
            if is_win
            else "SELECT id, path, created_at, recursive FROM scan_folders WHERE path = ?"
        )
        row = conn.execute(fallback_sql, (normalized,)).fetchone()
        if row is None:
            raise ValueError("Folder was concurrently removed")
        return dict(row), inserted
    finally:
        conn.close()


def add_scan_folder(path: str, recursive: bool | None = None) -> dict:
    row, _ = add_scan_folder_with_status(path, recursive)
    return row


def remove_scan_folder(id: int) -> bool:
    # sqlite INTEGER is signed 64-bit: binding a wider id raises instead of matching nothing.
    if not -(2**63) <= id < 2**63:
        return False
    conn = get_connection()
    try:
        cursor = conn.execute("DELETE FROM scan_folders WHERE id = ?", (id,))
        conn.commit()
        return cursor.rowcount > 0
    finally:
        conn.close()


def _json_loads(value: str | None, fallback):
    if value is None:
        return fallback
    try:
        return json.loads(value)
    except (json.JSONDecodeError, TypeError):
        return fallback


def _chat_thread_from_row(row: sqlite3.Row, include_settings: bool = True) -> dict:
    data = dict(row)
    thread = {
        "id": data["id"],
        "title": data["title"],
        "modelType": data["model_type"],
        "modelId": data.get("model_id") or "",
        "modelGgufVariant": data.get("model_gguf_variant") or None,
        "pairId": data.get("pair_id") or None,
        "projectId": data.get("project_id") or None,
        "archived": bool(data["archived"]),
        "createdAt": data["created_at"],
        "updatedAt": data.get("updated_at")
        if data.get("updated_at") is not None
        else data["created_at"],
        "openaiCodeExecContainerId": data.get("openai_code_exec_container_id"),
        "anthropicCodeExecContainerId": data.get("anthropic_code_exec_container_id"),
        "forkedFromThreadId": data.get("forked_from_thread_id"),
        "forkedFromMessageId": data.get("forked_from_message_id"),
        "forkBoundaryMessageId": data.get("fork_boundary_message_id"),
        "forkTitleBase": data.get("fork_title_base"),
        "modifiedAt": data.get("modified_at"),
        "scheduledTaskId": data.get("scheduled_task_id") or None,
    }
    if include_settings:
        thread["settings"] = _json_loads(data.get("settings_json"), None)
    return thread


def _chat_project_from_row(row: sqlite3.Row) -> dict:
    data = dict(row)
    root_path = data.get("root_path")
    return {
        "id": data["id"],
        "name": data["name"],
        "instructions": data.get("instructions") or "",
        "rootPath": root_path or None,
        "sandboxPath": os.path.join(root_path, "sandbox") if root_path else None,
        "workingFolder": data.get("working_folder") or None,
        "workingFolderIdentity": data.get("working_folder_identity") or None,
        "archived": bool(data["archived"]),
        "createdAt": data["created_at"],
        "updatedAt": data["updated_at"],
    }


def _chat_message_from_row(row: sqlite3.Row) -> dict:
    data = dict(row)
    message = {
        "id": data["id"],
        "threadId": data["thread_id"],
        "parentId": data.get("parent_id"),
        "role": data["role"],
        "content": _json_loads(data.get("content_json"), []),
        "createdAt": data["created_at"],
    }
    attachments = _json_loads(data.get("attachments_json"), None)
    metadata = _json_loads(data.get("metadata_json"), None)
    if attachments is not None:
        message["attachments"] = attachments
    if metadata is not None:
        message["metadata"] = metadata
    return message


class ChatThreadDeletedError(RuntimeError):
    """Raised when a stale writer tries to recreate a deleted thread id."""


def _raise_if_chat_thread_deleted(conn: sqlite3.Connection, thread_id: str) -> None:
    row = conn.execute(
        "SELECT 1 FROM chat_thread_tombstones WHERE id = ?",
        (thread_id,),
    ).fetchone()
    if row is not None:
        raise ChatThreadDeletedError(thread_id)


def upsert_chat_thread(
    thread: dict, *, external_import_session: Optional[tuple[str, str]] = None
) -> dict:
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        conn.execute("BEGIN IMMEDIATE")
        _raise_if_chat_thread_deleted(conn, thread["id"])
        if external_import_session is not None:
            conn.execute(
                "DELETE FROM external_import_sessions WHERE source = ? AND session_id = ? "
                "AND NOT EXISTS (SELECT 1 FROM chat_threads WHERE id = ?)",
                (*external_import_session, thread["id"]),
            )
        conn.execute(
            """
            INSERT INTO chat_threads
                (id, title, model_type, model_id, model_gguf_variant, pair_id, project_id, archived, created_at, updated_at, openai_code_exec_container_id, anthropic_code_exec_container_id, forked_from_thread_id, forked_from_message_id, fork_boundary_message_id, fork_title_base, settings_json, scheduled_task_id)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                title = excluded.title,
                model_type = excluded.model_type,
                model_gguf_variant = CASE
                    WHEN excluded.model_id = chat_threads.model_id
                    THEN COALESCE(excluded.model_gguf_variant, chat_threads.model_gguf_variant)
                    ELSE excluded.model_gguf_variant
                END,
                model_id = excluded.model_id,
                pair_id = excluded.pair_id,
                project_id = excluded.project_id,
                archived = excluded.archived,
                created_at = excluded.created_at,
                updated_at = COALESCE(excluded.updated_at, chat_threads.updated_at),
                openai_code_exec_container_id = excluded.openai_code_exec_container_id,
                anthropic_code_exec_container_id = excluded.anthropic_code_exec_container_id,
                forked_from_thread_id = excluded.forked_from_thread_id,
                forked_from_message_id = excluded.forked_from_message_id,
                -- fork_boundary_message_id is deliberately absent. It is set once, on the insert
                -- that makes the fork, and moved only by the prune that deletes the row it names.
                -- A whole-record writer carries whatever it read, which may predate that move, so
                -- on conflict the stored anchor always wins.
                -- Server-managed on an existing row, like the anchor above: the payload never
                -- contributes, since a writer carries whatever it read and a base it saw can
                -- already have been cleared by a rename it did not see. The title decides alone.
                -- A new one drops the base; the same one keeps whatever is stored.
                fork_title_base = CASE
                    WHEN excluded.title = chat_threads.title
                    THEN chat_threads.fork_title_base
                    ELSE NULL
                END,
                -- an absent snapshot keeps the stored one: most writers rebuild the record without it.
                settings_json = COALESCE(excluded.settings_json, chat_threads.settings_json),
                -- Set by the scheduler when it makes the chat; no later writer changes it.
                scheduled_task_id = COALESCE(chat_threads.scheduled_task_id, excluded.scheduled_task_id)
            """,
            (
                thread["id"],
                thread.get("title") or "New Chat",
                thread["modelType"],
                thread.get("modelId") or "",
                thread.get("modelGgufVariant"),
                thread.get("pairId"),
                thread.get("projectId"),
                1 if thread.get("archived") else 0,
                int(thread["createdAt"]),
                int(thread["updatedAt"]) if thread.get("updatedAt") is not None else None,
                thread.get("openaiCodeExecContainerId"),
                thread.get("anthropicCodeExecContainerId"),
                thread.get("forkedFromThreadId"),
                thread.get("forkedFromMessageId"),
                thread.get("forkBoundaryMessageId"),
                thread.get("forkTitleBase"),
                json.dumps(thread["settings"]) if thread.get("settings") is not None else None,
                thread.get("scheduledTaskId"),
            ),
        )
        conn.commit()
        return get_chat_thread(thread["id"]) or thread
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


class ChatThreadPreconditionFailed(Exception):
    """The opening message is picked in the same order as the title migration. Watermarks kept per thread, one per tab that has written settings."""


_MAX_SETTINGS_WRITERS = 32

_OPENING_USER_MESSAGE = """(
    SELECT id FROM chat_messages
    WHERE thread_id = ? AND role = 'user'
    ORDER BY created_at ASC, id ASC LIMIT 1
) IS ?"""


def update_chat_thread(
    id: str,
    patch: dict,
    expected_title: Optional[str] = None,
    expected_opening_message_id: Optional[str] = None,
    settings_write: Optional[dict] = None,
    preserve_title_modified_at: bool = False,
) -> Optional[dict]:
    """Writes only while expected_title still matches and expected_opening_message_id still exists."""
    allowed = {
        "title": ("title", patch.get("title")),
        "modelType": ("model_type", patch.get("modelType")),
        "modelId": ("model_id", patch.get("modelId")),
        "modelGgufVariant": ("model_gguf_variant", patch.get("modelGgufVariant")),
        "pairId": ("pair_id", patch.get("pairId")),
        "projectId": ("project_id", patch.get("projectId")),
        "archived": ("archived", 1 if patch.get("archived") else 0),
        "createdAt": ("created_at", patch.get("createdAt")),
        "updatedAt": ("updated_at", patch.get("updatedAt")),
        "openaiCodeExecContainerId": (
            "openai_code_exec_container_id",
            patch.get("openaiCodeExecContainerId"),
        ),
        "anthropicCodeExecContainerId": (
            "anthropic_code_exec_container_id",
            patch.get("anthropicCodeExecContainerId"),
        ),
        "forkedFromThreadId": (
            "forked_from_thread_id",
            patch.get("forkedFromThreadId"),
        ),
        "forkedFromMessageId": (
            "forked_from_message_id",
            patch.get("forkedFromMessageId"),
        ),
        "settings": (
            "settings_json",
            json.dumps(patch["settings"]) if patch.get("settings") is not None else None,
        ),
    }
    assignments = []
    values = []
    for key, (column, value) in allowed.items():
        if key in patch:
            assignments.append(f"{column} = ?")
            values.append(value)
    # Clear the fork base only on a real rename: a pair patches every thread, and re-sending a thread its own title would cost its next fork a number.
    if "title" in patch:
        assignments.append(
            "fork_title_base = CASE WHEN title = ? THEN fork_title_base ELSE NULL END"
        )
        values.append(patch.get("title"))
    # Generated title refreshes preserve recency. Moves and (un)archives still stamp it.
    # The CASE reads the pre-update row.
    edited = [
        (column, value)
        for key, (column, value) in allowed.items()
        if key in patch
        and key in ("title", "projectId", "archived")
        and not (key == "title" and preserve_title_modified_at)
    ]
    if edited:
        changed = " OR ".join(f"{column} IS NOT ?" for column, _ in edited)
        assignments.append(f"modified_at = CASE WHEN {changed} THEN ? ELSE modified_at END")
        values.extend(value for _, value in edited)
        values.append(int(time.time() * 1000))
    if not assignments and settings_write is None:
        return get_chat_thread(id)

    conn = get_connection()
    try:
        # Same transaction as the guarded write, or a 409 leaves the settings change committed.
        conn.execute("BEGIN IMMEDIATE")
        # Guards ride in the WHERE clause, so check and write are one statement.
        where = ["id = ?"]
        guard: list = [id]
        if expected_title is not None:
            where.append("title = ?")
            guard.append(expected_title)
        if expected_opening_message_id is not None:
            where.append(_OPENING_USER_MESSAGE)
            guard += [id, expected_opening_message_id]
        guarded = expected_title is not None or expected_opening_message_id is not None
        applied = 1
        if assignments:
            cursor = conn.execute(
                f"UPDATE chat_threads SET {', '.join(assignments)} WHERE {' AND '.join(where)}",
                (*values, *guard),
            )
            applied = cursor.rowcount
        if guarded and applied == 0:
            conn.rollback()
            if conn.execute("SELECT 1 FROM chat_threads WHERE id = ?", (id,)).fetchone() is None:
                return None
            raise ChatThreadPreconditionFailed(id)
        if settings_write is not None:
            if _write_chat_thread_settings_in_conn(conn, id, **settings_write) is None:
                conn.rollback()
                return None
        conn.commit()
        row = conn.execute("SELECT * FROM chat_threads WHERE id = ?", (id,)).fetchone()
        if row is None:
            return None
        return _chat_thread_from_row(row)
    finally:
        conn.close()


def _write_chat_thread_settings_in_conn(
    conn,
    id: str,
    *,
    replace: Optional[dict] = None,
    merge: Optional[dict] = None,
    clear: bool = False,
    seq: Optional[int] = None,
    writer: Optional[str] = None,
    keep_unreadable = None,
) -> Optional[bool]:
    """None if the row is missing, False if an older same-writer write is refused; never roll back metadata on False, or a rename already acknowledged is lost."""
    row = conn.execute(
        "SELECT settings_json, settings_seqs FROM chat_threads WHERE id = ?",
        (id,),
    ).fetchone()
    if row is None:
        return None
    seqs = _json_loads(row["settings_seqs"], None) if "settings_seqs" in row.keys() else None
    seqs = seqs if isinstance(seqs, dict) else {}
    if seq is not None and writer is not None:
        seen = seqs.get(writer)
        if isinstance(seen, int) and seq <= seen:
            # Older than the stored snapshot is a straggler; per-writer watermark survives other tabs' writes.
            return False
        # Re-inserted to keep LRU order.
        seqs.pop(writer, None)
        seqs[writer] = seq
        # Evict by last use, never by counter: each session's counter starts at 1.
        while len(seqs) > _MAX_SETTINGS_WRITERS:
            seqs.pop(next(iter(seqs)))
    stored = _json_loads(row["settings_json"], None)
    stored = stored if isinstance(stored, dict) else {}
    if clear:
        settings_json = None
    else:
        if merge is not None:
            base = stored
            changes = merge
        else:
            base = keep_unreadable(stored) if keep_unreadable else {}
            changes = replace or {}
        settings_json = json.dumps({**base, **changes})
    conn.execute(
        "UPDATE chat_threads SET settings_json = ?, settings_seqs = ? WHERE id = ?",
        (settings_json, json.dumps(seqs) if seqs else None, id),
    )
    return True


def write_chat_thread_settings(
    id: str,
    *,
    replace: Optional[dict] = None,
    merge: Optional[dict] = None,
    clear: bool = False,
    seq: Optional[int] = None,
    writer: Optional[str] = None,
    keep_unreadable = None,
) -> Optional[dict]:
    """Read and merge in one BEGIN IMMEDIATE so two requests cannot both replace from one stale snapshot; keep_unreadable parts carry forward, not deleted."""
    conn = get_connection()
    try:
        # IMMEDIATE takes the write lock up front, so the read below cannot be overtaken.
        conn.execute("BEGIN IMMEDIATE")
        applied = _write_chat_thread_settings_in_conn(
            conn,
            id,
            replace = replace,
            merge = merge,
            clear = clear,
            seq = seq,
            writer = writer,
            keep_unreadable = keep_unreadable,
        )
        if applied is None:
            conn.rollback()
            return None
        conn.commit()
        row = conn.execute("SELECT * FROM chat_threads WHERE id = ?", (id,)).fetchone()
        return _chat_thread_from_row(row) if row is not None else None
    finally:
        conn.close()


def get_chat_thread(id: str) -> Optional[dict]:
    conn = get_connection()
    try:
        row = conn.execute("SELECT * FROM chat_threads WHERE id = ?", (id,)).fetchone()
        return _chat_thread_from_row(row) if row is not None else None
    finally:
        conn.close()


def list_chat_threads(
    model_type: str | None = None,
    pair_id: str | None = None,
    project_id: str | None = None,
    include_archived: bool = True,
) -> list[dict]:
    clauses = []
    values: list[object] = []
    if model_type is not None:
        clauses.append("model_type = ?")
        values.append(model_type)
    if pair_id is not None:
        clauses.append("pair_id = ?")
        values.append(pair_id)
    if project_id is not None:
        clauses.append("project_id = ?")
        values.append(project_id)
    if not include_archived:
        clauses.append("archived = 0")
    where = f"WHERE {' AND '.join(clauses)}" if clauses else ""
    conn = get_connection()
    try:
        rows = conn.execute(
            f"SELECT * FROM chat_threads {where} "
            "ORDER BY COALESCE(updated_at, created_at) DESC, created_at DESC",
            values,
        ).fetchall()
        # the snapshot is only read when a thread is opened, so it is left out of the listing.
        return [_chat_thread_from_row(row, include_settings = False) for row in rows]
    finally:
        conn.close()


SIDEBAR_ROWS_CURSOR_VERSION = 2
SIDEBAR_ROWS_MAX_LIMIT = 200

_SIDEBAR_ROW_ID_SQL = "COALESCE(NULLIF(pair_id, ''), id)"
_SIDEBAR_ACTIVITY_SQL = "COALESCE(updated_at, created_at)"


class SidebarCursorError(ValueError):
    """A cursor this listing did not issue, or one issued for another sort."""


def encode_sidebar_cursor(payload: dict) -> str:
    raw = json.dumps({"v": SIDEBAR_ROWS_CURSOR_VERSION, **payload}, separators = (",", ":")).encode()
    return base64.urlsafe_b64encode(raw).decode().rstrip("=")


def decode_sidebar_cursor(cursor: str, sort: str) -> dict:
    try:
        padded = cursor + "=" * (-len(cursor) % 4)
        payload = json.loads(base64.urlsafe_b64decode(padded.encode()).decode())
    except (binascii.Error, UnicodeError, ValueError) as exc:
        raise SidebarCursorError("Malformed cursor") from exc
    if not isinstance(payload, dict) or payload.get("v") != SIDEBAR_ROWS_CURSOR_VERSION:
        raise SidebarCursorError("Unsupported cursor version")
    if payload.get("s") != sort:
        raise SidebarCursorError("Cursor was issued for another sort")
    keys = ("o",) if sort == "manual" else ("u", "c", "r")
    # SQLite binds 64-bit integers only; the exclusive lower bound keeps `-r` in range too
    if not all(type(v) is int and -(2**63) < v < 2**63 for v in map(payload.get, keys)):
        raise SidebarCursorError("Malformed cursor")
    if sort == "manual" and payload["o"] < 0:
        raise SidebarCursorError("Malformed cursor")
    return payload


def list_chat_sidebar_rows(
    *,
    archived: bool = False,
    project_id: str | None = None,
    unfiled_only: bool = False,
    include_ids: list[str] | None = None,
    exclude_ids: list[str] | None = None,
    sort: str = "updated",
    manual_order: list[str] | None = None,
    limit: int = 20,
    cursor: str | None = None,
) -> dict:
    """One page of sidebar rows, grouped (before the limit) and ordered as the frontend's groupThreads
    does. `updated` pages by keyset; `manual` follows applyManualOrder and pages by offset."""
    position = decode_sidebar_cursor(cursor, sort) if cursor else None

    filters: list[str] = []
    filter_values: list[object] = []
    if include_ids is not None:
        # a row id, or any member thread id: a running compare pane names its pair by its own id. Only a
        # pane on this side of the archive filter counts, as the full list filters panes before matching
        filters.append(
            "(rows.row_id IN (SELECT value FROM json_each(?)) OR rows.row_id IN ("
            f"SELECT {_SIDEBAR_ROW_ID_SQL} FROM chat_threads WHERE id IN (SELECT value FROM json_each(?))"
            " AND (COALESCE(archived, 0) <> 0) = ?))"
        )
        filter_values += [json.dumps(include_ids), json.dumps(include_ids), 1 if archived else 0]
    if exclude_ids:
        filters.append("rows.row_id NOT IN (SELECT value FROM json_each(?))")
        filter_values.append(json.dumps(exclude_ids))
    if project_id is not None:
        filters.append("rows.project_id = ?")
        filter_values.append(project_id)
    if unfiled_only:
        filters.append("rows.project_id IS NULL")

    # Last occurrence wins, as `new Map(order.map((id, i) => [id, i]))` does.
    ranks_json = json.dumps(manual_order or []) if sort == "manual" else "[]"
    base = f"""
        WITH members AS (
            SELECT {_SIDEBAR_ROW_ID_SQL} AS row_id,
                   CASE WHEN COALESCE(pair_id, '') <> '' THEN 1 ELSE 0 END AS is_pair,
                   title,
                   NULLIF(project_id, '') AS project_id,
                   created_at,
                   {_SIDEBAR_ACTIVITY_SQL} AS activity_at,
                   NULLIF(modified_at, 0) AS modified_at,
                   CASE WHEN COALESCE(forked_from_thread_id, '') <> '' THEN 1 ELSE 0 END AS is_fork,
                   CASE WHEN COALESCE(scheduled_task_id, '') <> '' THEN 1 ELSE 0 END AS is_scheduled,
                   rowid AS member_rowid,
                   ROW_NUMBER() OVER (
                       PARTITION BY {_SIDEBAR_ROW_ID_SQL}
                       ORDER BY {_SIDEBAR_ACTIVITY_SQL} DESC, created_at DESC, rowid ASC
                   ) AS member_rank
            FROM chat_threads
            WHERE (COALESCE(archived, 0) <> 0) = ?
        ),
        rows AS (
            SELECT row_id,
                   MAX(is_pair) AS is_pair,
                   MAX(created_at) AS created_at,
                   MAX(activity_at) AS updated_at,
                   MAX(modified_at) AS modified_at,
                   MAX(CASE WHEN member_rank = 1 THEN title END) AS title,
                   MAX(CASE WHEN member_rank = 1 THEN project_id END) AS project_id,
                   MAX(CASE WHEN member_rank = 1 THEN is_fork END) AS is_fork,
                   MAX(CASE WHEN member_rank = 1 THEN is_scheduled END) AS is_scheduled,
                   -- groupThreads sorts stably, so rows tied on updatedAt keep list order: their newest
                   -- member's created_at DESC, then storage order.
                   MAX(CASE WHEN member_rank = 1 THEN created_at END) AS lead_created_at,
                   MAX(CASE WHEN member_rank = 1 THEN member_rowid END) AS lead_rowid
            FROM members
            GROUP BY row_id
        ),
        ranks AS (
            SELECT value AS row_id, MAX(CAST(key AS INTEGER)) AS manual_rank
            FROM json_each(?)
            GROUP BY value
        ),
        eligible AS (
            SELECT rows.*, ranks.manual_rank
            FROM rows LEFT JOIN ranks ON ranks.row_id = rows.row_id
            {"WHERE " + " AND ".join(filters) if filters else ""}
        )
    """
    base_values: list[object] = [1 if archived else 0, ranks_json, *filter_values]

    page_values = list(base_values)
    if sort == "updated":
        where = ""
        if position is not None:
            where = "WHERE (updated_at, lead_created_at, -lead_rowid) < (?, ?, ?)"
            page_values += [position["u"], position["c"], -position["r"]]
        page_sql = (
            f"{base} SELECT * FROM (SELECT *, COUNT(*) OVER () AS total FROM eligible) {where} "
            "ORDER BY updated_at DESC, lead_created_at DESC, lead_rowid ASC LIMIT ?"
        )
        page_values.append(limit + 1)
    else:
        offset = position["o"] if position is not None else 0
        page_sql = (
            f"{base} SELECT *, COUNT(*) OVER () AS total FROM eligible "
            "ORDER BY manual_rank IS NOT NULL, manual_rank ASC, updated_at DESC, lead_created_at DESC, lead_rowid ASC "
            "LIMIT ? OFFSET ?"
        )
        page_values += [limit + 1, offset]

    conn = get_connection()
    try:
        conn.execute("BEGIN")
        page = conn.execute(page_sql, page_values).fetchall()
        if page:
            total = page[0]["total"]
        else:
            total = conn.execute(f"{base} SELECT COUNT(*) FROM eligible", base_values).fetchone()[0]
        has_more = len(page) > limit
        page = page[:limit]
        members_by_row: dict[str, list[sqlite3.Row]] = {row["row_id"]: [] for row in page}
        if page:
            row_ids = json.dumps(list(members_by_row))
            for member in conn.execute(
                f"""
                SELECT id, {_SIDEBAR_ROW_ID_SQL} AS row_id, NULLIF(model_id, '') AS model_id, title
                FROM chat_threads
                WHERE (COALESCE(archived, 0) <> 0) = ?
                  AND (
                      pair_id IN (SELECT value FROM json_each(?))
                      OR (COALESCE(pair_id, '') = '' AND id IN (SELECT value FROM json_each(?)))
                  )
                ORDER BY {_SIDEBAR_ACTIVITY_SQL} DESC, created_at DESC, rowid ASC
                """,
                (1 if archived else 0, row_ids, row_ids),
            ):
                members_by_row[member["row_id"]].append(member)
        conn.commit()
    finally:
        conn.close()

    rows: list[dict] = []
    for row in page:
        members = members_by_row[row["row_id"]]
        model_ids: list[str] = []
        for member in members:
            if member["model_id"] and member["model_id"] not in model_ids:
                model_ids.append(member["model_id"])
        item: dict[str, Any] = {
            "id": row["row_id"],
            "type": "compare" if row["is_pair"] else "single",
            "threadIds": [member["id"] for member in members],
            "title": row["title"],
            "createdAt": row["created_at"],
            "updatedAt": row["updated_at"],
            "projectId": row["project_id"],
        }
        if row["modified_at"]:
            item["modifiedAt"] = row["modified_at"]
        if model_ids:
            item["modelIds"] = model_ids
        if not row["is_pair"]:
            item["isFork"] = bool(row["is_fork"])
            if row["is_scheduled"]:
                item["isScheduled"] = True
        else:
            item["threadTitles"] = [member["title"] or "" for member in members]
        rows.append(item)

    next_cursor = None
    if has_more and page:
        if sort == "updated":
            last = page[-1]
            next_cursor = encode_sidebar_cursor(
                {
                    "s": "updated",
                    "u": last["updated_at"],
                    "c": last["lead_created_at"],
                    "r": last["lead_rowid"],
                }
            )
        else:
            offset = (position["o"] if position is not None else 0) + len(page)
            next_cursor = encode_sidebar_cursor({"s": "manual", "o": offset})
    return {"rows": rows, "nextCursor": next_cursor, "hasMore": has_more, "total": total}


def lookup_chat_sidebar_row_ids(thread_ids: list[str]) -> dict[str, str]:
    """The sidebar row each existing thread belongs to; unknown ids are left out."""
    if not thread_ids:
        return {}
    conn = get_connection()
    try:
        rows = conn.execute(
            f"SELECT id, {_SIDEBAR_ROW_ID_SQL} AS row_id FROM chat_threads "
            "WHERE id IN (SELECT value FROM json_each(?))",
            (json.dumps(thread_ids),),
        ).fetchall()
        return {row["id"]: row["row_id"] for row in rows}
    finally:
        conn.close()


def build_chat_history_export() -> tuple[list[dict], list[dict], list[dict]]:
    conn = get_connection()
    try:
        conn.execute("BEGIN")
        thread_rows = conn.execute(
            """
            SELECT * FROM chat_threads
            ORDER BY COALESCE(updated_at, created_at) DESC, created_at DESC
            """
        ).fetchall()
        project_rows = conn.execute(
            "SELECT * FROM chat_projects ORDER BY updated_at DESC"
        ).fetchall()
        message_rows = conn.execute(
            "SELECT * FROM chat_messages ORDER BY created_at ASC, id ASC"
        ).fetchall()
        conn.commit()
        return (
            [_chat_project_from_row(row) for row in project_rows],
            [_chat_thread_from_row(row) for row in thread_rows],
            [_chat_message_from_row(row) for row in message_rows],
        )
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def _reparent_surviving_forks(conn: sqlite3.Connection, deleted_ids: set[str]) -> None:
    """Keep fork lineage connected across any thread deletion path."""
    if not deleted_ids:
        return
    rows = conn.execute("SELECT id, forked_from_thread_id FROM chat_threads").fetchall()
    sources = {row["id"]: row["forked_from_thread_id"] for row in rows}
    for row in rows:
        if row["id"] in deleted_ids or row["forked_from_thread_id"] not in deleted_ids:
            continue
        source_id = row["forked_from_thread_id"]
        seen: set[str] = set()
        while source_id in deleted_ids and source_id not in seen:
            seen.add(source_id)
            parent_id = sources.get(source_id)
            if parent_id is None:
                # Preserve the deleted root id: sibling forks use it to elect one surviving copy of shared history.
                break
            source_id = parent_id
        conn.execute(
            "UPDATE chat_threads SET forked_from_thread_id = ? WHERE id = ?",
            (source_id, row["id"]),
        )


def lift_chat_thread_tombstones(thread_ids: Iterable[str]) -> None:
    """Forget these deleted thread ids, so an import into an emptied Studio can recreate them."""
    conn = get_connection()
    try:
        conn.executemany(
            "DELETE FROM chat_thread_tombstones WHERE id = ?", [(i,) for i in set(thread_ids)]
        )
        conn.commit()
    finally:
        conn.close()


def _tombstone_chat_threads(conn: sqlite3.Connection, thread_ids: Iterable[str]) -> None:
    thread_ids = set(thread_ids)
    # Recorded apart from unpins so a queued pin never waits forever on a deleted comparison.
    pairs = {
        row["pair_id"]
        for row in conn.execute("SELECT id, pair_id FROM chat_threads WHERE pair_id IS NOT NULL")
        if row["id"] in thread_ids and row["pair_id"]
    }
    deleted_pairs = {
        pair
        for pair in pairs
        if all(
            row["id"] in thread_ids
            for row in conn.execute("SELECT id FROM chat_threads WHERE pair_id = ?", (pair,))
        )
    }
    conn.executemany(
        "INSERT OR IGNORE INTO chat_pin_deletions (id) VALUES (?)",
        [(id,) for id in thread_ids | deleted_pairs],
    )
    deleted_at = int(datetime.now(timezone.utc).timestamp() * 1000)
    conn.executemany(
        """
        INSERT INTO chat_thread_tombstones (id, deleted_at)
        VALUES (?, ?)
        ON CONFLICT(id) DO UPDATE SET deleted_at = excluded.deleted_at
        """,
        [(thread_id, deleted_at) for thread_id in sorted(set(thread_ids))],
    )


# Only these states can own a worker; signalling others leaves unused cancellation events.
_ACTIVE_RESEARCH_RUN_STATUSES = (
    "planning",
    "running",
    "cancelling",
)


def _active_research_run_ids(
    conn: sqlite3.Connection, thread_ids: set[str] | None = None
) -> list[str]:
    # Unclaimed (no lease_owner) runs never get a worker once the row is gone, so signalling them leaves a cancellation event nothing consumes.
    status_placeholders = ",".join("?" for _ in _ACTIVE_RESEARCH_RUN_STATUSES)
    if thread_ids is None:
        rows = conn.execute(
            f"""
            SELECT id, created_at FROM research_runs
            WHERE status IN ({status_placeholders}) AND lease_owner IS NOT NULL
            """,
            _ACTIVE_RESEARCH_RUN_STATUSES,
        ).fetchall()
    else:
        rows = []
        sorted_thread_ids = sorted(thread_ids)
        for start in range(0, len(sorted_thread_ids), _SQLITE_IN_CHUNK_SIZE):
            chunk = sorted_thread_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
            thread_placeholders = ",".join("?" for _ in chunk)
            rows.extend(
                conn.execute(
                    f"SELECT id, created_at FROM research_runs "
                    f"WHERE thread_id IN ({thread_placeholders}) "
                    f"AND status IN ({status_placeholders}) "
                    f"AND lease_owner IS NOT NULL",
                    (*chunk, *_ACTIVE_RESEARCH_RUN_STATUSES),
                ).fetchall()
            )
    return [row["id"] for row in sorted(rows, key = lambda row: (row["created_at"], row["id"]))]


def _active_chat_generation_run_ids(
    conn: sqlite3.Connection, thread_ids: set[str] | None = None
) -> list[str]:
    if thread_ids is None:
        rows = conn.execute(
            """SELECT id, created_at FROM chat_generation_runs
               WHERE status IN ('queued','running','cancelling')"""
        ).fetchall()
    else:
        rows = []
        sorted_thread_ids = sorted(thread_ids)
        for start in range(0, len(sorted_thread_ids), _SQLITE_IN_CHUNK_SIZE):
            chunk = sorted_thread_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
            placeholders = ",".join("?" for _ in chunk)
            rows.extend(
                conn.execute(
                    f"""SELECT id, created_at FROM chat_generation_runs
                        WHERE status IN ('queued','running','cancelling')
                          AND thread_id IN ({placeholders})""",
                    chunk,
                ).fetchall()
            )
    return [row["id"] for row in sorted(rows, key = lambda row: (row["created_at"], row["id"]))]


def delete_chat_threads_in(conn: sqlite3.Connection, ids: list[str]) -> tuple[list[str], list[str]]:
    """Delete chats in the caller's write transaction; returns the still-active research and chat run ids."""
    _ensure_chat_attachment_inventory_current(conn)
    thread_ids = set(ids)
    active_research_run_ids = _active_research_run_ids(conn, thread_ids)
    active_chat_run_ids = _active_chat_generation_run_ids(conn, thread_ids)
    _reparent_surviving_forks(conn, thread_ids)
    # Tombstone even without a row, so a late POST with the same id cannot recreate the thread.
    _tombstone_chat_threads(conn, thread_ids)
    conn.executemany(
        "DELETE FROM chat_attachment_tombstones WHERE thread_id = ?",
        [(id,) for id in ids],
    )
    conn.executemany("DELETE FROM chat_threads WHERE id = ?", [(id,) for id in ids])
    _mark_chat_attachment_inventory_clean(conn)
    return active_research_run_ids, active_chat_run_ids


def delete_chat_threads_with_active_runs(ids: list[str]) -> tuple[list[str], list[str]]:
    if not ids:
        return [], []
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        conn.execute("BEGIN IMMEDIATE")
        active = delete_chat_threads_in(conn, ids)
        _load_chat_pins_in_conn(conn, repair = True, remember_removed = True)
        conn.commit()
        return active
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def delete_chat_threads_with_active_research_runs(ids: list[str]) -> list[str]:
    research_run_ids, _chat_run_ids = delete_chat_threads_with_active_runs(ids)
    return research_run_ids


def delete_chat_threads(ids: list[str]) -> list[str]:
    return delete_chat_threads_with_active_research_runs(ids)


def unreaped_clear_operation_image_ids(operation_id: Optional[str]) -> Optional[set]:
    """Ids a recorded clear never reaped, or None if nothing is left to finish (no op, old build, or already reaped); a replay must never reap on a guess."""
    if operation_id is None:
        return None
    try:
        conn = get_connection()
        try:
            row = conn.execute(
                """
                SELECT reapable_image_ids_json, caches_cleared_at
                FROM chat_clear_operations WHERE id = ?
                """,
                (operation_id,),
            ).fetchone()
            if row is None or row["caches_cleared_at"] is not None:
                return None
            stored = row["reapable_image_ids_json"]
            if stored is None:
                return None
            return set(json.loads(stored))
        finally:
            conn.close()
    except Exception:  # noqa: BLE001 -- an unreadable ledger just means nothing to finish
        return None


def record_clear_operation_reap_scope(
    operation_id: Optional[str], image_ids: Optional[set]
) -> None:
    """Records reap scope before it runs, as its own statement so the commit stays early; None is stored as explicit absence, never replayed blindly."""
    if operation_id is None:
        return
    try:
        conn = get_connection()
        try:
            conn.execute(
                "UPDATE chat_clear_operations SET reapable_image_ids_json = ? WHERE id = ?",
                (None if image_ids is None else json.dumps(sorted(image_ids)), operation_id),
            )
            conn.commit()
        finally:
            conn.close()
    except Exception:  # noqa: BLE001 -- best effort; failing here must not fail the clear
        logger.debug("could not record the reap scope for clear %s", operation_id)


def mark_clear_operation_caches_cleared(operation_id: Optional[str]) -> None:
    """Record that the thumbnail reap for this clear finished, so a replay does not redo it."""
    if operation_id is None:
        return
    try:
        conn = get_connection()
        try:
            conn.execute(
                "UPDATE chat_clear_operations SET caches_cleared_at = ? WHERE id = ?",
                (int(datetime.now(timezone.utc).timestamp() * 1000), operation_id),
            )
            conn.commit()
        finally:
            conn.close()
    except Exception:  # noqa: BLE001 -- a missed mark costs one redundant reap on a retry
        logger.debug("could not mark the reap complete for clear %s", operation_id)


def clear_chat_history_with_active_research_runs(
    additional_thread_ids: Iterable[str] = (), operation_id: Optional[str] = None
) -> tuple[list[str], list[str]]:
    removed, active_runs = clear_chat_history(
        additional_thread_ids,
        operation_id = operation_id,
    )
    return active_runs, removed


def clear_chat_history(
    additional_thread_ids: Iterable[str] = (),
    operation_id: Optional[str] = None,
    include_chat_generation_runs: bool = False,
) -> "tuple[list[str], list[str]] | tuple[list[str], list[str], list[str]]":
    result = clear_chat_history_with_replay_status(
        additional_thread_ids,
        operation_id = operation_id,
        include_chat_generation_runs = include_chat_generation_runs,
    )
    if include_chat_generation_runs:
        removed, active_runs, active_chat_runs, _replayed = result
        return removed, active_runs, active_chat_runs
    removed, active_runs, _replayed = result
    return removed, active_runs


def clear_chat_history_with_replay_status(
    additional_thread_ids: Iterable[str] = (),
    operation_id: Optional[str] = None,
    include_chat_generation_runs: bool = False,
) -> "tuple[list[str], list[str], bool] | tuple[list[str], list[str], list[str], bool]":
    """Reads removed ids and the replayed flag in one BEGIN IMMEDIATE so a racing retry replays; the ids must match the cascade or cleanup misses threads."""
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        conn.execute("BEGIN IMMEDIATE")
        if operation_id is not None:
            completed = conn.execute(
                """
                SELECT deleted_thread_ids_json
                FROM chat_clear_operations WHERE id = ?
                """,
                (operation_id,),
            ).fetchone()
            if completed is not None:
                conn.commit()
                # Workers were already signalled; replaying can leave a stale cancellation event.
                replay = (list(json.loads(completed["deleted_thread_ids_json"])), [])
                return (*replay, [], True) if include_chat_generation_runs else (*replay, True)
        _ensure_chat_attachment_inventory_current(conn)
        removed = sorted(str(row[0]) for row in conn.execute("SELECT id FROM chat_threads"))
        status_placeholders = ",".join("?" for _ in _ACTIVE_RESEARCH_RUN_STATUSES)
        active_runs = [
            row["id"]
            for row in conn.execute(
                f"SELECT id FROM research_runs WHERE status IN ({status_placeholders}) "
                "AND lease_owner IS NOT NULL ORDER BY created_at, id",
                _ACTIVE_RESEARCH_RUN_STATUSES,
            )
        ]
        active_chat_runs = _active_chat_generation_run_ids(conn)
        # Fence pending frontend writes and legacy-only ids in the same transaction as the clear.
        _tombstone_chat_threads(conn, sorted(set(additional_thread_ids) | set(removed)))
        conn.execute("DELETE FROM chat_attachment_tombstones")
        conn.execute("DELETE FROM chat_threads")
        _load_chat_pins_in_conn(conn, repair = True, remember_removed = True)
        _mark_chat_attachment_inventory_clean(conn)
        if operation_id is not None:
            conn.execute(
                """
                INSERT INTO chat_clear_operations (
                    id, active_research_run_ids_json, deleted_thread_ids_json, cleared_at
                ) VALUES (?, ?, ?, ?)
                """,
                (
                    operation_id,
                    json.dumps(active_runs),
                    json.dumps(removed),
                    int(datetime.now(timezone.utc).timestamp() * 1000),
                ),
            )
        conn.commit()
        if include_chat_generation_runs:
            return removed, active_runs, active_chat_runs, False
        return removed, active_runs, False
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def count_chat_threads() -> int:
    conn = get_connection()
    try:
        return int(conn.execute("SELECT COUNT(*) FROM chat_threads").fetchone()[0])
    finally:
        conn.close()


def _unretire_project_rag_scope(project_id: str) -> None:
    """Gives a recreated project id back its RAG scope; re-reads the owner under the scope lock so a delete that won the race keeps its tombstone."""
    from utils.paths import rag_db_path
    try:
        if not rag_db_path().exists():
            return
        from core.rag import folder_sync, store as rag_store

        scope = rag_store.project_scope(project_id)
        with folder_sync.scope_lock(scope):
            if get_chat_project(project_id) is None:
                return
            folder_sync.unretire_scope(scope)
    except Exception:
        logger.warning(
            "could not clear RAG retirement for project %s after the Studio row committed",
            project_id,
            exc_info = True,
        )


class ProjectWorkingFolderConflictError(ValueError):
    pass


def upsert_chat_project(
    project: dict, *, working_folder: Optional[tuple[str, tuple[int, int]]] = None
) -> dict:
    existing = get_chat_project(project["id"])
    root_path = existing.get("rootPath") if existing else None
    if not root_path:
        root_path = _default_project_root(project)
    root_path = _ensure_project_workspace(root_path)
    conn = get_connection()
    try:
        if working_folder is not None:
            # Create-only: an existing project changes its folder via the working-folder route.
            conn.execute("BEGIN IMMEDIATE")
            if conn.execute(
                "SELECT 1 FROM chat_projects WHERE id = ?", (project["id"],)
            ).fetchone():
                raise ProjectWorkingFolderConflictError(
                    "This project already exists. Change its working folder from Edit project."
                )
        conn.execute(
            """
            INSERT INTO chat_projects
                (id, name, instructions, root_path, archived, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                name = excluded.name,
                instructions = excluded.instructions,
                root_path = COALESCE(chat_projects.root_path, excluded.root_path),
                archived = excluded.archived,
                created_at = excluded.created_at,
                updated_at = excluded.updated_at
            """,
            (
                project["id"],
                project["name"],
                project.get("instructions") or "",
                root_path,
                1 if project.get("archived") else 0,
                int(project["createdAt"]),
                int(project["updatedAt"]),
            ),
        )
        if working_folder is not None:
            path, (device, inode) = working_folder
            conn.execute(
                "UPDATE chat_projects SET working_folder = ?, working_folder_identity = ? WHERE id = ?",
                (path, f"{device}:{inode}", project["id"]),
            )
        conn.commit()
        saved = get_chat_project(project["id"]) or project
    finally:
        conn.close()
    _unretire_project_rag_scope(project["id"])
    return saved


def update_chat_project(id: str, patch: dict) -> Optional[dict]:
    allowed = {
        "name": ("name", patch.get("name")),
        "instructions": ("instructions", patch.get("instructions")),
        "archived": ("archived", 1 if patch.get("archived") else 0),
        "createdAt": ("created_at", patch.get("createdAt")),
        "updatedAt": ("updated_at", patch.get("updatedAt")),
    }
    assignments = []
    values = []
    for key, (column, value) in allowed.items():
        if key in patch:
            assignments.append(f"{column} = ?")
            values.append(value)
    if not assignments:
        return get_chat_project(id)

    conn = get_connection()
    try:
        conn.execute(
            f"UPDATE chat_projects SET {', '.join(assignments)} WHERE id = ?",
            (*values, id),
        )
        conn.commit()
        row = conn.execute("SELECT * FROM chat_projects WHERE id = ?", (id,)).fetchone()
        return _chat_project_from_row(row) if row is not None else None
    finally:
        conn.close()


def set_chat_project_working_folder(
    id: str,
    path: Optional[str],
    identity: Optional[tuple[int, int]] = None,
) -> Optional[dict]:
    """The managed workspace is kept, so switching back finds its files."""
    conn = get_connection()
    try:
        conn.execute(
            "UPDATE chat_projects SET working_folder = ?, working_folder_identity = ?, "
            "updated_at = ? WHERE id = ?",
            (
                path or None,
                f"{identity[0]}:{identity[1]}" if path and identity else None,
                int(time.time() * 1000),
                id,
            ),
        )
        conn.commit()
        row = conn.execute("SELECT * FROM chat_projects WHERE id = ?", (id,)).fetchone()
        return _chat_project_from_row(row) if row is not None else None
    finally:
        conn.close()


def available_project_working_folder(project: Optional[dict]) -> Optional[str]:
    """None unless still the picked directory: the identity check stops a new dir at the old path inheriting the grant."""
    path = (project or {}).get("workingFolder")
    if not path or not is_owner_context():
        return None
    try:
        st = os.lstat(path)
    except OSError:
        return None
    if not stat.S_ISDIR(st.st_mode):
        return None
    identity = (project or {}).get("workingFolderIdentity")
    if identity and identity != f"{st.st_dev}:{st.st_ino}":
        return None
    real = os.path.realpath(path)
    # Reached through a new ancestor link, the same inode sits somewhere else: not the folder picked.
    if os.path.normcase(real) != os.path.normcase(path):
        return None
    return real


def ensure_chat_project_workspace(id: str) -> Optional[dict]:
    project = get_chat_project(id)
    if project is None:
        return None
    root_path = project.get("rootPath") or _default_project_root(project)
    root_path = _ensure_project_workspace(root_path)
    # A concurrent delete can drop the row before mkdir; recheck. Only delete decides file removal.
    project = get_chat_project(id)
    if project is None:
        return None
    if project.get("rootPath") == root_path:
        return project
    conn = get_connection()
    try:
        conn.execute(
            "UPDATE chat_projects SET root_path = ? WHERE id = ?",
            (root_path, id),
        )
        conn.commit()
    finally:
        conn.close()
    return get_chat_project(id)


def get_chat_project(id: str) -> Optional[dict]:
    conn = get_connection()
    try:
        row = conn.execute("SELECT * FROM chat_projects WHERE id = ?", (id,)).fetchone()
        return _chat_project_from_row(row) if row is not None else None
    finally:
        conn.close()


def list_chat_projects(include_archived: bool = False) -> list[dict]:
    """Projects with `lastActivityAt`: their own last edit or newest unarchived chat, whichever is later."""
    conn = get_connection()
    try:
        where = "" if include_archived else "WHERE p.archived = 0"
        rows = conn.execute(
            f"""
            SELECT p.*,
                   MAX(
                       COALESCE(p.updated_at, p.created_at),
                       COALESCE(chats.last_chat_at, 0)
                   ) AS last_activity_at
            FROM chat_projects p
            LEFT JOIN (
                -- one row per sidebar row, as the sidebar groups it: a compare pair's activity is its
                -- newest pane's and counts for that pane's project only
                SELECT project_id, MAX(row_activity) AS last_chat_at
                FROM (
                    SELECT NULLIF(project_id, '') AS project_id,
                           MAX({_SIDEBAR_ACTIVITY_SQL}) OVER (PARTITION BY {_SIDEBAR_ROW_ID_SQL}) AS row_activity,
                           ROW_NUMBER() OVER (
                               PARTITION BY {_SIDEBAR_ROW_ID_SQL}
                               ORDER BY {_SIDEBAR_ACTIVITY_SQL} DESC, created_at DESC, rowid ASC
                           ) AS member_rank
                    FROM chat_threads
                    WHERE COALESCE(archived, 0) = 0
                )
                WHERE member_rank = 1 AND project_id IS NOT NULL
                GROUP BY project_id
            ) chats ON chats.project_id = p.id
            {where}
            ORDER BY p.updated_at DESC
            """
        ).fetchall()
        return [
            {**_chat_project_from_row(row), "lastActivityAt": row["last_activity_at"]}
            for row in rows
        ]
    finally:
        conn.close()


def delete_chat_project(id: str, delete_files: bool = False) -> Optional[dict]:
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_chat_attachment_inventory_current(conn)
        row = conn.execute("SELECT * FROM chat_projects WHERE id = ?", (id,)).fetchone()
        if row is None:
            conn.rollback()
            return None
        project = _chat_project_from_row(row)
        thread_ids = {
            thread["id"]
            for thread in conn.execute(
                "SELECT id FROM chat_threads WHERE project_id = ?",
                (id,),
            )
        }
        # Read before the cascade, or the supervisor cannot know which runs to stop.
        active_runs = (
            [
                row["id"]
                for row in conn.execute(
                    "SELECT id FROM research_runs WHERE thread_id IN ({}) "
                    "AND status IN ({}) AND lease_owner IS NOT NULL "
                    "ORDER BY created_at, id".format(
                        ",".join("?" for _ in thread_ids) or "NULL",
                        ",".join("?" for _ in _ACTIVE_RESEARCH_RUN_STATUSES),
                    ),
                    (*tuple(sorted(thread_ids)), *_ACTIVE_RESEARCH_RUN_STATUSES),
                )
            ]
            if thread_ids
            else []
        )
        active_chat_runs = _active_chat_generation_run_ids(conn, thread_ids)
        _reparent_surviving_forks(conn, thread_ids)
        # Tombstone this membership so a late writer cannot recreate a member.
        _tombstone_chat_threads(conn, thread_ids)
        conn.execute("DELETE FROM chat_threads WHERE project_id = ?", (id,))
        conn.execute("DELETE FROM chat_projects WHERE id = ?", (id,))
        _load_chat_pins_in_conn(conn, repair = True, remember_removed = True)
        _mark_chat_attachment_inventory_clean(conn)
        conn.commit()
        if delete_files:
            _delete_project_workspace(project)
        # The membership actually deleted, which may differ from the caller's earlier listing.
        project = dict(project)
        project["memberIds"] = sorted(thread_ids)
        project["activeResearchRunIds"] = active_runs
        project["activeChatGenerationRunIds"] = active_chat_runs
        return project
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def delete_chat_project_with_active_research_runs(id: str) -> tuple[Optional[dict], list[str]]:
    project = delete_chat_project(id, delete_files = False)
    if project is None:
        return None, []
    return project, list(project.get("activeResearchRunIds") or [])


class ChatMessageConflictError(RuntimeError):
    """Raised when a chat message id already belongs to another thread."""


class ChatMessageProtectedError(RuntimeError):
    """Raised when pruning would remove a message owned by a durable feature."""


class CorruptSettingsError(RuntimeError):
    """Raised when a partial settings patch would overwrite corrupt settings."""


def _parse_chat_setting_json(key: str, value_json: str) -> tuple[bool, Any]:
    try:
        return True, json.loads(value_json)
    except (json.JSONDecodeError, TypeError) as exc:
        logger.warning(
            "Corrupt chat_settings JSON; quarantining key=%s error=%s",
            key,
            exc,
        )
        return False, None


def _load_chat_settings_for_merge(conn: sqlite3.Connection) -> tuple[dict[str, Any], set[str]]:
    rows = conn.execute("SELECT key, value_json FROM chat_settings").fetchall()
    current: dict[str, Any] = {}
    corrupt: set[str] = set()
    now = datetime.now(timezone.utc).isoformat()
    for row in rows:
        ok, value = _parse_chat_setting_json(row["key"], row["value_json"])
        if ok:
            current[row["key"]] = value
            continue
        corrupt.add(row["key"])
        conn.execute(
            """
            INSERT INTO chat_settings_quarantine
                (key, value_json, reason, quarantined_at)
            VALUES (?, ?, ?, ?)
            """,
            (row["key"], row["value_json"], "json_decode_error", now),
        )
        conn.execute(
            "DELETE FROM chat_settings WHERE key = ? AND value_json = ?",
            (row["key"], row["value_json"]),
        )
    return current, corrupt


def _raise_if_chat_message_thread_conflicts(
    conn: sqlite3.Connection, thread_id: str, message_ids: list[str]
) -> None:
    unique_ids = list(dict.fromkeys(message_ids))
    if not unique_ids:
        return
    conflicts: list[str] = []
    for start in range(0, len(unique_ids), _SQLITE_IN_CHUNK_SIZE):
        chunk = unique_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
        placeholders = ",".join("?" for _ in chunk)
        rows = conn.execute(
            f"""
            SELECT id FROM chat_messages
            WHERE id IN ({placeholders}) AND thread_id != ?
            ORDER BY id
            """,
            (*chunk, thread_id),
        ).fetchall()
        conflicts.extend(row["id"] for row in rows)
    if conflicts:
        preview = ", ".join(conflicts[:5])
        suffix = "" if len(conflicts) <= 5 else f" (+{len(conflicts) - 5} more)"
        raise ChatMessageConflictError(
            f"Message id already belongs to another thread: {preview}{suffix}"
        )


def _stored_chat_message_values(
    conn: sqlite3.Connection, thread_id: str, message_ids: list
) -> dict:
    """Stored upsert columns of this thread's messages, keyed by id, in upsert parameter order."""
    unique_ids = list(dict.fromkeys(message_ids))
    stored: dict = {}
    for start in range(0, len(unique_ids), _SQLITE_IN_CHUNK_SIZE):
        chunk = unique_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
        placeholders = ",".join("?" for _ in chunk)
        for row in conn.execute(
            f"""
            SELECT id, parent_id, role, content_json, attachments_json, metadata_json, created_at
            FROM chat_messages
            WHERE thread_id = ? AND id IN ({placeholders})
            """,
            (thread_id, *chunk),
        ):
            stored[row[0]] = tuple(row)[1:]
    return stored


def _bump_chat_thread_updated_at(
    conn: sqlite3.Connection, thread_id: str, message_created_at: int
) -> None:
    conn.execute(
        """
        UPDATE chat_threads
        SET updated_at = MAX(COALESCE(updated_at, created_at), ?)
        WHERE id = ?
        """,
        (message_created_at, thread_id),
    )


def _recompute_chat_thread_updated_at(conn: sqlite3.Connection, thread_id: str) -> None:
    """Set updated_at from the remaining messages, floored at created_at. Unlike the ratchet-only bump,
    this can lower updated_at -- needed after pruning, which may delete the thread's newest message."""
    conn.execute(
        """
        UPDATE chat_threads
        SET updated_at = MAX(
            COALESCE(
                (
                    SELECT MAX(m.created_at) FROM chat_messages m
                    WHERE m.thread_id = chat_threads.id
                ),
                created_at
            ),
            created_at
        )
        WHERE id = ?
        """,
        (thread_id,),
    )


def _research_message_ids(conn: sqlite3.Connection, thread_id: str) -> set[str]:
    return {
        str(message_id)
        for row in conn.execute(
            "SELECT user_message_id, assistant_message_id FROM research_runs WHERE thread_id = ?",
            (thread_id,),
        ).fetchall()
        for message_id in row
        if message_id is not None
    }


def _research_assistant_message_ids(conn: sqlite3.Connection, thread_id: str) -> set[str]:
    return {
        str(row[0])
        for row in conn.execute(
            "SELECT assistant_message_id FROM research_runs "
            "WHERE thread_id = ? AND assistant_message_id IS NOT NULL",
            (thread_id,),
        ).fetchall()
    }


def _generation_message_ids(conn: sqlite3.Connection, thread_id: str) -> set[str]:
    return {
        str(message_id)
        for row in conn.execute(
            """SELECT user_message_id, assistant_message_id
               FROM chat_generation_runs WHERE thread_id = ?""",
            (thread_id,),
        ).fetchall()
        for message_id in row
        if message_id is not None
    }


def _terminal_generation_message_ids(conn: sqlite3.Connection, thread_id: str) -> set[str]:
    terminal = {
        str(message_id)
        for row in conn.execute(
            """SELECT user_message_id, assistant_message_id
               FROM chat_generation_runs
               WHERE thread_id = ? AND status IN ('cancelled', 'completed', 'failed')""",
            (thread_id,),
        ).fetchall()
        for message_id in row
        if message_id is not None
    }
    active = {
        str(message_id)
        for row in conn.execute(
            """SELECT user_message_id, assistant_message_id
               FROM chat_generation_runs
               WHERE thread_id = ? AND status IN ('queued', 'running', 'cancelling')""",
            (thread_id,),
        ).fetchall()
        for message_id in row
        if message_id is not None
    }
    return terminal - active


def _references_tombstoned_generation(conn: sqlite3.Connection, message: dict) -> bool:
    metadata = message.get("metadata")
    if not isinstance(metadata, dict) or metadata.get("serverManaged") is not True:
        return False
    run_id = metadata.get("generationRunId")
    if not isinstance(run_id, str) or not run_id:
        return False
    return (
        conn.execute(
            "SELECT 1 FROM app_settings WHERE key=?",
            (f"chat-generation-run-tombstone:{run_id}",),
        ).fetchone()
        is not None
    )


def _server_managed_message_ids(conn: sqlite3.Connection, thread_id: str) -> set[str]:
    return _research_message_ids(conn, thread_id) | _generation_message_ids(conn, thread_id)


def _surviving_parent_id(
    conn: sqlite3.Connection, thread_id: str, message_id: str, pruned: set
) -> "str | None":
    """Walks the stored chain server side so a client cannot smuggle a parent past the relink guard."""
    seen = {message_id}
    row = conn.execute(
        "SELECT parent_id FROM chat_messages WHERE thread_id = ? AND id = ?",
        (thread_id, message_id),
    ).fetchone()
    parent = row["parent_id"] if row is not None else None
    while parent and str(parent) in pruned:
        if str(parent) in seen:
            # A cycle can only come from a corrupt thread; stop rather than spin.
            return None
        seen.add(str(parent))
        row = conn.execute(
            "SELECT parent_id FROM chat_messages WHERE thread_id = ? AND id = ?",
            (thread_id, str(parent)),
        ).fetchone()
        parent = row["parent_id"] if row is not None else None
    # Normalized like the caller reads parentId, so empty parent_id and self-links resolve to root.
    survivor = str(parent) if parent else None
    return None if survivor == message_id else survivor


def _research_message_would_change(
    conn: sqlite3.Connection,
    thread_id: str,
    message: dict,
    pruned: set = frozenset(),
) -> bool:
    message_id = str(message["id"])
    row = conn.execute(
        "SELECT parent_id, role, content_json, metadata_json, attachments_json, created_at "
        "FROM chat_messages WHERE thread_id = ? AND id = ?",
        (thread_id, message_id),
    ).fetchone()
    if row is None:
        return False

    def canon(value: object) -> str | None:
        return json.dumps(value, sort_keys = True) if value is not None else None

    stored_parent = row["parent_id"] or None
    sent_parent = message.get("parentId") or None
    parent_changed = sent_parent != stored_parent
    if parent_changed and stored_parent is not None and str(stored_parent) in pruned:
        # A relink forced by deleting this message's parent is allowed only if it matches the server's.
        parent_changed = sent_parent != _surviving_parent_id(conn, thread_id, message_id, pruned)

    # created_at compared too, or a re-upsert could reorder the research prompt/response pair.
    return (
        canon(message.get("content", [])) != canon(json.loads(row["content_json"] or "[]"))
        or canon(message.get("metadata"))
        != canon(json.loads(row["metadata_json"]) if row["metadata_json"] else None)
        or canon(message.get("attachments"))
        != canon(json.loads(row["attachments_json"]) if row["attachments_json"] else None)
        or parent_changed
        or str(message.get("role")) != str(row["role"])
        or int(message.get("createdAt", row["created_at"])) != int(row["created_at"])
    )


_GENERATION_ACTIVE_STATUSES = {"queued", "running", "cancelling"}
_GENERATION_TERMINAL_STATUSES = {"cancelled", "completed", "failed"}
_GENERATION_STATUS_RANK = {"queued": 0, "running": 1, "cancelling": 2}


def _safe_generation_assistant_update(
    conn: sqlite3.Connection, thread_id: str, message: dict
) -> bool:
    """Allow only monotonic writes to the assistant row owned by one run."""
    row = conn.execute(
        """SELECT r.id AS run_id, r.status AS run_status, r.last_event_seq,
                  m.parent_id, m.role, m.content_json, m.metadata_json,
                  m.attachments_json, m.created_at
           FROM chat_generation_runs r
           JOIN chat_messages m ON m.id = r.assistant_message_id
           WHERE r.thread_id = ? AND r.assistant_message_id = ?""",
        (thread_id, str(message["id"])),
    ).fetchone()
    if row is None or str(message.get("role")) != "assistant":
        return False
    if (message.get("parentId") or None) != (row["parent_id"] or None):
        return False
    if int(message.get("createdAt", row["created_at"])) != int(row["created_at"]):
        return False

    stored_attachments = json.loads(row["attachments_json"]) if row["attachments_json"] else None
    if json.dumps(message.get("attachments"), sort_keys = True) != json.dumps(
        stored_attachments, sort_keys = True
    ):
        return False

    incoming = message.get("metadata")
    stored = json.loads(row["metadata_json"]) if row["metadata_json"] else {}
    if not isinstance(incoming, dict) or not isinstance(stored, dict):
        return False
    if incoming.get("serverManaged") is not True:
        return False
    if incoming.get("generationRunId") != row["run_id"]:
        return False

    incoming_seq = incoming.get("generationSeq")
    stored_seq = stored.get("generationSeq", 0)
    if (
        isinstance(incoming_seq, bool)
        or not isinstance(incoming_seq, int)
        or isinstance(stored_seq, bool)
        or not isinstance(stored_seq, int)
        or incoming_seq < stored_seq
        or incoming_seq > int(row["last_event_seq"])
    ):
        return False
    if incoming_seq == stored_seq and json.dumps(
        message.get("content", []), sort_keys = True
    ) != json.dumps(json.loads(row["content_json"] or "[]"), sort_keys = True):
        return False

    # A settled terminal row is immutable; an old tab must not overwrite finish metadata.
    if stored.get("generationSettled") is True:
        return incoming == stored

    incoming_status = incoming.get("generationStatus")
    stored_status = stored.get("generationStatus")
    run_status = str(row["run_status"])
    if incoming_status not in _GENERATION_ACTIVE_STATUSES | _GENERATION_TERMINAL_STATUSES:
        return False
    if run_status in _GENERATION_TERMINAL_STATUSES and incoming_status != run_status:
        return False
    if run_status in _GENERATION_TERMINAL_STATUSES:
        # Terminal settlement adds timing but must keep every field the producer persisted (incl. incomplete).
        for key in stored:
            if key not in incoming:
                return False
        if incoming.get("incomplete") != stored.get("incomplete"):
            return False
    if (
        run_status in _GENERATION_ACTIVE_STATUSES
        and incoming_status in _GENERATION_TERMINAL_STATUSES
    ):
        return False
    if (
        incoming.get("generationSettled") is True
        and run_status not in _GENERATION_TERMINAL_STATUSES
    ):
        return False
    if incoming.get("generationSettled") is True and incoming_seq != int(row["last_event_seq"]):
        return False
    if stored_status in _GENERATION_TERMINAL_STATUSES and incoming_status != stored_status:
        return False
    if stored.get("generationSettled") is True and incoming.get("generationSettled") is not True:
        return False
    if (
        stored_status in _GENERATION_ACTIVE_STATUSES
        and incoming_status in _GENERATION_ACTIVE_STATUSES
        and _GENERATION_STATUS_RANK[incoming_status] < _GENERATION_STATUS_RANK[stored_status]
    ):
        return False
    return True


def _guard_server_managed_messages(
    conn: sqlite3.Connection,
    thread_id: str,
    messages: list[dict],
    pruned: set = frozenset(),
    *,
    allow_research_update: bool = False,
) -> None:
    generation = _generation_message_ids(conn, thread_id)
    if allow_research_update:
        # Once the generation settles the research run is the only writer; skip monotonic rules.
        generation -= _research_assistant_message_ids(
            conn, thread_id
        ) & _terminal_generation_message_ids(conn, thread_id)
        protected = set(generation)
    else:
        protected = generation | _research_message_ids(conn, thread_id)
    if not protected:
        return
    for message in messages:
        message_id = str(message["id"])
        if (
            message_id in generation
            and not _safe_generation_assistant_update(conn, thread_id, message)
        ) or (
            message_id in protected
            and message_id not in generation
            and _research_message_would_change(conn, thread_id, message, pruned)
        ):
            raise ChatMessageProtectedError("server-managed generation messages cannot be edited")


def _settle_handed_off_generation(conn: sqlite3.Connection, message: dict) -> dict:
    # An unsettled row would be replayed by generation recovery and clobber the report.
    metadata = message.get("metadata")
    if not isinstance(metadata, dict):
        return message
    row = conn.execute(
        """SELECT id, status, last_event_seq FROM chat_generation_runs
           WHERE thread_id = ? AND assistant_message_id = ?
             AND status IN ('cancelled', 'completed', 'failed')""",
        (message["threadId"], str(message["id"])),
    ).fetchone()
    if row is None or metadata.get("generationRunId") != row["id"]:
        return message
    # The research status now reports the outcome, not the acknowledgement's length/interrupt mark.
    metadata = {key: value for key, value in metadata.items() if key != "incomplete"}
    return {
        **message,
        "metadata": {
            **metadata,
            "generationStatus": row["status"],
            "generationSeq": int(row["last_event_seq"]),
            "generationSettled": True,
        },
    }


def _detach_terminal_generation_for_edit(
    conn: sqlite3.Connection, thread_id: str, message: dict
) -> bool:
    stored = conn.execute(
        """SELECT parent_id, role, metadata_json, attachments_json
           FROM chat_messages WHERE thread_id = ? AND id = ?""",
        (thread_id, str(message["id"])),
    ).fetchone()
    if stored is None:
        return False
    stored_metadata = json.loads(stored["metadata_json"]) if stored["metadata_json"] else {}
    run_id = stored_metadata.get("generationRunId") if isinstance(stored_metadata, dict) else None
    if not isinstance(run_id, str):
        return False
    # Bind by the stamped run id, not createdAt: create_run stamps its own created_at, so a tab's
    # own Date cannot match (issue 15004); the run row is the authority
    row = conn.execute(
        """SELECT id AS run_id, status AS run_status
           FROM chat_generation_runs
           WHERE id = ? AND thread_id = ? AND assistant_message_id = ?""",
        (run_id, thread_id, str(message["id"])),
    ).fetchone()
    if row is None or str(row["run_status"]) not in _GENERATION_TERMINAL_STATUSES:
        return False
    incoming_metadata = message.get("metadata")
    # Not generationSettled: a settle whose `incomplete` disagrees with the producer's is refused,
    # so a finished reply would never become editable (#13938). The run status above decides.
    if (
        stored_metadata.get("serverManaged") is not True
        or isinstance(incoming_metadata, dict)
        and incoming_metadata.get("serverManaged") is True
        or str(message.get("role")) != str(stored["role"])
        or (message.get("parentId") or None) != (stored["parent_id"] or None)
    ):
        return False
    stored_attachments = (
        json.loads(stored["attachments_json"]) if stored["attachments_json"] else None
    )
    if json.dumps(message.get("attachments"), sort_keys = True) != json.dumps(
        stored_attachments, sort_keys = True
    ):
        return False
    conn.execute("DELETE FROM chat_generation_runs WHERE id = ?", (row["run_id"],))
    return True


_CONTENT_PART_ID_PREFIX = "content-part-sha256-"
_URI_SCHEME_RE = re.compile(r"^[A-Za-z][A-Za-z0-9+.-]*:")


def _is_locally_stored_blob(value: str) -> bool:
    """True for data URIs or bare base64, never external/blob URI references."""
    candidate = value.lstrip()
    if not candidate:
        return False
    if candidate[:5].lower() == "data:":
        return True
    if candidate.startswith(("//", "\\\\")):
        return False
    return _URI_SCHEME_RE.match(candidate) is None


def _managed_content_part_payload(part: dict) -> Optional[tuple[str, Any]]:
    image = part.get("image")
    if isinstance(image, str) and image[:5].lower() == "data:":
        return "image", image

    audio = part.get("audio")
    if isinstance(audio, str) and _is_locally_stored_blob(audio):
        return "audio", audio
    if isinstance(audio, dict):
        data = audio.get("data")
        if isinstance(data, str) and _is_locally_stored_blob(data):
            return "audio", audio
    return None


def _content_part_id(part: dict) -> Optional[str]:
    """Stable managed id derived from blob data, without mutating inference content."""
    payload = _managed_content_part_payload(part)
    if payload is None:
        return None
    canonical = json.dumps(
        payload,
        ensure_ascii = False,
        separators = (",", ":"),
        sort_keys = True,
    ).encode("utf-8")
    return f"{_CONTENT_PART_ID_PREFIX}{hashlib.sha256(canonical).hexdigest()}"


def _chat_message_revision(metadata: Any) -> int:
    revision = metadata.get("revision") if isinstance(metadata, dict) else None
    return revision if type(revision) is int and revision > 0 else 0


def _chat_attachment_deletions_for_messages(
    conn: sqlite3.Connection, thread_id: str, message_ids: list[str]
) -> dict[str, tuple[set[str], int]]:
    deletions: dict[str, tuple[set[str], int]] = {}
    unique_ids = list(dict.fromkeys(message_ids))
    for start in range(0, len(unique_ids), _SQLITE_IN_CHUNK_SIZE):
        chunk = unique_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
        placeholders = ",".join("?" for _ in chunk)
        rows = conn.execute(
            f"""
            SELECT t.message_id, t.attachment_id, m.metadata_json
            FROM chat_attachment_tombstones AS t
            LEFT JOIN chat_messages AS m ON m.id = t.message_id AND m.thread_id = t.thread_id
            WHERE t.thread_id = ? AND t.message_id IN ({placeholders})
            """,
            (thread_id, *chunk),
        ).fetchall()
        for row in rows:
            message_id = row["message_id"]
            if message_id not in deletions:
                deletions[message_id] = (
                    set(),
                    _chat_message_revision(_json_loads(row["metadata_json"], {})),
                )
            deletions[message_id][0].add(row["attachment_id"])
    return deletions


def _reconcile_chat_message_uploads(
    message: dict,
    tombstones: set[str],
    stored_revision: int = 0,
) -> dict:
    """Strip uploads previously deleted through the Data tab from a stale write."""
    if not tombstones:
        return message

    reconciled = dict(message)
    removed_ids: set[str] = set()
    attachments = message.get("attachments")
    if isinstance(attachments, list):
        removed_ids.update(
            str(attachment.get("id") or "")
            for attachment in attachments
            if isinstance(attachment, dict) and str(attachment.get("id") or "") in tombstones
        )
        reconciled["attachments"] = [
            attachment
            for attachment in attachments
            if not (isinstance(attachment, dict) and str(attachment.get("id") or "") in tombstones)
        ]

    content = message.get("content")
    if isinstance(content, list):
        kept_content = []
        for part in content:
            part_id = _content_part_id(part) if isinstance(part, dict) else None
            if part_id is not None and part_id in tombstones:
                removed_ids.add(part_id)
            else:
                kept_content.append(part)
        reconciled["content"] = kept_content
    metadata = message.get("metadata")
    incoming_revision = _chat_message_revision(metadata)
    revision = max(stored_revision, incoming_revision + len(removed_ids))
    if revision != incoming_revision:
        reconciled["metadata"] = {
            **(metadata if isinstance(metadata, dict) else {}),
            "revision": revision,
        }
    return reconciled


def _chat_attachment_metadata_text(value, fallback: Optional[str] = None) -> Optional[str]:
    """Keep untyped legacy/import metadata safe for SQLite binding."""
    if value is None:
        return fallback
    if isinstance(value, str):
        return value or fallback
    if isinstance(value, (bool, int, float)):
        return str(value)
    # Objects and arrays are not useful display metadata and sqlite3 rejects binding them.
    return fallback


def _chat_attachment_inventory_entries(
    attachments_json: Optional[str],
    content_json: Optional[str],
    tombstones: Optional[set[str]] = None,
) -> list[dict]:
    tombstones = tombstones or set()
    attachments = _json_loads(attachments_json, None)
    if not isinstance(attachments, list):
        attachments = []
    attachments = [
        attachment
        for attachment in attachments
        if isinstance(attachment, dict) and attachment.get("id")
    ]
    represented_parts = {
        part_id
        for attachment in attachments
        for part in _attachment_content_parts(attachment)
        for part_id in [_content_part_id(part)]
        if part_id is not None
    }
    attachments.extend(
        attachment
        for attachment in _content_part_attachments(content_json)
        if attachment["id"] not in represented_parts
    )

    entries: list[dict] = []
    seen: set[str] = set()
    for attachment in attachments:
        attachment_id = str(attachment["id"])
        if attachment_id in seen or attachment_id in tombstones:
            continue
        seen.add(attachment_id)
        entries.append(
            {
                "id": attachment_id,
                "name": _chat_attachment_metadata_text(attachment.get("name"), "attachment"),
                "type": _chat_attachment_metadata_text(attachment.get("type")),
                "contentType": _chat_attachment_metadata_text(attachment.get("contentType")),
                "sizeBytes": _chat_attachment_size_bytes(attachment),
                "originalSha256": chat_originals.attachment_sha256(attachment),
                "textBytes": _chat_attachment_text_bytes(attachment),
            }
        )
    return entries


def count_chat_message_attachments(
    attachments_json: Optional[str], content_json: Optional[str]
) -> int:
    return len(_chat_attachment_inventory_entries(attachments_json, content_json))


def _replace_chat_attachment_inventory(
    conn: sqlite3.Connection,
    message_id: str,
    attachments_json: Optional[str],
    content_json: Optional[str],
    tombstones: Optional[set[str]] = None,
) -> None:
    conn.execute("DELETE FROM chat_attachment_inventory WHERE message_id = ?", (message_id,))
    entries = _chat_attachment_inventory_entries(
        attachments_json,
        content_json,
        tombstones,
    )
    conn.executemany(
        """
        INSERT INTO chat_attachment_inventory
            (message_id, attachment_id, name, type, content_type, size_bytes, original_sha256,
             text_bytes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """,
        [
            (
                message_id,
                entry["id"],
                entry["name"],
                entry["type"],
                entry["contentType"],
                entry["sizeBytes"],
                entry["originalSha256"],
                entry["textBytes"],
            )
            for entry in entries
        ],
    )


def _mark_chat_attachment_inventory_clean(conn: sqlite3.Connection) -> None:
    conn.execute(
        """
        INSERT INTO chat_attachment_inventory_state
            (singleton, inventory_version, dirty, backfilled_at)
        VALUES (1, ?, 0, ?)
        ON CONFLICT(singleton) DO UPDATE SET
            inventory_version = excluded.inventory_version,
            dirty = 0,
            backfilled_at = excluded.backfilled_at
        """,
        (
            _CHAT_ATTACHMENT_INVENTORY_VERSION,
            int(datetime.now(timezone.utc).timestamp() * 1000),
        ),
    )


def insert_server_chat_message(
    conn: sqlite3.Connection,
    message_id: str,
    thread_id: str,
    parent_id: Optional[str],
    content_json: str,
    metadata_json: str,
    created_at: int,
) -> None:
    """Insert a server-owned assistant message, indexing it in place so the insert trigger leaves a clean inventory clean."""
    was_clean = (
        conn.execute(
            "SELECT 1 FROM chat_attachment_inventory_state WHERE singleton=1 AND dirty=0"
        ).fetchone()
        is not None
    )
    conn.execute(
        """INSERT INTO chat_messages
           (id, thread_id, parent_id, role, content_json, metadata_json, created_at)
           VALUES (?, ?, ?, 'assistant', ?, ?, ?)""",
        (message_id, thread_id, parent_id, content_json, metadata_json, created_at),
    )
    if not was_clean:
        return
    tombstones = {
        row[0]
        for row in conn.execute(
            "SELECT attachment_id FROM chat_attachment_tombstones WHERE thread_id=? AND message_id=?",
            (thread_id, message_id),
        )
    }
    _replace_chat_attachment_inventory(conn, message_id, None, content_json, tombstones)
    conn.execute("UPDATE chat_attachment_inventory_state SET dirty=0 WHERE singleton=1")


def _rebuild_chat_attachment_inventory(conn: sqlite3.Connection) -> None:
    """Rebuild after schema upgrade or a write from an older Unsloth build."""
    conn.execute("DELETE FROM chat_attachment_inventory")
    tombstones: dict[tuple[str, str], set[str]] = {}
    for row in conn.execute(
        "SELECT thread_id, message_id, attachment_id FROM chat_attachment_tombstones"
    ).fetchall():
        tombstones.setdefault((row["thread_id"], row["message_id"]), set()).add(
            row["attachment_id"]
        )
    rows = conn.execute(
        "SELECT id, thread_id, attachments_json, content_json FROM chat_messages"
    ).fetchall()
    for row in rows:
        _replace_chat_attachment_inventory(
            conn,
            row["id"],
            row["attachments_json"],
            row["content_json"],
            tombstones.get((row["thread_id"], row["id"]), set()),
        )


def _ensure_chat_attachment_inventory_current(conn: sqlite3.Connection) -> None:
    state = conn.execute(
        """
        SELECT inventory_version, dirty
        FROM chat_attachment_inventory_state
        WHERE singleton = 1
        """
    ).fetchone()
    if (
        state is not None
        and state["inventory_version"] == _CHAT_ATTACHMENT_INVENTORY_VERSION
        and not state["dirty"]
    ):
        return

    owns_transaction = not conn.in_transaction
    if owns_transaction:
        conn.execute("BEGIN IMMEDIATE")
    try:
        state = conn.execute(
            """
            SELECT inventory_version, dirty
            FROM chat_attachment_inventory_state
            WHERE singleton = 1
            """
        ).fetchone()
        if (
            state is None
            or state["inventory_version"] != _CHAT_ATTACHMENT_INVENTORY_VERSION
            or state["dirty"]
        ):
            _rebuild_chat_attachment_inventory(conn)
            _mark_chat_attachment_inventory_clean(conn)
        if owns_transaction:
            conn.commit()
    except Exception:
        if owns_transaction:
            conn.rollback()
        raise


def upsert_chat_message(
    message: dict,
    *,
    allow_research_update: bool = False,
    allow_generation_edit: bool = False,
    expected_research_run_id: str | None = None,
    expected_research_attempt: int | None = None,
) -> dict | None:
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        if expected_research_run_id is not None:
            if expected_research_attempt is None:
                raise ValueError("expected_research_attempt is required with a research run")
            current = conn.execute(
                "SELECT 1 FROM research_runs "
                "WHERE id = ? AND thread_id = ? AND assistant_message_id = ? "
                "AND retry_count = ?",
                (
                    expected_research_run_id,
                    message["threadId"],
                    message["id"],
                    expected_research_attempt,
                ),
            ).fetchone()
            if current is None:
                conn.rollback()
                return None
        _ensure_chat_attachment_inventory_current(conn)
        if _references_tombstoned_generation(conn, message):
            raise ChatMessageProtectedError(
                "deleted server-managed generation messages cannot be restored"
            )
        if allow_generation_edit:
            _detach_terminal_generation_for_edit(conn, message["threadId"], message)
        _guard_server_managed_messages(
            conn,
            message["threadId"],
            [message],
            allow_research_update = allow_research_update,
        )
        if allow_research_update:
            message = _settle_handed_off_generation(conn, message)
        _raise_if_chat_message_thread_conflicts(
            conn,
            message["threadId"],
            [message["id"]],
        )
        deletions = _chat_attachment_deletions_for_messages(
            conn,
            message["threadId"],
            [message["id"]],
        )
        reconciled = _reconcile_chat_message_uploads(
            message,
            *deletions.get(message["id"], (set(), 0)),
        )
        content_json = json.dumps(reconciled.get("content", []))
        attachments_json = (
            json.dumps(reconciled.get("attachments"))
            if reconciled.get("attachments") is not None
            else None
        )
        conn.execute(
            """
            INSERT INTO chat_messages
                (id, thread_id, parent_id, role, content_json, attachments_json, metadata_json, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                parent_id = excluded.parent_id,
                role = excluded.role,
                content_json = excluded.content_json,
                attachments_json = excluded.attachments_json,
                metadata_json = excluded.metadata_json,
                created_at = excluded.created_at
            WHERE excluded.thread_id = chat_messages.thread_id
            """,
            (
                reconciled["id"],
                reconciled["threadId"],
                reconciled.get("parentId"),
                reconciled["role"],
                content_json,
                attachments_json,
                json.dumps(reconciled.get("metadata"))
                if reconciled.get("metadata") is not None
                else None,
                int(reconciled["createdAt"]),
            ),
        )
        _replace_chat_attachment_inventory(
            conn,
            reconciled["id"],
            attachments_json,
            content_json,
        )
        _bump_chat_thread_updated_at(
            conn,
            reconciled["threadId"],
            int(reconciled["createdAt"]),
        )
        _mark_chat_attachment_inventory_clean(conn)
        conn.commit()
        return reconciled
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


class ExternalImportMark(NamedTuple):
    source: str
    session_id: str
    turns: int
    revision: str = ""


def sync_chat_messages(
    thread_id: str,
    messages: list[dict],
    prune_missing: bool = False,
    *,
    allow_research_update: bool = False,
    deleted_message_ids: Iterable[str] = (),
    external_import_mark: Optional[ExternalImportMark] = None,
) -> list[dict]:
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_chat_attachment_inventory_current(conn)
        # Dropping a message of a tombstoned run is a content decision, not a delete: its run row is gone, so nothing else holds it back from the prune.
        tombstoned_ids = {
            str(message["id"])
            for message in messages
            if _references_tombstoned_generation(conn, message)
        }
        messages = [message for message in messages if str(message["id"]) not in tombstoned_ids]
        # Generation-linked messages are server-managed: keep them rather than reject on client drift; the single-message upsert still guards edits.
        research_ids = _research_message_ids(conn, thread_id)
        generation_ids = _generation_message_ids(conn, thread_id)
        managed_ids = research_ids | generation_ids
        requested_ids = {str(m["id"]) for m in messages}
        # Snapshot pruning is not delete intent: terminal generation messages survive unless named.
        explicitly_deleted_ids = {str(message_id) for message_id in deleted_message_ids}
        explicitly_deleted_terminal_ids = explicitly_deleted_ids & _terminal_generation_message_ids(
            conn, thread_id
        )
        prune_protected_ids = (
            research_ids
            | (generation_ids - explicitly_deleted_terminal_ids)
            | (tombstoned_ids - explicitly_deleted_ids)
        )
        # Computed before the upsert to tell forced relinks from edits; research ids are excluded since their delete is exempt, or the reseat skips a surviving parent.
        pruned: set = set()
        unseen_ids: set = set()
        if prune_missing:
            stored_rows = conn.execute(
                "SELECT id, parent_id, role FROM chat_messages WHERE thread_id = ?",
                (thread_id,),
            ).fetchall()
            unseen_ids = {str(row["id"]) for row in stored_rows} - requested_ids
            pruned = unseen_ids - prune_protected_ids
            if explicitly_deleted_ids:
                # Deleting a user message also deletes its replies, including ones this window never saw.
                deleted_user_ids = {
                    str(row["id"]) for row in stored_rows if row["role"] == "user"
                } & explicitly_deleted_ids
                pruned &= explicitly_deleted_ids | (
                    {
                        str(row["id"])
                        for row in stored_rows
                        if row["role"] == "assistant" and row["parent_id"] in deleted_user_ids
                    }
                    - managed_ids
                )
        protected = generation_ids if allow_research_update else managed_ids
        messages = [
            m
            for m in messages
            if str(m["id"]) not in protected
            or (
                str(m["id"]) in generation_ids
                and _safe_generation_assistant_update(conn, thread_id, m)
            )
        ]
        # Repair research parents the prune deletes, walking the stored chain (dangling parent breaks import).
        # Batch-written ids are excluded so an authorized reparent is not overwritten.
        reseat_candidates = (managed_ids | unseen_ids) - pruned - {str(m["id"]) for m in messages}
        reseat_parents = {
            message_id: _surviving_parent_id(conn, thread_id, message_id, pruned)
            for message_id, stored_parent in _parents_of(conn, thread_id, reseat_candidates).items()
            if stored_parent is not None and stored_parent in pruned
        }
        # Same repair for the fork divider anchor, walked before the delete.
        reseat_boundary = _fork_boundary_reseat(conn, thread_id, pruned)
        _raise_if_chat_message_thread_conflicts(
            conn,
            thread_id,
            [m["id"] for m in messages],
        )
        deletions = _chat_attachment_deletions_for_messages(
            conn,
            thread_id,
            [m["id"] for m in messages],
        )
        reconciled_messages = [
            _reconcile_chat_message_uploads(m, *deletions.get(m["id"], (set(), 0)))
            for m in messages
        ]
        serialized_messages = [
            (
                m,
                json.dumps(m.get("content", [])),
                json.dumps(m.get("attachments")) if m.get("attachments") is not None else None,
            )
            for m in reconciled_messages
        ]
        # The client resends the whole thread per run start/end: skip rows stored unchanged (O(thread) rewrite + media hashing).
        current_rows = _stored_chat_message_values(
            conn, thread_id, [m["id"] for m in reconciled_messages]
        )
        changed_rows = []
        for m, content_json, attachments_json in serialized_messages:
            values = (
                m.get("parentId"),
                m["role"],
                content_json,
                attachments_json,
                json.dumps(m.get("metadata")) if m.get("metadata") is not None else None,
                int(m["createdAt"]),
            )
            if current_rows.get(m["id"]) == values:
                continue
            # Later duplicates compare against what this batch wrote, as the upsert would.
            current_rows[m["id"]] = values
            changed_rows.append((m["id"], values))
        conn.executemany(
            """
            INSERT INTO chat_messages
                (id, thread_id, parent_id, role, content_json, attachments_json, metadata_json, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                parent_id = excluded.parent_id,
                role = excluded.role,
                content_json = excluded.content_json,
                attachments_json = excluded.attachments_json,
                metadata_json = excluded.metadata_json,
                created_at = excluded.created_at
            WHERE excluded.thread_id = chat_messages.thread_id
            """,
            [(message_id, thread_id, *values) for message_id, values in changed_rows],
        )
        # A clean inventory already matches every unchanged row.
        for message_id, (_, _, content_json, attachments_json, _, _) in changed_rows:
            _replace_chat_attachment_inventory(
                conn,
                message_id,
                attachments_json,
                content_json,
            )
        if prune_missing:
            missing_ids = sorted(pruned)
            for start in range(0, len(missing_ids), _SQLITE_IN_CHUNK_SIZE):
                chunk = missing_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
                placeholders = ",".join("?" for _ in chunk)
                conn.execute(
                    f"DELETE FROM chat_messages WHERE thread_id = ? AND id IN ({placeholders})",
                    (thread_id, *chunk),
                )
            _reseat_protected_messages(conn, thread_id, reseat_parents)
            if reseat_boundary is not _NO_RESEAT:
                conn.execute(
                    "UPDATE chat_threads SET fork_boundary_message_id = ? WHERE id = ?",
                    (reseat_boundary, thread_id),
                )
            _recompute_chat_thread_updated_at(conn, thread_id)
        elif reconciled_messages:
            _bump_chat_thread_updated_at(
                conn,
                thread_id,
                max(int(m["createdAt"]) for m in reconciled_messages),
            )
        _mark_chat_attachment_inventory_clean(conn)
        if external_import_mark is not None:
            _record_external_import_mark(conn, external_import_mark)
        conn.commit()
        return list_chat_messages(thread_id)
    except (ChatMessageConflictError, ChatMessageProtectedError):
        conn.rollback()
        raise
    except sqlite3.Error:
        logger.exception("Failed to sync chat messages for thread %s", thread_id)
        conn.rollback()
        raise
    finally:
        conn.close()


def _parents_of(conn, thread_id: str, message_ids: set) -> dict:
    if not message_ids:
        return {}
    return {
        str(row["id"]): (str(row["parent_id"]) if row["parent_id"] else None)
        for row in conn.execute(
            "SELECT id, parent_id FROM chat_messages WHERE thread_id = ?",
            (thread_id,),
        ).fetchall()
        if str(row["id"]) in message_ids
    }


# Distinct from None, which is a boundary to clear: the thread kept none of its inherited history.
_NO_RESEAT = object()


def _fork_boundary_reseat(conn, thread_id: str, pruned: set):
    """Moves a fork's divider to the last surviving inherited message, or off; _NO_RESEAT leaves it
    alone."""
    if not pruned:
        return _NO_RESEAT
    row = conn.execute(
        "SELECT fork_boundary_message_id FROM chat_threads WHERE id = ?", (thread_id,)
    ).fetchone()
    boundary = row["fork_boundary_message_id"] if row is not None else None
    if boundary is None or boundary not in pruned:
        return _NO_RESEAT
    return _surviving_visible_ancestor(conn, thread_id, boundary, pruned)


def _surviving_visible_ancestor(
    conn: sqlite3.Connection, thread_id: str, message_id: str, pruned: set
) -> "str | None":
    """Skips ancestors that render as nothing, since a divider on one is lost; None if none remain."""
    seen = {message_id}
    candidate = _surviving_parent_id(conn, thread_id, message_id, pruned)
    while candidate is not None and candidate not in seen:
        seen.add(candidate)
        row = conn.execute(
            "SELECT role FROM chat_messages WHERE thread_id = ? AND id = ?",
            (thread_id, candidate),
        ).fetchone()
        if row is not None and row["role"] in _RENDERED_MESSAGE_ROLES:
            return candidate
        candidate = _surviving_parent_id(conn, thread_id, candidate, pruned)
    return None


def _reseat_protected_messages(conn, thread_id: str, reseat_parents: dict) -> None:
    """Repoints protected messages at a surviving ancestor, since a dangling parent breaks the thread."""
    for message_id, parent_id in reseat_parents.items():
        row = conn.execute(
            "SELECT parent_id, metadata_json FROM chat_messages WHERE thread_id = ? AND id = ?",
            (thread_id, message_id),
        ).fetchone()
        if row is None or (row["parent_id"] or None) == parent_id:
            continue
        metadata = _json_loads(row["metadata_json"], {})
        if not isinstance(metadata, dict):
            metadata = {}
        metadata["revision"] = _chat_message_revision(metadata) + 1
        conn.execute(
            "UPDATE chat_messages SET parent_id = ?, metadata_json = ? WHERE thread_id = ? AND id = ?",
            (parent_id, json.dumps(metadata), thread_id, message_id),
        )


_RESEARCH_LINK_KEYS = {
    "researchRunId",
    "researchRun",
    "researchStatus",
    "researchPlanRevision",
    "serverManaged",
}
_SERVER_MANAGED_LINK_KEYS = _RESEARCH_LINK_KEYS | {
    "generationRunId",
    "generationSeq",
    "generationStatus",
    "generationSettled",
}


def _detach_research_message_json(
    content_json: str, metadata_json: str | None
) -> tuple[str, str | None]:
    content = _json_loads(content_json, [])
    metadata = _json_loads(metadata_json, None)
    custom = metadata.get("custom") if isinstance(metadata, dict) else None
    linked = (
        isinstance(metadata, dict)
        and any(key in metadata for key in _SERVER_MANAGED_LINK_KEYS)
        or isinstance(custom, dict)
        and any(key in custom for key in _SERVER_MANAGED_LINK_KEYS)
        or isinstance(content, list)
        and any(
            isinstance(part, dict) and any(key in part for key in _SERVER_MANAGED_LINK_KEYS)
            for part in content
        )
    )
    if not linked:
        return content_json, metadata_json

    if isinstance(content, list):
        content = [
            {key: value for key, value in part.items() if key not in _SERVER_MANAGED_LINK_KEYS}
            if isinstance(part, dict)
            else part
            for part in content
        ]
    if isinstance(metadata, dict):
        metadata = {
            key: value for key, value in metadata.items() if key not in _SERVER_MANAGED_LINK_KEYS
        }
        custom = metadata.get("custom")
        if isinstance(custom, dict):
            metadata["custom"] = {
                key: value for key, value in custom.items() if key not in _SERVER_MANAGED_LINK_KEYS
            }
    return (
        json.dumps(content, ensure_ascii = False),
        json.dumps(metadata, ensure_ascii = False) if metadata is not None else None,
    )


class ChatForkActiveGenerationError(RuntimeError):
    """A durable generation prevents copying a settled chat."""


_FORK_TITLE_SUFFIX = re.compile(r"^(?P<base>.*?)\s*\((?P<n>\d+)\)\s*$", re.DOTALL)
# The frontend auto-titles only a chat still named this (runtime-provider generateTitle).
_DEFAULT_CHAT_TITLE = "New Chat"

# Mirrors threadMessageKind in thread-message-slot.ts, pinned by thread-message-slot.test.ts; only places the fork divider.
_RENDERED_MESSAGE_ROLES = frozenset({"user", "assistant"})


def _title_family(conn: sqlite3.Connection, base: str) -> list[tuple[str, str]]:
    """Every chat named `base` or `base (n)`, as (id, title)."""
    return [
        (row["id"], row["title"] or "")
        for row in conn.execute(
            "SELECT id, title FROM chat_threads WHERE title = ? OR title LIKE ? ESCAPE '\\'",
            (base, _like_escape(base) + " (%)"),
        )
    ]


def fork_base_of(src: Mapping) -> str:
    """Forks number from the stored base after the source changes; with no base, from the whole title, so a user-named Budget (2026) is not stripped."""
    stored = (src["fork_title_base"] or "").strip()
    return stored or (src["title"] or "").strip()


def _next_fork_title(conn: sqlite3.Connection, base: str) -> str:
    """Lowest free `base (n)`, n >= 1; picked under the write lock so forks cannot collide."""
    taken: set[int] = set()
    for _id, title in _title_family(conn, base):
        match = _FORK_TITLE_SUFFIX.match(title)
        if match and match.group("base").strip() == base:
            taken.add(int(match.group("n")))
    n = 1
    while n in taken:
        n += 1
    return f"{base} ({n})"


def fork_chat_thread(
    source_thread_id: str,
    branch_message_id: Optional[str],
    new_thread_id: str,
    created_at: int,
    id_factory,
) -> Optional[dict]:
    """Clones the thread and ancestor messages in one transaction, resetting both code-exec
    container ids."""
    from storage.research_runs_db import ACTIVE_STATUSES as active_research_statuses

    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_chat_attachment_inventory_current(conn)
        _raise_if_chat_thread_deleted(conn, new_thread_id)
        src = conn.execute(
            "SELECT * FROM chat_threads WHERE id = ?", (source_thread_id,)
        ).fetchone()
        if src is None:
            conn.rollback()
            return None
        # admission and copying share the write lock, including the gap before supervisor registration.
        research_status_placeholders = ",".join("?" for _ in active_research_statuses)
        if (
            _active_chat_generation_run_ids(conn, {source_thread_id})
            or conn.execute(
                f"SELECT 1 FROM research_runs WHERE thread_id = ? "
                f"AND status IN ({research_status_placeholders}) LIMIT 1",
                (source_thread_id, *sorted(active_research_statuses)),
            ).fetchone()
            is not None
        ):
            raise ChatForkActiveGenerationError(
                "This chat is still generating. Fork it once it finishes."
            )
        rows = conn.execute(
            """SELECT * FROM chat_messages WHERE thread_id = ?
               ORDER BY created_at,
                 CASE role WHEN 'system' THEN 0 WHEN 'user' THEN 1
                           WHEN 'assistant' THEN 2 ELSE 99 END,
                 id""",
            (source_thread_id,),
        ).fetchall()
        by_id = {row["id"]: row for row in rows}
        branch_row = (
            (rows[-1] if rows else None)
            if branch_message_id is None
            else by_id.get(branch_message_id)
        )
        if branch_row is None:
            conn.rollback()
            return None
        branch_message_id = branch_row["id"]
        # match the runtime's sequential legacy prefix while preserving recorded branches and later roots.
        parents: dict[str, Optional[str]] = {}
        previous_id = None
        saw_recorded_parent = False
        for row in rows:
            parent = row["parent_id"]
            parents[row["id"]] = (
                previous_id if parent is None and not saw_recorded_parent else parent
            )
            if parent is not None:
                saw_recorded_parent = True
            previous_id = row["id"]
        ancestry: list[sqlite3.Row] = []
        cursor_row = branch_row
        seen: set[str] = set()
        while cursor_row is not None and cursor_row["id"] not in seen:
            ancestry.append(cursor_row)
            seen.add(cursor_row["id"])
            cursor_row = by_id.get(parents[cursor_row["id"]])
        ancestry.reverse()  # root .. branch msg
        id_map: dict[str, str] = {row["id"]: id_factory() for row in ancestry}
        src_dict = dict(src)
        # Read under the write lock so another tab's rename cannot race the fork number.
        base = fork_base_of(src)
        # An untitled chat's fork stays untitled so its first reply still names it.
        if base in ("", _DEFAULT_CHAT_TITLE):
            title, base = _DEFAULT_CHAT_TITLE, None
        else:
            title = _next_fork_title(conn, base)
        # Divider anchor: last rendered inherited message (system rows don't render); copies get
        # fresh ids.
        boundary_row = next(
            (row for row in reversed(ancestry) if row["role"] in _RENDERED_MESSAGE_ROLES), None
        )
        boundary_message_id = id_map[boundary_row["id"]] if boundary_row is not None else None
        conn.execute(
            """
            INSERT INTO chat_threads
                (id, title, model_type, model_id, model_gguf_variant, pair_id, project_id, archived, created_at,
                 openai_code_exec_container_id, anthropic_code_exec_container_id,
                 forked_from_thread_id, forked_from_message_id, fork_boundary_message_id,
                 fork_title_base, settings_json)
            VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, NULL, NULL, ?, ?, ?, ?, ?)
            """,
            (
                new_thread_id,
                title,
                src_dict["model_type"],
                src_dict.get("model_id") or "",
                src_dict.get("model_gguf_variant"),
                None,  # pairId: forks always standalone (compare-mode disabled v1)
                src_dict.get("project_id"),
                int(created_at),
                source_thread_id,
                branch_message_id,
                boundary_message_id,
                base,
                src_dict.get("settings_json"),
            ),
        )
        fork_messages = []
        for row in ancestry:
            content_json, metadata_json = _detach_research_message_json(
                row["content_json"], row["metadata_json"]
            )
            fork_messages.append(
                (
                    id_map[row["id"]],
                    new_thread_id,
                    id_map.get(parents[row["id"]]),
                    row["role"],
                    content_json,
                    row["attachments_json"],
                    metadata_json,
                    int(row["created_at"]),
                )
            )
        conn.executemany(
            """
            INSERT INTO chat_messages
                (id, thread_id, parent_id, role, content_json, attachments_json,
                 metadata_json, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            fork_messages,
        )
        for row in ancestry:
            _replace_chat_attachment_inventory(
                conn,
                id_map[row["id"]],
                row["attachments_json"],
                row["content_json"],
            )
        _mark_chat_attachment_inventory_clean(conn)
        conn.commit()
        thread_row = conn.execute(
            "SELECT * FROM chat_threads WHERE id = ?", (new_thread_id,)
        ).fetchone()
        return _chat_thread_from_row(thread_row) if thread_row is not None else None
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def remap_chat_thread_document_ids(thread_id: str, document_ids: dict[str, str]) -> None:
    if not document_ids:
        return
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_chat_attachment_inventory_current(conn)
        rows = conn.execute(
            "SELECT id, content_json, attachments_json, metadata_json FROM chat_messages "
            "WHERE thread_id = ?",
            (thread_id,),
        ).fetchall()
        for row in rows:
            content_json, metadata_json = row["content_json"], row["metadata_json"]
            for old, new in document_ids.items():
                content_json = content_json.replace(old, new)
                metadata_json = metadata_json and metadata_json.replace(old, new)
            if (content_json, metadata_json) != (row["content_json"], row["metadata_json"]):
                conn.execute(
                    "UPDATE chat_messages SET content_json = ?, metadata_json = ? "
                    "WHERE thread_id = ? AND id = ?",
                    (content_json, metadata_json, thread_id, row["id"]),
                )
                _replace_chat_attachment_inventory(
                    conn, row["id"], row["attachments_json"], content_json
                )
        _mark_chat_attachment_inventory_clean(conn)
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def count_forks_for_message(thread_id: str, message_id: str) -> int:
    conn = get_connection()
    try:
        row = conn.execute(
            """
            SELECT COUNT(*) FROM chat_threads
            WHERE forked_from_thread_id = ? AND forked_from_message_id = ?
            """,
            (thread_id, message_id),
        ).fetchone()
        return int(row[0]) if row is not None else 0
    finally:
        conn.close()


def next_message_created_at(thread_id: str, message_id: str) -> Optional[int]:
    """created_at of the thread's first message after `message_id`; None when it is the tip."""
    conn = get_connection()
    try:
        row = conn.execute(
            """
            SELECT MIN(created_at) FROM chat_messages
            WHERE thread_id = ? AND created_at > (
                SELECT created_at FROM chat_messages WHERE thread_id = ? AND id = ?
            )
            """,
            (thread_id, thread_id, message_id),
        ).fetchone()
        return int(row[0]) if row is not None and row[0] is not None else None
    finally:
        conn.close()


def chat_thread_has_messages(thread_id: str) -> bool:
    """Existence check: incognito chats are never written, so this tells archivable threads apart."""
    conn = get_connection()
    try:
        row = conn.execute(
            "SELECT 1 FROM chat_messages WHERE thread_id = ? LIMIT 1", (thread_id,)
        ).fetchone()
        return row is not None
    finally:
        conn.close()


def fork_counts_for_thread(thread_id: str) -> dict[str, int]:
    conn = get_connection()
    try:
        rows = conn.execute(
            """
            SELECT forked_from_message_id, COUNT(*) FROM chat_threads
            WHERE forked_from_thread_id = ? AND forked_from_message_id IS NOT NULL
            GROUP BY forked_from_message_id
            """,
            (thread_id,),
        ).fetchall()
        return {str(row[0]): int(row[1]) for row in rows}
    finally:
        conn.close()


def chat_thread_has_context_truncation(thread_id: str) -> bool:
    """Whether any message in the thread records a context fit (metadata `contextTruncation`), without loading the transcript."""
    conn = get_connection()
    try:
        row = conn.execute(
            "SELECT 1 FROM chat_messages WHERE thread_id = ? "
            "AND instr(metadata_json, 'contextTruncation') > 0 LIMIT 1",
            (thread_id,),
        ).fetchone()
        return row is not None
    finally:
        conn.close()


def list_chat_messages(thread_id: str) -> list[dict]:
    conn = get_connection()
    try:
        rows = conn.execute(
            """
            SELECT * FROM chat_messages
            WHERE thread_id = ?
            ORDER BY created_at ASC, id ASC
            """,
            (thread_id,),
        ).fetchall()
        return [_chat_message_from_row(row) for row in rows]
    finally:
        conn.close()


def get_chat_message(thread_id: str, message_id: str) -> Optional[dict]:
    conn = get_connection()
    try:
        row = conn.execute(
            """
            SELECT * FROM chat_messages
            WHERE thread_id = ? AND id = ?
            """,
            (thread_id, message_id),
        ).fetchone()
        return _chat_message_from_row(row) if row is not None else None
    finally:
        conn.close()


def _blob_part_base64_len(part: dict) -> int:
    image = part.get("image")
    if isinstance(image, str) and image[:5].lower() == "data:":
        return len(image.rsplit(",", 1)[-1])
    audio = part.get("audio")
    if isinstance(audio, str) and _is_locally_stored_blob(audio):
        return len(audio.rsplit(",", 1)[-1])
    if isinstance(audio, dict):
        data = audio.get("data")
        if isinstance(data, str) and _is_locally_stored_blob(data):
            return len(data)
    data = part.get("data")
    if part.get("type") == "file" and isinstance(data, str) and _is_locally_stored_blob(data):
        return len(data.rsplit(",", 1)[-1])
    return 0


def _attachment_content_parts(attachment: dict) -> list[dict]:
    content = attachment.get("content")
    if not isinstance(content, list):
        return []
    return [part for part in content if isinstance(part, dict)]


def _chat_attachment_text_bytes(attachment: dict) -> Optional[int]:
    """UTF-8 size of a kept original's extracted text; None without an original."""
    if chat_originals.attachment_sha256(attachment) is None:
        return None
    return sum(
        len(part["text"].encode("utf-8", errors = "ignore"))
        for part in _attachment_content_parts(attachment)
        if isinstance(part.get("text"), str)
    )


def _chat_attachment_size_bytes(attachment: dict) -> Optional[int]:
    """Base64 media counts at 3/4 of encoded length; a document with its original kept counts that file."""
    original_size = chat_originals.attachment_size(attachment)
    if original_size is not None:
        return original_size
    total = 0
    found = False
    for part in _attachment_content_parts(attachment):
        blob_len = _blob_part_base64_len(part)
        if blob_len > 0:
            total += (blob_len * 3) // 4
            found = True
            continue
        text = part.get("text")
        if isinstance(text, str) and text:
            total += len(text.encode("utf-8", errors = "ignore"))
            found = True
    return total if found else None


_AUDIO_FORMAT_TYPES = {
    "mp3": "audio/mpeg",
    "wav": "audio/wav",
    "ogg": "audio/ogg",
    "flac": "audio/flac",
}


def _content_part_audio_type(audio: Any) -> Optional[str]:
    """Audio type from data URL, format, or the bytes' header; bare base64 carries neither."""
    data = audio
    if isinstance(audio, dict):
        known = _AUDIO_FORMAT_TYPES.get(str(audio.get("format") or "").lower())
        if known:
            return known
        data = audio.get("data")
    if not isinstance(data, str):
        return None
    data = data.strip()
    if data[:5].lower() == "data:":
        header, _, data = data.partition(",")
        declared = header[5:].split(";", 1)[0].strip().lower()
        if declared.startswith("audio/"):
            return declared
    try:
        head = base64.b64decode(data[:16])
    except (binascii.Error, ValueError):
        return None
    if head.startswith(b"RIFF") and head[8:12] == b"WAVE":
        return "audio/wav"
    if head.startswith(b"ID3") or (len(head) > 1 and head[0] == 0xFF and head[1] & 0xE0 == 0xE0):
        return "audio/mpeg"
    if head.startswith(b"OggS"):
        return "audio/ogg"
    if head.startswith(b"fLaC"):
        return "audio/flac"
    return None


def _content_part_attachments(content_json: Optional[str]) -> list[dict]:
    """Managed blobs in content_json; identical blobs share one id, so deleting one removes all copies."""
    content = _json_loads(content_json, None)
    if not isinstance(content, list):
        return []
    out: list[dict] = []
    seen: set[str] = set()
    for part in content:
        if not isinstance(part, dict):
            continue
        attachment_id = _content_part_id(part)
        payload = _managed_content_part_payload(part)
        if attachment_id is None or payload is None or attachment_id in seen:
            continue
        seen.add(attachment_id)
        kind, value = payload
        content_type = None
        part_name = part.get("name")
        if kind == "audio":
            content_type = _content_part_audio_type(value)
        elif isinstance(value, str) and value[:5].lower() == "data:":
            content_type = value[5:].split(";", 1)[0].split(",", 1)[0] or None
        out.append(
            {
                "id": attachment_id,
                "type": kind,
                "name": part_name
                if isinstance(part_name, str) and part_name
                else ("Chat image" if kind == "image" else "Chat audio"),
                "contentType": content_type,
                "content": [part],
            }
        )
    return out


def list_chat_attachments_page(
    limit: int = 50, offset: int = 0
) -> tuple[list[dict], Optional[int]]:
    if not 1 <= limit <= 100:
        raise ValueError("limit must be between 1 and 100")
    if offset < 0:
        raise ValueError("offset must be non-negative")

    conn = get_connection()
    try:
        _ensure_chat_attachment_inventory_current(conn)
        rows = conn.execute(
            """
            SELECT i.attachment_id, i.name, i.type, i.content_type,
                   i.size_bytes, i.original_sha256, i.text_bytes, m.id AS message_id, m.thread_id,
                   m.created_at, t.title AS thread_title, t.pair_id
            FROM chat_attachment_inventory i
            JOIN chat_messages m ON m.id = i.message_id
            LEFT JOIN chat_threads t ON t.id = m.thread_id
            ORDER BY m.created_at DESC, m.id ASC, i.attachment_id ASC
            LIMIT ? OFFSET ?
            """,
            (limit + 1, offset),
        ).fetchall()
    finally:
        conn.close()

    has_more = len(rows) > limit
    page_rows = rows[:limit]
    originals = chat_originals.originals_dir()
    attachments = [
        {
            "id": row["attachment_id"],
            "messageId": row["message_id"],
            "threadId": row["thread_id"],
            "pairId": row["pair_id"],
            "threadTitle": row["thread_title"],
            "name": row["name"],
            "type": row["type"],
            "contentType": row["content_type"],
            "sizeBytes": row["size_bytes"],
            "originalSha256": row["original_sha256"],
            "textBytes": row["text_bytes"],
            "hasOriginal": bool(row["original_sha256"])
            and (originals / row["original_sha256"]).is_file(),
            "createdAt": row["created_at"],
        }
        for row in page_rows
    ]
    return attachments, offset + limit if has_more else None


def referenced_chat_original_hashes() -> set[str]:
    """Every stored original a chat attachment still points at."""
    conn = get_connection()
    try:
        _ensure_chat_attachment_inventory_current(conn)
        rows = conn.execute(
            "SELECT DISTINCT original_sha256 FROM chat_attachment_inventory"
            " WHERE original_sha256 IS NOT NULL"
        ).fetchall()
    finally:
        conn.close()
    return {row[0] for row in rows}


@contextmanager
def chat_original_unreferenced(sha256: str) -> Iterator[bool]:
    """Whether no attachment references ``sha256``, with the write lock held until the block
    ends: a message that would reference it waits, so it cannot do so while the file is removed."""
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_chat_attachment_inventory_current(conn)
        row = conn.execute(
            "SELECT 1 FROM chat_attachment_inventory WHERE original_sha256 = ? LIMIT 1", (sha256,)
        ).fetchone()
        yield row is None
        conn.commit()
    except BaseException:
        conn.rollback()
        raise
    finally:
        conn.close()


def list_chat_attachments() -> list[dict]:
    """Compatibility helper returning the full normalized inventory."""
    attachments: list[dict] = []
    offset = 0
    while True:
        page, next_offset = list_chat_attachments_page(limit = 100, offset = offset)
        attachments.extend(page)
        if next_offset is None:
            return attachments
        offset = next_offset


def get_chat_attachment(message_id: str, attachment_id: str) -> Optional[dict]:
    conn = get_connection()
    try:
        row = conn.execute(
            """
            SELECT message.attachments_json, message.content_json,
                   EXISTS(
                       SELECT 1 FROM chat_attachment_tombstones tombstone
                       WHERE tombstone.thread_id = message.thread_id
                         AND tombstone.message_id = message.id
                         AND tombstone.attachment_id = ?
                   ) AS tombstoned
            FROM chat_messages message
            WHERE message.id = ?
            """,
            (attachment_id, message_id),
        ).fetchone()
    finally:
        conn.close()
    if row is None or row["tombstoned"]:
        return None
    attachments = _json_loads(row["attachments_json"], None)
    if isinstance(attachments, list):
        for attachment in attachments:
            if isinstance(attachment, dict) and str(attachment.get("id") or "") == attachment_id:
                return attachment
    if attachment_id.startswith(_CONTENT_PART_ID_PREFIX):
        for attachment in _content_part_attachments(row["content_json"]):
            if attachment["id"] == attachment_id:
                return attachment
    return None


def _record_chat_attachment_tombstone(
    conn: sqlite3.Connection, thread_id: str, message_id: str, attachment_id: str
) -> None:
    conn.execute(
        """
        INSERT INTO chat_attachment_tombstones
            (thread_id, message_id, attachment_id, deleted_at)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(thread_id, message_id, attachment_id) DO UPDATE SET
            deleted_at = excluded.deleted_at
        """,
        (
            thread_id,
            message_id,
            attachment_id,
            int(datetime.now(timezone.utc).timestamp() * 1000),
        ),
    )


def delete_chat_attachment(message_id: str, attachment_id: str) -> bool:
    """Leaves a tombstone while the thread exists, so a recreated message cannot restore the upload;
    an id colliding with a content-blob id deletes both as one managed item"""
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        _ensure_chat_attachment_inventory_current(conn)
        row = conn.execute(
            """
            SELECT thread_id, attachments_json, content_json, metadata_json
            FROM chat_messages WHERE id = ?
            """,
            (message_id,),
        ).fetchone()
        if row is None:
            conn.rollback()
            return False
        protected_message_ids = _server_managed_message_ids(
            conn, str(row["thread_id"])
        ) - _terminal_generation_message_ids(conn, str(row["thread_id"]))
        if str(message_id) in protected_message_ids:
            conn.rollback()
            raise ChatMessageProtectedError(
                "Research prompts and responses are server-managed and cannot be edited"
            )

        attachments = _json_loads(row["attachments_json"], None)
        updated_attachments_json = row["attachments_json"]
        deleted_attachment = False
        if isinstance(attachments, list):
            remaining_attachments = [
                attachment
                for attachment in attachments
                if not (
                    isinstance(attachment, dict)
                    and str(attachment.get("id") or "") == attachment_id
                )
            ]
            deleted_attachment = len(remaining_attachments) != len(attachments)
            if deleted_attachment:
                updated_attachments_json = json.dumps(remaining_attachments)

        content = _json_loads(row["content_json"], None)
        updated_content_json = row["content_json"]
        deleted_content = False
        if attachment_id.startswith(_CONTENT_PART_ID_PREFIX) and isinstance(content, list):
            remaining_content = [
                part
                for part in content
                if not (isinstance(part, dict) and _content_part_id(part) == attachment_id)
            ]
            deleted_content = len(remaining_content) != len(content)
            if deleted_content:
                updated_content_json = json.dumps(remaining_content)

        if not deleted_attachment and not deleted_content:
            conn.rollback()
            return False
        metadata = _json_loads(row["metadata_json"], {})
        if not isinstance(metadata, dict):
            metadata = {}
        metadata["revision"] = _chat_message_revision(metadata) + 1
        conn.execute(
            """
            UPDATE chat_messages
            SET attachments_json = ?, content_json = ?, metadata_json = ?
            WHERE id = ?
            """,
            (updated_attachments_json, updated_content_json, json.dumps(metadata), message_id),
        )
        _record_chat_attachment_tombstone(
            conn,
            row["thread_id"],
            message_id,
            attachment_id,
        )
        _replace_chat_attachment_inventory(
            conn,
            message_id,
            updated_attachments_json,
            updated_content_json,
        )
        _mark_chat_attachment_inventory_clean(conn)
        conn.commit()
        return True
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def count_chat_messages_for_threads(thread_ids: list[str]) -> dict[str, int]:
    """Counts user and assistant messages on the newest branch of each thread, mirroring summarizeChatMessages; unknown ids count 0."""
    unique_thread_ids = list(dict.fromkeys(thread_ids))
    rows_by_thread: dict[str, list[tuple[str, Optional[str], str, int]]] = {
        tid: [] for tid in unique_thread_ids
    }
    if not unique_thread_ids:
        return {}
    conn = get_connection()
    try:
        for start in range(0, len(unique_thread_ids), _SQLITE_IN_CHUNK_SIZE):
            chunk = unique_thread_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
            placeholders = ",".join("?" for _ in chunk)
            for row in conn.execute(
                f"""
                SELECT thread_id, id, parent_id, role, created_at FROM chat_messages
                WHERE thread_id IN ({placeholders})
                ORDER BY created_at ASC, id ASC
                """,
                chunk,
            ):
                rows_by_thread[row[0]].append((row[1], row[2], row[3], row[4]))
    finally:
        conn.close()
    counts: dict[str, int] = {}
    for tid, rows in rows_by_thread.items():
        path = rows
        if any(parent for _, parent, _, _ in rows):
            by_id = {row[0]: row for row in rows}
            # First newest message, matching the frontend.
            at = max(rows, key = lambda row: row[3]) if rows else None
            path, seen = [], set()
            while at is not None and at[0] not in seen:
                seen.add(at[0])
                path.append(at)
                at = by_id.get(at[1]) if at[1] else None
        counts[tid] = sum(1 for _, _, role, _ in path if role in ("user", "assistant"))
    return counts


def list_chat_messages_for_threads(
    thread_ids: list[str], *, include_metadata: bool = True
) -> list[dict]:
    """include_metadata=False skips reading and decoding metadata_json (the search view drops it)."""
    if not thread_ids:
        return []
    columns = (
        "*"
        if include_metadata
        else "id, thread_id, parent_id, role, content_json, attachments_json, created_at"
    )
    unique_thread_ids = list(dict.fromkeys(thread_ids))
    messages: list[dict] = []
    conn = get_connection()
    try:
        for start in range(0, len(unique_thread_ids), _SQLITE_IN_CHUNK_SIZE):
            chunk = unique_thread_ids[start : start + _SQLITE_IN_CHUNK_SIZE]
            placeholders = ",".join("?" for _ in chunk)
            rows = conn.execute(
                f"""
                SELECT {columns} FROM chat_messages
                WHERE thread_id IN ({placeholders})
                ORDER BY created_at ASC, id ASC
                """,
                chunk,
            ).fetchall()
            messages.extend(_chat_message_from_row(row) for row in rows)
        return sorted(
            messages,
            key = lambda message: (message["createdAt"], message["id"]),
        )
    finally:
        conn.close()


def get_app_setting(key: str, fallback = None):
    conn = get_connection()
    try:
        row = conn.execute("SELECT value_json FROM app_settings WHERE key = ?", (key,)).fetchone()
        if row is None:
            return fallback
        return _json_loads(row["value_json"], fallback)
    finally:
        conn.close()


def get_app_settings(keys: list[str]) -> dict[str, Any]:
    """Reads keys from one snapshot so a concurrent multi-key upsert is never seen half-applied."""
    unique = list(dict.fromkeys(keys))
    if not unique:
        return {}
    conn = get_connection()
    try:
        placeholders = ",".join("?" for _ in unique)
        rows = conn.execute(
            f"SELECT key, value_json FROM app_settings WHERE key IN ({placeholders})",
            unique,
        ).fetchall()
        return {row["key"]: _json_loads(row["value_json"], None) for row in rows}
    finally:
        conn.close()


def compare_and_set_app_setting(
    key: str,
    expected: Any,
    value: Any,
    *,
    absent: tuple[str, ...] = (),
) -> bool:
    """Writes value only while key holds expected and no absent key is set, inside one BEGIN IMMEDIATE so a racing save is not reverted; returns if it wrote."""
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        row = conn.execute("SELECT value_json FROM app_settings WHERE key = ?", (key,)).fetchone()
        current = _json_loads(row["value_json"], None) if row is not None else None
        present = (
            absent
            and conn.execute(
                f"SELECT 1 FROM app_settings WHERE key IN ({', '.join('?' * len(absent))}) LIMIT 1",
                absent,
            ).fetchone()
        )
        if current != expected or present:
            conn.rollback()
            return False
        conn.execute(
            """
            INSERT INTO app_settings (key, value_json, updated_at)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json = excluded.value_json,
                updated_at = excluded.updated_at
            """,
            (key, json.dumps(value), datetime.now(timezone.utc).isoformat()),
        )
        conn.commit()
        return True
    finally:
        conn.close()


def upsert_app_settings(settings: dict[str, Any], *, read_back: bool = True) -> dict[str, Any]:
    if not settings:
        return {}
    conn = get_connection()
    committed = False
    try:
        now = datetime.now(timezone.utc).isoformat()
        conn.executemany(
            """
            INSERT INTO app_settings (key, value_json, updated_at)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json = excluded.value_json,
                updated_at = excluded.updated_at
            """,
            [(key, json.dumps(value), now) for key, value in settings.items()],
        )
        conn.commit()
        committed = True
        if not read_back:
            return settings
        rows = conn.execute("SELECT key, value_json FROM app_settings ORDER BY key").fetchall()
        return {row["key"]: _json_loads(row["value_json"], None) for row in rows}
    finally:
        try:
            conn.close()
        except Exception:
            if not committed or read_back:
                raise


def upsert_app_setting_map_entry(
    key: str,
    entry_key: str,
    entry_value: dict[str, Any] | None,
    *,
    fill_absent_fields: bool = False,
    coupled_fields: tuple[tuple[str, ...], ...] = (),
    keep_first_writer: bool = False,
    ambiguous_field: str | None = None,
    delete_if_entry_equals: dict[str, Any] | None = None,
) -> dict[str, Any]:
    """BEGIN IMMEDIATE so sub-entry writers cannot drop each other; fill_absent_fields only adds missing fields and skips coupled groups whole."""
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        row = conn.execute("SELECT value_json FROM app_settings WHERE key = ?", (key,)).fetchone()
        current = _json_loads(row["value_json"], {}) if row else {}
        if not isinstance(current, dict):
            current = {}
        if fill_absent_fields:
            if not entry_value:
                conn.rollback()
                return current
            stored = current.get(entry_key)
            if isinstance(stored, dict):
                incoming = entry_value
                for group in coupled_fields:
                    if any(field in stored for field in group):
                        incoming = {k: v for k, v in incoming.items() if k not in group}
                # Stored values win field by field, so this only adds.
                merged = {**incoming, **stored}
                if merged == stored:
                    conn.rollback()
                    return current
                current[entry_key] = merged
            else:
                current[entry_key] = entry_value
        elif keep_first_writer and entry_value and entry_key in current:
            stored = current[entry_key]
            if not isinstance(stored, dict) or ambiguous_field is None:
                conn.rollback()
                return current
            if stored.get(ambiguous_field) in (None, entry_value.get(ambiguous_field)):
                conn.rollback()
                return current
            current[entry_key] = {**stored, ambiguous_field: None}
        elif entry_value:
            current[entry_key] = entry_value
        else:
            if delete_if_entry_equals is not None and (
                current.get(entry_key) != delete_if_entry_equals
            ):
                conn.rollback()
                return current
            current.pop(entry_key, None)
        now = datetime.now(timezone.utc).isoformat()
        conn.execute(
            """
            INSERT INTO app_settings (key, value_json, updated_at)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json = excluded.value_json,
                updated_at = excluded.updated_at
            """,
            (key, json.dumps(current), now),
        )
        conn.commit()
        return current
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


_CHAT_PINS_KEY = "pinnedChatIds"
MAX_PINNED_CHAT_IDS = 10_000
MAX_PINNED_CHAT_ID_LENGTH = 512


def _normalized_chat_pin_ids(value: Any) -> list[str]:
    if not isinstance(value, list):
        return []
    return list(
        dict.fromkeys(
            item
            for item in value
            if isinstance(item, str)
            and 0 < len(item) <= MAX_PINNED_CHAT_ID_LENGTH
            and not any(0xD800 <= ord(character) <= 0xDFFF for character in item)
        )
    )


def _existing_chat_pin_ids(conn: sqlite3.Connection, ids: list[str]) -> list[str]:
    """Keep sidebar IDs that still have a thread, including archived comparisons."""
    present: set[str] = set()
    chunk_size = _SQLITE_IN_CHUNK_SIZE // 2
    for start in range(0, len(ids), chunk_size):
        chunk = ids[start : start + chunk_size]
        placeholders = ",".join("?" for _ in chunk)
        rows = conn.execute(
            f"""SELECT id FROM chat_threads
                WHERE id IN ({placeholders}) AND (pair_id IS NULL OR pair_id = '')
                UNION SELECT pair_id AS id FROM chat_threads WHERE pair_id IN ({placeholders})""",
            (*chunk, *chunk),
        ).fetchall()
        present.update(row["id"] for row in rows)
    return [id for id in ids if id in present][:MAX_PINNED_CHAT_IDS]


def _store_chat_pins_in_conn(conn: sqlite3.Connection, ids: list[str]) -> None:
    conn.execute(
        """INSERT INTO chat_settings (key, value_json, updated_at) VALUES (?, ?, ?)
           ON CONFLICT(key) DO UPDATE SET
               value_json = excluded.value_json, updated_at = excluded.updated_at""",
        (_CHAT_PINS_KEY, json.dumps(ids), datetime.now(timezone.utc).isoformat()),
    )


def _load_chat_pins_in_conn(
    conn: sqlite3.Connection,
    *,
    repair: bool = False,
    remember_removed: bool = False,
) -> tuple[Optional[list[str]], bool]:
    """None means genuinely absent; malformed data becomes a recoverable empty list."""
    row = conn.execute(
        "SELECT value_json FROM chat_settings WHERE key = ?", (_CHAT_PINS_KEY,)
    ).fetchone()
    if row is None:
        return None, False
    value = _json_loads(row["value_json"], None)
    normalized = _normalized_chat_pin_ids(value)
    pins = _existing_chat_pin_ids(conn, normalized)
    dirty = value != pins
    if repair and dirty:
        if value != normalized:
            # Retain the damaged value for recovery; never quarantine an ordinary orphan.
            conn.execute(
                """INSERT INTO chat_settings_quarantine
                   (key, value_json, reason, quarantined_at) VALUES (?, ?, ?, ?)""",
                (
                    _CHAT_PINS_KEY,
                    row["value_json"],
                    "invalid_chat_pins",
                    datetime.now(timezone.utc).isoformat(),
                ),
            )
        if remember_removed:
            remaining = set(pins)
            conn.executemany(
                "INSERT OR IGNORE INTO chat_pin_tombstones (id) VALUES (?)",
                [(id,) for id in normalized if id not in remaining],
            )
        _store_chat_pins_in_conn(conn, pins)
    return pins, dirty


def get_chat_pins() -> Optional[list[str]]:
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        pins, dirty = _load_chat_pins_in_conn(conn)
        if dirty:
            # Re-read under the write lock so a pin committed since the first read survives.
            conn.execute("BEGIN IMMEDIATE")
            pins, _ = _load_chat_pins_in_conn(conn, repair = True)
            conn.commit()
        return pins
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def migrate_chat_pins(ids: list[str]) -> Optional[list[str]]:
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        conn.execute("BEGIN IMMEDIATE")
        current, _ = _load_chat_pins_in_conn(conn, repair = True)
        incoming = _existing_chat_pin_ids(conn, _normalized_chat_pin_ids(ids))
        blocked: set[str] = set()
        for start in range(0, len(incoming), _SQLITE_IN_CHUNK_SIZE):
            chunk = incoming[start : start + _SQLITE_IN_CHUNK_SIZE]
            placeholders = ",".join("?" for _ in chunk)
            blocked.update(
                row["id"]
                for row in conn.execute(
                    f"SELECT id FROM chat_pin_tombstones WHERE id IN ({placeholders})", chunk
                )
            )
        present = set(current or [])
        additions = [id for id in incoming if id not in present and id not in blocked]
        pins = [*(current or []), *additions][:MAX_PINNED_CHAT_IDS]
        if pins != (current or []):
            _store_chat_pins_in_conn(conn, pins)
            current = pins
        conn.commit()
        return current
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def _record_chat_pin_change(
    conn: sqlite3.Connection,
    previous: Any,
    ids: list[str],
    explicit_unpins: Iterable[str] = (),
) -> None:
    old = set(_normalized_chat_pin_ids(previous))
    present = set(ids)
    # An unpin can predate legacy import creating the row; record it though it removes nothing.
    removed = (old - present) | set(explicit_unpins)
    conn.executemany(
        "INSERT OR IGNORE INTO chat_pin_tombstones (id) VALUES (?)",
        [(id,) for id in removed],
    )
    # Only an explicit pin write clears an unpin; migration never calls this helper.
    conn.executemany(
        "DELETE FROM chat_pin_tombstones WHERE id = ?", [(id,) for id in ids if id not in old]
    )


def list_chat_settings() -> dict[str, Any]:
    conn = get_connection()
    try:
        rows = conn.execute("SELECT key, value_json FROM chat_settings ORDER BY key").fetchall()
        settings: dict[str, Any] = {}
        for row in rows:
            settings[row["key"]] = _json_loads(row["value_json"], None)
        return settings
    finally:
        conn.close()


def upsert_chat_settings(settings: dict[str, Any]) -> dict[str, Any]:
    if not settings:
        return list_chat_settings()
    conn = get_connection()
    try:
        now = datetime.now(timezone.utc).isoformat()
        conn.executemany(
            """
            INSERT INTO chat_settings (key, value_json, updated_at)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json = excluded.value_json,
                updated_at = excluded.updated_at
            """,
            [(key, json.dumps(value), now) for key, value in settings.items()],
        )
        conn.commit()
        return list_chat_settings()
    finally:
        conn.close()


# Discriminated unions: merging a thread pick into a kb one would keep kbId.
_ATOMIC_SETTING_KEYS = frozenset({"ragSource"})


def _deep_merge_settings(current: dict[str, Any], updates: dict[str, Any]) -> dict[str, Any]:
    merged = dict(current)
    for key, value in updates.items():
        current_value = merged.get(key)
        if (
            key not in _ATOMIC_SETTING_KEYS
            and isinstance(current_value, dict)
            and isinstance(value, dict)
        ):
            merged[key] = _deep_merge_settings(current_value, value)
        else:
            merged[key] = value
    return merged


# A command, not a setting: per model id, the remembered keys dropped before the rest of the patch
# merges, since a deep merge can only add or overwrite (#14068). Keys, not rows, so a concurrent edit
# to a key the command does not name survives.
_FORGET_PARAMS_KEY = "forgetInferenceParamsByModel"


def _merge_settings_updates(current: dict[str, Any], updates: dict[str, Any]) -> dict[str, Any]:
    updates = dict(updates)
    forget = updates.pop(_FORGET_PARAMS_KEY, None) or {}
    by_model = current.get("inferenceParamsByModel")
    if forget and isinstance(by_model, dict):
        pruned = dict(by_model)
        for model_id, keys in forget.items():
            entry = pruned.get(model_id)
            if not isinstance(entry, dict):
                continue
            rest = {k: v for k, v in entry.items() if k not in keys}
            if rest:
                pruned[model_id] = rest
            else:
                del pruned[model_id]
        current = {**current, "inferenceParamsByModel": pruned}
    return _deep_merge_settings(current, updates)


def upsert_chat_settings_merge(updates: dict[str, Any]) -> dict[str, Any]:
    """Atomic read-merge-write under BEGIN IMMEDIATE so concurrent writers
    cannot drop each other's updates."""
    if not updates:
        return list_chat_settings()
    conn = get_connection()
    try:
        conn.execute("BEGIN IMMEDIATE")
        if _CHAT_PINS_KEY in updates:
            _load_chat_pins_in_conn(conn, repair = True)
        current, corrupt = _load_chat_settings_for_merge(conn)
        # An atomic key carries its whole value, so it repairs a quarantined row without a base.
        unsafe_partial_keys = [
            key
            for key, value in updates.items()
            if key in corrupt and isinstance(value, dict) and key not in _ATOMIC_SETTING_KEYS
        ]
        if unsafe_partial_keys:
            conn.commit()
            keys = ", ".join(sorted(unsafe_partial_keys))
            raise CorruptSettingsError(
                f"Cannot apply partial settings patch to corrupt key(s): {keys}"
            )
        if _CHAT_PINS_KEY in updates:
            updates = {
                **updates,
                _CHAT_PINS_KEY: _existing_chat_pin_ids(conn, updates[_CHAT_PINS_KEY]),
            }
            _record_chat_pin_change(conn, current.get(_CHAT_PINS_KEY), updates[_CHAT_PINS_KEY])
        merged = _merge_settings_updates(current, updates)
        now = datetime.now(timezone.utc).isoformat()
        conn.executemany(
            """
            INSERT INTO chat_settings (key, value_json, updated_at)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json = excluded.value_json,
                updated_at = excluded.updated_at
            """,
            [(key, json.dumps(value), now) for key, value in merged.items()],
        )
        conn.commit()
        return merged
    except CorruptSettingsError:
        raise
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def _chat_settings_match_expected(current: Any, expected: Any) -> bool:
    """Whether every expected leaf still has the value the caller read."""
    if isinstance(expected, dict):
        if not isinstance(current, dict):
            return False
        return all(
            key in current and _chat_settings_match_expected(current[key], value)
            for key, value in expected.items()
        )
    return current == expected


def _chat_settings_path_exists(current: Any, path: Iterable[str]) -> bool:
    """Whether every segment exists, without assigning meaning to its value."""
    node = current
    for segment in path:
        if not isinstance(node, dict) or segment not in node:
            return False
        node = node[segment]
    return True


def upsert_chat_settings_merge_if_current(
    expected: dict[str, Any],
    updates: dict[str, Any],
    expected_absent: Iterable[str] = (),
    expected_absent_paths: Iterable[Iterable[str]] = (),
    operation_id: Optional[str] = None,
    unpinned_chat_ids: Iterable[str] = (),
    cancelled_operation_ids: Iterable[str] = (),
) -> tuple[dict[str, Any], bool]:
    """Atomically merge ``updates`` only if ``expected`` still matches. Expected is a recursive subset
    so legacy keys omitted by the current client do not prevent a guarded migration. Any field the
    client did read is fenced against a newer tab write in the same BEGIN IMMEDIATE transaction."""
    # Contended timeout: runs during hydration beside the settings writer; 5s surfaces as a bare lock.
    conn = get_connection(_CONTENDED_BUSY_TIMEOUT_SECONDS)
    try:
        conn.execute("BEGIN IMMEDIATE")
        if _CHAT_PINS_KEY in updates:
            _load_chat_pins_in_conn(conn, repair = True)
        current, corrupt = _load_chat_settings_for_merge(conn)
        if (
            operation_id is not None
            and conn.execute(
                "SELECT 1 FROM chat_pin_operations WHERE id = ?", (operation_id,)
            ).fetchone()
            is not None
        ):
            # A replay after a lost response: acknowledge, never re-apply over a newer unpin.
            conn.commit()
            return current, True
        matches_expected = _chat_settings_match_expected(current, expected)
        has_new_expected_field = any(key in current for key in expected_absent)
        has_new_expected_path = any(
            _chat_settings_path_exists(current, path) for path in expected_absent_paths
        )
        if has_new_expected_field or has_new_expected_path or not matches_expected:
            conn.commit()
            return current, False
        unsafe_partial_keys = [
            key
            for key, value in updates.items()
            if key in corrupt and isinstance(value, dict) and key not in _ATOMIC_SETTING_KEYS
        ]
        if unsafe_partial_keys:
            conn.commit()
            keys = ", ".join(sorted(unsafe_partial_keys))
            raise CorruptSettingsError(
                f"Cannot apply partial settings patch to corrupt key(s): {keys}"
            )
        if not updates:
            # Skip empty patches or updated_at is rewritten, which reads as a settings change.
            conn.commit()
            return current, True
        if _CHAT_PINS_KEY in updates:
            requested = updates[_CHAT_PINS_KEY]
            existing = _existing_chat_pin_ids(conn, requested)
            missing = set(requested) - set(existing)
            # A chat still awaiting legacy import: leave unacknowledged so the journal retries.
            deleted = set()
            chunk_size = _SQLITE_IN_CHUNK_SIZE // 2
            for start in range(0, len(requested), chunk_size):
                chunk = requested[start : start + chunk_size]
                placeholders = ",".join("?" for _ in chunk)
                deleted.update(
                    row["id"]
                    for row in conn.execute(
                        f"SELECT id FROM chat_pin_deletions WHERE id IN ({placeholders}) "
                        f"UNION SELECT id FROM chat_thread_tombstones WHERE id IN ({placeholders})",
                        (*chunk, *chunk),
                    )
                )
            if missing - deleted:
                conn.commit()
                return current, False
            updates = {
                **updates,
                _CHAT_PINS_KEY: existing,
            }
            _record_chat_pin_change(
                conn, current.get(_CHAT_PINS_KEY), updates[_CHAT_PINS_KEY], unpinned_chat_ids
            )
        merged = _merge_settings_updates(current, updates)
        now = datetime.now(timezone.utc).isoformat()
        conn.executemany(
            """
            INSERT INTO chat_settings (key, value_json, updated_at)
            VALUES (?, ?, ?)
            ON CONFLICT(key) DO UPDATE SET
                value_json = excluded.value_json,
                updated_at = excluded.updated_at
            """,
            [(key, json.dumps(value), now) for key, value in merged.items()],
        )
        if operation_id is not None:
            conn.execute("INSERT INTO chat_pin_operations (id) VALUES (?)", (operation_id,))
        conn.executemany(
            "INSERT OR IGNORE INTO chat_pin_operations (id) VALUES (?)",
            [(id,) for id in cancelled_operation_ids],
        )
        conn.commit()
        return merged, True
    except CorruptSettingsError:
        raise
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def list_chat_legacy_imports() -> list[str]:
    conn = get_connection()
    try:
        rows = conn.execute("SELECT legacy_thread_id FROM chat_legacy_imports").fetchall()
        return [row[0] for row in rows]
    finally:
        conn.close()


def upsert_chat_legacy_imports(legacy_thread_ids: list[str]) -> tuple[int, int]:
    """Marks legacy thread ids as imported, idempotently; returns (accepted, inserted) counts."""
    ids = list(dict.fromkeys(tid for tid in legacy_thread_ids if tid))
    if not ids:
        return 0, 0
    ts = int(datetime.now(timezone.utc).timestamp() * 1000)
    conn = get_connection()
    try:
        inserted = 0
        for tid in ids:
            row = conn.execute(
                """
                INSERT INTO chat_legacy_imports (legacy_thread_id, imported_at)
                VALUES (?, ?)
                ON CONFLICT(legacy_thread_id) DO NOTHING
                RETURNING legacy_thread_id
                """,
                (tid, ts),
            ).fetchone()
            if row is not None:
                inserted += 1
        conn.commit()
        return len(ids), inserted
    finally:
        conn.close()


def get_external_import_mark(source: str, session_id: str) -> Optional[tuple[int, str]]:
    """(turns brought over, source revision they came from), or None if never imported."""
    conn = get_connection()
    try:
        row = conn.execute(
            "SELECT turns_imported, revision FROM external_import_sessions"
            " WHERE source = ? AND session_id = ?",
            (source, session_id),
        ).fetchone()
        return None if row is None else (row["turns_imported"], row["revision"])
    finally:
        conn.close()


def _record_external_import_mark(conn: sqlite3.Connection, mark: ExternalImportMark) -> None:
    conn.execute(
        """
        INSERT INTO external_import_sessions (source, session_id, turns_imported, revision)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(source, session_id) DO UPDATE SET
            turns_imported = CASE WHEN excluded.revision = external_import_sessions.revision
                THEN MAX(excluded.turns_imported, external_import_sessions.turns_imported)
                ELSE excluded.turns_imported END,
            revision = excluded.revision
        """,
        (mark.source, mark.session_id, int(mark.turns), mark.revision),
    )


def record_external_import_mark(
    source: str,
    session_id: str,
    turns: int,
    revision: str = "",
) -> None:
    conn = get_connection()
    try:
        _record_external_import_mark(conn, ExternalImportMark(source, session_id, turns, revision))
        conn.commit()
    finally:
        conn.close()