Skip to content

armgit / dify

Public Imported from GitHub
0

Build Agentic workflows, RAG pipelines, with rich AI model and tool support on one collaborative workspace. Deploy on cloud, VPC, or self-hosted, so teams move from prototype to production without rebuilding the stack.

72.9 KB
Raw Blame
"""
SQLAlchemy API WorkflowRun Repository Implementation

This module provides the SQLAlchemy-based implementation of the APIWorkflowRunRepository
protocol. It handles service-layer WorkflowRun database operations using SQLAlchemy 2.0
style queries with proper session management and multi-tenant data isolation.

Key Features:
- SQLAlchemy 2.0 style queries for modern database operations
- Cursor-based pagination for efficient large dataset handling
- Bulk operations with batch processing for performance
- Multi-tenant data isolation and security
- Proper session management with dependency injection

Implementation Notes:
- Uses sessionmaker for consistent session management
- Implements cursor-based pagination using created_at timestamps
- Provides efficient bulk deletion with batch processing
- Maintains data consistency with proper transaction handling
"""

import json
import logging
import uuid
from collections.abc import Callable, Mapping, Sequence
from datetime import datetime
from decimal import Decimal
from typing import Any, NamedTuple, cast, override

import sqlalchemy as sa
from pydantic import ValidationError
from sqlalchemy import and_, delete, func, null, or_, select, tuple_, update
from sqlalchemy.engine import CursorResult
from sqlalchemy.orm import Session, load_only, selectinload, sessionmaker

from core.workflow.human_input_forms import load_form_tokens_by_form_id
from core.workflow.nodes.human_input.entities import FormDefinition
from core.workflow.nodes.human_input.enums import HumanInputFormStatus
from core.workflow.nodes.human_input.pause_reason import (
    HumanInputRequired,
)
from core.workflow.nodes.human_input.pause_reason import (
    PauseReason as DifyPauseReason,
)
from core.workflow.nodes.human_input.session_binding import default_session_binding
from extensions.ext_storage import storage
from graphon.entities.pause_reason import (
    HitlRequired,
    PauseReasonType,
)
from graphon.entities.pause_reason import (
    PauseReason as GraphonPauseReason,
)
from graphon.enums import WorkflowExecutionStatus, WorkflowNodeExecutionStatus, WorkflowType
from libs.datetime_utils import naive_utc_now
from libs.helper import convert_datetime_to_date
from libs.infinite_scroll_pagination import InfiniteScrollPagination
from libs.time_parser import get_time_threshold
from models import Message
from models.enums import CreatorUserRole, MessageStatus, WorkflowRunTriggeredFrom
from models.human_input import HumanInputForm, HumanInputFormRecipient
from models.workflow import (
    WorkflowAppLog,
    WorkflowArchiveLog,
    WorkflowNodeExecutionModel,
    WorkflowPause,
    WorkflowPauseReason,
    WorkflowRun,
)
from repositories.api_workflow_run_repository import (
    APIWorkflowRunRepository,
    AppWorkflowRunStats,
    RunsWithRelatedCountsDict,
    WorkflowRunCleanupRef,
)
from repositories.entities.workflow_pause import WorkflowPauseEntity
from repositories.types import (
    AverageInteractionStats,
    DailyRunsStats,
    DailyTerminalsStats,
    DailyTokenCostStats,
)
from services.retention.workflow_run.tenant_prefix import tenant_prefix_condition

logger = logging.getLogger(__name__)
_HITL_REASON_TYPES = frozenset({PauseReasonType.LEGACY_HUMAN_INPUT_REQUIRED, PauseReasonType.HITL_REQUIRED})


class WorkflowRunMessageRef(NamedTuple):
    message_id: str
    conversation_id: str


class WorkflowRunPauseRecord(NamedTuple):
    status: WorkflowExecutionStatus
    pause: WorkflowPauseEntity | None


class _WorkflowRunError(Exception):
    pass


class WorkflowRunNotPausedError(_WorkflowRunError):
    """A concurrent stop or resume has already consumed the paused run."""


class WorkflowTaskOwnerMismatchError(_WorkflowRunError):
    """The scoped task belongs to another initiating user."""


class WorkflowPauseStoppedError(_WorkflowRunError):
    """A requested stop won while the engine was persisting its pause."""


_HEX_SHARD_VALUES = {
    "0": 0,
    "1": 1,
    "2": 2,
    "3": 3,
    "4": 4,
    "5": 5,
    "6": 6,
    "7": 7,
    "8": 8,
    "9": 9,
    "a": 10,
    "b": 11,
    "c": 12,
    "d": 13,
    "e": 14,
    "f": 15,
}


def _tenant_prefix_condition(prefixes: Sequence[str]) -> sa.ColumnElement[bool]:
    conditions = [tenant_prefix_condition(WorkflowRun.tenant_id, prefix) for prefix in prefixes]
    return sa.or_(*conditions)


def _workflow_run_id_shard_expr() -> sa.ColumnElement[int]:
    normalized_id = func.lower(func.replace(sa.cast(WorkflowRun.id, sa.String()), "-", ""))
    last_hex = func.substr(normalized_id, func.length(normalized_id), 1)
    return sa.case(
        *[(last_hex == hex_digit, shard_value) for hex_digit, shard_value in _HEX_SHARD_VALUES.items()],
        else_=0,
    )


def _build_human_input_required_reason(
    reason_model: WorkflowPauseReason,
    form_model: HumanInputForm | None,
    recipients: Sequence[HumanInputFormRecipient] = (),
) -> HumanInputRequired:
    form_content = ""
    inputs = []
    actions = []
    resolved_default_values: dict[str, Any] = {}
    node_title = "Human Input"
    form_id = reason_model.form_id
    node_id = reason_model.node_id
    if form_model is not None:
        form_id = form_model.id
        node_id = node_id or form_model.node_id
        try:
            definition_payload = json.loads(form_model.form_definition)
            if "expiration_time" not in definition_payload:
                definition_payload["expiration_time"] = form_model.expiration_time
            definition = FormDefinition.model_validate(definition_payload)
        except ValidationError:
            definition = None

        if definition is not None:
            form_content = form_model.rendered_content or definition.rendered_content or definition.form_content
            inputs = list(definition.inputs)
            actions = list(definition.user_actions)
            resolved_default_values = dict(definition.default_values)
            node_title = definition.node_title or node_title

    reason = HumanInputRequired(
        form_id=form_id,
        form_content=form_content,
        inputs=inputs,
        actions=actions,
        node_id=node_id,
        node_title=node_title,
        resolved_default_values=resolved_default_values,
    )
    return reason


def _to_dify_pause_reason(reason_model: WorkflowPauseReason) -> DifyPauseReason:
    """Map persisted pause reasons onto the Dify-facing repository contract."""
    if reason_model.type_ in _HITL_REASON_TYPES:
        return _build_human_input_required_reason(reason_model, None)
    return cast("DifyPauseReason", reason_model.to_entity())


