Skip to content

armgit / avibe

Public Imported from GitHub
0

Make your computer a home for your AI partner. Avibe is the local-first Agent OS: Vibey, its built-in AI partner, works out of the box, and Claude Code, Codex & OpenCode run unchanged beside it — reach them from your browser, phone, or any chat app.

68.9 KB
Raw Blame
"""Accepted communication records in the platform-agnostic ``messages`` table.

The workbench Inbox + per-session history both read through this
module so they get a consistent shape regardless of which platform
originated the row. Inbound content materializes here only after a Delivery is
accepted; ``append`` is for communication that is already accepted when written,
such as agent output and mirrored IM records.
"""

from __future__ import annotations

import json
import re
import time
import uuid
from datetime import datetime, timezone
from typing import Any, Iterable, Mapping, Optional

from sqlalchemy import and_, case, delete, func, or_, select, update
from sqlalchemy.engine import Connection

from storage.agent_session_rows import INBOX_SESSION_VISIBILITIES
from core.notification_categories import (
    CATEGORY_BOT,
    CATEGORY_SESSION,
    category_expression,
    current_bot_session_ids,
    current_bot_session_predicate,
    normalize_category,
    split_unread_counts,
)
from storage.db import escape_sql_like
from storage.models import (
    agent_runs,
    agent_sessions,
    agents,
    message_deliveries,
    messages,
    scope_settings,
    scopes,
    session_turns,
    vault_requests,
)
from storage.pagination import PageRequest, PageResult, page_result_from_limit_plus_one
from storage.sender_identity import attach_sender_labels
from storage.sessions_service import session_agent_display_label
from vibe.authorization import AuthorizationContext, require_instance_role
from vibe.message_identity import HARNESS_TYPE, INPUT_TURN_AUTHOR_TYPES, NOTIFY_TYPE, VAULT_TYPE
from vibe.message_types import types_with


def _utc_now_iso() -> str:
    return datetime.now(timezone.utc).isoformat(timespec="microseconds").replace(
        "+00:00", "Z"
    )


def canonical_message_timestamp(value: Any) -> str | None:
    """Return one lexically sortable UTC representation for Message timestamps."""

    text = str(value or "").strip()
    if not text:
        return None
    parseable = text[:-1] + "+00:00" if text.endswith("Z") else text
    try:
        instant = datetime.fromisoformat(parseable)
    except ValueError:
        return text
    if instant.tzinfo is None:
        instant = instant.replace(tzinfo=timezone.utc)
    return instant.astimezone(timezone.utc).isoformat(timespec="microseconds").replace(
        "+00:00", "Z"
    )


def _timestamp_key(value: Any, row_id: Any) -> tuple[datetime, str]:
    text = str(value or "").strip()
    if text.endswith("Z"):
        text = text[:-1] + "+00:00"
    try:
        instant = datetime.fromisoformat(text)
    except ValueError:
        instant = datetime.min.replace(tzinfo=timezone.utc)
    if instant.tzinfo is None:
        instant = instant.replace(tzinfo=timezone.utc)
    return instant.astimezone(timezone.utc), str(row_id or "")


def _new_message_id() -> str:
    """Time-sortable message id.

    The durable transcript key uses Message acceptance-or-creation time plus id.
    Legacy and imported rows may still have second-resolution timestamps, so a
    microsecond-clock prefix makes the id tie-break preserve insertion order.
    The random suffix keeps ids unique within the same microsecond.
    """
    return f"msg_{int(time.time() * 1_000_000):015x}{uuid.uuid4().hex[:8]}"


def _row_to_payload(
    row: dict[str, Any],
    *,
    include_private_metadata: bool = False,
    include_local_error_detail: bool = False,
    conn: Connection | None = None,
) -> dict[str, Any]:
    try:
        content = json.loads(row.get("content_json") or "{}")
    except json.JSONDecodeError:
        content = {}
    if isinstance(content, dict):
        # A transcript row's model payload (the Avibe Agent's context) is not display:
        # no reader of a message payload uses it, and it can be large or private.
        content.pop("model", None)
    link = content.get("failure_retry") if isinstance(content, dict) else None
    if conn is not None and isinstance(link, dict):
        from storage.message_deliveries import failure_retry_state, get_delivery

        delivery = get_delivery(conn, str(link.get("delivery_id") or ""))
        content["failure_retry"] = {
            "delivery_id": link.get("delivery_id"),
            "state": (
                failure_retry_state(delivery)
                if delivery is not None and delivery["session_id"] == row.get("session_id")
                else "retired"
            ),
        }
    try:
        metadata = json.loads(row.get("metadata_json") or "{}")
    except json.JSONDecodeError:
        metadata = {}
    if not isinstance(metadata, dict):
        metadata = {}
    elif not include_private_metadata:
        from storage.message_deliveries import public_message_metadata

        metadata = public_message_metadata(metadata, include_local_error_detail=include_local_error_detail)
    return {
        "id": row["id"],
        "scope_id": row.get("scope_id"),
        "session_id": row.get("session_id"),
        "platform": row.get("platform"),
        "author": row.get("author"),
        "type": row.get("type"),
        "author_id": row.get("author_id"),
        "author_name": row.get("author_name"),
        "source": row.get("source"),
        "native_message_id": row.get("native_message_id"),
        "parent_native_message_id": row.get("parent_native_message_id"),
        "text": row.get("content_text") or content.get("text") or "",
        "content": content,
        "metadata": metadata,
        "created_at": row.get("created_at"),
        "updated_at": row.get("updated_at"),
        "delivered_at": row.get("delivered_at"),
        "read_at": row.get("read_at"),
    }


_AGENT_RUN_NATIVE_PREFIX = "agent_run:"


def _native_harness_provenance(native_message_id: Any) -> tuple[str | None, str | None]:
    """Recover trigger kind/definition from the stable native Message identity.

    Delivery snapshots written before provenance was carried through the durable
    queue can have null ``author_name``/``author_id`` even though the native id
    still records the exact trigger. This is read compatibility for those rows;
    current writers persist the same values directly.
    """

    parts = str(native_message_id or "").split(":")
    kind = parts[0] if parts else ""
    if kind in {"watch", "webhook", "scheduled"}:
        return kind, parts[1] if len(parts) >= 3 and parts[1] else None
    if kind in {"hook", "agent_run"}:
        return kind, None
    return None, None


