Skip to content

AI-powered virtual executive team — a single coherent executive persona backed by 8 specialist agents (FastAPI + Next.js).

14.9 KB
Raw Blame
"""Topics that group one person's chats on their Chats page.

A chat's topic is chosen by the model when the chat is named
(``utils.chat_topics``), and the owner can move a chat, rename, pin, merge or
ungroup a topic. What the owner does sticks: a chat they filed
(``topic_source = 'owner'``) is never retagged, and a topic they touched
(``kept``) is never merged away by the tidy.

Related topics share an ``area`` (one property, deal or client), which the
Chats page shows as one heading over them. The model sets it; an area the
owner renamed or ungrouped (``area_kept``) is left as they made it.
"""
from __future__ import annotations

import re
from datetime import UTC, datetime
from pathlib import Path
from typing import Any

from openexecutive.memory.episodic import DB_PATH, _get_conn

# Longest topic name kept; the model is asked for two to four words.
MAX_TOPIC_CHARS = 40
# Topics shown to the model when it picks one: the ones used most recently.
PROMPT_TOPICS = 60


def clean_topic_name(raw: str | None) -> str | None:
    """One line, no quotes or end punctuation, capped. None when nothing is
    left, or the model said no topic fits."""
    if not raw:
        return None
    name = re.sub(r"\s+", " ", raw).strip().strip("\"'").rstrip(".!?,:;").strip()
    name = "".join(c for c in name if c.isprintable())[:MAX_TOPIC_CHARS].strip()
    if not name or name.lower() in {"none", "-", "n/a", "other"}:
        return None
    return name


def _now() -> str:
    return datetime.now(UTC).isoformat()


def topic_names(owner: int, db_path: Path = DB_PATH) -> list[str]:
    """The owner's topic names, most recently used first, for the prompt."""
    with _get_conn(db_path) as conn:
        rows = conn.execute(
            """
            SELECT t.name FROM chat_topics t
            LEFT JOIN sessions s ON s.topic_id = t.id
            WHERE t.owner_person_id = ?
            GROUP BY t.id
            ORDER BY MAX(COALESCE(s.updated_at, t.created_at)) DESC
            LIMIT ?
            """,
            (owner, PROMPT_TOPICS),
        ).fetchall()
    return [str(r["name"]) for r in rows]


def _topic_id(conn: Any, owner: int, name: str, kept: bool) -> tuple[int, bool]:
    # OR IGNORE: the backfill and a new chat's naming may make the same
    # topic at once; the second just finds the first's.
    made = conn.execute(
        "INSERT OR IGNORE INTO chat_topics (owner_person_id, name, kept, created_at) "
        "VALUES (?, ?, ?, ?)",
        (owner, name, 1 if kept else 0, _now()),
    ).rowcount > 0
    row = conn.execute(
        "SELECT id FROM chat_topics WHERE owner_person_id = ? AND name = ?",
        (owner, name),
    ).fetchone()
    if kept and not made:
        conn.execute("UPDATE chat_topics SET kept = 1 WHERE id = ?", (row["id"],))
    return int(row["id"]), made


def set_auto_topic(
    session_id: str,
    owner: int,
    name: str | None,
    db_path: Path = DB_PATH,
    *,
    only_untagged: bool = False,
) -> tuple[int | None, bool]:
    """File a chat under the model's topic (made if new; None = none fits).
    A chat the owner filed is left as it is, and with ``only_untagged`` so is
    any chat already filed. Returns the topic id and whether
    the topic was just made; (None, False) when nothing changed or none fits.
    One transaction, so a list running meanwhile never drops the new topic
    as empty."""
    with _get_conn(db_path) as conn:
        row = conn.execute(
            "SELECT topic_source FROM sessions WHERE session_id = ? AND caller_person_id = ?",
            (session_id, owner),
        ).fetchone()
        if row is None or row["topic_source"] == "owner":
            return None, False
        # The backfill fills only chats still waiting: a new chat's own naming
        # may have filed it while the batch call ran.
        if only_untagged and row["topic_source"] is not None:
            return None, False
        topic_id, made = _topic_id(conn, owner, name, False) if name else (None, False)
        conn.execute(
            "UPDATE sessions SET topic_id = ?, topic_source = 'auto' WHERE session_id = ?",
            (topic_id, session_id),
        )
        return topic_id, made


