Skip to main content
Ben Nadel at the New York ColdFusion User Group (Jun. 2010) with: Clark Valberg and Andy Matthews
Ben Nadel at the New York ColdFusion User Group (Jun. 2010) with: Clark Valberg Andy Matthews

Creating A Database-Driven Scheduled Task Runner In ColdFusion

By
Published in Comments (4)

While Dig Deep Fitness won't have much in the way of asynchronous processing (at least initially), there are some "cleanup" tasks that I need to run. As such, I've created a scheduled task as part of the application bootstrapping. This approach has served me well over the years: I create a single ColdFusion scheduled task that pulls task data out of the database and acts as the centralized ingress to all the actual tasks that need to be run.

As much as possible, I like to own the logic in my application. Which means moving as much configuration into the Application.cfc as is possible. Thankfully, ColdFusion allows for a whole host of per-application settings such as SMTP mail servers, database datasources, file mappings, etc. By using the CFSchedule tag, we can include ColdFusion scheduled task configuration right there in the onApplicationStart() event handler.

Here's a snippet of my onApplicationStart() method that sets up the single ingress point for all of my scheduled tasks. The ingress task is designed to run every 60-seconds.

component {

	public void function onApplicationStart() {

		// ... truncated code ...

		var config = getConfigSettings( useCacheConfig = false );

		cfschedule(
			action = "update",
			task = "Task Runner",
			group = "Dig Deep Fitness",
			mode = "application",
			operation = "HTTPRequest",
			url = "#config.scheduledTasks.url#/index.cfm?event=system.tasks",
			startDate = "1970-01-01",
			startTime = "00:00 AM",
			interval = 60 // Every 60-seconds.
		);

		// ... truncated code ...

	}

}

The action="update" will either create or modify the scheduled task with the given name. As such, this CFSchedule tag is idempotent, in that it is safe to run over-and-over again (every time the application gets bootstrapped).

The CFSchedule tag has a lot of options. But, I don't really care about most of the features. I just want it to run my centralized task runner (ie, make a request to the given url) once a minute; and then, I'll let my ColdFusion application handle the rest of the logic. For me, this reduces the amount of "magic"; and leads to better maintainability over time.

To manage the scheduled task state, I'm using a simple database table. In this table, the primary key (id) is the name of the ColdFusion component that will implement the actual task logic. Tasks can either be designated as daily tasks that run once at a given time (ex, 12:00 AM); or, they can be designated as interval tasks that run once every N-minutes.