def _attach_harness_provenance(
    conn: Connection,
    payloads: list[dict[str, Any]],
    *,
    authorization_context: AuthorizationContext | Mapping[str, Any] | None = None,
) -> list[dict[str, Any]]:
    """Read-side provenance for every durable Harness chat message.

    Stable native ids repair legacy queued rows whose trigger fields were lost.
    A harness ``agent_run`` prompt is stored as ``native_message_id =
    "agent_run:<execution_id>"`` with no source-session pointer (the write path
    records none). Resolve it read-side: message → its ``agent_runs`` row
    (``id == <execution_id>``) → the run's ``source_actor`` (the session that
    triggered the callback) → that session's title, so the Chat chip can name the
    source and deep-link to ``/chat/<source_session_id>``. Vault callbacks additionally
    resolve their ``vault:<request_id>`` actor into stable request type/status metadata
    so the transcript can label the callback as coming from Vault. Batched: at most
    two extra queries per page, only when such a message is present.
    """
    exec_by_msg: dict[str, str] = {}
    for payload in payloads:
        native_id = payload.get("native_message_id")
        if payload.get("source") == "harness":
            kind, definition_id = _native_harness_provenance(native_id)
            if not payload.get("author_name") and kind:
                payload["author_name"] = kind
            if not payload.get("author_id") and definition_id:
                payload["author_id"] = definition_id
        if (
            payload.get("source") == "harness"
            and isinstance(native_id, str)
            and native_id.startswith(_AGENT_RUN_NATIVE_PREFIX)
        ):
            exec_by_msg[payload["id"]] = native_id[len(_AGENT_RUN_NATIVE_PREFIX):]
    if not exec_by_msg:
        return payloads
    context = require_instance_role(authorization_context, "viewer")

    # execution_id == agent_runs.id. The source SESSION differs by run kind:
    #  - source_kind='agent'   → source_actor IS the caller session id.
    #  - source_kind='callback'→ source_actor is the parent RUN id (or a decorated
    #    "<run>:terminal:<status>"), NOT a session; the real source is the parent
    #    (delegated) run's session_id.
    runs = {
        row["id"]: row
        for row in conn.execute(
            select(
                agent_runs.c.id, agent_runs.c.source_kind,
                agent_runs.c.source_actor, agent_runs.c.parent_run_id,
            ).where(agent_runs.c.id.in_(set(exec_by_msg.values())))
        ).mappings()
    }
    callback_parents = {
        run["parent_run_id"] for run in runs.values()
        if run["source_kind"] == "callback" and run["parent_run_id"]
    }
    parent_session: dict[str, str] = {}
    if callback_parents:
        for row in conn.execute(
            select(agent_runs.c.id, agent_runs.c.session_id).where(
                agent_runs.c.id.in_(callback_parents)
            )
        ).mappings():
            sess = (row["session_id"] or "").strip()
            if sess:
                parent_session[row["id"]] = sess

    source_by_exec: dict[str, str] = {}
    for exec_id, run in runs.items():
        if run["source_kind"] == "callback":
            source_id = parent_session.get(run["parent_run_id"])
        else:
            source_id = (run["source_actor"] or "").strip() or None
        # A session id never contains ':' (decorated run/terminal forms do); guard
        # so an unexpected source_actor shape can't become a bogus /chat target.
        if source_id and ":" not in source_id:
            source_by_exec[exec_id] = source_id
    vault_by_exec: dict[str, tuple[str, dict[str, Any] | None]] = {}
    vault_request_ids: set[str] = set()
    for exec_id, run in runs.items():
        actor = str(run["source_actor"] or "").strip()
        if run["source_kind"] == "callback" and actor.startswith("vault:"):
            request_id = actor[len("vault:"):].strip()
            if request_id:
                vault_by_exec[exec_id] = (request_id, None)
                vault_request_ids.add(request_id)
    if vault_request_ids:
        request_rows = {
            str(row["id"]): dict(row)
            for row in conn.execute(
                select(
                    vault_requests.c.id,
                    vault_requests.c.request_type,
                    vault_requests.c.status,
                ).where(vault_requests.c.id.in_(vault_request_ids))
            ).mappings()
        }
        vault_by_exec = {
            exec_id: (request_id, request_rows.get(request_id))
            for exec_id, (request_id, _row) in vault_by_exec.items()
        }
    if not source_by_exec and not vault_by_exec:
        return payloads

    meta_by_session: dict[str, dict[str, Optional[str]]] = {}
    if source_by_exec:
        from storage import project_access_service

        for row in conn.execute(
            select(
                agent_sessions.c.id,
                agent_sessions.c.scope_id,
                agent_sessions.c.title,
                agent_sessions.c.agent_name,
                agent_sessions.c.agent_backend,
                agents.c.name.label("catalog_agent_name"),
                agents.c.archived_at.label("catalog_agent_archived_at"),
                agents.c.metadata_json.label("catalog_agent_metadata_json"),
            )
            .select_from(
                agent_sessions.outerjoin(
                    agents,
                    or_(
                        agents.c.id == agent_sessions.c.agent_id,
                        and_(
                            agent_sessions.c.agent_id.is_(None),
                            agents.c.name == agent_sessions.c.agent_name,
                        ),
                    ),
                )
                )
            .where(
                agent_sessions.c.id.in_(set(source_by_exec.values()))
            )
        ).mappings():
            project_id = project_access_service.project_id_from_scope_id(row["scope_id"])
            if (
                not context.can_manage_instance
                and (
                    project_id is None
                    or not project_access_service.can_read_project(conn, context, project_id)
                )
            ):
                continue
            meta_by_session[row["id"]] = {
                "title": row["title"],
                "agent_name": session_agent_display_label(row),
            }

    for payload in payloads:
        exec_id = exec_by_msg.get(payload["id"], "")
        source_id = source_by_exec.get(exec_id)
        # Only attach when the source session still exists (is in meta_by_session);
        # a stale/imported/deleted source would otherwise write source_session_id
        # and produce a dead /chat/<missing id> link with only the fallback label.
        if source_id in meta_by_session:
            meta = meta_by_session[source_id]
            payload["source_session_id"] = source_id
            payload["source_session_title"] = meta.get("title")
            payload["source_session_agent_name"] = meta.get("agent_name")
        vault_info = vault_by_exec.get(exec_id)
        if vault_info is not None:
            request_id, request = vault_info
            metadata = dict(payload.get("metadata") or {})
            metadata.setdefault("source_kind", "callback")
            metadata.setdefault("source_actor", f"vault:{request_id}")
            if request is not None:
                metadata.setdefault("vault_request_type", request.get("request_type"))
                metadata.setdefault("vault_request_status", request.get("status"))
            payload["metadata"] = metadata
    return payloads


def _attach_transcript_identity(
    conn: Connection,
    payloads: list[dict[str, Any]],
    *,
    authorization_context: AuthorizationContext | Mapping[str, Any] | None = None,
) -> list[dict[str, Any]]:
    """Everything a transcript row needs read-side but does not store.

    One entry point for both enrichments so a new transcript window inherits
    them together instead of picking up whichever one its neighbour happened to
    call.
    """
    return attach_sender_labels(
        conn,
        _attach_harness_provenance(
            conn, payloads, authorization_context=authorization_context
        ),
    )


def transcript_order_value(table: Any = messages) -> Any:
    """SQLite ordering key for when a Message entered the transcript.

    A queued Delivery may be submitted while another Turn is active. Its Message
    is authored then but enters the transcript only when native start accepts it,
    recorded in ``delivered_at``. Directly accepted rows have no ``delivered_at``
    and keep their microsecond ``created_at`` plus id tie-break.
    """

    return func.coalesce(table.c.delivered_at, table.c.created_at)


_WS_RE = re.compile(r"\s+")

# Snippet window radii (chars) either side of the matched term. Tuned for a
# single-line result row: a little context before, a bit more after.
_SNIPPET_BEFORE = 40
_SNIPPET_AFTER = 50
# Fallback head shown when the term isn't found in ``content_text`` (e.g. the
# match lived in a non-text field) — keeps the row from rendering empty.
_SNIPPET_FALLBACK_HEAD = 90


def _collapse_ws(value: str) -> str:
    """Collapse any run of whitespace/newlines to a single space (no strip)."""
    return _WS_RE.sub(" ", value)


def build_snippet(content_text: str, query: str) -> dict[str, str]:
    """Split *content_text* into ``{prefix, match, suffix}`` around *query*.

    The match is located case-insensitively but ``match`` carries the ORIGINAL
    casing from the text. A window of ~``_SNIPPET_BEFORE`` chars before and
    ~``_SNIPPET_AFTER`` after is kept; whitespace/newlines are collapsed to single
    spaces so the row stays one line. A leading ``…`` marks a prefix truncated at
    the start, a trailing ``…`` a suffix truncated at the end. When the query
    isn't found, fall back to the first ~``_SNIPPET_FALLBACK_HEAD`` chars as the
    prefix with an empty match (so the API contract — three string fields — holds
    regardless of where the DB matched)."""
    text = content_text or ""
    idx = text.lower().find(query.lower())
    if idx == -1:
        head = text[:_SNIPPET_FALLBACK_HEAD]
        prefix = _collapse_ws(head)
        if len(text) > _SNIPPET_FALLBACK_HEAD:
            prefix = f"{prefix}…"
        return {"prefix": prefix, "match": "", "suffix": ""}

    end = idx + len(query)
    start = max(0, idx - _SNIPPET_BEFORE)
    stop = min(len(text), end + _SNIPPET_AFTER)

    prefix = _collapse_ws(text[start:idx])
    match = text[idx:end]  # original casing
    suffix = _collapse_ws(text[end:stop])
    if start > 0:
        prefix = f"…{prefix}"
    if stop < len(text):
        suffix = f"{suffix}…"
    return {"prefix": prefix, "match": match, "suffix": suffix}