def file_session(
    session_id: str,
    owner: int,
    *,
    topic_id: int | None = None,
    name: str | None = None,
    db_path: Path = DB_PATH,
) -> bool:
    """The owner puts their chat under one of their topics (by id, or by a
    name, made if new) or under none. Kept so. False when the topic id isn't
    theirs."""
    with _get_conn(db_path) as conn:
        if name:
            topic_id, _ = _topic_id(conn, owner, name, True)
        elif topic_id is not None:
            if conn.execute(
                "SELECT 1 FROM chat_topics WHERE id = ? AND owner_person_id = ?",
                (topic_id, owner),
            ).fetchone() is None:
                return False
            conn.execute("UPDATE chat_topics SET kept = 1 WHERE id = ?", (topic_id,))
        conn.execute(
            "UPDATE sessions SET topic_id = ?, topic_source = 'owner' WHERE session_id = ?",
            (topic_id, session_id),
        )
        return True


def topic_id_by_name(owner: int, name: str, db_path: Path = DB_PATH) -> int | None:
    with _get_conn(db_path) as conn:
        row = conn.execute(
            "SELECT id FROM chat_topics WHERE owner_person_id = ? AND name = ?",
            (owner, name),
        ).fetchone()
    return int(row["id"]) if row is not None else None


def owned_topic(owner: int, topic_id: int, db_path: Path = DB_PATH) -> dict[str, Any] | None:
    with _get_conn(db_path) as conn:
        row = conn.execute(
            "SELECT id, name, kept, pinned_at FROM chat_topics WHERE id = ? AND owner_person_id = ?",
            (topic_id, owner),
        ).fetchone()
    return dict(row) if row is not None else None


def list_topics(owner: int, db_path: Path = DB_PATH) -> list[dict[str, Any]]:
    """The owner's topics that hold a chat: pinned first, then by their
    newest chat. Topics left empty (their chats deleted or moved) go."""
    with _get_conn(db_path) as conn:
        conn.execute(
            "DELETE FROM chat_topics WHERE owner_person_id = ? AND id NOT IN "
            "(SELECT topic_id FROM sessions WHERE topic_id IS NOT NULL)",
            (owner,),
        )
        rows = conn.execute(
            """
            SELECT t.id, t.name, t.kept, t.pinned_at, t.area, COUNT(s.session_id) AS chat_count,
                   MAX(s.updated_at) AS updated_at
            FROM chat_topics t JOIN sessions s ON s.topic_id = t.id
            WHERE t.owner_person_id = ? AND s.caller_person_id = ?
            GROUP BY t.id
            ORDER BY t.pinned_at IS NULL, t.pinned_at DESC, updated_at DESC
            """,
            (owner, owner),
        ).fetchall()
    out: list[dict[str, Any]] = []
    for r in rows:
        item: dict[str, Any] = {
            "id": int(r["id"]),
            "name": str(r["name"]),
            "kept": bool(r["kept"]),
            "chat_count": int(r["chat_count"]),
        }
        if r["pinned_at"]:
            item["pinned_at"] = r["pinned_at"]
        if r["area"]:
            item["area"] = str(r["area"])
        if r["updated_at"]:
            item["updated_at"] = str(r["updated_at"])
        out.append(item)
    return out


