Church Cashbook Schema Manual

The notification_queue table stores outgoing email notifications, providing a queued delivery buffer with retry capability.

Purpose

Rather than sending email synchronously during a web request, the application inserts a row into notification_queue and then immediately attempts delivery. If delivery fails (SMTP unavailable, mail() returns false), the row remains in PENDING state for retry. Superadmins can view the queue and trigger retries from Admin → Notification Queue.

Table definition

CREATE TABLE notification_queue (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  to_email        VARCHAR(255) NOT NULL,
  to_name         VARCHAR(255) NOT NULL DEFAULT '',
  subject         VARCHAR(255) NOT NULL,
  body_text       TEXT NOT NULL,
  status          ENUM('PENDING','SENT','FAILED') NOT NULL DEFAULT 'PENDING',
  context_type    VARCHAR(40) DEFAULT NULL,
  context_id      INT UNSIGNED DEFAULT NULL,
  attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  sent_at         DATETIME DEFAULT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_status (status),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Column reference

Column	Type	Notes
`id`	INT UNSIGNED PK	Auto-increment primary key
`to_email`	VARCHAR(255)	Recipient email address
`to_name`	VARCHAR(255)	Recipient display name
`subject`	VARCHAR(255)	Email subject line
`body_text`	TEXT	Plain text email body
`status`	ENUM	PENDING / SENT / FAILED
`context_type`	VARCHAR(40)	Optional — what triggered the notification (e.g. ie_assigned, viewer_account_created)
`context_id`	INT UNSIGNED	Optional — ID of the related entity (user, assignment, etc.)
`attempts`	TINYINT UNSIGNED	Number of send attempts made
`sent_at`	DATETIME	Timestamp of successful delivery; NULL if not yet sent
`created_at`	DATETIME	When the notification was queued

Related schema changes

The examiner_church_access table has an additional column added by migration:

ALTER TABLE examiner_church_access
  ADD COLUMN reminder_sent_at DATETIME DEFAULT NULL;

This records when the IE expiry reminder was last sent for each assignment, preventing duplicate reminders.

Migration

Applied by: public/migration_notifications_01_queue.sql