adrians
/
feca-mailshots-plugin
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
feca-mailshots-plugin/requirements/mailshot.md

454 lines
25 KiB
Markdown
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# 1 Mailshot
Use and comply with `requirements/ui_design.md`.
## 1.0 Environment Baseline
* All writable mailshot tables and mailshot asset binaries are stored in the database named by `.env` variable `MAILSHOTS_REMOTE_MYSQL_DB`.
* This includes `mailshots`, `mailshot_queries`, `mailshot_attachments`, `mailshot_last_run`, and any mailshot asset tables created for PDF/image assets.
* In this document, references in the form `MAILSHOTS_REMOTE_MYSQL_DB.<table>` are normative.
* Recipient/source data reads used to resolve mailshot recipients and DSL source fields must use `.env` variable `MEMBERS_REMOTE_MYSQL_DB`.
* SMTP/IMAP credentials are sourced from WordPress-side per-user mailshot credential storage.
* If user credentials are missing, send/run actions must be blocked and the UI must direct users to the WordPress mailshot credentials page.
## 1.1 Purpose
Provide a Mailshot feature to:
* manage mailshot definitions
* preview recipient data and template rendering
* test mailshots safely
* execute full mailshot runs
## 1.2 Data Model and Dependencies
* Use `MAILSHOTS_REMOTE_MYSQL_DB.mailshots` as the source of mailshot definitions.
* Use `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_queries` as the source of recipient/data-source queries.
* Send mail using per-user SMTP/IMAP credentials from WordPress-side mailshot credential storage.
* If user credentials are missing, block send/run and direct user to the WordPress mailshot credentials page.
* Copy successfully sent messages to configured IMAP `Sent` folder.
### 1.2.1 Schema Reference: `MAILSHOTS_REMOTE_MYSQL_DB.mailshots`
| Column | Type | Null | Key | Notes |
| -------------------------- | ---------------- | ---- | --- | ----------------------------------------------------------------------------------- |
| `id` | `int(11)` | NO | PK | Auto-increment |
| `Purpose` | `varchar(100)` | YES | | Human-readable mailshot purpose |
| `DataSource` | `varchar(100)` | NO | | Data-source name matching `mailshot_queries.name` |
| `CC` | `varchar(100)` | YES | | Optional CC list |
| `BCC` | `varchar(100)` | YES | | Optional BCC list |
| `Subject` | `longtext` | NO | | Message subject template |
| `Message` | `longtext` | NO | | Message body template |
| `PDFAttachment` | `longtext` | YES | | Optional PDF attachment html template |
| `AttachmentNames` | `longtext` | YES | | JSON array of named entries from `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_attachments` |
| `PDFFilenameDerivedFrom` | `varchar(128)` | YES | | Optional data-source field used as base filename for generated PDFs |
| `ReplyTo` | `varchar(100)` | YES | | Optional reply-to address |
| `RecipientEmailField` | `varchar(255)` | YES | | Optional selected data-source field used as recipient email destination |
### 1.2.2 Schema Reference: `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_queries`
| Column | Type | Null | Key | Notes |
| ------------- | -------------- | ---- | ------ | -------------------------------------------------------- |
| `ID` | `int(11)` | NO | PK | Auto-increment |
| `name` | `varchar(100)` | NO | UNIQUE | Data-source name used by `mailshots.DataSource` |
| `dsl_text` | `longtext` | YES | | DSL sentence (source of truth) |
| `dsl_version` | `varchar(16)` | NO | | DSL version marker; defaults to `v1` |
| `sql` | `longtext` | NO | | Legacy column retained; compile target not user-authored |
## 1.3 Navigation and Pages
Under `Mailshot`, provide child pages:
* `Mailshots`
* `Assets`
* `Mailshot Recipients Preview`
* `Mailshot Test`
* `Run Mailshot`
* `Download PDF`
Under `Mailshot > Assets`, provide:
* `Mailshot Data Source > Data Sources`
* `PDF Assets`
* `Attachments`
## 1.3.1 Attachments Data Model
Use `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_attachments` as reusable uploaded attachments with:
* `id`
* `name` (unique)
* `file_name`
* `mime_type`
* `file_bytes`
* `created_at`
* `updated_at`
Persist selected named attachments on each mailshot in `MAILSHOTS_REMOTE_MYSQL_DB.mailshots.AttachmentNames` (JSON array of attachment names).
## 1.4 Mailshots Page (`MAILSHOTS_REMOTE_MYSQL_DB.mailshots` CRUD)
### 1.4.1 Data Pane
* Display rows from `MAILSHOTS_REMOTE_MYSQL_DB.mailshots`.
* In the list/table view, hide internal `id` and display user-facing columns (`Purpose`, `DataSource`, `Subject`).
* Provide `Sort by` and direction controls for the list/table view.
* Sortable list columns are `Purpose`, `DataSource`, and `Subject`; the `Actions` column is not sortable.
* Default list sort is `Purpose` ascending.
* Preserve selected sort when editing, saving, duplicating, or deleting from the mailshot list.
* Provide editing for all mutable fields.
* Track whether the edit form has unsaved changes and warn before closing/quitting an edit with unsaved changes.
* Use a separate pane for editing the fields.  Note that the Message field is multiline html-formatted and should be edited using an html edit control.  The design of the page should maximise room for this field
* Provide a separate html edit control for `PDFAttachment` (optional template) using the same html editing features as `Message`.
* Message and PDF Attachment html editors should open in overlay panes to maximise editing room.
* Provide `PDF Filename Derived From` dropdown populated from available data-source fields for the selected `DataSource`.
* Persist `PDF Filename Derived From` as `MAILSHOTS_REMOTE_MYSQL_DB.mailshots.PDFFilenameDerivedFrom`.
* Provide `Recipient Email Field` dropdown populated from available data-source fields for the selected `DataSource`.
* Persist `Recipient Email Field` as `MAILSHOTS_REMOTE_MYSQL_DB.mailshots.RecipientEmailField`.
* If `RecipientEmailField` is blank, show an inline warning that the mailshot cannot be used to send a mailshot; PDF-only workflows may leave it blank.
* The Data Pane also include a table of mailshots,  width limited to the page width,  with truncation of fields.
* Keep editing responsive; do not trigger per-keystroke save/reload.
* Data-source pulldowns must be sorted by data-source `name` (ascending).
* Show available data-source fields in the editor and allow one-click token insertion into `Subject`, `Message`, and `PDFAttachment`.
* Token insertion must generate Twig tokens using `{{ ... }}` syntax (not `m_...` syntax).
* Field insert token format default is `{{ field_name }}` where `field_name` is a canonical safe field token derived from the selected source field.
* `Subject`, `Message`, and `PDFAttachment` are always rendered through Twig at render/send time.
* In editor pane, provide `Attachments` dropdown (initially blank) sourced from `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_attachments.name`.
* Provide `Add selected attachment` button to append selected item into mailshot attachment list.
* Allow removing previously selected attachments from the mailshot attachment list.
### 1.4.2 Action Pane
* Provide `Save` button.
* Provide `Quit`/close action for leaving the edit pane; if unsaved changes exist, require confirmation before closing without saving.
* Provide `New` button to add a new `MAILSHOTS_REMOTE_MYSQL_DB.mailshots` row.
* Provide per-row `Delete` button.
* On per-row `Delete`, require confirmation before delete is applied.
* Provide per-row `Duplicate` button in the list/table.
* `Duplicate` must copy all mailshot definition fields into a new row and assign a unique purpose:
* first choice: `Copy of <original purpose>`
* subsequent collisions: `Copy of <original purpose> (2)`, `Copy of <original purpose> (3)`, and so on
* blank original purpose fallback: `Copy of Mailshot #<source id>`
* resulting purpose must fit the `Purpose varchar(100)` column.
* After successful duplicate, redirect to the edit modal for the newly-created copy.
### 1.4.3 Validation
* Require `DataSource` to match an existing `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_queries.name`.
* Prevent save when `Subject` is blank.
* Allow `Message` to be blank for PDF-only workflows.
* If `RecipientEmailField` is non-blank, it must be one of the available fields for the selected `DataSource`.
* Display validation errors in the information pane.
## 1.5 Template Engine (Twig)
* Use Twig as the template engine for mailshot rendering.
* All template tokens and expressions use Twig syntax.
* Template context is the selected recipient row (plus approved helper functions/filters).
* Twig uses strict variables. Undefined variables or invalid expressions must fail rendering with a clear error.
* Default escaping policy is:
* Twig autoescape is `html` for `Subject`, `Message`, and `PDFAttachment`.
* Rendered `Subject` is HTML-entity-decoded before use as an email subject.
* Template authors may use Twig filters such as `raw` where unescaped HTML output is required.
* Control-flow tags (`{% if %}`, `{% for %}`, `{% set %}`) are supported by Twig in all template fields.
* Template context must include both original query field keys (for example `pages.Page`) and canonical safe token keys (for example `pages_page`).
* Boolean-like fields whose field name starts with `is`, `has`, or `can`, or whose field name is `selected`, `active`, `enabled`, `disabled`, or `lapsed`, should render as `Yes`/`No` when their values are recognizably boolean-ish.
## Mailshot Data Sources Page
This is defined in `requirements/mailshot_data_source.md`.
## 1.6 Mailshot Recipients Preview Page
### 1.6.1 Selection and Query Execution
* Provide control to select a data source from `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_queries`.
* On preview request, execute the selected data-source query and display resulting recipient rows.
### 1.6.2 Preview Table
* Display columns from a deterministic field contract derived from all DSL-cited sources, plus any additional source-native columns present in preview output.
* The deterministic field contract must include all allowed fields for each cited source (built-in or custom).
* Provide row count and sample-limit indicator in the information pane.
* Provide per-row checkbox selection in the recipients table.
* Provide a header-level checkbox to select/deselect all currently visible rows.
* Display count of currently selected rows in the information pane/status line.
* Persist row selection state for the current browser session only (no database storage).
* Session-persisted selection must survive leaving and re-entering the page in the same browser session.
* Provide generalized recipient sorting controls (`Sort by` and sort direction) and apply them to the displayed preview rows.
* If query returns zero rows, display zero-result message (no error state).
* Provide `Download spreadsheet` action.
* On `Download spreadsheet`, generate `recipients.xlsx` from rows currently shown in the data pane and download it.
* In `recipients.xlsx`, format the header row in bold.
* In `recipients.xlsx`, auto-size each column width to fit header/data content.
### 1.6.3 Safety Rules
* Preview does not send email.
* Preview does not persist changes to recipient-source tables.
## 1.7 Mailshot Test Page
### 1.7.1 Test Setup
* Provide control to select a mailshot.
* Provide control to select one preview/test recipient row from the resolved query output.
* Recipient-row selector must also provide an `All recipients` option.
* Provide `Test email address` input for explicit destination override, defaulting to configured `.env` value `MAILSHOT_TEST_TO_DEFAULT` when set.
* If `MAILSHOT_TEST_TO_DEFAULT` is unset/blank, initialize `Test email address` as blank.
* After successful render-only test, provide an `Open Render Preview` action.
* Render preview opens in an overlay/modal and displays rendered `Message` and rendered `PDFAttachment` side by side in sandboxed iframe previews.
* Render preview includes rendered `Subject` as modal context.
### 1.7.2 Test Mode: Render Only (No Send)
* Provide action `Render Test (No Send)`.
* Perform full Twig template rendering for selected row.
* Display rendered `Subject`, `Message`, and `PDFAttachment` through the render preview modal.
* Render-only UI must not store the full rendered html payload in WordPress options; large rendered payloads should be recomputed after redirect or passed by another size-safe mechanism.
* Do not send SMTP mail.
* Do not write IMAP `Sent` copy.
### 1.7.3 Test Mode: Send Test Email
* Provide action `Send Test Email`.
* Require non-blank `Test email address`.
* When `Test email address` is blank, block send and show validation error in the information pane.
* Use selected test row only for template rendering context.
* Send exactly one email to `Test email address` (not to original recipient address).
* When `All recipients` is selected, send one rendered test email per recipient row, all to `Test email address`.
* Confirmation prompt text must be context-aware:
* one-row mode: `Send one test email to the entered address?`
* all-recipient mode: `Send <n> emails to the entered address?`
* `Render Test (No Send)` must require a specific recipient row (not `All recipients`).
* Copy sent test email to IMAP `Sent`.
* Display success/failure status and timestamp.
* Send-test UI must keep the user on the Mailshot Test page and display same-pane feedback. If the server returns non-JSON or an HTTP error, show an inline error rather than navigating to a WordPress critical-error page.
* Apply the configured Download PDF memory limit, when valid, before render/send test actions that may render PDF attachments.
## 1.8 Run Mailshot Page
### 1.8.1 Pre-Run Validation
* Require selected mailshot.
* Require resolved recipient query.
* Block run when recipient query returns zero recipients.
* Display validation failures and do not start run when validation fails.
### 1.8.2 Execution Semantics
* On `Run Mailshot`, execute recipient query and iterate recipients.
* For each recipient row:
* perform Twig template rendering
* send one email to the recipient destination email
* copy successful sends to IMAP `Sent`
* Continue processing remaining recipients after per-recipient failures.
* Provide action `Run Mailshot to Selected Rows` to run only rows selected in `Review Recipients`.
* `Run Mailshot to Selected Rows` must use session-persisted selected recipient keys from `Review Recipients` (same browser session, no database storage).
* If no selected rows are available for the selected mailshot data source, block action with explicit validation message.
* Run page must include hint text: `Select rows for mailshot in Review Recipients page.` with a link to the Review Recipients admin page.
### 1.8.3 Run Results
* Display live or final counters:
* attempted
* sent
* failed
* skipped (if applicable)
* Provide downloadable or copyable run log in the UI.
* Include per-recipient failure reason where available.
### 1.8.4 Last Run Retry Table
* Persist the outcome of the most recent run only in `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_last_run`.
* Clear `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_last_run` when starting a new `Run Mailshot` execution.
* Each row in `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_last_run` must store:
* `mailshot_id`
* `recipient_key` (stable non-email identifier)
* `recipient_key_field` (source field used to derive key)
* `recipient_email_last` (most recent resolved destination email)
* `status`
* `error_message` and/or `warning_message`
* `attempt_count`
* `last_attempt_at`
* Run Mailshot page must display the latest rows for the selected mailshot from `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_last_run`.
* Run Mailshot page must provide:
* `Retry Failed Sends` button to retry all failed rows from the selected mailshot.
* per-row `Retry` button to retry one failed row by recipient key.
* Retry must resolve recipient data from the current data source and current template values at retry time (not snapshot payload from original run).
* Email address must not be used as the retry key.
## 1.9 Download PDF Page
### 1.9.1 Setup and Validation
* Provide control to select a mailshot.
* Require selected mailshot.
* Require resolved recipient query.
* Block generation when recipient query returns zero recipients.
* Block generation when selected mailshot has blank `PDFAttachment` template.
* Merged PDF generation requires Ghostscript (`gs`) and PHP `exec()` availability.
* ZIP generation uses PHP `ZipArchive` when available; if unavailable, it requires a `zip` CLI binary and PHP `exec()` availability.
### 1.9.2 Output Options
* Provide action to generate and download individual recipient PDFs as a single `.zip`.
* Provide action to generate and download a single merged PDF containing all recipient PDFs.
* Use html-to-pdf conversion of rendered `PDFAttachment` content per recipient row.
* Downloaded merged PDF filename must be based on the selected mailshot `Purpose`, sanitized to safe filename characters, with suffix `_merged.pdf`.
* Downloaded ZIP filename must be based on the selected mailshot `Purpose`, sanitized to safe filename characters, with suffix `_pdfs.zip`.
* If `Purpose` is blank or cannot be loaded, use fallback base name `mailshot_<id>`.
* PDF rendering must use Dompdf with default font `DejaVu Sans` and font subsetting enabled.
* Before Dompdf rendering, normalize contact-symbol characters that commonly fail in PDF fonts:
* `📞` and `☎` render as a `☎` symbol pinned to `DejaVu Sans`
* `📧` and `✉` render as a `✉` symbol pinned to `DejaVu Sans`
* Template authors should still prefer PDF-safe symbols (`☎`, `✉`) over color emoji symbols.
### 1.9.3 Results and Logging
* Display generation success/failure status in the UI.
* Generate downloadable files directly (no per-row log table required on this page).
## 1.10 Mailshot Queries Page
Provide a CRUD page for the `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_queries` table.
### 1.10.1 DSL Built-in Sources
Built-in DSL sources include:
* `contacts`
* `accounts`
* `renewals`
`renewals` is a first-class source and must be joinable to:
* `accounts` via `renewals.account_id = accounts.ID`
* `contacts` through accounts (`renewals.account_id = contacts.Accountid`)
### 1.10.2 DSL Renewal and Membership Filters
DSL filters include:
* `selected-renewal`
* `pending-renewal`
* `fen1-contact`
* `primary-contact`
* `member-or-affiliate-or-parish-council`
`selected-renewal` uses saved field values (`renewals.selected = true`).
`pending-renewal` filters rows to `renewals.status = 'pending'`.
## 2.0 No-Effect and Error Handling
* If `Save` is clicked with no changes, no effect.
* If `Quit`/close is clicked with no changes, close without confirmation.
* If a selected mailshot references a missing `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_queries` row, block preview/test/run and display error.
* If SMTP send fails for one recipient, record error and continue with next recipient.
* If IMAP copy fails after successful SMTP send, mark warning for that recipient.
* For any DSL sentence, all fields from all cited sources must be available for token insertion and template rendering; missing values may render empty, but fields must not disappear from mailshot field availability.
* Twig template syntax errors must not crash the page or run; show clear error identifying mailshot and template field (`Subject`, `Message`, or `PDFAttachment`).
* Default behavior on template syntax error is fail-fast for that recipient (skip send for recipient, continue run for others) and record error.
* Retry actions must update `MAILSHOTS_REMOTE_MYSQL_DB.mailshot_last_run` rows in-place (incrementing `attempt_count` and updating status/messages/timestamp).
### 2.0.1 IMAP Sent-Copy Contract
* Use per-user configured IMAP sent-folder path from WordPress mailshot credentials.
* IMAP folder names must be encoded/decoded using IMAP UTF-7 rules.
* For each successful SMTP send, attempt a single IMAP append for sent-copy persistence.
* IMAP append failure must not change SMTP send outcome; classify as `warning`, not `failed`.
* IMAP append retries are not automatic in run loop; retry occurs only via explicit operator retry action.
* Sent-copy operations must be idempotent per attempt id; duplicate append for the same attempt id is not allowed.
## 2.1 PDF Assets
### 2.1.1 Purpose
Provide reusable image assets that can be inserted into `PDFAttachment` html templates using Twig syntax and resolved at PDF render time.
### 2.1.2 Navigation and Page
Under `Mailshot`, provide page `PDF Assets`.
### 2.1.3 Data Model
Provide CRUD storage for PDF assets with at least:
* `id` (primary key)
* `name` (unique asset identifier used in token marker)
* `file_name` (original upload filename)
* `mime_type`
* `file_bytes` (or storage reference)
* `width_mm`
* `height_mm`
* `justification` (`left`, `right`, `in-place`)
* `created_at`
* `updated_at`
### 2.1.4 PDF Assets Page Behavior
* List assets in a table.
* Support `Create`, `Read`, `Update`, `Delete`.
* Create/update requires image bytes, provided either by file upload or by direct base64 entry.
* Edit dimensions in millimetres.
* Edit justification (`left`, `right`, `in-place`).
* Validate name, filename-derived or explicit MIME type, positive dimensions, allowed justification, and valid base64 image bytes.
### 2.1.5 Mailshots PDF Editor Integration
* In the `PDF Attachment` editor overlay, add an insert-asset action with dropdown of available assets.
* On insert, place Twig token syntax at cursor position.
* Default insert format is `{{ pdf_asset('asset_name') }}`.
* Asset names used in inserted tokens must be escaped safely as Twig string literals; the saved asset name itself is not renamed during insertion.
### 2.1.6 Rendering Behavior
* On rendering `PDFAttachment` html (Mailshot Test preview/send, Run Mailshot, Download PDF), resolve each inserted PDF asset Twig token to rendered image html for that asset.
* Render image using configured dimensions (`width_mm`, `height_mm`) and configured justification.
* If template references a missing asset, rendering must fail with a clear error naming the missing asset.
* If `pdf_asset()` is unavailable, the asset has no readable bytes, dimensions are invalid, or justification is invalid, rendering must fail with a clear error.
* PNG assets require either GD PNG support or ImageMagick `convert` for runtimes where Dompdf cannot consume the PNG bytes directly; otherwise rendering must fail with a clear error.
### 2.1.7 PDF Filename Derivation
* For each rendered recipient pdf (download and email attachment), if `PDFFilenameDerivedFrom` is set, use row value from that field as base filename.
* Sanitize filename to safe filename characters.
* If `PDFFilenameDerivedFrom` is blank or field value resolves blank, use fallback naming strategy.
* Ensure filename uniqueness within a generated batch (append numeric suffix as needed).
## 2.2 PDF Assets Decisions
* Asset binaries are stored in DB table (`LONGBLOB`) for portability and backup simplicity.
* Supported filename-derived MIME types are `jpg/jpeg`, `png`, `gif`, `webp`, `svg`, `bmp`, `tif`, and `tiff`.
* Scope/ownership: global asset library, editor-capable users (WordPress `edit_pages`) may CRUD.
* Asset naming: unique case-insensitive `name`; renaming does not auto-migrate existing asset insertions in templates and should warn user.
* Marker syntax is removed. PDF asset insertion uses Twig syntax.
* Helper name is `pdf_asset(name)` as a global Twig helper, not a filter.
* Dimension/aspect behavior: fixed width and height in millimetres are emitted as inline image style.
* Justification semantics: `in-place` renders inline at marker location; `left` renders a block-aligned element on its own line; `right` floats the image right so surrounding text can fill the space to its left.
* Missing asset token behavior: fail rendering with a clear error.
* Delete behavior: delete the asset row by id; current implementation does not block deletion for in-use assets.
## 2.3 Mailshot Attachments
### 2.3.1 Page and Layout
Under `Mailshot > Assets`, provide page `Attachments`.
* Data pane: table of attachments sorted by `name`.
* CRUD pane (right): edit `name` and uploaded file.
* Provide actions: `Save`, `New`, `Delete`.
### 2.3.2 CRUD and Validation
* `name` is required and unique.
* Create/update requires file bytes, provided either by file upload or by direct base64 entry.
* Supported filename-derived MIME types are `txt`, `csv`, `pdf`, `doc`, `docx`, `odt`, `rtf`, `html`/`htm`, `json`, `xml`, `jpg`/`jpeg`, `png`, `gif`, `webp`, `svg`, and `zip`.
* Delete behavior: delete the attachment row by id; current implementation does not block deletion for in-use attachments.
### 2.3.3 Send/Run Behavior
* On `Mailshot Test > Send Test Email`, include all selected named attachments.
* On `Run Mailshot`, include all selected named attachments for each recipient email.
* If a selected attachment name is missing, continue send/run and record warning.
Reference in New Issue View Git Blame Copy Permalink