I am trying to make a newsletter system with a database storing an email list
My raw design of the schema is simply:
primary_key, subscribe_name, email
However, to avoid issues of bouncing/complaining and unclean email list, I am planning on several possibilities:
Plan A: add a verification process (aka send an email for them to click to make their account verified)
primary_key, subscribe_name, email, verified
More ambitious version: if not verified within two days, then the email will be automatically removed from the database:
Should I do:
DB1 (unverified email):
primary_key, subscribe_name, email, expiry_date
with DB2 (verified email): (then every time when I send a newsletter I am simply querying a clean database)
primary_key, subscribe_name, email
Or just combined:
primary_key, subscribe_name, email, verified, expiry_date
The issue with this is I don't know if it is possible to set a conditional ttl for expiry_date so it only gets removed if unverified
However, it seems like extra frictions to make user verify their identity when subscribing newsletter - hence:
Plan B: maybe it is better to just let the emails be in the list and remove as emails have higher bouncing/complain rate
primary_key, subscribe_name, email, status
Status is whether the email is a deliverable one - though I ain't sure what metric should be used to measure and what is the threshold I should use to kick an email out of my subscription list.
For reference, I am using AWS SES to implement it, and the data is in CockroachDB
Using double opt-in is always the best way when dealing with email subscriptions. Since you are using CockroachDB, you can use Row-Level TTL and provide an expression that combines the expire date and a verified Boolean field. You can set the default value of the expire date to now() + Interval() so it will be populated automatically.
Full disclosure, I work at CockroachDB but I also used to work for a couple email companies. If this sounds good, I can follow up with some sample code.