CRUD Workflow Patterns

Overview: One Workflow Per Operation

The Application Data pattern uses four dedicated workflows — one per CRUD operation. This separation of concerns means each workflow is small, focused, and independently modifiable. Shared validation logic is extracted into a sub-workflow referenced by the create and update workflows.

Create Workflow Pattern

Create Workflow — SQL Node Config

{
  "nodeType": "SqlUpdateNode",
  "nodeId": "insert-lead",
  "datasourceId": "sales-data-db",
  "command": "INSERT INTO Lead (LeadId, TenantId, FullName, CompanyName, Email, Phone, Source, Status, AssignedToUserId, EstimatedValue, Notes, FollowUpDate, CreatedBy, CreatedAt, UpdatedAt) VALUES (NEWID(), @tenantId, @fullName, @companyName, @email, @phone, @source, @status, @assignedToUserId, @estimatedValue, @notes, @followUpDate, @createdBy, GETUTCDATE(), GETUTCDATE())",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "fullName": "{{input.fullName}}",
    "companyName": "{{input.companyName}}",
    "email": "{{input.email}}",
    "phone": "{{input.phone}}",
    "source": "{{input.source}}",
    "status": "{{input.status}}",
    "assignedToUserId": "{{input.assignedToUserId}}",
    "estimatedValue": "{{input.estimatedValue}}",
    "notes": "{{input.notes}}",
    "followUpDate": "{{input.followUpDate}}",
    "createdBy": "{{workflow.userId}}"
  },
  "outputVariable": "insertResult"
}

Read Workflow Patterns

The Read operation has two variants — single record and list:

Single Record Read

{
  "nodeType": "SqlQueryNode",
  "nodeId": "read-lead",
  "datasourceId": "sales-data-db",
  "query": "SELECT LeadId, FullName, CompanyName, Email, Phone, Source, Status, AssignedToUserId, EstimatedValue, Notes, FollowUpDate, CreatedAt, UpdatedAt, ClassificationLabel FROM Lead WHERE TenantId = @tenantId AND LeadId = @leadId AND IsDeleted = 0",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "leadId": "{{input.leadId}}"
  },
  "firstRowOnly": true,
  "outputVariable": "lead"
}

Paginated List Read

{
  "nodeType": "SqlQueryNode",
  "nodeId": "list-leads",
  "datasourceId": "sales-data-db",
  "query": "SELECT LeadId, FullName, CompanyName, Email, Status, AssignedToUserId, EstimatedValue, CreatedAt FROM Lead WHERE TenantId = @tenantId AND (@status IS NULL OR Status = @status) AND (@assignedTo IS NULL OR AssignedToUserId = @assignedTo) AND IsDeleted = 0 ORDER BY UpdatedAt DESC OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "status": "{{input.statusFilter}}",
    "assignedTo": "{{input.assignedToFilter}}",
    "offset": "{{input.offset}}",
    "pageSize": "{{input.pageSize}}"
  },
  "outputVariable": "leads"
}

Update Workflow Pattern

{
  "nodeType": "SqlUpdateNode",
  "nodeId": "update-lead",
  "datasourceId": "sales-data-db",
  "command": "UPDATE Lead SET FullName = @fullName, CompanyName = @companyName, Email = @email, Phone = @phone, Source = @source, Status = @status, AssignedToUserId = @assignedToUserId, EstimatedValue = @estimatedValue, Notes = @notes, FollowUpDate = @followUpDate, UpdatedAt = GETUTCDATE() WHERE TenantId = @tenantId AND LeadId = @leadId AND IsDeleted = 0",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "leadId": "{{input.leadId}}",
    "fullName": "{{input.fullName}}",
    "email": "{{input.email}}",
    "status": "{{input.status}}"
    // ... other fields
  },
  "outputVariable": "updateResult"
}

Delete Workflow Pattern (Soft Delete)

{
  "nodeType": "SqlUpdateNode",
  "nodeId": "soft-delete-lead",
  "datasourceId": "sales-data-db",
  "command": "UPDATE Lead SET IsDeleted = 1, DeletedAt = GETUTCDATE(), UpdatedAt = GETUTCDATE() WHERE TenantId = @tenantId AND LeadId = @leadId AND IsDeleted = 0",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "leadId": "{{input.leadId}}"
  },
  "outputVariable": "deleteResult"
}
// Follow with a condition node:
// If deleteResult.rowsAffected == 0 → return 404 Not Found
// If deleteResult.rowsAffected == 1 → return 200 Success