Storage for individual Mosaico mailings

[totten]

If you consider both traditional content (body_html) and newer Mosaico-based content (metadata + content + rendered HTML), and consider both the use-cases of templates and individual mailings, then we have four scenarios for storing things in the database:

1. Traditional-style templates (e.g. civicrm_msg_template records with body_html)
2. Traditional-style mailings (e.g. civicrm_mailing records with body_html)
3. Mosaico-style templates (e.g. civicrm_mosaico_msg_template with template, metadata, html plus civicrm_msg_template)
4. Mosaico-style mailings

There's currently no data-storage for saving a Mosaico-style mailing in its canonical/pristine form (without additional records in civicrm_msg_template and civicrm_mosaico_msg_template and synchronization logic). As we simplify the workflows for Mosaico, we need some way to pristinely store a Mosaico-style mailing.

I'm intending to work on this next week but wanted to verbalize some approaches first (partially for my own clarity - but also in case @deepak-srivastava or other folks see additional issues). The approaches are:

• (Core columns) Update civicrm_mailing
  • Schema: Add new columns:
    • template_type (varchar), e.g. traditional or mosaico
    • template_options (longtext), e.g. JSON blob containing Mosaico's metadata and data and html
  • Pro: In a core screen like "Mailings => Draft and Unscheduled" or "Scheduled and Sent Mailings", the edit/reuse actions can look at template_type and open a proper editor.
  • Pro: The Mosaico extension doesn't need to define any tables. It's part of core.
  • Con: Depends on a core patch, which means it's only compatible with future releases of civicrm-core (e.g. 4.7.15?).
  • Pro (speculative): In the future, other templating changes can use the fields template_type and template_options (e.g. supporting other formats like Twig or refining the Smarty options or fixing the way CiviMail's Smarty-integration breaks the Smarty cache).
• (New table) Add another table, civicrm_mosaico_mailing:
  • Schema:
    • id (int)
    • mailing_id (int, FK to civicrm_mailing for 1:1 relation)
    • template, metadata, html (same as civicrm_mosaico_msg_template)
  • Pro: Easier to support across multiple versions of core (because the extension defines its own schema).
  • Con: In a core screen like "Mailings => Draft and Unscheduled" or "Scheduled and Sent Mailings", it's hard for the edit/reuse actions to go to the proper editor. The information is not available in the core database.
• (Dynamic entity relation) Rework the table civicrm_mosaico_msg_template so that it can store Mosaico-style templates or mailings:
  • Schema: Rename the table to civicrm_mosaico_template and use entity_table/entity_id, e.g.
    • id
    • entity_table, entity_id (e.g. civicrm_mailing,200 or civicrm_msg_template,50)
    • template, metadata, html (same as civicrm_mosaico_msg_template)
  • Pro: Easier to support across multiple versions of core (because the extension defines its own schema).
  • Con: In a core screen like "Mailings => Draft and Unscheduled" or "Scheduled and Sent Mailings", it's hard for the edit/reuse actions to go to the proper editor. The information is not available in the core database.
  • Pro (speculative): In the future, other Mosaico-Civi integrations (e.g. scheduled reminders or personal emails) can use the same civicrm_mosaico_template with different entity_table values.

Personal preference is to define core columns because it addresses

(Tangential Observation: None of those options above discuss the hash_key. That field is part of Mosaico's localStorage adapter, but they have recommendations which allow you to ignore the localStorage and hash_key stuff when you have your own storage mechanism. See also: voidlabs/mosaico#73 (comment) IMHO, keeping the localStorage/hash_key makes it hard to think about the dataflow because client-state and server-state may not match.)

[deepak-srivastava]

I would prefer core columns approach as well - also provides room for other possible template builders in future e.g - https://beefree.io/ (template_type = 'beefree').

With the core support would it be better to have code structured in a way that it also allows other possible template builders to inject / take control? Need not be perfect at this point, but can evolve as new template builders appear / integrated? And in doing so it could also template builders to use their own tables if needed?

And totally agree on the idea of getting rid of hash_key and local storage.

[totten]

Yes, we'll need a way for the alternative template builders to inject themselves. Generally, expect two touch-points:

• Design/Composition: When loading the mailing in the composition UI (civicrm/a/#/mailing/123), we need to use a different Angular screen (e.g. change ~crmMailing/EditMailingCtrl/2step.html to ~crmMosaico/mosaico.html). (I've been using some small/experimental patches for this locally, but it only work if all mailings have been designed via Mosaico. Will revise when we have the schema for template_type.)
• Delivery/Mail-Merge: When sending out mailings, we need to use different logic for composition the message. (Ex: uk.co.vedaconsulting.mosaico should make the determination about when/how/whether to perform Smarty evaluation on the outgoing message.)

Will report back when I have more details.

[totten]

See also: https://issues.civicrm.org/jira/browse/CRM-19690

[totten]

It was good to have some discussion/evaluation here, but -- based on the chosen design -- the locus of attention on this shifted to CRM-19690, so I think we can close this.