def search_messages(
    conn: Connection,
    *,
    query: str,
    platform: str = "avibe",
    types: Optional[Iterable[str]] = None,
    limit: int = 50,
    include_archived: bool = False,
    scope_ids: Optional[Iterable[str]] = None,
    hidden_session_ids: Optional[Iterable[str]] = None,
) -> dict[str, Any]:
    """Global message-content search, grouped by session.

    Substring (case-insensitive) ``LIKE`` over ``messages.content_text``, scoped
    to one ``platform`` (Workbench = ``avibe``) and a set of transcript-visible
    ``types`` (human prompts + harness prompts + the agent's rendered ``result``
    replies + Show annotations — all land on a message the chat
    actually renders, so a clicked result is always jumpable). Archived SESSIONS
    are excluded unless ``include_archived=True`` opts them in (archive is
    terminal, so those transcripts are read-only — the flag only makes them
    findable). Messages under an archived PROJECT are excluded
    UNCONDITIONALLY, independent of ``include_archived``:
    ``projects_service.archive_project``
    disables a project by setting ``scope_settings.enabled = 0`` (its sessions
    stay ``active``), so the scope's disabled state is the authoritative
    "archived project" signal here, and project archive is reversible through its
    own restore flow rather than through search. A scope with no
    ``scope_settings`` row is
    treated as enabled (legacy / folder-less projects never got one). ``limit``
    caps the number of
    MATCHED messages scanned (newest first), so it bounds total work; the matches
    are then grouped into sessions. The snippet is built in Python (see
    :func:`build_snippet`) so the client renders ``match`` with a highlight and
    needs no offset math.

    Returns ``{"sessions": [...], "total": <#matches>, "session_count": <#sessions>}``
    where each session is ``{session_id, title, project_id, project_name,
    archived, matches: [{id, author, source, type, created_at, snippet}]}``.
    ``archived`` is always present and is ``False`` for every group in the
    default (opt-out) mode. Sessions are
    ordered by their most-recent match; matches are newest-first within a session.
    An empty / whitespace query short-circuits to an empty result.
    """
    cleaned = (query or "").strip()
    if not cleaned:
        return {"sessions": [], "total": 0, "session_count": 0}
    allowed_scope_ids = list(dict.fromkeys(scope_ids)) if scope_ids is not None else None
    if allowed_scope_ids == []:
        return {"sessions": [], "total": 0, "session_count": 0}

    like = escape_sql_like(cleaned)
    type_list = list(types if types is not None else types_with("searchable"))
    effective_limit = min(max(int(limit), 1), 200)
    # Applied in place below so the default (opt-out) statement keeps exactly the
    # predicates — and the predicate order — it had before the flag existed.
    archived_session_filters = () if include_archived else (agent_sessions.c.status != "archived",)

    stmt = (
        select(
            messages.c.id,
            messages.c.session_id,
            messages.c.author,
            messages.c.source,
            messages.c.type,
            messages.c.content_text,
            messages.c.created_at,
            agent_sessions.c.title,
            agent_sessions.c.status,
            scopes.c.native_id.label("project_id"),
            scopes.c.display_name.label("project_name"),
        )
        .select_from(
            messages.join(agent_sessions, agent_sessions.c.id == messages.c.session_id)
            .join(scopes, scopes.c.id == agent_sessions.c.scope_id, isouter=True)
            .join(scope_settings, scope_settings.c.scope_id == agent_sessions.c.scope_id, isouter=True)
        )
        .where(messages.c.platform == platform)
        .where(messages.c.type.in_(type_list))
        .where(messages.c.content_text.is_not(None))
        .where(messages.c.content_text.ilike(f"%{like}%", escape="\\"))
        # Archived sessions are soft-deleted — never surface their messages
        # unless the caller explicitly opted in via ``include_archived``.
        .where(*archived_session_filters)
        # DELIBERATELY ``foreground`` ALONE, i.e. narrower than the inbox
        # (``INBOX_SESSION_VISIBILITIES``, which also admits ``system``). Search
        # returns RESULTS a user goes on to open and read in context, and the only
        # system session is the runtime's workspace-notifications row: machine-authored
        # failure notices, already surfaced as inbox cards, with no conversation around
        # a hit to read. Admitting it would put runtime bookkeeping into every user text
        # search for no reachable next action. (Today's notices are additionally not
        # ``searchable`` by TYPE — this predicate is the one that stays true if that
        # ever changes.)
        .where(agent_sessions.c.visibility == "foreground")
        # Archived PROJECTS are modelled as scope_settings.enabled = 0 (the
        # sessions stay active), so exclude a disabled scope's messages too. A
        # missing scope_settings row (legacy / folder-less project) is enabled.
        .where(or_(scope_settings.c.enabled.is_(None), scope_settings.c.enabled != 0))
        .order_by(transcript_order_value().desc(), messages.c.id.desc())
        .limit(effective_limit)
    )
    if allowed_scope_ids is not None:
        stmt = stmt.where(agent_sessions.c.scope_id.in_(allowed_scope_ids))
    hidden = _hidden_ids(hidden_session_ids)
    if hidden:
        stmt = stmt.where(messages.c.session_id.not_in(hidden))

    rows = conn.execute(stmt).mappings().all()

    # Group by session, preserving the newest-match-first row order: the first
    # time a session appears is its most-recent match, so insertion order already
    # ranks sessions by recency and matches stay newest-first within each.
    grouped: dict[str, dict[str, Any]] = {}
    total = 0
    for row in rows:
        session_id = row["session_id"]
        bucket = grouped.get(session_id)
        if bucket is None:
            bucket = {
                "session_id": session_id,
                "title": row["title"],
                "project_id": row["project_id"],
                "project_name": row["project_name"],
                "archived": row["status"] == "archived",
                "matches": [],
            }
            grouped[session_id] = bucket
        bucket["matches"].append(
            {
                "id": row["id"],
                "author": row["author"],
                "source": row["source"],
                "type": row["type"],
                "created_at": row["created_at"],
                "snippet": build_snippet(row["content_text"], cleaned),
            }
        )
        total += 1

    sessions = list(grouped.values())
    return {"sessions": sessions, "total": total, "session_count": len(sessions)}


def append(
    conn: Connection,
    *,
    scope_id: Optional[str],
    session_id: Optional[str],
    platform: str,
    author: str,
    message_type: Optional[str] = None,
    text: Optional[str] = None,
    content: Optional[dict[str, Any]] = None,
    metadata: Optional[dict[str, Any]] = None,
    author_id: Optional[str] = None,
    author_name: Optional[str] = None,
    source: Optional[str] = None,
    native_message_id: Optional[str] = None,
    parent_native_message_id: Optional[str] = None,
    delivered_at: Optional[str] = None,
    read_at: Optional[str] = None,
    include_local_error_detail: bool = False,
) -> dict[str, Any]:
    """Insert a new message row and return its payload.

    ``content`` is the rich blob (text + attachments + tool_calls); if
    ``text`` is omitted we project ``content['text']`` into
    ``content_text`` so plain-text search keeps working.
    """

    body: dict[str, Any] = {}
    if content:
        body.update(content)
    if text is not None:
        body.setdefault("text", text)
    plain = text if text is not None else body.get("text") or None

    # Default the type from the author so legacy callers that only set ``author``
    # (e.g. show-page transcript annotations) stay correctly typed — a human row
    # must be ``user`` (not ``assistant``), or the user+result transcript filter
    # would drop it. Typed callers (inbox/IM mirror) pass message_type explicitly.
    resolved_type = message_type or ("user" if author == "user" else "assistant")
    if source == HARNESS_TYPE and author == "user" and resolved_type == "user":
        author = HARNESS_TYPE
        resolved_type = HARNESS_TYPE

    now = canonical_message_timestamp(_utc_now_iso())
    assert now is not None
    payload = {
        "id": _new_message_id(),
        "scope_id": scope_id,
        "session_id": session_id,
        "platform": platform,
        "author": author,
        "type": resolved_type,
        "author_id": author_id,
        "author_name": author_name,
        "source": source,
        "native_message_id": native_message_id,
        "parent_native_message_id": parent_native_message_id,
        "content_text": plain,
        "content_json": json.dumps(body),
        "metadata_json": json.dumps(metadata or {}),
        "created_at": now,
        "updated_at": now,
        "delivered_at": canonical_message_timestamp(delivered_at),
        "read_at": read_at,
    }
    conn.execute(messages.insert().values(**payload))
    return _row_to_payload(payload, include_local_error_detail=include_local_error_detail)


