Email Templates Management Design

Overview

Add template management functionality to the /email-schedules page, enabling admins to create, manage, and reuse email content.

Two Template Types

1. Snippet Templates - Reusable email content fragments with tag-based organization
2. Variable Format Templates - Define HTML output format for dynamic variables like {{summary_flow_meter}}

Key Features

• Shared templates among all admins
• Tag system for flexible categorization
• Insert snippets at cursor position (quick insert or preview first)
• HTML template editor with field insertion and live preview
• Uses existing variables only ({{summary_flow_meter}}, {{date_range_flow_meter}})

---

Data Model

Snippet Templates Table

CREATE TABLE email_snippet_templates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  subject text,                      -- Optional subject line
  body text NOT NULL,                -- Rich text content (HTML)
  tags text[] DEFAULT '{}',          -- Tag array for categorization
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Index for tag filtering
CREATE INDEX idx_snippet_templates_tags ON email_snippet_templates USING GIN (tags);

-- RLS: Admin only
ALTER TABLE email_snippet_templates ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins can manage snippet templates"
  ON email_snippet_templates
  FOR ALL
  USING (is_admin())
  WITH CHECK (is_admin());

Variable Format Templates Table

CREATE TABLE email_variable_templates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  variable_name text NOT NULL,       -- e.g., 'summary_flow_meter'
  name text NOT NULL,                -- Display name for the format
  html_template text NOT NULL,       -- HTML with Handlebars placeholders
  is_default boolean DEFAULT false,  -- Default format for this variable
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Ensure only one default per variable
CREATE UNIQUE INDEX idx_variable_templates_default
  ON email_variable_templates (variable_name)
  WHERE is_default = true;

-- RLS: Admin only
ALTER TABLE email_variable_templates ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins can manage variable templates"
  ON email_variable_templates
  FOR ALL
  USING (is_admin())
  WITH CHECK (is_admin());

---

Page Structure

Tab Layout

/email-schedules
├── [Tab] Schedules       (existing functionality)
├── [Tab] Snippets        (new)
└── [Tab] Variable Formats (new)

Snippets Tab

• Template list table (name, tags, updated date, actions)
• Tag filter dropdown
• Search input
• Create/Edit modal with RichTextEditor

Variable Formats Tab

• Grouped by variable name (e.g., summary_flow_meter section)
• Each variable shows its format templates
• Default format indicator
• Create/Edit modal with HTML editor + live preview

---

ComposeModal Integration

Insert Snippet Button

Add "Insert Snippet" button to RichTextEditor toolbar. Opens panel:

┌─────────────────────────────────────┐
│ Insert Snippet                  [×] │
├─────────────────────────────────────┤
│ Tags: [All ▼]  [Search...]          │
├─────────────────────────────────────┤
│ Name              Tags      Actions │
│ ─────────────────────────────────── │
│ Weekly Intro      weekly    [👁][+] │
│ Safety Notice     notice    [👁][+] │
│ Signature         general   [👁][+] │
└─────────────────────────────────────┘
  [👁] = Preview then insert
  [+] = Quick insert at cursor

Insert Variable Button

Add "Insert Variable" button to toolbar. Opens panel:

┌─────────────────────────────────┐
│ Insert Variable             [×] │
├─────────────────────────────────┤
│ {{summary_flow_meter}}          │
│   Format: [Default Table ▼]     │
│                                 │
│ {{date_range_flow_meter}}       │
│   (no format options)           │
└─────────────────────────────────┘

When a variable has multiple format templates, user can select which format to use.

---

Variable Format Editor

HTML Template Syntax (Handlebars)

