Skip to content

Self-improving AI Employees for everyone

211 KB
Raw Blame
"""Modern async database service with SQLModel and SQLAlchemy 2.0."""

import asyncio
import json
import inspect
import secrets
from copy import deepcopy
from datetime import datetime, timedelta, timezone
from typing import Awaitable, Callable, Dict, Any, List, Optional, Tuple, Union
from pydantic_core import to_jsonable_python
from sqlmodel import SQLModel, select
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.exc import IntegrityError
from sqlalchemy import case, text, func, update, or_
from contextlib import asynccontextmanager

from core.session_teardown import teardown_session as _teardown_session

from core.config import Settings
from models.database import (
    NodeParameter,
    RuntimeMutation,
    IdentitySequence,
    Workflow,
    Execution,
    APIKey,
    APIKeyValidation,
    NodeOutput,
    ConversationMessage,
    ToolSchema,
    UserSkill,
    ChatMessage,
    UserSettings,
    TokenUsageMetric,
    CompactionEvent,
    SessionTokenState,
    ProviderDefaults,
    AgentTeam,
    TeamMember,
    TeamTask,
    TeamTaskAttempt,
    AgentMessage,
    SubagentConcurrencyCounter,
    SubagentConcurrencyPermit,
    GoogleConnection,
    ProxyProviderConfig,
    ProxyRoutingRule,
    WorkflowControlExecution,
    WorkflowContextArchiveOutbox,
)
from models.agent_context import (  # noqa: F401 - registers SQLModel tables
    AgentConversation,
)
from models.pi_checkpoint import PiRuntimeBlob  # noqa: F401 - immutable Pi runtime caches
from models.employees import (  # noqa: F401 - registers SQLModel tables
    Employee,
    WorkflowRunRecord,
    EmployeeActivation,
    EmployeeGrant,
    EmployeeJob,
    EmployeeApply,
    WorkflowQueuedEvent,
)
from models.employee_conversion import EmployeeConversion  # noqa: F401
from models.attention import AttentionItem  # noqa: F401 - registers SQLModel tables
from models.approvals import (  # noqa: F401 - registers SQLModel tables
    ApprovalDecision,
    ApprovalRequest,
    WorkflowRule,
)
from models.chat import (  # noqa: F401 - registers SQLModel tables
    ChatBranchSnapshot,
    ChatFeedback,
    ChatNote,
    ChatRun,
    ChatRunPart,
    ChatThread,
)
from models.cache import CacheEntry  # SQLite-backed cache for Redis alternative
from models.workspace_tasks import WorkspaceTaskRecord  # noqa: F401
from models.credential_sources import CredentialSource, BrowserCredentialBinding  # noqa: F401
from models.browser_owners import BrowserOwner, BrowserProfileOwner, BrowserTransientRoute  # noqa: F401
from models.browser_profiles import BrowserProfileRow  # noqa: F401
from models.auth import User  # noqa: F401
from core.logging import get_logger

logger = get_logger(__name__)

#: ``Database.append_chat_row``'s default: the new message follows the
#: thread's active leaf.
FOLLOW_LEAF: Any = object()

RuntimeMutationCallback = Callable[
    [AsyncSession],
    Union[Optional[Dict[str, Any]], Awaitable[Optional[Dict[str, Any]]]],
]
ParameterMutator = Callable[
    [Dict[str, Any]],
    Union[
        Dict[str, Any],
        Tuple[Dict[str, Any], Optional[Dict[str, Any]]],
    ],
]


def _rebuild_chat_lane_index(sync_connection) -> None:
    """Make PostgreSQL's ``ux_chat_runs_lane`` the partial index the model
    declares. Shared databases created before the model named its PostgreSQL
    condition got a plain ``UNIQUE(session_id)``, which refuses every run
    after a conversation's first."""
    definition = sync_connection.execute(text(
        "SELECT indexdef FROM pg_indexes WHERE schemaname = current_schema() AND indexname = 'ux_chat_runs_lane'"
    )).scalar()
    if definition is not None and " WHERE " in definition:
        return
    index = next(index for index in ChatRun.__table__.indexes if index.name == "ux_chat_runs_lane")
    if definition is not None:
        index.drop(sync_connection)
    index.create(sync_connection)


def _workflow_context_node_ids(data: Any) -> set[str]:
    if not isinstance(data, dict):
        return set()
    return {str(node.get("id")) for node in data.get("nodes") or [] if isinstance(node, dict) and node.get("type") == "context" and node.get("id")}