def get_message(
    conn: Connection,
    message_id: str,
    *,
    session_id: Optional[str] = None,
    include_local_error_detail: bool = False,
) -> Optional[dict[str, Any]]:
    """Load one message by id, optionally requiring its owning session."""

    query = select(messages).where(messages.c.id == message_id)
    if session_id is not None:
        query = query.where(messages.c.session_id == session_id)
    row = conn.execute(query).mappings().first()
    if not row:
        return None
    # The ``message.new`` publisher loads the live row through here, so the
    # sender resolved on delivery is the one the following reload shows.
    return attach_sender_labels(
        conn, [_row_to_payload(dict(row), conn=conn, include_local_error_detail=include_local_error_detail)]
    )[0]


def native_message_exists(
    conn: Connection,
    *,
    platform: str,
    scope_id: str | None,
    native_message_id: str,
) -> bool:
    """True when a conversation-scoped native message has been recorded."""
    return (
        get_native_message(
            conn,
            platform=platform,
            scope_id=scope_id,
            native_message_id=native_message_id,
        )
        is not None
    )


def get_native_message(
    conn: Connection,
    *,
    platform: str,
    scope_id: str | None,
    native_message_id: str,
    include_local_error_detail: bool = False,
) -> Optional[dict[str, Any]]:
    """Load one accepted Message by its conversation-scoped native identity."""

    platform = str(platform or "").strip()
    native_message_id = str(native_message_id or "").strip()
    if not platform or not native_message_id:
        return None
    scope_predicate = (
        messages.c.scope_id == scope_id
        if scope_id is not None
        else messages.c.scope_id.is_(None)
    )
    row = conn.execute(
        select(messages)
        .where(messages.c.platform == platform)
        .where(scope_predicate)
        .where(messages.c.native_message_id == native_message_id)
        .limit(1)
    ).mappings().first()
    return _row_to_payload(dict(row), include_local_error_detail=include_local_error_detail) if row else None


def promote_suppressed_native_message(
    conn: Connection,
    *,
    platform: str,
    scope_id: str | None,
    session_id: str | None,
    native_message_id: str,
    message_type: str,
    text: str,
    content: Optional[dict[str, Any]] = None,
    metadata: Optional[dict[str, Any]] = None,
    include_local_error_detail: bool = False,
) -> Optional[dict[str, Any]]:
    """Turn local-only history into a receipt after outward delivery succeeds.

    ``suppress_delivery`` deliberately persists the transcript before there is an
    outward receipt. A later visible replay keeps the same native identity, so its
    post-send persistence hits the unique constraint. This guarded update is that
    replay's commit point: it removes the suppression marker only after the caller
    has delivered, rebinds the row to that visible target Session, refreshes the
    accepted body, and returns the now-valid receipt.
    """

    platform = str(platform or "").strip()
    native_message_id = str(native_message_id or "").strip()
    if not platform or not native_message_id:
        return None
    body: dict[str, Any] = {}
    if content:
        body.update(content)
    body.setdefault("text", text)
    now = canonical_message_timestamp(_utc_now_iso())
    assert now is not None
    scope_predicate = (
        messages.c.scope_id == scope_id
        if scope_id is not None
        else messages.c.scope_id.is_(None)
    )
    promoted = conn.execute(
        update(messages)
        .where(messages.c.platform == platform)
        .where(scope_predicate)
        .where(messages.c.native_message_id == native_message_id)
        .where(func.json_valid(messages.c.metadata_json) == 1)
        .where(func.json_extract(messages.c.metadata_json, "$.delivery_suppressed") == 1)
        .values(
            session_id=session_id,
            type=message_type,
            content_text=text,
            content_json=json.dumps(body),
            metadata_json=json.dumps(metadata or {}),
            delivered_at=now,
            updated_at=now,
        )
    )
    if not promoted.rowcount:
        return None
    return get_native_message(
        conn,
        platform=platform,
        scope_id=scope_id,
        native_message_id=native_message_id,
        include_local_error_detail=include_local_error_detail,
    )


def get_quick_reply_chosen(conn: Connection, session_id: str, message_id: str) -> Optional[str]:
    """The label already chosen for *message_id*'s quick-reply group, or None.

    The chosen answer is recorded on the AGENT message itself (the question) as
    the single source of truth for the locked/answered state, so this is one
    row lookup — no correlating a separate, mergeable user reply. Scoped to
    *session_id* so a request for one session can't read another's message.
    """
    row = conn.execute(
        select(messages.c.content_json).where(
            messages.c.id == message_id, messages.c.session_id == session_id
        )
    ).first()
    if not row or not row[0]:
        return None
    try:
        content = json.loads(row[0])
    except (TypeError, ValueError):
        return None
    chosen = content.get("quick_reply_chosen")
    return chosen if isinstance(chosen, str) and chosen else None


def set_quick_reply_chosen(conn: Connection, session_id: str, message_id: str, choice: str) -> bool:
    """Record *choice* as the answer to *message_id*'s quick-reply group, once.

    Returns True if newly recorded; False if the message has no such option or was
    already answered (set-once → idempotent). Writing the answer onto the agent
    message is the root of the design: the lock then derives from that one row and
    is immune to how the user reply is queued / merged / removed. Scoped to
    *session_id* so a request for one session can't mutate another's message.
    """
    row = conn.execute(
        select(messages.c.content_json).where(
            messages.c.id == message_id, messages.c.session_id == session_id
        )
    ).first()
    if not row or not row[0]:
        return False
    try:
        content = json.loads(row[0])
    except (TypeError, ValueError):
        return False
    options = content.get("quick_replies")
    if not isinstance(options, list) or choice not in options:
        return False
    if content.get("quick_reply_chosen"):
        return False  # set-once: already answered
    content["quick_reply_chosen"] = choice
    conn.execute(
        messages.update()
        .where(messages.c.id == message_id, messages.c.session_id == session_id)
        .values(content_json=json.dumps(content))
    )
    return True