CREATE TABLE `scheduled_task` (
	`id` varchar(50) NOT NULL,
	`description` varchar(50) NOT NULL,
	`isDailyTask` tinyint unsigned NOT NULL,
	`timeOfDay` time NOT NULL,
	`intervalInMinutes` int unsigned NOT NULL,
	`lastExecutedAt` datetime NOT NULL,
	`nextExecutedAt` datetime NOT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	KEY `byExecutionDate` (`nextExecutedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This table can get more robust depending on your application needs. For example, at work, we include a "parameters" column that allows data to be passed from one task execution to another. For Dig Deep Fitness, I don't need this level of robustness (yet).

My single CFSchedule tag is setup to invoke a URL end-point. The end-point does nothing but turn around and call my Task "Workflow" component:

<cfscript>

	scheduledTaskWorkflow = request.ioc.get( "lib.workflow.ScheduledTaskWorkflow" );

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	taskCount = scheduledTaskWorkflow.executeOverdueTasks();

</cfscript>

<cfsavecontent variable="request.template.primaryContent">
	<cfoutput>

		<p>
			Executed tasks: #numberFormat( taskCount )#
		</p>

	</cfoutput>
</cfsavecontent>

As you can see, this ColdFusion template turns around and calls executeOverdueTasks(). This method looks at the database for overdue tasks; and then invokes each one as a separate HTTP call. Unlike Lucee CFML, which can have nested threads, Adobe ColdFusion cannot have nested threads (as of ACF 2021). As such, in order to allow for each separate task to spawns its own child threads (as needed), I need each task to be executed as a top-level ColdFusion page request.

Here's the part of my ScheduledTaskWorkflow.cfc that relates to the centralized ingress / overall task runner:

component {

	// Define properties for dependency-injection.
	// ... truncated ...

	// ---
	// PUBLIC METHODS.
	// ---

	/**
	* I execute any overdue scheduled tasks.
	*/
	public numeric function executeOverdueTasks() {

		var tasks = scheduledTaskService.getOverdueTasks();

		for ( var task in tasks ) {

			makeTaskRequest( task );

		}

		return( tasks.len() );

	}

	// ---
	// PRIVATE METHODS.
	// ---

	/**
	* Each task is triggered as an individual HTTP request so that it can run in its own
	* context and spawn sub-threads if necessary.
	*/
	private void function makeTaskRequest( required struct task ) {

		// NOTE: We're using a small timeout because we want the tasks to all fire in
		// parallel (as much as possible).
		cfhttp(
			result = "local.results",
			method = "post",
			url = "#scheduledTasks.url#/index.cfm?event=system.tasks.executeTask",
			timeout = 1
			) {

			cfhttpparam(
				type = "formfield",
				name = "taskID",
				value = task.id
			);
			cfhttpparam(
				type = "formfield",
				name = "password",
				value = scheduledTasks.password
			);
		}

	}

}

The scheduledTaskService.getOverdueTasks() call is just a thin wrapper around a database call to get rows where the nextExecutedAt value is less than now. Each overdue task is then translated into a subsequent CFHttp call to an end-point that executes a specific task. Note that I am passing through a password field in an attempt to secure the task execution.

The end-point for the specific task just turns around and calls back into this workflow:

<cfscript>

	scheduledTaskWorkflow = request.ioc.get( "lib.workflow.ScheduledTaskWorkflow" );

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	param name="form.taskID" type="string";
	param name="form.password" type="string";

	scheduledTaskWorkflow.executeOverdueTask( form.taskID, form.password );

</cfscript>

<cfsavecontent variable="request.template.primaryContent">
	<cfoutput>

		<p>
			Executed task: #encodeForHtml( form.taskID )#
		</p>

	</cfoutput>
</cfsavecontent>

Here's the part of my ScheduledTaskWorkflow.cfc that relates to the execution of a single task. Remember that the taskID in this case is the filename for the ColdFusion component that implements the logic. I'm using my dependency injection (DI) framework to access that component dynamically in the Inversion of Control (IoC) container:

ioc.get( "lib.workflow.task.#task.id#" )

The workflow logic is fairly straightforward - I get the task, check to see if it's overdue, execute it, and then update the nextExecutedAt date (depending on weather it's a daily task or an interval task).

component {

	// Define properties for dependency-injection.
	// ... truncated code ...

	// ---
	// PUBLIC METHODS.
	// ---

	/**
	* I execute the overdue scheduled task with the given ID.
	*/
	public void function executeOverdueTask(
		required string taskID,
		required string password
		) {

		if ( compare( password, scheduledTasks.password ) ) {

			throw(
				type = "App.ScheduledTasks.IncorrectPassword",
				message = "Scheduled task invoked with incorrect password."
			);

		}

		var task = scheduledTaskService.getTask( taskID );
		var timestamp = clock.utcNow();

		if ( task.nextExecutedAt > timestamp ) {

			return;

		}

		lock
			name = "ScheduledTaskWorkflow.executeOverdueTask.#task.id#"
			type = "exclusive"
			timeout = 1
			throwOnTimeout = false
			{

			// Every scheduled task must implement an .executeTask() method.
			ioc
				.get( "lib.workflow.task.#task.id#" )
				.executeTask( task )
			;

			if ( task.isDailyTask ) {

				var lastExecutedAt = clock.utcNow();
				var tomorrow = timestamp.add( "d", 1 );
				var nextExecutedAt = createDateTime(
					year( tomorrow ),
					month( tomorrow ),
					day( tomorrow ),
					hour( task.timeOfDay ),
					minute( task.timeOfDay ),
					second( task.timeOfDay )
				);

			} else {

				var lastExecutedAt = clock.utcNow();
				var nextExecutedAt = lastExecutedAt.add( "n", task.intervalInMinutes );

			}

			scheduledTaskService.updateTask(
				id = task.id,
				lastExecutedAt = lastExecutedAt,
				nextExecutedAt = nextExecutedAt
			);

		} // END: Task lock.

	}

}

And that's all there is to it. Now, whenever I need to add a new scheduled task, I simply:

  1. Create a ColdFusion component that implements the logic (via an .executeTask() method).

  2. Add a new row to my scheduled_task database table with the execution scheduling properties.

Like I said earlier, I've been using this approach for years and I've always been happy with it. It reduces the "magic" of the scheduled task, and moves as much of the logic in the application where it can be seen, maintained, and included within the source control.

Want to use code from this post? Check out the license.

Reader Comments

13 Comments

Simple. Easy to understand. I love the reduction of magic concept and the escalation of "it just works" and application logic visibility. I do something similar. I have a wrapper function for utility around cfschedule and I define all my tasks in application.cfc onApplicationStart().

Pseudo Code:
Set up Scheduled Tasks in OnApplicationStart()

if(  ! Schedule().TaskExists('Task_Name')  ){
    Schedule().NewTask('Task_Name',[url of task]),60);
}

Link to gist for cfschedule wrapper function.

15,848 Comments

@Peter,

I like how much you've simplified the scheduled task interactions. I was a bit taken back when I saw how many attributes the CFSchedule tag can take. And, on top of that, it looks like there are ways in which you can define a CFC-path as the eventHandler. Normally, being able to use a CFC is exactly what I want. But, ACF's documentation says:

ColdFusion does not invoke Application.cfc methods, when invoking a task's event handler methods.

... which makes me think that none of my dependency-injection stuff will work. I'm guessing that ColdFusion instantiates this CFC somewhat independently of the application. Otherwise, the onCFCRequest() should probably fire. I might dig into that one day, but not today.

238 Comments

This is so useful! I love the idea of controlling scheduled tasks directly from the database...especially when in a multi-server environment.

15,848 Comments

@Chris,

One thing to take into account if you have multiple servers running tasks is that you might want to create some distributed-locking around the individual tasks, just so you don't have multiple servers running the same task at the same time.

A really nice thing about managing tasks in the database is that you get the opportunity to persist state across executions. I'm not doing that in this particular post, but you can imagine the execution method looking like this:

// Execute the task and RETURN DATA to store for NEXT execution.
var dataToPersist = ioc
	.get( "lib.workflow.task.#task.id#" )
	.executeTask( task )
;

....

scheduledTaskService.updateTask(
	id = task.id,
	lastExecutedAt = lastExecutedAt,
	nextExecutedAt = nextExecutedAt,
	data = dataToPersist
);

Then, the next time the scheduled task goes to execute, it could "pick up where it left off", if it's doing some large data processing or something.

Once you're working in the world of databases, you get a lot of options!

Post A Comment — I'd Love To Hear From You!

Post a Comment

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel