Volume 2: Organizational Intelligence Platforms

Pattern 24: Template-Based Communication

Intent

Encode domain intelligence, communication best practices, and proven messaging strategies into reusable templates that enable consistent, personalized, multi-channel communication at scale while capturing organizational learning about what works and allowing non-technical staff to create effective messages without starting from scratch every time.

Also Known As

  • Message Templates
  • Communication Templates
  • Dynamic Content Generation
  • Personalized Messaging Framework
  • Template-Based Messaging

Problem

Writing effective messages from scratch every time is hard, slow, inconsistent, and wastes domain knowledge.

The blank page problem:

Sarah needs to send payment reminder to Martinez family. She opens email client:

"Uh... how should I word this? Let me think..."

30 minutes later: "Hi Martinez family, your payment is overdue. Please pay. Thanks."

Problems with this message: - Too blunt (damages relationship) - No context (why is it overdue?) - No help offered (payment plan? questions?) - Not personalized (generic) - Took 30 minutes (doesn't scale) - Lost knowledge (if Sarah leaves, next person starts over)

The inconsistency problem:

Same situation, different coordinators:

Sarah's message: "Hi! Just a friendly reminder that payment is due Friday. Let me know if you need help! 😊"

Mike's message: "PAYMENT OVERDUE. Your account will be suspended if not resolved immediately."

Same family, same situation, completely different tone! - Confusion (which is right?) - Unfairness (Sarah's families get gentle treatment, Mike's don't) - Lost learning (both reinventing wheel)

The domain knowledge loss problem:

Sarah has learned over 5 years: - Payment reminders work best when sent Tuesday morning (not Friday evening) - Mentioning specific amount and due date increases response 40% - Offering help phrase "Let me know if you need a payment plan" increases resolution 25% - Friendly tone works better than formal for payment issues - Including coordinator's direct phone number increases callbacks 60%

When Sarah leaves, all this knowledge walks out the door. ⚠️

Mike starts fresh, makes same mistakes Sarah made years ago, learns slowly through trial and error.

The multi-channel problem:

Martinez family needs contact via: - Email (primary) - SMS (urgent follow-up) - Portal notification (when they log in) - Voice message (if call goes to voicemail)

Sarah writes 4 different versions of same message: - Email: 200 words - SMS: 160 characters (different wording) - Portal: 100 words (different again) - Voice: Spoken script

Takes 2 hours, all slightly different, hard to maintain consistency!

The personalization problem:

Sarah knows personalization increases response: - Use family name - Mention specific child's name - Reference recent interactions - Acknowledge their history

But manually personalizing 50 messages takes 10 hours!

The A/B testing problem:

Sarah wants to test: - Subject line A: "Payment Reminder" vs B: "Quick Question About Payment" - Tone: Formal vs Friendly - CTA: "Pay Now" vs "Update Payment Method"

But manually creating variants, tracking which family got which, analyzing results = impossible at scale.

What we need: Template system that:

  1. Captures domain intelligence (what Sarah learned over 5 years)
  2. Ensures consistency (everyone sends messages based on best practices)
  3. Enables personalization ({{family_name}}, {{child_name}}, {{amount}})
  4. Works multi-channel (email, SMS, portal, voice from ONE template)
  5. Allows iteration (improve templates as we learn)
  6. Scales effortlessly (send 1 or 1,000 messages with same effort)
  7. Preserves knowledge (templates outlive individuals)
  8. Enables testing (A/B test built in)

Without templates: - Start from scratch every time (slow, inconsistent) - Domain knowledge lost (when people leave) - No personalization at scale (too manual) - Can't A/B test effectively - Quality varies by author

With templates: - Start from proven foundation (fast, consistent) - Domain knowledge encoded (survives personnel changes) - Personalization automatic (variables substituted) - A/B testing built in (track variants) - Quality consistent (best practices encoded)

Context

When this pattern applies:

  • Send repetitive communications (same type of message repeatedly)
  • Need consistency across staff/time
  • Want to encode best practices
  • Personalization improves outcomes
  • Multiple channels used
  • Want to A/B test messaging
  • Have domain knowledge worth preserving

When this pattern may not be needed:

  • Every message is unique (no repetition)
  • Only one person writes messages (no consistency issue)
  • Very small scale (<10 messages/month)
  • No personalization needed

Forces

Competing concerns:

1. Flexibility vs Consistency - Flexibility = adapt to unique situations - Consistency = proven approach every time - Balance: Templates for common cases, custom for exceptions

2. Personalization vs Effort - Personalization = better response but takes work - Generic = easy but less effective - Balance: Variable substitution (automatic personalization)

3. Detailed vs Simple - Detailed templates = cover edge cases, complex - Simple templates = easy to understand, limited - Balance: Core template + optional sections

4. Single-Channel vs Multi-Channel - Single-channel templates = simple, channel-specific - Multi-channel = complex but DRY (Don't Repeat Yourself) - Balance: Shared content, channel-specific rendering

5. Static vs Dynamic - Static = predictable, simple - Dynamic = context-aware, complex - Balance: Variables + conditional sections

Solution

Build comprehensive template system with:

1. Template Structure

Template = {
  // Metadata
  template_id: unique_identifier,
  template_name: "payment_reminder_friendly",
  category: "payment",

  // Content (multi-channel)
  email: {
    subject: "{{subject_line}}",
    body: "{{email_body}}",
    from_name: "{{coordinator_name}}"
  },
  sms: {
    message: "{{sms_text}}"
  },
  portal: {
    title: "{{notification_title}}",
    message: "{{portal_text}}"
  },

  // Variables
  variables: [
    { name: "family_name", required: true },
    { name: "amount", required: true, format: "currency" },
    { name: "due_date", required: true, format: "date" }
  ],

  // Intelligence
  tone: "friendly",
  best_send_time: "Tuesday 9am",
  tested_variants: ["A", "B"],
  performance: {
    open_rate: 68%,
    response_rate: 45%
  }
}

2. Variable Substitution

"Hi {{family_name}}, your payment of {{amount|currency}} is due {{due_date|date_short}}."

→ "Hi Martinez Family, your payment of $450.00 is due Oct 15."

3. Conditional Sections

{{#if payment_overdue}}
  Your payment is now {{days_overdue}} days overdue.
{{else}}
  Your payment is due in {{days_until_due}} days.
{{/if}}

4. Channel-Specific Rendering

Same template, different channels: - Email: Full formatting, 200 words, HTML - SMS: Plain text, 160 chars, URL shortened - Portal: Medium format, 100 words, with action buttons - Voice: Spoken script, pauses, pronunciation guides

5. Domain Intelligence Encoding

Templates capture learning:

// Template metadata encodes best practices
best_practices: {
  send_time: "Tuesday 9-11am",  // 40% higher open rate
  subject_personalization: true,  // Include family name in subject
  include_phone: true,  // Coordinator phone increases response 60%
  tone: "friendly",  // Friendly outperforms formal by 25%
  cta_placement: "middle_and_end",  // Two CTAs increase action 35%
  include_help_offer: true  // "Need payment plan?" increases resolution 25%
}

6. A/B Testing Built In

template_variants: [
  {
    variant: "A",
    subject: "Payment Reminder - Action Needed",
    cta: "Pay Now"
  },
  {
    variant: "B", 
    subject: "Quick Question About Your Payment",
    cta: "Update Payment Method"
  }
]

7. Template Versioning

version: 3,
changelog: [
  { version: 3, date: "2024-10", change: "Added payment plan offer (↑25% resolution)" },
  { version: 2, date: "2024-08", change: "Changed tone from formal to friendly (↑15% response)" },
  { version: 1, date: "2024-06", change: "Initial version" }
]

Structure

Template Management Tables

-- Template definitions
CREATE TABLE message_templates (
  template_id INT PRIMARY KEY IDENTITY(1,1),

  template_name VARCHAR(200) NOT NULL,
  category VARCHAR(100),  -- 'payment', 'engagement', 'welcome', 'reminder'
  description NVARCHAR(1000),

  -- Version control
  version INT DEFAULT 1,
  status VARCHAR(50) DEFAULT 'active',  -- 'draft', 'active', 'archived'

  -- Content
  email_subject NVARCHAR(500),
  email_body NVARCHAR(MAX),
  email_from_name VARCHAR(200),

  sms_message NVARCHAR(500),

  portal_title NVARCHAR(200),
  portal_message NVARCHAR(2000),

  voice_script NVARCHAR(MAX),

  -- Metadata
  tone VARCHAR(50),  -- 'friendly', 'professional', 'urgent', 'formal'
  language VARCHAR(10) DEFAULT 'en',

  -- Intelligence
  best_send_time TIME,
  best_send_day VARCHAR(20),
  avg_response_time_hours INT,

  -- Performance
  times_sent INT DEFAULT 0,
  open_rate DECIMAL(5,2),
  response_rate DECIMAL(5,2),
  conversion_rate DECIMAL(5,2),

  -- Control
  requires_approval BIT DEFAULT 0,
  approved_by VARCHAR(100),
  approved_date DATETIME2,

  created_by VARCHAR(100),
  created_date DATETIME2 DEFAULT GETDATE(),
  updated_date DATETIME2 DEFAULT GETDATE(),

  CONSTRAINT UQ_template_name UNIQUE (template_name, version)
);

-- Template variables
CREATE TABLE template_variables (
  variable_id INT PRIMARY KEY IDENTITY(1,1),
  template_id INT NOT NULL,

  variable_name VARCHAR(100) NOT NULL,
  variable_type VARCHAR(50),  -- 'text', 'number', 'date', 'currency', 'boolean'
  required BIT DEFAULT 0,
  default_value NVARCHAR(500),

  -- Formatting
  format_pattern VARCHAR(200),  -- e.g., 'currency', 'date_short', 'phone'

  -- Validation
  validation_rule NVARCHAR(500),  -- JSON or regex

  -- Documentation
  description NVARCHAR(500),
  example_value NVARCHAR(200),

  CONSTRAINT FK_variable_template FOREIGN KEY (template_id)
    REFERENCES message_templates(template_id)
);

-- Template usage tracking
CREATE TABLE template_usage (
  usage_id INT PRIMARY KEY IDENTITY(1,1),
  template_id INT NOT NULL,

  family_id INT NOT NULL,

  -- Execution
  sent_date DATETIME2 DEFAULT GETDATE(),
  channel VARCHAR(50),  -- 'email', 'sms', 'portal', 'voice'

  -- Content
  rendered_subject NVARCHAR(500),
  rendered_message NVARCHAR(MAX),
  variables_used NVARCHAR(MAX),  -- JSON

  -- Variant (for A/B testing)
  variant_id INT,

  -- Delivery
  delivery_status VARCHAR(50),  -- 'sent', 'delivered', 'opened', 'clicked', 'bounced'
  opened_date DATETIME2,
  clicked_date DATETIME2,

  -- Response
  response_received BIT DEFAULT 0,
  response_date DATETIME2,
  response_type VARCHAR(100),

  CONSTRAINT FK_usage_template FOREIGN KEY (template_id)
    REFERENCES message_templates(template_id),
  CONSTRAINT FK_usage_family FOREIGN KEY (family_id)
    REFERENCES families(family_id)
);

-- Template variants (A/B testing)
CREATE TABLE template_variants (
  variant_id INT PRIMARY KEY IDENTITY(1,1),
  template_id INT NOT NULL,

  variant_name VARCHAR(50),  -- 'A', 'B', 'C'

  -- Variant content (overrides base template)
  variant_subject NVARCHAR(500),
  variant_body_diff NVARCHAR(MAX),  -- JSON patch or full override
  variant_cta_text VARCHAR(200),

  -- Performance
  times_sent INT DEFAULT 0,
  open_rate DECIMAL(5,2),
  response_rate DECIMAL(5,2),
  conversion_rate DECIMAL(5,2),

  -- Testing
  test_percentage INT DEFAULT 50,  -- % of sends that use this variant
  active BIT DEFAULT 1,

  CONSTRAINT FK_variant_template FOREIGN KEY (template_id)
    REFERENCES message_templates(template_id)
);

-- Template approval workflow
CREATE TABLE template_approvals (
  approval_id INT PRIMARY KEY IDENTITY(1,1),
  template_id INT NOT NULL,

  requested_by VARCHAR(100),
  requested_date DATETIME2 DEFAULT GETDATE(),

  approver VARCHAR(100),
  approval_status VARCHAR(50),  -- 'pending', 'approved', 'rejected'
  approval_date DATETIME2,
  approval_notes NVARCHAR(1000),

  CONSTRAINT FK_approval_template FOREIGN KEY (template_id)
    REFERENCES message_templates(template_id)
);

Implementation

Template Engine

class TemplateEngine {
  constructor(db) {
    this.db = db;
    this.cache = new Map();
  }

  async getTemplate(templateName, variant = null) {
    // Check cache first
    const cacheKey = `${templateName}_${variant || 'base'}`;
    if (this.cache.has(cacheKey)) {
      return this.cache.get(cacheKey);
    }

    // Load from database
    const template = await this.db.query(`
      SELECT * FROM message_templates 
      WHERE template_name = ? AND status = 'active'
      ORDER BY version DESC
      LIMIT 1
    `, [templateName]);

    if (!template.length) {
      throw new Error(`Template not found: ${templateName}`);
    }

    const tmpl = template[0];

    // Load variables
    const variables = await this.db.query(`
      SELECT * FROM template_variables WHERE template_id = ?
    `, [tmpl.template_id]);

    tmpl.variables = variables;

    // Load variant if specified
    if (variant) {
      const variantData = await this.db.query(`
        SELECT * FROM template_variants 
        WHERE template_id = ? AND variant_name = ? AND active = 1
      `, [tmpl.template_id, variant]);

      if (variantData.length > 0) {
        tmpl.variant = variantData[0];
      }
    }

    // Cache it
    this.cache.set(cacheKey, tmpl);

    return tmpl;
  }

  async render(templateName, context, channel = 'email') {
    // Get template
    const template = await this.getTemplate(templateName, context.variant);

    // Validate required variables
    this.validateContext(template, context);

    // Render based on channel
    let rendered;
    switch (channel) {
      case 'email':
        rendered = await this.renderEmail(template, context);
        break;
      case 'sms':
        rendered = await this.renderSMS(template, context);
        break;
      case 'portal':
        rendered = await this.renderPortal(template, context);
        break;
      case 'voice':
        rendered = await this.renderVoice(template, context);
        break;
      default:
        throw new Error(`Unknown channel: ${channel}`);
    }

    // Track usage
    await this.trackUsage(template, context, channel, rendered);

    return rendered;
  }

  validateContext(template, context) {
    // Check all required variables present
    for (const variable of template.variables) {
      if (variable.required && !(variable.variable_name in context)) {
        throw new Error(`Required variable missing: ${variable.variable_name}`);
      }

      // Validate value if present
      if (variable.variable_name in context) {
        this.validateVariable(variable, context[variable.variable_name]);
      }
    }
  }

  validateVariable(variable, value) {
    // Type validation
    switch (variable.variable_type) {
      case 'number':
        if (typeof value !== 'number') {
          throw new Error(`${variable.variable_name} must be a number`);
        }
        break;
      case 'date':
        if (!(value instanceof Date) && isNaN(Date.parse(value))) {
          throw new Error(`${variable.variable_name} must be a valid date`);
        }
        break;
      case 'currency':
        if (typeof value !== 'number' || value < 0) {
          throw new Error(`${variable.variable_name} must be a positive number`);
        }
        break;
    }

    // Custom validation rule
    if (variable.validation_rule) {
      const rule = JSON.parse(variable.validation_rule);
      if (rule.regex && !new RegExp(rule.regex).test(value)) {
        throw new Error(`${variable.variable_name} validation failed: ${rule.message}`);
      }
    }
  }

  async renderEmail(template, context) {
    // Apply variant if present
    let subject = template.email_subject;
    let body = template.email_body;

    if (template.variant) {
      subject = template.variant.variant_subject || subject;
      if (template.variant.variant_body_diff) {
        body = this.applyVariantDiff(body, template.variant.variant_body_diff);
      }
    }

    // Substitute variables
    subject = this.substituteVariables(subject, context);
    body = this.substituteVariables(body, context);

    // Process conditionals
    body = this.processConditionals(body, context);

    // Format for email (HTML, etc.)
    body = this.formatHTML(body);

    return {
      subject: subject,
      body: body,
      from_name: template.email_from_name || 'Homeschool Co-op'
    };
  }

  async renderSMS(template, context) {
    let message = template.sms_message;

    // Substitute variables
    message = this.substituteVariables(message, context);

    // Process conditionals
    message = this.processConditionals(message, context);

    // Ensure under 160 characters (standard SMS)
    if (message.length > 160) {
      console.warn(`SMS message exceeds 160 characters: ${message.length}`);
      // Could truncate or split into multiple messages
    }

    return {
      message: message
    };
  }

  async renderPortal(template, context) {
    let title = template.portal_title;
    let message = template.portal_message;

    // Substitute variables
    title = this.substituteVariables(title, context);
    message = this.substituteVariables(message, context);

    // Process conditionals
    message = this.processConditionals(message, context);

    return {
      title: title,
      message: message
    };
  }

  async renderVoice(template, context) {
    let script = template.voice_script;

    // Substitute variables
    script = this.substituteVariables(script, context);

    // Process conditionals
    script = this.processConditionals(script, context);

    // Add pronunciation guides, pauses
    script = this.formatVoice(script);

    return {
      script: script
    };
  }

  substituteVariables(text, context) {
    // Replace {{variable_name}} with context value
    return text.replace(/\{\{([^}|]+)(\|([^}]+))?\}\}/g, (match, varName, pipe, formatter) => {
      const value = context[varName.trim()];

      if (value === undefined) {
        return match;  // Leave placeholder if value missing
      }

      // Apply formatter if specified
      if (formatter) {
        return this.formatValue(value, formatter.trim());
      }

      return value;
    });
  }

  formatValue(value, formatter) {
    switch (formatter) {
      case 'currency':
        return new Intl.NumberFormat('en-US', {
          style: 'currency',
          currency: 'USD'
        }).format(value);

      case 'date_short':
        return new Date(value).toLocaleDateString('en-US', {
          month: 'short',
          day: 'numeric'
        });

      case 'date_long':
        return new Date(value).toLocaleDateString('en-US', {
          month: 'long',
          day: 'numeric',
          year: 'numeric'
        });

      case 'phone':
        // Format as (xxx) xxx-xxxx
        const cleaned = value.replace(/\D/g, '');
        return `(${cleaned.slice(0,3)}) ${cleaned.slice(3,6)}-${cleaned.slice(6,10)}`;

      case 'uppercase':
        return String(value).toUpperCase();

      case 'lowercase':
        return String(value).toLowerCase();

      case 'capitalize':
        return String(value).charAt(0).toUpperCase() + String(value).slice(1).toLowerCase();

      default:
        return value;
    }
  }

  processConditionals(text, context) {
    // Process {{#if condition}} ... {{/if}} blocks
    return text.replace(/\{\{#if ([^}]+)\}\}([\s\S]*?)\{\{\/if\}\}/g, (match, condition, content) => {
      if (this.evaluateCondition(condition, context)) {
        return content;
      }
      return '';
    });
  }

  evaluateCondition(condition, context) {
    // Simple condition evaluation
    // Examples: "payment_overdue", "engagement_score > 50"

    // Check for comparison operators
    const comparisonMatch = condition.match(/([^><=!]+)([><=!]+)(.+)/);
    if (comparisonMatch) {
      const [, left, operator, right] = comparisonMatch;
      const leftVal = context[left.trim()];
      const rightVal = parseFloat(right.trim()) || right.trim();

      switch (operator.trim()) {
        case '>': return leftVal > rightVal;
        case '<': return leftVal < rightVal;
        case '>=': return leftVal >= rightVal;
        case '<=': return leftVal <= rightVal;
        case '==': return leftVal == rightVal;
        case '!=': return leftVal != rightVal;
      }
    }

    // Simple boolean check
    return !!context[condition.trim()];
  }

  applyVariantDiff(baseBody, diff) {
    // Apply variant changes to base template
    // Could use JSON patch or simple string replacement
    try {
      const changes = JSON.parse(diff);
      let modified = baseBody;

      for (const change of changes) {
        modified = modified.replace(change.find, change.replace);
      }

      return modified;
    } catch (e) {
      return diff;  // If not JSON, treat as full override
    }
  }

  formatHTML(text) {
    // Convert plain text markers to HTML
    text = text.replace(/\*\*(.*?)\*\*/g, '<strong>$1</strong>');  // **bold**
    text = text.replace(/\*(.*?)\*/g, '<em>$1</em>');  // *italic*
    text = text.replace(/\n\n/g, '</p><p>');  // Paragraphs
    text = text.replace(/\n/g, '<br>');  // Line breaks

    return `<p>${text}</p>`;
  }

  formatVoice(text) {
    // Add SSML tags for text-to-speech
    text = text.replace(/\[pause\]/g, '<break time="500ms"/>');
    text = text.replace(/\[pause-long\]/g, '<break time="1s"/>');

    return `<speak>${text}</speak>`;
  }

  async trackUsage(template, context, channel, rendered) {
    await this.db.query(`
      INSERT INTO template_usage (
        template_id,
        family_id,
        channel,
        rendered_subject,
        rendered_message,
        variables_used,
        variant_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?)
    `, [
      template.template_id,
      context.family_id,
      channel,
      rendered.subject || '',
      rendered.body || rendered.message || '',
      JSON.stringify(context),
      template.variant?.variant_id || null
    ]);

    // Update template usage count
    await this.db.query(`
      UPDATE message_templates
      SET times_sent = times_sent + 1
      WHERE template_id = ?
    `, [template.template_id]);
  }
}

module.exports = TemplateEngine;

Usage Example

const engine = new TemplateEngine(db);

// Render payment reminder
const rendered = await engine.render('payment_reminder_friendly', {
  family_id: 123,
  family_name: 'Martinez Family',
  parent_first_name: 'Maria',
  amount: 450,
  due_date: new Date('2024-10-15'),
  days_overdue: 3,
  coordinator_name: 'Sarah',
  coordinator_phone: '(555) 123-4567',
  payment_overdue: true,
  variant: 'A'  // For A/B testing
}, 'email');

console.log(rendered.subject);
// "Quick Question About Your Payment, Martinez Family"

console.log(rendered.body);
// Hi Martinez Family,
//
// I hope you and Maria are doing well! I wanted to reach out about your 
// payment of $450.00 which was due on Oct 15.
//
// Your payment is now 3 days overdue.
//
// I know life gets busy! If you need a payment plan or have any questions,
// please don't hesitate to call me directly at (555) 123-4567.
//
// Thanks so much!
// Sarah

// Render same template as SMS
const sms = await engine.render('payment_reminder_friendly', {
  family_id: 123,
  family_name: 'Martinez',
  amount: 450,
  due_date: new Date('2024-10-15')
}, 'sms');

console.log(sms.message);
// Hi Martinez! Payment of $450 was due Oct 15. Need payment plan? Call Sarah (555) 123-4567

Variations

By Complexity

Simple Variable Substitution: - {{name}}, {{amount}}, {{date}} - Easy to understand - Limited flexibility

Conditional Logic: - {{#if overdue}}...{{/if}} - More sophisticated - Context-aware messaging

Full Templating Language: - Loops, functions, filters - Very powerful - Requires learning curve

By Channel Strategy

Separate Templates Per Channel: - email_payment_reminder, sms_payment_reminder - Channel-optimized - More templates to maintain

Unified Multi-Channel: - One template, renders to all channels - DRY principle - Complex rendering logic

Hybrid: - Shared content, channel-specific sections - Best of both

By Maintenance

Centrally Managed: - IT/marketing maintains all templates - Quality control - Bottleneck for changes

Distributed Creation: - Coordinators create own templates - Fast iteration - Consistency risk

Hybrid with Approval: - Anyone creates, admin approves - Balance speed and quality

Consequences

Benefits

1. Consistency Every payment reminder uses proven approach (not reinvented each time).

2. Speed Send message in 30 seconds (not 30 minutes).

3. Domain intelligence preserved 5 years of learning encoded in template (survives personnel changes).

4. Personalization at scale Send 100 personalized messages (same effort as 1).

5. Multi-channel easy Render to email, SMS, portal from one template.

6. A/B testing built in Test variants systematically.

7. Quality control Templates can require approval before use.

8. Learning loop Track what templates work (improve over time).

Costs

1. Upfront investment Building template system takes time.

2. Maintenance burden Templates need updating as business changes.

3. Flexibility trade-off Templates constrain (can't customize everything).

4. Learning curve Staff needs to learn template system.

5. Version control Need system to track template changes.

6. Testing complexity More templates = more to test.

Sample Code

Create new template:

async function createTemplate(templateData) {
  const result = await db.query(`
    INSERT INTO message_templates (
      template_name,
      category,
      email_subject,
      email_body,
      sms_message,
      tone,
      best_send_time
    )
    OUTPUT INSERTED.template_id
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `, [
    templateData.name,
    templateData.category,
    templateData.emailSubject,
    templateData.emailBody,
    templateData.smsMessage,
    templateData.tone,
    templateData.bestSendTime
  ]);

  const templateId = result[0].template_id;

  // Add variables
  for (const variable of templateData.variables) {
    await db.query(`
      INSERT INTO template_variables (
        template_id, variable_name, variable_type, required
      ) VALUES (?, ?, ?, ?)
    `, [templateId, variable.name, variable.type, variable.required]);
  }

  return templateId;
}

Known Uses

Homeschool Co-op Intelligence Platform - 25 templates covering payments, engagement, welcome, reminders - 68% increase in response rates (vs custom messages) - Templates encode 5 years of coordinator learning - A/B testing improved open rates 15%

Email Marketing Platforms - Mailchimp: Drag-and-drop template builder - SendGrid: Template engine with variables - HubSpot: Smart content based on context

CRM Systems - Salesforce: Email templates with merge fields - HubSpot: Sequences with personalization tokens - Intercom: Message templates for support

Communication Platforms - Twilio: SMS template engine - SendGrid: Transactional email templates - Postmark: Template variables and batching

Used By: - Pattern 21: Automated Workflow Execution - workflows send templated messages - Pattern 22: Progressive Escalation - each step uses template - Pattern 23: Triggered Interventions - triggers send templated messages

Requires: - Pattern 1: Universal Event Log - track template usage

Enables: - Pattern 25: Multi-Channel Orchestration - templates render to multiple channels - Pattern 26: Feedback Loop - track template effectiveness

References

Templating Engines

Email Templates

Voice & Conversational

Content Management

  • Pattern 24: Template-Based Communication - Personalized messaging at scale
  • Pattern 25: Multi-Channel Orchestration - Templates across channels
  • Pattern 22: Progressive Escalation Sequences - Template-based escalation messages
  • Volume 3, Pattern 4: Coherent Closure - Communication closure patterns

Practical Implementation

Tools & Services