def list_session_messages(
    conn: Connection,
    *,
    session_id: str,
    after_id: Optional[str] = None,
    before_id: Optional[str] = None,
    around_id: Optional[str] = None,
    around_native_id: Optional[str] = None,
    around_native_platform: Optional[str] = None,
    around_turn_id: Optional[str] = None,
    around_run_id: Optional[str] = None,
    limit: int = 50,
    types: Optional[Iterable[str]] = None,
    include_private_metadata: bool = False,
    authorization_context: AuthorizationContext | Mapping[str, Any] | None = None,
    tail: bool = False,
) -> dict[str, Any]:
    """Return messages for one session in chronological order with cursor pagination.

    ``types`` optionally restricts the rows to a set of message types. The chat
    transcript passes :data:`TRANSCRIPT_TYPES` so intermediate process-log rows
    stay out of the conversation view.

    ``before_id`` returns the page immediately older than that row, still in
    chronological order. This powers upward history loading from the chat page.

    ``around_id`` centers a window on a durable message id (deep-link / search
    jump). ``around_native_id`` provides the equivalent lookup for a platform
    native message id when a legacy caller context has not retained the durable
    row id; ``around_native_platform`` disambiguates native ids reused by another
    platform in the same session. ``around_turn_id`` and ``around_run_id`` resolve the initial accepted
    delivery for a stable Agent turn/run before applying the same window logic.
    Up to ``limit`` rows are returned on either side of the anchor,
    merged chronologically. These modes take precedence over ``after_id`` /
    ``before_id`` / ``tail``. ``next_before_id`` is set when older rows remain,
    ``next_after_id`` when newer rows remain, so the chat can page in both
    directions from the centered window. An unknown anchor returns no messages
    and null cursors.

    ``tail`` returns the most-recent ``limit`` rows (still chronological) instead
    of the oldest page — used by the Chat page's reconnect/visibility gap
    recovery, which needs the RECENT window (a long chat's oldest page would
    never surface a missed latest prompt/reply). ``tail`` ignores ``after_id``
    and returns no forward cursor.
    """

    include_local_error_detail = (
        authorization_context is not None
        and require_instance_role(authorization_context, "viewer").can_manage_instance
    )
    query = select(messages).where(messages.c.session_id == session_id)
    if types is not None:
        query = query.where(messages.c.type.in_(list(types)))
    effective_limit = min(max(int(limit), 1), 500)
    if around_id or around_native_id or around_turn_id or around_run_id:
        # Window centered on a specific message (deep-link / search jump). Resolve
        # the anchor's (created_at, id); an unknown id (or one in another session)
        # yields an empty window. ``query`` already carries the type/metadata
        # filter, so the older/anchor/newer sub-queries inherit it — the anchor
        # only appears if it is itself transcript-visible.
        order_value = transcript_order_value()
        anchor_id = around_id
        anchor = conn.execute(
            select(order_value).where(
                messages.c.id == anchor_id, messages.c.session_id == session_id
            )
        ).scalar_one_or_none()
        if anchor is None and around_native_id:
            native_query = select(messages.c.id).where(
                messages.c.native_message_id == around_native_id,
                messages.c.session_id == session_id,
            )
            if around_native_platform:
                native_query = native_query.where(messages.c.platform == around_native_platform)
            anchor_id = conn.execute(native_query.order_by(messages.c.id.asc()).limit(1)).scalar_one_or_none()
            if anchor_id is not None:
                anchor = conn.execute(
                    select(order_value).where(
                        messages.c.id == anchor_id, messages.c.session_id == session_id
                    )
                ).scalar_one_or_none()
        if anchor is None and around_turn_id:
            anchor_id = conn.execute(
                select(message_deliveries.c.message_id)
                .where(
                    message_deliveries.c.turn_id == around_turn_id,
                    message_deliveries.c.session_id == session_id,
                    message_deliveries.c.message_id.is_not(None),
                )
                .order_by(message_deliveries.c.turn_position.asc(), message_deliveries.c.id.asc())
                .limit(1)
            ).scalar_one_or_none()
            if anchor_id is not None:
                anchor = conn.execute(
                    select(order_value).where(
                        messages.c.id == anchor_id, messages.c.session_id == session_id
                    )
                ).scalar_one_or_none()
        if anchor is None and around_run_id:
            anchor_id = conn.execute(
                select(message_deliveries.c.message_id)
                .select_from(message_deliveries.join(agent_runs, agent_runs.c.delivery_id == message_deliveries.c.id))
                .where(
                    agent_runs.c.id == around_run_id,
                    message_deliveries.c.session_id == session_id,
                    message_deliveries.c.message_id.is_not(None),
                )
                .order_by(message_deliveries.c.turn_position.asc(), message_deliveries.c.id.asc())
                .limit(1)
                ).scalar_one_or_none()
            if anchor_id is None:
                anchor_id = conn.execute(
                    select(messages.c.id)
                    .where(
                        messages.c.native_message_id == f"agent_run:{around_run_id}",
                        messages.c.session_id == session_id,
                    )
                    .order_by(messages.c.id.asc())
                    .limit(1)
                ).scalar_one_or_none()
            if anchor_id is not None:
                anchor = conn.execute(
                    select(order_value).where(
                        messages.c.id == anchor_id, messages.c.session_id == session_id
                    )
                ).scalar_one_or_none()
        if anchor is None:
            return {"messages": [], "next_after_id": None, "next_before_id": None}

        older_q = (
            query.where(
                or_(
                    order_value < anchor,
                    and_(order_value == anchor, messages.c.id < anchor_id),
                )
            )
            .order_by(order_value.desc(), messages.c.id.desc())
            .limit(effective_limit + 1)
        )
        older = [
            _row_to_payload(
                dict(row), include_private_metadata=include_private_metadata,
                include_local_error_detail=include_local_error_detail, conn=conn,
            )
            for row in conn.execute(older_q).mappings().all()
        ]
        has_older = len(older) > effective_limit
        older = older[:effective_limit]
        older.reverse()

        anchor_rows = [
            _row_to_payload(
                dict(row), include_private_metadata=include_private_metadata,
                include_local_error_detail=include_local_error_detail, conn=conn,
            )
            for row in conn.execute(query.where(messages.c.id == anchor_id)).mappings().all()
        ]

        newer_q = (
            query.where(
                or_(
                    order_value > anchor,
                    and_(order_value == anchor, messages.c.id > anchor_id),
                )
            )
            .order_by(order_value.asc(), messages.c.id.asc())
            .limit(effective_limit + 1)
        )
        newer = [
            _row_to_payload(
                dict(row), include_private_metadata=include_private_metadata,
                include_local_error_detail=include_local_error_detail, conn=conn,
            )
            for row in conn.execute(newer_q).mappings().all()
        ]
        has_newer = len(newer) > effective_limit
        newer = newer[:effective_limit]

        merged = _attach_transcript_identity(
            conn,
            older + anchor_rows + newer,
            authorization_context=authorization_context,
        )
        return {
            "messages": merged,
            "next_after_id": newer[-1]["id"] if has_newer and newer else None,
            "next_before_id": older[0]["id"] if has_older and older else None,
            "anchor_id": anchor_id,
        }
    if tail:
        # Newest ``limit`` rows, then flip back to chronological for the caller.
        order_value = transcript_order_value()
        query = query.order_by(order_value.desc(), messages.c.id.desc()).limit(
            effective_limit + 1
        )
        rows = _attach_transcript_identity(
            conn,
            [
                _row_to_payload(
                    dict(row), include_private_metadata=include_private_metadata,
                    include_local_error_detail=include_local_error_detail, conn=conn,
                )
                for row in conn.execute(query).mappings().all()
            ],
            authorization_context=authorization_context,
        )
        has_older = len(rows) > effective_limit
        rows = rows[:effective_limit]
        rows.reverse()
        return {
            "messages": rows,
            "next_after_id": None,
            "next_before_id": rows[0]["id"] if has_older and rows else None,
        }
    if before_id:
        order_value = transcript_order_value()
        anchor = conn.execute(
            select(order_value).where(messages.c.id == before_id)
        ).scalar_one_or_none()
        if anchor is not None:
            query = query.where(
                or_(
                    order_value < anchor,
                    and_(order_value == anchor, messages.c.id < before_id),
                )
            )
        query = query.order_by(order_value.desc(), messages.c.id.desc()).limit(
            effective_limit + 1
        )
        rows = _attach_transcript_identity(
            conn,
            [
                _row_to_payload(
                    dict(row), include_private_metadata=include_private_metadata,
                    include_local_error_detail=include_local_error_detail, conn=conn,
                )
                for row in conn.execute(query).mappings().all()
            ],
            authorization_context=authorization_context,
        )
        has_older = len(rows) > effective_limit
        rows = rows[:effective_limit]
        rows.reverse()
        return {
            "messages": rows,
            "next_after_id": None,
            "next_before_id": rows[0]["id"] if has_older and rows else None,
        }
    if after_id:
        order_value = transcript_order_value()
        anchor = conn.execute(
            select(order_value).where(messages.c.id == after_id)
        ).scalar_one_or_none()
        if anchor is not None:
            query = query.where(
                or_(
                    order_value > anchor,
                    and_(order_value == anchor, messages.c.id > after_id),
                )
            )
    order_value = transcript_order_value()
    query = query.order_by(order_value.asc(), messages.c.id.asc()).limit(
        effective_limit + 1
    )
    rows = _attach_transcript_identity(
        conn,
        [
            _row_to_payload(
                dict(row), include_private_metadata=include_private_metadata,
                include_local_error_detail=include_local_error_detail, conn=conn,
            )
            for row in conn.execute(query).mappings().all()
        ],
        authorization_context=authorization_context,
    )
    # Probe one extra row against the clamped page size: a full page alone does
    # not prove there is another page, but the extra row does.
    has_newer = len(rows) > effective_limit
    rows = rows[:effective_limit]
    next_after = rows[-1]["id"] if has_newer and rows else None
    return {"messages": rows, "next_after_id": next_after, "next_before_id": None}


