Skip to content

LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows

27.8 KB
Raw Blame
import logging
import uuid
from datetime import timedelta

from api_v2.models import APIDeployment
from django.core.exceptions import ObjectDoesNotExist
from django.db import models
from django.db.models import Q, QuerySet, Sum
from pipeline_v2.models import Pipeline
from tags.models import Tag
from tenant_account_v2.organization_member_service import OrganizationMemberService
from usage_v2.constants import UsageKeys
from usage_v2.helper import UsageHelper
from usage_v2.models import Usage
from utils.common_utils import CommonUtils
from utils.models.base_model import BaseModel, BaseModelManager
from utils.user_context import UserContext

from workflow_manager.execution.dto import ExecutionCache
from workflow_manager.execution.execution_cache_utils import ExecutionCacheUtils
from workflow_manager.workflow_v2.enums import ExecutionStatus
from workflow_manager.workflow_v2.models import Workflow

logger = logging.getLogger(__name__)


EXECUTION_ERROR_LENGTH = 256


class WorkflowExecutionManager(BaseModelManager):
    """Custom manager for WorkflowExecution model to handle user-specific filtering."""

    def for_user(self, user) -> QuerySet:
        """Filter user's workflow executions with proper access control.

        Returns executions where the user has access to:
        - The workflow AND/OR
        - The pipeline/API deployment

        This handles independent sharing scenarios:
        1. Workflow shared but not API deployment -> User can see workflow-only executions
        2. API deployment shared but not workflow -> User can see those API executions
        3. Both shared -> User can see all executions
        4. Neither shared -> User cannot see executions

        Service accounts and org admins see every execution in the current
        organization. This manager is the only org scoping on that path — the
        view drops the organization filter backend.

        Args:
            user: The user to filter executions for

        Returns:
            QuerySet of executions that the user has permission to access
        """
        if getattr(user, "is_service_account", False):
            return self._org_scoped("service account", user)

        if OrganizationMemberService.is_user_organization_admin(user):
            return self._org_scoped("org admin", user)

        # Defer to each resource's own ``for_user`` so execution visibility matches
        # the resource lists (UN-2651). Those managers scope by the org in
        # context, so call this from a request, not a worker.
        workflow_filter = Q(workflow_id__in=Workflow.objects.for_user(user).values("pk"))

        # Filter for API deployments the user can access
        api_filter = Q(pipeline_id__in=APIDeployment.objects.for_user(user).values("pk"))

        # Filter for Pipelines the user can access
        pipeline_filter = Q(pipeline_id__in=Pipeline.objects.for_user(user).values("pk"))

        # Combine deployment filters
        deployment_filter = api_filter | pipeline_filter

        # User can see executions if they have access to:
        # 1. The workflow AND execution has no pipeline (workflow-level execution)
        # 2. The pipeline/API deployment (regardless of workflow access)
        final_filter = (workflow_filter & Q(pipeline_id__isnull=True)) | deployment_filter

        return self.filter(final_filter).distinct()

    def _org_scoped(self, actor: str, user) -> QuerySet:
        """Every execution in the current organization, for the bypass roles.

        Fails closed with no organization in context, rather than returning
        every tenant's executions.
        """
        org = UserContext.get_organization()
        if org:
            return self.filter(workflow__organization=org)
        logger.warning(
            "for_user called with no organization in context (%s, user=%s); "
            "returning no executions",
            actor,
            getattr(user, "id", None),
        )
        return self.none()

    def clean_invalid_workflows(self):
        """Remove execution records with invalid workflow references.

        This is a utility method to clean up data when converting from workflow_id to
        a proper foreign key relationship. It deletes any execution records where the
        workflow reference doesn't exist in the database.

        Returns:
            int: Number of deleted records
        """
        # Find executions with no valid workflow reference
        invalid_executions = self.filter(workflow__isnull=True)

        count = invalid_executions.count()
        if count > 0:
            logger.info(
                f"Deleting {count} execution records with invalid workflow references"
            )
            invalid_executions.delete()
        return count