class DifyAPISQLAlchemyWorkflowRunRepository(APIWorkflowRunRepository):
    """
    SQLAlchemy implementation of APIWorkflowRunRepository.

    Provides service-layer WorkflowRun database operations using SQLAlchemy 2.0
    style queries. Supports dependency injection through sessionmaker and
    maintains proper multi-tenant data isolation.

    Args:
        session_maker: SQLAlchemy sessionmaker instance for database connections
    """

    def __init__(self, session_maker: sessionmaker[Session]):
        """
        Initialize the repository with a sessionmaker.

        Args:
            session_maker: SQLAlchemy sessionmaker for database connections
        """
        self._session_maker = session_maker

    def get_message_refs(
        self,
        *,
        app_id: str,
        workflow_run_ids: Sequence[str],
    ) -> dict[str, WorkflowRunMessageRef]:
        if not workflow_run_ids:
            return {}

        stmt = select(Message.workflow_run_id, Message.id, Message.conversation_id).where(
            Message.app_id == app_id,
            Message.workflow_run_id.in_(workflow_run_ids),
        )
        with self._session_maker() as session:
            rows = session.execute(stmt).all()

        messages_by_run_id: dict[str, WorkflowRunMessageRef] = {}
        for workflow_run_id, message_id, conversation_id in rows:
            if workflow_run_id is not None:
                messages_by_run_id.setdefault(
                    workflow_run_id,
                    WorkflowRunMessageRef(message_id=message_id, conversation_id=conversation_id),
                )
        return messages_by_run_id

    @override
    def get_paginated_workflow_runs(
        self,
        tenant_id: str,
        app_id: str,
        triggered_from: WorkflowRunTriggeredFrom | Sequence[WorkflowRunTriggeredFrom],
        limit: int = 20,
        last_id: str | None = None,
        status: str | None = None,
    ) -> InfiniteScrollPagination:
        """
        Get paginated workflow runs with filtering.

        Implements cursor-based pagination using created_at timestamps for
        efficient handling of large datasets. Filters by tenant, app, and
        trigger source for proper data isolation.
        """
        with self._session_maker() as session:
            # Build base query with filters
            base_stmt = select(WorkflowRun).where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
            )

            # Handle triggered_from values
            if isinstance(triggered_from, WorkflowRunTriggeredFrom):
                triggered_from = [triggered_from]
            if triggered_from:
                base_stmt = base_stmt.where(WorkflowRun.triggered_from.in_(triggered_from))

            # Add optional status filter
            if status:
                base_stmt = base_stmt.where(WorkflowRun.status == status)

            if last_id:
                # Get the last workflow run for cursor-based pagination
                last_run_stmt = base_stmt.where(WorkflowRun.id == last_id)
                last_workflow_run = session.scalar(last_run_stmt)

                if not last_workflow_run:
                    raise ValueError("Last workflow run not exists")

                # Get records created before the last run's timestamp
                base_stmt = base_stmt.where(
                    WorkflowRun.created_at < last_workflow_run.created_at,
                    WorkflowRun.id != last_workflow_run.id,
                )

            # First page - get most recent records
            workflow_runs = session.scalars(base_stmt.order_by(WorkflowRun.created_at.desc()).limit(limit + 1)).all()

            # Check if there are more records for pagination
            has_more = len(workflow_runs) > limit
            if has_more:
                workflow_runs = workflow_runs[:-1]

            return InfiniteScrollPagination(data=workflow_runs, limit=limit, has_more=has_more)

    @override
    def get_workflow_run_by_id(
        self,
        tenant_id: str,
        app_id: str,
        run_id: str,
    ) -> WorkflowRun | None:
        """
        Get a specific workflow run by ID with tenant and app isolation.
        """
        with self._session_maker() as session:
            stmt = select(WorkflowRun).where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.id == run_id,
            )
            return session.scalar(stmt)

    @override
    def get_workflow_run_by_id_for_update(
        self,
        session: Session,
        *,
        tenant_id: str,
        app_id: str,
        run_id: str,
    ) -> WorkflowRun | None:
        """Lock the scoped run in the caller's transaction."""
        stmt = (
            select(WorkflowRun)
            .where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.id == run_id,
            )
            .with_for_update()
        )
        return session.scalar(stmt)

    @override
    def stop_workflow_run(
        self,
        session: Session,
        *,
        run: WorkflowRun,
        error: str,
        finished_at: datetime,
    ) -> None:
        run.status = WorkflowExecutionStatus.STOPPED
        run.error = error
        run.finished_at = finished_at
        pause = session.scalar(select(WorkflowPause).where(WorkflowPause.workflow_run_id == run.id).with_for_update())
        if pause is not None and pause.resumed_at is None:
            pause.resumed_at = finished_at

    @override
    def get_workflow_run_by_id_without_tenant(
        self,
        run_id: str,
    ) -> WorkflowRun | None:
        """
        Get a specific workflow run by ID without tenant/app context.
        """
        with self._session_maker() as session:
            stmt = select(WorkflowRun).where(WorkflowRun.id == run_id)
            return session.scalar(stmt)

    @override
    def get_workflow_runs_count(
        self,
        tenant_id: str,
        app_id: str,
        triggered_from: str,
        status: str | None = None,
        time_range: str | None = None,
    ) -> dict[str, int]:
        """
        Get workflow runs count statistics grouped by status.
        """
        _initial_status_counts = {
            "running": 0,
            "succeeded": 0,
            "failed": 0,
            "stopped": 0,
            "partial-succeeded": 0,
        }

        with self._session_maker() as session:
            # Build base where conditions
            base_conditions = [
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.triggered_from == triggered_from,
            ]

            # Add time range filter if provided
            if time_range:
                time_threshold = get_time_threshold(time_range)
                if time_threshold:
                    base_conditions.append(WorkflowRun.created_at >= time_threshold)

            # If status filter is provided, return simple count
            if status:
                count_stmt = select(func.count(WorkflowRun.id)).where(*base_conditions, WorkflowRun.status == status)
                total = session.scalar(count_stmt) or 0

                result = {"total": total} | _initial_status_counts

                # Set the count for the filtered status
                if status in result:
                    result[status] = total

                return result

            # No status filter - get counts grouped by status
            base_stmt = (
                select(WorkflowRun.status, func.count(WorkflowRun.id).label("count"))
                .where(*base_conditions)
                .group_by(WorkflowRun.status)
            )

            # Execute query
            results = session.execute(base_stmt).all()

            # Build response dictionary
            status_counts = _initial_status_counts.copy()

            total = 0
            for status_val, count in results:
                total += count
                if status_val in status_counts:
                    status_counts[status_val] = count

            return {"total": total} | status_counts

    @override
    def get_stats_by_app_ids(
        self,
        tenant_id_by_app_id: Mapping[str, str],
        triggered_from: Sequence[WorkflowRunTriggeredFrom],
        created_since: datetime,
    ) -> dict[str, AppWorkflowRunStats]:
        if not tenant_id_by_app_id:
            return {}
        counts_stmt = (
            select(WorkflowRun.tenant_id, WorkflowRun.app_id, WorkflowRun.status, func.count(WorkflowRun.id))
            .where(
                *self._app_run_conditions(tenant_id_by_app_id, triggered_from),
                WorkflowRun.created_at >= created_since,
            )
            .group_by(WorkflowRun.tenant_id, WorkflowRun.app_id, WorkflowRun.status)
        )
        # One LIMIT 1 lookup per app, so each stops at that app's newest run instead of scanning its history.
        latest_runs = [
            select(WorkflowRun.app_id, WorkflowRun.created_at)
            .where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.triggered_from.in_(triggered_from),
            )
            .order_by(WorkflowRun.created_at.desc())
            .limit(1)
            .subquery()
            for app_id, tenant_id in tenant_id_by_app_id.items()
        ]
        last_stmt = sa.union_all(*(select(latest_run) for latest_run in latest_runs))
        status_counts: dict[str, dict[WorkflowExecutionStatus, int]] = {}
        with self._session_maker() as session:
            for tenant_id, app_id, status, count in session.execute(counts_stmt):
                # The IN filters match any tenant/app pairing, so keep only each app's own tenant.
                if tenant_id_by_app_id[app_id] == tenant_id:
                    status_counts.setdefault(app_id, {})[WorkflowExecutionStatus(status)] = int(count)
            last_created_at: dict[str, datetime] = dict(session.execute(last_stmt).tuples().all())
        return {
            app_id: AppWorkflowRunStats(status_counts=status_counts.get(app_id, {}), last_created_at=created_at)
            for app_id, created_at in last_created_at.items()
        }

    @staticmethod
    def _app_run_conditions(
        tenant_id_by_app_id: Mapping[str, str], triggered_from: Sequence[WorkflowRunTriggeredFrom]
    ) -> list[sa.ColumnElement[bool]]:
        return [
            WorkflowRun.tenant_id.in_(set(tenant_id_by_app_id.values())),
            WorkflowRun.app_id.in_(list(tenant_id_by_app_id)),
            WorkflowRun.triggered_from.in_(triggered_from),
        ]

    @override
    def get_expired_runs_batch(
        self,
        tenant_id: str,
        before_date: datetime,
        batch_size: int = 1000,
    ) -> Sequence[WorkflowRun]:
        """
        Get a batch of expired workflow runs for cleanup operations.
        """
        with self._session_maker() as session:
            stmt = (
                select(WorkflowRun)
                .where(
                    WorkflowRun.tenant_id == tenant_id,
                    WorkflowRun.created_at < before_date,
                )
                .limit(batch_size)
            )
            return session.scalars(stmt).all()

    @override
    def delete_runs_by_ids(
        self,
        run_ids: Sequence[str],
    ) -> int:
        """
        Delete workflow runs by their IDs using bulk deletion.
        """
        if not run_ids:
            return 0

        with self._session_maker() as session:
            stmt = delete(WorkflowRun).where(WorkflowRun.id.in_(run_ids))
            result = cast(CursorResult, session.execute(stmt))
            session.commit()

            deleted_count = result.rowcount
            logger.info("Deleted %s workflow runs by IDs", deleted_count)
            return deleted_count

    @override
    def delete_runs_by_app(
        self,
        tenant_id: str,
        app_id: str,
        batch_size: int = 1000,
    ) -> int:
        """
        Delete all workflow runs for a specific app in batches.
        """
        total_deleted = 0

        while True:
            with self._session_maker() as session:
                # Get a batch of run IDs to delete
                stmt = (
                    select(WorkflowRun.id)
                    .where(
                        WorkflowRun.tenant_id == tenant_id,
                        WorkflowRun.app_id == app_id,
                    )
                    .limit(batch_size)
                )
                run_ids = session.scalars(stmt).all()

                if not run_ids:
                    break

                # Delete the batch
                delete_stmt = delete(WorkflowRun).where(WorkflowRun.id.in_(run_ids))
                result = cast(CursorResult, session.execute(delete_stmt))
                session.commit()

                batch_deleted = result.rowcount
                total_deleted += batch_deleted

                logger.info("Deleted batch of %s workflow runs for app %s", batch_deleted, app_id)

                # If we deleted fewer records than the batch size, we're done
                if batch_deleted < batch_size:
                    break

        logger.info("Total deleted %s workflow runs for app %s", total_deleted, app_id)
        return total_deleted

    @override
    def get_runs_batch_by_time_range(
        self,
        start_from: datetime | None,
        end_before: datetime,
        last_seen: tuple[datetime, str] | None,
        batch_size: int,
        run_types: Sequence[WorkflowType] | None = None,
        tenant_ids: Sequence[str] | None = None,
        tenant_prefixes: Sequence[str] | None = None,
        workflow_ids: Sequence[str] | None = None,
        run_shard_index: int | None = None,
        run_shard_total: int | None = None,
    ) -> Sequence[WorkflowRun]:
        """
        Fetch ended workflow runs in a time window for archival and clean batching.

        Query scope:
        - created_at in [start_from, end_before)
        - type in run_types (when provided)
        - status is an ended state
        - optional tenant_id, tenant_prefix, workflow_id filters and cursor (last_seen) for pagination
        - optional deterministic shard by the last hexadecimal digit of workflow_run_id
        """
        with self._session_maker() as session:
            stmt = (
                select(WorkflowRun)
                .where(
                    WorkflowRun.created_at < end_before,
                    WorkflowRun.status.in_(WorkflowExecutionStatus.ended_values()),
                )
                .order_by(WorkflowRun.created_at.asc(), WorkflowRun.id.asc())
                .limit(batch_size)
            )
            if run_types is not None:
                if not run_types:
                    return []
                stmt = stmt.where(WorkflowRun.type.in_(run_types))

            if start_from:
                stmt = stmt.where(WorkflowRun.created_at >= start_from)

            if tenant_ids:
                stmt = stmt.where(WorkflowRun.tenant_id.in_(tenant_ids))

            if tenant_prefixes:
                stmt = stmt.where(_tenant_prefix_condition(tenant_prefixes))

            if workflow_ids:
                stmt = stmt.where(WorkflowRun.workflow_id.in_(workflow_ids))

            if run_shard_index is not None and run_shard_total is not None:
                stmt = stmt.where((_workflow_run_id_shard_expr() % run_shard_total) == run_shard_index)

            if last_seen:
                stmt = stmt.where(
                    tuple_(WorkflowRun.created_at, WorkflowRun.id)
                    > tuple_(
                        sa.literal(last_seen[0], type_=sa.DateTime()),
                        sa.literal(last_seen[1], type_=WorkflowRun.id.type),
                    )
                )

            return session.scalars(stmt).all()

    @override
    def get_cleanup_refs_batch_by_time_range(
        self,
        start_from: datetime | None,
        end_before: datetime,
        last_seen: tuple[datetime, str] | None,
        batch_size: int,
        run_types: Sequence[WorkflowType] | None = None,
        tenant_ids: Sequence[str] | None = None,
        workflow_ids: Sequence[str] | None = None,
        upper_bound: tuple[datetime, str] | None = None,
    ) -> Sequence[WorkflowRunCleanupRef]:
        """
        Fetch lightweight ended workflow run refs in a time window for cleanup batching.

        The optional upper_bound is inclusive and is paired with last_seen by free-plan cleanup so a second,
        tenant-filtered target query stays within the candidate page already checked against billing.
        """
        with self._session_maker() as session:
            stmt = (
                select(WorkflowRun.id, WorkflowRun.tenant_id, WorkflowRun.created_at)
                .where(
                    WorkflowRun.created_at < end_before,
                    WorkflowRun.status.in_(WorkflowExecutionStatus.ended_values()),
                )
                .order_by(WorkflowRun.created_at.asc(), WorkflowRun.id.asc())
                .limit(batch_size)
            )
            if run_types is not None:
                if not run_types:
                    return []
                stmt = stmt.where(WorkflowRun.type.in_(run_types))

            if start_from:
                stmt = stmt.where(WorkflowRun.created_at >= start_from)

            if tenant_ids:
                stmt = stmt.where(WorkflowRun.tenant_id.in_(tenant_ids))

            if workflow_ids:
                stmt = stmt.where(WorkflowRun.workflow_id.in_(workflow_ids))

            if last_seen:
                stmt = stmt.where(
                    tuple_(WorkflowRun.created_at, WorkflowRun.id)
                    > tuple_(
                        sa.literal(last_seen[0], type_=sa.DateTime()),
                        sa.literal(last_seen[1], type_=WorkflowRun.id.type),
                    )
                )

            if upper_bound:
                stmt = stmt.where(
                    tuple_(WorkflowRun.created_at, WorkflowRun.id)
                    <= tuple_(
                        sa.literal(upper_bound[0], type_=sa.DateTime()),
                        sa.literal(upper_bound[1], type_=WorkflowRun.id.type),
                    )
                )

            return [
                WorkflowRunCleanupRef(id=run_id, tenant_id=tenant_id, created_at=created_at)
                for run_id, tenant_id, created_at in session.execute(stmt).all()
            ]

    @override
    def get_archived_run_ids(
        self,
        session: Session,
        run_ids: Sequence[str],
    ) -> set[str]:
        if not run_ids:
            return set()

        stmt = select(WorkflowArchiveLog.workflow_run_id).where(WorkflowArchiveLog.workflow_run_id.in_(run_ids))
        return set(session.scalars(stmt).all())

    @override
    def get_archived_log_by_run_id(
        self,
        run_id: str,
    ) -> WorkflowArchiveLog | None:
        with self._session_maker() as session:
            stmt = select(WorkflowArchiveLog).where(WorkflowArchiveLog.workflow_run_id == run_id).limit(1)
            return session.scalar(stmt)

    @override
    def delete_archive_log_by_run_id(
        self,
        session: Session,
        run_id: str,
    ) -> int:
        stmt = delete(WorkflowArchiveLog).where(WorkflowArchiveLog.workflow_run_id == run_id)
        result = session.execute(stmt)
        return cast(CursorResult, result).rowcount or 0

    @override
    def get_pause_records_by_run_id(
        self,
        session: Session,
        run_id: str,
    ) -> Sequence[WorkflowPause]:
        stmt = select(WorkflowPause).where(WorkflowPause.workflow_run_id == run_id)
        return list(session.scalars(stmt))

    @override
    def get_pause_reason_records_by_run_id(
        self,
        session: Session,
        pause_ids: Sequence[str],
    ) -> Sequence[WorkflowPauseReason]:
        if not pause_ids:
            return []

        stmt = select(WorkflowPauseReason).where(WorkflowPauseReason.pause_id.in_(pause_ids))
        return list(session.scalars(stmt))

    @override
    def delete_runs_with_related(
        self,
        runs: Sequence[WorkflowRun],
        delete_node_executions: Callable[[Session, Sequence[WorkflowRun]], tuple[int, int]] | None = None,
        delete_trigger_logs: Callable[[Session, Sequence[str]], int] | None = None,
    ) -> RunsWithRelatedCountsDict:
        with self._session_maker.begin() as session:
            return self.delete_runs_with_related_in_session(
                session,
                runs,
                delete_node_executions=delete_node_executions,
                delete_trigger_logs=delete_trigger_logs,
            )

    @override
    def delete_runs_with_related_in_session(
        self,
        session: Session,
        runs: Sequence[WorkflowRun],
        delete_node_executions: Callable[[Session, Sequence[WorkflowRun]], tuple[int, int]] | None = None,
        delete_trigger_logs: Callable[[Session, Sequence[str]], int] | None = None,
    ) -> RunsWithRelatedCountsDict:
        if not runs:
            return self._empty_runs_with_related_counts()

        runs = list(runs)

        def delete_node_executions_by_ids(active_session: Session, _run_ids: Sequence[str]) -> tuple[int, int]:
            if delete_node_executions is None:
                return 0, 0
            return delete_node_executions(active_session, runs)

        return self._delete_runs_with_related_by_ids_in_session(
            session,
            [run.id for run in runs],
            delete_node_executions=delete_node_executions_by_ids if delete_node_executions else None,
            delete_trigger_logs=delete_trigger_logs,
        )

    @override
    def delete_runs_with_related_by_ids(
        self,
        run_ids: Sequence[str],
        delete_node_executions: Callable[[Session, Sequence[str]], tuple[int, int]] | None = None,
        delete_trigger_logs: Callable[[Session, Sequence[str]], int] | None = None,
    ) -> RunsWithRelatedCountsDict:
        with self._session_maker.begin() as session:
            return self._delete_runs_with_related_by_ids_in_session(
                session,
                run_ids,
                delete_node_executions=delete_node_executions,
                delete_trigger_logs=delete_trigger_logs,
            )

    def _delete_runs_with_related_by_ids_in_session(
        self,
        session: Session,
        run_ids: Sequence[str],
        delete_node_executions: Callable[[Session, Sequence[str]], tuple[int, int]] | None = None,
        delete_trigger_logs: Callable[[Session, Sequence[str]], int] | None = None,
    ) -> RunsWithRelatedCountsDict:
        if not run_ids:
            return self._empty_runs_with_related_counts()

        run_ids = list(run_ids)
        if delete_node_executions:
            node_executions_deleted, offloads_deleted = delete_node_executions(session, run_ids)
        else:
            node_executions_deleted, offloads_deleted = 0, 0

        app_logs_result = session.execute(delete(WorkflowAppLog).where(WorkflowAppLog.workflow_run_id.in_(run_ids)))
        app_logs_deleted = cast(CursorResult, app_logs_result).rowcount or 0

        pause_stmt = select(WorkflowPause.id).where(WorkflowPause.workflow_run_id.in_(run_ids))
        pause_ids = session.scalars(pause_stmt).all()
        pause_reasons_deleted = 0
        pauses_deleted = 0

        if pause_ids:
            pause_reasons_result = session.execute(
                delete(WorkflowPauseReason).where(WorkflowPauseReason.pause_id.in_(pause_ids))
            )
            pause_reasons_deleted = cast(CursorResult, pause_reasons_result).rowcount or 0
            pauses_result = session.execute(delete(WorkflowPause).where(WorkflowPause.id.in_(pause_ids)))
            pauses_deleted = cast(CursorResult, pauses_result).rowcount or 0

        trigger_logs_deleted = delete_trigger_logs(session, run_ids) if delete_trigger_logs else 0

        runs_result = session.execute(delete(WorkflowRun).where(WorkflowRun.id.in_(run_ids)))
        runs_deleted = cast(CursorResult, runs_result).rowcount or 0

        return {
            "runs": runs_deleted,
            "node_executions": node_executions_deleted,
            "offloads": offloads_deleted,
            "app_logs": app_logs_deleted,
            "trigger_logs": trigger_logs_deleted,
            "pauses": pauses_deleted,
            "pause_reasons": pause_reasons_deleted,
        }

    @override
    def get_app_logs_by_run_id(
        self,
        session: Session,
        run_id: str,
    ) -> Sequence[WorkflowAppLog]:
        stmt = select(WorkflowAppLog).where(WorkflowAppLog.workflow_run_id == run_id)
        return list(session.scalars(stmt))

    @override
    def create_archive_logs(
        self,
        session: Session,
        run: WorkflowRun,
        app_logs: Sequence[WorkflowAppLog],
        trigger_metadata: str | None,
    ) -> int:
        if not app_logs:
            archive_log = WorkflowArchiveLog(
                log_id=None,
                log_created_at=None,
                log_created_from=None,
                tenant_id=run.tenant_id,
                app_id=run.app_id,
                workflow_id=run.workflow_id,
                workflow_run_id=run.id,
                created_by_role=run.created_by_role,
                created_by=run.created_by,
                run_version=run.version,
                run_status=run.status,
                run_triggered_from=run.triggered_from,
                run_error=run.error,
                run_elapsed_time=run.elapsed_time,
                run_total_tokens=run.total_tokens,
                run_total_steps=run.total_steps,
                run_created_at=run.created_at,
                run_finished_at=run.finished_at,
                run_exceptions_count=run.exceptions_count,
                trigger_metadata=trigger_metadata,
            )
            session.add(archive_log)
            return 1

        archive_logs = [
            WorkflowArchiveLog(
                log_id=app_log.id,
                log_created_at=app_log.created_at,
                log_created_from=app_log.created_from,
                tenant_id=run.tenant_id,
                app_id=run.app_id,
                workflow_id=run.workflow_id,
                workflow_run_id=run.id,
                created_by_role=run.created_by_role,
                created_by=run.created_by,
                run_version=run.version,
                run_status=run.status,
                run_triggered_from=run.triggered_from,
                run_error=run.error,
                run_elapsed_time=run.elapsed_time,
                run_total_tokens=run.total_tokens,
                run_total_steps=run.total_steps,
                run_created_at=run.created_at,
                run_finished_at=run.finished_at,
                run_exceptions_count=run.exceptions_count,
                trigger_metadata=trigger_metadata,
            )
            for app_log in app_logs
        ]
        session.add_all(archive_logs)
        return len(archive_logs)

    @override
    def get_archived_runs_by_time_range(
        self,
        session: Session,
        tenant_ids: Sequence[str] | None,
        start_date: datetime,
        end_date: datetime,
        limit: int,
    ) -> Sequence[WorkflowRun]:
        """
        Retrieves WorkflowRun records by joining workflow_archive_logs.

        Used to identify runs that are already archived and ready for deletion.
        """
        stmt = (
            select(WorkflowRun)
            .join(WorkflowArchiveLog, WorkflowArchiveLog.workflow_run_id == WorkflowRun.id)
            .where(
                WorkflowArchiveLog.run_created_at >= start_date,
                WorkflowArchiveLog.run_created_at < end_date,
            )
            .order_by(WorkflowArchiveLog.run_created_at.asc(), WorkflowArchiveLog.workflow_run_id.asc())
            .limit(limit)
        )
        if tenant_ids:
            stmt = stmt.where(WorkflowArchiveLog.tenant_id.in_(tenant_ids))
        return list(session.scalars(stmt))

    @override
    def get_archived_logs_by_time_range(
        self,
        session: Session,
        tenant_ids: Sequence[str] | None,
        start_date: datetime,
        end_date: datetime,
        limit: int,
    ) -> Sequence[WorkflowArchiveLog]:
        # Returns WorkflowArchiveLog rows directly; use this when workflow_runs may be deleted.
        stmt = (
            select(WorkflowArchiveLog)
            .where(
                WorkflowArchiveLog.run_created_at >= start_date,
                WorkflowArchiveLog.run_created_at < end_date,
            )
            .order_by(WorkflowArchiveLog.run_created_at.asc(), WorkflowArchiveLog.workflow_run_id.asc())
            .limit(limit)
        )
        if tenant_ids:
            stmt = stmt.where(WorkflowArchiveLog.tenant_id.in_(tenant_ids))
        return list(session.scalars(stmt))

    @override
    def count_runs_with_related(
        self,
        runs: Sequence[WorkflowRun],
        count_node_executions: Callable[[Session, Sequence[WorkflowRun]], tuple[int, int]] | None = None,
        count_trigger_logs: Callable[[Session, Sequence[str]], int] | None = None,
    ) -> RunsWithRelatedCountsDict:
        if not runs:
            return {
                "runs": 0,
                "node_executions": 0,
                "offloads": 0,
                "app_logs": 0,
                "trigger_logs": 0,
                "pauses": 0,
                "pause_reasons": 0,
            }

        with self._session_maker() as session:
            run_ids = [run.id for run in runs]
            if count_node_executions:
                node_executions_count, offloads_count = count_node_executions(session, runs)
            else:
                node_executions_count, offloads_count = 0, 0

            app_logs_count = (
                session.scalar(
                    select(func.count()).select_from(WorkflowAppLog).where(WorkflowAppLog.workflow_run_id.in_(run_ids))
                )
                or 0
            )

            pause_ids = session.scalars(
                select(WorkflowPause.id).where(WorkflowPause.workflow_run_id.in_(run_ids))
            ).all()
            pauses_count = len(pause_ids)
            pause_reasons_count = 0
            if pause_ids:
                pause_reasons_count = (
                    session.scalar(
                        select(func.count())
                        .select_from(WorkflowPauseReason)
                        .where(WorkflowPauseReason.pause_id.in_(pause_ids))
                    )
                    or 0
                )

            trigger_logs_count = count_trigger_logs(session, run_ids) if count_trigger_logs else 0

            return {
                "runs": len(runs),
                "node_executions": node_executions_count,
                "offloads": offloads_count,
                "app_logs": int(app_logs_count),
                "trigger_logs": trigger_logs_count,
                "pauses": pauses_count,
                "pause_reasons": int(pause_reasons_count),
            }

    @override
    def count_runs_with_related_by_ids(
        self,
        run_ids: Sequence[str],
        count_node_executions: Callable[[Session, Sequence[str]], tuple[int, int]] | None = None,
        count_trigger_logs: Callable[[Session, Sequence[str]], int] | None = None,
    ) -> RunsWithRelatedCountsDict:
        if not run_ids:
            return self._empty_runs_with_related_counts()

        run_ids = list(run_ids)
        with self._session_maker() as session:
            if count_node_executions:
                node_executions_count, offloads_count = count_node_executions(session, run_ids)
            else:
                node_executions_count, offloads_count = 0, 0

            runs_count = (
                session.scalar(select(func.count()).select_from(WorkflowRun).where(WorkflowRun.id.in_(run_ids))) or 0
            )
            app_logs_count = (
                session.scalar(
                    select(func.count()).select_from(WorkflowAppLog).where(WorkflowAppLog.workflow_run_id.in_(run_ids))
                )
                or 0
            )

            pause_ids = session.scalars(
                select(WorkflowPause.id).where(WorkflowPause.workflow_run_id.in_(run_ids))
            ).all()
            pauses_count = len(pause_ids)
            pause_reasons_count = 0
            if pause_ids:
                pause_reasons_count = (
                    session.scalar(
                        select(func.count())
                        .select_from(WorkflowPauseReason)
                        .where(WorkflowPauseReason.pause_id.in_(pause_ids))
                    )
                    or 0
                )

            trigger_logs_count = count_trigger_logs(session, run_ids) if count_trigger_logs else 0

            return {
                "runs": int(runs_count),
                "node_executions": node_executions_count,
                "offloads": offloads_count,
                "app_logs": int(app_logs_count),
                "trigger_logs": trigger_logs_count,
                "pauses": pauses_count,
                "pause_reasons": int(pause_reasons_count),
            }

    @staticmethod
    def _empty_runs_with_related_counts() -> RunsWithRelatedCountsDict:
        return {
            "runs": 0,
            "node_executions": 0,
            "offloads": 0,
            "app_logs": 0,
            "trigger_logs": 0,
            "pauses": 0,
            "pause_reasons": 0,
        }

    @override
    def create_workflow_pause(
        self,
        workflow_run_id: str,
        state_owner_user_id: str,
        state: str,
        pause_reasons: Sequence[GraphonPauseReason | DifyPauseReason],
    ) -> WorkflowPauseEntity:
        """
        Create a new workflow pause state.

        Creates a pause state for a workflow run, storing the current execution
        state and marking the workflow as paused. This is used when a workflow
        needs to be suspended and later resumed.

        Args:
            workflow_run_id: Identifier of the workflow run to pause
            state_owner_user_id: User ID who owns the pause state for file storage
            state: Serialized workflow execution state (JSON string)

        Returns:
            RepositoryWorkflowPauseEntity representing the created pause state

        Raises:
            ValueError: If workflow_run_id is invalid or workflow run doesn't exist
            RuntimeError: If workflow is already paused or in invalid state
        """
        # Do not hold a database transaction during object storage upload.
        # The subsequent run lock checks durable stop intent before publishing pause.
        state_obj_key = f"workflow-state-{uuid.uuid4()}.json"
        storage.save(state_obj_key, state.encode())
        try:
            previous_pause_model_query = select(WorkflowPause).where(WorkflowPause.workflow_run_id == workflow_run_id)
            with self._session_maker() as session, session.begin():
                # Get the workflow run
                workflow_run = session.scalar(
                    select(WorkflowRun).where(WorkflowRun.id == workflow_run_id).with_for_update()
                )
                if workflow_run is None:
                    raise ValueError(f"WorkflowRun not found: {workflow_run_id}")

                if workflow_run.stop_requested_at is not None or workflow_run.status == WorkflowExecutionStatus.STOPPED:
                    raise WorkflowPauseStoppedError(f"Workflow stopped while persisting pause: {workflow_run_id}")

                # Check if workflow is in RUNNING status
                # TODO(QuantumGhost): It seems that the persistence of `WorkflowRun.status`
                # happens before the execution of GraphLayer
                if workflow_run.status not in {WorkflowExecutionStatus.RUNNING, WorkflowExecutionStatus.PAUSED}:
                    raise _WorkflowRunError(
                        f"Only WorkflowRun with RUNNING or PAUSED status can be paused, "
                        f"workflow_run_id={workflow_run_id}, current_status={workflow_run.status}"
                    )
                #
                previous_pause = session.scalars(previous_pause_model_query).first()
                if previous_pause:
                    self._delete_pause_model(session, previous_pause)
                    # we need to flush here to ensure that the old one is actually deleted.
                    session.flush()

                # Create the pause record
                pause_model = WorkflowPause(
                    workflow_id=workflow_run.workflow_id,
                    workflow_run_id=workflow_run.id,
                    state_object_key=state_obj_key,
                )
                pause_reason_models = [
                    WorkflowPauseReason.from_entity(pause_id=pause_model.id, pause_reason=reason)
                    for reason in pause_reasons
                    if (
                        isinstance(reason, HumanInputRequired)
                        and reason.form_version == "1"
                        or isinstance(reason, HitlRequired)
                        and default_session_binding.resolve_form_id_from_session_id(session_id=reason.session_id)[0]
                        == "1"
                    )
                ]

                # Update workflow run status
                workflow_run.status = WorkflowExecutionStatus.PAUSED

                # Save everything in a transaction
                session.add(pause_model)
                session.add(workflow_run)
                session.add_all(pause_reason_models)

                logger.info("Created workflow pause %s for workflow run %s", pause_model.id, workflow_run_id)
                return _PrivateWorkflowPauseEntity(pause_model=pause_model)
        except Exception:
            try:
                storage.delete(state_obj_key)
            except Exception:
                logger.exception(
                    "Failed to clean up unpublished workflow pause state, workflow_run_id=%s", workflow_run_id
                )
            raise

    @override
    def get_legacy_pause_reasons(self, session: Session, pause_id: str) -> list[DifyPauseReason]:
        reason_stmt = select(WorkflowPauseReason).where(WorkflowPauseReason.pause_id == pause_id)
        return self._hydrate_pause_reasons(session, session.scalars(reason_stmt).all())

    def _hydrate_pause_reasons(
        self,
        session: Session,
        pause_reason_models: Sequence[WorkflowPauseReason],
    ) -> list[DifyPauseReason]:
        form_ids = [
            reason.form_id for reason in pause_reason_models if reason.type_ in _HITL_REASON_TYPES and reason.form_id
        ]
        form_models: dict[str, HumanInputForm] = {}
        if form_ids:
            form_stmt = select(HumanInputForm).where(HumanInputForm.id.in_(form_ids))
            for form in session.scalars(form_stmt).all():
                form_models[form.id] = form
        recipients_by_form_id: dict[str, list[HumanInputFormRecipient]] = {}
        if form_ids:
            recipient_stmt = select(HumanInputFormRecipient).where(HumanInputFormRecipient.form_id.in_(form_ids))
            for recipient in session.scalars(recipient_stmt).all():
                recipients_by_form_id.setdefault(recipient.form_id, []).append(recipient)

        pause_reasons: list[DifyPauseReason] = []
        for reason in pause_reason_models:
            if reason.type_ in _HITL_REASON_TYPES:
                form_model = form_models.get(reason.form_id)
                pause_reasons.append(
                    _build_human_input_required_reason(
                        reason,
                        form_model,
                        recipients_by_form_id.get(reason.form_id, ()),
                    )
                )
            else:
                pause_reasons.append(_to_dify_pause_reason(reason))
        return pause_reasons

    @override
    def bind_workflow_task(self, *, tenant_id: str, app_id: str, workflow_run_id: str, task_id: str) -> None:
        with self._session_maker() as session, session.begin():
            session.execute(
                update(WorkflowRun)
                .where(
                    WorkflowRun.id == workflow_run_id,
                    # Keep owner checks together so PostgreSQL can use the run's primary key instead of
                    # underestimating the tenant/app composite index when their values are correlated.
                    and_(WorkflowRun.tenant_id == tenant_id, WorkflowRun.app_id == app_id).is_(True),
                    WorkflowRun.task_id.is_(None),
                )
                .values(task_id=task_id)
            )

    def list_legacy_workflow_pauses(
        self,
        *,
        tenant_id: str,
        app_id: str,
        owner: tuple[CreatorUserRole, str] | None,
        after_id: str | None,
        limit: int,
    ) -> Sequence[tuple[WorkflowRun, WorkflowPauseEntity]]:
        """Page admitted legacy snapshots without loading graphs or pause reasons."""
        # TODO: Remove legacy task recovery 30 days after the last legacy writer is retired.
        stmt = (
            select(WorkflowRun, WorkflowPause)
            .select_from(WorkflowPause)
            .join(WorkflowRun, WorkflowRun.id == WorkflowPause.workflow_run_id)
            .options(
                load_only(
                    WorkflowRun.id,
                    WorkflowRun.tenant_id,
                    WorkflowRun.app_id,
                    WorkflowRun.task_id,
                    WorkflowRun.status,
                    WorkflowRun.created_by,
                    WorkflowRun.created_by_role,
                )
            )
            .where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.task_id.is_(None),
                WorkflowRun.type.in_([WorkflowType.WORKFLOW, WorkflowType.CHAT]),
                WorkflowRun.status == WorkflowExecutionStatus.PAUSED,
                WorkflowPause.resumed_at.is_(None),
            )
            .order_by(WorkflowPause.id)
            .limit(limit)
        )
        if owner is not None:
            stmt = stmt.where(WorkflowRun.created_by_role == owner[0], WorkflowRun.created_by == owner[1])
        if after_id is not None:
            stmt = stmt.where(WorkflowPause.id > after_id)
        with self._session_maker() as session:
            return [
                (run, _PrivateWorkflowPauseEntity(pause_model=pause)) for run, pause in session.execute(stmt).tuples()
            ]

    @override
    def backfill_workflow_pause_task_id(
        self, *, tenant_id: str, app_id: str, workflow_run_id: str, pause_id: str, task_id: str
    ) -> bool:
        current_pause = select(WorkflowPause.workflow_run_id).where(
            WorkflowPause.id == pause_id,
            WorkflowPause.workflow_run_id == workflow_run_id,
            WorkflowPause.resumed_at.is_(None),
        )
        with self._session_maker() as session, session.begin():
            result = session.execute(
                update(WorkflowRun)
                .where(
                    WorkflowRun.id.in_(current_pause),
                    WorkflowRun.tenant_id == tenant_id,
                    WorkflowRun.app_id == app_id,
                    WorkflowRun.status == WorkflowExecutionStatus.PAUSED,
                    WorkflowRun.task_id.is_(None),
                )
                .values(task_id=task_id)
            )
            return cast(CursorResult, result).rowcount == 1

    @override
    def get_workflow_pause(
        self,
        workflow_run_id: str,
    ) -> WorkflowPauseEntity | None:
        """
        Get an existing workflow pause state.

        Retrieves the pause state for a specific workflow run if it exists.
        Used to check if a workflow is paused and to retrieve its saved state.

        Args:
            workflow_run_id: Identifier of the workflow run to get pause state for

        Returns:
            RepositoryWorkflowPauseEntity if pause state exists, None otherwise

        Raises:
            ValueError: If workflow_run_id is invalid
        """
        with self._session_maker() as session:
            # Query workflow run with pause and state file
            stmt = select(WorkflowRun).options(selectinload(WorkflowRun.pause)).where(WorkflowRun.id == workflow_run_id)
            workflow_run = session.scalar(stmt)

            if workflow_run is None:
                raise ValueError(f"WorkflowRun not found: {workflow_run_id}")

            pause_model = workflow_run.pause
            if pause_model is None:
                return None

        return _PrivateWorkflowPauseEntity(pause_model=pause_model)

    @override
    def has_active_workflow_pause(
        self,
        session: Session,
        *,
        tenant_id: str,
        app_id: str,
        run_id: str,
    ) -> bool:
        active_pause = (
            select(WorkflowPause.id)
            .join(WorkflowRun, WorkflowRun.id == WorkflowPause.workflow_run_id)
            .where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.id == run_id,
                WorkflowPause.resumed_at.is_(None),
            )
            .exists()
        )
        return session.scalar(select(active_pause)) is True

    def get_pause_record(
        self,
        session: Session,
        *,
        workspace_id: str,
        workflow_run_id: str,
    ) -> WorkflowRunPauseRecord | None:
        stmt = (
            select(WorkflowRun)
            .options(selectinload(WorkflowRun.pause))
            .where(
                WorkflowRun.tenant_id == workspace_id,
                WorkflowRun.id == workflow_run_id,
            )
        )
        workflow_run = session.scalar(stmt)
        if workflow_run is None:
            return None
        pause_model = workflow_run.pause if workflow_run.status == WorkflowExecutionStatus.PAUSED else None
        return WorkflowRunPauseRecord(
            status=workflow_run.status,
            pause=_PrivateWorkflowPauseEntity(pause_model=pause_model) if pause_model is not None else None,
        )

    def get_legacy_form_tokens(self, session: Session, *, form_ids: Sequence[str]) -> dict[str, str]:
        return load_form_tokens_by_form_id(form_ids, session=session)

    def stop_paused_workflow_task(
        self,
        *,
        tenant_id: str,
        app_id: str,
        task_id: str,
        owner: tuple[CreatorUserRole, str] | None,
    ) -> WorkflowRun | None:
        """Terminate an admitted app's paused task and invalidate its pending forms.

        ``owner=None`` preserves app-scoped Workflow API cancellation. Chat
        callers supply the initiating account or end user. The run lock is also
        taken by resume, so a stopped run cannot be changed back to running.
        """
        stmt = (
            select(WorkflowRun)
            .where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.app_id == app_id,
                WorkflowRun.task_id == task_id,
            )
            .with_for_update(of=WorkflowRun)
        )
        with self._session_maker(expire_on_commit=False) as session, session.begin():
            workflow_run = session.scalar(stmt)
            if workflow_run is None:
                return None
            if owner is not None and (workflow_run.created_by_role, workflow_run.created_by) != owner:
                raise WorkflowTaskOwnerMismatchError()
            if workflow_run.status == WorkflowExecutionStatus.STOPPED and workflow_run.stop_requested_at is not None:
                # A retry can finish publishing history/SSE if the external
                # history backend failed after the durable stop committed.
                return workflow_run
            if workflow_run.status not in (WorkflowExecutionStatus.RUNNING, WorkflowExecutionStatus.PAUSED):
                return None
            workflow_run.stop_requested_at = naive_utc_now()
            if workflow_run.status != WorkflowExecutionStatus.PAUSED:
                # A resumed attempt may not have recreated its Redis ownership
                # cache yet. Preserve this durable admission so stop can still
                # arm its command channel during that handoff.
                return workflow_run
            pause = session.scalar(select(WorkflowPause).where(WorkflowPause.workflow_run_id == workflow_run.id))
            workflow_run.status = WorkflowExecutionStatus.STOPPED
            workflow_run.error = "User requested stop"
            finished_at = naive_utc_now()
            workflow_run.finished_at = finished_at
            session.execute(
                update(Message)
                .where(
                    Message.app_id == app_id,
                    Message.workflow_run_id == workflow_run.id,
                    Message.status == MessageStatus.PAUSED,
                )
                .values(status=MessageStatus.NORMAL)
            )
            # Nested Workflow Tool forms share their root run ID. The admitted
            # run establishes ownership before these dependent rows are changed.
            session.execute(
                update(HumanInputForm)
                .where(
                    HumanInputForm.tenant_id == tenant_id,
                    HumanInputForm.workflow_run_id == workflow_run.id,
                    HumanInputForm.status == HumanInputFormStatus.WAITING,
                )
                .values(status=HumanInputFormStatus.EXPIRED)
            )
            unfinished_nodes = session.scalars(
                select(WorkflowNodeExecutionModel).where(
                    WorkflowNodeExecutionModel.tenant_id == tenant_id,
                    WorkflowNodeExecutionModel.workflow_run_id == workflow_run.id,
                    WorkflowNodeExecutionModel.status.in_(
                        [
                            WorkflowNodeExecutionStatus.RUNNING,
                            WorkflowNodeExecutionStatus.PAUSED,
                            WorkflowNodeExecutionStatus.RETRY,
                        ]
                    ),
                )
            )
            for node in unfinished_nodes:
                node.status = WorkflowNodeExecutionStatus.FAILED
                node.error = workflow_run.error
                node.finished_at = finished_at
                node.elapsed_time = max((finished_at - node.created_at).total_seconds(), 0.0)
            state_object_key = pause.state_object_key if pause is not None else None
            if pause is not None:
                session.execute(delete(WorkflowPauseReason).where(WorkflowPauseReason.pause_id == pause.id))
                session.delete(pause)

        # Storage is external I/O; a cleanup failure must not undo cancellation.
        if state_object_key is not None:
            try:
                storage.delete(state_object_key)
            except Exception:
                logger.exception("Failed to delete stopped workflow state, workflow_run_id=%s", workflow_run.id)
        return workflow_run

    @override
    def resume_workflow_pause(
        self,
        workflow_run_id: str,
        pause_entity: WorkflowPauseEntity,
        *,
        before_resume: Callable[[], None] | None = None,
    ) -> WorkflowPauseEntity:
        """
        Resume a paused workflow.

        Marks a paused workflow as resumed, clearing the pause state and
        returning the workflow to running status. Returns the pause entity
        that was resumed.

        Args:
            workflow_run_id: Identifier of the workflow run to resume
            pause_entity: The pause entity to resume

        Returns:
            RepositoryWorkflowPauseEntity representing the resumed pause state

        Raises:
            ValueError: If workflow_run_id is invalid
            RuntimeError: If workflow is not paused or already resumed
        """
        with self._session_maker() as session, session.begin():
            # Get the workflow run with pause
            stmt = (
                select(WorkflowRun)
                .options(selectinload(WorkflowRun.pause))
                .where(WorkflowRun.id == workflow_run_id)
                .with_for_update()
            )
            workflow_run = session.scalar(stmt)

            if workflow_run is None:
                raise ValueError(f"WorkflowRun not found: {workflow_run_id}")

            if workflow_run.status != WorkflowExecutionStatus.PAUSED or workflow_run.stop_requested_at is not None:
                raise WorkflowRunNotPausedError(
                    f"WorkflowRun is not in PAUSED status, workflow_run_id={workflow_run_id}, "
                    f"current_status={workflow_run.status}"
                )
            pause_model = workflow_run.pause
            if pause_model is None:
                raise _WorkflowRunError(f"No pause state found for workflow run: {workflow_run_id}")

            if pause_model.id != pause_entity.id:
                raise _WorkflowRunError(
                    "different id in WorkflowPause and WorkflowPauseEntity, "
                    f"WorkflowPause.id={pause_model.id}, "
                    f"WorkflowPauseEntity.id={pause_entity.id}"
                )

            if pause_model.resumed_at is not None:
                raise _WorkflowRunError(f"Cannot resume an already resumed pause, pause_id={pause_model.id}")

            # Reset the bounded Redis cancellation channels while holding the
            # run lock: stop must either win this transition or send its signals
            # after the reset. A duplicate resume must never clear a live stop.
            if before_resume is not None:
                before_resume()

            # Mark as resumed
            pause_model.resumed_at = naive_utc_now()
            workflow_run.status = WorkflowExecutionStatus.RUNNING

            session.add(pause_model)
            session.add(workflow_run)

            logger.info("Resumed workflow pause %s for workflow run %s", pause_model.id, workflow_run_id)

            return _PrivateWorkflowPauseEntity(pause_model=pause_model)

    @override
    def delete_workflow_pause(
        self,
        pause_entity: WorkflowPauseEntity,
    ) -> None:
        """
        Delete a workflow pause state.

        Removes the pause record for a workflow run and attempts to delete its
        stored state file. Used for cleanup operations when a paused workflow
        is no longer needed.

        Args:
            pause_entity: The pause entity to delete

        Raises:
            ValueError: If pause_entity is invalid
            _WorkflowRunError: If workflow is not paused

        Note:
            Storage deletion is best-effort. If it fails, the pause record is
            still deleted and the orphaned object key is logged for cleanup.
        """
        with self._session_maker() as session, session.begin():
            # Get the pause model by ID
            pause_model = session.get(WorkflowPause, pause_entity.id)
            if pause_model is None:
                raise _WorkflowRunError(f"WorkflowPause not found: {pause_entity.id}")
            self._delete_pause_model(session, pause_model)

    @staticmethod
    def _delete_pause_model(session: Session, pause_model: WorkflowPause) -> None:
        try:
            storage.delete(pause_model.state_object_key)
        except Exception:
            # Keeping the database row would block the next pause because workflow_run_id is unique.
            logger.exception(
                "Failed to delete state object for workflow pause; continuing with pause record deletion, "
                "pause_id=%s, workflow_run_id=%s, object_key=%s",
                pause_model.id,
                pause_model.workflow_run_id,
                pause_model.state_object_key,
            )

        # Delete the pause record
        session.delete(pause_model)

        logger.info("Deleted workflow pause %s for workflow run %s", pause_model.id, pause_model.workflow_run_id)

    @override
    def prune_pauses(
        self,
        expiration: datetime,
        resumption_expiration: datetime,
        limit: int | None = None,
    ) -> Sequence[str]:
        """
        Clean up expired and old pause states.

        Removes pause states that have expired (created before expiration time)
        and pause states that were resumed more than resumption_duration ago.
        This is used for maintenance and cleanup operations.

        Args:
            expiration: Remove pause states created before this time
            resumption_expiration: Remove pause states resumed before this time
            limit: maximum number of records deleted in one call

        Returns:
            a list of ids for pause records that were pruned

        Raises:
            ValueError: If parameters are invalid
        """
        _limit: int = limit or 1000
        pruned_record_ids: list[str] = []
        cond = or_(
            WorkflowPause.created_at < expiration,
            and_(
                WorkflowPause.resumed_at.is_not(null()),
                WorkflowPause.resumed_at < resumption_expiration,
            ),
        )
        # First, collect pause records to delete with their state files
        # Expired pauses (created before expiration time)
        stmt = select(WorkflowPause).where(cond).limit(_limit)

        with self._session_maker(expire_on_commit=False) as session:
            # Old resumed pauses (resumed more than resumption_duration ago)

            # Get all records to delete
            pauses_to_delete = session.scalars(stmt).all()

        # Delete state files from storage
        for pause in pauses_to_delete:
            with self._session_maker(expire_on_commit=False) as session, session.begin():
                # todo: this issues a separate query for each WorkflowPause record.
                # consider batching this lookup.
                try:
                    storage.delete(pause.state_object_key)
                    logger.info(
                        "Deleted state object for pause, pause_id=%s, object_key=%s",
                        pause.id,
                        pause.state_object_key,
                    )
                except Exception:
                    logger.exception(
                        "Failed to delete state file for pause, pause_id=%s, object_key=%s",
                        pause.id,
                        pause.state_object_key,
                    )
                    continue
                session.delete(pause)
                pruned_record_ids.append(pause.id)
                logger.info(
                    "workflow pause records deleted, id=%s, resumed_at=%s",
                    pause.id,
                    pause.resumed_at,
                )

        return pruned_record_ids

    @override
    def get_daily_runs_statistics(
        self,
        tenant_id: str,
        app_id: str,
        triggered_from: str,
        start_date: datetime | None = None,
        end_date: datetime | None = None,
        timezone: str = "UTC",
    ) -> list[DailyRunsStats]:
        """
        Get daily runs statistics using raw SQL for optimal performance.
        """
        converted_created_at = convert_datetime_to_date("created_at")
        sql_query = f"""SELECT
    {converted_created_at} AS date,
    COUNT(id) AS runs
FROM
    workflow_runs
WHERE
    tenant_id = :tenant_id
    AND app_id = :app_id
    AND triggered_from = :triggered_from"""

        arg_dict: dict[str, Any] = {
            "tz": timezone,
            "tenant_id": tenant_id,
            "app_id": app_id,
            "triggered_from": triggered_from,
        }

        if start_date:
            sql_query += " AND created_at >= :start_date"
            arg_dict["start_date"] = start_date

        if end_date:
            sql_query += " AND created_at < :end_date"
            arg_dict["end_date"] = end_date

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []
        with self._session_maker() as session:
            rs = session.execute(sa.text(sql_query), arg_dict)
            for row in rs:
                response_data.append({"date": str(row.date), "runs": row.runs})

        return cast(list[DailyRunsStats], response_data)

    @override
    def get_daily_terminals_statistics(
        self,
        tenant_id: str,
        app_id: str,
        triggered_from: str,
        start_date: datetime | None = None,
        end_date: datetime | None = None,
        timezone: str = "UTC",
    ) -> list[DailyTerminalsStats]:
        """
        Get daily terminals statistics using raw SQL for optimal performance.
        """
        converted_created_at = convert_datetime_to_date("created_at")
        sql_query = f"""SELECT
    {converted_created_at} AS date,
    COUNT(DISTINCT created_by) AS terminal_count
FROM
    workflow_runs
WHERE
    tenant_id = :tenant_id
    AND app_id = :app_id
    AND triggered_from = :triggered_from"""

        arg_dict: dict[str, Any] = {
            "tz": timezone,
            "tenant_id": tenant_id,
            "app_id": app_id,
            "triggered_from": triggered_from,
        }

        if start_date:
            sql_query += " AND created_at >= :start_date"
            arg_dict["start_date"] = start_date

        if end_date:
            sql_query += " AND created_at < :end_date"
            arg_dict["end_date"] = end_date

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []
        with self._session_maker() as session:
            rs = session.execute(sa.text(sql_query), arg_dict)
            for row in rs:
                response_data.append({"date": str(row.date), "terminal_count": row.terminal_count})

        return cast(list[DailyTerminalsStats], response_data)

    @override
    def get_daily_token_cost_statistics(
        self,
        tenant_id: str,
        app_id: str,
        triggered_from: str,
        start_date: datetime | None = None,
        end_date: datetime | None = None,
        timezone: str = "UTC",
    ) -> list[DailyTokenCostStats]:
        """
        Get daily token cost statistics using raw SQL for optimal performance.
        """
        converted_created_at = convert_datetime_to_date("created_at")
        sql_query = f"""SELECT
    {converted_created_at} AS date,
    SUM(total_tokens) AS token_count
FROM
    workflow_runs
WHERE
    tenant_id = :tenant_id
    AND app_id = :app_id
    AND triggered_from = :triggered_from"""

        arg_dict: dict[str, Any] = {
            "tz": timezone,
            "tenant_id": tenant_id,
            "app_id": app_id,
            "triggered_from": triggered_from,
        }

        if start_date:
            sql_query += " AND created_at >= :start_date"
            arg_dict["start_date"] = start_date

        if end_date:
            sql_query += " AND created_at < :end_date"
            arg_dict["end_date"] = end_date

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []
        with self._session_maker() as session:
            rs = session.execute(sa.text(sql_query), arg_dict)
            for row in rs:
                response_data.append(
                    {
                        "date": str(row.date),
                        "token_count": row.token_count,
                    }
                )

        return cast(list[DailyTokenCostStats], response_data)

    @override
    def get_average_app_interaction_statistics(
        self,
        tenant_id: str,
        app_id: str,
        triggered_from: str,
        start_date: datetime | None = None,
        end_date: datetime | None = None,
        timezone: str = "UTC",
    ) -> list[AverageInteractionStats]:
        """
        Get average app interaction statistics using raw SQL for optimal performance.
        """
        converted_created_at = convert_datetime_to_date("c.created_at")
        sql_query = f"""SELECT
    AVG(sub.interactions) AS interactions,
    sub.date
FROM
    (
        SELECT
            {converted_created_at} AS date,
            c.created_by,
            COUNT(c.id) AS interactions
        FROM
            workflow_runs c
        WHERE
            c.tenant_id = :tenant_id
            AND c.app_id = :app_id
            AND c.triggered_from = :triggered_from
            {{{{start}}}}
            {{{{end}}}}
        GROUP BY
            date, c.created_by
    ) sub
GROUP BY
    sub.date"""

        arg_dict: dict[str, Any] = {
            "tz": timezone,
            "tenant_id": tenant_id,
            "app_id": app_id,
            "triggered_from": triggered_from,
        }

        if start_date:
            sql_query = sql_query.replace("{{start}}", " AND c.created_at >= :start_date")
            arg_dict["start_date"] = start_date
        else:
            sql_query = sql_query.replace("{{start}}", "")

        if end_date:
            sql_query = sql_query.replace("{{end}}", " AND c.created_at < :end_date")
            arg_dict["end_date"] = end_date
        else:
            sql_query = sql_query.replace("{{end}}", "")

        response_data = []
        with self._session_maker() as session:
            rs = session.execute(sa.text(sql_query), arg_dict)
            for row in rs:
                response_data.append(
                    {"date": str(row.date), "interactions": float(row.interactions.quantize(Decimal("0.01")))}
                )

        return cast(list[AverageInteractionStats], response_data)

    @override
    def get_workflow_run_by_id_and_tenant_id(self, tenant_id: str, run_id: str) -> WorkflowRun | None:
        """Get a specific workflow run by its id and the associated tenant id."""
        with self._session_maker() as session:
            stmt = select(WorkflowRun).where(
                WorkflowRun.tenant_id == tenant_id,
                WorkflowRun.id == run_id,
            )
            return session.scalar(stmt)