class Database:
    """Async database service with SQLModel."""

    def __init__(self, settings: Settings):
        self.settings = settings
        self.engine = None
        self.async_session = None

    async def startup(self):
        """Initialize database connection and create tables."""
        try:
            # Disable verbose database and asyncio logging
            import logging

            logging.getLogger("aiosqlite").setLevel(logging.WARNING)
            logging.getLogger("sqlalchemy.engine").setLevel(logging.WARNING)
            logging.getLogger("sqlalchemy.dialects").setLevel(logging.WARNING)
            logging.getLogger("sqlalchemy.pool").setLevel(logging.WARNING)

            # Create async engine.
            #
            # ``json_serializer`` is SQLAlchemy's documented extension point
            # for every JSON column on this engine (node_outputs.data,
            # node_parameters, ...). ``pydantic_core.to_jsonable_python`` is
            # Pydantic's official arbitrary-object → JSON coercion
            # (dataclasses, BaseModel, datetime, enums, sets natively;
            # ``fallback=str`` for true unknowns) — stdlib ``json.dumps``
            # alone raised ``TypeError: Object of type X is not JSON
            # serializable`` and silently dropped node-output persistence.
            self.engine = create_async_engine(
                self.settings.database_url,
                echo=self.settings.database_echo,
                pool_size=self.settings.database_pool_size,
                max_overflow=self.settings.database_max_overflow,
                future=True,
                json_serializer=lambda obj: json.dumps(to_jsonable_python(obj, fallback=str)),
            )

            # Create session factory
            self.async_session = async_sessionmaker(bind=self.engine, class_=AsyncSession, expire_on_commit=False)

            # Create tables
            async with self.engine.begin() as conn:
                if self.engine.dialect.name == "postgresql":
                    from core.shared_database import install_postgresql_timestamp_types
                    install_postgresql_timestamp_types(SQLModel.metadata)
                    await conn.execute(text("SELECT pg_advisory_xact_lock(763746822)"))
                await conn.run_sync(SQLModel.metadata.create_all)
                # Earlier development builds of the owner registry predate
                # the frozen viewer association. These nullable fields add no
                # ownership or task claims and preserve all recovery latches.
                from sqlalchemy import inspect as inspect_schema
                owner_columns = await conn.run_sync(lambda sync: {
                    column["name"] for column in inspect_schema(sync).get_columns("browser_profile_owners")})
                for name in ("workflow_id", "browser_node_id"):
                    if name not in owner_columns:
                        await conn.execute(text(f"ALTER TABLE browser_profile_owners ADD COLUMN {name} VARCHAR(255)"))
                from models.browser_owners import BrowserProfileOwner
                for index in BrowserProfileOwner.__table__.indexes:
                    await conn.run_sync(lambda sync, index=index: index.create(sync, checkfirst=True))
                if self.engine.dialect.name == "postgresql":
                    await conn.run_sync(_rebuild_chat_lane_index)

            # Existing SQLite upgrades retain their exact backfills. PostgreSQL
            # starts with the current SQLModel schema; legacy SQLite data is
            # moved explicitly by the offline database transfer command.
            if self.engine.dialect.name == "sqlite":
                await self._migrate_user_settings()
                await self._migrate_agent_teams()
                await self._migrate_workflow_controls()
                await self._migrate_generation_scoped_runtime_data()
                await self._migrate_chat_messages()
                await self._migrate_chat_runs()
                await self._migrate_approvals()
                await self._migrate_employees()
            else:
                await self._validate_shared_schema()

            logger.info("Database initialized successfully")

        except Exception as e:
            logger.error("Database startup failed", error=str(e))
            raise

    async def _validate_shared_schema(self):
        """Fail before serving an incompatible shared schema; never mask drift."""
        from sqlalchemy import inspect as inspect_schema

        async with self.engine.connect() as connection:
            def missing(sync_connection):
                inspector = inspect_schema(sync_connection)
                absent = []
                for table in SQLModel.metadata.sorted_tables:
                    names = {entry["name"] for entry in inspector.get_columns(table.name)}
                    absent.extend(f"{table.name}.{column.name}" for column in table.columns if column.name not in names)
                return absent
            absent = await connection.run_sync(missing)
        if absent:
            raise RuntimeError("Shared database schema requires migration: " + ", ".join(absent))

    async def allocate_identity(self, namespace: str) -> int:
        """Atomically allocate the next positive integer in ``namespace``."""
        if not namespace or len(namespace) > 512:
            raise ValueError("invalid_identity_namespace")
        # A compare-and-swap update is portable and prevents duplicate values
        # when multiple API/Temporal worker processes allocate concurrently.
        for _ in range(20):
            async with self.get_session() as session:
                row = await session.get(IdentitySequence, namespace)
                if row is None:
                    session.add(IdentitySequence(namespace=namespace, next_value=2))
                    try:
                        await session.commit()
                        return 1
                    except IntegrityError:
                        await session.rollback()
                        continue
                current = row.next_value
                result = await session.execute(
                    update(IdentitySequence)
                    .where(
                        IdentitySequence.namespace == namespace,
                        IdentitySequence.next_value == current,
                    )
                    .values(next_value=current + 1, updated_at=datetime.now(timezone.utc))
                )
                if result.rowcount == 1:
                    await session.commit()
                    return current
                await session.rollback()
        raise RuntimeError("identity_allocation_conflict")

    async def allocate_workflow_id(self) -> str:
        """Return the next canonical workflow ID: ``1``, ``2``, ..."""
        return str(await self.allocate_identity("workflow"))

    async def allocate_execution_id(self, workflow_id: str) -> str:
        """Return a workflow-scoped application execution identity.

        The canonical workflow alias is used in the visible ID. This method
        never manufactures or replaces a Temporal Run ID.
        """
        value = await self.allocate_identity(f"workflow-execution:{workflow_id}")
        return f"{workflow_id}:execution:{value}"

    async def _migrate_user_settings(self):
        """Add missing columns to user_settings table."""
        try:
            async with self.engine.begin() as conn:
                # Check if column exists and add if missing
                result = await conn.execute(text("PRAGMA table_info(user_settings)"))
                columns = {row[1] for row in result.fetchall()}

                if "console_panel_default_open" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN console_panel_default_open BOOLEAN DEFAULT 0"))
                    logger.info("Added console_panel_default_open column to user_settings")

                if "examples_loaded" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN examples_loaded BOOLEAN DEFAULT 0"))
                    logger.info("Added examples_loaded column to user_settings")

                if "onboarding_completed" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN onboarding_completed BOOLEAN DEFAULT 0"))
                    # Existing users (examples_loaded=1) skip onboarding
                    await conn.execute(text("UPDATE user_settings SET onboarding_completed = 1 WHERE examples_loaded = 1"))
                    logger.info("Added onboarding_completed column to user_settings")

                if "onboarding_step" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN onboarding_step INTEGER DEFAULT 0"))
                    logger.info("Added onboarding_step column to user_settings")

                if "memory_window_size" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN memory_window_size INTEGER DEFAULT 100"))
                    logger.info("Added memory_window_size column to user_settings")

                if "compaction_ratio" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN compaction_ratio REAL DEFAULT 0.8"))
                    logger.info("Added compaction_ratio column to user_settings")

                if "default_llm_provider" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN default_llm_provider VARCHAR(50)"))
                    logger.info("Added default_llm_provider column to user_settings")

                if "default_llm_model" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN default_llm_model VARCHAR(200)"))
                    logger.info("Added default_llm_model column to user_settings")

                if "auto_add_skill_for_tools" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN auto_add_skill_for_tools BOOLEAN DEFAULT 1"))
                    logger.info("Added auto_add_skill_for_tools column to user_settings")

                if "auto_rebind_tools_after_canvas_change" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN auto_rebind_tools_after_canvas_change BOOLEAN DEFAULT 1"))
                    logger.info("Added auto_rebind_tools_after_canvas_change column to user_settings")

                if "agent_recursion_limit" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN agent_recursion_limit INTEGER DEFAULT 200"))
                    logger.info("Added agent_recursion_limit column to user_settings")

                if "tool_result_max_chars" not in columns:
                    # The model field's default is the source of truth; existing
                    # rows need a real value because the client parser rejects null.
                    default_chars = int(UserSettings.model_fields["tool_result_max_chars"].default)
                    await conn.execute(text(f"ALTER TABLE user_settings ADD COLUMN tool_result_max_chars INTEGER DEFAULT {default_chars}"))
                    logger.info("Added tool_result_max_chars column to user_settings")

                if "max_concurrent_subagents" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN max_concurrent_subagents INTEGER DEFAULT 3"))
                if "max_delegation_depth" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN max_delegation_depth INTEGER DEFAULT 2"))

                if "getting_started_dismissed" not in columns:
                    await conn.execute(text("ALTER TABLE user_settings ADD COLUMN getting_started_dismissed BOOLEAN DEFAULT 0"))
                    # Existing users who already finished/skipped onboarding never see the checklist
                    await conn.execute(text("UPDATE user_settings SET getting_started_dismissed = 1 WHERE onboarding_completed = 1"))
                    logger.info("Added getting_started_dismissed column to user_settings")

                for col in [
                    "getting_started_added_key",
                    "getting_started_ran_example",
                    "getting_started_built_workflow",
                    "getting_started_tried_theme",
                    "getting_started_said_hello",
                    "getting_started_approved_draft",
                ]:
                    if col not in columns:
                        await conn.execute(text(f"ALTER TABLE user_settings ADD COLUMN {col} BOOLEAN DEFAULT 0"))
                        logger.info(f"Added {col} column to user_settings")

                # Owner profile (Normal mode). Text columns start empty; the
                # two switches take the model default for existing rows.
                for col, length in [
                    ("profile_full_name", 100),
                    ("profile_call_name", 60),
                    ("profile_role", 100),
                    ("profile_preferences", 2000),
                    ("profile_timezone", 64),
                ]:
                    if col not in columns:
                        await conn.execute(text(f"ALTER TABLE user_settings ADD COLUMN {col} VARCHAR({length})"))
                        logger.info(f"Added {col} column to user_settings")
                for col in ["memory_across_chats", "prefer_local_ai"]:
                    if col not in columns:
                        default_on = 1 if UserSettings.model_fields[col].default else 0
                        await conn.execute(text(f"ALTER TABLE user_settings ADD COLUMN {col} BOOLEAN DEFAULT {default_on}"))
                        logger.info(f"Added {col} column to user_settings")
                # The chat's model picker; existing rows take the model default.
                for col, length in [("chat_model", 300), ("chat_effort", 10)]:
                    if col not in columns:
                        default = str(UserSettings.model_fields[col].default)
                        await conn.execute(text(f"ALTER TABLE user_settings ADD COLUMN {col} VARCHAR({length}) NOT NULL DEFAULT '{default}'"))
                        logger.info(f"Added {col} column to user_settings")

                # Migrate token_usage_metrics table - add cost columns
                result = await conn.execute(text("PRAGMA table_info(token_usage_metrics)"))
                columns = {row[1] for row in result.fetchall()}

                for col in ["input_cost", "output_cost", "cache_cost", "total_cost"]:
                    if col not in columns:
                        await conn.execute(text(f"ALTER TABLE token_usage_metrics ADD COLUMN {col} REAL DEFAULT 0.0"))
                        logger.info(f"Added {col} column to token_usage_metrics")

                # Migrate session_token_states table - add cumulative cost columns
                result = await conn.execute(text("PRAGMA table_info(session_token_states)"))
                columns = {row[1] for row in result.fetchall()}

                for col in ["cumulative_input_cost", "cumulative_output_cost", "cumulative_total_cost"]:
                    if col not in columns:
                        await conn.execute(text(f"ALTER TABLE session_token_states ADD COLUMN {col} REAL DEFAULT 0.0"))
                        logger.info(f"Added {col} column to session_token_states")

                # Migrate provider_defaults table - add default_model column
                result = await conn.execute(text("PRAGMA table_info(provider_defaults)"))
                columns = {row[1] for row in result.fetchall()}
                if columns and "default_model" not in columns:
                    await conn.execute(text("ALTER TABLE provider_defaults ADD COLUMN default_model TEXT DEFAULT ''"))
                    logger.info("Added default_model column to provider_defaults")

                # Create api_usage_metrics table if not exists
                await conn.execute(
                    text("""
                    CREATE TABLE IF NOT EXISTS api_usage_metrics (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                        session_id TEXT NOT NULL,
                        node_id TEXT NOT NULL,
                        workflow_id TEXT,
                        service TEXT NOT NULL,
                        operation TEXT NOT NULL,
                        endpoint TEXT NOT NULL,
                        resource_count INTEGER DEFAULT 1,
                        cost REAL DEFAULT 0.0
                    )
                """)
                )
                await conn.execute(text("CREATE INDEX IF NOT EXISTS idx_api_usage_session ON api_usage_metrics(session_id)"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS idx_api_usage_service ON api_usage_metrics(service)"))
                logger.info("Ensured api_usage_metrics table exists")

                # Migrate gmail_connections to google_connections
                # Check if old table exists and new table doesn't
                result = await conn.execute(text("SELECT name FROM sqlite_master WHERE type='table' AND name='gmail_connections'"))
                old_table_exists = result.fetchone() is not None

                result = await conn.execute(text("SELECT name FROM sqlite_master WHERE type='table' AND name='google_connections'"))
                new_table_exists = result.fetchone() is not None

                if old_table_exists and not new_table_exists:
                    await conn.execute(text("ALTER TABLE gmail_connections RENAME TO google_connections"))
                    logger.info("Migrated gmail_connections table to google_connections")

        except Exception as e:
            logger.warning(f"Migration check failed (table may not exist yet): {e}")

    async def _migrate_agent_teams(self):
        """Add execution identity columns used by durable delegation."""
        try:
            async with self.engine.begin() as conn:
                result = await conn.execute(text("PRAGMA table_info(agent_teams)"))
                columns = {row[1] for row in result.fetchall()}
                if columns and "execution_id" not in columns:
                    await conn.execute(text("ALTER TABLE agent_teams ADD COLUMN execution_id VARCHAR(255)"))
                if columns and "root_execution_id" not in columns:
                    await conn.execute(text("ALTER TABLE agent_teams ADD COLUMN root_execution_id VARCHAR(255)"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_agent_teams_execution_id ON agent_teams(execution_id)"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_agent_teams_root_execution_id ON agent_teams(root_execution_id)"))
                await conn.execute(text("CREATE UNIQUE INDEX IF NOT EXISTS uq_agent_team_execution_lead ON agent_teams(execution_id, team_lead_node_id) WHERE execution_id IS NOT NULL"))

                result = await conn.execute(text("PRAGMA table_info(agent_messages)"))
                message_columns = {row[1] for row in result.fetchall()}
                if message_columns and "event_id" not in message_columns:
                    await conn.execute(text("ALTER TABLE agent_messages ADD COLUMN event_id VARCHAR(255)"))
                await conn.execute(text("CREATE UNIQUE INDEX IF NOT EXISTS ix_agent_messages_event_id ON agent_messages(event_id) WHERE event_id IS NOT NULL"))

                result = await conn.execute(text("PRAGMA table_info(team_tasks)"))
                task_columns = {row[1] for row in result.fetchall()}
                additions = {
                    "workflow_id": "VARCHAR(255)", "execution_id": "VARCHAR(255)",
                    "root_execution_id": "VARCHAR(255)", "parent_agent_id": "VARCHAR(255)",
                    "mission": "VARCHAR(10000)", "context": "JSON",
                    "acceptance_criteria": "JSON", "queue_sequence": "INTEGER DEFAULT 0",
                    "revision": "INTEGER DEFAULT 0", "current_attempt": "INTEGER DEFAULT 0",
                    "child_workflow_id": "VARCHAR(500)", "child_run_id": "VARCHAR(255)",
                    "runner_workflow_id": "VARCHAR(500)", "runner_run_id": "VARCHAR(255)",
                    "parent_workflow_id": "VARCHAR(500)", "parent_run_id": "VARCHAR(255)",
                    "trace_id": "VARCHAR(255)", "cancellation_requested": "BOOLEAN DEFAULT 0",
                    "cancellation_reason": "VARCHAR(2000)", "usage": "JSON",
                }
                for column, definition in additions.items():
                    if task_columns and column not in task_columns:
                        await conn.execute(text(f"ALTER TABLE team_tasks ADD COLUMN {column} {definition}"))
                if task_columns:
                    await conn.execute(text("UPDATE team_tasks SET status='queued' WHERE status='pending'"))
                    await conn.execute(text("UPDATE team_tasks SET status='running' WHERE status='in_progress'"))
                    await conn.execute(text("UPDATE team_tasks SET status='submitted' WHERE status='completed'"))
                    await conn.execute(text("UPDATE team_tasks SET status='cancelled' WHERE status='skipped'"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_team_tasks_execution_id ON team_tasks(execution_id)"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_team_tasks_root_execution_id ON team_tasks(root_execution_id)"))

                result = await conn.execute(text("PRAGMA table_info(team_task_attempts)"))
                attempt_columns = {row[1] for row in result.fetchall()}
                attempt_additions = {
                    "runner_workflow_id": "VARCHAR(500)", "runner_run_id": "VARCHAR(255)",
                    "parent_workflow_id": "VARCHAR(500)", "parent_run_id": "VARCHAR(255)",
                }
                for column, definition in attempt_additions.items():
                    if attempt_columns and column not in attempt_columns:
                        await conn.execute(text(f"ALTER TABLE team_task_attempts ADD COLUMN {column} {definition}"))
        except Exception as e:
            logger.warning(f"Agent-team migration check failed: {e}")

    async def shutdown(self):
        """Close database connections."""
        if self.engine:
            await self.engine.dispose()
            logger.info("Database connections closed")

    @asynccontextmanager
    async def get_session(self):
        """Get async database session.

        Teardown (rollback + close) runs under ``asyncio.shield``. When the
        calling task is cancelled mid-statement -- a WebSocket client that
        disconnects while its connect-time handlers are still reading --
        the cancellation would otherwise land inside the pool's
        reset-on-return rollback; SQLAlchemy then logs "Exception during
        reset or similar" with a full traceback and discards the
        connection. Shielding lets the reset finish while the
        CancelledError still propagates to the caller.
        """
        if not self.async_session:
            raise RuntimeError("Database not initialized")

        session = self.async_session()
        try:
            yield session
        except asyncio.CancelledError:
            await asyncio.shield(_teardown_session(session, rollback=True))
            raise
        except Exception:
            await asyncio.shield(_teardown_session(session, rollback=True))
            raise
        else:
            await asyncio.shield(_teardown_session(session, rollback=False))

    @asynccontextmanager
    async def reserved_session(self):
        """A session whose write transaction is reserved before its first read.

        SQLite's deferred transactions let two writers read the same rows
        before either writes; ``BEGIN IMMEDIATE`` takes the write reservation
        first, so a whole read/modify/write runs alone (as in
        :meth:`run_runtime_mutation`). The caller commits; leaving the block
        without committing rolls back.
        """
        async with self.get_session() as session:
            if self.engine is not None and self.engine.dialect.name == "sqlite":
                await session.execute(text("BEGIN IMMEDIATE"))
            elif self.engine is not None and self.engine.dialect.name == "postgresql":
                # Match SQLite's existing write reservation for callers that
                # perform read/modify/write, including first-row creation.
                await session.execute(text("SELECT pg_advisory_xact_lock(763746821)"))
            yield session

    async def _migrate_workflow_controls(self):
        """Backfill control-plane columns when upgrading an early preview DB."""
        try:
            async with self.engine.begin() as conn:
                result = await conn.execute(text("PRAGMA table_info(workflow_control_executions)"))
                columns = {row[1] for row in result.fetchall()}
                additions = {
                    "controller_workflow_id": "VARCHAR(500)",
                    "controller_run_id": "VARCHAR(255)",
                    "data_scope_id": "VARCHAR(255)",
                    "resource_manifest": "JSON DEFAULT '{}'",
                    "terminal_reason": "VARCHAR(2000)",
                    "completed_at": "DATETIME",
                    "pause_reason": "VARCHAR(50)",
                    "pause_detail": "VARCHAR(500)",
                }
                for column, definition in additions.items():
                    if column not in columns:
                        await conn.execute(text(f"ALTER TABLE workflow_control_executions ADD COLUMN {column} {definition}"))
                await conn.execute(text("CREATE UNIQUE INDEX IF NOT EXISTS uq_workflow_control_generation ON workflow_control_executions(workflow_id, generation)"))
                await conn.execute(text("CREATE UNIQUE INDEX IF NOT EXISTS uq_workflow_control_idempotency ON workflow_control_executions(workflow_id, idempotency_key)"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_workflow_control_data_scope_id ON workflow_control_executions(data_scope_id)"))
                # Preview releases already have durable generations but no
                # explicit runtime-data namespace. Their execution identity is
                # the only safe deterministic scope key.
                await conn.execute(text(
                    "UPDATE workflow_control_executions "
                    "SET data_scope_id = execution_id WHERE data_scope_id IS NULL"
                ))
                await conn.execute(text("""
                    INSERT OR IGNORE INTO workflow_run_data_scopes (
                        id, control_id, workflow_id, generation, execution_id,
                        root_execution_id, temporal_workflow_id, temporal_run_id,
                        source_session_id, graph_hash, node_data, runtime_data,
                        status, created_at, archived_at
                    )
                    SELECT
                        execution_id, id, workflow_id, generation, execution_id,
                        root_execution_id, controller_workflow_id, controller_run_id,
                        session_id, graph_hash, '{}', '{}',
                        CASE WHEN status IN ('reset', 'failed') THEN 'archived' ELSE 'active' END,
                        created_at,
                        CASE WHEN status IN ('reset', 'failed') THEN completed_at ELSE NULL END
                    FROM workflow_control_executions
                    WHERE execution_id IS NOT NULL
                """))
        except Exception as exc:
            logger.warning(f"Workflow-control migration check failed: {exc}")

    async def _migrate_generation_scoped_runtime_data(self):
        """Add nullable generation keys while preserving legacy history."""
        try:
            async with self.engine.begin() as conn:
                for table_name in ("chat_messages", "console_logs"):
                    result = await conn.execute(text(f"PRAGMA table_info({table_name})"))
                    columns = {row[1] for row in result.fetchall()}
                    if columns and "execution_id" not in columns:
                        await conn.execute(text(
                            f"ALTER TABLE {table_name} ADD COLUMN execution_id VARCHAR(255)"
                        ))
                    await conn.execute(text(
                        f"CREATE INDEX IF NOT EXISTS ix_{table_name}_execution_id "
                        f"ON {table_name}(execution_id)"
                    ))
        except Exception as exc:
            logger.warning(f"Generation runtime-data migration check failed: {exc}")

    async def _migrate_chat_runs(self):
        """Give older ``chat_runs`` and ``chat_threads`` tables the columns
        added since."""
        additions = {
            "chat_runs": {"stop_requested_at": "DATETIME", "context_cursors": "JSON DEFAULT '{}'"},
            "chat_threads": {"next_source": "INTEGER NOT NULL DEFAULT 1"},
        }
        try:
            async with self.engine.begin() as conn:
                for table, columns_added in additions.items():
                    result = await conn.execute(text(f"PRAGMA table_info({table})"))
                    columns = {row[1] for row in result.fetchall()}
                    if not columns:
                        continue
                    for column, definition in columns_added.items():
                        if column not in columns:
                            await conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {column} {definition}"))
        except Exception as exc:
            logger.warning(f"Chat run migration check failed: {exc}")

    async def _migrate_employees(self):
        """Give an older ``employees`` table the columns added since."""
        columns_added = {"photo_path": "VARCHAR(500)", "team_plan": "JSON NOT NULL DEFAULT '{}'", "claim_token": "VARCHAR(64)", "lease_until": "DATETIME"}
        try:
            async with self.engine.begin() as conn:
                result = await conn.execute(text("PRAGMA table_info(employees)"))
                columns = {row[1] for row in result.fetchall()}
                if not columns:
                    return
                for column, definition in columns_added.items():
                    if column not in columns:
                        await conn.execute(text(f"ALTER TABLE employees ADD COLUMN {column} {definition}"))
                apply_columns = {row[1] for row in (await conn.execute(text("PRAGMA table_info(employee_apply_requests)"))).fetchall()}
                if apply_columns and "producer_states" not in apply_columns:
                    await conn.execute(text("ALTER TABLE employee_apply_requests ADD COLUMN producer_states JSON NOT NULL DEFAULT '{}'"))
        except Exception as exc:
            logger.warning(f"Employee migration check failed: {exc}")

    async def _migrate_approvals(self):
        """Give an older ``approval_requests`` table the columns approvals v2
        added (held tool calls, Undo and Restore, outcomes; RFC-0007's decline
        ``note``), and an older ``workflow_rules`` table its ``mode``. A draft approved
        before them was handed on when it was decided, so it gets that as
        ``consumed_at`` and never reads as still undoable."""
        columns_added = {
            "kind": "VARCHAR(20) NOT NULL DEFAULT 'gate'",
            "action": "VARCHAR(200)",
            "details": "JSON",
            "node_type": "VARCHAR(100)",
            "tool_node_id": "VARCHAR(255)",
            "tool_call_id": "VARCHAR(255)",
            "agent_node_id": "VARCHAR(255)",
            "run_id": "VARCHAR(64)",
            "ui_part_id": "VARCHAR(64)",
            "node_data": "JSON",
            "args": "JSON",
            "original_args": "JSON",
            "body_field": "VARCHAR(100)",
            "subject_field": "VARCHAR(100)",
            "approved_by": "VARCHAR(20)",
            "grace_until": "DATETIME",
            "restore_until": "DATETIME",
            "consumed_at": "DATETIME",
            "claim_token": "VARCHAR(64)",
            "outcome": "VARCHAR(20)",
            "outcome_error": "VARCHAR(1000)",
            "outcome_at": "DATETIME",
            "note": "VARCHAR(50000)",
        }
        try:
            async with self.engine.begin() as conn:
                result = await conn.execute(text("PRAGMA table_info(workflow_rules)"))
                rule_columns = {row[1] for row in result.fetchall()}
                if rule_columns and "mode" not in rule_columns:
                    await conn.execute(text("ALTER TABLE workflow_rules ADD COLUMN mode VARCHAR(20)"))
                result = await conn.execute(text("PRAGMA table_info(approval_requests)"))
                columns = {row[1] for row in result.fetchall()}
                if not columns:
                    return
                for column, definition in columns_added.items():
                    if column not in columns:
                        await conn.execute(text(f"ALTER TABLE approval_requests ADD COLUMN {column} {definition}"))
                if "consumed_at" not in columns:
                    await conn.execute(text(
                        "UPDATE approval_requests SET consumed_at = COALESCE(decided_at, updated_at) WHERE status = 'approved'"
                    ))
                if "run_id" not in columns:
                    await conn.execute(text(
                        "CREATE INDEX IF NOT EXISTS ix_approval_requests_run_id ON approval_requests(run_id)"
                    ))
        except Exception as exc:
            logger.warning(f"Approval migration check failed: {exc}")

    async def _migrate_chat_messages(self):
        """Give older chat rows the columns chat runs need.

        Rows written before this migration get a ``uid`` (``m<id>``) and a
        ``parent_uid`` chaining each session's rows in order, the shape every
        new row is written in. The backfill runs once: only in the startup
        that adds the ``uid`` column, so a session's first message or a
        report (both legitimately parentless) never gains a parent later.
        """
        try:
            async with self.engine.begin() as conn:
                result = await conn.execute(text("PRAGMA table_info(chat_messages)"))
                columns = {row[1] for row in result.fetchall()}
                if not columns:
                    return
                additions = {
                    "uid": "VARCHAR(64)",
                    "parent_uid": "VARCHAR(64)",
                    "run_id": "VARCHAR(64)",
                    "kind": "VARCHAR(20) DEFAULT 'text'",
                    "status": "VARCHAR(20) DEFAULT 'complete'",
                    "parts": "JSON DEFAULT '{}'",
                    "attachments": "JSON DEFAULT '[]'",
                    "meta": "JSON DEFAULT '{}'",
                }
                backfill = "uid" not in columns
                for column, definition in additions.items():
                    if column not in columns:
                        await conn.execute(text(f"ALTER TABLE chat_messages ADD COLUMN {column} {definition}"))
                if backfill:
                    await conn.execute(text("UPDATE chat_messages SET uid = 'm' || id WHERE uid IS NULL"))
                    await conn.execute(text(
                        "UPDATE chat_messages SET parent_uid = ("
                        " SELECT prev.uid FROM chat_messages AS prev"
                        " WHERE prev.session_id = chat_messages.session_id"
                        " AND (prev.created_at < chat_messages.created_at"
                        "  OR (prev.created_at = chat_messages.created_at AND prev.id < chat_messages.id))"
                        " ORDER BY prev.created_at DESC, prev.id DESC LIMIT 1)"
                    ))
                await conn.execute(text("CREATE UNIQUE INDEX IF NOT EXISTS ux_chat_messages_uid ON chat_messages(uid)"))
                await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_chat_messages_run_id ON chat_messages(run_id)"))
        except Exception as exc:
            logger.warning(f"Chat message migration check failed: {exc}")

    async def run_runtime_mutation(
        self,
        *,
        resource_type: str,
        resource_id: str,
        operation: str,
        mutate: RuntimeMutationCallback,
        mutation_id: Optional[str] = None,
    ) -> Tuple[Dict[str, Any], bool]:
        """Run one shared-state mutation under a reserved write transaction.

        SQLite's normal deferred transactions allow two workers to read the
        same JSON value before either writes it.  ``BEGIN IMMEDIATE`` obtains
        the write reservation *before* the read, serialising the complete
        read/modify/write cycle.  When ``mutation_id`` is supplied, the
        durable ledger row is committed in that same transaction and a retry
        returns the original result with ``applied=False``.

        The callback receives the transaction's :class:`AsyncSession` and
        must not commit it.  This method also works with non-SQLite test or
        future production databases, using their regular transaction begin.
        """
        if not self.async_session:
            raise RuntimeError("Database not initialized")

        async with self.async_session() as session:
            try:
                if self.engine is not None and self.engine.dialect.name == "sqlite":
                    await session.execute(text("BEGIN IMMEDIATE"))
                else:
                    await session.begin()
                    if self.engine is not None and self.engine.dialect.name == "postgresql":
                        await session.execute(text("SELECT pg_advisory_xact_lock(763746821)"))

                if mutation_id:
                    found = await session.execute(
                        select(RuntimeMutation).where(
                            RuntimeMutation.mutation_id == mutation_id,
                            RuntimeMutation.resource_type == resource_type,
                            RuntimeMutation.resource_id == resource_id,
                        )
                    )
                    existing = found.scalar_one_or_none()
                    if existing is not None:
                        result = deepcopy(existing.result or {})
                        await session.rollback()
                        return result, False

                result_or_awaitable = mutate(session)
                if inspect.isawaitable(result_or_awaitable):
                    result = await result_or_awaitable
                else:
                    result = result_or_awaitable
                result = deepcopy(result or {})

                if mutation_id:
                    session.add(
                        RuntimeMutation(
                            mutation_id=mutation_id,
                            resource_type=resource_type,
                            resource_id=resource_id,
                            operation=operation,
                            result=result,
                        )
                    )

                await session.commit()
                return result, True
            except Exception:
                await session.rollback()
                raise

    async def mutate_node_parameters_atomic(
        self,
        node_id: str,
        mutator: ParameterMutator,
        *,
        mutation_id: Optional[str] = None,
        operation: str = "update",
    ) -> Tuple[Dict[str, Any], Dict[str, Any], bool]:
        """Atomically transform one node's parameters.

        ``mutator`` receives an isolated copy and may return either the new
        parameter dict or ``(new_parameters, result_metadata)``.  The final
        persisted parameters, metadata, and whether this call applied the
        write are returned.
        """

        async def _mutate(session: AsyncSession) -> Dict[str, Any]:
            selected = await session.execute(
                select(NodeParameter).where(NodeParameter.node_id == node_id)
            )
            row = selected.scalar_one_or_none()
            current = deepcopy(row.parameters if row is not None else {})
            original = deepcopy(current)
            transformed = mutator(current)
            if isinstance(transformed, tuple):
                new_parameters, metadata = transformed
            else:
                new_parameters, metadata = transformed, {}
            if not isinstance(new_parameters, dict):
                raise TypeError("node parameter mutator must return a dict")
            from services.agent_runtime_policy import preserve_runtime_version
            new_parameters = preserve_runtime_version(original, new_parameters)
            if row is None:
                session.add(
                    NodeParameter(
                        node_id=node_id,
                        parameters=deepcopy(new_parameters),
                    )
                )
            else:
                row.parameters = deepcopy(new_parameters)
                row.updated_at = datetime.now(timezone.utc)
            return deepcopy(metadata or {})

        metadata, applied = await self.run_runtime_mutation(
            resource_type="node_parameters",
            resource_id=node_id,
            operation=operation,
            mutation_id=mutation_id,
            mutate=_mutate,
        )
        parameters = await self.get_node_parameters(node_id) or {}
        return parameters, metadata, applied

    async def mutate_workflow_data_atomic(
        self,
        workflow_id: str,
        mutator: ParameterMutator,
        *,
        mutation_id: Optional[str] = None,
        operation: str = "update",
    ) -> Tuple[Optional[Workflow], Dict[str, Any], bool]:
        """Atomically transform ``Workflow.data`` without stale overwrites."""

        async def _mutate(session: AsyncSession) -> Dict[str, Any]:
            selected = await session.execute(
                select(Workflow).where(Workflow.id == workflow_id)
            )
            workflow = selected.scalar_one_or_none()
            if workflow is None:
                return {"found": False}
            previous_context_ids = _workflow_context_node_ids(workflow.data)
            transformed = mutator(deepcopy(workflow.data or {}))
            if isinstance(transformed, tuple):
                new_data, metadata = transformed
            else:
                new_data, metadata = transformed, {}
            if not isinstance(new_data, dict):
                raise TypeError("workflow data mutator must return a dict")
            for context_node_id in sorted(
                previous_context_ids - _workflow_context_node_ids(new_data)
            ):
                session.add(
                    WorkflowContextArchiveOutbox(
                        id=f"ctxarc-{secrets.token_hex(16)}",
                        workflow_id=workflow_id,
                        context_node_id=context_node_id,
                    )
                )
            workflow.data = deepcopy(new_data)
            workflow.updated_at = datetime.now(timezone.utc)
            return {"found": True, **deepcopy(metadata or {})}

        metadata, applied = await self.run_runtime_mutation(
            resource_type="workflow",
            resource_id=workflow_id,
            operation=operation,
            mutation_id=mutation_id,
            mutate=_mutate,
        )
        workflow = await self.get_workflow(workflow_id)
        return workflow, metadata, applied

    # ============================================================================
    # Node Parameters
    # ============================================================================

    async def save_node_parameters(self, node_id: str, parameters: Dict[str, Any]) -> bool:
        """Save or update node parameters."""
        try:
            async with self.get_session() as session:
                # Try to get existing parameter
                stmt = select(NodeParameter).where(NodeParameter.node_id == node_id)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                if existing:
                    from services.agent_runtime_policy import preserve_runtime_version
                    existing.parameters = preserve_runtime_version(existing.parameters or {}, parameters)
                else:
                    existing = NodeParameter(node_id=node_id, parameters=deepcopy(parameters))
                    session.add(existing)

                await session.commit()
                return True

        except Exception as e:
            logger.error("Failed to save node parameters", node_id=node_id, error=str(e))
            return False

    async def get_node_parameters(self, node_id: str) -> Optional[Dict[str, Any]]:
        """Get node parameters."""
        try:
            async with self.get_session() as session:
                stmt = select(NodeParameter).where(NodeParameter.node_id == node_id)
                result = await session.execute(stmt)
                parameter = result.scalar_one_or_none()

                return parameter.parameters if parameter else None

        except Exception as e:
            logger.error("Failed to get node parameters", node_id=node_id, error=str(e))
            return None

    async def delete_node_parameters(self, node_id: str) -> bool:
        """Delete node parameters."""
        try:
            async with self.get_session() as session:
                stmt = select(NodeParameter).where(NodeParameter.node_id == node_id)
                result = await session.execute(stmt)
                parameter = result.scalar_one_or_none()

                if parameter:
                    await session.delete(parameter)
                    await session.commit()

                return True

        except Exception as e:
            logger.error("Failed to delete node parameters", node_id=node_id, error=str(e))
            return False

    # ============================================================================
    # Workflows
    # ============================================================================

    async def save_workflow(
        self,
        workflow_id: str,
        name: str,
        slug: str,
        data: Dict[str, Any],
        description: Optional[str] = None,
        context_id_aliases: Optional[Dict[str, str]] = None,
        *,
        require_existing: bool = False,
    ) -> bool:
        """Save or update workflow.

        ``workflow_id`` accepts either the canonical decimal identity or a
        retained legacy alias. ``slug`` is required for new rows and is updated on every save
        for existing rows (so a rename routed through this path stays
        consistent). Callers compute the slug via
        :func:`services.workflow_naming.next_available_slug` before
        calling this method — the unique constraint on the column is
        the final check against collision.

        ``require_existing`` makes this an update only: an editor save or
        read-time migration must not recreate a workflow deleted since its
        caller read the graph. The UPDATE checks the affected row count in
        the same transaction as the Context archive intents.
        """
        try:
            async with self.get_session() as session:
                stmt = select(Workflow).where(Workflow.id == workflow_id)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()
                previous_context_ids = _workflow_context_node_ids(existing.data if existing is not None else {})
                next_context_ids = _workflow_context_node_ids(data)

                if existing is not None:
                    updated = await session.execute(
                        update(Workflow)
                        .where(Workflow.id == workflow_id)
                        .values(name=name, slug=slug, description=description, data=data)
                    )
                    if updated.rowcount != 1:
                        return False
                elif require_existing:
                    return False
                else:
                    existing = Workflow(id=workflow_id, name=name, slug=slug, description=description, data=data)
                    session.add(existing)

                aliases = context_id_aliases or {}
                canonicalized_context_ids = {
                    old_id
                    for old_id in previous_context_ids
                    if aliases.get(old_id) in next_context_ids
                }
                for context_node_id in sorted(
                    previous_context_ids
                    - next_context_ids
                    - canonicalized_context_ids
                ):
                    session.add(
                        WorkflowContextArchiveOutbox(
                            id=f"ctxarc-{secrets.token_hex(16)}",
                            workflow_id=workflow_id,
                            context_node_id=context_node_id,
                        )
                    )
                await session.commit()
                return True

        except Exception as e:
            logger.error("Failed to save workflow", workflow_id=workflow_id, error=str(e))
            return False

    async def get_workflow(self, workflow_id: str) -> Optional[Workflow]:
        """Get workflow by ID."""
        try:
            async with self.get_session() as session:
                stmt = select(Workflow).where(Workflow.id == workflow_id)
                result = await session.execute(stmt)
                return result.scalar_one_or_none()

        except Exception as e:
            logger.error("Failed to get workflow", workflow_id=workflow_id, error=str(e))
            return None

    async def get_all_workflows(self) -> List[Workflow]:
        """Get all workflows."""
        try:
            async with self.get_session() as session:
                stmt = select(Workflow).order_by(Workflow.updated_at.desc())
                result = await session.execute(stmt)
                return result.scalars().all()

        except Exception as e:
            logger.error("Failed to get all workflows", error=str(e))
            return []

    async def list_workflow_slugs(self) -> List[tuple]:
        """``(id, slug)`` pairs of the slugs in use: each saved workflow's,
        and the one each live generation of a saved workflow names its
        Temporal ids by (``graph_snapshot.workflow_slug``). A rename leaves
        the latter as it was, and handed to another workflow it would give
        both the same cron Schedule id.

        Consumed by :func:`services.workflow_naming.next_available_slug`
        to find the lowest free ``_<N>`` suffix for a given slug base.
        Returns an empty list on error so the slug allocator falls
        through to ``_1``.
        """
        from models.database import WORKFLOW_CONTROL_ACTIVE_STATES

        try:
            async with self.get_session() as session:
                stmt = select(Workflow.id, Workflow.slug)
                result = await session.execute(stmt)
                rows = list(result.all())
                live = await session.execute(
                    select(WorkflowControlExecution.workflow_id, WorkflowControlExecution.graph_snapshot).where(
                        WorkflowControlExecution.status.in_(WORKFLOW_CONTROL_ACTIVE_STATES),
                        WorkflowControlExecution.workflow_id.in_(select(Workflow.id)),
                    )
                )
                for workflow_id, snapshot in live.all():
                    slug = snapshot.get("workflow_slug") if isinstance(snapshot, dict) else None
                    if isinstance(slug, str) and slug:
                        rows.append((workflow_id, slug))
                return rows
        except Exception as e:
            logger.error("Failed to list workflow slugs", error=str(e))
            return []

    async def rename_workflow(self, workflow_id: str, new_name: str, new_slug: str) -> bool:
        """Atomically update display name + slug. The storage PK never moves.

        Cross-table FK references and retained Temporal history may still key
        on a legacy storage identity, so renaming is a single-row UPDATE — no cascade
        needed. The unique constraint on ``slug`` is the collision
        guard; the caller must pre-allocate a free slug via
        :func:`services.workflow_naming.next_available_slug`.
        """
        try:
            async with self.get_session() as session:
                stmt = select(Workflow).where(Workflow.id == workflow_id)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()
                if not existing:
                    return False
                existing.name = new_name
                existing.slug = new_slug
                await session.commit()
                return True
        except Exception as e:
            logger.error("Failed to rename workflow", workflow_id=workflow_id, error=str(e))
            return False

    async def delete_workflow(self, workflow_id: str) -> bool:
        """Delete a workflow and atomically enqueue its Context archives."""
        try:
            async with self.get_session() as session:
                stmt = select(Workflow).where(Workflow.id == workflow_id)
                result = await session.execute(stmt)
                workflow = result.scalar_one_or_none()

                if workflow:
                    for context_node_id in sorted(_workflow_context_node_ids(workflow.data)):
                        session.add(
                            WorkflowContextArchiveOutbox(
                                id=f"ctxarc-{secrets.token_hex(16)}",
                                workflow_id=workflow_id,
                                context_node_id=context_node_id,
                            )
                        )
                    await session.delete(workflow)
                    await session.commit()

                return True

        except Exception as e:
            logger.error("Failed to delete workflow", workflow_id=workflow_id, error=str(e))
            return False

    async def list_workflow_context_archive_outbox(
        self,
        workflow_id: str,
    ) -> List[Dict[str, str]]:
        """List pending archive identities without loading Context payloads."""

        try:
            async with self.get_session() as session:
                result = await session.execute(
                    select(WorkflowContextArchiveOutbox)
                    .where(
                        WorkflowContextArchiveOutbox.workflow_id == workflow_id,
                        WorkflowContextArchiveOutbox.status == "pending",
                    )
                    .order_by(
                        WorkflowContextArchiveOutbox.created_at,
                        WorkflowContextArchiveOutbox.id,
                    )
                )
                return [
                    {
                        "id": row.id,
                        "workflow_id": row.workflow_id,
                        "context_node_id": row.context_node_id,
                    }
                    for row in result.scalars().all()
                ]
        except Exception as e:
            logger.error(
                "Failed to list workflow Context archive outbox",
                workflow_id=workflow_id,
                error=str(e),
            )
            return []

    async def complete_workflow_context_archive_outbox(
        self,
        *,
        outbox_id: str,
        workflow_id: str,
    ) -> bool:
        """Idempotently mark one successfully archived Context intent."""

        try:
            async with self.get_session() as session:
                row = await session.get(
                    WorkflowContextArchiveOutbox,
                    outbox_id,
                )
                if row is None or row.workflow_id != workflow_id:
                    return False
                if row.status == "completed":
                    return True
                row.status = "completed"
                row.completed_at = datetime.now(timezone.utc)
                await session.commit()
                return True
        except Exception as e:
            logger.error(
                "Failed to complete workflow Context archive outbox",
                workflow_id=workflow_id,
                outbox_id=outbox_id,
                error=str(e),
            )
            return False

    # ============================================================================
    # Executions
    # ============================================================================

    async def save_execution(
        self,
        execution_id: str,
        workflow_id: str,
        node_id: str,
        status: str,
        result: Optional[Dict[str, Any]] = None,
        error: Optional[str] = None,
        execution_time: Optional[float] = None,
    ) -> bool:
        """Save execution result."""
        try:
            async with self.get_session() as session:
                execution = Execution(
                    id=execution_id,
                    workflow_id=workflow_id,
                    node_id=node_id,
                    status=status,
                    result=result,
                    error=error,
                    execution_time=execution_time,
                )
                session.add(execution)
                await session.commit()
                return True

        except Exception as e:
            logger.error("Failed to save execution", execution_id=execution_id, error=str(e))
            return False

    async def get_execution(self, execution_id: str) -> Optional[Execution]:
        """Get execution by ID."""
        try:
            async with self.get_session() as session:
                stmt = select(Execution).where(Execution.id == execution_id)
                result = await session.execute(stmt)
                return result.scalar_one_or_none()

        except Exception as e:
            logger.error("Failed to get execution", execution_id=execution_id, error=str(e))
            return None

    # ============================================================================
    # API Keys
    # ============================================================================

    async def save_api_key(
        self, key_id: str, provider: str, session_id: str, key_encrypted: str, key_hash: str, models: Optional[List[str]] = None
    ) -> bool:
        """Save encrypted API key."""
        logger.info(f"Database save_api_key called with key_id: {key_id}, provider: {provider}")

        try:
            async with self.get_session() as session:
                api_key = APIKey(
                    id=key_id,
                    provider=provider,
                    session_id=session_id,
                    key_encrypted=key_encrypted,
                    key_hash=key_hash,
                    models={"models": models} if models else None,
                    last_validated=datetime.now(timezone.utc),
                )
                session.add(api_key)
                await session.commit()
                logger.info(f"Successfully saved new API key: {key_id}")
                return True

        except IntegrityError as e:
            logger.info(f"API key {key_id} already exists, attempting update. Error: {str(e)}")
            # Key already exists, update it
            try:
                async with self.get_session() as session:
                    stmt = select(APIKey).where(APIKey.id == key_id)
                    result = await session.execute(stmt)
                    existing = result.scalar_one_or_none()

                    if existing:
                        logger.info(f"Found existing API key {key_id}, updating...")
                        existing.key_encrypted = key_encrypted
                        existing.key_hash = key_hash
                        existing.models = {"models": models} if models else None
                        existing.last_validated = datetime.now(timezone.utc)
                        await session.commit()
                        logger.info(f"Successfully updated API key: {key_id}")
                        return True
                    else:
                        logger.error(f"Could not find existing API key {key_id} for update")
                        return False
            except Exception as update_e:
                logger.error(f"Failed to update API key {key_id}", error=str(update_e))
                return False

        except Exception as e:
            logger.error("Failed to save API key", provider=provider, error=str(e))
            import traceback

            logger.error("Full traceback", traceback=traceback.format_exc())
            return False

    async def get_api_key(self, key_id: str) -> Optional[APIKey]:
        """Get API key by ID."""
        try:
            async with self.get_session() as session:
                stmt = select(APIKey).where(APIKey.id == key_id)
                result = await session.execute(stmt)
                return result.scalar_one_or_none()

        except Exception as e:
            logger.error("Failed to get API key", key_id=key_id, error=str(e))
            return None

    async def get_api_key_by_provider(self, provider: str, session_id: str = "default") -> Optional[APIKey]:
        """Get API key by provider and session."""
        try:
            async with self.get_session() as session:
                stmt = select(APIKey).where(APIKey.provider == provider, APIKey.session_id == session_id, APIKey.is_valid)
                result = await session.execute(stmt)
                return result.scalar_one_or_none()

        except Exception as e:
            logger.error("Failed to get API key by provider", provider=provider, error=str(e))
            return None

    async def delete_api_key(self, provider: str, session_id: str = "default") -> bool:
        """Delete API key."""
        try:
            async with self.get_session() as session:
                stmt = select(APIKey).where(APIKey.provider == provider, APIKey.session_id == session_id)
                result = await session.execute(stmt)
                api_key = result.scalar_one_or_none()

                if api_key:
                    await session.delete(api_key)
                    await session.commit()
                    logger.debug("API key deleted", provider=provider, session_id=session_id)

                return True

        except Exception as e:
            logger.error("Failed to delete API key", provider=provider, error=str(e))
            return False

    # ============================================================================
    # API Key Validation Cache
    # ============================================================================

    async def save_api_key_validation(self, key_hash: str) -> bool:
        """Save API key validation status."""
        try:
            async with self.get_session() as session:
                validation = APIKeyValidation(key_hash=key_hash, validated=True)
                session.add(validation)
                await session.commit()
                return True

        except IntegrityError:
            # Already exists, update timestamp
            async with self.get_session() as session:
                stmt = select(APIKeyValidation).where(APIKeyValidation.key_hash == key_hash)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                if existing:
                    existing.timestamp = datetime.now(timezone.utc)
                    await session.commit()
                    return True
                return False

        except Exception as e:
            logger.error("Failed to save API key validation", key_hash=key_hash, error=str(e))
            return False

    async def is_api_key_validated(self, key_hash: str) -> bool:
        """Check if API key is validated."""
        try:
            async with self.get_session() as session:
                stmt = select(APIKeyValidation).where(APIKeyValidation.key_hash == key_hash)
                result = await session.execute(stmt)
                validation = result.scalar_one_or_none()
                return validation is not None and validation.validated

        except Exception as e:
            logger.error("Failed to check API key validation", key_hash=key_hash, error=str(e))
            return False

    # ============================================================================
    # Node Outputs
    # ============================================================================

    async def save_node_output(self, node_id: str, session_id: str, output_name: str, data: Dict[str, Any]) -> bool:
        """Save or update node output."""
        try:
            async with self.get_session() as session:
                # Try to get existing output
                stmt = select(NodeOutput).where(
                    NodeOutput.node_id == node_id, NodeOutput.session_id == session_id, NodeOutput.output_name == output_name
                )
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                action = "updated"
                if existing:
                    existing.data = data
                else:
                    action = "inserted"
                    existing = NodeOutput(node_id=node_id, session_id=session_id, output_name=output_name, data=data)
                    session.add(existing)

                await session.commit()
                logger.debug("[DB] Node output saved", action=action, node_id=node_id, session_id=session_id, output_name=output_name)
                return True

        except Exception as e:
            logger.error("Failed to save node output", node_id=node_id, error=str(e))
            import traceback

            traceback.print_exc()
            return False

    async def get_node_output(self, node_id: str, session_id: str = "default", output_name: str = "output_0") -> Optional[Dict[str, Any]]:
        """Get node output data."""
        try:
            async with self.get_session() as session:
                stmt = select(NodeOutput).where(
                    NodeOutput.node_id == node_id, NodeOutput.session_id == session_id, NodeOutput.output_name == output_name
                )
                result = await session.execute(stmt)
                output = result.scalar_one_or_none()

                return output.data if output else None

        except Exception as e:
            logger.error("Failed to get node output", node_id=node_id, error=str(e))
            return None

    async def get_node_output_by_session(self, session_id: str, output_name: str = "output_0") -> Optional[Dict[str, Any]]:
        """Get node output by session_id only (for delegation result lookup).

        Used when node_id is unknown but session_id encodes the lookup key.
        """
        try:
            async with self.get_session() as session:
                stmt = select(NodeOutput).where(NodeOutput.session_id == session_id, NodeOutput.output_name == output_name)
                result = await session.execute(stmt)
                output = result.scalar_one_or_none()

                return {"data": output.data} if output else None

        except Exception as e:
            logger.error("Failed to get node output by session", session_id=session_id, error=str(e))
            return None

    async def delete_node_output(self, node_id: str) -> int:
        """Delete all outputs for a node (any session). Returns count deleted."""
        try:
            async with self.get_session() as session:
                stmt = select(NodeOutput).where(NodeOutput.node_id == node_id)
                result = await session.execute(stmt)
                outputs = result.scalars().all()

                count = len(outputs)
                for output in outputs:
                    await session.delete(output)

                await session.commit()
                logger.info("Deleted node outputs", node_id=node_id, count=count)
                return count

        except Exception as e:
            logger.error("Failed to delete node output", node_id=node_id, error=str(e))
            return 0

    async def clear_session_outputs(self, session_id: str = "default") -> int:
        """Clear all outputs for a session. Returns count deleted."""
        try:
            async with self.get_session() as session:
                stmt = select(NodeOutput).where(NodeOutput.session_id == session_id)
                result = await session.execute(stmt)
                outputs = result.scalars().all()

                count = len(outputs)
                for output in outputs:
                    await session.delete(output)

                await session.commit()
                logger.info("Cleared session outputs", session_id=session_id, count=count)
                return count

        except Exception as e:
            logger.error("Failed to clear session outputs", session_id=session_id, error=str(e))
            return 0

    # ============================================================================
    # Conversation Messages (AI Memory)
    # ============================================================================

    async def add_conversation_message(self, session_id: str, role: str, content: str) -> bool:
        """Add a message to conversation history."""
        try:
            async with self.get_session() as session:
                message = ConversationMessage(session_id=session_id, role=role, content=content)
                session.add(message)
                await session.commit()
                logger.info(f"[Memory] Added {role} message to session '{session_id}'")
                return True

        except Exception as e:
            logger.error("Failed to add conversation message", session_id=session_id, error=str(e))
            return False

    async def get_conversation_messages(self, session_id: str, window_size: Optional[int] = None) -> List[Dict[str, Any]]:
        """Get conversation messages, optionally limited to last N."""
        try:
            async with self.get_session() as session:
                stmt = (
                    select(ConversationMessage)
                    .where(ConversationMessage.session_id == session_id)
                    .order_by(ConversationMessage.created_at.asc())
                )

                result = await session.execute(stmt)
                messages = result.scalars().all()

                # Apply window limit if specified
                if window_size and window_size > 0:
                    messages = messages[-window_size:]

                return [{"role": m.role, "content": m.content, "timestamp": m.created_at.isoformat()} for m in messages]

        except Exception as e:
            logger.error("Failed to get conversation messages", session_id=session_id, error=str(e))
            return []

    async def clear_conversation(self, session_id: str) -> int:
        """Clear all messages in a conversation session. Returns count deleted."""
        try:
            async with self.get_session() as session:
                stmt = select(ConversationMessage).where(ConversationMessage.session_id == session_id)
                result = await session.execute(stmt)
                messages = result.scalars().all()

                count = len(messages)
                for message in messages:
                    await session.delete(message)

                await session.commit()
                logger.info(f"[Memory] Cleared {count} messages from session '{session_id}'")
                return count

        except Exception as e:
            logger.error("Failed to clear conversation", session_id=session_id, error=str(e))
            return 0

    async def get_all_conversation_sessions(self) -> List[Dict[str, Any]]:
        """Get info about all conversation sessions."""
        try:
            async with self.get_session() as session:
                # Get distinct session IDs with message count
                from sqlalchemy import func as sql_func

                stmt = select(
                    ConversationMessage.session_id,
                    sql_func.count(ConversationMessage.id).label("message_count"),
                    sql_func.min(ConversationMessage.created_at).label("created_at"),
                ).group_by(ConversationMessage.session_id)

                result = await session.execute(stmt)
                rows = result.all()

                return [
                    {
                        "session_id": row.session_id,
                        "message_count": row.message_count,
                        "created_at": row.created_at.isoformat() if row.created_at else None,
                    }
                    for row in rows
                ]

        except Exception as e:
            logger.error("Failed to get conversation sessions", error=str(e))
            return []

    # ============================================================================
    # Chat Messages (Console Panel persistence)
    # ============================================================================

    async def append_chat_row(
        self,
        session: AsyncSession,
        *,
        session_id: str,
        role: str,
        message: str,
        execution_id: Optional[str] = None,
        uid: Optional[str] = None,
        run_id: Optional[str] = None,
        kind: str = "text",
        status: str = "complete",
        parts: Optional[Dict[str, Any]] = None,
        attachments: Optional[List[Dict[str, Any]]] = None,
        meta: Optional[Dict[str, Any]] = None,
        after: Any = FOLLOW_LEAF,
    ) -> ChatMessage:
        """Append a message to the end of a session's shown path, inside the
        caller's :meth:`reserved_session` (the caller commits).

        The new row follows the thread's active leaf and becomes the leaf, so
        every message joins one chain: two writers at once would otherwise
        both follow the same leaf and fork it, and a fork hides one branch. A
        session written before threads existed follows its newest message.
        ``after`` names the message it follows instead (None: it starts the
        conversation), the way an edit goes beside the message it edits
        (``services/chat/branches.py``).
        """
        from uuid import uuid4

        thread = await session.get(ChatThread, session_id)
        if thread is None:
            newest = await session.execute(
                select(ChatMessage.uid)
                .where(ChatMessage.session_id == session_id)
                .order_by(ChatMessage.created_at.desc(), ChatMessage.id.desc())
                .limit(1)
            )
            parent_uid = newest.scalar_one_or_none()
            thread = ChatThread(session_id=session_id)
            session.add(thread)
        else:
            parent_uid = thread.active_leaf_uid
        if after is not FOLLOW_LEAF:
            parent_uid = after
        row = ChatMessage(
            session_id=session_id, role=role, message=message,
            execution_id=execution_id,
            uid=uid or f"m_{uuid4().hex}",
            parent_uid=parent_uid,
            run_id=run_id,
            kind=kind,
            status=status,
            parts=parts or {},
            attachments=attachments or [],
            meta=meta or {},
        )
        session.add(row)
        thread.active_leaf_uid = row.uid
        thread.revision = (thread.revision or 0) + 1
        thread.updated_at = datetime.now(timezone.utc)
        await session.flush()
        return row

    async def add_chat_message(
        self, session_id: str, role: str, message: str,
        execution_id: Optional[str] = None,
        *,
        uid: Optional[str] = None,
        run_id: Optional[str] = None,
        kind: str = "text",
        status: str = "complete",
        parts: Optional[Dict[str, Any]] = None,
        attachments: Optional[List[Dict[str, Any]]] = None,
        meta: Optional[Dict[str, Any]] = None,
    ) -> Optional[Dict[str, Any]]:
        """Append a chat message (:meth:`append_chat_row`) and return it as
        :meth:`chat_row` shapes it, or None when it could not be saved.

        A ``uid`` already in the session returns the stored row unchanged, so
        a retried write (an activity attempt, a resent message) saves once.
        """
        try:
            async with self.reserved_session() as session:
                if uid is not None:
                    found = await session.execute(select(ChatMessage).where(ChatMessage.uid == uid))
                    existing = found.scalar_one_or_none()
                    if existing is not None:
                        if existing.session_id != session_id:
                            raise ValueError("message id belongs to another chat")
                        return self.chat_row(existing)
                row = await self.append_chat_row(
                    session,
                    session_id=session_id, role=role, message=message, execution_id=execution_id,
                    uid=uid, run_id=run_id, kind=kind, status=status,
                    parts=parts, attachments=attachments, meta=meta,
                )
                await session.commit()
                logger.debug(f"[Chat] Added {role} message to session '{session_id}'")
                return self.chat_row(row)

        except Exception as e:
            logger.error("Failed to add chat message", session_id=session_id, error=str(e))
            return None

    @staticmethod
    def chat_row(message: ChatMessage) -> Dict[str, Any]:
        """A chat row as the handlers read it. The timestamp carries its UTC
        offset (SQLite returns the stored UTC time naive)."""
        moment = message.created_at
        if moment is not None and moment.tzinfo is None:
            moment = moment.replace(tzinfo=timezone.utc)
        return {
            "id": message.id,
            "uid": message.uid,
            "parent_uid": message.parent_uid,
            "run_id": message.run_id,
            "role": message.role,
            "message": message.message,
            "kind": message.kind or "text",
            "status": message.status or "complete",
            "parts": message.parts or {},
            "attachments": message.attachments or [],
            "meta": message.meta or {},
            "timestamp": moment.isoformat() if moment is not None else None,
            "execution_id": message.execution_id,
        }

    async def read_chat_messages(
        self, session_id: str, limit: Optional[int] = None,
        execution_id: Optional[str] = None,
    ) -> List[Dict[str, Any]]:
        """Chat messages for a session, oldest first: the newest ``limit``
        when given, of one generation when ``execution_id`` is. Rows are
        shaped by :meth:`chat_row`. Raises when the read fails, so a failure
        is never mistaken for an empty thread."""
        async with self.get_session() as session:
            stmt = select(ChatMessage).where(ChatMessage.session_id == session_id)
            if execution_id is not None:
                stmt = stmt.where(ChatMessage.execution_id == execution_id)
            stmt = stmt.order_by(ChatMessage.created_at.desc(), ChatMessage.id.desc())
            if limit and limit > 0:
                stmt = stmt.limit(limit)

            result = await session.execute(stmt)
            return [self.chat_row(m) for m in reversed(result.scalars().all())]

    async def clear_chat_messages(self, session_id: str) -> int:
        """Clear a session's chat: its messages, and the runs, run parts,
        notes, branch snapshots, ratings and thread state that belong to
        them. Returns the messages deleted."""
        from sqlalchemy import delete as sa_delete

        try:
            async with self.get_session() as session:
                stmt = select(ChatMessage).where(ChatMessage.session_id == session_id)
                result = await session.execute(stmt)
                messages = result.scalars().all()

                count = len(messages)
                for message in messages:
                    await session.delete(message)

                run_ids = select(ChatRun.run_id).where(ChatRun.session_id == session_id)
                await session.execute(sa_delete(ChatRunPart).where(ChatRunPart.run_id.in_(run_ids)))
                await session.execute(sa_delete(ChatRun).where(ChatRun.session_id == session_id))
                await session.execute(sa_delete(ChatNote).where(ChatNote.session_id == session_id))
                await session.execute(sa_delete(ChatBranchSnapshot).where(ChatBranchSnapshot.session_id == session_id))
                await session.execute(sa_delete(ChatFeedback).where(ChatFeedback.session_id == session_id))
                await session.execute(sa_delete(ChatThread).where(ChatThread.session_id == session_id))

                await session.commit()
                logger.info(f"[Chat] Cleared {count} messages from session '{session_id}'")
                return count

        except Exception as e:
            logger.error("Failed to clear chat messages", session_id=session_id, error=str(e))
            return 0

    # ============================================================================
    # Console Logs (Console Panel persistence)
    # ============================================================================

    async def add_console_log(self, log_data: Dict[str, Any]) -> bool:
        """Add a console log entry to the database."""
        from models.database import ConsoleLog
        import json

        try:
            async with self.get_session() as session:
                console_log = ConsoleLog(
                    node_id=log_data.get("node_id", ""),
                    label=log_data.get("label", ""),
                    workflow_id=log_data.get("workflow_id"),
                    execution_id=log_data.get("execution_id"),
                    data=json.dumps(log_data.get("data", {})),
                    formatted=log_data.get("formatted", ""),
                    format=log_data.get("format", "text"),
                    source_node_id=log_data.get("source_node_id"),
                    source_node_type=log_data.get("source_node_type"),
                    source_node_label=log_data.get("source_node_label"),
                )
                session.add(console_log)
                await session.commit()
                logger.debug(f"[Console] Added log from node '{log_data.get('node_id')}'")
                return True

        except Exception as e:
            logger.error("Failed to add console log", error=str(e))
            return False

    async def get_console_logs(
        self, limit: int = 100, workflow_id: Optional[str] = None,
        execution_id: Optional[str] = None,
    ) -> List[Dict[str, Any]]:
        """Get console logs, optionally limited and scoped to a workflow.

        ``workflow_id=None`` returns the global stream (legacy behavior, used
        by the initial WebSocket-bootstrap fetch before any workflow is
        selected). When a workflow is open, the frontend passes its id so
        the panel only shows logs from that workflow.
        """
        from models.database import ConsoleLog
        import json

        try:
            async with self.get_session() as session:
                stmt = select(ConsoleLog).order_by(ConsoleLog.created_at.desc()).limit(limit)
                if workflow_id is not None:
                    stmt = stmt.where(ConsoleLog.workflow_id == workflow_id)
                if execution_id is not None:
                    stmt = stmt.where(ConsoleLog.execution_id == execution_id)

                result = await session.execute(stmt)
                logs = result.scalars().all()

                # Return in reverse-chronological order (newest first)
                # Matches real-time prepend convention in WebSocketContext
                return [
                    {
                        "node_id": log.node_id,
                        "label": log.label,
                        "workflow_id": log.workflow_id,
                        "execution_id": log.execution_id,
                        "data": json.loads(log.data) if log.data else {},
                        "formatted": log.formatted,
                        "format": log.format,
                        "source_node_id": log.source_node_id,
                        "source_node_type": log.source_node_type,
                        "source_node_label": log.source_node_label,
                        "timestamp": log.created_at.isoformat(),
                    }
                    for log in logs
                ]

        except Exception as e:
            logger.error("Failed to get console logs", error=str(e))
            return []

    async def clear_console_logs(self, workflow_id: Optional[str] = None) -> int:
        """Clear console logs, optionally scoped to a single workflow.

        ``workflow_id=None`` clears every row (used for the global "Clear"
        action and one-shot maintenance). When a workflow is open, the
        frontend passes its id so other workflows' history is preserved.
        """
        from models.database import ConsoleLog

        try:
            async with self.get_session() as session:
                stmt = select(ConsoleLog)
                if workflow_id is not None:
                    stmt = stmt.where(ConsoleLog.workflow_id == workflow_id)
                result = await session.execute(stmt)
                logs = result.scalars().all()

                count = len(logs)
                for log in logs:
                    await session.delete(log)

                await session.commit()
                logger.info(f"[Console] Cleared {count} console logs" + (f" (workflow={workflow_id})" if workflow_id else " (all)"))
                return count

        except Exception as e:
            logger.error("Failed to clear console logs", error=str(e))
            return 0

    async def cleanup_old_console_logs(self, keep: int = 1000) -> int:
        """Keep only the most recent N console logs. Returns count deleted."""
        from models.database import ConsoleLog

        try:
            async with self.get_session() as session:
                # Count total logs
                count_stmt = select(func.count()).select_from(ConsoleLog)
                total_result = await session.execute(count_stmt)
                total = total_result.scalar() or 0

                if total <= keep:
                    return 0

                # Get IDs of logs to keep (most recent)
                keep_stmt = select(ConsoleLog.id).order_by(ConsoleLog.created_at.desc()).limit(keep)
                keep_result = await session.execute(keep_stmt)
                keep_ids = {row[0] for row in keep_result.fetchall()}

                # Delete logs not in keep list
                delete_stmt = select(ConsoleLog).where(ConsoleLog.id.notin_(keep_ids))
                result = await session.execute(delete_stmt)
                old_logs = result.scalars().all()

                count = len(old_logs)
                for log in old_logs:
                    await session.delete(log)

                await session.commit()
                if count > 0:
                    logger.info("Cleaned up old console logs", deleted=count, kept=keep)
                return count

        except Exception as e:
            logger.error("Failed to cleanup old console logs", error=str(e))
            return 0

    # ============================================================================
    # Cache Entries (SQLite-backed Redis alternative)
    # ============================================================================

    async def get_cache_entry(self, key: str) -> Optional[str]:
        """Get cache value by key. Returns None if expired or not found."""
        import time

        try:
            async with self.get_session() as session:
                stmt = select(CacheEntry).where(CacheEntry.key == key)
                result = await session.execute(stmt)
                entry = result.scalar_one_or_none()

                if not entry:
                    return None

                # Check expiration
                if entry.expires_at and entry.expires_at < time.time():
                    # Entry expired - delete it
                    await session.delete(entry)
                    await session.commit()
                    return None

                return entry.value

        except Exception as e:
            logger.error("Failed to get cache entry", key=key, error=str(e))
            return None

    async def set_cache_entry(self, key: str, value: str, ttl: Optional[int] = None) -> bool:
        """Set cache value with optional TTL in seconds."""
        import time

        try:
            expires_at = time.time() + ttl if ttl else None

            async with self.get_session() as session:
                # Try to get existing entry
                stmt = select(CacheEntry).where(CacheEntry.key == key)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                if existing:
                    existing.value = value
                    existing.expires_at = expires_at
                    existing.created_at = time.time()
                else:
                    entry = CacheEntry(key=key, value=value, expires_at=expires_at, created_at=time.time())
                    session.add(entry)

                await session.commit()
                return True

        except Exception as e:
            logger.error("Failed to set cache entry", key=key, error=str(e))
            return False

    async def delete_cache_entry(self, key: str) -> bool:
        """Delete cache entry by key."""
        try:
            async with self.get_session() as session:
                stmt = select(CacheEntry).where(CacheEntry.key == key)
                result = await session.execute(stmt)
                entry = result.scalar_one_or_none()

                if entry:
                    await session.delete(entry)
                    await session.commit()

                return True

        except Exception as e:
            logger.error("Failed to delete cache entry", key=key, error=str(e))
            return False

    async def delete_cache_pattern(self, pattern: str) -> int:
        """Delete cache entries matching pattern (uses SQL LIKE)."""
        try:
            # Convert glob pattern to SQL LIKE pattern
            sql_pattern = pattern.replace("*", "%")

            async with self.get_session() as session:
                stmt = select(CacheEntry).where(CacheEntry.key.like(sql_pattern))
                result = await session.execute(stmt)
                entries = result.scalars().all()

                count = len(entries)
                for entry in entries:
                    await session.delete(entry)

                await session.commit()
                logger.debug("Deleted cache entries", pattern=pattern, count=count)
                return count

        except Exception as e:
            logger.error("Failed to delete cache pattern", pattern=pattern, error=str(e))
            return 0

    async def cleanup_expired_cache(self) -> int:
        """Remove all expired cache entries. Returns count deleted."""
        import time

        try:
            async with self.get_session() as session:
                stmt = select(CacheEntry).where(CacheEntry.expires_at.isnot(None), CacheEntry.expires_at < time.time())
                result = await session.execute(stmt)
                entries = result.scalars().all()

                count = len(entries)
                for entry in entries:
                    await session.delete(entry)

                await session.commit()
                if count > 0:
                    logger.info("Cleaned up expired cache entries", count=count)
                return count

        except Exception as e:
            logger.error("Failed to cleanup expired cache", error=str(e))
            return 0

    async def cleanup_old_cache(self, max_age_hours: int = 24) -> int:
        """Remove cache entries older than max_age_hours. Returns count deleted."""
        import time

        try:
            async with self.get_session() as session:
                cutoff_time = time.time() - (max_age_hours * 3600)
                stmt = select(CacheEntry).where(CacheEntry.created_at < cutoff_time)
                result = await session.execute(stmt)
                entries = result.scalars().all()

                count = len(entries)
                for entry in entries:
                    await session.delete(entry)

                await session.commit()
                if count > 0:
                    logger.info("Cleaned up old cache entries", count=count, max_age_hours=max_age_hours)
                return count

        except Exception as e:
            logger.error("Failed to cleanup old cache", error=str(e))
            return 0

    async def cache_exists(self, key: str) -> bool:
        """Check if cache key exists and is not expired."""
        import time

        try:
            async with self.get_session() as session:
                stmt = select(CacheEntry).where(CacheEntry.key == key)
                result = await session.execute(stmt)
                entry = result.scalar_one_or_none()

                if not entry:
                    return False

                # Check expiration
                if entry.expires_at and entry.expires_at < time.time():
                    return False

                return True

        except Exception as e:
            logger.error("Failed to check cache exists", key=key, error=str(e))
            return False

    # ============================================================================
    # Tool Schemas (Source of truth for tool node configurations)
    # ============================================================================

    async def save_tool_schema(
        self,
        node_id: str,
        tool_name: str,
        tool_description: str,
        schema_config: Dict[str, Any],
        connected_services: Optional[Dict[str, Any]] = None,
    ) -> bool:
        """Save or update tool schema for a node."""
        try:
            async with self.get_session() as session:
                # Try to get existing schema
                stmt = select(ToolSchema).where(ToolSchema.node_id == node_id)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                action = "updated"
                if existing:
                    existing.tool_name = tool_name
                    existing.tool_description = tool_description
                    existing.schema_config = schema_config
                    existing.connected_services = connected_services
                else:
                    action = "created"
                    existing = ToolSchema(
                        node_id=node_id,
                        tool_name=tool_name,
                        tool_description=tool_description,
                        schema_config=schema_config,
                        connected_services=connected_services,
                    )
                    session.add(existing)

                await session.commit()
                logger.info(f"[DB] Tool schema {action}", node_id=node_id, tool_name=tool_name)
                return True

        except Exception as e:
            logger.error("Failed to save tool schema", node_id=node_id, error=str(e))
            return False

    async def get_tool_schema(self, node_id: str) -> Optional[Dict[str, Any]]:
        """Get tool schema for a node."""
        try:
            async with self.get_session() as session:
                stmt = select(ToolSchema).where(ToolSchema.node_id == node_id)
                result = await session.execute(stmt)
                schema = result.scalar_one_or_none()

                if not schema:
                    return None

                return {
                    "node_id": schema.node_id,
                    "tool_name": schema.tool_name,
                    "tool_description": schema.tool_description,
                    "schema_config": schema.schema_config,
                    "connected_services": schema.connected_services,
                    "created_at": schema.created_at.isoformat() if schema.created_at else None,
                    "updated_at": schema.updated_at.isoformat() if schema.updated_at else None,
                }

        except Exception as e:
            logger.error("Failed to get tool schema", node_id=node_id, error=str(e))
            return None

    async def delete_tool_schema(self, node_id: str) -> bool:
        """Delete tool schema for a node."""
        try:
            async with self.get_session() as session:
                stmt = select(ToolSchema).where(ToolSchema.node_id == node_id)
                result = await session.execute(stmt)
                schema = result.scalar_one_or_none()

                if schema:
                    await session.delete(schema)
                    await session.commit()
                    logger.info("[DB] Tool schema deleted", node_id=node_id)

                return True

        except Exception as e:
            logger.error("Failed to delete tool schema", node_id=node_id, error=str(e))
            return False

    async def get_all_tool_schemas(self) -> List[Dict[str, Any]]:
        """Get all tool schemas."""
        try:
            async with self.get_session() as session:
                stmt = select(ToolSchema).order_by(ToolSchema.updated_at.desc())
                result = await session.execute(stmt)
                schemas = result.scalars().all()

                return [
                    {
                        "node_id": s.node_id,
                        "tool_name": s.tool_name,
                        "tool_description": s.tool_description,
                        "schema_config": s.schema_config,
                        "connected_services": s.connected_services,
                        "updated_at": s.updated_at.isoformat() if s.updated_at else None,
                    }
                    for s in schemas
                ]

        except Exception as e:
            logger.error("Failed to get all tool schemas", error=str(e))
            return []

    # ============================================================================
    # Android Relay Session Persistence
    # ============================================================================

    async def save_android_relay_session(
        self, relay_url: str, api_key: str, device_id: str, device_name: Optional[str] = None, session_token: Optional[str] = None
    ) -> bool:
        """Save Android relay pairing session for auto-reconnect on server restart.

        Args:
            relay_url: WebSocket relay URL
            api_key: API key for relay authentication
            device_id: Paired Android device ID
            device_name: Paired device name
            session_token: Relay session token
        """
        import json

        try:
            session_data = json.dumps(
                {
                    "relay_url": relay_url,
                    "api_key": api_key,
                    "device_id": device_id,
                    "device_name": device_name,
                    "session_token": session_token,
                }
            )
            # No TTL - session persists until explicitly cleared
            return await self.set_cache_entry("android_relay_session", session_data)
        except Exception as e:
            logger.error("Failed to save Android relay session", error=str(e))
            return False

    async def get_android_relay_session(self) -> Optional[Dict[str, Any]]:
        """Get stored Android relay session for auto-reconnect.

        Returns:
            Session data dict or None if not found
        """
        import json

        try:
            value = await self.get_cache_entry("android_relay_session")
            if value:
                return json.loads(value)
            return None
        except Exception as e:
            logger.error("Failed to get Android relay session", error=str(e))
            return None

    async def clear_android_relay_session(self) -> bool:
        """Clear stored Android relay session (on explicit disconnect)."""
        try:
            return await self.delete_cache_entry("android_relay_session")
        except Exception as e:
            logger.error("Failed to clear Android relay session", error=str(e))
            return False

    # ============================================================================
    # User Skills (Custom skills for Zeenie)
    # ============================================================================

    async def create_user_skill(
        self,
        name: str,
        display_name: str,
        description: str,
        instructions: str,
        allowed_tools: Optional[str] = None,
        category: str = "custom",
        icon: str = "star",
        color: str = "#6366F1",
        metadata_json: Optional[Dict[str, Any]] = None,
        created_by: Optional[int] = None,
    ) -> Optional[Dict[str, Any]]:
        """Create a new user skill."""
        try:
            async with self.get_session() as session:
                skill = UserSkill(
                    name=name,
                    display_name=display_name,
                    description=description,
                    instructions=instructions,
                    allowed_tools=allowed_tools,
                    category=category,
                    icon=icon,
                    color=color,
                    metadata_json=metadata_json,
                    created_by=created_by,
                )
                session.add(skill)
                await session.commit()
                await session.refresh(skill)

                logger.info(f"[DB] Created user skill: {name}")
                return self._skill_to_dict(skill)

        except IntegrityError:
            logger.error(f"User skill with name '{name}' already exists")
            return None
        except Exception as e:
            logger.error("Failed to create user skill", name=name, error=str(e))
            return None

    async def get_user_skill(self, name: str) -> Optional[Dict[str, Any]]:
        """Get user skill by name."""
        try:
            async with self.get_session() as session:
                stmt = select(UserSkill).where(UserSkill.name == name)
                result = await session.execute(stmt)
                skill = result.scalar_one_or_none()

                return self._skill_to_dict(skill) if skill else None

        except Exception as e:
            logger.error("Failed to get user skill", name=name, error=str(e))
            return None

    async def get_user_skill_by_id(self, skill_id: int) -> Optional[Dict[str, Any]]:
        """Get user skill by ID."""
        try:
            async with self.get_session() as session:
                stmt = select(UserSkill).where(UserSkill.id == skill_id)
                result = await session.execute(stmt)
                skill = result.scalar_one_or_none()

                return self._skill_to_dict(skill) if skill else None

        except Exception as e:
            logger.error("Failed to get user skill by id", skill_id=skill_id, error=str(e))
            return None

    async def get_all_user_skills(self, active_only: bool = True) -> List[Dict[str, Any]]:
        """Get all user skills, optionally filtered by active status."""
        try:
            async with self.get_session() as session:
                if active_only:
                    stmt = select(UserSkill).where(UserSkill.is_active).order_by(UserSkill.display_name)
                else:
                    stmt = select(UserSkill).order_by(UserSkill.display_name)

                result = await session.execute(stmt)
                skills = result.scalars().all()

                return [self._skill_to_dict(s) for s in skills]

        except Exception as e:
            logger.error("Failed to get all user skills", error=str(e))
            return []

    async def update_user_skill(
        self,
        name: str,
        display_name: Optional[str] = None,
        description: Optional[str] = None,
        instructions: Optional[str] = None,
        allowed_tools: Optional[str] = None,
        category: Optional[str] = None,
        icon: Optional[str] = None,
        color: Optional[str] = None,
        metadata_json: Optional[Dict[str, Any]] = None,
        is_active: Optional[bool] = None,
    ) -> Optional[Dict[str, Any]]:
        """Update an existing user skill."""
        try:
            async with self.get_session() as session:
                stmt = select(UserSkill).where(UserSkill.name == name)
                result = await session.execute(stmt)
                skill = result.scalar_one_or_none()

                if not skill:
                    logger.error(f"User skill '{name}' not found for update")
                    return None

                # Update only provided fields
                if display_name is not None:
                    skill.display_name = display_name
                if description is not None:
                    skill.description = description
                if instructions is not None:
                    skill.instructions = instructions
                if allowed_tools is not None:
                    skill.allowed_tools = allowed_tools
                if category is not None:
                    skill.category = category
                if icon is not None:
                    skill.icon = icon
                if color is not None:
                    skill.color = color
                if metadata_json is not None:
                    skill.metadata_json = metadata_json
                if is_active is not None:
                    skill.is_active = is_active

                await session.commit()
                await session.refresh(skill)

                logger.info(f"[DB] Updated user skill: {name}")
                return self._skill_to_dict(skill)

        except Exception as e:
            logger.error("Failed to update user skill", name=name, error=str(e))
            return None

    async def delete_user_skill(self, name: str) -> bool:
        """Delete a user skill by name."""
        try:
            async with self.get_session() as session:
                stmt = select(UserSkill).where(UserSkill.name == name)
                result = await session.execute(stmt)
                skill = result.scalar_one_or_none()

                if skill:
                    await session.delete(skill)
                    await session.commit()
                    logger.info(f"[DB] Deleted user skill: {name}")
                    return True

                return False

        except Exception as e:
            logger.error("Failed to delete user skill", name=name, error=str(e))
            return False

    def _skill_to_dict(self, skill: UserSkill) -> Dict[str, Any]:
        """Convert UserSkill model to dictionary."""
        return {
            "id": skill.id,
            "name": skill.name,
            "display_name": skill.display_name,
            "description": skill.description,
            "instructions": skill.instructions,
            "allowed_tools": skill.allowed_tools.split(",") if skill.allowed_tools else [],
            "category": skill.category,
            "icon": skill.icon,
            "color": skill.color,
            "metadata": skill.metadata_json,
            "is_active": skill.is_active,
            "created_by": skill.created_by,
            "created_at": skill.created_at.isoformat() if skill.created_at else None,
            "updated_at": skill.updated_at.isoformat() if skill.updated_at else None,
        }

    # ============================================================================
    # User Settings (UI defaults and preferences)
    # ============================================================================

    async def get_user_settings(self, user_id: str = "default") -> Optional[Dict[str, Any]]:
        """Get user settings. Returns None if not found."""
        try:
            async with self.get_session() as session:
                stmt = select(UserSettings).where(UserSettings.user_id == user_id)
                result = await session.execute(stmt)
                settings = result.scalar_one_or_none()

                if not settings:
                    return None

                return {
                    "user_id": settings.user_id,
                    "auto_save": settings.auto_save,
                    "auto_save_interval": settings.auto_save_interval,
                    "sidebar_default_open": settings.sidebar_default_open,
                    "component_palette_default_open": settings.component_palette_default_open,
                    "console_panel_default_open": settings.console_panel_default_open,
                    "memory_window_size": settings.memory_window_size,
                    "compaction_ratio": settings.compaction_ratio,
                    "examples_loaded": settings.examples_loaded,
                    "onboarding_completed": settings.onboarding_completed,
                    "onboarding_step": settings.onboarding_step,
                    "getting_started_dismissed": settings.getting_started_dismissed,
                    "getting_started_added_key": settings.getting_started_added_key,
                    "getting_started_ran_example": settings.getting_started_ran_example,
                    "getting_started_built_workflow": settings.getting_started_built_workflow,
                    "getting_started_tried_theme": settings.getting_started_tried_theme,
                    "getting_started_said_hello": settings.getting_started_said_hello,
                    "getting_started_approved_draft": settings.getting_started_approved_draft,
                    "default_llm_provider": settings.default_llm_provider,
                    "default_llm_model": settings.default_llm_model,
                    "chat_model": settings.chat_model,
                    "chat_effort": settings.chat_effort,
                    "auto_add_skill_for_tools": settings.auto_add_skill_for_tools,
                    "auto_rebind_tools_after_canvas_change": settings.auto_rebind_tools_after_canvas_change,
                    "agent_recursion_limit": settings.agent_recursion_limit,
                    "tool_result_max_chars": settings.tool_result_max_chars,
                    "max_concurrent_subagents": settings.max_concurrent_subagents,
                    "max_delegation_depth": settings.max_delegation_depth,
                    "profile_full_name": settings.profile_full_name,
                    "profile_call_name": settings.profile_call_name,
                    "profile_role": settings.profile_role,
                    "profile_preferences": settings.profile_preferences,
                    "profile_timezone": settings.profile_timezone,
                    "memory_across_chats": settings.memory_across_chats,
                    "prefer_local_ai": settings.prefer_local_ai,
                    "created_at": settings.created_at.isoformat() if settings.created_at else None,
                    "updated_at": settings.updated_at.isoformat() if settings.updated_at else None,
                }

        except Exception as e:
            logger.error("Failed to get user settings", user_id=user_id, error=str(e))
            return None

    async def save_user_settings(self, settings_data: Dict[str, Any], user_id: str = "default") -> bool:
        """Save or update user settings.

        Both branches now read field names off ``UserSettings.model_fields``
        instead of duplicating each setting's name + default. Adding a new
        field to :class:`UserSettings` no longer requires touching this
        method, and the SQLModel field's ``Field(default=...)`` is the
        single source of truth for the default value.
        """
        try:
            async with self.get_session() as session:
                stmt = select(UserSettings).where(UserSettings.user_id == user_id)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                # Filter incoming dict to known model fields. ``user_id`` and
                # the bookkeeping columns (id / created_at / updated_at) are
                # never overridden through this path.
                _MANAGED = {"id", "user_id", "created_at", "updated_at"}
                known_fields = set(UserSettings.model_fields.keys()) - _MANAGED
                payload = {k: v for k, v in settings_data.items() if k in known_fields}

                if existing:
                    for key, value in payload.items():
                        setattr(existing, key, value)
                else:
                    # SQLModel field defaults (``Field(default=...)``) apply
                    # for every key the caller did not explicitly pass.
                    existing = UserSettings(user_id=user_id, **payload)
                    session.add(existing)

                await session.commit()
                logger.info(f"[DB] User settings saved for user_id: {user_id}")
                return True

        except Exception as e:
            logger.error("Failed to save user settings", user_id=user_id, error=str(e))
            return False

    # ============================================================================
    # Provider Defaults
    # ============================================================================

    async def get_provider_defaults(self, provider: str) -> Optional[Dict[str, Any]]:
        """Get default parameters for a provider."""
        try:
            async with self.get_session() as session:
                stmt = select(ProviderDefaults).where(ProviderDefaults.provider == provider.lower())
                result = await session.execute(stmt)
                record = result.scalar_one_or_none()
                if not record:
                    return None
                return {
                    "default_model": record.default_model,
                    "temperature": record.temperature,
                    "max_tokens": record.max_tokens,
                    "thinking_enabled": record.thinking_enabled,
                    "thinking_budget": record.thinking_budget,
                    "reasoning_effort": record.reasoning_effort,
                    "reasoning_format": record.reasoning_format,
                }
        except Exception as e:
            logger.error("Failed to get provider defaults", provider=provider, error=str(e))
            return None

    async def save_provider_defaults(self, provider: str, defaults: Dict[str, Any]) -> bool:
        """Upsert provider defaults."""
        try:
            async with self.get_session() as session:
                stmt = select(ProviderDefaults).where(ProviderDefaults.provider == provider.lower())
                result = await session.execute(stmt)
                record = result.scalar_one_or_none()

                now = datetime.now(timezone.utc)
                if record:
                    record.default_model = defaults.get("default_model", record.default_model)
                    record.temperature = defaults.get("temperature", record.temperature)
                    record.max_tokens = defaults.get("max_tokens", record.max_tokens)
                    record.thinking_enabled = defaults.get("thinking_enabled", record.thinking_enabled)
                    record.thinking_budget = defaults.get("thinking_budget", record.thinking_budget)
                    record.reasoning_effort = defaults.get("reasoning_effort", record.reasoning_effort)
                    record.reasoning_format = defaults.get("reasoning_format", record.reasoning_format)
                    record.updated_at = now
                else:
                    record = ProviderDefaults(
                        provider=provider.lower(),
                        default_model=defaults.get("default_model", ""),
                        temperature=defaults.get("temperature", 0.7),
                        max_tokens=defaults.get("max_tokens", 1000),
                        thinking_enabled=defaults.get("thinking_enabled", False),
                        thinking_budget=defaults.get("thinking_budget", 2048),
                        reasoning_effort=defaults.get("reasoning_effort", "medium"),
                        reasoning_format=defaults.get("reasoning_format", "parsed"),
                        created_at=now,
                        updated_at=now,
                    )
                    session.add(record)

                await session.commit()
                logger.info(f"[DB] Provider defaults saved for: {provider}")
                return True

        except Exception as e:
            logger.error("Failed to save provider defaults", provider=provider, error=str(e))
            return False

    # ============================================================================
    # Token Usage Metrics
    # ============================================================================

    async def save_token_metric(self, metric: Dict[str, Any]) -> bool:
        """Save a token usage metric record."""
        try:
            async with self.get_session() as session:
                entry = TokenUsageMetric(
                    session_id=metric.get("session_id", "default"),
                    node_id=metric.get("node_id", ""),
                    workflow_id=metric.get("workflow_id"),
                    provider=metric.get("provider", ""),
                    model=metric.get("model", ""),
                    input_tokens=metric.get("input_tokens", 0),
                    output_tokens=metric.get("output_tokens", 0),
                    total_tokens=metric.get("total_tokens", 0),
                    cache_creation_tokens=metric.get("cache_creation_tokens", 0),
                    cache_read_tokens=metric.get("cache_read_tokens", 0),
                    reasoning_tokens=metric.get("reasoning_tokens", 0),
                    iteration=metric.get("iteration", 1),
                    execution_id=metric.get("execution_id"),
                    created_at=datetime.now(timezone.utc),
                    # Cost fields
                    input_cost=metric.get("input_cost", 0.0),
                    output_cost=metric.get("output_cost", 0.0),
                    cache_cost=metric.get("cache_cost", 0.0),
                    total_cost=metric.get("total_cost", 0.0),
                )
                session.add(entry)
                await session.commit()
                return True
        except Exception as e:
            logger.error("Failed to save token metric", error=str(e))
            return False

    async def get_session_token_metrics(self, session_id: str, limit: int = 100) -> List[Dict[str, Any]]:
        """Get token metrics for a session."""
        try:
            async with self.get_session() as session:
                stmt = (
                    select(TokenUsageMetric)
                    .where(TokenUsageMetric.session_id == session_id)
                    .order_by(TokenUsageMetric.created_at.desc())
                    .limit(limit)
                )
                result = await session.execute(stmt)
                metrics = result.scalars().all()

                return [
                    {
                        "id": m.id,
                        "session_id": m.session_id,
                        "node_id": m.node_id,
                        "provider": m.provider,
                        "model": m.model,
                        "input_tokens": m.input_tokens,
                        "output_tokens": m.output_tokens,
                        "total_tokens": m.total_tokens,
                        "cache_creation_tokens": m.cache_creation_tokens,
                        "cache_read_tokens": m.cache_read_tokens,
                        "reasoning_tokens": m.reasoning_tokens,
                        "iteration": m.iteration,
                        "created_at": m.created_at.isoformat() if m.created_at else None,
                    }
                    for m in metrics
                ]
        except Exception as e:
            logger.error("Failed to get token metrics", session_id=session_id, error=str(e))
            return []

    async def get_provider_usage_summary(self) -> List[Dict[str, Any]]:
        """Get aggregated token usage and cost by provider.

        Returns a list of provider summaries with:
        - provider: Provider name (openai, anthropic, etc.)
        - total_input_tokens: Sum of input tokens
        - total_output_tokens: Sum of output tokens
        - total_tokens: Sum of all tokens
        - total_input_cost: Sum of input costs (USD)
        - total_output_cost: Sum of output costs (USD)
        - total_cost: Sum of all costs (USD)
        - execution_count: Number of executions
        - models: Breakdown by model (list of dicts)
        """
        try:
            async with self.get_session() as session:
                from sqlalchemy import func

                # First get per-model breakdown
                model_stmt = (
                    select(
                        TokenUsageMetric.provider,
                        TokenUsageMetric.model,
                        func.sum(TokenUsageMetric.input_tokens).label("input_tokens"),
                        func.sum(TokenUsageMetric.output_tokens).label("output_tokens"),
                        func.sum(TokenUsageMetric.total_tokens).label("total_tokens"),
                        func.sum(TokenUsageMetric.input_cost).label("input_cost"),
                        func.sum(TokenUsageMetric.output_cost).label("output_cost"),
                        func.sum(TokenUsageMetric.cache_cost).label("cache_cost"),
                        func.sum(TokenUsageMetric.total_cost).label("total_cost"),
                        func.count().label("execution_count"),
                    )
                    .group_by(TokenUsageMetric.provider, TokenUsageMetric.model)
                    .order_by(TokenUsageMetric.provider, TokenUsageMetric.model)
                )
                result = await session.execute(model_stmt)
                rows = result.all()

                # Aggregate by provider
                providers: Dict[str, Dict] = {}
                for row in rows:
                    provider = row.provider or "unknown"
                    if provider not in providers:
                        providers[provider] = {
                            "provider": provider,
                            "total_input_tokens": 0,
                            "total_output_tokens": 0,
                            "total_tokens": 0,
                            "total_input_cost": 0.0,
                            "total_output_cost": 0.0,
                            "total_cache_cost": 0.0,
                            "total_cost": 0.0,
                            "execution_count": 0,
                            "models": [],
                        }

                    p = providers[provider]
                    p["total_input_tokens"] += row.input_tokens or 0
                    p["total_output_tokens"] += row.output_tokens or 0
                    p["total_tokens"] += row.total_tokens or 0
                    p["total_input_cost"] += float(row.input_cost or 0)
                    p["total_output_cost"] += float(row.output_cost or 0)
                    p["total_cache_cost"] += float(row.cache_cost or 0)
                    p["total_cost"] += float(row.total_cost or 0)
                    p["execution_count"] += row.execution_count or 0

                    p["models"].append(
                        {
                            "model": row.model,
                            "input_tokens": row.input_tokens or 0,
                            "output_tokens": row.output_tokens or 0,
                            "total_tokens": row.total_tokens or 0,
                            "input_cost": round(float(row.input_cost or 0), 6),
                            "output_cost": round(float(row.output_cost or 0), 6),
                            "cache_cost": round(float(row.cache_cost or 0), 6),
                            "total_cost": round(float(row.total_cost or 0), 6),
                            "execution_count": row.execution_count or 0,
                        }
                    )

                # Round provider totals
                for p in providers.values():
                    p["total_input_cost"] = round(p["total_input_cost"], 6)
                    p["total_output_cost"] = round(p["total_output_cost"], 6)
                    p["total_cache_cost"] = round(p["total_cache_cost"], 6)
                    p["total_cost"] = round(p["total_cost"], 6)

                return list(providers.values())

        except Exception as e:
            logger.error("Failed to get provider usage summary", error=str(e))
            return []

    # ============================================================================
    # API Usage Metrics (Twitter, Google Maps, etc.)
    # ============================================================================

    async def save_api_usage_metric(self, metric: Dict[str, Any]) -> bool:
        """Save an API usage metric record.

        Args:
            metric: Dict with session_id, node_id, service, operation, endpoint, resource_count, cost
        """
        try:
            from models.database import APIUsageMetric

            async with self.get_session() as session:
                entry = APIUsageMetric(
                    session_id=metric.get("session_id", "default"),
                    node_id=metric.get("node_id", ""),
                    workflow_id=metric.get("workflow_id"),
                    service=metric.get("service", ""),
                    operation=metric.get("operation", ""),
                    endpoint=metric.get("endpoint", ""),
                    resource_count=metric.get("resource_count", 1),
                    cost=metric.get("cost", 0.0),
                )
                session.add(entry)
                await session.commit()
                return True
        except Exception as e:
            logger.error("Failed to save API usage metric", error=str(e))
            return False

    async def get_api_usage_summary(self, service: str = None) -> List[Dict[str, Any]]:
        """Get aggregated API usage and cost by service.

        Args:
            service: Optional service name to filter (e.g., 'twitter')

        Returns a list of service summaries with:
        - service: Service name (twitter, google_maps, etc.)
        - total_resources: Sum of resources fetched/requests made
        - total_cost: Sum of costs (USD)
        - execution_count: Number of API calls
        - operations: Breakdown by operation (list of dicts)
        """
        try:
            from models.database import APIUsageMetric
            from sqlalchemy import func

            async with self.get_session() as session:
                # Build query with optional service filter
                query = select(
                    APIUsageMetric.service,
                    APIUsageMetric.operation,
                    func.sum(APIUsageMetric.resource_count).label("resource_count"),
                    func.sum(APIUsageMetric.cost).label("cost"),
                    func.count().label("execution_count"),
                ).group_by(APIUsageMetric.service, APIUsageMetric.operation)

                if service:
                    query = query.where(APIUsageMetric.service == service)

                query = query.order_by(APIUsageMetric.service, APIUsageMetric.operation)
                result = await session.execute(query)
                rows = result.all()

                # Aggregate by service
                services: Dict[str, Dict] = {}
                for row in rows:
                    svc = row.service or "unknown"
                    if svc not in services:
                        services[svc] = {"service": svc, "total_resources": 0, "total_cost": 0.0, "execution_count": 0, "operations": []}

                    s = services[svc]
                    s["total_resources"] += row.resource_count or 0
                    s["total_cost"] += float(row.cost or 0)
                    s["execution_count"] += row.execution_count or 0

                    s["operations"].append(
                        {
                            "operation": row.operation,
                            "resource_count": row.resource_count or 0,
                            "total_cost": round(float(row.cost or 0), 6),
                            "execution_count": row.execution_count or 0,
                        }
                    )

                # Round service totals
                for s in services.values():
                    s["total_cost"] = round(s["total_cost"], 6)

                return list(services.values())

        except Exception as e:
            logger.error("Failed to get API usage summary", error=str(e))
            return []

    # ============================================================================
    # Session Token State
    # ============================================================================

    async def get_or_create_session_token_state(self, session_id: str) -> Dict[str, Any]:
        """Get or create token state for a session.

        Handles race conditions when multiple requests try to create the same session.
        """
        try:
            async with self.get_session() as session:
                stmt = select(SessionTokenState).where(SessionTokenState.session_id == session_id)
                result = await session.execute(stmt)
                state = result.scalar_one_or_none()

                if not state:
                    try:
                        state = SessionTokenState(session_id=session_id, updated_at=datetime.now(timezone.utc))
                        session.add(state)
                        await session.commit()
                        await session.refresh(state)
                    except IntegrityError:
                        # Race condition: another request created the session
                        # Rollback and fetch the existing record
                        await session.rollback()
                        result = await session.execute(stmt)
                        state = result.scalar_one_or_none()
                        if not state:
                            # Shouldn't happen, but return defaults if it does
                            return {
                                "session_id": session_id,
                                "cumulative_input_tokens": 0,
                                "cumulative_output_tokens": 0,
                                "cumulative_cache_tokens": 0,
                                "cumulative_reasoning_tokens": 0,
                                "cumulative_total": 0,
                                "last_compaction_at": None,
                                "compaction_count": 0,
                                "custom_threshold": None,
                                "compaction_enabled": True,
                            }

                return {
                    "session_id": state.session_id,
                    "cumulative_input_tokens": state.cumulative_input_tokens,
                    "cumulative_output_tokens": state.cumulative_output_tokens,
                    "cumulative_cache_tokens": state.cumulative_cache_tokens,
                    "cumulative_reasoning_tokens": state.cumulative_reasoning_tokens,
                    "cumulative_total": state.cumulative_total,
                    "last_compaction_at": state.last_compaction_at.isoformat() if state.last_compaction_at else None,
                    "compaction_count": state.compaction_count,
                    "custom_threshold": state.custom_threshold,
                    "compaction_enabled": state.compaction_enabled,
                }
        except Exception as e:
            logger.error("Failed to get session token state", error=str(e))
            return {"session_id": session_id, "cumulative_total": 0, "compaction_enabled": True}

    async def update_session_token_state(self, session_id: str, updates: Dict[str, Any]) -> bool:
        """Update session token state."""
        try:
            async with self.get_session() as session:
                stmt = select(SessionTokenState).where(SessionTokenState.session_id == session_id)
                result = await session.execute(stmt)
                state = result.scalar_one_or_none()

                if not state:
                    state = SessionTokenState(session_id=session_id)
                    session.add(state)

                for key, value in updates.items():
                    if hasattr(state, key):
                        setattr(state, key, value)

                state.updated_at = datetime.now(timezone.utc)
                await session.commit()
                return True
        except Exception as e:
            logger.error("Failed to update session token state", error=str(e))
            return False

    async def reset_session_token_state(self, session_id: str) -> bool:
        """Reset token state after compaction."""
        return await self.update_session_token_state(
            session_id,
            {
                "cumulative_input_tokens": 0,
                "cumulative_output_tokens": 0,
                "cumulative_cache_tokens": 0,
                "cumulative_reasoning_tokens": 0,
                "cumulative_total": 0,
                "last_compaction_at": datetime.now(timezone.utc),
            },
        )

    # ============================================================================
    # Compaction Events
    # ============================================================================

    async def save_compaction_event(self, event: Dict[str, Any]) -> bool:
        """Save a compaction event record."""
        try:
            async with self.get_session() as session:
                entry = CompactionEvent(
                    session_id=event.get("session_id", "default"),
                    node_id=event.get("node_id", ""),
                    workflow_id=event.get("workflow_id"),
                    trigger_reason=event.get("trigger_reason", "threshold"),
                    tokens_before=event.get("tokens_before", 0),
                    tokens_after=event.get("tokens_after", 0),
                    messages_before=event.get("messages_before", 0),
                    messages_after=event.get("messages_after", 0),
                    summary_model=event.get("summary_model", ""),
                    summary_provider=event.get("summary_provider", ""),
                    summary_tokens_used=event.get("summary_tokens_used", 0),
                    success=event.get("success", True),
                    error_message=event.get("error_message"),
                    summary_content=event.get("summary_content"),
                    created_at=datetime.now(timezone.utc),
                )
                session.add(entry)
                await session.commit()
                return True
        except Exception as e:
            logger.error("Failed to save compaction event", error=str(e))
            return False

    async def get_compaction_history(self, session_id: str, limit: int = 50) -> List[Dict[str, Any]]:
        """Get compaction history for a session."""
        try:
            async with self.get_session() as session:
                stmt = (
                    select(CompactionEvent)
                    .where(CompactionEvent.session_id == session_id)
                    .order_by(CompactionEvent.created_at.desc())
                    .limit(limit)
                )
                result = await session.execute(stmt)
                events = result.scalars().all()

                return [
                    {
                        "id": e.id,
                        "session_id": e.session_id,
                        "node_id": e.node_id,
                        "trigger_reason": e.trigger_reason,
                        "tokens_before": e.tokens_before,
                        "tokens_after": e.tokens_after,
                        "messages_before": e.messages_before,
                        "messages_after": e.messages_after,
                        "success": e.success,
                        "error_message": e.error_message,
                        "created_at": e.created_at.isoformat() if e.created_at else None,
                    }
                    for e in events
                ]
        except Exception as e:
            logger.error("Failed to get compaction history", session_id=session_id, error=str(e))
            return []

    # ============================================================================
    # Durable workflow control
    # ============================================================================

    async def get_latest_workflow_control(self, workflow_id: str) -> Optional[WorkflowControlExecution]:
        async with self.get_session() as session:
            result = await session.execute(
                select(WorkflowControlExecution)
                .where(WorkflowControlExecution.workflow_id == workflow_id)
                .order_by(WorkflowControlExecution.generation.desc())
                .limit(1)
            )
            return result.scalar_one_or_none()

    async def get_workflow_control_generation(
        self, workflow_id: str, generation: int
    ) -> Optional[WorkflowControlExecution]:
        async with self.get_session() as session:
            result = await session.execute(
                select(WorkflowControlExecution).where(
                    WorkflowControlExecution.workflow_id == workflow_id,
                    WorkflowControlExecution.generation == generation,
                )
            )
            return result.scalar_one_or_none()

    async def list_latest_workflow_controls(
        self, workflow_ids: List[str]
    ) -> Dict[str, WorkflowControlExecution]:
        """The latest control generation for each of ``workflow_ids``, in one
        query. Workflows that were never started are absent. For summaries
        that list many workflows at once (Normal mode's team list); the
        single-workflow status handler still reconciles against Temporal."""
        ids = [str(workflow_id) for workflow_id in workflow_ids if workflow_id]
        if not ids:
            return {}
        latest = (
            select(
                WorkflowControlExecution.workflow_id.label("workflow_id"),
                func.max(WorkflowControlExecution.generation).label("generation"),
            )
            .where(WorkflowControlExecution.workflow_id.in_(ids))
            .group_by(WorkflowControlExecution.workflow_id)
            .subquery()
        )
        async with self.get_session() as session:
            result = await session.execute(
                select(WorkflowControlExecution).join(
                    latest,
                    (WorkflowControlExecution.workflow_id == latest.c.workflow_id)
                    & (WorkflowControlExecution.generation == latest.c.generation),
                )
            )
            return {row.workflow_id: row for row in result.scalars().all()}

    async def has_active_workflow_controls(self) -> bool:
        from models.database import WORKFLOW_CONTROL_ACTIVE_STATES

        async with self.get_session() as session:
            result = await session.execute(
                select(func.count()).select_from(WorkflowControlExecution).where(
                    WorkflowControlExecution.status.in_(WORKFLOW_CONTROL_ACTIVE_STATES)
                )
            )
            return bool(result.scalar_one())

    async def list_active_workflow_controls(self) -> List[WorkflowControlExecution]:
        """Every control row in a non-terminal status (boot-time reconcile).

        At most one generation per workflow can be active (``begin_generation``
        requires the previous one to be ``reset``), so this is effectively
        latest-generation-per-workflow filtered to the active set.
        """
        from models.database import WORKFLOW_CONTROL_ACTIVE_STATES

        async with self.get_session() as session:
            result = await session.execute(
                select(WorkflowControlExecution)
                .where(WorkflowControlExecution.status.in_(WORKFLOW_CONTROL_ACTIVE_STATES))
                .order_by(WorkflowControlExecution.workflow_id, WorkflowControlExecution.generation.desc())
            )
            return list(result.scalars().all())

    async def get_workflow_control_by_idempotency_key(
        self, workflow_id: str, idempotency_key: str
    ) -> Optional[WorkflowControlExecution]:
        async with self.get_session() as session:
            result = await session.execute(
                select(WorkflowControlExecution).where(
                    WorkflowControlExecution.workflow_id == workflow_id,
                    WorkflowControlExecution.idempotency_key == idempotency_key,
                )
            )
            return result.scalar_one_or_none()

    async def create_workflow_control(self, control: WorkflowControlExecution) -> WorkflowControlExecution:
        from models.database import WorkflowRunDataScope
        from services.workflow_sanitizer import sanitize_runtime_payload

        async with self.get_session() as session:
            session.add(control)
            scope_id = control.data_scope_id or control.execution_id
            control.data_scope_id = scope_id
            graph_nodes = [
                node for node in control.graph_snapshot.get("nodes", [])
                if node.get("id")
            ]
            node_ids = [str(node["id"]) for node in graph_nodes]
            parameter_rows = []
            if node_ids:
                parameter_result = await session.execute(
                    select(NodeParameter).where(NodeParameter.node_id.in_(node_ids))
                )
                parameter_rows = list(parameter_result.scalars().all())
            parameters_by_id = {
                row.node_id: dict(row.parameters or {}) for row in parameter_rows
            }
            runtime_nodes = {
                str(node["id"]): {
                    "type": node.get("type"),
                    "canvas_data": sanitize_runtime_payload(dict(node.get("data") or {})),
                    "parameters": sanitize_runtime_payload(parameters_by_id.get(str(node["id"]), {})),
                }
                for node in graph_nodes
            }
            session.add(WorkflowRunDataScope(
                id=scope_id,
                control_id=control.id,
                workflow_id=control.workflow_id,
                generation=control.generation,
                execution_id=control.execution_id,
                root_execution_id=control.root_execution_id,
                temporal_workflow_id=control.controller_workflow_id,
                temporal_run_id=control.controller_run_id,
                source_session_id=control.session_id,
                graph_hash=control.graph_hash,
                node_data={
                    str(node.get("id")): {
                        "type": node.get("type"),
                        "data": sanitize_runtime_payload(node.get("data", {})),
                    }
                    for node in control.graph_snapshot.get("nodes", [])
                    if node.get("id")
                },
                runtime_data={"nodes": runtime_nodes},
            ))
            await session.commit()
            await session.refresh(control)
            return control

    async def update_workflow_run_data_scope(
        self, scope_id: str, *, temporal_run_id: Optional[str] = None,
        status: Optional[str] = None, archived_at: Optional[datetime] = None,
        runtime_data: Optional[Dict[str, Any]] = None,
    ) -> bool:
        """Update the execution link/lifecycle without mutating its node snapshot."""
        from models.database import WorkflowRunDataScope

        values: Dict[str, Any] = {}
        if temporal_run_id is not None:
            values["temporal_run_id"] = temporal_run_id
        if status is not None:
            values["status"] = status
        if archived_at is not None:
            values["archived_at"] = archived_at
        if runtime_data is not None:
            values["runtime_data"] = runtime_data
        if not values:
            return True
        async with self.get_session() as session:
            result = await session.execute(
                update(WorkflowRunDataScope).where(WorkflowRunDataScope.id == scope_id).values(**values)
            )
            await session.commit()
            return result.rowcount == 1

    async def get_workflow_run_data_scope(self, scope_id: str):
        from models.database import WorkflowRunDataScope

        async with self.get_session() as session:
            result = await session.execute(
                select(WorkflowRunDataScope).where(WorkflowRunDataScope.id == scope_id)
            )
            return result.scalar_one_or_none()

    async def transition_workflow_control(
        self, control_id: str, *, expected_revision: int, from_statuses: set[str], status: str,
        values: Optional[Dict[str, Any]] = None,
    ) -> Optional[WorkflowControlExecution]:
        """Optimistically transition one generation; return None on conflict."""
        now = datetime.now(timezone.utc)
        update_values: Dict[str, Any] = {"status": status, "revision": expected_revision + 1, "updated_at": now}
        update_values.update(values or {})
        async with self.get_session() as session:
            result = await session.execute(
                update(WorkflowControlExecution)
                .where(
                    WorkflowControlExecution.id == control_id,
                    WorkflowControlExecution.revision == expected_revision,
                    WorkflowControlExecution.status.in_(from_statuses),
                )
                .values(**update_values)
            )
            if result.rowcount != 1:
                await session.rollback()
                return None
            await session.commit()
            refreshed = await session.execute(select(WorkflowControlExecution).where(WorkflowControlExecution.id == control_id))
            return refreshed.scalar_one()

    # ============================================================================
    # Agent Teams - CRUD Operations
    # ============================================================================

    async def create_team(
        self, team_id: str, workflow_id: str, team_lead_node_id: str, config: Optional[Dict[str, Any]] = None,
        execution_id: Optional[str] = None, root_execution_id: Optional[str] = None,
    ) -> Optional[Dict[str, Any]]:
        """Create a new agent team."""
        try:
            async with self.get_session() as session:
                team = AgentTeam(
                    id=team_id,
                    workflow_id=workflow_id,
                    team_lead_node_id=team_lead_node_id,
                    execution_id=execution_id,
                    root_execution_id=root_execution_id or execution_id,
                    config=config or {},
                    created_at=datetime.now(timezone.utc),
                )
                session.add(team)
                await session.commit()
                logger.info(f"[Teams] Created team {team_id}")
                return {"id": team.id, "workflow_id": team.workflow_id, "execution_id": team.execution_id,
                        "root_execution_id": team.root_execution_id, "status": team.status}
        except Exception as e:
            logger.error(f"Failed to create team: {e}")
            return None

    async def get_team(self, team_id: str) -> Optional[Dict[str, Any]]:
        """Get team by ID."""
        try:
            async with self.get_session() as session:
                result = await session.execute(select(AgentTeam).where(AgentTeam.id == team_id))
                team = result.scalar_one_or_none()
                if not team:
                    return None
                return {
                    "id": team.id,
                    "workflow_id": team.workflow_id,
                    "team_lead_node_id": team.team_lead_node_id,
                    "execution_id": team.execution_id,
                    "root_execution_id": team.root_execution_id,
                    "status": team.status,
                    "config": team.config,
                    "created_at": team.created_at.isoformat() if team.created_at else None,
                }
        except Exception as e:
            logger.error(f"Failed to get team: {e}")
            return None

    async def find_team(
        self, workflow_id: str, team_lead_node_id: str,
        execution_id: Optional[str] = None, active_first: bool = True,
    ) -> Optional[Dict[str, Any]]:
        """Resolve an execution team, or the latest team for a workflow lead."""
        try:
            async with self.get_session() as session:
                query = select(AgentTeam).where(
                    AgentTeam.workflow_id == workflow_id,
                    AgentTeam.team_lead_node_id == team_lead_node_id,
                )
                if execution_id:
                    query = query.where(AgentTeam.execution_id == execution_id)
                else:
                    # Execution identity, not a mutable lifecycle flag, defines
                    # the current run. A crashed/legacy team can remain marked
                    # active forever; active-first ordering then hides every
                    # newer submitted or accepted task. The newest execution is
                    # the default, and callers select older runs explicitly by
                    # execution_id.
                    query = query.order_by(AgentTeam.created_at.desc())
                result = await session.execute(query.limit(1))
                team = result.scalar_one_or_none()
                return await self.get_team(team.id) if team else None
        except Exception as e:
            logger.error(f"Failed to find team: {e}")
            return None

    async def list_team_executions(
        self, workflow_id: str, team_lead_node_id: str
    ) -> List[Dict[str, Any]]:
        """List every durable execution owned by a workflow lead, newest first."""
        try:
            async with self.get_session() as session:
                result = await session.execute(
                    select(AgentTeam)
                    .where(
                        AgentTeam.workflow_id == workflow_id,
                        AgentTeam.team_lead_node_id == team_lead_node_id,
                    )
                    .order_by(AgentTeam.created_at.desc(), AgentTeam.id.desc())
                )
                return [
                    {
                        "team_id": team.id,
                        "execution_id": team.execution_id,
                        "root_execution_id": team.root_execution_id,
                        "status": team.status,
                        "created_at": team.created_at.isoformat() if team.created_at else None,
                        "completed_at": team.completed_at.isoformat() if team.completed_at else None,
                    }
                    for team in result.scalars().all()
                ]
        except Exception as e:
            logger.error(f"Failed to list team executions: {e}")
            return []

    async def update_team_status(self, team_id: str, status: str) -> bool:
        """Update team status."""
        try:
            async with self.reserved_session() as session:
                result = await session.execute(select(AgentTeam).where(AgentTeam.id == team_id))
                team = result.scalar_one_or_none()
                if not team:
                    return False
                if status != "failed" and await self._team_failed_without_assignments(session, team_id):
                    return False
                team.status = status
                if status in ("completed", "failed", "dissolved"):
                    team.completed_at = datetime.now(timezone.utc)
                elif status == "active":
                    team.completed_at = None
                await session.commit()
                return True
        except Exception as e:
            logger.error(f"Failed to update team status: {e}")
            return False

    async def add_team_member(
        self, team_id: str, agent_node_id: str, agent_type: str, role: str = "teammate", agent_label: Optional[str] = None,
        capabilities: Optional[Dict[str, Any]] = None,
    ) -> Optional[Dict[str, Any]]:
        """Add member to team."""
        try:
            async with self.get_session() as session:
                existing = await session.execute(select(TeamMember).where(
                    TeamMember.team_id == team_id, TeamMember.agent_node_id == agent_node_id
                ))
                member = existing.scalar_one_or_none()
                if member:
                    member.agent_type, member.agent_label, member.role = agent_type, agent_label, role
                    member.capabilities = capabilities
                    await session.commit()
                    return {"id": member.id, "agent_node_id": agent_node_id, "role": role}
                member = TeamMember(
                    team_id=team_id,
                    agent_node_id=agent_node_id,
                    agent_type=agent_type,
                    agent_label=agent_label,
                    role=role,
                    capabilities=capabilities,
                    joined_at=datetime.now(timezone.utc),
                )
                session.add(member)
                await session.commit()
                return {"id": member.id, "agent_node_id": agent_node_id, "role": role}
        except Exception as e:
            logger.error(f"Failed to add team member: {e}")
            return None

    async def get_team_members(self, team_id: str) -> List[Dict[str, Any]]:
        """Get all team members."""
        try:
            async with self.get_session() as session:
                result = await session.execute(select(TeamMember).where(TeamMember.team_id == team_id))
                return [
                    {
                        "id": m.id,
                        "agent_node_id": m.agent_node_id,
                        "agent_type": m.agent_type,
                        "agent_label": m.agent_label,
                        "role": m.role,
                        "status": m.status,
                    }
                    for m in result.scalars().all()
                ]
        except Exception as e:
            logger.error(f"Failed to get team members: {e}")
            return []

    async def update_member_status(self, team_id: str, agent_node_id: str, status: str) -> bool:
        """Update member status (idle, working, offline)."""
        try:
            async with self.get_session() as session:
                result = await session.execute(
                    select(TeamMember).where(TeamMember.team_id == team_id, TeamMember.agent_node_id == agent_node_id)
                )
                member = result.scalar_one_or_none()
                if not member:
                    return False
                member.status = status
                await session.commit()
                return True
        except Exception as e:
            logger.error(f"Failed to update member status: {e}")
            return False

    async def add_team_task(
        self,
        task_id: str,
        team_id: str,
        title: str,
        created_by: str,
        description: Optional[str] = None,
        priority: int = 3,
        depends_on: Optional[List[str]] = None,
    ) -> Optional[Dict[str, Any]]:
        """Add task to team's shared list."""
        try:
            async with self.reserved_session() as session:
                existing = await session.execute(select(TeamTask).where(TeamTask.id == task_id))
                task = existing.scalar_one_or_none()
                if task:
                    if task.team_id != team_id:
                        return None
                    return {"id": task.id, "title": task.title, "status": task.status}
                if await self._team_failed_without_assignments(session, team_id):
                    return None
                task = TeamTask(
                    id=task_id,
                    team_id=team_id,
                    title=title,
                    description=description,
                    priority=priority,
                    created_by=created_by,
                    depends_on={"task_ids": depends_on} if depends_on else None,
                    status="queued",
                    created_at=datetime.now(timezone.utc),
                )
                session.add(task)
                await session.commit()
                return {"id": task.id, "title": title, "status": "queued"}
        except Exception as e:
            logger.error(f"Failed to add team task: {e}")
            return None

    @staticmethod
    def _team_task_dict(task: TeamTask, attempts: Optional[List[TeamTaskAttempt]] = None) -> Dict[str, Any]:
        allowed_actions = {
            "blocked": ["modify", "cancel"], "queued": ["modify", "cancel"],
            "running": ["cancel"], "submitted": ["accept", "retry", "reassign"],
            "failed": ["retry", "reassign"], "cancelled": ["retry", "reassign"],
            "accepted": [],
        }.get(task.status, [])
        return {
            "id": task.id, "team_id": task.team_id, "workflow_id": task.workflow_id,
            "execution_id": task.execution_id, "root_execution_id": task.root_execution_id,
            "parent_agent_id": task.parent_agent_id, "title": task.title,
            "description": task.description, "mission": task.mission,
            "context": task.context, "acceptance_criteria": task.acceptance_criteria,
            "status": task.status, "priority": task.priority, "queue_sequence": task.queue_sequence,
            "created_by": task.created_by, "assigned_to": task.assigned_to,
            "depends_on": task.depends_on.get("task_ids", []) if task.depends_on else [],
            "result": task.result, "error": task.error, "progress": task.progress,
            "revision": task.revision, "current_attempt": task.current_attempt,
            "retry_count": task.retry_count, "max_retries": task.max_retries,
            "child_workflow_id": task.child_workflow_id, "child_run_id": task.child_run_id,
            "runner_workflow_id": task.runner_workflow_id, "runner_run_id": task.runner_run_id,
            "parent_workflow_id": task.parent_workflow_id, "parent_run_id": task.parent_run_id,
            "trace_availability": (
                "available" if task.child_workflow_id or task.runner_workflow_id
                else "execution_not_registered"
            ),
            "trace_id": task.trace_id, "cancellation_requested": task.cancellation_requested,
            "cancellation_reason": task.cancellation_reason, "usage": task.usage,
            "created_at": task.created_at.isoformat() if task.created_at else None,
            "started_at": task.started_at.isoformat() if task.started_at else None,
            "completed_at": task.completed_at.isoformat() if task.completed_at else None,
            "attempts": [
                {"id": a.id, "attempt_number": a.attempt_number, "assignee_node_id": a.assignee_node_id,
                 "status": a.status, "child_workflow_id": a.child_workflow_id,
                 "child_run_id": a.child_run_id, "result": a.result, "error": a.error,
                 "runner_workflow_id": a.runner_workflow_id, "runner_run_id": a.runner_run_id,
                 "parent_workflow_id": a.parent_workflow_id, "parent_run_id": a.parent_run_id,
                 "usage": a.usage, "created_at": a.created_at.isoformat() if a.created_at else None,
                 "started_at": a.started_at.isoformat() if a.started_at else None,
                 "completed_at": a.completed_at.isoformat() if a.completed_at else None}
                for a in (attempts or [])
            ],
            "allowed_actions": allowed_actions,
        }

    async def create_durable_team_task(self, **values: Any) -> Optional[Dict[str, Any]]:
        """Persist one scoped queue item, idempotently by task id."""
        try:
            async with self.reserved_session() as session:
                existing = (await session.execute(select(TeamTask).where(TeamTask.id == values["id"]))).scalar_one_or_none()
                if existing:
                    return self._team_task_dict(existing) if existing.team_id == values["team_id"] else None
                if await self._team_failed_without_assignments(session, values["team_id"]):
                    return None
                maximum = (await session.execute(
                    select(func.max(TeamTask.queue_sequence)).where(TeamTask.team_id == values["team_id"])
                )).scalar_one_or_none() or 0
                values.setdefault("queue_sequence", maximum + 1)
                values.setdefault("status", "blocked" if values.get("depends_on") else "queued")
                if isinstance(values.get("depends_on"), list):
                    values["depends_on"] = {"task_ids": values["depends_on"]}
                task = TeamTask(**values)
                session.add(task)
                await session.commit()
                await session.refresh(task)
                return self._team_task_dict(task)
        except Exception as e:
            logger.error(f"Failed to create durable team task: {e}")
            return None

    @staticmethod
    async def _team_failed_without_assignments(session: AsyncSession, team_id: str) -> bool:
        jobs = (await session.execute(select(EmployeeJob).where(EmployeeJob.team_id == team_id))).scalars()
        return any(job.state == "failed" and (job.delivery.get("failure") or {}).get("code") == "lead_no_assignments" for job in jobs)

    async def get_durable_team_task(self, team_id: str, task_id: str) -> Optional[Dict[str, Any]]:
        async with self.get_session() as session:
            task = (await session.execute(select(TeamTask).where(TeamTask.id == task_id, TeamTask.team_id == team_id))).scalar_one_or_none()
            if not task:
                return None
            attempts = (await session.execute(
                select(TeamTaskAttempt).where(TeamTaskAttempt.task_id == task_id).order_by(TeamTaskAttempt.attempt_number)
            )).scalars().all()
            return self._team_task_dict(task, list(attempts))

    async def transition_team_task(
        self, team_id: str, task_id: str, expected_revision: int,
        allowed_statuses: List[str], values: Dict[str, Any], *, create_attempt: bool = False,
    ) -> Optional[Dict[str, Any]]:
        """Revision-and-state guarded task transition (compare-and-swap)."""
        async with self.get_session() as session:
            values = dict(values)
            values["revision"] = expected_revision + 1
            changed = await session.execute(
                update(TeamTask).where(
                    TeamTask.id == task_id, TeamTask.team_id == team_id,
                    TeamTask.revision == expected_revision, TeamTask.status.in_(allowed_statuses),
                ).values(**values)
            )
            if changed.rowcount != 1:
                await session.rollback()
                return None
            task = (await session.execute(select(TeamTask).where(TeamTask.id == task_id))).scalar_one()
            if create_attempt:
                attempt_number = task.current_attempt
                session.add(TeamTaskAttempt(
                    id=f"{task.id}:attempt:{attempt_number}", task_id=task.id, team_id=team_id,
                    attempt_number=attempt_number, assignee_node_id=task.assigned_to,
                    status=task.status, child_workflow_id=task.child_workflow_id,
                    child_run_id=task.child_run_id, created_at=datetime.now(timezone.utc),
                ))
            await session.commit()
            await session.refresh(task)
            return self._team_task_dict(task)

    async def register_team_task_execution(
        self, *, team_id: str, task_id: str, attempt_number: int,
        runner_workflow_id: Optional[str] = None, runner_run_id: Optional[str] = None,
        child_workflow_id: Optional[str] = None, child_run_id: Optional[str] = None,
        parent_workflow_id: Optional[str] = None, parent_run_id: Optional[str] = None,
    ) -> Optional[Dict[str, Any]]:
        """Idempotently attach actual Temporal identities to one task attempt."""
        async with self.get_session() as session:
            task = (await session.execute(select(TeamTask).where(
                TeamTask.id == task_id, TeamTask.team_id == team_id,
            ))).scalar_one_or_none()
            if task is None or task.current_attempt != attempt_number:
                return None
            values = {
                key: value for key, value in {
                    "runner_workflow_id": runner_workflow_id, "runner_run_id": runner_run_id,
                    "child_workflow_id": child_workflow_id, "child_run_id": child_run_id,
                    "parent_workflow_id": parent_workflow_id, "parent_run_id": parent_run_id,
                }.items() if value
            }
            for key, value in values.items():
                setattr(task, key, value)
            attempt_id = f"{task_id}:attempt:{attempt_number}"
            attempt = (await session.execute(select(TeamTaskAttempt).where(
                TeamTaskAttempt.id == attempt_id,
            ))).scalar_one_or_none()
            if attempt is None:
                attempt = TeamTaskAttempt(
                    id=attempt_id, task_id=task_id, team_id=team_id,
                    attempt_number=attempt_number, assignee_node_id=task.assigned_to,
                    status=task.status, started_at=task.started_at or datetime.now(timezone.utc),
                )
                session.add(attempt)
            for key, value in values.items():
                setattr(attempt, key, value)
            await session.commit()
            await session.refresh(task)
            return self._team_task_dict(task, [attempt])

    async def get_team_tasks(self, team_id: str, status: Optional[str] = None) -> List[Dict[str, Any]]:
        """Get team tasks, optionally filtered by status."""
        try:
            async with self.get_session() as session:
                query = select(TeamTask).where(TeamTask.team_id == team_id)
                if status:
                    query = query.where(TeamTask.status == status)
                query = query.order_by(TeamTask.queue_sequence.asc(), TeamTask.created_at.asc(), TeamTask.id.asc())
                result = await session.execute(query)
                tasks = [self._team_task_dict(t) for t in result.scalars().all()]
                queue_position = 0
                for task in tasks:
                    if task["status"] in {"blocked", "queued", "pending"}:
                        queue_position += 1
                        task["queue_position"] = queue_position
                    else:
                        task["queue_position"] = None
                return tasks
        except Exception as e:
            logger.error(f"Failed to get team tasks: {e}")
            return []

    async def claim_task(self, task_id: str, agent_node_id: str) -> Optional[Dict[str, Any]]:
        """Atomically claim a pending task; same-agent retries are safe."""
        try:
            async with self.get_session() as session:
                claimed = await session.execute(
                    update(TeamTask)
                    .where(
                        TeamTask.id == task_id,
                        TeamTask.status.in_(["queued", "pending"]),
                        or_(TeamTask.assigned_to.is_(None), TeamTask.assigned_to == agent_node_id),
                    )
                    .values(
                        assigned_to=agent_node_id,
                        status="running",
                        started_at=datetime.now(timezone.utc),
                    )
                )
                # The guarded UPDATE is the compare-and-swap. It remains
                # correct on databases where a SELECT + Python mutation would
                # race because there is no SQLite BEGIN IMMEDIATE reservation.
                selected = await session.execute(
                    select(TeamTask).where(TeamTask.id == task_id)
                )
                task = selected.scalar_one_or_none()
                if task is None:
                    await session.rollback()
                    return None

                won = bool(claimed.rowcount == 1)
                same_agent_retry = (
                    task.status == "running"
                    and task.assigned_to == agent_node_id
                )
                if not won and not same_agent_retry:
                    await session.rollback()
                    return None

                await session.commit()
                return {
                    "id": task.id,
                    "title": task.title,
                    "assigned_to": agent_node_id,
                }
        except Exception as e:
            logger.error(f"Failed to claim task: {e}")
            return None

    async def complete_task(self, task_id: str, result_data: Optional[Dict[str, Any]] = None) -> bool:
        """Atomically submit a running task for lead review."""
        try:
            usage = (
                result_data.get("usage")
                if isinstance(result_data, dict) and isinstance(result_data.get("usage"), dict)
                else None
            )
            async def _complete(session: AsyncSession) -> Dict[str, Any]:
                completed = await session.execute(
                    update(TeamTask)
                    .where(
                        TeamTask.id == task_id,
                        TeamTask.status == "running",
                    )
                    .values(
                        status="submitted",
                        result=result_data,
                        usage=usage,
                        progress=100,
                        completed_at=datetime.now(timezone.utc),
                    )
                )
                if completed.rowcount == 1:
                    task = (await session.execute(select(TeamTask).where(TeamTask.id == task_id))).scalar_one()
                    await session.execute(update(TeamTaskAttempt).where(
                        TeamTaskAttempt.id == f"{task_id}:attempt:{task.current_attempt}"
                    ).values(status="submitted", result=result_data, usage=usage,
                             completed_at=datetime.now(timezone.utc)))
                    return {"success": True}

                selected = await session.execute(
                    select(TeamTask).where(TeamTask.id == task_id)
                )
                task = selected.scalar_one_or_none()
                return {
                    "success": bool(
                        task is not None and task.status == "submitted"
                    )
                }

            result, _applied = await self.run_runtime_mutation(
                resource_type="team_task",
                resource_id=task_id,
                operation="complete",
                mutate=_complete,
            )
            return bool(result.get("success"))
        except Exception as e:
            logger.error(f"Failed to complete task: {e}")
            return False

    async def fail_task(self, task_id: str, error: str) -> bool:
        """Atomically record one failed attempt and release or fail it."""
        try:
            async def _fail(session: AsyncSession) -> Dict[str, Any]:
                next_retry = TeamTask.retry_count + 1
                retryable = next_retry < TeamTask.max_retries
                failed = await session.execute(
                    update(TeamTask)
                    .where(
                        TeamTask.id == task_id,
                        TeamTask.status == "running",
                    )
                    .values(
                        error=error,
                        retry_count=next_retry,
                        status=case(
                            (retryable, "queued"),
                            else_="failed",
                        ),
                        assigned_to=case(
                            (retryable, None),
                            else_=TeamTask.assigned_to,
                        ),
                        completed_at=case(
                            (retryable, TeamTask.completed_at),
                            else_=datetime.now(timezone.utc),
                        ),
                    )
                )
                if failed.rowcount == 1:
                    task = (await session.execute(select(TeamTask).where(TeamTask.id == task_id))).scalar_one()
                    await session.execute(update(TeamTaskAttempt).where(
                        TeamTaskAttempt.id == f"{task_id}:attempt:{task.current_attempt}"
                    ).values(status="failed", error=error, completed_at=datetime.now(timezone.utc)))
                    return {"success": True}

                selected = await session.execute(
                    select(TeamTask).where(TeamTask.id == task_id)
                )
                task = selected.scalar_one_or_none()
                # A transport retry after this exact failure observes the
                # released/terminal state and must not increment twice.
                return {
                    "success": bool(
                        task is not None
                        and task.status in {"queued", "failed"}
                        and task.error == error
                    )
                }

            result, _applied = await self.run_runtime_mutation(
                resource_type="team_task",
                resource_id=task_id,
                operation="fail",
                mutate=_fail,
            )
            return bool(result.get("success"))
        except Exception as e:
            logger.error(f"Failed to fail task: {e}")
            return False

    async def cancel_team_task(
        self,
        team_id: str,
        task_id: str,
        reason: str,
    ) -> Optional[Dict[str, Any]]:
        """Terminally cancel active delegated work without applying retry policy.

        The state guard makes retries idempotent and prevents cancellation
        from overwriting a result that won the race to a terminal state.
        Active attempts receive the same terminal reason as the task row;
        queued work gets an attempt record as well so every cancellation has
        a durable terminal audit entry.
        """
        active_statuses = ["blocked", "queued", "pending", "running"]
        terminal_statuses = {
            "submitted",
            "accepted",
            "failed",
            "cancelled",
            "skipped",
            "completed",
        }
        cancellation_reason = reason or "Delegated agent cancelled"
        now = datetime.now(timezone.utc)

        async def _cancel(session: AsyncSession) -> Dict[str, Any]:
            changed = await session.execute(
                update(TeamTask)
                .where(
                    TeamTask.id == task_id,
                    TeamTask.team_id == team_id,
                    TeamTask.status.in_(active_statuses),
                )
                .values(
                    status="cancelled",
                    cancellation_requested=True,
                    cancellation_reason=cancellation_reason,
                    completed_at=now,
                    revision=TeamTask.revision + 1,
                )
            )
            task = (
                await session.execute(
                    select(TeamTask).where(
                        TeamTask.id == task_id,
                        TeamTask.team_id == team_id,
                    )
                )
            ).scalar_one_or_none()
            if task is None:
                return {"found": False}

            cancellation_applied = changed.rowcount == 1
            if cancellation_applied:
                attempt_id = f"{task_id}:attempt:{task.current_attempt}"
                attempt = (
                    await session.execute(
                        select(TeamTaskAttempt).where(
                            TeamTaskAttempt.id == attempt_id
                        )
                    )
                ).scalar_one_or_none()
                if attempt is None:
                    session.add(
                        TeamTaskAttempt(
                            id=attempt_id,
                            task_id=task_id,
                            team_id=team_id,
                            attempt_number=task.current_attempt,
                            assignee_node_id=task.assigned_to,
                            status="cancelled",
                            child_workflow_id=task.child_workflow_id,
                            child_run_id=task.child_run_id,
                            runner_workflow_id=task.runner_workflow_id,
                            runner_run_id=task.runner_run_id,
                            parent_workflow_id=task.parent_workflow_id,
                            parent_run_id=task.parent_run_id,
                            error=cancellation_reason,
                            created_at=task.created_at or now,
                            started_at=task.started_at,
                            completed_at=now,
                        )
                    )
                else:
                    attempt.status = "cancelled"
                    attempt.error = cancellation_reason
                    attempt.completed_at = now

            if not cancellation_applied and task.status not in terminal_statuses:
                return {
                    "found": True,
                    "status": task.status,
                    "cancellation_applied": False,
                    "valid_state": False,
                }

            snapshot = self._team_task_dict(task)
            snapshot["found"] = True
            snapshot["cancellation_applied"] = cancellation_applied
            snapshot["valid_state"] = True
            return snapshot

        try:
            result, _ = await self.run_runtime_mutation(
                resource_type="team_task",
                resource_id=task_id,
                operation="cancel",
                mutate=_cancel,
            )
            if not result.get("found") or not result.get("valid_state", True):
                return None
            return result
        except Exception as e:
            logger.error(
                "Failed to cancel team task",
                team_id=team_id,
                task_id=task_id,
                error=str(e),
            )
            return None

    async def get_claimable_tasks(self, team_id: str) -> List[Dict[str, Any]]:
        """Get pending tasks with resolved dependencies."""
        try:
            async with self.get_session() as session:
                result = await session.execute(select(TeamTask).where(TeamTask.team_id == team_id))
                tasks = result.scalars().all()
                completed_ids = {t.id for t in tasks if t.status == "accepted"}
                claimable = []
                for t in tasks:
                    if t.status != "queued":
                        continue
                    deps = t.depends_on.get("task_ids", []) if t.depends_on else []
                    if all(d in completed_ids for d in deps):
                        claimable.append({"id": t.id, "title": t.title, "priority": t.priority})
                return claimable
        except Exception as e:
            logger.error(f"Failed to get claimable tasks: {e}")
            return []

    async def add_agent_message(
        self, team_id: str, from_agent: str, content: str, message_type: str = "direct", to_agent: Optional[str] = None,
        event_id: Optional[str] = None, extra_data: Optional[Dict[str, Any]] = None,
    ) -> Optional[Dict[str, Any]]:
        """Add message between agents."""
        try:
            async with self.get_session() as session:
                if event_id:
                    existing = await session.execute(select(AgentMessage).where(AgentMessage.event_id == event_id))
                    msg = existing.scalar_one_or_none()
                    if msg:
                        return {"id": msg.id, "event_id": msg.event_id, "from_agent": msg.from_agent, "to_agent": msg.to_agent}
                msg = AgentMessage(
                    event_id=event_id,
                    team_id=team_id,
                    from_agent=from_agent,
                    to_agent=to_agent,
                    message_type=message_type,
                    content=content,
                    extra_data=extra_data,
                    created_at=datetime.now(timezone.utc),
                )
                session.add(msg)
                await session.commit()
                return {"id": msg.id, "event_id": msg.event_id, "from_agent": from_agent, "to_agent": to_agent}
        except Exception as e:
            logger.error(f"Failed to add agent message: {e}")
            return None

    async def get_agent_messages(
        self, team_id: str, agent_node_id: Optional[str] = None, unread_only: bool = False
    ) -> List[Dict[str, Any]]:
        """Get messages for team or specific agent."""
        try:
            async with self.get_session() as session:
                from sqlalchemy import or_

                query = select(AgentMessage).where(AgentMessage.team_id == team_id)
                if agent_node_id:
                    query = query.where(or_(AgentMessage.to_agent == agent_node_id, AgentMessage.to_agent.is_(None)))
                if unread_only:
                    query = query.where(not AgentMessage.read)
                query = query.order_by(AgentMessage.created_at.asc()).limit(100)
                result = await session.execute(query)
                return [
                    {
                        "id": m.id,
                        "event_id": m.event_id,
                        "from_agent": m.from_agent,
                        "to_agent": m.to_agent,
                        "message_type": m.message_type,
                        "content": m.content,
                        "read": m.read,
                        "created_at": m.created_at.isoformat() if m.created_at else None,
                        "extra_data": m.extra_data,
                    }
                    for m in result.scalars().all()
                ]
        except Exception as e:
            logger.error(f"Failed to get agent messages: {e}")
            return []

    async def mark_messages_read(self, team_id: str, agent_node_id: str) -> int:
        """Mark all messages as read for an agent."""
        try:
            async with self.get_session() as session:
                from sqlalchemy import or_

                result = await session.execute(
                    select(AgentMessage).where(
                        AgentMessage.team_id == team_id,
                        not AgentMessage.read,
                        or_(AgentMessage.to_agent == agent_node_id, AgentMessage.to_agent.is_(None)),
                    )
                )
                messages = result.scalars().all()
                for m in messages:
                    m.read = True
                await session.commit()
                return len(messages)
        except Exception as e:
            logger.error(f"Failed to mark messages read: {e}")
            return 0

    async def get_team_stats(self, team_id: str) -> Dict[str, Any]:
        """Get team statistics."""
        try:
            async with self.get_session() as session:
                # Get counts in simple queries
                team_result = await session.execute(select(AgentTeam).where(AgentTeam.id == team_id))
                team = team_result.scalar_one_or_none()
                if not team:
                    return {"error": "Team not found"}

                members_result = await session.execute(select(TeamMember).where(TeamMember.team_id == team_id))
                members = members_result.scalars().all()

                tasks_result = await session.execute(select(TeamTask).where(TeamTask.team_id == team_id))
                tasks = tasks_result.scalars().all()

                counts = {
                    state: sum(t.status == state for t in tasks)
                    for state in ("blocked", "queued", "running", "submitted", "accepted", "failed", "cancelled")
                }
                # Compatibility with rows created by older runtimes during a rolling upgrade.
                pending_count = counts["blocked"] + counts["queued"] + sum(t.status == "pending" for t in tasks)
                active_count = counts["running"] + sum(t.status == "in_progress" for t in tasks)
                # A worker result is only submitted for review. "Done" means
                # the lead explicitly accepted it (plus historical completed
                # rows retained for compatibility).
                completed_count = counts["accepted"] + sum(t.status == "completed" for t in tasks)
                failed_count = counts["failed"]

                return {
                    "team_id": team_id,
                    "status": team.status,
                    "workflow_id": team.workflow_id,
                    "team_lead_node_id": team.team_lead_node_id,
                    "execution_id": team.execution_id,
                    "root_execution_id": team.root_execution_id,
                    "member_count": len(members),
                    "task_total": len(tasks),
                    "task_pending": pending_count,
                    "task_in_progress": active_count,
                    "task_completed": completed_count,
                    "task_failed": failed_count,
                    # Stable response aliases consumed by TeamMonitor and
                    # existing WS clients.
                    "task_count": len(tasks),
                    "pending_count": pending_count,
                    "queued_count": pending_count,
                    "active_count": active_count,
                    "completed_count": completed_count,
                    "failed_count": failed_count,
                    "blocked_count": counts["blocked"],
                    "submitted_count": counts["submitted"],
                    "accepted_count": counts["accepted"],
                    "cancelled_count": counts["cancelled"],
                    "status_counts": counts,
                    "members": [{"id": m.agent_node_id, "agent_node_id": m.agent_node_id,
                                 "type": m.agent_type, "agent_type": m.agent_type, "label": m.agent_label,
                                 "role": m.role, "status": m.status, "capabilities": m.capabilities} for m in members],
                    "queued_tasks": [
                        {"id": t.id, "title": t.title, "assigned_to": t.assigned_to}
                        for t in tasks if t.status in {"pending", "blocked", "queued"}
                    ],
                    "active_tasks": [
                        {"id": t.id, "title": t.title, "assigned_to": t.assigned_to, "progress": t.progress}
                        for t in tasks
                        if t.status in {"in_progress", "running"}
                    ],
                    "tasks": [self._team_task_dict(t) for t in sorted(tasks, key=lambda item: (item.queue_sequence, item.created_at))],
                }
        except Exception as e:
            logger.error(f"Failed to get team stats: {e}")
            return {"error": str(e)}

    async def acquire_subagent_permit(self, root_execution_id: str, permit_id: str, limit: int = 3) -> bool:
        """Atomically acquire a root-global permit; retries by the owner succeed.

        Active leases older than the maximum delegated-workflow duration are
        opportunistically reconciled in the same write transaction. This is a
        last-resort capacity repair for workers that died before compensation.
        """
        if limit < 1:
            return False
        now = datetime.now(timezone.utc)
        stale_before = now - timedelta(hours=3)

        try:
            async def _acquire(session: AsyncSession) -> Dict[str, Any]:
                stale = await session.execute(
                    update(SubagentConcurrencyPermit)
                    .where(
                        SubagentConcurrencyPermit.root_execution_id
                        == root_execution_id,
                        SubagentConcurrencyPermit.status == "active",
                        SubagentConcurrencyPermit.acquired_at < stale_before,
                    )
                    .values(status="released", released_at=now)
                )
                stale_count = max(int(stale.rowcount or 0), 0)
                if stale_count:
                    await session.execute(
                        update(SubagentConcurrencyCounter)
                        .where(
                            SubagentConcurrencyCounter.root_execution_id
                            == root_execution_id
                        )
                        .values(
                            active_count=case(
                                (
                                    SubagentConcurrencyCounter.active_count
                                    >= stale_count,
                                    SubagentConcurrencyCounter.active_count
                                    - stale_count,
                                ),
                                else_=0,
                            ),
                            updated_at=now,
                        )
                    )

                existing = await session.execute(
                    select(SubagentConcurrencyPermit).where(
                        SubagentConcurrencyPermit.permit_id == permit_id
                    )
                )
                permit = existing.scalar_one_or_none()
                if permit:
                    return {
                        "acquired": (
                            permit.root_execution_id == root_execution_id
                            and permit.status == "active"
                        )
                    }

                if self.engine.dialect.name == "postgresql":
                    from sqlalchemy.dialects.postgresql import insert
                else:
                    from sqlalchemy.dialects.sqlite import insert
                await session.execute(insert(SubagentConcurrencyCounter).values(
                    root_execution_id=root_execution_id, active_count=0, updated_at=now
                ).on_conflict_do_nothing(index_elements=["root_execution_id"]))
                claimed = await session.execute(
                    update(SubagentConcurrencyCounter)
                    .where(
                        SubagentConcurrencyCounter.root_execution_id
                        == root_execution_id,
                        SubagentConcurrencyCounter.active_count < limit,
                    )
                    .values(
                        active_count=SubagentConcurrencyCounter.active_count + 1,
                        updated_at=now,
                    )
                )
                if claimed.rowcount != 1:
                    return {"acquired": False}
                session.add(
                    SubagentConcurrencyPermit(
                        permit_id=permit_id,
                        root_execution_id=root_execution_id,
                        status="active",
                        acquired_at=now,
                    )
                )
                return {"acquired": True}

            result, _ = await self.run_runtime_mutation(
                resource_type="subagent_concurrency",
                resource_id=root_execution_id,
                operation="acquire",
                mutate=_acquire,
            )
            return bool(result.get("acquired"))
        except IntegrityError:
            # A concurrent retry inserted the same permit. Resolve ownership
            # from durable state rather than incrementing the counter again.
            async with self.get_session() as session:
                result = await session.execute(
                    select(SubagentConcurrencyPermit).where(
                        SubagentConcurrencyPermit.permit_id == permit_id
                    )
                )
                permit = result.scalar_one_or_none()
                return bool(
                    permit
                    and permit.root_execution_id == root_execution_id
                    and permit.status == "active"
                )
        except Exception as e:
            logger.error(
                "Failed to acquire subagent permit",
                root_execution_id=root_execution_id,
                permit_id=permit_id,
                error=str(e),
            )
            return False

    async def release_subagent_permit(self, root_execution_id: str, permit_id: str) -> bool:
        """Idempotently release a permit and decrement its root counter once.

        A lease that was never created is an expected no-op (not an ownership
        error). A lease owned by another root remains a hard failure.
        """
        try:
            async with self.get_session() as session:
                released = await session.execute(
                    update(SubagentConcurrencyPermit)
                    .where(
                        SubagentConcurrencyPermit.permit_id == permit_id,
                        SubagentConcurrencyPermit.root_execution_id
                        == root_execution_id,
                        SubagentConcurrencyPermit.status == "active",
                    )
                    .values(
                        status="released",
                        released_at=datetime.now(timezone.utc),
                    )
                )
                if released.rowcount == 1:
                    await session.execute(
                        update(SubagentConcurrencyCounter)
                        .where(
                            SubagentConcurrencyCounter.root_execution_id
                            == root_execution_id,
                            SubagentConcurrencyCounter.active_count > 0,
                        )
                        .values(
                            active_count=SubagentConcurrencyCounter.active_count
                            - 1,
                            updated_at=datetime.now(timezone.utc),
                        )
                    )
                    await session.commit()
                    return True
                existing = await session.execute(
                    select(SubagentConcurrencyPermit).where(
                        SubagentConcurrencyPermit.permit_id == permit_id
                    )
                )
                permit = existing.scalar_one_or_none()
                if permit is None:
                    await session.rollback()
                    return True
                already_released = bool(
                    permit.root_execution_id == root_execution_id
                    and permit.status == "released"
                )
                await session.rollback()
                return already_released
        except Exception as e:
            logger.error(
                "Failed to release subagent permit",
                root_execution_id=root_execution_id,
                permit_id=permit_id,
                error=str(e),
            )
            return False

    # ============================================================================
    # Google Connections - Customer Mode OAuth Storage
    # ============================================================================

    async def save_google_connection(
        self,
        customer_id: str,
        email: str,
        access_token: str,
        refresh_token: str,
        scopes: str,
        name: Optional[str] = None,
    ) -> bool:
        """Save or update a Google Workspace connection for a customer."""
        try:
            async with self.get_session() as session:
                # Check if connection exists
                result = await session.execute(select(GoogleConnection).where(GoogleConnection.customer_id == customer_id))
                existing = result.scalar_one_or_none()

                now = datetime.now(timezone.utc)
                if existing:
                    # Update existing connection
                    existing.email = email
                    existing.name = name
                    existing.access_token = access_token
                    existing.refresh_token = refresh_token
                    existing.scopes = scopes
                    existing.is_active = True
                    existing.updated_at = now
                else:
                    # Create new connection
                    connection = GoogleConnection(
                        customer_id=customer_id,
                        email=email,
                        name=name,
                        access_token=access_token,
                        refresh_token=refresh_token,
                        scopes=scopes,
                        is_active=True,
                        connected_at=now,
                        updated_at=now,
                    )
                    session.add(connection)

                await session.commit()
                logger.info(f"Saved Google connection for customer {customer_id}")
                return True
        except Exception as e:
            logger.error(f"Failed to save Google connection: {e}")
            return False

    async def get_google_connection(self, customer_id: str) -> Optional[GoogleConnection]:
        """Get Google Workspace connection for a customer."""
        try:
            async with self.get_session() as session:
                result = await session.execute(
                    select(GoogleConnection).where(GoogleConnection.customer_id == customer_id, GoogleConnection.is_active)
                )
                return result.scalar_one_or_none()
        except Exception as e:
            logger.error(f"Failed to get Google connection: {e}")
            return None

    async def delete_google_connection(self, customer_id: str) -> bool:
        """Delete Google Workspace connection for a customer."""
        try:
            async with self.get_session() as session:
                result = await session.execute(select(GoogleConnection).where(GoogleConnection.customer_id == customer_id))
                connection = result.scalar_one_or_none()
                if connection:
                    await session.delete(connection)
                    await session.commit()
                    logger.info(f"Deleted Google connection for customer {customer_id}")
                return True
        except Exception as e:
            logger.error(f"Failed to delete Google connection: {e}")
            return False

    async def list_google_connections(self, limit: int = 100) -> List[Dict[str, Any]]:
        """List all Google Workspace connections."""
        try:
            async with self.get_session() as session:
                result = await session.execute(
                    select(GoogleConnection).where(GoogleConnection.is_active).order_by(GoogleConnection.connected_at.desc()).limit(limit)
                )
                connections = result.scalars().all()
                return [
                    {
                        "customer_id": c.customer_id,
                        "email": c.email,
                        "name": c.name,
                        "connected_at": c.connected_at.isoformat() if c.connected_at else None,
                        "last_used_at": c.last_used_at.isoformat() if c.last_used_at else None,
                    }
                    for c in connections
                ]
        except Exception as e:
            logger.error(f"Failed to list Google connections: {e}")
            return []

    async def update_google_connection_tokens(
        self,
        customer_id: str,
        access_token: str,
        refresh_token: Optional[str] = None,
    ) -> bool:
        """Update tokens for a Google Workspace connection (after refresh)."""
        try:
            async with self.get_session() as session:
                result = await session.execute(select(GoogleConnection).where(GoogleConnection.customer_id == customer_id))
                connection = result.scalar_one_or_none()
                if connection:
                    connection.access_token = access_token
                    if refresh_token:
                        connection.refresh_token = refresh_token
                    connection.updated_at = datetime.now(timezone.utc)
                    await session.commit()
                    return True
                return False
        except Exception as e:
            logger.error(f"Failed to update Google tokens: {e}")
            return False

    async def update_google_last_used(self, customer_id: str) -> bool:
        """Update last_used_at timestamp for a Google Workspace connection."""
        try:
            async with self.get_session() as session:
                result = await session.execute(select(GoogleConnection).where(GoogleConnection.customer_id == customer_id))
                connection = result.scalar_one_or_none()
                if connection:
                    connection.last_used_at = datetime.now(timezone.utc)
                    await session.commit()
                    return True
                return False
        except Exception as e:
            logger.error(f"Failed to update Google last used: {e}")
            return False

    # ============================================================================
    # Proxy Provider CRUD
    # ============================================================================

    async def save_proxy_provider(self, data: Dict[str, Any]) -> bool:
        """Save or update a proxy provider configuration (upsert by name)."""
        try:
            name = data.get("name")
            if not name:
                logger.error("Cannot save proxy provider without name")
                return False

            async with self.get_session() as session:
                stmt = select(ProxyProviderConfig).where(ProxyProviderConfig.name == name)
                result = await session.execute(stmt)
                existing = result.scalar_one_or_none()

                if existing:
                    for field in ["enabled", "priority", "cost_per_gb", "gateway_host", "gateway_port", "url_template"]:
                        if field in data:
                            setattr(existing, field, data[field])
                    existing.updated_at = datetime.now(timezone.utc)
                else:
                    existing = ProxyProviderConfig(
                        name=name,
                        enabled=data.get("enabled", True),
                        priority=data.get("priority", 50),
                        cost_per_gb=data.get("cost_per_gb", 0.0),
                        gateway_host=data.get("gateway_host", ""),
                        gateway_port=data.get("gateway_port", 0),
                        url_template=data.get("url_template", "{}"),
                    )
                    session.add(existing)

                await session.commit()
                logger.info(f"[DB] Proxy provider saved: {name}")
                return True

        except Exception as e:
            logger.error(f"Failed to save proxy provider: {e}")
            return False

    async def get_proxy_provider(self, name: str) -> Optional[Dict[str, Any]]:
        """Get a single proxy provider by name."""
        try:
            async with self.get_session() as session:
                stmt = select(ProxyProviderConfig).where(ProxyProviderConfig.name == name)
                result = await session.execute(stmt)
                p = result.scalar_one_or_none()

                if not p:
                    return None

                return {
                    "id": p.id,
                    "name": p.name,
                    "enabled": p.enabled,
                    "priority": p.priority,
                    "cost_per_gb": p.cost_per_gb,
                    "gateway_host": p.gateway_host,
                    "gateway_port": p.gateway_port,
                    "url_template": p.url_template,
                    "created_at": p.created_at.isoformat() if p.created_at else None,
                    "updated_at": p.updated_at.isoformat() if p.updated_at else None,
                }

        except Exception as e:
            logger.error(f"Failed to get proxy provider: {e}")
            return None

    async def get_proxy_providers(self) -> List[Dict[str, Any]]:
        """Get all proxy provider configurations."""
        try:
            async with self.get_session() as session:
                stmt = select(ProxyProviderConfig).order_by(ProxyProviderConfig.priority)
                result = await session.execute(stmt)
                providers = result.scalars().all()

                return [
                    {
                        "id": p.id,
                        "name": p.name,
                        "enabled": p.enabled,
                        "priority": p.priority,
                        "cost_per_gb": p.cost_per_gb,
                        "gateway_host": p.gateway_host,
                        "gateway_port": p.gateway_port,
                        "url_template": p.url_template,
                        "created_at": p.created_at.isoformat() if p.created_at else None,
                        "updated_at": p.updated_at.isoformat() if p.updated_at else None,
                    }
                    for p in providers
                ]

        except Exception as e:
            logger.error(f"Failed to get proxy providers: {e}")
            return []

    async def delete_proxy_provider(self, name: str) -> bool:
        """Delete a proxy provider by name."""
        try:
            async with self.get_session() as session:
                stmt = select(ProxyProviderConfig).where(ProxyProviderConfig.name == name)
                result = await session.execute(stmt)
                provider = result.scalar_one_or_none()

                if provider:
                    await session.delete(provider)
                    await session.commit()
                    logger.info(f"[DB] Proxy provider deleted: {name}")

                return True

        except Exception as e:
            logger.error(f"Failed to delete proxy provider: {e}")
            return False

    # ============================================================================
    # Proxy Routing Rules CRUD
    # ============================================================================

    async def save_proxy_routing_rule(self, data: Dict[str, Any]) -> bool:
        """Save a new proxy routing rule."""
        try:
            async with self.get_session() as session:
                rule = ProxyRoutingRule(
                    domain_pattern=data.get("domain_pattern", ""),
                    preferred_providers=data.get("preferred_providers", "[]"),
                    required_country=data.get("required_country", ""),
                    session_type=data.get("session_type", "rotating"),
                )
                session.add(rule)
                await session.commit()
                logger.info(f"[DB] Proxy routing rule saved: {data.get('domain_pattern')}")
                return True

        except Exception as e:
            logger.error(f"Failed to save proxy routing rule: {e}")
            return False

    async def get_proxy_routing_rules(self) -> List[Dict[str, Any]]:
        """Get all proxy routing rules."""
        try:
            async with self.get_session() as session:
                stmt = select(ProxyRoutingRule).order_by(ProxyRoutingRule.id)
                result = await session.execute(stmt)
                rules = result.scalars().all()

                return [
                    {
                        "id": r.id,
                        "domain_pattern": r.domain_pattern,
                        "preferred_providers": r.preferred_providers,
                        "required_country": r.required_country,
                        "session_type": r.session_type,
                        "created_at": r.created_at.isoformat() if r.created_at else None,
                    }
                    for r in rules
                ]

        except Exception as e:
            logger.error(f"Failed to get proxy routing rules: {e}")
            return []

    async def delete_proxy_routing_rule(self, rule_id: int) -> bool:
        """Delete a proxy routing rule by ID."""
        try:
            async with self.get_session() as session:
                stmt = select(ProxyRoutingRule).where(ProxyRoutingRule.id == rule_id)
                result = await session.execute(stmt)
                rule = result.scalar_one_or_none()

                if rule:
                    await session.delete(rule)
                    await session.commit()
                    logger.info(f"[DB] Proxy routing rule deleted: {rule_id}")

                return True

        except Exception as e:
            logger.error(f"Failed to delete proxy routing rule: {e}")
            return False