Creating and storing email template in DB

[Nishit-Gopani]

Create a unit test for testing
There won't any endpoint for creating & storing email template. Create a service method, POJOS...

[shah-tejas]

Create a way to store / fetch email templates in DB. Email templates will be used for different actions.

Example: For User registration we want to send emails to user for different actions like:

1. User Account Created: Email user informing their account is created and will be in pending status till reviewed by a community member.
2. User Confirmed: Email user informing their account was approved by a community member.
3. User Rejected: Email user their account was rejected with the Reject message.

Going down the line, there would be more actions that would trigger these emails. We need a generic way to store and retrieve these email templates.

Acceptance Criteria:

• Email templates should be stored in DB, preferably a separate table.
• Templates should be static and should be fetched based on an enum / code.
• Templates should accept dynamic values in terms of parameter, like name, email, etc.
• Unit tests that test features like fetching of Email template by enum, injecting parameter values to templates.

[darshan-dedhia]

After analyzing the change and possible solutions I have few questions:

1. Should we be storing templates in HTML format or just plain text format.
2. Also do we need to set the database column size that will hold up the body to certain limiting characters i.e VARCHAR(n) or should we keep it variable unlimited for now i.e VARCHAR. This actually depends on how big a template we expect to be handled.

Proposed Schema for Email template table: TABLE NAME: email_template

Columns: _templateid (NUMBER): Unique primary key _templatename (VARCHAR(255)): Unique template name for identification _emailsubject(VARCHAR(255)): Subject to be used for sending an email _emailbody(VARCHAR): Email body's template _templatestatus CHAR(1): (Optional) We can have statuses like A-Active, P- Purged, etc. if needed.

How would we be storing tokens for dynamic replacement in templates? Email templates would have generalized tokens like "name" which would be replaced by the user's original name on processing. Hence we need to use some tokenizers for their identification in the templates. Planning to use this format: ^%NAME%^

Comment if anything needs to be changed or added.

[shah-tejas]

1. It can be either html or rich-text. HTML would be preferred. From database perspective it should just be text right?
2. The database column should be of type text (https://www.postgresql.org/docs/current/datatype-character.html)

I like the proposed schema for Email Template. It looks good enough.

For token replacement, we can have the tokens in any format ${} or even % % works. @Nishit-Gopani mentioned he knows a way to inject the token values, maybe he can suggest a better format for tokens. But from database perspective it does not matter, it should support any format for tokens.

[Nishit-Gopani]

Darshan, Proposed schema looks good.

The way I am using dynamic email templates is: I create an HTML file with text and possible tokens as ${} and store it at some location(Network folder) and then in backend I use thymeleaf template resolver with the data to plug tokens with actual data then I convert to Xhtml using Tidy.

In your case, we can store template in DB

Also there are also couple of solution/lib available online. I am adding some example in our meeting docs have a look.

[shah-tejas]

A nice way to resolve email templates in spring boot: https://www.baeldung.com/spring-email-templates