<table style="border-collapse: collapse; width: 100%;">
  <thead>
    <tr style="background: #f5f5f5;">
      <th style="padding: 8px; border: 1px solid #ddd;">Date</th>
      <th style="padding: 8px; border: 1px solid #ddd;">Litres</th>
    </tr>
  </thead>
  <tbody>
    {{#each daily_summary}}
    <tr>
      <td style="padding: 8px; border: 1px solid #ddd;">{{date}}</td>
      <td style="padding: 8px; border: 1px solid #ddd;">{{litres}}</td>
    </tr>
    {{/each}}
  </tbody>
  <tfoot>
    <tr style="background: #f5f5f5;">
      <td style="padding: 8px; border: 1px solid #ddd;"><strong>Total</strong></td>
      <td style="padding: 8px; border: 1px solid #ddd;"><strong>{{total_litres}}</strong></td>
    </tr>
  </tfoot>
</table>

Available Fields (summary_flow_meter)

Field	Type	Description
site_name	string	Mine site name
total_litres	number	Total water usage
record_count	number	Number of records
daily_summary	array	Daily breakdown
daily_summary[].date	string	Date (formatted)
daily_summary[].litres	number	Daily water usage

Editor Layout

┌────────────────────────┬────────────────────────┐
│ HTML Template Editor   │ Live Preview           │
│                        │                        │
│ [Field List Panel]     │ (Rendered with real    │
│ Click to insert        │  data from database)   │
│ placeholder            │                        │
└────────────────────────┴────────────────────────┘

---

File Structure

New Files

src/features/email-schedules/
├── components/
│   ├── SnippetTemplatesTab.tsx      # Snippet template list
│   ├── SnippetTemplateModal.tsx     # Snippet create/edit modal
│   ├── VariableTemplatesTab.tsx     # Variable format list
│   ├── VariableTemplateModal.tsx    # Variable format editor
│   ├── TemplateInsertPanel.tsx      # Snippet insert panel for ComposeModal
│   └── VariableInsertPanel.tsx      # Variable insert panel for ComposeModal
├── services/
│   ├── snippetTemplateService.ts    # Snippet template CRUD
│   └── variableTemplateService.ts   # Variable format CRUD + preview data
├── hooks/
│   ├── useSnippetTemplates.ts
│   └── useVariableTemplates.ts
└── types.ts                         # Add new type definitions

supabase/
├── migrations/
│   └── 20260122_add_email_templates.sql
└── functions/
    └── send-email/index.ts          # Modify: support dynamic variable formats

Modified Files

• src/app/(admin)/(pages)/email-schedules/index.tsx - Add tab navigation
• src/features/email-schedules/components/ComposeModal.tsx - Integrate insert panels
• src/features/email-schedules/components/RichTextEditor.tsx - Add toolbar buttons

---

Technical Implementation

Handlebars Processing

Use handlebars library for template rendering:

import Handlebars from 'handlebars';

function renderVariableTemplate(
  htmlTemplate: string,
  data: Record<string, unknown>
): string {
  const template = Handlebars.compile(htmlTemplate);
  return template(data);
}

Edge Function Changes

Modify send-email/index.ts to:

1. Accept optional variable_format_id parameter per variable
2. Fetch the HTML template from email_variable_templates
3. Render using Handlebars instead of hardcoded format
4. Fall back to default format if not specified

Preview Data Service

Add method to fetch sample data for live preview:

// variableTemplateService.ts
static async getPreviewData(variableName: string): Promise<Record<string, unknown>> {
  switch (variableName) {
    case 'summary_flow_meter':
      // Fetch recent flow meter data for preview
      return FlowMeterExportService.getSummaryData(/* last 7 days */);
    default:
      return {};
  }
}

---

Out of Scope

• Adding new variable types (e.g., {{summary_dust_levels}})
• Variable parameter passing (e.g., {{summary_flow_meter:site=xxx}})
• Non-admin access to templates
• Template versioning/history

---

Implementation Order

1. Database migration (create tables)
2. Services and hooks for snippet templates
3. Snippets Tab UI (list + modal)
4. Services and hooks for variable templates
5. Variable Formats Tab UI (list + editor with preview)
6. ComposeModal integration (insert panels)
7. Edge Function modification for dynamic formats
8. Testing and refinement