def first_user_text(conn: Connection, session_id: str) -> str:
    """Return the first visible user text for a session, if any."""

    row = conn.execute(
        select(messages.c.content_text, messages.c.content_json)
        .where(messages.c.session_id == session_id)
        .where(messages.c.type == "user")
        .order_by(messages.c.created_at.asc(), messages.c.id.asc())
        .limit(1)
    ).first()
    if row is None:
        return ""
    text = str(row[0] or "").strip()
    if text:
        return text
    try:
        content = json.loads(row[1] or "{}")
    except json.JSONDecodeError:
        return ""
    return str(content.get("text") or "").strip() if isinstance(content, dict) else ""


ANNOTATION_TYPE = "annotation"
INBOX_ACTIVITY_TYPES = types_with("inboxActivity")

# The transcript-visible types — the SINGLE source of truth shared by the
# history fetch (``list_session_messages``) AND the live ``message.new`` publish
# gate, so what a page loads and what it receives over the stream are identical.
# Excludes hidden ``assistant`` communication; tool calls live in ``agent_events``
# and ``system`` is not persisted. Harness-triggered prompts have their own type
# so they cannot be mistaken for human input. Show Page annotations in either
# direction share one explicit transcript type. ``error`` is a terminal FAILED
# result (turned the dot red): shown in the conversation like any terminal
# message, but the unread queries below stay ``result``-only so a failure is not
# counted as an unread agent reply.
TRANSCRIPT_TYPES = types_with("transcript")
_INBOX_PREVIEW_TYPES = types_with("inboxPreview")
_INBOX_SETTLES_REPLY_TYPES = types_with("inboxSettlesReply")
_DETACHED_COMPLETION_TYPES = types_with("detachedCompletion")
_UNREAD_TYPES = types_with("unread")


def _hidden_ids(session_ids: Optional[Iterable[str]]) -> list[str]:
    return sorted({str(session_id) for session_id in session_ids or () if session_id})


def unread_counts(
    conn: Connection,
    *,
    platform: Optional[str] = None,
    scope_ids: Optional[Iterable[str]] = None,
    hidden_session_ids: Optional[Iterable[str]] = None,
) -> dict[str, int]:
    """Return ``{scope_id: count}`` for unread agent ``result`` messages.

    Used by the sidebar / hover popover to show per-session unread dots
    plus the global count without dragging every row through Python.
    Filtered to ``type='result'`` so it agrees with the inbox feed's UNREAD
    count, which is also result-only — otherwise intermediate ``assistant`` /
    process-event rows in ``agent_events`` would inflate the badge past what the
    feed shows. (Inbox *eligibility* and *preview* also accept a
    terminal ``notify`` so failed turns stay visible, but a failure notify is
    not an unread reply — it never bumps this badge.)
    """

    allowed_scope_ids = list(dict.fromkeys(scope_ids)) if scope_ids is not None else None
    if allowed_scope_ids == []:
        return {}

    query = (
        select(messages.c.scope_id, func.count(messages.c.id))
        .where(messages.c.author == "agent")
        .where(messages.c.type.in_(_UNREAD_TYPES))
        .where(messages.c.read_at.is_(None))
        # Don't let an archived session's unread results inflate its scope badge
        # (keep null-session rows, which aren't attributable to any session).
        .where(
            or_(
                messages.c.session_id.is_(None),
                messages.c.session_id.not_in(
                    select(agent_sessions.c.id).where(
                        or_(
                            agent_sessions.c.status == "archived",
                            # NEGATIVE form, so it has to name every visibility the
                            # inbox admits or the badge would disagree with the feed
                            # it is supposed to count. Spelling it as "not one of the
                            # admitted values" also keeps a future fourth visibility
                            # hidden by default rather than silently badged.
                            agent_sessions.c.visibility.notin_(INBOX_SESSION_VISIBILITIES),
                        )
                    )
                ),
            )
        )
        .group_by(messages.c.scope_id)
    )
    if platform is not None:
        query = query.where(messages.c.platform == platform)
    if allowed_scope_ids is not None:
        query = query.where(messages.c.scope_id.in_(allowed_scope_ids))
    hidden = _hidden_ids(hidden_session_ids)
    if hidden:
        query = query.where(or_(messages.c.session_id.is_(None), messages.c.session_id.not_in(hidden)))
    return {scope: int(count) for scope, count in conn.execute(query).all()}


def unread_counts_by_session(
    conn: Connection,
    *,
    platform: Optional[str] = None,
    scope_ids: Optional[Iterable[str]] = None,
    hidden_session_ids: Optional[Iterable[str]] = None,
) -> dict[str, int]:
    """Return ``{session_id: count}`` for unread agent ``result`` messages.

    ``hidden_session_ids`` are Sessions this reader may not see (another member's Bots, a removed Bot's Session).

    Per-session granularity for the sidebar: a project can hold several
    sessions, so a scope-level count (see ``unread_counts``) would stamp the
    same badge on every session row. Rows with a null ``session_id`` are
    skipped — they can't be attributed to a specific session. Filtered to
    ``type='result'`` so the sidebar badge matches the inbox card's unread
    count (the realtime ``inbox.session.updated`` row is result-only too).
    """

    allowed_scope_ids = list(dict.fromkeys(scope_ids)) if scope_ids is not None else None
    if allowed_scope_ids == []:
        return {}

    query = (
        select(messages.c.session_id, func.count(messages.c.id))
        .where(messages.c.author == "agent")
        .where(messages.c.type.in_(_UNREAD_TYPES))
        .where(messages.c.read_at.is_(None))
        .where(messages.c.session_id.is_not(None))
        # Archived sessions are inert — their unread results must not light the
        # sidebar / global badge.
        .where(
            messages.c.session_id.not_in(
                select(agent_sessions.c.id).where(
                    or_(
                        agent_sessions.c.status == "archived",
                        # See ``unread_counts``: negative form, so it names the
                        # admitted set rather than one value.
                        agent_sessions.c.visibility.notin_(INBOX_SESSION_VISIBILITIES),
                    )
                )
            )
        )
        .group_by(messages.c.session_id)
    )
    if platform is not None:
        query = query.where(messages.c.platform == platform)
    if allowed_scope_ids is not None:
        query = query.where(
            messages.c.session_id.in_(
                select(agent_sessions.c.id).where(
                    agent_sessions.c.scope_id.in_(allowed_scope_ids)
                )
            )
        )
    hidden = _hidden_ids(hidden_session_ids)
    if hidden:
        query = query.where(messages.c.session_id.not_in(hidden))
    return {session_id: int(count) for session_id, count in conn.execute(query).all()}


