Customer Mail History

Flow ID: AD-41 Module(s): eshop Complexity: Medium Last Updated: 2026-09-29

Business Context

The customer message history system provides a durable log of all emails and SMS messages sent to customers: order confirmations, shipping notifications, promotional messages, password resets, gift cards, and transactional communications. Each message's full HTML body (for email) or text (for SMS) is stored for compliance, customer support, and audit purposes.

The system has two independent write paths that converge on the same table:

1. Legacy path (high-volume): The internal mailer (Adv_mailer) and SMS helpers (sms_helper, shopmodule_helper) call customer_message_history_model::addRecord() directly on every send, without validation.
2. REST path (low-volume, admin-operated): POST /rest/customer/customer-message-history writes via the modern Domain layer's WriteService, which validates input through a Validator before persisting.

Administrators view message history per-customer on the customer detail page (legacy UI). A separate REST endpoint provides filterable, sortable, paginated list and item access.

A retention job (ClearOldEmails) removes records older than 15 days (configurable) to prevent table bloat.

---

API Reference

REST Endpoints (Modern Layer)

Method	Path	Action	Auth	Roles	Responses
GET	/rest/customer/customer-message-history	index	backend	ADMIN, MARKETING	200 Collection
GET	/rest/customer/customer-message-history/item	item	backend	ADMIN, MARKETING	200 Resource, 404
GET	/rest/customer/customer-message-history/{id}	show	backend	ADMIN, MARKETING	200 Resource, 404
POST	/rest/customer/customer-message-history	store	backend	ADMIN, MARKETING	201, 400, 422
POST	/rest/customer/customer-message-history/{id}	update	backend	ADMIN, MARKETING	200, 400, 404, 422
DELETE	/rest/customer/customer-message-history/{id}	destroy	backend	ADMIN, MARKETING	200, 404

All endpoints are available under a (\w{2})/ locale prefix. Routes registered at application/config/rest_routes.php:681-695. Policy at application/config/rest_policies.php:870.

Versioning: Available under /rest/v1/customer/customer-message-history (v1 active, released 2026-04-15, changelog v1.16 at application/config/rest_api_versions.php:44).

Filters (all exact-match): id, userId, type, messageType. Sorts: id, datetime. No default sort; unsorted requests return MySQL-arbitrary order.

Relation: customer (BELONGS_TO, unrestricted ?with=customer since policy declares no relations allowlist).

Legacy Storefront Route

Route	Controller	Method	Description
mail_history/view/{id}	Adv_customer_mail_history::view()	GET	Render stored email HTML body (unauthenticated)

Route registered at application/config/routes.php:610. Note: there is no (\w{2})/ locale-prefixed variant.

Legacy Admin Surface

Mail history is displayed on the customer detail page (admin). No separate listing page. Loaded by Adv_customers_admin::getCustomerEmails($customerId) at ecommercen/eshop/controllers/Adv_customers_admin.php:394-400, invoked from viewExtras() at :288.

---

Code Flow

Recording a Message

Legacy path (high-volume: email, SMS, gift-card sends):

1. Adv_mailer::addEmailToCustomerHistory() (:813-827) → calls customer_message_history_model->addRecord() (unvalidated)
2. sms_helper::sendSms() (:37-45) → addRecord() (unvalidated)
3. shopmodule_helper::sendSmsForOrder() (:409-417) → addRecord() (unvalidated)
4. All three routes: addRecord($customerId, $type, messageChannel, $subject, $message) → $this->db->insert() directly

REST path (admin-operated, low-volume):

1. POST /rest/customer/customer-message-history → CustomerMessageHistory::store()
2. → WriteService::create() → Validator::validateForCreate() (enforces schema) → WriteRepository::insert()
3. POST /rest/customer/customer-message-history/{id} → update() → Validator::validateForUpdate() (enforces schema on non-null fields)
4. On validation failure: HTTP 422 with per-field errors map

Viewing a Message (Storefront)

1. mail_history/view/{id} → Adv_customer_mail_history::view($id) (no auth check)
2. → customer_message_history_model::getCustomerEmail($id) → $this->db->where('id', $id)->get() (selects email_body AS email)
3. → echo $data->email; (raw HTML, no template wrapping)

Viewing Message History (Admin)

