Trigger Rules & Scheduled Jobs

Overview

This domain has 4 tables: trigger_rule stores rules describing "when this happens, do that" (source + action as JSONB), trigger_log records every rule firing and its outcome, scheduled_action holds actions queued for later execution, and scheduled_trigger_execution records the result of each scheduled run of a rule

Table `trigger_rule`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	`nextval(...)`	Primary key
`name`	`VARCHAR(255)`	NO	-	Trigger rule name
`source_type`	`VARCHAR(30)`	NO	-	Type of the event source that fires the rule
`source_config`	`JSONB`	NO	-	Event source configuration (trigger conditions)
`action_type`	`VARCHAR(30)`	NO	-	Type of action performed when the rule fires
`action_config`	`JSONB`	NO	-	Action configuration
`enabled`	`BOOLEAN`	NO	`true`	Whether the rule is enabled
`frequency`	`VARCHAR(20)`	NO	`'once_per_hour'`	Maximum firing frequency for this rule
`cooldown_seconds`	`INTEGER`	YES	-	Cooldown before the rule may fire again (seconds)
`line_oa_id`	`INTEGER`	NO	-	LINE OA the rule belongs to
`organization_id`	`INTEGER`	NO	-	Organization that owns the rule
`created_by`	`INTEGER`	YES	-	Creator (user id)
`updated_by`	`INTEGER`	YES	-	Last updater (user id)
`created_date`	`TIMESTAMPTZ(3)`	NO	`now()`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Deletion timestamp (soft delete)
`workflow_id`	`INTEGER`	YES	-	Workflow this rule is part of (FK to `workflow.id`)
`workflow_node_path`	`VARCHAR(500)`	YES	-	Path of the workflow node this rule refers to

Table `trigger_log`

Column	Type	Nullable	Default	Description
`id`	`BIGSERIAL`	NO	`nextval(...)`	Primary key
`trigger_rule_id`	`INTEGER`	NO	-	Rule that fired
`audience_id`	`INTEGER`	YES	-	Audience involved in this firing
`user_id`	`VARCHAR(255)`	NO	-	User the rule acted on (LINE user id)
`trigger_on`	`VARCHAR(20)`	NO	-	Event that caused the rule to fire
`action_type`	`VARCHAR(30)`	NO	-	Type of action that was performed
`action_config`	`JSONB`	YES	-	Action configuration actually used for this run
`status`	`VARCHAR(20)`	NO	`'success'`	Outcome of the action
`error_message`	`TEXT`	YES	-	Error message when the action failed
`line_oa_id`	`INTEGER`	NO	-	Related LINE OA
`organization_id`	`INTEGER`	NO	-	Related organization
`created_date`	`TIMESTAMPTZ(3)`	NO	`now()`	Log timestamp

Table `scheduled_action`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	`nextval(...)`	Primary key
`trigger_rule_id`	`INTEGER`	YES	-	Rule that created this scheduled action
`user_id`	`VARCHAR(64)`	NO	-	Target user of the action (LINE user id)
`line_oa_id`	`INTEGER`	NO	-	Related LINE OA
`organization_id`	`INTEGER`	NO	-	Related organization
`action_type`	`VARCHAR(30)`	NO	-	Type of action to perform when due
`action_config`	`JSONB`	NO	-	Action configuration
`source_config`	`JSONB`	YES	-	Configuration of the event source that produced this action
`execute_at`	`TIMESTAMPTZ`	NO	-	Time the action is scheduled to run
`expires_at`	`TIMESTAMPTZ`	YES	-	Time after which the action is no longer valid
`status`	`VARCHAR(20)`	NO	`'pending'`	Status of the action in the queue
`retry_count`	`INTEGER`	NO	`0`	Number of retry attempts
`created_at`	`TIMESTAMPTZ`	NO	`now()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	NO	`now()`	Last update timestamp
`executed_at`	`TIMESTAMPTZ`	YES	-	Time the action actually ran
`workflow_id`	`INTEGER`	YES	-	Workflow this action originated from
`workflow_node_path`	`VARCHAR(500)`	YES	-	Workflow node path that created this action

Table `scheduled_trigger_execution`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	`nextval(...)`	Primary key
`trigger_rule_id`	`INTEGER`	NO	-	Rule executed in this run
`scheduled_at`	`TIMESTAMPTZ`	NO	-	Scheduled time of this run
`started_at`	`TIMESTAMPTZ`	NO	`now()`	Time the run started
`completed_at`	`TIMESTAMPTZ`	YES	-	Time the run finished
`status`	`VARCHAR(20)`	NO	`'processing'`	Status of the run
`total_users`	`INTEGER`	NO	`0`	Total users in this run
`processed_users`	`INTEGER`	NO	`0`	Users processed successfully
`failed_users`	`INTEGER`	NO	`0`	Users that failed processing
`error_message`	`TEXT`	YES	-	Error message for the run
`created_at`	`TIMESTAMPTZ`	NO	`now()`	Record creation timestamp

Notes

trigger_rule.workflow_id is the only schema-level FK in this group — it points to workflow.id (see Workflow Automation)
trigger_log.trigger_rule_id, scheduled_action.trigger_rule_id and scheduled_trigger_execution.trigger_rule_id reference trigger_rule.id logically but are not declared as FKs in the schema
scheduled_trigger_execution has the unique constraint uq_scheduled_trigger_execution on (trigger_rule_id, scheduled_at), preventing the same run from executing twice
scheduled_action has dedicated queue-polling indexes: idx_scheduled_action_pending (on execute_at), idx_scheduled_action_processing (on updated_at) and idx_scheduled_action_user (on user_id, trigger_rule_id)
trigger_log is indexed on (trigger_rule_id, created_date DESC) for fetching a rule's most recent logs
trigger_rule is indexed on (line_oa_id, organization_id) and on deleted_date
Timestamp columns in scheduled_action and scheduled_trigger_execution use TIMESTAMPTZ at default precision (microseconds), unlike most other tables which use TIMESTAMPTZ(3)
Some of the events that fire trigger_rule arrive through pg_notify / the event outbox (see pg_notify and Event Outbox)

Overview​

Table trigger_rule​

Table trigger_log​

Table scheduled_action​

Table scheduled_trigger_execution​

Notes​