Email Queues

MVP email queue: no project sends email directly, everything is written to a queue and sent via the SetupCase utility.

MVP rule: no project sends email directly. Every message is written to email_queues via EmailQueuesTable::queueEmail(), and a worker (manual button for now, cron later) drains it through the SetupCase send utility.

Write

Any code calls EmailQueuesTable::queueEmail() to enqueue a message. Nothing sends synchronously.

Drain

EmailQueuesTable::send() / sendAll() pull waiting rows and hand them to the SetupCase send utility.

Park, don't retry

A failed send writes its message to error and stamps last_attempt_at. No automatic retry in this alpha.

Self-Contained Block

This is a single copy-paste unit: migrations, EmailQueuesTable, EmailQueueAttachmentsTable, EmailQueuesController (staff prefix), and the Staff/EmailQueues templates. Copy the whole set into a new project without unpicking dependencies. Full spec: docs/features/email-queue-feature.md.

Route	Purpose
`/staff/email-queues`	Waiting / Sent tabs (`?tab=sent`)
`/staff/email-queues/create`	Queue a new email, with optional attachments
`/staff/email-queues/edit/{id}`	Edit a waiting row before it sends
`/staff/email-queues/send/{id}`, `send-all`	Real send: marks `sent` and stamps `sent_at`
`/staff/email-queues/send-test/{id}`, `send-all-test`	Sends via the same utility but never marks `sent` — safe to repeat while debugging
`/staff/email-queues/remove/{id}`, `remove-all`	Soft delete only (`removed = true`) — never a hard delete

Recipients & Attachments

to/cc/bcc are comma-delimited bare addresses — no display names.
Attachments store a tmp/-relative path reference only (never webroot, never blob bytes), so they're never directly downloadable by URL and a failed send can be retried without re-uploading.
No filename hashing in this MVP — original filenames are kept as-is; collisions are accepted.

Language

language is metadata only — translation happens upstream, before the row is queued. It exists so the queue can be filtered by locale and so a future per-language footer has the information already available. Two-letter codes only, defaults to en.

Enqueue from anywhere in the app

Pass getUserId() as user_id to record who queued the message — it is never the recipient.

$this->EmailQueues->queueEmail(
    $to,
    $subject,
    $body,
    [
        'user_id' => $this->getUserId(),
        'cc' => $cc,
        'language' => 'en',
        'attachments' => $uploadedFiles,
    ]
);

Table response contract

Every public EmailQueuesTable method returns at minimum:

['STATUS' => 200, 'MSG' => 'Email queued', 'id' => 42]

Schema

# Email Queuing System
# See docs/features/email-queue-feature.md for the full spec.

