Sending notification emails based on each db record timestamp

Question

I have a db of "event" (a conference, birthday party etc.) records, and each event has an endTime field. After an event ends, I'd like to send out 2-3 emails for each past event.

Right now, I run an hourly cron job to find all events that has ended in the past hour, so it looks something like:

• at 2:01pm: find all events ending between 1-2pm, send emails
• at 3:01pm: find all events ending between 2-3pm, send emails
• at 4:01pm: find all events ending between 3-4pm, send emails
• at 5:01pm: ...

However, say when the 3:01pm job fails for some reason (Heroku crashes my rest api, email service provider goes down etc.), the events ending between 2-3pm won't get emails.

At the moment, my db records are not marked as notified if the emails were sent successfully. Should I do that so I can change the cron script to "find all records before current time where notified=false" (which catches all previously unsent records)? Would you then set a flag for each type of email you send out successfully?

Or is there a cleverer way that avoids setting flags?

(It was hell coming up with the title and tags for this question — suggestions/edits welcome!)

Answer 1

The problem with notified flag is that it requires an index just to make WHERE notified = FALSE¹ efficient, yet TRUE values stay in the index despite never being used in a query ever again.

In time, you'll have a small FALSE portion in the index (which is useful) and a huge TRUE portion (which just wastes space). If your DBMS supports it, use a partial index of some sort to remove the TRUE from the index:

• Oracle doesn't index NULLs, so use that instead of TRUE.
• Use filtered index under MS SQL Server, for example: CREATE INDEX ... WHERE notified = 0 .
• Use partial index under PostgreSQL.

Alternatively, create an extra table with exactly one row and one field: LAST_SENT², and update that field whenever a bunch of notifications have been succesfully sent. This of course precludes sending notifications out of order and you'll need to handle partial failures carefully, since you can't just skip the unsuccessful notifications.

---

¹ Replace FALSE with whatever is appropriate for your database.

² If your table uses an auto-incremented integer as primary key, consider using that for LAST_SENT instead of timestamp, to avoid edge cases where timestamps may not be unique.

Answer 2

CREATE TABLE events_tbl
(
    e_id    NUMBER
,   e_date  DATE
,   e_info  VARCHAR2(4000)
);

INSERT INTO events_tbl VALUES (43, '2000-01-01 01:10:00', 'event1');
INSERT INTO events_tbl VALUES (44, '2000-01-01 01:15:00', 'event2');
INSERT INTO events_tbl VALUES (45, '2000-01-01 01:20:00', 'event3');
INSERT INTO events_tbl VALUES (46, '2000-01-01 01:25:00', 'event4');

INSERT INTO events_tbl VALUES (47, '2000-01-01 02:10:00', 'event5');
INSERT INTO events_tbl VALUES (48, '2000-01-01 02:15:00', 'event6');
INSERT INTO events_tbl VALUES (49, '2000-01-01 02:20:00', 'event7');
INSERT INTO events_tbl VALUES (50, '2000-01-01 02:25:00', 'event8');

/* table that holds information about sent events */
CREATE TABLE events_sent_tbl
(
    s_e_id NUMBER
,   s_date DATE
);

INSERT INTO events_sent_tbl VALUES (43, '2000-01-01 02:01:00');
INSERT INTO events_sent_tbl VALUES (44, '2000-01-01 02:02:00');

SELECT  *
FROM    events_tbl
WHERE   TO_NUMBER(TO_CHAR(e_date, 'MMDDHH24MiSS')) BETWEEN 0101010000 AND 0101015959
AND     NOT EXISTS
        (
            SELECT  1
            FROM    events_sent_tbl
            WHERE   e_id = s_e_id
        )
;
/*
45  2000-01-01 01:20:00 event3
46  2000-01-01 01:25:00 event4
*/

Work with:

• events to send
• sent events

...in collections. Read the events_tbl and events_sent_tbl into collections. Send data and update information in the collections. That dump it with FORALL to the events_sent_tbl table.