Self-improving AI Employees for everyone
Python ai-agentsai-employeeai-employeesassistantautonomous-agentsco-employeescompanygrok-botinstinctloop-agentsmuseown-your-datapersonalself-hosted
"""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