I built directly in Airtable a way to let users schedule an automation run at a certain time with unlimited time options, e.g. "run a send an email automation on X day at Y time" where that datetime is infinitely dynamic (great for situations where the regular "scheduled an automation" doesn't work).
Typically this use case is when you need some kind of "job queue" or "message queue" system that is handled in most traditional software development with cron jobs and message or event streaming queues like Sequin, Kafka, QStash, RabbitMQ, etc (or in the Airtable community, with Make.com automations). But I wanted to build this natively in Airtable with no dependencies on external tools to keep it really simple.
Airtable's fastest "cron job" is 15 minute intervals so that doesn't work by itself. Being a set interval, it's also not relative to the scheduled time so theoretically it could create up to a 14 min 59 second delay.
So below I'm sharing my first iteration quick test of a simple message queue I built directly in Airtable that I ran 1,000 tests on over 72 hours and got a median 3 minute 20 second delay (average 3 min 44 seconds, with a range between 6 seconds at minimum and 8 min 35 sec at maximum). That's acceptable latency for many Airtable use cases, and further iterations/tests could probably get it even lower.
I also imagine this may similarly apply to decreasing Airtable sync durations, although that was outside the scope of my tests.
Here's how I did it (video version):
https://www.loom.com/share/45154bf8e0904eef9e64358e6bb7b274
Written version:
- Create a table with a singleton record that will be our NOW() time calculation with 4 fields: final_now_time, now_formula, now_from_cron_helper, and a linked record field to another table with "job queue" records. The now_formula is just NOW(), the now_from_cron_helper is a plain datetime field, and the final_now time uses this formula:
IF(
DATETIME_DIFF({now_formula}, {now_from_cron_helper}, 'milliseconds') >= 0,
{now_formula}, {now_from_cron_helper}
)
- In that second table, connect upcoming jobs to the singleton, and use a lookup to get the "final_now_time". Have a time you want it to trigger at
- Set up two automations "at a scheduled time" to start in the future: One automation at a :00 o'clock time (e.g. 1:00pm) and one at an :07 time (e.g 1:07pm). Both have interval of 15 minutes.
- Make their action "update a record." Update the "now_from_cron_helper" field to be the automation's actual run time. That way, at any given moment we have the most recent time from either the last automation run or the last normal "NOW()" update, giving us multiple chances to have the most current time.
- Create an automation to do whatever you want based on a record enters view trigger in the job queue table which should be filtered to "ready_for_processing" or similar which is a formula like this based on the now time:
IF(
DATETIME_DIFF(now_datetime_from_singleton, {trigger_at}, 'milliseconds') >= 0,
TRUE()
)
- Make sure the last step of the processing automation run removes the linked record relationship from the singleton record to decrease dependencies/data that needs to be updated based on the NOW() change
I could probably add an additional cron helper to reduce the interval even further, but didn't want to blow through a ton of automations. On the Business plan with 100k automations it's reasonable to use two helper automations which use 24 hrs / 15 minutes = 96 automation runs per automatoin per day, e.g. 192/day, 1,344/week, or (accounting for 52 weeks a year, 12 months per year) ~5,824 runs per month. That's a moderate amount but not crazy for a message queue system that runs under 8.5 minutes max, and usually around 3 minutes, rather than having to handle everything separately with external software.
Future things I'd like to test:
- I assumed the idea with the singleton of calculating time is the base only has to process NOW (and then the linked record relationship subsequently "getting" or SELECT'ing that data via the linked record relationship on only the records not yet processed) is more performant than the compute of NOW() on every record. But without testing or more knowledge of how Airtable works it's not a 100% guarantee.
- There are also obviously a lot of other ways to do this, e.g. batch processing where you just run the at a schedule time trigger every 7 minutes and do a batch action based on a condition rather than the rest of the queue system, but without the NOW() calculation involved it likely would have a much higher average/median processing time and is dependent on the date time of the scheduled action in comparison to the scheduled batch automation run (whereas the system above I proposed is more likely to work on relative time by involving the NOW() function). But then again, NOW() is kind of just acting as a third, non-automation based pseudo "at a scheduled time" trigger so it'd be interesting to test.
Hope this helps someone who is considering building something similar. Looking forward to what anyone else shares.
