Outline architecture for a weekly email digest of unread notifications

WHAT THIS TESTS: Batch processing patterns, anti-corruption layers between OLTP and analytics workloads, and compliance-sensitive delivery at scale. Interviewers want to see that you avoid poisoning the primary transaction database with large scan workloads, that you understand idempotency and at-least-once delivery semantics, and that you treat unsubscribes as a hard requirement rather than an afterthought.

A GOOD ANSWER COVERS: First, data modeling: maintain a pre-aggregated digest store populated by a stream processor or hourly batch job that reads from the notification events table and rolls up unread counts per user per channel. Second, scheduling: shard users by user ID modulo N across distributed workers or use a delayed queue where each user gets one digest job enqueued for their local timezone window; include an idempotency key so retries do not duplicate emails. Third, query efficiency: the digest worker reads only the pre-aggregated row for that user, not the raw notifications table, keeping query time O(1). Fourth, unsubscribes: store opt-out state in a hot cache with sub-millisecond latency and check it at both enqueue time and send time; treat the cache as a filter, not the source of truth, and back it with a durable preference store. Fifth, delivery: hand off rendered emails to a provider-agnostic outbound service that handles rate limiting and bounce processing asynchronously.

COMMON WRONG ANSWERS: Scanning the notifications table directly at send time for every user; this creates a full table scan that will degrade the primary database and likely time out. Using a single monolithic cron job instead of a distributed scheduler, which becomes a single point of failure and cannot meet a tight delivery window for millions of users. Checking unsubscribe status only at send time without a scheduling-time filter, which wastes compute generating emails that will be dropped. Storing digest content in the same transactional database as user profiles without read replicas or separate connection pools, causing head-of-line blocking for login traffic.

LIKELY FOLLOW-UPS: How would you handle a user who marks a notification as read after the digest is generated but before it is sent? How would you support per-user timezone scheduling instead of a global UTC batch? What happens if your stream processor double-writes an unread count? How do you gracefully degrade if the email provider enforces a rate limit of ten thousand messages per second?

ONE CONCRETE EXAMPLE: Imagine a platform with fifty million weekly digest recipients. A Flink or Kafka Streams job consumes notification events and writes rolled-up unread counts into a Cassandra table partitioned by user ID. Every Sunday at 8 AM local time, a Temporal or Cadence scheduler triggers digest generation for the users in that timezone shard. Each worker reads one Cassandra row per user, renders the email, checks a Redis SET for unsubscribe status, and enqueues the payload to an internal email service that fans out to SendGrid. If a user unsubscribes at 7:55 AM, the Redis filter blocks the send and asynchronously updates the preference store.

Source: designgurus.substack.com