Skip to content
TheChatArchiveThe Chat Archive
gpt-4.1-mini-2025-04-14· OpenAIAi2 WildChat-4.8M (AI2)

implement this : 10:05:15:456 - Event (Add to cart) SMS Node - Delay - 1 hr (60 min) SMS…

  1. User

    implement this : 10:05:15:456 - Event (Add to cart) SMS Node - Delay - 1 hr (60 min)

    SMS Trigger - 11:05

    09:30 cron

    start time: 08:30:00 end time: 08:30:59

    11:05 cron

    start time: 10:05:00 end time: 10:05:59 ---

    package cubejs

    import ( "encoding/json" "fmt" "time"

    "omni-data-fetch-cron/model"
    

    )

    type QueryBuilder interface { Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) }

    type queryBuilder struct{}

    func NewQueryBuilder() QueryBuilder { return &queryBuilder{} }

    func (q *queryBuilder) Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) { var ( channelFilters []model.CubeQueryFilter actionFilters []model.CubeQueryFilter err error )

    if channel.ConditionQuery != nil {
    	channelFilters, err = parseConditionQuery(*channel.ConditionQuery)
    	if err != nil {
    		return model.CubeQuery{}, fmt.Errorf("parse channel condition query: %w", err)
    	}
    }
    
    if action.ConditionQuery != "" {
    	actionFilters, err = parseConditionQuery(action.ConditionQuery)
    	if err != nil {
    		return model.CubeQuery{}, fmt.Errorf("parse action condition query: %w", err)
    	}
    }
    
    filters := append(channelFilters, actionFilters...)
    
    
    filters = append(filters, []model.CubeQueryFilter{
    	{
    		Member:   "AttributionLogs.clientId",
    		Operator: "equals",
    		Values:   []string{fmt.Sprintf("%d", channel.ClientID)},
    	},
    	{
    		Member:   "AttributionLogs.workspaceId",
    		Operator: "equals",
    		Values:   []string{fmt.Sprintf("%d", channel.WorkspaceID)},
    	},
    }...)
    
    delay := time.Duration(*channel.Delay) * time.Minute
    startTime := cronStartTime.Add(-delay).UTC().Format(time.RFC3339)
    endTime := cronStartTime.UTC().Format(time.RFC3339)
    
    return model.CubeQuery{
    	Query: model.CubeQueryPayload{
    		Dimensions: []string{},
    		Measures: []string{"AttributionLogs.count"},
    		Filters:  filters,
    		Order: map[string]string{
    			 "AttributionLogs.eventCount": "desc",
    		},
    		TimeDimensions: []model.TimeDimension{
    			{
    	Dimension: "AttributionLogs.timestamp",
    	DateRange: []string{startTime, endTime},
    			},
    		},
    	},
    }, nil
    

    }

    func parseConditionQuery(raw string) ([]model.CubeQueryFilter, error) { if raw == "" { return nil, nil }

    var filters []model.CubeQueryFilter
    if err := json.Unmarshal([]byte(raw), &filters); err != nil {
    	return nil, err
    }
    return filters, nil
    

    }

  2. Assistant

    Your code snippet implements a Cube query builder in Go, which creates a query with a time range that considers a delay (in minutes) from the channel configuration, anchored to the cronStartTime parameter.

    The scenario you described is:

    • Event Add to cart happens at 10:05:15:456
    • You want to send an SMS after a delay of 1 hour (60 minutes)
    • Cron jobs run at 09:30 and 11:05 (and their respective start/end time windows)
    • Start/end time windows shown for crons like 08:30:00 to 08:30:59, 10:05:00 to 10:05:59 etc.

    Understanding Your Scenario vs Code

    Your code calculates the query time range as:

    startTime = cronStartTime - delay
    endTime = cronStartTime
    

    For example, if cronStartTime = 11:05, delay = 60 mins, then:

    startTime = 11:05 - 60min = 10:05
    endTime = 11:05
    

    This time window queries for events from 10:05 to 11:05, which fits your example perfectly — you trigger an SMS at 11:05 based on the event that happened at 10:05.


    Suggested improvements:

    1. Clarify usage of start/end time for cron consistency

    The cron jobs have their own start/end time window to query a 1-minute interval. Your Cube query is set with a wider time range (60 mins window, considering delay). I suggest you explicitly integrate the cron window (like 10:05:00 to 10:05:59) to build your query time ranges for filtering the event records exactly in that corresponding minute.

    2. Example usage:

    • At 11:05 cron, you search for events in 10:05:00 - 10:05:59 (which is startTime = cronStartTime - delay)
    • Your Cube query time range should be for that 1 minute window, not the full 60 minutes.

    Code to adjust query time range to exactly that 1 min interval (your cron window):

    func (q *queryBuilder) Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) {
        var (
            channelFilters []model.CubeQueryFilter
            actionFilters  []model.CubeQueryFilter
            err            error
        )
    
        if channel.ConditionQuery != nil {
            channelFilters, err = parseConditionQuery(*channel.ConditionQuery)
            if err != nil {
                return model.CubeQuery{}, fmt.Errorf("parse channel condition query: %w", err)
            }
        }
    
        if action.ConditionQuery != "" {
            actionFilters, err = parseConditionQuery(action.ConditionQuery)
            if err != nil {
                return model.CubeQuery{}, fmt.Errorf("parse action condition query: %w", err)
            }
        }
    
        filters := append(channelFilters, actionFilters...)
    
        filters = append(filters, []model.CubeQueryFilter{
            {
                Member:   "AttributionLogs.clientId",
                Operator: "equals",
                Values:   []string{fmt.Sprintf("%d", channel.ClientID)},
            },
            {
                Member:   "AttributionLogs.workspaceId",
                Operator: "equals",
                Values:   []string{fmt.Sprintf("%d", channel.WorkspaceID)},
            },
        }...)
    
        delay := time.Duration(*channel.Delay) * time.Minute
        smsTriggerStart := cronStartTime.Add(-delay).UTC()
        smsTriggerEnd := smsTriggerStart.Add(time.Minute).UTC() // 1 minute window
    
        startTime := smsTriggerStart.Format(time.RFC3339)
        endTime := smsTriggerEnd.Format(time.RFC3339)
    
        return model.CubeQuery{
            Query: model.CubeQueryPayload{
                Dimensions: []string{},
                Measures:   []string{"AttributionLogs.count"},
                Filters:    filters,
                Order: map[string]string{
                    "AttributionLogs.eventCount": "desc",
                },
                TimeDimensions: []model.TimeDimension{
                    {
                        Dimension: "AttributionLogs.timestamp",
                        DateRange: []string{startTime, endTime},
                    },
                },
            },
        }, nil
    }
    

    Explanation:

    • cronStartTime — the actual run time of the cron (e.g., 11:05:00)
    • delay — delay in minutes from the channel configuration (e.g., 60 mins)
    • smsTriggerStart = cronStartTime - delay (e.g., 11:05 - 60mins = 10:05)
    • smsTriggerEnd = smsTriggerStart + 1 minute (e.g., 10:06)
    • Thus you query exactly for events in the cron-window of 10:05 – 10:05:59 (to match your event timestamp of 10:05:15)

    This matches the SMS trigger time and delay logic exactly.


    Let me know if you want me to add unit tests or additional logic for handling the Add to cart event or for cron scheduling!

  3. User

    make a main file to test a query building

  4. Assistant

    Certainly! Below is a simple main.go file that demonstrates how to use your cubejs package's NewQueryBuilder and Build method to build a Cube query with a sample ChannelNode, ActionNode, and a sample cron start time.


    Assumptions:

    • You have your model package already defined with the structures used (ChannelNode, ActionNode, CubeQuery, CubeQueryFilter, TimeDimension, etc.).
    • Your cubejs package is correctly located and imported in your project.

    main.go

    package main
    
    import (
    	"encoding/json"
    	"fmt"
    	"time"
    
    	"yourmodule/cubejs"    // replace with your actual module path
    	"yourmodule/model"     // replace with your actual module path
    )
    
    func main() {
    	// Create dummy channel and action nodes for testing
    	delay := 60 // 60 minutes delay for SMS trigger
    
    	channel := model.ChannelNode{
    		ClientID:    123,
    		WorkspaceID: 456,
    		Delay:       &delay,
    		// Example condition query: filter for specific property, JSON string format
    		ConditionQuery: nil, // or &channelConditionQuery, see below
    	}
    
    	// Example condition query: only events with name "Add to cart"
    	actionConditionJSON := `[{"member":"AttributionLogs.eventName","operator":"equals","values":["Add to cart"]}]`
    	action := model.ActionNode{
    		ConditionQuery: actionConditionJSON,
    	}
    
    	// Simulate the cron start time at 11:05:00 (time when SMS is triggered)
    	cronTimeStr := "2024-06-12T11:05:00Z"
    	cronStartTime, err := time.Parse(time.RFC3339, cronTimeStr)
    	if err != nil {
    		panic(fmt.Errorf("failed to parse cron start time: %w", err))
    	}
    
    	qb := cubejs.NewQueryBuilder()
    
    	// Build Cube query
    	query, err := qb.Build(channel, action, 0, 100, cronStartTime)
    	if err != nil {
    		panic(fmt.Errorf("failed to build query: %w", err))
    	}
    
    	// Print the generated query structure
    	prettyJSON, _ := json.MarshalIndent(query, "", "  ")
    	fmt.Println("Generated Cube Query:")
    	fmt.Println(string(prettyJSON))
    }
    

    What this example does:

    • Creates a channel node with a 60-minute delay.
    • Creates an action node with a filter condition targeting "Add to cart" event.
    • Sets the cron start time to 2024-06-12T11:05:00Z as in your example.
    • Calls your Build method to get the query.
    • Marshals the query struct into pretty JSON and prints it.

    Sample output would look like:

    {
      "Query": {
        "Dimensions": [],
        "Measures": [
          "AttributionLogs.count"
        ],
        "Filters": [
          {
            "Member": "AttributionLogs.eventName", // from action.ConditionQuery
            "Operator": "equals",
            "Values": [
              "Add to cart"
            ]
          },
          {
            "Member": "AttributionLogs.clientId",
            "Operator": "equals",
            "Values": [
              "123"
            ]
          },
          {
            "Member": "AttributionLogs.workspaceId",
            "Operator": "equals",
            "Values": [
              "456"
            ]
          }
        ],
        "Order": {
          "AttributionLogs.eventCount": "desc"
        },
        "TimeDimensions": [
          {
            "Dimension": "AttributionLogs.timestamp",
            "DateRange": [
              "2024-06-12T10:05:00Z",
              "2024-06-12T10:06:00Z"
            ]
          }
        ]
      }
    }
    

    Notes

    • Replace "yourmodule/cubejs" and "yourmodule/model" with your actual Go module import paths.
    • You can add additional fields or custom logic depending on your real model.

    Let me know if you want me to help with model struct definitions or runnable full example!

  5. User

    did not met withour expectations

  6. Assistant

    Thanks for the feedback! Let me provide a complete minimal working example including:

    1. Minimal model package structs.
    2. Your cubejs package code (simplified).
    3. A main.go file that compiles and runs successfully.
    4. Prints the resulting Cube query JSON to confirm the correct time range and filters.

    1. model/model.go

    package model
    
    type CubeQueryFilter struct {
    	Member   string   `json:"Member"`
    	Operator string   `json:"Operator"`
    	Values   []string `json:"Values"`
    }
    
    type TimeDimension struct {
    	Dimension string   `json:"Dimension"`
    	DateRange []string `json:"DateRange"`
    }
    
    type CubeQueryPayload struct {
    	Dimensions     []string          `json:"Dimensions"`
    	Measures       []string          `json:"Measures"`
    	Filters        []CubeQueryFilter `json:"Filters"`
    	Order          map[string]string `json:"Order"`
    	TimeDimensions []TimeDimension   `json:"TimeDimensions"`
    }
    
    type CubeQuery struct {
    	Query CubeQueryPayload `json:"Query"`
    }
    
    // ChannelNode example similar to your code
    type ChannelNode struct {
    	ClientID       int
    	WorkspaceID    int
    	Delay          *int    // minutes delay
    	ConditionQuery *string // JSON string of condition query filters (optional)
    }
    
    // ActionNode example similar to your code
    type ActionNode struct {
    	ConditionQuery string
    }
    

    2. cubejs/cubejs.go

    Place your provided code, but adjusted to this minimal model package and improved Build method with 1-min window time range.

    package cubejs
    
    import (
    	"encoding/json"
    	"fmt"
    	"time"
    
    	"yourmodule/model"
    )
    
    type QueryBuilder interface {
    	Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error)
    }
    
    type queryBuilder struct{}
    
    func NewQueryBuilder() QueryBuilder {
    	return &queryBuilder{}
    }
    
    func (q *queryBuilder) Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) {
    	var (
    		channelFilters []model.CubeQueryFilter
    		actionFilters  []model.CubeQueryFilter
    		err            error
    	)
    
    	if channel.ConditionQuery != nil && *channel.ConditionQuery != "" {
    		channelFilters, err = parseConditionQuery(*channel.ConditionQuery)
    		if err != nil {
    			return model.CubeQuery{}, fmt.Errorf("parse channel condition query: %w", err)
    		}
    	}
    
    	if action.ConditionQuery != "" {
    		actionFilters, err = parseConditionQuery(action.ConditionQuery)
    		if err != nil {
    			return model.CubeQuery{}, fmt.Errorf("parse action condition query: %w", err)
    		}
    	}
    
    	filters := append(channelFilters, actionFilters...)
    
    	filters = append(filters, []model.CubeQueryFilter{
    		{
    			Member:   "AttributionLogs.clientId",
    			Operator: "equals",
    			Values:   []string{fmt.Sprintf("%d", channel.ClientID)},
    		},
    		{
    			Member:   "AttributionLogs.workspaceId",
    			Operator: "equals",
    			Values:   []string{fmt.Sprintf("%d", channel.WorkspaceID)},
    		},
    	}...)
    
    	delay := 0
    	if channel.Delay != nil {
    		delay = *channel.Delay
    	}
    	// Calculate the start and end of the 1-minute window for event filtering
    	eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC()
    	eventEndTime := eventStartTime.Add(1 * time.Minute).UTC()
    
    	startTime := eventStartTime.Format(time.RFC3339)
    	endTime := eventEndTime.Format(time.RFC3339)
    
    	return model.CubeQuery{
    		Query: model.CubeQueryPayload{
    			Dimensions: []string{},
    			Measures:   []string{"AttributionLogs.count"},
    			Filters:    filters,
    			Order: map[string]string{
    				"AttributionLogs.eventCount": "desc",
    			},
    			TimeDimensions: []model.TimeDimension{
    				{
    					Dimension: "AttributionLogs.timestamp",
    					DateRange: []string{startTime, endTime},
    				},
    			},
    		},
    	}, nil
    }
    
    func parseConditionQuery(raw string) ([]model.CubeQueryFilter, error) {
    	if raw == "" {
    		return nil, nil
    	}
    
    	var filters []model.CubeQueryFilter
    	err := json.Unmarshal([]byte(raw), &filters)
    	return filters, err
    }
    

    3. main.go

    package main
    
    import (
    	"encoding/json"
    	"fmt"
    	"time"
    
    	"yourmodule/cubejs" // replace with your module path
    	"yourmodule/model"
    )
    
    func main() {
    	// Delay in minutes - 60 minutes = 1 hour
    	delay := 60
    
    	channel := model.ChannelNode{
    		ClientID:       123,
    		WorkspaceID:    456,
    		Delay:          &delay,
    		ConditionQuery: nil, // no channel-level condition filters for this example
    	}
    
    	// Add to cart event condition filter to match your message
    	actionQuery := `[{"Member":"AttributionLogs.eventName","Operator":"equals","Values":["Add to cart"]}]`
    	action := model.ActionNode{
    		ConditionQuery: actionQuery,
    	}
    
    	// Cron time when SMS trigger happens at 11:05 UTC
    	cronTime, err := time.Parse(time.RFC3339, "2024-06-12T11:05:00Z")
    	if err != nil {
    		panic(err)
    	}
    
    	builder := cubejs.NewQueryBuilder()
    
    	query, err := builder.Build(channel, action, 0, 100, cronTime)
    	if err != nil {
    		panic(err)
    	}
    
    	// Print as formatted JSON
    	prettyJSON, err := json.MarshalIndent(query, "", "  ")
    	if err != nil {
    		panic(err)
    	}
    
    	fmt.Println("Generated Cube Query:")
    	fmt.Println(string(prettyJSON))
    }
    

    4. Run and check output

    If you replace "yourmodule/cubejs" and "yourmodule/model" with your actual module path(s), save the files and run:

    go run main.go
    

    Expected output:

    Generated Cube Query:
    {
      "Query": {
        "Dimensions": [],
        "Measures": [
          "AttributionLogs.count"
        ],
        "Filters": [
          {
            "Member": "AttributionLogs.eventName",
            "Operator": "equals",
            "Values": [
              "Add to cart"
            ]
          },
          {
            "Member": "AttributionLogs.clientId",
            "Operator": "equals",
            "Values": [
              "123"
            ]
          },
          {
            "Member": "AttributionLogs.workspaceId",
            "Operator": "equals",
            "Values": [
              "456"
            ]
          }
        ],
        "Order": {
          "AttributionLogs.eventCount": "desc"
        },
        "TimeDimensions": [
          {
            "Dimension": "AttributionLogs.timestamp",
            "DateRange": [
              "2024-06-12T10:05:00Z",
              "2024-06-12T10:06:00Z"
            ]
          }
        ]
      }
    }
    

    Summary

    • The TimeDimensions.DateRange corresponds exactly to the event minute window 60 minutes before your 11:05 SMS trigger: from 10:05:00 to 10:06:00 UTC.
    • Filters apply as expected (client, workspace, and the event name "Add to cart").
    • You have a working runnable program that prints the constructed query.

    If you want, I can help prepare a zipped project or a GitHub repo example too. Just let me know!

  7. User

    can we make time range in this format : "2025-02-27 16:37:00", "2025-02-27 16:37:59"

  8. Assistant

    Yes, you can absolutely format the time range like that instead of RFC3339 — just format your timestamps using time.Format with the layout "2006-01-02 15:04:05" and then adjust the end time's seconds to 59 (instead of a full 1-minute ahead).


    How to do that:

    1. Format start time as "YYYY-MM-DD HH:mm:ss" (e.g. "2025-02-27 16:37:00").
    2. Format end time as "YYYY-MM-DD HH:mm:ss" but setting seconds to 59 (e.g. "2025-02-27 16:37:59").

    Adjusting the Build method snippet:

    Replace this part in your Build method:

    eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC()
    eventEndTime := eventStartTime.Add(1 * time.Minute).UTC()
    
    startTime := eventStartTime.Format(time.RFC3339)
    endTime := eventEndTime.Format(time.RFC3339)
    

    with something like this:

    // Calculate start time at beginning of the minute (seconds = 0)
    eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC()
    eventStartTime = eventStartTime.Truncate(time.Minute)
    
    // Calculate end time with seconds = 59 in same minute
    eventEndTime := eventStartTime.Add(59 * time.Second)
    
    // Format times in "YYYY-MM-DD HH:mm:ss"
    format := "2006-01-02 15:04:05"
    startTime := eventStartTime.Format(format)
    endTime := eventEndTime.Format(format)
    

    Full relevant part:

    eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC()
    eventStartTime = eventStartTime.Truncate(time.Minute)
    
    eventEndTime := eventStartTime.Add(59 * time.Second)
    
    format := "2006-01-02 15:04:05"
    startTime := eventStartTime.Format(format)
    endTime := eventEndTime.Format(format)
    

    Result example:

    If cronStartTime = "2025-02-27T16:37:00Z" and delay = 0:

    "DateRange": [
      "2025-02-27 16:37:00",
      "2025-02-27 16:37:59"
    ]
    

    Note: Make sure your Cube backend supports this datetime string format. Sometimes APIs expect ISO 8601 / RFC3339 timestamps with time zone info, but if your Cube backend is known to accept and parse "YYYY-MM-DD HH:mm:ss" strings, this is perfectly fine.


    If you want, I can provide the full updated Build function with this format!