def rename_topic(owner: int, topic_id: int, name: str, db_path: Path = DB_PATH) -> int | None:
    """Rename the owner's topic. A name another of their topics has merges
    this one into it. Returns the topic the chats are under now, None when
    there is no such topic."""
    if owned_topic(owner, topic_id, db_path) is None:
        return None
    with _get_conn(db_path) as conn:
        other = conn.execute(
            "SELECT id FROM chat_topics WHERE owner_person_id = ? AND name = ? AND id != ?",
            (owner, name, topic_id),
        ).fetchone()
    if other is not None:
        merge_topics(owner, topic_id, int(other["id"]), db_path)
        with _get_conn(db_path) as conn:
            conn.execute("UPDATE chat_topics SET kept = 1 WHERE id = ?", (other["id"],))
        return int(other["id"])
    with _get_conn(db_path) as conn:
        conn.execute(
            "UPDATE chat_topics SET name = ?, kept = 1 WHERE id = ?", (name, topic_id)
        )
    return topic_id


def keep_topic(owner: int, topic_id: int, db_path: Path = DB_PATH) -> None:
    with _get_conn(db_path) as conn:
        conn.execute(
            "UPDATE chat_topics SET kept = 1 WHERE id = ? AND owner_person_id = ?",
            (topic_id, owner),
        )


def pin_topic(owner: int, topic_id: int, pinned: bool, db_path: Path = DB_PATH) -> bool:
    with _get_conn(db_path) as conn:
        cur = conn.execute(
            "UPDATE chat_topics SET pinned_at = CASE WHEN ? THEN COALESCE(pinned_at, ?) END, "
            "kept = CASE WHEN ? THEN 1 ELSE kept END "
            "WHERE id = ? AND owner_person_id = ?",
            (1 if pinned else 0, _now(), 1 if pinned else 0, topic_id, owner),
        )
        return cur.rowcount > 0


def merge_topics(owner: int, source_id: int, into_id: int, db_path: Path = DB_PATH) -> bool:
    """Move every chat under ``source_id`` to ``into_id`` and drop the
    source. Both must be the owner's. A chat keeps who filed it."""
    if source_id == into_id:
        return owned_topic(owner, into_id, db_path) is not None
    if owned_topic(owner, source_id, db_path) is None or owned_topic(owner, into_id, db_path) is None:
        return False
    with _get_conn(db_path) as conn:
        conn.execute("UPDATE sessions SET topic_id = ? WHERE topic_id = ?", (into_id, source_id))
        conn.execute("DELETE FROM chat_topics WHERE id = ?", (source_id,))
    return True


def ungroup_topic(owner: int, topic_id: int, db_path: Path = DB_PATH) -> bool:
    """Drop the owner's topic; its chats go under no topic and stay there."""
    if owned_topic(owner, topic_id, db_path) is None:
        return False
    with _get_conn(db_path) as conn:
        conn.execute(
            "UPDATE sessions SET topic_id = NULL, topic_source = 'owner' WHERE topic_id = ?",
            (topic_id,),
        )
        conn.execute("DELETE FROM chat_topics WHERE id = ?", (topic_id,))
    return True


def owner_only(session_id: str, owner: int, db_path: Path = DB_PATH) -> bool:
    """Whether only the owner has written in this chat and it is shared with
    no one, so a prompt may carry the owner's topic names and its title may
    show them: anyone else could steer the call into echoing them. A line a
    host origin's page sent is someone else's."""
    with _get_conn(db_path) as conn:
        if conn.execute(
            "SELECT 1 FROM session_shares WHERE session_id = ? LIMIT 1", (session_id,)
        ).fetchone() is not None:
            return False
        other = conn.execute(
            "SELECT 1 FROM chat_messages WHERE session_id = ? AND role = 'user' "
            "AND ((sender_person_id IS NOT NULL AND sender_person_id != ?) "
            "OR sender_surface IS NOT NULL) LIMIT 1",
            (session_id, owner),
        ).fetchone() or conn.execute(
            # A host line taken back and sent again is saved as the owner's.
            "SELECT 1 FROM sessions WHERE session_id = ? AND host_wrote = 1", (session_id,)
        ).fetchone()
    return other is None


