improvement(queries): add workspaceId to execution logs, added missing indexes based on query insights

[waleedlatif1]

• improvement(queries): added missing indexes
• add workspaceId to execution logs

Summary

• add workspaceId to execution logs, added missing indexes based on query insights
• we were joining on workflows -> workspaces -> logs
• we already had workspaceId in the execution data, just had to pass it through

Type of Change

• [x] Other: Performance

Testing

Tested manually

Checklist

• [x] Code follows project style guidelines
• [x] Self-reviewed my changes
• [x] Tests added/updated and passing
• [x] No new warnings introduced
• [x] I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Review	Updated (UTC)
docs	Ready	Preview, Comment	Dec 20, 2025 5:47am

[greptile-apps[bot]]

Greptile Summary

This PR adds workspaceId directly to execution logs and creates composite indexes to optimize query performance.

Key Changes:

• Added workspace_id column to workflow_execution_logs table with a safe 6-step migration (nullable → backfill → delete orphans → NOT NULL → foreign key → indexes)
• Created composite index workspace_id + started_at on execution logs to eliminate expensive joins (previously: logs → workflows → workspaces)
• Added performance indexes: api_key(workspace_id, type), api_key(user_id, type), verification(expires_at), workflow_blocks(type)
• Propagated workspaceId through all execution entry points (API routes, webhooks, schedules, chat)
• Changed workspaceId from optional to required throughout logging infrastructure
• Updated all log queries to filter directly on workflowExecutionLogs.workspaceId instead of joining through workflow table
• Added validation to ensure workspaceId exists before execution, failing fast with clear error messages

Confidence Score: 5/5

• This PR is safe to merge - well-structured database migration with performance improvements and no logical issues
• The migration follows best practices with a safe 6-step approach. The workspaceId propagation is systematic and includes proper validation at all entry points. Query optimizations are sound and will significantly improve performance by eliminating joins. All changes maintain data integrity with foreign key constraints.
• No files require special attention

Important Files Changed

Filename	Overview
packages/db/migrations/0127_flimsy_sister_grimm.sql	Adds workspace_id column to execution logs with safe migration steps, creates composite indexes for improved query performance
packages/db/schema.ts	Adds workspaceId field and composite index to workflowExecutionLogs table, enabling direct workspace filtering
apps/sim/lib/logs/execution/logger.ts	Modified to accept and insert workspaceId directly into execution logs
apps/sim/app/api/logs/route.ts	Refactored query to filter by workspaceId directly on logs table, removed join-based workspace filtering
apps/sim/app/api/workflows/[id]/execute/route.ts	Added validation for workspaceId existence, removed fallback to empty string, passes workspaceId to logging
apps/sim/background/webhook-execution.ts	Added workspaceId validation with error logging fallback, removed empty string defaults

Sequence Diagram

sequenceDiagram
    participant Client
    participant Route as API Route<br/>(execute/webhook/schedule)
    participant Preprocess as Preprocessing
    participant LogSession as LoggingSession
    participant Logger as ExecutionLogger
    participant DB as Database<br/>(workflow_execution_logs)
    
    Client->>Route: Trigger workflow execution
    Route->>Preprocess: Validate workflow access
    Preprocess->>DB: Fetch workflow record
    DB-->>Preprocess: workflow (with workspaceId)
    Preprocess-->>Route: workflowRecord
    
    Route->>Route: Validate workspaceId exists
    alt workspaceId is null
        Route-->>Client: Error: No workspace
    end
    
    Route->>LogSession: safeStart({ workspaceId, userId })
    LogSession->>Logger: startWorkflowExecution({ workspaceId, workflowId, executionId })
    Logger->>DB: INSERT INTO workflow_execution_logs<br/>(workspace_id, workflow_id, execution_id)
    DB-->>Logger: Log entry created
    Logger-->>LogSession: Log started
    LogSession-->>Route: Session ready
    
    Route->>Route: Execute workflow
    Route-->>Client: Execution result
    
    Note over DB: New composite index:<br/>workspace_id + started_at<br/>enables fast queries