1. Admin customer detail page loads Adv_customers_admin::getCustomerEmails($customerId) (:394-400)
2. → customer_message_history_model::getCustomerEmails($customerId) → $this->db->get_where() (no ORDER BY; selects all columns including email_body longtext)
3. Returns all rows; admin view paginates client-side (DataTables at application/views/admin/footer_js.php:3021-3034)
4. Admin table displays: Date sent, Subject, Type (renders message_type — the delivery channel), Choices (preview link)

Cleanup Job

1. Daily at 00:30 server time: ClearOldEmails runs
2. Default: deletes rows where datetime <= (now - 15 days)
3. Retention can be overridden via job option days (client-configurable at application/config/jobs.php:41)
4. Fallback entry point: Cronjob::clearOldEmails() (:741-746) hardcodes -15 days and ignores options

---

Data Model

shop_customer_message_history

Column	Type	Null	Default	Semantics
id	int(11)	NOT NULL	AUTO_INCREMENT	Primary key
user_id	int(11)	NOT NULL	—	Customer ID (logical FK to shop_customer.id, no FOREIGN KEY constraint)
datetime	datetime	NULL	NULL	Send timestamp. Legacy writers always set date('Y-m-d H:i:s'); REST writer leaves NULL unless supplied. NULL rows are immortal (never purged by retention job) and render as 01-01-1970 in the admin view. See Known Issues #3.
type	varchar(255)	NOT NULL	—	Message category/purpose — e.g. ORDER_ON_STORE, GIFT_CARD, GIFT_CARD_INFORM_CUSTOMER, or the mailer's $emailType. Not constrained by enum; any string ≤255 chars accepted.
message_type	varchar(50)	NOT NULL	—	Delivery channel — EMAIL or SMS, per src/Domains/Customer/CustomerMessageHistory/MessageChannel.php:5-9. The REST Validator (post-#497) enforces length and constrains to MessageChannel enum membership — a case-sensitive tryFrom() with no casing normalisation, so only the exact strings EMAIL/SMS are accepted on write. Legacy writers always use MessageChannel::EMAIL->value or MessageChannel::SMS->value.
subject	varchar(255)	NULL	NULL	Subject line (for email)
email_body	longtext	NULL	NULL	Full rendered HTML body (email) or SMS text

Indexes: PRIMARY (id), KEY datetime, KEY user_id, KEY type. Note: no index on message_type despite being an exposed REST filter.

Schema source: database/initial/initial.sql:1195-1207. No Phinx DDL migration; only a data patcher at database/migrations/20260713142705_backfill_customer_message_history_message_type.php (delegates to patches/BackfillCustomerMessageHistoryMessageType.php:22-28) which corrected rows corrupted by a null-coalesce bug (#434).

Charset: utf8 (not utf8mb4). The Validator uses mb_strlen(..., 'UTF-8') to measure character length (not byte length) to accommodate multibyte scripts.

---

Domain Layer

Modern Domain (src/Domains/Customer/CustomerMessageHistory/)

File	Purpose
Repository/Entity.php	BaseEntity docblock property map
Repository/Repository.php	BaseRepository, table shop_customer_message_history
Repository/RepositoryConfigurator.php	Declares customer BELONGS_TO relation to CustomerRepository on user_id
Repository/WriteRepository.php	BaseWriteRepository, handles inserts/updates/deletes
Service.php	ReadService + RendersPagination; all(), item(), get() methods
WriteService.php	create(array $data), update(int|string $id, array $data), delete(int|string $id) — create() and update() call Validator before persistence; delete() is a pass-through to WriteRepository::delete() with no validation
Validator.php	Enforces: userId required/positive integer, type required/non-empty/≤255 chars (mb_strlen), messageType required/non-empty/≤50 chars, then MessageChannel enum membership (#497). Check order is required/non-empty → length cap → enum membership, so an overlength value reports the length error, not the enum error. On update the enum check sits inside the existing messageType !== null presence gate, so a partial update omitting messageType is unaffected.
WriteData.php	DTO + OpenAPI schema. Accepts both camelCase and snake_case input; emits snake_case. toArray(excludeNull: true) used on updates.
ListRequest.php	Allowed filters: id, userId, type, messageType (all exact). Allowed sorts: id, datetime. No default sort or filters.
MessageChannel.php	Enum: EMAIL, SMS. Used by all three legacy writers to fill message_type, and — since #497 — is also the write-path Validator's source of truth for messageType membership, including the allowed-value list in its rejection message (derived from MessageChannel::cases(), not hardcoded).

DI registration (autowired): src/Domains/Customer/container.php:31-37 — includes Validator::class at :36 and WriteService::class at :37.

REST Layer (src/Rest/Customer/)

File	Purpose
Controllers/CustomerMessageHistory.php	extends HandlesRestfulActions, use HandlesWriteActions. Constructor injects WriteService. Actions: index(), show(), item(), store(), update(), destroy(). OA tag at :11-26 documents relations, sorts, filters.
Resources/CustomerMessageHistory/Resource.php	Maps entity to REST response. Casts userId to (int).
Resources/CustomerMessageHistory/Collection.php	Wraps Resource collection.

DI registration (autowired): src/Rest/Customer/container.php:29-34 — wires controller with $service, $resourceClass, $collectionClass, $listRequestClass, $writeService.

Validation → HTTP: Validator thrown errors caught by HandlesWriteActions::doStore() (:43-44) and doUpdate() (:78-79) → sendValidationErrors() (from ecommercen/eshop/traits/ApiEndpointTrait.php:36-39) → HTTP 422 with {'errors': {...}} map.

Legacy Layer (ecommercen/eshop/, application/)

File	Purpose
ecommercen/eshop/models/Adv_customer_message_history_model.php:8-41	42-line model. addRecord($customerId, $purpose, $messageType, $subject, $message) → inserts row with date('Y-m-d H:i:s'). getCustomerEmails($customerId) (no ORDER BY). getCustomerEmail($id) (selects email_body AS email). deleteEmailBeforeDate($date). NO validation, NO Validator reference.
application/modules/eshop/models/Customer_message_history_model.php	Empty subclass (6 lines)
ecommercen/eshop/controllers/Adv_customer_mail_history.php:18-22	Storefront reader: view($id) → echo $data->email; (no auth check, no sanitization)
application/modules/eshop/controllers/Customer_mail_history.php	Empty subclass (6 lines)
application/models/Adv_mailer.php:813-827	Email writer: addEmailToCustomerHistory($customerId, $emailType, $subject, $message) → addRecord() with MessageChannel::EMAIL->value
ecommercen/helpers/sms_helper.php:37-45	SMS writer: sendSms() → addRecord() with MessageChannel::SMS->value
ecommercen/helpers/shopmodule_helper.php:409-417	SMS writer: sendSmsForOrder() → addRecord() with MessageChannel::SMS->value

---

Configuration

Source	Key / Setting	Citation	Effect
Job schedule	ClearOldEmails	application/config/jobs.php:33	'schedule' => '30 0 * * *', 'graceTime' => 300, 'retryTimes' => 3
Job retention default	DAYS_BEFORE	ecommercen/job/libraries/AdvClearOldEmails.php:5	15 days (not 30)
Job option schema	ClearOldEmails::getOptions()	application/config/jobs.php:193	Registers optional days int parameter
Per-client retention	options.days	application/config/jobs.php:41 (commented example)	Template for client override, e.g. 'options' => ['days' => 5]
Fallback cleanup	Cronjob::clearOldEmails()	application/controllers/Cronjob.php:741-746	Hardcodes -15 days, ignores job options (legacy entry point)
REST policy	CustomerMessageHistory::class	application/config/rest_policies.php:870	auth: backend, roles: [AUTH_ROLE_ADMIN, AUTH_ROLE_MARKETING]
REST routes	12 route patterns	application/config/rest_routes.php:681-695	Base + (\w{2})/ locale variants
Legacy route	mail_history/(.+)	application/config/routes.php:610	Routed to eshop/customer_mail_history/$1
API versioning	v1 / changelog 1.16	application/config/rest_api_versions.php:19-25, 44	Write validation / #502/#501 fixes documented at v1.16
App version	current	application/config/version.php:4	04.122.000.000

No registry keys gate this flow. No env vars. No feature flag — FeatureGuardMiddleware short-circuits because CustomerMessageHistory is not in rest_features.php:73-75 'guarded'.

Language Keys (English)

ecommercen/language/english/adv_advisable_lang.php:

• :1861 — No messages: "There are no messages"
• :1871 — Admin section header: "System messages to the customer"
• :1872 — Column header: "Date sent"
• :1873 — Column header: "Subject"
• :1874 — Column header: "Type" (renders message_type — the channel)
• :1875 — Column header: "Choices"
• :2364 — SMS label: "ORDER ON STORE"
• :2375 — Preview button: "Preview"

Translated in all 8 languages: Chinese, English, French, German, Greek, Italian, Russian, Spanish.

---

Client Extension Points

• Override model: Subclass Customer_message_history_model in application/modules/eshop/models/ to add custom fields or methods.
• Override controller: Subclass Customer_mail_history in application/modules/eshop/controllers/ to add authentication or custom rendering.
• Override cleanup schedule: Configure options.days in application/config/jobs.php (example at line 41).
• Override REST policy: Add a per-client CustomerMessageHistory::class entry to application/config/rest_policies.php (if needed).

---

Business Rules

1. No authentication on storefront view: The Adv_customer_mail_history::view() endpoint (route mail_history/view/{id}) extends Front_c and performs no auth check and no ownership check. Security relies on URL opacity (numeric auto-increment IDs). This is the primary security concern; see Known Issues #1.

2. Raw HTML output on storefront: The email body is echoed directly without sanitization or template wrapping — exactly as it was sent. Stored HTML may contain scripts, images, or tracking pixels not escaped.

3. Dual write paths with asymmetric validation: REST writes are validated through Validator; legacy addRecord() calls insert directly with zero validation. Both write to the same table. The legacy path is high-volume (every transactional email, SMS, gift-card send). The REST path is low-volume (admin-operated only).

4. Multi-channel support: message_type supports both EMAIL (with optional email_body HTML) and SMS (typically just text). Writers use MessageChannel enum to fill this field.

5. Automatic retention cleanup: Records older than 15 days (configurable) are deleted daily at 00:30. Null datetime rows are never purged and render as 01-01-1970 in the admin view. See Known Issues #3.

6. No ordering guarantee on legacy read: The legacy getCustomerEmails() query has no ORDER BY clause. Apparent chronological order is provided only by client-side DataTables sort in the admin UI. The modern REST index() also has no default sort.

7. Unbounded SELECT * on admin page load: The admin detail page pulls all message records (including longtext email_body) for the customer into PHP memory without pagination or column selection. High-tenured customers can cause OOM or performance degradation.

8. Type field is category, not channel: type column records the message purpose (ORDER_ON_STORE, GIFT_CARD, mailer emailType); message_type records the channel (EMAIL, SMS). These are distinct and must not be confused. The #501 fix (deployed in #502) corrected OpenAPI documentation to reflect this.

---

Known Issues & Security Gaps

1. #507: Unauthenticated IDOR on stored message bodies. ecommercen/eshop/controllers/Adv_customer_mail_history.php:18-22 — the view($id) endpoint performs no auth or ownership check. Sequential IDs allow enumeration of any customer's stored email bodies. Status: Unfixed. See issue #507.

2. RBAC mismatch between legacy admin and REST surfaces. Legacy admin (Adv_customers_admin.php:18-24) admits AUTH_ROLE_ADVISABLE, AUTH_ROLE_ADMIN, AUTH_ROLE_ORDERS. REST policy (rest_policies.php:870) admits AUTH_ROLE_ADMIN, AUTH_ROLE_MARKETING (+ AUTH_ROLE_ADVISABLE superuser bypass). The domain roles (MARKETING vs ORDERS) do not align between surfaces — a MARKETING user can read/write via REST but is 401'd from the legacy admin page; an ORDERS user is the reverse. This violates the convention stated at rest_policies.php:39-40 ("Roles arrays are aligned with legacy admin controller allowRole() checks."). The roles appear to have been copied from a different controller (Adv_campaigns_admin, per banner comment at :868).

3. REST-created rows with null datetime are immortal. BaseWriteRepository::insert() filters nulls before insert (:20). If a REST store() omits datetime, the column is set to NULL. Consequences: (a) deleteEmailBeforeDate() uses ['datetime <=' => $date] — NULL <= any date is NULL, so these rows are never purged. (b) Admin view does strtotime(null) → renders as 01-01-1970. Legacy path is immune (always sets datetime to date('Y-m-d H:i:s')).

4. Stored-XSS amplification via REST. Adv_customer_mail_history.php:21 echoes email_body as raw unescaped HTML on storefront origin with no script-src CSP — the only CSP the platform prescribes is the deploy-time frame-ancestors 'self'; directive (docs/changelog/Changelog.4.96.md:62), which does not restrict script execution. Previously, only internal template renders could write to this field. Now POST /rest/customer/customer-message-history (rest_routes.php:690) accepts arbitrary emailBody from an ADMIN or MARKETING token, and the result is served unauthenticated as HTML. First-class stored-XSS vector on storefront origin, gated only by marketing role.

5. view() 500s on non-numeric segment. Adv_customer_mail_history.php:18 declares view($id) untyped; getCustomerEmail(int $id) at Adv_customer_message_history_model.php:28 is typed int. Route mail_history/(.+) passes any string. /mail_history/view/abc → uncaught TypeError → HTTP 500. Same family as #440/#472 (URI page-segment type errors).

6. view() on missing ID emits warning and blank page, not 404. getCustomerEmail() returns [] on no-match (Adv_customer_message_history_model.php:35). Reading ->email on an array → PHP 8 warning, null output. No 404 response.

7. Test fixtures encode inverted type/messageType semantics. Test fixtures in multiple files encode 'type' => 'email', 'message_type' => 'order_confirmation' — exactly backwards. After the #501 correction, fixtures are incorrect and would mask a regression that swapped the columns. Sites: tests/Integration/Domains/.../ServiceTest.php:60-62,70-71,100-101,173-174,192-193,227-228; tests/Integration/Domains/.../RepositoryTest.php:29-31,62-63,111,176; tests/Unit/Rest/.../ResourceTest.php:23-24,71-72; tests/Unit/Rest/.../CollectionTest.php:52-53. Partially closed by #497: the write-path sites in tests/Integration/.../ServiceTest.php (the ones exercising WriteService/Validator) were de-inverted in that delivery — type now carries the category and message_type the channel — because the new enum check rejects the old inverted values outright. The direct-insert seeds and the RepositoryTest.php / ResourceTest.php / CollectionTest.php sites bypass the Validator entirely and remain inverted, unaffected by enum enforcement.

8. Admin "Type" column header mislabels the channel. application/views/admin/customers/view.php:814 renders the header ...emails.to.customer.type.label = "Type" over $email->message_type (the channel). The actual type column (the category: ORDER_ON_STORE, GIFT_CARD, etc.) is never displayed.

9. Unbounded SELECT * with longtext on every admin customer page load. Adv_customer_message_history_model::getCustomerEmails() is get_where($table, ['user_id' => ...]) with no column list or LIMIT. Every email_body longtext for the customer is pulled into PHP memory; the view paginates client-side (5 rows per page). No cache exists. Long-tenured customers cause OOM or slow page loads.

10. No ORDER BY in legacy read queries. Adv_customer_message_history_model.php:23 — get_where() produces MySQL-arbitrary order. Modern ListRequest sets $defaultSorts = [] (ListRequest.php:10), so unsorted REST index() is also arbitrary.

11. No index on message_type despite exposed REST filter. database/initial/initial.sql:1203-1206 indexes only id, datetime, user_id, type. REST ListRequest.php:21 exposes messageType as an exact-match filter → full table scan.

12. No FOREIGN KEY on user_id. database/initial/initial.sql:1197 — logical FK to shop_customer.id with no constraint. Deleting a customer orphans message rows. REST store() accepts any positive userId, so orphaned rows can exist. A test comment in tests/Integration/Domains/Customer/CustomerMessageHistory/RepositoryTest.php:26 wrongly asserts the constraint exists.

13. Duplicate cleanup entry points with divergent behavior. ecommercen/job/libraries/AdvClearOldEmails.php:31-39 honors options.days from the job config. application/controllers/Cronjob.php:741-746 hardcodes -15 days and ignores options. A client setting options.days => 5 gets 5 via the job queue and 15 via the Cronjob controller path.

14. SMS history truth semantics diverge between callers. ecommercen/helpers/sms_helper.php:37-45 records history before dispatch → failed SMS still logs as sent. ecommercen/helpers/shopmodule_helper.php:404-418 records history after a successful response. Two SMS paths, two different semantics for "was the message sent?"

15. No monolog channel, no observability on legacy writes. ecommercen/eshop/models/Adv_customer_message_history_model.php:18 — a failed addRecord() insert is completely silent. No structured logging. Only REST writes are audited via AuditMiddleware (to user_audit_logs, src/Rest/Middleware/AuditMiddleware.php:8); legacy writes leave no audit trail.

16. Dead property in legacy model. ecommercen/eshop/models/Adv_customer_message_history_model.php:6 — protected $orderTable = 'shop_order'; is never referenced in the 42-line file.

17. Type parameter untyped in legacy model. Adv_customer_message_history_model.php:8 — addRecord($customerId, $purpose, ...) — $customerId typed string though it's an int (coerced by non-strict-types), $purpose is untyped entirely. Callers type it as int and string respectively.

18. No cache or invalidation strategy. All reads hit the database on every request. No L0/L1/L2 cache keys exist for this table.

19. Retention has an up-to-24-hour boundary slip. ecommercen/job/libraries/AdvClearOldEmails.php:31-39 and application/controllers/Cronjob.php:741-746 both return a date-only string via date('Y-m-d', …). The deleteEmailBeforeDate() query executes datetime <= '2026-07-07', which MySQL widens to '2026-07-07 00:00:00'. Messages sent later on the boundary day survive an extra cycle — effective retention is 15–16 days, not a clean 15. Not yet filed as a GitHub issue.

20. Stored-XSS in admin customer-detail mail-history table. application/views/admin/customers/view.php:823 echoes <?= $email->subject; ?> raw and unescaped, and :824 echoes <?= $email->message_type; ?> raw and unescaped. Since POST /rest/customer/customer-message-history (:690, rest_routes.php) accepts arbitrary subject from an ADMIN or MARKETING token, this is a second stored-XSS sink on the admin origin (distinct from the existing #4 storefront email_body sink). The attack surface is ADMIN/MARKETING role only, but the impact is a compromised admin origin for any admin viewing the customer detail page.

---

Tests

File	Lines	Coverage
tests/Unit/Domains/Customer/CustomerMessageHistory/ValidatorTest.php	230	17 cases: create validation (missing userId/type/messageType, empty, non-positive userId, overlength, boundary 255/50), update validation (partial, full, explicit-empty, negative, overlength), multibyte (200 Greek chars pass, 256 fail)
tests/Unit/Domains/Customer/CustomerMessageHistory/ServiceTest.php	203	9 cases: all() pagination, item(), get() with various filters
tests/Integration/Domains/Customer/CustomerMessageHistory/ServiceTest.php	255	8 cases: reads + WriteService create/update/delete/round-trip
tests/Integration/Domains/Customer/CustomerMessageHistory/RepositoryTest.php	269	15 cases: get/match/count/sort/pagination + customer relation loading
tests/Unit/Rest/Customer/Resources/CustomerMessageHistory/ResourceTest.php	77	4 cases: resource structure, nulls, userId int cast, null handling
tests/Unit/Rest/Customer/Resources/CustomerMessageHistory/CollectionTest.php	58	3 cases: collection wrapping

Coverage Gaps

• Zero tests on the legacy path: No test exercises Adv_customer_message_history_model (addRecord, getCustomerEmails, getCustomerEmail, deleteEmailBeforeDate), Adv_customer_mail_history::view(), AdvClearOldEmails, or Adv_mailer::addEmailToCustomerHistory().
• Zero controller/HTTP-level tests: No test asserts that a 422 is actually sent over the wire, or that RBAC policy rejects a non-ADMIN/MARKETING token. The 422 mapping is verified only by reading code.
• Zero retention job tests: No test of the cleanup job or its NULL-datetime blind spot.
• Test fixtures encode inverted semantics: See Known Issues #7.

---

Related Flows

• AD-04 Customer Management Admin — Customer detail page displays email history.
• SY-24 Email Dispatch — High-volume writer; describes the three legacy call sites that populate this table.
• SY-01 Cron Framework — ClearOldEmails job runs on the framework's schedule.
• AD-09 Gift Rules — Gift-card sends are a writer of this table (via Adv_mailer).