def has_untagged(owner: int, db_path: Path = DB_PATH) -> bool:
    """Whether any of the owner's chats with a message waits for a topic."""
    with _get_conn(db_path) as conn:
        row = conn.execute(
            """
            SELECT 1 FROM sessions s
            WHERE s.caller_person_id = ? AND s.topic_source IS NULL
              AND EXISTS (SELECT 1 FROM chat_messages m WHERE m.session_id = s.session_id)
            LIMIT 1
            """,
            (owner,),
        ).fetchone()
    return row is not None


def untagged_sessions(owner: int, db_path: Path = DB_PATH) -> list[dict[str, str]]:
    """The owner's chats no one has looked at for a topic yet, newest first,
    leaving out ones with no message (a chat-app /new not used yet)."""
    with _get_conn(db_path) as conn:
        rows = conn.execute(
            """
            SELECT s.session_id, s.title FROM sessions s
            WHERE s.caller_person_id = ? AND s.topic_source IS NULL
              AND EXISTS (SELECT 1 FROM chat_messages m WHERE m.session_id = s.session_id)
            ORDER BY s.updated_at DESC
            """,
            (owner,),
        ).fetchall()
    return [{"session_id": str(r["session_id"]), "title": str(r["title"])} for r in rows]


def topics_with_areas(owner: int, db_path: Path = DB_PATH) -> list[dict[str, Any]]:
    """The owner's topics that hold a chat, with their area ('' = none,
    None = not looked at) and whether the owner set it, most recently used
    first, for the area prompts."""
    with _get_conn(db_path) as conn:
        rows = conn.execute(
            """
            SELECT t.id, t.name, t.area, t.area_kept FROM chat_topics t
            JOIN sessions s ON s.topic_id = t.id
            WHERE t.owner_person_id = ?
            GROUP BY t.id
            ORDER BY MAX(s.updated_at) DESC
            LIMIT ?
            """,
            (owner, PROMPT_TOPICS),
        ).fetchall()
    return [
        {"id": int(r["id"]), "name": str(r["name"]), "area": r["area"], "area_kept": bool(r["area_kept"])}
        for r in rows
    ]


def needs_areas(owner: int, db_path: Path = DB_PATH) -> bool:
    """Whether any topic holding a chat waits for its area to be looked at,
    once the owner has enough topics (three) for areas to mean anything."""
    topics = topics_with_areas(owner, db_path)
    return len(topics) >= 3 and any(t["area"] is None for t in topics)


def set_area(owner: int, topic_ids: list[int], area: str | None, db_path: Path = DB_PATH) -> int:
    """Put the owner's topics under ``area`` ('' when None: looked at, none).
    Topics whose area the owner set are left alone. Returns how many moved."""
    if not topic_ids:
        return 0
    marks = ",".join("?" for _ in topic_ids)
    with _get_conn(db_path) as conn:
        cur = conn.execute(
            f"UPDATE chat_topics SET area = ? WHERE owner_person_id = ? AND area_kept = 0 AND id IN ({marks})",
            (area or "", owner, *topic_ids),
        )
        return int(cur.rowcount)


def rename_area(owner: int, area: str, name: str, db_path: Path = DB_PATH) -> int:
    """Rename one of the owner's areas (an existing one's name joins the
    two). Kept so. Returns how many topics it holds."""
    with _get_conn(db_path) as conn:
        cur = conn.execute(
            "UPDATE chat_topics SET area = ?, area_kept = 1 WHERE owner_person_id = ? AND area = ?",
            (name, owner, area),
        )
        return int(cur.rowcount)


def ungroup_area(owner: int, area: str, db_path: Path = DB_PATH) -> int:
    """Drop one of the owner's areas: its topics stand on their own, and the
    model doesn't put them back under one. Returns how many it held."""
    with _get_conn(db_path) as conn:
        cur = conn.execute(
            "UPDATE chat_topics SET area = '', area_kept = 1 WHERE owner_person_id = ? AND area = ?",
            (owner, area),
        )
        return int(cur.rowcount)