Updating mysql db when messages first read by IMAP user

[KT Walrus]

I need to implement updating a mysql db when the \SEEN flag is set by IMAP.

New message delivery is done by having Postfix store all new inbound messages in a mysql db and a batch job watching the db for new messages and delivering them to the correct Dovecot server for the recipients. The batch job leaves a log entry in the db (for each recipient) with the current timestamp and adds a header with the log id to the outgoing message that is sent to Dovecot. The log table has a column for the first read timestamp which is set to 0 initially.

I plan on implementing a Sieve IMAP script that watches for changes to the \SEEN flag and sends a mailto: notify message that contains the log id (from the message header) and the current date to a mail address dedicated for keep track of \SEEN flag changes. The notify message will be delivered to this mail address where a batch job watches that mail address and updates the db for the log id in the notify message with the timestamp and the current date if the row in the log table has a first read timestamp of 0.

I plan on providing a web page that the sender can use to see whether the messages they have sent have been seen by the recipients. The web page will show other info too, but the part that I need to implement next is updating the db when a sent message has been read by the recipients of the message.

Is this a good way to do this?

Or, is there a better way than using an IMAP Sieve script to send notify messages?

Is there any other way to have a Sieve script update a mysql database?

Or, is there some other mechanism in Dovecot to use instead of an IMAP Sieve script?

Kevin