def unread_counts_by_category(
    conn: Connection,
    *,
    platform: Optional[str] = None,
    scope_ids: Optional[Iterable[str]] = None,
    hidden_session_ids: Optional[Iterable[str]] = None,
) -> dict[str, int]:
    """Return unread result counts split by the current Bot registry."""

    per_session = unread_counts_by_session(
        conn,
        platform=platform,
        scope_ids=scope_ids,
        hidden_session_ids=hidden_session_ids,
    )
    return split_unread_counts(
        per_session,
        bot_session_ids=current_bot_session_ids(conn),
    )


def total_unread(
    conn: Connection,
    *,
    platform: Optional[str] = None,
    scope_ids: Optional[Iterable[str]] = None,
    hidden_session_ids: Optional[Iterable[str]] = None,
) -> int:
    """Global unread agent-``result`` count across all non-archived sessions.

    This is the sum of :func:`unread_counts_by_session`, i.e. the exact number
    the Inbox nav badge shows (``ui_server`` returns it as ``unread_total``). It
    is mirrored onto the installed PWA's app-icon badge — page-side while the app
    is open, and from the Web Push payload while it is closed — so the home
    screen icon never disagrees with the in-app count.
    """

    return sum(
        unread_counts_by_session(
            conn,
            platform=platform,
            scope_ids=scope_ids,
            hidden_session_ids=hidden_session_ids,
        ).values()
    )


def list_inbox_sessions(
    conn: Connection,
    *,
    platform: Optional[str] = "avibe",
    unread_only: bool = False,
    limit: int = 30,
    before: Optional[str] = None,
    only_session: Optional[str] = None,
    scope_ids: Optional[Iterable[str]] = None,
    hidden_session_ids: Optional[Iterable[str]] = None,
    category: Optional[str] = None,
) -> dict[str, Any]:
    """Per-session ("Slack-like") inbox feed.

    Bot Sessions have no scope and belong in the feed like any other conversation; ``hidden_session_ids`` names
    the ones this reader may not see (``core.bots.hidden_bot_session_ids``).

    One row per session that has at least one agent reply. Sorted by the
    session's most recent message of *any* author (the activity clock),
    descending. The preview text is the session's latest *agent* reply
    (distinct from the sort key). ``replied`` is True when the session is
    *awaiting the agent* — the latest human or harness input is newer than the
    agent's latest reply — so it stays set for the whole time the agent is
    working and survives a reload, clearing only once the agent replies.

    Keyset pagination via ``before`` (an opaque ``"<last_activity_at>|<session_id>"``
    cursor returned as ``next_cursor``).
    """

    allowed_scope_ids = list(dict.fromkeys(scope_ids)) if scope_ids is not None else None
    if allowed_scope_ids == []:
        return {"sessions": [], "next_cursor": None}

    normalized_category = normalize_category(category)
    bot_session_predicate = current_bot_session_predicate(agent_sessions.c.id)

    def _latest_message_value(
        column_name: Optional[str],
        *,
        author: Optional[str] = None,
        types: Optional[tuple[str, ...]] = None,
        conversation_only: bool = False,
        input_turn_only: bool = False,
        active_turn_reply_only: bool = False,
    ) -> Any:
        msg = messages.alias()
        order_value = transcript_order_value(msg)
        selected_value = order_value if column_name is None else getattr(msg.c, column_name)
        query = (
            select(selected_value)
            .where(msg.c.session_id == agent_sessions.c.id)
            .where(msg.c.session_id.is_not(None))
            .order_by(order_value.desc(), msg.c.id.desc())
            .limit(1)
            # Always the outer row's session. Nested in another subquery whose
            # FROM lacks ``agent_sessions`` (``last_input_turn_state``),
            # auto-correlation would instead add ``agent_sessions`` to this FROM
            # and return the latest value across every session.
            .correlate(agent_sessions)
        )
        if platform is not None:
            query = query.where(msg.c.platform == platform)
        if author is not None:
            query = query.where(msg.c.author == author)
        if types is not None:
            query = query.where(msg.c.type.in_(types))
        if active_turn_reply_only:
            query = query.where(
                ~and_(
                    msg.c.type.in_(_DETACHED_COMPLETION_TYPES),
                    func.coalesce(func.json_extract(msg.c.metadata_json, "$.detached"), 0)
                    == 1,
                )
            )
        if input_turn_only:
            query = query.where(
                or_(
                    *(
                        and_(msg.c.author == input_author, msg.c.type == input_type)
                        for input_author, input_type in INPUT_TURN_AUTHOR_TYPES
                    )
                )
            )
        if conversation_only:
            query = query.where(msg.c.type.in_(INBOX_ACTIVITY_TYPES))
        return query.scalar_subquery()

    # Drive from the small session set and do top-1 index probes per session.
    # This preserves the inbox contract while avoiding full message-window
    # materialization as history grows.
    last_activity_at = _latest_message_value(None, conversation_only=True)
    last_author = _latest_message_value("author", conversation_only=True)
    preview_id = _latest_message_value("id", types=_INBOX_PREVIEW_TYPES)
    preview_at = _latest_message_value(None, types=_INBOX_PREVIEW_TYPES)
    last_terminal_id = _latest_message_value(
        "id",
        types=_INBOX_SETTLES_REPLY_TYPES,
        active_turn_reply_only=True,
    )
    last_terminal_at = _latest_message_value(
        None,
        types=_INBOX_SETTLES_REPLY_TYPES,
        active_turn_reply_only=True,
    )
    last_turn_terminal_id = (
        select(session_turns.c.id)
        .where(
            session_turns.c.session_id == agent_sessions.c.id,
            session_turns.c.state == "terminal",
            session_turns.c.terminal_outcome != "not_written",
        )
        .order_by(
            func.julianday(session_turns.c.terminal_at).desc(),
            session_turns.c.id.desc(),
        )
        .limit(1)
        .scalar_subquery()
    )
    last_turn_terminal_at = (
        select(session_turns.c.terminal_at)
        .where(
            session_turns.c.session_id == agent_sessions.c.id,
            session_turns.c.state == "terminal",
            session_turns.c.terminal_outcome != "not_written",
        )
        .order_by(
            func.julianday(session_turns.c.terminal_at).desc(),
            session_turns.c.id.desc(),
        )
        .limit(1)
        .scalar_subquery()
    )
    last_input_at = _latest_message_value(
        None, conversation_only=True, input_turn_only=True
    )
    last_input_id = _latest_message_value("id", conversation_only=True, input_turn_only=True)
    last_input_turn_state = (
        select(session_turns.c.state)
        .select_from(
            message_deliveries.join(
                session_turns,
                session_turns.c.id == message_deliveries.c.turn_id,
            )
        )
        .where(message_deliveries.c.message_id == last_input_id)
        # The input's Delivery is this session's: seek by session rather than
        # walk every Delivery for one message id, which has no index.
        .where(message_deliveries.c.session_id == agent_sessions.c.id)
        .correlate(agent_sessions)
        .limit(1)
        .scalar_subquery()
    )

    # Unread agent messages per session.
    m = messages
    unread_q = (
        select(m.c.session_id.label("session_id"), func.count().label("unread_count"))
        .where(m.c.session_id.is_not(None))
        .where(m.c.author == "agent")
        .where(m.c.type.in_(_UNREAD_TYPES))
        .where(m.c.read_at.is_(None))
        .group_by(m.c.session_id)
    )
    if platform is not None:
        unread_q = unread_q.where(m.c.platform == platform)
    if only_session:
        # One session's row counts only that session's unread messages, rather
        # than grouping every session's and joining one back.
        unread_q = unread_q.where(m.c.session_id == only_session)
    unread_sub = unread_q.subquery()

    unread_count_col = func.coalesce(unread_sub.c.unread_count, 0).label("unread_count")
    session_rows = (
        select(
            agent_sessions.c.id.label("session_id"),
            last_activity_at.label("last_activity_at"),
            last_author.label("last_author"),
            agent_sessions.c.title,
            agent_sessions.c.scope_id,
            scopes.c.native_id.label("project_id"),
            scopes.c.display_name.label("project_name"),
            category_expression(agent_sessions.c.id).label("category"),
            unread_count_col,
            preview_id.label("preview_id"),
            preview_at.label("preview_at"),
            last_terminal_id.label("last_terminal_id"),
            last_terminal_at.label("last_terminal_at"),
            last_turn_terminal_id.label("last_turn_terminal_id"),
            last_turn_terminal_at.label("last_turn_terminal_at"),
            last_input_at.label("last_input_at"),
            last_input_id.label("last_input_id"),
            last_input_turn_state.label("last_input_turn_state"),
        )
        .select_from(
            agent_sessions.join(scopes, scopes.c.id == agent_sessions.c.scope_id, isouter=True).join(
                unread_sub, unread_sub.c.session_id == agent_sessions.c.id, isouter=True
            )
        )
    )
    # Archived sessions are hidden everywhere — keep them out of the inbox feed too.
    # Visibility is the ADMISSION list, not "foreground": the runtime's
    # workspace-notifications row is ``system`` — deliberately absent from every
    # ordinary session list — and the inbox is the surface it is delivered on, so
    # excluding it here would hide the notice this session exists to show while the
    # notice was still recorded as sent. ``background`` remains excluded (it also
    # sets ``suppress_delivery``).
    session_rows = session_rows.where(
        agent_sessions.c.status != "archived",
        agent_sessions.c.visibility.in_(INBOX_SESSION_VISIBILITIES),
    )
    if only_session:
        session_rows = session_rows.where(agent_sessions.c.id == only_session)
    if allowed_scope_ids is not None:
        session_rows = session_rows.where(agent_sessions.c.scope_id.in_(allowed_scope_ids))
    if normalized_category == CATEGORY_BOT:
        session_rows = session_rows.where(bot_session_predicate)
    elif normalized_category == CATEGORY_SESSION:
        session_rows = session_rows.where(~bot_session_predicate)

    hidden = _hidden_ids(hidden_session_ids)
    if hidden:
        session_rows = session_rows.where(agent_sessions.c.id.not_in(hidden))

    session_rows_sub = session_rows.subquery()
    query = select(session_rows_sub).where(session_rows_sub.c.preview_id.is_not(None))
    if unread_only:
        query = query.where(session_rows_sub.c.unread_count > 0)
    if before:
        cursor_at, _, cursor_session = before.partition("|")
        if cursor_at and cursor_session:
            query = query.where(
                or_(
                    session_rows_sub.c.last_activity_at < cursor_at,
                    and_(
                        session_rows_sub.c.last_activity_at == cursor_at,
                        session_rows_sub.c.session_id < cursor_session,
                    ),
                )
            )

    effective_limit = min(max(int(limit), 1), 100)
    query = query.order_by(
        session_rows_sub.c.last_activity_at.desc(), session_rows_sub.c.session_id.desc()
    ).limit(effective_limit)
    limited_sessions = query.subquery()
    preview_msg = messages.alias()
    query = (
        select(
            limited_sessions,
            preview_msg.c.content_text.label("preview_text"),
            preview_msg.c.content_json.label("preview_json"),
        )
        .select_from(limited_sessions.join(preview_msg, preview_msg.c.id == limited_sessions.c.preview_id))
        .order_by(limited_sessions.c.last_activity_at.desc(), limited_sessions.c.session_id.desc())
    )

    rows = conn.execute(query).mappings().all()
    sessions: list[dict[str, Any]] = []
    for row in rows:
        preview = row["preview_text"]
        if not preview and row["preview_json"]:
            try:
                preview = (json.loads(row["preview_json"]) or {}).get("text") or ""
            except json.JSONDecodeError:
                preview = ""
        unread = int(row["unread_count"] or 0)
        # Awaiting the agent: the latest human or harness input is newer than the
        # agent's latest reply. Persistent across a reload and stays set for the whole
        # agent turn, unlike a literal "last author" check. The timestamp values
        # here are the same acceptance-or-creation positions used by the transcript;
        # id remains the stable tie-break for legacy second-resolution rows.
        last_input_at = row["last_input_at"]
        last_input_id = row["last_input_id"]
        # Execution settlement belongs to SessionTurn. A visible result Message
        # can be later (for example after delivery retries), so use the newest
        # semantic terminal evidence without creating an invisible Message marker.
        terminal_candidates = [
            (row["last_terminal_at"], row["last_terminal_id"]),
            (row["last_turn_terminal_at"], row["last_turn_terminal_id"]),
        ]
        terminal_at, terminal_id = max(
            (candidate for candidate in terminal_candidates if candidate[0] is not None),
            key=lambda candidate: _timestamp_key(candidate[0], candidate[1]),
            default=(None, None),
        )
        accepted_turn_state = str(row["last_input_turn_state"] or "")
        if accepted_turn_state:
            awaiting_reply = accepted_turn_state in {"starting", "active"}
        else:
            awaiting_reply = bool(
                last_input_at is not None
                and terminal_at is not None
                and _timestamp_key(last_input_at, last_input_id)
                > _timestamp_key(terminal_at, terminal_id)
            )
        sessions.append(
            {
                "session_id": row["session_id"],
                "scope_id": row["scope_id"],
                "project_id": row["project_id"],
                "project_name": row["project_name"],
                "title": row["title"],
                "last_activity_at": row["last_activity_at"],
                "last_message_author": row["last_author"],
                "replied": awaiting_reply,
                "preview_text": preview or "",
                "preview_at": row["preview_at"],
                "unread_count": unread,
                "unread": unread > 0,
                "category": row["category"],
            }
        )

    next_cursor = None
    if len(sessions) == effective_limit:
        tail = sessions[-1]
        next_cursor = f"{tail['last_activity_at']}|{tail['session_id']}"
    return {"sessions": sessions, "next_cursor": next_cursor}