class _PrivateWorkflowPauseEntity(WorkflowPauseEntity):
    """
    Private implementation of WorkflowPauseEntity for SQLAlchemy repository.

    This implementation is internal to the repository layer and provides
    the concrete implementation of the WorkflowPauseEntity interface.
    """

    def __init__(self, *, pause_model: WorkflowPause) -> None:
        self._pause_model = pause_model
        self._cached_state: bytes | None = None

    @property
    @override
    def id(self) -> str:
        return self._pause_model.id

    @property
    @override
    def workflow_execution_id(self) -> str:
        return self._pause_model.workflow_run_id

    @override
    def get_state(self) -> bytes:
        """
        Retrieve the serialized workflow state from storage.

        Returns:
            Mapping[str, Any]: The workflow state as a dictionary

        Raises:
            FileNotFoundError: If the state file cannot be found
            IOError: If there are issues reading the state file
            _Workflow: If the state cannot be deserialized properly
        """
        if self._cached_state is not None:
            return self._cached_state

        # Load the state from storage
        state_data = storage.load(self._pause_model.state_object_key)
        self._cached_state = state_data
        return state_data

    @property
    @override
    def resumed_at(self) -> datetime | None:
        return self._pause_model.resumed_at

    @property
    @override
    def paused_at(self) -> datetime:
        return self._pause_model.created_at