class WorkflowExecution(BaseModel):
    # Use the custom manager
    objects = WorkflowExecutionManager()

    class Mode(models.TextChoices):
        INSTANT = "INSTANT", "will be executed immediately"
        QUEUE = "QUEUE", "will be placed in a queue"

    class Method(models.TextChoices):
        DIRECT = "DIRECT", " Execution triggered manually"
        SCHEDULED = "SCHEDULED", "Scheduled execution"

    class Type(models.TextChoices):
        COMPLETE = "COMPLETE", "For complete execution"
        STEP = "STEP", "For step-by-step execution "

    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    # TODO: Make as foreign key to access the instance directly
    pipeline_id = models.UUIDField(
        editable=False,
        null=True,
        db_comment="ID of the associated pipeline, if applicable",
    )
    task_id = models.UUIDField(
        editable=False,
        null=True,
        db_comment="task id of asynchronous execution",
    )
    queue_message_id = models.BigIntegerField(
        editable=False,
        null=True,
        db_comment="pg_queue_message.msg_id for PG-transport executions "
        "(the queue-row handle; task_id stays NULL on the PG path)",
    )
    workflow = models.ForeignKey(
        Workflow,
        on_delete=models.CASCADE,
        editable=False,
        db_comment="Workflow to be executed",
        related_name="workflow_executions",
        null=True,
        db_column="workflow_id",  # Reuse the existing column name
    )

    execution_mode = models.CharField(
        choices=Mode.choices, db_comment="Mode of execution"
    )
    execution_method = models.CharField(
        choices=Method.choices, db_comment="Method of execution"
    )
    execution_type = models.CharField(
        choices=Type.choices, db_comment="Type of execution"
    )
    execution_log_id = models.CharField(
        default="", editable=False, db_comment="Execution log events Id"
    )
    status = models.CharField(
        choices=ExecutionStatus.choices,
        db_comment="Current status of the execution",
    )
    result_acknowledged = models.BooleanField(
        default=False,
        db_comment=(
            "To track if result is acknowledged by user - used mainly by API deployments"
        ),
    )
    total_files = models.PositiveIntegerField(
        default=0, verbose_name="Total files", db_comment="Number of files to process"
    )
    successful_files = models.PositiveIntegerField(
        null=True,
        blank=True,
        db_comment=(
            "Per-run aggregate of files that completed successfully. Written by "
            "the worker callback at terminal state. Null on rows created before "
            "this column was added."
        ),
    )
    failed_files = models.PositiveIntegerField(
        null=True,
        blank=True,
        db_comment=(
            "Per-run aggregate of files that errored. Written by the worker "
            "callback at terminal state. Null on rows created before this "
            "column was added."
        ),
    )
    error_message = models.CharField(
        max_length=EXECUTION_ERROR_LENGTH,
        blank=True,
        default="",
        db_comment="Details of encountered errors",
    )
    attempts = models.IntegerField(default=0, db_comment="number of attempts taken")
    dispatched_at = models.DateTimeField(
        null=True,
        blank=True,
        db_comment=(
            "Set when the orchestrator task was handed to its transport. NULL means "
            "never dispatched. The POSITIVE fact the undispatched sweep needs: absent "
            "task_id/queue_message_id also occurs for executions that ARE running, "
            "because three paths in workflow_helper return without recording a handle "
            "after the message is already enqueued."
        ),
    )
    execution_time = models.FloatField(default=0, db_comment="execution time in seconds")
    tags = models.ManyToManyField(Tag, related_name="workflow_executions", blank=True)

    class Meta:
        verbose_name = "Workflow Execution"
        verbose_name_plural = "Workflow Executions"
        db_table = "workflow_execution"
        indexes = [
            models.Index(fields=["workflow_id", "-created_at"]),
            models.Index(fields=["pipeline_id", "-created_at"]),
            # Partial index over ACTIVE (non-terminal) executions only — see
            # migration 0023. Keeps the hot "is there an in-flight execution of
            # this workflow?" lookup (WHERE workflow_id=… AND status IN active)
            # O(active) instead of O(all-history-for-workflow). The predicate is
            # the COMPLEMENT of the terminal set: the *index* stays usable if a new
            # *active* status is added, but the callers use a frozen positive
            # status IN ('PENDING','EXECUTING') list — so a new active status is
            # indexed yet not returned until every caller's list is updated too.
            # Only a new *terminal* status needs this predicate updated; the literal
            # is kept in sync with ExecutionStatus.terminal_values() by
            # tests/test_active_execution_index.py.
            models.Index(
                fields=["workflow_id"],
                name="we_active_by_workflow_idx",
                condition=~Q(status__in=["COMPLETED", "STOPPED", "ERROR"]),
            ),
            # Partial index over UNDISPATCHED executions — see migration 0026.
            # Serves the undispatched-execution sweep (undispatched_sweep.py), which
            # terminalises rows left PENDING because the request died between
            # create_workflow_execution and execute_workflow_async. Effectively EMPTY
            # in steady state: an execution leaves PENDING within seconds, and one of
            # task_id / queue_message_id is stamped the moment dispatch succeeds — so
            # it costs almost nothing and only grows when something is wrong.
            # we_active_by_workflow_idx above is *usable* for the same predicate
            # (PENDING implies NOT IN terminal) but is keyed on workflow_id, which the
            # sweep does not filter on — so without this the sweep falls back to a full
            # scan of that index. Literals are frozen in the migration and tied to
            # ExecutionStatus by tests/test_undispatched_execution_index.py.
            models.Index(
                fields=["created_at"],
                name="we_undispatched_dispatch_idx",
                condition=Q(
                    status="PENDING",
                    dispatched_at__isnull=True,
                    task_id__isnull=True,
                    queue_message_id__isnull=True,
                ),
            ),
            # Bare created_at range scans, which no index above serves: the two that
            # are keyed on created_at are partial, so neither covers the full range.
            # See migration 0029.
            models.Index(fields=["created_at"], name="we_created_at_idx"),
        ]

    @property
    def tag_names(self) -> list[str]:
        """Return a list of tag names associated with the workflow execution."""
        return list(self.tags.values_list("name", flat=True))

    @property
    def workflow_name(self) -> str | None:
        """Obtains the workflow's name associated to this execution."""
        if self.workflow:
            return self.workflow.workflow_name
        return None

    @property
    def pipeline_name(self) -> str | None:
        """Obtains the pipeline's name associated to this execution.
        It could be ETL / TASK / API pipeline, None returned if there's no such pipeline
        """
        if not self.pipeline_id:
            return None

        try:
            return APIDeployment.objects.get(id=self.pipeline_id).display_name
        except ObjectDoesNotExist:
            pass

        try:
            return Pipeline.objects.get(id=self.pipeline_id).pipeline_name
        except ObjectDoesNotExist:
            pass

        return None

    @property
    def pretty_execution_time(self) -> str:
        """Convert execution_time from seconds to HH:MM:SS format

        Returns:
            str: Time in HH:MM:SS format
        """
        # Compute execution time for a run that's in progress
        time_in_secs = (
            self.execution_time
            if self.execution_time
            else CommonUtils.time_since(self.created_at)
        )
        return str(timedelta(seconds=time_in_secs)).split(".")[0]

    @property
    def aggregated_usage_cost(self) -> float | None:
        """Retrieve aggregated cost for the given execution_id.

        Returns:
        Optional[float]: The total cost in dollars if available, else None.

        Raises:
            APIException: For unexpected errors during database operations.
        """
        # Aggregate the cost for the given execution_id
        queryset = Usage.objects.filter(execution_id=self.id)

        if queryset.exists():
            result = queryset.aggregate(cost_in_dollars=Sum(UsageKeys.COST_IN_DOLLARS))
            total_cost = result.get(UsageKeys.COST_IN_DOLLARS)
        else:
            # Handle the case where no usage data is found for the given execution_id
            logger.warning(
                f"Usage data not found for the specified execution_id: {self.id}"
            )
            return None

        logger.debug(
            f"Cost aggregated successfully for execution_id: {self.id}"
            f", Total cost: {total_cost}"
        )

        return total_cost

    @property
    def aggregated_total_pages_processed(self) -> int | None:
        """Retrieve aggregated total pages processed for this execution.

        Returns:
            int | None: Total pages processed across all file executions,
            or None if no page usage data exists.
        """
        file_execution_ids = list(self.file_executions.values_list("id", flat=True))
        if not file_execution_ids:
            return None

        return UsageHelper.get_aggregated_pages_processed(
            run_ids=[str(fid) for fid in file_execution_ids]
        )

    @property
    def is_completed(self) -> bool:
        return ExecutionStatus.is_completed(self.status)

    @property
    def organization_id(self) -> str | None:
        """Get the organization ID from the associated workflow."""
        if (
            self.workflow
            and hasattr(self.workflow, "organization")
            and self.workflow.organization
        ):
            return str(self.workflow.organization.organization_id)
        return None

    def __str__(self) -> str:
        return (
            f"Workflow execution: {self.id} ("
            f"pipeline ID: {self.pipeline_id}, "
            f"workflow: {self.workflow}, "
            f"status: {self.status}, "
            f"files: {self.total_files}, "
            f"error message: {self.error_message})"
        )

    def update_execution(
        self,
        status: ExecutionStatus | None = None,
        error: str | None = None,
        increment_attempt: bool = False,
        *,
        row_locked: bool = False,
    ) -> None:
        """Update the execution status and related fields.

        Args:
            status (Optional[ExecutionStatus], optional): New execution status. Defaults to None.
            error (Optional[str], optional): Error message if any. Defaults to None.
            increment_attempt (bool, optional): Whether to increment attempt counter. Defaults to False.
            row_locked (bool, optional): True only when THIS instance was itself
                read with ``select_for_update()`` (either mode) in the current, still-open
                transaction. Its ``queue_message_id`` / ``status`` / ``attempts``
                are then the persisted values under the lock, so the second
                ``SELECT ... FOR NO KEY UPDATE`` round-trip is skipped. Passing it
                for an instance read without the lock reintroduces the
                stale-routing race described below.
        """
        from django.db import connection, transaction

        if row_locked and not connection.in_atomic_block:
            # Outside a transaction the caller's lock was already released when its
            # statement autocommitted, so the instance proves nothing.
            raise RuntimeError(
                "update_execution(row_locked=True) requires the caller's open "
                "transaction that holds the row lock"
            )

        should_release_rate_limit = False
        with transaction.atomic():
            # Lock the row and read the PERSISTED marker + status together, so routing
            # AND the write are atomic. Never trust the (possibly stale) in-memory
            # self.queue_message_id — unless the caller passes row_locked=True, i.e.
            # this instance was itself read under the lock (see the docstring): a PG row snapshotted before dispatch recorded its
            # handle, mis-routed to an unlocked legacy full save(), would write NULL
            # back into queue_message_id and permanently disable every guard. Under
            # this lock a concurrent _record_dispatch_handle serializes after us.
            #
            # no_key=True takes FOR NO KEY UPDATE rather than FOR UPDATE. Status
            # writers still exclude each other (the mode conflicts with itself), but
            # it no longer conflicts with the FOR KEY SHARE that every child-row
            # INSERT takes on this row for its foreign-key check — so file-execution
            # inserts and log writes for this execution stop queueing behind a
            # status update. Nothing here changes the primary key, which is the only
            # thing FOR KEY SHARE protects.
            if row_locked:
                locked = {
                    "queue_message_id": self.queue_message_id,
                    "status": self.status,
                    "attempts": self.attempts,
                }
            else:
                locked = (
                    WorkflowExecution.objects.select_for_update(no_key=True)
                    .filter(pk=self.pk)
                    .values("queue_message_id", "status", "attempts")
                    .first()
                )
            if locked is None:
                return
            if locked["queue_message_id"] is None:
                should_release_rate_limit = self._apply_legacy_update(
                    status, error, increment_attempt
                )
            else:
                should_release_rate_limit = self._apply_pg_guarded_update(
                    locked, status, error, increment_attempt
                )
        if should_release_rate_limit and self.pipeline_id:
            # Release the slot only once the status write is DURABLE. update_execution()
            # has its own atomic(), but callers (update_status, the PG reaper's
            # _recover_one_stuck_pg_execution) wrap it in an OUTER transaction with
            # further writes (file aggregates, cascade). Firing the Redis release inline
            # would free the rate-limit slot even if that outer txn later rolls back —
            # freed slot + un-persisted status. transaction.on_commit fires on the
            # OUTERMOST commit and is dropped on rollback; in autocommit (no surrounding
            # txn) it runs immediately. So the happy path is unchanged for both
            # transports, only the rollback leak is closed.
            transaction.on_commit(self._release_api_deployment_rate_limit)
        if status is not None:
            # On commit. Callers must write the file counts in the same
            # transaction, or the callback sees stale counts.
            from inbox.events import notify_on_terminal_status

            notify_on_terminal_status(self.pk, status)

    def _apply_legacy_update(
        self,
        status: ExecutionStatus | None,
        error: str | None,
        increment_attempt: bool,
    ) -> bool:
        """Celery-path (queue_message_id NULL) — unchanged full save(); runs inside the
        caller's locked transaction. Returns whether to release the rate-limit slot.
        """
        should_release = False
        if status is not None:
            status = ExecutionStatus(status)
            self.status = status.value
            if status in [
                ExecutionStatus.COMPLETED,
                ExecutionStatus.ERROR,
                ExecutionStatus.STOPPED,
            ]:
                self.execution_time = CommonUtils.time_since(self.created_at, 3)
                should_release = True
        if error:
            self.error_message = error[:EXECUTION_ERROR_LENGTH]
        if increment_attempt:
            self.attempts += 1
        self.save()
        return should_release

    def _apply_pg_guarded_update(
        self,
        locked: dict,
        status: ExecutionStatus | None,
        error: str | None,
        increment_attempt: bool,
    ) -> bool:
        """PG-path terminal-one-way guarded write, inside the caller's row lock. Refuses
        a revert of a protected-terminal (COMPLETED/STOPPED) status — ERROR stays
        correctable — while still applying error / increment_attempt. Field-scoped
        (``update_fields``) so a stale object can never clobber counters or the marker,
        and the post-save cache publishes the status actually persisted. Returns whether
        to release the rate-limit slot.
        """
        committed = locked["status"]
        should_release = False
        update_fields: list[str] = []
        if status is not None:
            status_fields, should_release = self._apply_guarded_status(
                ExecutionStatus(status), committed, error, increment_attempt
            )
            update_fields.extend(status_fields)
        if error:
            self.error_message = error[:EXECUTION_ERROR_LENGTH]
            update_fields.append("error_message")
        if increment_attempt:
            # Increment from the locked committed value, not the stale in-memory one.
            self.attempts = locked["attempts"] + 1
            update_fields.append("attempts")
        if update_fields:
            update_fields.append("modified_at")
            self.save(update_fields=update_fields)
        return should_release

    def _apply_guarded_status(
        self,
        status: ExecutionStatus,
        committed: str,
        error: str | None,
        increment_attempt: bool,
    ) -> tuple[list[str], bool]:
        """Apply the status change under the terminal-one-way guard.

        Returns ``(update_fields, should_release_rate_limit)``. On a refused revert of
        a protected-terminal (COMPLETED/STOPPED) row, keeps ``self.status == committed``
        and returns ``([], False)`` — the caller still applies error / increment.
        """
        if (
            committed in ExecutionStatus.protected_terminal_values()
            and status.value != committed
        ):
            logger.warning(
                "update_execution: refusing to revert protected status '%s' with "
                "'%s' for execution %s (stale-writer guard); error=%s "
                "increment_attempt=%s still applied",
                committed,
                status.value,
                self.id,
                bool(error),
                increment_attempt,
            )
            self.status = committed  # keep self + post-save cache consistent with DB
            return [], False

        self.status = status.value
        fields = ["status"]
        should_release = False
        if status in [
            ExecutionStatus.COMPLETED,
            ExecutionStatus.ERROR,
            ExecutionStatus.STOPPED,
        ]:
            self.execution_time = CommonUtils.time_since(self.created_at, 3)
            fields.append("execution_time")
            should_release = True
        return fields, should_release

    def _release_api_deployment_rate_limit(self) -> None:
        """Release rate limit slot for API deployment executions.

        Checks if this execution is for an API deployment and releases
        the rate limit slot if applicable.
        """
        try:
            # Check if this is an API deployment execution
            api_deployment = APIDeployment.objects.filter(id=self.pipeline_id).first()
            if api_deployment and api_deployment.organization:
                from api_v2.rate_limiter import APIDeploymentRateLimiter

                APIDeploymentRateLimiter.release_slot(
                    str(api_deployment.organization.organization_id), str(self.id)
                )
        except Exception as e:
            # Log but don't fail the execution update for rate limit release errors
            logger.exception(
                f"Failed to release rate limit slot for execution {self.id}: {e}"
            )

    def update_execution_err(self, err_msg: str = "") -> None:
        """Update execution status to ERROR with an error message.

        Args:
            err_msg (str, optional): Error message to store. Defaults to "".
        """
        self.update_execution(status=ExecutionStatus.ERROR, error=err_msg)

    def _handle_execution_cache(self):
        if not ExecutionCacheUtils.is_execution_exists(
            workflow_id=self.workflow.id, execution_id=self.id
        ):
            execution_cache = ExecutionCache(
                workflow_id=self.workflow.id,
                execution_id=self.id,
                total_files=self.total_files,
                status=self.status,
            )
            ExecutionCacheUtils.create_execution(
                execution=execution_cache,
            )
        else:
            ExecutionCacheUtils.update_status(
                workflow_id=self.workflow.id, execution_id=self.id, status=self.status
            )

    def save(self, *args, **kwargs):
        super().save(*args, **kwargs)
        self._handle_execution_cache()

    @classmethod
    def get_last_run_statuses(cls, pipeline_id: uuid.UUID, limit: int = 5) -> list[dict]:
        """Fetch the last N execution statuses for a pipeline.

        Computes PARTIAL_SUCCESS dynamically when execution completed but has
        both successful and failed files.

        Args:
            pipeline_id: UUID of the pipeline (ETL or API deployment)
            limit: Number of recent executions to fetch (default 5)

        Returns:
            List of dicts with execution_id, status, timestamp, and file counts.
            Ordered oldest to newest (for left-to-right timeline display).
        """
        executions = cls.objects.filter(pipeline_id=pipeline_id).order_by("-created_at")[
            :limit
        ]

        result = []
        for e in executions:
            successful = e.successful_files or 0
            failed = e.failed_files or 0

            # Compute display_status: PARTIAL_SUCCESS if completed with mixed results
            display_status = e.status
            if e.status == "COMPLETED" and failed > 0 and successful > 0:
                display_status = "PARTIAL_SUCCESS"

            result.append(
                {
                    "execution_id": str(e.id),
                    "status": display_status,
                    "timestamp": e.created_at.isoformat() if e.created_at else None,
                    "successful_files": successful,
                    "failed_files": failed,
                }
            )

        # Reverse to get oldest first (left-to-right timeline)
        return list(reversed(result))