[Bug] Weekly digest background job transaction rollback and duplicate email risk #1118

Problem Statement

The background job that dispatches weekly digest emails to subscribers suffers from a transactional design flaw. The function dispatches emails to active subscribers one by one via SMTP and updates their last_sent_at timestamp in the database. However, the database session is only committed (db.commit()) once at the very end of the entire loop. If an exception occurs (such as an unhandled error during stats computation, an SMTP timeout, or a transient database connection issue) mid-way through the loop, the entire transaction is rolled back. Any emails that were already successfully sent cannot be unsent, but because the database was not updated, the system has no record of sending them. On the next run, these subscribers will receive duplicate emails.

Business Impact: Prevents duplicate spam emails to subscribers and ensures robust database state synchronization for background tasks.

Root Cause Analysis

• Inside _send_weekly_digests in backend/app/services/scheduler.py, the loop processes all active subscribers:

  for sub in subs:
      stats = compute_subscriber_stats(db, sub.email)
      ...
      ok = send_digest(stats, sub.unsubscribe_token)
      if ok:
          sub.last_sent_at = datetime.now(UTC)

The database commit db.commit() is positioned outside and after the loop.
If an unhandled exception or database connection error occurs during the loop, the function exits, going straight to the finally block where db.close() is called. This implicitly rolls back the uncommitted changes for all subscribers processed up to that point.

Solution Overview

Modify the background scheduler job to commit changes to the database incrementally (per subscriber or in small chunks) immediately after a digest email is successfully sent.
Add try-except isolation inside the loop so that a failure to process or send a digest to one subscriber does not halt or roll back the entire dispatch process for other subscribers.

Implementation Steps:

Relocate the db.commit() call inside the subscriber loop in backend/app/services/scheduler.py.
Wrap the stats computation and email dispatch logic for each subscriber in a nested try-except block to prevent a single failure from aborting the entire batch.
Verify the changes by writing a test case where one subscriber's email dispatch fails, and asserting that the other subscribers still receive their digests and have their database records updated.

Type of Change

Bug Fix, Reliability, Backend

Testing Done

Background job execution verification with mock SMTP server.
Exception isolation testing (injecting an error mid-loop and asserting partial success is committed).
Database state assertion for last_sent_at timestamps.
Related Issues & Standards
Transaction management best practices, background worker safety, batch processing standards.

Suggested Labels

bug, backend, reliability, GSSoC26