Trying to do this with any kind of lightweight process (asynchronous processes, threads or anything else like that) is a recipe for failure unless you're in an environment that gives you hard guarantees about when the code will be executed. It also doesn't help that if your program dies and is restarted, any pending status updates will be lost unless you have a way to reconstitute them and make updates that should have happened while the program wasn't running.
The advance notice you get of a new status and when it takes effect are data, and what better way to deal with it than to put it in the database? You were, after all, going to do a transaction to update the status anyway once your LWP decided it was time. Doing so gets you all of the ACIDity your database has to offer and will scale to whatever your database can store rather than the number of jobs the LWP environment you're using can muster.
Doing this requires that you break the status of your events out into a separate table that lists the time ranges and what status is in effect during each one.
This example is nominally PostgreSQL, but you can do the equivalent in any relational database:
CREATE TABLE event (
id BIGSERIAL PRIMARY KEY,
-- All other columns except the status
CREATE TABLE event_status (
event BIGINT REFERENCES events(id), -- Which event
times TSTZRANGE, -- Range of times this status is valid
status INTEGER -- The status (doesn't have to be INTEGER)
That's the table structure.* You'll need to set up constraints and triggers to make sure that rows in
event_status for a given event express a contiguous range of times that covers the dawn of time to infinity and that insertion of a new row properly adjusts the end times of surrounding rows.
With that in place, getting the current status is a
JOIN of the two tables that weeds out rows that are either not in effect anymore or not in effect yet. This works nicely in a view:
CREATE VIEW event_current_status AS
SELECT event.*, event_status.status
FROM event JOIN event_status ON event_status.event = event.id
-- The && operator means containment, i.e., the current time
-- falls within the range of the 'times' column.
WHERE event_status.times && now()
Properly indexed (
times in the same index on
event_status should do), you should have no performance concerns.
* You mentioned in a comment that this information is available elsewhere in the database; I'm going to hand-wave that for now. That said, it might be a good idea to investigate the merits of pulling it directly from the tables where it originates instead of having a duplicate status table. There are trade-offs for both that will depend on your situation.