CREATE TABLE `email_queues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL COMMENT 'Staff user who queued this email, not a recipient',
  `email_to` varchar(255) NOT NULL COMMENT 'Single or comma-delimited bare addresses (named email_to, not to, since TO is a MySQL reserved word)',
  `email_from` varchar(255) NOT NULL DEFAULT 'from@example.com' COMMENT 'Chosen from a fixed list on the create/edit form',
  `cc` text DEFAULT NULL COMMENT 'Comma delimited',
  `bcc` text DEFAULT NULL COMMENT 'Comma delimited',
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL COMMENT 'Arrives already translated',
  `language` char(2) NOT NULL DEFAULT 'en',
  `sent` tinyint(1) NOT NULL DEFAULT 0,
  `sent_at` datetime DEFAULT NULL,
  `last_attempt_at` datetime DEFAULT NULL COMMENT 'Written on every send attempt, test or real',
  `error` text DEFAULT NULL COMMENT 'Last failure message, null on success',
  `removed` tinyint(1) NOT NULL DEFAULT 0,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sent_removed` (`sent`, `removed`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `email_queue_attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_queue_id` int(11) NOT NULL,
  `original_filename` varchar(255) NOT NULL COMMENT 'As uploaded, no hashing',
  `path` varchar(255) NOT NULL COMMENT 'Relative to webroot',
  `mime_type` varchar(127) NOT NULL,
  `size` int(11) NOT NULL COMMENT 'Bytes',
  `removed` tinyint(1) NOT NULL DEFAULT 0,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `email_queue_id` (`email_queue_id`),
  CONSTRAINT `email_queue_attachments_ibfk_1` FOREIGN KEY (`email_queue_id`) REFERENCES `email_queues` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table

<?php
declare(strict_types=1);

namespace App\Model\Table;

use App\Util\SetupCase;
use Cake\I18n\FrozenTime;
use Cake\ORM\Table;

class EmailQueuesTable extends Table
{
    public const DEFAULT_FROM = 'from@example.com';

    public const FROM_OPTIONS = [
        'from@example.com' => 'from@example.com',
    ];

    public function initialize(array $config): void
    {
        $this->setTable('email_queues');
        $this->addBehavior('Timestamp');

        $this->belongsTo('Users', [
            'foreignKey' => 'user_id',
        ]);

        $this->hasMany('EmailQueueAttachments', [
            'foreignKey' => 'email_queue_id',
        ]);
    }

    /**
     * Writes an email to the queue. No project sends email directly.
     */
    public function queueEmail(string $to, string $subject, string $body, array $options = []): array
    {
        $entity = $this->queueEmail_buildEntity($to, $subject, $body, $options);

        if (!$this->save($entity)) {
            return ['STATUS' => 400, 'MSG' => 'Email could not be queued'];
        }

        $attachmentResult = $this->attachFiles((int)$entity->id, $options['attachments'] ?? []);
        if ($attachmentResult['STATUS'] !== 200) {
            return $attachmentResult;
        }

        return ['STATUS' => 200, 'MSG' => 'Email queued', 'id' => (int)$entity->id];
    }

    private function queueEmail_buildEntity(string $to, string $subject, string $body, array $options)
    {
        $entity = $this->newEmptyEntity();
        $entity->user_id = !empty($options['user_id']) ? $options['user_id'] : null;
        $entity->email_to = $to;
        $entity->email_from = $options['email_from'] ?? self::DEFAULT_FROM;
        $entity->cc = $options['cc'] ?? null;
        $entity->bcc = $options['bcc'] ?? null;
        $entity->subject = $subject;
        $entity->body = $body;
        $entity->language = $options['language'] ?? 'en';
        $entity->sent = false;
        $entity->removed = false;

        return $entity;
    }

    /**
     * Moves each upload to disk via EmailQueueAttachments and records it.
     * Shared by queueEmail() and the edit-form "add attachment" flow.
     *
     * @param \Psr\Http\Message\UploadedFileInterface|\Psr\Http\Message\UploadedFileInterface[] $uploads
     *   A single file input without `[]` in its name comes back from
     *   getUploadedFiles() as one object, not an array — accept both.
     */
    public function attachFiles(int $emailQueueId, $uploads): array
    {
        if (!is_iterable($uploads)) {
            $uploads = [$uploads];
        }

        foreach ($uploads as $upload) {
            if ($upload->getError() === UPLOAD_ERR_NO_FILE) {
                continue;
            }

            $result = $this->EmailQueueAttachments->saveUpload($emailQueueId, $upload);
            if ($result['STATUS'] !== 200) {
                return $result;
            }
        }

        return ['STATUS' => 200, 'MSG' => 'Attachments saved'];
    }

    /**
     * Sends one queued row via the SetupCase utility.
     * Failures park (no retry): error + lastAttemptAt are written either way.
     */
    public function send(int $id, bool $markAsSent = true): array
    {
        $entity = $this->get($id, ['contain' => ['EmailQueueAttachments']]);

        $attachments = $this->send_resolveAttachments($entity);
        if ($attachments === false) {
            return $this->send_recordFailure($entity, 'One or more attachments are missing from disk');
        }

        $result = SetupCase::sendEmail(
            $entity->email_to,
            'email_queues',
            $entity->email_from ?: self::DEFAULT_FROM,
            $entity->subject,
            ['message_html' => $entity->body, 'message_text' => strip_tags($entity->body)],
            $entity->cc ?: false,
            $attachments
        );

        if ($result !== true) {
            return $this->send_recordFailure($entity, (string)$result);
        }

        return $this->send_recordSuccess($entity, $markAsSent);
    }

    private function send_resolveAttachments($entity)
    {
        $attachments = [];
        foreach ($entity->email_queue_attachments as $row) {
            $absolutePath = TMP . $row->path;
            if (!is_file($absolutePath)) {
                return false;
            }
            $attachments[$row->original_filename] = ['file' => $absolutePath];
        }

        return $attachments;
    }

    private function send_recordFailure($entity, string $message): array
    {
        $entity->error = $message;
        $entity->last_attempt_at = FrozenTime::now();
        $this->save($entity);

        return ['STATUS' => 400, 'MSG' => $message];
    }

    private function send_recordSuccess($entity, bool $markAsSent): array
    {
        $entity->error = null;
        $entity->last_attempt_at = FrozenTime::now();

        if ($markAsSent) {
            $entity->sent = true;
            $entity->sent_at = FrozenTime::now();
        }

        $this->save($entity);

        return ['STATUS' => 200, 'MSG' => $markAsSent ? 'Email sent' : 'Test email sent'];
    }

    /**
     * Loops every waiting row and sends it. Used by "Send All" / "Send All Test".
     */
    public function sendAll(bool $markAsSent = true): array
    {
        $ids = $this->find()
            ->select(['id'])
            ->where(['sent' => false, 'removed' => false])
            ->all()
            ->extract('id');

        $sent = 0;
        $failed = 0;

        foreach ($ids as $id) {
            $result = $this->send((int)$id, $markAsSent);
            $result['STATUS'] === 200 ? $sent++ : $failed++;
        }

        return ['STATUS' => 200, 'MSG' => 'Queue processed', 'total' => $sent + $failed, 'sent' => $sent, 'failed' => $failed];
    }

    /**
     * Soft delete only — this table never issues a hard delete.
     */
    public function remove(int $id): array
    {
        $entity = $this->get($id);
        $entity->removed = true;

        if (!$this->save($entity)) {
            return ['STATUS' => 400, 'MSG' => 'Email could not be removed'];
        }

        return ['STATUS' => 200, 'MSG' => 'Email removed'];
    }

    /**
     * Bulk soft delete of every visible waiting row. Never a hard delete.
     */
    public function removeAll(): array
    {
        $count = $this->updateAll(['removed' => true], ['sent' => false, 'removed' => false]);

        return ['STATUS' => 200, 'MSG' => 'Waiting emails removed', 'count' => $count];
    }

    /**
     * Attaches a ready-to-render status label/badge class to each row so the
     * listing template stays a passive view (no business branching in it).
     */
    public function decorateStatus(iterable $rows): array
    {
        $decorated = [];
        foreach ($rows as $row) {
            $row->status_label = $this->decorateStatus_label($row);
            $row->status_badge_class = $this->decorateStatus_badgeClass($row);
            $decorated[] = $row;
        }

        return $decorated;
    }

    private function decorateStatus_label($row): string
    {
        if ($row->sent) {
            return 'Sent';
        }

        return !empty($row->error) ? 'Failed' : 'Waiting';
    }

    private function decorateStatus_badgeClass($row): string
    {
        if ($row->sent) {
            return 'bg-success';
        }

        return !empty($row->error) ? 'bg-danger' : 'bg-secondary';
    }
}

Attachments Table

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;

class EmailQueueAttachmentsTable extends Table
{
    public function initialize(array $config): void
    {
        $this->setTable('email_queue_attachments');
        $this->addBehavior('Timestamp');

        $this->belongsTo('EmailQueues', [
            'foreignKey' => 'email_queue_id',
        ]);
    }

    /**
     * Moves an uploaded file into tmp/ (not webroot — attachments are never
     * directly downloadable by URL) and records a path reference.
     * No filename randomising or hashing for this MVP — collisions are accepted.
     *
     * @param \Psr\Http\Message\UploadedFileInterface $upload
     */
    public function saveUpload(int $emailQueueId, $upload): array
    {
        if ($upload->getError() !== UPLOAD_ERR_OK) {
            return ['STATUS' => 400, 'MSG' => 'Upload failed'];
        }

        $originalFilename = $upload->getClientFilename();
        $relativePath = $this->saveUpload_targetPath($originalFilename);

        $upload->moveTo(TMP . $relativePath);

        $entity = $this->newEmptyEntity();
        $entity->email_queue_id = $emailQueueId;
        $entity->original_filename = $originalFilename;
        $entity->path = $relativePath;
        $entity->mime_type = $upload->getClientMediaType();
        $entity->size = $upload->getSize();
        $entity->removed = false;

        if (!$this->save($entity)) {
            return ['STATUS' => 400, 'MSG' => 'Attachment could not be recorded'];
        }

        return ['STATUS' => 200, 'MSG' => 'Attachment saved', 'id' => (int)$entity->id];
    }

    private function saveUpload_targetPath(string $originalFilename): string
    {
        $folder = 'uploads' . DS . 'email-queue-attachments';
        $absoluteFolder = TMP . $folder;

        if (!is_dir($absoluteFolder)) {
            mkdir($absoluteFolder, 0755, true);
        }

        return $folder . DS . $originalFilename;
    }
}

Controller

<?php
declare(strict_types=1);

namespace App\Controller\Staff;

use App\Controller\AppController;
use App\Model\Table\EmailQueuesTable;
use Cake\Event\EventInterface;
use Cake\Routing\Router;

/**
 * Staff-only email queue management: list, create, edit, send, and
 * soft-remove queued emails. See docs/features/email-queue-feature.md.
 */
class EmailQueuesController extends AppController
{
    public function beforeFilter(EventInterface $event)
    {
        $this->set('webroot', Router::url('/'));
        $this->viewBuilder()->setLayout('code_blocks');
        parent::beforeFilter($event); // TODO: Change the autogenerated stub
    }

    public function index()
    {
        $tab = $this->request->getQuery('tab') === 'sent' ? 'sent' : 'waiting';
        $this->set('activeTab', $tab);

        $rows = $this->EmailQueues->find()
            ->contain(['Users'])
            ->where([
                'EmailQueues.removed' => false,
                'EmailQueues.sent' => $tab === 'sent',
            ])
            ->orderDesc('EmailQueues.id')
            ->all();

        $this->set('rows', $this->EmailQueues->decorateStatus($rows));
    }

    public function create()
    {
        $entity = $this->EmailQueues->newEmptyEntity();

        if ($this->request->is('post')) {
            $data = $this->request->getData();
            $uploads = $this->request->getUploadedFiles()['attachments'] ?? [];

            $result = $this->EmailQueues->queueEmail(
                (string)($data['email_to'] ?? ''),
                (string)($data['subject'] ?? ''),
                (string)($data['body'] ?? ''),
                [
                    'user_id' => $this->getUserId(),
                    'email_from' => $data['email_from'] ?? EmailQueuesTable::DEFAULT_FROM,
                    'cc' => $data['cc'] ?? null,
                    'bcc' => $data['bcc'] ?? null,
                    'language' => $data['language'] ?? 'en',
                    'attachments' => $uploads,
                ]
            );

            if ($result['STATUS'] === 200) {
                $this->Flash->success($result['MSG']);

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error($result['MSG']);
        }

        $this->set('entity', $entity);
        $this->set('fromOptions', EmailQueuesTable::FROM_OPTIONS);
        $this->set('defaultFrom', EmailQueuesTable::DEFAULT_FROM);
    }

    public function edit($id)
    {
        $entity = $this->EmailQueues->get($id, ['contain' => ['EmailQueueAttachments', 'Users']]);

        if ($this->request->is(['post', 'put', 'patch'])) {
            $this->edit_save($entity);

            return $this->redirect(['action' => 'index']);
        }

        $this->set('entity', $entity);
        $this->set('fromOptions', EmailQueuesTable::FROM_OPTIONS);
        $this->set('defaultFrom', EmailQueuesTable::DEFAULT_FROM);
    }

    private function edit_save($entity): void
    {
        $data = $this->request->getData();
        $entity = $this->EmailQueues->patchEntity($entity, [
            'email_to' => $data['email_to'] ?? '',
            'email_from' => $data['email_from'] ?? EmailQueuesTable::DEFAULT_FROM,
            'cc' => $data['cc'] ?? null,
            'bcc' => $data['bcc'] ?? null,
            'subject' => $data['subject'] ?? '',
            'body' => $data['body'] ?? '',
            'language' => $data['language'] ?? 'en',
        ]);

        if (!$this->EmailQueues->save($entity)) {
            $this->Flash->error('Email could not be updated');

            return;
        }

        $uploads = $this->request->getUploadedFiles()['attachments'] ?? [];
        $this->EmailQueues->attachFiles((int)$entity->id, $uploads);
        $this->Flash->success('Email updated');
    }

    public function send($id)
    {
        $result = $this->EmailQueues->send((int)$id, true);
        $this->flashResult($result, 'Email sent!');

        return $this->redirect($this->referer(['action' => 'index']));
    }

    public function sendTest($id)
    {
        $result = $this->EmailQueues->send((int)$id, false);
        $this->flashResult($result, 'Test email sent (not marked as sent)');

        return $this->redirect($this->referer(['action' => 'index']));
    }

    private function flashResult(array $result, string $successMessage): void
    {
        if ($result['STATUS'] === 200) {
            $this->Flash->success($successMessage);
        } else {
            $this->Flash->error($result['MSG']);
        }
    }

    public function sendAll()
    {
        $result = $this->EmailQueues->sendAll(true);
        $this->Flash->success("Sent {$result['sent']} of {$result['total']} queued email(s).");

        return $this->redirect($this->referer(['action' => 'index']));
    }

    public function sendAllTest()
    {
        $result = $this->EmailQueues->sendAll(false);
        $this->Flash->success("Test-sent {$result['sent']} of {$result['total']} queued email(s).");

        return $this->redirect($this->referer(['action' => 'index']));
    }

    public function remove($id)
    {
        $result = $this->EmailQueues->remove((int)$id);
        $result['STATUS'] === 200 ? $this->Flash->success($result['MSG']) : $this->Flash->error($result['MSG']);

        return $this->redirect($this->referer(['action' => 'index']));
    }

    public function removeAll()
    {
        $this->EmailQueues->removeAll();
        $this->Flash->success('Waiting emails removed.');

        return $this->redirect($this->referer(['action' => 'index']));
    }
}