def get_inbox_session(
    conn: Connection,
    session_id: str,
    *,
    platform: Optional[str] = "avibe",
) -> Optional[dict[str, Any]]:
    """Return one session's inbox row (or None if it has no agent ``result`` /
    terminal ``notify`` yet). Used to build realtime ``inbox.session.updated``
    payloads."""
    rows = list_inbox_sessions(conn, platform=platform, only_session=session_id, limit=1)["sessions"]
    return rows[0] if rows else None


def mark_session_read(
    conn: Connection,
    session_id: str,
    *,
    until_message_id: Optional[str] = None,
) -> int:
    """Mark unread agent messages in a session as read, up to ``until_message_id``.

    Returns the number of rows updated.
    """

    now = _utc_now_iso()
    base = (
        update(messages)
        .where(messages.c.session_id == session_id)
        .where(messages.c.author == "agent")
        .where(messages.c.read_at.is_(None))
        .values(read_at=now, updated_at=now)
    )
    if until_message_id:
        anchor = conn.execute(
            select(transcript_order_value()).where(messages.c.id == until_message_id)
        ).scalar_one_or_none()
        if anchor is not None:
            order_value = transcript_order_value()
            base = base.where(
                or_(
                    order_value < anchor,
                    and_(
                        order_value == anchor,
                        messages.c.id <= until_message_id,
                    ),
                )
            )
    result = conn.execute(base)
    return result.rowcount or 0


def list_messages_for_inbox_scope(
    conn: Connection,
    scope_id: str,
    *,
    limit: int = 1,
) -> Iterable[dict[str, Any]]:
    """Return the latest N messages for a given scope (for inbox previews)."""

    query = (
        select(messages)
        .where(messages.c.scope_id == scope_id)
        .order_by(transcript_order_value().desc(), messages.c.id.desc())
        .limit(min(max(int(limit), 1), 50))
    )
    return [_row_to_payload(dict(row)) for row in conn.execute(query).mappings().all()]