Skip to content

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

5.7 KB
Raw Blame
"""Where a setup interview lives between requests.

The interview used to be held only in the API process, so a restart (a
deploy, a crash) or a second worker lost it, and the person started setup
again from the first question. Each session is now written through to the
episodic SQLite database after every turn and read back when the process
does not hold it. Rows carry the same TTL and cap the in-memory table has,
and a saved session's row is deleted at once: the transcript is unedited
free text with the company's financials in it, and the profile it produced
is the artifact worth keeping.
"""

from __future__ import annotations

import contextlib
import json
import logging
import sqlite3
import time
from pathlib import Path
from typing import TYPE_CHECKING, Any

from openexecutive.memory import episodic

if TYPE_CHECKING:
    from openexecutive.api.routes.onboarding import InterviewSession

logger = logging.getLogger(__name__)

# Tests point this at a temp file; None means the episodic database.
DB_PATH: Path | None = None

TTL_SECONDS = 2 * 3600
MAX_ROWS = 50


def _path() -> Path:
    return DB_PATH if DB_PATH is not None else episodic.DB_PATH


def _conn() -> contextlib.closing[sqlite3.Connection]:
    """A connection that commits on success and is closed either way: the
    sqlite3 context manager alone only commits or rolls back."""
    conn = sqlite3.connect(str(_path()))
    # Deleted transcripts are zeroed, not left in free pages of the file.
    conn.execute("PRAGMA secure_delete=ON")
    conn.execute(
        "CREATE TABLE IF NOT EXISTS onboarding_interviews ("
        " session_id TEXT PRIMARY KEY,"
        " payload TEXT NOT NULL,"
        " updated_at REAL NOT NULL)"
    )
    return contextlib.closing(conn)


def _sweep(conn: sqlite3.Connection, now: float) -> None:
    """Drop expired rows and everything past the cap. Runs on every call, so
    an abandoned interview goes even when no other setup ever starts."""
    conn.execute("DELETE FROM onboarding_interviews WHERE updated_at < ?", (now - TTL_SECONDS,))
    conn.execute(
        "DELETE FROM onboarding_interviews WHERE session_id NOT IN ("
        " SELECT session_id FROM onboarding_interviews ORDER BY updated_at DESC LIMIT ?)",
        (MAX_ROWS,),
    )


def to_payload(session: InterviewSession) -> dict[str, Any]:
    return {
        "owner_key": session.owner_key,
        "transcript": [t.model_dump() for t in session.transcript],
        "questions_asked": session.questions_asked,
        "draft": session.draft.model_dump(mode="json") if session.draft is not None else None,
        "last_hint": session.last_hint,
        "last_options": list(session.last_options),
        "website": session.website,
        "saved": session.saved,
    }


def from_payload(payload: dict[str, Any]) -> InterviewSession:
    from openexecutive.api.routes.onboarding import InterviewSession
    from openexecutive.onboarding.interview import CompanyDraft, Turn

    draft = payload.get("draft")
    return InterviewSession(
        owner_key=str(payload.get("owner_key", "")),
        transcript=[Turn.model_validate(t) for t in payload.get("transcript", [])],
        questions_asked=int(payload.get("questions_asked", 0)),
        draft=CompanyDraft.model_validate(draft) if draft else None,
        last_hint=str(payload.get("last_hint", "")),
        last_options=[str(o) for o in payload.get("last_options") or [] if isinstance(o, str)],
        website=str(payload.get("website") or ""),
        saved=bool(payload.get("saved", False)),
    )


def save(session_id: str, session: InterviewSession) -> None:
    """Write the session through; a failure only costs the resume."""
    now = time.time()
    session.stored_at = now
    try:
        with _conn() as conn, conn:
            conn.execute(
                "INSERT OR REPLACE INTO onboarding_interviews (session_id, payload, updated_at) "
                "VALUES (?, ?, ?)",
                (session_id, json.dumps(to_payload(session)), now),
            )
            _sweep(conn, now)
    except (OSError, sqlite3.Error) as exc:
        logger.warning("onboarding interview: could not persist (%s)", type(exc).__name__)


def load(session_id: str, *, newer_than: float | None = None) -> InterviewSession | None:
    """The stored session, or None when there is none or it has expired.

    With ``newer_than``, only a row written after that time (a save by
    another process) comes back, so a caller holding its own copy learns
    whether it is stale without paying for the row otherwise.
    """
    try:
        with _conn() as conn, conn:
            _sweep(conn, time.time())
            row = conn.execute(
                "SELECT payload, updated_at FROM onboarding_interviews WHERE session_id = ?",
                (session_id,),
            ).fetchone()
    except (OSError, sqlite3.Error) as exc:
        logger.warning("onboarding interview: could not read (%s)", type(exc).__name__)
        return None
    if row is None or time.time() - float(row[1]) > TTL_SECONDS:
        return None
    if newer_than is not None and float(row[1]) <= newer_than:
        return None
    try:
        session = from_payload(json.loads(row[0]))
        session.stored_at = float(row[1])
        return session
    except (ValueError, TypeError, AttributeError) as exc:
        logger.warning("onboarding interview: stored row unreadable (%s)", type(exc).__name__)
        return None


def delete(session_id: str) -> None:
    try:
        with _conn() as conn, conn:
            conn.execute("DELETE FROM onboarding_interviews WHERE session_id = ?", (session_id,))
            _sweep(conn, time.time())
    except (OSError, sqlite3.Error) as exc:
        logger.warning("onboarding interview: could not delete (%s)", type(exc).__name__)