feat: harden prepaid billing and commercial operations

1 files changed, 16 insertions, 3 deletions

diff --git a/internal/controlplane/outbox.go b/internal/controlplane/outbox.go
index b54a064..b708a90 100644
--- a/internal/controlplane/outbox.go
+++ b/internal/controlplane/outbox.go
@@ -109,8 +109,9 @@ func (s *Store) ClaimMail(ctx context.Context) (mailer.Message, bool, error) {
 	var ciphertext []byte
 	err = tx.QueryRow(ctx, `WITH candidate AS (
 		SELECT id FROM console_mail_outbox
-		WHERE ((status IN ('pending','failed') AND available_at <= now())
+		WHERE ((status IN ('pending','retry') AND available_at <= now())
 		   OR (status='sending' AND claimed_at < now()-interval '5 minutes'))
+		AND NOT EXISTS (SELECT 1 FROM mail_suppressions s WHERE lower(s.recipient)=lower(console_mail_outbox.recipient))
 		ORDER BY available_at,created_at FOR UPDATE SKIP LOCKED LIMIT 1
 	) UPDATE console_mail_outbox o SET status='sending',claimed_at=now(),attempts=attempts+1,last_error=''
 	FROM candidate WHERE o.id=candidate.id
@@ -146,7 +147,19 @@ func (s *Store) MarkMailFailed(ctx context.Context, id string, deliveryErr error
 	if len(message) > 1000 {
 		message = message[:1000]
 	}
-	_, err := s.db.Exec(ctx, `UPDATE console_mail_outbox SET status='failed',claimed_at=NULL,last_error=$2,
-		available_at=now()+make_interval(secs => LEAST(300, 5 * attempts)) WHERE id=$1 AND status='sending'`, id, message)
+	_, err := s.db.Exec(ctx, `UPDATE console_mail_outbox SET
+		status=CASE WHEN attempts>=10 THEN 'dead' ELSE 'retry' END,claimed_at=NULL,last_error=$2,
+		available_at=now()+make_interval(secs => LEAST(3600, 5 * power(2,LEAST(attempts,9))::int))
+		WHERE id=$1 AND status='sending'`, id, message)
 	return err
 }
+
+func (s *Store) MailQueueStatus(ctx context.Context) (MailQueueStatus, error) {
+	var result MailQueueStatus
+	err := s.db.QueryRow(ctx, `SELECT
+		count(*) FILTER (WHERE status IN ('pending','sending','retry')),
+		count(*) FILTER (WHERE status='dead'),
+		min(created_at) FILTER (WHERE status IN ('pending','sending','retry'))
+		FROM console_mail_outbox`).Scan(&result.Backlog, &result.Failed, &result.OldestPending)
+	return result, err
+}