Email problems
Emails that did not go out and need a personWhat each action would do in the database (design note)
The list reads scheduled_emails where status='failed', joined to email_log, email_templates, enrollment and contact. Rows older than a resolved mark are hidden. A "held" email is a failed scheduled row whose email_log row is still 'sending' (the runner logs a critical line for it).
| Action | Held (delivery unknown) | Failed (not sent) |
|---|---|---|
| Retry | Asks first: "check the mailbox or Postmark before resending". On confirm: email_log.status from 'sending' to 'failed' (so the exactly-once gate no longer holds it), then scheduled_emails set to status='pending', attempts=0, locked_at=NULL, run_at=now. The runner then sends once. Risk: a duplicate to the student if the first one did arrive. |
One step: scheduled_emails status='pending', attempts=0, locked_at=NULL, run_at=now. Runner picks it up on its next tick. Disabled with a hint when the cause cannot be fixed by retrying (placeholder bank profile, template deleted, no address) until the cause is fixed. |
| Mark resolved | Staff confirms they checked. They choose "It was delivered" (email_log.status to 'sent') or "It was not delivered, I handled it another way" (to 'failed'). The scheduled row stays 'failed' (terminal) and gets a resolved stamp, so it leaves the list. |
Scheduled row stays 'failed' and gets a resolved stamp plus optional note. No email is sent, no email_log row is written. |
Not in the schema today (assumptions in this mockup): scheduled_emails has no reason column (the runner only logs the reason) and no resolved marker. The Reason column and "Mark resolved" need a small migration: last_error text, resolved_at timestamptz, resolved_by (staff user id), resolved_note text; the Resolved tab shows these, written by the runner at each failure point. Without it, only held emails can show a reason (derived from the 'sending' log row). Both actions would also write a history line, and retry and resolve are staff-only.
The 4 new columns on scheduled_emails (all nullable, no backfill):
| Column | Type | Holds | Example (fictional) |
|---|---|---|---|
last_error (the "reason") | text | Why the send failed, written by the runner at each failure point | Gave up after 5 attempts. Mail server refused the message. |
resolved_at | timestamptz | When staff marked it resolved. NULL means still open | 20 Oct, 11:20 |
resolved_by | staff user id | Who marked it resolved | Karlis |
resolved_note | text | Optional note, plus the outcome for held emails | Student confirmed it arrived. Outcome: delivered. |
MAX_ATTEMPTS is 5; a row that fails 5 times ends 'failed' with the mail server error as reason.